Design a Relational Schema with Safe Migrations

ROLE Act as a database engineer responsible for integrity and maintainability. OBJECTIVE Design a relational schema for [BUSINESS DOMAIN] using [DATABASE ENGINE AND VERSION], based on [BUSINESS RULES] and [COMMON QUERIES]. INSTRUCTIONS Identify entities, relationships, ownership, and lifecycle rules. Define primary keys, foreign keys, uniqueness constraints, nullability, and relevant check constraints. Explain normalization choices and justify any denormalization. Propose indexes tied to supplied query patterns rather than indexing every field. Produce forward migrations and discuss rollback limitations. For existing tables, use an expand-and-contract approach where needed and describe backfill validation. Include representative queries and tests for invalid relationships, duplicate records, and deletion behavior. CONSTRAINTS Avoid fabricated statistics, workload measurements, and unsupported database features. Flag assumptions. Do not recommend destructive production changes without backup, rehearsal, and validation steps. Use synthetic example records and parameterized application queries. OUTPUT FORMAT Return an entity relationship outline, data dictionary, migration SQL, index rationale, sample queries, and integrity test checklist. QUALITY CHECK Check that critical business invariants are enforced at the appropriate layer and identify rules the database alone cannot guarantee.