Phase 3: project_members replaces shared_with JSONB

↗ view on GitHub · Claude · 2026-05-15 · 6e9bf945

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.
Repository cpatpa/PIP
Author Claude <noreply@anthropic.com>
Authored
Parents 171d88d4
Stats 5 files changed , +418 , -176
Part of Phase 3 - granular sharing (project_members, review_members)

Capture this commit into my fork

Download a Markdown prompt that tells Claude how to port this exact commit into your working tree. Run it via claude -p < capture-commit-6e9bf945.md from inside the repo you want the change in.

⬇ Download capture-commit-6e9bf945.md