Persistence and Transactions

Database Mappings

Arda uses Exposed for database access.

Column names must be specified in lowercase snake_case.
Use column types defined under EntityTable. When those types are insufficient, use native Exposed column types.
When using Filter.Eq and similar, use <TBL>.<COLUMN>.name for the locator parameter instead of hardcoded strings, to eliminate the risk of mismatched column names.
JSON columns with custom serializers: The reified json<T>(name, format) overload calls serializer<T>() at runtime, which ignores @Serializable(with=...) and @Contextual annotations on type arguments. When T contains a non-@Serializable class (e.g., java.net.URI), use the three-argument overload with an explicit KSerializer:
```
// WRONG — fails at runtime: serializer<Map<String, URI>>() cannot find URI serializer
val sites = tbl.json<Map<String, URI>>(name, JsonConfig.standardJson)

// CORRECT — explicit serializer
val sites = tbl.json<Map<String, URI>>(name, JsonConfig.standardJson, MapSerializer(String.serializer(), URISerializer))
```
EntityTable also provides standardJson<T>(name), which resolves contextual serializers from JsonConfig.standardJson.serializersModule. Prefer it over json<T>(name, JsonConfig.standardJson) when contextual serializers are sufficient.

Unchecked casts to ChildTable: When casting EntityTable to ChildTable (required due to invariant generics on ExposedLocatorTranslator), always guard with check() before the cast:

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

QueryCompiler reuse: Never construct QueryCompiler(table) inline inside service methods. Define a module-level lazy val in the persistence package, or expose an internal accessor on the universe:

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

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

Persistence shape: Universe vs. plain table

Choose how to persist an entity by whether it has a lifecycle:

No lifecycle — standalone, immutable records that are never updated (an audit / event log). A plain Exposed table is fine; there are no “versions of one entity” to interpret, so the Universe machinery buys nothing. Deduplication, if needed, is a unique-index concern.
Has a lifecycle — created, mutated, soft-deleted, or otherwise evolving over time (multiple rows are versions of one logical entity). Default to a bitemporal ScopedUniverse. Do not hand-roll soft-delete, version history, or tenant scoping on a plain table — the AbstractScopedUniverse / ScopedTable / ScopedRecord / Persistence stack already provides them, tested and composable: retired-flag soft-delete, a row per change (full audit history), scoped metadata (tenant_id, author, created / updated timestamps), structural tenant isolation via ScopedUniversalCondition, and create / read / findOne(Filter, asOf) / list(Query, asOf) / update / delete operations that honor the “latest non-retired version” semantics.
Special cases (e.g. a Draft Store, or another non-standard store) — consult the user / operator before deviating from the Universe default; do not pick a bespoke persistence shape unilaterally.

“No tenant” is not a reason to leave the Universe. AbstractScopedUniverse is the default because nearly every entity belongs to a tenant, but an entity that is global — or that is keyed by a user across all of that user’s tenants — belongs on the non-scoped AbstractUniverse, not on a hand-rolled table. A ScopedTable requires a tenant_id on every row, so it genuinely cannot hold such an entity; that constraint rules out the scoped base, not the Universe stack. Choosing a bespoke store there gives up identity, the version chain, audit columns, and latest-non-retired semantics in exchange for solving a problem the non-scoped base already solves.

A concept that needs both — the same payload stored sometimes tenant-partitioned and sometimes not — is served by two universes, one on each base, sharing their behavior through a by delegate rather than by one universe with an optional tenant column. See Universe Design.

A business-key uniqueness constraint on a bitemporal table (e.g. “at most one active row per (tenant, key)”) is enforced in the application layer — check for a live entry before inserting — not by a DB partial-unique index: a row-version write inserts the new version before retiring the old one and would otherwise trip a non-deferrable unique constraint.

Naming pitfall: a ScopedTable column property named source (or any name that collides with an Exposed ColumnSet member) fails to compile — “‘source’ hides member of supertype ‘ColumnSet’ and needs an ‘override’ modifier.” Name the Kotlin property non-collidingly and keep the DB column name via the string argument, e.g. enumerated<…>("source").

Deferred DB actions carry the transaction requirement (`DBIO`)

Enforced by CollapsedDBIO (tree).

A database action is a DBIO<T> — suspend () -> Result<T> — that reads the ambient Exposed transaction from the coroutine context when it runs. Invoking one (dbio()) outside an active transaction is a runtime error. So the transaction requirement is real, and it should live in the type, not in a comment.

When a method produces a DB action whose execution must be controlled by the caller’s transaction, return the un-invoked DBIO<T> — do not call () internally. Persistence methods already follow this (Universe.create/update/… return DBIO<…>); a method that composes one for a caller to run must not collapse it back to a value.

