recon_gen.apps.l1_dashboard.datasets

QuickSight DataSet builders for the L1 Dashboard app.

Each builder wraps one M.1a.7 L1 invariant view. The SQL is intentionally trivial (SELECT * FROM <prefix>_<view>) — the views already do the filtering, computation, and shape work. Datasets here are thin façades that surface columns to QuickSight visuals via the dataset contract.

The visual_identifier convention is l1-<viewname>-ds so every dataset’s logical name traces back to the underlying L1 invariant.

Substep landmarks:

M.2a.3 — drift + ledger_drift datasets M.2a.4 — overdraft dataset M.2a.5 — limit_breach dataset M.2a.6 — L1 exceptions UNION dataset (this commit)

Functions

build_all_l1_dashboard_datasets(cfg, l2_instance)	Return every dataset the L1 dashboard's sheets reference.
build_daily_statement_summary_dataset(cfg, ...)	Wrap the <prefix>_daily_statement_summary matview from M.1a.9.
build_daily_statement_transactions_dataset(...)	Wrap the per-leg ledger feed for Daily Statement detail rows.
build_drift_dataset(cfg, l2_instance)	Wrap the leaf-account drift view from M.1a.7.
build_drift_timeline_dataset(cfg, l2_instance)	Pre-aggregate leaf-account drift by (business_day_end, account_role).
build_l1_accounts_dataset(cfg, l2_instance)	Y.2.g companion — distinct (account_id, account_role) over the universe of accounts.
build_l1_ds_accounts_dataset(cfg, l2_instance)	BO.1 — Daily-Statement-specific account picker source.
build_l1_ds_roles_dataset(cfg, l2_instance)	AA.B.1 — distinct account_role over the daily-balances universe.
build_l1_exceptions_dataset(cfg, l2_instance)	Wrap the <prefix>_l1_exceptions matview from M.1a.9.
build_l1_tx_facets_dataset(cfg, l2_instance)	Y.2.g companion — distinct (status, origin) over the current ledger.
build_l1_tx_ids_dataset(cfg, l2_instance)	Y.2.g companion — distinct transfer_id over the current ledger.
build_ledger_drift_dataset(cfg, l2_instance)	Wrap the parent-account drift view from M.1a.7.
build_ledger_drift_timeline_dataset(cfg, ...)	Pre-aggregate ledger drift by (business_day_end, account_role).
build_limit_breach_dataset(cfg, l2_instance)	Wrap the per-(account, day, type) limit-breach view from M.1a.7.
build_overdraft_dataset(cfg, l2_instance)	Wrap the internal-account overdraft view from M.1a.7.
build_stuck_pending_dataset(cfg, l2_instance)	Wrap the M.2b.8 <prefix>_stuck_pending matview.
build_stuck_unbundled_dataset(cfg, l2_instance)	Wrap the M.2b.9 <prefix>_stuck_unbundled matview.
build_supersession_daily_balances_dataset(...)	Pull rows from <prefix>_daily_balances whose logical key (account_id, business_day_start) has multiple entry values.
build_supersession_transactions_dataset(cfg, ...)	Pull rows from <prefix>_transactions whose logical id has multiple entry values — the audit trail for superseded postings.
build_transactions_dataset(cfg, l2_instance)	Wrap <prefix>_current_transactions matview for the Transactions sheet's raw posting ledger.
l1_account_role_values(l2_instance)	Sorted distinct account roles declared by singleton Accounts + AccountTemplates — the universe the L1 matviews' account_role column draws from.
l1_check_type_values()	The check_type discriminator values the L1 Exceptions matview projects.
l1_matview_specs(cfg)	The L2-prefixed matviews + base tables the L1 dashboard reads, paired with the date column the App Info latest_date KPI takes its MAX from.
l1_rail_universe_values(l2_instance)	Sorted distinct rail_name values declared across the L2's rails + limit schedules — the universe the L1 matviews' rail_name column draws from (Z.B 2026-05-15 subsumed transfer_type into the rail).
l1_rail_values(l2_instance)	Sorted distinct declared Rail names — the universe the L1 matviews' rail_name column draws from.
l1_supersede_reason_values()	The v1 SupersedeReason vocabulary.

recon_gen.apps.l1_dashboard.datasets.build_all_l1_dashboard_datasets(cfg, l2_instance)

Return every dataset the L1 dashboard’s sheets reference.

build_l1_dashboard_app calls this and registers each result on the App tree. Per Z.C, the cfg arrives fully populated (deployment_name + db_table_prefix are required cfg fields), so dataset IDs carry the deployment prefix via cfg.prefixed(...) directly — no auto- stamping dance.

