# PostgreSQL Trigger Naming Convention on Supabase

PostgreSQL won't auto-update timestamps or clean up related rows — you wire that with triggers. Here's a naming convention that keeps the catalog readable for you and for AI agents on Supabase.

## Context: why I ended up thinking about this

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.

## The convention I adopted

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:

```sql
-- 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):

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

## Why I use this syntax (opinion)

### 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**.

```text
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".

## Advantages (with criteria)

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

## Typical use cases (where the convention pays off)

- **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)

## Anti-patterns I avoid

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

## Quick reference (cheat sheet)

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