Common Patterns

Recipes for common Quickback scenarios — public/private data, user-scoped resources, multi-table features, and more.

Quick recipes for common scenarios. Each pattern shows the complete defineTable configuration.

Team-Wide Read, Manager-Only Write

A job board where all team members can browse open positions but only hiring managers can create or edit jobs:

// 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, {
  firewall: [{ field: 'organizationId', equals: 'ctx.activeOrgId' }],
  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"] } },
});

User-Scoped Personal Data

Interview notes that belong to a specific interviewer. Each interviewer only sees their own notes:

// Schema includes both organizationId AND ownerId (q DSL)
import { q, defineTable } from "@quickback/compiler";

export const interviewNotes = q.table("interview_notes", {
  id:             q.id(),
  candidateId:    q.text().required(),
  content:        q.text().required(),
  rating:         q.int().optional(),
  organizationId: q.scope("organization"),
  ownerId:        q.text().required(),
  ...q.audit(),
  ...q.softDelete(),
});

export default defineTable(interviewNotes, {
  firewall: [
    { field: 'organizationId', equals: 'ctx.activeOrgId' },
    { field: 'ownerId',        equals: 'ctx.userId' },  // Only the interviewer can see their own notes
  ],
  guards: {
    createable: ["candidateId", "content", "rating"],
    updatable: ["content", "rating"],
  },
  read: {
    access: { roles: ["owner", "hiring-manager", "recruiter", "interviewer"] },
  },
  create: { access: { roles: ["owner", "hiring-manager", "recruiter", "interviewer"] } },
  update: { access: { roles: ["owner", "hiring-manager", "recruiter", "interviewer"] } },
  delete: { access: { roles: ["owner", "hiring-manager", "recruiter", "interviewer"] } },
});

The owner predicate ({ field: 'ownerId', equals: 'ctx.userId' }) means even hiring managers can only see notes they authored. To let hiring managers see all notes regardless of author, drop the owner predicate from the firewall and gate per-id access via record conditions instead.

Multi-Table Feature with Internal Tables

A feature with a main table exposed via API and a junction table used only in actions:

// quickback/features/applications/applications.ts — exposed via API
import { q, defineTable } from "@quickback/compiler";

export const applications = q.table("applications", {
  id:             q.id(),
  candidateId:    q.text().required(),
  jobId:          q.text().required(),
  stage:          q.text().default("applied").required(),
  notes:          q.text().optional(),
  organizationId: q.scope("organization"),
  ...q.audit(),
  ...q.softDelete(),
});

export default defineTable(applications, {
  firewall: [{ field: 'organizationId', equals: 'ctx.activeOrgId' }],
  guards: {
    createable: ["candidateId", "jobId", "stage", "notes"],
    updatable: ["notes"],
  },
  read: {
    access: { roles: ["owner", "hiring-manager", "recruiter", "interviewer"] },
  },
  create: { access: { roles: ["owner", "hiring-manager", "recruiter"] } },
  update: { access: { roles: ["owner", "hiring-manager", "recruiter"] } },
  delete: { access: { roles: ["owner", "hiring-manager"] } },
});

// quickback/features/applications/interview-scores.ts — internal, no API routes
// No default export = no routes generated
import { sqliteTable, text, integer } from "drizzle-orm/sqlite-core";

export const interviewScores = sqliteTable("interview_scores", {
  id: text("id").primaryKey(),
  applicationId: text("application_id").notNull(),
  interviewerId: text("interviewer_id").notNull(),
  score: integer("score").notNull(),
  feedback: text("feedback"),
  organizationId: text("organization_id").notNull(),  // Always scope junction tables

  // Internal tables are validated too — there is no exemption for a table
  // without a default export. Raw Drizzle spells the managed columns out;
  // `...q.audit()` / `...q.softDelete()` only work in the `q` DSL.
  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"),
});

// quickback/features/applications/actions/submitScore.ts — uses the internal table
import { z } from "zod";
import { defineAction } from "../.quickback/define-action";
import { interviewScores } from "../interview-scores";

// No `path:` → record-based action, mounted at POST /applications/:id/submit-score
export default defineAction({
  description: "Record an interviewer's score and written feedback for an application.",
  input: z.object({ score: z.number().min(1).max(5), feedback: z.string() }),
  access: { roles: ["owner", "hiring-manager", "interviewer"] },
  async execute({ db, record, input, ctx }) {
    // The scoped db auto-injects organizationId (and audit fields) on insert.
    await db.insert(interviewScores).values({
      applicationId: record.id,
      interviewerId: ctx.userId,
      score: input.score,
      feedback: input.feedback,
    });
    return record;
  },
});

Referencing Users / Org Members (Assignees)

Assignee-style features need to validate that a submitted userId is actually a member of the caller's organization. The scoped db only sees feature tables — reach the Better Auth tables via c.get("authDb") and the generated getOrgMemberRole helper:

// quickback/features/projects/actions/assign.ts
import { z } from "zod";
import { ActionError } from "@quickback/compiler";
import { defineAction, projects } from "../.quickback/define-action";
import { getOrgMemberRole } from "../../../lib/org-access";

export default defineAction({
  description: "Assign a project to an org member",
  input: z.object({ assigneeId: z.string() }),
  access: { roles: ["member+"] },  // "+" requires auth.roleHierarchy in quickback.config.ts
  async execute({ db, record, input, ctx, c, whereRecord }) {
    const role = await getOrgMemberRole(c.get("authDb"), input.assigneeId, ctx.activeOrgId!);
    if (!role) {
      throw new ActionError("Assignee is not a member of this organization", "NOT_A_MEMBER", 400);
    }
    const [updated] = await db
      .update(projects)
      .set({ assigneeId: input.assigneeId })
      .where(whereRecord!(projects))
      .returning();
    return updated;
  },
});

