Relations

Relations connect entities to each other. CMS supports three types of relations, all defined with the same relation() helper.

Single relations

A single relation stores a foreign key column on the entity's table.

          import { entity, text, relation } from "@cms/config";

const authors = entity("authors", {
  fields: [text("name", { required: true })],
});

const posts = entity("posts", {
  fields: [
    text("title", { required: true }),
    relation("author", { to: authors }),
  ],
});
        

This creates an author_id column on the posts table referencing authors.id.

In the API, an unresolved single relation returns a { id, _entity } reference:

          { "id": "...", "title": "My Post", "author": { "id": "author-uuid", "_entity": "authors" } }
        

Many-to-many relations

Set multiple: true to create a junction table.

          const posts = entity("posts", {
  fields: [
    text("title", { required: true }),
    relation("tags", { to: tags, multiple: true, min: 1, max: 5 }),
  ],
});
        

This creates a posts_tags junction table with sourceId, targetId, and position columns. Position preserves insertion order. The database enforces referential integrity — deleting a tag automatically removes it from all posts.

min and max are optional and validate the array length.

• On a required: true field, bounds are enforced unconditionally. An implicit min: 1 applies when no min is given; an explicit min takes precedence.
• On an optional field, empty is treated as absent — bounds only apply when the field is filled. So min: 2 on an optional field means "if you provide tags, list at least 2, otherwise leave blank."

Negative bounds, min > max, and the contradictory required: true + min: 0 combo are rejected at config build.

array(of: relation(...)) is rejected at config build. Use relation({ multiple: true }) for many-relations — it's the one canonical form (junction table, referential integrity, ordering, filtering, validation).

Unresolved multiple relations return an array of { id, _entity } references:

          { "id": "...", "title": "My Post", "tags": [
  { "id": "tag-1", "_entity": "tags" },
  { "id": "tag-2", "_entity": "tags" }
] }
        

Polymorphic relations

A polymorphic relation can point at one of several target entities. Pass an array of two or more entities to to:

          const article = entity("article", {
  fields: [
    text("title", { required: true }),
    relation("author", { to: [staffAuthors, guestAuthors] }),
  ],
});
        

Storage

The schema-builder emits a discriminator column alongside the id:

• Single polymorphic (<name>_id, <name>_type):

  
            author_id   UUID
  author_type TEXT NOT NULL   -- e.g. 'staffAuthors' or 'guestAuthors'
          

• Many polymorphic (junction table) — target_id + target_type:

  
            source_id   UUID FK to <source>.id ON DELETE CASCADE
  target_id   UUID
  target_type TEXT NOT NULL
  position    INTEGER
          

Polymorphic columns have no FK constraint — Postgres can't enforce a foreign key that points at multiple tables. The engine validates the discriminator and target existence at write time, and cleans up references when the target row is deleted (see Delete behavior below). Single-target relations are unchanged (FK + cascade).

Reading

Unresolved polymorphic relations use the same { id, _entity } shape as single-target relations — _entity is a union of the configured target names:

          { "id": "...", "title": "X", "author": { "id": "uuid", "_entity": "staffAuthors" } }
        

After resolution, _entity stays on the resolved object so consumers can discriminate:

          GET /api/articles/uuid?resolve[author]=*
        

          {
  "id": "...",
  "title": "X",
  "author": {
    "id": "...",
    "_entity": "staffAuthors",
    "name": "Ada",
    "employeeId": "E1"
  }
}
        

Many polymorphic works the same way:

          {
  "id": "...",
  "topics": [
    { "id": "...", "_entity": "tags", "label": "design" },
    { "id": "...", "_entity": "categories", "name": "engineering" }
  ]
}
        

Writing

All relations use the same { id, _entity } shape for writes — single-target, multiple, and polymorphic:

          { "title": "X", "author": { "id": "uuid", "_entity": "staffAuthors" } }
        

Multiple:

          { "title": "X", "topics": [
  { "id": "uuid-a", "_entity": "tags" },
  { "id": "uuid-b", "_entity": "categories" }
] }
        

The engine validates at write time:

• _entity is one of the configured targets (for single-target relations, it must match exactly).
• For polymorphic relations, the (id, _entity) pair points at an existing row in that target table (replaces the FK integrity Postgres would have given us if FKs could span tables).
• For single-target relations, _entity is validated by the Zod schema but the engine only persists the id (the target is known from the config).

Filtering

Filter syntax mirrors single-target relations with two rules layered on top:

Common-shape fields filter implicitly across every target. The engine builds a UNION ALL of subqueries — one per target table:

          GET /api/articles?filter[author.name]=Pedro
        

This works only when every target in to has a field named name (with a compatible column type).

Type-specific fields require an <name>_type scope in the same request. If a field exists on only some targets, the engine throws a clear error pointing at the missing targets:

          GET /api/articles?filter[author.employeeId]=E1
→ Field "employeeId" is not common across polymorphic targets of "author".
  Missing on: guestAuthors. Add ?filter[author_type]=<target> to scope.
        

Adding the scope narrows the subquery to a single target table:

          GET /api/articles?filter[author_type]=staffAuthors&filter[author.employeeId]=E1
        

The discriminator column itself filters like any other column — implicit $eq, plus $in / $neq / etc.

          GET /api/articles?filter[author_type][$in]=staffAuthors,guestAuthors
        

Operator forms on the discriminator don't auto-scope traversals: if you write ?filter[author_type][$in]=a,b&filter[author.name]=..., the name filter still uses the default UNION across all configured targets.

Delete behavior

Polymorphic columns have no FK cascade. The engine compensates by scanning every polymorphic relation in the config when a target row is deleted:

• For polymorphic single refs: matching (id, type) pairs on source rows are set to NULL.
• For polymorphic many refs: matching junction rows are deleted.

Single-target FK cascades still happen at the Postgres level and aren't touched by this scan.

Migration recipe — switching to polymorphic

The schema-diff layer doesn't auto-generate polymorphic transitions yet. When you change a single-target relation to polymorphic in your config, run this SQL manually before deploying the new schema:

          -- For a single-target relation `author` that points at `authors`:
ALTER TABLE article
  ADD COLUMN author_type TEXT NOT NULL DEFAULT 'authors';
ALTER TABLE article ALTER COLUMN author_type DROP DEFAULT;

-- For a multi-relation junction (table `article_tags` pointing at `tags`):
ALTER TABLE article_tags
  ADD COLUMN target_type TEXT NOT NULL DEFAULT 'tags';
ALTER TABLE article_tags ALTER COLUMN target_type DROP DEFAULT;
ALTER TABLE article_tags DROP CONSTRAINT article_tags_target_id_fk; -- if it exists
        

Replace 'authors' / 'tags' with whatever the previous single-target was. The default-then-drop pattern backfills existing rows; new writes still require an explicit _entity.

Brand-new polymorphic relations need no migration — the engine creates the discriminator columns on first cms migration push.

Sorting across polymorphic targets

?sort=author.name works when name exists on every configured target with a compatible column type (the same common-shape rule as filters). The engine emits a CASE expression keyed on the discriminator column that pulls the value from the correct target per row.

Type-specific fields error out — there's no scoping option for sort because the result set must remain consistent across all rows. Many-relations (?sort=tags.label) aren't supported either: there's no canonical aggregation when each row has multiple targets.

Removing a target

Dropping an entity from a polymorphic to: […] is a breaking schema change. Existing rows with the removed discriminator value stay in the database but:

• The CHECK constraint refuses any new writes that mention them, so updates start failing once the new config is deployed.
• The resolver silently skips them in API responses (orphan-looking rows appear with their raw UUID instead of a resolved object).

Run a cleanup migration before removing the target — either delete the rows, reassign their discriminator to a remaining target, or drop the CHECK constraint first and reinstate it after the cleanup. The engine has no automatic guard for this case.

Performance notes

• UNION ALL cost grows with to.length. Filtering across a 5-target polymorphic relation runs 5 subqueries unioned per filter — keep the target list focused on entities that genuinely share the relation. The engine creates a composite index on (<name>_type, <name>_id) for source rows and on (target_type, target_id) for polymorphic junctions, so individual lookups stay fast.
• Resolver mutates rows in place. resolveRelations writes resolved objects directly onto the input array (and into nested JSONB payloads). This is fine for normal use — the engine owns the row objects between fetch and serialization — but anything reusing those references after the resolver runs sees the mutated shape.

Migrations

cms migration generate now detects polymorphic transitions in the snapshot diff and writes a structured comment block (and, when safe, a backfill UPDATE) into the migration file:

