# database.schema

> Five generation schemes — TypeID (default), ULID, numeric, Snowflake, custom. Decision matrix, auto-injection lifecycle, cursor pagination, branded TypeScript types.



---

<!-- source: en/database/columns.md -->
## Column types

_Every column constructor — id, text, integer, boolean, timestamp, json, vector, reference — with their SQL types, defaults, and modifiers._

A column declaration is a single function call returning a `ColumnBuilder`. You can chain modifiers (`.nullable()`, `.unique()`, `.default(...)`, `.computed(...)`, `.onUpdate(...)`). Indexes are declared at the table level — there is no column-level `.index()`.

```ts
import { text } from '@voltro/database'

text()                       // text NOT NULL
text().nullable()            // text NULL
text().unique()              // text NOT NULL UNIQUE
text().default('draft')      // text NOT NULL DEFAULT 'draft'
```

## The complete column constructor list

### `id()`

```ts
id()                         // PRIMARY KEY — value generated by the runtime
```

**TypeID by default** — `<prefix>_<26-char-ULID>` (Stripe-style), with the prefix auto-derived from the table name. Sortable by creation time, URL-safe, no leaking sequential counts, and branded to its table type at compile time. You don't pass it explicitly on insert; the runtime generates it.

To override the scheme:

```ts
import { id } from '@voltro/database'
id()                         // TypeID — `<prefix>_<26-char-ULID>` (default)
id({ prefix: 'org' })        // TypeID with an explicit prefix
id({ scheme: 'ulid' })       // bare ULID — no prefix (don't leak the table type)
id({ scheme: 'numeric' })    // DB-managed BIGSERIAL / AUTO_INCREMENT / IDENTITY
id({ scheme: 'snowflake' })  // 64-bit Snowflake id
```

#### Composite primary key

For a natural composite key — a join table keyed on both its FKs, a time-series table keyed on `(deviceId, ts)` — declare it at the table level with `.primaryKey([...])`. It replaces the single-column `id()` PK: the listed columns become the table's key, emitted as `PRIMARY KEY (a, b)` on every dialect.

```ts
import { reference, text, table } from '@voltro/database'

table('memberships', {
  userId: reference(() => users),
  orgId:  reference(() => orgs),
  role:   text(),
}).primaryKey(['userId', 'orgId'])
// → PRIMARY KEY ("userId", "orgId")
```

The member columns are type-checked against the row, and the key implies NOT NULL on each. Don't also declare an `id()` column — the composite key **is** the key; a stray `id()` would emit a second primary key and the DDL is rejected (the DSL catches this at declaration time). Two or more columns are required — a single-column key is just `id()`.

### `text()`

```ts
text()                       // unbounded text NOT NULL (TEXT / LONGTEXT)
text().nullable()            // text NULL
text().oneOf(['a', 'b', 'c'])  // narrows to 'a' | 'b' | 'c' + CHECK (col IN (...))
text().maxLength(191)        // VARCHAR(191) (NVARCHAR on mssql) — bounded + index-able
```

Plain `text()` is **unbounded** — `TEXT` on Postgres/SQLite, `LONGTEXT` on MySQL/MariaDB (full `TEXT` there silently truncates past 64 KB). That's the right default for user prose (article bodies, JSON-as-string blobs).

Reach for **`.maxLength(n)`** when the column is a short identifier (a kind, slug, external id, resource discriminator) that participates in a `.unique([...])` or composite `.index([...])`: an unbounded column can only back a HASH long-unique constraint (MariaDB) or a prefix index, whereas a bounded `VARCHAR(n)` takes a plain BTREE key. It's cross-dialect (`VARCHAR(n)` on Postgres/MySQL/MariaDB, `NVARCHAR(n)` on MSSQL, `TEXT` on SQLite) — unlike `raw('varchar(20)')`, which hardcodes one dialect's DDL.

**Changing the bound on an EXISTING column is a real migration.** The differ compares `maxLength` on both sides, so `text()` → `text().maxLength(64)`, or 64 → 128, produces an operation:

- **widening** (a larger bound, or dropping the bound) is `safe` — no value can be lost;
- **narrowing** is `needs-backfill`: the `ALTER` fails at the database for any existing value longer than the new bound. The plan says so and gives you the query to run first.

Not compared on SQLite, which has no length-enforced type — a declared bound there is real, the live column is always `TEXT`, and comparing them would re-plan the same ALTER on every boot.

For **value** constraints (length ranges, regex, email, numeric min/max, cross-field invariants), use table-level `table().validate(Schema)` (see below) — that's the single validation surface, not a pile of per-column modifiers. There are no `.minLength()` / `.pattern()` column modifiers by design.

### `integer()`

```ts
integer()                    // integer NOT NULL
integer().default(0)
```

32-bit integer. For values past the 32-bit range use `bigint()`; for money use `decimal(p, s)` — **never** a float. Range constraints (`>= 0`, `between`) go in `table().validate(Schema)`, not on the column.

### `decimal(precision, scale)` / `numeric(precision, scale)`

Fixed-point **exact** decimal — money, tax rates, anything where floating-point rounding is unacceptable. `numeric` is an exact alias of `decimal` (same DDL; Postgres treats `NUMERIC` and `DECIMAL` as the same type).

```ts
import { decimal, numeric } from '@voltro/database'

decimal(12, 2)               // up to 9_999_999_999.99
numeric(5, 4)                // 0.0000 .. 9.9999  (alias for decimal)
decimal(38, 9).nullable()
```

Emits `NUMERIC(p, s)` on Postgres and `DECIMAL(p, s)` on MySQL / MariaDB / MSSQL; SQLite / Turso have no fixed-point type, so it's `NUMERIC` affinity storing the value as text (still exact). `precision` is the total number of significant digits; `scale` the digits after the point (`0 <= scale <= precision`, default `0`) — both validated at declaration time.

**The row type is `string`, not `number`** — on purpose. A JS `number` is an IEEE-754 double and silently loses precision past ~15-17 significant digits (`0.1 + 0.2 !== 0.3`), so a big `DECIMAL` round-tripped through a JS number would corrupt. The value carries as a string end-to-end. Do arithmetic with a decimal library (or in SQL), never JS `+`.

> **MSSQL ceiling:** the tedious driver parses a `DECIMAL` into a JS number on read, so a decimal whose full value exceeds ~15-17 significant digits loses low-order precision *on read* (the database stores it exactly; Postgres / MySQL / MariaDB / SQLite are all lossless). On MSSQL, project a large decimal via a `CAST(col AS VARCHAR)` raw fragment, or keep the value inside the JS-double range. `bigint()` is unaffected on every dialect.

### `bigint()`

64-bit integer — Snowflake ids, byte counters, large sequence numbers, anything past the 32-bit `integer()` range or the JS-safe-integer ceiling (`2^53 − 1`).

```ts
import { bigint } from '@voltro/database'

bigint()                     // BIGINT (INTEGER on sqlite/turso — both 64-bit)
bigint().default('0')
```

The row type is **`string`** (not `number`) so a value larger than `Number.MAX_SAFE_INTEGER` survives intact. Parse with `BigInt(value)` when you need to compute on it.

### `boolean()`

```ts
boolean()                    // boolean NOT NULL
boolean().default(false)
```

### `timestamp()`

```ts
timestamp()                  // timestamptz NOT NULL — always UTC
timestamp().default('now')   // server-side now()
timestamp().onUpdate('now')  // auto-bump on every write
```

Always `timestamptz` (`timestamp with time zone`). Plain `timestamp` (without tz) is a footgun across deployments in different zones.

### `date()`

```ts
date()                       // date — no time component
```

For calendar dates (birthdays, anniversaries) where wall-clock semantics matter regardless of viewer's timezone.

### `interval()`

```ts
interval()                   // interval — durations
```

Use Effect's `Duration.toIso` when writing; reads come back as ISO 8601 strings (`PT1H30M`).

### `json<T>()`

Typed JSONB column. The type parameter flows to `ctx.store.select(...)` so reads are narrowed without manual casts.

```ts
import { json } from '@voltro/database'

interface NotePrefs {
  readonly fontSize: 'sm' | 'md' | 'lg'
  readonly collapsed: ReadonlyArray<string>
}

const notes = table('notes', {
  id:    id(),
  prefs: json<NotePrefs>().default({ fontSize: 'md', collapsed: [] }),
})
```

See [JSON columns](/docs/database/json) for indexing + path queries.

### `vector(dimensions)`

```ts
vector(1536)                 // pgvector vector(1536)
```

The HNSW index is **table-level** — there is no column-level `.index('hnsw')`. Declare it as `.index('docsEmbeddingHnsw', ['embedding'], { kind: 'hnsw' })`. Requires the `pgvector` extension on postgres (the framework emits `CREATE EXTENSION IF NOT EXISTS vector` automatically). See [Vector columns](/docs/database/vectors) for the full RAG story.

### `reference(() => table, options?)`

Foreign key to another table's id column. Pass a **thunk** returning the target table (not a string name) — singular `reference`, not `references`.

```ts
import { reference } from '@voltro/database'

const messages = table('messages', {
  id:       id(),
  threadId: reference(() => threads, { onDelete: 'cascade' }),
  authorId: reference(() => users, { onDelete: 'setNull' }).nullable(),
})
```

| `onDelete` | SQL |
|---|---|
| `'restrict'` *(default)* | `ON DELETE RESTRICT` |
| `'cascade'` | `ON DELETE CASCADE` |
| `'setNull'` | `ON DELETE SET NULL` (requires `.nullable()`) |
| `'noAction'` | `ON DELETE NO ACTION` |

Every FK column gets a B-tree index by default (`index: true`); opt out with `reference(() => buckets, { index: false })` for tiny lookup tables.

### `dbEnum(name, values)`

