recon_gen.common.l2.config_table

<prefix>_config_kv — flattened cfg + L2 JSON tree (BC.12).

Originally a 3-column <prefix>_config(as_of, cfg_yaml, l2_yaml) single-row table (Phase AW). Replaced in BC.12 to dodge Oracle’s ORA-32368: cannot create JSON materialized view without relational table — Oracle 19c+ refuses to build a matview whose source is JSON_TABLE of a CLOB column. The kv flattening lets matviews JOIN typed projection views (<prefix>_v_config_rails etc.) whose own bodies are plain self-joins on relational columns; the matview engine sees a relational source, not a JSON_TABLE-of-CLOB.

Table shape:

<prefix>_config_kv(
    node_id   BIGINT       PRIMARY KEY,
    parent_id BIGINT       NULL,   -- self-ref, NULL for roots
    key       VARCHAR(255) NULL,   -- JSON key / array index
    value     TEXT         NULL    -- CLOB on Oracle (holds l2_yaml_raw)
)

Walk semantics (Python-side at populate time):

  • Top-level scalars (as_of, l2_yaml_raw opaque provenance) live at parent_id IS NULL as flat rows.

  • Nested structures (rails, limit_schedules, etc.) walk recursively; each container gets a row with value IS NULL (its descendants carry the data); each scalar leaf gets a row with the scalar value.

  • Array elements: key is the stringified index ('0', '1'…).

  • Object fields: key is the field name.

Operational lifecycle (BC.12 lock):

  1. Once — schema apply --execute creates <prefix>_config_kv.

  2. Every deploy (L2 changes) — schema apply --execute re-creates matviews + repopulates <prefix>_config_kv (TRUNCATE + INSERT-N from the parsed cfg+L2 JSON).

  3. Daily (post-ETL) — data refresh --execute REFRESHes matviews.

Deploy-artifact contract (BL.0.B, 2026-05-27): <prefix>_config_kv is part of the deployment artifact bundle — NOT a runtime database. Reads off the table are reads of whatever the last ``schema apply –execute`` or ``data apply –execute`` wrote. Edits land via re-deploy; never via SQL UPDATE / INSERT from Studio mutations, ETL pipelines, or operator patches. The DELETE-then-INSERT at the top of emit_config_populate_sql / replace_config makes “what’s in the table” == “what’s in the on-disk yaml at deploy time” by construction. This is the discipline that obviates the (prefix, l2_yaml_sha) keying spike the BL.0 audit considered: since edits always go through the wipe, the temporal-drift case (“yaml v1 deployed Monday, yaml v2 on disk by Friday, kv still holds v1”) is operationally impossible — Friday’s deploy wipes Monday’s rows before INSERTing v2.

Indexed on (parent_id, key) — the typed views’ filter shape. No index on value (CLOB-incompatible on Oracle without a function- based index; the typed views walk by parent_id+key, never by value).

Migration from the pre-BC.12 <prefix>_config table: existing customer deploys had <prefix>_config empty (BC.6 surfaced production never populated it), so the “migration” is just schema apply --execute against the v11.19.0 schema — no data loss because there was no data.

Functions

config_table_name(prefix)

The canonical <prefix>_config_kv table name.

emit_config_populate_sql(*, prefix, ...)

Emit the DELETE + INSERT-N-rows SQL that populates <prefix>_config_kv from parsed cfg+L2 JSON + as_of.

emit_config_table_ddl(prefix, dialect)

CREATE TABLE <prefix>_config_kv DDL for the given dialect.

emit_config_table_drop(prefix, dialect)

DROP TABLE IF EXISTS <prefix>_config_kv; DDL for re-runs.

get_as_of(conn, *, prefix)

Read the current as_of value back as a Python datetime.

kv_as_of_as_timestamp_sql(prefix, dialect)

Read the as_of kv row and project as TIMESTAMP for date arithmetic (epoch_seconds_between and friends).

kv_as_of_subquery(prefix)

Scalar subquery returning the raw as_of value (text form).

kv_root_id_for(prefix, top_key)

Scalar subquery returning the node_id of the root container keyed top_key (one of 'l2_yaml' / 'cfg_yaml').

kv_rows_for(cfg_json, l2_json, *, as_of)

Walk parsed cfg + L2 JSON into kv rows.

replace_config(conn, *, prefix, cfg_json, ...)

