recon_gen.common.l2.schema

Prefix-aware SQL DDL emission for an L2Instance (M.1.4 + M.1.5).

Emits one idempotent (drop-then-create) DDL script per L2 instance, prefixed per the SPEC’s storage-isolation rule (F10):

<prefix>transactions — base table; L1 Transaction

denormalized with Account + Transfer fields per the Implementation Entities section.

<prefix>daily_balances — base table; L1 StoredBalance

denormalized with Account fields.

<prefix>current_transactions — view; the L1 CurrentTransaction

theorem materialized as max-Entry- per-ID over the base table.

<prefix>current_daily_balances — view; the L1 CurrentStoredBalance

theorem materialized as max-Entry- per-(account, business_day) over the base table.

Plus B-tree indexes for the dashboard’s hot-path queries on the bases.

The dashboard SQL targets the current_* views, never the bases — that way Entry-supersession (technical-error correction per the F1 principle) is transparent to dashboard consumers.

What is NOT emitted as SQL tables (per the M.0 spike’s experience): - L2’s account topology (Roles, AccountTemplates, parent_role chains) —

the relevant fields denormalize onto the transactions / daily_balances rows; no separate dim table needed for v1.

  • L2’s Limits — projected into daily_balances.metadata.limits (a nested JSON map keyed by Rail name) by integrator ETL; no separate limits table. AV (2026-05-23) renamed the column from limits to metadata and demoted the per-rail caps to a nested key so the column mirrors transactions.metadata and has room for siblings (scenario_id per AV.5, future per-day tags).

  • L2’s Chains, TransferTemplates — read by dashboard SQL at view-build time (the SQL string knows which TransferTypes can chain into which via L2 lookups), not materialized as tables.

The “minimum SQL surface” stance follows from the spike: M.2 (porting AR CMS) will surface what L2 derived tables are actually needed beyond the base layer. Add them then.

Functions

emit_schema(instance, *, prefix[, dialect])

Emit the full DDL script for an L2 instance's prefixed L1 schema.

emit_schema_drop_sql(instance, *, prefix[, ...])

Emit DROP statements for every per-prefix object emit_schema creates.

refresh_matviews_sql(instance, *, prefix[, ...])

Emit REFRESH MATERIALIZED VIEW commands in dependency order.

wipe_demo_data_sql(instance, *, prefix[, ...])

Emit DELETE statements that empty the per-prefix base tables.
recon_gen.common.l2.schema.emit_schema(instance, *, prefix, dialect=Dialect.POSTGRES)[source]

Emit the full DDL script for an L2 instance’s prefixed L1 schema.

Three layers, all per L2 instance prefix:

  1. Base tables — <prefix>_transactions + <prefix>_daily_balances, v6 column shape (entry BIGSERIAL, amount_money + amount_direction, transfer_parent_id, rail_name, template_name, bundle_id, supersedes, …).

  2. Current* views — <prefix>_current_transactions + <prefix>_current_daily_balances, materializing L1’s max-Entry-per-logical-key theorems so dashboard SQL is transparent to technical-error supersession.

  3. L1 invariant views (M.1a.7) — <prefix>_drift / <prefix>_ledger_drift / <prefix>_overdraft / <prefix>_expected_eod_balance_breach / <prefix>_limit_breach (plus 2 helpers: <prefix>_computed_subledger_balance + <prefix>_computed_ledger_balance). Each materializes one of the SPEC’s L1 SHOULD-constraints as a queryable exception surface; rows in these views are the constraint violations. Caps for <prefix>_limit_breach are embedded inline from instance.limit_schedules at view-emit time (CASE branches per declared (parent_role, rail) pair) so the view DDL stays JSON-path-portable.

Idempotent: every CREATE is preceded by a DROP IF EXISTS so re-running the same apply schema clears stale state. The returned string can be fed straight to psql or psycopg2.cursor.execute(sql).

dialect selects the SQL flavor. P.3.d unblocked Oracle by threading dialect helpers through every template; both branches are now first-class. New dialects would need a new Dialect enum value plus per-helper Oracle/Postgres-style branches in common.sql.dialect.

Z.C — prefix is the cfg.db_table_prefix (formerly read off the dropped L2Instance.instance field).

Return type:

str

Parameters:
recon_gen.common.l2.schema.emit_schema_drop_sql(instance, *, prefix, dialect=Dialect.POSTGRES)[source]

Emit DROP statements for every per-prefix object emit_schema creates.

The teardown counterpart of emit_schema. Composes the same private drop helpers that prelude every CREATE in the full schema output, plus the base-table and base-index drops.

Order matters: matviews → views → tables, with Inv matviews and L1 invariant matviews first (they depend on the Current* matviews which depend on the base tables). Indexes get dropped before the tables they index.

Returns one SQL string suitable for piping to psql or splitting + executing per-statement. Idempotent — every DROP is IF EXISTS (or a swallow-already-gone PL/SQL block on Oracle). Use schema clean -o FILE for the CLI surface.

Z.C — prefix is the cfg.db_table_prefix.

Return type:

str

Parameters:
recon_gen.common.l2.schema.refresh_matviews_sql(instance, *, prefix, dialect=Dialect.POSTGRES)[source]

Emit REFRESH MATERIALIZED VIEW commands in dependency order.

M.1a.9 made every L1-pipeline view a MATERIALIZED VIEW (kills the correlated-subquery cost the deployed dashboard pays per visual on DIRECT_QUERY mode). Refresh contract for integrators: after every batch insert into the base tables, call this SQL to recompute every dependent matview. Order matters — leaves first, then helpers, then L1 invariants — because a downstream matview’s REFRESH reads from upstream matview data.

Returns one REFRESH MATERIALIZED VIEW <name>; per line on PG / Oracle. SQLite has no matviews — refresh becomes a per-table DELETE FROM <name>; INSERT INTO <name> <body>; pair, where <body> is the same SELECT the matview was originally created with. To avoid duplicating every matview body here, the SQLite branch uses DROP TABLE … CREATE TABLE … AS <body> — re-runs the schema’s matview-create SQL by tearing down + rebuilding.

Caller splits + executes (psycopg2’s cursor.execute can’t run multiple statements separated by ; reliably; the verify script splits on ;n and runs each per-statement).

Z.C — prefix is the cfg.db_table_prefix.

Return type:

str

Parameters:
recon_gen.common.l2.schema.wipe_demo_data_sql(instance, *, prefix, dialect=Dialect.POSTGRES)[source]

Emit DELETE statements that empty the per-prefix base tables.

BS.4 (2026-05-29) — step 1 of the deploy pipeline (the wipe swapped to first per the architecture shift; the etl_hook now writes into the clean DB directly). The demo DB’s <prefix>_transactions + <prefix>_daily_balances are emptied so the etl_hook (step 2) and the generator (step 3) both write into clean state. Step 4’s matview refresh then re-derives every Current* / L1 invariant / Inv matview from the new base data.

Schema is preserved — this is row-level wipe, not DROP. The operator’s dataset / dashboard ARNs stay intact, so their bookmarked URLs still resolve after the deploy.

Returns one SQL string for execute_script(cur, sql, dialect=…). No FK between the two base tables (per Schema_v6), so order is irrelevant; daily_balances first matches the schema-emit order.

Z.C — prefix is the cfg.db_table_prefix.

Return type:

str

Parameters: