Entities and migrations

An entity is a table + its domain type + its invariants. The single source of the DB schema, the TS type, and the parse boundary.

One entity() call per table, in packages/db/src/schema/<name>.ts; a feature's own entity.ts holds only that feature's view schemas. Migrations sit beside the entities in packages/db/migrations/ as plain SQL. Neither holds any business logic. Reads and writes go through @ultimat3/entity's own postgresDriver(), which compiles a query plan to parameterised SQL — no ORM in the request path (pg-driver.ts).

Six projections

One inferred chain, no hand-typed link:

entity('posts', { columns })  →  typeof posts.$row + invariants  →  action input/output  →  typed client + MCP tool  →  component props

Rename a column and the entity type changes, the action's output stops matching, and the component prop errors — all at typecheck, before a test runs.

Shape

As of 2026-08:

export const posts = entity('posts', {
  columns: {
    id: uuid().primaryKey(),
    orgId: uuid().references(() => orgs.id, { onDelete: 'cascade' }).tenant(),
    title: text({ max: TITLE_MAX }),
    body: text(),
    status: enumerated(POST_STATUSES).default('draft'),
    likeCount: integer().default(0),
    publishedAt: timestamp().nullable(),
    createdAt: timestamp().defaultNow(),
    updatedAt: timestamp().defaultNow().onUpdateNow(),
  },
  tenant: 'orgId',   // said out loud; inferred from `.tenant()` or an `orgId` column if omitted
  invariants: (c) => [
    invariant('post_title_present', c.title.trimmed().minLength(1)),
    invariant('post_like_count_non_negative', c.likeCount.atLeast(0)),
  ],
  indexes: [{ on: ['orgId', 'publishedAt'], order: 'desc' }],
});

export type Post = typeof posts.$row;

The entity name is the first argument. Everything else is the init object:

Presence of a deletedAt column is what makes an entity soft-deletable — not a flag.

Which invariants reach the database

"Where expressible" is a rule, not a hedge. An invariant reports a sql: string or sql: null, and $describe() tells you which — a rule the database does not know is a rule a bulk UPDATE from a migration or a psql session can violate.

iff renders =, not IS NOT DISTINCT FROM, and that is not an oversight. Every operator in this language is false on a null operand in TypeScript, and = between two booleans where either is NULL yields NULL — which a CHECK passes. The total form fixes that row and breaks a worse one: with a partial operand it makes Postgres refuse a row TypeScript accepted, which reaches the caller as a raw 23514 instead of X_INVARIANT_VIOLATED. = keeps the disagreement in the direction where the app refuses first, so no write reaches a CHECK that would have refused it. Where both operands are total — a non-nullable column, an IS NOT NULL — the two spellings are the same predicate, measured.

Pass the pattern, not a function wrapping it. matches(isValidSlug) and matches(SLUG_PATTERN) read almost identically and only one of them reaches a constraint.

Nothing is translated. pattern.source is the string .test() runs and the string spliced into the CHECK — one string, two engines, so they cannot drift. That is only safe because the constructs where the two engines disagree are refused at declaration:

The refusal is X_INVARIANT_VIOLATED at entity() time — before a migration exists — and names the construct, its index, both readings, and the portable spelling where there is one. The subset is measured against a live server and re-measured by its own tests, so a future Postgres that closes a gap turns those tests red rather than leaving a stale exclusion in place.

The fluent surface

Every projection is a method on the entity — posts.$view(['id', 'title']), never view(posts, ['id', 'title']) — and every declared field is lifted onto it under a $. An entity has no .def.

entity is the one primitive whose surface is $-prefixed, and the type says why: type Entity<Row, C> = EntityCore<Row, C> & C — the entity is its columns. posts.title is a column reference, so an unprefixed member would make view, name or tenant an illegal column name; with the sigil, a column called name and the entity's own name coexist as posts.name and posts.$name. action, query and job carry no sigil because nothing is merged into them. Reaching for posts.view([…]) after reading Actions does not typecheck — there is no such member, only $view, and there is no free view(entity, keys) to import either. Nothing is hidden the way an action's handle is: an entity has no body to protect, so the whole declaration reads back, and the sigil protects the column namespace instead. Registration is not a second step — entity() registers itself as it is declared, which is why a duplicate name is X_ENTITY_DUPLICATE at import rather than a silent last-one-wins.

