Build Multi-Tenant Auth & RBAC Core on nextjs-postgres

Every B2B SaaS eventually needs the same spine — orgs, roles, invites, sessions, and a guarantee that tenant A can never see tenant B's data — and it's the code teams reliably get wrong the first time, usually by finding out in a security review or a customer's pentest. This pack builds it: DB-backed sessions, multi-org membership with owner/admin/member roles, a full invite lifecycle, and Postgres row-level security as the actual enforcement wall, not just an app-layer check that hopes nobody forgets a `WHERE` clause. The receipt proves it: a 39-check, ~105-assertion verify suite — including cross-tenant probes run directly against the database as the app's own restricted role — has to pass before this pack ships.

Example use cases

• **A solo founder** turning a single-user side project into a real team plan — adding "invite your team," role-gated admin controls, and per-org data before their first paid pilot.
• **An agency** shipping the same B2B admin panel to multiple clients, each client isolated as its own org on shared infrastructure, without operating a separate database per customer.
• **A dev-tools startup** adding organization support ahead of a security review, where "prove tenant data can't leak" is a question an enterprise buyer's security team will actually ask.
• **A vertical SaaS team** (scheduling, ticketing, ops tooling) forking the pack's `notes` exemplar resource into their real per-tenant table — the org-scoped CRUD, permission checks, and RLS policies are already worked out; they copy the pattern instead of re-deriving it.

Scale envelope

Derived from the shipped architecture, not aspiration — and labeled as an engineering estimate, since it isn't load-tested at this pack's price point:

• **Per-query cost of enforcement:** every tenant-scoped policy resolves through a `SECURITY DEFINER` helper (`is_org_member`) doing a single primary-key-indexed lookup on `org_members(org_id, user_id)` — not a join, not a scan. `org_id` is denormalized onto every tenant table specifically so policies never need one. In practical terms, RLS enforcement adds the cost of one indexed lookup per query, not a cost that grows with your data.
• **Why the pooling design matters at scale:** identity is set with `set_config('app.user_id', ..., true)` inside an explicit transaction — `is_local=true` means it dies at COMMIT/ROLLBACK. That's what makes this safe under **transaction-mode connection pooling** (PgBouncer, Neon's pooler), which is the standard way to serve far more concurrent app requests than Postgres's raw connection limit allows. A session-scoped `SET` — the naive version — would leak context between requests sharing a pooled connection and simply doesn't work under transaction pooling. This design does.
• **Estimate:** a single small managed Postgres instance (roughly the smallest paid tier on Railway or Neon — shared vCPU, a few hundred MB–1GB RAM, fronted by a transaction-mode pooler) should comfortably serve low thousands of orgs and tens of thousands of users at this schema's normal CRUD load. This is an estimate based on the indexing and policy shape, not a benchmark result.
• **Where the first ceiling actually is:** not RLS policy cost — it's the same two things that limit any single-instance Postgres app: raw connection count (before you're pooling) and general write throughput/vacuum overhead as `org_members` and your tenant tables grow. Nothing about the RLS/GUC design imposes a lower ceiling than plain Postgres would have anyway.
• **Upgrade path:** the standard Postgres playbook, unchanged by this pattern — bump instance size, add a read replica for read-heavy endpoints (member lists, notes lists), and only at genuinely large single-tenant volume consider partitioning a table like `notes` by `org_id`. Because `org_id` is already denormalized onto every tenant table, this schema is also a reasonable starting point for sharded/distributed Postgres (e.g. Citus-style) if you ever outgrow a single instance — that's a distant follow-on, not something this pack implements.

**What it costs to skip the pack (modelled, not measured):** building this spine yourself the way most people actually work — one agent on a top-tier model, no cheap-tier routing, no parallel subagents, no prompt-caching discipline — models out at roughly **$60–245 in API tokens, likely around $120**. That's this pack's own measured build volume repriced at Opus-class rates, multiplied by an exploration factor for the ~20 tenancy decisions in `ARCHITECTURE.md`; the pack-guided build itself measured ≈$6 of tokens per verification run (August 2026 rates), because tasks route down to cheaper tiers. Honest reading: at the likely figure that is **less** than the $149 price — the token math alone does not pay for this pack, and we won't pretend otherwise. What it pays for is the 39-check isolation suite, and the fact that the leak classes above are the ones you'd otherwise find in someone else's pentest report.

What you get

**Features**

• Email/password accounts with argon2id hashing and enumeration-resistant login
• DB-backed sessions: hashed tokens, sliding 30-day expiry, rotation on login, logout, revoke-all-others on password change
• Multi-org membership: create orgs, belong to many, act in one per request
• Roles `owner / admin / member` with owner-only role changes & removals, last-owner protection, and instant effect (no stale claims)
• Invite lifecycle: single-use hashed tokens, email-bound, 7-day expiry, role caps by inviter, revoke, re-invite supersedes
• Postgres row-level security on every tenant table — default-deny, `WITH CHECK` on writes, membership verified inside the database
• SSO-ready identity table (`(provider, provider_user_id)` unique) with safe no-auto-link rules
• `notes` — a fully worked org-scoped CRUD resource to copy for every future tenant table
• 39-check / ~105-assertion verification suite incl. direct-SQL cross-tenant probes, wired for OneShot receipts (`CHECK:` lines)

**Screens** (shadcn/ui + Tailwind, functional not fancy)

• `/login` — sign in / sign up
• `/` — your orgs, create-org form, logout
• `/orgs/[orgId]` — notes list + editor; members list with role controls (owner) and invite form (owner/admin), gated by your role

**API** — 19 JSON endpoints (auth ×4, me, orgs ×2, members ×3, invites ×4, notes ×5, health); full contract in ACCEPTANCE.md.

nextjs-postgres versions lock