Skip to content

Sankey Diagram — Architecture & Design

Last Updated: 2026-08-11 Location: Spending page, #page-overview in index.html, below the category breakdown API Endpoint: GET /api/sankey?start_date=YYYY-MM-DD&end_date=YYYY-MM-DD

The /sankey route redirects to the wrong page

app.py redirects /sankey to /money, with a comment claiming Cash Flow moved to the Money page. It did not. #sankeyChart is still inside #page-overview in index.html, so the redirect lands on a page with no diagram. Either move the chart or point the redirect at /.

What It Shows

A multi-stage cash flow diagram: payroll deposits flow into BofA Checking, which then distributes to mortgage, credit cards, savings, and other transfers. Credit card nodes fan out further into spend categories.

Bravo ──┐
        ├──► BofA Checking ──► Mortgage
Optomi ─┘                  ├──► Chase CC ──┐
                           ├──► BofA CC 8144 ──┤──► Food & Grocery
                           ├──► BofA CC 7503 ──┤──► Utilities
                           ├──► Capital One    ├──► Restaurant
                           ├──► BofA Savings   └──► (other categories)
                           └──► Direct Debit ──► Utilities / Travel / etc.

Database Views

Three views power the diagram. All use bank_transactions JOIN bank_statements ON statement_id — no hardcoded filenames. Account classification is driven by bank, account_type, and account_last4 from bank_statements.

vw_sankey_income

Credits to BofA checking only (payroll deposits). Filters out credits on other accounts (Capital One, credit cards) to avoid double-counting.

WHERE bt.type = 'credit'
  AND bs.bank = 'Bank of America'
  AND bs.account_type = 'checking'

Sources classified as: BravoLt, Optomi, Other Income (by description keyword match).

vw_sankey_transfers

Debits from BofA checking, classified by destination. Includes both category = 'Transfer' AND category = 'Mortgage' rows — mortgage payments are categorized as Mortgage not Transfer by the ingest pipeline.

Each branch matches on merchant_normalized or a description pattern. The description fallbacks exist because merchant_normalized is populated by the ingest classifier and older rows predate the rules that would have set it. Branches are evaluated in order, so the two last4-specific BofA CC branches must stay above the generic one.

Destination label Match logic
Mortgage merchant_normalized = 'mortgage (fifth third)', or description LIKE %5/3 MORTGAGE% or %FIFTH THIRD%
Chase CC merchant_normalized = 'chase credit card', or description LIKE %CHASE CREDIT CRD%
BofA CC 8144 merchant_normalized = 'credit card payment' AND description LIKE %CRD 8144%
BofA CC 7503 merchant_normalized = 'credit card payment' AND description LIKE %CRD 7503%
BofA CC (other) merchant_normalized IN ('credit card payment', 'boa credit card'), or description LIKE %CREDIT CARD%. Catches payments with no last4 in the description
Capital One merchant_normalized = 'capital one transfer', or description LIKE %CAPITAL ONE%
BofA Savings merchant_normalized IN ('scheduled transfer to sav', 'banking transfer to sav'), or description LIKE %TRANSFER TO SAV%
Personal Transfers merchant_normalized IN ('venmo', 'paypal', 'zelle'), or description LIKE %VENMO% or %PAYPAL%
Other Transfer Everything else

vw_sankey_spend

Actual charges on each credit card account plus non-transfer direct debits from BofA checking. Has an account column (not just category) so the API can route spend to the correct intermediate CC node.

-- account values:
'Chase CC'      -- bs.account_type = 'credit_card' AND bs.bank = 'Chase'
'BofA CC 8144'  -- bs.account_type = 'credit_card' AND bs.account_last4 = '8144'
'BofA CC 7503'  -- bs.account_type = 'credit_card' AND bs.account_last4 = '7503'
'Direct Debit'  -- everything else in scope, i.e. BofA checking debits

Row scope is type = 'debit', category NOT IN (Transfer, Income, Mortgage), and the statement is either any credit card or BofA checking specifically.

Capital One credit card spend falls into Direct Debit

The account CASE has no Capital One branch, and the outer WHERE admits every credit card statement. A Cap1 credit card charge therefore lands under Direct Debit, which reads as a checking debit. Cap1 is currently checking and savings only, so nothing is misfiled today. Add a branch before loading a Cap1 credit card statement.

API (api_sankey in app.py)

Queries all three views, then assembles nodes and links for a 3-stage Plotly Sankey:

  1. Stage 1: Income sources → BofA Checking
  2. Stage 2: BofA Checking → transfer destinations (Mortgage, CC nodes, Capital One, etc.) + Direct Debit spend categories
  3. Stage 3: CC nodes → spend categories (actual charges on each card)

Node colors are server-supplied in node_colors array. The client uses them directly.

The date filter is month-granular, not day-granular

All three views pre-aggregate to month_start. api_sankey filters with month_start >= DATE_TRUNC('month', start_date) and <= DATE_TRUNC('month', end_date), so a range of 2026-03-15 to 2026-04-10 returns all of March and all of April. Every other endpoint on the page honours exact days, so the Sankey will not tie out against the KPI strip on a partial month. With no dates supplied the label is All Time.

The query runs with SET LOCAL statement_timeout = '8000' in an explicit transaction. Three view scans over the full history are the slowest thing on the page.

CC Detail Toggle (client-side)

A button in the section header switches between two views of the same data — no extra API call.

  • CC Detail (default): Chase CC, BofA CC 8144, BofA CC 7503 as separate intermediate nodes
  • Combined CCs: All three collapse into a single Credit Cards node

The toggle is implemented in buildSimpleSankey() in index.html. It rebuilds nodes and links from the cached _sankeyData response.

CC_NODES set is defined in two places — keep them in sync if adding a new CC account. Both currently hold {Chase CC, BofA CC 8144, BofA CC 7503, BofA CC (other)}:

  1. api_sankey() in app.py — Python set used to classify intermediate nodes
  2. index.html:1528 — JS const CC_NODES = new Set([...]) used by buildSimpleSankey()

Adding a New Bank Account

When a new account's statements are ingested:

  1. The bank_statements row will have bank, account_type, account_last4 populated by the parser
  2. If it's a checking account: credits automatically flow into vw_sankey_income if it's BofA checking; transfers flow into vw_sankey_transfers
  3. If it's a credit card: charges automatically appear in vw_sankey_spend under Direct Debit unless you add a CASE branch for the new last4
  4. To give it its own node: add a CASE branch in vw_sankey_spend, add it to CC_NODES in both app.py and index.html, add a color entry in the COLORS dict in api_sankey()

Known Limitations

  • BofA CC (other) catches generic boa credit card payment descriptions that don't include a last4 — these are older payment records where the description format didn't include the card number. They show as a transfer from checking but their actual spend doesn't appear in vw_sankey_spend (no matching CC statement loaded for those).
  • Capital One transfers show as a single lump — no breakdown of checking vs savings vs which Capital One account. This is intentional; Capital One is treated as a savings/transfer destination, not a spend account.
  • The diagram only reflects months where BofA checking statements have been loaded. Gaps in statement coverage = gaps in the sankey.