Database Schema

There is one way to write a feature: feature() — one file, one export, the table and its security contract in a single call. See feature() for the full reference.

Two lower-level forms exist for specific situations, not as alternatives to choose between — both compile to exactly the same runtime, and feature() itself is sugar over the first:

• q.table() + defineTable() — the two-export form feature() expands into. Reach for it only when another file needs to import the table identifier directly (e.g. an action file referencing the table before the default export).
• Drizzle sqliteTable() / pgTable() + defineTable() — interop for bringing an existing Drizzle schema forward, or for dialect-specific column options.

If you're starting fresh — or you're an agent defining a user's features — use feature() for every table.

feature() — the one-call form (default)

// quickback/features/jobs/jobs.ts
import { feature, q } from '@quickback/compiler';

export default feature('jobs', {
  columns: {
    id:             q.id(),
    title:          q.text().required(),
    department:     q.text().required(),
    status:         q.text().default('draft').required(),  // "draft" | "open" | "closed"
    salaryMin:      q.int().optional(),
    salaryMax:      q.int().optional(),
    organizationId: q.scope('organization'),
    ...q.audit(),
    ...q.softDelete(),
  },
  // firewall block omitted — q.scope('organization') triggers auto-derivation
  // of [{ field: 'organizationId', equals: 'ctx.activeOrgId' }, { field: 'deletedAt', isNull: true }].
  guards: {
    createable: ["title", "department", "status", "salaryMin", "salaryMax"],
    updatable:  ["title", "department", "status"],
  },
  read: {
    access: { roles: ["owner", "hiring-manager", "recruiter", "interviewer"] },
  },
  create: { access: { roles: ["owner", "hiring-manager"] } },
  update: { access: { roles: ["owner", "hiring-manager"] } },
  delete: { access: { roles: ["owner", "hiring-manager"] } },
});

Type inference still works — typeof import('./jobs').default.$infer gives you the row type. Action files don't import the table file directly: they import both defineAction and the table from the generated per-feature helper (../.quickback/define-action), which re-exports the table typed with the generated schema. See Actions.

q.table() + defineTable() — the expanded form

This is what feature() compiles into. Write it by hand only when another file needs the table identifier as a named export.

// quickback/features/jobs/jobs.ts
import { q, defineTable } from '@quickback/compiler';

export const jobs = q.table('jobs', {
  id:             q.id(),
  title:          q.text().required(),
  department:     q.text().required(),
  status:         q.text().default('draft').required(),
  salaryMin:      q.int().optional(),
  salaryMax:      q.int().optional(),
  organizationId: q.scope('organization'),
  ...q.audit(),
  ...q.softDelete(),
});

export default defineTable(jobs, {
  guards: { /* ... */ },
  read:    { /* ... */ },
  create:  { /* ... */ },
  update:  { /* ... */ },
  delete:  { /* ... */ },
});

export type Job = typeof jobs.$infer;

Produces byte-identical output to the feature() form above — the compiler expands feature() to exactly this shape internally. Use whichever reads better for the file you're writing.

Code that imports jobs — actions, lib/ files, service handlers — gets the generated Drizzle table (pgTable or sqliteTable, per your database), because that is what the build hands it. Pass it straight to Drizzle: db.select({ id: jobs.id }).from(jobs).where(eq(jobs.status, 'open')); typeof jobs.$inferSelect is the row type. quickback check types it the same way once the project has compiled.

A few mechanics to know:

• Column names default to snake_case of the JS key — organizationId ↔ organization_id, salaryMin ↔ salary_min. Pass a positional argument to override: q.text('custom_name').
• q.id() emits text('id').primaryKey() with a provider-driven app-layer default.
• .required() / .optional() control NOT NULL. Chain before or after .default(x).
• References: q.text().required().references(() => other.id, { onDelete: 'cascade' }), or .references('self') for a column pointing at another row of the same table (see Self-references). An FK declares a pointer, not ownership — when the child rows are a composition of one root (junctions, phones/emails, line items), also declare owns on the root to generate the atomic aggregate write path. other must be a feature table in the same database.

Match a referenced row's parent

An ordinary FK checks that its target exists and is visible. It does not infer that two rows belong to the same project, event, or other parent. Declare that rule on the FK when both rows must share one:

stageId: q.text().optional().references(() => projectStages.id, {
  match: { source: 'projectId', target: 'projectId' },
}),

Here source names a column on this table and target names one on the referenced table. If stageId is set, both values must be non-null and equal. If stageId is null, the match is not required. The compiler rejects unknown columns or an unresolved target table. This is opt-in: some visible records may legitimately link across projects.

The compiler emits database triggers for inserts and updates of the source row, and for changes to the target's parent column. Use the Quickback API for client writes; the triggers backstop generated CRUD, batch writes, changesets, and actions at the database boundary. Updating projectId while leaving stageId unchanged still checks the resulting pair. A mismatch returns 400 DB_RELATIONSHIP_MISMATCH from generated write routes. Apply the generated trigger migration before using the new rule; it validates future writes, so check existing rows for mismatches before rollout. The ordinary target firewall check remains in place for generated CRUD writes.

userId:    q.text().required(),  // ctx.userId — no .references()
channelId: q.text().required().references(() => channels.id, { onDelete: 'cascade' }),

• Indexes and constraints: column-level .index() / .unique() / .check(expr), or table-level indexes / unique / check as a third arg to q.table(name, cols, { indexes, unique, check }). With feature(), pass indexes, unique and check as top-level keys alongside columns — the sugar forwards them into the same q.table opts. In the two-export form they go on q.table; on defineTable(...) they are a compile error, because they describe the table, not the resource. See Constraints.
• Race-safe write kinds (on defineTable): ledger: { grants, reserve } makes the table the debits side of a balance served as db.ledger.<name> (Ledgers); idempotency: { rowToken, clientId? } makes POST / and POST /batch replay a create whose row token is already stored (Row-token idempotency).

Column Types

The Drizzle column is the Postgres form; on SQLite/D1 the compiler lowers it. The next section has the per-dialect storage for the exact-numeric, date, and array types.

In request bodies a column that is not .required() is .optional().nullable() — omit it to leave it untouched, send null to clear it. "" is not a blank: it is validated like any other value. .required() columns never accept null.

q.text() accepts either a SQL-name string or an options object — q.text({ sqlName: 'org_id', maxLength: 80 }). The options are the field-level rules:

Every rule is enforced on the generated create, update, and batch routes, described in OpenAPI, and read by the CMS form from the schema registry. A misspelled option (maxlength) is a compile error, not a silent no-op. q.url() takes sqlName and maxLength only.

q.secret() is a write-only credential column — an API client secret, a webhook URL with an embedded token. It takes sqlName, minLength, maxLength, and pattern; it is encrypted at rest like .encrypted(), redacted on every read path for every role (owner included), and read server-side only through ctx.unmask(row, column). The table needs a q.scope('organization') column. See Secret columns.

q.url() is q.text() plus an absolute-http(s)-URL check at the validation edge, so javascript: / data: / ftp: URIs and free-form strings are rejected before they ever land in storage or get rendered in the CMS.

q.id() emits a plain text primary key. Actual ID assignment at insert time is driven by the provider's generateId setting (default "uuid"). This keeps q's output decoupled from any specific ID library.

Typed JSON columns

Pass q.json() a Zod schema to give the column's value a shape. A table file may import { z } from zod for this:

import { feature, q } from "@quickback/compiler";
import { z } from "zod";

