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

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' }).
• 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.

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

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

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

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