# 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. | Aspect | Rule | |---|---| | Projects to | SQL DDL, domain type (`typeof posts.$row`), migration, repo type, admin screen, seed factory | | Owns | column types, defaults, invariants, tenant column | | Never | business logic, I/O, HTTP awareness, policy decisions | One `entity()` call per table, in `packages/db/src/schema/.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`](https://github.com/developerz-ai/ultimate/blob/main/packages/entity/src/pg-driver.ts)). ## Six projections | Projection | Where it lands | Consumed by | |---|---|---| | SQL DDL | the generated migration — columns, CHECKs, indexes | Postgres | | Domain type | `export type Post = typeof posts.$row` | actions, queries, Solid component props | | Migration | `packages/db/migrations/*.sql` | `x db migrate`, `ROLE=migrate` | | Repo type | the feature's `repo.ts` signature | `ctx.` inside `handle` | | Admin screen | `apps/admin/` | operators, and the admin app's MCP surface | | Seed factory | `seed(name)` fixtures | all six test types | One inferred chain, no hand-typed link: ```text 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`: ```ts 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: | Field | Meaning | |---|---| | `columns` | types + defaults + FKs. Money is `bigint` minor units + `char(3)` currency, never a float; timestamps are `timestamptz`, stored UTC | | `table` | the physical table, when it is not the entity's own name. For a schema this framework did not generate — see [Adopting an existing database](#adopting-an-existing-database) | | `tenant` | the tenant column. Omitted, it is inferred from `.tenant()` or a column named `orgId` — silence never means unscoped | | `shared` | `''` — for an entity with no tenant column whose references reach one that has a tenant (a price list every org's subscriptions point at). Without it the `exercise` step reports `X_ENTITY_TENANT_UNDECLARED`; blank is refused at `entity()` with the same code, and beside a tenant column it is `X_INVARIANT_VIOLATED`. See [Policies and authz](/policies-and-authz#the-static-tenant-check) | | `scan` | `''` — a bounded table (`'a closed catalog of 8 rows'`) the `exercise` step's plan sweep may see read whole. Without it a sequential scan of it is `X_QUERY_UNINDEXED`; blank is `X_INVARIANT_VIOLATED`. See [Testing](/testing#the-plan-and-scale-sweeps) | | `invariants` | `(c) => [invariant(name, expr), …]` — named predicates enforced on write, projected to a CHECK or UNIQUE constraint where expressible. `c` is typed from `columns`, so a typo is a compile error | | `indexes` | composite and partial indexes; a single unique or indexed column declares it on the column instead. `concurrently: true` on an entry builds it with `create index concurrently` in a migration of its own, outside a transaction — for a big, hot table ([Indexes built concurrently](/migrations-and-backfills#indexes-built-concurrently--expand--contract)) | | `primaryKey` | composite keys only — a single key is `.primaryKey()` on the column | | `tags` | extra cache tags this entity participates in, beyond its own `entity:` | 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. | Written as | Reaches SQL | Note | |---|---|---| | `c.title.trimmed().minLength(1)` · `c.likeCount.atLeast(0)` · `c.email.contains('@')` · `c.a.eq(c.b)` · `c.flag.isTrue()` · `c.publishedAt.isNull()` / `.isNotNull()` | yes | `CHECK`. `trimmed()` strips leading and trailing **spaces** only, as one-argument `btrim` does — a tab is a character on both sides. `atLeast` / `eq()` compare the number the column holds, so a `bigint()` or `decimal()` row (a decimal string) is judged by its digits | | `iff(c.status.eq('published'), c.publishedAt.isNotNull())` | yes | `(a) = (b)` — a rule that holds *exactly when* another does. The **one** combinator: `and`/`or`/`not` are deliberately absent | | `c.unique(['orgId', 'userId'])` | yes | `UNIQUE`, not a CHECK — a single row cannot see a duplicate | | `c.slug.matches(SLUG_PATTERN)` — a **`RegExp`** | yes | `slug ~ '…'` | | `c.slug.matches(isValidSlug)` — a **function** | **no** | `sql: null` | | `c.satisfies(fn, ['status', 'publishedAt'])` | **no** | `sql: null`, by construction — a JS function over several columns | **`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**: | Refused | Because | Write instead | |---|---|---| | `\b` | Postgres reads it as BACKSPACE — `'foo' ~ '\bfoo'` is **false** | a predicate; the rule stays app-only | | `.` | matches `\n` in Postgres, not in JS | `[^\n\r]` | | `\w` `\W` | locale alphanumeric — `'é' ~ '^\w$'` is **true** | `[A-Za-z0-9_]` | | `\s` `\S` | `'\u00a0'` is whitespace to JS, not to Postgres | `[ \t\n\r\f\v]` | | `\A` `\Z` | anchors in Postgres, letters in JS | `^` `$` | | `\1`–`\9`, `(?…)`, `(?i)`, `[[:alpha:]]`, a non-ASCII range endpoint | backreference, named group, embedded flag, POSIX class, collation-ordered range | a predicate | 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`. | Member | Is | Rule | |---|---|---| | `posts.title` | a column reference | the columns are the entity's own properties, which is what forces the `$` sigil onto every framework member | | `$row` | the phantom that carries the row type | `export type Post = typeof posts.$row`. Reading it as a value throws `X_INVARIANT_VIOLATED` — a type was meant | | `$schema` | the Standard Schema the columns already describe | forms and actions hand input to it; the row shape is never declared a second time | | `$parse(value)` | the parse boundary | fills declared defaults, then validates every column through the column that owns it. Throws on a bad value | | `$view(keys)` | the row projection | `const PostView = posts.$view(['id', 'title'])` — what an action names as its `output`. An unknown key is a compile error, and `X_INVARIANT_VIOLATED` at declaration for a JS caller | | `$assert(row)` | every invariant, run | called by the repo on insert and on update; reports **every** failing invariant at once, so one round trip fixes all | | `$describe()` | the manifest row | name, table, primary key, physical columns, invariants, indexes (name **and** column list, uniqueness, predicate, direction — a name cannot be parsed back into columns), tags, `cacheTag`, `softDelete`, `orgScoped` | | `$cacheTag` | `entity:` | the string `@ultimat3/cache` invalidates by | | `$tagFor(id)` | `entity::` | row-level invalidation for live queries | | `$tenantColumn` | the tenant column's property key, or `null` | presence is what turns tenancy on — resolution order under **Tenant column rule** below | | `$name` `$table` `$columns` `$primaryKey` `$indexes` `$invariants` `$tags` `$softDelete` | the declaration, lifted | readable. `$table` is `$name` verbatim — the first argument to `entity()` is the physical table name, never pluralised or snake-cased for you | `entity` is the one primitive whose surface is `$`-prefixed, and the type says why: `type Entity = EntityCore & 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](/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`, 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: | Order | Source | Detail | |---|---|---| | 1 | `tenant: 'workspaceId'` | the declaration, said out loud. It need not be named `orgId` and need not carry `.tenant()`. Declared, it wins outright — a `.tenant()` mark elsewhere is not a conflict, just not the winner | | 2 | `.tenant()` on a column | the first column marked, in declaration order | | 3 | a column named `orgId` | literal property name, no mark needed | | 4 | none of those | `$tenantColumn` is `null` and the entity is not tenant-scoped | 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`. | Consequence | Detail | |---|---| | Repo methods | inject the tenant predicate from `ctx.actor.orgId`; a raw `db.*` call in a service is a boundary error | | Cache keys | framework-generated from the query name, its parsed input and its tags — never hand-built. The tenant reaches the key through the **input**, so a `cache:` read must take it there ([Caching and invalidation](/caching-and-invalidation)) | | Live queries | the tenant predicate is part of the matcher, not a post-filter | | Vector search | tenant + policy filters applied **in SQL**, so similarity search cannot leak across tenants | | Foreign keys | a reference holds its two ends to one tenant, in the database: below | ### 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. ```ts 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(), }, }); ``` | Edge | Key | |---|---| | both ends scoped | `__tenant_fkey` over `(tenant, column)`. A self-reference too | | an end with no tenant (`orgId -> orgs.id`), or the tenant column itself | the plain `
__fkey` | | `crossTenant: ''` | the plain key. A blank reason, and a reason on an edge that was never same-tenant, are `X_INVARIANT_VIOLATED` | | a null key | checked by nobody, as on any foreign key | | Question | Answer | |---|---| | The refusal | `X_DB_FOREIGN_KEY_VIOLATION`, the same code and text as a dangling id: no existence oracle | | Raw SQL | meets the same key. It is the database's, not the repo's | | Memory driver | refuses a target **found under another tenant**, same code. Not found stays accepted: it enforces no foreign key at all | | The index behind it | a unique `(tenant, column)` on the target, one per referenced table, skipped when a total unique over those columns exists. `x db gen` declares it for you, and keeps it while a retired column's key still needs it | | `onDelete: 'set null'` | written `on delete set null ()`, or the tenant column would go too. Needs Postgres 15+ | | Onto live tables | `x db gen "same-tenant keys"`: all deferred, in order — build the index, add the key `not valid`, validate, drop the old key. Writes keep landing and the old key holds until the new one validates | | Validate fails `X_DB_FOREIGN_KEY_VIOLATION` | rows already point across tenants, and that is the finding. Decide those rows, or state `crossTenant` on an edge that means it | ### Locking a row or a key A read-modify-write through the handle loses updates. `db.
.lock` does not. `As of 2026-11-02`. ```ts 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; export async function like(postId: string): Promise { 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 { return db.invoices.lockKey(`number:${String(year)}`, async (tx) => nextNumber(year, tx)); } ``` | | `lock(id, (row, tx) => R, options?)` | `lockKey(key, (tx) => R, options?)` | |---|---|---| | Postgres | `select … for update`, the read path's own statement | `pg_advisory_xact_lock(hashtextextended('
::', 0))` | | Scope | that row | one key, one table, one tenant: org A's key never blocks org B | | Absent, another tenant's, soft-deleted | `X_NOT_FOUND` | n/a | | No actor on a scoped entity | `X_TENANCY_UNSCOPED`, as a read | `X_TENANCY_UNSCOPED`, as a read | | Blank key | n/a | `X_INVARIANT` | | Rule | Detail | |---|---| | Transaction | its own, committed when `fn` returns, rolled back when it throws. Nested in `withTransaction` it is a savepoint, and the lock lives to the root COMMIT | | Writes inside `fn` | with `{ tx }`, the rule on every write. Memory undoes only what names it | | Bound | no wait option. The role's `statement_timeout`. A deadlock is `X_DB_SERIALIZATION_FAILURE` | | Memory driver | a queue per key, reentrant within one async chain; a nested lock joins the outer one's transaction; `update(id)` and `delete(id)` wait for a row's holder, the filtered writes and `upsertAll` do not; a waiter past 5 s is `X_DB_STATEMENT_TIMEOUT`, Postgres' code, usually two locks taken in opposite order | | A hand-written `Repo` | both are optional on the contract; the handle answers `X_LOCK_UNSUPPORTED` | | `crossTenant()` with no `orgId` | holds its own `
:-:`, no org's. Name `orgId` to take that org's key | ## 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`. ```ts // 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 { return (await db.widgets.where({ id }).one()) ?? undefined; } export async function list(limit = 50): Promise { return db.widgets.orderBy('createdAt', 'desc').limit(limit).all(); } export async function insert(row: Omit): Promise { return db.widgets.insert(row); } ``` ```ts // 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 handle owns | So the repo never writes | |---|---| | tenancy — every read runs under the actor's org | an org parameter, a `.where({ orgId })`, an `org_id = …` in SQL. No function in a generated repo takes or names an org. A read with no actor is `X_TENANCY_UNSCOPED`; an actor with no org is `X_TENANCY_ACTOR_ORG_REQUIRED`; a row written for another org is `X_TENANCY_ACTOR_MISMATCH` | | codecs — money, timestamps, [sealed columns](#sealed-columns) | row decoding, a column list, money's three columns | | paging — `.limit(n)`, then `.after(cursor).page()`; a raw join pages with `keyset()` from `@ultimat3/db` ([below](#paging-a-raw-join)) | an offset, or a paging module | | the transaction — inside `withTransaction` the handle is the transaction, and a statement on it after the scope ended is `X_DB_TRANSACTION_CLOSED`: `await` it, or `outsideTransaction(fn)` for work that must outlive the scope | a client argument | | locks — `db.posts.lock(id, fn)` for a row, `db.posts.lockKey(key, fn)` for a key, both under the tenant | `for update` by hand, or an advisory lock | | abandonment — once `ctx.signal` is aborted with a coded reason (`X_TIMEOUT`, a job's cancellation) a write rethrows that reason: a transaction rolls back and neither driver writes. A client hang-up is not a refusal. `runToCompletion(fn)` for work that must land | a check of the signal before each write | | Raw `sql` | Where | |---|---| | belongs | a `query`'s source, built with `from()`; a migration | | is the exception | a `repo.ts`, for what the handle cannot say: a join, an aggregate, a CTE, a window. Its unit test runs [the migrated-database recipe](/testing#database-state-and-what-a-run-leaves-behind): the handle's `testDriver()` follows the installed client, so the handle's writes and the join meet in one database | | is refused | a `repo.ts`, for `select *` / `insert` / `update` / `delete` on one table, and `select * … where id = ? for update` (`db.
.lock(id, fn)`) | | is refused, any statement | `order by date_trunc('milliseconds', …)`: the index cannot serve it. `keyset()` pages at the column's own precision | | Enforced by | What fails | |---|---| | `tsc` | `db.widgets` exists only when `widget` is in the set; a renamed column is an error in the repo | | `guards/repo-raw-sql.ts` — `X_REPO_RAW_SQL`, `boundaries` step | a `sql` literal in a `repo.ts` the handle can express; the `fix:` is the handle call | | `X_DB_HANDLE_UNREGISTERED` — `x g entity`, `x g resource` | a `client.ts` with no `const = { … }` passed to `database()`, or an `index.ts` that does not export the handle; the `fix:` is the lines to add | ### 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`. ```ts 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 } } ``` | Question | Answer | |---|---| | Microseconds | each key is selected as `col::text` and bound back as `$n::`: exact. A `Date` would drop them, and a seek from a floored position drops rows | | The plan | `order by` stays on the bare columns: an index on `(created_at desc, id desc)` answers it as an Index Only Scan. `date_trunc('milliseconds', …)` measured 100 ms against 0.2 ms on 300,000 rows | | Kinds | `timestamptz`, `uuid`, `text`, `integer`, `bigint` | | Directions | all one way: a row comparison. Mixed: the or-chain | | A null key | `X_KEYSET_NULL_KEY` on any row of the page: a row comparison has no null order. `coalesce` the key, or order by a not-null column | | A cursor from another read | `X_CURSOR_INVALID`. The scope is yours plus the keys: the same scope under another sort is refused too | | Memory | none: raw SQL has no memory path. Test it on the migrated database | 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 `: `bunx x db gen "create
"`, then `bunx x db migrate`. The generator prints both, with `bun install` first when it added a workspace dependency ([CLI reference](/cli-reference)). A feature with more than one table: `x g entity --feature ` writes `entity-.ts` + `repo-.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`: ```ts // 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. | Property | Behavior | |---|---| | Window | one microtask, closed before the statement goes out. A sequential `for … of` loop shares no microtask — its `await` ends the window — so what batches one is the page it is looping over (below) | | Lifetime | one request. The batch is keyed by context identity, so it dies with the request and never crosses one | | Never shared with | another tenant, another soft-delete visibility, another projection, another entity, another client. The coalesced statement has to be one each single lookup would have been served by | | Scope | the tenant predicate and `deleted_at is null` are **inside** the statement, so an id that is missing, soft-deleted or another tenant's still reads as `null` — never another caller's row | | Wide batches | past 500 ids, several whole statements rather than one Postgres refuses for its bind count | | Outside a request | a job, a script, a migration: the single statement it always was | ## 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`: ```ts 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. | Property | Behavior | |---|---| | Trigger | the first `findById` whose id is a foreign key value on a page this request read. It resolves that key for **every** row of the page, in one statement | | Never shared with | a different scope key (another tenant, another soft-delete visibility, another projection, another entity), another client, or the same entity after a write to it | | Scope | the tenant predicate and `deleted_at is null` are inside the preload statement — it is the statement each single lookup would have sent, widened to the page's ids — so a page's ids can never resolve rows outside the reader's own scope | | A write | drops what was preloaded for that entity before the statement goes out, so a row this request changed is re-read, never served from a page read before it | | Lifetime | one request. What is held is the ids, not the rows, keyed by context identity | | Wide pages | past 500 ids, whole statements — the same bound the microtask batch has | | Outside a request | a job, a script: a page leaves nothing behind, and every lookup is the statement it always was | ## 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`: ```ts 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. | Property | Behavior | |---|---| | Shape | a `belongsTo` attaches the row or `null`; a `hasMany` attaches an array, empty when there are none. Always present, so "no author" and "nobody preloaded the author" cannot read the same | | Statements | one extra statement per named relation, resolved **concurrently** — two `preload()` calls are two statements in flight, never one after the other. Naming one relation twice is one statement | | Ceiling | `preload('', { max })` — the most related rows one relation may attach to one page; default 10,000 (`MAX_PRELOADED_ROWS`, the largest page a read may ask for). Past it the read is refused with `X_INVARIANT_VIOLATED`, never truncated | | Terminals | `page()`, `all()`, `one()` resolve every named relation. `count()`, `countBy()` and `plan()` do not — a count reads no row to attach one to | | Projection | attached after `select()`. `select()` is widened internally with the relation's own key, so a projection can drop neither the key the preload reads nor the relation it attaches — `plan().select` reports the widened list, the one that actually ran | | Tenancy | the page's own tenant predicate carries onto the related read only when **both** entities are scoped by a column of that same name — a value that scopes one entity is a guess on another, and a source scoped by `workspaceId` may carry an ordinary `orgId` predicate that is a filter and not its tenancy. Carrying nothing is not silence: the related read builds its own plan, so it is refused as `X_TENANCY_UNSCOPED` rather than answered with a guess | | Soft delete | the related read is `findMany`, so `deleted_at is null` applies exactly as it does to any other read | | Wide relations | past 500 ids, whole statements — the same bound the two batches above share. A `hasMany` wider than one page costs another keyset page rather than a silent truncation | | Reach | a table reads only the entities its own `database()` call named. A relation to one outside the set is `X_INVARIANT_VIOLATED`; the fix names the missing entity in that same `database({ … })` call | 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`: ```ts // 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: ```ts 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) ``` | Property | Behavior | |---|---| | Statements | one per batch, each asking for one row past it exactly as a page does. An empty batch is never yielded, so a consumer never checks `batch.length` | | Position | keyset, never OFFSET — a row written mid-iteration cannot make the loop skip or repeat one. `.after(cursor)` starts it, `.cursor` is where the next batch starts, `null` once exhausted | | Closing | `break`, `return` and a throw all stop the next statement; `await using` is the same guarantee for a handle held in a variable, and `close()` is idempotent. One handle is one iteration — a second `for await` continues it rather than restarting the table | | Batch size | a whole number of rows, at least one. A chain that also called `limit()` is `X_INVARIANT_VIOLATED`: one number with two meanings, and neither reading is safe to guess | | Sort order | an ordering no cursor can carry is refused at `inBatches()`, not one batch later — a result that fits in a single batch mints no cursor, so deferring it would hide the mistake until the table grew. **An ordinary nullable sort column is no longer one of them, `As of 2026-08-24`**: NULL has a written-down place (`asc nulls last` / `desc nulls first`), the cursor carries it and the seek reaches it. What is refused is a **nullable primary-key column** — the tiebreak `totalOrder` appends precisely so two rows sharing a sort value cannot straddle a page boundary, and `null = null` is unknown, so it cannot do that job. Reachable only through `primaryKey: [...]`; drop `.nullable()` from the column | | Unsortable kinds | `orderBy` on a `json`, `bytes` or array column is refused at plan time, at any row count (`X_INVARIANT_VIOLATED`). It paged wrongly or threw past page 1. Order by a scalar | | Patches | `update`, `updateWhere` validate named columns, with `insert`'s issues, before the statement, on both drivers. Absent stays absent, `null` clears a nullable column | | Tenancy | the plan's, as everywhere else: an unscoped chain is `X_TENANCY_UNSCOPED` on its first batch | ## 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`: ```ts // 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. | Property | Behavior | |---|---| | Counts | the whole predicate, exactly as `count()` does: the chain's filters, its tenancy and its soft-delete visibility are in the statement. `limit()` and `after()` bound a page, never a count | | A value nothing matched | absent, never `0`. That is what `group by` returns, and it is what tells "none" apart from "never asked" — the default is the caller's `?? 0` | | NULL | one group, keyed `null`, in both drivers: a property a row never carried is read as `null`, so it lands where Postgres puts its NULL rows. `0`, `''` and `false` stay the values they are | | Order | biggest group first, ties by the value — numbers and bigints numerically, everything else by its text — and `null` last. Applied after the rows are in, not in the statement: a hash aggregate returns groups in whatever order it built them, so an `order by` there would let the two drivers disagree about a result they agree on | | Groupable columns | `uuid`, `text`, `char`, `boolean`, `integer`, `bigint`. A timestamp, a `jsonb` or a `money` column is `X_INVARIANT_VIOLATED`, whose `fix` names a column of that entity that *is* groupable — a `Map` compares a non-primitive key by identity, so such a map could only ever answer `undefined` | | More than 1000 groups | `X_INVARIANT_VIOLATED`, never a truncated map: the statement asks for one group past the bound, exactly as a page reads one row past its limit, and the `fix` spells the `andWhere('', 'in', )` that bounds it. A map that lost its tail reads like a complete one, and recounting from it writes the wrong number to every row it missed | | Statement | `select "post_id" as group_value, count(*) as group_count … group by "post_id"` — both names are fixed aliases, so an entity may still declare a column called `count`, and the grouped value is re-parsed by the column that declared it | | Tenancy | the plan's, as everywhere else: an unscoped chain on a tenant-scoped entity is `X_TENANCY_UNSCOPED`, never a count across tenants | 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`: ```ts 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' }); ``` | Property | Behavior | |---|---| | Zone | required by the type, an IANA name (`X_ENTITY_SERIES_INVALID` for `'+09:00'` or an unknown one). The statement is `date_trunc(unit, col, zone)` with the zone bound — never the session's. 23:30 UTC is the next day in Tokyo; a spring-forward day has 23 hourly buckets | | Units | `minute`, `hour`, `day`, `week` (from Monday, as `date_trunc`), `month`. Closed | | `minute`/`hour` | keep the instant's own offset: the repeated hour of a fall-back day is two buckets, and `Asia/Kolkata`'s hour starts at :30 UTC | | `day`/`week`/`month` | local midnight, resolved afresh: a midnight that never happens reads at the offset before the jump, one that happens twice at the offset after it — measured on Postgres 18 and matched by the memory driver (`time-bucket.ts`) | | `value` | `'count'` (a `number`) or `{ sum: column }` — decimal text, or a `MoneyValue`, exactly as `sum()`; one currency across the whole series | | A bucket with no rows | absent — the range is the caller's to fill | | A row with no instant | in no bucket | | More than 1000 buckets | `X_ENTITY_SERIES_INVALID`, never truncated: bound the range or widen the unit | | A hand-written `Repo` | `series` is optional on it; the chain answers `X_ENTITY_SERIES_UNSUPPORTED` | A recomputed report table is `rollup()` over this read ([Jobs and workflows](/jobs-and-workflows#rollups-and-materialized-views)). ## 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`: ```ts 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. | Property | Behavior | |---|---| | One builder | every insert in the framework compiles through the same function, so `insertAll([row])` is the text `insert(row)` always produced. There is no second insert path to drift | | What a collision writes | every column the batch's rows **named** (defaults `$parse` fills reach a new row only, so an unnamed `createdAt` or `label` keeps its stored value; `onUpdateNow()` columns move), minus the conflict target (how the row was found), minus the primary key (where it lives) and minus the soft-delete stamp (whether the row is there at all). Moving either of the first two moves a row nobody asked to move, and every foreign key pointing at that id misses it | | A soft-deleted row it lands on | stays deleted, and takes the batch's other columns. A stamped row still occupies its conflict target — that unique index is not partial — so writing `deletedAt` from the incoming row would bring back a row the app deleted, which is the resurrection `update()` and `updateWhere()` refuse by carrying `deleted_at is null`. `insertAll` is unaffected: a row that collides with nothing writes the stamp it carries | | Conflict target | properties of a **declared** unique constraint — the primary key, a `unique()` column, an `indexes: [{ on, unique: true }]` entry, or an `invariant(name, c.unique([…]))`. Anything else is `X_INVARIANT_VIOLATED` here rather than `42P10` from the server | | Tenancy | on a tenant-scoped entity, `onMatch: 'update'` requires the tenant column *inside* the conflict target, else `X_TENANCY_UNSCOPED`: an upsert builds no read plan, so a target that omits it matches another tenant's row and rewrites it. `'nothing'` is allowed — it writes nothing to a row it does not own | | A batch repeating itself | two rows with one conflict target under `'update'` is refused: Postgres answers that statement `ON CONFLICT DO UPDATE command cannot affect row a second time`, so it cannot pass in memory either | | Uneven batches | under `'update'` every row names the same columns — `excluded.` for a row that omitted one is that column's *default*, not the stored value. `insertAll` and `'nothing'` accept an uneven batch and render `default` in the missing cell. A property present with `undefined` is absent here as in every write: refused beside a row writing a value, and left out of the set list when no row writes one | | Nulls | a null in the conflict target collides with nothing, in both drivers — a Postgres unique index is `NULLS DISTINCT` | | Size | past 65535 bind parameters the batch is several statements inside one transaction (a savepoint inside an open `withTransaction`), never one the server refuses. A failure in any chunk leaves no row of the batch, in both drivers; one chunk stays one autocommit statement, no `BEGIN`. Partial progress is yours to ask for: split the batch and call once per part | 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 []` applies it; `defineSeed` declares it, in `packages//seeds/.ts` or `packages//src/seed.ts` — the two places `x db seed` looks. ```ts 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); }); ``` | Verb | Use it when | What a replay does | |---|---|---| | `insert(entity, rows)` | the seed chose the ids (`id('label')`) | one `on conflict … do nothing` statement per call — a stored row is left exactly as it is, and counted `skipped` | | `upsert(entity, { by }, values)` | the table owns the id and a natural key identifies the row | reads first so an unchanged row answers `'skipped'` with no statement, then one `on conflict … do update` — never a read-decide-write race between two containers booting at once | | `exists(entity, where?)` / `count(entity, where?)` | volume data, where the file is the unit | the sentinel returns early and nothing is written | | `deleteWhere(entity, where)` | a scoped wipe before a regenerate | **refused on a soft-deleting entity** — see the trap below | 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. | Rule | Detail | |---|---| | Tier | `reference` ships to production through this same command; `dev` is fixture data and is the default. `x db seed` runs `reference` alone under `ULTIMATE_ENV=production` — naming `--tier dev` (or `ULTIMATE_SEED_TIER=dev`) is both the selection and the consent → [CLI reference](/cli-reference#x-db) | | Transaction | one per seed, never one around the run: a seed that throws must not roll back the seeds that already landed | | `createdAt` | an `upsert` that lands on a stored row does **not** overwrite it. A replay must not move when a row first arrived; `preserve: [...]` names other columns to spare | | Tenancy | `upsert` on a tenant-scoped entity needs the tenant column inside `by`, else `X_TENANCY_UNSCOPED` — the same rule `upsertAll` carries. `insert` has no such requirement: `do nothing` writes nothing to a row it does not own | | Generated key | `insert` refuses a row that leaves a `uuid().primaryKey()` unnamed. `$parse` would fill it with a fresh uuid, the conflict target would match nothing, and run five would leave five copies — the one duplication nothing else can see | | `now` | one instant per run, so every row a bulk pass stamps carries the same timestamp | | `metrics` | `{ inserted, updated, skipped }`, returned by `run()` and reported per seed by `x db seed --json` | **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](/testing#a-whole-graph-seedgraph)). ## 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`. | Builder | Postgres | Row type | |---|---|---| | `uuid()`, `uuid()` | `uuid` | `string`, branded if declared. `.primaryKey()` generates a v7 when omitted | | `text({ max })` · `integer()` · `boolean()` · `url()` | `text` · `integer` · `boolean` · `text` + CHECK | `string` / `number` / `boolean` | | `enumerated(v)` · `tz(zones)` · `locale(tags)` | `text` + CHECK | the union of the declared values | | `timestamp()` | `timestamptz` | `Date` — an instant, stored UTC | | `money()` | `_minor bigint` + `_currency char(3)` + `_scale integer null` | `MoneyValue` | | `json(schema)` | `jsonb` | whatever the schema infers. The schema is **required** — a `json()` returning `unknown` is an `any` hole, and the value arrives from the database as often as from a caller. `null` in a NOT NULL `json()` column is the JSON value — the schema judges it (`t.nullable(…)` admits it) and it is stored as `'null'::jsonb`; in a `.nullable()` column `null` is SQL NULL, the clear `is-null` matches | | `decimal({ precision, scale })` | `numeric(p, s)` | `string`, the exact digits. A value with more decimal places than the column stores is refused, not rounded | | `date()` | `date` | `PlainDate` — a calendar date, no time, no zone | | `bigint()` | `bigint` | `string`. A JS `bigint` is what `JSON.stringify` throws on and a `number` loses digits past 2^53, which is where a legacy `int8` key lives | | `bytes()` | `bytea` | `Uint8Array`, normalised across both drivers | | `arrayOf(column)` | `[]` | `readonly T[]`, each member parsed by the element column. Elements: `uuid`, `text` (and `url`, `enumerated`, `tz`, `locale`), `integer`, `boolean`, `timestamp`, `decimal`, `date`, `bigint`, each optionally `.nullable()`. The row is the same on both drivers: a driver that returns an array as its text literal (`Bun.SQL` does for `uuid[]`) is read by the column's declaration | **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](#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. ```ts 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 ``` | Fact | | |---|---| | Builder | `text()` only; `.sealed()` reads the same before or after `.nullable()`, `.unique()`, `.column()` | | Stored | `x1...` in a plain `text` column — no new SQL type, and `x db gen` writes no migration for sealing a `text()` with no `max`. A `max` is checked against the plaintext and its CHECK is dropped | | Key | the master key `x secrets` manages; purpose `entity:
.`, derived | | Drivers | memory and Postgres both seal | | Manifest | `x.manifest.json` lists the column as `"sealed": "opaque"` or `"lookup"` | **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`. | Reads it | Does not | |---|---| | `row.password`, `const { password } = row`, `'password' in row` | `JSON.stringify(row)`, `{ ...row }`, `Object.keys` / `entries` / `assign`, `structuredClone(row)` | | `update(id, row)` — the row passed back whole | the logger, an error's `meta`, an island's props, a cache tier, a job's stored input and step output | | `console.log(row)` / `Bun.inspect(row)` — Bun prints own properties, enumerable or not | — log through `logger`, never `console`, on a path that holds one | | Leaves the server through | Sealed column | |---|---| | `entity.$schema` as an `output` — HTTP, the typed client, MCP, a job result | absent, in the schema and the value | | an `output` that is not the entity's schema (`t.record(…)`, a hand-written Standard Schema) | absent — the row enumerates none | | a `query`'s rows — HTTP list, `Page`, `single: true`, the record envelope's `data`, the MCP read, a `cache:` entry | absent — a query has no output parse, so this is the row's own doing | | a live query's snapshot | absent | | a live query's change, on the socket and on the bus | absent — the row decoded off the write-ahead log drops the stored string | | `entity.$view([...])` | `X_ENTITY_SEALED_IN_VIEW` | | a record, the record envelope, a `persist: true` browser store | absent | | an island's props, a log line, an error's `meta` | absent | | a job's input and a `step.run()` output holding the row | absent from the stored JSON — pass the id and re-read | | `{ token: row.token }` — the secret named by hand | **leaves.** Naming it is the one read there is; a serialised copy is the author's statement | | It costs | What happens | Write instead | |---|---|---| | a spread patch | `update(id, { ...row, label })` leaves the stored secret alone — an absent property is not a write | nothing | | a spread insert or upsert, required column | `X_INVARIANT_VIOLATED` — `is required, sealed, and missing from the row to insert`, with the rewrite as its `fix:` | `insert({ ...row, password: row.password })` | | a spread insert, **nullable** column | stored NULL, silently — an absent nullable column is a legal insert | `insert({ ...row, token: row.token })` | | equality in a test | `expect(row).toEqual({ …, password })` fails with an empty diff; `toEqual(otherRow)` cannot see a wrong secret | `expect(row).toEqualRow({ …, password })` | | a row out of a shared `cache:` tier or a replayed `step.run()` | came back through JSON, so the property is gone | read the secret from the repository, by id | `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: ```ts expect(row).toEqualRow({ id, label: 'primary', password: 'hunter2' }); // every own property expect(row.password).toBe('hunter2'); expect(row).toMatchObject({ password: 'hunter2' }); ``` `toEqualRow` ([Testing](/testing#matchers)) 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). | The database may | `.sealed()` | `.sealed({ lookup: true })` | |---|---|---| | test for NULL (`is-null`, `is-not-null`) | yes — `null` is stored as NULL, never sealed | yes | | match by `eq` / `in` | no — a compile error, and `X_ENTITY_SEALED_PREDICATE` | yes | | order, range, `like`, group, aggregate | no | no | | hold `.unique()` or an index | no | yes | | run an invariant over it | no | only `unique` | `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 `. ### 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. | # | Step | Command or edit | |---|---|---| | 1 | expand: a NEW nullable sealed column beside the old one | `passwordSealed: text().nullable().sealed()` · `x db gen "add sealed password"` · `x db migrate` | | 2 | write both: every writer sets `passwordSealed` as well as `password` | deploy | | 3 | backfill the rows written before step 2 | the declaration below · `x db backfill seal-connection-passwords --write` | | 4 | switch readers to `passwordSealed`; stop writing `password` | deploy | | 5 | contract: drop the plaintext, take the name back | delete `password`; `password: text().sealed().column('password_sealed')` · `x db gen "drop plaintext password"` · `x db migrate` | ```ts // 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 }); } }, }); ``` | Why | | |---|---| | a new column, not `.sealed()` on the old one | reads of the old rows would be `X_SEAL_INVALID` from the first request to the last row of the backfill | | step 5 needs no re-seal | the purpose follows the PHYSICAL column (`password_sealed`), and `.column()` pins it | | `count` | the same predicate, counted: the pass has converged when it answers 0, and `X_BACKFILL_STALLED` says so if it has not. `source` and `count` must filter alike — the `password` `is-not-null` line goes in both or neither | | a `lookup` column | same recipe; add `.unique()` in step 5, once every row holds a sealed value | ## 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. ```ts 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'), }, }); ``` | Override | What follows it | What does not | |---|---|---| | `entity(name, { table })` | every statement, index name and foreign key | the entity NAME stays the key: the registry, the cache tag `entity:account`, `x entities show`, every relation and every policy | | `.column(name)` | the DDL, the binding, the decoder, predicates, sort keys, cursors | nothing — name it LAST in a chain, since the link returns the general column; `uuid()` and `timestamp()` keep their own methods across it | | `money({ columns })` | the three physical columns, per part, merged over the defaults | `scale: null` says the table has no scale column: every amount is then at the currency's own minor unit, which is what an absent scale already meant | 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: | Shape | Why, and what to do instead | |---|---| | a `numeric` money column with no currency column beside it | `money()` is an amount **and** a currency by construction, and a single implied currency is the bug it exists to prevent. Declare it `decimal()` and keep the currency where the app already keeps it, or add the column | | a Postgres `enum` TYPE | `enumerated()` emits a CHECK, never `CREATE TYPE`, so a column of a real `pg_enum` type has no declaration. `text()` reads and writes it correctly today; what is missing is the DDL and the closed set | | a composite type, a range, `hstore` | no builder, and no way to spell one | | a live query over a renamed column | `@ultimat3/realtime` rebuilds a row from the physical names alone (it holds no dependency on this package) and would deliver `ghLogin` where the repository says `githubLogin`. Renamed columns are safe everywhere else | | `x db gen` over an adopted table | the generator would emit `create table` for a table that exists. Baselining an existing schema is its own command and is not built | ## Invariants | Rule | Behavior | |---|---| | Named | `inv('published-post-has-title', …)` — the name is the error text and the test name | | Enforced on write | via the repo and, where expressible, a Postgres CHECK generated into the migration | | Violation | throws `X_INVARIANT_VIOLATED` with the invariant name and the offending row id | | Not authz | an invariant is about data shape. Permission belongs in [Policies and authz](/policies-and-authz) | | Duplicate entity | two entities on one table name is `X_ENTITY_DUPLICATE` — rename or merge | ## 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". | Index | How `.search(term)` answers | |---|---| | the built-in Postgres one (default) | inside the statement, its `matches` predicate over the `.searchable()` columns — the same SQL as before the port; costs a write nothing | | an external one (`searchIndex` in `apps//runtime.ts`: Meilisearch, Typesense, the app's adapter) | ids first, then the chain reads those rows — tenancy, soft delete, order and cursor still apply, so a hit can never widen what the chain sees | | Fact | Detail | |---|---| | the version | every `SearchDocument` carries `version`, the row's `onUpdateNow()` instant in epoch ms — STRICTLY increasing per row: a write (a soft delete included) stores it no earlier than the stored one + 1 ms (Postgres `greatest($now, updated_at + interval '1 millisecond')`; the memory driver and a `defineDriver` store through `advanceVersion` / `DriverRepo.update`'s `advance`), so two writes inside one millisecond are two versions and the column may run a millisecond ahead of the clock per same-ms write. A searchable entity declares `updatedAt: timestamp().defaultNow().onUpdateNow()`; without one `searchDocumentOf` and `reindex()` refuse (`X_INVARIANT_VIOLATED`) | | keep only newer | an adapter applies a document only when its version is newer than the document or tombstone it holds for that id; `remove(scope, ids, version)` leaves a tombstone (a delete's: past the row's last version and no earlier than its instant); an equal version is a replay. EXCEPT a `committed` document — the write path's, `index(docs, { committed: true })` — which replaces a tombstone whatever its version, so a key deleted and written again is live at once; `reindex()`'s reads are not `committed`, so a stale read never brings a deleted row back. `searchIndexConformance` (`@ultimat3/testing/conformance`) checks each case | | the write path | with an external index installed, every committed write of a searchable entity reaches it: insert, upsert, update, transition and `updateWhere` index the rows written; `delete` and `deleteWhere` (soft or hard) remove them — whatever the primary key (`id`, another column, composite; an instant in a key is spelled ISO 8601 UTC). Taken when the write runs, merged per key per transaction, sent at commit on one chain per index (`search-mirror-batch.ts`), so a rolled-back write indexes nothing. The driver hands a delete's or filtered write's rows over (`returning *` on Postgres) only for a searchable entity under an external index; under the built-in index every write is the statement it always was | | a failed index write | never fails the business write: one `entity.search-index.write-failed` log line; repair with `reindex()` ([Jobs](/jobs-and-workflows#rebuilding-a-search-index--reindex)), whose sweep also removes what the table lost | | not mirrored | a delete or filtered write past `MAX_ASSERTED_ROWS` rows: Postgres counts first and sends no `returning *` past it (memory and `defineDriver` stores the same) — logged the same way; `reindex()` repairs it | ## Embeddings ```ts embed: { field: 'body', model: 'text-embedding-3-large' }, ``` | Piece | What is generated | |---|---| | Column | a pgvector column sized from the model, in the same Postgres — no second datastore | | Index | **HNSW**, created by the generated migration | | Backfill | a `job` with steps: resumable, rate-limited per tenant, safe to re-run (at-least-once) | | Re-embed | content-hash change enqueues a job; unchanged text is never re-embedded | | Exact cache | embeddings cached by content hash + model in cache tier 3 | | Search | one hybrid `query` fusing pgvector cosine + Postgres FTS with Reciprocal Rank Fusion; weights are config | More in [MCP and AI](/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. | Declaration | Compiles to | Refused when | |---|---|---| | `retain: { after: '90d', on: 'createdAt' }` | a `PurgeTarget` per entity — batched, cross-tenant `delete … where created_at < now - after` | no window (`X_ENTITY_RETAIN_INVALID`), `on` not an indexed `timestamp()` (`X_ENTITY_RETAIN_UNINDEXED`), beside `appendOnly`, or on a table the catalog holds partitioned (drift, `X_ENTITY_RETAIN_INVALID`: detach partitions in a `task`) | | `history: true` · `history: { retain: { after: '365d' } }` | `
_history (id, , row_id, op, at, actor_id, request_id, before, after)` — a registered entity (`posts.$history`), written by the `ultimate_history` trigger, guarded by `ultimate_history_guard` | a composite primary key, `appendOnly` (`X_INVARIANT_VIOLATED`) | | `references(() => posts.id, { countedIn: () => posts.likeCount })` | a trigger on the child moving `posts.like_count` by one in the write's own transaction | the column is no `integer()`, or the key points at no single primary key (`X_ENTITY_COUNTS_INVALID`) | ```ts 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: '' }`. **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: {
: '' }` 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](/jobs-and-workflows#one-persons-data--subjectexport-and-subjecterase). ## 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`: ```ts 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'], }); ``` | Rule | | |---|---| | `columns` | the view's output columns, by physical name; `primaryKey` is the read identity, no constraint | | `x db gen` | emits `create [materialized] view` after every table; a materialized view's indexes with it | | A change | the query, an index, `materialized`, or a retype/drop under it: every recorded view is dropped before the table statements and every declared one recreated after — declare a view after a view it reads | | Refused at declaration | a blank query, a second statement, `appendOnly`, a `.sealed()` column, an index on a plain view | | Refresh | `refreshView()` — a job ([Jobs and workflows](/jobs-and-workflows#rollups-and-materialized-views)) | | Drift, source | the snapshot records each view's statements; `x verify`'s `drift` step compares them to the entities, so a view declared and never generated, changed since, or recorded and no longer declared is `X_DB_SCHEMA_UNMIGRATED` / `X_DB_SCHEMA_UNDECLARED` naming `view ""` — one code per direction, as for a table | | Drift, columns | only a database knows what a query outputs, so the same step's scratch replay reads each created view's columns and holds `columns` to them: one named and not output (every read a `42703`), or output and named by none, is `X_ENTITY_VIEW_DRIFT` | | Schema dump | `07_views/.sql`, a materialized view's indexes in the same file; load equals replay, as for a table | | Seeds | `x db seed --generate` and `x exercise` seed tables only — a view's rows are derived | | Admin, MCP | listed read-only: `list`, `detail`, `search`, and no create/edit/delete route, button or admin MCP tool. An `operations` override naming a write is `X_ADMIN_VIEW_WRITE` ([Admin dashboard](/admin-dashboard)) | | Manifest | the entity's fact carries `view: { materialized }`; a table becoming a view is a **breaking** change, a view becoming a table additive | ## Read-only tables `entity(name, { readOnly: '', 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: | Rule | | |---|---| | Writes | `insert`, `insertAll`, `upsertAll`, `update`, `delete`, `updateWhere`, `deleteWhere`, `lock`: `X_ENTITY_READ_ONLY`, in both drivers, before a statement exists, the reason in the cause | | Refused at declaration | a blank reason, `view` (already read-only), `appendOnly` (it admits the inserts this refuses): `X_INVARIANT_VIOLATED` | | Seeds | `x db seed --generate` and `x exercise` seed no row of it. A nullable reference into it is seeded null; a NOT NULL one is `X_SEED_GRAPH_UNSATISFIED` naming the table, its reason, and the fix: `readOnly` on the referencing table too, or `.nullable()` then `x db gen` | | Subjects | `defineSubject`'s `'delete'` or `anonymize` on it is `X_SUBJECT_TREATMENT_INVALID`; `retain` is the one treatment that holds | | Admin, MCP | listed read-only, as a view; an `operations` override naming a write is `X_ADMIN_READ_ONLY_WRITE` | | Projection, manifest | `$describe().readOnly` and the manifest's entity fact carry the reason, absent when undeclared; becoming readOnly is **breaking**, ceasing to be additive | | DDL | none: `x db gen` emits nothing for it. The trigger, grant or ownership that closes the table is yours | ## Migration workflow | Step | Command | Behavior | |---|---|---| | Generate | `x db gen "add publish_at"` | diffs entities vs migrations, writes a named, ordered migration + its `down` | | Apply (dev) | `x db migrate` | runs pending migrations against the dev DB; live queries resubscribe | | Check | `x db migrate --json` | the live schema against the ledger it just wrote; exits non-zero on a difference | | Inspect | `x db studio` | tables, columns, indexes, FKs, generated SQL — also the `/_x` **Schema** panel | | Pre-deploy | `ROLE=migrate` container | run-once hook, same image; waits on the session-pinned migration lock while another version's migration is in flight, then applies. The wait is bounded at 60s (`As of 2026-08`) — past that it exits non-zero with `X_MIGRATE_CONCURRENT` instead of hanging the rollout | | Test template | automatic | migrate + seed once into `myapp_test_tpl`, then clone per worker | 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: ['']` (refused otherwise, `X_MIGRATION_ROLLING_UNSAFE`), and a key or CHECK on a live table is added without stopping writes → [Migrations And Backfills](/migrations-and-backfills). See [Deployment](/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`. | The kit owns | The store owns | |---|---| | tenant scoping, soft delete, the page bound, the total order and the cursor — the store is handed a plan already scoped, never `FindManyArgs` | answering `plan.where` / `plan.orderBy` (`selectRows(entity, rows, read)` does it in process) | | narrowing, `assertRowTenant` and EVERY invariant, judged before a write reaches the store | its schema, its unique indexes (a duplicate key: `entityUniqueViolation(entity, constraint)`), its references | | refusing a returned row outside the plan (`X_DRIVER_ROW_OUT_OF_SCOPE`) | atomicity: declare `'transactions'` and a `transactor`, or every `{ tx }` and a seed dry run is refused | An undeclared capability (`upsert`, `matches`, `aggregate:`, `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" ``` | Direction | Meaning | Caught by | |---|---|---| | Entity has what migrations lack | you edited an entity and did not generate — run the `fix` | `x verify` | | DB has what migrations lack | someone changed the database by hand; generate a migration or revert the change | `x db migrate` | | Migrations have what the entity lacks | a stale migration or a deleted column; reconcile before shipping. Deleting the **last** entity is this direction, not "nothing is declared so nothing drifts": `x db gen ""` refuses with `X_MIGRATION_IRREVERSIBLE`, and the same command plus `--allow-destructive` writes the drop | `x verify` | | Neither declares anything | not drift. Zero entities against zero migrations is agreement, which is what keeps a scaffold with no `entity()` green until its first one | — | 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](/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`](https://github.com/developerz-ai/ultimate/blob/main/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](/migrations-and-backfills#the-schema-dump)). **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`. | Kind | Raised when | |---|---| | `unexpected-column` · `missing-column` · `changed-column` | a modelled table's columns disagree with the snapshot. A column of the **declared** primary key reads as `NOT NULL` whether or not anything declared it; a column only the database keys does not, `As of 2026-10-02`. `changed-column`'s `fix:` is one command, `psql "$DATABASE_URL" -c 'alter table … alter column … set not null;'` (or `drop not null`), run against the drifted database, then `x db migrate` to re-check | | `changed-primary-key` | new `As of 2026-10-02`. The two key column lists differ — a different column, a different **order**, or a key on one side only. Its `fix:` is one command — `psql "$DATABASE_URL" -c ''`, naming the constraint the database holds — run against the drifted database, then `x db migrate` to re-check. Until this kind existed a re-keyed table read `ok: true` | | `unexpected-table` · `missing-table` · `unknown-schema` | a modelled table is absent, or a table outside `x_` is present that no migration declares | | `missing-index` | the snapshot names an index the catalog does not hold | | `missing-check` | the snapshot names a CHECK constraint the catalog does not hold. Compared **by name only** — `pg_get_constraintdef` answers Postgres' own rewriting (`status in ('draft','published')` comes back as `CHECK ((status = ANY (ARRAY['draft'::text, 'published'::text])))`), so the predicate's text can never be compared, exactly as an index's `where` cannot. Only the **declared** side is judged, so a `NOT NULL`, an `enumerated()` column's old anonymous constraint and an extension's own are all silent. Its `fix:` is the `add constraint … check (…)` statement as one `psql "$DATABASE_URL" -c '…'` command | | `changed-index` | the index is there and one of **four** facts about it differs: its column list, its uniqueness, its **direction**, or whether it is **partial**. The last two are new `As of 2026-08-19` — a `desc` index rebuilt ascending served a feed's newest page off the wrong end, and a partial index recreated as a total one silently widened the constraint, and both read `ok: true`. `asc` is normalised to `null` first, because Postgres stores an ascending index as not-descending. The predicate's **text** is deliberately never compared → [Known gaps](/known-gaps) | | `missing-foreign-key` | no key points those columns at that table. Matched on **where the key points**, never on its name: a hand-written `constraint fk_posts_org` is the same constraint as a generated `posts_org_id_fkey` | | `changed-foreign-key` | new `As of 2026-08-19`. The key points where it was declared to point and one side's `on delete` rule is not the other's. Reported apart from `missing-foreign-key` because it is a different repair — the constraint is there, and what changed is what happens to the child rows. Its `fix:` is the **pair** as one `psql "$DATABASE_URL" -c '…'` command, not `x db migrate`: a rule cannot be altered in place, `add constraint` alone is `42710` on a name already taken, and no `x db gen` diff emits either statement. Both sides go through one normalisation, so the catalog's `c` and a snapshot's `cascade` agree, and Postgres' `a` (`no action`) on every undeclared key reads as no rule | 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](/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. | Rule | Detail | |---|---| | Every migration has a `down` | `x db gen` writes both halves of one file, split by a lone `-- down` line | | A generated column drop | takes two releases. Not retired first: `X_MIGRATION_ROLLING_UNSAFE`, whose fix prints the path. Retired: `X_MIGRATION_IRREVERSIBLE` — its `down` cannot restore the rows — until `x db gen "" --allow-destructive` | | A changed primary key | `As of 2026-10-02`: `alter table … drop constraint if exists "
_pkey"` ahead of the table's column statements and `add constraint "
_pkey" primary key (…)` after them, reversed in `down`. Until then it wrote **no statement** and `x db gen` re-recorded the hash sidecar as if nothing had changed. A key another table's foreign key is written against refuses with `X_MIGRATION_IRREVERSIBLE` naming those constraints: drop the keys in one migration, change the key in the next, restore them in a third. A key over a column the same migration adds with no default refuses the same way — every existing row would hold `NULL` there: add the column, backfill it, then key it. A nullable column leaving the key gets `drop not null`, in `up` and in `down`, because Postgres keeps a key column `NOT NULL` after the key is gone | | A destructive `up` | must carry `-- destructive: true` as a line in the file. `x db gen` writes it for you; an unmarked one fails `x verify`'s `drift` step with `X_MIGRATION_DESTRUCTIVE` | | What counts as destructive | `drop table`, `drop column`, `truncate`, `alter column … type` — a closed list. `drop constraint`, `drop default`, `drop not null` and `drop index` do not: the database rebuilds those. A truncate is a statement that **starts with** `TRUNCATE`. `create trigger … before truncate` and `grant`/`revoke … truncate` only name the operation to guard or permit it, and are not truncates (`As of 2026-09-24`, #522) | | Only `up` is judged | reversing a `create table` is a `drop table`, so a rail that read `down` would mark every migration ever generated | | Mark it before it is applied | the marker is SQL the checksum covers, so adding it to an applied migration is an edit — `X_MIGRATION_CONFLICT`, correctly | | Rollout | expand → migrate → contract across two releases is what the generator enforces for a column: `retired`, deploy, drop | ```sql -- 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 ``` ```text 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 "" --allow-destructive ``` ## Branch DBs for agents The shipped command is `x db branch`, and it takes a verb from a closed set — `list`, `create `, `delete `. `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. ```bash x db branch create feat-new-billing --json ``` ```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"}} ``` | Property | Detail | `As of 2026-08` | |---|---|---| | Mechanism | `CREATE DATABASE "_branch_" TEMPLATE ""` — Postgres file-copies, cheap, isolated, disposable. `` is the name with every character outside `[A-Za-z0-9_]` replaced by `_` (`branchDatabaseName`), because a hyphen is not legal in an unquoted identifier — hence `myapp_branch_feat_new_billing` above. On the embedded database it is `branchPglite()`, a data-directory copy named `pgdata-`, which keeps the name as typed | **shipped** | | Writes | the MCP `db.migrate` tool applies **only** in a branch DB, never the shared dev DB (`X_MCP_NOT_BRANCH_DB`) | **shipped** | | Preview URL | reported in `data.preview`, subdomain-routed off `PORT` | **the URL is computed**; nothing routes that subdomain for you | | Listing | `x db branch list` — name, location, created-at, size, over either database. **Managed branches only**: external, a database carrying `createBranch()`'s marker comment (`listBranches()` is that read); embedded, a `pgdata-` directory under the state dir (`listPgliteBranches()`, in the CLI). A branch cloned by the pre-1.2.x `psql` path carries no marker and is invisible → [Known gaps](/known-gaps). `created-at` and `size` read `unknown` where nothing recorded one — always the size on the embedded side, since measuring it is a full directory walk | **shipped** | | Teardown | `x db branch delete `. It may only delete what `list` shows, and that guard lives in the **CLI** (`runDelete`): the shared database this session is connected to carries no marker, so it is not in the set, and there is no `--force` to get it wrong with. `@ultimat3/db`'s `dropBranch()` is the statement underneath, not the same operation — it takes the **database** name (`_branch_`), reads no marker, and will drop any database but the current one. Its `force: true` means "terminate other sessions first", which the CLI always passes; it is not an override of the guard | **shipped** | | Reaping | `reapBranches({ maxAgeMs })` drops **branches of this database only**, `As of 2026-08-19`. The marker is `ultimate:branch::` and `BranchInfo` carries `base`; a branch whose base is not `current_database()` is skipped, and so is a pre-3.x marker that records no base at all. A `createdAt` that is not finite, or that does not round-trip through `toISOString()`, is also skipped — a truncated comment used to read as an infinitely old branch and be dropped on the next sweep whatever `maxAgeMs` said | **shipped** | | Build + scoped MCP socket | a per-branch build id scoping the service worker, and `ws://localhost:9229/` | **planned**, part of `x branch` | The same clone mechanism powers test parallelism: each worker gets its own `ultimate_test_template_w` cloned from the migrated template `ultimate_test_template`, typically 100–400ms. Never mock the database — clone it. ## Errors | Code | Cause | Fix | |---|---|---| | `X_DB_DRIFT` | schema differs from migrations | `x db gen ""` | | `X_ENTITY_DUPLICATE` | two entities on the same table | rename one, or merge them | | `X_INVARIANT_VIOLATED` | a write broke a named invariant | fix the caller, or change the invariant and generate a migration | | `X_TENANCY_UNSCOPED` | a query without a tenant predicate | go through the repo | | `X_PRELOAD_UNKNOWN_RELATION` | `preload('')` named a relation no `references()` produces | pick one of the relation names the error lists, or add the `.references()` call that creates it | | `X_ENTITY_SEALED_PREDICATE` | a `.sealed()` column named in a filter, an order, a group, an index or an invariant | find the row by another column, or declare `text().sealed({ lookup: true })` for equality | | `X_ENTITY_SEALED_IN_VIEW` | a `.sealed()` column listed in `$view([...])` | remove it from the list | Full list with `--json` shapes: [Error codes](/error-codes). Source: [`docs/idea/02-primitives.md`](https://github.com/developerz-ai/ultimate/blob/main/docs/idea/02-primitives.md), [`docs/idea/10-testing.md`](https://github.com/developerz-ai/ultimate/blob/main/docs/idea/10-testing.md).