Database Schema Generation Skill — v1.0

<!-- Generated by tools/exporter.py from skills/database_skill.md -->
<!-- Do not edit manually. Re-run: python tools/exporter.py -->

# Database Schema Generation Skill — v1.0

---

## 1. Purpose

This skill enables database architects and backend developers to:
- Generate idempotent SQL migration scripts
- Create normalized, performant schemas with proper constraints
- Define indexes and partitioning strategies
- Validate schemas against best practices
- Document schema changes with clear migration audit trails

The skill covers three RDBMS platforms: PostgreSQL, MySQL, and SQL Server.

---

## 2. Input

The skill receives:

1. **Entity Definitions** — entities with fields, types, constraints
   ```yaml
   entities:
     - name: users
       fields:
         - name: id
           type: uuid
           primary_key: true
         - name: email
           type: varchar(255)
           unique: true
           nullable: false
         - name: created_at
           type: timestamp
           default: current_timestamp
   ```

2. **Relationship Definitions** — foreign keys and cardinality
   ```yaml
   relationships:
     - from: orders
       to: users
       cardinality: many-to-one
       cascade_delete: true
   ```

3. **Indexing Requirements** — performance specifications
   ```yaml
   indexes:
     - table: users
       columns: [email]
       type: unique
     - table: orders
       columns: [user_id, created_at]
       type: composite
   ```

4. **Platform Selection** — target RDBMS (PostgreSQL, MySQL, SQL Server)

---

## 3. Output

The skill generates:

1. **Schema Creation Script** — `schema.sql`
   - CREATE TABLE statements with constraints
   - CREATE INDEX statements
   - CREATE SEQUENCE (PostgreSQL) or AUTO_INCREMENT (MySQL) statements

2. **Migration Script** — `migration_001_initial_schema.sql`
   - Idempotent: includes `IF NOT EXISTS` / `CREATE TABLE IF NOT EXISTS`
   - Versioned with timestamp prefix
   - Includes rollback information in comments

3. **Rollback Script** — `rollback_001_initial_schema.sql`
   - DROP TABLE statements (reverse order of dependencies)
   - DROP INDEX statements
   - Safely handles cascading deletes

4. **Schema Documentation** — `SCHEMA.md`
   - Table descriptions
   - Field documentation with business context
   - Relationship diagrams
   - Performance notes

5. **Validation Report** — includes checks for:
   - Primary key presence on all tables
   - Foreign key constraint validity
   - Index coverage on filter/join columns
   - Naming convention compliance
   - Idempotency of migration scripts

---

## 4. Process

### Step 1: Validate Input Schema
- Ensure all tables have primary keys
- Verify foreign key references exist
- Check for naming convention violations
- Detect circular dependencies

### Step 2: Generate Platform-Specific DDL
For each table and relationship, generate CREATE TABLE statements:
- Use platform-native data types (UUID in PostgreSQL, CHAR(36) in MySQL)
- Add constraints (NOT NULL, UNIQUE, CHECK, DEFAULT)
- Include foreign key declarations with ON DELETE behavior
- Add table-level comments documenting business purpose

### Step 3: Generate Indexes
Create indexes for:
- Foreign key columns (required for efficient joins)
- Unique columns (automatically indexed in most platforms)
- Filter columns in WHERE clauses
- Composite indexes for common query patterns

### Step 4: Create Migration Scripts
- Wrap all statements in transactions (BEGIN/COMMIT)
- Use `IF NOT EXISTS` for safe re-runs (idempotency)
- Include migration version number and timestamp
- Add rollback section as comments showing reverse operations

### Step 5: Validate and Report
- Run DDL syntax check for target platform
- Verify all constraints are satisfiable
- Check index coverage on foreign keys
- Generate validation report

---

## 5. Code Example: Users Table with Indexes

### PostgreSQL

