Triggers - Postgres-Style Table Triggers

Run data rules and side-effects on writes. SQL triggers compile to a real database trigger — SQLite on D1, a SECURITY INVOKER PL/pgSQL function on Postgres; handler triggers run as application hooks at the write chokepoint.

Run logic when rows change — Postgres-style before/after triggers on insert/update/delete, declared per table.

Each trigger lowers one of two ways, and the compiler reports which:

You write	Lowers to	Fires on
`sql:`	A real database trigger in your migrations — a SQLite `CREATE TRIGGER` on D1, a `SECURITY INVOKER` PL/pgSQL function + trigger on Postgres	Every write path — REST, actions, queue handlers, cron, raw SQL. Atomic with the write.
`handler:`	An application hook at the audit-wrapper write chokepoint	Writes through the wrapped `db` — REST CRUD and actions. Not queue handlers, cron, or raw SQL.

Both lanes exist on every database provider, with the same contract. handler: is the default and the business-logic lane. sql: is the invariant lane: data rules that must hold on every write path, atomically with the write — soft-delete mapping, counters, denormalized fields. SQL triggers carry data rules, never business flows and never role logic; the compiler's own audit-stamping triggers are the precedent.

There is no auto-translation between the lanes — SQL bodies stay SQL, functions stay functions — and none between SQL dialects either. A sql: body is authored in the target database's own dialect and validated against it at compile time. See Dialects.

Basic Usage

// features/invoices/invoices.ts
import { feature, q } from '@quickback/compiler';

export default feature('invoices', {
  columns: {
    id:             q.id(),
    title:          q.text({ maxLength: 200 }).required(),
    notes:          q.text({ maxLength: 2000 }).optional(),
    total:          q.int().default(0).required(),
    status:         q.enum(['draft', 'sent', 'paid']).default('draft').required(),
    organizationId: q.scope('organization'),
    ...q.audit(),
    ...q.softDelete(),
  },
  read:   { access: { roles: ['member'] } },
  create: { access: { roles: ['member'] } },
  update: { access: { roles: ['member'] } },
  delete: { access: { roles: ['member'] }, mode: 'soft' },

  triggers: {
    beforeInsert: [
      {
        name: 'guardTotals',
        handler: ({ values }) => {
          if (values.total < 0) throw new Error('total must be >= 0');
          return { notes: values.notes ?? 'created' }; // merge into the write
        },
      },
    ],
    afterInsert: [
      {
        // SQL lane — fires for raw SQL too, atomic with the insert
        name: 'stampNotes',
        sql: `UPDATE invoices SET notes = NEW.title WHERE id = NEW.id;`,
      },
    ],
    beforeUpdate: [
      {
        // The universal-enforcement shape: WHEN guard + RAISE(ABORT)
        name: 'lockPaid',
        when: `OLD.status = 'paid' AND NEW.status != 'paid'`,
        sql: `SELECT RAISE(ABORT, 'paid invoices cannot be reopened');`,
      },
    ],
  },
});

Events: beforeInsert, afterInsert, beforeUpdate, afterUpdate, beforeDelete, afterDelete. Each event takes an array of named triggers; names must be unique per event.

SQL triggers (`sql:`)

The compiler owns the object names and ships the DDL in a journaled migration — content-addressed, so an unchanged trigger set produces no new migration, and removing a trigger from config drops it from the database on the next migrate.

What is the same on every provider:

You write bare, ;-terminated statements. The compiler owns the surrounding frame — you never author CREATE TRIGGER (or, on Postgres, CREATE FUNCTION).
OLD.column / NEW.column references use SQL column names (deleted_at, not deletedAt) and are validated against the table at compile time.
Optional when: is a SQL expression guard (WHEN clause).
SQL triggers run inside the triggering statement, so they are atomic with the write, and a rejection rolls the whole statement back.
Names are qb_trg_<table>_<event-code>_<name> and are the compiler's, not yours.

Dialects — a `sql:` body is never translated

A sql: body is raw SQL for your database. The compiler validates it in the target's dialect and fails the compile when it sees the other one; it never rewrites a body to fit. Declaring sql: on a provider with neither lane (anything that is not cloudflare-d1 or a Postgres provider) is also a compile error.

A SQLite RAISE(ABORT, ...) in a project targeting Postgres is rejected with:

RAISE(ABORT, ...) is SQLite syntax and has no PL/pgSQL equivalent — this project targets PostgreSQL. Reject the write with RAISE EXCEPTION 'message'; instead. Trigger bodies are never translated between dialects.

Porting a project between D1 and Postgres means porting its sql: bodies by hand. That is deliberate: a silent translation of a rule that must hold on every write path is the last thing you want to be wrong about.

SQLite (`cloudflare-d1`)

The body is inlined into CREATE TRIGGER ... BEGIN <body> END.

