cpatpa replaces shared_with JSONB arrays with proper join tables

Two migrations swap the upstream `shared_with text[]` pattern for `project_members` and `review_members` join tables, both with FK to `users(id)`, RLS forced, and a live backfill of existing rows. The legacy JSONB columns stay in the schema for one cycle as a rollback net.

multi-tenantinfrastructure

Migration 0015 (6e9bf945) creates project_members(id, project_id FK, user_id FK, role CHECK IN ('member','editor'), invited_by FK, joined_at, UNIQUE(project_id, user_id)) with indexes on both FKs. A DO block walks every project's shared_with JSONB, matches each email to an active user by lower(email::text), and inserts a project_members row. Unmatched emails are counted and reported via NOTICE then dropped - they had no user account behind them and couldn't have accessed the project anyway. Owner-matches are skipped. The legacy shared_with column stays on projects but application code stops reading and writing it.

lib/access.ts is rewritten against project_members: checkProjectAccess now joins the table rather than scanning emails, and a new listAccessibleProjectIds helper replaces the JSONB @> containment queries in the projects list route. The API response shape is kept identical - shared_with is still a string[] of emails in the response, but it's now derived from project_members JOIN users. Frontend code needs no changes.

Migration 0016 (510d7e86) mirrors the same pattern for standalone tabular reviews. review_members has the same column shape; ensureReviewAccess in lib/access.ts now checks review_members for direct shares and falls through to project_members via checkProjectAccess for project-scoped access. The actual drop of both legacy JSONB columns and their GIN indexes is deferred to a follow-on migration once retention UI lands.

The role CHECK IN ('member', 'editor') enum is minimal. editor is reserved for a follow-up that permits document upload/edit while keeping share and role management on the owner. If you want richer roles, that's an extension point.

So what Worth copying if you want proper join-table sharing in your own fork. The two-phase migration pattern - add the join table with a backfill, leave the legacy column for a cycle, drop in a follow-up - is the safe approach, and the continuity trick of keeping the API response shape unchanged by deriving `shared_with` from a JOIN means the frontend doesn't have to move in lockstep with the backend. The JSONB-to-join-table backfill logic in the DO blocks is directly reusable.

View this fork on GitHub →

Spotted something wrong? Or know the PR text has fresher detail than the writeup above?

Commits in this thread

2 commits from cpatpa/PIP, oldest first. Source extracted verbatim from the harvested git log.

SHA	Subject	Author	Date
`6e9bf945`	Phase 3: project_members replaces shared_with JSONB	Claude	2026-05-15	↗ GitHub
commit body New project_members table joined on user_id. The legacy projects.shared_with JSONB column stays in the DB as a rollback safety net but is no longer read or written by application code; API responses still surface a shared_with array of emails derived from project_members joined to users so the frontend keeps working. Schema (migration 0015_project_members.sql): - project_members(id, project_id FK, user_id FK, role CHECK in ('member','editor'), invited_by FK, joined_at, UNIQUE (project_id, user_id)). - Indexes on project_id and user_id. - RLS forced. - Backfill DO block: walks every project's shared_with JSONB, matches each email to an active user, INSERTs a project_members row, skips owner-matches, reports unmatched count via NOTICE. Helpers (lib/projectMembers.ts): - PROJECT_SELECT_COLUMNS: canonical SELECT list including a computed shared_with array (lower(email) of every member), COALESCEd to '[]' so the response shape stays stable. - listProjectMembers: members with user_id, email, display_name, role, ordered by email. - setProjectMembers: transactional replace. Matches emails to active users, returns the matched + unmatched arrays, skips the project owner, deletes members not in the new set, upserts the rest. Access helpers (lib/access.ts) rewritten: - checkProjectAccess: owner match -> isOwner=true; else project_ members lookup. No more email-string comparison. - listAccessibleProjectIds: UNION of own projects and projects joined via project_members. - ensureDocAccess and filterAccessibleDocumentIds follow. - ensureReviewAccess still uses the legacy tabular_reviews.shared_with JSONB for standalone reviews; that column is the next migration target. Routes (routes/projects.ts): - GET /projects shared-by-email branch -> JOIN project_members ON pm.user_id = $1 AND p.user_id <> $1. - POST /projects inserts shared_with='[]' in the DB column and populates project_members via setProjectMembers. - GET /projects/:id uses checkProjectAccess for the gate; response unchanged. - GET /projects/:id/people returns owner from users join + members from listProjectMembers (display_name + role surface). - PATCH /projects/:id is owner-only via checkProjectAccess; supplied shared_with array routes through setProjectMembers. - Removed the now-unused isValidEmail helper. End-to-end verified against Postgres 16 across 11 scenarios: - Mixed valid + unknown emails -> unknown silently dropped, only valid ones in project_members. - Shared user gets project access on read paths. - PATCH shared_with:[] revokes access immediately (member can no longer GET, list returns empty). - PATCH shared_with:[email] re-adds member. - /people returns owner with display_name plus members with display_name and role. - Non-owner PATCH -> 404. Next: replace tabular_reviews.shared_with with a similar review_members table, then frontend workspace switcher.
`510d7e86`	Phase 3: review_members replaces tabular_reviews.shared_with JSONB	Claude	2026-05-15	↗ GitHub
commit body Mirrors the project_members migration from the previous commit. Standalone tabular reviews now share via the new review_members table joined on user_id; the legacy tabular_reviews.shared_with JSONB column stays as a rollback safety net but is no longer read or written by application code. Schema (migration 0016_review_members.sql): - review_members(id, review_id FK, user_id FK, role CHECK in ('member','editor'), invited_by FK, joined_at, UNIQUE (review_id, user_id)). - Indexes on review_id and user_id. RLS forced; anon/authenticated grants revoked. - Backfill DO block walks every tabular_reviews.shared_with JSONB list, matches emails to active users, INSERTs review_members rows, skips owner-matches, reports unmatched count via NOTICE. Helpers (lib/reviewMembers.ts) match projectMembers: - REVIEW_SELECT_COLUMNS: canonical SELECT including a computed shared_with array derived from review_members + users so the response shape stays stable. - listReviewMembers: user_id, email, display_name, role. - setReviewMembers: transactional replace with matched/unmatched email reporting; skips owner; deletes-then-upserts. Access helpers (lib/access.ts): - ensureReviewAccess signature now requires review.id so it can look up review_members directly. Falls back to checkProjectAccess for project-scoped reviews. The legacy shared_with field on the review object is accepted but ignored. Routes (routes/tabular.ts): - GET / direct-share branch -> JOIN review_members. - GET, GET /:id, PATCH all use REVIEW_SELECT_COLUMNS so the response carries a derived shared_with array. - PATCH routes the supplied shared_with through setReviewMembers instead of writing the legacy JSONB. - /people returns owner + listReviewMembers with display_name and role. End-to-end verified against Postgres 16 across 8 scenarios: - Standalone review starts with shared_with=[]. - PATCH shared_with with valid + unknown email -> unknown silently dropped; only valid in review_members. - Direct member can GET the review and sees it in their list. - /people returns owner + members with display_name + role. - Non-owner PATCH shared_with -> 403. - Empty shared_with revokes access; ex-member 404s.

Capture this thread into my fork

Download a single Markdown prompt that tells Claude how to port every commit above into your working tree — adapting paths and structure to match your repo. Run it via claude -p < capture-thread-364.md from inside the repo you want the changes in.

⬇ Download capture-thread-364.md