recon_gen.apps.l2_flow_tracing.datasets

QuickSight DataSet builders for the L2 Flow Tracing app.

The Chains / L2 Exceptions tabs join L2-declared values (static, from the L2 instance) to runtime activity (from the prefixed <prefix>_current_transactions matview). The L2 declarations are inlined into the SQL as a CTE of literal rows — no per-rail dataset proliferation, no per-instance database table.

The Rails tab is a transactions explorer (M.3.10c rewrite — the M.3.5 declared-rails table moves to a future Docs tab). It uses two new datasets that participate in the metadata cascade:

• l2ft-postings-ds: one row per leg, parameterized on pKey + pValues so the metadata cascade filters it via QS <<$param>> substitution into a JSONPath.

• l2ft-meta-values-ds: distinct metadata values for the chosen key, parameterized on pKey so the Value dropdown narrows when the Key dropdown changes.

Substep landmarks:

• M.3.4 — skeleton (no datasets)

• M.3.5 — Rails dataset (later DROPPED in M.3.10c — moves to Docs tab)

• M.3.6 — Chains dataset

• M.3.7 — L2 Exceptions datasets (six small KPI-backers)

• M.3.8 — Auto metadata-driven filter dropdown sources (later DROPPED in M.3.10c — replaced by the cascade)

• M.3.10c — Rails tab redesign on dataset parameters

Functions

build_all_l2_flow_tracing_datasets(cfg, ...)	Return every Dataset the L2 Flow Tracing app needs.
build_chain_instances_dataset(cfg, l2_instance)	One row per parent transfer firing of a declared chain parent (M.3.10d, completion_status extended in M.3.10i for XOR groups).
build_chains_dataset(cfg, l2_instance)	One row per declared Chain-row child (Z.A: a multi-children row contributes N rows) — the L2's parent→child topology joined to runtime parent firing counts + matched-child counts.
build_exc_chain_orphans_dataset(cfg, l2_instance)	L2.1 — Required Chain entries where parent fired but no matched child fired in the window.
build_exc_dead_bundles_activity_dataset(cfg, ...)	L2.4 — Aggregating-rail bundles_activity targets that no posting matched in the window.
build_exc_dead_limit_schedules_dataset(cfg, ...)	L2.6 — LimitSchedule (parent_role, rail_name) cells with zero outbound debit flow in the window.
build_exc_dead_metadata_dataset(cfg, l2_instance)	L2.5 — Rail.metadata_keys declared in L2 that no posting carries a non-null value for in the window.
build_exc_dead_rails_dataset(cfg, l2_instance)	L2.3 — Rails declared in L2 with zero postings in the window.
build_exc_unmatched_rail_name_dataset(cfg, ...)	L2.2 — Posted Transactions whose rail_name doesn't match any declared Rail.rail_name.
build_meta_values_dataset(cfg, l2_instance)	Long-form (metadata_key, metadata_value) for the cascade.
build_postings_dataset(cfg, l2_instance)	One row per leg from <prefix>_current_transactions, parameterized so the Rails sheet's filters push down into SQL via QS <<$param>> substitution.
build_tt_instances_dataset(cfg, l2_instance)	One row per shared Transfer that matches a declared TransferTemplate (M.3.10f, completion_status reshaped M.3.10j).
build_tt_legs_dataset(cfg, l2_instance)	One row per Sankey edge segment for a TransferTemplate firing (M.3.10f, chain-edge UNION added in M.3.10i).
build_unified_l2_exceptions_dataset(cfg, ...)	UNION ALL across the 6 L2 hygiene checks into one row-per- violation dataset (M.3.10l).
bundle_status_values()	Bounded enum of bundle_status calc-field values.
chain_completion_status_values()	Bounded enum of chain-instances completion_status values.
declared_chain_parents(l2_instance)	Sorted list of distinct Chain row parent names.
declared_metadata_keys(l2_instance)	Sorted list of distinct metadata keys declared across every rail in the L2 instance.
declared_rail_names(l2_instance)	Sorted list of declared Rail names.
declared_template_names(l2_instance)	Sorted list of declared TransferTemplate names.
l2ft_matview_specs(cfg)	Matviews + base tables the L2 Flow Tracing dashboard reads, paired with the date column for App Info's latest_date KPI.
metadata_filter_clause(l2_instance, ...)	WHERE-fragment that filters by the metadata key/value cascade, portable across Postgres + Oracle + SQLite.
transaction_status_values()	Bounded enum of transaction status values.
tt_completion_status_values()	Bounded enum of transfer-template completion_status values.