```sql
-- Migration: 001_initial_schema.sql
-- Created: 2025-05-20T10:00:00Z
-- Purpose: Create users and orders tables with indexes

BEGIN;

-- Users table with UUID primary key
CREATE TABLE IF NOT EXISTS users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) NOT NULL UNIQUE,
  username VARCHAR(100) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  first_name VARCHAR(100),
  last_name VARCHAR(100),
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT email_format CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$')
);

-- Indexes on users table
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
CREATE INDEX IF NOT EXISTS idx_users_created_at ON users(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_users_active_created ON users(is_active, created_at DESC);

-- Orders table with foreign key to users
CREATE TABLE IF NOT EXISTS orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  order_number VARCHAR(50) NOT NULL UNIQUE,
  total_amount DECIMAL(10, 2) NOT NULL CHECK (total_amount > 0),
  status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'completed', 'cancelled')),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Indexes on orders table
CREATE INDEX IF NOT EXISTS idx_orders_user_id ON orders(user_id);
CREATE INDEX IF NOT EXISTS idx_orders_user_created ON orders(user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(status, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_orders_created_at ON orders(created_at DESC);

-- Audit trail table
CREATE TABLE IF NOT EXISTS audit_log (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  entity_type VARCHAR(50) NOT NULL,
  entity_id UUID NOT NULL,
  action VARCHAR(20) NOT NULL CHECK (action IN ('INSERT', 'UPDATE', 'DELETE')),
  changed_by UUID REFERENCES users(id) ON DELETE SET NULL,
  changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  old_values JSONB,
  new_values JSONB
);

CREATE INDEX IF NOT EXISTS idx_audit_entity ON audit_log(entity_type, entity_id);
CREATE INDEX IF NOT EXISTS idx_audit_changed_at ON audit_log(changed_at DESC);

COMMIT;

-- Rollback: DROP TABLE IF EXISTS audit_log CASCADE;
-- Rollback: DROP TABLE IF EXISTS orders CASCADE;
-- Rollback: DROP TABLE IF EXISTS users CASCADE;
```

### MySQL

```sql
-- Migration: 001_initial_schema.sql
-- Created: 2025-05-20T10:00:00Z
-- Purpose: Create users and orders tables with indexes

START TRANSACTION;

-- Users table with INT auto-increment primary key
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  username VARCHAR(100) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  first_name VARCHAR(100),
  last_name VARCHAR(100),
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT email_format CHECK (email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$'),
  KEY idx_email (email),
  KEY idx_username (username),
  KEY idx_created_at (created_at DESC),
  KEY idx_active_created (is_active, created_at DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Orders table with foreign key to users
CREATE TABLE IF NOT EXISTS orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  order_number VARCHAR(50) NOT NULL UNIQUE,
  total_amount DECIMAL(10, 2) NOT NULL CHECK (total_amount > 0),
  status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'completed', 'cancelled')),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_orders_users FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  KEY idx_user_id (user_id),
  KEY idx_user_created (user_id, created_at DESC),
  KEY idx_status (status, created_at DESC),
  KEY idx_created_at (created_at DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Audit trail table
CREATE TABLE IF NOT EXISTS audit_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  entity_type VARCHAR(50) NOT NULL,
  entity_id INT NOT NULL,
  action VARCHAR(20) NOT NULL CHECK (action IN ('INSERT', 'UPDATE', 'DELETE')),
  changed_by INT REFERENCES users(id) ON DELETE SET NULL,
  changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  old_values JSON,
  new_values JSON,
  KEY idx_entity (entity_type, entity_id),
  KEY idx_changed_at (changed_at DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

COMMIT;

-- Rollback: DROP TABLE IF EXISTS audit_log;
-- Rollback: DROP TABLE IF EXISTS orders;
-- Rollback: DROP TABLE IF EXISTS users;
```

### SQL Server

