stack/docs/design/multi-tenant-rls.md

# Multi-Tenant Row-Level Security (RLS)

## Overview

Mosaic Stack implements multi-tenancy using PostgreSQL Row-Level Security (RLS) to ensure complete data isolation between workspaces at the database level. This provides defense-in-depth security, preventing data leakage even if application-level checks fail.

## Architecture

### Core Concepts

1. **Workspaces**: Top-level tenant containers
2. **Teams**: Sub-groups within workspaces for collaboration
3. **Workspace Members**: Users associated with workspaces (OWNER, ADMIN, MEMBER, GUEST roles)
4. **Team Members**: Users associated with teams (OWNER, ADMIN, MEMBER roles)

### Database Schema

```
User
  ├── WorkspaceMember (role: OWNER, ADMIN, MEMBER, GUEST)
  │   └── Workspace
  │       ├── Team
  │       │   └── TeamMember (role: OWNER, ADMIN, MEMBER)
  │       ├── Task, Event, Project, etc.
  │       └── All tenant-scoped data
```

## RLS Implementation

### Tables with RLS Enabled

All tenant-scoped tables have RLS enabled:

- `workspaces`
- `workspace_members`
- `teams`
- `team_members`
- `tasks`
- `events`
- `projects`
- `activity_logs`
- `memory_embeddings`
- `domains`
- `ideas`
- `relationships`
- `agents`
- `agent_sessions`
- `user_layouts`
- `knowledge_entries`
- `knowledge_tags`
- `knowledge_entry_tags`
- `knowledge_links`
- `knowledge_embeddings`
- `knowledge_entry_versions`

### Helper Functions

The RLS implementation uses several helper functions:

#### `current_user_id()`

Returns the current user's UUID from the session variable `app.current_user_id`.

```sql
SELECT current_user_id(); -- Returns UUID or NULL
```

#### `is_workspace_member(workspace_uuid, user_uuid)`

Checks if a user is a member of a workspace.

```sql
SELECT is_workspace_member('workspace-uuid', 'user-uuid'); -- Returns BOOLEAN
```

#### `is_workspace_admin(workspace_uuid, user_uuid)`

Checks if a user is an owner or admin of a workspace.

```sql
SELECT is_workspace_admin('workspace-uuid', 'user-uuid'); -- Returns BOOLEAN
```

### Policy Pattern

All RLS policies follow a consistent pattern:

```sql
CREATE POLICY <table>_workspace_access ON <table>
  FOR ALL
  USING (
    is_workspace_member(workspace_id, current_user_id())
  );
```

For tables without direct `workspace_id`, policies join through parent tables:

```sql
CREATE POLICY knowledge_links_access ON knowledge_links
  FOR ALL
  USING (
    source_id IN (
      SELECT id FROM knowledge_entries
      WHERE is_workspace_member(workspace_id, current_user_id())
    )
  );
```

## API Integration

### Setting the Current User

Before executing any queries, the API **must** set the current user ID:

```typescript
import { prisma } from "@mosaic/database";

async function withUserContext<T>(userId: string, fn: () => Promise<T>): Promise<T> {
  await prisma.$executeRaw`SET LOCAL app.current_user_id = ${userId}`;
  return fn();
}
```

### Example Usage in API Routes

```typescript
import { withUserContext } from "@/lib/db-context";

// In a tRPC procedure or API route
export async function getTasks(userId: string, workspaceId: string) {
  return withUserContext(userId, async () => {
    // RLS automatically filters to workspaces the user can access
    const tasks = await prisma.task.findMany({
      where: {
        workspaceId,
      },
    });
    return tasks;
  });
}
```

### Middleware Pattern

For tRPC or Next.js API routes, use middleware to automatically set the user context:

```typescript
// middleware/auth.ts
export async function withAuth(userId: string, handler: () => Promise<any>) {
  await prisma.$executeRaw`SET LOCAL app.current_user_id = ${userId}`;
  return handler();
}

// In tRPC procedure
.query(async ({ ctx }) => {
  return withAuth(ctx.user.id, async () => {
    // All queries here are automatically scoped to the user's workspaces
    return prisma.workspace.findMany();
  });
});
```

### Transaction Pattern

For transactions, set the user context within the transaction:

```typescript
await prisma.$transaction(async (tx) => {
  await tx.$executeRaw`SET LOCAL app.current_user_id = ${userId}`;

  // All queries in this transaction are scoped to the user
  const workspace = await tx.workspace.create({
    data: { name: "New Workspace", ownerId: userId },
  });

  await tx.workspaceMember.create({
    data: {
      workspaceId: workspace.id,
      userId,
      role: "OWNER",
    },
  });

  return workspace;
});
```

