I explain the architecture and how I operate it, but I don't publish exact table names, function names, endpoint paths, or policy predicates here. This system handles real customer data and real payments. If you want implementation-level detail, I'm happy to walk through it live.

Roughly 30 tables grouped by concern: customer identity and profile, vehicles and addresses owned by customers, the booking lifecycle (holds, appointments, status transitions, messaging threads), the service and pricing catalog, membership types and per-customer credit accounting, payment and order history, admin notes, notifications, verification/recovery codes, and configurable application settings.

Integrity is enforced in the database, not in application code. ~55 foreign keys with deliberate ON DELETE behaviour (CASCADE for owned children, RESTRICT where deletion would orphan a money-relevant record, SET NULL where a sweep can run without losing user-facing context). ~80 CHECK constraints lock down enum values, price ranges, and cross-column invariants. Unique constraints back the natural keys (one default address per user, one membership per user, idempotent payment session lookups).

Triggers handle three jobs only: derived-data maintenance (timestamp columns, parent-row activity tracking, empty-cart cleanup), validation that has to apply on every write path including manual SQL (per-customer caps, admin role guards, admin email domain enforcement), and operational notifications (rows written when an appointment changes so the notification dispatcher has something to read).

RLS is enabled on every table that holds user-facing data. ~95 policies across four tiers. Tier 1 is user-owned data (most tables): predicates key on the caller's auth identity matching the row's owner, so a customer can only see and modify their own records. Tier 2 is admin-gated: an admin-check helper function gates the policy, so non-admins get nothing back from a query against admin tables. Tier 3 is the public catalog (services, vehicle types, public reviews): readable by anyone, mutations restricted. Tier 4 is system metadata (notifications, redemption logs, verification records): readable by the recipient or owner, otherwise invisible.

Defense in depth: even when a stored procedure runs with elevated privilege and bypasses RLS for legitimate reasons, the procedure still validates the caller's identity in its function body. If the procedure layer were ever compromised, RLS is still the second wall behind it.

Deny-by-default is the rule. A new table starts with RLS enabled and no policies, which means no one can read or write it until policies are explicitly added. There is no path where a forgotten policy silently leaks data.

~55 procedures in total. Clients do not write directly to any table that touches money, scheduling, credits, coupons, or auth state. Every such mutation goes through a procedure that owns the business rules: availability calculation against working hours and crew capacity, hold creation with conflict checking, hold confirmation under transaction scope, reschedule under row locks, credit and coupon redemption with idempotency, verification code issuance and consumption with HMAC hashing.

Concurrency is handled with explicit row-level locking on contended state. After deploy, two race conditions were caught (concurrent reschedule and concurrent credit redemption could each interleave); both were patched within a day by adding the appropriate locks, and the pattern is now applied uniformly across every procedure that mutates contended rows.

Authorization is enforced inside the procedure body, not just at the gateway. Customer-facing procedures verify the caller owns the data they're touching. Admin-facing procedures call the admin-check helper before doing anything destructive. Service-role calls (from edge functions) are recognized and allowed to bypass per-customer caps where that's the legitimate intent (e.g., admin onboarding a customer over the phone), but never to bypass identity checks.

Procedures run with elevated privilege and explicit `EXECUTE` grants to the right roles. Search-path is locked down on every function so extension calls (cryptographic helpers especially) resolve predictably across environments.

~30 edge functions, grouped by what they exist to do that the database can't: anything that needs a server-side secret, a signed external call, or a multi-step transaction across more than one external system.

