database-table-creator

Database Table Creator Skill

Creates complete database table implementation with SQL migration, Kotlin code, and comprehensive tests.

What This Skill Does

Generates:

1. SQL Migration - PostgreSQL table with soft deletes, indexes, triggers
2. Kotlin Table Object - Exposed ORM table definition (extends UUIDTable)
3. Entity Data Class - Kotlin entity implementing Entity<Instant>
4. Repository Interface + Implementation - CRUD operations with soft delete
5. Repository Tests - Comprehensive test coverage (7+ tests)
6. Factory Bean - Dependency injection registration

Critical Rules

Database Design (rules/database/SCHEMA.md)

SQL Requirements:

• ❌ NO foreign key constraints (NEVER use REFERENCES or FOREIGN KEY)
• ✅ Use TEXT for strings (NOT VARCHAR)
• ✅ Include: id (UUID), created_at, updated_at, deleted_at
• ✅ ALL indexes MUST have WHERE deleted_at IS NULL
• ✅ Add soft delete index: WHERE deleted_at IS NOT NULL
• ✅ Add update trigger for updated_at

Kotlin Requirements:

• ✅ Table MUST extend UUIDTable (NOT Table)
• ✅ Entity MUST implement Entity<Instant>
• ✅ ALL queries MUST filter deletedAt.isNull()
• ❌ NO !! operators (forbidden by pre-commit hooks)
• ✅ Soft delete only (set deletedAt, never hard delete)

Workflow

Step 1: Create SQL Migration

Location: database-migration/sql/{number}-{description}.sql

-- ============================================================================
-- Description: [Describe what this migration does]
-- Table: {table_name}
-- ============================================================================

CREATE TABLE {table_name} (
    -- Primary key (REQUIRED)
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Business columns
    name TEXT NOT NULL,
    email TEXT,
    status TEXT DEFAULT 'active',
    user_id UUID,
    count INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT true,
    metadata JSONB,

    -- Standard timestamps (REQUIRED)
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP
);

-- Unique index (allows duplicate soft-deleted records)
CREATE UNIQUE INDEX idx_{table_name}_email_unique
    ON {table_name}(email) WHERE deleted_at IS NULL;

-- Foreign key index (NO FK constraint, just index)
CREATE INDEX idx_{table_name}_user_id
    ON {table_name}(user_id) WHERE deleted_at IS NULL;

-- Soft delete index (REQUIRED)
CREATE INDEX idx_{table_name}_deleted_at
    ON {table_name}(deleted_at) WHERE deleted_at IS NOT NULL;

-- Update trigger (REQUIRED)
CREATE TRIGGER update_{table_name}_updated_at
BEFORE UPDATE ON {table_name}
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();

Critical:

• Use TEXT not VARCHAR
• NO REFERENCES or FOREIGN KEY
• ALL indexes have WHERE clause
• Soft delete index required
• Update trigger required

Step 2: Run Migration

./gradlew :app:module-repository:flywayMigrate

Verify success before proceeding.

Steps 3-10: Kotlin Implementation

See kotlin-templates.md for all Kotlin code templates (Table, Entity, Repository, Factory Bean, Tests).

Follow this order:

1. Step 3 — Table object (extends UUIDTable, use text() not varchar())
2. Step 4 — Entity data class (implements Entity<Instant>)
3. Step 5 — Constants/Enums (if needed)
4. Step 6 — Repository interface
5. Step 7 — Repository implementation (db.primary for writes, db.replica for reads, ALWAYS filter deletedAt.isNull())
6. Step 8 — Factory bean registration
7. Step 9 — Repository tests (minimum 7 tests, must include soft delete verification)
8. Step 10 — Run tests: ./gradlew test

Common Mistakes to Avoid

See examples.md for common mistakes and anti-patterns.

Success Criteria

✅ SQL migration runs successfully ✅ Uses TEXT (not VARCHAR) ✅ NO foreign key constraints ✅ All indexes have WHERE clause ✅ Kotlin Table extends UUIDTable ✅ Entity implements Entity<Instant> ✅ All queries filter deletedAt.isNull() ✅ No !! operators ✅ Soft delete only (no hard delete) ✅ Factory bean registered ✅ All 7+ tests pass