srDB POC

If you read one thing: docs/mcp_demo_transcript.md — a captured (not hand-written) MCP protocol trace of an agent answering "What's our hurricane exposure right now and what would a Cat-4 in South FL do to it?" against the substrate. The agent walks LTRe's lookthrough (4 asset classes, $108M attributed), injects a Cat-4 sandbox event (3 affected instruments — 2 CAT_XOL treaties + Tampa SFR), reads the FL treaty's reinstatement state from first principles, then demonstrates apply_driver_shock on HH M1 +20% (213 instruments touched). The agent never sees a table.

What this is: a canonical normalized data substrate for a heterogeneous multi-asset book (NatGas wells, reinsurance treaties, SFR portfolios, crypto derivatives), with bitemporal lineage, Shadow IBOR reconciliation to a synthesized ABOR feed, and an MCP-protocol agent surface. Built as an interview artifact for Head of Core Strats at Stone Ridge Holdings Group.

What's load-bearing: ADR-001 bitemporal everywhere. ADR-002 polymorphic positions. ADR-003 Shadow IBOR not Replacement. ADR-004 DuckDB not Snowflake. ADR-005 build IBOR not buy Eagle/Aladdin. ADR-006 Claude+MCP not Bedrock/OpenAI. See docs/adr/.

What's deferred: private credit as a 5th asset class; Iceberg-on-S3 at production scale; vendor ABOR integration. See §Scope boundaries.

103 tests pass, 2 skipped. 6 acceptance gates verified end-to-end (see docs/t15_verification.md). Cross-model reviewed across 8+ Codex + Claude rounds — see commit history.

What this is in long form

A canonical normalized data substrate for a heterogeneous multi-asset book — natural gas wells, reinsurance treaties, SFR portfolios, crypto derivatives — in one DuckDB schema, with bitemporal lineage, Shadow IBOR reconciliation to a synthesized ABOR feed, and a grounded AI narrative layer.

The thesis: no downstream analytics team can compensate for a wrong canonical layer. Everything here is anchored in "is the substrate right?"

All data is real public sources (EIA, PA DEP, Zillow, CoinMetrics, Deribit, FRED, FHFA, NOAA HURDAT2) or transparently synthesized where free public data doesn't exist (paywalled basis curves, well production for one TX basin). Synthesized fields are tagged source='SYNTHETIC_*' and listed in Scope boundaries below.

See docs/design.md (docs/design.md, in the private repo) for the full spec and docs/adr/ for the six load-bearing design decisions: three architecture (bitemporal-everywhere, polymorphic-positions, Shadow-IBOR-not-Replacement) and three buy-vs-build (DuckDB-not-Snowflake, build-IBOR-not-buy-Eagle/Aladdin, Claude+MCP-not-Bedrock/OpenAI/local).

Agent-first surface. srdb_mcp/ exposes the substrate as a Model Context Protocol server — 9 typed tools + 1 resource that let an agent query positions, walk lookthrough, compare to ABOR, and compose stress scenarios from primitives without ever seeing the schema. See docs/mcp_demo_transcript.md for the captured Cat-4 walkthrough.

Quick start

One-time setup:

git clone https://github.com/maplelemondragon/srdb-poc.git
cd srdb-poc && uv sync

Then to rebuild + verify + see the flagship scenario (~1 minute total):

rm -f srdb.duckdb && uv run python scripts/build_all.py    # clean rebuild, ~30-45s
uv run pytest tests/                                        # 103 passed, 2 skipped
uv run python -m scenarios.cat4_fla_landfall | tail -25     # Cat-4 flagship scenario
uv run python scripts/demo_mcp_session.py                   # capture a live MCP transcript

The three things to look at:

tests/test_end_to_end.py — six end-to-end gates: substrate query coverage, ABOR reconciliation (4 deliberate breaks, no false positives, break carry-forward), LTRe lookthrough through SRAM, Cat-4 scenario accounting identity, position mark lineage non-null. Codified from docs/plan.md (docs/plan.md, in the private repo) Batch 15.
uv run python scripts/run_demo_queries.py — the six substrate demo queries from queries/demo.sql. The reconciliation query (Q4) and bitemporal-as-of queries (Q3a/3b) are the load-bearing ones.
uv run python -m scenarios.cat4_fla_landfall — Cat-4 South Florida landfall, $80B industry loss. Exercises the entire stack: event → exposure walk → treaty XoL payout with reinstatement bookkeeping → SFR dual impairment → REINSTATEMENT_PREMIUM cash → lookthrough → narrative. Accounting identity must hold to $0.0000.