export default feature("journalEntries", {
  columns: {
    id:             q.id(),
    organizationId: q.scope("organization"),
    autocatResult:  q.json(z.object({ method: z.string(), confidence: z.number().min(0).max(1) })).optional(),
    applications:   q.json(z.array(z.object({ invoiceId: z.string(), amount: z.string() }))).optional(),
    raw:            q.json().optional(),
    ...q.audit(),
    ...q.softDelete(),
  },
  read:   { access: { roles: ["member"] } },
  create: { access: { roles: ["admin"] } },
});

The schema validates create and update bodies (and changeset ops), and its JSON Schema types the column in openapi.json, api-types.gen.ts, quickback.gen.ts and the MCP tools. A bare q.json() stays unknown there. Storage doesn't change: the column is jsonb on Postgres and JSON text on SQLite/D1.

The schema must be written inline from z and literals, because the compiler copies it into the generated routes and converts it to JSON Schema itself. These are compile errors:

• a reference to another binding: q.json(Shape), or z.object({ a: Shape })
• a callback such as .refine(...) or .transform(...)
• using z anywhere else in the table file
• importing anything from zod other than { z }

Per-key write authority (keys)

createable and updatable decide whether a client may write a column. One JSON column can mix keys a project member may change with keys only a platform operator may change (entitlements, plan features, feature flags). The second argument of q.json(schema, opts) can declare a tier for each key:

import { feature, q } from "@quickback/compiler";
import { z } from "zod";

export default feature("projects", {
  columns: {
    id:             q.id(),
    name:           q.text().required(),
    settings:       q.json(
      z.object({
        theme: z.string().optional(),
        beta:  z.boolean().optional(),
      }),
      {
        keys: {
          theme: { tier: "organizer" },   // ordinary: writable wherever `settings` is
          beta:  { tier: "platform" },    // generic writes refuse it
        },
      },
    ).optional(),
    organizationId: q.scope("organization"),
    ...q.audit(),
    ...q.softDelete(),
  },
  guards: { createable: ["name", "settings"], updatable: ["name", "settings"] },
  read:   { access: { roles: ["member"] } },
  create: { access: { roles: ["member"] } },
  update: { access: { roles: ["member"] } },
});

keys needs an inline z.object({ ... }) value schema, and every key it lists must be a property of that schema. A key the schema doesn't declare, an unknown option, or a non-literal options object is a compile error.

Platform keys. Generic CRUD writes (POST, PATCH, PUT, the batch routes, and changeset ops) refuse a body that carries a platform key with 422 JSON_KEY_RESTRICTED, and details.fields names it as <column>.<key>. A PATCH that replaces the column keeps the stored platform keys: PATCH { settings: { theme: "dark" } } leaves beta as it was. Platform keys are readOnly in the column's JSON Schema (OpenAPI, api-types.gen.ts, MCP).

Only an action can change a platform key, and only one of these actions:

• an action whose access names a userRole lane, when the caller's user.role matched it (access: { userRole: ["operator"] });
• an action that names the key in writes:.

The scoped db enforces this rule, and so do handles from unsafeDb.scopeTo(...). Any other action that writes the column gets 422 JSON_KEY_RESTRICTED if it changes or drops a platform key. Read-modify-write that carries the key over unchanged, such as .set({ settings: { ...record.settings, theme } }), is allowed. An insert may not set a platform key without the grant. An upsert's onConflictDoUpdate({ set }) that touches the column is refused unless the action holds every grant, because the compiler cannot compare it against the stored row. The raw unsafeDb handle bypasses this check, as it bypasses the firewall.

Derived keys. derivedFrom: { table, column, in, match? } turns a key into a projection of another table. The key is true when a row of table that is linked to this row has column in the in set, and false otherwise. Soft-deleted rows don't count.

settings: q.json(
  z.object({ premiumExport: z.boolean().optional() }),
  {
    keys: {
      premiumExport: {
        tier: "platform",
        // true while a `plans` row for this project is active or trialing
        derivedFrom: { table: "plans", column: "status", in: ["active", "trialing"] },
      },
    },
  },
),

• table is the other table's SQL name or export name, and column is a column of it.
• match: { source, target } links the rows: source is a column on this table, target a column on table. Omit it when table has exactly one .references() to this table. The compiler then infers the link (plans.projectId → projects.id). Zero or several references with no match is a compile error.
• A derived key is never stored and never writable. Every write path refuses it, actions with a grant included. CRUD reads (GET, list, views) and CRUD write responses fill it in from the source table. Realtime frames carry the stored value, which never holds a derived key. An action's requires: also reads the source table, so the purchase or subscription row is the only source of truth.

Under generateId: "cuid", "nanoid", or "short", the q.id() default calls an npm helper (createId from @paralleldrive/cuid2, or nanoid / customAlphabet from nanoid). Don't import it in the table file, which may not import either package. The compiler adds the import to the generated schema.ts and the package to package.json, so the default mints the same id shape as the CRUD route.

Decimals, dates, 64-bit integers, and arrays

Each of these has native Postgres DDL. SQLite has no equivalent type, so on D1 the compiler lowers it to a storage class that keeps the value exact, and the wire format is the same on both dialects:

import { feature, q } from "@quickback/compiler";

export default feature("invoices", {
  columns: {
    id:             q.id(),
    number:         q.text({ maxLength: 40 }).required(),
    total:          q.decimal({ precision: 12, scale: 2 }).required().filterable("eq"),
    taxRate:        q.decimal({ precision: 6, scale: 5 }).optional(),
    issuedOn:       q.date().required().filterable("range"),
    amountCents:    q.bigint().required(),
    lineCount:      q.smallint().default(1).required(),
    labels:         q.text().array().default([]).required(),
    channels:       q.enum(["web", "phone", "store"]).array().optional(),
    organizationId: q.scope("organization"),
    ...q.audit(),
  },
  read:   { access: { roles: ["owner", "admin", "member"] } },
  create: { access: { roles: ["owner", "admin"] } },
  update: { access: { roles: ["owner", "admin"] } },
  delete: { access: { roles: ["owner", "admin"] }, mode: "hard" },
});

• Decimals are strings, so no value passes through a float. Send "19.9" and the API stores and returns "19.90": the value is padded to scale digits and leading zeros are dropped, which is how Postgres prints numeric(p,s). D1 stores that same canonical text, so both dialects return identical strings. precision (1–1000) and scale (0–precision) must be number literals, or the compile fails.
• Dates have no time zone. A q.date() value is never converted, so "2026-03-31" is the same day for every caller. Use q.timestamp() for an instant.
• Timestamps are ISO strings on the wire, on both dialects. On D1, q.timestamp() is text holding the ISO string. On Postgres it is timestamptz. The column accepts a Date or an ISO string on write, and its TypeScript type is Date | string. Reads come back as a Date, which serializes as the same ISO Z string. Because Drizzle uses one type for reads and writes, a read is also typed Date | string even though it is always a Date at runtime — wrap it as new Date(row.dueAt) before calling Date methods such as .getTime() (a breaking type change for Postgres projects written against the earlier Date-only type). So .set({ archivedAt: now }) works and typechecks on either database, on every handle: the scoped db, unsafeDb, and a raw createDb(). Postgres feature schemas import this column's timestamp from the generated src/lib/pg-timestamp.ts instead of drizzle-orm/pg-core. Its SQL type and migrations are unchanged. Filter values for a Postgres timestamp column must parse as a timestamp, or the request returns 400.
• q.bigint() values are JSON numbers, and are capped at ±(2^53 − 1). Past that, a JSON number can't hold an exact integer, and D1 returns every INTEGER as a JS number. The cap still takes a cents column past $90 trillion, where q.int() (Postgres integer) stops at about $21.4 million. Values written to a Postgres bigint outside the API beyond 2^53 will read back rounded.
• Defaults are checked at compile time against the same rules, because nothing re-validates a DDL default. q.smallint().default(40000) fails the compile. A decimal default must already be in canonical form, so write .default("0.00") for scale 2, not "0".
• .array() works on q.text(), q.enum(), q.int(), and q.uuid(), and must be written once. .default([]) gives Postgres '{}' and D1 '[]'. An array column can't declare .references(): Postgres has no foreign keys over array elements, so use a join table.

