What the ORM generates in Postgres

In part 1 I walked through the collection config (the one layer of this site's data stack I actually write) and left a thread hanging: Posts has a beforeDelete hook that removes a post's comments before the post itself, and I claimed the schema forced that, not me. This post is where that claim gets paid off, along with the rest of what @payloadcms/db-postgres builds on my behalf.

One framing fact makes everything else make sense: there is no schema file. Payload's Postgres adapter wraps Drizzle, and the Drizzle schema is built in memory at boot, derived from the collection configs. Nothing in the repo declares a posts table. The only durable artifacts are the migrations (plain SQL in .ts files) and, next to each one, a .json snapshot of the entire schema as Drizzle understood it at that moment. So if you want to know what your collections actually became, the docs won't tell you and the repo can't. You open psql and look.

That's what this post is: me, psql, and \d, working out the mapping rules one table at a time.

One collection, one table

Start with the obvious layer. \d posts, trimmed to the interesting parts:

\d posts
--  id            | integer                     | not null
--  title         | character varying           |
--  slug          | character varying           |
--  description   | character varying           |
--  content       | jsonb                       |
--  published_at  | timestamp(3) with time zone |
--  category_id   | integer                     |
--  created_by_id | integer                     |
--  _status       | enum_posts_status           | default 'draft'
--  updated_at    | timestamp(3) with time zone | not null
--  created_at    | timestamp(3) with time zone | not null

A collection becomes a snake_case table with a serial id and managed timestamps. Simple fields become the obvious columns: text and textarea are both varchar. Every field I marked index: true in the config has its btree index here, plus ones Payload adds on its own (created_at, updated_at, _status). Camel case gets mechanically snaked, with occasionally comic results: the Comments collection's authorIP field lives in a column called author_i_p.

The column that surprised me is content. The entire Lexical rich-text tree (every paragraph, heading, and my custom code-snippet, terminal, and Mermaid blocks) is one jsonb column. I'd assumed the editor blocks would get their own tables; they don't. They live inside the JSON, and the block schemas from the config exist only to drive the editor UI and the generated types. Postgres does document-store duty for the one field that's genuinely document-shaped, and stays relational for everything else.

Relationships: two shapes, and an ultimatum

Single relationships are plain foreign key columns. category (hasMany: false) is category_id integer REFERENCES categories(id) ON DELETE SET NULL: delete a category and its posts just lose the reference.

Here's where part 1's hanging thread resolves. A required single relationship gets a NOT NULL column, but Payload still generates the same ON DELETE SET NULL behavior on the foreign key. Look at what that combination means on comments:

\d comments
--  post_id | integer | not null
--  ...
-- Foreign-key constraints (from \d posts, "Referenced by"):
--   comments_post_id_posts_id_fk
--     FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE SET NULL

Delete a post that still has comments, and Postgres tries to null out each comment's post_id: a column that is NOT NULL. Constraint violation; the delete aborts. There is no configuration I wrote that says "you must delete a post's comments first." The generated schema says it, in the form of two constraints that cannot both be satisfied. That's why the beforeDelete hook from part 1 exists: comments have to go first, and the schema is what decided that, not me. (It's also the right behavior: a deleted post's thread should go with it, but the hook is there because the constraints made it mandatory, not because I was being principled about composition.)

hasMany relationships get a different shape entirely. There's no tags_id on posts. Instead there's a join table:

\d posts_rels
--  id        | integer           | not null
--  order     | integer           |
--  parent_id | integer           | not null   -> posts(id)  ON DELETE CASCADE
--  path      | character varying | not null   -- which field: 'tags'
--  tags_id   | integer           |            -> tags(id)   ON DELETE CASCADE

The design choice worth noticing: this is not a posts_tags table. It's posts_rels: one table per collection carrying all of that collection's hasMany relationships, discriminated by the path column, with order preserving the array order you see in the admin. Add a second many-relationship to Posts and it doesn't get a new table; it gets a new nullable *_id column in this one, with path telling the rows apart. Deletes cascade here, which is what quietly cleans up a deleted post's tag links (and, from the series side, its series membership).

Arrays are a third shape that looks like a cousin of the second. The socialLinks array on the SiteSettings global becomes site_settings_social_links: one row per item, _order and _parent_id columns (underscored, unlike the rels table's), each item's fields as real columns, and ON DELETE CASCADE to the parent. Auth gets the same treatment: every login session is a row in users_sessions. Rows you never think about, managed by fields you barely remember writing.

Drafts double the storage

Part 1 called versions.drafts the most consequential line in the config. Here's its footprint in the schema. Enabling it created a parallel table:

\d _posts_v
--  id                 | integer   -- the version row's own id
--  parent_id          | integer   -> posts(id)
--  version_title      | varchar
--  version_slug       | varchar
--  version_content    | jsonb
--  version__status    | enum__posts_v_version_status
--  latest             | boolean
--  ...a version_* copy of every content column

Every save writes a row here; the latest flag marks each document's current version, and the admin's history/compare/restore UI reads from this table. The posts row is just the currently-published projection of a document whose real life is in _posts_v. Even with the config capping versions at 50 per post, _posts_v is the biggest table in the database: on my copy it's twice the size of posts itself. Drafts doubled the storage before I wrote a single new post.

The same migration that created it did something sneakier. This is my favorite line in any of this site's migrations:

ALTER TABLE "posts" ALTER COLUMN "title" DROP NOT NULL;

Title, slug, description, content: all required: true in the config, all nullable in the database. It has to be that way: a draft must be saveable half-finished. But it means that after drafts, required is no longer a database constraint. It's enforced by Payload, at publish time, in the application layer. The word in the config didn't change; its meaning did.

Rounding out the inventory: each status field becomes a real Postgres enum type (enum_posts_status, enum__posts_v_version_status: remember those for part 3, they have a war story), and Payload provisions a set of system tables you never asked for but constantly use: payload_migrations (bookkeeping for part 3), payload_preferences (admin UI state), payload_locked_documents (the "someone else is editing this" lock), payload_jobs (where scheduled publishes queue), payload_kv.

The whole mapping for Posts in one picture:

What depth actually does

Every Payload query takes a depth option, and the docs describe it as how many levels of relationships get "populated." I wanted to know what that means in SQL, so I turned on statement logging (ALTER SYSTEM SET log_statement = 'all' on the local Docker Postgres) and ran the same payload.find() for one post twice.

At depth: 0, one query. Heavily abridged. The real thing is a wall of generated aliases:

SELECT posts.*,
  posts__rels.data AS _rels
FROM posts
LEFT JOIN LATERAL (
  SELECT coalesce(json_agg(
    json_build_array(r."order", r.path, r.tags_id) ORDER BY r."order"
  ), '[]') AS data
  FROM posts_rels r
  WHERE r.parent_id = posts.id
) posts__rels ON true
WHERE posts.slug = $1
LIMIT 1;

The relationship rows come back in the same round trip, aggregated into a JSON array by a lateral join, but only as ids. The logged query also had a correlated subquery in the select list I didn't expect: for the join field, walking series_rels backwards to find which series owns this post. Part 1 said the join field stores nothing; here's the receipt. It's recomputed from the other side's rels table on every single read.

At depth: 1, the exact same query runs first, and then a follow-up per related collection:

SELECT users.* FROM users WHERE users.id IN ($1);

So depth is not a JOIN fan-out, and popping it higher doesn't build some monster query. It's breadth-first: fetch the documents, collect the foreign ids, run one IN (...) query per related collection, recurse until the depth runs out. Predictable, no row explosion, but every level is another round of queries. Which explains two choices in this site's frontend that predate my understanding of why they were right: every list query pins depth: 1, and the category-tree builder uses depth: 0 and resolves the bare parent ids itself rather than letting Payload chase them.

The schema is legible: read it

What I took away from this layer: the mapping is boringly predictable once you know the five shapes: column, jsonb, FK, rels table, version table. "ORM magic" turned out to be a short list of rules applied uniformly, and the generated schema is worth actually reading, because it occasionally encodes decisions you didn't consciously make. The comments ultimatum and the DROP NOT NULL were both sitting in plain sight in psql the whole time.

There's one artifact from this layer I've barely mentioned: that .json snapshot sitting next to each migration. It's how a config edit becomes an ALTER TABLE: the next migration is generated by diffing the config-derived schema against the last snapshot. That diff, and everything that can go wrong between my laptop and production while applying it, is part 3.