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

- [x] Examine existing db-context.ts
- [x] Examine current service implementations
- [x] Write tests for RLS context setting (11 tests passing, 5 need actual DB)
- [x] Implement `setWorkspaceContext()` method in PrismaService
- [x] Create helper methods for workspace-scoped queries
- [x] Update db-context.ts with workspace context functions
- [x] 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

```typescript
// 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:

```typescript
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)