What you can query depends on the type. The compiler refuses the rest at compile time, and the list endpoint refuses them at runtime too:

.searchable() and .filterable("like") are text-only. Masks that format strings (email, phone, partial, and the rest) are refused on q.bigint(), q.smallint(), and array columns; use redact or custom there. Decimal and date values are strings, so every mask type works on them. .defaultNow() is only accepted on q.timestamp().

.defaultNow() stamps the insert time whenever an insert omits the column. On Postgres it is a real DEFAULT now(). On SQLite / D1 the column carries a constant DEFAULT '1970-01-01T00:00:00.000Z' (D1 refuses a non-constant default on ALTER TABLE ADD COLUMN) plus a $defaultFn, and the generated src/db/index.ts makes every Drizzle insert use the $defaultFn — so only raw SQL that omits the column gets the epoch. Compilers from v0.33.2 until this fix wrote the epoch for Drizzle inserts too; see Troubleshooting → timestamps read 1970-01-01 to find and repair affected rows.

q.scope() — tenant scope columns

q.scope(kind) is the canonical way to declare a column that holds a tenant-scope value (org / owner / team). It compiles to text(...).notNull() like a regular required text column, plus metadata that the compiler reads to:

1. Auto-derive the firewall predicate — q.scope('organization') produces [{ field: 'organizationId', equals: 'ctx.activeOrgId' }] so you can omit the explicit firewall: block. (If deletedAt is present — and it is whenever the resource uses soft delete, which is the default — the isNull(deletedAt) predicate is appended automatically.)
2. Reject the column from client input — scope fields land in GUARDS_CONFIG.systemManaged alongside the audit fields, so POST { organizationId: 'org_other' } returns 400 even if guards aren't otherwise configured.
3. Auto-populate on create / upsert — the value is taken from the request context, not the request body.

columns: {
  organizationId: q.scope('organization'),  // → ctx.activeOrgId
  ownerId:        q.scope('owner'),         // → ctx.userId
  teamId:         q.scope('team'),          // → ctx.activeTeamId
}

Override the context source for non-default sources:

ownerId: q.scope('owner', { source: 'ctx.userId' }),

q.scope() is preferred over q.text().required() for tenant columns. Auto-detection on column names (organizationId, userId, etc.) still works as a fallback for projects bringing forward Drizzle schemas, but the explicit q.scope() form locks in the semantics in the source.

q.stamp() — proven-principal identity columns

q.stamp({ fromScope, claim }) server-stamps a column from a proven scope claim — the caller's own identity within a relationship they were cryptographically proven to hold (e.g. an event attendee's own guest id). It is the identity sibling of q.scope:

columns: {
  eventId:  q.scope('event'),                                    // tenant firewall column
  authorId: q.stamp({ fromScope: 'event', claim: 'subjectId' }), // proven identity — un-spoofable
}

