Multi-tenant security: RLS is the boundary, not the app code

There are roughly three patterns for multi-tenant SaaS:

1. Database-per-tenant. Strong isolation. Operationally expensive — you're running thousands of databases.

2. Schema-per-tenant. Cheaper, but DDL changes get gnarly.

3. Shared-schema with tenant_id columns. Cheapest. The trap: you're relying on application code to filter by tenant_id. One missing WHERE clause is a cross-tenant data leak.

AutoDealerPro went with shared-schema, but with a twist: every tenant-scoped table has Postgres Row-Level Security on it. The database itself refuses to return rows that don't match the active tenant_id, regardless of what the app code does.

This sounds simple but it's a mindset shift. The boundary is no longer "remember to add WHERE tenant_id = ?". The boundary is the database. The application code can't bypass it because the queries return nothing.

How it works mechanically:

Every query goes through a connection that has SET adp.active_tenant_id = '<uuid>' set. Every tenant-scoped table has a policy: USING (tenant_id = current_setting('adp.active_tenant_id')::uuid). If you forget to set the session var, you read zero rows. If you set the wrong one, you read zero of the wrong rooftop's rows.

The Drizzle wrapper makes this enforced — `inTenantTx(ctx, fn)` opens a transaction with the right session var set and tears it down after. If you try to query without going through that helper, the query returns empty.

There's a small performance cost (per-query session var lookup) but it's under 5% in our benchmarks. The trade is well worth it: a single missed WHERE clause goes from "bug → potential data breach → SOC 2 finding" to "bug → empty query result → noisy 500".

The full schema is in packages/database/src/schema/ if you want to read it. Every table that has tenant_id has a policy. Audit logs cross-cut: they're append-only with their own RLS so a tenant can read their own audit trail but no one else's.