$view() is what closes the type chain. output: posts.$view(['id', 'title', 'excerpt']) returns a Standard Schema over Pick<Row, K>, so renaming a column stops the key list itself from compiling, and the action's output type and every consumer of it fail in the same pass — not at runtime as a missing field. It is also introspectable, As of 2026-09-05: the view carries the same schema IR a t.object does, so that output: reaches openapi.json, the MCP tool's outputSchema and the typed client with each column's own constraints (text({ max: 2000 }) publishes maxLength: 2000). Until then a view was refused at registration with X_SCHEMA_UNSUPPORTED and every app kept a hand-copied t.object beside its entity — a copy that had already drifted in the reference app. What it catches is the key list and the projected type; a value is still checked at runtime, by the column that owns it rather than by a second copy of its rule. A view never invents a default: it projects a row that already exists, so an absent required column is missing data.

Tenant column rule

Every multi-tenant entity declares tenant. Every query against it is tenant-scoped or it fails:

X_TENANCY_UNSCOPED: query is not scoped to a tenant
  cause: select on "posts" has no predicate on tenant column "orgId"
  fix:   query through posts repo (ctx.posts.*) — it applies the tenant scope from ctx.actor

Which column that is, in order — first match wins:

Omitting tenant keeps steps 2–4, so silence never means unscoped. A tenant key naming no column is a declaration error — because the alternative is a silently unscoped table. The error lists the columns to pick from and both edits that resolve it, so the declaration is repaired without opening the entity file:

X_INVARIANT_VIOLATED: a domain invariant rejected this row
  cause: posts.tenant: tenant: 'workspaceId' names no column — pick from: id, title, body, orgId
  fix:   set tenant to one of id, title, body, orgId in entity('posts'), or remove the tenant key — inference then takes the .tenant() column, else one named orgId

Removing the key is a real option, not a hedge: inference (steps 2–4) still applies, so the table stays scoped whenever a column is marked .tenant() or named orgId.

Same-tenant keys

A references() with both ends tenant-scoped is a composite key, and refuses a row pointing into another tenant. As of 2026-11-02, the default.

import { entity, text, uuid } from '@ultimat3/entity';

const orgs = entity('orgs', { columns: { id: uuid().primaryKey() } });
const posts = entity('posts', {
  columns: { id: uuid().primaryKey(), orgId: uuid().references(() => orgs.id).tenant() },
});
const items = entity('items', {
  columns: { id: uuid().primaryKey(), orgId: uuid().references(() => orgs.id).tenant() },
});

export const comments = entity('comments', {
  columns: {
    id: uuid().primaryKey(),
    orgId: uuid().references(() => orgs.id).tenant(),
    postId: uuid().references(() => posts.id), // (org_id, post_id) -> posts (org_id, id)
    // the plain key, on purpose
    catalogId: uuid().references(() => items.id, { crossTenant: 'a shared catalog row' }),
    body: text(),
  },
});

Locking a row or a key

A read-modify-write through the handle loses updates. db.<table>.lock does not. As of 2026-11-02.

import { database, entity, integer, type Tx, uuid } from '@ultimat3/entity';

const posts = entity('posts', {
  columns: { id: uuid().primaryKey(), likeCount: integer().default(0) },
});
const invoices = entity('invoices', {
  columns: { id: uuid().primaryKey(), number: integer() },
});
const db = database({ posts, invoices });
declare function nextNumber(year: number, tx: Tx): Promise<number>;

export async function like(postId: string): Promise<void> {
  await db.posts.lock(postId, async (post, tx) => {
    await db.posts.update(postId, { likeCount: post.likeCount + 1 }, { tx });
  });
}

export async function nextInvoice(year: number): Promise<number> {
  return db.invoices.lockKey(`number:${String(year)}`, async (tx) => nextNumber(year, tx));
}

The repo

A feature's repo.ts is the only module that touches the database, and every statement in it goes through the typed handle. As of 2026-10.

// apps/web/app/widget/repo.ts — as `x g entity widget` writes it
import { db } from '@myapp/db';
import type { Widget } from './entity';