Runtime precedence — the org/admin hierarchy takes priority. The stamp fires only when the caller is admitted as a scope-only principal (a scope-token holder, ctx.scopePrincipal === true): the column is force-stamped from the proven claim and any client-sent value is ignored. A caller admitted via the org role hierarchy (member / admin / owner) writes the column through the org path — a normal client-settable value, bounded by the org firewall (this is legitimate org-admin capability over their own org's rows). So a table whose create admits both org roles and a scope role needs no split: each identity writes the column the way its principal allows. The invariant that always holds is that a scope-only principal can never set a scope-stamped column to anything but its proven token.

Fail-closed. If a scope-only caller reaches the write without the required claim (ctx.scope.<kind>.<claim> absent) the write is refused with 403 before the insert — never a NULL identity row — for nullable stamp columns too. A q.stamp column may not also be a firewall arm, live on an exception: true table, or be sourced from a set-valued sub-key; each is a compile error.

optional: true makes the DDL column nullable (an org caller may then omit it); it does not relax the scope-principal 403.

Set-on-insert, immutable thereafter. A q.stamp column is a create-time identity — it is never re-derived on update. On every update-shaped write (PATCH, PUT, batch update, upsert, and a changeset root/child patch) the column is systemManaged: a client value for it is rejected (400), regardless of your guards.updatable list. Re-keying a proven identity is not an operation. (The force-stamp and the org-writable-on-create allowance apply to the create path only.)

A non-nullable stamp on the org path must be supplied by the caller. The force-stamp fires for scope-only principals; an org-admitted caller writes the column as normal client input. So a NOT NULL q.stamp column (the default) that an org caller omits lands on the database NOT NULL constraint (a deterministic field-named 400), not a NULL row. Add the column to guards.createable so org callers can supply it, or mark it optional: true if an org-created row may legitimately have no identity.

The scope-only lane needs a scope-reachable write. The force-stamp only runs once the scope-only principal actually reaches the insert. A table with a hard organization firewall arm (an organizationId sourced from ctx.activeOrgId) emits an ORG_REQUIRED gate that denies a sessionless scope principal (who has no active org) before the stamp — so on such tables the stamp serves the org-admitted caller, and the scope-only principal writes these rows as a changeset child that inherits organizationId from a firewall-verified parent. For a plain create/batch by a scope-only principal, firewall the table on the scope arm only (e.g. firewall: { any: [{ field: 'eventId', equals: 'ctx.scope.event' }] }, no org column) so no ORG_REQUIRED gate is emitted.

Idempotency (at-least-once). The stamp rides inside the write, so it inherits the endpoint's replay semantics: a keyed replay (a repeated Idempotency-Key whose handler is skipped) neither re-writes nor re-stamps; a keyless retry re-writes and therefore re-stamps from the caller's then-current proven claim.

q.storageKey() — R2 object keys the compiler owns

q.storageKey(...) declares a column that holds an R2 object key. The compiler, not the client, writes the key:

r2Key: q.storageKey({
  bucket: "MEDIA",                                   // a bindings.r2Buckets binding
  key: "media/{organizationId}/{eventId}/{uuid}.{ext}",
  mime: ["image/*", "application/pdf"],
  maxBytes: 10_000_000,
}),

• Never client-writable. No generated write body (create, update, batch, PUT, changeset) carries the column. Listing it in guards.createable, updatable, immutable or protected is a compile error (STORAGE_KEY_GUARDED).
• Written only by db.allocateUpload(table, { ...rowValues, ext, mime, size }) inside an action. It renders the key from the template, with {column} values from the row, a fresh {uuid}, and {ext} from the call. It then returns { row, putUrl, headers }: a PUT signed for exactly the declared mime and at most maxBytes.
• Prefix-checked. The row's prefix is the template up to {uuid} with the row's own values (media/org_abc/evt_1/). Reads (db.signDownload), deletes, and tenant purge refuse a stored key outside it. Each firewall tenant column (organizationId, ownerId, a scope column) must be a placeholder before {uuid}. Each placeholder fills a whole path segment.
• Deleted with the row. A hard delete through the generated routes or the action db deletes the object too.

The column takes .required(), .optional(), .unique() and .index(). Other modifiers fail the compile. The template rules, the error codes, and the full upload and download flow are in Using R2 → Signed upload.

Naming Convention

By default, q picks the SQL column name from the JS key using the target database's conventional casing. Both cloudflare-d1 and neon default to snake_case (organizationId → organization_id).

You can override for any project by setting namingConvention on the database provider config:

// quickback/quickback.config.ts
export default {
  providers: {
    database: {
      name: 'cloudflare-d1',
      config: {
        namingConvention: 'camelCase', // keep JS-style names in the SQL
      },
    },
  },
};

For one-off overrides on a specific column, pass an explicit name as the positional argument:

organizationId: q.scope('organization', { sqlName: 'org_id' }),   // SQL name: 'org_id'

Constraints — CHECK and unique

Declare CHECK constraints on a column or on the feature() config, and unique constraints as unique entries. They compile to Drizzle's check(), uniqueIndex() and unique(), so drizzle-kit writes the DDL into the migration on both dialects.

// quickback/features/line_items/line_items.ts
import { feature, q } from '@quickback/compiler';

export default feature('line_items', {
  columns: {
    id:             q.id(),
    sku:            q.text().required(),
    quantity:       q.int().required().check('> 0'),
    unitPriceCents: q.int().required().check('>= 0'),
    productId:      q.text().optional(),
    serviceId:      q.text().optional(),
    organizationId: q.scope('organization'),
    ...q.audit(),
    ...q.softDelete(),
  },
  check: [
    // exactly one of productId / serviceId
    { name: 'line_items_one_target', sql: '(product_id IS NULL) <> (service_id IS NULL)' },
  ],
  unique: [
    { columns: ['organizationId', 'sku'], whereNotDeleted: true },
  ],
  read:   { access: { roles: ['member'] } },
  create: { access: { roles: ['member'] } },
  update: { access: { roles: ['member'] } },
  delete: { access: { roles: ['admin'] } },
});

How the SQL is written. Column-level .check(expr) is appended to the quoted physical column name, as "<column>" <expr>. Write only what follows the column: '> 0', "IN ('draft', 'open')", 'BETWEEN 1 AND 5'. The table-level sql and a string unique where are complete expressions over the physical (snake_case) column names: product_id, not productId.

The object form of where takes the JS column names and renders the physical ones for you, AND-joined. Each value is a literal: null (IS NULL), { notNull: true } (IS NOT NULL), a string, a finite number, or a boolean (= TRUE / = FALSE, valid on Postgres and on the SQLite D1 ships). A key that is not a column of the table, or any other value, is a compile error.

Nothing is translated per dialect. The SQL must be valid for your database. num_nonnulls(a, b, c) = 1 works on Postgres only. On D1, write (a IS NOT NULL) + (b IS NOT NULL) + (c IS NOT NULL) = 1. An invalid expression fails when the migration is applied, not at compile time.

Names. A column check is named <table>_<column>_check. A table-level check needs an explicit name that matches /^[A-Za-z_][A-Za-z0-9_]*$/. The name can be at most 63 characters, because Postgres truncates longer identifiers. It must also differ from every other check, unique and index name on the table. A column takes one .check(): combine conditions with AND, or use a table-level check.

Compile errors instead of silent drops. The compiler reads these options from source, and it fails the compile on any of the following:

• a value that isn't a literal
• an unknown key
• where together with whereNotDeleted
• an object where naming an unknown column, or a non-literal value
• nullsNotDistinct on a partial unique
• whereNotDeleted on a table without ...q.softDelete()
• nullsNotDistinct in a SQLite/D1 project, since SQLite always treats NULLs as distinct

Enforced by the database. CHECK is not part of the request validator. A violating write reaches the database and comes back as 400 DB_CHECK_VIOLATION naming the constraint. A unique collision comes back as 409 DB_UNIQUE_VIOLATION. See Errors. Mirror a check at the edge (e.g. maxLength) when you want a field-level validation message instead.

The Postgres-only options, as a fragment:

// Postgres (Neon / PlanetScale Postgres) only
check:  [{ name: 'contacts_one_channel', sql: 'num_nonnulls(email, phone, address) = 1' }],
unique: [{ columns: ['organizationId', 'externalRef'], nullsNotDistinct: true }],

Unique constraints and soft delete

A soft-deleted row is still a row. A plain unique on (organizationId, sku) keeps counting it, so after DELETE /line_items/:id the same SKU can't be created again. The insert returns 409 DB_UNIQUE_VIOLATION against a row the caller can no longer see. Column-level .unique() has the same problem.

On a soft-delete table, make the unique partial. whereNotDeleted: true (or the object form where: { deletedAt: null }) adds WHERE "deleted_at" IS NULL, using the table's actual soft-delete column under your naming convention. Uniqueness then holds among live rows only, and a deleted value can be reused. Restoring a soft-deleted row whose value a live row has since taken fails with a unique violation. That is the correct outcome, because the two rows would otherwise collide.

ON CONFLICT. Generated CRUD, PUT and batch upsert don't use ON CONFLICT: they look the row up by id and then insert or update, so partial uniques need nothing from them. An action that upserts against a partial unique with Drizzle's onConflictDoUpdate / onConflictDoNothing must repeat the predicate as targetWhere. Postgres and SQLite only match a partial index to a conflict target that carries its WHERE:

await db.insert(lineItems).values(row).onConflictDoUpdate({
  target: [lineItems.organizationId, lineItems.sku],
  targetWhere: sql`"deleted_at" IS NULL`,
  set: { quantity: row.quantity },
});

Drizzle interop

If you have an existing Drizzle schema you're bringing forward — or need a dialect-specific column option q doesn't expose — use pgTable / sqliteTable / mysqlTable directly:

For PostgreSQL targets, the compiler places feature tables in the app database schema. It initializes the generated schema binding before any table declarations, including when several authored table files are combined and retain imports between tables. Keep authoring with pgTable; the compiler handles schema qualification.

// quickback/features/jobs/jobs.ts
import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';
import { defineTable } from '@quickback/compiler';

export const jobs = sqliteTable('jobs', {
  id: text('id').primaryKey(),
  organizationId: text('organization_id').notNull(),
  title: text('title').notNull(),
  department: text('department').notNull(),
  status: text('status').notNull(),
  salaryMin: integer('salary_min'),
  salaryMax: integer('salary_max'),
  // ── quickback:audit (compiler-managed — edits are validated, not merged) ──
  createdAt: text('created_at').notNull().default('1970-01-01T00:00:00.000Z').$defaultFn(() => new Date().toISOString()),
  modifiedAt: text('modified_at').notNull().default('1970-01-01T00:00:00.000Z').$defaultFn(() => new Date().toISOString()).$onUpdate(() => new Date().toISOString()),
  createdBy: text('created_by'),
  modifiedBy: text('modified_by'),
  deletedAt: text('deleted_at'),
  deletedBy: text('deleted_by'),
});

export default defineTable(jobs, {
  firewall: [{ field: 'organizationId', equals: 'ctx.activeOrgId' }],
  guards: {
    createable: ["title", "department", "status", "salaryMin", "salaryMax"],
    updatable: ["title", "department", "status"],
  },
  create: { /* ... */ },
  update: { /* ... */ },
  delete: { /* ... */ },
});

export type Job = typeof jobs.$inferSelect;

When using Drizzle (rather than q.scope()), declare the firewall as a predicate array. Auto-detection still works on column names (organizationId, ownerId, teamId) so you can omit firewall: entirely if exactly one isolation column is present.

When Drizzle interop is the right call

Default to feature(). Drop to Drizzle only when:

• You already have Drizzle schemas to bring forward
• You need a Drizzle-specific column option not yet in q (e.g., decimal, varchar with length, dialect-specific types)

Features in the same project may mix freely — Quickback dispatches per feature — so one interop table doesn't force the rest of the project off feature().

One dialect rule: interop files without a defineTable(...) default export (internal child tables) are not dialect-translated — author them in the target database's dialect. On Postgres providers (neon, planetscale-postgres), a sqliteTable interop source without defineTable is a compile-time error; write it with pgTable from drizzle-orm/pg-core instead. Sources with defineTable(...) keep compiling across dialects automatically. On Postgres, managed audit timestamps a sqliteTable source declares in the SQLite shape (createdAt / modifiedAt / deletedAt as text) are emitted as timestamptz(3), the same as every other Postgres table; API responses still carry the same ISO-8601 string.

Drizzle Column Types

Drizzle supports all standard SQL column types:

Drizzle Column Modifiers

// Required field
name: text('name').notNull()

// Default value
isActive: boolean('is_active').default(true)

// Primary key
id: text('id').primaryKey()

// Unique constraint
email: text('email').unique()

// Default to current timestamp
createdAt: timestamp('created_at').defaultNow()

File Organization

Organize your tables by feature. Each feature directory contains table files:

quickback/
├── quickback.config.ts
└── features/
    ├── jobs/
    │   ├── jobs.ts              # Main table + config
    │   ├── applications.ts      # Related table + config
    │   └── actions/             # Custom actions — one file per action
    │       └── publish.ts
    ├── candidates/
    │   ├── candidates.ts        # Table + config
    │   └── candidate-notes.ts   # Related table
    └── organizations/
        └── organizations.ts

Key points:

• Tables with export default defineTable(...) get resource routes generated
• Tables without a default export are internal (no routes, used for joins/relations)
• Route paths are derived from filenames: applications.ts → /api/v1/applications

Table files are declarative

Everything above export default defineTable(...) becomes part of the generated schema.ts, which migration and auth-schema tooling load during a compile. So the compiler only accepts declarative code there, and fails the compile naming the file and line otherwise:

• Imports from drizzle-orm / drizzle-orm/*, @quickback/compiler, or another table file (./other-table, ../<feature>/<table>). import type may come from anywhere — it is erased.
• Top-level statements: imports, type / interface declarations, const / let bindings, and export { … } lists.
• Expressions built from literals, your imports and bindings, and Date, Math, JSON, Number, String, Boolean, crypto. Callbacks such as .references(() => …), $defaultFn(() => …) and the index callback (t) => [...] follow the same rule; their bodies may hold consts and a return.

Not allowed: require, process, globalThis, dynamic import(), function declarations, loops, assignments, and constructor / prototype access. Logic belongs in an action; the defineTable(...) config itself is not affected.

defineTable vs defineResource

• defineTable — The standard function for defining tables with generated API routes
• defineResource — Deprecated and scheduled for removal. Still compiles with a warning; do not write new code with it — migrate to feature() (or defineTable).

// Preferred:
export default defineTable(jobs, { /* config */ });