Return type:

list[DataSet]

Parameters:

• cfg (Config)

• l2_instance (L2Instance)

recon_gen.apps.l1_dashboard.datasets.build_daily_statement_summary_dataset(cfg, l2_instance)

Wrap the <prefix>_daily_statement_summary matview from M.1a.9.

M.1a.9 promoted the LAG window + LEFT JOIN + GROUP BY CTE from inline CustomSql to a per-instance MATERIALIZED VIEW so each KPI on the Daily Statement sheet reads a precomputed table instead of re-evaluating the multi-CTE per visual (5 KPIs × CTE = 5 re-evaluations otherwise). Refresh contract via refresh_matviews_sql() after every batch insert.

Y.2.g — the per-account narrow pushes down via account_id = <<$pL1DsAccount>> instead of an analysis-level CategoryFilter; the sheet’s account dropdown reads its options from the DS_L1_ACCOUNTS companion (not this parameterized dataset).

Return type:

DataSet

Parameters:

• cfg (Config)

• l2_instance (L2Instance)

recon_gen.apps.l1_dashboard.datasets.build_daily_statement_transactions_dataset(cfg, l2_instance)

Wrap the per-leg ledger feed for Daily Statement detail rows.

Return type:

DataSet

Parameters:

• cfg (Config)

• l2_instance (L2Instance)

recon_gen.apps.l1_dashboard.datasets.build_drift_dataset(cfg, l2_instance)

Wrap the leaf-account drift view from M.1a.7.

Rows in this dataset are leaf-account drift violations only — the M.1a.7 view pre-filters to stored_balance != computed_balance. No drift_status=’in_balance’ rows; if the dashboard wants to show “all accounts including no-drift”, it queries the underlying Current* view directly, not this dataset.

Y.2.g — the Account / Account-Role dropdowns push down here via account_id (data-value, sentinel-OR) + account_role (enum, IN (...)). Same dataset-param names on build_ledger_drift_dataset so one ALL_DATASETS dropdown narrows both.

Phase BM — date narrowing pushes down via the unified <<$pL1DateStart>> / <<$pL1DateEnd>> dataset-SQL parameters (replaces the pre-BM dual-SQL {date_filter} template + analysis-level TimeRangeFilter FG). One SQL form across QS + App2; the day-edge quirk dissolves.

AO.1.impl — money columns (stored_balance / computed_balance / drift) project as BIGINT cents from the matview; wrap each with cents_to_dollars_sql at this read boundary so the dashboard receives dollars. SELECT * is replaced with the explicit column list because the wrap needs per-column control.

Return type:

DataSet

Parameters:

• cfg (Config)

• l2_instance (L2Instance)

recon_gen.apps.l1_dashboard.datasets.build_drift_timeline_dataset(cfg, l2_instance)

Pre-aggregate leaf-account drift by (business_day_end, account_role).

One row per (day, role) carrying SUM(ABS(drift)). Source matview is already small (only violations) and indexed on account_role, so the GROUP BY runs at indexed-scan latency. Backs the leaf-drift LineChart.

Y.2.g — the Drift Timelines sheet’s Account-Role dropdown narrows BOTH timeline datasets via the same pL1DriftTlRole dataset param; the predicate sits before the GROUP BY.

Phase BM — date pushdown via <<$pL1Date*>> over business_day_end; the date-range AND-clause lands BEFORE the GROUP BY so the narrowing is part of the WHERE.

Return type:

DataSet

Parameters:

• cfg (Config)

• l2_instance (L2Instance)

recon_gen.apps.l1_dashboard.datasets.build_l1_accounts_dataset(cfg, l2_instance)

Y.2.g companion — distinct (account_id, account_role) over the universe of accounts. Feeds every L1 sheet’s Account dropdown via LinkedValues (the Daily Statement sheet’s account dropdown re-points here too).

Reads the UNION of <prefix>_current_daily_balances and <prefix>_current_transactions so every account that’s reachable from ANY L1 sheet’s matview appears in the dropdown. The earlier daily-balances-only source missed accounts that only show up in Pending-state transactions (StuckPendingGenerator emits a Pending transaction with NO balance row — see spine/stuck_pending.py:10). Spine-planted tmpl-cust-* accounts only have Pending rows, so the old dropdown excluded them — picking them was impossible even though the Pending Aging sheet’s table surfaced the matching matview rows. Same DISTINCT-inside-UNION shape stays cheap as the matview grows (DISTINCT on the (small) account column, not on full rows).

