Database Schema

Define your database schema using Drizzle ORM with defineTable. Combine schema definition and security configuration in a single TypeScript file.

Quickback uses Drizzle ORM to define your database schema. With defineTable, you combine your schema definition and security configuration in a single file.

Defining Tables with defineTable

Each table gets its own file with schema and config together. Use the Drizzle dialect that matches your target database:

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`

// quickback/features/jobs/jobs.ts
// For D1/SQLite targets:
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(),         // "draft" | "open" | "closed"
  salaryMin: integer('salary_min'),
  salaryMax: integer('salary_max'),
});

export default defineTable(jobs, {
  firewall: { organization: {} },
  guards: {
    createable: ["title", "department", "status", "salaryMin", "salaryMax"],
    updatable: ["title", "department", "status"],
  },
  crud: {
    list: { access: { roles: ["owner", "hiring-manager", "recruiter", "interviewer"] } },
    get: { 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"] } },
  },
});

export type Job = typeof jobs.$inferSelect;

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

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

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

// Internal scoring table - no routes needed
export const interviewScores = sqliteTable('interview_scores', {
  applicationId: text('application_id').notNull(),
  interviewerId: text('interviewer_id').notNull(),
  score: integer('score'),
  organizationId: text('organization_id').notNull(),  // Always scope junction tables
});

// No default export = no CRUD routes generated

These internal tables still participate in the database schema and migrations — they just don't get API routes or security configuration.

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.

The compiler automatically injects audit fields (createdAt, modifiedAt, deletedAt, etc.) 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 automatically adds and manages these audit fields on all tables in a feature (including child/junction tables without defineTable) - you don't need to define them:

Field	Type	Description
`createdAt`	timestamp	Set when record is created
`createdBy`	text	User ID who created the record
`modifiedAt`	timestamp	Updated on every change
`modifiedBy`	text	User ID who last modified
`deletedAt`	timestamp	Set on soft delete (optional)
`deletedBy`	text	User ID who deleted (optional)

The soft delete fields (deletedAt, deletedBy) are only added if your resource uses soft delete mode.

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

Protected System Fields

These fields are always protected and cannot be set by clients, even with guards: false:

id (when generateId is not false)
createdAt, createdBy
modifiedAt, modifiedBy
deletedAt, deletedBy

Ownership Fields

For the firewall to work, include the appropriate ownership columns:

// For organization-scoped data (most common)
organizationId: text('organization_id').notNull()

// For user-owned data (personal data)
ownerId: text('owner_id').notNull()

// For team-scoped data
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'
  firewall: { organization: {} },
  crud: {
    list: { access: { roles: ["hiring-manager", "recruiter"] } },
    get: { access: { roles: ["hiring-manager", "recruiter"] } },
  },
});

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: "textarea",
  },
  // ... firewall, guards, crud
});

Available Hint Values

Hint	Renders As
`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.

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

Feature	SQLite	PostgreSQL
Boolean	`integer({ mode: 'boolean' })`	`boolean()`
JSON	`text({ mode: 'json' })`	`jsonb()` or `json()`
Auto-increment	`integer().primaryKey()`	`serial()`
UUID	`text()`	`uuid()`

Next Steps

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

On this page