quicksight-gen 4.0.0

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. It currently ships three independent QuickSight apps:

• Payment Reconciliation — sales → settlements → payments → external-system matching for a merchant bank.
• Account Reconciliation — stored daily balances, transfers, and postings for a double-entry ledger.
• Investigation — compliance / AML triage: recipient fanout, volume anomalies, and money-trail provenance over the shared base ledger. (K.4.2 skeleton — sheets land in K.4.3 / K.4.4 / K.4.5.)

All three apps share one theme registry, one AWS account, one datasource, and the same CLI surface (quicksight-gen generate|deploy|demo|cleanup). Change the Python (or ask Claude), re-run deploy --generate, get a new dashboard.

Demo Docs

The demo ships with three task-shaped handbooks, one per persona team at Sasquatch National Bank. Deployed to GitHub Pages at chotchki.github.io/Quicksight-Generator.

• GL Reconciliation Handbook — how the Accounting Operations team works the AR Exceptions sheet. Morning rollups + per-check drill-downs for 17 exception classes.
• Payment Reconciliation Handbook — how the Merchant Support team answers "where's my money?" calls. 7 walkthroughs organized by operator question.
• Data Integration Handbook — how the Data Integration Team maps an upstream system into transactions + daily_balances, validates the load, and extends the metadata contract. 5 foundational / extension / debug walkthroughs.

Source lives in src/quicksight_gen/docs/ (shipped with the wheel — extract with quicksight-gen export docs -o ./somewhere/); rebuild locally with mkdocs 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 three apps

Payment Reconciliation — 6 tabs

Tab	What it shows
Getting Started	Landing page — heading + per-sheet highlights; demo scenario block when seeded.
Sales Overview	KPIs + by-merchant / by-location bar charts + detail table.
Settlements	KPIs + bar by merchant type + detail table. Click a row to drill into Sales.
Payments	KPIs + pie by status + detail table. Click a row to drill into Settlements. Right-click external_transaction_id to drill into Payment Reconciliation.
Exceptions & Alerts	Unsettled sales, returned payments, sale↔settlement and settlement↔payment mismatches, and unmatched external transactions. Compact half-width tables.
Payment Reconciliation	KPIs + match-status bar + dual mutually-filterable tables (external transactions ↔ internal payments). Click a row in either to filter the other.

Account Reconciliation — 5 tabs

Tab	What it shows
Getting Started	Landing page — heading + per-sheet highlights; demo scenario block when seeded.
Balances	Ledger and sub-ledger account balance tables. Click an account to drill into its transactions.
Transfers	One row per transfer_id with net-zero flags. Click to drill into transactions.
Transactions	Raw ledger (one row per leg, with an origin tag for filtering), filtered by date / type / posting-level / origin / Show-Only-Failed.
Exceptions	Cross-check rollups at the top (expected-zero EOD, two-sided post mismatch, balance-drift timelines), then per-check details: ledger / sub-ledger drift, non-zero transfers, limit breaches, overdrafts, and seven Cash Management Suite checks (ZBA sweep, ACH origination non-zero EOD, missing Fed confirmations, force-posted card without internal catch-up, GL-vs-Fed Master drift, stuck-in-suspense, reversed-but-not-credited). Aging bars on every check.

Investigation — 4 tabs (K.4.2 skeleton — visuals incoming)

Tab	What it shows
Getting Started	Landing page — heading + roadmap of the three question-shaped sheets below.
Recipient Fanout	(K.4.3) Who is receiving money from too many distinct senders? KPI + ranked table; cross-app drill into AR Transactions for the recipient.
Volume Anomalies	(K.4.4) Which sender → recipient pair just spiked above its rolling baseline? Distribution + flagged-rows table; cross-app drill into the underlying transactions for the pair × window.
Money Trail	(K.4.5) Where did this transfer originate, and where does it go? Recursive walk up/down the parent_transfer_id chain; Sankey + hop-by-hop table; cross-app drill into PR Payment Reconciliation or AR Transactions depending on the edge.

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.11+
• An AWS account with QuickSight Enterprise enabled
• Either a pre-existing QuickSight datasource ARN or a PostgreSQL 17+ database URL for demo mode (the schema uses SQL/JSON path syntax)

