Motir

Estimate: 20m · Depends on: 1.4.2, 1.4.3

Add Postgres Row-Level Security policies on work_item, work_item_revision, AND work_item_link matching the workspace-RLS pattern Story 1.2.3 + 1.3.2 established. Without the active app.workspace_id GUC, none of these tables can be read or written. Optional app.project_id GUC further narrows reads of work_item (when a route is project-scoped, the active project is set; when it's not, all projects in the workspace are visible). work_item_link does NOT narrow by project — cross-project links are a v1 use case (see 1.4.3); the link table is workspace-scoped only.

Why a project-scope GUC too: a workspace can host many projects after multi-project lights up (the schema already supports it — Story 1.3's UI just surfaces one). Workspace-only RLS would mean any signed-in member of workspace A can read every project's work items, including ones their role excludes them from in future Epic 6 RBAC. Adding the project GUC NOW (even though v1's UI shows only one project) is the durable shape — when Epic 6's permissions land, the GUC layer is already in place. Anti-shortcut: a v1-only "workspace is good enough" RLS that Epic 6 has to rewrite.

Policy shape: CREATE POLICY work_item_workspace ON work_item USING (workspace_id = current_setting('app.workspace_id', true)::text) WITH CHECK (workspace_id = current_setting('app.workspace_id', true)::text); The project narrowing is a separate FOR SELECT-only policy when app.project_id is set: USING (project_id = current_setting('app.project_id', true)::text OR current_setting('app.project_id', true) = ''). work_item_revision's policy joins to the work_item for the workspace check — simpler and faster than denormalizing workspace_id onto the revision row. work_item_link carries denormalized workspace_id (from 1.4.3), so its RLS policy is the same shape as work_item's — direct comparison, no join, fast lookup.

What you'll do: Add a raw-SQL Prisma migration work_item_rls with ALTER TABLE … ENABLE ROW LEVEL SECURITY and the policy DDL. Extend lib/db/withWorkspaceContext.ts (from 1.2.3) so it optionally also sets app.project_id if the request's active project is known. Update the middleware tests from 1.2.3 to cover the new tables. No application-layer code change beyond the optional project-context wiring.

Acceptance criteria

• RLS enabled on work_item, work_item_revision, AND work_item_link via the work_item_rls migration.
• Workspace policy on all three tables: rows readable / writable only when app.workspace_id matches the row's (or, for revisions, the parent work_item's) workspace_id. Verified by a test that queries without GUC set and gets zero rows on each table.
• Project narrowing on work_item: when app.project_id is set, SELECTs return only that project's items; when unset (empty string), all workspace projects are visible. work_item_link intentionally does NOT narrow by project (cross-project links are supported).
• work_item_revision RLS joins to the parent work_item's workspace; revisions for cross-workspace tampering are unreadable.
• work_item_link RLS uses the denormalized workspace_id column directly (no join). The WITH CHECK clause rejects link inserts whose workspace_id doesn't match the active GUC.
• withWorkspaceContext helper optionally takes projectId and sets the GUC inside the transaction.
• Test fixture: User A in workspace W1, User B in workspace W2 — A cannot SELECT B's work items / revisions / links even with explicit IDs; A cannot INSERT into any of the three with W2's workspace_id (the WITH CHECK clause rejects).
• All quality gates green; existing suite stays green.

Context refs

• prisma/migrations/.../workspace_rls/migration.sql from 1.2.3 — the exact policy pattern
• prisma/migrations/.../project_rls/migration.sql from 1.3.2 — the second instance of the pattern
• lib/db/withWorkspaceContext.ts — the GUC-setting transaction helper to extend
• Tests under tests/integration/rls/ from 1.2.3 + 1.3.2 — the pattern these tests should extend
• This Story page — policy specification