For the interactive walkthrough: uv run marimo edit notebooks/walkthrough.py.

10-minute tour

What	Where	Why
Three load-bearing design decisions, each with context + alternatives	docs/adr/	Defensible direction-setting
Agent-first MCP server (9 tools + 1 resource, typed audit boundary)	`srdb_mcp/`	The substrate as an agent's runtime
Cat-4 worked transcript (agent composes the scenario from primitives)	docs/mcp_demo_transcript.md	Off-script-question safe
End-to-end verification report (6 gates × pass/fail × evidence)	docs/t15_verification.md	Proves the gates actually hold
Cat-4 flagship scenario (the engineering identity stack end-to-end)	`scenarios/cat4_fla_landfall.py`	Reinsurance math + lookthrough together
ABOR reconciliation pipeline (4-table model, break carry-forward)	`pipelines/reconcile_abor.py`	Shadow-IBOR thesis in code
v1 AI narrative layer (Jaccard router + Claude narration on 3 closed scenarios)	`ai/`	Kept for contrast — what an agent layer looks like before being agent-first
Bitemporal correction demo (txn-time + valid-time)	`synth/bitemporal_seeds.py`, `queries/demo.sql` Q3	Audit lineage, vintage tracking

Notes:

build_all.py is idempotent. Re-runs against the same DB are safe and skip already-inserted rows.
ANTHROPIC_API_KEY is optional. The narrator falls back to a stub response if absent. Set it to get live Claude narration: export ANTHROPIC_API_KEY=sk-ant-...
A pre-built srdb.duckdb is checked in. Skip the rebuild and go straight to the demo queries if you just want to explore.

For the full step-by-step manual build sequence (if build_all.py is too opaque), see Appendix: Manual build sequence at the bottom.

Schema Tour — Five Layers

┌──────────────────────────────────────────────────────────────────────────┐
│ Layer 5 — AI narrative layer                                             │
│   Question parsing → scenario lookup → retrieval → LLM narration         │
├──────────────────────────────────────────────────────────────────────────┤
│ Layer 4 — Analytics primitives                                           │
│   Engineering identities (PnL models)  |  Scenario library               │
│   driver_exposure_map (cross-asset propagation mechanism)                │
├──────────────────────────────────────────────────────────────────────────┤
│ Layer 3 — IBOR / position layer                                          │
│   position (header) + position_<class> (well, treaty, sfr, deriv)        │
│   trade (with lifecycle)  |  cash_transaction (balance is derived)       │
│   cost_basis_lot (HIFO/LIFO/FIFO for crypto + others)                    │
│   reconciliation_run → reconciliation_pair → reconciliation_break        │
│     → break_history                                                      │
├──────────────────────────────────────────────────────────────────────────┤
│ Layer 2 — Time-series layer                                              │
│   series catalog (with curve/surface dims) | series_obs (valid-time +    │
│     vintage) | event + event_instrument_exposure | series_derivation     │
├──────────────────────────────────────────────────────────────────────────┤
│ Layer 1 — Reference layer                                                │
│   entity | entity_relationship | instrument | instrument_xref            │
│   instrument_<class> (well, treaty, sfr, deriv) | role | holiday_calendar│
└──────────────────────────────────────────────────────────────────────────┘

Layer 1 — Reference. The entity graph (SRHG, SRAM funds, LongTail Re, NYDIG, external counterparties) and instrument master. Each asset class gets a per-class extension table holding intrinsic facts only — the well's decline curve, the treaty's attachment and limit, the SFR portfolio's MSA. Holder-specific economics live in Layer 3, not here.

Layer 2 — Time-series. Every observable in the firm is a row in series: market data, production history, derived NAVs, FX rates. Observations are valid-time bitemporal with a vintage field that captures which derivation pipeline run produced each value. Structured events (event + event_instrument_exposure) sit here because a hurricane is not a scalar at a timestamp.