The compiler emits the BEGIN/END frame uppercase; lowercase begin breaks remote D1's statement splitter even though local D1 accepts it. An authored frame is rejected rather than double-wrapped.
Bare CASE ... END expressions, including nested cases, are supported. For example: SELECT CASE WHEN NEW.total < 0 THEN RAISE(ABORT, 'invalid total') END;. That END closes the expression.
To reject a write: SELECT RAISE(ABORT, 'message');
SQL triggers are the only transactional option on D1, which has no db.transaction().

PostgreSQL (`neon`, `planetscale-postgres`)

Postgres has no inline trigger body, so each declaration becomes two compiler-owned objects in the app schema: a RETURNS trigger function carrying your statements, and the trigger that calls it.

triggers: {
  beforeInsert: [
    {
      // Before-row mutation: assign to NEW. The compiler adds `RETURN NEW`.
      name: 'defaultNotes',
      sql: `NEW."notes" := COALESCE(NEW."notes", NEW."title");`,
    },
  ],
  beforeUpdate: [
    {
      name: 'lockPaid',
      when: `OLD."status" = 'paid' AND NEW."status" <> 'paid'`,
      sql: `RAISE EXCEPTION 'paid invoices cannot be reopened';`,
    },
  ],
  afterInsert: [
    {
      // Cross-table write. Unqualified names resolve in the `app` schema.
      name: 'logCreate',
      sql: `INSERT INTO invoice_events (id, invoice_id, organization_id, actor)
VALUES (NEW."id" || ':ins', NEW."id", NEW."organization_id", auth.user_id());`,
    },
  ],
}

Write PL/pgSQL statements, not a function. The compiler owns CREATE OR REPLACE FUNCTION, the dollar quote, and the trailing RETURN NEW (or RETURN OLD on a hard-delete trigger) — so a before* trigger that mutates NEW proceeds, and you never return NULL by accident and silently skip a write. Authoring CREATE FUNCTION or CREATE TRIGGER is rejected.
IF … END IF, LOOP … END LOOP and nested BEGIN … END blocks are ordinary statements here and are accepted.
To reject a write: RAISE EXCEPTION 'message'; (SQLSTATE P0001). The whole statement rolls back.
Functions are SECURITY INVOKER and run with a pinned search_path = "app", public, pg_catalog. That is the security contract: a cross-table write from your trigger is still subject to RLS and grants, so it cannot reach another tenant's rows — a cross-tenant write fails with 42501 and rolls the statement back. Quickback never elevates a user trigger to SECURITY DEFINER.
The caller claims are transaction-local, so auth.user_id(), auth.org_id() and current_setting('request.jwt.claim.sub', true) all read the acting principal from inside the body.
Your triggers sort before the compiler's audit-stamping triggers (qb_trg_* < set_*_audit_on_*, and Postgres fires BEFORE row triggers by name), so a trigger cannot forge createdBy / modifiedBy.
Object names are bounded to Postgres' 63-byte identifier limit with a hash suffix, so two long trigger names that differ only past the cut stay distinct instead of silently truncating onto each other.

Soft-delete mapping

On soft-delete tables (the default), *Delete events map to the soft-delete transition — the compiler emits an AFTER UPDATE OF "deleted_at" ... WHEN NEW."deleted_at" IS NOT NULL AND OLD."deleted_at" IS NULL trigger, plus a plain AFTER DELETE variant so raw-SQL hard deletes are still covered. *Update events get the inverse guard, so a soft delete fires delete triggers exactly once and never double-fires as an update.

Identical on both dialects. On Postgres the two delete variants are two functions, not one, because the transition trigger fires on UPDATE and returns NEW while the hard-delete one returns OLD.

Upgrades and removal

The trigger migration is appended to the journal after the schema migration (and, on Postgres, after the RLS document), and is content-addressed:

An unchanged trigger set appends nothing on recompile.
Editing or renaming a trigger appends one migration that drops the previous objects and creates the current ones.
Removing a trigger from config appends one drop-only migration; on Postgres the generated function is dropped too, so no orphan is left behind.
Nothing already applied is ever rewritten.

Drops never use CASCADE — an unexpected dependency fails loudly rather than being swept — and Postgres trigger drops are guarded on to_regclass, so a table the preceding schema migration removed does not break the cleanup.

Handler triggers (`handler:`)

Handlers run at the same write chokepoint that powers audit stamping and Live Views, with full application context:

handler: ({ table, op, values /* before */, row /* after */, ctx, db, env }) => { ... }

The db a handler receives is the features database. There is no c and no authDb. Prefer ctx.userId, ctx.activeOrgId, ctx.roles, and ctx.userRole for the acting user.

Do not import ../../auth/schema from the table file to reach AUTH_DB — that import used to survive compile and then fail package-mode wrangler with Could not resolve. Checking that another user is an org member belongs in an action.