recon_gen.apps.l2_flow_tracing.datasets.build_all_l2_flow_tracing_datasets(cfg, l2_instance)

Return every Dataset the L2 Flow Tracing app needs.

Mirrors build_all_l1_dashboard_datasets: derives an L2-aware cfg (so dataset IDs carry the L2 instance prefix as their middle segment per M.2d.3) when the caller hasn’t pre-stamped it. Idempotent — re-deriving an already-L2-aware cfg is a no-op.

M.3.6 ships Chains; M.3.7 adds the 6 L2 Exceptions sections; M.3.10c adds the postings explorer + meta-values cascade source for the Rails tab (replacing M.3.5’s declared-rails table — moves to a future Docs tab — and M.3.8’s 28 per-key metadata dropdowns — replaced by the cascade); M.3.10d swaps the chains aggregate dataset for a per-parent-firing explorer (chain-instances); M.3.10f adds the Transfer Templates sheet with tt-instances (per shared Transfer) + tt-legs (per leg, backing the multi-leg flow Sankey); M.3.10l replaces the 6 separate L2 exception datasets with one unified UNION-ALL dataset (mirrors L1’s exceptions pattern — single KPI + bar chart + detail table).

Return type:

list[DataSet]

Parameters:

• cfg (Config)

• l2_instance (L2Instance)

recon_gen.apps.l2_flow_tracing.datasets.build_chain_instances_dataset(cfg, l2_instance)

One row per parent transfer firing of a declared chain parent (M.3.10d, completion_status extended in M.3.10i for XOR groups). Backs the Chains sheet’s per-instance explorer.

Columns:

• parent_chain_name — the L2-declared parent rail / template name. Drives the Chain dropdown’s selectable values.

• parent_transfer_id — DISTINCT transfer_id of the parent firing. Multiple legs of one transfer collapse to one row via GROUP BY.

• completion_status — computed inline; one of:

  • 'Completed' — every Required child fired AND every XOR group has exactly one member fired.

  • 'Incomplete' — at least one Required child missing OR any XOR group has 0 fired (orphan) OR > 1 fired (violation).

  The pre-X.1.j third branch 'No Required Children' is gone — validator rule C5 rejects all-optional / no-XOR chains at L2 load, so the SQL never produces that case.

• parent_metadata is read in the WHERE only — kept off the contract so users don’t see raw JSON. pKey / pValues substitute into a JSONPath IN (...) predicate same as the postings dataset; the __ALL__ sentinel short-circuits to “no metadata filter”.

SQL portability: correlated subqueries (no ARRAY_AGG); no JSONB; MAX aggregates over varchar status / metadata which isn’t perfect but the parent transfer’s legs share these values in practice. The chains table is bounded by L2 declarations (typically tens of entries) so the cost stays predictable.

Return type:

DataSet

Parameters:

• cfg (Config)

• l2_instance (L2Instance)

recon_gen.apps.l2_flow_tracing.datasets.build_chains_dataset(cfg, l2_instance)

One row per declared Chain-row child (Z.A: a multi-children row contributes N rows) — the L2’s parent→child topology joined to runtime parent firing counts + matched-child counts.

A row IS one Sankey edge in the Chains visual. Counts come from <prefix>_current_transactions matched on the parent’s name (which can be a Rail’s rail_name OR a TransferTemplate’s template_name — every leg row carries both, with template_name taking precedence when a rail is part of a template). Child matches require transfer_parent_id to point at one of the parent’s transfer_ids — that’s the runtime “did this child fire in response to this parent” relation.

Orphan rate = (parent_firings without a matched child) / parent_firings. A required Chain entry with non-zero orphan rate is the seed for M.3.7’s L2.1 ‘Chain orphans’ exception.

Note on portability: uses correlated subqueries instead of ARRAY_AGG (PG-only) to keep the SQL portable. The chains table is small (typically tens of entries), so the cost is bounded.

Return type:

DataSet

Parameters:

• cfg (Config)

• l2_instance (L2Instance)

recon_gen.apps.l2_flow_tracing.datasets.build_exc_chain_orphans_dataset(cfg, l2_instance)

L2.1 — Required Chain entries where parent fired but no matched child fired in the window.

Reuses the chains dataset’s CTE shape (declared edges + edge runtime) and filters to required = 'Required' AND orphan_count > 0. XOR-group multi/none violations are deferred to a follow-on substep — a precise XOR check needs per-Transfer-id grouping that the simpler aggregate doesn’t capture.