Install from PyPI

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

pip install quicksight-gen

For demo mode (Postgres 17+, requires psycopg2-binary):

pip install "quicksight-gen[demo]"

Setup from source

For development on this repo:

python3 -m venv .venv
source .venv/bin/activate
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: which theme preset to use. One of: default, sasquatch-bank, sasquatch-bank-ar
theme_preset: "default"

# Optional: IAM principals granted permissions on generated resources.
# Accepts a single string or a list; one ResourcePermission is emitted per entry.
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: PostgreSQL URL for demo apply
# demo_database_url: "postgresql://user:pass@localhost:5432/quicksight_demo"

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 all three apps' JSON
quicksight-gen generate --all -c config.yaml -o out/

# Deploy everything (delete-then-create, idempotent)
quicksight-gen deploy --all -c config.yaml -o out/

# Or combine: regenerate + deploy in one shot (typical iteration loop)
quicksight-gen deploy --all --generate -c config.yaml -o out/

# Deploy a single app
quicksight-gen generate payment-recon  -c config.yaml -o out/
quicksight-gen generate account-recon  -c config.yaml -o out/
quicksight-gen generate investigation  -c config.yaml -o out/
quicksight-gen deploy   payment-recon  -c config.yaml -o out/

deploy 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 cleanup --dry-run       # list stale tagged resources
quicksight-gen cleanup --yes           # delete them without prompting

What you get

out/
  theme.json
  payment-recon-analysis.json
  payment-recon-dashboard.json
  account-recon-analysis.json
  account-recon-dashboard.json
  investigation-analysis.json            # K.4.2 skeleton (no datasets yet)
  investigation-dashboard.json
  datasource.json                        # demo apply only
  datasets/
    qs-gen-merchants-dataset.json              # 11 PR datasets
    qs-gen-sales-dataset.json
    qs-gen-settlements-dataset.json
    qs-gen-payments-dataset.json
    qs-gen-settlement-exceptions-dataset.json
    qs-gen-payment-returns-dataset.json
    qs-gen-sale-settlement-mismatch-dataset.json
    qs-gen-settlement-payment-mismatch-dataset.json
    qs-gen-unmatched-external-txns-dataset.json
    qs-gen-external-transactions-dataset.json
    qs-gen-payment-recon-dataset.json
    qs-gen-ar-ledger-accounts-dataset.json     # 21 AR datasets
    qs-gen-ar-subledger-accounts-dataset.json
    qs-gen-ar-transactions-dataset.json
    qs-gen-ar-ledger-balance-drift-dataset.json
    qs-gen-ar-subledger-balance-drift-dataset.json
    qs-gen-ar-transfer-summary-dataset.json
    qs-gen-ar-non-zero-transfers-dataset.json
    qs-gen-ar-limit-breach-dataset.json
    qs-gen-ar-overdraft-dataset.json
    qs-gen-ar-sweep-target-nonzero-dataset.json
    qs-gen-ar-concentration-master-sweep-drift-dataset.json
    qs-gen-ar-ach-orig-settlement-nonzero-dataset.json
    qs-gen-ar-ach-sweep-no-fed-confirmation-dataset.json
    qs-gen-ar-fed-card-no-internal-catchup-dataset.json
    qs-gen-ar-gl-vs-fed-master-drift-dataset.json
    qs-gen-ar-internal-transfer-stuck-dataset.json
    qs-gen-ar-internal-transfer-suspense-nonzero-dataset.json
    qs-gen-ar-internal-reversal-uncredited-dataset.json
    qs-gen-ar-expected-zero-eod-rollup-dataset.json
    qs-gen-ar-two-sided-post-mismatch-rollup-dataset.json
    qs-gen-ar-balance-drift-timelines-rollup-dataset.json

Demo mode

A deterministic demo generator seeds all three apps end-to-end so you can see them work without wiring up real data. Investigation rides on the shared transactions + daily_balances base tables; its scenario-shaped seed lands in K.4.6.

# Emit SQL only (no DB connection needed) — schema ships in the wheel,
# `demo schema` writes a copy out for inspection or hand-loading.
quicksight-gen demo schema --all -o /tmp/schema.sql
quicksight-gen demo seed   --all -o /tmp/seed.sql