export async function byId(id: string): Promise<Widget | undefined> {
  return (await db.widgets.where({ id }).one()) ?? undefined;
}

export async function list(limit = 50): Promise<readonly Widget[]> {
  return db.widgets.orderBy('createdAt', 'desc').limit(limit).all();
}

export async function insert(row: Omit<Widget, 'id' | 'createdAt'>): Promise<Widget> {
  return db.widgets.insert(row);
}

// packages/db/src/client.ts — the handle. `x g entity` adds the import and the line.
const entities = {
  posts: post,
  widgets: widget,
};

export const db = database(entities, { driver });

Paging a raw join

keyset() (@ultimat3/db): keyset paging for a statement the handle cannot say. .after() on the handle, keyset() in a raw read: two surfaces, never two ways for one read. As of 2026-11-06.

import { db, keyset, raw, sql } from '@ultimat3/db';

export async function feed(orgId: string, after: string | null) {
  const page = keyset({
    scope: `posts.feed:${orgId}`,
    keys: [
      { column: raw('p.created_at'), kind: 'timestamptz', direction: 'desc' },
      { column: raw('p.id'), kind: 'uuid', direction: 'desc' },
    ],
    after,
    limit: 50,
  });
  const rows = await db().query<{ id: string; title: string; author: string }>(
    sql`select p.id, p.title, m.name as author, ${page.select}
        from posts p join members m on m.id = p.author_id
        where p.org_id = ${orgId} and ${page.where}
        order by ${page.orderBy} limit ${page.limit}`,
  );
  return page.page(rows); // core's Page: { rows, nextCursor, hasMore }
}

The generated repo.test.ts runs all three functions against the in-memory driver, inside runWithContext — the actor is what scopes the read, so a test names one, and asserts that an actor in another org reads nothing with no org named in any call. Row equality is expect(row).toEqualRow(other): a sealed column is a non-enumerable property, which toEqual skips.

After x g entity <name>: bunx x db gen "create <table>", then bunx x db migrate. The generator prints both, with bun install first when it added a workspace dependency (CLI reference).

A feature with more than one table: x g entity <second> --feature <feature> writes entity-<second>.ts + repo-<second>.ts (and a test beside each) next to the first entity's entity.ts, and registers it on the handle the same way (As of 27.6.2). One file per table; x db gen picks every one up from the registry.

Point lookups batch themselves

findById called several times in one microtask of one request is one statement, As of 2026-08:

// One `select … where "id" in ($1, $2, $3, …)`, not one statement per post.
const authors = await Promise.all(posts.map((post) => users.findById(post.authorId, { orgId })));

Nothing to opt into — no dataloader(), no batch(). findById keeps its signature and its meaning, which is the only way the fix reaches code that is already written.

A page batches the loop it causes

A for … of loop awaits between iterations, so no two of its lookups share a microtask. The page they came from batches them instead, As of 2026-08:

const page = await db.posts.findMany({ orgId });
for (const post of page.rows) {
  // Two statements for the whole loop: the page, then one `where "id" in (…)` over every author
  // on it. Every lookup after the first is memory.
  const author = await db.users.findById(post.authorId, { orgId });
}

Nothing new to write, and nothing to opt into: the relation is the references() the column already declares, so the fix reaches loops that are already written.

Preload states a relation the loop would infer

The eager, declarative third of the family: the relation is named on the chain and resolved before the caller sees a row, As of 2026-08:

export const db = database({ orgs, posts, members });

// Two statements: the page, then one `select … where "id" in (…)` over its authors.
const page = await db.posts.where({ orgId }).preload('author').page();
page.rows[0].author;        // the member row, or null — always present

Nothing new to declare: 'author' is the relation the authorId foreign key on posts already produces — preload() names it, it does not define it. A name no foreign key produces is X_PRELOAD_UNKNOWN_RELATION, resolved and thrown when preload() is called, on the chain and not a page later.

Reach for it when the relation is part of what the page is — a list rendered with its authors, rows handed to something that will not call back into the repo, or a read a reviewer should see stated rather than inferred from a loop. The other two members of the family ask for nothing: findById batches a same-microtask fan-out for itself, and a for … of loop over a page is already two statements, above.

Reading a whole table is a loop of pages

