Triggers — Postgres trigger catalog
Triggers — Postgres trigger catalog
Mirrors
surfc/supabase/migrations/*.sql. If they drift, the SQL wins. Last verified against migrations0001–0021on 2026-05-15; the two sync triggers below (0050/0051) added + verified 2026-07-02. Siblings: Tables · Functions. Architecture narrative lives at Data Architecture.
This file lists every Postgres trigger we own: the one on auth.users (the only auth-schema trigger Surfc has), the one on public.ai_usage_daily, and (SUR-739/740) the two sync triggers — t01_lww_guard + t02_change_seq — that fire on each of the 8 synced tables. Each trigger entry cross-links to its function in Functions and to the table it fires on in Tables.
A note on naming history: the auth.users trigger has been called on_auth_user_created since 0007. The function it executes has changed — match_waitlist_on_signup until 0020, handle_new_auth_user since. The trigger name was preserved for migration safety. If a Linear ticket older than SUR-362 refers to “the match_waitlist_on_signup trigger”, it’s pointing at this one — read the linked SQL, not the ticket text.
on_auth_user_created
Materialises a user_profiles row on every confirmed Supabase Auth signup. Without this trigger, a fresh auth.users row would have no profile, and the getResolvedMonthlyLimit quota path + the user-profile-joined RLS reads would all fail closed. Idempotent — ON CONFLICT (user_id) DO NOTHING in the function body guards against duplicate INSERTs from the legacy approve-waitlist overlay path.
| Field | Value |
|---|---|
| Table | auth.users |
| Timing | AFTER |
| Event | INSERT |
| Granularity | FOR EACH ROW |
| Function | public.handle_new_auth_user() |
| Trigger name defined in | 0007_waitlist_requests.sql |
| Current function defined in | 0020_generic_profile_creation_trigger.sql (replaced match_waitlist_on_signup via DROP TRIGGER … CREATE TRIGGER) |
Side effects. One row inserted into public.user_profiles per new auth user (assuming no pre-existing row with the same user_id). The row inherits column DEFAULTs for everything except name, email, role, member_since, which the function sets explicitly:
name←COALESCE(NULLIF(NEW.raw_user_meta_data->>'full_name', ''), split_part(NEW.email, '@', 1))email←NEW.emailrole←'user'member_since←EXTRACT(EPOCH FROM NEW.created_at)::int4
All other columns take their table DEFAULTs: month_limit = 50, month_usage = 0, user_tier = 'free', tier_started_at = now(), image_storage_bytes_used = 0, Stripe columns NULL, etc.
No downstream RPC calls or Edge Function invocations — the trigger is purely a row insert. The approve-waitlist Edge Function still upserts as a best-effort overlay on top for legacy admin-UI approval flows; SUR-369 tracks final retirement.
History. This is one trigger that has gone through three function bodies:
| Migration | Function body | Behaviour |
|---|---|---|
0007 | match_waitlist_on_signup v1 | Back-fills waitlist_requests.user_id for approved-and-signed-up users. |
0009 | match_waitlist_on_signup v2 | v1 + also inserts the matching user_profiles row when an approved row exists. |
0011 | match_waitlist_on_signup v3 | v2 + pinned search_path = public, auth, pg_temp (live-bug fix on the Dashboard Invite User flow). |
0020 | handle_new_auth_user | Replaces the waitlist-coupled body entirely. Always inserts a user_profiles row regardless of waitlist state. Legacy function dropped. |
trg_ai_usage_month_delta
Keeps user_profiles.month_usage in sync with ai_usage_daily so the Profile UsageBar reads live numbers. Drives the percent_use generated column the UI surfaces. Real-time quota enforcement runs against ai_usage_daily directly in getMonthlyUsage — this trigger feeds the display snapshot, not the quota gate.
| Field | Value |
|---|---|
| Table | public.ai_usage_daily |
| Timing | AFTER |
| Event | INSERT OR UPDATE OR DELETE |
| Granularity | FOR EACH ROW |
| Function | public.apply_ai_usage_month_delta() |
| Originally defined in | 0013_capture_apply_ai_usage_month_delta.sql — AFTER INSERT OR UPDATE only, additive math. |
| Current shape defined in | 0018_apply_ai_usage_month_delta_self_healing.sql — extended to DELETE, recompute under per-user advisory lock (SUR-330). |
Side effects. One UPDATE on public.user_profiles per fired trigger, setting month_usage to SUM(request_count) over the calling user’s current calendar-month rows of ai_usage_daily. The recompute is held under a per-user pg_advisory_xact_lock keyed off the user’s UUID so concurrent same-user writes serialise — see the apply_ai_usage_month_delta section for the full concurrency rationale.
Why this fires AFTER and not BEFORE. The recompute reads the post-write state of ai_usage_daily — running BEFORE would miss the just-written row.
Why both INSERT/UPDATE/DELETE. INSERT and UPDATE cover the steady-state writes from upsert_ai_usage. DELETE was added in 0018 so any future cleanup job that removes ai_usage_daily rows (none today — the proxy only inserts and upserts) would correctly decrement the snapshot. The earlier 0013 shape skipped DELETE, which would have allowed month_usage to drift positive after a cleanup.
t01_lww_guard (SUR-740)
Server-side last-write-wins guard. Rejects an UPDATE whose updated_at is strictly older than the stored row, so a delayed / replayed / concurrent client flush can’t overwrite a newer server row — the server-side complement to braird-core’s client-side outbox rebase (SUR-736). Compares, never stamps: updated_at stays the client’s edit time, so offline last-write-wins is preserved — this is deliberately not a server-time trigger. Equality passes (a same-updated_at re-upsert is a harmless no-op; a pre-landing audit confirmed no client write path sends an older updated_at).
| Field | Value |
|---|---|
| Tables | all 8 synced: books, notes, custom_ideas, note_links, lenses, collections, collection_memberships, note_signals |
| Timing | BEFORE |
| Event | UPDATE |
| Granularity | FOR EACH ROW |
| Function | public.sync_lww_guard() — SECURITY INVOKER (reads NEW/OLD only, no privileged op) |
| Defined in | 0050_sur740_lww_guard.sql |
Side effects. A rejected stale UPDATE returns NULL → the row’s update is cancelled (the statement still succeeds, no error), leaving the newer stored row intact. Mirrors the client’s silent tie-keeps-local, so a racing flush sees a 2xx and simply doesn’t clobber.
Why BEFORE, and why named t01. BEFORE so it can cancel the row before it’s written. t01_* sorts before t02_change_seq — Postgres fires same-event row triggers alphabetically — so a write the guard rejects never reaches the change_seq stamp and therefore can’t bump the visibility watermark (no spurious re-delivery to other devices).
Caveat. A service-role backfill that intentionally writes an older updated_at is silently skipped too — bump updated_at, or drop the trigger for the backfill window.
t02_change_seq (SUR-739, commit-ordered by SUR-743)
Stamps a server-assigned, monotonic change_seq on every INSERT and UPDATE — the visibility watermark the incremental-pull cursor is keyed on, distinct from the client-authored updated_at used for LWW. A cursor over change_seq delivers a row whenever the server made it visible, closing the delayed/offline-flush hole (a row whose updated_at predates a puller’s cursor but became visible after it) and enabling keyset pagination (SUR-652).
Commit-ordered, per user (SUR-743). The original 0051 implementation stamped from a per-table SEQUENCE (nextval) — but nextval allocates eagerly and non-transactionally, so sequence order is not commit order. A writer holding a higher change_seq could commit before one holding a lower value, letting a keyset puller advance its cursor past the lower value and miss that row permanently (the same skip hole the updated_at cursor had). 0052 replaces the sequences with one per-user counter row in public.user_change_seq, bumped inside this trigger via INSERT … ON CONFLICT DO UPDATE … RETURNING. The upsert’s row lock — belt-and-braced by a per-user pg_advisory_xact_lock — is held to COMMIT, so per user allocation order == commit order and the keyset cursor is skip-safe by construction. change_seq is now allocated per user; each table’s values are a strictly-increasing subsequence of that user’s counter, and the per-table client cursors (meta.lastSeq:<t>) are unaffected because keyset paging tolerates gaps.
| Field | Value |
|---|---|
| Tables | all 8 synced (as above) |
| Timing | BEFORE |
| Event | INSERT OR UPDATE |
| Granularity | FOR EACH ROW |
| Function | public.sync_stamp_change_seq() — SECURITY DEFINER (bumps public.user_change_seq for NEW.user_id under a per-user advisory lock; server-only) |
| Defined in | 0051_sur739_change_seq.sql (column · index · trigger) · 0052_sur743_commit_ordered_change_seq.sql (commit-order counter) |
Side effects. Bumps the caller’s user_change_seq.seq and stamps NEW.change_seq with the new value. change_seq is server-only ordering metadata — never in any client/core upsert payload, so it doesn’t alter the synced column set. Because t01 fires first, a guard-rejected stale UPDATE never reaches here — no lock taken, no seq consumed, watermark unbumped.
Why BEFORE. It must set NEW.change_seq before the row is written (and before the NOT NULL check 0051 added).
Counter table. public.user_change_seq (user_id PK, seq bigint) is service-role-only (RLS on, no client policy) and written only by this SECURITY DEFINER function. It was seeded to each user’s GREATEST change_seq across all 8 synced tables so post-migration values stay strictly above 0051’s backfill. The rollback recipe (restore the nextval function + recreate the per-table sequences) is in the 0052 header.
Triggers we do not own
For completeness, the only triggers anywhere in our schema that aren’t catalogued above are Supabase-managed internal triggers on auth.* and storage.* (signup confirmation, storage object ownership, etc). They live in the platform and are not part of Surfc’s surface area.
If a future migration adds a trigger we own, add it here and to the table’s “Triggers fired on this table” line in Tables.