Postgres-native enum. Declare it once, then build a column from it with `.column()`; the migrator creates the type + the column.

```ts
import { dbEnum } from '@voltro/database'

const NoteStatus = dbEnum('note_status', ['draft', 'published', 'archived'])

const notes = table('notes', {
  status: NoteStatus.column().default('draft'),
})
```

You get TypeScript autocomplete on `where('status', '=', 'published')` — typos fail the type check.

### `raw<T>(ddl)`

The escape hatch for any column type the DSL doesn't model:

```ts
import { table, id, raw } from '@voltro/database'

const events = table('events', {
  id:         id(),
  occurredAt: raw<Date>('timestamptz NOT NULL'),
  geo:        raw<string>('geography(Point, 4326)'),
})
```

The TypeScript row shape comes from the generic parameter; the DDL
fragment is emitted **verbatim on every dialect** — the framework does
no translation, so you own dialect-portability of the text.

Because the fragment already encodes the type, nullability, and default,
the fragment is the single source of truth: chaining `.nullable()`,
`.default()`, or `.unique()` on a `raw(...)` column **throws at
declaration time** (two sources would drift). Encode `NULL` / `DEFAULT` /
`UNIQUE` directly in the fragment instead.

## Universal modifiers

These chain on every column type:

| Modifier | What it does |
|---|---|
| `.nullable()` | Drops the `NOT NULL` constraint. |
| `.unique()` | Adds a unique constraint. Optional `{ dedup }` migration strategy. |
| `.default(value)` | Default value. Literal, `'now'` for `now()`, OR a callback — see [Default with callback](#default-with-callback). |
| `.computed(row => ...)` | Computed-from-row value, app-side at write-time. See [Computed columns](#computed-columns). |
| `.onUpdate(value)` | Auto-bump on UPDATE (timestamps). |
| `.generatedAs(expr, { stored })` | DB-level generated column — see [Generated columns](/docs/database/generated-columns). |
| `.oneOf([...])` | Closed value set (text only) — narrows the type + emits a `CHECK`. |
| `.maxLength(n)` | Bounds a text column → `VARCHAR(n)` / `NVARCHAR(n)` (text only). For index-ability — see the `text()` section. |
| `.check(expr)` | Raw SQL `CHECK` on the column — DB-enforced. Chainable for multiple. Any column type. See [DB-level checks](#db-level-checks). |

### DB-level checks

Two layers enforce row invariants, and they're complementary:

- **`.check(expr)` (column) / `table().check(name, expr)` (table)** — emit a SQL `CHECK` constraint. **DB-enforced**: the rule holds no matter which client writes the row (a raw `psql`, another service, a buggy migration) — defense in depth. `expr` is **raw SQL emitted verbatim**, so YOU own its cross-dialect portability (stick to standard operators + unquoted column names; avoid dialect-specific functions / regex). `CHECK` is enforced on Postgres / MySQL 8+ / MariaDB / MSSQL / SQLite.

  ```ts
  table('bookings', {
    id:       id(),
    seats:    integer().check('seats > 0'),
    startsAt: timestamp(),
    endsAt:   timestamp(),
  })
    .check('booking_window', 'startsAt < endsAt')   // cross-column → table level
  ```

- **`table().validate(Schema)` (below)** — app-level `effect/Schema` validation, run before the INSERT/UPDATE. Use it for the rich stuff a portable `CHECK` can't express: regex / email / structured strings, length ranges, numeric bounds, cross-field rules with custom typed errors. It's the single validation surface — don't reach for a pile of `.min()` / `.max()` / `.pattern()` column modifiers (there aren't any, by design).

- **`table().rule(name, predicate)` (cross-table)** — neither of the above can read ANOTHER table. A [business rule](/docs/data/mutations#cross-table-business-rules-rule) is a predicate the runtime evaluates inside the mutation transaction, so it can compare the written row against rows in other tables (an invoice total vs. its line items) and rolls the mutation back with a typed `BusinessRuleViolation` on failure.

Rule of thumb: **`.check()` when the DATABASE must guarantee a single-row constraint; `table().rule()` for a cross-table invariant; `validate(Schema)` for everything else.** There is no `.comment()` / `.index()` column modifier — indexes are table-level.

## Default with callback

`default()` accepts three forms:

```ts
status:        text().default('pending')              // literal — lands in DDL DEFAULT
createdAt:     timestamp().default('now')              // server-now — lands in DDL DEFAULT
correlationId: text().default(() => crypto.randomUUID())  // app-side factory
```

The literal + `'now'` forms emit `DEFAULT` in the table DDL — the database fills in the value when the row omits the column. The **callback form** is evaluated **app-side** at insert time by the MutationStore wrapper, BEFORE the row reaches the database. Use it for per-insert dynamic values the DB can't produce:

```ts
import { id, table, text, timestamp } from '@voltro/database'

export const sessions = table('sessions', {
  id:            id(),
  correlationId: text().default(() => crypto.randomUUID()),    // unique per row, JS-generated
  region:        text().default(() => process.env.REGION!),     // env-driven
  startedAt:     timestamp().default('now'),                    // DB-generated, lands in DDL
})
```

Callback defaults compose with the framework's existing auto-stamps (audit `createdAt` / `updatedAt`, tenant `tenantId`, etc.) — your callback runs after the framework fills its own auto-stamps, so the callback's `row` argument carries the stamped fields.

**Important**: callback defaults are NOT emitted in the DDL. Raw SQL inserts that bypass the framework (psql, manual migrations, third-party tooling) won't see the callback fire — only the literal portion of the column declaration lands in `CREATE TABLE`. For DB-enforced defaults always reachable from raw SQL, use the literal/`'now'` form.

## Computed columns

`.computed(row => ...)` marks the column as **derived from other row fields**. The function runs on INSERT (AFTER framework auto-stamps + factory defaults) and again on every UPDATE; its return value **overwrites whatever the caller passed** for that column (computed values are an opinion of the schema, not a user-overridable input).

```ts
import { id, table, text } from '@voltro/database'

const slugify = (s: string): string => s.toLowerCase().replace(/\s+/g, '-')

export const posts = table('posts', {
  id:    id(),
  title: text(),
  // slug ALWAYS comes from title — no caller can pass a stale slug
  slug:  text().computed((row) => slugify(String(row.title))),
})
```

Computed columns **recompute on UPDATE too**. Every computed column on the table re-runs against the merged post-update row (the patch layered over the existing row), so a derived `slug` stays in sync when its source `title` changes — no `dependsOn` declaration, no explicit recompute in the handler:

```ts
// mutations/posts.update.mutation.server.ts — slug is recomputed automatically
const execute = async (input, ctx) =>
  ctx.store.update('posts', input.id, { title: input.title })
  // slug becomes slugify(input.title) on its own; a `slug` in the patch is overwritten
```

Because all computed columns re-run on every UPDATE, keep the functions pure and cheap. They receive the full merged row, so an unchanged source column the computed value reads is still available.

## Table-level validation

`.validate(Schema)` adds a pre-INSERT validation pass. The framework decodes the row against the schema BEFORE the SQL INSERT runs. Decode failure throws a typed `TableValidationFailed` error you can declare on the mutation's `error:` channel.

```ts
import { id, table, text } from '@voltro/database'
import { Schema } from 'effect'

export const users = table('users', {
  id:    id(),
  email: text().unique(),
}).validate(Schema.Struct({
  id:    Schema.String,
  email: Schema.String.pipe(Schema.pattern(/^[^@]+@[^@]+$/)),
}))
```

The validation runs **after** framework auto-stamps + computed columns — by the time the decoder sees the row, `createdAt`/`updatedAt` from `audit()`, `tenantId` from `tenant()`, your `.computed()` values, and your callback `.default()` values are all in place. The schema sees the fully-stamped row.

This lets you express constraints the column DSL can't:

- **Cross-field invariants** (`startDate < endDate`, `total === items.reduce(sum)`)
- **Format checks beyond `oneOf`** (regex patterns, length bounds, structured strings)
- **Refinements** (`Schema.NonEmptyString`, `Schema.Number.pipe(Schema.between(0, 1))`)
- **Discriminated unions** for shape variants

Catch the failure in your mutation handler if you want typed UI feedback:

```ts
// mutations/users.create.mutation.ts
import { TableValidationFailed } from '@voltro/runtime'

export const createUser = defineMutation({
  name:   'users.create',
  guards: [{ scope: 'users:write' }],
  input:  Schema.Struct({ email: Schema.String }),
  output: Schema.Struct({ id: Schema.String }),
  target: { table: 'users', op: 'insert' },
  error:  TableValidationFailed,
})
```

### Validating UPDATE patches — `.validatePatch(Schema)`

`.validate(...)` runs on INSERT. For UPDATE patches, add `.validatePatch(Schema)` — the framework wraps the schema in `Schema.partial`, so a patch that touches only some columns is valid; every field that IS present must satisfy its rule. A bad patch throws the same typed `TableValidationFailed` error.

```ts
import { id, integer, table, text } from '@voltro/database'
import { Schema } from 'effect'

export const users = table('users', {
  id:    id(),
  email: text().unique(),
  age:   integer(),
})
  .validate(Schema.Struct({
    email: Schema.String.pipe(Schema.pattern(/^[^@]+@[^@]+$/)),
  }))
  .validatePatch(Schema.Struct({
    age: Schema.Number.pipe(Schema.greaterThanOrEqualTo(0)),
  }))
```

Pass the same shape you'd pass to `.validate(...)` — the partial wrapping is applied for you, so don't pre-wrap fields in `Schema.optional`. The patch decoder runs on the (stamped) patch right before the UPDATE; `ctx.store.update('users', id, { age: -5 })` rejects, while `ctx.store.update('users', id, { email: 'new@x.io' })` passes (the `age` rule only fires when `age` is in the patch).

## Specialized column types

Beyond the core `text` / `integer` / `boolean` / `timestamp` / etc.,
the framework ships richer types for specific use cases:

- **[Enums (`dbEnum`)](/docs/database/enums)** — postgres-native ENUM
  types with literal-union narrowing, cheap `ALTER TYPE ADD VALUE`
  migrations. Falls back to CHECK constraints on other dialects.
- **[Generated columns (`generatedAs`)](/docs/database/generated-columns)**
  — DB-level computed values, kept consistent on UPDATE without
  app-side stamping. Distinct from `.computed()` which runs in the
  MutationStore middleware at INSERT.
- **[Arrays + intervals](/docs/database/arrays-intervals)** —
  postgres-native `array(text())` / `array(integer())` / `interval()`
  with JSON-stringified codec for non-postgres dialects.
- **[Vectors](/docs/database/vectors)** — `vector(dim)` for embedding-
  based semantic search via `pgvector`.
- **[PostGIS](/docs/database/postgis)** — `geography()` /
  `geometry()` from `@voltro/plugin-postgis` for location-aware apps.
- **[JSON](/docs/database/json)** — `json<T>()` for arbitrary nested
  data; native JSONB on postgres.

## Pointing at a plugin-owned row — `pluginRef`

```ts
import { pluginRef } from '@voltro/database'
import { aiFlowsTable } from '@voltro/plugin-ai-flows'

export const favourites = table('favourites', {
  id: id(),
  flowId:     pluginRef(aiFlowsTable, { orphanPolicy: 'delete' }),
  sharedFlow: pluginRef(aiFlowsTable, { orphanPolicy: 'null' }).nullable(),
})
```

A plain typed id column with **no foreign key**, plus a declared orphan rule the
framework runs on the post-commit change channel.

**Reach for `reference()` first.** A real foreign key across a plugin boundary
works and survives the plugin renaming its table, because `reference()` takes
the table as a VALUE — see
[plugins/overview](/docs/plugins/overview#pointing-your-table-at-a-plugins-row).
`pluginRef` is for the case where you have deliberately chosen NOT to have a
key: it enforces nothing at the database level.

What it restores is the piece that goes missing with that choice — not the
constraint but the RULE, which otherwise becomes a hand-written subscriber per
app that nobody notices the absence of.

| policy | on the target's delete |
| --- | --- |
| `'delete'` | delete the referencing row — for one that only exists to point (a favourite, a pin, a share) |
| `'null'` | clear the column, keep the row. Requires `.nullable()` |
| `'keep'` (default) | nothing — the explicit "I handle it myself" |

Four things worth knowing before you rely on it:

- **The tenant boundary fails closed.** A referencing row whose tenant differs
  from the deleted row's — or which has none — is left alone. Deleting across a
  tenant boundary because a scope was missing is worse than leaving an orphan.
- **Soft deletes are opt-in** (`onSoftDelete: true`). A soft delete is a state
  the target can undo, so cascading on it destroys rows a restore cannot bring
  back — and plugin tables are inconsistent about carrying `deletedAt` at all.
- **The target is a table VALUE.** A rename carries the rule with it, which is
  the whole reason the FK was refused; a string would put the coupling back.
- **A missing plugin refuses at boot**, naming both sides. A declared rule
  against a table nothing registers would sit there looking enforced.


## Anti-patterns

- **`serial`/`bigserial` integer ids.** Leaks row counts via `/users/12345`. Use `id()` (TypeID).
- **`text` (or a float) for currency.** Use `decimal(15, 2)` — exact fixed-point, carried as a string.
- **`timestamp` without `tz`.** Use `timestamp()` which is `timestamptz`.
- **Storing JSON blobs as `text`.** Use `json<T>()` for type-safety + JSONB performance.
- **`references('table')`.** The constructor is `reference(() => table)` — singular, thunk-arg.
- **Declaring a `timestamp()` column as `Schema.Number` in a descriptor's `output`.** That describes the *wire* type, not the domain type, so the schema has nothing left to convert and the handler ends up doing it by hand. Declare the field schema instead — [`timestampMs` / `timestampMsOrNull`](/docs/data/queries#output-is-the-serializer-timestampms) from `@voltro/database/wire`. Do **not** reach for `rowSchema(table)` here: a descriptor cannot import a table value without dragging `@voltro/database` into the browser bundle, and the boot aborts. `rowSchema` is a [server-side row codec](/docs/data/queries#row-codecs-for-server-side-code-rowschema-columnschema).



---

<!-- source: en/database/mixins.md -->
## Mixins

_Reusable column bundles with runtime behaviour — tenant(), audit(), softDelete(), deactivation(), and how to write your own._

A mixin is a value you chain onto a `table(...)` declaration with `.with(...)`. It brings more than just columns — the framework's mixins register hooks with the runtime (tenant scoping, audit row writes, soft-delete filtering) that fire transparently. Each ships from its own plugin package.

```ts
import { table, id, text } from '@voltro/database'
import { softDelete } from '@voltro/plugin-soft-delete'
import { audit } from '@voltro/plugin-audit'
import { tenant } from '@voltro/plugin-multitenancy'

export const notes = table('notes', {
  id:    id(),
  title: text(),
  body:  text(),
})
  .with(tenant(), audit(), softDelete())   // chain them — order doesn't matter
```

Mixins compose: when one `requires` another, the dep is auto-applied (deduped by stable `id`). `softDelete()`, `tenant()`, and `deactivation()` all transitively require `audit()`, so you don't have to list `audit()` separately.

## Built-in mixins

### `tenant()` — `@voltro/plugin-multitenancy`

```ts
.with(tenant())
```

Adds:

- **Column** `tenantId: text NOT NULL` (indexed).
- **Subscription behaviour** — every `*.query.ts` reading this table has `WHERE tenantId = ctx.subject.tenantId` AND-merged into the executor's own filters.
- **No automatic write enforcement.** Mutations must call `assertOwnTenant(input.tenantId, ctx.subject)` explicitly. See [Multi-tenancy](/docs/multi-tenancy/overview).

### `audit()` — `@voltro/plugin-audit`

```ts
.with(audit())
```

Adds the four audit columns + auto-fill hooks:

| Column | Type | Filled by |
|---|---|---|
| `createdAt` | `timestamptz` | server `now()` on insert |
| `updatedAt` | `timestamptz` | server `now()` on insert + update |
| `createdBy` | `text` (nullable) | `ctx.subject.id` on insert |
| `updatedBy` | `text` (nullable) | `ctx.subject.id` on update |

Use this any time you need a "who touched this row and when". For high-frequency tables (events, metrics) audit overhead is a few bytes per row — fine.

### `softDelete()` — `@voltro/plugin-soft-delete`

```ts
.with(softDelete())
```

Adds:

- **Column** `deletedAt: timestamptz NULL`.
- **Read filter** — every subscription auto-merges `deletedAt IS NULL`.
- **Helper** `ctx.store.delete('notes').where(...).soft()` → sets `deletedAt = now()` instead of actually deleting.

Restore with `.restore()`:

```ts
ctx.store.update('notes')
  .where('id', noteId)
  .restore()                 // sets deletedAt back to NULL
```

To query INCLUDING soft-deleted rows (admin audit log, recycle bin), use `.withDeleted()`:

```ts
ctx.store.select('notes').withDeleted().where('id', noteId).maybeOne()
```

### `deactivation()` — `@voltro/plugin-deactivation`

```ts
.with(deactivation())
```

Adds:

- **Column** `deactivatedAt: timestamptz NULL`.
- **Column** `deactivatedBy: text` (nullable) → `actors`.

A row whose `deactivatedAt` is set is a **deactivated** subject (typically a user): locked out, but still **visible** — their name on past audit rows, assignments, and history stays intact. That's the difference from `softDelete()`:

- `softDelete()` → row is **hidden** from default reads (recycle-bin semantics).
- `deactivation()` → row stays **visible**, just flagged inactive. No read-scoping, no delete interception — you set/clear `deactivatedAt` with a normal update.

The two are orthogonal and compose: a user can be deactivated (visible) and later soft-deleted (hidden) for GDPR. `deactivation()` transitively requires `audit()` — a deactivation is an audit-worthy event, so `audit()` captures the WHO/WHEN via `updatedBy`/`updatedAt` alongside.

```ts
const users = table('users', { id: id(), email: text() })
  .with(audit(), deactivation())

// deactivate — a plain update; the row stays visible
await ctx.store.update('users').where('id', userId).set({ deactivatedAt: new Date() })
// reactivate
await ctx.store.update('users').where('id', userId).set({ deactivatedAt: null })
```

### `vectorEmbedding({ from, model, dimensions })` — `@voltro/database`

For RAG use cases — derives an embedding from a text column automatically.

```ts
const docs = table('docs', {
  id:    id(),
  body:  text(),
}).with(vectorEmbedding({ from: 'body', model: 'text-embedding-3-small', dimensions: 1536 }))
```

Adds:

- **Column** `embedding: vector(1536)` (override the name with `as`) plus an HNSW index over it (skip with `index: false`, pick the metric with `distance`).
- **Mutation hook** — re-embeds on every insert/update of the source column. The runtime injects `@voltro/ai`'s `embed` into the hook.
- **Query helper** `nearestNeighbours(query: string, k: number)` that embeds the query string + finds the nearest rows.

ANN acceleration is first-class on postgres + MariaDB 11.7+; on the other dialects the column + hook still work, but search falls back to a sequential scan. See [Vector columns](/docs/database/vectors) for the deep dive.

## Composing mixins

Chain every mixin in one `.with(...)` call (or several — they accumulate):

```ts
const notes = table('notes', {
  id:    id(),
  body:  text(),
})
  .with(tenant(), audit(), softDelete())
```

Order doesn't matter — the dep resolver materialises each mixin's columns once, and a `requires` graph (e.g. `softDelete()` → `audit()`) auto-applies the dependency. If two mixins declare the same column name the table fails to compile with a clear error.

> A directly-applied mixin's columns (`tenantId`, `createdAt` / `updatedAt`, `deletedAt`) **are addressable in an `.index([...])` / `.unique([...])` key** — declare the index/unique **after** `.with(...)` so the columns are in scope. Only directly-applied mixins count: `.with(tenant())` exposes `tenantId` but not the transitively-required `audit()` columns — apply `audit()` explicitly if you need to index those. See [Indexing mixin-contributed columns](/docs/database/indexes#indexing-mixin-contributed-columns).

## Patterns that aren't mixins

A few common needs are NOT shipped as mixins — they're one-liners on the column or table:

- **`createdAt` / `updatedAt` only** (no actor tracking) — declare
  `timestamp().default('now')` and `timestamp().default('now').onUpdate('now')`
  directly, or use `audit()` if you want the `*By` columns too.
- **Slug from a title** — `slug: text().computed((row) => slugify(String(row.title)))`.
  See [Computed columns](/docs/database/columns#computed-columns).
- **Optimistic locking** — add an `integer` `version` column and guard
  updates with the runtime's `.expectVersion(n)` (throws `OptimisticLockError`
  on a stale version). See [Transactions](/docs/database/transactions#optimistic-concurrency).

## Writing your own mixin

A mixin is a function returning a `MixinDefinition` — build it with `defineMixin`. Columns go in `fields`; runtime behaviour goes in `behaviors`:

```ts
import { defineMixin, timestamp, type MixinDefinition } from '@voltro/database'

export const heartbeat = (): MixinDefinition<Record<string, never>> =>
  defineMixin({
    id: 'acme/heartbeat',
    fields: {
      lastSeenAt: timestamp().nullable(),
    },
    behaviors: {
      // Stamp lastSeenAt on the row whenever it's written.
      beforeUpdate: (input) => ({ ...input, lastSeenAt: new Date() }),
    },
  })
```

`behaviors` channels:

- `beforeInsert(input, ctx)` → input — mutate / decorate the about-to-be-inserted row.
- `beforeUpdate(input, ctx)` → input — same for updates.
- `onDelete(ctx)` — intercept deletes (e.g. soft-delete semantics).
- `defaultWhere(ctx)` → predicate — AND-merged into every read, including reactive subscriptions.

A mixin can also declare `requires: [otherMixin()]` (resolved depth-first) and `indexes: [{ fields: [...] }]`. The framework's `tenant()` is implemented as a single-file mixin with `defaultWhere` doing the AND-merge — read the source for a full reference.

## When NOT to use a mixin

- One-off columns specific to a single table — just declare them inline.
- Cross-table denormalisation — that's an event-driven workflow, not a mixin.
- "Magic" timestamp formats — keep it boring: `timestamp().default('now')` is clearer than a custom mixin.



---

<!-- source: en/database/concurrency-and-expiry.md -->
## Concurrency + expiry

_.version() for optimistic locking and expires() for time-limited rows — what each guarantees, on which dialect._

Two column-level tools for questions a schema cannot otherwise answer: **which write is newest**, and **when does this row stop counting**.

## `.version()` — optimistic locking

Two clients read the same row and both write it. Without a version the second silently wins, and the first user's change is gone with no trace. That is the shape of every *"my edit disappeared"* report.

```ts
export const documents = table('documents', {
  id:      id(),
  title:   text(),
  version: integer().version(),
})
```

From then on the store **increments** `version` on every update, and an update that carries an expectation fails when the row has moved on:

```ts
// The client sends the version it read.
yield* ctx.store.update('documents', input.id, { title: input.title, version: input.version })
// → VersionConflict { expected: 3, actual: 7 } when four writes landed in between
```

`VersionConflict` is a typed error, so it reaches the client typed and a UI can offer *reload and re-apply* instead of showing a crash. It carries **both** numbers, because "someone else changed it" is not actionable while "you had 3, it is now 7" is.

**Why not `updatedAt`.** A timestamp cannot do this job. Two writes in the same millisecond are indistinguishable, and across replicas the clocks disagree — a comparison that looks correct in a test loses rows under load. An integer the database owns is totally ordered and needs no clock. (`.version()` therefore rejects a `text()` or `timestamp()` column at declaration.)

**What it does not do.** It is not a history — it records *that* a row changed, not what to; use [`plugin-row-history`](/docs/plugins/row-history) for that. It is not a lock: a conflict is **reported**, never queued or merged, because merging two intents is a decision only your application can make. And it is not a retry — "re-apply my change on top of theirs" is correct for some changes and wrong for others, so you write it.

**Three details worth knowing:**

- The version a caller sends is an **expectation, never a write**. It is stripped from the patch, so a client cannot pin its own version and win every race.
- An update with **no** expectation is still last-write-wins — the default does not change — but the version **still advances**. A version that moved only for careful writers would be worse than none: it would sit still while a careless write changed the row.
- A row **deleted** underneath you is a conflict too, with `actual: null`. That is how you tell "deleted" from "changed".

## `expires()` — a row with an end date

```ts
export const inviteLinks = table('inviteLinks', {
  id:    id(),
  email: text(),
}).with(expires())

await ctx.store.insert('inviteLinks', {
  email,
  expiresAt: new Date(Date.now() + 24 * 3_600_000),
})
```

After that instant the row is **not returned by reads**. `expiresAt` is nullable and null means *never*, so adding the mixin to an existing table does not make its rows vanish.

Opt out for a deliberate read — an admin view, a grace-period check:

```ts
ctx.store.select('inviteLinks').includeExpired()
```

### Read this before you rely on it

**Visibility and storage are two different guarantees, and only one of them holds everywhere.**

| | where | when |
| --- | --- | --- |
| **Invisible to reads** | every dialect | immediately, the instant it passes |
| **Physically deleted** | every dialect | eventually, by the retention sweep |

So an expired row is **invisible immediately and still present in the database for a while**. That is the right trade — making visibility depend on the sweep would mean a row that vanished on postgres and kept serving on MariaDB — but it matters: *do not treat an expired row as unreachable*. If the value must actually be gone, delete it, or do not store it in a row at all.

The second row of that table used to say **postgres only**, and it was accurate: the sweep was registered behind a `dialect !== 'postgres'` early return, so on mariadb, mysql, mssql and sqlite nothing was ever deleted and the boot printed no armed-policies line to say so. What is postgres-specific is the fast set-based DELETE, not the policy — the sweep falls back to a bounded read plus one set-based delete per batch elsewhere. Fixed in 0.33.0; on those dialects the first boot after upgrading will have a backlog to work through.

## See also

[Column types](/docs/database/columns) · [Mutations](/docs/data/mutations) · [Soft delete](/docs/plugins/soft-delete)



---

<!-- source: en/database/ids/index.md -->
## ID schemes

_Five generation schemes — TypeID (default), ULID, numeric, Snowflake, custom. Decision matrix, auto-injection lifecycle, cursor pagination, branded TypeScript types._

Every `id()` column in a Voltro schema has a generation scheme. The framework auto-injects the ID before each insert based on the column's declared scheme; application code never has to generate one by hand for the common case.

The default is **TypeID** — a Stripe-style `<prefix>_<26-char-ulid>` shape that's sortable, brandable, URL-friendly. Other schemes exist for cases where TypeID doesn't fit:

- [TypeID](./typeid) — default. Sortable, branded by table, doubleclick-selectable.
- [ULID](./ulid) — 26-char Crockford-base32 without prefix. Drops the type tag for privacy-sensitive IDs.
- [Numeric](./numeric) — dialect-native auto-increment. Internal tables only.
- [Snowflake](./snowflake) — Twitter 64-bit. Extreme-throughput distributed inserts.
- [Custom](./custom) — bring your own generator.

## Decision matrix

| Scheme       | Shape                                | Pick when |
|--------------|--------------------------------------|-----------|
| `typeid` (default) | `user_01j5xkqyz8x3n4m9pvabcd`   | Every user-facing entity. Sortable, brandable, URL-safe. |
| `ulid`       | `01j5xkqyz8x3n4m9pvabcd`             | Public-facing IDs where the table type should NOT leak. Invite tokens, share links, magic links. |
| `numeric`    | `1`, `2`, `3`, …                     | Internal tables that are never URL-exposed AND benefit from compact FK joins. CDC log, audit log, migration log. |
| `snowflake`  | `1879382109193580544`                | Extreme-throughput distributed inserts. Set `SNOWFLAKE_MACHINE_ID` (0–1023) per process. |
| `custom`     | user-supplied generator              | Escape hatch — any string format you control. |

Default to **typeid**. The other schemes exist for specific reasons; don't pick one without a reason.

## Declaration

```typescript
import { id, table, text } from '@voltro/database'

// Default — implicit typeid, prefix auto-derived from table name.
export const users = table('users', {
  id: id(),                // → 'user_01j5xkqyz8x3n4m9pvabcd'
  email: text(),
})

// Explicit prefix (for tables whose singularize-prefix would be ugly):
export const orgs = table('organizations', {
  id: id({ prefix: 'org' }),
})

// Explicit non-typeid scheme:
export const cdcLog = table('_voltro_cdc_log', {
  id: id({ scheme: 'numeric' }),
})

export const inviteTokens = table('invite_tokens', {
  id: id({ scheme: 'ulid' }),
})

export const events = table('events', {
  id: id({ scheme: 'snowflake' }),
})

export const shortlinks = table('shortlinks', {
  id: id({
    scheme: 'custom',
    generate: (_tableName) => `sl_${nanoid(8)}`,
  }),
})
```

The schema registry records the scheme + (for typeid) the prefix at registration time. The framework's mutation middleware reads it before every insert to inject the ID.

## Auto-injection lifecycle

The mutation middleware (in `runtime/storeMiddleware.ts`) wraps every `ctx.store.insert(...)` call:

```typescript
// You write:
await ctx.store.insert('todos', { title: 'hello', done: false })

// The middleware reads the schema's id scheme + prefix:
//   schemaRegistry.idScheme('todos')  → 'typeid' | 'ulid' | 'numeric' | 'snowflake' | custom
//   schemaRegistry.idPrefix('todos')  → 'todo' (auto-derived)

// For typeid/ulid/snowflake/custom: generate + insert.
// For numeric: omit `id` from the insert; let the dialect's
// AUTO_INCREMENT / SERIAL / IDENTITY fire + return via RETURNING.
```

Explicit `id:` values pass through unchanged — useful for tests with deterministic IDs and for signup flows where the actor is the row being created.

```typescript
// Production code — id auto-injected.
const row = await ctx.store.insert('todos', { title, done: false })
// row.id === 'todo_01j5xkqyz8x3n4m9pvabcd'

// Test — deterministic id.
await ctx.store.insert('todos', { id: 'todo_pinned_for_test', title, done: false })

// Signup self-stamping — the actor IS the row. Generate the id up front
// so createdBy can point at the row being created, in the same insert.
import { typeid } from 'typeid-js'
const userId = typeid('user').toString()
await ctx.store.insert('users', { id: userId, email, createdBy: userId })
```

## Branded TypeScript types

The framework's schema registry brands each table's ID type — `UserId`, `OrgId`, `ProjectId` — at the TypeScript level. They're strings at runtime, opaque-branded types at compile time:

```typescript
declare const tagOfTable: unique symbol
type UserId = string & { [tagOfTable]: 'users' }
type OrgId  = string & { [tagOfTable]: 'organizations' }

function findUser(id: UserId): Promise<User> { ... }

const user = await store.query(database.users.findFirst())
findUser(user.id)            // ✓ typechecks — user.id is UserId
findUser('plain-string')     // ✗ "Type 'string' is not assignable to type 'UserId'"
findUser(org.id)             // ✗ "Type 'OrgId' is not assignable to 'UserId'"
```

The brand flows through `reference()` columns too — `posts.authorId` is typed as `UserId`, not just `string`, so passing the wrong ID kind into a FK lookup fails compilation.

Brand-skipping escape hatch: cast through `as unknown as UserId` when you genuinely need to construct an ID from a free string (e.g. reading from a URL param after validation). The cast is loud + greppable.

## Cursor pagination

Sortable IDs (TypeID, ULID, Snowflake) enable cheap cursor pagination — `WHERE id > $cursor ORDER BY id LIMIT N` — without OFFSET's O(n) scan.

```typescript
import { paginateById } from '@voltro/database'

// `paginateById(descriptor, cursor, limit)` RETURNS a QueryDescriptor
// — pass it to `store.query()`. The cursor is the last row's id.

// First page.
const rows = await ctx.store.query(
  paginateById(database.posts.where(eq('userId', subject.id)).descriptor, undefined, 20),
)
const nextCursor = rows.at(-1)?.id   // undefined on the last page

// Subsequent pages: pass nextCursor back in.
const page2 = await ctx.store.query(
  paginateById(database.posts.where(eq('userId', subject.id)).descriptor, nextCursor, 20),
)
```

Numeric IDs work too. Custom IDs work IFF the generator output is comparable in lexical order (Snowflake's left-padded 19-digit form is; an arbitrary `generate()` may not be).

## Per-scheme details

See the detail pages for the runtime behaviour:

- [TypeID](./typeid) covers prefix-resolution rules + the `typeid-js` integration + sort-order properties.
- [ULID](./ulid) covers when to drop the prefix tag.
- [Numeric](./numeric) covers per-dialect AUTO_INCREMENT idioms + enumeration-attack risks.
- [Snowflake](./snowflake) covers the Twitter spec + `SNOWFLAKE_MACHINE_ID` + the 70-year epoch caveat + JS Number precision.
- [Custom](./custom) covers the generator API + deterministic-output requirements.

## Where it lives

- `voltro/packages/database/src/idGenerator.ts` — `resolveIdScheme` / `generateId(scheme, tableName)` / `deriveTypeIdPrefix` for TypeID / ULID / Numeric / Snowflake / Custom
- `voltro/packages/database/src/snowflake.ts` — Twitter-spec Snowflake implementation
- `voltro/packages/database/src/columns.ts` — `id()` column constructor + `IdOptions`
- `voltro/packages/runtime/src/schemaRegistry.ts` — tracks scheme + prefix per table for branded TS types
- `voltro/packages/runtime/src/storeMiddleware.ts` — `stampedForInsert` auto-injects from the registered scheme
- `voltro/packages/database/src/queryBuilder.ts` — `paginateById` cursor pagination helper



---

<!-- source: en/database/ids/typeid.md -->
## TypeID — the default

_Stripe-style prefix_ulid IDs. Sortable, branded, URL-friendly, doubleclick-selectable, ~30 char. Default for every user-facing entity._

TypeID is the framework's default ID scheme — the format Stripe popularized: a short table-tag prefix, an underscore, then a 26-character Crockford-base32 ULID body.

```
user_01j5xkqyz8x3n4m9pvabcd
└─┘ └──────────────────────┘
type    ulid body (48-bit ms timestamp + 80-bit random)
```

Every `id()` call without an explicit `scheme:` argument produces TypeID.

## Spec

TypeID is a community spec maintained at [typeid.io](https://typeid.io). The framework implements it via the [`typeid-js`](https://github.com/jetify-com/typeid-js) library — Jetify's official TypeScript implementation. Shape:

- **Prefix**: must match `^[a-z][a-z0-9_]*$` — lowercase ASCII that STARTS with a letter (a leading digit or underscore is rejected by `typeid-js`). Up to 63 chars (practical limit: keep it short).
- **Separator**: single underscore `_`.
- **Body**: 26 characters in Crockford-base32 (no `i`, `l`, `o`, `u` to avoid visual ambiguity). Represents a 128-bit ULID.
- **Body composition**: 48-bit Unix millisecond timestamp followed by 80 random bits.

Total length: prefix + 1 + 26 chars. For a typical 3–5 char prefix, that's 30–32 characters.

## Why default

### Sortable by creation time

The 48-bit timestamp prefix means IDs generated in order are lexically ordered:

```typescript
import { typeid } from 'typeid-js'
const a = typeid('user').toString()  // user_01j5xkqyz8x3n4m9pvabcd
await new Promise(r => setTimeout(r, 1))
const b = typeid('user').toString()  // user_01j5xkqyz8x3n4n0pcdefgh
a < b   // true
```

`ORDER BY id` works as a cheap "ORDER BY createdAt" with no separate column. Cursor pagination via `paginateById` exploits this.

### Doubleclick-selectable

Modern browsers treat `_` as a word-boundary character. Doubleclicking on `user_01j5xkqyz8x3n4m9pvabcd` in a log line, terminal, or page selects the WHOLE id. UUIDv4's dashes break this — `01234567-89ab-...` selects only one of the hyphen-separated chunks.

This is the difference between "I can paste this from a Slack log into a SQL query" and "I have to manually fix the substring on every paste".

### URL-friendly

Only `[a-z0-9_]` — no percent-encoding needed. `/posts/post_01j5xkqyz8x3n4m9pvabcd` is exactly 31 characters in the URL with no escaping overhead. Compare to UUID's dashes (which RFC-3986 allows but tooling sometimes mangles) or base64-encoded random bytes (which need URL-safe base64 + are rarely the right call anyway).

### Branded at the TypeScript level

The framework's schema registry brands each table's ID type. `UserId` ≠ `OrgId` ≠ `ProjectId` at compile time, even though they're all `string` at runtime:

```typescript
function getUser(id: UserId): Promise<User> { ... }

const user: User = ...
const org:  Org  = ...

getUser(user.id)              // ✓ typechecks
getUser(org.id)               // ✗ Type 'OrgId' not assignable to 'UserId'
getUser('plain-string')       // ✗ Type 'string' not assignable to 'UserId'
```

The brand flows through `reference()` columns — `posts.authorId` is `UserId` not `string`, so FK lookups can't accept the wrong table's ID by accident.

### Compact

~30 chars vs UUIDv4's 36 (`xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx`). Multiplies across foreign keys + log lines + URL params. Snowflake (8 bytes) is denser still but loses the type tag.

## Prefix resolution

Two paths:

### Explicit prefix

```typescript
id({ prefix: 'foo' })  // → 'foo_01j5xkqyz8x3n4m9pvabcd'
```

Use when the implicit derivation would produce something ugly or non-unique.

### Implicit (derived from table name)

```typescript
table('users', { id: id() })            // → 'user_01j5...'
table('todos', { id: id() })            // → 'todo_01j5...'
table('apps',  { id: id() })            // → 'app_01j5...'
table('posts', { id: id() })            // → 'post_01j5...'
```

The derivation runs at schema-registration time via a simple-singularize:

- Strip trailing `s` from plural nouns.
- Special-case nothing else.

For unconventional plurals (`feet`, `children`, `status`, `data`, `series`), set an explicit prefix to avoid the resolver guessing wrong:

```typescript
table('feet',     { id: id({ prefix: 'foot' }) })
table('children', { id: id({ prefix: 'child' }) })
table('status',   { id: id({ prefix: 'status' }) })  // identical singular/plural
table('data',     { id: id({ prefix: 'datum' }) })
```

The schema registry validates prefix at registration — a prefix collision across two tables throws at boot with both call sites pointed at. You can't accidentally use `user_` for both `users` and `system_users`.

## Lifecycle

```typescript
// Production handler — id auto-injected by the mutation middleware.
const row = await ctx.store.insert('todos', { title: 'hello', done: false })
// row.id === 'todo_01j5xkqyz8x3n4m9pvabcd'

// Test — deterministic id for assertion.
await ctx.store.insert('todos', { id: 'todo_pinned_for_test', title, done: false })

// Signup self-stamping — actor is the row being created.
import { typeid } from 'typeid-js'
const userId = typeid('user').toString()  // pre-compute the id
await ctx.store.insert('users', { id: userId, email, createdBy: userId })
```

## Storage

TypeID values are strings. On every dialect:

- `VARCHAR(64)` on mysql / mariadb / mssql (length set explicitly because MySQL's `TEXT` can't be a PK).
- `TEXT` on postgres / sqlite (typed strings of arbitrary length).

The PK index is a B-tree. Lookup is O(log n) on every dialect; the framework's eager-load join compiler relies on indexed PK lookup for child rows.

## Performance

TypeID's body is monotonically increasing in time, so:

- **Index hot spots are predictable**: new inserts cluster at the high end of the PK B-tree. Postgres / mysql leaf-page splits happen at the right edge — no random insertion penalty.
- **Range scans by id approximate range scans by createdAt**: useful for cursor pagination, batch deletes by age, and audit queries.

## Caveats

- **Renaming a table breaks branded type compatibility**. If you rename `users` → `customers`, every `UserId`-typed function signature now wants `CustomerId`. The framework's typecheck surfaces this at the call site, which is the right place — fix the migrations + propagate the rename through code.
- **Prefix changes are silent**. Changing `id({ prefix: 'usr' })` → `id({ prefix: 'user' })` on an existing table makes NEW rows prefixed differently from OLD rows. The framework doesn't migrate existing IDs. Pick the prefix carefully + change it only with a data migration.
- **The 26-char body is canonical lowercase**. Don't uppercase typeids for display — it breaks the spec and Crockford-base32's case-insensitivity gets confusing.

## Where it lives

- `voltro/packages/database/src/idGenerator.ts` — `generateId(scheme, tableName)` wires up `typeid-js`; `deriveTypeIdPrefix(tableName)` does the singularize
- `voltro/packages/runtime/src/schemaRegistry.ts` — branded type registration
- `voltro/packages/runtime/src/storeMiddleware.ts` — auto-injection on insert



---

<!-- source: en/database/ids/ulid.md -->
## ULID — bare, no prefix

_26-char Crockford-base32 ULID without a prefix tag. For public-facing IDs where the table type should not leak from the ID itself._

ULID drops TypeID's prefix and keeps just the 26-character body. Use when the **mere existence** of a row in a specific table is sensitive — leaking the table tag in the ID leaks the row's category.

```typescript
import { id } from '@voltro/database'

export const inviteTokens = table('invite_tokens', {
  id: id({ scheme: 'ulid' }),       // → '01j5xkqyz8x3n4m9pvabcd' (no prefix)
  invitedBy: reference(() => users),
  email: text(),
  expiresAt: timestamp(),
})
```

## Spec

ULID is the [Universally Unique Lexicographically Sortable Identifier](https://github.com/ulid/spec). The framework implements it via the [`ulidx`](https://github.com/perry-mitchell/ulidx) library — a Crockford-base32 encoder/decoder with monotonic guarantees.

- **Length**: 26 characters.
- **Alphabet**: Crockford-base32 (no `I`, `L`, `O`, `U`).
- **Body**: 48-bit Unix millisecond timestamp + 80 random bits — same as TypeID's body component.
- **Case**: lowercase canonical (`01j5xkqyz8x3n4m9pvabcd`). Crockford-base32 is case-insensitive but the framework + ulidx emit lowercase.

The 48-bit timestamp prefix means ULIDs sort by creation time — same property as TypeID, just without the type tag in front.

## When to pick ULID over TypeID

Three cases, in order of how often they apply:

### 1. Existence of the row is sensitive

For invite tokens, share links, magic-link references, password-reset tokens — the URL containing the ID is the secret. If the prefix `invite_` is in the URL, an attacker who sees `https://app/i/invite_01j5xkqyz8x3n4m9pvabcd` knows the row is an invite_tokens entry, not a public document. That information is itself useful for targeted attacks.

```typescript
// TypeID — leaks "this is an invite" in the URL.
export const inviteTokens = table('invite_tokens', {
  id: id({ prefix: 'invite' }),
})
// URL: https://app/accept?token=invite_01j5xkqyz8x3n4m9pvabcd
//                              └──┘ — leaks the category

// ULID — opaque from the URL alone.
export const inviteTokens = table('invite_tokens', {
  id: id({ scheme: 'ulid' }),
})
// URL: https://app/accept?token=01j5xkqyz8x3n4m9pvabcd
```

The framework's schema-registry brand still flows — `InviteTokenId` ≠ `UserId` at compile time even though both are 26-char strings at runtime.

### 2. Interop with a system that expects bare ULIDs

If you're integrating with software that already standardized on bare ULIDs and you want the framework's IDs to be drop-in compatible, ULID gives you that without a translation step.

### 3. You really hate the prefix aesthetic

Subjective, but valid. Some teams prefer the cleaner look of `01j5xkqyz8x3n4m9pvabcd` over `user_01j5xkqyz8x3n4m9pvabcd`. Decide at the schema level — picking ULID for the whole table is the right place; trying to strip prefixes in display code is a mess.

## What you lose vs TypeID

- **Doubleclick selection**. ULIDs are pure alphanumeric so this works fine — same as TypeID.
- **Type-tag at-a-glance in logs**. Greppping for `user_` in `voltro logs` won't catch ULID-typed entities. The compile-time brand is still there but you lose the runtime affordance.
- **Disambiguation in mixed-table dumps**. If you `SELECT id FROM users UNION SELECT id FROM orgs` and grep the result, ULIDs are visually identical across tables. TypeID makes the source self-evident.

## What you DON'T lose

- **Sortable by creation time**: ULID's timestamp prefix is identical to TypeID's body. `ORDER BY id` works the same.
- **Branded at the TypeScript level**: the framework's schema registry brands `InviteTokenId` regardless of the wire format.
- **Cursor pagination via `paginateById`**: works on ULID columns the same as TypeID.

## Storage

`VARCHAR(64)` on mysql / mariadb / mssql, `TEXT` on postgres / sqlite / turso. Same column type as TypeID — the framework doesn't tune the column shape per scheme. The PK B-tree behaves identically (timestamp-leading lexical order).

## Migration between schemes

ULID and TypeID share the same body format. Going from TypeID `user_01j5xkqyz8x3n4m9pvabcd` to ULID `01j5xkqyz8x3n4m9pvabcd` is a string-prefix strip. Going the other way is a string-prefix prepend. The framework doesn't ship a one-shot migrator; if you flip the scheme on an existing table, write a one-off UPDATE.

```sql
-- TypeID → ULID
UPDATE invite_tokens SET id = SUBSTRING(id, 8) WHERE id LIKE 'invite_%';

-- ULID → TypeID
UPDATE invite_tokens SET id = 'invite_' || id WHERE id NOT LIKE 'invite_%';
```

Run inside a transaction. Both directions invalidate any URL or external reference that embedded the old form — coordinate with the consumers.

## Caveats

- **Monotonicity within the same millisecond is not guaranteed**. ULID spec says two IDs generated in the same ms can be sorted in any order. For most uses this is fine; for nano-precision audit logs, prefer Snowflake (which uses a sequence counter within each ms).
- **No type discrimination in error messages**. A handler that logs "couldn't find id `01j5xkqyz8x3n4m9pvabcd`" gives the user no hint which table to check. TypeID's prefix is the better choice for any ID a user might encounter in an error message.
- **Don't try to derive type from the ID at runtime**. The framework's brand exists at compile time only; once a string crosses an API boundary as `unknown`, you can't tell what table it belongs to without looking it up. If runtime discrimination matters, use TypeID.

## Where it lives

- `voltro/packages/database/src/idGenerator.ts` — `generateId(scheme, tableName)` wires up `ulidx` for the `ulid` scheme
- `voltro/packages/runtime/src/schemaRegistry.ts` — ULID-scheme registration (no prefix to resolve)



---

<!-- source: en/database/ids/numeric.md -->
## Numeric — dialect-native auto-increment

_BIGSERIAL / AUTO_INCREMENT / IDENTITY columns. For internal tables that are never URL-exposed and benefit from compact FK joins._

Numeric IDs are the framework's internal-only ID scheme — `1`, `2`, `3`, … generated by the database's native auto-increment idiom. The framework uses them for its own bookkeeping tables (CDC log, audit log, migration ledger); application tables should default to TypeID unless they fit the constraints below.

```typescript
import { id, table, text, timestamp, json } from '@voltro/database'

export const cdcLog = table('_voltro_cdc_log', {
  id:        id({ scheme: 'numeric' }),
  tableName: text(),
  op:        text(),
  oldRow:    json().nullable(),
  newRow:    json().nullable(),
  writtenAt: timestamp().default('now'),
})
```

## When to pick numeric

Three constraints; ALL three must apply.

### 1. The ID is internal — never URL-exposed

Sequential numeric IDs make enumeration attacks trivial. Knowing `/api/users/4221` exists tells an attacker `/api/users/4222` exists too. The framework doesn't add an authorization layer to obscure this — if your auth model accidentally lets a request through, the numeric ID makes the attack surface linear in the row count.

Internal tables — CDC log, audit log, migration ledger, schedule run records — are never exposed via URL or API. They're tools for the framework + operators, not user-visible objects.

### 2. The table is append-mostly + high-volume

Numeric IDs are compact: 8 bytes per column. TypeID is ~30 bytes per column. On a foreign-key column referenced from 100M child rows, that's 2.2 GB of disk + index space savings.

For tables in the tens of millions of rows where FK joins are hot, the compactness matters. For typical app tables (10k–100k rows), the savings are noise; pay the TypeID tax for the URL-safe, brandable upside.

### 3. You won't merge data across databases

Numeric IDs collide across separately-deployed databases. Two staging instances both have `users.id = 1`. Trying to merge their data later requires re-mapping every FK. UUID-family schemes (TypeID, ULID, Snowflake) don't collide.

Internal tables are usually scoped to one process / one deploy / one instance, so this doesn't apply. Application tables often outgrow their original deploy boundary; pick a non-numeric scheme to keep the option open.

## Per-dialect emission

The framework's DDL emitter dispatches numeric ID columns to the dialect-native idiom:

| Dialect   | Column shape                                          |
|-----------|-------------------------------------------------------|
| postgres  | `"id" BIGSERIAL PRIMARY KEY`                          |
| mysql     | `` `id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY ``  |
| mariadb   | `` `id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY ``  |
| mssql     | `[id] BIGINT IDENTITY(1,1) PRIMARY KEY`               |
| sqlite    | `"id" INTEGER PRIMARY KEY AUTOINCREMENT`              |

The framework chose BIGINT (or INTEGER on sqlite, which auto-promotes) so a 64-bit address space avoids the 2^31 INT overflow that's bitten enough deploys to be a meme. SQLite's INTEGER promotes to 64-bit when used as PRIMARY KEY AUTOINCREMENT anyway.

## Insert behaviour

The framework's mutation middleware OMITS the `id` field from the insert when the column's scheme is numeric:

```typescript
// You write:
await ctx.store.insert('_voltro_cdc_log', {
  tableName: 'users',
  op: 'insert',
  oldRow: null,
  newRow: { id: 'user_abc', email: 'a@b.c' },
  writtenAt: new Date(),
})

// The framework emits (on postgres):
INSERT INTO "_voltro_cdc_log" (table_name, op, old_row, new_row, written_at)
VALUES ($1, $2, $3, $4, $5)
RETURNING *

// The returned row carries the server-assigned id:
// { id: 4221, tableName: 'users', op: 'insert', ... }
```

This means:

- You can't pre-compute the ID before insert. The server hands it back.
- Distributed inserts (multiple processes hitting the same table) take an extra round-trip vs client-generated schemes.
- The auto-increment counter is shared per-table. Two parallel inserts get sequentially distinct IDs (DB-enforced); no race window.

## What you lose vs TypeID

- **Pre-computed IDs**: typeid/ulid/snowflake are generated client-side. Numeric forces a server round-trip.
- **Cursor pagination via lexical sort**: numeric works for `paginateById` because integers sort lexically when zero-padded for comparison, but `ORDER BY id` against an int column does the right thing without padding.
- **Brand-by-table at compile time**: branded types still flow (`CdcLogId` ≠ `AuditId`) but at runtime an unsigned `1` is a `1` is a `1`.
- **URL safety against enumeration**: `4221` invites guessing; `cdc_01j5xkqyz8x3n4m9pvabcd` does not.

## What you gain

- **Compact joins**: BIGINT FK is 8 bytes; TypeID/ULID FK is 30+ bytes. On hot join paths with hundreds of millions of rows, the index size matters.
- **Cheap range scans by id**: `WHERE id BETWEEN 1000 AND 2000` against an INT column hits a B-tree leaf range in O(log n + k). String-typed PKs do the same big-O but the constants are bigger.
- **Familiar to DBAs**: every relational DBA knows BIGSERIAL / AUTO_INCREMENT / IDENTITY. The CDC log table looks like every other audit table they've maintained.

## Caveats

- **Auto-increment sequences can be reset**. `TRUNCATE TABLE … RESTART IDENTITY` on postgres resets the counter; mysql's `ALTER TABLE … AUTO_INCREMENT = 1` does the same. If you do this in dev and the table still has historical numeric IDs in FKs elsewhere, you can collide new rows with old references.
- **Cross-DB migrations are painful**. Re-mapping every FK from old IDs to new IDs is an `O(n × m)` operation that locks every related table during the rewrite.
- **Serial overflow exists**. BIGINT's 9.2 × 10^18 ceiling is essentially unreachable for normal workloads but very-high-throughput multi-decade systems have hit it. If you're inserting 1M rows/sec for 100 years, plan for it.

## Where it lives

- `voltro/packages/database/src/migrate.ts` — `numericIdSql(name, dialect)` emits the per-dialect column shape
- `voltro/packages/database/src/idGenerator.ts` — numeric scheme registers no generator (server-side)
- `voltro/packages/runtime/src/storeMiddleware.ts` — skips `id` injection when the registered scheme is numeric; falls through to RETURNING / OUTPUT to capture the server-assigned value



---

<!-- source: en/database/ids/snowflake.md -->
## Snowflake — Twitter 64-bit

_41-bit ms timestamp + 10-bit machine ID + 12-bit sequence. For extreme-throughput distributed inserts. Set SNOWFLAKE_MACHINE_ID (0–1023) per process._

Snowflake is the framework's high-throughput ID scheme — a 64-bit integer composed of a millisecond timestamp, a machine identifier, and an in-millisecond sequence counter. Use when you're inserting at a rate where TypeID's 30-character string is a meaningful storage cost across hundreds of millions of rows.

```typescript
import { id } from '@voltro/database'

export const events = table('events', {
  id:        id({ scheme: 'snowflake' }),    // → 1879382109193580544
  userId:    reference(() => users),
  eventType: text(),
  payload:   json(),
  occurredAt: timestamp(),
})
```

Each process needs a unique `SNOWFLAKE_MACHINE_ID`:

```sh
SNOWFLAKE_MACHINE_ID=42 voltro start
```

## Spec

Twitter's [original Snowflake](https://github.com/twitter-archive/snowflake) layout, slightly adjusted for JavaScript:

```
+--------+------+------+------+
| 1 bit  | 41   | 10   | 12   |
| sign   | time | mach | seq  |
+--------+------+------+------+
```

- **Sign bit**: always 0 (positive integers — JavaScript `Number` can represent up to 2^53 safely, the framework wire-formats as string).
- **Timestamp (41 bits)**: milliseconds since the framework's epoch (`2024-01-01T00:00:00.000Z`). 41 bits ≈ 69.7 years from epoch → 2093-08-14.
- **Machine ID (10 bits)**: 0–1023. Set per process via `SNOWFLAKE_MACHINE_ID` env. Unique across all processes that share a database.
- **Sequence (12 bits)**: 0–4095. Resets every millisecond. Allows up to 4096 IDs per machine per millisecond — 4M IDs/sec/machine.

The framework implements this in ~100 LOC at `voltro/packages/database/src/snowflake.ts`. No external dependency.

## When to pick Snowflake

One constraint: you need MORE than the trade-offs.

- **Throughput**: hundreds of thousands to millions of inserts per second per process. TypeID's `typeid-js` generator does ~1M ops/sec on a modern Node, so most workloads don't need Snowflake's tighter inner loop.
- **Storage**: 8 bytes per ID, vs TypeID's ~30. At 10 billion rows with three FK references per row, that's ~720 GB saved.
- **Distributed insert without coordination**: with `SNOWFLAKE_MACHINE_ID` correctly set per process, no two processes ever produce the same ID. No DB round-trip, no central counter, no UUID collision improbability arguments.

If none of these apply, TypeID is the better default.

## Configuration

```sh
SNOWFLAKE_MACHINE_ID=0     # process 0
SNOWFLAKE_MACHINE_ID=1     # process 1
SNOWFLAKE_MACHINE_ID=2     # process 2
# ...up to 1023
```

The framework reads this at boot. If unset on a process running tables that use the Snowflake scheme, boot fails with a clear message:

```
ERROR: SNOWFLAKE_MACHINE_ID env var must be set to an integer 0–1023.
Reason: a table with id({ scheme: 'snowflake' }) is in the schema +
        no machine id is configured — concurrent processes would
        produce duplicate Snowflakes (silent ID collision).
```

If you only use TypeID + ULID, you don't need to set this even on tables that interact with Snowflake-using tables.

## Storage

Snowflake values are JavaScript `bigint` at the runtime layer; the framework wire-formats them as strings on the wire (avoiding the `Number.MAX_SAFE_INTEGER = 2^53` precision loss that bites JSON-based APIs).

On the database side:

- `BIGINT` on every SQL dialect.
- 8 bytes on disk per column.

The PK index is a B-tree. Inserts cluster at the high end of the tree (timestamp-leading) — same B-tree-friendly pattern as TypeID/ULID, no random insertion penalty.

## What you lose vs TypeID

- **Prefix tag** — `1879382109193580544` carries no table affinity. Brand types still flow at compile time, but logs / error messages can't disambiguate at a glance.
- **URL friendliness in the same sense** — 19-digit numbers are URL-safe (they're just digits) but they look like database internal IDs. The framework's brand keeps them safe; if you're optimizing for "looks intentional," TypeID wins.
- **JavaScript number safety**. The largest possible Snowflake (`(2^63) - 1`) is `9.2 × 10^18`, far exceeding `Number.MAX_SAFE_INTEGER` (`9 × 10^15`). The framework uses BigInt at the type level and wire-formats as string, but any code path that goes through `JSON.parse(string)` → `Number` silently loses precision.
- **70-year epoch ceiling**. The framework's Snowflake epoch is `2024-01-01`. The 41-bit timestamp gives ~69.7 years → 2093-08-14. Systems that need to outlive that timeline need a new epoch.

## What you DON'T lose

- **Sortable by creation time**: timestamp is the high-order bits. `ORDER BY id` works.
- **Branded at the TypeScript level**: same schema-registry brand as TypeID.
- **Cursor pagination via `paginateById`**: BigInt comparison works for cursor windows.

## Caveats

- **Machine ID collisions corrupt the ID space silently**. Two processes with `SNOWFLAKE_MACHINE_ID=42` produce overlapping IDs. The framework can't detect this — it's deployment configuration. Coordinate IDs via your orchestrator's metadata (k8s pod ordinal, Nomad index, ECS task instance).
- **Clock skew matters**. The framework uses `Date.now()` for the timestamp. If a process's clock is set backwards (NTP failover, manual adjustment), it can produce IDs that go backwards in the body for a window. The framework defensively waits if the new timestamp is less than the last one used; this manifests as a tiny pause on insert.
- **Snowflake is 8-byte**. Don't store it as a string in your application code — the framework wire-formats as string but in-process it's BigInt. `JSON.stringify(BigInt(1879382109193580544))` throws — handle the BigInt → string conversion explicitly at API boundaries.
- **Cross-process clock drift produces non-monotonic IDs across processes**. Two writes happening "at the same time" from machines with skewed clocks produce IDs that sort differently than real-world time. Don't rely on cross-process Snowflake order for audit purposes; use a centralized timestamp source if order across processes matters.

## Where it lives

- `voltro/packages/database/src/snowflake.ts` — Twitter-spec implementation, ~100 LOC; exports `generateSnowflake()`
- `voltro/packages/database/src/idGenerator.ts` — `generateId(scheme, tableName)` calls `generateSnowflake()` for the `snowflake` scheme
- `voltro/packages/database/src/columns.ts` — `id({ scheme: 'snowflake' })` registration
- `voltro/packages/runtime/src/storeMiddleware.ts` — boot-time machine-ID check + per-insert auto-injection



---

<!-- source: en/database/ids/custom.md -->
## Custom — bring your own generator

_Escape hatch for IDs that don't fit any of the built-in schemes. Pure, side-effect-free generator function returns a string per insert._

When the four built-in schemes (TypeID, ULID, numeric, Snowflake) don't fit, declare a custom generator. The framework calls it before every insert; the returned string becomes the row's ID.

```typescript
import { id, table } from '@voltro/database'
import { customAlphabet } from 'nanoid'

const nano = customAlphabet('abcdefghjkmnpqrstuvwxyz23456789', 12)

export const shortlinks = table('shortlinks', {
  id: id({
    scheme:   'custom',
    generate: (_tableName) => `sl_${nano()}`,
  }),
  targetUrl: text(),
  createdAt: timestamp().default('now'),
})
```

Examples of valid use cases:

- **NanoID-style human-readable short codes**. URL shorteners, OTPs, room codes.
- **Time-bucketed IDs** like `2026-05-30/abc12345` for date-partitioned tables.
- **IDs sourced from an external system** — a Redis counter, AWS-generated reference, hash of input.
- **Hash-prefixed IDs** like `sha256:abc123…` for content-addressable storage.
- **Voltro Cloud's per-customer numeric IDs** that need to start at a customer-specific offset.

If you're reaching for custom IDs, double-check none of the built-in schemes fit — they handle 99% of cases. Custom is the escape hatch, not the default.

## Generator contract

The `id()` argument is FLAT — `{ scheme: 'custom', generate }`. The
`{ kind: 'custom', generate }` object below is the RESOLVED internal
scheme the framework stores on the column after `table()` walks the
fields; you never write it yourself.

```typescript
// What you pass to id():
id({ scheme: 'custom', generate: (tableName: string) => string })

// Resolved internal scheme (framework-internal, FYI only):
interface CustomScheme {
  readonly kind: 'custom'
  readonly generate: (tableName: string) => string
}
```

The contract:

1. **Synchronous**. The framework calls `generate(tableName)` during the mutation middleware's pre-insert phase. No async work — the mutation pipeline doesn't await Promises here.
2. **Pure**. Same inputs should produce equivalent outputs IF the generator is deterministic; if it's random (typical for nanoID-style), entropy comes from `crypto.randomBytes` or equivalent. Don't make a network call.
3. **Side-effect-free**. Don't write to a counter / cache / log inside `generate`. Side effects make the insert non-idempotent on retry.
4. **Unique per call**. Two calls to `generate` for the same `tableName` MUST produce different IDs (unless your application logic intentionally produces deterministic ones, in which case collisions are your responsibility).
5. **Returns a string**. The framework stores it as a `text()` / `VARCHAR(64)` column; arbitrary length is allowed but keep it reasonable (under 100 chars for URL-safety).

### What if I need async ID generation?

You can't have it inside the auto-injection path. Two alternatives:

```typescript
// Option 1: Pass id: explicitly, generated upstream.
const id = await fetchExternalId()
await ctx.store.insert('shortlinks', { id, targetUrl })

// Option 2: Generate a placeholder client-side, replace via UPDATE
// after the external call resolves. Requires the table to support
// UPDATE of the id column (which is unusual + fragile).
```

Most "async" cases turn out to be solvable with option 1 — fetch the ID, then insert. The framework's auto-injection is for cases where the ID generator is cheap + synchronous.

## Collision handling

`generate` MUST produce unique IDs for the framework's auto-injection contract to hold. If a duplicate slips through and the DB rejects the insert with a PK conflict, the framework surfaces it as a `PrimaryKeyConflictError` typed error — your handler can catch and retry:

```typescript
import { PrimaryKeyConflictError } from '@voltro/database'

try {
  await ctx.store.insert('shortlinks', { targetUrl: input.url })
} catch (e) {
  if (e instanceof PrimaryKeyConflictError) {
    // Generator collision — retry with a fresh ID.
    return retryWithBackoff()
  }
  throw e
}
```

Built-in schemes (TypeID, ULID, Snowflake) collision-rate is astronomically low; you'll never hit `PrimaryKeyConflictError` from them. Custom generators with short alphabets / short bodies (e.g. 4-character codes) WILL collide and need explicit retry.

## TypeScript brand

The schema registry brands every custom-scheme table the same way it brands TypeID tables — `ShortlinkId` ≠ `UserId` at compile time. The brand survives across hooks, query handlers, RPC boundaries via descriptor serialization.

## Storage

`TEXT` on postgres / sqlite / turso, `VARCHAR(64)` on mysql / mariadb / mssql. The framework picks `64` because typical custom-scheme outputs are short; if you need longer (e.g. SHA-256 hex is 64 chars + prefix), set the column type explicitly via `text()` instead of `id()` — but you lose the auto-injection at that point.

The PK index is a B-tree on every dialect. Performance depends on your generator's distribution: clustered timestamp-leading IDs (like TypeID/ULID/Snowflake) are B-tree friendly; random scattered IDs (like UUIDv4 or nanoID) cause leaf-page splits and write amplification at high insert rates.

## Examples

### Nano-ID short codes

```typescript
import { customAlphabet } from 'nanoid'
const nano = customAlphabet('abcdefghjkmnpqrstuvwxyz23456789', 12)

id({
  scheme: 'custom',
  generate: () => `sl_${nano()}`,
})
```

12-char body, 32-char alphabet → 12 × log2(32) = 60 bits of entropy. Plenty for short-link uniqueness up to ~10^9 IDs (~5% collision at that scale). At higher scale, increase the body length.

### Hash-prefixed (content-addressable)

```typescript
import { createHash } from 'node:crypto'

id({
  scheme: 'custom',
  generate: (_tableName) => {
    // This pattern only works when the row's content is known
    // before the insert is built — e.g. a content blob the
    // caller already has in hand. Generator can't access the
    // row, so you'd actually want to pre-compute outside.
    throw new Error('use ctx.store.insert with explicit id for content-addressed')
  },
})
```

In practice, content-addressed IDs are pre-computed by the caller + passed via explicit `id:` rather than auto-injected.

### Time-bucketed

```typescript
import { ulid } from 'ulidx'

id({
  scheme: 'custom',
  generate: () => {
    const now = new Date().toISOString().slice(0, 10)  // YYYY-MM-DD
    return `${now}/${ulid().toLowerCase()}`
  },
})
// → '2026-05-30/01j5xkqyz8x3n4m9pvabcd'
```

Useful when you want PK ordering to reflect the date partition + want to query a single day's rows via `WHERE id BETWEEN '2026-05-30/' AND '2026-05-31/'`. Caveat: doesn't sort cleanly across years (`2026/` < `2027/` is fine, but you can't compare two IDs from different calendars).

## Migration to / from custom

Going from custom to a built-in scheme: same approach as ULID ↔ TypeID — a one-off UPDATE that rewrites IDs across all FK references. Get a lock, run inside a transaction.

Going from a built-in to custom: usually means generating new IDs for every row (since the built-in body isn't compatible with your custom format). At scale this is expensive; consider whether the custom benefit outweighs the migration cost.

## Caveats

- **Generator throughput is the framework's ceiling**. If `generate` takes 5ms, every insert takes 5ms minimum. Profile + optimize before adopting in a hot path.
- **No type-level branding distinction between custom schemes**. The framework brands by table name; two custom-scheme tables with overlapping ID formats are still distinguishable by brand, but the BRAND comes from the table, not the scheme. Don't confuse runtime format with compile-time identity.
- **Custom generators that produce non-comparable strings break `paginateById`**. The cursor-pagination helper assumes IDs sort lexically. Hash-based IDs (UUIDv4, sha256) don't; use a separate sortable column (`createdAt`) as the cursor key.

## Where it lives

- `voltro/packages/database/src/idGenerator.ts` — `resolveIdScheme` resolves `{ scheme: 'custom', generate }` to the internal `{ kind: 'custom', generate }`; `generateId(scheme, tableName)` calls your `generate`
- `voltro/packages/database/src/columns.ts` — `id({ scheme: 'custom', generate })` column constructor
- `voltro/packages/runtime/src/schemaRegistry.ts` — registers the scheme + brand at table-declaration time