A page is bounded on purpose, so reading everything is iteration — and the iteration is a terminal on the same chain, not something written around page(), As of 2026-08:

// One statement per batch, one page of rows in memory at a time.
for await (const batch of db.posts.where({ orgId }).preload('author').inBatches(500)) {
  await search.index(batch);
}

A batch is the page page() would have returned at that position: same filters, same tenancy, same soft-delete visibility, same select(), same preload(). Nothing here is a second read path.

Stopping early keeps the position, which is what makes a long backfill a job that can run out of time and pick up where it stopped:

await using batches = db.posts.where({ orgId }).after(checkpoint).inBatches(500);
for await (const batch of batches) {
  await search.index(batch);
  if (ctx.clock.now() > deadline) break;
}
await db.checkpoints.update(id, { cursor: batches.cursor });   // resume with .after(cursor)

A count per row is one grouped count

The batching family above collapses a lookup repeated per row. A count() per row is not a lookup — each statement asks a different question, so no batch reaches it. One statement asks all of them, As of 2026-08:

// One statement for all of `ids`, not one `select count(*)` per post.
const counts = await db.likes.where({ orgId }).andWhere('postId', 'in', ids).countBy('postId');
for (const id of ids) await db.posts.update(id, { likeCount: counts.get(id) ?? 0 });

A ReadonlyMap keyed by the column's own values and typed from the row, so counts.get(postId) is a number | undefined — and the undefined is load-bearing.

There is no groupBy() builder and no error code of its own: countBy is a terminal on the chain that already exists, over exactly the rows count() counts.

A count per day is a series

A chart is a count per bucket of time, in a person's zone. One statement, under every guard the chain applies, As of 2026-11:

import { database, entity, timestamp, uuid } from '@ultimat3/entity';

const likes = entity('likes', {
  columns: { id: uuid().primaryKey(), orgId: uuid().tenant(), createdAt: timestamp() },
});
const db = database({ likes });

// [{ bucket: Date, value: number }, …] ascending; `{ sum: 'amount' }` sums instead.
export const likesPerDay = (orgId: string, from: Date, timeZone: string) =>
  db.likes
    .where({ orgId })
    .andWhere('createdAt', 'gte', from)
    .series({ on: 'createdAt', unit: 'day', timeZone, value: 'count' });

A recomputed report table is rollup() over this read (Jobs and workflows).

Writing many rows is one statement

The sections above make a read loop stop being N statements. A loop of insert() calls is the same defect on the write side, and insertAll/upsertAll are its answer, As of 2026-08. Each call is all or nothing, As of 2026-11-02:

await db.tags.insertAll(names.map((name) => ({ orgId, name })));   // one statement, n rows

await db.likes.upsertAll(rows, {                                  // insert, or leave what is there
  onConflict: ['orgId', 'postId', 'memberId'],
  onMatch: 'nothing',
});

await db.counters.upsertAll(rows, { onConflict: ['orgId', 'day'] });   // insert, or overwrite

Rows are Insertable and go through $parse exactly as one row does, so declared defaults are filled here rather than by the caller; upsertAll also stamps onUpdateNow() columns through the same touch() update(id, patch) uses, because an upsert that lands on a stored row is an update. Both resolve with the rows this call wrote, in order — under onMatch: 'nothing' a row already stored is skipped and absent, which is how a caller counts what it actually inserted.

There is no updateAll: updateWhere(filter, patch) and deleteWhere(filter) are already the bulk forms of update and delete, and a second spelling of one of them would be a second path.

The composite unique index the tenancy rule requires is emitted correctly since 2.0.0 — declare it on the entity and generate. Through 1.2.0 indexes: [{ on: ['orgId','createdAt'] }] emitted one mangled name (("org_id_created_at")) and the SQL would not apply, so an app pinned there writes that create unique index by hand.

Seeding

A seed is the fixture graph, written once and replayed anywhere: run it twice and the second run writes nothing and raises nothing. x db seed [<name>] applies it; defineSeed declares it, in packages/<pkg>/seeds/<name>.ts or packages/<pkg>/src/seed.ts — the two places x db seed looks.