```sql
-- Migration: 001_initial_schema.sql
-- Created: 2025-05-20T10:00:00Z
-- Purpose: Create users and orders tables with indexes

BEGIN TRANSACTION;

-- Users table with NEWID() for GUID primary key
CREATE TABLE IF NOT EXISTS [dbo].[users] (
  [id] UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
  [email] VARCHAR(255) NOT NULL UNIQUE,
  [username] VARCHAR(100) NOT NULL UNIQUE,
  [password_hash] VARCHAR(255) NOT NULL,
  [first_name] VARCHAR(100),
  [last_name] VARCHAR(100),
  [is_active] BIT DEFAULT 1,
  [created_at] DATETIME DEFAULT GETUTCDATE(),
  [updated_at] DATETIME DEFAULT GETUTCDATE(),
  CONSTRAINT [ck_users_email] CHECK (email LIKE '%@%.%')
);

-- Indexes on users table
CREATE INDEX [idx_users_email] ON [dbo].[users]([email]);
CREATE INDEX [idx_users_username] ON [dbo].[users]([username]);
CREATE INDEX [idx_users_created_at] ON [dbo].[users]([created_at] DESC);
CREATE INDEX [idx_users_active_created] ON [dbo].[users]([is_active], [created_at] DESC);

-- Orders table with foreign key to users
CREATE TABLE IF NOT EXISTS [dbo].[orders] (
  [id] UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
  [user_id] UNIQUEIDENTIFIER NOT NULL REFERENCES [dbo].[users]([id]) ON DELETE CASCADE,
  [order_number] VARCHAR(50) NOT NULL UNIQUE,
  [total_amount] DECIMAL(10, 2) NOT NULL CHECK (total_amount > 0),
  [status] VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'completed', 'cancelled')),
  [created_at] DATETIME DEFAULT GETUTCDATE(),
  [updated_at] DATETIME DEFAULT GETUTCDATE()
);

-- Indexes on orders table
CREATE INDEX [idx_orders_user_id] ON [dbo].[orders]([user_id]);
CREATE INDEX [idx_orders_user_created] ON [dbo].[orders]([user_id], [created_at] DESC);
CREATE INDEX [idx_orders_status] ON [dbo].[orders]([status], [created_at] DESC);
CREATE INDEX [idx_orders_created_at] ON [dbo].[orders]([created_at] DESC);

-- Audit trail table
CREATE TABLE IF NOT EXISTS [dbo].[audit_log] (
  [id] UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
  [entity_type] VARCHAR(50) NOT NULL,
  [entity_id] UNIQUEIDENTIFIER NOT NULL,
  [action] VARCHAR(20) NOT NULL CHECK (action IN ('INSERT', 'UPDATE', 'DELETE')),
  [changed_by] UNIQUEIDENTIFIER REFERENCES [dbo].[users]([id]) ON DELETE SET NULL,
  [changed_at] DATETIME DEFAULT GETUTCDATE(),
  [old_values] NVARCHAR(MAX),
  [new_values] NVARCHAR(MAX)
);

CREATE INDEX [idx_audit_entity] ON [dbo].[audit_log]([entity_type], [entity_id]);
CREATE INDEX [idx_audit_changed_at] ON [dbo].[audit_log]([changed_at] DESC);

COMMIT TRANSACTION;

-- Rollback: DROP TABLE IF EXISTS [dbo].[audit_log];
-- Rollback: DROP TABLE IF EXISTS [dbo].[orders];
-- Rollback: DROP TABLE IF EXISTS [dbo].[users];
```

---

## 6. Validation Checklist

- [ ] **Syntax Valid** — All DDL passes platform-specific validation (try `sqlparse` or native parser)
- [ ] **Primary Keys** — Every table has exactly one primary key
- [ ] **Foreign Keys** — All FK references point to existing tables and columns
- [ ] **Indexes** — All FK columns are indexed (for join performance)
- [ ] **Indexes** — All frequent filter columns have indexes
- [ ] **Composite Indexes** — Common WHERE + ORDER BY patterns have multi-column indexes
- [ ] **Constraints** — CHECK constraints are accurate and enforced
- [ ] **Uniqueness** — UNIQUE constraints prevent duplicates where required
- [ ] **Defaults** — DEFAULT values are sensible (e.g., timestamps, booleans)
- [ ] **Idempotency** — Migration includes `IF NOT EXISTS` or `CREATE OR REPLACE`
- [ ] **Transactions** — All migrations are wrapped in BEGIN/COMMIT
- [ ] **Rollback Path** — Each migration includes commented rollback SQL
- [ ] **Data Types** — Types are platform-native and match application ORM expectations
- [ ] **Collation** — Character sets and collation are consistent (UTF-8 recommended)
- [ ] **Comments** — Table and column purposes are documented
- [ ] **No Circular Dependencies** — Foreign key references don't create cycles
- [ ] **Cascade Rules** — ON DELETE / ON UPDATE behavior is explicitly set
- [ ] **Performance** — No missing indexes on frequently queried columns
- [ ] **Partitioning** — Large tables have appropriate partition strategy (if needed)
- [ ] **Audit Trail** — Schema includes change tracking mechanism

---

## 7. Success Criteria

1. **Schema Creation Succeeds** — Run `schema.sql` against empty database, all tables created without errors
2. **Data Insertion Works** — Insert sample data respecting all constraints
3. **Foreign Keys Enforced** — Inserting invalid FK values raises constraint violation
4. **Indexes Exist** — Query `information_schema` or `sys.indexes` to verify all indexes present
5. **Migrations Are Idempotent** — Run migration script twice, second run succeeds without errors
6. **Rollback Works** — Execute rollback script, all tables and indexes dropped cleanly
7. **Re-creation After Rollback** — Run migration again after rollback, schema recreated successfully
8. **Query Performance** — Queries on indexed columns use index scan (check execution plans)
9. **Constraint Enforcement** — Duplicate unique values are rejected; null values respected in NOT NULL columns
10. **Cross-Platform** — Schema is valid and executable on PostgreSQL, MySQL, and SQL Server

---

## 8. Integration with Agent Workflows

This skill is consumed by database-focused agents:

