Database Schema Design

Design the database schema for [THE FEATURE/SYSTEM, e.g. "orders, payments, and refunds for our marketplace"].

The entities and how they relate, in plain language: [DESCRIBE THE DOMAIN: what exists, what belongs to what, what changes often]
Database: [POSTGRES (assumed) / MYSQL / OTHER + VERSION]
Scale expectations, honestly: [ROWS/GROWTH FOR THE BIG TABLES: "small forever" changes decisions vs "millions of writes/day"]
Access patterns: [THE 3-5 QUERIES THAT WILL RUN CONSTANTLY: schemas are designed for reads that actually happen]
Existing conventions: [PASTE A REPRESENTATIVE EXISTING TABLE, or "greenfield"]

Design with these defaults, deviating only with stated reasons:
1. **Normalize to 3NF first**: then denormalize only where a listed access pattern justifies it, with the duplicated invariant documented ("orders.customer_email is a snapshot at purchase time, intentionally").
2. **Primary keys by ownership:** bigint identity when this database generates all IDs; UUIDv7 (time-ordered: B-tree friendly, unlike v4) when IDs are client-generated, distributed, or externally exposed. Never UUIDv4 on large write-heavy tables (random inserts shred index locality). Never expose bare sequence IDs publicly (enumeration). Pair internal bigint with an external identifier if both needs exist.
3. **Every foreign key gets an explicit index**: Postgres does not auto-index FK columns, and the missing-FK-index is the classic cause of mystery lock storms on parent deletes.
4. **timestamptz, not timestamp**: created_at/updated_at NOT NULL DEFAULT now() on every table.
5. **Soft delete only if undelete is a real requirement**: deleted_at poisons every query and every unique constraint (which then need partial indexes). Default to hard delete + an audit/archive table; if I insist on soft delete, show the partial unique index pattern and the query-filter discipline it commits me to.
6. **JSONB only for genuinely schemaless data** (user-defined fields, raw payload capture): anything filtered, joined, or constrained gets real columns.
7. **Constraints in the database:** NOT NULL by default, CHECK constraints for domain rules (status enums, non-negative amounts), UNIQUE where the business says unique; the database is the last line of defense against every future bug.

Deliver:
- The DDL, commented: every non-obvious decision gets its one-line why.
- The index plan: each index tied to a listed access pattern; no speculative indexes ("every index taxes every write").
- The ER summary in text (tables, relationships, cardinality).
- **The migration path**: if this touches existing tables: expand → backfill → contract as three separate deploy steps, batched backfill for big tables, NOT VALID + VALIDATE for new constraints, CREATE INDEX CONCURRENTLY: nothing that takes a long lock on a hot table.
- The 3 questions my domain description left open that most affect the schema, with your assumption marked for each.

Rules: design for the access patterns I listed, not hypothetical ones. Where my scale claims and my design instincts conflict (e.g. premature sharding talk on a small-forever table), say so.