cpatpa adds a workspaces layer between users and the organisation

Three commits build a team-scope container that sits above individual users: the `workspaces` and `workspace_members` tables, workspace_id foreign keys on projects/chats/reviews/workflows with backfill, and a full frontend workspace switcher and management UI. Each user gets an auto-provisioned Personal workspace on signup.

multi-tenantcompliance

Migration 0013 (61dda046) creates workspaces with nullable AI policy override columns - instructions, allow_external_models, retention_days - that inherit from org_settings when null. A partial unique index limits each user to one Personal workspace. The ensure_personal_workspace trigger auto-provisions a Personal workspace and owner membership on every users INSERT; the migration also backfills existing users. workspace_members carries role CHECK IN ('owner', 'admin', 'member'), invited_by, and joined_at. Both tables have RLS enabled and forced, anon/authenticated grants revoked, matching the Phase 2 table posture.

Migration 0014 (171d88d4) adds workspace_id to projects, chats, tabular_reviews, and workflows via a three-step pattern: add the nullable column with FK and cascade, UPDATE every row to the owner's Personal workspace (or parent project's workspace for chats and reviews), then SET NOT NULL. Workflows keep workspace_id nullable because system workflows have no owner. A small STABLE SQL function personal_workspace_id(uuid) is defined once and reused in the backfill UPDATE, which is a sensible approach when the same lookup appears across multiple tables.

The backend route surface (routes/workspaces.ts) covers the expected CRUD plus member management: GET/POST/PATCH/DELETE on the workspace, GET/POST/PATCH/DELETE on members, with last-owner protection (can't demote or remove the last owner), Personal-workspace protection (can't delete), and self-leave. Audit events emitted on every member lifecycle action.

The frontend commit (1587d287) adds a WorkspaceProvider that persists the active workspace selection to localStorage, a workspace switcher in the sidebar, and a /workspaces/[id] settings page. Admins can edit name, description, instructions, the external-AI policy override, and retention days. Owners can change member roles and invite by email. The sidebar was rebranded from "Mike" to "PIP" in this same commit.

So what The data model is a solid reference design for team scoping: workspaces + members + a three-role enum + nullable AI policy overrides that inherit from org. The "Personal workspace per user, system workflows nullable" edge-case decisions are the kind of thing that's easy to get wrong on a first pass. Every backend route and migration is built on the post-Phase-2 pg stack, so a cherry-pick would require rewriting the route handlers against whatever data layer you're using. Reading the migrations is the practical takeaway, not running `git cherry-pick`.

View this fork on GitHub →

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

Commits in this thread

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

SHA Subject Author Date
61dda046 Phase 3 foundation: workspaces and workspace_members Claude 2026-05-15 ↗ GitHub
commit body
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.
171d88d4 Phase 3: link projects/chats/reviews/workflows to workspaces Claude 2026-05-15 ↗ GitHub
commit body
Stamps every workspace-scoped resource with its workspace_id and
teaches the create paths to populate it, the list paths (projects
only for now) to filter by it.

Schema (migration 0014_workspace_links.sql):
- ALTER ... ADD COLUMN workspace_id uuid REFERENCES workspaces(id)
  ON DELETE CASCADE on projects, chats, tabular_reviews, workflows.
- Backfill UPDATE per table: projects use the owner's Personal
  workspace; chats and tabular_reviews inherit the parent project's
  workspace when set, else Personal; workflows go to the author's
  Personal (or NULL when user_id IS NULL for system workflows).
- ALTER COLUMN ... SET NOT NULL on projects, chats, tabular_reviews.
  workflows stays nullable because system workflows have no owner.
- Helper function personal_workspace_id(uuid) for the backfill UPDATE
  and any future helpers. Marked STABLE.
- Indexes on workspace_id for the typical list filter; workflows
  uses a partial index keyed on the not-null subset.

Helpers (lib/workspaces.ts):
- getPersonalWorkspaceId, resolveWorkspaceForUser (verifies caller
  is a member; falls back to Personal when null), and
  listAccessibleWorkspaceIds for future scope-by-membership queries.

Routes:
- POST /projects reads optional workspace_id and uses
  resolveWorkspaceForUser. 404 on non-member.
- POST /workflows same shape.
- POST /tabular-review prefers the parent project's workspace when
  project_id is set; otherwise resolveWorkspaceForUser.
- POST /chat/create and POST /chat (streaming) likewise inherit when
  the chat is project-scoped.
- POST /projects/:id/chat (projectChat.ts) always inherits the
  project's workspace; refuses if the project somehow has no
  workspace.
- GET /projects accepts ?workspace_id= and scopes both branches
  (own + shared-by-email). Unknown ids return [] without error to
  avoid leaking existence.

End-to-end verified against a fresh Postgres 16:
- Project without workspace_id stamps Personal.
- Project with explicit workspace_id stamps that workspace.
- Project with non-member workspace_id returns 404.
- GET /projects with no filter returns both; filtered returns the
  right subset; unknown returns [].
- Chat under project inherits the project's workspace; standalone
  chat goes to Personal.
- Workflow respects workspace_id parameter.
- Tabular review standalone goes to Personal; under a project
  inherits the project's workspace.
- DB shows every row in projects, chats, tabular_reviews, and
  workflows has workspace_id populated.

Next sub-phase: replace projects.shared_with JSONB with a proper
project_members table joined on user_id, and add a frontend
workspace switcher.
1587d287 Frontend workspace switcher and management UI Claude 2026-05-15 ↗ GitHub
commit body
Wires up the long-pending frontend half of the workspace feature.
Adds a sidebar workspace switcher that persists the active selection
to localStorage, scopes the projects list and new-project creation to
that workspace, and provides a /workspaces management page (list,
create, settings, members, danger-zone delete).

Workspace admins can edit name/description/instructions, set the
external-AI policy override and retention days. Owners can change
member roles, invite by email and delete non-Personal workspaces.

Also rebrands the sidebar wordmark from "Mike" to "PIP".

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-363.md from inside the repo you want the changes in.

⬇ Download capture-thread-363.md