Phase 3 foundation: workspaces and workspace_members

↗ view on GitHub · Claude · 2026-05-15 · 61dda046

Adds the team-scope container that sits above users and below the
organisation. Future commits hang projects, chats, tabular reviews,
and workflows off this layer; this commit lays the schema and the
route surface.

Schema (migration 0013_workspaces.sql):
- workspaces: id, slug (unique, auto-generated), name, description,
  created_by, is_personal, plus nullable AI policy overrides
  (instructions, allow_external_models, retention_days). RLS forced
  in line with every other backend-owned table; anon/authenticated
  grants revoked.
- workspace_members: workspace_id, user_id, role
  CHECK (role IN ('owner','admin','member')), invited_by, joined_at.
  Unique (workspace_id, user_id).
- Partial unique index workspaces_personal_unique pins each user to
  at most one Personal workspace.
- ensure_personal_workspace trigger auto-provisions a Personal
  workspace + owner membership on every users INSERT. Backfill loop
  covers pre-existing users.

Helpers (lib/workspaces.ts):
- listWorkspacesForUser, loadWorkspaceForUser, getWorkspaceRole,
  createWorkspace, listWorkspaceMembers, addWorkspaceMemberByEmail,
  setWorkspaceMemberRole, removeWorkspaceMember.
- Role helpers (roleAtLeast) for ladder checks.
- Slug generator that walks suffixes until unique.

Routes (routes/workspaces.ts):
- Member-scoped list, create with auto-slug, fetch, rename, AI
  policy edit, delete (owner; Personal protected).
- Member CRUD with role restrictions: admin can invite member/admin;
  only owners can grant owner; cannot demote or remove the last
  owner; Personal workspaces refuse member additions; any user can
  self-leave (non-owner).
- Audit events for every state-changing call.

End-to-end verified against Postgres 16 across 16 scenarios:
- Fresh user sees just their Personal workspace.
- Create returns a workspace with auto-generated slug.
- Invite by email looks up the matching public.users row.
- Member trying to invite returns 403.
- Owner promoting a member to admin returns 204.
- Admin trying to grant owner returns 403.
- Personal workspace delete attempt returns 403.
- Demoting the last owner returns 400.
- AI policy override (instructions, allow_external_models,
  retention_days) round-trips.
- Self-leave returns 204; owner delete returns 204.
- Audit log captures workspace.create, .member.add, .member.role,
  .member.leave, .delete.

Docs:
- docs/developer/03-database-schema.md gains a Phase 3 section
  documenting workspaces, the personal-workspace invariant, and the
  inherit-from-org policy semantics.

Next sub-phase: add workspace_id to projects/chats/tabular_reviews/
workflows and migrate routes to filter by it, then replace the
projects.shared_with JSONB with a proper project_members table.
Repository cpatpa/PIP
Author Claude <noreply@anthropic.com>
Authored
Parents c2bff6cc
Stats 6 files changed , +906
Part of Phase 3 - workspaces (team-scope layer)

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-61dda046.md from inside the repo you want the change in.

⬇ Download capture-commit-61dda046.md