export const dev = defineSeed('dev', async ({ insert, upsert, exists, id }) => {
  // The table owns the id, so the NATURAL key is the only key: insert, or update what is there.
  await upsert(plans, { by: ['code', 'currency'] }, { code: 'team', currency: 'EUR', monthly });

  // The SEED owns the id, so a replay finds its own rows: `id('org:acme')` is a uuid v5 of the
  // label — the same value on every machine, in CI and in the container.
  await insert(orgs, [{ id: id('org:acme'), slug: 'acme', name: 'Acme Editorial' }]);

  // Bulk volume data has no natural key worth upserting ten thousand rows against. The unit of
  // idempotency is the FILE, and the guard is a sentinel.
  if (await exists(reports)) return;
  await insert(reports, generated);
});

Rows go through entity.$parse and the invariants either way, so a seed is a test of the schema as well as data for one.

The soft-delete trap, and why deleteWhere refuses. Deleting a seeded row on a soft-deleting entity stamps it rather than removing it. The stamped row still occupies its unique key — that index is not partial — and upsertPlan spares the soft-delete column on purpose, so no replay can clear the stamp: the rows are invisible to every read and no re-seed brings them back. A wipe-then- replay that looked idempotent left a demo's feed permanently empty. Inside a seed the call is therefore X_INVARIANT_VIOLATED naming x db reset, which is the only wipe such an entity has.

A generated seed follows ownership, not just the tenant column. x db seed --generate and the exercise pass write @ultimat3/testing's seedGraph: a table with no tenant column whose references reach a tenant-owned one — and no shared reason — gets its rows per tenant, each under its own tenant's parent, so the tenant proof has a row of each org to attack through. A shared entity is seeded once and is not walked through (A whole graph).

Column types

The blessed set is a decision the framework made for a table it was going to create. The wide set is the shape a table already has, As of 2026-08.

No NUL in text. text() and url() refuse a string carrying U+0000 (X_INVARIANT_VIOLATED, column.format) — the one character a Postgres text value cannot hold (SQLSTATE 22021), so the memory driver no longer stores what production answers X_DB_STATEMENT_FAILED for. t.string refuses it at the wire first, as a 400. As of 2026-09-29.

Chain: .primaryKey() · .nullable() · .unique() · .default(v) · .defaultNow() · .onUpdateNow() · .references(() => other.id, { onDelete }) · .tenant() · .column(name) · .sealed() on text() — Sealed columns.

A date is not an instant. effective_on is the date a rate applies, not a moment; stored as a timestamptz it is a different date on either side of midnight for half the planet. PlainDate (@ultimat3/time) is a branded YYYY-MM-DD string — it sorts chronologically because it sorts lexicographically, round-trips through JSON as itself, and is the literal Postgres accepts. The framework's "never a date without an IANA zone" rule is about instants: a calendar date needs no zone because it names no instant.

Sealed columns

A credential column is text().sealed(). The row reads string; the database holds ciphertext.

export const connections = entity('connections', {
  columns: {
    id: uuid().primaryKey(),
    orgId: uuid().tenant(),
    password: text({ max: 200 }).sealed(),               // opaque
    email: text().sealed({ lookup: true }).unique(),     // equality only
  },
});

const row = await db.connections.where({ email }).one();  // found by the stored string
row?.password;                                            // the plaintext, on the server

A sealed property is server-only. On every row a repository answers it is an own, readable, writable property that is not enumerable: row.password is the plaintext, and nothing that walks the row sees it. As of 2026-10.

insert(row) and update(id, row) with the row passed whole write what it holds; select({ password: true }) and preload() keep the property, still server-only.

Testing a sealed row. toEqual walks enumerable properties. Write one of:

expect(row).toEqualRow({ id, label: 'primary', password: 'hunter2' }); // every own property
expect(row.password).toBe('hunter2');
expect(row).toMatchObject({ password: 'hunter2' });

toEqualRow (Testing) names what differs — a sealed property by name, never by value. A test needs no key: the testing preload installs a throwaway one.

A live read may not filter or order on a sealed column — no change row carries one (X_MATCHER_UNSUPPORTED at subscribe).

lookup reveals equality: equal values store equal strings. Never for a low-entropy value.

Rotation. x secrets rotate keeps the old key in the ring. Until the rows are rewritten a lookup column matches through in (…) over every key, .unique() holds per key, and x doctor reports X_SEAL_RESEAL_PENDING. Re-seal with a backfill() (update(id, { col: row.col })), then x secrets rotate --drop <keyId>.

