ADR-003: Per-Case Database Schemas for Multi-Tenancy

Status

Accepted

Date

2024-01-01

Context

The platform serves multiple litigation cases, each with sensitive data requiring isolation. Options: 1. Row-level isolation (shared tables, WHERE case_id = ?) 2. Schema-level isolation (one MySQL schema per case) 3. Database-level isolation (separate RDS instances per case)

Decision

Use schema-level isolation: each case gets its own MySQL schema within a shared RDS Aurora cluster. Schema naming convention: {RDS_DBNAME}_case_{case_id}.

A shared "core" schema holds cross-case data (case metadata, user accounts).

Consequences

Positive

• Strong data isolation without per-case infrastructure cost
• No risk of cross-case data leakage through missing WHERE clauses
• Smaller, more efficient indexes per schema
• Easy lifecycle management (archive/drop schema when case closes)
• Compliance-friendly (per-case retention policies)

Negative

• Connection management complexity (engine cache needed)
• Schema migrations must run per-case
• Lambda must track which case it's operating on (ContextVar pattern)

Risks

• MySQL schema count limit (~10,000 schemas per instance)
• Engine cache memory pressure in Lambda (mitigated by MAX_CACHE_SIZE = 50)
• Schema drift if migrations aren't applied consistently

Sign In

Sign In

Please set a new password.

Set Password

Cancel

?

Ask the Architecture ×

Ask questions about Nextpoint architecture, patterns, rules, or any module. Powered by Claude Opus 4.6.

Ask