recon_gen.apps.executives.datasets
Custom-SQL datasets for the Executives app (L.6.3).
Two datasets, both reading the shared base tables:
exec_transaction_summary— one row per(posted_date, rail_name)aggregated fromtransactions. Drives the Transaction Volume Over Time + Money Moved sheets.exec_account_summary— one row peraccount_idjoined against an activity rollup overtransactions. Drives the Account Coverage sheet.
Aggregation choices. Both queries aggregate per transfer_id
first, then roll up to (date, type). Aggregating at the leg grain
would double-count multi-leg transfers — e.g. a $100 ACH transfer
posts as a +$100 + a -$100 leg, both with amount=100; raw
SUM(amount) gives $200 of “money moved” when only $100 actually
moved. The per-transfer pre-aggregation collapses each transfer to
one row (MAX(amount) since both legs share the magnitude;
SUM(signed_amount) for the net flow which is 0 for balanced
multi-leg, non-zero for single-leg or unbalanced).
Status filter. Both datasets filter to status = 'Posted' —
the canonical settled-leg status across the v6 schema (matching the
L1 invariant matviews + Investigation datasets). Pending / Failed
legs are excluded; including them would inflate the executive trends
with operational noise.
Module Attributes
BH.8 follow-up (2026-05-26) — per-leg / all-status counter dataset bound to a sibling KPI on Transaction Volume to disclose the documented gap vs the deduped-Posted-only "Total Transactions" KPI. |
Functions
Y.2.h — same shape as |
|
One row per account that has ever appeared in |
|
|
Return every dataset used by the Executives app. |
Per-(posted_date) rollup of exec-transaction-summary. |
|
BH.8 follow-up (2026-05-26) — single-row dataset returning the per-leg / all-status row count of <prefix>_transactions. |
|
Per-(date, rail_name) aggregates: transfer count, gross + net dollars. |
|
|
Tables Executives reads, paired with their date columns for App Info's |
- recon_gen.apps.executives.datasets.DS_EXEC_TRANSACTION_LEGS = 'exec-transaction-legs-ds'
BH.8 follow-up (2026-05-26) — per-leg / all-status counter dataset bound to a sibling KPI on Transaction Volume to disclose the documented gap vs the deduped-Posted-only “Total Transactions” KPI. Cold-read agents read the difference between Total Transactions and App Info’s matview row_count as a bug; surfacing both numbers side-by-side in the headline tile makes the predicate-mismatch visible.
- recon_gen.apps.executives.datasets.build_account_summary_active_dataset(cfg)[source]
Y.2.h — same shape as
exec-account-summary-dsbut narrowed to accounts with at least one Posted transaction in the date window (WHERE COALESCE(act.activity_count, 0) > 0baked into the outer SELECT).Replaces the visual-pinned
NumericRangeFilter(activity_count >= 1scoped to the active-only KPI + bar) — that filter narrowed correctly in QuickSight but App2’s renderer doesn’t apply visual-scoped filters yet (X.2.g.4 territory). Baking the predicate into a second dataset and re-pointing the visuals fixes both renderers without growing App2’s filter coverage.Phase BM — date narrowing pushes down via
<<$pExecDate*>>overt.posting(one SQL form across QS + App2; the day-edge quirk dissolves).
- recon_gen.apps.executives.datasets.build_account_summary_dataset(cfg)[source]
One row per account that has ever appeared in
daily_balances.Y.2.h — pure date-independent snapshot. Used by visuals whose semantic IS “every account that exists” (Total Open Accounts KPI, Open Accounts by Type bar, Account Detail table). The activity rollup columns (
last_activity_date/activity_count) reflect ALL-TIME activity, NOT the date-window — that’s the difference vs theexec-account-summary-active-dsvariant.Without
:date_from, the date-sensitive count-KPI test heuristic correctly skips Total Open Accounts (its expected behavior IS date-independent). Active KPIs use the_activevariant which keeps the date filter + bakesWHERE activity_count > 0.N.4.a: reads from
<prefix>_transactions+<prefix>_daily_balances. v6 column rename: posted_at → posting; account_type → account_role (output column kept asaccount_typeso dashboard-side consumers don’t need to follow the rename — only the SELECT does).
- recon_gen.apps.executives.datasets.build_all_datasets(cfg)[source]
Return every dataset used by the Executives app.
- recon_gen.apps.executives.datasets.build_transaction_daily_dataset(cfg)[source]
Per-(posted_date) rollup of exec-transaction-summary.
AO.5 fix: the “Average Daily Volume” KPI in the Volume sheet used to consume exec-transaction-summary (one row per (date, rail)) and ask QS for AVG(transfer_count) — that’s the average across (date × rail) rows, which is days-with-activity × distinct-rails- on-that-day in the denominator. With Sasquatch’s ~30 declared rails, the KPI read ≈30× too small vs the analyst’s total / active-days expectation (cold-read MAJOR 2/4, reported as “~67× off”). This dataset collapses the per-(date, rail) breakdown to a single row per active day so AVG(daily_transfer_ count) gets the right denominator structurally — no calc-field expression DSL gymnastics.
Shares the upstream per_transfer shape with build_transaction_summary_dataset (so multi-leg transfers are counted once, not once per leg).
- recon_gen.apps.executives.datasets.build_transaction_legs_dataset(cfg)[source]
BH.8 follow-up (2026-05-26) — single-row dataset returning the per-leg / all-status row count of <prefix>_transactions. Used by the Transaction Volume sheet’s sibling “Transfer Legs (all statuses)” KPI to surface the documented gap vs the deduped-Posted-only “Total Transactions” KPI. Cold-read agents (v11.22.1) read the Total-Transactions-vs-App-Info-row-count delta as a defect; putting both numbers in the headline row makes the predicate mismatch visible instead of mysterious.
Two reasons NOT to add this measure on the existing transaction- summary dataset: 1. The summary dataset filters status = ‘Posted’ + GROUPs by
(transfer_id, rail_name). Asking it for an all-leg / all-status count means dropping those filters → different dataset.
The point of THIS KPI is the App Info parity. App Info reads <prefix>_transactions raw; this dataset matches that exactly.
- recon_gen.apps.executives.datasets.build_transaction_summary_dataset(cfg)[source]
Per-(date, rail_name) aggregates: transfer count, gross + net dollars.
Aggregates per
transfer_idfirst so multi-leg transfers are counted once, not once per leg.gross_amountis the per-transfer handle;net_amountis the per-transfer net flow (0 for balanced multi-leg, non-zero for single-leg or unbalanced transfers).N.4.a: reads from
<prefix>_transactions(per-instance prefixed base table). v6 column rename: posted_at → posting; amount →ABS(amount_money)(the per-leg signed Decimal — magnitude is abs); signed_amount → amount_money (already signed in v6).BQ.6 (cold-read F7) — top-N + Other rollup on
rail_name. The executive dashboards display this dataset stacked byrail_name(Daily Stacked) or grouped byrail_name(Period Total). With 60-80 distinct rails in sasquatch_pr, the legend takes ~30% of the canvas and the long tail of small rails is illegible. Rolling everything past the top-20 by gross volume into"Other"caps the legend at 21 entries and keeps the long-tail aggregate visible instead of invisibly dispersed. Rank is by GROSS (the most operator-meaningful sort for executive scanning); counts + net-amount aggregate cleanly under the same partition.
- recon_gen.apps.executives.datasets.exec_matview_specs(cfg)[source]
Tables Executives reads, paired with their date columns for App Info’s
latest_dateKPI. No app-specific matviews — just the base tables (which is what the Executives sheets aggregate over).- Return type:
list[tuple[str,str|None]]- Parameters:
cfg (Config)