recon_gen.apps.investigation.datasets

Custom-SQL datasets for the Investigation app.

K.4.3 ships the recipient-fanout dataset. K.4.4 adds the rolling-window anomaly dataset (read from the inv_pair_rolling_anomalies matview). K.4.5 adds the money-trail dataset (read from the inv_money_trail_edges matview, which precomputes the WITH RECURSIVE walk over parent_transfer_id). K.4.8 wraps the same matview as a second dataset so the account-centric filters (anchor account, min amount) don’t cross-contaminate K.4.5’s chain-rooted filters.

All datasets read the shared transactions + daily_balances base tables — Investigation has no app-specific schema. The K.4.4 + K.4.5 matviews are computed at refresh time, not dataset time, because the rolling-window z-score and the recursive chain walk were both too heavy for QuickSight Direct Query at realistic transaction volumes.

Functions

build_account_network_accounts_dataset(cfg)	Narrow accounts dataset feeding the K.4.8 anchor dropdown only.
build_account_network_dataset(cfg)	Per-edge account-network rows — Y.2.b SQL pushdown.
build_account_network_inbound_dataset(cfg)	BO.2 directional dataset — rows where target_display = anchor.
build_account_network_outbound_dataset(cfg)	BO.2 directional dataset — rows where source_display = anchor.
build_all_datasets(cfg, l2_instance)	Return every dataset Investigation's sheets reference.
build_money_trail_dataset(cfg)	Per-edge money trail rows — Y.2.a SQL pushdown.
build_money_trail_roots_dataset(cfg)	Companion to build_money_trail_dataset — distinct chain roots.
build_recipient_fanout_dataset(cfg)	Recipient × sender × transfer rows, one per (recipient leg, sender leg).
build_volume_anomalies_dataset(cfg)	Pair-grain rolling-window anomalies — σ-filtered at the DB.
build_volume_anomalies_distribution_dataset(cfg)	Same matview as build_volume_anomalies_dataset — no σ filter.
inv_matview_specs(cfg)	The deployment-prefixed Inv matviews + base tables the dashboard reads, paired with the date column for App Info's latest_date KPI.

recon_gen.apps.investigation.datasets.build_account_network_accounts_dataset(cfg)

Narrow accounts dataset feeding the K.4.8 anchor dropdown only.

Single column source_display distinct’d over the matview so QuickSight’s dropdown can SELECT DISTINCT source_display FROM ... in O(distinct accounts) work instead of O(matview rows). Originally the dropdown pointed at the full Account Network dataset; that dataset wraps the matview with a per-row concat that the dropdown’s DISTINCT couldn’t push past, so the dropdown started timing out as the matview grew. This dataset puts the DISTINCT inside the SELECT so PG dedupes the (id, name) pairs before concatenating.

Reuses inv_money_trail_edges — no new matview needed.

Return type:

DataSet

Parameters:

cfg (Config)

recon_gen.apps.investigation.datasets.build_account_network_dataset(cfg)

Per-edge account-network rows — Y.2.b SQL pushdown.

Same matview as money trail (inv_money_trail_edges) but with two analysis-level parameters bridging into dataset-level parameters substituted by QS into the dataset SQL at query time:

• pInvANetworkAnchor → broad anchor narrow: WHERE (source_display = <<$pInvANetworkAnchor>> OR target_display = <<$pInvANetworkAnchor>>). Pre-narrows the wire to only edges that touch the anchor account in either direction. Initial paint substitutes a sentinel that matches no row; the dropdown’s first commit fires the bridge.

• pInvANetworkMinAmount → AND hop_amount >= <<$...>>. Default 0 = keep all on first paint.

BO.2 (2026-05-28) — this bidirectional dataset now feeds the Touching-Edges Table only; each Sankey reads its directional sibling (build_account_network_inbound_dataset / ..._outbound_dataset). See _account_network_sql for the reasoning behind the split.

The K.4.5 chain-root filters that pre-Y.2 lived on a separate dataset registration (Money Trail’s chain-root context) are now irrelevant here — Account Network’s narrow is anchor-driven, Money Trail’s narrow is chain-root-driven; the two datasets keep their own pushdowns.

The anchor dropdown reads from DS_INV_ANETWORK_ACCOUNTS (K.4.8k) — already an unfiltered companion shape. No new companion dataset needed for Y.2.b.

Return type:

DataSet

Parameters:

cfg (Config)

recon_gen.apps.investigation.datasets.build_account_network_inbound_dataset(cfg)

BO.2 directional dataset — rows where target_display = anchor.

Feeds the Inbound Sankey only. Same anchor + min-amount pushdown bridge as the bidirectional dataset; same contract. See _account_network_sql for why the directional split exists.

Return type:

DataSet

Parameters:

cfg (Config)

recon_gen.apps.investigation.datasets.build_account_network_outbound_dataset(cfg)

BO.2 directional dataset — rows where source_display = anchor.

Feeds the Outbound Sankey only. Same anchor + min-amount pushdown bridge as the bidirectional dataset; same contract. See _account_network_sql for why the directional split exists.

Return type:

DataSet

Parameters:

cfg (Config)

recon_gen.apps.investigation.datasets.build_all_datasets(cfg, l2_instance)

Return every dataset Investigation’s sheets reference.

Z.C: l2_instance is no longer load-bearing for the prefix — that comes from cfg.db_table_prefix now — but the parameter stays for signature parity with the L1 / L2FT / Exec build_all_datasets callers.

Return type:

list[DataSet]

Parameters:

• cfg (Config)

• l2_instance (L2Instance)

recon_gen.apps.investigation.datasets.build_money_trail_dataset(cfg)

Per-edge money trail rows — Y.2.a SQL pushdown.

