"""L1 Dashboard — generic L2-fed reconciliation dashboard.
Tree-built from scratch around the M.1a.7 L1 invariant views. Replaces
the v5 idiom translation layer (apps/account_recon/_l2_datasets.py) with
direct view consumption — each sheet IS one L1 SHOULD-constraint
visualized.
Architecture (M.2a.1 decision): parallel-stack with the legacy
apps/account_recon/ — the v5 AR app keeps working against its v5
schema deployment until M.2a.10 deprecates it. The L1 dashboard builds
fresh tree-built sheets against the v6 prefixed schema + L1 invariant
views per L2 instance, with no v5-idiom column shims.
Build pipeline:
build_l1_dashboard_app(cfg, *, l2_instance=None) -> App
Default L2 instance is the canonical Sasquatch AR fixture (same as the
AR legacy stack); callers MAY override (tests, alternative-persona
deployments) via the kwarg.
Substep landmarks:
M.2a.1 — package skeleton + Analysis + Dashboard registered
M.2a.2 — Getting Started sheet with description-driven prose
M.2a.3 — Drift sheet — KPIs + leaf + ledger drift tables
M.2a.4 — Overdraft sheet — KPI + violations table
M.2a.5 — Limit Breach sheet — KPI + breach table
M.2a.6 — L1 Exceptions sheet — UNION across L1 views
M.2a.7 — Description-driven prose across every sheet (this commit)
M.2a.8 — Hash-lock the seed at the M.2a structure
M.2a.9 — Deploy + verify against Aurora
M.2a.10 — Iteration gate; decide on apps/account_recon/ deprecation
"""
from __future__ import annotations
from dataclasses import dataclass
from typing import Literal
from recon_gen.common.l2 import default_l2_instance
from recon_gen.apps.l1_dashboard.datasets import (
DS_DAILY_STATEMENT_SUMMARY,
DS_DAILY_STATEMENT_TRANSACTIONS,
DS_DRIFT,
DS_DRIFT_TIMELINE,
DS_L1_ACCOUNTS,
DS_L1_DS_ACCOUNTS,
DS_L1_DS_ROLES,
DS_L1_TX_FACETS,
DS_L1_TX_IDS,
DS_LEDGER_DRIFT,
DS_LEDGER_DRIFT_TIMELINE,
DS_LIMIT_BREACH,
DS_OVERDRAFT,
DS_STUCK_PENDING,
DS_STUCK_UNBUNDLED,
DS_SUPERSESSION_DAILY_BALANCES,
DS_SUPERSESSION_TRANSACTIONS,
DS_L1_EXCEPTIONS,
DS_TRANSACTIONS,
L1_ALL_SENTINEL,
P_L1_DATE_END as _P_L1_DATE_END,
P_L1_DATE_START as _P_L1_DATE_START,
P_L1_DRIFT_ACCOUNT,
P_L1_DRIFT_ROLE,
P_L1_DRIFT_TL_ROLE,
P_L1_DS_ACCOUNT_DSP,
P_L1_DS_BALANCE_DATE_DSP,
P_L1_DS_ROLE_DSP,
P_L1_LIMIT_BREACH_ACCOUNT,
P_L1_LIMIT_BREACH_TYPE,
P_L1_OVERDRAFT_ACCOUNT,
P_L1_OVERDRAFT_ROLE,
P_L1_PENDING_ACCOUNT,
P_L1_PENDING_RAIL,
P_L1_PENDING_TYPE,
P_L1_SUPERSEDE_REASON,
P_L1_TODAYS_EXC_ACCOUNT,
P_L1_TODAYS_EXC_CHECK_TYPE,
P_L1_TODAYS_EXC_TYPE,
P_L1_TX_ACCOUNT,
P_L1_TX_ORIGIN,
P_L1_TX_STATUS,
P_L1_TX_TRANSFER_ID,
P_L1_TX_TYPE,
P_L1_UNBUNDLED_ACCOUNT,
P_L1_UNBUNDLED_RAIL,
P_L1_UNBUNDLED_TYPE,
build_all_l1_dashboard_datasets,
l1_account_role_values,
l1_check_type_values,
l1_rail_values,
l1_supersede_reason_values,
l1_rail_universe_values,
)
from recon_gen.common import rich_text as rt
from recon_gen.common.config import Config
from recon_gen.common.handbook.invariants import (
load_bundled_invariants,
panel_markdown,
)
from recon_gen.common.ids import FilterGroupId, ParameterName, SheetId
from recon_gen.common.l2 import L2Instance
from recon_gen.common.dataset_contract import ColumnShape
from recon_gen.common.sheets.app_info import (
APP_INFO_SHEET_DESCRIPTION,
APP_INFO_SHEET_NAME,
APP_INFO_SHEET_TITLE,
app_info_liveness_id,
app_info_matviews_id,
populate_app_info_sheet,
)
# BO.5 — per-app App Info dataset identifiers. Pre-BO.5 these were the
# shared ``"app-info-liveness-ds"`` / ``"app-info-matviews-ds"`` strings
# from app_info.py; the shared name corrupted the App2 SQL registry on
# ``dashboards --app all`` so every dashboard rendered the last-registered
# app's matview-status panel. Per-segment IDs let all four coexist.
_DS_APP_INFO_LIVENESS = app_info_liveness_id("l1")
_DS_APP_INFO_MATVIEWS = app_info_matviews_id("l1")
from recon_gen.common.l2 import ThemePreset
from recon_gen.common.theme import resolve_l2_theme
from recon_gen.common.tree.actions import DrillWrite
from recon_gen.common.tree import (
AUTO,
Analysis,
App,
AutoResolved,
CalcField,
CategoryFilter,
CellAccentText,
Dataset,
DateTimeParam,
DateView,
Drill,
DrillParam,
DrillResetSentinel,
DrillStaticDateTime,
FilterGroup,
KPIValueZeroIndicator,
LinkedValues,
Sheet,
StaticValues,
StringParam,
TextBox,
)
# Layout constants — mirror apps/account_recon/app.py so visual heights
# read consistently across the two AR stacks.
_FULL = 36
_HALF = 18
_THIRD = 12 # AO.9 — used by Supersession Audit's 3-KPI row
_KPI_ROW_SPAN = 6
_CHART_ROW_SPAN = 12
_TABLE_ROW_SPAN = 18
# Sheet IDs — inlined in app.py per the greenfield-app convention
# (L.7 Executives) since the L1 dashboard isn't dragging legacy URL
# stability constraints from a previous deploy.
SHEET_GETTING_STARTED = SheetId("l1-sheet-getting-started")
SHEET_DRIFT = SheetId("l1-sheet-drift")
SHEET_DRIFT_TIMELINES = SheetId("l1-sheet-drift-timelines")
SHEET_OVERDRAFT = SheetId("l1-sheet-overdraft")
SHEET_LIMIT_BREACH = SheetId("l1-sheet-limit-breach")
SHEET_PENDING_AGING = SheetId("l1-sheet-pending-aging")
SHEET_UNBUNDLED_AGING = SheetId("l1-sheet-unbundled-aging")
SHEET_SUPERSESSION_AUDIT = SheetId("l1-sheet-supersession-audit")
SHEET_L1_EXCEPTIONS = SheetId("l1-sheet-exceptions")
SHEET_DAILY_STATEMENT = SheetId("l1-sheet-daily-statement")
SHEET_TRANSACTIONS = SheetId("l1-sheet-transactions")
SHEET_APP_INFO = SheetId("l1-sheet-app-info")
# Parameter names — analysis-level parameters that drive the universal
# date-range filter (M.2b.1). Each data-bearing sheet has paired
# date-time picker controls bound to these params, so all 4 sheets'
# pickers stay in sync via shared parameter values. Phase BM —
# the underlying string literals live in ``datasets.py`` so the
# dataset-side ``<<$pL1DateStart>>`` / ``<<$pL1DateEnd>>`` placeholders
# bridge to the same NAME the analysis-side declares (one source).
P_L1_DATE_START = ParameterName(_P_L1_DATE_START)
P_L1_DATE_END = ParameterName(_P_L1_DATE_END)
# M.2b.4 — Daily Statement parameters. Single-value account_id +
# single-value business_day_start drive the per-account-day filter on
# both the summary KPIs and the transactions detail table.
P_L1_DS_ACCOUNT = ParameterName("pL1DsAccount")
P_L1_DS_BALANCE_DATE = ParameterName("pL1DsBalanceDate")
# AA.B.1 — Daily Statement Role cascade. The role dropdown narrows the
# Account dropdown's options via the ``pL1DsRole`` dataset param on
# ``DS_L1_ACCOUNTS``. Default = ``L1_ALL_SENTINEL`` (show every account
# regardless of role on first load).
P_L1_DS_ROLE = ParameterName("pL1DsRole")
# M.2b.7 — Drill-target parameters (sentinel-pattern, mirror of AR).
# These never appear as visible sheet controls — they're only written
# by drill actions. Each per-invariant sheet (Drift / Overdraft /
# Limit Breach) carries a calc-field-backed FilterGroup that reads
# ``pL1FilterAccount`` to narrow its dataset to one account; the
# Transactions sheet does the same for ``pL1TxTransfer``. The "__ALL__"
# sentinel default means "no filter" — destination calc fields special-
# case it to PASS so the un-drilled state shows everything.
P_L1_FILTER_ACCOUNT = ParameterName("pL1FilterAccount")
P_L1_TX_TRANSFER = ParameterName("pL1TxTransfer")
# Sentinel value the M.2b.7 drill calc fields treat as PASS — same
# string AR uses (mirror).
_DRILL_RESET_SENTINEL = "__ALL__"
# Typed DrillParam constants — pair each ParameterName with its
# expected ColumnShape so cross_sheet_drill() refuses shape-mismatched
# writes at construction time (the K.2 invariant).
_DP_FILTER_ACCOUNT = DrillParam(P_L1_FILTER_ACCOUNT, ColumnShape.ACCOUNT_ID)
_DP_TX_TRANSFER = DrillParam(P_L1_TX_TRANSFER, ColumnShape.TRANSFER_ID)
_DP_DS_ACCOUNT = DrillParam(P_L1_DS_ACCOUNT, ColumnShape.ACCOUNT_ID)
_DP_DS_BALANCE_DATE = DrillParam(
P_L1_DS_BALANCE_DATE, ColumnShape.DATETIME_DAY,
)
# v8.5.7 — universal date-range params, exposed as drill targets so a
# cross-sheet drill from an unscoped current-state sheet (Pending Aging,
# Unbundled Aging, Supersession Audit) into a date-scoped sheet
# (Transactions) can widen the window to "all time" so the drill
# target row is in scope. See ``_WIDE_DATE_WRITES`` below.
_DP_DATE_START = DrillParam(P_L1_DATE_START, ColumnShape.DATETIME_DAY)
_DP_DATE_END = DrillParam(P_L1_DATE_END, ColumnShape.DATETIME_DAY)
# Far-past + far-future ISO-8601 literals the drill writes use when
# widening the universal date range. The destination sheet's picker
# will visibly snap to these values (a known QuickSight UX wart with
# in-app drill writes) — analysts re-narrow if they want a tighter
# slice. Mirrors the L2 Flow Tracing app's ``1900-01-01`` static
# default convention; we picked 1990 instead because it predates any
# realistic banking dataset while still being "modern" enough not to
# look like an off-by-error. End at 2099 keeps the picker from
# showing dates an analyst would mistake for a typo.
_WIDE_DATE_START_VALUE = "1990-01-01T00:00:00.000Z"
_WIDE_DATE_END_VALUE = "2099-12-31T00:00:00.000Z"
def _wide_date_writes() -> list[tuple[DrillParam, DrillStaticDateTime]]:
"""Pair of writes that widen the universal date range to "all time".
Caller appends these to ``writes=`` on cross-sheet drills whose
destination sheet is universally-date-scoped AND whose source
sheet is not — i.e. drills FROM Pending Aging / Unbundled Aging /
Supersession Audit (current-state views, unscoped) INTO
Transactions (universally scoped). Without these writes the drill
target row falls outside the destination's default 7-day window
and the table renders empty.
Same-scope drills (e.g. L1 Exceptions → Drift, both already
in the universal date filter) do NOT need these writes — the
user's existing window is preserved across the drill.
"""
return [
(_DP_DATE_START, DrillStaticDateTime(_WIDE_DATE_START_VALUE)),
(_DP_DATE_END, DrillStaticDateTime(_WIDE_DATE_END_VALUE)),
]
_GETTING_STARTED_NAME = "Getting Started"
_GETTING_STARTED_TITLE = "L1 Reconciliation Dashboard"
_GETTING_STARTED_DESCRIPTION = (
"Where to start. The dashboard groups every L1 SHOULD-constraint "
"into one tab per exception kind — drift, overdraft, limit breach, "
"expected EOD balance variance — plus a L1 Exceptions roll-up. "
"Each tab queries one L1 invariant view directly; rows ARE the "
"constraint violations."
)
_DRIFT_NAME = "Drift"
_DRIFT_TITLE = "Account Balance Drift"
_DRIFT_DESCRIPTION = (
"Stored vs computed balance disagreements at end-of-day. Leaf table "
"covers individual posting accounts — **ledger drift** (computed = "
"cumulative net of every Money record through that BusinessDay's "
"end; stored ≠ computed ⇒ ledger doesn't agree with the postings). "
"Ledger table covers parent accounts — **aggregation drift** "
"(computed = sum of child accounts' stored balances; stored ≠ "
"computed ⇒ rollup doesn't match the children). Both tables only "
"show rows where stored ≠ computed — every row is one "
"SHOULD-constraint violation. Distinct from Daily Statement's "
"\"Posting Drift\" KPI (single-day flow drift); both are correct, "
"they answer different questions at different time grains."
)
_DRIFT_TIMELINES_NAME = "Drift Timelines"
_DRIFT_TIMELINES_TITLE = "Drift Magnitude Over Time"
_DRIFT_TIMELINES_DESCRIPTION = (
"Σ ABS(drift) per BusinessDay end, one line per account_role. "
"Healthy days sit on the zero baseline; spikes mark when the feed "
"or the parent-roll-up diverged. Use this to spot recurring "
"violations vs one-off events — a role that spikes every Monday is "
"a different problem than a role that spiked once after a deploy. "
"KPIs surface the largest single-day magnitude in the past 7 days."
)
_OVERDRAFT_NAME = "Overdraft"
_OVERDRAFT_TITLE = "Internal Account Overdrafts"
_OVERDRAFT_DESCRIPTION = (
"Internal accounts holding negative money at end-of-day. The L1 "
"invariant is 'no internal account holds negative balance' — every "
"row in the table below is one violation. External accounts are "
"excluded by the underlying view (banks may legitimately overdraft "
"us; we MUST NOT overdraft them). "
"**Orthogonal to Drift**: Overdraft asks 'is stored < 0?' "
"(an absolute sign check). Drift asks 'does stored agree with the "
"cumulative net of postings?' (a reconciliation check). A "
"chronically-negative account whose postings have always summed to "
"the same negative number is **overdrafted but NOT drifted** — its "
"ledger is internally consistent, it's just consistently in the "
"red. Same datum, two independent SHOULD-constraints; expect "
"Overdraft rows that don't appear on the Drift sheet."
)
_LIMIT_BREACH_NAME = "Limit Breach"
_LIMIT_BREACH_TITLE = "Outbound Transfer Limit Breaches"
_LIMIT_BREACH_DESCRIPTION = (
"Per-account, per-day, per-transfer-type cells where cumulative "
"outbound debit exceeded the L2-configured cap. Caps are pulled "
"from the L2 instance's LimitSchedules at schema-emit time and "
"embedded inline in the underlying view — no JSON path lookups in "
"the dataset SQL. Every row is one violation."
)
_PENDING_AGING_NAME = "Pending Aging"
_PENDING_AGING_TITLE = "Pending Transactions Aging Past Cap"
_PENDING_AGING_DESCRIPTION = (
"Transactions stuck in `status='Pending'` past their rail's "
"configured `max_pending_age` cap. Each Rail in the L2 instance "
"with an aging watch contributes its own threshold; the underlying "
"view inlines these caps at schema-emit time. KPI shows total stuck "
"count; the aging bar chart breaks the population into 5 buckets "
"(0–6h, 6–24h, 1–3d, 3–7d, >7d) so operators can see whether they're "
"fighting one big spike or a slow drift. Right-click any row → "
"View Transactions to see every leg of that transfer."
)
_UNBUNDLED_AGING_NAME = "Unbundled Aging"
_UNBUNDLED_AGING_TITLE = "Unbundled Posted Legs Aging Past Cap"
_UNBUNDLED_AGING_DESCRIPTION = (
"Posted transactions whose `bundle_id` is still NULL past their "
"rail's `max_unbundled_age` cap. An AggregatingRail's job is to "
"pick up these legs and group them into a Bundle; an unbundled leg "
"older than the rail's cadence means the bundler hasn't fired or "
"is failing to match. KPI shows total stuck count; the aging bar "
"chart breaks the population into 4 buckets (<1d, 1–2d, 2–7d, >7d) "
"— the typical max_unbundled_age cadence is a day or two, so "
"buckets are wider than Pending Aging's. Right-click any row → "
"View Transactions to see every leg of that transfer."
)
_SUPERSESSION_AUDIT_NAME = "Supersession Audit"
_SUPERSESSION_AUDIT_TITLE = "Supersession Audit Trail"
_SUPERSESSION_AUDIT_DESCRIPTION = (
"Every logical row whose append-only `entry` column has more than "
"one version. Each rewrite carries a `supersedes` reason from L1's "
"v1 vocabulary (Inflight / BundleAssignment / TechnicalCorrection). "
"Reads from the BASE tables, not Current* — by definition Current* "
"hides the prior entries we want to audit here. Use the supersedes "
"filter to slice by reason: high TechnicalCorrection volume signals "
"a feed problem; high Inflight is normal in a busy bundling cadence."
)
_L1_EXCEPTIONS_NAME = "L1 Exceptions"
_L1_EXCEPTIONS_TITLE = "L1 Exceptions"
_L1_EXCEPTIONS_DESCRIPTION = (
# C6 (cold-read v11.26.1) — copy used to claim "5 L1 invariants" but
# the matview UNIONs across 10 check kinds: 5 balance/numeric checks
# (drift / ledger_drift / overdraft / limit_breach /
# expected_eod_balance_breach), 3 chain/cardinality checks
# (chain_parent_disagreement / fan_in_disagreement /
# multi_xor_violation), and 2 time-based stuck checks (stuck_pending
# / stuck_unbundled). Updated to the full taxonomy so the chart's
# bars match the prose.
"The 9am scan — every L1 SHOULD-constraint violation across all "
"10 invariant views: **balance** (drift, ledger drift, overdraft, "
"limit breach, expected EOD balance), **chain/cardinality** (chain "
"parent disagreement, fan-in disagreement, multi-XOR violation), "
"and **time** (stuck pending, stuck unbundled), scoped to the most "
"recent business day in the data. Replaces v5's "
"ar_unified_exceptions matview with a live UNION; no REFRESH "
"contract. KPI tracks total open count; bar chart breaks down by "
"check_type; detail table sorts by magnitude so the biggest "
"variances surface first."
)
_DAILY_STATEMENT_NAME = "Daily Statement"
_DAILY_STATEMENT_TITLE = "Per-Account Daily Statement"
_DAILY_STATEMENT_DESCRIPTION = (
"Per-account, per-day walk: opening balance + day's debits + "
"credits + closing balance + drift. Pick one account and one "
"business day via the controls; KPIs surface the 5-number summary "
"and the detail table lists every Money record posted that day. "
"Drift = stored closing − (opening + signed-net flow); on a healthy "
"feed it's exactly zero, so non-zero drift here is the single "
"visual cue the underlying ledger doesn't reconcile for that "
"account-day. Mirrors AR's Daily Statement pattern. "
"Account picker lists accounts with stored daily balances only "
"(L2 control-account stubs that lack their own balance row are "
"filtered out). "
"**Role dropdown — renderer divergence:** App2 narrows the "
"Account list to the picked Role; QuickSight does not (a QS "
"endpoint bug — see quicksight-quirks.md). On QS the Account "
"dropdown shows the full universe regardless."
)
_TRANSACTIONS_NAME = "Transactions"
_TRANSACTIONS_TITLE = "Posting Ledger"
_TRANSACTIONS_DESCRIPTION = (
"The raw posting ledger — one row per Money record (leg). "
"Supersession-aware: the underlying view filters out replaced "
"entries so what you see IS the current truth. Filter by account, "
"transfer, status (Pending / Posted / Failed), origin "
"(InternalInitiated / ExternalForcePosted / ExternalAggregated), "
"or transfer type. Drill out to Daily Statement for the account-day "
"context any leg sits in (drill wiring lands at M.2b.7)."
)
def _analysis_name(cfg: Config, l2_instance: L2Instance) -> str:
"""Title shown on the deployed QuickSight Analysis."""
return f"L1 Reconciliation Dashboard ({cfg.deployment_name})"
# -- L2-prose helpers --------------------------------------------------------
#
# M.2a.7's "description-driven prose" core: pull facts about the configured
# L2 instance into per-sheet text boxes so each sheet IS the handbook page
# for that L1 invariant under this institution. Switching L2 instance
# switches the prose across every sheet — tested at the substep that
# introduces each helper's call site.
def _l2_inventory_lines(l2_instance: L2Instance) -> list[str]:
"""Compact inventory bullets for the Getting Started coverage block."""
accounts = l2_instance.accounts
internal = sum(1 for a in accounts if a.scope == "internal")
external = sum(1 for a in accounts if a.scope == "external")
return [
f"{internal} internal accounts, {external} external accounts",
f"{len(l2_instance.account_templates)} account templates "
f"(role classes that bind to specific accounts at posting time)",
f"{len(l2_instance.rails)} rails "
f"(reconciliation patterns the integrator declares)",
f"{len(l2_instance.transfer_templates)} transfer templates "
f"(multi-leg shared transfers)",
f"{len(l2_instance.chains)} chains "
f"(transfer-of-transfers ordered flows)",
f"{len(l2_instance.limit_schedules)} limit schedules "
f"(daily outbound caps by parent role × transfer type)",
]
def _l2_limit_schedule_lines(l2_instance: L2Instance) -> list[str]:
"""Per-LimitSchedule bullets — name, cap, and L2-supplied prose."""
if not l2_instance.limit_schedules:
return [
"No limit schedules configured on this L2 instance — "
"the limit-breach view returns zero rows by construction.",
]
lines: list[str] = []
for ls in l2_instance.limit_schedules:
# Money is a Decimal; format with thousands separators + 2dp.
cap_str = f"${ls.cap:,.2f}/day"
head = f"{ls.parent_role} × {ls.rail}: {cap_str}"
if ls.description:
lines.append(f"{head} — {ls.description}")
else:
lines.append(head)
return lines
def _l2_internal_account_role_lines(l2_instance: L2Instance) -> list[str]:
"""One bullet per internal account or template with prose."""
lines: list[str] = []
for a in l2_instance.accounts:
if a.scope != "internal":
continue
head = f"{a.role or a.id} ({a.id})"
if a.description:
lines.append(f"{head} — {a.description}")
else:
lines.append(head)
for t in l2_instance.account_templates:
if t.scope != "internal":
continue
head = f"{t.role} (template)"
if t.description:
lines.append(f"{head} — {t.description}")
else:
lines.append(head)
return lines
def _l1_datasets(
cfg: Config, l2_instance: L2Instance,
) -> dict[str, Dataset]:
"""Build every L1 dataset and return tree-ref Datasets keyed by id.
Each AWS DataSet's ``DataSetId`` becomes the tree Dataset's ARN
path component; the visual identifier (the registry key passed to
`build_dataset()`) becomes the tree Dataset's ``identifier`` field.
The contract is registered as a side-effect of `build_dataset()`,
so subsequent ``ds["col"]`` accesses validate.
M.4.4.5 — App Info ("i") sheet datasets land at the end of the
list; their order matches `build_all_l1_dashboard_datasets`'s
appended App Info pair.
"""
aws_datasets = build_all_l1_dashboard_datasets(cfg, l2_instance)
# `build_all_l1_dashboard_datasets` returns AWS DataSets in the same
# order as the visual identifiers below; map each to a tree Dataset.
visual_ids = [
DS_DRIFT, DS_LEDGER_DRIFT, DS_OVERDRAFT,
DS_LIMIT_BREACH, DS_L1_EXCEPTIONS,
DS_DAILY_STATEMENT_SUMMARY, DS_DAILY_STATEMENT_TRANSACTIONS,
DS_TRANSACTIONS,
DS_DRIFT_TIMELINE, DS_LEDGER_DRIFT_TIMELINE,
DS_STUCK_PENDING, DS_STUCK_UNBUNDLED,
DS_SUPERSESSION_TRANSACTIONS, DS_SUPERSESSION_DAILY_BALANCES,
DS_L1_ACCOUNTS, DS_L1_DS_ACCOUNTS,
DS_L1_DS_ROLES, DS_L1_TX_IDS, DS_L1_TX_FACETS,
_DS_APP_INFO_LIVENESS, _DS_APP_INFO_MATVIEWS,
]
return {
vid: Dataset(identifier=vid, arn=cfg.dataset_arn(aws.DataSetId))
for vid, aws in zip(visual_ids, aws_datasets)
}
def _populate_getting_started(
cfg: Config,
sheet: Sheet,
l2_instance: L2Instance,
*,
theme: ThemePreset,
) -> None:
"""Render the Getting Started sheet using the L2 instance's prose.
M.2a's "description-driven prose" core: the welcome text uses
`l2_instance.description` as the body, and the coverage block lists
the L2 inventory (account counts, rail counts, etc.) — both
derived from the L2 instance, NOT hardcoded persona strings.
Switching L2 instance switches the prose.
"""
accent = theme.accent
welcome_body = (
l2_instance.description
if l2_instance.description
else "(L2 instance description missing — fill the top-level "
"`description` field in the L2 YAML.)"
)
sheet.layout.row(height=8).add_text_box(
TextBox(
text_box_id="l1-gs-welcome",
content=rt.text_box(
rt.inline(
_GETTING_STARTED_TITLE,
font_size="36px",
color=accent,
),
rt.BR, rt.BR,
rt.markdown(welcome_body),
),
),
width=_FULL,
)
sheet.layout.row(height=8).add_text_box(
TextBox(
text_box_id="l1-gs-coverage",
content=rt.text_box(
rt.subheading("L2 Coverage", color=accent),
rt.BR,
rt.markdown(
"What this dashboard reconciles, derived from the "
"configured L2 instance:"
),
rt.bullets(_l2_inventory_lines(l2_instance)),
),
),
width=_FULL,
)
def _populate_drift_sheet(
cfg: Config,
sheet: Sheet,
*,
datasets: dict[str, Dataset],
l2_instance: L2Instance,
daily_statement_sheet: Sheet,
theme: ThemePreset,
) -> None:
"""Drift sheet — 2 KPIs + leaf-drift table + ledger-drift table.
Both tables are unaggregated row passthroughs: the L1 views
pre-filter to violations only (``stored_balance != computed_balance``)
so each row is one SHOULD-constraint failure.
M.2a.7: top-of-sheet TextBox enumerates the L2's internal accounts
+ their roles + L2-supplied prose so analysts see the universe of
accounts drift can surface against.
"""
accent = theme.accent
ds_drift = datasets[DS_DRIFT]
ds_ledger_drift = datasets[DS_LEDGER_DRIFT]
sheet.layout.row(height=8).add_text_box(
TextBox(
text_box_id="l1-drift-accounts",
content=rt.text_box(
rt.subheading("Internal Accounts in Scope", color=accent),
rt.BR,
rt.markdown(
"Accounts where drift is checked — drift surfaces "
"where stored balance disagrees with the cumulative "
"net of posted Money records (leaf) or the sum of "
"child stored balances (parent):"
),
rt.bullets(_l2_internal_account_role_lines(l2_instance)),
),
),
width=_FULL,
)
# Row 2: four KPIs (BH.4 follow-up 2026-05-26 after v11.22.1
# cold-read). Previously two counts ("Leaf Accounts in Drift",
# "Parent Accounts in Drift"); cold-read agents read a count of
# zero as "no drift problem" and stopped investigating even when
# adjacent table had populated rows from a different scope (e.g.
# leaf=0 in current window but parent timeline shows persistent
# drift across the window). Add sibling MAX(ABS) money-amount
# KPIs so the headline carries TWO signals: how MANY accounts
# have drift today AND the LARGEST magnitude. The pair makes
# "0 accounts AND $0 max" the unambiguous healthy state; any
# divergence between the two surfaces the scope confusion that
# bit the cold-read.
quarter = _FULL // 4
kpi_row = sheet.layout.row(height=_KPI_ROW_SPAN)
kpi_row.add_kpi(
width=quarter,
# C8 (cold-read v11.26.1) — title relabeled to "Account-Days"
# to match what the count actually measures (one row per
# leaf-account-day in the matview, not distinct accounts).
# Same fix class as C18 (Overdraft title/subtitle mismatch).
title="Leaf Account-Days in Drift",
subtitle=(
"Count of leaf-account day-rows where stored balance "
"disagrees with the cumulative net of posted Money records "
"in the current date window."
),
values=[ds_drift["account_id"].count()],
)
kpi_row.add_kpi(
width=quarter,
title="Largest Leaf Drift (anywhere in window)",
subtitle=(
"Max |drift| across any single leaf-account day-row in the "
"current date window. **Sibling of the count KPI** — count "
"+ magnitude together prevent the failure mode where a "
"SUM-based KPI cancels positives + negatives to ~0 while "
"per-account drifts remain non-trivial. Distinct from "
"Drift Timelines' \"Largest Leaf Drift Day\" KPI, which is "
"the peak Σ|drift| on the worst single business day "
"(day-grain rollup) — this one is the peak account-day "
"(row-grain peak). The ✓/✗ glyph next to the number is "
"the accessible state signal — green ✓ when the largest "
"absolute drift in the window is $0, red ✗ otherwise."
),
values=[ds_drift["abs_drift"].max(currency=True)],
value_zero_indicator=KPIValueZeroIndicator(),
)
kpi_row.add_kpi(
width=quarter,
# C8 (cold-read v11.26.1) — same relabel as the leaf KPI above.
title="Parent Account-Days in Drift",
subtitle=(
"Count of parent-account day-rows where stored balance "
"disagrees with the sum of child accounts' stored balances. "
"Demos with the bundled `sasquatch_pr` fixture show a "
"persistent **~$2.8M** ledger drift on `gl-2010-dda-control` — "
"that's the **Drift Parent (DDAControl) plant**, the L1 SPEC "
"example showing cross-boundary drift propagation. Real "
"deploys should see 0 here unless there's a real ledger "
"rollup gap."
),
values=[ds_ledger_drift["account_id"].count()],
)
kpi_row.add_kpi(
width=quarter,
title="Largest Parent Drift (anywhere in window)",
subtitle=(
"Max |drift| across any single parent-account day-row in "
"the current date window. Sibling magnitude KPI to the "
"Parent count — see Leaf sibling subtitle for rationale. "
"Distinct from Drift Timelines' \"Largest Parent Drift "
"Day\" — that one rolls up to a per-business-day Σ before "
"taking the max, so it answers \"worst SINGLE day in the "
"window\" rather than \"worst single account-day row\". "
"The ✓/✗ glyph next to the number is the accessible state "
"signal — green ✓ when the largest parent drift in the "
"window is $0, red ✗ otherwise. "
# C11 (cold-read v11.26.1) — sasquatch_pr's bundled demo
# plants a persistent ~$2.8M ledger drift on the DDAControl
# parent to exercise this surface. Read as expected on
# demo deploys, not as a conservation failure.
"**Closed-loop pool note:** demo pools accumulate "
"pre-settlement value at the parent layer by design "
"(the SPEC's L1 example); a persistent non-zero parent "
"drift on a known pool account is **expected for the "
"demo seed**, not a real ledger gap."
),
values=[ds_ledger_drift["abs_drift"].max(currency=True)],
value_zero_indicator=KPIValueZeroIndicator(),
)
# Row 2: leaf-drift table. Pull account_id + business_day_start Dims
# local so the link tint + drill can reference the same field_id
# as the columns. Right-click → View Daily Statement narrows the
# forward investigation to that account-day.
#
# AA.A.996, 2026-05-18 — display ``business_day_start`` (the matview
# natural key + the timestamp the trading day BEGINS at for THIS
# account). One row = one logical day per account, but per-account
# business-day boundaries differ (a 17:00→17:00 customer DDA vs a
# midnight→midnight retail DDA are different actual windows even
# when the date portion matches), so render at SECOND granularity
# to keep the boundary timestamp visible — ``.date()`` (DAY) would
# truncate it. Aligns with ``_matview_extract`` + scenario plants
# + the universal date filter (see ``_scope_one`` at the bottom of
# this file). Bonus: the Daily Statement drill writes
# ``leaf_day_col`` into ``_DP_DS_BALANCE_DATE``, which Daily
# Statement filters by start-of-day — previously off by 1 day
# because the visual was showing end and the drill wrote end.
leaf_account_col = ds_drift["account_id"].dim()
leaf_day_col = ds_drift["business_day_start"].date(
date_granularity="SECOND",
)
sheet.layout.row(height=_TABLE_ROW_SPAN).add_table(
width=_FULL,
title="Leaf Account Drift",
subtitle=(
"Each leaf account's stored vs computed balance per "
"BusinessDay. Computed = cumulative Σ signed Money through "
"that day's end. Drift = stored − computed; non-zero ⇒ feed "
"diverged from the underlying ledger. Right-click any row "
"→ View Daily Statement to open that account-day."
),
columns=[
leaf_account_col,
ds_drift["account_name"].dim(),
ds_drift["account_role"].dim(),
ds_drift["account_parent_role"].dim(),
leaf_day_col,
ds_drift["stored_balance"].numerical(currency=True),
ds_drift["computed_balance"].numerical(currency=True),
ds_drift["drift"].numerical(currency=True),
],
actions=[
_l1_drill(
target_sheet=daily_statement_sheet,
name="View Daily Statement for this account-day",
writes=[
(_DP_DS_ACCOUNT, leaf_account_col),
(_DP_DS_BALANCE_DATE, leaf_day_col),
],
trigger="DATA_POINT_MENU",
),
],
conditional_formatting=[
CellAccentText(on=leaf_account_col, color=accent),
],
)
# Row 3: ledger (parent-account) drift table — same shape minus
# account_parent_role (parents ARE the parents). Same Daily
# Statement drill.
# AA.A.996 — see ``leaf_day_col`` above for the natural-key alignment
# + SECOND-granularity + per-account boundary rationale.
parent_account_col = ds_ledger_drift["account_id"].dim()
parent_day_col = ds_ledger_drift["business_day_start"].date(
date_granularity="SECOND",
)
sheet.layout.row(height=_TABLE_ROW_SPAN).add_table(
width=_FULL,
title="Parent Account Drift",
subtitle=(
"Each parent account's stored vs computed balance per "
"BusinessDay. Computed = Σ stored balances of its child "
"accounts on that day. Drift = stored − computed; non-zero "
"⇒ a child posting didn't roll up correctly. Right-click "
"any row → View Daily Statement to open that account-day."
),
columns=[
parent_account_col,
ds_ledger_drift["account_name"].dim(),
ds_ledger_drift["account_role"].dim(),
parent_day_col,
ds_ledger_drift["stored_balance"].numerical(currency=True),
ds_ledger_drift["computed_balance"].numerical(currency=True),
ds_ledger_drift["drift"].numerical(currency=True),
],
actions=[
_l1_drill(
target_sheet=daily_statement_sheet,
name="View Daily Statement for this account-day",
writes=[
(_DP_DS_ACCOUNT, parent_account_col),
(_DP_DS_BALANCE_DATE, parent_day_col),
],
trigger="DATA_POINT_MENU",
),
],
conditional_formatting=[
CellAccentText(on=parent_account_col, color=accent),
],
)
def _populate_drift_timelines_sheet(
cfg: Config,
sheet: Sheet,
*,
datasets: dict[str, Dataset],
) -> None:
"""Drift Timelines sheet — 2 KPIs + 2 line charts.
KPIs surface the largest single-day Σ ABS(drift) over the past 7
days for leaf and parent accounts respectively. Line charts plot
Σ ABS(drift) per BusinessDay end with one line per account_role,
so a recurring-drift role visually separates from a one-off spike.
Datasets pre-aggregate via `GROUP BY business_day_end, account_role`
on the (already small) drift / ledger_drift matviews. The line-chart
Y-axis is the SUM of the pre-aggregated `abs_drift` measure since
the dataset rows are already at (day, role) grain — the SUM is a
no-op per cell but lets QS render the line chart.
"""
ds_drift_timeline = datasets[DS_DRIFT_TIMELINE]
ds_ledger_drift_timeline = datasets[DS_LEDGER_DRIFT_TIMELINE]
# Row 1: 2 KPIs side-by-side — max single-day Σ ABS(drift) per kind.
half = _FULL // 2
kpi_row = sheet.layout.row(height=_KPI_ROW_SPAN)
kpi_row.add_kpi(
width=half,
title="Largest Leaf Drift Day (peak business day)",
subtitle=(
"Max Σ ABS(drift) on any single BusinessDay across leaf "
"accounts in the visible date range. Healthy = $0 (green "
"✓); red ✗ otherwise. Distinct from the Drift sheet's "
"\"Largest Leaf Drift\" KPI: that one is the worst single "
"account-day row; this one is the day when total |drift| "
"across all leaves was largest, so it can be smaller (one "
"big drift on a quiet day) or larger (many medium drifts "
"on a busy day) than the row-grain peak."
),
values=[ds_drift_timeline["abs_drift"].max(currency=True)],
value_zero_indicator=KPIValueZeroIndicator(),
)
kpi_row.add_kpi(
width=half,
title="Largest Parent Drift Day (peak business day)",
subtitle=(
"Max Σ ABS(drift) on any single BusinessDay across parent "
"accounts in the visible date range. Healthy = $0 (green "
"✓); red ✗ otherwise. Distinct from the Drift sheet's "
"\"Largest Parent Drift\" — that one is the worst single "
"account-day row, this one is the worst single business "
"day's roll-up. See \"Largest Leaf Drift Day\" subtitle "
"for the why."
),
values=[ds_ledger_drift_timeline["abs_drift"].max(currency=True)],
value_zero_indicator=KPIValueZeroIndicator(),
)
# Row 2: leaf drift line chart — one line per account_role.
leaf_day_col = ds_drift_timeline["business_day_end"].date()
sheet.layout.row(height=_CHART_ROW_SPAN).add_line_chart(
width=_FULL,
title="Leaf Account Drift Over Time",
subtitle=(
"Σ ABS(drift) per BusinessDay end for **leaf accounts** "
"(cardholder DDAs, merchant DDAs, external counters — every "
"non-aggregate L2 instance in the system). One line per "
"account_role so a single recurring-drift role separates "
"from a one-off spike. Companion: 'Parent Account Drift "
"Over Time' (below) shows the same shape for aggregate "
"accounts. A role hugging zero is healthy; persistent "
"non-zero ⇒ ongoing feed divergence; one-off spike ⇒ "
"isolated event worth drilling into."
),
category=[leaf_day_col],
values=[ds_drift_timeline["abs_drift"].sum(currency=True)],
colors=[ds_drift_timeline["account_role"].dim()],
category_label="BusinessDay end",
value_label="Σ |drift|",
sort_by=(leaf_day_col, "ASC"),
)
# Row 3: ledger (parent) drift line chart — same shape.
parent_day_col = ds_ledger_drift_timeline["business_day_end"].date()
sheet.layout.row(height=_CHART_ROW_SPAN).add_line_chart(
width=_FULL,
title="Parent Account Drift Over Time",
subtitle=(
"Σ ABS(drift) per BusinessDay end for **parent accounts** "
"(GL controls + concentration masters + funds pools — every "
"aggregate L2 instance that should equal SUM(its leaves)). "
"One line per account_role. Non-zero ⇒ child postings "
"didn't roll up correctly that day. Compare with 'Leaf "
"Account Drift Over Time' (above) to triage whether a "
"drift event lives at the leaf or at the rollup."
),
category=[parent_day_col],
values=[ds_ledger_drift_timeline["abs_drift"].sum(currency=True)],
colors=[ds_ledger_drift_timeline["account_role"].dim()],
category_label="BusinessDay end",
value_label="Σ |drift|",
sort_by=(parent_day_col, "ASC"),
)
def _populate_overdraft_sheet(
cfg: Config,
sheet: Sheet,
*,
datasets: dict[str, Dataset],
daily_statement_sheet: Sheet,
theme: ThemePreset,
) -> None:
"""Overdraft sheet — KPI (count of violations) + violations table.
Single dataset (`<prefix>_overdraft`) — only internal accounts, only
days where stored balance < 0. Right-click any row → Daily Statement
for that account-day (M.2b.7).
"""
accent = theme.accent
ds_overdraft = datasets[DS_OVERDRAFT]
sheet.layout.row(height=_KPI_ROW_SPAN).add_kpi(
width=_FULL,
# BH.14 follow-up (2026-05-26) — v11.22.1 cold-read finding #14
# noted "Internal Accounts in Overdraft" suggests pool/sweep
# accounts but detail rows include leaf-cardholder (cust-*).
# Broader title matches the matview's actual scope (any
# internal-scope account incl. customer DDAs).
# C18 (v11.26.1 cold-read) — title/subtitle grain mismatch:
# subtitle says "day-rows" but title said "Accounts." Same
# class as C8. Relabel matches the matview's actual unit.
title="Account-Days in Overdraft",
subtitle=(
"Count of internal-account day-rows holding negative stored "
"balance — every row in the table below is one violation."
),
values=[ds_overdraft["account_id"].count()],
)
# AA.A.996 — see leaf_day_col on the Drift sheet for the natural-key
# alignment + SECOND-granularity + per-account boundary rationale.
account_col = ds_overdraft["account_id"].dim()
day_col = ds_overdraft["business_day_start"].date(
date_granularity="SECOND",
)
sheet.layout.row(height=_TABLE_ROW_SPAN).add_table(
width=_FULL,
title="Overdraft Violations",
subtitle=(
"Each internal account-day where stored balance < 0. "
"Negative magnitude indicates how far below zero the account "
"ended the day. Right-click any row → View Daily Statement."
),
columns=[
account_col,
ds_overdraft["account_name"].dim(),
ds_overdraft["account_role"].dim(),
ds_overdraft["account_parent_role"].dim(),
day_col,
ds_overdraft["stored_balance"].numerical(currency=True),
],
actions=[
_l1_drill(
target_sheet=daily_statement_sheet,
name="View Daily Statement for this account-day",
writes=[
(_DP_DS_ACCOUNT, account_col),
(_DP_DS_BALANCE_DATE, day_col),
],
trigger="DATA_POINT_MENU",
),
],
conditional_formatting=[
CellAccentText(on=account_col, color=accent),
],
)
def _populate_l1_exceptions_sheet(
cfg: Config,
sheet: Sheet,
*,
datasets: dict[str, Dataset],
l2_instance: L2Instance,
drift_sheet: Sheet,
daily_statement_sheet: Sheet,
theme: ThemePreset,
) -> None:
"""L1 Exceptions sheet — KPI + check-type breakdown bar +
sorted detail table.
Backed by the live UNION ALL dataset across all 5 L1 invariant views
(drift, ledger_drift, overdraft, limit_breach, expected_eod_balance_breach),
pre-filtered to the most recent business day at the SQL layer. This
is the v5 ar_unified_exceptions matview's replacement — no REFRESH
contract; queries are live.
M.2a.7: footer TextBox carries the L2 instance's top-level
description, mirroring the Getting Started welcome — the unified
view's job is to be the morning landing page, so it gets the
institution's "what we are" prose at the bottom for context.
"""
accent = theme.accent
ds = datasets[DS_L1_EXCEPTIONS]
# Row 1: total count KPI (full width — single headline number).
sheet.layout.row(height=_KPI_ROW_SPAN).add_kpi(
width=_FULL,
title="Open Exceptions",
subtitle=(
# C6 (cold-read v11.26.1) — was "5 invariant checks" but
# the matview UNIONs 10; updated for accuracy. See the
# sheet description for the full taxonomy.
"Total count of L1 SHOULD-constraint violations in the "
"picker's date window across all 10 invariant checks. "
# BV.3.3.c.bug3 (2026-05-31) — the matview is no longer
# pre-filtered to latest_day; date narrowing happens at
# the dataset SQL via pL1DateStart / pL1DateEnd pushdown.
# KPI count == App Info matview row-count within the
# selected window. Wider window → larger count."
),
values=[ds["account_id"].count()],
)
# Row 2: bar chart broken out by check_type (count per check kind).
# Q.1.c — plain-English axis labels in place of the raw column
# names QuickSight defaults to ("check_type" / "Count of account_id").
# BQ.5 (cold-read F6): one check_type (typically stuck_unbundled)
# swamps the others at ~5000 vs near-zero; log_scale keeps the
# short bars readable so the rarer error classes still register.
sheet.layout.row(height=_CHART_ROW_SPAN).add_bar_chart(
width=_FULL,
title="Exceptions by Check Type",
subtitle=(
# C6 (cold-read v11.26.1) — same "5 → 10" copy fix as KPI
# above. The bar chart's GROUP BY check_type covers every
# branch of the l1_exceptions UNION ALL, so a check
# type with rows in the detail table always renders a bar
# (the cold-read flagged fan_in_disagreement showing in
# the table but not on the chart — addressed by the
# ``COALESCE(SUM(1), 0)`` count semantics fix in C2,
# which also makes the GROUP BY emit zero-bar entries
# for ANY check_type present in the day's matview slice).
"How open exceptions in the date window distribute across the 10 L1 "
"invariants. Spikes in one check kind point at a recurring "
"error class to investigate first. **Log-scale Y axis:** "
"the dominant check kind would otherwise swamp the rarer "
"ones — log scale lets you read all 10 at once."
),
category=[ds["check_type"].dim()],
values=[ds["account_id"].count()],
orientation="HORIZONTAL",
category_label="Check Type",
value_label="Open Exceptions (log)",
log_scale=True,
)
# Row 3: detail table — every row is one violation, sorted by
# money-magnitude DESC so the biggest dollar variances surface
# first. AO.4 — magnitude split: ``magnitude_amount`` ($, money
# branches: drift/ledger_drift/overdraft/eod/limit/stuck_*) +
# ``magnitude_count`` (#, transfer-keyed cardinality branches:
# chain_parent_disagreement/xor/fan_in/multi_xor). Exactly one
# populated per row; the other displays as blank — visually
# disambiguating "$1,250.00" (money) from "3" (count).
# Drills: left-click → Drift (back-toward per-invariant source);
# right-click menu → Daily Statement (forward into per-account-day).
amount_col = ds["magnitude_amount"].numerical(currency=True)
count_col = ds["magnitude_count"].numerical()
account_col = ds["account_id"].dim()
business_day_col = ds["business_day"].date()
sheet.layout.row(height=_TABLE_ROW_SPAN).add_table(
width=_FULL,
title="Exception Detail",
subtitle=(
"Every violation in the picker's date window. Sorted by "
"dollar magnitude (largest first) so the biggest variances "
"are the top rows. Transfer-keyed checks (chain / XOR / "
"fan-in) carry a count instead of an amount and sort below. "
"Left-click an account_id to narrow Drift to that account; "
"right-click → View Daily Statement to open the per-"
"account-day walk."
),
columns=[
ds["check_type"].dim(),
account_col,
ds["account_name"].dim(),
ds["account_role"].dim(),
ds["account_parent_role"].dim(),
business_day_col,
ds["rail_name"].dim(),
amount_col,
count_col,
],
sort_by=(amount_col, "DESC"),
actions=[
_l1_drill(
target_sheet=drift_sheet,
name="Narrow Drift to this account",
writes=[(_DP_FILTER_ACCOUNT, account_col)],
trigger="DATA_POINT_CLICK",
),
_l1_drill(
target_sheet=daily_statement_sheet,
name="View Daily Statement for this account-day",
writes=[
(_DP_DS_ACCOUNT, account_col),
(_DP_DS_BALANCE_DATE, business_day_col),
],
trigger="DATA_POINT_MENU",
),
],
conditional_formatting=[
CellAccentText(on=account_col, color=accent),
],
)
# Row 4: L2-description footer — the institution's "what we are"
# prose. Mirrors the Getting Started welcome at the bottom of the
# unified-view landing page.
footer_body = (
l2_instance.description
if l2_instance.description
else "(L2 instance description missing — fill the top-level "
"`description` field in the L2 YAML.)"
)
sheet.layout.row(height=6).add_text_box(
TextBox(
text_box_id="l1-te-l2-footer",
content=rt.text_box(
rt.subheading("Institution Context", color=accent),
rt.BR,
rt.markdown(footer_body),
),
),
width=_FULL,
)
def _populate_limit_breach_sheet(
cfg: Config,
sheet: Sheet,
*,
datasets: dict[str, Dataset],
l2_instance: L2Instance,
daily_statement_sheet: Sheet,
theme: ThemePreset,
) -> None:
"""Limit Breach sheet — KPI + per-(account, day, type) breach table.
Single dataset (`<prefix>_limit_breach`). Each row is one cell where
cumulative outbound debit on that (account, day, rail_name)
exceeded the L2-configured cap. The cap column lives next to the
outbound_total so analysts can read both numbers at once. Right-click
any row → Daily Statement for that account-day (M.2b.7).
M.2a.7: top-of-sheet TextBox enumerates the L2 LimitSchedules
(parent_role × rail_name → cap, plus L2-supplied prose) so
analysts see "what's configured" before "what got breached" —
description-driven, not hardcoded.
"""
accent = theme.accent
ds_lb = datasets[DS_LIMIT_BREACH]
# BO.11 — count KPI is now the TOP row so an operator landing on
# this sheet sees the answer ("how many breaches?") before the
# reference material. Pre-BO.11 the KPI sat under the Configured
# Caps TextBox; the cold-read author scrolled past it and reported
# the sheet as "no top-line KPI" — the placement, not the absence,
# was the bug.
sheet.layout.row(height=_KPI_ROW_SPAN).add_kpi(
width=_FULL,
title="Breaches in Window",
subtitle=(
"Count of (account, day, rail_name, direction) cells where "
"the flow on the breaching side exceeded the L2-configured "
"cap, across the visible date range. **Zero** = no rule "
"violations in the window — the unambiguous healthy state. "
"If the limit_breach matview hasn't refreshed since the "
"last ETL load, the App Info sheet's matview-status table "
"shows the lag — a stale matview can also read zero."
),
values=[ds_lb["account_id"].count()],
)
sheet.layout.row(height=8).add_text_box(
TextBox(
text_box_id="l1-lb-config",
content=rt.text_box(
rt.subheading("Configured Caps", color=accent),
rt.BR,
rt.markdown(
"Outbound debit caps from the L2 instance's "
"LimitSchedules — these are the thresholds the "
"view below compares against:"
),
rt.bullets(_l2_limit_schedule_lines(l2_instance)),
),
),
width=_FULL,
)
account_col = ds_lb["account_id"].dim()
day_col = ds_lb["business_day"].date()
sheet.layout.row(height=_TABLE_ROW_SPAN).add_table(
width=_FULL,
title="Limit Breach Detail",
subtitle=(
"Each (account, day, rail_name, direction) cell where "
"flow > cap. `direction` is Outbound (classic per-rail send "
"cap) or Inbound (AML / structuring threshold on inbound "
"volume — AB.1). `outbound_total` (totals on the breaching "
"side) and `cap` shown side-by-side so the magnitude of "
"the breach is readable in-line. Right-click any row → "
"View Daily Statement."
),
columns=[
account_col,
ds_lb["account_name"].dim(),
ds_lb["account_role"].dim(),
ds_lb["account_parent_role"].dim(),
day_col,
ds_lb["rail_name"].dim(),
# AB.1 — per-direction cap. Outbound = Debit flow exceeds
# the cap; Inbound = Credit flow (typical AML threshold).
ds_lb["direction"].dim(),
ds_lb["outbound_total"].numerical(currency=True),
ds_lb["cap"].numerical(currency=True),
],
actions=[
_l1_drill(
target_sheet=daily_statement_sheet,
name="View Daily Statement for this account-day",
writes=[
(_DP_DS_ACCOUNT, account_col),
(_DP_DS_BALANCE_DATE, day_col),
],
trigger="DATA_POINT_MENU",
),
],
conditional_formatting=[
CellAccentText(on=account_col, color=accent),
],
)
# Aging-bucket helper. Number-prefixed labels keep the QS horizontal
# bar chart sorted correctly without an explicit sort_by override.
# Mirrors AR's aging-bucket convention.
def _populate_pending_aging_sheet(
cfg: Config,
analysis: Analysis,
sheet: Sheet,
*,
datasets: dict[str, Dataset],
transactions_sheet: Sheet,
theme: ThemePreset,
) -> None:
"""Pending Aging sheet — KPI + horizontal aging BarChart + detail.
Backed by the M.2b.8 `<prefix>_stuck_pending` matview. Aging
buckets come from a CASE column in the dataset SQL (5 bands;
number-prefixed labels keep the bar chart sort stable; X.2.u.4.c).
Right-click any detail-table row → Transactions narrowed to that
transfer (M.2b.7 drill plumbing).
"""
accent = theme.accent
ds = datasets[DS_STUCK_PENDING]
# X.2.u.4.c — aging bucket is now a CASE column in the dataset SQL
# ('stuck_pending_aging_bucket'), so App2's column-only fetcher
# renders it; the BarChart category + detail-table column read it.
aging_bucket = ds["stuck_pending_aging_bucket"]
# Row 1: total stuck count KPI.
sheet.layout.row(height=_KPI_ROW_SPAN).add_kpi(
width=_FULL,
title="Stuck Pending",
subtitle=(
"Count of Pending transactions whose live age has exceeded "
"their rail's `max_pending_age` cap. Healthy = 0."
),
values=[ds["transaction_id"].count()],
)
# Row 2: horizontal aging bar chart — count per bucket, stacked
# by rail_name (AB.3.8 — per-variant rollup). For
# XOR-grouped multi-Variable templates this segments the stuck
# population by which variant fired: ``SettlementAuto`` /
# ``SettlementStandard`` / ``SettlementSlow`` each become a color
# band so analysts can see "the slow variant is dragging".
# Single-rail rows still render cleanly (one color per bucket).
sheet.layout.row(height=_CHART_ROW_SPAN).add_bar_chart(
width=_FULL,
title="Stuck Pending by Age Bucket",
category_label="Age Bucket",
value_label="Transactions",
color_label="Rail",
subtitle=(
"Distribution of stuck-Pending transactions across 5 age "
"bands, stacked by rail. Right-skewed (>3d, >7d) ⇒ slow "
"drift; spike at 0-6h ⇒ a recent batch failed to post. "
"Color bands surface per-variant rollup for "
"XOR-grouped multi-mode templates."
),
category=[aging_bucket.dim()],
values=[ds["transaction_id"].count()],
colors=[ds["rail_name"].dim()],
bars_arrangement="STACKED",
orientation="HORIZONTAL",
)
# Row 3: detail table — every stuck-Pending leg, drillable to
# Transactions for that transfer.
transfer_col = ds["transfer_id"].dim()
sheet.layout.row(height=_TABLE_ROW_SPAN).add_table(
width=_FULL,
title="Stuck Pending Detail",
subtitle=(
"Every stuck-Pending leg with rail / amount / posting / "
"live age. `max_pending_age_seconds` is the rail's cap "
"(inlined at view-emit time from L2). Right-click any "
"row → View Transactions to see every leg of that transfer."
),
columns=[
ds["account_id"].dim(),
ds["account_name"].dim(),
transfer_col,
ds["rail_name"].dim(),
ds["amount_money"].numerical(currency=True),
ds["amount_direction"].dim(),
ds["posting"].date(),
aging_bucket.dim(),
ds["max_pending_age_seconds"].numerical(),
ds["age_seconds"].numerical(),
],
actions=[
_l1_drill(
target_sheet=transactions_sheet,
name="View Transactions for this transfer",
# v8.5.7 — widen the destination's universal date
# filter on drill so a stuck-pending row older than
# the picker's default 7-day window still surfaces in
# Transactions. See ``_wide_date_writes`` for why.
writes=[
(_DP_TX_TRANSFER, transfer_col),
*_wide_date_writes(),
],
trigger="DATA_POINT_MENU",
),
],
conditional_formatting=[
CellAccentText(on=transfer_col, color=accent),
],
)
def _populate_unbundled_aging_sheet(
cfg: Config,
analysis: Analysis,
sheet: Sheet,
*,
datasets: dict[str, Dataset],
transactions_sheet: Sheet,
theme: ThemePreset,
) -> None:
"""Unbundled Aging sheet — KPI + horizontal aging BarChart + detail.
Mirror of `_populate_pending_aging_sheet` but backed by
`<prefix>_stuck_unbundled` and using the `_UNBUNDLED_AGING_BUCKETS`
bucket cadence (4 bands sized for the typical 1-2 day
`max_unbundled_age` configuration).
"""
accent = theme.accent
ds = datasets[DS_STUCK_UNBUNDLED]
aging_bucket = ds["stuck_unbundled_aging_bucket"] # X.2.u.4.c — dataset-SQL CASE col
# AO.9 — KPI row pairs the leg-count with total $ exposure so the
# cold-read judge sees both how many AND how much. "802 stuck legs"
# alone leaves the dollar magnitude ambiguous; the paired SUM(amount)
# makes the reconciliation gap dimensional.
unbundled_kpi_row = sheet.layout.row(height=_KPI_ROW_SPAN)
unbundled_kpi_row.add_kpi(
width=_HALF,
title="Stuck Unbundled",
subtitle=(
"Count of Posted transactions whose `bundle_id` is still "
"NULL past their rail's `max_unbundled_age` cap. Healthy = 0. "
# C19 (cold-read v11.26.1) — bundled demo seeds plant
# stuck-unbundled rows to exercise this surface, so a
# non-zero count here on a demo deploy is **expected**, not
# a conservation issue. The dollar exposure beside it is
# tiny by design; real production deploys without planted
# demos should read 0 here.
"**Demo-seed note:** the bundled demo plants stuck-"
"unbundled rows so this view has something to show — "
"expect a non-zero count on demo deploys."
),
values=[ds["transaction_id"].count()],
)
unbundled_kpi_row.add_kpi(
width=_HALF,
# BH.20 (2026-05-25) — title was "Stuck Unbundled — $ Exposure";
# the "$ Exposure" suffix read as a concatenated literal label
# (cold-read finding: "Stuck Unbundled = $ Exposure 490,826").
# Reframing as "Stuck Unbundled Exposure" — the value below is
# already $-prefixed by currency=True format, so the title
# doesn't need its own dollar glyph.
title="Stuck Unbundled Exposure",
subtitle=(
"Sum of amount across the stuck-unbundled legs. The dollar "
"side of the reconciliation gap — how much money is sitting "
"unrolled-up past its rail's bundling cap."
),
values=[ds["amount_money"].sum(currency=True)],
)
# AB.3.8 — stacked by rail_name for per-variant rollup
# (mirrors `_populate_pending_aging_sheet`'s shape).
sheet.layout.row(height=_CHART_ROW_SPAN).add_bar_chart(
width=_FULL,
title="Stuck Unbundled by Age Bucket",
category_label="Age Bucket",
value_label="Transactions",
color_label="Rail",
subtitle=(
"Distribution of stuck-Unbundled transactions across 4 age "
"bands, stacked by rail. Right-skewed (>2d, >7d) ⇒ the "
"bundler hasn't fired for those rails in a while. Color "
"bands surface per-variant rollup for XOR-grouped "
"multi-mode templates."
),
category=[aging_bucket.dim()],
values=[ds["transaction_id"].count()],
colors=[ds["rail_name"].dim()],
bars_arrangement="STACKED",
orientation="HORIZONTAL",
)
transfer_col = ds["transfer_id"].dim()
sheet.layout.row(height=_TABLE_ROW_SPAN).add_table(
width=_FULL,
title="Stuck Unbundled Detail",
subtitle=(
"Every stuck-Unbundled leg with rail / amount / posting / "
"live age. `max_unbundled_age_seconds` is the rail's cap "
"(inlined at view-emit time from L2). Right-click any "
"row → View Transactions to see every leg of that transfer."
),
columns=[
ds["account_id"].dim(),
ds["account_name"].dim(),
transfer_col,
ds["rail_name"].dim(),
ds["amount_money"].numerical(currency=True),
ds["amount_direction"].dim(),
ds["posting"].date(),
aging_bucket.dim(),
ds["max_unbundled_age_seconds"].numerical(),
ds["age_seconds"].numerical(),
],
actions=[
_l1_drill(
target_sheet=transactions_sheet,
name="View Transactions for this transfer",
# v8.5.7 — widen the destination's universal date
# filter on drill (mirror of Pending Aging).
writes=[
(_DP_TX_TRANSFER, transfer_col),
*_wide_date_writes(),
],
trigger="DATA_POINT_MENU",
),
],
conditional_formatting=[
CellAccentText(on=transfer_col, color=accent),
],
)
def _populate_supersession_audit_sheet(
cfg: Config,
analysis: Analysis,
sheet: Sheet,
*,
datasets: dict[str, Dataset],
theme: ThemePreset,
) -> None:
"""Supersession Audit sheet — 2 KPIs + 2 detail tables.
Both detail tables read from BASE tables (not Current*), filtered
to only logical keys with multiple `entry` versions. The audit
trail is sorted top-down per logical row so the analyst can read
what changed across re-postings.
KPIs: (1) count of distinct logical keys in the transactions audit
(not row count — one entity can have N entries; we want the entity
count); (2) count of higher-Entry rows whose `supersedes` reason is
blank (target value = 0 — every supersession should declare its
cause per the L1 SPEC).
`supersedes` filter dropdown applies to the transactions table
(the daily-balances superceding pattern is so rare in practice
that adding a second filter would be noise).
"""
accent = theme.accent
ds_tx = datasets[DS_SUPERSESSION_TRANSACTIONS]
ds_db = datasets[DS_SUPERSESSION_DAILY_BALANCES]
# Row 1: three KPIs — supersession count on the left, $ exposure in
# the middle (AO.9 — the dollar side of the audit; count alone left
# the cold-read judge without a magnitude anchor), no-reason
# policy-violation count on the right.
kpi_row = sheet.layout.row(height=_KPI_ROW_SPAN)
kpi_row.add_kpi(
width=_THIRD,
# C14 (cold-read v11.26.1) — title clarified to make the
# "keys, not rows" unit explicit. Sibling KPI on the right
# counts ROWS (higher-entry rows with blank reason), so a
# naive side-by-side read on the original titles flagged them
# as contradictory. They aren't — they measure different
# populations against different units.
title="Logical Keys (Transactions) with Supersession",
subtitle=(
"Count of distinct transaction_id values (logical keys) "
"whose append-only `entry` column has more than one row. "
"**Unit: distinct keys.** The 'no-reason' KPI to the right "
"counts ROWS, not keys — a key may have multiple "
"no-reason rows. Healthy demos may be 0; production "
"workloads typically have a small steady trickle of "
"TechnicalCorrection / BundleAssignment events."
),
values=[ds_tx["transaction_id"].distinct_count()],
)
kpi_row.add_kpi(
width=_THIRD,
title="Supersession $ Exposure",
subtitle=(
"Sum of |amount| across superseded transaction entries — "
"the dollar magnitude of the audit surface. Counts alone "
"leave the size question open; this is the answer to "
"\"how much money do these revisions move?\""
),
values=[ds_tx["amount_money"].sum(currency=True)],
)
kpi_row.add_kpi(
width=_THIRD,
# C14 (cold-read v11.26.1) — title clarified to match the
# "rows, not keys" unit. See "Logical Keys" KPI on the left for
# the cross-reference.
title="Supersession Rows with No Reason",
subtitle=(
"Count of higher-Entry rows whose `supersedes` reason is "
"blank. **Unit: rows, not distinct keys** — one logical "
"key may have multiple no-reason rows. Target value = 0; "
"every supersession SHOULD declare its cause (Inflight / "
"BundleAssignment / TechnicalCorrection) per the L1 SPEC. "
"Drill into the detail table below to see which rows lack "
"a reason."
),
values=[ds_tx["l1_supersession_no_reason"].sum()],
)
# Row 2: transactions audit detail — every entry of every
# superseded logical row, sorted by (transaction_id, entry).
tx_id_col = ds_tx["transaction_id"].dim()
sheet.layout.row(height=_TABLE_ROW_SPAN).add_table(
width=_FULL,
title="Transactions Audit",
subtitle=(
"Every entry of every logical transaction with >1 entry. "
"The `supersedes` column on the higher-entry row tells you "
"why it exists. Use the supersedes filter (Inflight / "
"BundleAssignment / TechnicalCorrection) to narrow the "
"audit to one cause class."
),
columns=[
ds_tx["entry"].numerical(),
tx_id_col,
ds_tx["supersedes"].dim(),
ds_tx["account_id"].dim(),
ds_tx["account_name"].dim(),
ds_tx["transfer_id"].dim(),
ds_tx["rail_name"].dim(),
ds_tx["amount_money"].numerical(currency=True),
ds_tx["amount_direction"].dim(),
ds_tx["status"].dim(),
ds_tx["posting"].date(),
ds_tx["bundle_id"].dim(),
],
conditional_formatting=[
CellAccentText(on=tx_id_col, color=accent),
],
)
# Row 3: daily-balances audit detail — every entry of every
# superseded (account_id, business_day_start) cell.
db_account_col = ds_db["account_id"].dim()
sheet.layout.row(height=_TABLE_ROW_SPAN).add_table(
width=_FULL,
title="Daily Balances Audit",
subtitle=(
"Every entry of every (account, business_day) cell with "
"more than one stored value. The `money` column changing "
"across entries is the audit trail for an end-of-day "
"re-statement."
),
columns=[
ds_db["entry"].numerical(),
db_account_col,
ds_db["account_name"].dim(),
ds_db["account_role"].dim(),
ds_db["supersedes"].dim(),
ds_db["business_day_start"].date(),
ds_db["business_day_end"].date(),
ds_db["money"].numerical(currency=True),
],
conditional_formatting=[
CellAccentText(on=db_account_col, color=accent),
],
)
def _populate_transactions_sheet(
cfg: Config,
sheet: Sheet,
*,
datasets: dict[str, Dataset],
theme: ThemePreset,
) -> None:
"""Transactions sheet — single detail table over the per-leg ledger.
No KPIs above the table — the value of this sheet is "show me every
leg + filter to the slice I care about." Filter dropdowns (wired in
`_wire_per_sheet_dropdowns`) cover account / transfer / status /
origin / rail_name. M.2b.2 link tint on `account_id` +
`transfer_id` cues the M.2b.7 drill plumbing.
"""
accent = theme.accent
ds_tx = datasets[DS_TRANSACTIONS]
account_col = ds_tx["account_id"].dim()
transfer_col = ds_tx["transfer_id"].dim()
posting_col = ds_tx["posting"].date()
sheet.layout.row(height=_TABLE_ROW_SPAN).add_table(
width=_FULL,
title="Posting Ledger",
subtitle=(
"Every Money record (leg) in the L2 instance's current "
"view — supersession-aware, so replaced entries don't "
"show. Sorted by posting time DESC so the most recent "
"activity is at the top."
),
columns=[
account_col,
ds_tx["account_name"].dim(),
ds_tx["account_role"].dim(),
transfer_col,
ds_tx["rail_name"].dim(),
ds_tx["amount_money"].numerical(currency=True),
ds_tx["amount_direction"].dim(),
ds_tx["status"].dim(),
ds_tx["origin"].dim(),
posting_col,
],
sort_by=(posting_col, "DESC"),
conditional_formatting=[
CellAccentText(on=account_col, color=accent),
CellAccentText(on=transfer_col, color=accent),
],
)
def _populate_daily_statement_sheet(
cfg: Config,
sheet: Sheet,
*,
datasets: dict[str, Dataset],
transactions_sheet: Sheet,
theme: ThemePreset,
) -> None:
"""Daily Statement — 5 KPIs across the day's walk + detail table.
KPIs read the summary dataset (one row per account-day after sheet
filters narrow). Detail table reads the per-leg transactions
dataset. Both filtered by the M.2b.4 sheet-local
(P_L1_DS_ACCOUNT, P_L1_DS_BALANCE_DATE) parameters via the filter
groups wired in `_wire_daily_statement_filters`.
"""
ds_summary = datasets[DS_DAILY_STATEMENT_SUMMARY]
ds_txn = datasets[DS_DAILY_STATEMENT_TRANSACTIONS]
ds_ds_accounts = datasets[DS_L1_DS_ACCOUNTS]
ds_ds_roles = datasets[DS_L1_DS_ROLES]
# BR.x — context KPIs (Accounts available / Roles available). Two
# purposes: (1) operator-facing — surfaces how many accounts the
# current Role pick narrows to; (2) plumbing — registers
# ``l1-ds-accounts-ds`` + ``l1-ds-roles-ds`` as configured datasets
# on this sheet, which QS REQUIRES for the
# ``CascadingControlConfiguration`` re-fetch to fire on Role pick.
# Without a visual binding, the Account control's source dataset
# is "linked but inactive" — Role pick doesn't trigger a re-fetch
# and the Account dropdown shows the initial-load universe forever.
# See docs/reference/quicksight-quirks.md — cascade source dataset
# must be visual-bound entry.
ctx_row = sheet.layout.row(height=_KPI_ROW_SPAN)
ctx_row.add_kpi(
width=18,
title="Accounts available",
subtitle=(
"Distinct accounts in today's balance feed that match the "
"picked Role (Role pick narrows this KPI via SQL pushdown). "
"**Renderer divergence:** in App2 the Account dropdown "
"narrows to the same set; on QuickSight the dropdown stays "
"showing the full universe because QS's unique-values "
"endpoint can't execute parameterized datasets — see the "
"sheet description note."
),
values=[ds_ds_accounts["account_id"].count(
field_id="ds-ctx-account-count",
)],
)
ctx_row.add_kpi(
width=18,
title="Roles available",
subtitle=(
"Distinct account roles in today's balance feed. The Role "
"dropdown above lists exactly these."
),
values=[ds_ds_roles["account_role"].count(
field_id="ds-ctx-role-count",
)],
)
# Row 1: 5 KPIs at width 7 each (sums to 35 of 36 grid cols; 1
# column slack on the right).
kpi_width = 7
kpi_row = sheet.layout.row(height=_KPI_ROW_SPAN)
kpi_row.add_kpi(
width=kpi_width,
title="Opening Balance",
subtitle="End-of-prior-day stored balance for the picked account.",
values=[ds_summary["opening_balance"].max(currency=True)],
)
kpi_row.add_kpi(
width=kpi_width,
title="Debits (signed)",
subtitle=(
"Sum of `amount_money` over Debit-direction legs posted "
"today, signed by v6 convention (Debit = negative). "
"**Closing = Opening + Credits + Debits** (the signs already "
"carry direction; do NOT subtract)."
),
values=[ds_summary["total_debits"].max(currency=True)],
)
kpi_row.add_kpi(
width=kpi_width,
title="Credits (signed)",
subtitle=(
"Sum of `amount_money` over Credit-direction legs posted "
"today, signed by v6 convention (Credit = positive). See "
"Debits subtitle for the formula."
),
values=[ds_summary["total_credits"].max(currency=True)],
)
kpi_row.add_kpi(
width=kpi_width,
title="Closing Stored",
subtitle="The day's stored closing balance from the feed.",
values=[ds_summary["closing_balance_stored"].max(currency=True)],
)
kpi_row.add_kpi(
width=kpi_width,
title="Posting Drift",
subtitle=(
"Closing stored − (opening + signed-net flow) for THIS "
"account-day. Non-zero ⇒ today's postings don't reconcile "
"with today's stored balance change. Distinct from the "
"Drift sheet's leaf/parent drift (stored vs cumulative "
"computed; whole-history rather than per-day) and from "
"parent aggregation drift (parent stored vs Σ child "
"stored). The ✓/✗ glyph next to the number is the "
"accessible state signal — green ✓ when drift = $0 "
"(healthy reconciliation), red ✗ otherwise."
),
values=[ds_summary["drift"].max(currency=True)],
value_zero_indicator=KPIValueZeroIndicator(),
)
# Row 2: detail table — every Money record posted that day for the
# picked account, after sheet filters narrow. Right-click any row →
# Transactions narrowed to that transfer_id (every leg of the
# multi-leg transfer the clicked row is part of).
accent = theme.accent
transfer_col = ds_txn["transfer_id"].dim()
sheet.layout.row(height=_TABLE_ROW_SPAN).add_table(
width=_FULL,
title="Posted Money Records",
subtitle=(
"Every leg posted on the picked account-day. Direction "
"shows Debit / Credit; status filters out Failed legs in "
"the summary KPIs but not here. Right-click any row → "
"View Transactions to see every leg of that transfer."
),
columns=[
ds_txn["transaction_id"].dim(),
transfer_col,
ds_txn["rail_name"].dim(),
ds_txn["amount_money"].numerical(currency=True),
ds_txn["amount_direction"].dim(),
ds_txn["status"].dim(),
ds_txn["origin"].dim(),
ds_txn["posting"].date(),
],
actions=[
_l1_drill(
target_sheet=transactions_sheet,
name="View Transactions for this transfer",
# v8.5.7 — widen the destination's universal date
# filter on drill (mirror of Pending Aging).
writes=[
(_DP_TX_TRANSFER, transfer_col),
*_wide_date_writes(),
],
trigger="DATA_POINT_MENU",
),
],
conditional_formatting=[
CellAccentText(on=transfer_col, color=accent),
],
)
# -- M.2b.1: Universal date-range filter -------------------------------------
#
# Two analysis-level DateTimeParams drive a per-dataset FilterGroup family:
# every data-bearing sheet has paired date-time picker controls bound to the
# same params, so changing the date range on one sheet propagates to all.
# Per-dataset FilterGroups (rather than a single ALL_DATASETS group) because
# the L1 invariant views don't share a single date column name — daily-balance
# views expose `business_day_start`, while limit_breach + l1_exceptions
# expose `business_day` (DATE_TRUNC of posting). Per-dataset binding sidesteps
# the column-name mismatch without a schema migration.
# AR.4 — the per-app RollingDate exprs (pre-AR.4: "last 7 days off now")
# are gone; the universal range is a 7-day DateView constructed from
# cfg.test_generator.as_of_frame(). Strict-collapse, same as AR.2's
# balance-date — bake at deploy, no wall-clock drift between deploys,
# no disagreement with the dataset side.
def _wire_date_range_filter(
analysis: Analysis,
*,
datasets: dict[str, Dataset],
drift_sheet: Sheet,
drift_timelines_sheet: Sheet,
overdraft_sheet: Sheet,
limit_breach_sheet: Sheet,
pending_aging_sheet: Sheet,
unbundled_aging_sheet: Sheet,
supersession_audit_sheet: Sheet,
l1_exceptions_sheet: Sheet,
transactions_sheet: Sheet,
universal_range_view: DateView,
) -> None:
"""Wire the universal date-range filter (params + bridges + controls).
Phase BM — the per-dataset ``TimeRangeFilter`` FilterGroups dissolved
in favor of dataset-SQL pushdown via ``<<$pL1DateStart>>`` /
``<<$pL1DateEnd>>`` (see ``datasets.py::_l1_universal_range_params``).
This wire now declares only:
1. Two analysis-level ``DateTimeParam``s with ``mapped_dataset_params``
bridging each of the 8 data-bearing datasets' ``pL1DateStart`` /
``pL1DateEnd`` dataset params.
2. Paired ``ParameterDateTimePicker`` controls on every data-bearing
sheet so the analyst sets the window once and it propagates.
Pre-BM dual-SQL ``{date_filter}`` template + analysis-level
``TimeRangeFilter`` are gone; one SQL form across renderers; the
day-edge quirk dissolves by construction.
"""
# Phase BM — the 8 data-bearing datasets the universal date range
# narrows (current-state sheets — pending_aging, unbundled_aging,
# supersession_audit — intentionally skip; "stuck" is stuck until
# cleared regardless of the analyst's period of interest, and
# adding a date filter here would diverge from the audit PDF /
# break U.8.b's three-way agreement contract).
date_scoped_datasets = (
DS_DRIFT, DS_LEDGER_DRIFT,
DS_DRIFT_TIMELINE, DS_LEDGER_DRIFT_TIMELINE,
DS_OVERDRAFT,
DS_LIMIT_BREACH, DS_L1_EXCEPTIONS,
DS_TRANSACTIONS,
)
start_bridges = [
(datasets[k], str(P_L1_DATE_START)) for k in date_scoped_datasets
]
end_bridges = [
(datasets[k], str(P_L1_DATE_END)) for k in date_scoped_datasets
]
date_start = analysis.add_parameter(DateTimeParam(
name=P_L1_DATE_START,
time_granularity="DAY",
default=universal_range_view.emit_qs_analysis_default_start(),
mapped_dataset_params=start_bridges,
))
date_end = analysis.add_parameter(DateTimeParam(
name=P_L1_DATE_END,
time_granularity="DAY",
default=universal_range_view.emit_qs_analysis_default_end(),
mapped_dataset_params=end_bridges,
))
# Per-sheet date pickers — bound to the shared params so every
# date-scoped sheet's pickers sync. The current-state sheets
# (pending_aging, unbundled_aging, supersession_audit) are
# intentionally absent — see date_scoped_datasets note above.
for sheet in (
drift_sheet, drift_timelines_sheet, overdraft_sheet,
limit_breach_sheet, l1_exceptions_sheet,
transactions_sheet,
):
sheet.add_parameter_datetime_picker(
parameter=date_start, title="Date From",
)
sheet.add_parameter_datetime_picker(
parameter=date_end, title="Date To",
)
def _populate_pushdown_enum_dropdown(
*,
sheet: Sheet,
analysis: Analysis,
bridges: list[tuple[Dataset, str]],
param_name: ParameterName,
title: str,
all_values: list[str],
) -> None:
"""Y.2.g + AA.A.3 — single-select dropdown whose narrowing pushes
into the consuming dataset(s)' SQL via ``<<$dataset_param>>``
substitution (a ``col = <<$p>>`` predicate guarded by the sentinel-OR
shape — see ``datasets.py::_data_value_clause``). Mirrors
``apps/l2_flow_tracing/app.py::_populate_pushdown_dropdown``.
A single-valued ``StringParam`` whose default is ``L1_ALL_SENTINEL``
(so a freshly-loaded dashboard matches every row via the sentinel
disjunct) is bridged to each ``(dataset, dataset_param)`` pair —
usually one; ALL_DATASETS dropdowns pass two (the Drift / Drift
Timelines sheets' controls narrow both the leaf-drift and
ledger-drift datasets). A ``ParameterDropdown(SINGLE_SELECT,
StaticValues)`` lets the analyst pick one value to narrow with one
click. No analysis-level FilterGroup — picking the value writes the
bridged dataset param; clearing the dropdown reverts to the sentinel
default (= all rows match). AA.A.3 flipped this from MULTI to SINGLE
per the drill-to-one default (audit at
``docs/audits/aa_a_dropdown_audit.md``).
Use for bounded enum columns (``rail_name`` / ``account_role`` /
``check_type`` / ``supersedes``); for data-value columns use
``_populate_pushdown_value_dropdown``.
"""
p = analysis.add_parameter(StringParam(
name=param_name,
multi_valued=False,
default=[L1_ALL_SENTINEL],
mapped_dataset_params=list(bridges),
))
sheet.add_parameter_dropdown(
parameter=p,
title=title,
selectable_values=StaticValues(values=list(all_values)),
)
def _populate_pushdown_value_dropdown(
*,
sheet: Sheet,
analysis: Analysis,
bridges: list[tuple[Dataset, str]],
param_name: ParameterName,
title: str,
options_dataset: Dataset,
options_column: str,
) -> None:
"""Y.2.g + AA.A.3 — like ``_populate_pushdown_enum_dropdown`` but for
data-value columns (``account_id`` / ``transfer_id`` / open-set
``status`` / ``origin``) whose value universe isn't enumerable at
deploy time.
The single-valued analysis ``StringParam`` defaults to ``L1_ALL_SENTINEL``;
the bridged dataset param's static default is the same sentinel and the
consuming SQL guards ``('__l1_all__' = <<$p>> OR col = <<$p>>)`` (see
``datasets.py::_data_value_clause``), so a freshly-loaded dashboard
(bridge un-fired → dataset param at its static default) matches every
row, and clearing the dropdown (which reverts the dataset param to that
default) restores "all". The dropdown's options come from
``options_dataset[options_column]`` via ``LinkedValues`` — a well-formed
``SELECT DISTINCT`` query, not the lazy ``tenK-sample-values-V2`` fetch
the old empty-CategoryFilter pattern triggered (the X.1.g cold-CI 404
source). AA.A.3 flipped from MULTI to SINGLE per the drill-to-one default.
"""
p = analysis.add_parameter(StringParam(
name=param_name,
multi_valued=False,
default=[L1_ALL_SENTINEL],
mapped_dataset_params=list(bridges),
))
sheet.add_parameter_dropdown(
parameter=p,
title=title,
selectable_values=LinkedValues.from_column(
options_dataset[options_column],
),
)
def _wire_per_sheet_dropdowns(
analysis: Analysis,
*,
datasets: dict[str, Dataset],
l2_instance: L2Instance,
drift_sheet: Sheet,
drift_timelines_sheet: Sheet,
overdraft_sheet: Sheet,
limit_breach_sheet: Sheet,
pending_aging_sheet: Sheet,
unbundled_aging_sheet: Sheet,
supersession_audit_sheet: Sheet,
l1_exceptions_sheet: Sheet,
transactions_sheet: Sheet,
) -> None:
"""Y.2.g + AA.A.3 — per-sheet filter dropdowns, all pushed into
dataset SQL.
Replaces the M.2b.3 ``CategoryFilter.with_values(values=[],
FILTER_ALL_VALUES)`` per-sheet dropdowns (the X.1.g cold-fetch
footgun — those lazy-fetch the column's distinct values from QS's
``tenK-sample-values-V2`` endpoint, which 404s on cold per-CI-run
dashboards). Each dropdown is now a parameter-backed SINGLE_SELECT
(AA.A.3 — was MULTI_SELECT pre-flip; the drill-to-one default
collapsed the sentinel-IN-list guard into a scalar ``=`` form)
bridged to a dataset parameter substituted into the dataset's
CustomSql, so QS does the narrowing in the database — no
analysis-level FilterGroup, no lazy fetch. Bounded enum columns use
``StaticValues``; data-value columns (account_id / transfer_id /
status / origin) use ``LinkedValues`` against a small companion
dataset (``DS_L1_ACCOUNTS`` / ``DS_L1_TX_IDS`` / ``DS_L1_TX_FACETS``).
The ALL_DATASETS Drift / Drift-Timelines dropdowns bridge to both of
their respective datasets.
"""
ds_drift = datasets[DS_DRIFT]
ds_ledger_drift = datasets[DS_LEDGER_DRIFT]
ds_drift_tl = datasets[DS_DRIFT_TIMELINE]
ds_ledger_drift_tl = datasets[DS_LEDGER_DRIFT_TIMELINE]
ds_overdraft = datasets[DS_OVERDRAFT]
ds_lb = datasets[DS_LIMIT_BREACH]
ds_sp = datasets[DS_STUCK_PENDING]
ds_su = datasets[DS_STUCK_UNBUNDLED]
ds_sa_tx = datasets[DS_SUPERSESSION_TRANSACTIONS]
ds_te = datasets[DS_L1_EXCEPTIONS]
ds_tx = datasets[DS_TRANSACTIONS]
ds_accounts = datasets[DS_L1_ACCOUNTS]
ds_tx_ids = datasets[DS_L1_TX_IDS]
ds_tx_facets = datasets[DS_L1_TX_FACETS]
role_values = l1_account_role_values(l2_instance)
type_values = l1_rail_universe_values(l2_instance)
rail_values = l1_rail_values(l2_instance)
# --- Drift sheet — Account (data-value) + Account Role (enum),
# both narrowing leaf-drift + ledger-drift together.
_populate_pushdown_value_dropdown(
sheet=drift_sheet, analysis=analysis,
bridges=[(ds_drift, P_L1_DRIFT_ACCOUNT),
(ds_ledger_drift, P_L1_DRIFT_ACCOUNT)],
param_name=ParameterName(P_L1_DRIFT_ACCOUNT), title="Account",
options_dataset=ds_accounts, options_column="account_display",
)
_populate_pushdown_enum_dropdown(
sheet=drift_sheet, analysis=analysis,
bridges=[(ds_drift, P_L1_DRIFT_ROLE),
(ds_ledger_drift, P_L1_DRIFT_ROLE)],
param_name=ParameterName(P_L1_DRIFT_ROLE), title="Account Role",
all_values=role_values,
)
# --- Drift Timelines sheet — Account Role (enum), both timeline
# datasets.
_populate_pushdown_enum_dropdown(
sheet=drift_timelines_sheet, analysis=analysis,
bridges=[(ds_drift_tl, P_L1_DRIFT_TL_ROLE),
(ds_ledger_drift_tl, P_L1_DRIFT_TL_ROLE)],
param_name=ParameterName(P_L1_DRIFT_TL_ROLE), title="Account Role",
all_values=role_values,
)
# --- Overdraft sheet — Account (data-value) + Account Role (enum).
_populate_pushdown_value_dropdown(
sheet=overdraft_sheet, analysis=analysis,
bridges=[(ds_overdraft, P_L1_OVERDRAFT_ACCOUNT)],
param_name=ParameterName(P_L1_OVERDRAFT_ACCOUNT), title="Account",
options_dataset=ds_accounts, options_column="account_display",
)
_populate_pushdown_enum_dropdown(
sheet=overdraft_sheet, analysis=analysis,
bridges=[(ds_overdraft, P_L1_OVERDRAFT_ROLE)],
param_name=ParameterName(P_L1_OVERDRAFT_ROLE), title="Account Role",
all_values=role_values,
)
# --- Limit Breach sheet — Account (data-value) + Transfer Type (enum).
_populate_pushdown_value_dropdown(
sheet=limit_breach_sheet, analysis=analysis,
bridges=[(ds_lb, P_L1_LIMIT_BREACH_ACCOUNT)],
param_name=ParameterName(P_L1_LIMIT_BREACH_ACCOUNT), title="Account",
options_dataset=ds_accounts, options_column="account_display",
)
_populate_pushdown_enum_dropdown(
sheet=limit_breach_sheet, analysis=analysis,
bridges=[(ds_lb, P_L1_LIMIT_BREACH_TYPE)],
param_name=ParameterName(P_L1_LIMIT_BREACH_TYPE),
title="Transfer Type", all_values=type_values,
)
# --- Pending Aging sheet — Account (data-value) + Transfer Type +
# Rail (enums).
_populate_pushdown_value_dropdown(
sheet=pending_aging_sheet, analysis=analysis,
bridges=[(ds_sp, P_L1_PENDING_ACCOUNT)],
param_name=ParameterName(P_L1_PENDING_ACCOUNT), title="Account",
options_dataset=ds_accounts, options_column="account_display",
)
_populate_pushdown_enum_dropdown(
sheet=pending_aging_sheet, analysis=analysis,
bridges=[(ds_sp, P_L1_PENDING_TYPE)],
param_name=ParameterName(P_L1_PENDING_TYPE), title="Transfer Type",
all_values=type_values,
)
_populate_pushdown_enum_dropdown(
sheet=pending_aging_sheet, analysis=analysis,
bridges=[(ds_sp, P_L1_PENDING_RAIL)],
param_name=ParameterName(P_L1_PENDING_RAIL), title="Rail",
all_values=rail_values,
)
# --- Unbundled Aging sheet — same three over the stuck_unbundled
# matview.
_populate_pushdown_value_dropdown(
sheet=unbundled_aging_sheet, analysis=analysis,
bridges=[(ds_su, P_L1_UNBUNDLED_ACCOUNT)],
param_name=ParameterName(P_L1_UNBUNDLED_ACCOUNT), title="Account",
options_dataset=ds_accounts, options_column="account_display",
)
_populate_pushdown_enum_dropdown(
sheet=unbundled_aging_sheet, analysis=analysis,
bridges=[(ds_su, P_L1_UNBUNDLED_TYPE)],
param_name=ParameterName(P_L1_UNBUNDLED_TYPE), title="Transfer Type",
all_values=type_values,
)
_populate_pushdown_enum_dropdown(
sheet=unbundled_aging_sheet, analysis=analysis,
bridges=[(ds_su, P_L1_UNBUNDLED_RAIL)],
param_name=ParameterName(P_L1_UNBUNDLED_RAIL), title="Rail",
all_values=rail_values,
)
# --- Supersession Audit sheet — Supersedes Reason (enum, nullable;
# the daily-balances table stays unfiltered — see M.2b.12).
_populate_pushdown_enum_dropdown(
sheet=supersession_audit_sheet, analysis=analysis,
bridges=[(ds_sa_tx, P_L1_SUPERSEDE_REASON)],
param_name=ParameterName(P_L1_SUPERSEDE_REASON),
title="Supersedes Reason", all_values=l1_supersede_reason_values(),
)
# --- L1 Exceptions sheet — Check Type (enum) + Account
# (data-value) + Transfer Type (enum, nullable).
_populate_pushdown_enum_dropdown(
sheet=l1_exceptions_sheet, analysis=analysis,
bridges=[(ds_te, P_L1_TODAYS_EXC_CHECK_TYPE)],
param_name=ParameterName(P_L1_TODAYS_EXC_CHECK_TYPE),
title="Check Type", all_values=l1_check_type_values(),
)
_populate_pushdown_value_dropdown(
sheet=l1_exceptions_sheet, analysis=analysis,
bridges=[(ds_te, P_L1_TODAYS_EXC_ACCOUNT)],
param_name=ParameterName(P_L1_TODAYS_EXC_ACCOUNT), title="Account",
options_dataset=ds_accounts, options_column="account_display",
)
_populate_pushdown_enum_dropdown(
sheet=l1_exceptions_sheet, analysis=analysis,
bridges=[(ds_te, P_L1_TODAYS_EXC_TYPE)],
param_name=ParameterName(P_L1_TODAYS_EXC_TYPE), title="Transfer Type",
all_values=type_values,
)
# --- Transactions sheet — Account / Transfer / Status / Origin
# (data-value; status + origin are open-set in the L1 schema) +
# Transfer Type (enum).
_populate_pushdown_value_dropdown(
sheet=transactions_sheet, analysis=analysis,
bridges=[(ds_tx, P_L1_TX_ACCOUNT)],
param_name=ParameterName(P_L1_TX_ACCOUNT), title="Account",
options_dataset=ds_accounts, options_column="account_display",
)
_populate_pushdown_value_dropdown(
sheet=transactions_sheet, analysis=analysis,
bridges=[(ds_tx, P_L1_TX_TRANSFER_ID)],
param_name=ParameterName(P_L1_TX_TRANSFER_ID), title="Transfer",
options_dataset=ds_tx_ids, options_column="transfer_id",
)
_populate_pushdown_value_dropdown(
sheet=transactions_sheet, analysis=analysis,
bridges=[(ds_tx, P_L1_TX_STATUS)],
param_name=ParameterName(P_L1_TX_STATUS), title="Status",
options_dataset=ds_tx_facets, options_column="status",
)
_populate_pushdown_value_dropdown(
sheet=transactions_sheet, analysis=analysis,
bridges=[(ds_tx, P_L1_TX_ORIGIN)],
param_name=ParameterName(P_L1_TX_ORIGIN), title="Origin",
options_dataset=ds_tx_facets, options_column="origin",
)
_populate_pushdown_enum_dropdown(
sheet=transactions_sheet, analysis=analysis,
bridges=[(ds_tx, P_L1_TX_TYPE)],
param_name=ParameterName(P_L1_TX_TYPE), title="Transfer Type",
all_values=type_values,
)
def _wire_daily_statement_filters(
analysis: Analysis,
*,
datasets: dict[str, Dataset],
daily_statement_sheet: Sheet,
balance_date_view: DateView,
) -> None:
"""M.2b.4 + Y.2.g.9 — wire the Daily Statement sheet's per-account-day
filter.
Two analysis-level parameters drive both the summary dataset and the
transactions dataset:
- **P_L1_DS_ACCOUNT** — Y.2.g.9 pushes this *into* both datasets'
SQL via the single-valued ``pL1DsAccount`` dataset parameter
(``WHERE account_id = <<$pL1DsAccount>>``), so QS does the
per-account narrow in the database rather than via an
analysis-level CategoryFilter. The dropdown's options come from the
``DS_L1_ACCOUNTS`` companion (an unparameterized DISTINCT-accounts
dataset) — not the now-parameterized summary dataset, whose
``SELECT DISTINCT account_id`` would inherit the WHERE and return
nothing. Sentinel default → empty statement until the analyst
picks (no L2-specific account hardcoded).
- **P_L1_DS_BALANCE_DATE** — stays an analysis-level
``TimeEqualityFilter`` on each dataset's day column (Y.2.f date
territory; not pushed down here).
"""
ds_account = analysis.add_parameter(StringParam(
name=P_L1_DS_ACCOUNT,
# BR.x — explicit default matches the dataset's
# ``_L1_DS_ACCOUNT_SENTINEL`` ("__l1_no_account_selected__").
# Without it, the analysis param emits ``DefaultValues: []``
# which makes QS error with "calculated field has invalid
# syntax" on first load (the cascade machinery substitutes the
# param into an internal expression).
default=["__l1_no_account_selected__"],
# BR.x — same reasoning as ``ds_role`` below. The Account
# dropdown is the cascade TARGET (so QS evaluates
# ``account_role = ${pL1DsRole}``) AND uses its own
# ``pL1DsAccount`` for the picked-value-write. QS errors on
# the dropdown if EITHER param emits a NULL reserved value;
# both need explicit ValueWhenUnset.CustomValue matching the
# dataset-side default.
value_when_unset="__l1_no_account_selected__",
mapped_dataset_params=[
(datasets[DS_DAILY_STATEMENT_SUMMARY], P_L1_DS_ACCOUNT_DSP),
(datasets[DS_DAILY_STATEMENT_TRANSACTIONS], P_L1_DS_ACCOUNT_DSP),
],
))
# AA.B.1 — Role cascade. The role dropdown's value bridges into
# the ``DS_L1_DS_ACCOUNTS`` companion's ``pL1DsRole`` dataset
# param, narrowing the Account dropdown's options. Sentinel
# default (``L1_ALL_SENTINEL``) means "show every account
# regardless of role" — preserves the un-picked behaviour
# exactly.
# BO.1 — switched the cascade target from ``DS_L1_ACCOUNTS`` to
# ``DS_L1_DS_ACCOUNTS`` (Daily-Statement-specific, sourced from
# ``<prefix>_current_daily_balances`` only). The wider
# ``DS_L1_ACCOUNTS`` includes Pending-only + spine-planted
# accounts that have no balance row — picking one on Daily
# Statement returns blank KPIs (the v11.23.0 cold-read's
# triple-convergent NEW top blocker). The 7 other L1 sheets
# still bridge through ``DS_L1_ACCOUNTS`` for the wider universe.
# BR.x — bridge + cascade together. Cascade-only is invalid because
# the ``l1-accounts`` dataset SQL has ``<<$pL1DsRole>>`` placeholders
# that REQUIRE ``DataSetParameters`` declaration via the bridge — without
# it, fetch-time substitution fails with "calculated field has invalid
# syntax". The bridge handles the DB-side WHERE filter; cascade is the
# QS-side UI narrowing.
ds_role = analysis.add_parameter(StringParam(
name=P_L1_DS_ROLE,
# BR.x — explicit default + value_when_unset; bridge restored.
# SQL-pushdown variant: ``l1-ds-accounts-ds`` declares
# ``pL1DsRole`` and filters in SQL via ``<<$pL1DsRole>>``.
# User is driving in the QS UI editor to explore further.
default=[L1_ALL_SENTINEL],
value_when_unset=L1_ALL_SENTINEL,
mapped_dataset_params=[
(datasets[DS_L1_DS_ACCOUNTS], P_L1_DS_ROLE_DSP),
],
))
# AO.2 — the balance date pushes DOWN into both datasets' SQL via
# the ``pL1DsBalanceDate`` dataset param (day-truncated equality).
# AR.2 (D5 strict-collapse) — the picker default + dataset default
# + App2 binding all derive from ONE ``DateView``; the pre-AR.2
# RollingDate(yesterday) UX-hint + dataset latest-sentinel + SQL
# OR-clause fallback are gone (one source, no disagreement, no
# dead safety net). The picker now shows the view's anchor day
# (= today live / LOCKED_ANCHOR locked). Blank-on-empty is the
# accepted trade per the operator call — operator picks the
# account anyway, and adjusts the picker if needed.
ds_balance_date = analysis.add_parameter(DateTimeParam(
name=P_L1_DS_BALANCE_DATE,
time_granularity="DAY",
# M.4.4.10ab — must have a default; QS UI errors with
# "epochMilliseconds must be a number, you gave: null"
# when the picker initializes with no value.
default=balance_date_view.emit_qs_analysis_default(),
mapped_dataset_params=[
(datasets[DS_DAILY_STATEMENT_SUMMARY], P_L1_DS_BALANCE_DATE_DSP),
(datasets[DS_DAILY_STATEMENT_TRANSACTIONS], P_L1_DS_BALANCE_DATE_DSP),
],
))
# Sheet controls — Role → Account → Business Day. AA.B.1 added the
# Role dropdown above Account so the cascade direction is visually
# explicit (left/top narrows the right/bottom). The Account
# dropdown's options come from the DS_L1_DS_ACCOUNTS companion
# (BO.1 — Daily-Statement-specific balance-only source), which
# carries a ``pL1DsRole`` dataset param; picking a role re-fetches
# the account options narrowed to that role.
#
# Role dropdown is SINGLE_SELECT with the show-all sentinel default
# (the standard AA.A pattern), so first-load lists every account
# exactly like before AA.B.1. ``hidden_select_all=True`` on Account
# mirrors pre-AA.B.1 behaviour: SINGLE_SELECT semantically requires
# picking exactly one — "All" doesn't apply.
daily_statement_sheet.add_parameter_dropdown(
parameter=ds_role, title="Role",
type="SINGLE_SELECT",
selectable_values=LinkedValues.from_column(
datasets[DS_L1_DS_ROLES]["account_role"],
),
)
daily_statement_sheet.add_parameter_dropdown(
parameter=ds_account, title="Account",
type="SINGLE_SELECT",
selectable_values=LinkedValues.from_column(
# AA.E.2 fix: bind to ``account_display`` so the picker's
# bound value matches the dataset SQL's display-format
# WHERE clause (``(account_name || ' (' || account_id || ')')
# = <<$pL1DsAccount>>``).
# BO.1 — sourced from ``DS_L1_DS_ACCOUNTS`` (balance-only)
# so every option has a matching ``daily_balances`` row.
datasets[DS_L1_DS_ACCOUNTS]["account_display"],
),
# BR.x — cascade config dropped. QS's
# ``GetUniqueAttributeValuesSyncForAnalysis`` endpoint (which
# populates LinkedValues dropdowns) refuses to execute against
# a parameterized dataset — it returns 400 BEFORE any SQL
# reaches the DB (verified by pg_stat_activity capture during
# cascade trigger: zero QS-originated queries hit Postgres).
# No SQL escape works because we never reach SQL execution.
# Without cascade, the bridge re-fetch on ``pL1DsRole`` change
# still narrows the Account options via SQL pushdown — the
# dropdown rebuilds with role-narrowed accounts. Operator may
# need to hit "Refresh this list" if QS's bridge-fire
# auto-refresh doesn't trigger. See
# docs/reference/quicksight-quirks.md — unique-values endpoint
# parameterized-dataset blocker.
hidden_select_all=True,
)
daily_statement_sheet.add_parameter_datetime_picker(
parameter=ds_balance_date, title="Business Day",
)
# ---------------------------------------------------------------------------
# M.2b.7 — Cross-sheet drill plumbing
# ---------------------------------------------------------------------------
#
# Two sentinel-pattern parameters (`pL1FilterAccount`, `pL1TxTransfer`)
# carry the drill-target value. Each destination sheet has a calc-field-
# backed FilterGroup that reads its parameter and either narrows the
# dataset to one row or PASSes everything through (when the param is
# the `__ALL__` sentinel).
#
# Drill writes auto-reset every sentinel-pattern param the caller didn't
# explicitly write — so a stale "I drilled to account-A on Drift earlier"
# value doesn't leak into the next drill's filtered view. Mirrors the
# AR `_ar_drill_to_transactions` pattern.
# Auto-reset list — only the sentinel-pattern params. Picker-driven
# params (P_L1_DS_*, P_L1_DATE_*) stay sticky across drills, since
# clearing a DateTimeParam to a string sentinel would break it.
_L1_DRILL_RESET_PARAMS = (_DP_FILTER_ACCOUNT, _DP_TX_TRANSFER)
def _l1_drill(
*,
target_sheet: Sheet,
name: str,
writes: list[DrillWrite],
trigger: Literal["DATA_POINT_CLICK", "DATA_POINT_MENU"] = "DATA_POINT_CLICK",
action_id: str | AutoResolved = AUTO,
) -> Drill:
"""Cross-sheet drill with auto-reset on un-written sentinel params.
Caller writes only the params that should narrow the destination;
any sentinel-pattern param the caller doesn't write gets a
DrillResetSentinel write, so a prior drill's value can't leak.
"""
written = {param.name for param, _ in writes}
full_writes = list(writes)
for param in _L1_DRILL_RESET_PARAMS:
if param.name not in written:
full_writes.append((param, DrillResetSentinel()))
return Drill(
target_sheet=target_sheet,
writes=full_writes,
name=name,
trigger=trigger,
action_id=action_id,
)
def _wire_drill_filter_groups(
analysis: Analysis,
*,
datasets: dict[str, Dataset],
sheets: dict[str, Sheet],
) -> None:
"""5 sentinel-pattern FilterGroups + their backing calc fields.
Each spec encodes one drill-destination filter:
- parameter to test (sentinel-pattern StringParam, default "__ALL__")
- destination dataset + the column to compare against
- destination sheet to scope the FilterGroup to
The calc-field expression is the K.2 sentinel-or-match pattern;
the FilterGroup uses ``CategoryFilter.with_literal(value="PASS")``
so the parameter test lives in the calc field — sidesteps the
parameter-bound CustomFilterConfiguration's empty-string narrowing
bug AR's docstring calls out.
Parameters are added to the analysis via ``_wire_drill_parameters``;
this helper wires the per-destination calc field + filter group.
"""
# Declare the 2 drill-target StringParams with sentinel defaults.
analysis.add_parameter(StringParam(
name=P_L1_FILTER_ACCOUNT,
default=[_DRILL_RESET_SENTINEL],
))
analysis.add_parameter(StringParam(
name=P_L1_TX_TRANSFER,
default=[_DRILL_RESET_SENTINEL],
))
@dataclass(frozen=True)
class _DrillDest:
fg_id: FilterGroupId
param_name: str
dataset_id: str
column_name: str
sheet_id: str
specs: list[_DrillDest] = [
_DrillDest(
fg_id=FilterGroupId("fg-l1-drill-account-on-drift"),
param_name=P_L1_FILTER_ACCOUNT,
dataset_id=DS_DRIFT,
column_name="account_id",
sheet_id=SHEET_DRIFT,
),
_DrillDest(
fg_id=FilterGroupId("fg-l1-drill-account-on-ledger-drift"),
param_name=P_L1_FILTER_ACCOUNT,
dataset_id=DS_LEDGER_DRIFT,
column_name="account_id",
sheet_id=SHEET_DRIFT,
),
_DrillDest(
fg_id=FilterGroupId("fg-l1-drill-account-on-overdraft"),
param_name=P_L1_FILTER_ACCOUNT,
dataset_id=DS_OVERDRAFT,
column_name="account_id",
sheet_id=SHEET_OVERDRAFT,
),
_DrillDest(
fg_id=FilterGroupId("fg-l1-drill-account-on-limit-breach"),
param_name=P_L1_FILTER_ACCOUNT,
dataset_id=DS_LIMIT_BREACH,
column_name="account_id",
sheet_id=SHEET_LIMIT_BREACH,
),
_DrillDest(
fg_id=FilterGroupId("fg-l1-drill-transfer-on-transactions"),
param_name=P_L1_TX_TRANSFER,
dataset_id=DS_TRANSACTIONS,
column_name="transfer_id",
sheet_id=SHEET_TRANSACTIONS,
),
]
for spec in specs:
ds = datasets[spec.dataset_id]
# Sentinel-or-match calc field. Mirrors AR's
# `_drill_pass_<param>_on_<suffix>` shape so the analyst-facing
# calc-field name reads consistently across apps.
on_suffix = spec.fg_id.split("on-", 1)[-1].replace("-", "_")
calc_name = f"_drill_pass_{spec.param_name}_on_{on_suffix}"
calc = analysis.add_calc_field(CalcField(
name=calc_name,
dataset=ds,
expression=(
f"ifelse("
f"${{{spec.param_name}}} = '{_DRILL_RESET_SENTINEL}', "
f"'PASS', "
f"ifelse({{{spec.column_name}}} = ${{{spec.param_name}}}, "
f"'PASS', 'FAIL')"
f")"
),
))
fg = analysis.add_filter_group(FilterGroup(
filter_group_id=spec.fg_id,
cross_dataset="SINGLE_DATASET",
filters=[CategoryFilter.with_literal(
filter_id=f"filter-{spec.fg_id}",
dataset=ds,
column=calc,
value="PASS",
null_option="NON_NULLS_ONLY",
)],
))
fg.scope_sheet(sheets[spec.sheet_id])
# AA.C.3 — Exception-literacy panel wiring. Pulls per-invariant prose
# (SHOULD-constraint + body + remediation) from L1_Invariants.md via
# the AA.C.2 parser and lands a SheetTextBox at the bottom of each
# invariant sheet. L1 Exceptions gets a generic intro panel
# instead of per-kind prose (it aggregates every kind, and a stack of
# seven panels would crowd the sheet).
_PANEL_LAYOUT_HEIGHT = 6
# Per-sheet ordered list of invariant kinds whose prose lands as
# stacked panels at the sheet bottom. Drift sheet hosts BOTH the
# leaf-level (`drift`) and parent-rollup (`ledger_drift`) panels --
# operators get the formal SHOULD + remediation for each
# without having to drill out to the docs site.
_PER_SHEET_PANELS: dict[str, tuple[str, ...]] = {
"drift": ("drift", "ledger_drift"),
"overdraft": ("overdraft",),
"limit_breach": ("limit_breach",),
"pending_aging": ("stuck_pending",),
"unbundled_aging": ("stuck_unbundled",),
"supersession_audit": ("supersession_audit",),
}
# AA.C.3.e — L1 Exceptions intro panel. Hand-authored vs
# parser-driven because the sheet doesn't map to a single
# invariant kind; instead it aggregates rows from every L1
# SHOULD-constraint matview. The bullet list points operators at
# the per-kind sheets where they'll find the formal SHOULD +
# remediation, keeping THIS sheet focused on the rollup view
# rather than re-printing every kind's prose.
_L1_EXCEPTIONS_PANEL = """\
**About this sheet**
L1 Exceptions aggregates every L1 SHOULD-constraint violation
across the date picker's window. One row per violation,
across all kinds -- drift, overdraft, limit breach, expected EOD
balance breach, pending aging, unbundled aging.
Drill from a row to the source sheet for that kind to see the
formal SHOULD-constraint, the full column shape, and the
remediation guidance specific to that violation:
- **Drift** -- internal sub-ledger doesn't match the bank's
cumulative net.
- **Overdraft** -- internal account went negative.
- **Limit Breach** -- outbound flow exceeded the rail's cap.
- **Pending Aging** -- transaction stuck in Pending past its
rail's max.
- **Unbundled Aging** -- posted leg not bundled into an
AggregatingRail past the cap.
- **Expected EOD Balance Breach** -- an account day declared an
expected EOD balance the stored balance didn't match.
For supersession-audit diagnostics (which are not SHOULD-violations
themselves), see the Supersession Audit sheet."""
def _wire_invariant_panels(
*,
drift_sheet: Sheet,
overdraft_sheet: Sheet,
limit_breach_sheet: Sheet,
pending_aging_sheet: Sheet,
unbundled_aging_sheet: Sheet,
supersession_audit_sheet: Sheet,
l1_exceptions_sheet: Sheet,
) -> None:
"""AA.C.3 -- land per-invariant prose panels at the bottom of the
L1 dashboard sheets.
Six invariant sheets get one or more :class:`TextBox` panels
composed from the L1_Invariants.md sections (AA.C.2 parser +
:func:`panel_markdown`). L1 Exceptions gets a single
hand-authored intro panel that points at the per-kind sheets
(AA.C.3.e -- avoiding a seven-panel stack here)."""
sections = load_bundled_invariants()
sheet_targets = {
"drift": drift_sheet,
"overdraft": overdraft_sheet,
"limit_breach": limit_breach_sheet,
"pending_aging": pending_aging_sheet,
"unbundled_aging": unbundled_aging_sheet,
"supersession_audit": supersession_audit_sheet,
}
for sheet_key, kinds in _PER_SHEET_PANELS.items():
sheet = sheet_targets[sheet_key]
for kind in kinds:
section = sections[kind]
sheet.layout.row(height=_PANEL_LAYOUT_HEIGHT).add_text_box(
TextBox(
text_box_id=f"l1-{kind}-panel",
content=rt.text_box(
rt.markdown(panel_markdown(section)),
),
),
width=_FULL,
)
# L1 Exceptions hand-authored intro panel.
l1_exceptions_sheet.layout.row(
height=_PANEL_LAYOUT_HEIGHT,
).add_text_box(
TextBox(
text_box_id="l1-exceptions-panel",
content=rt.text_box(rt.markdown(_L1_EXCEPTIONS_PANEL)),
),
width=_FULL,
)
[docs]
def build_l1_dashboard_app(
cfg: Config,
*,
l2_instance: L2Instance | None = None,
) -> App:
"""Construct the L1 Reconciliation Dashboard App as a tree.
M.2a.3: registers Analysis + Dashboard + Getting Started + Drift
sheets, plus the 2 L1 invariant datasets (drift + ledger_drift).
Substeps M.2a.4-M.2a.6 add the remaining per-invariant sheets
(Overdraft, Limit Breach, L1 Exceptions). Each sheet IS one
L1 SHOULD-constraint visualized via the M.1a.7 invariant views.
Dashboard ID convention: ``<deployment_name>-l1-dashboard`` (Z.C) —
``cfg.deployment_name`` is the operator-set per-deployment namespace
that lets N apps (L1, PR, Exec) deploy against the same L2 instance,
AND the same app deploy against N L2 instances, all in one QS account
without collision. The cfg arrives fully populated (``deployment_name``
+ ``db_table_prefix`` are required cfg fields); no auto-stamping dance.
"""
if l2_instance is None:
l2_instance = default_l2_instance()
# N.1.e / N.4.k — resolve theme once from the L2 instance, coerced
# to the registry default for in-canvas accent colors when the
# instance declares no inline ``theme:`` block. The CLI uses the
# un-coerced ``resolve_l2_theme`` return to decide whether to
# deploy a custom Theme resource (silent-fallback to AWS CLASSIC).
from recon_gen.common.theme import DEFAULT_PRESET
theme = resolve_l2_theme(l2_instance) or DEFAULT_PRESET
app = App(name="l1-dashboard", cfg=cfg)
analysis = app.set_analysis(Analysis(
analysis_id_suffix="l1-dashboard-analysis",
name=_analysis_name(cfg, l2_instance),
))
# Datasets first — registers contracts so visual ds["col"] refs validate.
datasets = _l1_datasets(cfg, l2_instance)
for ds in datasets.values():
app.add_dataset(ds)
# M.2b.7 — sheets built upfront so populators can drill across.
getting_started = analysis.add_sheet(Sheet(
sheet_id=SHEET_GETTING_STARTED,
name=_GETTING_STARTED_NAME,
title=_GETTING_STARTED_TITLE,
description=_GETTING_STARTED_DESCRIPTION,
))
drift_sheet = analysis.add_sheet(Sheet(
sheet_id=SHEET_DRIFT,
name=_DRIFT_NAME,
title=_DRIFT_TITLE,
description=_DRIFT_DESCRIPTION,
))
drift_timelines_sheet = analysis.add_sheet(Sheet(
sheet_id=SHEET_DRIFT_TIMELINES,
name=_DRIFT_TIMELINES_NAME,
title=_DRIFT_TIMELINES_TITLE,
description=_DRIFT_TIMELINES_DESCRIPTION,
))
overdraft_sheet = analysis.add_sheet(Sheet(
sheet_id=SHEET_OVERDRAFT,
name=_OVERDRAFT_NAME,
title=_OVERDRAFT_TITLE,
description=_OVERDRAFT_DESCRIPTION,
))
limit_breach_sheet = analysis.add_sheet(Sheet(
sheet_id=SHEET_LIMIT_BREACH,
name=_LIMIT_BREACH_NAME,
title=_LIMIT_BREACH_TITLE,
description=_LIMIT_BREACH_DESCRIPTION,
))
pending_aging_sheet = analysis.add_sheet(Sheet(
sheet_id=SHEET_PENDING_AGING,
name=_PENDING_AGING_NAME,
title=_PENDING_AGING_TITLE,
description=_PENDING_AGING_DESCRIPTION,
))
unbundled_aging_sheet = analysis.add_sheet(Sheet(
sheet_id=SHEET_UNBUNDLED_AGING,
name=_UNBUNDLED_AGING_NAME,
title=_UNBUNDLED_AGING_TITLE,
description=_UNBUNDLED_AGING_DESCRIPTION,
))
supersession_audit_sheet = analysis.add_sheet(Sheet(
sheet_id=SHEET_SUPERSESSION_AUDIT,
name=_SUPERSESSION_AUDIT_NAME,
title=_SUPERSESSION_AUDIT_TITLE,
description=_SUPERSESSION_AUDIT_DESCRIPTION,
))
l1_exceptions_sheet = analysis.add_sheet(Sheet(
sheet_id=SHEET_L1_EXCEPTIONS,
name=_L1_EXCEPTIONS_NAME,
title=_L1_EXCEPTIONS_TITLE,
description=_L1_EXCEPTIONS_DESCRIPTION,
))
daily_statement_sheet = analysis.add_sheet(Sheet(
sheet_id=SHEET_DAILY_STATEMENT,
name=_DAILY_STATEMENT_NAME,
title=_DAILY_STATEMENT_TITLE,
description=_DAILY_STATEMENT_DESCRIPTION,
))
transactions_sheet = analysis.add_sheet(Sheet(
sheet_id=SHEET_TRANSACTIONS,
name=_TRANSACTIONS_NAME,
title=_TRANSACTIONS_TITLE,
description=_TRANSACTIONS_DESCRIPTION,
))
# M.4.4.5 — App Info ("i") sheet, ALWAYS LAST. Diagnostic canary;
# see common/sheets/app_info.py.
app_info_sheet = analysis.add_sheet(Sheet(
sheet_id=SHEET_APP_INFO,
name=APP_INFO_SHEET_NAME,
title=APP_INFO_SHEET_TITLE,
description=APP_INFO_SHEET_DESCRIPTION,
))
# Populators — each receives the sheets it drills into so the drill
# actions can reference target_sheet by typed ref. ``theme`` is the
# N.1-resolved L2 brand theme (or the registry default fallback).
_populate_getting_started(cfg, getting_started, l2_instance, theme=theme)
_populate_drift_sheet(
cfg, drift_sheet, datasets=datasets, l2_instance=l2_instance,
daily_statement_sheet=daily_statement_sheet, theme=theme,
)
_populate_drift_timelines_sheet(
cfg, drift_timelines_sheet, datasets=datasets,
)
_populate_overdraft_sheet(
cfg, overdraft_sheet, datasets=datasets,
daily_statement_sheet=daily_statement_sheet, theme=theme,
)
_populate_limit_breach_sheet(
cfg, limit_breach_sheet,
datasets=datasets, l2_instance=l2_instance,
daily_statement_sheet=daily_statement_sheet, theme=theme,
)
_populate_pending_aging_sheet(
cfg, analysis, pending_aging_sheet,
datasets=datasets,
transactions_sheet=transactions_sheet, theme=theme,
)
_populate_unbundled_aging_sheet(
cfg, analysis, unbundled_aging_sheet,
datasets=datasets,
transactions_sheet=transactions_sheet, theme=theme,
)
_populate_supersession_audit_sheet(
cfg, analysis, supersession_audit_sheet,
datasets=datasets, theme=theme,
)
_populate_l1_exceptions_sheet(
cfg, l1_exceptions_sheet,
datasets=datasets, l2_instance=l2_instance,
drift_sheet=drift_sheet,
daily_statement_sheet=daily_statement_sheet, theme=theme,
)
_populate_daily_statement_sheet(
cfg, daily_statement_sheet, datasets=datasets,
transactions_sheet=transactions_sheet, theme=theme,
)
_populate_transactions_sheet(
cfg, transactions_sheet, datasets=datasets, theme=theme,
)
populate_app_info_sheet(
cfg, app_info_sheet,
liveness_ds=datasets[_DS_APP_INFO_LIVENESS],
matview_status_ds=datasets[_DS_APP_INFO_MATVIEWS],
theme=theme,
)
# M.2b.1 — Universal date-range filter wires the sheets together.
# Lands AFTER all sheets are populated since the per-sheet picker
# controls register on the sheets directly.
# AR.4 — 7-day window per the pre-AR.4 RollingDate defaults.
# Phase BM — narrowing pushed into dataset SQL via
# ``<<$pL1Date*>>``; analysis-level FilterGroups + BL.2's
# default_universal_date_range bind-layer fallback both dissolved.
_wire_date_range_filter(
analysis,
datasets=datasets,
drift_sheet=drift_sheet,
drift_timelines_sheet=drift_timelines_sheet,
overdraft_sheet=overdraft_sheet,
limit_breach_sheet=limit_breach_sheet,
pending_aging_sheet=pending_aging_sheet,
unbundled_aging_sheet=unbundled_aging_sheet,
supersession_audit_sheet=supersession_audit_sheet,
l1_exceptions_sheet=l1_exceptions_sheet,
transactions_sheet=transactions_sheet,
universal_range_view=DateView(
frame=cfg.test_generator.as_of_frame(window_days=7),
),
)
# M.2b.3 + M.2b.5 + M.2b.10 + M.2b.11 + M.2b.12 — Per-sheet
# category filter dropdowns (account / role / rail_name /
# check_type / status / origin / rail_name / supersedes as
# appropriate per sheet).
_wire_per_sheet_dropdowns(
analysis,
datasets=datasets,
l2_instance=l2_instance,
drift_sheet=drift_sheet,
drift_timelines_sheet=drift_timelines_sheet,
overdraft_sheet=overdraft_sheet,
limit_breach_sheet=limit_breach_sheet,
pending_aging_sheet=pending_aging_sheet,
unbundled_aging_sheet=unbundled_aging_sheet,
supersession_audit_sheet=supersession_audit_sheet,
l1_exceptions_sheet=l1_exceptions_sheet,
transactions_sheet=transactions_sheet,
)
# M.2b.4 — Daily Statement per-account-day parameter filters.
# AR.2 — the balance-date view is constructed once from
# ``cfg.test_generator.as_of_frame()`` and threaded through; the
# same view's emissions drive the analysis-param default in the
# picker AND (via ``datasets.py::_date_dataset_param``) the
# dataset-param default. One source of truth.
_wire_daily_statement_filters(
analysis,
datasets=datasets,
daily_statement_sheet=daily_statement_sheet,
balance_date_view=DateView(frame=cfg.test_generator.as_of_frame()),
)
# M.2b.7 — Cross-sheet drill filter groups (sentinel-pattern).
_wire_drill_filter_groups(
analysis,
datasets=datasets,
sheets={
SHEET_DRIFT: drift_sheet,
SHEET_OVERDRAFT: overdraft_sheet,
SHEET_LIMIT_BREACH: limit_breach_sheet,
SHEET_TRANSACTIONS: transactions_sheet,
},
)
# AA.C.3 — exception-literacy panels (sheet-bottom rich-text from
# L1_Invariants.md). Per-kind on the 6 invariant sheets, generic
# intro on L1 Exceptions.
_wire_invariant_panels(
drift_sheet=drift_sheet,
overdraft_sheet=overdraft_sheet,
limit_breach_sheet=limit_breach_sheet,
pending_aging_sheet=pending_aging_sheet,
unbundled_aging_sheet=unbundled_aging_sheet,
supersession_audit_sheet=supersession_audit_sheet,
l1_exceptions_sheet=l1_exceptions_sheet,
)
app.create_dashboard(
dashboard_id_suffix="l1-dashboard",
name=_analysis_name(cfg, l2_instance),
)
return app
# ---------------------------------------------------------------------------
# CLI / external-caller shims. The CLI imports these directly and writes
# the emit_analysis() / emit_dashboard() output to JSON files, mirroring
# the AR / PR / Investigation / Executives shape.
# ---------------------------------------------------------------------------
[docs]
def build_analysis(
cfg: Config,
*,
l2_instance: L2Instance | None = None,
):
"""Build the complete L1 Dashboard Analysis resource via the tree.
Forwards ``l2_instance`` to ``build_l1_dashboard_app``; default
behaviour (unset) auto-loads the canonical Sasquatch AR L2 fixture.
Return type is the AWS-shape Analysis dataclass from common.models.
"""
return build_l1_dashboard_app(cfg, l2_instance=l2_instance).emit_analysis()
[docs]
def build_l1_dashboard_dashboard(
cfg: Config,
*,
l2_instance: L2Instance | None = None,
):
"""Build the L1 Dashboard Dashboard resource via the tree."""
return build_l1_dashboard_app(
cfg, l2_instance=l2_instance,
).emit_dashboard()