AJ.4 (Gap I): a fan_in (N:1) chain is singleton-children, so it’s labeled Required and would take the naive parent_firing_count − child_firing_count path — but N parents converging on far fewer shared child Transfers makes that difference large and positive on healthy batching, so every fan_in chain read as a pile of orphans. For fan_in edges the orphan count is instead the number of parent firings whose transfer_id is NOT a parent of any child batch Transfer (the genuine “cycle closed but never assigned to a batch”), derived from the AB.4.3 _transfer_parents matview. This is production-correct: fan_in is a real structural property of the L2, not a demo artifact.

Return type:

DataSet

Parameters:

• cfg (Config)

• l2_instance (L2Instance)

recon_gen.apps.l2_flow_tracing.datasets.build_exc_dead_bundles_activity_dataset(cfg, l2_instance)

L2.4 — Aggregating-rail bundles_activity targets that no posting matched in the window.

Per Z.B (2026-05-15): bundles_activity refs are Identifier rail names; the SQL checks t.rail_name = db.bundle_target. Each row is one (aggregating_rail, target) pair the L2 declared but the runtime never realized.

Return type:

DataSet

Parameters:

• cfg (Config)

• l2_instance (L2Instance)

recon_gen.apps.l2_flow_tracing.datasets.build_exc_dead_limit_schedules_dataset(cfg, l2_instance)

L2.6 — LimitSchedule (parent_role, rail_name) cells with zero outbound debit flow in the window.

Means the cap is effectively dead — either nobody routes that role/type combination, or the L2 declared a cap nobody enforces against. NOT EXISTS over the prefixed transactions matview keeps the query bounded by the (small) limit-schedule count.

Return type:

DataSet

Parameters:

• cfg (Config)

• l2_instance (L2Instance)

recon_gen.apps.l2_flow_tracing.datasets.build_exc_dead_metadata_dataset(cfg, l2_instance)

L2.5 — Rail.metadata_keys declared in L2 that no posting carries a non-null value for in the window.

Each (rail, metadata_key) pair gets its own SQL fragment in the UNION ALL. Static JSON paths sidestep PostgreSQL’s reluctance to accept dynamic JSONPath arguments to JSON_VALUE — keeps the SQL portable per the project’s no-JSONB constraint.

Return type:

DataSet

Parameters:

• cfg (Config)

• l2_instance (L2Instance)

recon_gen.apps.l2_flow_tracing.datasets.build_exc_dead_rails_dataset(cfg, l2_instance)

L2.3 — Rails declared in L2 with zero postings in the window.

Same shape as the Rails dataset but pre-filtered to COALESCE(r.total_postings, 0) = 0. The KPI shows the count; the detail table lists the dead rails so the integrator can decide whether to retire the declaration or fix the ETL.

Return type:

DataSet

Parameters:

• cfg (Config)

• l2_instance (L2Instance)

recon_gen.apps.l2_flow_tracing.datasets.build_exc_unmatched_rail_name_dataset(cfg, l2_instance)

L2.2 — Posted Transactions whose rail_name doesn’t match any declared Rail.rail_name.

The runtime version of M.2d.1’s deferred validator check (‘every Transfer MUST match a Rail’). LEFT JOIN to a CTE of declared types + filter to NULL surfaces the unmatched rows. Output is per-transfer-type with a count of postings carrying that type — the table reveals what’s leaking past the L2’s rails.

Return type:

DataSet

Parameters:

• cfg (Config)

• l2_instance (L2Instance)

recon_gen.apps.l2_flow_tracing.datasets.build_meta_values_dataset(cfg, l2_instance)

Long-form (metadata_key, metadata_value) for the cascade.

Built as a UNION ALL across declared metadata keys, projecting one row per (transaction, key) combination where that key has a non-null value. The Value dropdown’s LinkedValues sources from the metadata_value column; QS’s CascadingControlConfiguration filters rows by the Key dropdown’s selection matched against the metadata_key column — picking ‘customer_id’ narrows the dataset to that key’s rows, then DISTINCT metadata_value populates the dropdown.

No dataset parameters needed — the cascade is purely column-match on the analysis side. The earlier single-column + parameter-substituted shape didn’t work because QS’s cascade is a column-match filter, not a parameter-driven dataset re-query (M.3.10c finding).

For an L2 instance with no declared metadata keys, the SELECT is replaced with WHERE FALSE so the dataset emits valid SQL that returns no rows.

Return type:

DataSet

Parameters:

• cfg (Config)

• l2_instance (L2Instance)

recon_gen.apps.l2_flow_tracing.datasets.build_postings_dataset(cfg, l2_instance)

