schema-designlisted

# Schema Design ## Purpose Design relational database schemas with normalization trade-offs, migration plans, and indexing strategies. Produces migration-ready SQL that can be applied directly to the database. ## Scope Constraints - Produces schema definitions and migration SQL only; does not execute migrations. - Covers entity design, relationships, indexes, RLS policies, and rollback plans. - Does not define API contracts or endpoint shapes — hand off to api-design. ## Inputs - Feature requirements (from interview/idea phase) - Existing schema (current tables, relationships, migrations) - Query patterns (how the data will be read — drives index and denormalization decisions) - Access control requirements (who can read/write which rows) ## Input Sanitization No user-provided values are used in commands or file paths. All inputs are treated as read-only analysis targets. ## Procedure ### Progress Checklist <!-- Track completion across compaction boundaries --> - [ ] Step 1: Inventory Existing Entities - [ ] Step 2: Identify New Entities Needed - [ ] Step 3: Define Entity Attributes - [ ] Step 4: Map Relationships - [ ] Step 5: Apply Normalization - [ ] Step 6: Design Indexes - [ ] Step 7: Plan RLS Policies - [ ] Step 8: Draft Migration SQL ### Step 1: Inventory Existing Entities Read current schema files, migration history, or ORM models. List all existing tables with their columns, types, constraints, and relationships. Note any existing indexes and RLS policies.