persistence-conformance

Persistence Conformance Corpus

End-to-end cross-language integration tests against a real Postgres (via [Testcontainers]). Every supported language port runs the query scenarios against its own MetaObjects persistence layer — generated code in C# (MetaObjects.Codegen → EF Core), TS runtime-ts's ObjectManager (Kysely), and the equivalents for Java (OMDB) / Kotlin (Exposed) / Python. Identical normalized results across every port is the contract.

Per ADR-0015, TypeScript owns schema migrations and is the single producer of the canonical schema DDL. No port synthesizes schema: every query-scenario runner executes the committed canonical/schema.postgres.sql to provision its test DB. The migration scenarios are exercised by TS only; all other ports are pure data-access.

The corpus is on-demand: per-port unit test runs (bun test, dotnet test, pytest, mvn test) stay container-free. Integration runs against this corpus are invoked explicitly via scripts/integration-test.sh (or per-port runner commands), and are required before any release publish — see docs/RELEASING.md.

Layout

fixtures/persistence-conformance/
├── README.md                     # this file — spec + DSL
├── normalization.md              # how each port serializes result rows
├── canonical/                    # SHARED "kitchen-sink" metadata + committed schema DDL
│   ├── meta.*.json               # the kitchen-sink metadata every query scenario reads
│   └── schema.postgres.sql       # TS-produced canonical DDL every port executes to set up its DB
├── migrations/                   # per-scenario schema-evolution tests (TS-only)
│   └── <name>.yaml
└── queries/                      # query scenarios against the canonical schema (every port)
    └── <name>.yaml

The canonical schema artifact

canonical/schema.postgres.sql is generated by TypeScript from canonical/meta.fitness.json (base tables + the origin.aggregate / origin.passthrough projection views) and committed. It carries a @generated … DO NOT EDIT header; every port's query runner executes it verbatim to provision its test DB, so the runtime layer is exercised against a schema no port synthesized.

Regenerate it with bun run gen:schema in server/typescript/packages/integration-tests (pure metadata→SQL, no DB). A TS test (schema-artifact.test.ts) drift-checks the committed file against what the generator would produce, so CI fails if the artifact diverges from the metadata — this dog-foods the "TS owns schema" contract.

Two scenario kinds