BR.x — the dead <<$pL1DsRole>> WHERE clause + matching dataset_parameters declaration removed. Only Daily Statement has a Role control, and Daily Statement’s Account dropdown sources from DS_L1_DS_ACCOUNTS (the narrower balance-only companion), not this wider dataset. No analysis param bridged into l1-accounts-ds.pL1DsRole — QS flagged the dataset with “You have an unmapped dataset parameter.” on analysis-editor open and the cascade tooltip on the Daily Statement Account dropdown read “A calculated field contains invalid syntax.” (see docs/reference/quicksight-quirks.md — unmapped DatasetParameter entry). Dropping the dead substitution dissolves both errors.

Return type:

DataSet

Parameters:

• cfg (Config)

• l2_instance (L2Instance)

recon_gen.apps.l1_dashboard.datasets.build_l1_ds_accounts_dataset(cfg, l2_instance)

BO.1 — Daily-Statement-specific account picker source.

Same shape as build_l1_accounts_dataset but sourced ONLY from <prefix>_current_daily_balances so every option in the picker is guaranteed to have a balance row. See DS_L1_DS_ACCOUNTS docstring above for the rationale (v11.23.0 cold-read F1: pre-BM the picker was a no-op so the FK gap was invisible; BM made the filter strict, exposing the mismatch between the BL.3-widened universe and Daily Statement’s per-(account, day) reconciliation contract).

Same contract + same pL1DsRole cascade param as DS_L1_ACCOUNTS; the Daily Statement Role dropdown bridges here so picking a role narrows the account dropdown without breaking the BL.3-widened source for the 7 other L1 sheets.

Return type:

DataSet

Parameters:

• cfg (Config)

• l2_instance (L2Instance)

recon_gen.apps.l1_dashboard.datasets.build_l1_ds_roles_dataset(cfg, l2_instance)

AA.B.1 — distinct account_role over the daily-balances universe. Feeds the Daily Statement Role dropdown (the new cascade source) via LinkedValues. Unparameterized — every role the deployed institution actually uses shows up.

Same shape as build_l1_accounts_dataset: cheap DISTINCT over current_daily_balances; the matview’s per-account-day granularity guarantees every role with at least one tracked account appears.

Return type:

DataSet

Parameters:

• cfg (Config)

• l2_instance (L2Instance)

recon_gen.apps.l1_dashboard.datasets.build_l1_exceptions_dataset(cfg, l2_instance)

Wrap the <prefix>_l1_exceptions matview from M.1a.9.

M.1a.9 promoted the UNION ALL from inline CustomSql to a per-instance MATERIALIZED VIEW so each visual on the L1 Exceptions sheet reads a precomputed table instead of re-running the 5-branch UNION. Refresh contract: integrators MUST call refresh_matviews_sql() after every batch insert into the base tables.

Y.2.g + AA.A.3 — three dropdowns push down, all SINGLE_VALUED post-AA.A.3 sentinel-guard form: check_type (enum), account_id (data-value), and rail_name (enum). rail_name is NULL for every branch except limit / stuck rows, so the predicate keeps the NULL-type rows on load (and while narrowing) — matching the FILTER_ALL_VALUES behavior it replaces.

Phase BM — date pushdown via <<$pL1Date*>> over business_day.

AO.4 — the matview now splits magnitude into two columns by source-branch unit: magnitude_amount (BIGINT cents — money branches) and magnitude_count (INT — transfer-keyed cardinality branches). Exactly one is non-NULL per row. Wrap magnitude_amount cents → dollars; pass magnitude_count through bare.

Return type:

DataSet

Parameters:

• cfg (Config)

• l2_instance (L2Instance)

recon_gen.apps.l1_dashboard.datasets.build_l1_tx_facets_dataset(cfg, l2_instance)

Y.2.g companion — distinct (status, origin) over the current ledger. Feeds the Transactions sheet’s Status + Origin dropdowns via LinkedValues. Both columns are open-set in the L1 schema, so a companion (cheap, well-formed query) replaces the StaticValues path used for the bounded enum columns.

Return type:

DataSet

Parameters:

• cfg (Config)

• l2_instance (L2Instance)

recon_gen.apps.l1_dashboard.datasets.build_l1_tx_ids_dataset(cfg, l2_instance)

Y.2.g companion — distinct transfer_id over the current ledger. Feeds the Transactions sheet’s Transfer dropdown via LinkedValues.

Return type:

DataSet

Parameters:

• cfg (Config)

• l2_instance (L2Instance)

recon_gen.apps.l1_dashboard.datasets.build_ledger_drift_dataset(cfg, l2_instance)

Wrap the parent-account drift view from M.1a.7.

