> ## Documentation Index > Fetch the complete documentation index at: https://docs.vertz.dev/llms.txt > Use this file to discover all available pages before exploring further. # Schema > Tables, columns, types, relations, and annotations with the d builder The `d` builder is the API for defining your database schema. Every table, column type, constraint, and annotation is declared in TypeScript — no separate schema files, no decorators. ## Tables ```ts theme={null} import { d } from '@vertz/db'; const users = d.table('users', { id: d.uuid().primary({ generate: 'cuid' }), email: d.email().unique(), name: d.text(), bio: d.text().nullable(), role: d.enum('user_role', ['admin', 'user']).default('user'), createdAt: d.timestamp().default('now').readOnly(), updatedAt: d.timestamp().autoUpdate(), }); ``` The first argument is the table name in the database. The second is an object mapping column names to column definitions. ## Column types | Builder | SQL type | TypeScript type | | ---------------------- | ------------------ | --------------- | | `d.text()` | `TEXT` | `string` | | `d.varchar(n)` | `VARCHAR(n)` | `string` | | `d.email()` | `TEXT` | `string` | | `d.uuid()` | `UUID` | `string` | | `d.integer()` | `INTEGER` | `number` | | `d.bigint()` | `BIGINT` | `bigint` | | `d.serial()` | `SERIAL` | `number` | | `d.real()` | `REAL` | `number` | | `d.doublePrecision()` | `DOUBLE PRECISION` | `number` | | `d.decimal(p, s)` | `NUMERIC(p,s)` | `string` | | `d.boolean()` | `BOOLEAN` | `boolean` | | `d.timestamp()` | `TIMESTAMPTZ` | `Date` | | `d.date()` | `DATE` | `string` | | `d.time()` | `TIME` | `string` | | `d.jsonb()` | `JSONB` | `T` | | `d.textArray()` | `TEXT[]` | `string[]` | | `d.integerArray()` | `INTEGER[]` | `number[]` | | `d.vector(n)` | `vector(n)` | `number[]` | | `d.enum(name, values)` | `ENUM` | literal union | ## Column modifiers ### Constraints ```ts theme={null} d.uuid().primary(); // PRIMARY KEY (no auto-generation) d.uuid().primary({ generate: 'cuid' }); // Primary key with CUID2 generation d.uuid().primary({ generate: 'uuid' }); // Primary key with UUID v7 d.uuid().primary({ generate: 'nanoid' }); // Primary key with Nano ID d.email().unique(); // UNIQUE constraint d.text().nullable(); // Allows NULL (default: NOT NULL) d.text().default('untitled'); // DEFAULT value d.timestamp().default('now'); // DEFAULT NOW() d.text().check('length(name) > 0'); // SQL CHECK constraint ``` Foreign key constraints are derived automatically from relations (`d.ref.one()`). You don't declare them on columns — define the relation on the model and the FK constraint is generated for you. See [Relations](#relations). ### Annotations Annotations control how fields are exposed in the API — they don't affect the database schema: ```ts theme={null} d.text().hidden(); // Never sent to clients (e.g., password hashes) d.text().readOnly(); // Included in responses, excluded from create/update inputs d.timestamp().autoUpdate(); // Read-only + auto-updated on writes d.text().is('sensitive'); // Custom annotation for selective queries ``` ### How annotations affect types Given this table: ```ts theme={null} const users = d.table('users', { id: d.uuid().primary({ generate: 'cuid' }), email: d.email(), passwordHash: d.text().hidden(), createdAt: d.timestamp().default('now').readOnly(), }); ``` The framework derives different types for different contexts: | Type | Includes | Excludes | | ---------------------------------- | -------------------------- | --------------------------------------------------------------- | | **Response** (`$response`) | `id`, `email`, `createdAt` | `passwordHash` (hidden) | | **Create input** (`$create_input`) | `email` | `id` (primary), `passwordHash` (hidden), `createdAt` (readOnly) | | **Update input** (`$update_input`) | `email` (optional) | Same as create | You never define these types manually — they're inferred from the column annotations. ## Composite primary keys For tables with multi-column primary keys (e.g., join tables), use the `primaryKey` table option instead of column-level `.primary()`: ```ts theme={null} const tenantMembers = d.table( 'tenant_members', { tenantId: d.uuid(), userId: d.uuid(), role: d.text().default('member'), }, { primaryKey: ['tenantId', 'userId'] }, ); ``` ### Type behavior Composite PK columns affect the derived types: | Type | Composite PK columns | | --------------------------- | --------------------------------------------------------- | | `$insert` | **Required** (unless the column has `.default()`) | | `$update` / `$update_input` | **Excluded** — PKs are immutable after creation | | `$create_input` | **Required** — provided by the caller, not auto-generated | | `$infer` | Included (read-side) | ### Validation rules * Cannot mix column-level `.primary()` and table-level `primaryKey` on the same table * `primaryKey` array must contain at least one column * All column names must exist in the columns object * Invalid column names are caught at compile time via TypeScript ### Entity CRUD limitation Entity CRUD (`entity()`) does not support composite primary keys — it requires a single-column PK for route parameters like `/api/tasks/:id`. If you need to use a table with a composite PK as an entity, add a surrogate single-column PK and use a unique index for the composite key: ```ts theme={null} const tenantMembers = d.table( 'tenant_members', { id: d.uuid().primary({ generate: 'cuid' }), // surrogate PK for entity CRUD tenantId: d.uuid(), userId: d.uuid(), role: d.text().default('member'), }, { indexes: [ d.index(['tenantId', 'userId'], { unique: true }), // composite uniqueness ], }, ); ``` Tables that genuinely need composite PKs and don't participate in entity CRUD (e.g., join tables) can use `primaryKey` directly and be queried via the raw SQL API. ## Models A model combines a table with its relations: ```ts theme={null} // Without relations const usersModel = d.model(usersTable); // With relations const postsModel = d.model(postsTable, { author: d.ref.one(() => usersTable, 'authorId'), comments: d.ref.many(() => commentsTable, 'postId'), }); ``` Models are what you pass to `createDb()` and `entity()`. ## Relations ### One-to-many The parent has many children. The foreign key lives on the child table: ```ts theme={null} const usersModel = d.model(usersTable, { posts: d.ref.many(() => postsTable, 'authorId'), }); // "users has many posts via posts.authorId" ``` ### Many-to-one The child belongs to a parent. The foreign key lives on the current table: ```ts theme={null} const postsModel = d.model(postsTable, { author: d.ref.one(() => usersTable, 'authorId'), }); // "posts belongs to users via posts.authorId" ``` ### Many-to-many Two tables connected through a join table: ```ts theme={null} const studentsModel = d.model(studentsTable, { courses: d.ref.many(() => coursesTable).through(() => enrollmentsTable, 'studentId', 'courseId'), }); ``` ### Foreign keys Foreign key constraints are derived from `d.ref.one()` relations — there's no separate `.references()` on columns. When you define a relation, the framework: 1. Identifies the FK column on the source table (e.g., `authorId`) 2. Resolves the target table's primary key 3. Generates the `FOREIGN KEY` constraint in the migration This means relations are the single source of truth for both query-time eager loading and database-level referential integrity. ## Indexes Define indexes in the third argument to `d.table()`: ```ts theme={null} const tasks = d.table( 'tasks', { id: d.uuid().primary(), title: d.text(), status: d.text(), createdAt: d.timestamp(), }, { indexes: [ d.index('title'), // basic index d.index(['status', 'createdAt']), // compound index d.index('email', { unique: true }), // unique index d.index('title', { type: 'gin' }), // GIN index (full-text) d.index('status', { where: "status = 'active'" }), // partial index ], }, ); ``` ### Index types | Type | Use case | PostgreSQL | SQLite | | --------- | ------------------------------------ | ---------- | ------------- | | `btree` | Default — equality and range queries | Yes | N/A (default) | | `hash` | Equality-only lookups | Yes | No | | `gin` | Full-text search, JSONB containment | Yes | No | | `gist` | Geospatial, range types | Yes | No | | `brin` | Large sequential datasets | Yes | No | | `hnsw` | Vector similarity search (pgvector) | Yes | No | | `ivfflat` | Vector similarity search (pgvector) | Yes | No | ### Vector indexes (pgvector) If you're using [pgvector](https://github.com/pgvector/pgvector) for vector similarity search, `d.vector(n)` defines a vector column and `d.index()` supports HNSW and IVFFlat index types with tuning parameters: ```ts theme={null} const documents = d.table( 'documents', { id: d.uuid().primary(), content: d.text(), embedding: d.vector(1536), // vector(1536) in Postgres, number[] in TypeScript }, { indexes: [ d.index('embedding', { type: 'hnsw', opclass: 'vector_cosine_ops', m: 16, efConstruction: 64, }), ], }, ); ``` #### Index options | Option | Type | Applies to | Description | | ---------------- | -------- | ---------- | ------------------------------------------------------------------------ | | `opclass` | `string` | Both | Distance function: `vector_cosine_ops`, `vector_l2_ops`, `vector_ip_ops` | | `m` | `number` | HNSW | Max connections per node (default: 16) | | `efConstruction` | `number` | HNSW | Build-time search breadth (default: 64) | | `lists` | `number` | IVFFlat | Number of inverted lists (default: 100) | #### IVFFlat example ```ts theme={null} d.index('embedding', { type: 'ivfflat', opclass: 'vector_l2_ops', lists: 100, }); ``` The generated SQL includes the `USING`, operator class, and `WITH` clauses: ```sql theme={null} -- HNSW CREATE INDEX "idx_documents_embedding" ON "documents" USING hnsw ("embedding" vector_cosine_ops) WITH (m = 16, ef_construction = 64); -- IVFFlat CREATE INDEX "idx_documents_embedding" ON "documents" USING ivfflat ("embedding" vector_l2_ops) WITH (lists = 100); ``` pgvector requires the `vector` extension to be installed in your Postgres database. Run `CREATE EXTENSION IF NOT EXISTS vector;` before running migrations that use vector columns. `d.vector(n)` validates that dimensions are an integer between 1 and 16000 (pgvector's maximum). Vector columns are PostgreSQL-only — using them with SQLite emits a warning at migration time. ### Dialect validation If you use a Postgres-only index type (like `gin`) with SQLite, the framework warns at definition time — not at migration time with a cryptic SQL error. This aligns with the compile-time-over-runtime principle. ## Enums Enums are defined with a name (for the database type) and an array of values: ```ts theme={null} const status = d.enum('task_status', ['todo', 'in_progress', 'done']); ``` The TypeScript type is inferred as the literal union `'todo' | 'in_progress' | 'done'`. ## JSONB columns Store structured data with optional type safety: ```ts theme={null} // Typed but no runtime validation d.jsonb<{ tags: string[]; priority: number }>(); // Typed with runtime validation d.jsonb(settingsSchema); ``` ### JSONB across dialects `d.jsonb()` round-trips through both Postgres and SQLite (including Cloudflare D1) without any `JSON.parse` at the call site: * **Postgres:** native `JSONB` type. `postgres.js` parses values automatically on read. * **SQLite / D1:** stored as `TEXT`. Vertz parses JSON on read and stringifies plain objects / arrays on write. Everything non-JSON (`Date`, typed arrays, `Buffer`, `Map`, `Set`, `URL`, `RegExp`, class instances) passes through to the driver unchanged — no silent `JSON.stringify(new Map())` → `{}` corruption. If a validator is supplied, it runs on **both sides** of the round-trip: * **Reads:** the parsed value flows through `validator.parse`. A failed parse or a rejected validator surfaces via the existing error-as-value Result API. * **Writes (`create` / `update` / `upsert`):** the caller's payload runs through `validator.parse` before the SQL is built. Invalid payloads surface as `{ ok: false, error: { code: 'JSONB_VALIDATION_ERROR' } }` without reaching the driver, and no row is written. The validator's return value (not the caller's input) is what gets persisted, so Zod `.default()` / `.transform()` on the schema land in the DB. ```ts theme={null} const result = await db.install.create({ data: { meta: { wrong: true } } }); if (!result.ok && result.error.code === 'JSONB_VALIDATION_ERROR') { // result.error.table, result.error.column, result.error.value // `value` is the raw caller input — pre-validator — so avoid attaching // validators that carry secrets if you don't want them in error logs. } // On reads: const listed = await db.install.list({}); if (!listed.ok) { if (listed.error.code === 'JSONB_PARSE_ERROR') { // listed.error.table, listed.error.column, listed.error.columnType } if (listed.error.code === 'JSONB_VALIDATION_ERROR') { // listed.error.table, listed.error.column, listed.error.value } } ``` On SQLite specifically, `INSERT ... RETURNING` rows also pass through the read-side validator before the write Result resolves. If your validator is non-idempotent (transforms input to a shape the validator itself would reject), you'll see `JSONB_VALIDATION_ERROR` on the write even though the INSERT succeeded at the driver. Keep validators idempotent — `parse(parse(x))` should equal `parse(x)`. ### JSONB filter operators are dialect-conditional Path-based filters (`where: { 'meta->field': ... }`) are **Postgres-only**: ```ts theme={null} // Postgres — compiles and runs await pgDb.install.list({ where: { 'meta->displayName': { eq: 'Acme' } }, }); // SQLite — TypeScript rejects the shape at compile time: await sqliteDb.install.list({ where: { // Error: 'meta->displayName' does not exist in type // 'JsonbPathFilter_Error_Requires_Dialect_Postgres_On_SQLite_Use_list_And_Filter_In_JS'. 'meta->displayName': { eq: 'Acme' }, }, }); ``` On SQLite, fetch with `list()` and filter in application code, or switch dialects. Inline the `where` object in your query call for the best TypeScript diagnostic. Building `where` indirectly into a variable widens the dialect type and bypasses the compile-time gate — the runtime throw still catches the problem, but the error happens later. ### Typed path filters with `path()` String-keyed path filters (`'meta->field'`) are an escape hatch — they work, but lose the payload's leaf type. For static paths, prefer `path()`: the selector preserves `T` through the leaf and conditionally exposes the operators that make sense for that leaf type. ```ts theme={null} import { path } from '@vertz/db'; interface InstallMeta { displayName: string; settings: { theme: 'light' | 'dark'; count: number }; tags: readonly string[]; } // Leaf type flows: await pgDb.install.list({ where: { meta: path((m: InstallMeta) => m.settings.theme).eq('dark') }, }); // TypeScript rejects an invalid literal at the leaf: await pgDb.install.list({ where: { meta: path((m: InstallMeta) => m.settings.theme).eq('foggy') }, // ^^^^^^^ // Error: 'foggy' is not assignable to 'light' | 'dark'. }); // Operator availability matches the leaf type — string operators on strings: await pgDb.install.list({ where: { meta: path((m: InstallMeta) => m.displayName).contains('Acme') }, }); // Numeric comparisons on numbers: await pgDb.install.list({ where: { meta: path((m: InstallMeta) => m.settings.count).gt(5) }, }); // Numeric array indexing — Postgres emits integer index unquoted (->0 not ->'0'): await pgDb.install.list({ where: { meta: path((m: InstallMeta) => m.tags[0]).eq('urgent') }, }); ``` Pass the JSONB column's payload type as the selector parameter annotation (the same type you declared at `d.jsonb()`). TypeScript infers the leaf type from the selector's return, so no explicit generic is needed on `path()`. `path()` filters are Postgres-only — they resolve to `JsonbOperator_Error_Requires_Dialect_Postgres_On_SQLite_Fetch_And_Filter_In_JS` on SQLite, with the same recovery-sentence-in-the-type-name mechanism as string-keyed path filters. Reach for the string-keyed form only when the path is computed at runtime. ### Whole-payload JSONB operators Three operators filter against the payload as a whole, without a path: ```ts theme={null} // jsonContains (@>) — subset containment. Operand is DeepPartial. await pgDb.install.list({ where: { meta: { jsonContains: { settings: { theme: 'dark' } } } }, }); // jsonContainedBy (<@) — reverse containment. await pgDb.install.list({ where: { meta: { jsonContainedBy: { displayName: 'Acme', settings: { theme: 'dark', count: 1 } } }, }, }); // hasKey (?) — top-level key existence. Operand is keyof T & string. await pgDb.install.list({ where: { meta: { hasKey: 'displayName' } }, }); ``` `jsonContains` operands are deep-partials of `T` (recursion capped at 5 levels). `hasKey` checks **top-level** keys only — for nested key presence, use `path((m: T) => m.nested.key).isNull(false)`. All three are Postgres-only; on SQLite they resolve to the same `JsonbOperator_Error_…` brand. ## Multi-tenancy Declare the tenant root by calling `.tenant()` on the root table definition. The framework automatically derives all tenant scoping from the relation graph — no per-model configuration needed. ```ts theme={null} const orgsTable = d .table('organizations', { id: d.uuid().primary(), name: d.text(), }) .tenant(); const usersTable = d.table('users', { id: d.uuid().primary(), orgId: d.uuid(), email: d.email(), }); const usersModel = d.model(usersTable, { org: d.ref.one(() => orgsTable, 'orgId'), }); ``` Only one table per application can be marked as `.tenant()`. The framework scans all `ref.one` relations to discover which models are scoped to the tenant root. ### Any table can be the tenant root The tenant root is not limited to a table called "tenants". Any table can serve as the root — `organizations`, `workspaces`, `teams`, etc. Just call `.tenant()` on it. For example, the [Linear clone](/examples/task-manager) uses `workspaces` as the tenant root, matching Linear's domain model: ```ts theme={null} // Workspace is the tenant root (Linear's organizational unit) const workspacesTable = d .table('workspaces', { id: d.text().primary(), name: d.text(), }) .tenant(); const workspacesModel = d.model(workspacesTable); // Users are directly scoped to a workspace via ref.one const usersModel = d.model(usersTable, { workspace: d.ref.one(() => workspacesTable, 'workspaceId'), }); // Projects are directly scoped to a workspace via ref.one const projectsModel = d.model(projectsTable, { workspace: d.ref.one(() => workspacesTable, 'workspaceId'), creator: d.ref.one(() => usersTable, 'createdBy'), }); ``` The tenant FK column name is automatically resolved from the `ref.one` relation targeting the `.tenant()` root table. You can name it anything — `orgId`, `workspaceId`, `tenantId`, etc. ### Direct vs indirect scoping Models with a `ref.one` relation pointing to the `.tenant()` root table are **directly scoped** — they are automatically filtered by the current tenant context at query time. Models without a direct relation to the root but reachable via `ref.one` chains from a directly-scoped model are **indirectly scoped**. The framework follows relations transitively using BFS to find the shortest path: ```ts theme={null} // Directly scoped — ref.one to the .tenant() root const projectsModel = d.model(projectsTable, { workspace: d.ref.one(() => workspacesTable, 'workspaceId'), }); // Indirectly scoped — project → workspace chain provides tenant context const issuesModel = d.model(issuesTable, { project: d.ref.one(() => projectsTable, 'projectId'), }); // Indirectly scoped — issue → project → workspace chain const commentsModel = d.model(commentsTable, { issue: d.ref.one(() => issuesTable, 'issueId'), }); ``` When multiple `ref.one` paths exist to the tenant root, the framework picks the shortest one. ### Shared tables Tables that should be accessible across all tenants (system-wide config, feature flags, etc.) can opt out of tenant scoping with `.shared()`: ```ts theme={null} const featureFlags = d .table('feature_flags', { id: d.uuid().primary(), name: d.text().unique(), enabled: d.boolean().default(false), }) .shared(); const flagsModel = d.model(featureFlags); ``` Shared tables are never filtered by tenant context, regardless of their relations. A table cannot be both `.tenant()` and `.shared()` — calling one on a table already marked with the other throws an error.