Schema - Vertz

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

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<T>()`	`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

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.

Annotations

Annotations control how fields are exposed in the API — they don’t affect the database schema:

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:

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():

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:

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:

// 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:

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:

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:

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:

Identifies the FK column on the source table (e.g., authorId)
Resolves the target table’s primary key
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():

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 for vector similarity search, d.vector(n) defines a vector column and d.index() supports HNSW and IVFFlat index types with tuning parameters:

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

d.index('embedding', {
  type: 'ivfflat',
  opclass: 'vector_l2_ops',
  lists: 100,
});

The generated SQL includes the USING, operator class, and WITH clauses:

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

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:

// Typed but no runtime validation
d.jsonb<{ tags: string[]; priority: number }>();

// Typed with runtime validation
d.jsonb<Settings>(settingsSchema);

JSONB across dialects

d.jsonb<T>() 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.

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:

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

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<T>()). TypeScript infers the leaf type from the selector’s return, so no explicit generic is needed on path<T>(). 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:

// jsonContains (@>) — subset containment. Operand is DeepPartial<T>.
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.

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 uses workspaces as the tenant root, matching Linear’s domain model:

// 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:

// 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():

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.

Documentation Index

​Tables

​Column types

​Column modifiers

​Constraints

​Annotations

​How annotations affect types

​Composite primary keys

​Type behavior

​Validation rules

​Entity CRUD limitation

​Models

​Relations

​One-to-many

​Many-to-one

​Many-to-many

​Foreign keys

​Indexes

​Index types

​Vector indexes (pgvector)

​Index options

​IVFFlat example

​Dialect validation

​Enums

​JSONB columns

​JSONB across dialects

​JSONB filter operators are dialect-conditional

​Typed path filters with path()

​Whole-payload JSONB operators

​Multi-tenancy

​Any table can be the tenant root

​Direct vs indirect scoping

​Shared tables

Tables

Column types

Column modifiers

Constraints

Annotations

How annotations affect types

Composite primary keys

Type behavior

Validation rules

Entity CRUD limitation

Models

Relations

One-to-many

Many-to-one

Many-to-many

Foreign keys

Indexes

Index types

Vector indexes (pgvector)

Index options

IVFFlat example

Dialect validation

Enums

JSONB columns

JSONB across dialects

JSONB filter operators are dialect-conditional

Typed path filters with `path()`

Whole-payload JSONB operators

Multi-tenancy

Any table can be the tenant root

Direct vs indirect scoping

Shared tables