Deploy event — re-populate kv from parsed cfg+L2 + as_of.

set_as_of(conn, *, prefix[, as_of])

ETL refresh event — update the as_of scalar row.
recon_gen.common.l2.config_table.config_table_name(prefix)[source]

The canonical <prefix>_config_kv table name.

BC.12 renamed from <prefix>_config to <prefix>_config_kv so the suffix announces the shape change (flattened kv vs the old 3-column shape). Pre-BC.12 deploys had the old table empty (BC.6 finding); no migration needed beyond running the new schema.

Return type:

str

Parameters:

prefix (str)

recon_gen.common.l2.config_table.emit_config_populate_sql(*, prefix, cfg_json, l2_json, as_of, dialect)[source]

Emit the DELETE + INSERT-N-rows SQL that populates <prefix>_config_kv from parsed cfg+L2 JSON + as_of.

Replaces the pre-BC.12 single-row INSERT INTO <prefix>_config (as_of, cfg_yaml, l2_yaml) VALUES (...).

Empties the table first (DELETE — TRUNCATE isn’t atomic with the subsequent INSERTs in every dialect’s transactional semantics, and DELETE is plenty fast for a single-deploy populate), then issues one INSERT per kv row. Each INSERT goes on its own line so the script splitter in common/db.execute_script can run them individually — dialect drivers don’t all support multi-row VALUES cleanly across vendor lines (and oracledb in particular).

Long string values (e.g. the l2_yaml_raw provenance row at ~37 KB for sasquatch_pr) are split on Oracle into 4000-byte chunks concatenated via TO_CLOB(chunk1) || TO_CLOB(chunk2) || ... — Oracle’s literal limit is ORA-01704: string literal too long at 4000 bytes per single-quoted literal, but the concatenation of multiple shorter literals into a CLOB has no such cap. PG + SQLite accept multi-megabyte literals directly; their path is the single- literal form.

All input is walker-controlled (parsed JSON values, never operator input flowing through to SQL) — no SQL-injection surface.

Return type:

str

Parameters:

  • prefix (str)

  • cfg_json (str)

  • l2_json (str)

  • as_of (datetime)

  • dialect (Dialect)

recon_gen.common.l2.config_table.emit_config_table_ddl(prefix, dialect)[source]

CREATE TABLE <prefix>_config_kv DDL for the given dialect.

Column shape (BC.12.3 spike-locked):

  • node_id BIGINT PK — Python-side monotonic counter at populate time (no DB sequence; keeps TRUNCATE + repopulate atomic).

  • parent_id BIGINT NULL — self-ref to node_id; NULL for root nodes (top-level keys). No FK declared: kv is internal-only and the walker writes parents before children, so the constraint adds no value over correctness-by-construction (and Oracle would force a deferred constraint for batch inserts).

  • key VARCHAR(255) — JSON field name or stringified array index.

  • value TEXT/CLOB — scalar leaf value (or NULL for container nodes). CLOB on Oracle so the l2_yaml_raw opaque-provenance row fits (sasquatch_pr’s full L2 JSON is ~37KB; VARCHAR2(4000) would force splitting). The typed projection views coerce CLOB → VARCHAR2 via lob_substr before MAX/aggregation (Oracle’s MAX rejects CLOB with ORA-22849).

Index on (parent_id, key) — the typed views’ filter shape (WHERE parent_id = X AND key = 'Y'). No index on value (CLOB-incompatible on Oracle without function-based indexes).

Return type:

str

Parameters:
recon_gen.common.l2.config_table.emit_config_table_drop(prefix, dialect)[source]

DROP TABLE IF EXISTS <prefix>_config_kv; DDL for re-runs.

Drops the index implicitly (PG + Oracle + SQLite all drop indexes when the table they belong to is dropped).

Return type:

str

Parameters:
recon_gen.common.l2.config_table.get_as_of(conn, *, prefix)[source]

Read the current as_of value back as a Python datetime.

Reads from the single as_of kv row at parent_id IS NULL. Raises RuntimeError if the row doesn’t exist — the single-row invariant: call replace_config before get_as_of / set_as_of.

Return type:

datetime

Parameters:

  • conn (SyncConnection)

  • prefix (str)

recon_gen.common.l2.config_table.kv_as_of_as_timestamp_sql(prefix, dialect)[source]

