Dev Database Schema Design Document
===================================

Create a database schema design document for [application name].

Application details:
- Application: [name and purpose]
- Database type: [PostgreSQL/MySQL/MongoDB]
- Scale expectation: [records/users/transactions]
- Key entities: [list main objects]
- Relationships: [describe connections]

DATABASE SCHEMA DESIGN

1. DESIGN PRINCIPLES
   - Normalization level: [3NF/denormalized for performance]
   - Naming convention: [snake_case/camelCase]
   - Primary keys: [UUID/auto-increment — rationale]
   - Timestamps: [created_at/updated_at on all tables]
   - Soft deletes: [deleted_at vs hard delete]

2. ENTITY DEFINITIONS

   TABLE: [table_name]
   Purpose: [what this table stores]

   | Column | Type | Constraints | Description |
   | id | UUID/BIGINT | PRIMARY KEY | |
   | [column] | [type] | [NOT NULL/UNIQUE] | [description] |
   | created_at | TIMESTAMP | NOT NULL DEFAULT NOW() | |
   | updated_at | TIMESTAMP | NOT NULL DEFAULT NOW() | |

   [Repeat for each table]

3. RELATIONSHIPS

   ONE TO MANY
   - [Table A] has many [Table B]
   - Foreign key: [table_b.table_a_id]
   - Index on foreign key: YES

   MANY TO MANY
   - [Table A] and [Table B] via [junction_table]
   - Junction table: [name]
   - Composite primary key: [a_id, b_id]

4. INDEXES

   | Table | Column(s) | Index Type | Reason |
   | users | email | UNIQUE | Login lookup |
   | orders | user_id | BTREE | Foreign key |
   | [table] | [columns] | [type] | [reason] |

5. CONSTRAINTS
   - Check constraints: [business rules]
   - Foreign key constraints: [with ON DELETE behavior]
   - Unique constraints: [natural keys]

6. MIGRATIONS STRATEGY
   - Migration tool: [Flyway/Liquibase/Alembic/Knex]
   - Naming convention: [V001__description.sql]
   - Backward compatibility: [zero-downtime migrations]
   - Rollback plan: [per migration]

7. PERFORMANCE CONSIDERATIONS
   - Partitioning: [if large tables]
   - Archiving strategy: [old data]
   - Connection pooling: [settings]
   - Query optimization: [common query patterns]

Source: https://promptzyo.com/prompt/dev-database-schema-design-document