Same shape as build_drift_dataset minus account_parent_role (parent accounts ARE the parents — no parent_role column on this view). Carries the same Y.2.g dataset-param names as the leaf-drift dataset so the Drift sheet’s ALL_DATASETS dropdowns narrow both.

Phase BM — date pushdown via <<$pL1Date*>> matches build_drift_dataset (same shared universal-range params).

AO.1.impl — cents → dollars wrap mirrors build_drift_dataset.

Return type:

DataSet

Parameters:

• cfg (Config)

• l2_instance (L2Instance)

recon_gen.apps.l1_dashboard.datasets.build_ledger_drift_timeline_dataset(cfg, l2_instance)

Pre-aggregate ledger drift by (business_day_end, account_role).

Same shape as the leaf-drift timeline, sourced from the parent-account drift matview. Backs the ledger-drift LineChart. Carries the same pL1DriftTlRole Y.2.g param so the ALL_DATASETS dropdown narrows both timelines.

Phase BM — date pushdown via <<$pL1Date*>> matches build_drift_timeline_dataset.

Return type:

DataSet

Parameters:

• cfg (Config)

• l2_instance (L2Instance)

recon_gen.apps.l1_dashboard.datasets.build_limit_breach_dataset(cfg, l2_instance)

Wrap the per-(account, day, type) limit-breach view from M.1a.7.

Each row is one cell where the cumulative outbound debit exceeded the L2-configured cap. Caps are inlined in the view at emit-time from the L2 LimitSchedules — no JSON path lookups in the dataset SQL.

Y.2.g — Account dropdown pushes down via account_id (data-value, sentinel-OR); Transfer Type dropdown via rail_name IN (...).

Phase BM — date pushdown via <<$pL1Date*>> over business_day.

AO.1.impl — both outbound_total (SUM(ABS(amount_money)) in matview) and cap (multiplied ×100 in the matview per foundation so cents-vs-cents comparison holds) project as BIGINT cents; wrap each → dollars at the dataset boundary.

Return type:

DataSet

Parameters:

• cfg (Config)

• l2_instance (L2Instance)

recon_gen.apps.l1_dashboard.datasets.build_overdraft_dataset(cfg, l2_instance)

Wrap the internal-account overdraft view from M.1a.7.

Rows are accounts with negative stored balance — the L1 invariant is “no internal account holds negative money.” External accounts are excluded by the view (filtered to account_scope = 'internal').

Y.2.g — Account dropdown pushes down via account_id (data-value, sentinel-OR); Account-Role dropdown via account_role IN (...).

Phase BM — date pushdown via <<$pL1Date*>> matches build_drift_dataset.

AO.1.impl — wrap stored_balance (BIGINT cents) → dollars.

Return type:

DataSet

Parameters:

• cfg (Config)

• l2_instance (L2Instance)

recon_gen.apps.l1_dashboard.datasets.build_stuck_pending_dataset(cfg, l2_instance)

Wrap the M.2b.8 <prefix>_stuck_pending matview.

Pending transactions whose age exceeds the per-rail max_pending_age cap. Backs the M.2b.10 Pending Aging sheet — aging buckets come from a calc field on the dataset, so the SQL stays a thin SELECT * passthrough plus the Y.2.g pushdown WHERE (account_id data-value + rail_name / rail_name enums).

Return type:

DataSet

Parameters:

• cfg (Config)

• l2_instance (L2Instance)

recon_gen.apps.l1_dashboard.datasets.build_stuck_unbundled_dataset(cfg, l2_instance)

Wrap the M.2b.9 <prefix>_stuck_unbundled matview.

Posted transactions where bundle_id IS NULL and age exceeds the per-rail max_unbundled_age cap. Backs the M.2b.11 Unbundled Aging sheet. Same Y.2.g pushdown vectors as the Pending Aging dataset.

Return type:

DataSet

Parameters:

• cfg (Config)

• l2_instance (L2Instance)

recon_gen.apps.l1_dashboard.datasets.build_supersession_daily_balances_dataset(cfg, l2_instance)

Pull rows from <prefix>_daily_balances whose logical key (account_id, business_day_start) has multiple entry values.

Same window-function pattern as the transactions audit dataset — COUNT(*) OVER (PARTITION BY account_id, business_day_start) + outer WHERE > 1 filter. Sort handled by the dashboard.

Return type:

DataSet

Parameters:

• cfg (Config)

• l2_instance (L2Instance)

recon_gen.apps.l1_dashboard.datasets.build_supersession_transactions_dataset(cfg, l2_instance)

Pull rows from <prefix>_transactions whose logical id has multiple entry values — the audit trail for superseded postings.