• to: "x" → to: ["x", "y"] — backfills the new <name>_type column with the previous single target.
• to: ["x", "y"] → to: "x" — emits an inspect query for rows whose _type doesn't match the new single target so the operator can decide how to handle orphans.
• to: ["x", "y"] → to: ["x", "y", "z"] — no data work; the existing rows are still valid.
• to: ["x", "y", "z"] → to: ["x", "y"] — emits an inspect query for rows whose _type is in the removed list; they'd violate the new CHECK constraint.

The polymorphic comments live next to the drizzle-kit DDL in the migration file — review them before running cms migration apply.

Known limitations

A few rough edges remain:

• Sort by many-relation field isn't supported (no canonical aggregation across multiple targets).
• cms migration generate doesn't auto-split column NOT-NULL into add-then-backfill-then-tighten. If a polymorphic transition lands a NOT NULL <name>_type column on a table with existing rows, the DDL fails. Run the backfill comment from the generated file first (UPDATE … SET <name>_type = …), then re-run cms migration apply.

Resolving relations

By default, relations return raw IDs. Use the resolve query parameter to expand them into full objects:

          GET /api/posts?resolve[author]=*
GET /api/posts?resolve[author]=name,bio
GET /api/posts?resolve[tags]=title
        

Resolved response:

          {
  "data": {
    "id": "...",
    "title": "My Post",
    "author": {
      "id": "author-uuid",
      "name": "Pedro",
      "bio": "Design engineer"
    }
  }
}
        

Field selection

Each resolve[...] key controls exactly what its target returns. id is always included.

• resolve[field]=* — all fields on the related entity
• resolve[field]=name,bio — only the listed fields plus id

Chained resolution

Drill into a relation's own relations with dot notation. The parent is implied — you don't need to list it separately.

          GET /api/posts?resolve[author.avatar]=url
        

Response:

          {
  "author": {
    "id": "author-uuid",
    "avatar": { "id": "image-uuid", "url": "/img/pedro.png" }
  }
}
        

The parent (author) defaults to just its id plus whatever drill-downs were requested. To return more parent fields, name it explicitly alongside the chain:

          GET /api/posts?resolve[author]=name&resolve[author.avatar]=url
        

          {
  "author": {
    "id": "author-uuid",
    "name": "Pedro",
    "avatar": { "id": "image-uuid", "url": "/img/pedro.png" }
  }
}
        

Chains work the same way through multi-relations and through nested fields (see below). Maximum chain depth is 2 — resolve[a.b.c.d]=* resolves a.b.c but stops there.

Nested fields

Relations defined inside object(), union(), array(), or richText() fields are stored in JSONB but still resolve through the same syntax. Use dot notation to address them by their position in the schema, not their JSONB shape.

For a page with a blocks array:

          const page = entity("page", {
  fields: [
    array("blocks", {
      of: union("block", {
        of: [
          object("author_feature", {
            fields: [
              text("title"),
              relation("author", { to: authors }),
            ],
          }),
        ],
      }),
    }),
  ],
});
        

Resolve the author inside every author_feature block:

          GET /api/pages/uuid?resolve[blocks.author]=name
        

Variants of a union that don't match are left untouched — a hero block in the same array stays as-is.

For richText fields, the same syntax works for custom blocks, inline blocks, and annotations:

          richText("content", {
  blocks: [
    richText.block("author_card", {
      fields: [relation("author", { to: authors })],
    }),
  ],
  annotations: [
    richText.annotation("link", {
      fields: [relation("target", { to: pages })],
    }),
  ],
});
        

          GET /api/posts/uuid?resolve[content.author_card.author]=name
GET /api/posts/uuid?resolve[content.link.target]=title,slug
        

Chained resolution composes with nested fields:

          GET /api/pages/uuid?resolve[blocks.author.avatar]=url
        

Performance

• All resolution uses batched WHERE id IN (...) queries — never N+1.
• When the same name appears in multiple richText slots (e.g. registered as both a block and an inline block), the resolver coalesces them into a single batch per target entity.
• Unresolved relations on resolved entities return raw IDs unless explicitly resolved.

Filtering across relations

You can filter by fields on related entities using dot notation:

          GET /api/posts?filter[author.name]=Pedro
        

This generates a subquery that joins to the related table. See Querying for more on filtering.