cpatpa rebuilds how PIP shares projects and reviews

The fork swaps a quick-and-dirty list of email addresses for a real membership system - without breaking anything visible to users.

multi-tenantinfrastructure

Until now, sharing a project or a review in PIP meant tacking an email onto a hidden list inside the record itself. That works until you want to ask questions like who invited whom, when they joined, or what they're allowed to do. cpatpa replaces that list with a proper membership table on both projects and standalone reviews, capturing the inviter, the join date, and a role (member or editor).

The rollout is the careful kind: build the new structure, copy the existing shared emails into it, but leave the old list in place as a safety net for one release. The screens users actually see don't change - the same list of email addresses still appears, just assembled from the new source. The old column gets retired later, once related retention work lands.

So what Anyone running a multi-user legal workspace should note the migration choreography here - it's how you upgrade a sharing model in production without a scary cutover weekend.

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