docs: mark multi-tenant RLS isolation DONE + operational notes (superuser migrations)

39ea230cd6ab8ead04dfcf76280e3a502b62dbdd · 2026-08-06 10:13:41 -0700 · Steve
Files touched

M src/pr/MULTITENANT_TODO.md
Diff

commit 39ea230cd6ab8ead04dfcf76280e3a502b62dbdd
Author: Steve <steve@designerwallcoverings.com>
Date:   Thu Aug 6 10:13:41 2026 -0700

    docs: mark multi-tenant RLS isolation DONE + operational notes (superuser migrations)
---
 src/pr/MULTITENANT_TODO.md | 70 ++++++++++++++++++++++------------------------
 1 file changed, 34 insertions(+), 36 deletions(-)

diff --git a/src/pr/MULTITENANT_TODO.md b/src/pr/MULTITENANT_TODO.md
index 322515a0..8ff74026 100644
--- a/src/pr/MULTITENANT_TODO.md
+++ b/src/pr/MULTITENANT_TODO.md
@@ -1,41 +1,39 @@
-# Multi-tenant isolation — completion checklist (b2)
+# Multi-tenant isolation — DONE (RLS)
 
-Status: the AUTH/RBAC platform is done + live (tenants, users, sessions, roles, router
-capability gate, clean client login, credentials page, Gmail sync). Tenant DATA-filtering
-is PARTIAL — a `PR_MULTI_TENANT_READY=1` guard in `auth.createUser` blocks onboarding any
-tenant > 1 until this list is complete + verified, so no false isolation can ship.
+Tenant isolation is enforced at the database via Postgres Row-Level Security. There is
+NO per-query filtering to maintain — the DB scopes every query automatically, so it's
+impossible to forget a `WHERE tenant_id`.
 
-## Done (record-returning list reads scoped by tenant_id)
-- [x] `organizations.list` + org list/get routes (get-by-id 404s cross-tenant)
-- [x] `people.list` + people list/get routes
-- [x] `outreach.list` + `/api/pr/outreach` route
-- [x] `/people/:id/timeline` (tenant-scoped in the route query)
+## How it works
+- **migration 007**: `ENABLE` + `FORCE ROW LEVEL SECURITY` + a policy on all 17 entity
+  tables: `USING (NULLIF(current_setting('app.tenant_id', true),'') IS NULL OR … = '0' OR
+  tenant_id = NULLIF(current_setting('app.tenant_id', true),'')::bigint)`. Auth tables
+  (`pr_tenants/pr_users/pr_sessions`) are NOT RLS-guarded (login is pre-tenant).
+- **db.js**: `tenantMiddleware` checks out one connection per web request, `SET app.tenant_id`,
+  and stashes it in AsyncLocalStorage → every `db.query/one/rows/tx` in that request runs on
+  that connection, scoped by RLS. Outside a request (worker, CLI tools) → no ALS → GUC unset
+  → the policy grants FULL cross-tenant access (system operations).
+- **index.js**: the PR router mounts `db.tenantMiddleware` right after the capability gate.
+- **The app + worker connect as the NON-SUPERUSER `pr_app` role** — superusers bypass RLS,
+  so this is required. `PR_DATABASE_URL=postgresql://pr_app:<pw>@127.0.0.1:5432/rentv_pr`
+  (prod uses TCP + scram; local uses the `/tmp` socket, trust).
 
-## Remaining before lifting the guard (each: add `WHERE/AND <alias>.tenant_id = $` + pass
-##   `req.prAuth.tenant.id` from the route). All tables already carry `tenant_id`.
-- [ ] `campaigns.list` (`FROM pr_campaigns c` → `WHERE c.tenant_id`)
-- [ ] `tasks.list` (`FROM pr_tasks t` → add `AND t.tenant_id`)
-- [ ] `sources.listSources` (`FROM pr_sources s` → add to WHERE)
-- [ ] `relationships.list` (`FROM pr_org_relationships r` → add to WHERE)
-- [ ] `suppression.list` (+ its `total` count)
-- [ ] `runs.list` (`pr_research_runs`) and the jobs list (`pr_jobs`)
-- [ ] `letters` templates/blocks/versions lists
-- [ ] `review/queue`, `activities`, `audit` list routes
-- [ ] get-by-id + evidence routes: add a `tenant_id === req.prAuth.tenant.id` check (like org/people)
-- [ ] **Dashboard + meta + search** (`src/pr/index.js`): ~12 hard-coded `FROM pr_organizations|
-      pr_people|pr_outreach_messages` aggregate queries — thread `req.prAuth.tenant.id` into each.
-      (These leak COUNTS, not records — lower sensitivity, but required for full isolation.)
-- [ ] EXPORT routes (`/export/*`) — scope by tenant.
+## Verified
+- Local: a tenant-2 user logs in via HTTP and sees ONLY tenant-2 data; tenant-1 sees 0
+  tenant-2 rows; unset (worker) sees all. Writes + reads + the worker all work.
+- Prod: app + worker connect as pr_app, RLS active, tenant-1 data flows, anon 401, writes OK,
+  no regression.
 
-## Recommended alternative (lower-risk, scopes everything at once)
-Postgres **Row-Level Security**: `ALTER TABLE … ENABLE ROW LEVEL SECURITY` + a policy
-`USING (tenant_id = current_setting('app.tenant_id')::bigint)` on every entity table, and in
-`db.js` run `SET app.tenant_id = <req tenant>` per transaction. One migration + a per-request
-GUC instead of ~25 query edits — and impossible to forget a filter. Caveat: needs per-request
-connection binding (the current pool would need `SET LOCAL` inside a tx wrapper), and the
-Basic-admin/service callers must resolve to tenant 1.
+## ⚠️ Operational note — MIGRATIONS
+`pr_app` is not a superuser, so it CANNOT run DDL. The app's lazy-migrate only fires on a
+0-migration DB, so it won't try. **Any future migration must be applied as a superuser**
+(root) before/with the deploy:
+`ssh root@prod 'cd /root/public-projects/rentv && node -e "require(\"./src/pr/db\").runMigrations({log:console.log})"'`
+— but that reads PR_DATABASE_URL (pr_app) and will FAIL on DDL. To migrate, run it with
+PR_DATABASE_URL unset so it connects as the superuser: `PR_DATABASE_URL= node -e "…runMigrations…"`.
+(Consider adding a `pr:migrate:prod` script that forces the superuser connection.)
 
-## Verification gate (before setting PR_MULTI_TENANT_READY=1)
-Create a throwaway tenant 2 + a few tenant-2 rows, log in as a tenant-2 user, and confirm
-EVERY list/get/dashboard/search/export returns ONLY tenant-2 data (0 tenant-1 rows). Only
-then lift the guard.
+## Onboarding a new tenant (now safe)
+`INSERT INTO pr_tenants …`, then `auth.createUser({tenant_id:N, role:'admin', …})` (the old
+PR_MULTI_TENANT_READY guard is retired). Their users log in at `/admin/pr-intelligence/login.html`
+and see only their tenant's data. Wire per-tenant Gmail on the credentials page.
← 9d3b82a9 Sitewide nav: collapse all tab strips into a hamburger + cen · back to Rentv 2026 · auto-data-snapshot: 2026-08-06T10:13:41 (7 data files) — dat 4600ddf8 →