1 Resource = 1 Security Boundary

Each defineTable() call defines a complete, self-contained security boundary. The security config you write — firewall, access, guards, and masking — is compiled into a single resource file that wraps all generated routes for that table.

This is a deliberate design choice. Mixing two resources with different security rules in one configuration would create ambiguity about which firewall, access, or masking rules apply to which table. By keeping it 1:1, there's never any question.

A supporting table without defineTable() is useful when it's accessed internally — by action handlers, joins, or background jobs — but should never be directly exposed as its own API endpoint.

Internal Tables (No Routes)

For junction tables or internal data structures that shouldn't have API routes, simply omit the defineTable export. Add a // @quickback-internal marker so the compiler skips the "no defineTable found" warning:

// quickback/features/jobs/interview-scores.ts

// @quickback-internal — child table, no routes intentionally.
import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';

export const interviewScores = sqliteTable('interview_scores', {
  applicationId: text('application_id').notNull(),
  interviewerId: text('interviewer_id').notNull(),
  score: integer('score'),
  organizationId: text('organization_id').notNull(),  // Scope junction tables for cascade soft-deletes
  // ── quickback:audit (compiler-managed — edits are validated, not merged) ──
  createdAt: text("created_at").notNull().default('1970-01-01T00:00:00.000Z').$defaultFn(() => new Date().toISOString()),
  modifiedAt: text("modified_at").notNull().default('1970-01-01T00:00:00.000Z').$defaultFn(() => new Date().toISOString()).$onUpdate(() => new Date().toISOString()),
  createdBy: text("created_by"),
  modifiedBy: text("modified_by"),
  deletedAt: text("deleted_at"),
  deletedBy: text("deleted_by"),
});

// No default export = no routes generated

These internal tables still participate in the database schema and migrations — they just don't get API routes or security configuration. Without the @quickback-internal marker the compiler emits a warning per table, since the missing defineTable is more often a bug than a deliberate choice.

Internal child/junction tables get no exemption from the managed-column rule. compiler.features.visibleAuditColumns validates every table file in a feature directory, whether or not it has a defineTable() default export — and a table with no resource config is treated as soft-deleting, so it must declare the four audit fields (createdAt/createdBy/modifiedAt/modifiedBy) and the soft-delete pair (deletedAt/deletedBy). ...q.audit() is not available inside sqliteTable(...) — the spread is expanded by the q lowering, which never runs on raw Drizzle source — so write the columns out literally, as above. organizationId is still yours to declare, and you should, so cascade soft deletes and scoped queries work. Route-backed tables — anything with a feature() / defineTable() default export — declare the same columns through the spreads; see Declaring them visibly.

Audit Fields

Quickback maintains four audit columns on every table (including child/junction tables without defineTable):

Audit fields are a separate concept from soft-delete (deletedAt/deletedBy) — they're always present, regardless of delete mode. See Soft-Delete Fields below.

Opt out of audit injection entirely with compiler.features.auditFields: false in quickback.config.ts. (You almost never want to.)

Writing through a raw handle — auditStamp(ctx)

The scoped db injected into actions stamps these four columns for you. A handle built directly from createDb() does not — that's the documented escape hatch for system writes, sessionless principals, and broad reads the scoped wrapper over-narrows.

Those writes still land in audit-managed tables. A Drizzle insert that omits createdAt / modifiedAt gets the insert time (Postgres DEFAULT now(); on SQLite the column's $defaultFn), but on SQLite nothing fills createdBy / modifiedBy — they stay NULL. (On Postgres the database's audit triggers fill them — see How auto-injection works.) On SQLite the managed timestamps also carry a DDL default of the unix epoch (so ALTER TABLE ADD COLUMN works on populated tables), so raw SQL that omits them does not fail on NOT NULL — it silently persists 1970-01-01T00:00:00.000Z, which reads as a real timestamp everywhere downstream.

Spread the stamp instead of hand-writing four columns at each call site:

import { auditStamp } from '../lib/audit-wrapper';

await db.insert(invoices).values({ ...auditStamp(ctx), amount, organizationId });
await db.update(invoices).set({ ...auditStamp(ctx, 'update'), status });

'insert' (the default) returns all four columns; 'update' returns only modifiedAt / modifiedBy, so an update never rewrites createdAt. Actor resolution is identical to the wrapper's, including the delegated-principal and scope:<kind>:<subjectId> lanes, and it fails closed the same way — a raw handle is not a licence to write an anonymous audit row.

Declaring them visibly — ...q.audit() / ...q.softDelete()

Historically these columns were invisible: injected into the generated schema only, so the table object in your editor didn't have them. They are now declared visibly (required by default — see below) so $infer, your editor, and the emitted schema all agree:

export default feature("candidates", {
  columns: {
    id:             q.id(),
    name:           q.text({ maxLength: 200 }).required(),
    organizationId: q.scope("organization"),
    ...q.audit(),        // createdAt / modifiedAt / createdBy / modifiedBy
    ...q.softDelete(),   // deletedAt / deletedBy — matches crud.delete.mode
  },
})