Sealing a column that already holds data

There is no reading of an unsealed value: a stored string that is not sealed is X_SEAL_INVALID. A column with existing plaintext is migrated — expand, backfill, contract.

// apps/web/app/connection/backfills/seal-connection-passwords.ts — a backfill IS a job: register
// the module in defineApi({ jobs }) beside the slice's other jobs
import { backfill } from '@ultimat3/jobs';

export const sealConnectionPasswords = backfill({
  name: 'seal-connection-passwords',
  tenant: 'none',                       // every tenant; the pass opens the cross-tenant scope
  requires: '20261001120000_add_sealed_password',
  // Only the rows still to seal: a NULL test is the one predicate an opaque column answers.
  // The second filter is for a NULLABLE old column — a row whose `password` is NULL has nothing to
  // seal and would never leave the set. Delete that line when `password` is NOT NULL.
  source: () =>
    db.connections.andWhere('passwordSealed', 'is-null').andWhere('password', 'is-not-null'),
  count: () =>
    db.connections.andWhere('passwordSealed', 'is-null').andWhere('password', 'is-not-null').count(),
  handle: async ({ rows, signal }) => {
    for (const row of rows) {
      signal.throwIfAborted();
      // Reads the old column, writes the sealed one. Idempotent: a replayed page seals again.
      await db.connections.update(row.id, { passwordSealed: row.password });
    }
  },
});

Adopting an existing database

Three overrides, As of 2026-08, and together they are what makes a schema Ultimate did not generate declarable at all. An entity that uses none of them emits exactly what it always did.

export const accounts = entity('account', {
  table: 'legacy_accounts',
  columns: {
    id: uuid().primaryKey().column('account_id'),
    githubLogin: text({ max: 40 }).column('gh_login'),
    balance: money({ columns: { minor: 'amount_cents', currency: 'currency', scale: null } }),
    openedOn: date().column('opened_on'),
  },
});

A physical name is validated where it is written — lower-case letters, digits and underscores, at most the 63 bytes Postgres truncates at — because it is spliced into every statement as an identifier.

What is not adoptable yet, As of 2026-08. Each of these is a real table shape that this vocabulary cannot express, said precisely rather than approximated:

Invariants

Full-text search — the searchIndex port

Rule: .search(term) on a chain asks the INSTALLED SearchIndex — one search path. As of 2026-11-10. The port is index / remove / query / list: list({ entity, tenant?, after, limit }) pages the live documents an index holds (never a tombstone); tenant left out is every tenant's — the sweep's maintenance read, under the tenancy rule query follows (crossTenant for the built-in index). searchIndexConformance checks it: each id once across pages, removed ids absent, another tenant's absent. Canonical wording: packages/entity/README.md, "Search index".

Embeddings

embed: { field: 'body', model: 'text-embedding-3-large' },

More in MCP and AI.

Lifecycle: retention, history, counters

Three declarations on the entity, each compiled to a trigger or a purge target, each replacing a hand-written one. As of 2026-11.

import { entity, integer, timestamp, uuid } from '@ultimat3/entity';

export const posts = entity('posts', {
  columns: { id: uuid().primaryKey(), orgId: uuid().tenant(), likeCount: integer().default(0) },
  history: { retain: { after: '365d' } },
});
export const likes = entity('likes', {
  columns: {
    id: uuid().primaryKey(),
    postId: uuid().references(() => posts.id, { countedIn: () => posts.likeCount }),
    createdAt: timestamp().defaultNow(),
  },
  indexes: [{ on: ['createdAt'] }],
  retain: { after: '90d', on: 'createdAt' },
});

