# 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()](/define/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)

```typescript
// 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](/define/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.

```typescript
// 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`](/define/changesets) on the root to generate the atomic aggregate write path. `other` must be a **feature table in the same database**.

> **Never FK Better Auth tables**
>
> `.references(() => users.id)` (or `user`, `organization`, `member`, `session`) on a feature column is illegal. Those tables live in `AUTH_DB`; your features live in `DB`. SQLite cannot FK across D1 databases, and drizzle-kit fails generating features migrations:
>
> `Critical command failed: Generate features database migrations`
>
> A Start chat compile (`complete=false`) still succeeds — it never runs drizzle-kit. Deploy (`complete=true`) is the first time that command runs.
>
> A junction's `userId` is a **plain text** Better Auth id, not a SQL FK:
>
> {/* doc-compile: skip — two-column excerpt, not a host feature */}
> ```typescript
> userId:    q.text().required(),  // ctx.userId — no .references()
> channelId: q.text().required().references(() => channels.id, { onDelete: 'cascade' }),
> ```
>
> `authz.relationships` `subject: { column: 'userId', equals: 'ctx.userId' }` does not need, and must not create, a SQL FK. See [Relationships](/define/relationships). To query Better Auth tables, use `c.get("authDb")` from an action — see [Scoped DB](/define/actions/scoped-db#reaching-better-auth-tables-authdb).


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

| Builder | Drizzle equivalent | Zod type |
|---------|---------------------|----------|
| `q.text()` | `text(...)` | `z.string()` |
| `q.url()` | `text(...)` | `z.string().url()` |
| `q.int()` | `integer(...)` | `z.number().int()` |
| `q.bool()` | `boolean(...)` | `z.boolean()` |
| `q.uuid()` | `uuid(...)` | `z.string().uuid()` |
| `q.timestamp()` | `timestamp(...)` (converted to SQLite `text` on sqlite targets) | `z.coerce.date()` |
| `q.json()` | `jsonb(...)` | `z.unknown()` (or the type you pass in) |
| `q.enum(['a','b'])` | `text(..., { enum: [...] })` | `z.enum([...])` |
| `q.id()` | `text('id').primaryKey()` | `z.string()` |
| `q.scope(kind)` | `text(...).notNull()` | `z.string()` |
| `q.stamp({ fromScope, claim })` | `text(...).notNull()` (nullable with `optional: true`) | `z.string().optional()` |

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

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

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

| | `q.scope('<kind>')` | `q.stamp({ fromScope, claim })` |
|---|---|---|
| **Purpose** | tenant isolation column | proven-principal **identity** column |
| **Value** | `ctx.scope.<kind>.id` / `activeOrgId` / `userId` | `ctx.scope.<kind>.<claim>` (`id` \| `subjectId` \| a scalar sub-key) |
| **Firewall arm?** | **yes** — it is the tenant `WHERE` | **never** — identity is not a tenant scope |
| **Guarantee** | "stamp **and verify**" (firewall re-checks on read/patch/delete) | "stamp **and prove**" (un-spoofable by token provenance) |

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

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

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

| Target Database | Import From | Table Function |
|-----------------|-------------|----------------|
| Cloudflare D1, Turso, SQLite | `drizzle-orm/sqlite-core` | `sqliteTable` |
| Supabase, Neon, PostgreSQL | `drizzle-orm/pg-core` | `pgTable` |
| PlanetScale, MySQL | `drizzle-orm/mysql-core` | `mysqlTable` |

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

| Type | Drizzle Function | Example |
|------|------------------|---------|
| String | `text()`, `varchar()` | `text('name')` |
| Integer | `integer()`, `bigint()` | `integer('count')` |
| Boolean | `boolean()` | `boolean('is_active')` |
| Timestamp | `timestamp()` | `timestamp('created_at')` |
| JSON | `json()`, `jsonb()` | `jsonb('metadata')` |
| UUID | `uuid()` | `uuid('id')` |
| Decimal | `decimal()`, `numeric()` | `decimal('price', { precision: 10, scale: 2 })` |

### Drizzle Column Modifiers

```typescript
// 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`).

{/* doc-compile: skip — a two-line `defineTable` vs `defineResource` name comparison with its config elided to a comment; there is no file here to compile. The whole-file form is at the top of this page. */}
```typescript
// 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.

| Scenario | What to do |
|----------|------------|
| Table needs its own API routes + security | Own file with `defineTable()` |
| Table is internal/supporting (no direct API) | Extra `.ts` file in the parent feature directory, no `defineTable()` |

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:

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

> **Always Include a Scope Column**
>
> Internal and junction tables should include `organizationId` (or your relevant scope column) even though they don't have their own firewall config. This ensures the scoped `db` in action handlers automatically filters them correctly, and cascade soft deletes work across org boundaries.


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](#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](#declaring-them-visibly--qaudit--qsoftdelete).

> **Route keys are handled automatically**
>
> Quickback gives every route-backed table one stable key. `compiler.injectId`
> defaults to `'auto'`: a table without a primary key receives an `id` whose
> generation strategy follows `providers.database.config.generateId`.
>
> For a greenfield table with a non-`id` or compound primary key, the compiler
> preserves that domain uniqueness as a unique index and adds the route `id`.
> For an already-deployed table where changing the primary key needs a migration,
> the compile stops with migration guidance instead of emitting broken routes.
>
> Use `q.id()` when you want the key visible in authored source. Set
> `compiler.injectId: false` only when deliberately retaining a custom
> single-column route key; Quickback resolves that key through schema metadata.
> Internal `// @quickback-internal` junction tables can keep compound keys because
> they do not expose CRUD routes.


## Audit Fields

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