One row per leg from <prefix>_current_transactions, parameterized so the Rails sheet’s filters push down into SQL via QS <<$param>> substitution.

Two parameter families, both server-side:

• Metadata cascade — pKey (single) + pValues (single). pKey = '__ALL__' short-circuits the metadata WHERE to “no filter” (freshly-loaded dashboard renders every leg); pValues = '__placeholder__' matches no real value, so picking a Key without a Value goes empty (UX hint to pick both). Stays in the inner query’s WHERE.

• Category pushdown (Y.2.c + AA.A.3) — pL2ftRail / pL2ftStatus / pL2ftBundle (all SINGLE_VALUED post-AA.A.3; was MULTI_VALUED pre-flip). Defaults to the L2FT_ALL_SENTINEL so a freshly-loaded dashboard matches every row via the sentinel disjunct in _match_all_in_clause. Bridged from the Rails sheet’s SINGLE_SELECT dropdowns; clearing a dropdown reverts to the sentinel default. Pushed into the OUTER WHERE because status and bundle_status are CASE-aliases (not visible to a WHERE in the same SELECT) so the projection wraps in a subquery.

Phase BM — date narrowing pushes down via <<$pL2ftDateStart>> / <<$pL2ftDateEnd>> (replaces the pre-BM per-sheet TimeRangeFilter on QS + no-narrow on App2). One SQL form across both renderers.

Return type:

DataSet

Parameters:

• cfg (Config)

• l2_instance (L2Instance)

recon_gen.apps.l2_flow_tracing.datasets.build_tt_instances_dataset(cfg, l2_instance)

One row per shared Transfer that matches a declared TransferTemplate (M.3.10f, completion_status reshaped M.3.10j).

A “shared Transfer” is one transfer_id from <prefix>_current_transactions whose legs all carry the same template_name matching a declared template. Per SPEC: every firing of a leg_rails rail with the same transfer_key Metadata values posts to the same shared Transfer, so the transfer_id distinct-count = number of TransferTemplate instances.

completion_status is one of:

• ‘Imbalanced’ — ABS(actual_net - expected_net) >= 0.01 (L1 Conservation break).

• ‘Orphaned’ — balanced, but a Required chain child didn’t fire OR an XOR group has 0 or > 1 fired members (L2 chain break).

• ‘Complete’ — balanced AND every Required child fired AND every XOR group has exactly one fired member.

Mirrors the chain-instances completion_status semantics so the analyst sees consistent language across both sheets.

Parameterized on pKey + pValues for the metadata cascade.

Return type:

DataSet

Parameters:

• cfg (Config)

• l2_instance (L2Instance)

recon_gen.apps.l2_flow_tracing.datasets.build_tt_legs_dataset(cfg, l2_instance)

One row per Sankey edge segment for a TransferTemplate firing (M.3.10f, chain-edge UNION added in M.3.10i).

The query UNIONs three row-sources:

1. Template legs (edge_kind='template_leg') — actual transactions where template_name matches a declared TransferTemplate. flow_source / flow_target derive from amount_direction so the Sankey reads as debit account → template_name → credit account.

2. Matched chain children (edge_kind='chain_matched') — actual transactions whose transfer_parent_id points at a template firing’s transfer_id AND whose rail_name matches a declared chain child. flow_source = template_name, flow_target = child_rail_name.

3. Orphan chain children (edge_kind='chain_orphan') — SYNTHETIC rows for declared (template, child) chain edges that the runtime didn’t fire. One row per (parent firing, declared child) where no matching child transaction exists. flow_source = template_name, flow_target = child_rail_name || ' (orphan)' so the analyst sees the missing edge visualized as a thin dashed-style ribbon to a distinct node.

Together this gives a complete Sankey per parent firing — every declared chain edge is visible whether it fired or not, with the template legs forming the central trunk.

Width = ABS(amount_money). Synthetic orphan rows carry the parent firing’s amount as a representative ribbon thickness so they’re visually present but recognizable.

Joining against the declared-templates CTE filters out any rogue template_name value in current_transactions that isn’t in the L2 declaration (mirrors tt-instances).

Parameterized on pKey + pValues for the metadata cascade.

Return type:

DataSet

Parameters:

• cfg (Config)

• l2_instance (L2Instance)

recon_gen.apps.l2_flow_tracing.datasets.build_unified_l2_exceptions_dataset(cfg, l2_instance)

UNION ALL across the 6 L2 hygiene checks into one row-per- violation dataset (M.3.10l).