• Retention is a promise x verify checks. A retain no purge() covers is X_ENTITY_RETAIN_UNSWEPT in the manifest step. purge({ entities: 'declared' }) covers every one, history tables included, and needs no line per entity.
• History is recorded by trigger, so updateWhere, a cascade and raw SQL are recorded too. An update that changes nothing records nothing; a change to a counted column alone records nothing. .sealed() columns stay ciphertext in the images. actor_id/request_id are what the WRITE'S transaction set (x.actor, x.request): the repository sets them before its first write to a history entity inside a transaction; an autocommit write records null, never a guess. Update of a history row is refused always; delete only under x.purge naming that table, which the retention sweep sets (X_ENTITY_APPEND_ONLY, SQLSTATE 23001). Reading it is an ordinary, tenant-scoped, cursor-paged read: database({ posts, postsHistory: posts.$history }).
• A counter locks its parent row. A hot parent serializes its writers; where that matters, countBy plus a cache is the alternative. A soft-deleted child is not counted. A new counter's migration recounts once, so an existing table starts true; x db recount posts.likeCount reports drift and --write repairs it.
• A counter's write is the trigger's, not the repository's. The WAL replicator reports it; the in-process entity.row event (what x dev on PGlite and the memory driver feed live queries from) does not, so a live view of the counter on those does not move until the parent row is written again. A counter a page shows live stays the mutator's job until that event reports trigger writes. As of 2026-11.
• Memory answers as Postgres does: the memory driver records history and moves counters in its repository (counter.test.ts, history.test.ts are the parity tables).
• Drift names a missing or disabled lifecycle trigger: X_LIFECYCLE_TRIGGER_MISSING, its fix the statements that re-create it.

One person's data reaches through the foreign keys

defineSubject(members, { tables }) (@ultimat3/jobs) reads the keys every references() already declares: each entity with a key into the subject — or into a table its erase deletes — needs a treatment, 'delete', { anonymize: { col: value } } or { retain: '<why>' }. A table added with a key to the subject and no line there is red on x verify (X_SUBJECT_TABLE_UNDECLARED, naming the path). A table holding the person's key with no references() is invisible to the walk until columns: { <table>: '<column>' } lists it. 'delete' on an appendOnly or soft-delete entity is refused: one keeps every row, the other every value. Long form: Jobs and Workflows.

Views

entity(name, { view: { sql, materialized? }, columns }) declares a relation Postgres derives. The chain reads it like a table — tenancy, the codec, $view — and every write is X_ENTITY_VIEW_READ_ONLY, in both drivers, before a statement exists. As of 2026-11:

import { bigint, entity, uuid } from '@ultimat3/entity';

export const postTotals = entity('post_totals', {
  view: {
    sql: 'select org_id, post_id, count(*) as total from likes group by org_id, post_id',
    materialized: true,
  },
  columns: { orgId: uuid().tenant(), postId: uuid().unique(), total: bigint() },
  primaryKey: ['postId'],
});

Read-only tables

entity(name, { readOnly: '<why>', columns }) declares a table the app reads and never writes — legacy history a trigger closed to INSERTs, a table another system owns. The reason is required. As of 2026-11:

Migration workflow

Prod ordering is fixed: ROLE=migrate completes, then web / sync / worker / scheduler roll. A generated migration is forward-compatible with the previous release, so a rolling restart never serves a request against a schema it cannot read: a column drop takes two releases through retired: ['<c>'] (refused otherwise, X_MIGRATION_ROLLING_UNSAFE), and a key or CHECK on a live table is added without stopping writes → Migrations And Backfills. See Deployment.

Custom drivers

A store that is not Postgres — Mongo, Cassandra, a key-value store — is a Driver built with defineDriver({ name, capabilities, repo }) from @ultimat3/entity/adapter. As of 2026-11-09.

An undeclared capability (upsert, matches, aggregate:<kind>, transactions) is X_DRIVER_UNSUPPORTED with meta.capability; a declared one with no method behind it is X_DRIVER_CAPABILITY_MISSING when the table opens. lock and series undeclared answer the shipped X_LOCK_UNSUPPORTED / X_ENTITY_SERIES_UNSUPPORTED.

A searchable entity's version is strictly increasing per row, and a custom store advances it in its OWN write, atomically: update(filter, patch, advance?) writes advance.property per matched row as the later of the patch's instant and that row's stored one + advance.stepMs (an in-code merge: advanceVersion(entity, row, { ...row, ...patch }, (p) => p === advance.property)), and a store declaring 'upsert' passes its merged row through advanceVersion(entity, stored, merged, (p) => plan.set.includes(p)). Both from @ultimat3/entity/adapter; entityDriverConformance refuses a store that skips either. As of 2026-11-10.