| Field | Type | Description |
|-------|------|-------------|
| `createdAt` | timestamp | Set on insert |
| `createdBy` | text | Actor that performed the insert (`ctx.userId`) |
| `modifiedAt` | timestamp | Set on every insert and update |
| `modifiedBy` | text | Actor that performed the latest insert/update (`ctx.userId`) |

Audit fields are a separate concept from soft-delete (`deletedAt`/`deletedBy`) — they're always present, regardless of delete mode. See [Soft-Delete Fields](#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, and the managed columns carry a DDL default of the unix epoch (so `ALTER TABLE ADD COLUMN` works on populated tables). An omitted column therefore **does not** fail loudly 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:

```typescript
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.

> For genuinely context-free writes — cron, seed scripts, backfills that must preserve historical timestamps — pass your own values. `auditStamp` needs a real caller and throws without one, by design.


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

```ts
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.

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

| Boundary | Actor id |
|----------|----------|
| Inbound webhook | `system:webhook-<provider>` (e.g. `system:webhook-stripe`) |
| Scheduled / cron | `system:cron-<job>` (e.g. `system:cron-cleanup-stale`) |
| Queue worker | `system:queue-<queue>` (e.g. `system:queue-embeddings-retry`) |
| Quickback platform-internal write with no user upstream | `system:quickback` |
| Out-of-band operator script | `system:script-<name>` |

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:

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

| Field | Type | Description |
|-------|------|-------------|
| `deletedAt` | timestamp | Set when the record is soft-deleted |
| `deletedBy` | text | Actor that performed the soft delete (`ctx.userId`) |

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

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

```ts
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`](/platform/schedules#retention-and-purge-jobs) 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.

| Trigger | Index | Why |
|---------|-------|-----|
| Tenant column (`organizationId` / `organisationId` / `userId`) + `deletedAt` | Composite `(<isolation_col>, deletedAt)` | Every list query filters by isolation AND `deletedAt IS NULL` — a composite index lets D1/SQLite prune both predicates in one btree walk. |
| Any FK column (`.references(() => other.id)`) | Single-column `(<fk_col>)` | SQLite/D1 don't auto-index FK columns, and the generated FK existence checks read by id on the parent. |
| Any column that appears in a view's `read.query.sortable` allowlist (or `defaultSort`) | Single-column `(<col>)` | Sort scans go through an index instead of a full-table scan. |

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:

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

```typescript
// 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()`](#qstamp--proven-principal-identity-columns). 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`:

{/* doc-compile: skip — one-key excerpt: the point is the single `displayColumn` line a reader adds to a table they already have. This page's whole-file examples are the `feature()` and `q.table()`+`defineTable()` forms at the top, which the gate does compile. */}
```typescript
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`:

{/* doc-compile: skip — one-key excerpt: the point is the single `defaultSort` line a reader adds to a table they already have. This page's whole-file examples are the `feature()` and `q.table()`+`defineTable()` forms at the top, which the gate does compile. */}
```typescript
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:

```json
{
  "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:

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

{/* doc-compile: skip — one-key excerpt: the fence is the `references` map only, with columns/firewall/guards/crud already elided to `// ...`. This page's whole-file examples are the `feature()` and `q.table()`+`defineTable()` forms at the top, which the gate does compile. */}
```typescript
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:

{/* doc-compile: skip — one-key excerpt: the fence is the `inputHints` map only, with firewall/guards/crud already elided to `// ...`. This page's whole-file examples are the `feature()` and `q.table()`+`defineTable()` forms at the top, which the gate does compile. */}
```typescript
export default defineTable(jobs, {
  inputHints: {
    status: "select",
    department: "select",
    salaryMin: "currency",
    salaryMax: "currency",
    description: "richtext",
  },
  // ... firewall, guards, crud
});
```

### Available Hint Values

| Hint | Renders As |
|------|-----------|
| `richtext` | Rich text editor (tiptap) — bold, italic, headings, lists, links. Stores HTML. Lazy-loaded in edit mode, rendered as formatted HTML in view mode. |
| `select` | Dropdown select (single value) |
| `multi-select` | Multi-value select |
| `radio` | Radio button group |
| `checkbox` | Checkbox toggle |
| `textarea` | Multi-line text input |
| `lookup` | FK typeahead search |
| `hidden` | Hidden from forms |
| `color` | Color picker |
| `date` | Date picker |
| `datetime` | Date + time picker |
| `time` | Time picker |
| `currency` | Currency input with formatting |

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

{/* doc-compile: skip — one-key excerpt: three lines showing `inputHints: { description: "richtext" }`, with the rest of the file already elided to `// ...`. This page's whole-file examples are the `feature()` and `q.table()`+`defineTable()` forms at the top, which the gate does compile. */}
```typescript
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:

```typescript
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`](/configure#neon-foreign-key-migrations)
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:

```typescript
// 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)

```typescript
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)

```typescript
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

| Feature | SQLite | PostgreSQL |
|---------|--------|------------|
| Boolean | `integer({ mode: 'boolean' })` | `boolean()` |
| JSON | `text({ mode: 'json' })` with D1 JSON functions and `->` / `->>` operators | `jsonb()` or `json()` with PostgreSQL JSON indexing |
| Arrays | Relational child rows or JSON arrays (`json_each`) | Native arrays, relational rows, or JSONB |
| Auto-increment | `integer().primaryKey()` | `serial()` |
| UUID | `text()` | `uuid()` |

## Next Steps

- [Configure the firewall](/define/firewall) for data isolation
- [Set up access control](/define/access) for read and write operations
- [Define guards](/define/guards) for field modification rules
- [Add custom actions](/define/actions) for business logic