Layer 3 — IBOR / position. The JD's literal headline. A position header with full bitemporality (valid_from/to, txn_from/to) plus per-asset-class extension tables that carry state a scalar quantity cannot model: position_treaty holds limit_remaining_usd, reinstatements_consumed, IBNR; position_well holds working interest and NRI; position_sfr holds occupancy and arrears. Cash balance is derived from typed cash_transaction rows, never stored directly. Reconciliation is decomposed into four tables: run → pair → break → break_history.

Layer 4 — Analytics. Per-instrument PnL models (instrument_pnl_model) with formulas in Python, keyed by model_code. The driver_exposure_map is the cross-asset propagation mechanism: it records, in structured and auditable form, which market drivers touch which asset classes through which economic channel. The scenario library (scenario) maps named stress events to driver shocks and event injections — curated, not discovered.

Layer 5 — AI narrative. A Python pipeline: NL question → scenario match → driver_exposure_map walk → engineering identity evaluation → entity rollup via entity_relationship graph → structured retrieval JSON → Claude. The LLM receives only the retrieval output, never raw schema. Narration is translation, not derivation.

Three Load-Bearing Design Decisions

1. Intrinsic vs. Holder Split

What the thing is lives on the instrument. What we have lives on the position.

instrument_well holds the Arps decline parameters (qi, Di, b) and the basis hub — these are properties of the well itself. position_well holds working interest pct and NRI — these are SRHG's share of that well, which can differ by holding entity.

instrument_treaty holds attachment, limit, reinstatements_max, and reinstatement_premium_pct — intrinsic to the contract structure. position_treaty holds cession_pct, limit_remaining_usd, and reinstatements_consumed — SRHG's holder-specific state.

This separation matters operationally: the same well or treaty can be held at different proportions by different SRHG entities (e.g., Energy Fund vs. a co-investment SPV). Putting holder economics on the instrument would silently break the moment that happens. This was the post-design-review correction that propagated through all four asset classes.

2. Polymorphic Positions

The position header holds the canonical scalar (quantity, market_value_usd, full bitemporality, mark lineage). But a scalar quantity cannot honestly represent reinsurance treaty state — a treaty's economic condition after a cat event is captured by limit_remaining_usd, reinstatements_consumed, attached_loss_to_date_usd, upr_usd, IBNR, and ULAE. Forcing that into a single number would be a lie.

Each asset class gets a position_<class> extension table keyed on position_id:

Extension table	Key state it carries
`position_well`	`working_interest_pct`, `nri_pct`, `monthly_loe_usd`
`position_treaty`	`limit_remaining_usd`, `reinstatements_consumed`, `upr_usd`, `ibnr_usd`
`position_sfr`	`occupancy_pct`, `units_in_lease`, `arrears_usd`
`position_crypto_deriv`	`notional_btc`, `margin_posted_usd`, `unrealized_pnl_usd`

The schema enforces "exactly one extension row per position, matching the instrument's asset class" as a pipeline-level invariant test (DuckDB cannot do cross-table CHECK constraints natively).

3. Bitemporal + Vintage Lineage

Full bitemporality (valid_from/to + txn_from/to) is on the tables where it earns its weight: position, trade, cash_transaction, reconciliation_break, and entity_relationship. This supports the auditing question "what did we think we owned on date X, given what we knew on date Y vs. what we know now?"

series_obs gets a different treatment: valid-time only, plus a vintage field. The vintage captures "which derivation pipeline run produced this observation" — the load-bearing question for derived series reproducibility and for attributing reconciliation breaks to upstream re-derivations.

The position table stamps the mark used: (mark_series_id, mark_observation_ts, mark_vintage). When a price series is re-derived with a corrected vintage, the reconciliation pipeline can trace which position marks are stale and why.

Cat-4 Demo Walkthrough

Scenario: CAT4_FLA_LANDFALL_80B — synthesized Cat-4 hurricane, South Florida landfall, $80B modeled industry loss.

Step 1: Event → Exposure Walk

The event is inserted into event with a validated JSONB payload (category, landfall coordinates, max windspeed, modeled industry loss). The driver_exposure_map identifies which asset classes are affected: Cat XoL treaties via CLAIM_TRIGGER, SFR portfolios via VALUATION_DRIVER. event_instrument_exposure rows are written for each affected treaty and the Tampa SFR portfolio, with modeled_loss_usd computed from the event payload using a cedant market-share assumption.

