Database Implementation

What this skill does

# Database Implementation Skill

Domain-specific guidance for database schema design, migrations, and data modeling.

## When To Use This Skill

Load this Skill when task has tags:
- `database`, `migration`, `schema`, `sql`, `flyway`
- `exposed`, `orm`, `query`, `index`, `constraint`

## Validation Commands

### Run Migrations
```bash
# Gradle + Flyway
./gradlew flywayMigrate

# Test migration on clean database
./gradlew flywayClean flywayMigrate

# Check migration status
./gradlew flywayInfo

# Validate migrations
./gradlew flywayValidate
```

### Run Tests
```bash
# Migration tests
./gradlew test --tests "*migration*"

# Database integration tests
./gradlew test --tests "*Repository*"

# All tests
./gradlew test
```

## Success Criteria (Before Completing Task)

✅ **Migration runs without errors** on clean database
✅ **Schema matches design specifications**
✅ **Indexes created correctly**
✅ **Constraints validate as expected**
✅ **Rollback works** (if applicable)
✅ **Tests pass** with new schema

## Common Database Tasks

### Creating Migrations
- Add tables with columns, constraints
- Create indexes for performance
- Add foreign keys for referential integrity
- Modify existing schema (ALTER TABLE)
- Seed data (reference data)

### ORM Models
- Map entities to tables (Exposed, JPA)
- Define relationships (one-to-many, many-to-many)
- Configure cascading behavior
- Define custom queries

### Query Optimization
- Add indexes for frequently queried columns
- Analyze query plans (EXPLAIN)
- Optimize N+1 query problems
- Use appropriate JOIN types

## Migration Patterns

### Create Table

```sql
-- V001__create_users_table.sql
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created_at ON users(created_at);
```

### Add Column

```sql
-- V002__add_users_phone.sql
ALTER TABLE users
ADD COLUMN phone VARCHAR(20);

-- Add with default value
ALTER TABLE users
ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT true;
```

### Create Foreign Key

```sql
-- V003__create_tasks_table.sql
CREATE TABLE tasks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title VARCHAR(500) NOT NULL,
    user_id UUID NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- Foreign key with cascade
    CONSTRAINT fk_tasks_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE
);

CREATE INDEX idx_tasks_user_id ON tasks(user_id);
```

### Create Junction Table (Many-to-Many)

```sql
-- V004__create_user_roles.sql
CREATE TABLE user_roles (
    user_id UUID NOT NULL,
    role_id UUID NOT NULL,
    assigned_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (user_id, role_id),

    CONSTRAINT fk_user_roles_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_user_roles_role
        FOREIGN KEY (role_id)
        REFERENCES roles(id)
        ON DELETE CASCADE
);

CREATE INDEX idx_user_roles_user_id ON user_roles(user_id);
CREATE INDEX idx_user_roles_role_id ON user_roles(role_id);
```

## Testing Migrations

### Test Migration Execution

```kotlin
@Test
fun `migration V004 creates user_roles table`() {
    // Arrange - Clean database
    flyway.clean()

    // Act - Run migrations
    flyway.migrate()

    // Assert - Check table exists
    val tableExists = database.useConnection { connection ->
        val meta = connection.metaData
        val rs = meta.getTables(null, null, "user_roles", null)
        rs.next()
    }

    assertTrue(tableExists, "user_roles table should exist after migration")
}
```

### Test Constraints

```kotlin
@Test
fun `user_roles enforces foreign key constraint`() {
    // Arrange
    val invalidUserId = UUID.randomUUID()
    val role = createTestRole()

    // Act & Assert
    assertThrows<SQLException> {
        database.transaction {
            UserRoles.insert {
                it[userId] = invalidUserId  // Invalid - user doesn't exist
                it[roleId] = role.id
            }
        }
    }
}
```

## Common Blocker Scenarios

### Blocker 1: Migration Fails on Existing Data

**Issue:** Adding NOT NULL column to table with existing rows
```
ERROR: column "status" contains null values
```

**What to try:**
- Add column as nullable first
- Update existing rows with default value
- Then alter column to NOT NULL

**Example fix:**
```sql
-- Step 1: Add nullable
ALTER TABLE tasks ADD COLUMN status VARCHAR(20);

-- Step 2: Update existing rows
UPDATE tasks SET status = 'pending' WHERE status IS NULL;

-- Step 3: Make NOT NULL
ALTER TABLE tasks ALTER COLUMN status SET NOT NULL;
```

### Blocker 2: Circular Foreign Key Dependencies

**Issue:** Table A references B, B references A - which to create first?

**What to try:**
- Create both tables without foreign keys first
- Add foreign keys in separate migration after both exist

**Example:**
```sql
-- V001: Create tables without FKs
CREATE TABLE users (...);
CREATE TABLE profiles (...);

-- V002: Add foreign keys
ALTER TABLE users ADD CONSTRAINT fk_users_profile ...;
ALTER TABLE profiles ADD CONSTRAINT fk_profiles_user ...;
```

### Blocker 3: Index Creation Takes Too Long

**Issue:** Creating index on large table times out

**What to try:**
- Use CREATE INDEX CONCURRENTLY (PostgreSQL)
- Create index during low-traffic period
- Check if similar index already exists

### Blocker 4: Data Type Mismatch

**Issue:** ORM expects UUID but database has VARCHAR

**What to try:**
- Check migration SQL - correct type used?
- Check ORM mapping - correct type specified?
- Migrate data type if needed:
  ```sql
  ALTER TABLE tasks ALTER COLUMN id TYPE UUID USING id::uuid;
  ```

### Blocker 5: Missing Prerequisite Table

**Issue:** Foreign key references table that doesn't exist yet

**What to try:**
- Check migration order - migrations run in version order (V001, V002, etc.)
- Ensure referenced table created in earlier migration
- Check for typos in table names

**If blocked:** Report to orchestrator - migration order issue or missing prerequisite

## Blocker Report Format

```
⚠️ BLOCKED - Requires Senior Engineer

Issue: [Specific problem - migration fails, constraint violation, etc.]

Attempted Fixes:
- [What you tried #1]
- [What you tried #2]
- [Why attempts didn't work]

Root Cause (if known): [Your analysis]

Partial Progress: [What work you DID complete]

Context for Senior Engineer:
- Migration SQL: [Paste migration]
- Error output: [Database error]
- Related migrations: [Dependencies]

Requires: [What needs to happen]
```

## Exposed ORM Patterns

### Table Definition

```kotlin
object Users : UUIDTable("users") {
    val email = varchar("email", 255).uniqueIndex()
    val passwordHash = varchar("password_hash", 255)
    val name = varchar("name", 255)
    val createdAt = timestamp("created_at").defaultExpression(CurrentTimestamp())
    val updatedAt = timestamp("updated_at").defaultExpression(CurrentTimestamp())
}
```

### Foreign Key Relationship

```kotlin
object Tasks : UUIDTable("tasks") {
    val title = varchar("title", 500)
    val userId = reference("user_id", Users)
    val createdAt = timestamp("created_at").defaultExpression(CurrentTimestamp())
}
```

### Query with Join

```kotlin
fun findTasksWithUser(userId: UUID): List<TaskWithUser> {
    return (Tasks innerJoin Users)
        .select { Tasks.userId eq userId }
        .map { row ->
            TaskWithUser(
                task = rowToTask(row),
                user = rowToUser(row)
            )
        }
}
```

## Rollback Strategies

### Reversible Migrations

**Good (can rollback):**
- Adding nullable columns
- Adding indexes
- Creating new tables (if no data)

**Difficult