Categories: payment session creation against the payment provider, the inbound payment webhook (signature-verified, idempotent on the provider's session ID, sources foreign-key values from the database rather than trusting webhook metadata even after signature verification), transactional email dispatch, code-based verification and password recovery flows, admin onboarding flows that create auth users and roll back on partial failure, scheduled reminder jobs invoked by a database cron with a service-role bearer, and address/geocoding proxies that keep the third-party API key off the client.

JWT verification is configured per function. Functions that serve authenticated users have gateway-level JWT verification on. Functions that legitimately serve anonymous callers (verification code issuance, the payment webhook) have it off and do their own verification in the function body: signature header for the webhook, code-against-hash for the verification flows, service-role bearer match for the cron-invoked jobs. There is no function that's both anonymous-callable and unverified.

Service-role usage is scoped: edge functions hold both an authenticated client (RLS-respecting) and a service-role client (RLS-bypassing) and use the latter only for the specific operations that need it. The default is the authenticated client.

All schema lives as timestamped SQL files in version control. Roughly 25 migrations to date, starting with one large baseline that established the table set, the procedure layer, and the policy baseline in a single reviewable file, then a steady sequence of incremental migrations: concurrency hardening, configurable per-customer caps, admin email domain enforcement, payment-link plumbing, status-string normalization, notification deep-links, the membership cap redesign, the addons catalog.

Forward-only by convention. Rollbacks are new migrations that revert intent, not destructive down files. The reasoning is operational: a down migration is itself a forward operation against a database that might already have data shaped by the migration being reverted, so writing one ahead of time is a fiction. When something needs to be undone, it gets a new migration that's reviewed like any other change. In a true emergency the database dashboard can edit SQL directly, but that goes back into a migration the same day so the file history stays the source of truth.

CI applies migrations on merge: push to the staging branch applies them to the staging Postgres project; push to main applies them to the production project, in lexicographic order, idempotently (the CLI tracks applied migrations and no-ops anything already there). The production migration job posts a commit status, and the frontend deploy waits on that status before promoting. If the migration job fails, the old frontend stays live against the old schema and there is never a window where a UI is shipped against a half-migrated database.

Roughly 40 indexes, designed against the actual hot paths from the procedure layer. Composite indexes back the availability and conflict-detection queries (the busiest read path in the system). Partial indexes back the constraints that only need indexing in one state: a unique default per customer, an active-only featured catalog lookup, an external-ID lookup that's only relevant when the external ID is present. Single-column B-tree everywhere else.

No GIN/GIST yet — there's nothing in the workload that needs full-text or trigram search at scale. If that changes, the index choice is a per-query decision, not an upfront one.

Email isn't a side feature; it's the second user interface for the system. The full set of automated comms: booking confirmation when payment succeeds, an appointment reminder a day out, change notifications whenever an appointment is mutated by either party, in-thread messages between customer and admin, verification and password recovery codes, coupon delivery, and the payment provider's own receipts and invoices (configured server-side so they ship with the business's tax info baked in).

The change-notification loop is the interesting piece. When an appointment is mutated (a customer reschedules, an admin updates the time or status, anyone sends a message in the appointment thread), a database trigger writes notification rows describing what changed and who needs to know. The frontend then calls a dispatcher edge function that looks up all pending notifications for that appointment, validates the caller actually has access to it (their own appointment or admin), and fans them out through the email sender. So if I change something on your appointment as admin, you get an email. If you reschedule it, I get an email. If we exchange messages in the thread, both sides get notified the next time either of us hits the dispatcher. The trigger writes the intent; the dispatcher gates it on identity; the sender renders the right template. Three layers, each with one job.

Booking confirmation runs on the payment webhook, not on hold creation, so a customer only gets a 'you're booked' email after their payment actually settles. Reminders run as a database-cron job that scans for appointments approaching the next-day window and dispatches once per appointment. Both go through the same email-sender function as the change notifications, just with different templates and different triggers.

Payment receipts and invoices come from the payment provider directly, but the customer record is configured server-side at checkout-session creation: invoice settings carry the business's tax number both as a custom field and as an invoice footer line, so every receipt and invoice the provider issues for that customer is properly tax-attributed without anyone having to remember to add it. The configuration helper is idempotent (it doesn't duplicate the field if it's already there) and preserves any existing footer the customer has accumulated.

All transactional email is best-effort from the application's perspective: the database state is the source of truth, and an email failure logs and continues rather than blocking the underlying operation. A failed reminder doesn't undo a successful booking; a failed confirmation doesn't roll back a successful payment. Recovery is via the same dispatcher that flushes pending notifications, which means a missed email is a re-call away rather than a re-engineering job.

Audit is embedded in operational tables rather than a separate audit log: notification rows are written by triggers when appointments change, redemption logs are written for every coupon use, verification records preserve issued/consumed timestamps. The query path to reconstruct what happened is a join against tables the application already cares about, not a parallel ledger to keep in sync.

Backups are managed by Supabase with point-in-time recovery available on the production project. Restore drills against a staging branch are the next thing on the operational checklist; today the backup posture is provider-managed and the restore path has been read but not run end-to-end.

Secrets management is application-side: external API keys (payment provider, email provider, geocoding provider) live in the edge function runtime, never in the database, never in the repo. The database stores opaque external IDs (customer IDs, session IDs) but no provider credentials.