database-patterns

name	database-patterns
description	Database design patterns including schema design, migrations, soft deletes, and Exposed ORM. Use when creating tables, writing migrations, or implementing repositories.
allowed_tools	["Read","Write","Edit","Glob","Grep"]

Database Patterns — Quick Reference

Hard Rules

Rule	Do	Don't
Data types	TEXT	VARCHAR
Primary keys	UUID	SERIAL, BIGINT
Soft delete	deleted_at TIMESTAMP	DELETE FROM
Foreign keys	App-level validation	REFERENCES, ON DELETE CASCADE
Standard columns	id, created_at, updated_at, deleted_at	Skip any of these

Migration Template

SQL migration with table, trigger, and partial indexes for soft delete.

See code-templates.md for the complete SQL template.

Exposed Table Object

Extend UUIDTable, use text() not varchar(), add standard timestamp columns.

See code-templates.md for the complete template.

Entity Data Class

Implement Entity<Instant>, include all business fields + createdAt, updatedAt, deletedAt.

See code-templates.md for the complete template.

Repository Pattern

Interface + Default* implementation. Reads on db.replica, writes on db.primary. Soft delete via deletedAt update. convert() method maps ResultRow to entity.

See code-templates.md for the full interface + implementation code.

When Creating a New Table

Full checklist:

1. SQL migration file (next number in sequence)
2. Table object in module-repository/table/
3. Entity data class in module-repository/entity/
4. Enum/constants in module-repository/constant/ (if needed)
5. Repository interface + implementation
6. Factory bean for repository
7. Repository tests

Flyway Rules

• NEVER add a migration that fills a gap in deployed sequence
• NEVER rename an already-deployed migration file
• Migration numbers must be sequential from the latest
• Keep migrations simple and focused (one table per migration)

Gotchas

• Only id uses .value -- everything else is direct. row[Table.id].value gives UUID, but row[Table.projectId] already returns UUID. Adding .value to non-id columns causes compile errors.
• gen_random_uuid() vs uuid_generate_v4() -- pick one per project. Mixing them works but confuses code review. Check existing migrations for which one the project uses.
• Don't re-declare createdAt, updatedAt, deletedAt if extending SoftDeleteTable. They're inherited. Declaring them again causes duplicate column errors.
• Forgetting WHERE deleted_at IS NULL on indexes wastes space. Every index on a soft-delete table should be partial. Full indexes include dead records nobody queries.
• Text columns that hold JSON should still use text() in Exposed. JSONB in Postgres, text() in Kotlin, serialize/deserialize in the entity layer.