## Security Considerations

### Defense in Depth

RLS provides **database-level** security, but should not be the only security layer:

1. **Application-level validation**: Always validate workspace access in your API
2. **RLS policies**: Prevent data leakage at the database level
3. **API authentication**: Verify user identity before setting `app.current_user_id`

### Important Notes

- **RLS does not replace application logic**: Application code should still check permissions
- **Performance**: RLS policies use indexes on `workspace_id` for efficiency
- **Bypass for admin operations**: System-level operations may need to bypass RLS using a privileged connection
- **Testing**: Always test RLS policies with different user roles

### Admin/System Operations

For system-level operations (migrations, admin tasks), use a separate connection or temporarily disable RLS:

```sql
-- Disable RLS for superuser (use with caution)
SET SESSION AUTHORIZATION postgres;
-- Or use a connection with a superuser role
```

## Testing RLS

### Manual Testing

```sql
-- Set user context
SET app.current_user_id = 'user-uuid-here';

-- Try to query another workspace (should return empty)
SELECT * FROM tasks WHERE workspace_id = 'other-workspace-uuid';

-- Query your own workspace (should return data)
SELECT * FROM tasks WHERE workspace_id = 'my-workspace-uuid';
```

### Automated Tests

```typescript
import { prisma } from "@mosaic/database";

describe("RLS Policies", () => {
  it("should prevent cross-workspace access", async () => {
    const user1Id = "user-1-uuid";
    const user2Id = "user-2-uuid";
    const workspace1Id = "workspace-1-uuid";
    const workspace2Id = "workspace-2-uuid";

    // Set context as user 1
    await prisma.$executeRaw`SET LOCAL app.current_user_id = ${user1Id}`;

    // Should only see workspace 1's tasks
    const tasks = await prisma.task.findMany();
    expect(tasks.every((t) => t.workspaceId === workspace1Id)).toBe(true);
  });
});
```

## Migration Strategy

### Existing Data

If migrating from a non-RLS setup:

1. Enable RLS on tables (already done in migration `20260129221004_add_rls_policies`)
2. Create policies (already done)
3. Update application code to set `app.current_user_id`
4. Test thoroughly with different user roles

### Rolling Back RLS

If needed, RLS can be disabled per table:

```sql
ALTER TABLE <table_name> DISABLE ROW LEVEL SECURITY;
```

Or policies can be dropped:

```sql
DROP POLICY <policy_name> ON <table_name>;
```

## Performance Optimization

### Indexes

All tenant-scoped tables have indexes on `workspace_id`:

```sql
CREATE INDEX tasks_workspace_id_idx ON tasks(workspace_id);
```

### Function Optimization

Helper functions are marked as `STABLE` and `SECURITY DEFINER` for optimal performance:

```sql
CREATE OR REPLACE FUNCTION is_workspace_member(workspace_uuid UUID, user_uuid UUID)
RETURNS BOOLEAN AS $$
...
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER;
```

### Query Planning

Check query plans to ensure RLS policies are efficient:

```sql
EXPLAIN ANALYZE
SELECT * FROM tasks WHERE workspace_id = 'workspace-uuid';
```

## Future Enhancements

### Team-Level Permissions

Currently, RLS ensures workspace-level isolation. Future enhancements could include:

- Team-specific data visibility
- Project-level permissions
- Fine-grained role-based access control (RBAC)

### Audit Logging

RLS policies could be extended to automatically log all data access:

```sql
CREATE POLICY tasks_audit ON tasks
  FOR ALL
  USING (
    is_workspace_member(workspace_id, current_user_id())
    AND log_access('tasks', id, current_user_id()) IS NOT NULL
  );
```

## References

- [PostgreSQL Row Security Policies](https://www.postgresql.org/docs/current/ddl-rowsecurity.html)
- [Prisma Raw Database Access](https://www.prisma.io/docs/concepts/components/prisma-client/raw-database-access)
- [Multi-Tenancy Patterns](https://docs.microsoft.com/en-us/azure/architecture/guide/multitenant/considerations/tenancy-models)

## Migration Files

- `20260129220941_add_team_model` - Adds Team and TeamMember models
- `20260129221004_add_rls_policies` - Enables RLS and creates policies

## Summary

Row-Level Security in Mosaic Stack provides:

✅ **Database-level tenant isolation**  
✅ **Defense in depth security**  
✅ **Automatic filtering of all queries**  
✅ **Performance-optimized with indexes**  
✅ **Extensible for future RBAC features**

Always remember: **Set `app.current_user_id` before executing queries!**