Step 2: Treaty XoL Payouts

treaty_xol_payout implements:

payout = min(max(modeled_loss − attachment, 0), limit_remaining) × cession_pct

The model is verified by three unit tests before it touches the database:

Test	Input loss	Outcome
Partial fill, no exhaustion	$250M	Partial payout; `limit_remaining` drops; no reinstatement
Exhaustion + reinstatement	$350M	Full limit consumed; `reinstatements_consumed` increments; reinstatement premium booked
Exhausted reinstatements	$350M, reinst_consumed=max	Full limit consumed; layer is dead; no reinstatement premium

For the Cat-4 scenario, both Cat XoL treaties written by LongTail Re exhaust their remaining limits. Total claim payments (gross outflows): $6M across the two treaties. Reinstatement premiums triggered (inflows to LTRe): $60M.

Step 3: State Mutations

After payouts, the position layer is updated:

position_treaty.limit_remaining_usd drops (layer consumed)
position_treaty.reinstatements_consumed increments
position_treaty.attached_loss_to_date_usd increases

Cash rows are booked:

CLAIM_PAYMENT rows (outflows from LTRe to cedants)
REINSTATEMENT_PREMIUM rows (inflows to LTRe), each linked to the event via related_event_id

The Tampa SFR portfolio takes a dual impact: repair opex booked as a cash_transaction of type INSURANCE_PREMIUM (net hurricane P&L effect), plus a valuation impairment written to position.market_value_usd (~5% of gross asset value). These are kept distinct — the P&L effect and the balance sheet impairment are not collapsed.

Step 4: Lookthrough — LTRe Direct vs. Lookthrough

LTRe's impact is computed two ways, which are deliberately distinct:

Direct view: LTRe's Cat XoL treaties paid out $6M in claims and collected $60M in reinstatement premiums → LTRe direct net = +$54M
Lookthrough view: LTRe also holds units in SRAM Multi-Strat → SRAM SFR Fund → Tampa SFR SPV. After applying ownership percentages through the graph, LTRe's lookthrough net = ~+$49.5M (the SFR impairment reduces LTRe's attributed value through the fund stack)

The two views being distinct — and the graph traversal doing the right thing — is the proof that lookthrough works. entity_relationship is the DAG; elimination_required=TRUE prevents double-counting LTRe's SRAM holdings in the firmwide rollup.

Step 5: Firmwide Net

Firmwide P&L is the sum of direct holders' direct impacts (no lookthrough double-count):

Entity	Direct impact
LongTail Re Ltd	+$54,000,000
SRAM SFR Tampa SPV LLC	-$17,742,103
Firmwide net	+$36,257,897

Net positive — reinstatement premiums ($60M inflow to LTRe) exceed total claim payments ($6M outflow) and Tampa SFR impairment (~$17.7M). This is economically plausible: hard-market Cat XoL treaties price reinstatements at 100% pro-rata, and a well-structured reinsurer benefits from reinstatement premium income in large loss years.

Step 6: AI Narration

The narrator receives the structured retrieval output (entity rollup, per-instrument impacts, cash transactions, event summary) and returns a grounded narrative. It never sees raw schema. Temperature is set to 0; responses are cached keyed on scenario + retrieval hash for repeatable walkthroughs.

What's Deliberately Out of Scope

See docs/design.md §2 (docs/design.md, in the private repo) for the full scope table and docs/design.md §13 (docs/design.md, in the private repo) for Phase 2 items.

Short list: ASIC mining rigs, BTC spot/custody, alternative lending (LINDEX), art and fund units, live data feeds, production-grade ABOR ingestion, agentic LLM tool-calling, per-class detail table bitemporality (only the header layer is fully bitemporal), and risk metrics (VaR, stress, Greeks aggregation at portfolio level).

Asset-Class Coverage