Migration scenarios (migrations/*.yaml)

Test the schema-evolution pipeline. Each scenario carries its own metadata states; the runner applies the migration and asserts SQL and / or post-DDL state.

Migration scenarios are TS-only (ADR-0015): schema migrations are owned by the TypeScript toolchain, and the C# / Java / Python migration engines and the Kotlin Exposed-migration harness were all removed. Every non-TS port runs the query scenarios only, provisioning its test DB from the committed canonical/schema.postgres.sql (no port synthesizes schema).

name: add-nullable-column-to-existing-table
description: A new nullable column on an existing table → ADD COLUMN, no allow flags.

# Initial schema — what's in the DB before the migration runs.
seed-metadata: ./states/program-v1/        # path to a metadata directory
# OR an inline metadata block:
# seed-metadata-inline:
#   - { object.entity: { name: Program, children: [ ... ] } }

# Optional: raw SQL to set up before-state data (after the schema is applied).
seed-data: |
  INSERT INTO "programs" ("id", "title") VALUES (1, 'Foundations');

# Target schema — what we want the DB to look like.
target-metadata: ./states/program-v2/

# Assertions on the generated up migration (any of these may be omitted).
expect:
  blocked: []                                # no blocked changes
  up-contains:                               # substrings the up SQL must include
    - 'ALTER TABLE "programs" ADD COLUMN "subtitle" TEXT'
  up-empty: false                            # set to true to assert no-op (snapshot already matches)
  apply-up-then-query:                       # after running up.sql, run a sanity query
    sql: SELECT "id", "subtitle" FROM "programs" ORDER BY "id"
    rows:
      - { id: "1", subtitle: null }

Query scenarios (queries/*.yaml)

Test that each port's persistence layer can read/write the canonical schema and produce identical normalized rows. All query scenarios share the canonical schema (no per-scenario metadata) so we exercise the runtime layer in isolation.

name: list-programs-filtered-by-title-like
description: ObjectManager / generated DbContext must surface the same rows for a `like` filter.

# Optional seed SQL run after the canonical schema is bootstrapped.
seed-data: |
  INSERT INTO "programs" ("id", "title") VALUES
    (1, 'Foundations'), (2, 'Strength'), (3, 'Mobility');

queries:
  - name: filter-with-like
    op: list                                 # list | get | count
    entity: Program
    filter: { title: { like: "%th%" } }      # uses the standard cross-language filter ops
    sort: [{ field: id, dir: asc }]
    expect:
      - { id: "2", title: "Strength" }

  - name: get-by-id
    op: get
    entity: Program
    by: { id: 1 }                            # equivalent to filter: { id: { eq: 1 } } + first
    expect: { id: "1", title: "Foundations" }

  - name: aggregate-projection
    op: list
    entity: ProgramStat                      # read-only projection
    filter: { programId: { eq: 1 } }
    expect:
      - { programId: "1", weekCount: 3 }

Query DSL (v2-day-one)

The DSL is intentionally small. Each port translates it to the most idiomatic call into its persistence layer (C#: _db.Set<T>().Where(...).ToListAsync(); TS: om.findMany(entityName, filter, opts)).

field	type	notes
op	list | get | count | relate | create | update | delete | roundtrip	required
entity	string	metadata name (Program, ProgramStat, …)
by	{ id: scalar }	required for op: get, op: relate, op: update, op: delete (the record key)
data	row object	required for op: create / op: update (the row payload to write)
relation	string	required for op: relate; the relationship name to traverse
insert	row (field → value)	required for op: roundtrip; the row to WRITE through the runtime
filter	filter object (below)	optional; same vocabulary as Project D
sort	[{ field, dir }]	optional; dir ∈ `asc
limit	integer	optional
offset	integer	optional
expect	row or row[] or integer	required unless expect-error; the normalized expected result
expect-error	boolean	optional; when true the op must FAIL (throw/reject). expect is ignored. Portable: each runner asserts "the op raised an error", not a message.

A documented DSL key must stay exercised. For a long time every sort: in this corpus was [{ field: id, dir: asc }] over seed rows inserted in ascending-id order, and nothing used dir: desc, limit, offset, or in combined with a second predicate — so a port that dropped sort, hardcoded ascending, was off-by-one on offset, or rendered in as an unparenthesized OR-chain passed the whole corpus (three real Java query-lowering defects survived exactly this way). sort-desc-non-id.yaml, pagination-limit-offset.yaml and filter-in-with-second-predicate.yaml now pin those paths, each with seed data under which the wrong behavior provably returns a different row set. When adding a DSL key, add a scenario whose expected rows DIFFER when the key is ignored — a scenario that passes either way gates nothing.

op: create / op: update — runtime writes (FR-017 TPH)

create inserts a row (om.create(entity, data)); update patches a row by id (om.update(entity, by.id, data)). They exercise the runtime write path, including TPH (FR-017): a create on a discriminator SUBTYPE injects the discriminator value (omit it from data); reads/updates are scoped to the subtype; the discriminator is immutable; a subtype's write surface is its own columns only. A cross-subtype write (an unknown subtype column, or a different-subtype id) is expected to fail (expect-error: true).

op: delete — runtime delete-by-PK

delete removes a row by id (om.delete(entity, by.id)) through the runtime write path. Its expect is the boolean outcome (true = a row was deleted, false = no matching row). The portable pattern for proving a delete actually removed the row is a follow-up op: get by the same PK asserting expect: null. delete is exercised end-to-end by update-delete-all-types.yaml (seed a fixed PK → update every subtype → read back → delete → get-returns-null).

A port's UPDATE codec is a distinct path from its INSERT codec (PATCH paths frequently skip the type coercion the INSERT path applies), so update-delete-all-types.yaml is the gate that catches an UPDATE-only re-encode regression per subtype. All five ports run it green (the SP-H per-port write-codec units all landed; every port's skip list is empty).

TPH (table-per-hierarchy) scenarios

The tph-*.yaml scenarios exercise single-table inheritance through the runtime (Auth base + BridgeAuth / CopayAuth / PriorAuthAuth subtypes over the one auths table). They require the port's runtime TPH support (resolving a subtype's inherited fields/identity/table via extends, plus discriminator inject/scope/strip). All five ports run the tph-* scenarios green — TypeScript (runtime-ts ObjectManager), Java (OMDB + TphHelper), Python (runtime/tph.py), C# (EF DbContextAdapter CLR-type discrimination), and Kotlin (the Exposed persistence lane's QueryScenarioRunner inject/scope/project). The generated code's TPH behavior is gated separately by the api-contract generated-controller lanes (e.g. Kotlin's TphGeneratedApiContractConformanceTest boots the unmodified generated controller over HTTP).

op: roundtrip — runtime WRITE round-trip

roundtrip is the all-types write gate: the runner INSERTS the insert: row through the port's runtime / ORM write path (not raw SQL), reads the inserted row back by primary key, normalizes it, and asserts it equals expect. It therefore exercises the WRITE codec and the read path together — the structural complement to the read gate, and what would have caught the field.byte/field.short hole, the Java timestamp-write hazard, and native-uuid-write.

• insert: carries field values in the runtime's native authoring forms (a decimal as a string, a uuid as a string, a field.object as an object, a boolean as a bool). expect: is the wire-normalized read-back (the same per-type rules as every read scenario — BIGINT→string, decimal canonical, uuid→lowercase, temporal→native, jsonb→sorted-keys).
• The primary key is excluded from the comparison: a server-/runtime-generated PK (gen_random_uuid / increment) is non-deterministic, so expect asserts the written field VALUES, not the identity. (op: get covers PK round-trip.)
• The AllTypes entity (all_types table) in the canonical model exists to cover every persistable field subtype in one row — string / int / long / double / float / decimal / boolean / date / time / timestamp / timestamp(tz) / currency / enum / uuid / object(@objectRef, jsonb storage) — plus an array-of-VO field.object @isArray @storage:jsonb column (labels) written as a 2-element, empty-[], and single-element array across the three roundtrip rows, exercising each port's array-of-value-object write+read jsonb codec (the gap that once let a non-compiling / wrong array serializer ship). No read scenario references it, so it is inert for the read runners (it just adds a table to the schema).
• Full int64 fidelity: one roundtrip query writes the BIGINT max (9223372036854775807, > 2^53) to field.long AND field.currency as a numeric string — the write contract for those two subtypes accepts a base-10 integer string (or a bigint) precisely so a 64-bit value survives the write codec without precision loss. A port routing these through a 64-bit-lossy numeric type (a JS number, a 32-bit int, a double) corrupts the low bits and fails.

All five ports run the roundtrip-* scenarios green — the SP-H per-port write-codec units landed everywhere (TS via runtime-ts ObjectManager.create → Kysely insert; Java OMDB, Kotlin Exposed, Python ObjectManager, C# EF Core). When adding a port, implement the verb (insert-via-ORM → read-back-by-PK → normalize → assert, PK excluded), then run these scenarios green; do not delete them.

op: relate — relationship traversal (M:N)

relate traverses a relationship from a single source record (by) and returns the related rows. It is how the corpus exercises many-to-many (FR-017) navigation through a junction: the port's runtime resolver derives the junction FK fields from the junction entity's two identity.reference children (no @joinFields) and handles all three modes — hetero, directed self-join (@sourceRefField), and symmetric self-join (@symmetric, union-on-read).

Because M:N membership is a set, the runner compares relate results order-independently (rows sorted by canonical JSON on both sides) — a relate scenario does not need (or honor) sort:.

All five ports run the m2n-* scenarios green — the FR-017 per-port runtime M:N resolvers (Units 5–9) all landed, and every port's skip list is empty. When adding a port, implement the resolver, then run these scenarios green; do not delete them.

Symmetric self-pair (a,a) contract. m2n-symmetric-self-join.yaml seeds a self-edge (Alice is her own friend, (1,1)) and asserts Alice's friends include Alice. The contract is that the runtime resolver KEEPS the self endpoint. A generated query that filters personAId = X OR personBId = X and then takes "the column that is NOT the source" silently DROPS the self-pair (both columns equal X) — the historical Kotlin failure mode this scenario pins; all five ports now retain the (a,a) row.

Filter operators

Same vocabulary as the cross-language filter spec (Project D):

op	meaning
eq	equals
ne	not equals
gt	greater than
gte	greater than or equal
lt	less than
lte	less than or equal
in	value in list
like	SQL LIKE (string fields only)
isNull	true → IS NULL, false → IS NOT NULL

Multiple field filters compose with AND. To compose multiple ops on the same field (e.g. a range query) use an explicit and: block — applying two ops directly to one field is undefined across runtimes:

filter:
  and:
    - { priceCents: { gte: 3000 } }
    - { priceCents: { lte: 7000 } }

and is the only logical combinator today; or would require both runner adapters and runtime-ts' compileFilter to grow it first.

Result format

expect is the already-normalized value. The per-port runner reads its result rows, applies the normalization rules, and asserts byte-equality (after JSON canonicalization) against expect. Failure prints the exact (scenario, query) pair plus a row-level diff.

Quick reference (see normalization.md for the full contract)

• BIGINT → JSON string ("1") — JS Number loses precision above 2⁵³.
• INTEGER, SMALLINT → JSON number.
• BOOLEAN → JSON bool.
• NUMERIC / DECIMAL → JSON string (canonical decimal, no trailing zeros).
• TEXT / VARCHAR → JSON string.
• DATE → "YYYY-MM-DD".
• TIMESTAMP (no TZ) → "YYYY-MM-DDTHH:MM:SS[.fff]" (no timezone suffix).
• TIMESTAMPTZ → "YYYY-MM-DDTHH:MM:SS[.fff]Z" (always UTC).
• UUID → lowercase canonical ("550e8400-...").
• JSON / JSONB → re-serialized with sorted keys.
• NULL → JSON null.

Per-port runner protocol

A runner walks the corpus and, for each scenario:

1. Spin up a fresh Postgres testcontainer.
2. Migration scenarios (TS-only): apply seed-metadata, run seed-data if present, then run meta migrate --from-db against the target metadata; assert expect. Only the TS runner exercises these.
3. Query scenarios (every port): execute the committed canonical/schema.postgres.sql to provision the schema, run seed-data, then for each queries[*] execute via the port's persistence layer, normalize, assert. A read op (get/list/count/relate) queries the seeded rows; an op: roundtrip instead INSERTS its insert: row through the port's runtime write path, reads it back by PK, and asserts the normalized read-back (PK excluded) — the WRITE gate.
4. Tear down the container.

Failure reports (scenario file, query name, row index, expected, actual).

Adding a port

1. Drop a runner under your port (server/<lang>/integration-tests/ or equivalent) that consumes this corpus.
2. Implement the DSL → persistence-layer translation.
3. Pre-generate any per-port code needed to query the canonical schema (commit the generated output so test runs don't depend on a working codegen pipeline to validate the runtime pipeline).
4. Wire your runner into scripts/integration-test.sh.