database-design

<!-- This is a SCAFFOLD skill --> # Database Design ## When to Apply - **Classification**: feature (if new tables/columns), architecture-change (schema redesign), hotfix (data-related bug) - **Phase**: /plan (schema design), /implement (migration creation), /review (schema review), /test (data integrity) - **Trigger**: Task involves creating tables, modifying schema, writing migrations, or changing data access patterns ## Conventions > **Customize after /app-init**: Replace these generic conventions with your project's ADR and ORM-specific patterns. ### Table Design - Every table MUST have: `id` (primary key), `created_at`, `updated_at` - Use UUIDs or auto-increment IDs consistently (per ADR decision) - Soft delete: add `deleted_at` column if ADR specifies soft delete - Table names: plural, snake_case (e.g., `users`, `order_items`) - Column names: snake_case (e.g., `first_name`, `is_active`) ### Relationship Patterns | Relationship | Implementation | Example | |---|---|---| | One-to-Many | Foreign key on "many" side | `orders.user_id → users.id` | | Many-to-Many | Junction table | `user_roles (user_id, role_id)` | | One-to-One | Foreign key + unique constraint | `user_profiles.user_id (UNIQUE) → users.id` | | Self-referencing | Foreign key to same table | `categories.parent_id → categories.id` | ### Index Strategy - Every foreign key column MUST have an index - Columns used in WHERE clauses frequently → add index - Composite indexes: put the most selective column firs...