Table triggers

A table trigger is a per-table Deno function that runs on Postgres writes — INSERT, UPDATE, DELETE — routed through the functions-svc policy gate. It has a BEFORE phase that can allow, deny, or modify the write, and an AFTER phase for side effects once the row is committed. Unlike Mongo policies, triggers are opt-in per table: a table with no trigger configured behaves normally and the gate is a passthrough.

How it works

On the Triggers page you switch on, per table, which operations (INSERT/UPDATE/DELETE) and which phases (BEFORE/AFTER) fire. When an enabled write arrives, the gate calls one shared Deno function named _table_policy with a JSON payload describing the operation. One function handles every table, operation, and phase — you branch on table, operation, and timing inside it.

• BEFORE is synchronous. Its return value decides the write: allow it, deny it (the caller gets 403), or fold extra columns onto the row before it reaches PostgREST.
• AFTERis fire-and-forget. It runs in the background after the row commits; the caller's HTTP response has already gone out and the return value is ignored. Use it for webhooks, audit logs, or search indexing.

How this differs from RLS

RLS is SQL: Postgres policies that control row visibility on both reads and writes, enforced on every query including raw SQL. Triggers are Deno: programmatic writehooks for validation, derived fields, and side effects. They are complementary — use RLS for row-level ownership and access (it can't be bypassed), and triggers for logic that's awkward in SQL: shaping the incoming row, calling out to another service, or stamping server-set fields.

The context (what you receive)

Read it with await req.json() inside Deno.serve. The platform hands the function a raw Request whose JSON body matches this shape:

interface TablePolicyContext {
  schema: string;                  // always 'public'
  table: string;                   // the table being written, e.g. 'notes'
  operation: 'INSERT' | 'UPDATE' | 'DELETE';   // compare against TableOp.*
  timing: 'BEFORE' | 'AFTER';                  // compare against Timing.*

  new_row: Record<string, unknown> | null;
  //   INSERT -> the full row being inserted
  //   UPDATE -> ONLY the columns being changed (the patch), not the whole row
  //   DELETE -> null
  //   AFTER  -> the committed row(s) returned by PostgREST

  old_row: Record<string, unknown> | null;     // always null (no pre-image is fetched)

  filter: Record<string, unknown>;
  //   the URL query as an object; ?id=eq.123 -> { id: 'eq.123' }

  user_id?: string;                // signed-in user's id (from their JWT), or undefined
  user_email?: string;
  role: 'anon' | 'service_role';   // 'anon' = ich_pub_ key, 'service_role' = ich_admin_ key
}

role is anon for the publishable key and service_role for the secret key. Always use user_id for identity — never values from new_row, which the client controls. old_row is always null (the stored row is not loaded, to keep the gate cheap); for row-level ownership rules use RLS, which Postgres enforces on every read and write.

The decision (what you return)

Only meaningful for timing === 'BEFORE' — for AFTER the return value is ignored.

type TablePolicyResult =
  | { allow: true }                       // let the write through unchanged
  | { allow: true; merge_row?: { ... } }  // force these columns onto the row
  | { allow: false; deny_reason?: string };  // block it — caller gets HTTP 403

On INSERT, merge_row is merged onto every row in the batch (PostgREST sends inserts as an array). On UPDATE, it's merged onto the patch. It is ignored on DELETE. Merged values are applied afterthe client's data, so they win — use this to stamp server-controlled fields like created_by.

Starter example

On the first trigger save the platform auto-creates _table_policy with this starter (the function name is platform-reserved — the leading underscore is off-limits to the regular Functions editor). Edit it from Project → Triggers → the code editor.

Deno.serve(async (req) => {
  const { table, operation, timing, new_row, user_id, role } = await req.json();

  // ── BEFORE: validate and/or shape the row before it's written ────
  if (timing === Timing.BEFORE) {
    // Your own backend (secret key) usually skips per-user checks.
    if (role === "service_role") return Response.json({ allow: true });

    if (operation === TableOp.INSERT) {
      // Require a signed-in user, then stamp who created the row.
      if (!user_id) {
        return Response.json({ allow: false, deny_reason: "sign in to create rows" });
      }
      return Response.json({ allow: true, merge_row: { created_by: user_id } });
    }

    if (operation === TableOp.UPDATE) {
      // Stamp who last edited it (new_row holds only the changed columns).
      return Response.json({ allow: true, merge_row: { updated_by: user_id } });
    }

    if (operation === TableOp.DELETE) {
      // Example: block deletes outright — delete this line to allow them.
      return Response.json({ allow: false, deny_reason: "deletes are disabled" });
    }

    return Response.json({ allow: true });
  }

  // ── AFTER: react to a committed write (does NOT block the caller) ─
  // The write already happened. Example — notify an external service:
  //
  //   await fetch("https://hooks.example.com/row-changed", {
  //     method: "POST",
  //     body: JSON.stringify({ table, operation, new_row }),
  //   });

  return Response.json({ ok: true });
});

TableOp (.INSERT/.UPDATE/.DELETE) and Timing (.BEFORE/.AFTER) are real runtime globals injected by the worker — comparing against them works at runtime, and the editor types catch typos at edit time. No imports are needed.

Where the code lives & how to deploy

Open Project → Triggers. The sidebar lists your tables; for the selected table you toggle the operation and timing checkboxes and click Enable policy (or Update policy). The shared _table_policy function — auto-created on first save — is edited in the dedicated code editor on the same page. Both the per-table config rows (auth.table_policies) and the function code (auth.functions) are stored in your project database. Saving either restarts the functions container (~1s) so the change takes effect. Remove policy drops the config for that table; writes then bypass the gate again.

Mongo: AFTER triggers

Mongo has the parallel concept, but AFTER-only. On the Mongo Triggers page you enable per-collection operations (insert/update/delete); successful writes then fire a shared _mongo_after Deno function in the background (fire-and-forget, return value ignored). There is no BEFORE phase here — BEFORE-style validation and authorization for Mongo is the separate Mongo policy layer. The _mongo_after payload carries the operation, the op result (with result.doc for single-doc ops), the raw request body, and the caller identity, so you can re-query by filter when the result doesn't already include the document.

Limits

A trigger is an Edge Function, so it's bound by your plan's function limits — the per-invocation timeout and container memory cap: