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

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

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 ✅ All queries filter deletedAt.isNull() ✅ No !! operators ✅ Soft delete only (no hard delete) ✅ Factory bean registered ✅ All 7+ tests pass