Three analysis-level parameters bridge into dataset-level parameters substituted by QS into the dataset SQL at query time:

• pInvMoneyTrailRoot → WHERE root_transfer_id = <<$...>> narrows to a single chain. Initial paint substitutes a sentinel that matches nothing; the dropdown’s first commit fires the bridge and the visuals populate.

• pInvMoneyTrailMaxHops → AND depth <= <<$...>> caps chain depth. Default 5 substitutes literally on first paint.

• pInvMoneyTrailMinAmount → AND hop_amount >= <<$...>> drops noise edges. Default 0 = keep all on first paint.

The chain-root dropdown reads from the build_money_trail_roots_dataset companion (no parameters) so the dropdown’s DISTINCT-roots query doesn’t inherit the WHERE clause we just baked in here. Pattern: Y.1.b.companion.

App2 reads the same SQL after the translate_qs_dataset_params preprocessor in _sql_executor rewrites <<$pName>> → :param_pName bind variables.

Return type:

DataSet

Parameters:

cfg (Config)

recon_gen.apps.investigation.datasets.build_money_trail_roots_dataset(cfg)

Companion to build_money_trail_dataset — distinct chain roots.

Y.2.a — the parameter-bearing money-trail dataset filters rows by root_transfer_id = <<$pInvMoneyTrailRoot>>. The chain-root dropdown can’t read its options from that dataset (its SELECT DISTINCT root_transfer_id would inherit the WHERE clause). This companion wraps the same matview without the parameter so the dropdown’s option fetch sees every chain root. Same pattern as build_volume_anomalies_distribution_dataset (Y.1.b.companion) and build_account_network_accounts_dataset (K.4.8k).

BQ.7 (cold-read F8) — order by chain magnitude DESC so QS’s SelectAll=HIDDEN auto-pick-first lands on the LARGEST chain. Pre-BQ.7 the SELECT DISTINCT returned rows in matview-physical order; the first row was the smallest / oldest chain and the Money Trail Sankey paints empty for ~30s while the operator realizes they have to pick something interesting. Now the default pick IS the operator’s most likely target (“show me the biggest money movement first”).

Return type:

DataSet

Parameters:

cfg (Config)

recon_gen.apps.investigation.datasets.build_recipient_fanout_dataset(cfg)

Recipient × sender × transfer rows, one per (recipient leg, sender leg).

Filters to leaf-internal recipients (account_scope='internal' AND account_parent_role IS NOT NULL — the v6 equivalent of the v5 account_type IN ('dda', 'merchant_dda') filter, mirroring the Inv matview convention) so administrative sweeps into control accounts don’t dominate the fanout ranking. v5 column names kept in the output projection (_account_type) so downstream consumers aren’t sensitive to the source-side rename.

Y.3.a — distinct_senders count computed at the DB and joined back per recipient (PG doesn’t support COUNT(DISTINCT) OVER, Oracle/SQLite same — see Y.3.a hotfix); the analyst-facing threshold pushes down via WHERE distinct_senders >= <<$pInvFanoutThreshold>>. Replaces the pre-Y.3 analysis-level CalcField + NumericRangeFilter pair (which QS handled but App2 didn’t apply). Both renderers now see one shape.

Return type:

DataSet

Parameters:

cfg (Config)

recon_gen.apps.investigation.datasets.build_volume_anomalies_dataset(cfg)

Pair-grain rolling-window anomalies — σ-filtered at the DB.

Y.1.b — the σ-threshold parameter is pushed into the dataset SQL via <<$pInvAnomaliesSigma>> (QS substitutes the literal at query time) so QS Direct Query hits the database with the WHERE clause already applied — the matview’s full row count never crosses the wire. The companion dataset build_volume_anomalies_distribution_dataset reads the same matview WITHOUT the parameter so the distribution chart stays unfiltered (its UX role is to show the full population shape against which the analyst reads the threshold).

App2 reads the same SQL after a one-line preprocessor in _sql_executor translates <<$pInvAnomaliesSigma>> → :param_pInvAnomaliesSigma (bind variable from the URL). Both dialects converge on one SQL truth.

Bridges to the analysis-level pInvAnomaliesSigma parameter via MappedDataSetParameters declared on the parameter declaration in apps/investigation/app.py.

Return type:

DataSet

Parameters:

cfg (Config)

recon_gen.apps.investigation.datasets.build_volume_anomalies_distribution_dataset(cfg)

Same matview as build_volume_anomalies_dataset — no σ filter.

Y.1.b.companion — the SELECTED_VISUALS-scope workaround for SQL pushdown. The σ filter under Y.1 lives in the dataset SQL of build_volume_anomalies_dataset; that filter applies to every visual reading that dataset. The Volume Anomalies sheet’s distribution bar chart deliberately stays UNFILTERED (its UX role is to show the full population shape against which the analyst reads “where my σ threshold sits”). Filtering the distribution by σ would defeat its purpose.

Solution: a second dataset over the same matview without the parameter. The distribution chart binds to this dataset; KPI + Table bind to the parameter-bearing one. Same matview, two dataset SELECT wrappers — DB cost is one matview scan per visual, identical to pre-Y. The duplication is the cost of preserving SELECTED_VISUALS scope under SQL-level pushdown.

Y.2 will reuse this pattern wherever a FilterGroup with SELECTED_VISUALS scope gets pushed to dataset SQL.

Return type:

DataSet

Parameters:

cfg (Config)

recon_gen.apps.investigation.datasets.inv_matview_specs(cfg)

The deployment-prefixed Inv matviews + base tables the 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 / l2ft_matview_specs.

Return type:

list[tuple[str, str | None]]

Parameters:

cfg (Config)