quicksight-gen 8.5.7

Programmatic AWS QuickSight analysis generator for financial reporting

QuickSight Analysis Generator

Python tool that programmatically generates AWS QuickSight JSON definitions (theme, datasets, analyses, dashboards) and deploys them via boto3. Ships four bundled QuickSight apps, all L2-fed off one institution YAML:

• L1 Dashboard — persona-blind L1 invariant violation surface (drift / overdraft / limit breach / stuck pending / stuck unbundled / supersession audit / today's exceptions / daily statement / transactions). 11 sheets. Configured by an L2 instance YAML; the same dashboard renders against any institution that declares its accounts / rails / templates / chains / limit schedules in L2 form.
• L2 Flow Tracing — Rails / Chains / Transfer Templates / L2 Hygiene Exceptions for the integrator validating their L2 instance against the SPEC.
• Investigation — compliance / AML triage: 4 question-shaped sheets — recipient fanout, volume anomalies, money-trail provenance, account-network graphs — over the shared base ledger.
• Executives — board-cadence rollups: account coverage (open vs active), transaction volume over time, money moved (gross + net) over time. Reads only the shared base tables — no Executives-specific schema.

CLI is organized as five artifact groups: quicksight-gen schema|data|json|docs|audit. Each artifact has apply/clean/test (plus a few extras); destructive operations default to emit and require --execute to actually run. The audit group also exposes a verify subcommand for recomputing a generated PDF's provenance fingerprint. Change the Python (or ask Claude), re-run json apply --execute, get a new dashboard.

Demo Docs

The demo ships with task-shaped handbooks deployed to GitHub Pages at chotchki.github.io/Quicksight-Generator.

• L1 Dashboard handbook — 11 sheets covering 5 baseline L1 invariants + 2 aging-watch invariants + supersession audit + per-account-day walk + raw posting ledger. Switch the L2 instance to switch the persona prose without touching dashboard code.
• L2 Flow Tracing handbook — Rails / Chains / Transfer Templates / L2 Hygiene Exceptions for L2 spec verification.
• Investigation handbook — Compliance / Investigation team flow. 4 walkthroughs, one per sheet's question.
• Executives handbook — board scorecard: account coverage, transaction volume, money moved.
• Data Integration handbook — how the Data Integration Team maps an upstream system into <prefix>_transactions + <prefix>_daily_balances, validates the load, and extends the metadata contract.
• Audit Reconciliation Report handbook — regulator-ready PDF generated by quicksight-gen audit apply; covers the L1 invariants, embeds a provenance fingerprint, optionally auto-signs via pyHanko.

Source lives in src/quicksight_gen/docs/ (shipped with the wheel — extract with quicksight-gen docs export -o ./somewhere/); rebuild locally with quicksight-gen docs serve.

Why this exists

The customer for these reports doesn't know exactly what they want yet. Rather than click through the QuickSight console and lose the work when requirements change, everything is generated from code and deployed idempotently (delete-then-create). Iteration is one command.

The four apps

L1 Dashboard — 11 tabs

The recommended path for new integrators. Configured by an L2 instance YAML — declare your institution once (accounts, rails, transfer templates, chains, limit schedules, per-rail aging caps), and the same dashboard renders against you. Switching the L2 instance switches the prose on every TextBox without touching dashboard code.

Tab	What it shows
Getting Started	Welcome + L2 coverage inventory pulled from the L2 instance's prose.
Drift	Leaf + parent account balance drift detail tables. Right-click any row → Daily Statement for that account-day.
Drift Timelines	KPI for largest single-day drift + 2 LineCharts (one line per account_role) tracking Σ ABS(drift) over the visible date range.
Overdraft	KPI + violations table for internal accounts holding negative money at EOD. Right-click → Daily Statement.
Limit Breach	KPI + per-(account, day, transfer_type) breach table. Caps inlined from L2 LimitSchedules at schema-emit time.
Pending Aging	Stuck-Pending transactions past their rail's max_pending_age. KPI + 5-bucket horizontal aging bar + detail. Right-click → Transactions.
Unbundled Aging	Posted legs with bundle_id IS NULL past their rail's max_unbundled_age. Same KPI + bar + detail shape with 4 day-scale buckets.
Supersession Audit	Logical keys with multiple entry versions — the rewrite trail (Inflight / BundleAssignment / TechnicalCorrection).
Today's Exceptions	UNION across all 5 baseline invariant views scoped to the most recent business day. KPI + by-check bar + detail sorted by magnitude.
Daily Statement	Per-account-day walk: 5 KPIs (Opening / Debits / Credits / Closing / Drift) + every Money record posted that day.
Transactions	Raw posting ledger (<prefix>_current_transactions matview — supersession-aware). 5 dropdown filters for analyst-driven slicing.

Reads from per-instance <prefix>_* views/matviews emitted by common.l2.emit_schema(instance). See L1 Invariants for the per-view contract + SHOULD-constraint motivation.

L2 Flow Tracing — 4 tabs

Tab	What it shows
Getting Started	Welcome + roadmap of the three flow tabs below.
Rails	Postings explorer + per-rail firing counts + L2 declaration cascade.
Chains	Parent → child rail/template firings with per-chain SUM amounts.
Transfer Templates	Multi-leg transfer template firings + L2 hygiene exception list.

Investigation — 5 tabs

Tab	What it shows
Getting Started	Landing page — heading + roadmap of the four question-shaped sheets below.
Recipient Fanout	Who is receiving money from too many distinct senders? 3 KPIs (qualifying recipients / distinct senders / total inbound) + ranked table; threshold slider sets where "too many" starts.
Volume Anomalies	Which sender → recipient pair just spiked above its rolling baseline? Backed by inv_pair_rolling_anomalies matview (rolling 2-day SUM per pair + population z-score). KPI flagged-pair count + σ distribution chart + ranked table; σ slider gates KPI + table while the chart shows the full population.
Money Trail	Where did this transfer originate, and where does it go? Backed by inv_money_trail_edges matview (recursive WITH RECURSIVE walk over parent_transfer_id). Sankey as the headline + hop-by-hop table beside it; chain-root dropdown + max-hops + min-hop-amount controls.
Account Network	What does this account's money network look like, on either side? Two side-by-side directional Sankeys (inbound on the left, outbound on the right, anchor visually meeting in the middle) + touching-edges table. Walk-the-flow drill: right-click any table row or left-click any Sankey node to walk the anchor to the counterparty and re-render around the new center.

Executives — 4 tabs

Tab	What it shows
Getting Started	Landing page — heading + per-sheet highlights.
Account Coverage	Open vs Active account KPIs + bar chart by account_type + detail table. The Active KPI + Active bar carry a visual-pinned activity_count >= 1 filter so they read as "accounts that moved money in the period" while the Open KPI/bar count every row — same dataset, different scope.
Transaction Volume Over Time	Total transfers + average daily KPIs + daily stacked bar by transfer_type + per-type bar. Per-transfer pre-aggregation collapses multi-leg transfers so a 2-leg $100 movement counts as one $100 transfer, not two $200.
Money Moved	Gross + net amount KPIs + daily stacked bar by transfer_type + per-type bar. Net = inflows − outflows from the bank's perspective.

Shared conventions

• Clickable cells look clickable. Accent-colored text = left-click drill; accent text on a pale tint background = right-click menu drill.
• Every sheet has a plain-language description; every visual has a subtitle. Coverage is asserted in unit + API e2e tests.
• All resources tagged ManagedBy: quicksight-gen; extra tags via extra_tags in config.

Quick start

Prerequisites

• Python 3.12+
• An AWS account with QuickSight Enterprise enabled
• Either a pre-existing QuickSight datasource ARN or a PostgreSQL 17+ / Oracle 19c+ database URL for demo mode (the schema uses SQL/JSON path syntax — JSON_VALUE / JSON_QUERY / JSON_EXISTS — supported on both engines)

Install from PyPI

For consumers — using a pre-existing QuickSight datasource ARN:

pip install quicksight-gen

For demo mode against PostgreSQL 17+ (requires psycopg2-binary):

pip install "quicksight-gen[demo]"

For demo mode against Oracle 19c+ (requires oracledb thin mode — no Oracle Instant Client install):

pip install "quicksight-gen[demo,demo-oracle]"

Setup from source

The repo uses uv for env / lock management (deterministic resolution from uv.lock). One command sets up .venv/ with every extra:

uv sync --all-extras

Then invoke tools directly via the venv (no source activate needed):

.venv/bin/pytest
.venv/bin/quicksight-gen --help

For a leaner install, swap --all-extras for the specific extras you need: --extra dev (tests + pyright + boto3), --extra audit (PDF report deps), --extra docs (mkdocs + macros), --extra demo / --extra demo-oracle (DB drivers).

If you'd rather stick with pip, the standard PEP-621 path still works:

python3 -m venv .venv
.venv/bin/pip install -e ".[dev]"

Configure

cp config.example.yaml config.yaml

Edit config.yaml:

aws_account_id: "123456789012"
aws_region: "us-east-2"

# Pre-existing QuickSight datasource ARN.
# Not required when demo_database_url is set (auto-derived).
datasource_arn: "arn:aws:quicksight:us-east-2:123456789012:datasource/your-datasource-id"

# Optional: prefix for all generated resource IDs (default: qs-gen)
resource_prefix: "qs-gen"

# Optional: IAM principals granted permissions on generated resources.
principal_arns:
  - "arn:aws:quicksight:us-east-1:123456789012:user/default/admin"

# Optional: additional tags on every generated resource
extra_tags:
  Environment: production
  Team: finance

# Optional: which database family for `data apply --execute` (default: postgres)
# dialect: "postgres"   # or "oracle"

# Optional: database URL for `data apply --execute` and friends
# Postgres:
# demo_database_url: "postgresql://user:pass@localhost:5432/quicksight_demo"
# Oracle (Easy Connect form, no scheme prefix):
# demo_database_url: "system/pass@localhost:1521/FREEPDB1"

Theme is declared inline on the L2 institution YAML's theme: block, not on the deploy config. When the L2 instance carries no theme: block, AWS QuickSight CLASSIC takes over at deploy.

All values can also be set via QS_GEN_-prefixed environment variables (e.g. QS_GEN_AWS_ACCOUNT_ID). Env vars override YAML.

Generate and deploy

# Generate JSON for all four bundled apps to out/
quicksight-gen json apply -c config.yaml -o out/

# Same emit, then deploy to AWS (delete-then-create, idempotent)
quicksight-gen json apply -c config.yaml -o out/ --execute

# Override the L2 instance (defaults to bundled spec_example)
quicksight-gen json apply -c config.yaml -o out/ --l2 run/sasquatch_pr.yaml --execute

json apply --execute polls async resources (analyses, dashboards) until they reach a terminal state. Resources with the ManagedBy: quicksight-gen tag that aren't in the current output aren't touched — clean those up explicitly:

quicksight-gen json clean              # dry-run: list stale tagged resources
quicksight-gen json clean --execute    # delete them

What you get

out/
  theme.json
  datasource.json                              # demo only (auto-derived)
  investigation-analysis.json
  investigation-dashboard.json
  executives-analysis.json
  executives-dashboard.json
  l1-dashboard-analysis.json
  l1-dashboard-dashboard.json
  l2-flow-tracing-analysis.json
  l2-flow-tracing-dashboard.json
  datasets/
    qs-gen-<l2_prefix>-inv-*.json              # 5 Investigation datasets
    qs-gen-<l2_prefix>-exec-*.json             # 2 Executives datasets
    qs-gen-<l2_prefix>-l1-*.json               # 14 L1 Dashboard datasets
    qs-gen-<l2_prefix>-l2ft-*.json             # 2 L2 Flow Tracing datasets
    qs-gen-<l2_prefix>-*-app-info-*.json       # 2 App Info datasets per app (8 total)

The <l2_prefix> middle segment auto-derives from the L2 instance's instance: field (default spec_example), so multiple L2 instances can deploy into the same QuickSight account without colliding.

Demo mode

A deterministic demo generator seeds the four apps end-to-end so you can see them work without wiring up real data. Every app feeds two per-prefix base tables — <prefix>_transactions (every money-movement leg) and <prefix>_daily_balances (per-account end-of-day snapshots).

# Apply schema + seed to your demo database, then generate QuickSight JSON.
# Requires: demo_database_url + dialect in config.yaml and the matching
# extra installed (`[demo]` for Postgres, `[demo,demo-oracle]` for Oracle).
# Per-prefix DDL + seed are emitted at apply time from the L2 instance YAML.
quicksight-gen schema apply -c config.yaml --execute   # tables + matviews
quicksight-gen data apply   -c config.yaml --execute   # 90-day baseline + plants
quicksight-gen data refresh -c config.yaml --execute   # populate matviews
quicksight-gen json apply   -c config.yaml -o out/ --execute  # JSON + AWS deploy
quicksight-gen audit apply  -c config.yaml --execute -o report.pdf  # regulator-ready PDF (optional)

schema apply --execute creates the per-prefix base tables + matviews via common/l2/schema.py::emit_schema(l2_instance). data apply --execute inserts the L2-shape seed data (90-day baseline + every L1 SHOULD-violation plant + the Investigation fanout / volume / chain plants). data refresh --execute refreshes every dependent matview in dependency order. json apply --execute writes a datasource.json derived from the database URL (Type=POSTGRESQL or ORACLE, dispatched off dialect), generates all QuickSight JSON to out/, and deploys to AWS. audit apply --execute queries the per-prefix L1 invariant matviews and writes a regulator-ready PDF reconciliation report (cover, executive summary, per-invariant violation tables, per-account-day Daily Statement walks, sign-off block, cryptographic provenance fingerprint) — see the Audit Reconciliation Report handbook for the full reference. The account_type and transfer_type columns discriminate which app a row belongs to. See Schema_v6.md for the full feed contract, canonical type values, metadata key catalog, and ETL examples.

PostgreSQL 17+ or Oracle 19c+ required for schema apply --execute. Both engines support the SQL/JSON path syntax (JSON_VALUE, JSON_QUERY, JSON_EXISTS) the schema uses for metadata JSON columns. The portable subset forbids the Postgres-only ->> / -> / @> / ? operators and JSONB; on Oracle, also no named WINDOW clause and no TIMESTAMP WITH TIME ZONE in PK columns. See Schema_v6.md → Forbidden SQL patterns for the full constraint matrix.

Datasets are all Direct Query (no SPICE), so seed changes show up immediately after a fresh data apply --execute + data refresh --execute — no QuickSight-side refresh needed.

Demo scenarios

Two L2 institution YAMLs ship in tests/l2/:

• spec_example.yaml — the persona-neutral default fixture. Generic accounts/rails/chains exercising every L2 primitive without naming a specific institution.
• sasquatch_pr.yaml — a flavored Sasquatch National Bank persona block carrying the curated demo narrative: SNB control accounts, merchant DDAs (Bigfoot Brews, Sasquatch Sips, etc.), Investigation anchor (Juniper Ridge LLC) with three converging scenarios (12-sender fanout cluster, Cascadia Trust Bank → Juniper anomaly spike, 4-hop layering chain through shell entities).

Pass --l2 tests/l2/sasquatch_pr.yaml (or your own) to switch the rendered handbook + demo data narrative without touching dashboard code.

Theming

Theme is declared inline on the L2 institution YAML's theme: block. When the L2 instance carries no theme: block, build_theme returns None and AWS QuickSight CLASSIC takes over at deploy (silent-fallback contract). The single DEFAULT_PRESET in common/theme.py is the in-canvas-accent fallback for apps when their L2 instance declares no theme — no registry, no CLI flag.

To customize the demo persona's brand: edit the theme: block on tests/l2/sasquatch_pr.yaml (or your own L2 YAML). See the ThemePreset dataclass in common/l2/theme.py for the full field list. Rich-text on the Getting Started sheets resolves the accent color to hex at generate time.

Project structure

src/quicksight_gen/
    __main__.py         # python -m quicksight_gen entry point
    cli/                # Click CLI shell — schema | data | json | docs groups
        __init__.py     # main + group registration
        schema.py / data.py / json.py / docs.py
        _helpers.py     # shared resolve_l2_for_demo / emit_to_target / connect_and_apply
        _app_builders.py # per-app JSON-emit helpers
    common/
        config.py       # Config dataclass + YAML/env loader
        models.py       # Dataclasses → AWS QuickSight API JSON
        ids.py          # Typed ID newtypes (SheetId / VisualId / FilterGroupId / ParameterName)
        theme.py        # DEFAULT_PRESET fallback + build_theme(cfg, theme | None)
        persona.py      # DemoPersona dataclass — generic skeleton; populated from L2 YAML
        deploy.py       # Python deploy (delete-then-create, async waiters)
        cleanup.py      # Tag-based cleanup of stale resources
        dataset_contract.py  # ColumnSpec / DatasetContract / build_dataset()
        drill.py        # Cross-app deep-link URL builder
        clickability.py # Conditional-format helpers
        rich_text.py    # XML helpers for SheetTextBox.Content
        probe.py        # Playwright walker for deployed-dashboard error surfacing
        tree/           # Typed tree primitives (Phase L). App / Analysis / Dashboard / Sheet,
                        # typed Visual subtypes, typed Filter wrappers, Parameter + Filter
                        # Controls, Drill actions, Datasets + Columns + Dim/Measure factories,
                        # CalcFields. Object-ref cross-references, auto-IDs, emit-time
                        # validation. All four apps are tree-built — see CLAUDE.md
                        # "Tree pattern" for the L1 / L2 / L3 layer model.
        l2/             # L2 model: primitives, validate, loader, schema, seed,
                        # auto_scenario, derived, theme, topology
        sql/dialect.py  # Dialect enum (POSTGRES / ORACLE)
        browser/        # Playwright helpers (helpers.py + ScreenshotHarness)
        handbook/       # mkdocs-macros vocabulary + diagrams
        sheets/app_info.py  # populate_app_info_sheet — Info canary builder
    apps/
        l1_dashboard/   # 11 sheets, configured by L2 instance
        l2_flow_tracing/ # 4 sheets — Rails / Chains / Templates / Hygiene
        investigation/  # 5 sheets — fanout / anomalies / money trail / account network
        executives/     # 4 sheets — coverage / volume / money moved
    docs/               # Unified mkdocs site source — concepts/, handbook/, walkthroughs/,
                        # for-your-role/, scenario/, Schema_v6.md, _diagrams/, _macros/.
                        # Renders against any L2 instance via mkdocs-macros + HandbookVocabulary.
tests/                  # Mirror the artifact split: tests/{schema,data,json,docs,unit,e2e}/
run_e2e.sh              # One-shot: regenerate + deploy + e2e
config.example.yaml

Tests

pytest                  # unit + integration (fast, no AWS)
./run_e2e.sh            # regenerate + deploy all four apps + e2e (pytest-xdist -n 4)
./run_e2e.sh --parallel 8            # override worker count (1 = serial; stable ceiling ~8)
./run_e2e.sh --skip-deploy api       # only API e2e
./run_e2e.sh --skip-deploy browser   # only browser e2e

Coverage:

• Unit / integration: models, tags, config, CLI, demo determinism + scenario coverage (per-instance SHA256 seed-hash locks), tree primitives + validators, dataset builders, visual builders, filter groups, cross-reference validation (dataset ARNs, filter bindings, visual ID uniqueness, sheet scoping), explanation coverage, schema + seed SQL structure for both Postgres + Oracle.
• E2E: two layers gated by QS_GEN_E2E=1.
  • API layer (boto3) — resource existence, status, dashboard structure (per-sheet visual counts, parameter / filter-group source-of-truth checks), dataset import health.
  • Browser layer (Playwright WebKit, headless) — dashboard loads via pre-authenticated embed URL, sheet tabs, per-sheet visual counts + spot-checked titles, drill-downs, mutual-filter reconciliation tables, date-range filter narrowing, Show-Only-X toggles, Investigation slider + dropdown filters.

E2E tunables (env vars): QS_E2E_PAGE_TIMEOUT, QS_E2E_VISUAL_TIMEOUT, QS_E2E_USER_ARN, QS_E2E_IDENTITY_REGION. Failure screenshots land in tests/e2e/screenshots/<app>/ (gitignored).

Customising

Change the SQL

Edit the dataset builders in apps/<app>/datasets.py. Each dataset has a sql string and a DatasetContract (column name + type list) — unit tests assert the SQL projection matches the contract, so the contract is the safety net when rewriting.

The dataset SQL reads from two shared base tables (<prefix>_transactions, <prefix>_daily_balances) plus the L1 invariant + Investigation matviews. To wire your production data in, ETL into the same shape: see Schema_v6.md for column specifications, the canonical account_type / transfer_type values, the JSON metadata key catalog, and end-to-end ETL examples.

Add a visual or tab

1. Open apps/<app>/app.py and find the relevant sheet's populator function.
2. Place the visual on a layout row: row.add_kpi(...), row.add_table(...), row.add_bar_chart(...), row.add_sankey(...). Pass title=, subtitle=, and the typed Dim/Measure slots — the tree validates dataset / column references at emit time.
3. Subtitle is required (coverage tests enforce this).
4. Run pytest — typed cross-reference errors fail at the wiring site, not deep in the generated JSON.

Add a filter

1. In apps/<app>/app.py, build a FilterGroup: fg = FilterGroup.with_category_filter(...) (or with_numeric_range_filter / with_time_range_filter). Pass the typed Dim ref directly — no string IDs.
2. Scope it: fg.scope_sheet(sheet_obj) for sheet-wide; fg.scope_visuals(visual_a, visual_b) for visual-pinned.
3. For UI controls, attach the filter's default_control to a sheet via sheet.filter_controls.append(...).
4. pytest walks the tree and flags missing references at emit time.

Re-skin

Edit your L2 institution YAML's theme: block (or copy from tests/l2/sasquatch_pr.yaml for a worked example). Keys: theme_name, version_description, accent, primary_fg, link_tint, analysis_name_prefix. See common/l2/theme.py::ThemePreset for the full field contract.

Ask Claude

The codebase is intentionally easy to mutate. Ask Claude to add visuals, reshape the layout, adjust filters, update SQL for your schema, or add conditional formatting — it'll edit the Python and re-run tests.