- **Database Architect Agent** — Uses schema generation to design normalized models
- **Backend Developer Agent** — Uses migration scripts in CI/CD pipelines
- **DevOps/DBA Agent** — Uses rollback scripts for disaster recovery planning

When invoked, the agent provides the entity definitions (from domain model or existing codebase analysis) and requests generated scripts for a specific platform.

---

## 9. Performance Optimization Guidelines

### Indexing Strategy

**Single-Column Indexes:**
- Primary key (automatic in most RDBMS)
- Foreign key columns (enables efficient joins)
- Unique columns (automatic in most RDBMS)
- High-cardinality filter columns (user_id, email, created_at)

**Composite Indexes (Multi-Column):**
- Query patterns: WHERE user_id AND created_at DESC → index (user_id, created_at DESC)
- Covering indexes: Add frequently selected columns to avoid table lookups
- Example: `CREATE INDEX idx_orders_user_created_amount ON orders(user_id, created_at DESC) INCLUDE (total_amount);`

**Partitioning (Large Tables):**
- Partition `orders` by date range (monthly or yearly)
- Partition `audit_log` by entity_type + date
- Example: `PARTITION BY RANGE (YEAR(created_at))`

### Normalization

- **3NF minimum** — eliminate transitive dependencies
- **BCNF recommended** — stricter than 3NF for complex schemas
- **Avoid denormalization** unless performance testing proves necessary
- **Use views** for denormalized read queries (don't duplicate data)

---

## 10. Error Handling

### Common Errors and Fixes

| Error | Cause | Fix |
|-------|-------|-----|
| "Duplicate key value" on INSERT | Unique constraint violated | Check for existing data; update migration to skip or merge |
| "Foreign key constraint fails" | FK reference doesn't exist | Verify FK table exists; may need to reorder migration steps |
| "Syntax error near CREATE" | Platform-specific syntax mismatch | Check data type and keyword support (e.g., SERIAL vs AUTO_INCREMENT) |
| "Index already exists" | Non-idempotent migration | Add `IF NOT EXISTS` clause |
| "Table is locked" | Concurrent access during migration | Run migrations in single-threaded mode or acquire exclusive lock |

---

## 11. Examples by Pattern

### Audit Logging Pattern

Implement table-level audit trail:
```sql
CREATE TABLE audit_log (
  id UUID PRIMARY KEY,
  table_name VARCHAR(100),
  record_id UUID,
  action VARCHAR(10) CHECK (action IN ('INSERT', 'UPDATE', 'DELETE')),
  old_row JSONB,
  new_row JSONB,
  changed_by UUID,
  changed_at TIMESTAMP
);

CREATE TRIGGER audit_users_trigger AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();
```

### Soft Delete Pattern

Implement logical deletion (data retention):
```sql
CREATE TABLE users (
  id UUID PRIMARY KEY,
  email VARCHAR(255),
  deleted_at TIMESTAMP,  -- NULL if active, timestamp if deleted
  
  CONSTRAINT users_unique_email_active CHECK (deleted_at IS NOT NULL OR email IS NOT NULL)
);

CREATE INDEX idx_users_active ON users(deleted_at) WHERE deleted_at IS NULL;
```

### Time Series Data Pattern

Optimize for time-based queries:
```sql
CREATE TABLE metrics (
  id UUID PRIMARY KEY,
  metric_name VARCHAR(100),
  value DECIMAL(10, 2),
  measured_at TIMESTAMP,
  
  CONSTRAINT metrics_pkey PRIMARY KEY (metric_name, measured_at)
) PARTITION BY RANGE (YEAR(measured_at));

CREATE INDEX idx_metrics_time ON metrics(measured_at DESC);
```

---

## 12. Platform-Specific Notes

### PostgreSQL
- Uses `UUID` for globally unique identifiers
- Supports `JSONB` for semi-structured data
- `gen_random_uuid()` for default UUID values
- `CHECK` constraints with regular expressions (using `~` operator)
- Triggers for advanced features (auditing, soft deletes)

### MySQL
- Uses `INT AUTO_INCREMENT` for surrogate keys
- `JSON` type for semi-structured data (less efficient than PostgreSQL JSONB)
- `CHECK` constraints with `REGEXP` operator (limited pattern support)
- Event scheduler for time-based automation
- Foreign keys only work with InnoDB engine

### SQL Server
- Uses `UNIQUEIDENTIFIER` with `NEWID()` for GUIDs
- `NVARCHAR(MAX)` for JSON/document storage
- `CHECK` constraints with simple patterns (limited)
- Triggers and service broker for advanced features
- Indexed views for materialized aggregate queries

---