Migrations, drift and x db describe Postgres only. A custom store owns its schema: x verify's drift step skips an app whose only store is a defineDriver one and which has no migration, and prints why. An app mixing Postgres and a custom store keeps its migrations and its drift check for the Postgres tables.

Drift is a x verify failure

X_DB_DRIFT: schema differs from migrations
  cause: table "posts" has column "publish_at" not present in any migration
  fix:   x db gen "add publish_at"

One code, two detectors, because one check cannot be both. x verify's drift step reads the entity source and the .hash a migration recorded — no database of yours, which is what lets the gate run in CI (it boots a scratch embedded one for the schema dump, below). x db migrate diffs the live catalog against the x_migrations ledger on the connection it just migrated over — the only place a hand-edited column is visible at all. A pending migration is not drift, and neither is a table in the framework's x_ namespace.

The schema dump. packages/db/schema/ is the schema as committed SQL — one file per table, the framework's x_ tables under framework/ — written by x db gen and x db migrate, never by hand. From the first migration on it is owed: x verify's drift step replays the migrations on a scratch database, compares bytes, and loads the dump back. A missing, stale or hand-edited dump is X_SCHEMA_DUMP_DRIFT, fix: x db gen. The .snapshot.json sidecar is a different thing: the generator's diff base in the entity's vocabulary. Layout, the engine rule and limits: Migrations and backfills → The schema dump. As of 2026-10.

What the catalog diff compares

Thirteen DriftKind values, As of 2026-10-02 — the executable list is DriftKind in packages/db/src/drift-findings.ts. Twelve are raised by the snapshot comparison in the table below; the thirteenth, unexpected-object, by the schema-dump comparison (Migrations and backfills). For indexes, CHECKs and foreign keys only the declared side is judged: a live index or foreign key no snapshot names is not drift, because Postgres creates an index for every primary key and every unique constraint and an index a DBA added is a planner decision. The primary key is the exception — both sides are compared: a key the database holds and the snapshot does not, or the reverse, is changed-primary-key.

As of 2026-11-07 the missing-column, missing-table, missing-index, changed-index and missing-foreign-key findings carry one psql "$DATABASE_URL" -c '…' repair built from the snapshot (a psql session when no name can be written), never x db migrate: the migration declaring it is already in the ledger, so re-running the migrator applies nothing. Run the repair, then x db migrate re-checks.

There is no separate migration tool and no "regenerate types" step. drift is one of x verify's 21 steps — the list, in order, is in Testing.

Reversible or marked

Two questions, two answers. Reversible is asked at generation: can the down put the rows back? Destructive is asked at the gate: does applying the up destroy data, and does the file say so? A retype is reversible in DDL and still rewrites every row, which is why one flag cannot answer both.

-- 20260814120000_drop_legacy
-- GENERATED by `x db gen` from the app's entities — do not edit.
-- Editing an applied migration changes its checksum: X_MIGRATION_CONFLICT on the next apply.
-- destructive: true

alter table "posts" drop column "legacy";

-- down
alter table "posts" add column "legacy" text; -- data is not restored

X_MIGRATION_DESTRUCTIVE: this migration destroys data and does not say so
  cause: packages/db/migrations/20260814120000_drop_legacy.sql drops a column and does not
         declare it: alter table "posts" drop column "legacy"
  fix:   add the line "-- destructive: true" to packages/db/migrations/20260814120000_drop_legacy.sql,
         or regenerate it: x db gen "<name>" --allow-destructive

Branch DBs for agents

The shipped command is x db branch, and it takes a verb from a closed set — list, create <name>, delete <name>. x branch (no db) is planned and exits X_NOT_IMPLEMENTED; the build and MCP-socket halves of the design below are what it will add.

x db branch create feat-new-billing --json

{"ok":true,"command":"db","data":{"branch":"feat-new-billing",
 "database":"myapp_branch_feat_new_billing",
 "preview":"http://feat-new-billing.localhost:3000","mode":"external"}}

The same clone mechanism powers test parallelism: each worker gets its own ultimate_test_template_w<N> cloned from the migrated template ultimate_test_template, typically 100–400ms. Never mock the database — clone it.

Errors

Full list with --json shapes: Error codes. Source: docs/idea/02-primitives.md, docs/idea/10-testing.md.