Asset class	Instruments	Economics modeled	Data source
NatGas wells	6 (3 Marcellus, 3 Haynesville)	Arps decline production → net revenue via HH price + basis + NRI − LOE − severance tax	Production: real PA DEP bulk CSVs; Arps params fit from real data. HH price: EIA. Waha/Dom South basis: synthesized (paywalled)
Reinsurance treaties	5 (3 Cat XoL, 2 Casualty QS)	XoL payout with reinstatement consumption; claim/premium cash flows; limit/IBNR/UPR state	Treaty terms: synthesized to industry norms. Event losses: NOAA HURDAT2 + prospective Cat-4
SFR portfolios	3 MSAs (Phoenix, Tampa, Atlanta)	NOI = units × occupancy × rent − opex − tax − insurance; hurricane dual-impact (P&L + impairment)	Rent: Zillow ZORI. Home values: Zillow ZHVI + FHFA HPI. Position-level: synthesized
Crypto derivatives	4 (2 CME BTC futures, 2 Deribit BTC options)	Black-76 (European); Δ, Γ, ν, Θ; HIFO lot accounting	Spot: CoinMetrics community API. 30d ATM IV: Deribit public API

Scope boundaries

What's real public data, what's modeled, what's deliberately deferred.

EIA Waha and Dom South basis are synthesized via realistic historical dynamics. The real series are paywalled (ICE, Platts). Henry Hub spot and the 12-month forward curve are real EIA data.
PA DEP production is ingested via the PA DEP bulk CSV portal. Arps decline parameters are fit from the real monthly production history for 3 Marcellus wells. The 3 Haynesville wells use plausible synthesized parameters.
Some Haynesville positions show $0 net revenue in certain months. This is correct, not broken: small working interest (8–12%) combined with low realized gas prices (HH − basis) produces sub-zero net revenue after LOE and severance tax. The model floors at zero per design.
Deribit implied vol is ingested as 30-day ATM only. A full vol surface (by strike and tenor) is Phase 2.
SFR is portfolio-level, not per-property. A production srDB would have each property as an instrument with the portfolio as a derived rollup. The upgrade path is structural (add rows), not a redesign.
Treaty casualty QS reinstatement and sliding-scale commission identity is stubbed to base XoL-style payout for the POC. Full QS economics (sliding scale, loss corridor) are Phase 2.
LTRe → SRAM cross-holding is the Hedge Fund Re structure. It is modeled as a real graph with elimination logic. The lookthrough traversal is depth-limited to 10 hops with cycle detection; DuckDB handles the POC entity count (~50 entities) easily.

Repo Map