Mirrors L1’s l1_exceptions pattern: one row = one violation; check_type is the discriminator; count is the per-violation row count used for sort + bar stacking. Each branch inlines its own CTEs as a subquery so the outer SELECT can do consistent typing across branches without colliding CTE names.

Each branch is functionally equivalent to one of the 6 retired build_exc_* queries, just projected to the unified shape via CASTs + literal check_type labels.

Return type:

DataSet

Parameters:

• cfg (Config)

• l2_instance (L2Instance)

recon_gen.apps.l2_flow_tracing.datasets.bundle_status_values()

Bounded enum of bundle_status calc-field values. Static dropdown source — see _BUNDLE_STATUS_VALUES for rationale.

Return type:

list[str]

recon_gen.apps.l2_flow_tracing.datasets.chain_completion_status_values()

Bounded enum of chain-instances completion_status values. Static dropdown source on the Chains sheet (X.1.g).

Return type:

list[str]

recon_gen.apps.l2_flow_tracing.datasets.declared_chain_parents(l2_instance)

Sorted list of distinct Chain row parent names. Drives the Chain dropdown’s selectable values on the Chains sheet (M.3.10d).

Return type:

list[str]

Parameters:

l2_instance (L2Instance)

recon_gen.apps.l2_flow_tracing.datasets.declared_metadata_keys(l2_instance)

Sorted list of distinct metadata keys declared across every rail in the L2 instance. Drives both the dropdown-source dataset list and the analysis-level parameter list — single source of truth for “what metadata keys does this L2 expose?”.

Return type:

list[str]

Parameters:

l2_instance (L2Instance)

recon_gen.apps.l2_flow_tracing.datasets.declared_rail_names(l2_instance)

Sorted list of declared Rail names. Drives the Rail dropdown’s selectable values on the L2FT Rails sheet (X.1.b).

Pre-X.1.b the Rail dropdown carried no selectable_values and relied on QS’s auto-distinct fetch (tenK-sample-values-V2 endpoint), which 404s on cold per-CI-run dashboards. Static enumeration from the L2 sidesteps the lazy fetch entirely — rail names are bounded + known at deploy time.

Return type:

list[str]

Parameters:

l2_instance (L2Instance)

recon_gen.apps.l2_flow_tracing.datasets.declared_template_names(l2_instance)

Sorted list of declared TransferTemplate names. Drives the Template dropdown’s selectable values on the Transfer Templates sheet (M.3.10f).

Return type:

list[str]

Parameters:

l2_instance (L2Instance)

recon_gen.apps.l2_flow_tracing.datasets.l2ft_matview_specs(cfg)

Matviews + base tables the L2 Flow Tracing dashboard reads, paired with the date column for App Info’s latest_date KPI.

Includes the base tables (transactions / daily_balances) so the operator can spot stale matviews against fresh ETL loads at a glance. Mirrors l1_matview_specs / inv_matview_specs.

Return type:

list[tuple[str, str | None]]

Parameters:

cfg (Config)

recon_gen.apps.l2_flow_tracing.datasets.metadata_filter_clause(l2_instance, metadata_col, dialect)

WHERE-fragment that filters by the metadata key/value cascade, portable across Postgres + Oracle + SQLite.

The natural form JSON_VALUE(metadata, '$.' || <<$pKey>>) IN (...) works on Postgres but fails on Oracle: Oracle’s JSON_VALUE requires the path argument to be a string literal at parse time (ORA-40597 — JSON path expression syntax error). The runtime concatenation '$.' || pKey is rejected even when the planner could constant-fold it.

Workaround: emit one branch per declared metadata key with the JSON path as a literal, gated by <<$pKey>> matching that key. The L2 instance enumerates the keys at generate time, so the fan-out is bounded and static. Sentinel __ALL__ short-circuits the cascade so a freshly-loaded dashboard renders all rows.

metadata_col is the column the JSON_VALUE reads from (typically metadata or parent_metadata). dialect routes through json_value so SQLite gets json_extract (the JSON1 equivalent) instead of the SQL/JSON-standard form.

Return type:

str

Parameters:

• l2_instance (L2Instance)

• metadata_col (str)

• dialect (Dialect)

recon_gen.apps.l2_flow_tracing.datasets.transaction_status_values()

Bounded enum of transaction status values. Static dropdown source — see _TRANSACTION_STATUS_VALUES for rationale.

Return type:

list[str]

recon_gen.apps.l2_flow_tracing.datasets.tt_completion_status_values()

Bounded enum of transfer-template completion_status values. Static dropdown source on the Transfer Templates sheet (X.1.g).

Return type:

list[str]