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

| You write | Lowers to | Fires on |
|---|---|---|
| `sql:` | A real SQLite `CREATE TRIGGER` in your migrations | **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. |

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

```typescript
// 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 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:

```typescript
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](/define/actions/scoped-db#reaching-better-auth-tables-authdb).

- **`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:

```typescript
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)`.

> Cron jobs themselves are first-class — declare them with
> [`defineSchedule`](/platform/schedules) 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)` |
| Denormalized columns, counters, tombstones | `sql:` |
| Validation/derivation with application logic | `before*` `handler:` |
| Side-effects after a write (notify, write related rows) | `after*` `handler:` |
| 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`](/platform/realtime/named-invalidations) — 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:

```typescript
// 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:

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