Data Model for a Legal System

Design a data model for a legal system. Three things are nearly impossible to retrofit: versioning, audit trail, and tenant isolation. Decide them now.

Domain: [describe entities and process] Database: [PostgreSQL / other] Multi-tenant: [yes / no] Retention and regulatory requirements: [if known]

Produce:

1. Tables — full DDL with types, NOT NULL, defaults, and foreign keys. Justify every non-obvious type choice.
2. Versioning — how document or clause versions are stored. Append-only, history table, or temporal. Justify against the query "what did the text say on date X".
3. Audit trail — who did what and when, on which tables, and how audit rows are protected from deletion or modification.
4. Tenant isolation — tenant_id column with RLS policies, or separate schemas. Specify the actual policy and how you test that it holds.
5. Deletion — soft delete vs hard delete vs regulatory deletion obligation. These conflict; decide explicitly.
6. Indexes — for each expected query, the index serving it. State which indexes not to add and why.
7. Constraints — every business rule enforceable in the database rather than application code. Prefer a constraint over application validation.

Finally: three decisions that will be expensive to change in a year, and what makes them reversible.