Database functions and triggers: pushing logic into Postgres itself
Every other domain in this curriculum's backend coverage has assumed business logic lives in application code — a Django view, an Express route handler. Postgres functions and triggers are a real, different architectural choice: logic that lives inside the database itself, and Supabase leans on this more than most teams' prior experience has prepared them for.
Advanced
3 min read
A Postgres function: a reusable piece of logic, written in SQL (or PL/pgSQL)
CREATE FUNCTION calculate_order_total(order_id UUID)RETURNS NUMERIC AS $$ SELECT SUM(quantity * unit_price) FROM order_items WHERE order_items.order_id = calculate_order_total.order_id;$$ LANGUAGE SQL;SELECT calculate_order_total('abc-123');
A database function is logic defined and stored inside Postgres itself, callable from SQL directly — genuinely similar in spirit to a regular application-code function, just living and executing inside the database rather than in an API server. PL/pgSQL (Postgres's own procedural extension to SQL) supports real control flow — conditionals, loops, variables — for logic too complex for a single SQL query to express cleanly.
Triggers: functions that run automatically, in response to a table event
CREATE FUNCTION update_updated_at()RETURNS TRIGGER AS $$BEGIN NEW.updated_at = NOW(); RETURN NEW;END;$$ LANGUAGE plpgsql;CREATE TRIGGER set_updated_atBEFORE UPDATE ON documentsFOR EACH ROWEXECUTE FUNCTION update_updated_at();
A trigger attaches a function to a table event (before/after insert, update, or delete) — this example guarantees updated_at is always correct on every single update, from any code path (the auto-generated REST API, a direct SQL connection, an Edge Function, a different application entirely) without a single application developer needing to remember to set it manually, anywhere, ever.
Why this is the same underlying principle RLS already established, applied to business logic generally
Lesson 2's entire argument for RLS was that enforcement living in the database, rather than in application code every developer has to remember correctly, is structurally stronger — triggers and functions generalize this exact principle beyond access control to any invariant a team wants guaranteed regardless of which code path touches the data. A trigger that validates a business rule (an order's total can't go negative, a status transition must follow an allowed sequence) enforces it for every access path simultaneously, the same structural guarantee RLS provides for read/write access specifically.
The real trade-off: logic inside the database is harder to test, version, and debug the way application code is
Software Engineering Fundamentals' own domain covered testing and code review as disciplines built around application code — version control, a test runner, a familiar debugger. Database functions and triggers live in a genuinely different world: versioning them means tracking them carefully in migrations (lesson 9), testing them means writing SQL-level tests rather than reaching for a familiar unit-testing framework, and debugging an unexpected trigger side effect means remembering a trigger exists at all, since it runs invisibly on every write with no line of application code calling it directly.
When this trade-off is genuinely worth it, and when it isn't
A correctness guarantee that genuinely must hold regardless of which application, language, or direct database access touches the data (an audit timestamp, a hard invariant a business rule truly cannot violate under any circumstances) is a strong case for pushing it into the database. Ordinary business logic that's naturally expressed in application code, well-tested by a familiar framework, and only ever touched through one application anyway gains little from the added debugging and versioning friction of living inside Postgres instead — this is a real architectural decision, not a default to reach for on every table.