# Apply schema + seed to PostgreSQL, then generate QuickSight JSON
# Requires: demo_database_url in config.yaml and `pip install -e ".[demo]"`
quicksight-gen demo apply --all -c config.yaml -o out/

demo apply creates tables + views, inserts the sample data, writes a datasource.json derived from the database URL, and generates all QuickSight JSON. Both apps feed two shared base tables — transactions (every money-movement leg) and daily_balances (per-account end-of-day snapshots) — plus AR-only dimension tables (ar_ledger_accounts, ar_subledger_accounts, ar_ledger_transfer_limits). The account_type and transfer_type columns discriminate which app a row belongs to. See Schema_v3.md for the full feed contract, canonical type values, metadata key catalog, and ETL examples for piping production data into the same shape.

PostgreSQL 17+ is required for demo apply: the schema uses SQL/JSON path syntax (JSON_VALUE, JSON_QUERY, JSON_EXISTS) for the metadata TEXT columns, and the portable subset forbids the Postgres-only ->> / -> / @> / ? operators and JSONB.

Datasets are all Direct Query (no SPICE), so seed changes show up immediately after a fresh demo apply — no refresh step needed.

Demo scenarios

• Payment Recon — Sasquatch National Bank (merchant settlement). Six fictional Seattle coffee shops (Bigfoot Brews, Sasquatch Sips, Yeti Espresso, Skookum Coffee Co., Cryptid Coffee Cart, Wildman's Roastery). Sales flow into settlements and payments; planted unsettled sales, returned payments, amount mismatches, and orphan external transactions populate every exception table.
• Account Recon — Sasquatch National Bank (treasury / GL). Same bank from the treasury side, after SNB absorbed Farmers Exchange Bank's commercial book. Eight internal GL control accounts (Cash & Due From FRB, ACH Origination Settlement, Card Acquiring Settlement, Wire Settlement Suspense, Internal Transfer Suspense, Cash Concentration Master, Internal Suspense / Reconciliation, Customer Deposits — DDA Control) plus per-customer DDAs for three coffee retailers (Bigfoot Brews, Sasquatch Sips, Yeti Espresso) and four commercial customers (Cascade Timber Mill, Pinecrest Vineyards, Big Meadow Dairy, Harvest Moon Bakery). The Cash Management Suite drives four telling-transfer flows — ZBA / Cash Concentration sweeps, daily ACH origination sweeps to the FRB Master Account, external force-posted card settlements, and on-us internal transfers through Internal Transfer Suspense. Each flow plants both success cycles and characteristic failures so every Exceptions check (including the cross-check rollups) surfaces distinct rows.

Theming

Preset	Palette	Analysis name prefix
default	Navy / blue / grey	—
sasquatch-bank	Forest green + bark brown + bank gold	Demo —
sasquatch-bank-ar	Valley green + harvest gold + earth	Demo —

Set theme_preset: in config.yaml (or pass --theme-preset to generate / deploy --generate). Add a new preset by declaring a ThemePreset in src/quicksight_gen/common/theme.py and registering it in PRESETS.

Rich-text on the Getting Started sheets (headings, bullets, hyperlinks) uses the preset's accent color, resolved to hex at generate time.

Project structure

src/quicksight_gen/
    __main__.py         # python -m quicksight_gen entry point
    cli.py              # Click CLI — generate / deploy / cleanup / demo
    common/
        config.py       # Config dataclass + YAML/env loader
        models.py       # Dataclasses mapping to QuickSight API JSON
        theme.py        # Theme presets (default, sasquatch-bank, sasquatch-bank-ar, sasquatch-bank-investigation)
        deploy.py       # Python deploy (delete-then-create, async waiters)
        cleanup.py      # Tag-based cleanup of stale resources
        clickability.py # Conditional-format helpers (plain + menu-link accent styles)
        rich_text.py    # XML helpers for SheetTextBox.Content (heading/bullets/…)
    apps/
        payment_recon/
            analysis.py     # 6 sheets, drill-downs, filter groups, dashboard
            visuals.py      # Sales / Settlements / Payments / Exceptions visuals
            recon_visuals.py# Payment Reconciliation side-by-side tables + KPIs
            filters.py      # Pipeline-tab filter groups + controls
            recon_filters.py# Payment Reconciliation filters
            datasets.py     # 11 custom-SQL datasets
            demo_data.py    # Sasquatch Bank demo data generator
            constants.py    # Sheet + dataset identifier constants
        account_recon/
            analysis.py     # 5 sheets, drill-downs, filter groups, dashboard
            visuals.py      # Balances / Transfers / Transactions / Exceptions visuals
            filters.py      # Per-tab filters + Show-Only-X toggles
            datasets.py     # 21 custom-SQL datasets
            demo_data.py    # Sasquatch National Bank — CMS treasury demo data generator
            constants.py    # Sheet + dataset identifier constants
        investigation/      # K.4.2 skeleton — sheets land in K.4.3 / K.4.4 / K.4.5
            analysis.py     # 4 sheets: Getting Started + 3 stubs (Fanout / Anomalies / Money Trail)
            visuals.py      # placeholder
            filters.py      # placeholder (no filter groups yet)
            datasets.py     # placeholder (no datasets yet)
            demo_data.py    # placeholder (K.4.6 plants scenarios)
            etl_examples.py # placeholder (no app-specific ETL keys)
            constants.py    # SheetId constants
    schema.py           # `generate_schema_sql()` — reads the canonical DDL
    schema.sql          # Canonical PostgreSQL DDL (interface contract for ETL); shared `transactions` + `daily_balances` base layer + AR dimension tables
    docs/               # mkdocs site source — handbook/, walkthroughs/, Schema_v3.md, Training_Story.md (extract via `quicksight-gen export docs`)
    training/           # Whitelabel handbook kit — handbook/, mapping.yaml.example (extract via `quicksight-gen export training`)
tests/
    test_models.py, test_generate.py, test_recon.py, test_account_recon.py,
    test_theme_presets.py, test_demo_data.py, test_demo_sql.py
    e2e/                # Two-layer e2e (API + browser); skipped unless QS_GEN_E2E=1
run_e2e.sh              # One-shot: generate + deploy + e2e
config.example.yaml

Tests

pytest                  # unit + integration (fast, no AWS)
./run_e2e.sh            # regenerate + deploy both 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 (344 tests): models, tags, config, CLI, demo determinism + FK integrity + scenario coverage, theme preset registry, dataset builders, visual builders, filter groups, cross-reference validation (dataset ARNs, filter bindings, visual ID uniqueness, sheet scoping), explanation coverage, schema + seed SQL structure.
• E2E (101 tests): two layers gated by QS_GEN_E2E=1.
  • API layer (boto3) — resource existence, status, dashboard structure, dataset import health.
  • Browser layer (Playwright WebKit, headless) — dashboard loads via pre-authenticated embed URL, sheet tabs, per-sheet visual counts, drill-downs, mutual-filter reconciliation tables, date-range filter narrowing, Show-Only-X toggles.

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).

