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.

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' }). 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.
• Indexes: column-level .index() or table-level indexes / unique as a third arg to q.table(name, cols, { indexes, unique }). With feature(), pass indexes and unique as top-level keys alongside columns — the sugar forwards them into the same q.table opts.

Column Types

q.text() and q.url() accept either a SQL-name string or an options object — q.text({ sqlName: 'org_id', maxLength: 80 }). maxLength adds z.string().max(n) at the validation edge; the SQL column stays text (D1 ignores VARCHAR(n) length anyway).

q.url() is q.text() plus z.string().url() at the validation edge, so javascript: / data: 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. This keeps q's output decoupled from any specific ID library.

For generateId: "cuid", there is one important nuance: import-capable runtime paths can emit real createId() usage, but compiler-managed schema auto-injection may keep the same createId() call site and back it with a file-local crypto.randomUUID() polyfill so schema-generation sandboxes do not depend on project node_modules.

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.

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'

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:

// 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, Supabase), 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.

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

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

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.

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

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. The audit columns will fall back to DB defaults (createdAt/modifiedAt populate from defaultNow(); 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.
• Encrypted data — envelope-encrypted fields support per-organization crypto-shredding by deleting the wrapped DEK. 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

The audit and soft-delete columns are always protected from client input, even with guards: false:

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

Protection is enforced by the runtime wrapper — even if a request body passes one of these, it gets overwritten before the SQL is built.

Auto-Indexes

The compiler injects performance-critical indexes you'd otherwise have to author by hand. The pass runs after audit-field injection (so deletedAt is present) and is idempotent — if you already declared a matching index it's left alone.

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

You can opt out of any auto-injected index by declaring your own with the same name or column set — the injector treats both as already-present and skips.

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

When your FK columns don't match the target table name by convention (e.g., vendorId actually points to the contact table), declare explicit references so the CMS and schema registry know the correct target:

export default defineTable(applications, {
  references: {
    candidateId: "candidate",
    jobId: "job",
    referredById: "candidate",
  },
  // ... firewall, guards, crud
});

Each key is a column name ending in Id, and the value is the camelCase table name it references. These mappings flow into the schema registry as fkTarget on each column, enabling the CMS to render typeahead/lookup inputs that search the correct table.

Convention-based matching (strip Id suffix, look for a matching table) still works for simple cases like projectId → project. Use references only for columns where the convention doesn't match.

Input Hints

Control how the CMS renders form inputs for specific columns. By default the CMS infers input types from the column's SQL type — inputHints lets you override that:

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.

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.

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