stack/docs/scratchpads/195-rls-context-helpers.md

Issue #195: Implement RLS context helpers consistently across all services

Objective

Implement consistent RLS (Row-Level Security) context helpers across all services to ensure proper workspace isolation through PostgreSQL session variables.

Current State Analysis

Need to examine:

1. Existing db-context.ts helpers
2. How services currently handle workspace filtering
3. Whether RLS policies are defined in Prisma schema
4. Best approach for setting PostgreSQL session variables

Approach

Use Prisma Extension with PostgreSQL Session Variables:

• Create a Prisma extension that sets session variables before queries
• Session variables: app.current_workspace_id and app.current_user_id
• Apply to all workspace-scoped operations
• This works with connection pooling (uses SET LOCAL which is transaction-scoped)

Implementation Plan

• Examine existing db-context.ts
• Examine current service implementations
• Write tests for RLS context setting (11 tests passing, 5 need actual DB)
• Implement setWorkspaceContext() method in PrismaService
• Create helper methods for workspace-scoped queries
• Update db-context.ts with workspace context functions
• Export new helper functions
• Document RLS usage patterns
• Add example of service using RLS context

Changes Made

PrismaService

Added three new methods:

1. setWorkspaceContext(userId, workspaceId, client?) - Sets session variables
2. clearWorkspaceContext(client?) - Clears session variables
3. withWorkspaceContext(userId, workspaceId, fn) - Transaction wrapper

db-context.ts

Added new functions:

1. setCurrentWorkspace(workspaceId, client) - Set workspace ID
2. setWorkspaceContext(userId, workspaceId, client) - Set both user and workspace
3. clearWorkspaceContext(client) - Clear both variables
4. withWorkspaceContext(userId, workspaceId, fn) - High-level transaction wrapper

All functions use SET LOCAL for transaction-scoped variables (connection pool safe).

Usage Pattern

// In a service
const tasks = await this.prisma.withWorkspaceContext(userId, workspaceId, async (tx) => {
  return tx.task.findMany({
    where: { status: "IN_PROGRESS" },
  });
});

Or using db-context helpers:

import { withWorkspaceContext } from "../lib/db-context";

const tasks = await withWorkspaceContext(userId, workspaceId, async (tx) => {
  return tx.task.findMany();
});

Testing Strategy

Unit Tests

• PrismaService sets context correctly
• Context is cleared after transaction
• Multiple concurrent requests don't interfere

Integration Tests

• Workspace isolation is enforced
• Cross-workspace queries are blocked
• RLS policies work with context variables

Notes

• Must use transaction-scoped SET LOCAL (not session-level SET)
• Connection pooling compatible
• Should work with or without actual RLS policies (defense in depth)