The spreads are declarative, not definitional: the compiler expands them to the exact canonical column lines it would otherwise inject, so emitted output is identical either way. Raw Drizzle tables declare the canonical column lines literally instead (under a ── quickback:audit (compiler-managed — edits are validated, not merged) ── marker). Run quickback migrate visible-columns to add either form across an existing project in one shot.

Two rules keep this safe:

• The compiler validates managed columns against canon — on by default. A createdAt missing its default chain, a .notNull() createdBy, a defaulted deletedAt, a renamed SQL name — each breaks a security pillar (audit integrity, ownership predicates, the Firewall's soft-delete ridge, name-keyed guards/RLS), so divergence is a compile error, and every feature table must declare the audit quartet visibly (soft-delete pair iff the resource soft-deletes). Never silently normalized. compiler.features.visibleAuditColumns: false opts out — invisible injection returns and divergence downgrades to a warning.
• Unknown spreads are a compile error. The columns object is compiled from source, so ...someSharedCols can't be resolved — it fails the build rather than silently dropping columns.

The scoped db an action receives types every managed key as ?: never on .insert().values(), so supplying one is a compile error, not a silently discarded value (below).

How auto-injection works (two layers)

"Auto-injection" covers two distinct steps. Conflating them is the most common source of confusion:

1. Schema injection (compile-time) — the compiler adds the four columns to any Drizzle table that doesn't already declare them. Idempotent: a column you authored yourself is left alone. With visibleAuditColumns on (the default) this only ever fires for internal child tables — a route-backed table that hasn't declared them fails the compile instead.
2. Runtime injection (per-write) — a Proxy around the request-context db (createAuditDb, generated to src/lib/audit-wrapper.ts) intercepts .insert(...).values(...) and .update(...).set(...) and writes the audit columns from ctx.userId and the actual write moment.

The runtime layer hard-stamps every audit field on every write, so the audit log can never be forged by a crafted request body or by a client echoing a row back into a PATCH.

On Postgres the RLS migration adds a database-side backstop: BEFORE INSERT / BEFORE UPDATE triggers on every feature table (public.set_audit_fields_on_insert() / _on_update()). They set created_at / modified_at to the database's now() truncated to the millisecond (date_trunc('milliseconds', now())) and apply the same actor rule as the wrapper — the actor column always names whoever did the write:

• a verified caller (auth.user_id()) always wins, so no request can write someone else's id;
• with no verified caller — a scope: principal, a delegated principal, the service lane, a system: actor — the actor the Worker already stamped is kept;
• only a write that carries no actor at all (raw SQL) gets system:<database role>.

created_at / created_by never change on update.

Postgres stores managed timestamps at millisecond precision. createdAt / modifiedAt / deletedAt are timestamp(3) with time zone, the same precision as D1's ISO text, a JS Date, and the wire format (.sssZ). A keyset cursor carries its boundary value through a Date, so a microsecond column would never compare equal to the cursor: ascending pages repeated rows and descending pages skipped rows that shared a millisecond (#602). The triggers truncate rather than round, so a stamp never lands after the transaction's now(). The column DEFAULT now() (reached only by raw SQL on a table without the triggers) is rounded to the millisecond by Postgres. Timestamp columns you declare yourself keep the precision you give them.

Recompiling a Postgres project last compiled by 0.69.0 or earlier adds a migration that changes each managed column's type:

ALTER TABLE "app"."notes" ALTER COLUMN "created_at" SET DATA TYPE timestamp (3) with time zone USING "created_at"::timestamp (3) with time zone;

Existing values are rounded to the nearest millisecond (…08.624500 becomes …08.625). The type change rewrites the table under an ACCESS EXCLUSIVE lock, so apply it in a quiet window on large tables. The same compile re-emits the trigger functions (CREATE OR REPLACE) in a new RLS migration.

Because the value would be discarded anyway, the generated db handle refuses it at the type level: on the scoped db, .insert().values() types createdAt / modifiedAt / createdBy / modifiedBy / deletedAt / deletedBy as ?: never, and .update().set() types the audit quartet plus deletedBy the same way. Supplying one is a compile error — not a silent discard.

// In a custom action — this does not compile
await db.insert(records).values({
  status: 'active',
  createdBy: 'admin',           // ✗ Type 'string' is not assignable to type 'never'
  modifiedAt: '1999-01-01',     // ✗ same
});

Drop the keys and let the wrapper write them from ctx.userId and the actual write moment.

One deliberate exception: .set({ deletedAt: ... }) is allowed on update — that assignment is the soft-delete signal, and the stored value is normalized to the write moment and the verified caller. The raw unsafeDb handle an unsafe: action receives is unwrapped Drizzle and carries none of these type fences.

What the caller is responsible for: the domain fields. (id auto-fills via the column's $defaultFn — q.id() and injected ids both carry one, and the typed db treats id as optional on insert for every id strategy except serial and false.)

What the caller does not need to set: id, the scope column, or any of the audit fields, ever.

Actors: who shows up in createdBy / modifiedBy

The wrapper writes whatever string is in ctx.userId. For user-driven requests this is the authenticated user's id — set by auth middleware before the handler runs. For non-user-driven entry points (inbound webhooks, cron, queue workers), you mint a synthetic actor id at the boundary so the audit log preserves provenance.

Inherit before you mint. If a real user's request triggers cascades, hooks, or enqueues background jobs, every downstream write should carry that user's id — not a synthetic one. The cascade is part of the user's action. Only mint when there is genuinely no upstream user.

When you do mint, use a single namespace and a stable convention:

The system: prefix marks "not a real user" — readable by anyone joining the audit columns to users, and cheap to filter on. Don't use tenant:admin or quickback:admin: those imply a human role nobody is actually playing. The tenant context is already on the row (in organizationId); the actor id is purely about source.

Failure mode: missing ctx.userId

If a write reaches the wrapper without a ctx.userId, the wrapper throws:

audit-wrapper: insert reached without ctx.userId.
If this is an intentional system-side write, use the unwrapped db directly.

Two paths to fix it, depending on intent:

• Mint a synthetic actor (preferred — preserves attribution) — populate ctx.userId with one of the conventions above before calling into your handler.
• Bypass the wrapper entirely — use the unwrapped createDb instead of the request-context db. Use this only for migrations, schema repair, or backfill scripts where you genuinely don't want any audit attribution. createdAt/modifiedAt still get the insert time from their column default (a raw SQL insert that omits them on SQLite gets the epoch — set them yourself); createdBy/modifiedBy end up NULL.

Disabling Audit Fields

To disable automatic audit fields for your project:

// quickback.config.ts
export default defineConfig({
  // ...
  compiler: {
    features: {
      auditFields: false,  // Disable for entire project
    }
  }
});

This turns off both layers — schema injection and the runtime wrapper.

Soft-Delete Fields

deletedAt and deletedBy are not audit fields. They're a separate, per-table mechanism for soft delete:

A resource soft-deletes unless both delete paths — single and batch — resolve to 'hard'. Soft is the default, so most tables carry the pair. When both resolve to 'hard' the table skips them entirely, which also drops the isNull(deletedAt) predicate from the firewall WHERE clause.

The batch path inherits the single path's mode. batch picks up delete.mode whenever it doesn't set its own — whether batch is absent, false, or an object with no mode key. So delete: { mode: 'hard' } on its own is already a hard-delete resource; you don't need to repeat the mode on batch.

// soft delete (default) — table gets deletedAt + deletedBy
delete: {}

// hard delete — batch inherits mode: 'hard'
delete: { mode: 'hard' }

// hard delete — the same thing, stated explicitly
delete: { mode: 'hard', batch: { mode: 'hard' } }

// SOFT delete — the one case that flips back: batch names a different mode
delete: { mode: 'hard', batch: { mode: 'soft' } }

Because the columns' presence must agree with the resolved mode, a hard-delete resource that declares ...q.softDelete() (or the deletedAt / deletedBy lines) is a compile error, and a soft-delete resource that omits them is too.

Caller contract

In custom-action handlers, passing deletedAt in .set({}) is the signal that this update is a soft delete. Any value works — the wrapper detects the field and overwrites both deletedAt and deletedBy with now and ctx.userId:

await db.update(records)
  .set({ deletedAt: new Date() })  // signal — value is overwritten
  .where(eq(records.id, id));

You don't set deletedBy yourself; the wrapper pairs it with the deletedAt signal.

Retention and permanent erasure

Soft delete is the recoverable default, not a fixed data-retention policy. Quickback leaves the retention window in application code because legal holds, customer contracts, and record classes rarely share one universal timer.

The platform provides the pieces for an explicit policy:

• Immediate permanent deletion — configure delete: { mode: 'hard' } (the batch path inherits the mode). Generated hard-delete routes record the deletion in the security audit pipeline before removing the row.
• Time-based purging — declare a defineSchedule job that hard-deletes rows whose deletedAt is older than your policy cutoff. The scheduler emits the Worker cron trigger and runs the same code on D1 or Neon.
• Aggregate deletion — use database foreign keys with onDelete: 'cascade' for same-database ownership, and orchestrate cross-database records, R2 objects, and external systems from an explicit offboarding action or queued job. For a whole organization, tenancy.purge derives the table list from the firewall and deletes the rows' storage-key objects with them.
• Encrypted data — deleting an organization's wrapped DEK makes its envelope-encrypted fields unreadable, provided no backup or export still holds that row (crypto-shredding is a manual, service-role delete). Sealed deployments should include their vault grants, recovery records, and key versions in the same reviewed offboarding workflow.

That makes retention ordinary, version-controlled business logic rather than a global platform timer that might erase the wrong class of record. It also keeps the compliance claim honest: the schedule and erasure workflow implement your policy; enabling soft delete alone does not assert GDPR, HIPAA, or SOC 2 compliance.

Protected system fields

These fields never take their value from client input, even with guards: false — but the mechanism differs by field class:

• createdAt, createdBy (audit — always present)
• modifiedAt, modifiedBy (audit — always present)
• deletedAt, deletedBy (soft-delete — present when delete mode allows soft)

The six audit/soft-delete columns are overwritten by the runtime wrapper — a request body that carries one is server-stamped before the SQL is built.

id (when generateId is not false) is rejected at the edge instead: it is omitted from the .strict() Zod body schema, so a body carrying it is a 400 VALIDATION_ERROR, and the guards createable allow-list backstops that. Nothing overwrites id at the insert — the Zod/guards boundary is the protection. Under allowClientIds: true a strategy-shaped id is accepted on create only.

The default index set

Drizzle owns every index in a Quickback project. The compiler writes a default set into your schema source before drizzle-kit reads it, so the indexes are generated, diffed and dropped by the same journal as your tables — there is no second emitter putting indexes in the security migration.

The pass runs after audit-field injection (so deletedAt and createdAt are present) and is idempotent.

Entries are named <table>_<col>_idx (or <table>_<col1>_<col2>_idx for a composite) and emitted into the table's Drizzle extras callback. The index import is added to your schema's drizzle-orm/<dialect>-core import line if it isn't already there.

Coverage — what suppresses a default index

A default index is skipped when a non-partial index already has its columns as a leading prefix. So the tenant composite (organizationId, deletedAt) suppresses a bare (organizationId) index, and an index you author yourself suppresses the default on the same columns:

• Leading prefix wins. (organizationId, deletedAt, status) covers (organizationId) and (organizationId, deletedAt), but not (status) — status is not a leading column.
• Unique counts. A uniqueIndex on the same leading columns serves the same lookups, so it suppresses the non-unique default.
• Partial covers nothing. An index with .where(...) may exclude the rows the default index exists for, so it never suppresses one.
• Order modifiers are ignored. .on(t.status.desc()) still covers (status).

...q.indexes(t) — placing the defaults yourself

In a Drizzle interop table you can declare exactly where the default set lands, the same way ...q.audit() declares the audit columns:

import { pgTable, uniqueIndex } from "drizzle-orm/pg-core";
import { q } from "@quickback/compiler";

export const invoices = pgTable("invoices", {
  // …columns
}, (t) => ({
  ...q.indexes(t),
  invoices_number_unique: uniqueIndex("invoices_number_unique").on(t.number),
}));

The spread is expanded in place at compile time into literal index(...).on(...) entries and disappears from the emitted schema. Without it the same entries are appended to the end of the callback and a compile warning points you here — the emitted schema is identical either way.

Tables authored with the q DSL (feature() / q.table()) don't take the placeholder: the compiler writes their index callback, and q.<col>().index() and q.table(…, { indexes }) are the authoring controls there.

Ownership Fields

For the firewall to work, declare a tenant-scope column. With q.scope() (recommended), the compiler auto-derives the firewall predicate, marks the column as system-managed, and auto-populates it on create:

// q DSL (recommended)
organizationId: q.scope('organization')   // → ctx.activeOrgId
ownerId:        q.scope('owner')          // → ctx.userId
teamId:         q.scope('team')           // → ctx.activeTeamId

When using Drizzle directly, declare a regular column. Auto-detection picks it up by name; q.scope() semantics (systemManaged, auto-populate) still apply because they're keyed off the firewall predicate, not the column DSL:

// Drizzle interop
organizationId: text('organization_id').notNull()
ownerId:        text('owner_id').notNull()
teamId:         text('team_id').notNull()

Those three are tenant auto-stamps (org / owner / team). To auto-stamp a proven-principal identity column instead — the caller's own id within a relationship, not a tenant key — use q.stamp(). It sources from ctx.scope.<kind>.<claim> for scope-only principals and, unlike the tenant stamps, is never a firewall arm.

Display Column

When a table has foreign key columns (e.g., departmentId referencing departments), Quickback can automatically resolve the human-readable label in GET and LIST responses. This eliminates the need for frontend lookup calls.

Auto-Detection

Quickback auto-detects the display column by checking for common column names in this order:

name → title → label → headline → subject → code → displayName → fullName → description

If your departments table has a name column, it's automatically used as the display column. No config needed.

Explicit Override

Override the auto-detected column with displayColumn:

export default defineTable(interviewStages, {
  // ... columns, firewall, guards, crud
  displayColumn: 'label',  // Use 'label' instead of auto-detected 'name'
  read: { access: { roles: ["hiring-manager", "recruiter"] } },
});

Default Sort

Set a default sort order for the CMS table list view with defaultSort:

export default defineTable(podcastEpisodes, {
  // ... columns
  defaultSort: { field: "createdAt", order: "desc" },
  firewall: [{ exception: true }],
  read: { access: { roles: ["PUBLIC"] } },
});

The CMS applies this sort when the table first loads. Users can still click column headers to change the sort order.

How Labels Appear in Responses

For any FK column ending in Id, the API adds a _label field with the resolved display value:

{
  "id": "job_abc",
  "title": "Senior Engineer",
  "departmentId": "dept_xyz",
  "department_label": "Engineering",
  "locationId": "loc_123",
  "location_label": "San Francisco"
}

The pattern is {columnWithoutId}_label. The frontend can find all labels with:

Object.keys(record).filter(k => k.endsWith('_label'))

Label resolution works within the same feature (tables in the same feature directory). System columns (organizationId, createdBy, modifiedBy) are never resolved.

For LIST endpoints, labels are batch-resolved efficiently — one query per FK column, not per record.

References

Declare every feature-table foreign key in the schema with .references(). That one declaration creates the database relationship and gives the API, schema registry, and CMS the same target for label resolution and selector/typeahead controls, and brands the column with the target table's Id in quickback.gen.ts. The resource-level references: map and the <table>Id naming convention resolve the same way for the CMS and the type tree, but create no database constraint:

// features/applications/applications.ts
import candidates from "../candidates/candidates";
import jobs from "../jobs/jobs";

const applications = q.table("applications", {
  id:          q.id(),
  candidateId: q.text().required().references(() => candidates.id),
  jobId:       q.text().required().references(() => jobs.id),
});

When the target table lives in another feature, import it in the table file — import <table> from "../<feature>/<table>", one ../, since table files sit at features/<feature>/. The thunk names a real binding; without the import the generated schema fails migration generation.

Do this even when a column name appears obvious, such as sessionId beside a sessions table. A matching *Id name is not enough evidence to silently invent a relationship, so the compiler refuses the omission instead: SCHEMA_UNDECLARED_FK fails the build when a *Id column names a feature table in the same database, is client-writable or used in a firewall / relationship arm, and declares no .references(). When the column genuinely is not a pointer into that table, say so with a reason:

references: {
  cventEventId: { references: false, reason: 'Cvent-side id; no row in this database' },
},

When both tables carry an org column, also declare match on it — SCHEMA_FK_MATCH_REQUIRED warns until you do, or until you opt out with references: { eventId: { match: false, reason: '…' } }.

Self-references

A column that points at another row of the same table — a parent folder, the version a document replaces — uses .references('self'). It targets the table's own id:

// quickback/features/folders/folders.ts
import { feature, q } from '@quickback/compiler';

export default feature('folders', {
  columns: {
    id:             q.id(),
    name:           q.text().required(),
    parentId:       q.text().optional().references('self', { onDelete: 'set null' }),
    organizationId: q.scope('organization'),
    ...q.audit(),
    ...q.softDelete(),
  },
  read:   { access: { roles: ['member'] } },
  create: { access: { roles: ['member'] } },
  update: { access: { roles: ['member'] } },
});

() => folders.id cannot work here: inside feature() there is no folders binding. In the two-export form the binding exists, and .references(() => folders.id) inside its own initializer works: the compiler reads the thunk as source, so its return is not part of the table's type and there is no TypeScript cycle in your editor or in quickback check. 'self' and the annotated .references((): AnyQuickbackColumn => folders.id) (with type AnyQuickbackColumn from @quickback/compiler) are also accepted. All three spellings generate the same schema.

A self-reference is a normal FK everywhere else:

• Writes check it through the table's own firewall, like any FK referential check: a row can't point at another tenant's row or a soft-deleted one. null detaches the row.
• Reads resolve the parent's display label (parent_label on GET /:id and the list) through the same firewall, and the CMS renders the column as a picker over the same table.
• Cycles are not refused. A row may point at itself, and A → B → A passes too: each write checks one hop. If your data must stay a tree, enforce that in an action or a trigger.
• A batch create checks parents that exist before the batch. A record can't name a sibling from the same batch as its parent; create the parent first.

The legacy resource-level references: { vendorId: "contact" } map can override schema-registry target metadata for an alias, but it does not create a database foreign key. Prefer .references(() => contacts.id) whenever the target is a feature table in the same database. Do not add .references() for Better Auth ids (userId, organizationId, memberId, or auth sessionId): those rows live in AUTH_DB, outside the feature database.

Input Hints

Control how the CMS renders form inputs for specific columns. By default the CMS infers the control from the column itself — q.timestamp() → date-time picker, q.date() → date picker, q.bool() → checkbox, q.int() / q.bigint() / q.smallint() → number, q.decimal() → text (so the exact string is sent), q.enum() → select, q.enum([...]).array() → multiple checkboxes that send an array, q.url() → URL input, q.json() → textarea that validates and sends JSON, and name patterns for plain text (*Url, *Notes, email). The registry carries the q column kind, so this works on SQLite too, where every timestamp is text at the SQL layer. Other .array() columns do not yet have a dedicated CMS editor. inputHints overrides the inference except for enum arrays, which must retain an array value:

export default defineTable(jobs, {
  inputHints: {
    status: "select",
    department: "select",
    salaryMin: "currency",
    salaryMax: "currency",
    description: "richtext",
  },
  // ... firewall, guards, crud
});

Available Hint Values

Input hints are emitted in the schema registry as inputHints on the table metadata, and the CMS reads them to render the appropriate form controls. The bundled CMS form renders richtext, select, checkbox, textarea, date, datetime, currency, number, email, url, and text today; any other hint falls back to the inferred control.

Rich Text Example

For fields that contain HTML content (e.g., blog posts, descriptions from RSS feeds), use "richtext":

export default defineTable(podcastEpisodes, {
  inputHints: { description: "richtext" },
  // ...
});

The CMS will:

• View mode: Render the HTML with proper formatting (headings, lists, links, etc.)
• Edit mode: Load a tiptap rich text editor with a toolbar (bold, italic, H2, H3, bullet/ordered lists, links, code, undo/redo)
• Storage: HTML strings — no format conversion needed, compatible with external HTML content

Relations (Optional)

Drizzle supports defining relations for type-safe joins:

import { relations } from 'drizzle-orm';
import { organizations } from '../organizations/organizations';
import { applications } from './applications';

export const jobsRelations = relations(jobs, ({ one, many }) => ({
  organization: one(organizations, {
    fields: [jobs.organizationId],
    references: [organizations.id],
  }),
  applications: many(applications),
}));

Drizzle relations(...) describe application-side joins; they do not always create a database foreign-key constraint. On Neon, use compiler.migrations.foreignKeys for fully qualified cross-schema or composite constraints that cannot be declared alongside a table. The ordered source and target column tuples must have equal arity, and the referenced target tuple must have a matching unique or primary-key constraint before the foreign key migration runs. A partial unique (where / whereNotDeleted) does not qualify as an FK target.

Use these constraints for relational integrity only. Authorization, pass consumption, state transitions, and other business logic belong in the API action layer.

Database Configuration

Configure database options in your Quickback config:

// quickback.config.ts
export default defineConfig({
  name: 'my-app',
  providers: {
    database: defineDatabase('cloudflare-d1', {
      generateId: 'prefixed',          // 'uuid' | 'cuid' | 'nanoid' | 'short' | 'prefixed' | 'serial' | false
      namingConvention: 'snake_case',   // 'camelCase' | 'snake_case'
      usePlurals: false,                // Auth table names: 'users' vs 'user'
    }),
  },
  compiler: {
    features: {
      auditFields: true,           // Auto-manage audit timestamps
    }
  }
});

Choosing Your Dialect

Use the Drizzle dialect that matches your database provider:

SQLite (Cloudflare D1)

import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';

export const jobs = sqliteTable('jobs', {
  id: text('id').primaryKey(),
  title: text('title').notNull(),
  metadata: text('metadata', { mode: 'json' }),  // JSON stored as text
  isOpen: integer('is_open', { mode: 'boolean' }).default(false),
  organizationId: text('organization_id').notNull(),
});

PostgreSQL (Neon)

import { pgTable, text, serial, jsonb, boolean } from 'drizzle-orm/pg-core';

export const jobs = pgTable('jobs', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  metadata: jsonb('metadata'),           // Native JSONB
  isOpen: boolean('is_open').default(false),
  organizationId: text('organization_id').notNull(),
});

Key Differences

Next Steps

• Configure the firewall for data isolation
• Set up access control for read and write operations
• Define guards for field modification rules
• Add custom actions for business logic