By Sanjay Gupta 4 min readEnforcing Business Rules in Postgres, Not the Frontend
Duleko's work lifecycle - pending, accepted, confirmed, completed - is modelled as Postgres triggers with Row Level Security on every table, so a modified client can't skip a step. Here's why that decision, plus the outbox pattern behind its notifications.
The easy way to build a marketplace's work lifecycle - pending, accepted, confirmed, completed - is to check permissions in the API layer and trust the frontend to only show the right buttons. It works, right up until someone calls the API directly with a modified request, or a future contributor adds a new endpoint and forgets the check. On Duleko, a bilingual local-skills marketplace for Nepal, I moved that logic somewhere a forgotten check can't happen: the database itself.
Modelling the lifecycle as a state machine, in SQL
A job on Duleko moves through four states: pending, accepted, confirmed, completed. Only the worker can accept a pending job. Only the employer who posted it can confirm that it's done. Those two rules sound like `if` statements you'd write in an Express route handler - and that's exactly the trap. An `if` statement in one route is easy to forget to repeat in the next one.
Instead, the transitions are Postgres triggers, and every table carries Row Level Security policies that check `auth.uid()` against the row before any read or write is allowed. A worker's UPDATE on a job they don't own doesn't get rejected by application logic - it doesn't match a single row, because RLS filters it out before the trigger even runs. The rule is enforced at the same layer as the data, which means every future client, script or admin tool that talks to the database inherits it automatically.
Locking contact info behind one function, not scattered checks
Phone numbers are the other place trust matters: showing a worker's number to a browsing guest is a privacy leak, and Duleko needed that gate to be impossible to bypass by accident. So phone numbers live in their own table, hidden behind a single `can_view_contact()` policy function, and chat is routed through a matching `can_chat_with()` check.
The payoff of putting it in one function instead of a WHERE clause repeated across queries: there is exactly one place to audit, and exactly one place to change if the rule ever needs to loosen (say, letting an employer see a number after the job is accepted rather than confirmed). Every query that touches contact data goes through it, because RLS means there is no other path to that row.
The outbox pattern: notifications that can't turn into spam
Notifications are the other place a lifecycle change matters - a worker accepting a job should tell the employer, by email and, if they've opted in, SMS. The naive version calls the email/SMS provider directly inside the request that changes the job's state. That's a bad idea the first time a burst of activity (a popular job category, a retry storm) turns into a burst of outbound messages, or the provider is briefly down and the whole request fails because of it.
Duleko writes each notification-worthy event to an outbox table instead, inside the same transaction as the state change, so the notification can never be recorded without the event actually happening (or vice versa). A Supabase edge function drains that outbox on a `pg_cron` schedule, with per-sender rate limiting on the way out. The lifecycle change and the notification are decoupled: one is instant and transactional, the other happens on a schedule and can be throttled without touching the request path that users are waiting on.
What free-tier constraints taught me about the design
Duleko runs entirely on free tiers, which ruled out an always-on worker process for the outbox drain - so `pg_cron` inside Postgres does that job instead of a separate service. That constraint turned out to simplify the architecture rather than compromise it: there's one fewer moving part to deploy, monitor and pay for, and the schedule lives next to the data it operates on.
The same free-tier pressure pushed skills, all 77 districts, and user-facing copy into the database as rows instead of code. A new skill category or a copy change ships as a data update, not a deploy - useful for a bilingual (English/नेपाली) product maintained by one person.
None of this is exotic - triggers, RLS and outbox tables are decades-old ideas. What changed my mind about using them here was realising that 'the frontend won't show that button' and 'the database won't allow that write' are very different guarantees, and only one of them survives a bug, a bypassed client or a future contributor who didn't read the whole codebase. Push authorisation as close to the data as it can go, and the UI only has to reflect it - not enforce it.