Database Schema

Quickback supports three co-equal authoring patterns. All compile to the same generated runtime — the compiler dispatches per file, so every feature in a project may choose independently.

• feature() — single-export sugar for the q DSL. One import, one export, everything about the table and its security contract in one call. Recommended default. See feature() — single-export sugar.
• q.table() + defineTable() — the two-export form. Same DSL, more explicit. Use when you want to reference the table identifier before the default export (e.g. from an action file).
• Drizzle sqliteTable() / pgTable() + defineTable() — interop with existing Drizzle code. Use when you're bringing an existing schema forward or want dialect-specific column options.

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'),
  },
  // 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. In action files, import jobs from './jobs' gives you the same typed handle you'd get from q.table(), so defineActions(jobs, { … }) accepts it unchanged.

q.table() + defineTable() — the two-export form

// 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'),
});

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 cuid2 app-layer default.
• .required() / .optional() control NOT NULL. Chain before or after .default(x).
• References: q.text().required().references(() => other.id, { onDelete: 'cascade' }).
• 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 — prefixed UUIDs for D1, cuid for libsql/bun-sqlite, crypto.randomUUID() for supabase, etc. This keeps q's output decoupled from any specific ID library.

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.

Naming Convention

By default, q picks the SQL column name from the JS key using the target database's conventional casing:

• Postgres family (Supabase, Neon, D1) → snake_case (organizationId → organization_id)
• SQLite family (bun-sqlite, better-sqlite3, libsql) → camelCase (organizationId → organizationId)

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

// quickback/quickback.config.ts
export default {
  providers: {
    database: {
      name: 'bun-sqlite',
      config: {
        path: './data/app.db',
        namingConvention: 'snake_case', // force snake_case everywhere
      },
    },
  },
};

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'

Defining Tables with Drizzle

If you prefer Drizzle — or have an existing schema you're bringing forward — 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'),
});

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.

Choosing Between q and Drizzle

Features in the same project may mix freely — Quickback dispatches per feature.

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.ts           # Custom actions
    ├── 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. Still works but emits a warning during compilation. Use defineTable instead.

// 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
});

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

The compiler automatically injects the four audit fields (createdAt/createdBy/modifiedAt/modifiedBy), the soft-delete pair (deletedAt/deletedBy, when soft delete is enabled), and organizationId into child/junction tables when the parent feature is org-scoped. This ensures cascade soft deletes and scoped queries work correctly without manual column definitions.

Audit Fields

Quickback adds four audit columns to every table (including child/junction tables without defineTable) — never declare them in your schemas:

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

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 every Drizzle table. Idempotent: if you've authored a column with the same name yourself, the compiler leaves yours alone.
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. Caller-supplied values for createdAt/createdBy/modifiedAt/modifiedBy are silently overwritten. This is intentional: the audit log can never be forged by a crafted request body or by a client echoing a row back into a PATCH.

// In a custom action handler — caller cannot fake audit attribution
await db.insert(records).values({
  id: createId(),
  status: 'active',
  createdBy: 'admin',           // ← ignored; wrapper writes ctx.userId
  modifiedAt: '1999-01-01',     // ← ignored; wrapper writes "now"
});

What the caller is responsible for: id (the framework doesn't mint ids for you) and the domain fields.

What the caller does not need to set: any of the four 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:

The compiler injects them only when the resource uses soft delete — i.e. when neither delete.mode nor delete.batch.mode is explicitly 'hard'. Soft is the default, so most tables get the columns. Tables with both delete paths set to 'hard' skip them entirely, which also drops the isNull(deletedAt) predicate from the firewall WHERE clause.

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

// hard delete — no deletedAt/deletedBy columns, no soft-delete predicate
delete: { mode: 'hard', batch: { mode: 'hard' } }

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.

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

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, {
  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, {
  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),
}));

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 (D1, Turso, better-sqlite3)

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 (Supabase, 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