1.2.3 Postgres RLS policies + workspace-context middleware

Estimate: 25m · Depends on: 1.2.2

The two structural gates that make multi-tenancy enforced rather than convention. (1) Postgres Row-Level Security policies on every workspace-scoped table — Workspace itself and WorkspaceMembership for now; future Stories add more. Each policy matches the row's workspaceId column (or, for Workspace itself, the id column) against the session GUC app.workspace_id. Queries that don't set the GUC see no rows. (2) Workspace-context middleware that runs on every authenticated request, looks up the active workspace, opens a Prisma $transaction, runs SET LOCAL app.workspace_id = $1 inside it, and routes the request handler's queries through that transaction so RLS sees the GUC.

Why RLS at the schema layer AND middleware at the app layer: defense-in-depth. The middleware sets the GUC for every legitimate request, but a bug in the middleware (forgotten SET LOCAL, dropped transaction) would silently let queries see all rows without RLS. RLS without middleware would force every query in the application to remember to set the GUC manually, which would rot within a Story. Together: the middleware does the right thing by default; RLS catches every bug in the middleware. The Story-level AC bullet phrases this as "leak is structurally impossible" — that requires both gates.

Why session GUC, not Postgres role-per-workspace: the session-GUC pattern is the documented Postgres-RLS-with-pooled-connections pattern. Per-workspace Postgres roles would explode role count and break connection pooling (PgBouncer doesn't multiplex sessions across roles). The session GUC is set per-request, applies only to the current transaction (SET LOCAL, not SET), and leaves the connection clean for the next request. Standard pattern documented by Supabase, Neon, and the Postgres docs themselves.

Why the middleware uses $transaction: Prisma's SET LOCAL needs a transaction scope to bind to. Without $transaction, SET LOCAL applies to a single statement and then is reset — the next query in the same request would run without the GUC. Wrapping the handler in $transaction gives every query in the request the same GUC.

What you'll do: Add a new Prisma migration (add_workspace_rls) that runs the raw SQL: ALTER TABLE workspace ENABLE ROW LEVEL SECURITY; ALTER TABLE workspace_membership ENABLE ROW LEVEL SECURITY; + create policies that match against current_setting('app.workspace_id', true)::text (the true second arg returns NULL if the setting is missing, which the policy rejects). Also create a policy on Workspace that allows a user to see ALL workspaces they have a membership in (the switcher needs this — it queries the user's memberships to populate the menu), keyed off current_setting('app.user_id', true). Add a second session GUC app.user_id set alongside app.workspace_id. Create lib/workspaces/context.ts exporting withWorkspaceContext(userId, workspaceId, fn) that opens the transaction, sets both GUCs, and runs fn. Create lib/workspaces/middleware.ts that resolves the active workspace from the cookie (or falls back to the user's first membership), and a server-side getWorkspaceContext() helper for server components to read the active workspace.

Acceptance criteria

• New Prisma migration add_workspace_rls enables RLS on workspace and workspace_membership tables; creates policies matching rows against current_setting('app.workspace_id', true) (and current_setting('app.user_id', true) for the workspace-list-membership policy).
• The Workspace table has TWO policies: (a) "row.id = app.workspace_id" for queries within an active workspace, (b) "row.id IN (SELECT workspace_id FROM workspace_membership WHERE user_id = app.user_id)" for the switcher's listing query.
• WorkspaceMembership table policy: "row.workspace_id = app.workspace_id OR row.user_id = app.user_id" (the user can see their own memberships across workspaces — needed for the switcher).
• lib/workspaces/context.ts exports withWorkspaceContext({ userId, workspaceId }, fn) opening a Prisma $transaction that runs SET LOCAL app.workspace_id = $1; SET LOCAL app.user_id = $2; via $executeRaw before invoking fn(tx). The function returns whatever fn returns.
• lib/workspaces/middleware.ts exports resolveWorkspaceContext(request) that reads the Better-Auth session, reads a workspace_id cookie (or falls back to findUserWorkspaces(userId)[0]), and returns { userId, workspaceId } | null. Returns null if the user has no memberships.
• Server-side helper getWorkspaceContext() in lib/workspaces/index.ts reads the session + cookie at request time (similar shape to Story 1.1's getSession()).
• Vitest integration tests in tests/workspace-rls.test.ts cover: queries without the GUC see zero workspace rows; queries with the GUC see only the active workspace's rows; queries against a workspace the user isn't a member of return zero rows even if the GUC is set; cross-workspace UPDATE attempts are rejected.
• Vitest test for withWorkspaceContext verifies that SET LOCAL persists across multiple queries inside the same callback (the load-bearing reason for using $transaction).
• All 4 quality gates green; test suite green.

Context refs

• prisma/schema.prisma + the latest migration from 1.2.2
• lib/db.ts — singleton Prisma client (the $transaction entry point)
• lib/auth/index.ts — Better-Auth instance, for the session-read pattern getWorkspaceContext() mirrors
• Postgres docs (fetched at prompt-gen time): row security policies, current_setting() with the missing-setting NULL fallback, SET LOCAL semantics within transactions
• Prisma docs (fetched at prompt-gen time): $transaction, $executeRaw + parameter binding, interactive transactions vs sequential
• Supabase RLS guide (fetched at prompt-gen time): the canonical session-GUC pattern for Postgres-RLS with pooled connections