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

Gradle + Flyway

./gradlew flywayMigrate

Test migration on clean database

./gradlew flywayClean flywayMigrate

Check migration status

./gradlew flywayInfo

Validate migrations

./gradlew flywayValidate

Run Tests

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

-- 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

-- 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

-- 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)

-- 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

@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

@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:

-- 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:

-- 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: 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

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

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

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 to rollback:

• Dropping columns (data loss)

• Changing data types (data transformation)

• Deleting tables (data loss)

Include Rollback SQL

For complex migrations, document rollback steps:

-- Migration: V005__add_user_status.sql ALTER TABLE users ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'active';

-- Rollback (document in comments): -- ALTER TABLE users DROP COLUMN status;

Performance Tips

Indexing Strategy

✅ DO create indexes on:

• Foreign key columns

• Frequently queried columns (WHERE, JOIN)

• Columns used in ORDER BY

• Unique constraints

❌ DON'T create indexes on:

• Small tables (< 1000 rows)

• Columns that change frequently

• Low cardinality columns (gender, boolean)

Query Optimization

-- ❌ BAD - Missing index, full table scan SELECT * FROM users WHERE email = 'user@example.com';

-- ✅ GOOD - Index on email column CREATE INDEX idx_users_email ON users(email);

-- ❌ BAD - N+1 query problem SELECT * FROM users; -- 1 query SELECT * FROM tasks WHERE user_id = ?; -- N queries (one per user)

-- ✅ GOOD - Single query with JOIN SELECT u., t. FROM users u LEFT JOIN tasks t ON t.user_id = u.id;

Common Patterns to Follow

• Sequential migration versioning (V001, V002, V003...)

• Descriptive migration names (V004__add_user_status.sql)

• Idempotent migrations (can run multiple times safely)

• Test on clean database before committing

• Foreign keys with indexes for performance

• NOT NULL with defaults for required fields

• Timestamps for audit trail (created_at, updated_at)

What NOT to Do

❌ Don't modify existing migrations (create new one) ❌ Don't drop columns without data backup ❌ Don't forget indexes on foreign keys ❌ Don't use SELECT * in production queries ❌ Don't skip testing migrations on clean database ❌ Don't forget CASCADE behavior on foreign keys ❌ Don't create migrations that depend on data state

Focus Areas

When reading task sections, prioritize:

• requirements

• What schema changes needed

• technical-approach

• Migration strategy

• data-model

• Entity relationships

• migration

• Specific SQL requirements

Remember

• Test on clean database - always validate migration from scratch

• Indexes on foreign keys - critical for performance

• Sequential versioning - V001, V002, V003...

• Descriptive names - migration filename explains what it does

• Report blockers promptly - constraint issues, circular dependencies

• Document rollback - comment how to reverse if needed

• Validation is mandatory - migration must succeed before completion

Additional Resources

For deeper patterns and examples, see:

• PATTERNS.md - Complex schema patterns, performance optimization (load if needed)

• BLOCKERS.md - Detailed database-specific blockers (load if stuck)

• examples.md - Complete migration examples (load if uncertain)