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).

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.

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.

"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.

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.

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.

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 });

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.

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 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.

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.

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)

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.

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:

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.

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.

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.

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>.

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 });
    }
  },
});

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:

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

More in MCP and AI.

Prod ordering is fixed: ROLE=migrate completes, then web / sync / worker / scheduler roll. Migrations are forward-compatible with the previous release so a rolling restart never serves a request against a schema it cannot read. See Deployment.

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.

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.

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

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

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.

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