Store the assignee as a plain text column (e.g. assigneeId: q.text().optional()) — don't name it userId. userId is not auto-detected as an owner column on feature tables (that column is ownerId), but the compiler flags it: an error if it's the only candidate column on the table, a warning alongside a detected isolation column. Rename to assigneeId, or declare firewall: { owner: { column: "userId" } } if the column really is the row's owner. See Firewall.

Admin-Only Status Field

A field that only hiring managers can modify, but everyone can see:

// quickback/features/applications/applications.ts
import { q, defineTable } from "@quickback/compiler";

export const applications = q.table("applications", {
  id:             q.id(),
  candidateId:    q.text().required(),
  jobId:          q.text().required(),
  stage:          q.text().default("applied").required(),
  notes:          q.text().optional(),
  organizationId: q.scope("organization"),
  ...q.audit(),
  ...q.softDelete(),
});

export default defineTable(applications, {
  firewall: [{ field: 'organizationId', equals: 'ctx.activeOrgId' }],
  guards: {
    createable: ["candidateId", "jobId", "notes"],
    updatable: ["notes"],                       // Recruiters can only update notes
    protected: { stage: ["advanceStage"] },     // Stage only changes via the named action
  },
  read: {
    access: { roles: ["owner", "hiring-manager", "recruiter", "interviewer"] },
  },
  create: { access: { roles: ["owner", "hiring-manager", "recruiter"] } },
  update: { access: { roles: ["owner", "hiring-manager", "recruiter"] } },
  delete: { access: { roles: ["hiring-manager"] } },
});

Then create an action that hiring managers use to change stage:

// quickback/features/applications/actions/advanceStage.ts
// Mounted at POST /api/v1/applications/:id/advance-stage
import { z } from "zod";
// The generated per-feature helper re-exports the bound table, so one import
// gets you both — and `applications` here is the GENERATED table, with the
// audit/scope columns visible on `record` without `as any`.
import { defineAction, applications } from "../.quickback/define-action";

export default defineAction({
  description: "Move an application to a new stage in the hiring pipeline.",
  input: z.object({ stage: z.enum(["applied", "screening", "interview", "offer", "hired", "rejected"]) }),
  access: { roles: ["hiring-manager"] },
  async execute({ db, record, input, whereRecord }) {
    const [updated] = await db
      .update(applications)
      .set({ stage: input.stage })
      .where(whereRecord!(applications))
      .returning();
    return updated;
  },
});

Sensitive Data with Role-Based Masking

A candidate record where PII is masked differently per role:

// quickback/features/candidates/candidates.ts
import { q, defineTable } from "@quickback/compiler";

export const candidates = q.table("candidates", {
  id:             q.id(),
  name:           q.text().required(),
  email:          q.text().required(),
  phone:          q.text().optional(),
  resumeUrl:      q.text().optional(),
  source:         q.text().optional(),
  organizationId: q.scope("organization"),
  ...q.audit(),
  ...q.softDelete(),
});

export default defineTable(candidates, {
  firewall: [{ field: 'organizationId', equals: 'ctx.activeOrgId' }],
  guards: {
    createable: ["name", "email", "phone", "resumeUrl", "source"],
    updatable: ["name", "phone"],
  },
  masking: {
    email: { type: "email", show: { roles: ["hiring-manager", "recruiter"] } },
    phone: { type: "phone", show: { roles: ["hiring-manager", "recruiter"] } },
  },
  read: {
    access: { roles: ["owner", "hiring-manager", "recruiter", "interviewer"] },
  },
  create: { access: { roles: ["hiring-manager", "recruiter"] } },
  update: { access: { roles: ["hiring-manager", "recruiter"] } },
  delete: { access: { roles: ["hiring-manager"] } },
});

Role	email	phone
hiring-manager	`jane@example.com`	`555-123-4567`
recruiter	`jane@example.com`	`555-123-4567`
interviewer	`j*@e****.com`	`*-*-4567`

External System Sync

An import table for syncing candidates from an external ATS via upsert:

// quickback/features/ats-imports/ats-imports.ts
import { q, defineTable } from "@quickback/compiler";

export const atsImports = q.table("ats_imports", {
  id:             q.id(),
  externalId:     q.text().required(),
  source:         q.text().required(),
  candidateName:  q.text().required(),
  candidateEmail: q.text().required(),
  rawPayload:     q.json().optional(),
  syncedAt:       q.text().optional(),
  organizationId: q.scope("organization"),
  ...q.audit(),
  ...q.softDelete(),
});

export default defineTable(atsImports, {
  firewall: [{ field: 'organizationId', equals: 'ctx.activeOrgId' }],
  // `candidateEmail` is detected as sensitive, so it needs an explicit answer.
  masking: { candidateEmail: false },
  guards: {
    createable: ["externalId", "source", "candidateName", "candidateEmail", "rawPayload"],
    updatable: ["candidateName", "candidateEmail", "rawPayload", "syncedAt"],
  },
  read: {
    access: { roles: ["owner", "hiring-manager"] },
  },
  create: { access: { roles: ["owner", "hiring-manager"] } },
  update: { access: { roles: ["owner", "hiring-manager"] } },
  delete: { access: { roles: ["owner"] } },
});