Legal Case Management — Document Hierarchy Schema

Problem Description

A law firm is building a case management system on SQL Server. The domain has a natural hierarchy: a Law Firm has many Clients, each Client has many Cases, each Case has many Documents, and each Document can have multiple Revisions. The team needs the table schema and key-generation infrastructure for this hierarchy.

The team has previously struggled with databases where a query like "find all revisions for client X's cases" required joining through four tables using opaque row numbers. They want a design where the relationships are explicit in the data model and support staff can read meaningful identifiers directly from the keys — knowing that they're looking at Client 42's Case 3, Document 7, Revision 2 without having to look anything up.

The system should support these read patterns:

• Fetch all documents for a case without joining back through the entire ancestry
• Know at a glance how many cases a client has had (approximately, from the key alone)
• Allow efficient queries scoped to a single client across all their cases and documents

The schema should include all tables in the hierarchy with proper composite primary keys, well-named foreign key constraints, and the scalar functions needed to generate keys for new records at each level.

Output Specification

Produce a SQL script file named schema.sql containing:

• Custom type definitions for the domain identifiers used in the hierarchy
• The four entity tables (Client, Case, Document, Revision or equivalent hierarchy of your design) with composite primary keys that grow wider at each level
• All foreign key constraints with meaningful names
• A scalar function for generating the next key value at each level in the hierarchy (one function per level below the root)

The script should be self-contained and runnable on a fresh SQL Server database. Name output files as specified.