This matters most for a deferred write that must be atomic with something else — e.g. a domain write that has to commit in the same transaction as an idempotency settlement, an outbox row, or a sibling aggregate. Such a write must run inside the caller’s outer transaction, so it must be handed back as a DBIO the caller runs — never opened in its own nested inTransaction { … }. (Nested inTransaction cannot yet be relied on to join the outer transaction; explicit propagation semantics are tracked in PDEV-955. Until it lands, atomicity-bound deferred writes must be DBIO, run by the caller.)

// WRONG — invokes the DBIO (`()`) inside a lambda and re-wraps it as an untyped closure,
// so the "needs a transaction" guarantee is only a convention + comment.
FreshAttemptResult.Commit(job) {
  universe.create(job, metadata, asOf)().unitify()   // runs the insert here; throws if no tx
}

// CORRECT — hold the un-invoked DBIO<Unit>; the caller runs it inside its transaction.
FreshAttemptResult.Commit(
  result = job,
  persist = universe.create(job, metadata, asOf).map { },   // DBIO<Unit>, not yet run
)

The carrier type states the contract: val persist: DBIO<Unit> (not suspend Transaction.() -> Result<Unit> invoked eagerly). The caller does inTransaction(db) { … attempt.persist() … }, and the type makes it impossible to forget that a transaction is required.

Exception — a deferred action that may legitimately do no persistence. If a carrier’s action is sometimes a DB write and sometimes a pure computation, do not force a DBIO; route the DB case through a service method that owns its own inTransaction (which, post-PDEV-955, joins the caller’s transaction when present). Choose this only when non-persistence implementations are a real, foreseen case — not to avoid typing a write as DBIO.

Trade-off to note: a DBIO built from create(payload, metadata, asOf) captures asOf at build time, not run time. When the exact recorded/effective instant must be the settlement time rather than the build time, evaluate it inside the DBIO body; otherwise the build-time value (usually the more meaningful business instant) is fine.

Work that must not join the caller’s transaction

Most deferred work should join the caller’s transaction — that is what DBIO is for, and rule 10 exists to keep it that way. A small amount must not, and the distinction is worth stating because getting it wrong is silent in both directions.

The convention

A record of a failure must be written outside the transaction that failed.

When a guard refuses an operation, the unit of work that asked is rolled back — that is what refusing means. An audit record written inside it goes down with it, and the result is the worst kind of gap: no error, no log, just a missing record that nobody notices until someone asks what happened and the answer is nothing.

So the record needs a transaction of its own, opened after the first has closed. In common-module:

inTransaction(db) { universe.doThing()() }          // the caller's own unit of work
  .onFailureInNewTransaction(db) { err -> … }       // the record, in a transaction of its own

The Result receiver makes the correct call the natural one to write: there is no Result to act on until inTransaction has returned.

Nesting a transaction: refused by default, permitted deliberately

Opening a second pooled connection beside a transaction still holding its locks deadlocks if the two touch the same rows. This is a bug that reproduces only under load — in a test the pool has spare connections and the two rarely collide, so the code looks correct until production, where a request waits on itself and the stack trace says nothing about why.

inNewTransaction therefore refuses a usable ambient transaction on the same database by default:

inNewTransaction(db) { … }                                    // refuses if a transaction is open
inNewTransaction(db, ambient = AmbientTransaction.PERMIT) { … } // proceeds anyway, deliberately

There are rare but real reasons to nest on purpose, so this is a default rather than a prohibition. If you find yourself passing PERMIT, that is fine — but say in a comment what made it necessary, because the next reader’s first assumption will be that it is a mistake.

“Usable” is not “present.” Detached work — a completion launched on its own coroutine — inherits the launching scope without inheriting its connection. That is not a transaction to deadlock against, and those callers are unaffected.

What to reach for

You want	Use	Joins an ambient transaction?
Work that is part of the caller’s unit of work	`DBIO<T>`, returned un-invoked	Yes — the caller runs it
Work that must run whether or not one exists	`inTransaction(db)`	Joins when present, opens when not
Work that is only correct inside the caller’s	`inEnclosingTransaction(db)`	Requires one; fails without
Work whose lifetime is not the caller’s	`inNewTransaction(db)`	No — and refuses if one is open
A record of a failure that must survive its rollback	`Result<T>.onFailureInNewTransaction(db)`	No — runs after the first has closed

Static analysis flags the lexical form of a nested inNewTransaction as a warning. It cannot see the cross-function case, which is why the runtime check exists as well; see PDEV-1594.

Universe Design — building a Universe once you have chosen one.
Service-Layer Architecture — who owns the transaction boundary.
Bitemporal Persistence — the substrate.
Kotlin Coding Standards — index.