Exposed ORM Patterns

This document captures Exposed ORM-specific patterns, gotchas, and conventions discovered through implementation.

Column Naming Conventions

• Database column names must be lowercase snake_case. While Exposed allows uppercase, the project standard is lowercase to prevent surprises in SQL queries and external tools.
• Database column names (e.g., default_supply_eid) can differ from Kotlin property names (e.g., defaultSupplyEId). The mapping is explicit in persistence classes using Exposed’s column definition. Be careful with case variations.

Flyway Migration Requirements

Exposed index definitions require explicit Flyway migrations.

index() inside init {} only describes the schema for Exposed’s internal model — it does NOT automatically create the index at runtime. Every schema element (indexes, constraints, columns) defined in Exposed table objects must have a corresponding Flyway migration script.

Example: if you add index() to a table object, you must also add a migration:

-- V010__add_item_name_index.sql
CREATE INDEX IF NOT EXISTS idx_item_name ON item (item_name);

Transaction Semantics

inTransaction Is Reentrant

inTransaction(db) is reentrant — when called within an existing transaction, it joins rather than opening a new one. Safe for nested calls (e.g., Impl -> SupplyService).

Per-Transaction Error Isolation

Moving from a single transaction wrapping all supply updates to per-supply inTransaction calls provides better error isolation — a failure updating one supply doesn’t roll back updates to other supplies.

Transaction Bubble Helpers

Use inTransaction from TransactionExt.kt to create or reuse Exposed transactions and propagate ApplicationContext across coroutine boundaries:

• Code: /common-module/lib/src/main/kotlin/cards/arda/common/lib/persistence/rdbms/TransactionExt.kt

Universal Condition Gotchas

compileFilter Override Required

When implementing ScopedUniversalCondition with custom field mapping, you must override compileFilter. Failure to do so causes NullPointerException in QueryCompiler.

Protected Visibility

universalCondition in AbstractUniverse must be protected. Overriding with private visibility triggers Kotlin compilation errors.

DB Bootstrap and Migrations

Use DataSource.newDb(...) for consistent database creation via Hikari connection pool + Exposed:

val db: Database = DataSource(dsCfg.db, dsCfg.pool).newDb(dsCfg.flywayConfig)

Migrations use Flyway via DbMigration. Centralize migration config, validation, and structured error logging so failures are actionable.

• Code: /common-module/lib/src/main/kotlin/cards/arda/common/lib/persistence/rdbms/DataSource.kt
• Code: /common-module/lib/src/main/kotlin/cards/arda/common/lib/persistence/rdbms/DbMigration.kt

Draft Persistence

Store drafts in a dedicated table with (entity_id, tenant_id) uniqueness and JSON payload/metadata columns to support Edit–Draft–Publish without polluting bitemporal history.

• Code: /common-module/lib/src/main/kotlin/cards/arda/common/lib/persistence/drafts/DraftStore.kt

Testing

Use ContainerizedPostgres to wrap Testcontainers Postgres and expose an Exposed Database for integration tests against real SQL + migrations without external dependencies.

• Code: /common-module/lib/src/main/kotlin/cards/arda/common/lib/testing/persistence/ContainerizedPostgres.kt

Use AbstractUniverseTestTemplate for comprehensive CRUD/query/history behavior coverage for any AbstractUniverse.

• Code: /common-module/lib/src/main/kotlin/cards/arda/common/lib/testing/persistence/AbstractUniverseTestTemplate.kt

EntityServiceConfiguration and Structured Translators

EntityServiceConfiguration introspects a @Serializable entity payload class to build an ExposedLocatorTranslator that maps JSON field names to database columns. This enables query locators to use camelCase payload property paths (e.g., identity.email) alongside raw column names (e.g., identity_email).

Configuration Pattern

// Define once per entity type in the persistence package
val myQueryConfig = EntityServiceConfiguration.create(MyEntity.Entity::class) {
  opaque("settings")  // exclude non-filterable JSON blob fields
}.also { it.freeze() }

Universe Integration

Set the universe’s translator property:

class MyUniverse : AbstractScopedUniverse<...>() {
  override val translator by lazy { myQueryConfig.bindToTable(persistence.bt) }
}

For child universes, guard the ChildTable cast:

override val translator by lazy {
  check(persistence.bt is ChildTable) {
    "MyChildUniverse requires a ChildTable, got ${persistence.bt::class}"
  }
  myChildQueryConfig.bindToTable(persistence.bt as ChildTable)
}

QueryCompiler Reuse

Define QueryCompiler instances as module-level lazy vals rather than constructing them inline in service methods:

// Module-level lazy val (preferred for child universes and validators)
internal val myQCompiler by lazy {
  QueryCompiler(MY_TABLE, myQueryConfig.bindToTable(MY_TABLE))
}

// Internal accessor on parent universe (exposes protected qCompiler)
internal val queryCompiler get() = qCompiler

Domain-Specific Column Notes

• ITEM_SUPPLY_TABLE.supplierEId stores BusinessAffiliate eId, not BusinessRole eId. Set via resolveVendor which does copy(supplierEId = existingBa.payload.eId).