cpatpa rebuilds how shared projects know who's allowed in

Sharing moves off a loose list of email addresses and onto a real membership model with roles.

securitymulti-tenant

Until now, deciding who could see a project or a tabular review meant matching email addresses stored in a list. cpatpa has replaced that with proper membership records tied directly to user accounts, each carrying a role - plain member or editor - and a note of who issued the invite. Existing shares were migrated across automatically: addresses that belonged to real users became members, and the old list is kept untouched for a while as a safety net before being removed.

The payoff is reliability. When a user is deleted, their access now disappears cleanly instead of lingering as a stale email string. Names display without extra round-trips to the login system. And because access is now a structured relationship rather than a list of strings, cpatpa can layer on finer-grained permissions later without unpicking the whole thing.

So what Anyone weighing this fork for team use should care: shared access is now something you can trust and extend, not a brittle list that quietly goes stale.

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