Triggers - Postgres-Style Table Triggers

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:

There is no auto-translation between the lanes: SQL bodies stay SQL, functions stay functions. Pick the lane by what the trigger must guarantee.

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'),
  },
  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 wraps your statements in a CREATE TRIGGER named qb_trg_<table>_<event>_<name> and ships it in the journaled features 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.

• Write bare, ;-terminated statements — the compiler owns the BEGIN/END frame. (It emits the frame uppercase; lowercase begin breaks remote D1's statement splitter even though local D1 accepts it.)
• 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).
• To reject a write, SELECT RAISE(ABORT, 'message'); — this is the only trigger form that blocks every write path, including raw SQL.
• SQL triggers run inside the triggering statement — the only transactional option on D1, which has no db.transaction().

Currently supported on cloudflare-d1 only; declaring sql: triggers on another database provider is a compile error.

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.

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 }) => { ... }

• 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, and 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: SQLite's recursive_triggers pragma defaults off — a trigger cannot re-enter itself, directly or through a cycle.

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 with RAISE(ABORT).

Choosing a lane

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.