before* hooks are synchronous. Return a partial object to merge into the write payload, or throw to reject — the write never executes and the request gets a structured TRIGGER_REJECTED (422) error. Audit fields (createdBy, modifiedAt, …) are re-stamped after hooks run and cannot be forged.
after* hooks run once per affected row and are awaited before the write's promise resolves. They may be async. A throw surfaces as TRIGGER_AFTER_FAILED (500) with details.committed: true — the row was already persisted (no transactions on D1), so clients must not blind-retry.
Handlers must be inline arrow/function expressions. Imports they reference are hoisted into the generated registry (src/lib/trigger-hooks.ts) with relative paths rewritten from the table file — including dynamic import("…") inside the handler. Write them as they resolve next to the table. Each table's drizzle object is auto-imported under its authored name — so this works as written:

import { eq } from 'drizzle-orm';

// ...
afterUpdate: [
  {
    name: 'syncNotes',
    handler: async ({ row, db }) => {
      await db.update(invoices)
        .set({ notes: `updated: ${row.title}` })
        .where(eq(invoices.id, row.id));
    },
  },
],

after* hooks only receive rows when the write chain used .returning() — generated CRUD routes and actions do.

Recursion

Trigger cascades are bounded in every lane:

Handler hooks: the db a hook receives is re-wrapped at depth + 1. A cascade deeper than 5 levels (a hook's write firing hooks that write again, …) throws with the table/op that exceeded the cap.
SQL triggers on D1: SQLite's recursive_triggers pragma defaults off — a trigger cannot re-enter itself, directly or through a cycle.
SQL triggers on Postgres: there is no such guard. A trigger whose write fires another trigger that writes back will recurse until Postgres aborts on stack depth. Cascading sql: triggers on Postgres are yours to keep acyclic — a when: guard on the state the trigger changes is the usual way.

Coverage — read the compile report

Handler hooks do not fire for queue handlers, cron jobs, or raw SQL. The compiler emits a per-trigger lowering report and warns at compile time when a before* handler is used as a guard — if a rule must hold for every write path, use the sql: lane and reject with RAISE(ABORT, ...) (D1) or RAISE EXCEPTION (Postgres).

Cron jobs themselves are first-class — declare them with defineSchedule and the compiler emits the Worker scheduled() handler + wrangler.toml triggers. This note is only about the table-trigger handler: lane not firing inside them.

Choosing a lane

Need	Use
Invariant that must hold universally (raw SQL included)	`sql:` with `RAISE(ABORT)` (D1) / `RAISE EXCEPTION` (Postgres)
Denormalized columns, counters, tombstones	`sql:`
Validation/derivation with application logic	`before*` `handler:`
Side-effects after a write (notify, write related rows)	`after*` `handler:`
Business flow, or anything that reads a role	`handler:` — `sql:` carries data rules only
Atomic with the write (D1 has no transactions)	`sql:` only

Planned (not yet available — declaring them is a compile error rather than a silent no-op): async: true queue-dispatched after-hooks with retries, and withOld: true previous-row access on update/delete hooks.

After-commit hooks

For typed realtime "this changed — refresh" signals, use invalidates — it rides this same after-commit model but delivers a versioned, targeted frame to subscribers.

Secondary effects — activity feeds, notification nudges, cache warmers — must never roll back or delay the primary write. Instead of hand-wrapping each leg in try/catch + executionCtx.waitUntil, declare named afterCommit hooks:

// features/guests/actions/checkIn.ts
import { z } from "zod";
import { defineAction, guests } from "../.quickback/define-action";

export default defineAction({
  description: "Check a guest in and record the arrival timestamp.",
  input: z.object({ badgePrinted: z.boolean().default(false) }),
  access: { roles: ["admin", "member"] },
  execute: async ({ db, record, ctx, now }) => {
    await db.update(guests).set({ checkedInAt: now }).where(/* … */);
    return { success: true, checkedInAt: now };
  },
  afterCommit: {
    // Named hooks run after execute returns successfully, in declared order.
    activityFeed: async ({ db, env, record, ctx }, result) => {
      await recordActivity(db, env, { verb: "guest.checked_in", /* … */ });
    },
    // blocking: true is awaited before the response (client depends on it).
    issueBadge: { blocking: true, run: async (actx, result) => { /* … */ } },
  },
});

The contract:

Hooks receive (actx, result) — the same context execute got, plus its return value.
Each hook is independently wrapped: a throw is logged as [<action>] afterCommit:<hook> failed and never affects the response or other hooks.
Non-blocking hooks (the default) ride executionCtx.waitUntil; blocking hooks are awaited before responding.
If execute throws, no hooks run. A result carrying dryRun: true also skips them (preview branches never fire side effects).
Bulk twins run hooks once per invocation with the bulk envelope as result — per-record fan-out is the hook author's job.

For effects that need mid-execute data, the context carries an imperative escape hatch with the same semantics:

execute: async ({ db, effects }) => {
  // …
  effects.enqueue("cohortRecalc", () => recalculate(/* … */));
  return { success: true };
}

Effects enqueued by an execute that later throws are dropped. The context also carries now — one ISO timestamp per invocation; prefer it over new Date().toISOString().

Triggers - Postgres-Style Table Triggers

On this page