AI Prompts Library

Generate a database migration for [DATABASE - Postgres / MySQL / SQLite].

Goal: [DESCRIBE - add column, rename table, add index, etc.].
Framework: [Prisma / Knex / Rails / Alembic / raw SQL].

Requirements:
1. The migration must be safe to run on a table with millions of rows in production. No table-level locks if avoidable.
2. Include both UP and DOWN migrations.
3. Defaults handled correctly - if adding a NOT NULL column, propose a backfill strategy.
4. Add appropriate indexes.
5. Name the migration descriptively with a timestamp prefix.

Return the migration file content and a one-paragraph runbook explaining how to apply it safely in production.

You are an expert database architect. Design a normalized, performant database schema based on the application requirements below.

**Application:** [DESCRIBE_YOUR_APPLICATION]
**Database Engine:** [PostgreSQL/MySQL/SQLite/MongoDB]
**Expected Scale:** [Number of users, records, transactions per day]
**Key Requirements:** [LIST_MAIN_FEATURES]

**Your schema design must include:**

1. **Entity-Relationship Diagram** (describe in text format):
   - All entities with their attributes and types
   - Primary keys, foreign keys, and unique constraints
   - Relationship types (1:1, 1:N, M:N) with cardinality
   - Junction tables for M:N relationships

2. **Table Definitions** (SQL CREATE statements):
   - Proper data types (VARCHAR lengths, DECIMAL precision, ENUM values)
   - NOT NULL constraints where appropriate
   - DEFAULT values (timestamps, UUIDs, boolean flags)
   - CHECK constraints for data validation
   - Proper indexing strategy (B-tree, GIN, GiST where appropriate)
   - Composite indexes for common query patterns
   - Partial indexes for filtered queries

3. **Normalization Analysis**:
   - Confirm the schema is at least in 3NF
   - Document any intentional denormalization with justification
   - Identify potential data anomalies and how they are prevented

4. **Performance Considerations**:
   - Index recommendations based on expected query patterns
   - Partitioning strategy if tables will exceed 10M rows
   - Materialized views for complex reporting queries
   - Connection pooling and caching recommendations

5. **Migration Script**: A versioned migration script (using a tool like Alembic, Flyway, or Prisma) that creates the schema from scratch.

6. **Seed Data**: Sample INSERT statements with realistic test data for development.

7. **Common Queries**: Write the 5 most common queries the application will need, with EXPLAIN analysis notes.

You are a database performance expert specializing in SQL query optimization. Analyze the provided query and suggest improvements for maximum performance.

**Query to Optimize:**
```sql
[PASTE YOUR SQL QUERY HERE]
```

**Database:** [PostgreSQL / MySQL / SQL Server / SQLite]
**Table Size:** [Approximate row counts for each table involved]
**Current Execution Time:** [If known]
**Available Indexes:** [List existing indexes, or say 'unknown']

**Optimization Analysis:**

1. **Query Plan Analysis:**
   - Identify sequential scans that should be index scans
   - Detect sort operations that could be avoided with indexes
   - Find hash joins that could be merge joins or nested loops
   - Spot unnecessary subqueries or CTEs

2. **Index Recommendations:**
   - Covering indexes for the most common queries
   - Composite indexes (correct column order for the query pattern)
   - Partial indexes for queries with WHERE filters
   - Expression indexes for computed conditions
   - Include CONCURRENTLY option for production index creation

3. **Query Rewriting:**
   - Replace correlated subqueries with JOINs
   - Convert IN (SELECT ...) to EXISTS where appropriate
   - Use window functions instead of self-joins
   - Replace DISTINCT with GROUP BY when more efficient
   - Optimize pagination (keyset pagination vs OFFSET)
   - Use UNION ALL instead of UNION when duplicates are impossible

4. **Schema Suggestions:**
   - Denormalization opportunities for read-heavy queries
   - Materialized views for complex aggregations
   - Partitioning strategy for large tables
   - Data type optimizations (e.g., INT vs BIGINT, VARCHAR vs TEXT)

5. **Optimized Query:**
   - Provide the rewritten query
   - Include EXPLAIN ANALYZE output comparison notes
   - Estimate the performance improvement

**Output**: The optimized query, required index DDL statements, and a brief explanation of each change and its expected impact.

Generate database migration script for [DATABASE: PostgreSQL/MySQL/MongoDB].

**Migration Type:**
- [ ] Create table
- [ ] Alter table
- [ ] Add column
- [ ] Drop column
- [ ] Add index
- [ ] Data migration

**Details:**
[DESCRIBE SCHEMA CHANGES]

**Generate:**

1. **Up Migration** (apply changes)
2. **Down Migration** (rollback)
3. **Data Migration** (if needed)
4. **Validation Queries**

**Requirements:**
- Idempotent (can run multiple times safely)
- Transaction-wrapped
- Rollback-safe
- Include comments
- Handle edge cases
- Test data examples
- Performance considerations for large tables

**Also provide:**
- Pre-migration checklist
- Post-migration validation
- Backup recommendations

Design database schema for [APPLICATION]:

**Naming Conventions**:
- Tables: plural, snake_case (users, order_items)
- Columns: singular, snake_case (user_id, created_at)
- Primary keys: id (integer, auto-increment)
- Foreign keys: [table_singular]_id

**Standard Columns** (all tables):
- id: Primary key
- created_at: Timestamp
- updated_at: Timestamp
- deleted_at: Soft deletes (optional)

**Relationships**:
- One-to-Many: Foreign key in child table
- Many-to-Many: Junction table
- One-to-One: Foreign key with UNIQUE constraint

**Indexes**:
- Primary key (automatic)
- Foreign keys
- Frequently queried columns
- Composite indexes for multi-column queries

**Data Types**:
- Use appropriate sizes (VARCHAR vs TEXT)
- ENUM for fixed options
- JSON for flexible data (use sparingly)
- UUID for distributed systems

**Constraints**:
- NOT NULL where appropriate
- UNIQUE for natural keys
- CHECK for validation
- DEFAULT values

**Normalization**:
- 3NF for OLTP
- Denormalize for read-heavy (with care)
- Avoid EAV anti-pattern

**Example Schema**:
```sql
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);
```

Analyze and optimize the following SQL query for maximum performance.

**Current Query:**
```sql
[PASTE YOUR SQL QUERY HERE]
```

**Database Context:**
- Database System: [MySQL/PostgreSQL/SQL Server/Oracle]
- Table row counts: [TABLE1: X rows, TABLE2: Y rows]
- Current indexes: [LIST EXISTING INDEXES]
- Query execution time: [CURRENT TIME]
- Use case: [DESCRIPTION OF WHAT QUERY DOES]

**Performance Requirements:**
- Target execution time: [GOAL]
- Frequency: [How often this query runs]
- Data freshness needs: [Real-time/Near real-time/Can be cached]

**Please provide:**

1. **Performance Analysis**
   - Identify performance bottlenecks
   - Explain current query execution plan
   - Point out inefficient operations (full table scans, cartesian products, etc.)

2. **Optimized Query**
   - Rewritten query with improvements
   - Inline comments explaining changes
   - Alternative approaches if multiple solutions exist

3. **Indexing Strategy**
   - Recommended indexes to create
   - Explain which clauses each index helps
   - Consider trade-offs (write performance, storage)

4. **Additional Optimizations**
   - Partitioning recommendations
   - Caching opportunities
   - Database configuration tweaks
   - Query refactoring into multiple queries if beneficial

5. **Expected Performance Gain**
   - Estimated improvement
   - Explain why these changes help

6. **Monitoring & Maintenance**
   - What metrics to track
   - When to revisit the optimization