PostgreSQL Trigger Naming Convention on Supabase

Over the past few months I worked on a project that challenged me at the data layer. The database was PostgreSQL on Supabase — a solid stack, but with friction I did not expect coming from ORMs and more opinionated databases.

Things that are trivial elsewhere — keeping updated_at current, bootstrapping related rows on insert, propagating status changes, cleaning up resources on delete — do not exist by default in PostgreSQL. There is no universal ON UPDATE CURRENT_TIMESTAMP nor high-level declarative hooks. If you want that behavior, you build it: a PL/pgSQL function and a trigger that runs it at the right point in the row lifecycle.

That scales fast. A table with basic auditing already has a BEFORE UPDATE. If you also sync related entities, enqueue side effects, or enforce invariants, you go from one to three or four triggers per table. Without a convention, the catalog fills with trg1, handle_user, set_timestamp, and functions whose names do not say which table they belong to or when they run.

That is when I started thinking about how to name both triggers and their functions — not as aesthetic preference, but as a tool so that I (and whoever reads the schema later, human or agent) can navigate without opening every PL/pgSQL body.

I treat two artifacts separately that PostgreSQL allows in one block, but that are better handled as distinct concerns in practice:

• Trigger (DDL object) · Wiring: table, event, timing, WHEN clause — {event}_{timing}_{table}
• Trigger function (PL/pgSQL body) · Logic executed by the engine — trigger_{event}_{timing}_{table}

Where:

• event: insert | update | delete
• timing: before | after
• table: exact snake_case table name (users, order_items, …)

Example — the case that pushed me to standardize: auto-updating updated_at:

-- Binding: when and where it attaches
CREATE OR REPLACE TRIGGER "update_before_users"
  BEFORE UPDATE ON "public"."users"
  FOR EACH ROW
  EXECUTE FUNCTION "public"."trigger_update_before_users"();

-- Body: what it does
CREATE OR REPLACE FUNCTION "public"."trigger_update_before_users"()
  RETURNS "trigger"
  LANGUAGE "plpgsql"
  SET "search_path" TO 'public'
  AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$;

The function gets the trigger_ prefix; the trigger object does not. That one-token difference is intentional.

Suggested repo layout (adaptable):

schemas/
  triggers/{table}.sql              ← binding DDL only
  functions/triggers/{table}/     ← one file per function
    trigger_update_before_{table}.sql

1. PostgreSQL forces triggers; names should offset the friction

When updated_at does not update itself, every table repeats the same pattern with variations. Without a convention, you end up copying Stack Overflow snippets with different names in each migration. With {event}_{timing}_{table}, the pattern is instantly recognizable: “this is the UPDATE BEFORE hook on users”, without opening the file.

2. The name follows the order I think about the problem

I read insert_after_orders as: “on INSERT, after the row is persisted, on `orders`”.

It does not mirror the literal CREATE TRIGGER … AFTER INSERT ON … word order (timing → event → table), but it matches domain mental flow: what happened → when in the row lifecycle → where. That reduces friction when debugging: stack traces, psql \dy, and the file tree all speak the same language.

3. trigger_ prefix on functions to avoid competing with exposed RPCs

In Supabase, public schema functions can be exposed via PostgREST as RPCs. Business functions use explicit verbs: create_order, get_user_by_id. If the trigger function were named like the trigger (update_before_users), pg_proc would hold two families of nearly identical names with different roles: client-callable vs engine-only.

The trigger_ prefix unambiguously signals: not an RPC; do not expose; do not call from the application layer. It is a cheap namespace inside the same schema.

4. Physical separation of binding vs body

• Triggers → wiring only (table, timing, WHEN).
• Functions → logic, side effects, calls to other functions, validation.

Concrete benefits:

• A logic change does not necessarily touch the trigger contract.
• You can list all hooks for a table in one small file.
• It scales when a table accumulates several triggers — common when PostgreSQL replaces features other engines ship built-in.

5. Predictable 1:1 mapping

Simple rule: trigger name + `trigger_` prefix = function name.

update_before_users     →  trigger_update_before_users
insert_after_orders     →  trigger_insert_after_orders
delete_before_projects  →  trigger_delete_before_projects

That removes the classic ambiguity where the trigger is trg_users_upd and the function is fn_set_updated_at. No mental translation layer.

6. Explicit names, no cryptic abbreviations

A trigger is not the place for onomastic creativity. If an AI agent or a new developer greps update_before_, they should find exactly what they expect — not ub_u_ts or "Set Updated At".

For AI agents

• Syntactic regularity — The {event}_{timing}_{table} pattern is regex-parseable; an agent can infer names without reading the full schema.
• Path inference — Given users + update + before, the file path is deterministic if the repo follows per-entity structure.
• RPC vs trigger disambiguation — The trigger_ prefix prevents an LLM from generating RPC update_before_users or confusing a trigger function with a PostgREST endpoint.
• Scoped edits — “Change the update before logic for users” narrows scope to one file and one known identifier.
• Readable diffs — Logic changes vs trigger wiring changes land in different files; impact is assessed faster.
• Repeatable patterns — The updated_at case repeats across dozens of tables with the same name; an agent learns once and applies everywhere.

Limitations worth documenting in the project:

• PostgreSQL truncates identifiers to 63 bytes. Long table names produce clipped names in the catalog. Check \df / \dy when in doubt.
• WHEN conditions are not encoded in the name; they live in the trigger DDL.
• Special schemas (e.g. Supabase auth) may need variants; document them as exceptions, not the general pattern.

For software engineers

• Catalog readability — \dy+ users and \df trigger_*users* show a coherent vocabulary.
• Useful alphabetical sort — Groups by event/timing: all update_before_* entries cluster together.
• Onboarding — The rule fits in one sentence; no separate wiki required.
• Debugging — Error function trigger_update_before_users() does not exist points straight to the expected file.
• Code review — If a PR renames the function but not the trigger (or vice versa), the mismatch is obvious.
• Migrations — DROP TRIGGER IF EXISTS update_before_users ON public.users is self-describing in git history.
• Supabase / declarative schema — Separating binding and body fits schema-as-code workflows and reviewable PR diffs.

• Keep updated_at current · update_before_{table} — No universal auto-update on timestamp columns
• Bootstrap related rows on insert · insert_after_{table} — No declarative cascade for business logic
• Enforce invariants before persist · update_before_{table} / insert_before_{table} — CHECK constraints cover less than cross-table logic
• Post-commit side effects (queues, notifications) · insert_after_{table} / update_after_{table} — No native event bus
• Cleanup on delete · delete_before_{table} / delete_after_{table} — FK CASCADE is not enough for external resources (storage, queues, secrets)

• Prose names ("Sync Audience", "Set Updated At") — Do not scale, do not grep, break in migrations
• Opaque abbreviations (trg_u_ins, fn1) — Require parallel documentation
• Same name for trigger and function without prefix — Semantic collision with RPC; PostgREST confusion
• One mega-file with all triggers — Noisy diffs, hard to review
• Heavy business logic inside the trigger — The name identifies the hook; complexity goes to helper functions

Trigger:   {insert|update|delete}_{before|after}_{table}
Function:  trigger_{insert|update|delete}_{before|after}_{table}
Rule:      function_name = "trigger_" + trigger_name