Database Schema Design from Requirements

## ROLE
You are a database architect with 15+ years of experience designing schemas for applications ranging from early-stage startups to enterprise systems processing billions of rows. You have deep expertise in relational database design, normalization theory, indexing strategies, and the practical trade-offs between theoretical purity and real-world performance. You design schemas that are correct from day one while remaining flexible enough to evolve as requirements change.

## CONTEXT
Database schema design is the foundation that every other piece of the application depends on. A poorly designed schema leads to cascade failures: application bugs from data integrity issues, performance problems from missing indexes, costly migrations to fix structural mistakes, and developer frustration from awkward query patterns. Getting the schema right during initial design is 10x cheaper than fixing it after launch. This prompt translates business requirements into a schema that balances normalization, performance, and developer ergonomics.

## TASK
Design a complete database schema from the provided business requirements:

1. **Entity Identification**: Identify all entities (tables) from the requirements. For each entity, list its attributes, data types (with precision — varchar(255) vs text, decimal(10,2) vs float), and nullability constraints. Explain why each type was chosen.
2. **Relationship Mapping**: Define all relationships between entities (one-to-one, one-to-many, many-to-many). For many-to-many relationships, design the junction table with appropriate composite keys and any extra attributes. Include a text-based entity-relationship diagram.
3. **Primary Keys**: Choose between auto-increment integers, UUIDs, or ULIDs for primary keys, with justification based on the use case (distributed systems, URL exposure, sorting requirements).
4. **Indexes**: Design indexes for every query pattern mentioned in the requirements. Include composite indexes where queries filter on multiple columns. Explain the index selection rationale and the column ordering for composite indexes.
5. **Constraints**: Add foreign key constraints with appropriate ON DELETE behavior (CASCADE, SET NULL, RESTRICT), unique constraints, check constraints for valid value ranges, and default values.
6. **Audit Fields**: Include created_at, updated_at, and optionally deleted_at (soft delete) on every table. Add created_by and updated_by if the application has user context.
7. **Normalization Analysis**: State the normal form achieved (typically 3NF) and document any intentional denormalization with justification (e.g., storing a computed total for query performance).
8. **Migration Script**: Provide the complete CREATE TABLE statements in SQL for the specified database engine.

## INFORMATION ABOUT ME
- [DESCRIBE YOUR BUSINESS REQUIREMENTS IN DETAIL]
- [DATABASE ENGINE] (e.g., PostgreSQL, MySQL, SQLite)
- [EXPECTED SCALE] (rows per table, concurrent users)
- [ANY EXISTING TABLES TO INTEGRATE WITH]

## RESPONSE FORMAT
Deliver the schema as: (1) a text-based ER diagram, (2) a table-by-table specification with columns, types, constraints, and indexes, and (3) complete SQL migration scripts. Include comments explaining design decisions.