Reads from the BASE table (not <prefix>_current_transactions) because Current* hides superseded entries by design. Uses a window function (COUNT(*) OVER (PARTITION BY id) > 1) instead of an id IN (… GROUP BY id HAVING COUNT(*) > 1) subquery — the window form survives QuickSight’s distinct-values dropdown query rewriting (which wraps the dataset SQL in SELECT DISTINCT col FROM (…) for filter dropdowns; QS chokes on the IN-subquery + ORDER BY combo). Sort handled by the dashboard, not the dataset.

Y.2.g + AA.A.3 — the Supersedes-Reason dropdown pushes down via ((sentinel = <<$p>> OR supersedes = <<$p>>) OR supersedes IS NULL). AA.A.3 flipped it from MULTI to SINGLE per the drill-to-one default (audit row pL1SupersedeReason); the OR supersedes IS NULL keeps the entry-1 rows of every audit trail visible on load and while narrowing (you always see the trail, the dropdown narrows which cause class you’re auditing).

Return type:

DataSet

Parameters:

• cfg (Config)

• l2_instance (L2Instance)

recon_gen.apps.l1_dashboard.datasets.build_transactions_dataset(cfg, l2_instance)

Wrap <prefix>_current_transactions matview for the Transactions sheet’s raw posting ledger.

Reads from the matview directly (M.1a.9 made it a MATERIALIZED VIEW + indexed) so per-account / per-transfer / per-status / per-origin / per-rail_name filter dropdowns hit indexed lookups. Projects only the analyst-visible columns; internal columns (entry, account_scope, supersedes, bundle_id, template_name, metadata) stay in the matview but aren’t surfaced.

Y.2.g — all five dropdowns push into this SQL: account_id / transfer_id / status / origin via the sentinel-OR data-value guard, rail_name via IN (...).

Phase BM — date pushdown via <<$pL1Date*>> over posting (TIMESTAMP); the helper’s upper-bound expands to “+1 day” so same-day non-midnight rows on the end day are included.

Return type:

DataSet

Parameters:

• cfg (Config)

• l2_instance (L2Instance)

recon_gen.apps.l1_dashboard.datasets.l1_account_role_values(l2_instance)

Sorted distinct account roles declared by singleton Accounts + AccountTemplates — the universe the L1 matviews’ account_role column draws from. Drives the Account-Role dropdowns on the Drift / Drift Timelines / Overdraft sheets.

Return type:

list[str]

Parameters:

l2_instance (L2Instance)

recon_gen.apps.l1_dashboard.datasets.l1_check_type_values()

The check_type discriminator values the L1 Exceptions matview projects. Static dropdown source — see _L1_CHECK_TYPE_VALUES.

Return type:

list[str]

recon_gen.apps.l1_dashboard.datasets.l1_matview_specs(cfg)

The L2-prefixed matviews + base tables the L1 dashboard reads, paired with the date column the App Info latest_date KPI takes its MAX from.

Includes both base tables (so the operator can spot ETL freshness against the matviews’ staleness) and every L1 invariant matview. Date columns chosen to match what each table tracks “as-of”:

• transactions / current_transactions / stuck_* → posting

• daily_balances / current_daily_balances / daily_statement_summary → business_day_start

• drift / ledger_drift / overdraft → business_day_end

• limit_breach / l1_exceptions → business_day

Z.C — was l1_matview_specs(l2_instance) reading l2_instance.instance; now reads cfg.db_table_prefix.

Return type:

list[tuple[str, str | None]]

Parameters:

cfg (Config)

recon_gen.apps.l1_dashboard.datasets.l1_rail_universe_values(l2_instance)

Sorted distinct rail_name values declared across the L2’s rails + limit schedules — the universe the L1 matviews’ rail_name column draws from (Z.B 2026-05-15 subsumed transfer_type into the rail). Drives the StaticValues default + dropdown options on every L1 sheet with a rail-keyed dropdown. Broader source set than l1_rail_values() because limit_breach can surface rows for a rail declared only via a LimitSchedule.

Return type:

list[str]

Parameters:

l2_instance (L2Instance)

recon_gen.apps.l1_dashboard.datasets.l1_rail_values(l2_instance)

Sorted distinct declared Rail names — the universe the L1 matviews’ rail_name column draws from. Drives the Rail dropdowns on the Pending / Unbundled Aging sheets.

Return type:

list[str]

Parameters:

l2_instance (L2Instance)

recon_gen.apps.l1_dashboard.datasets.l1_supersede_reason_values()

The v1 SupersedeReason vocabulary. Static dropdown source on the Supersession Audit sheet — see _L1_SUPERSEDE_REASON_VALUES.

Return type:

list[str]