Known limitations

Drill-down parameters stack across tab-switches

QuickSight has no API to clear a parameter on tab-switch. When a drill-down sets a parameter on its destination sheet (e.g. clicking a settlement_id on Settlements navigates to Sales and sets pSettlementId), the parameter stays set even after the user tabs away and back — the destination sheet stays filtered to that one value.

Workaround: refresh the dashboard tab in the browser to clear all parameter filters.

Captured as an xfail(strict=False) characterization test in tests/e2e/test_filter_stacking.py so the behavior is documented and would surface if AWS ever fixes it.

Customising

Change the SQL

Edit the dataset builders in <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 (transactions, daily_balances) plus the AR-only dimension tables. To wire your production data in, ETL into the same shape: see Schema_v3.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. Add the builder function in <app>/visuals.py.
2. Wire it into the sheet layout in <app>/analysis.py.
3. Add a subtitle (coverage tests enforce this).
4. Run pytest.

Add a filter

1. Add the FilterGroup builder in <app>/filters.py (or recon_filters.py for PR).
2. Add a matching FilterControl.
3. Register it on the relevant sheet's FilterControls in analysis.py.
4. pytest will flag any broken SourceFilterId references.

Add a theme preset

Declare a ThemePreset in common/theme.py and add it to the PRESETS dict. Set analysis_name_prefix="Demo" if it should tag analyses with a demo prefix.

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.