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.