srdb-poc/
├── docs/
│   └── design.md                    Full design spec — primary reference
├── schema/
│   ├── 01_reference.sql             Entity, instrument, xref, role, calendar DDL
│   ├── 02_timeseries.sql            Series, observations, events, derivations DDL
│   ├── 03_position.sql              Position + extensions, trade, cash, recon DDL
│   ├── 04_analytics.sql             PnL models, driver_exposure_map, scenario DDL
│   └── views_lookthrough.sql        Consolidated vs. lookthrough view definitions
├── synth/                           Data synthesis and public ingestion modules
│   ├── entities.py                  SRHG entity graph + relationships
│   ├── entities_gleif.py            LEI lookups for external counterparties
│   ├── instruments_well.py          6 well instruments (Marcellus + Haynesville)
│   ├── instruments_treaty.py        5 reinsurance treaties (Cat XoL + Casualty QS)
│   ├── instruments_sfr.py           3 SFR portfolios (PHX, TPA, ATL)
│   ├── instruments_deriv.py         4 crypto derivatives (CME futures + Deribit opts)
│   ├── series_drivers_eia.py        EIA HH spot + forward curve ingestion
│   ├── series_drivers_coinmetrics.py  BTC spot ingestion
│   ├── series_drivers_deribit.py    BTC 30d ATM IV ingestion
│   ├── series_drivers_fred.py       UST, SOFR, mortgage rates ingestion
│   ├── series_drivers_zillow.py     ZORI rent + ZHVI home value ingestion
│   ├── series_drivers_fhfa.py       FHFA HPI by MSA ingestion
│   ├── series_instrument_padep.py   PA DEP production + Arps fitting
│   ├── events_hurdat2.py            NOAA HURDAT2 historical hurricane events
│   ├── events.py                    Synthetic events + event_instrument_exposure
│   ├── bitemporal_seeds.py          Bitemporal position/trade history seeding
│   ├── positions_well.py            Well positions + position_well extensions
│   ├── positions_treaty.py          Treaty positions + position_treaty extensions
│   ├── positions_sfr.py             SFR positions + position_sfr extensions
│   ├── positions_deriv.py           Deriv positions + position_crypto_deriv + lots
│   ├── cash_transactions.py         Premiums, royalties, taxes, funding, FX
│   ├── cost_basis_lots.py           BTC lot opens + partial closes (HIFO)
│   ├── abor_feed.py                 Synthesized ABOR with 4 deliberate breaks
│   └── seed_analytics.py           PnL models, driver map, scenario library
├── pipelines/
│   ├── derive_series.py             Evaluates all series_derivation formulas
│   ├── reconcile_abor.py            Produces run/pair/break/history records
│   └── lookthrough.py              Consolidation + lookthrough graph traversal
├── models/
│   ├── well_net_revenue.py          Arps + HH + NRI − LOE − tax identity
│   ├── treaty_xol_payout.py         XoL payout with reinstatement logic
│   ├── sfr_noi.py                   NOI + hurricane dual-impact identity
│   └── bs_option.py                 Black-76 pricing + Greeks
├── scenarios/
│   ├── cat4_fla_landfall.py         Flagship: Cat-4 FL landfall end-to-end
│   ├── btc_drawdown_vol_spike.py    BTC −30% spot, +40pt IV
│   └── hh_front_up.py              HH front-month +20% over 30 days
├── ai/
│   ├── question_router.py           NL question → scenario match
│   ├── retrieval.py                 driver_exposure_map walk + identity eval
│   └── narrator.py                  Structured retrieval → Claude narration
├── tests/                           Unit + integration tests (pytest)
├── queries/
│   └── demo.sql                     9 substrate and analytics demo queries
├── scripts/
│   ├── load_schema.py               Idempotent DDL loader
│   └── run_demo_queries.py          Runs and prints all demo queries
├── data/
│   └── raw/                         Cached parquet/json from public ingestion
├── srdb.duckdb                      Pre-built database (31 MB)
└── pyproject.toml                   uv project config (Python 3.12, DuckDB, Polars, Marimo)

Reference

docs/design.md (docs/design.md, in the private repo) is the primary reference. It covers the full data model DDL (§4–6), engineering identities (§7), the consolidation and lookthrough algorithm (§8b), the real vs. synthesized data plan (§9), all nine demo queries (§10), and Phase 2 scope (§13).

Appendix: Manual build sequence

scripts/build_all.py runs these 28 steps in order. Reproduced here if you want to invoke them individually:

# 1. Schema
uv run python scripts/load_schema.py

# 2. Layer 2 — Public-data ingestion (each requires internet on first run)
uv run python -m synth.series_drivers_eia
uv run python -m synth.series_drivers_zillow
uv run python -m synth.series_drivers_fred
uv run python -m synth.series_drivers_deribit
uv run python -m synth.series_drivers_coinmetrics
uv run python -m synth.series_drivers_fhfa
uv run python -m synth.series_instrument_padep
uv run python -m synth.events_hurdat2

# 3. Entities + LEIs
uv run python -m synth.entities
uv run python -m synth.entities_gleif

# 4. Instruments
uv run python -m synth.instruments_well
uv run python -m synth.instruments_treaty
uv run python -m synth.instruments_sfr
uv run python -m synth.instruments_deriv

# 5. Positions + events
uv run python -m synth.positions_well
uv run python -m synth.positions_treaty
uv run python -m synth.positions_sfr
uv run python -m synth.positions_deriv
uv run python -m synth.events

# 6. Position-derivatives + ABOR + analytics catalogs
uv run python -m synth.cash_transactions
uv run python -m synth.cost_basis_lots
uv run python -m synth.abor_feed
uv run python -m synth.seed_analytics

# 7. Pipelines
uv run python -m pipelines.derive_series
uv run python -m pipelines.reconcile_abor
uv run python -m pipelines.lookthrough

# 8. Bitemporal seed (last — see scripts/build_all.py for why)
uv run python -c "import duckdb; from synth.bitemporal_seeds import seed_bitemporal_corrections; seed_bitemporal_corrections(duckdb.connect('srdb.duckdb'))"