Ko-fi: https://ko-fi.com/s/006956d6b5
Every B2B SaaS ends up needing the same dozen pieces in its data layer: multi-tenancy, roles and permissions, an audit log, a billing mirror, a job queue, outbound webhooks, an events table for analytics. And almost every team builds each one twice — because the first version compiles, ships, and then quietly breaks at scale.
I wrote up the failure modes I keep seeing (and the Postgres 16 patterns that fix them).
Multi-tenancy: the forgotten WHERE tenant_id
Pooled multi-tenancy — one database, a tenant_id column on every table — is the model that actually stays operable past a few hundred tenants. The danger is a single query that forgets WHERE tenant_id = $1 and leaks another tenant's data.
Row-Level Security turns that from a breach into a non-event:
ALTER TABLE resources ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON resources
USING (tenant_id = current_setting('app.current_tenant')::uuid);
Set app.current_tenant once per request and every query is constrained automatically. The matching index leads with tenant_id: (tenant_id, created_at DESC) — because in a pooled model every hot query filters by tenant first.
The job queue race nobody tests for
The naive "SELECT a pending job, then UPDATE it to running" has a race: two workers grab the same row. Under load you run the same job twice. The fix is one statement:
UPDATE jobs SET status = 'running', attempts = attempts + 1
WHERE id = (
SELECT id FROM jobs
WHERE status = 'pending' AND scheduled_at <= now()
ORDER BY priority DESC, scheduled_at
FOR UPDATE SKIP LOCKED
LIMIT 1
) RETURNING *;
FOR UPDATE SKIP LOCKED lets N workers pull disjoint rows with zero collisions — a real queue, no Redis, backed by a partial index on (priority DESC, scheduled_at) WHERE status = 'pending'.
The audit log that's unqueryable at 50M rows
An audit trail grows forever. If it's a plain table, retention means a slow DELETE and endless vacuum. Declarative monthly partitioning makes retention a DROP TABLE of last year's partition — instant. The tax you accept: a partitioned table's primary key must include the partition key, so lookups need (id, created_at), not id alone.
The Stripe webhook that double-charges
Stripe delivers events at-least-once and sometimes out of order. A handler that just applies each event will eventually apply a stale subscription.updated after a newer one. The mirror needs a stripe_event_id UNIQUE for idempotency and a last_event_at guard so an older event can't overwrite a newer state.
Why an AI's first attempt isn't this
Ask a model for "a multi-tenant schema with RBAC and an audit log" and you get something plausible — and missing the partial unique index that lets you re-create a soft-deleted row, the BRIN index that keeps the events table from bloating, the deny-wins resolution in the permission check, the idempotency key on the webhook. Those are the parts you only add after the second rewrite.
The pack
I packaged the iterated versions: 12 Postgres 16 schemas, each with schema.sql + Drizzle + Prisma, up/down migrations, seed data, and a queries.md with EXPLAIN notes — plus an ARCHITECTURE.md showing how the 12 layer into one backend and a DECISIONS.md for the cross-schema trade-offs. Every schema + seed is verified to load on a clean Postgres instance; core PG16 only, no extensions.
If you're starting a B2B SaaS and want the "I'll just adapt these and move on" reference: https://ko-fi.com/s/006956d6b5
Top comments (0)