srDB POC
← All ADRs

ADR-002 — Polymorphic positions over per-class position tables

Status: Accepted (POC). Date: 2026-05-28.

Context

The substrate spans four asset classes whose mark-to-market mechanics, contract terms, and lifecycle events differ deeply: a NatGas well has decline-curve economics and royalty/severance tax math; a reinsurance treaty has loss-attached, reinstatement, and reinstatement-premium logic; an SFR portfolio has NOI and cap-rate-driven appraisal; a crypto derivative has notional, mark price, and unrealized PnL with margin posting. Yet every one of them is a position — a quantity, a holder, an instrument, a market value, a mark lineage, a cost basis, and (in this design) a bitemporal validity window.

Cross-asset queries are the substrate's reason for existing. "What does SRHG own as of 2025-12-31 across all asset classes?" is Q1 of the demo set. "Reconcile every position to ABOR" is Q4. "What does LTRe own through the SRAM fund-of-funds graph?" is Q2. If positions live in four different tables, every cross-asset query is UNION ALL across them — joins to entity, to instrument, to ABOR, to lookthrough, all duplicated four ways. The substrate-first thesis ("downstream analytics doesn't have to compensate for a wrong canonical layer") falls apart when even basic queries require asset-class case analysis.

The opposing concern is type safety. A per-class position table can enforce that a well position has working-interest and severance fields, that a treaty position has reinstatements_consumed, etc. A polymorphic position with per-class detail tables linked by position_id cannot guarantee in the engine (DuckDB doesn't enforce 1:1 relationships) that every position row has the matching detail row.

Decision

One position table holds the common surface — position_id, instrument_id, holding_entity_id, as_of_date, quantity, quantity_unit, cost_basis_usd, market_value_usd, valuation_method, valuation_source, mark_series_id, mark_observation_ts, mark_vintage, plus bitemporal columns.

Four detail tables (position_well, position_treaty, position_sfr, position_crypto_deriv) hold asset-class-specific fields. Each detail row's primary key is position_id referencing the parent.

Asset class is recoverable via position.instrument_id → instrument.asset_class. No discriminator column on position (it would denormalize and could drift from instrument.asset_class).

The 1:1 invariant is enforced by application logic (synth modules write parent + detail together; failure on detail aborts the parent). T15 has no gate that directly catches an orphan parent row; the gap is acknowledged and a CHECK (EXISTS (SELECT 1 FROM position_<class>)) trigger is on the Phase 2 list.

Consequences

Positive:

  • Cross-asset queries are uniform. Demo Q1 is one JOIN position p ON instrument i with a GROUP BY i.asset_class. ABOR reconciliation reads position directly with no per-class fan-out. The lookthrough pipeline traverses positions as a single set.
  • Adding a fifth asset class (private credit, FX, etc.) is one detail table + one ingestion module. The substrate surface is unchanged.
  • Mark lineage (mark_series_id, mark_observation_ts, mark_vintage) lives on the common surface — every position, regardless of class, can be traced back to the observation that produced its market value. T15 G6 codifies this as a hard gate.

Negative:

  • DuckDB doesn't enforce that position has a matching per-class detail row. A code path that inserts to position and forgets the detail leaves an orphan, and no T15 gate currently catches it. Mitigation: synth modules write parent + detail together in the same code path; a Phase 2 CHECK trigger (or a vw_position_with_detail view that INNER JOINs on position_id) would add engine-level enforcement. This is the single load-bearing weakness of the polymorphic design.
  • Per-class queries pay a join. A "give me all CAT_XOL treaty positions" query is position JOIN position_treaty JOIN instrument_treaty. Three-way joins on 350 rows are free in DuckDB; at production scale they'd want covering indices.
  • A future asset class with no per-class state (e.g., pure cash positions) creates an empty detail table. Not a real problem; just noise in the schema.

Alternatives Considered

  • Per-class position tables (position_well, position_treaty, ...) as primary tables. Every cross-asset query becomes UNION ALL. The lookthrough pipeline either runs four times (once per class) or pre-computes a unified view. Loses the substrate-first promise.
  • One position table with all asset-class fields as nullable columns. Wide, sparse schema. NRI is NULL for everything that isn't a well; reinstatements_consumed is NULL for everything that isn't a treaty. Hard to evolve (every new asset class adds columns that are NULL for all prior rows) and the type-safety claim ("a well position has NRI") is now table-wide convention rather than schema constraint.
  • JSON detail column on position. Loses the ability to query per-class fields in pure SQL without JSON_EXTRACT everywhere. DuckDB has good JSON support, but the substrate's promise ("anyone can write the SQL") suffers when half the interesting fields are inside a JSON blob.