Read the as_of kv row and project as TIMESTAMP for date arithmetic (epoch_seconds_between and friends).

Dialect-specific coercion:

  • PG: CAST(... AS TIMESTAMP) — PG accepts the ISO-format text and yields a proper TIMESTAMP.

  • DuckDB: CAST(... AS TIMESTAMP) — same shape as PG. DuckDB is strict about implicit casts (VARCHAR - TIMESTAMP raises BinderException unless the VARCHAR is explicitly cast), so the cast is load-bearing here even though it looks redundant.

  • Oracle: TO_TIMESTAMP(DBMS_LOB.SUBSTR(value, 100, 1), 'YYYY-MM-DD HH24:MI:SS') — Oracle won’t CAST CLOB to TIMESTAMP (ORA-00932); DBMS_LOB.SUBSTR converts to VARCHAR2 first, then TO_TIMESTAMP parses the fixed-format string.

  • SQLite: bare text — SQLite has no TIMESTAMP type, and julianday(text) accepts ISO-format strings natively (so the epoch_seconds_between SQLite branch’s (julianday(later) - julianday(earlier)) * 86400 works unchanged).

Return type:

str

Parameters:
recon_gen.common.l2.config_table.kv_as_of_subquery(prefix)[source]

Scalar subquery returning the raw as_of value (text form).

The walker stores as_of as a stringified YYYY-MM-DD HH:MM:SS leaf; this helper wraps the kv read so callers don’t need to know the storage shape. Pair with kv_as_of_as_timestamp_sql when the consumer needs a typed TIMESTAMP (e.g. for date arithmetic).

Return type:

str

Parameters:

prefix (str)

recon_gen.common.l2.config_table.kv_root_id_for(prefix, top_key)[source]

Scalar subquery returning the node_id of the root container keyed top_key (one of 'l2_yaml' / 'cfg_yaml'). Used by typed views to anchor their walks.

The kv populate guarantees exactly one row per top-level key at parent_id IS NULL; the subquery returns a single value.

Return type:

str

Parameters:

  • prefix (str)

  • top_key (str)

recon_gen.common.l2.config_table.kv_rows_for(cfg_json, l2_json, *, as_of)[source]

Walk parsed cfg + L2 JSON into kv rows.

Row order: as_of scalar first, then the parsed L2 tree, then the parsed cfg tree, then the opaque-provenance l2_yaml_raw / cfg_yaml_raw rows. The walker assigns node_id monotonically; callers MUST insert in this order to preserve parent-before-child ordering even without an FK constraint.

The L2 tree’s top-level fields (rails, limit_schedules, etc.) land at parent_id = the l2_yaml container node. The typed projection views walk DOWN from that container via parent_id JOINs.

cfg_json is included for downstream symmetry / future cfg-derived views, even though no current matview consumes it. Caller-friendly: pass '{}' to skip cfg-side rows entirely (the walk produces one empty-container row).

Return type:

list[tuple[int, int | None, str | None, str | None]]

Parameters:

  • cfg_json (str)

  • l2_json (str)

  • as_of (datetime)

recon_gen.common.l2.config_table.replace_config(conn, *, prefix, cfg_json, l2_json, as_of)[source]

Deploy event — re-populate kv from parsed cfg+L2 + as_of.

DELETE + INSERT preserves an idempotent populate without relying on dialect-specific UPSERT. The caller is responsible for serializing cfg + L2 to JSON strings (typically via dataclasses.asdict + json.dumps, or by reading the source YAML and re-serializing).

Cursor flavor: the type annotation is SyncConnection for SQLite-test convenience, but the function is duck-typed against the PEP 249 conn / cursor interface — psycopg2 + oracledb both work.

Return type:

None

Parameters:

  • conn (SyncConnection)

  • prefix (str)

  • cfg_json (str)

  • l2_json (str)

  • as_of (datetime)

recon_gen.common.l2.config_table.set_as_of(conn, *, prefix, as_of=None)[source]

ETL refresh event — update the as_of scalar row.

as_of=None (production default) → updates to CURRENT_TIMESTAMP so matview age formulas pick up “right now” at refresh time. Literal datetime → pinned (tests + backfill scenarios).

Assumes the kv table is already populated — call replace_config once at deploy/init time before any set_as_of calls.

Return type:

None

Parameters:

  • conn (SyncConnection)

  • prefix (str)

  • as_of (datetime | None)