← Back to all projects
In Progress Created 2026-05-15 12/58 tasks

RBD Financial P&L Dashboard

Priority: P1 Category: PREP


Executive Summary

Build a live financial P&L dashboard at financials.rileyblakedesigns.com that pulls directly from NetSuite's REST API (SuiteQL) and displays Ken's custom GL account groupings. Replaces a manual Excel process with a browser-based tool giving Ken (finance) and Brett (exec) on-demand access to actuals vs. 2026 monthly budget with historical trending back to 2021. Mirrors the architecture of the existing rbd-reorder-tool (FastAPI + React on rb.alpineanalytica.com).


Research Phase

Current State

  • Ken maintains a P&L manually in Excel (2026 Projections - Tableau Groupings.xlsx)
  • NetSuite's native income statement doesn't match Ken's desired grouping structure
  • 199 GL accounts mapped across a 3-level hierarchy: Dept Summary → Line Item → GL Account (extracted and confirmed from Excel)
  • Two P&L views required:
  • Departmental rollup (6 expense groups: Shipping, Selling/Mktg/Adv, Product Creation, Warehouse, G&A, plus COGS breakdown)
  • Line-item detail (16 expense categories: Salaries, Freight, Royalties, Professional Services, Marketing, Credit Card Fees, Commissions, T&E, Computer/Software, Warehouse, Donations, Bad Debt, Product Creation, Office, Taxes, Insurance, Other)
  • Historical data: 2021, 2022, 2023, 2024 actuals + 2025 projected + 2026 monthly budget
  • No live dashboard exists — all analysis is manual

Technical Context

  • NetSuite account: 4582045 (Riley Blake Designs)
  • NetSuite REST API (SuiteQL): SQL-like queries against NetSuite data model; available on all NetSuite accounts; endpoint: POST /services/rest/query/v1/suiteql; auth: OAuth 1.0a
  • No existing NetSuite REST API credentials in encrypted secrets — need to create Integration record in NetSuite admin (one-time, ~30 min)
  • rb.alpineanalytica.com: Server already running rbd-reorder-tool (FastAPI + React); same deploy pattern applies
  • reorder tool pattern: FastAPI backend (Python), Vite + React frontend, rsync deploy, systemd service, nginx reverse proxy, Let's Encrypt SSL

Key Data Notes (from Excel analysis)

  • Rows 0–37 of rows 36: high-level summary P&L (departmental view)
  • Rows 47–84: line-item operating expense P&L
  • 199 GL accounts, 35+ columns of historical + projected data
  • Some GL accounts appear in multiple departments (e.g., 60050 - Earned Wages appears in Selling, Product Creation, Warehouse, G&A — NetSuite will have department attribute to disambiguate)
  • Budget data (2026 monthly, Jan–Dec) needs to be loaded from Excel → SQLite (one-time seed)

Constraints

  • NetSuite OAuth 1.0a credentials must be created before API work can begin
  • Ken's Tableau PC setup is a separate issue — out of scope for this project
  • WarehouseAPI on rb.alpineanalytica.com has NetSuite connectors but code changes require Cole's explicit approval → building as a standalone app avoids that constraint
  • DNS record for financials.rileyblakedesigns.com requires access to RBD's DNS (confirm who controls this: IT, Brett, or Ken)

Options Considered

Option 1: Tableau

Approach: Tableau workbook connected to NetSuite via ODBC (SuiteAnalytics Connect). Custom GL mapping via Tableau Prep. Publish to Tableau Server or Public. Pros: Finance teams expect it; drag-and-drop; no custom dev Cons: SuiteAnalytics Connect ODBC license required (~$600+/yr); GL mapping is fragile (separate maintenance); data extracts are scheduled, not live; Tableau Server needed for web sharing; Ken's PC Tableau is currently broken (per his email) Estimated Effort: Medium (2–3 days + license procurement)

Option 2: Custom Web App — FastAPI + React (Chosen)

Approach: New Python FastAPI backend + React frontend deployed to rb.alpineanalytica.com. Pulls live P&L from NetSuite SuiteQL. GL mapping in JSON config. 2026 budget in SQLite. Pros: Live data; fully custom P&L format; no license fees; matches existing deploy pattern; simple login for Ken; subdomain gives professional UX Cons: ~1–2 weeks build; Cole owns long-term maintenance; NetSuite OAuth 2.0 JWT client must be ported to Python (WarehouseAPI uses PHP/Laravel) Estimated Effort: High (1–2 weeks total)

Option 3: Extend WarehouseAPI (Laravel)

Approach: Add financial reporting module to existing WarehouseAPI on rb.alpineanalytica.com. Reuse existing NetSuite connectors. Pros: Existing NetSuite auth; already deployed Cons: All Laravel code requires Cole's explicit approval before editing; adds complexity to a production app; Brett must maintain PHP code Estimated Effort: Medium-High + approval overhead

Option 4: Python Script → Styled Excel

Approach: Script pulls NetSuite data, applies GL mapping, outputs formatted Excel matching Ken's Summary layout. Runs on demand or via cron. Pros: Simplest; zero infrastructure; immediate Cons: Not a live dashboard; not shareable without emailing a file; no browser access Estimated Effort: Low (2–3 days)


Chosen Approach

Decision: Option 2 — Custom Web App (FastAPI + React)

Rationale:

  1. Live data is the explicit ask — Ken wants to "refresh the income statement anytime." Excel and Tableau can't do this cleanly.
  2. The FastAPI + React pattern on rb.alpineanalytica.com is proven and the deploy scripts already exist. This is not a greenfield infrastructure problem.
  3. Custom GL mapping lives in a JSON config file — trivial to update when accounts change vs. Tableau's mapping table maintenance.
  4. financials.rileyblakedesigns.com with a Ken-specific login is a professional, durable solution with no per-seat license cost.
  5. No WarehouseAPI code approval bottleneck — clean standalone app.

Trade-offs Accepted:

  • More upfront dev time (~1–2 weeks vs. 2–3 days for a script)
  • Cole owns ongoing maintenance — GL account changes require config file update + redeploy
  • NetSuite JWT client must be ported from PHP to Python (30–60 min; proven pattern from WarehouseAPI)

Implementation Plan

Phase 0: Pre-Commit Spike (Day 1 — Full Day)

This phase is a go/no-go gate. Do not start Phase 1 until all 4 checks pass.

  • Confirm private-key.pem location on rb.alpineanalytica.com; confirm WarehouseAPI role has GL/finance permissions (not just inventory/orders)
  • Test SuiteQL query specifically against TransactionAccountingLine (not just Account table — auth can succeed but GL data tables may be permission-denied) sql SELECT tal.account, tal.debit, tal.credit, t.trandate, t.postingperiod FROM TransactionAccountingLine tal JOIN Transaction t ON tal.transaction = t.id WHERE t.posting = 'T' AND t.trandate >= '01/01/2024' LIMIT 10

  • Check department field population rate on 2024 expense transactions — if >5% NULL on accounts like 60050, departmental view is structurally impossible without a fallback strategy sql SELECT COUNT(*) total, SUM(CASE WHEN department IS NULL THEN 1 ELSE 0 END) null_dept FROM TransactionLine WHERE account = '60050' AND trandate >= '01/01/2024'

  • Confirm DNS control for financials.rileyblakedesigns.com

  • If Phase 0 fails on GL permissions or department data: escalate to Cole immediately — may need a new NetSuite integration record with finance role, or rethink approach

Phase 1: Data Exploration, Tie-Out & Config (Days 2–5) ✅ COMPLETE

  • Build correct SuiteQL P&L pattern: TransactionAccountingLine posting=T, JOIN Transaction for trandate, sign-flip income accounts
  • Extract 199-account GL mapping to gl_mapping.json (171 unique GL numbers; 28 multi-dept duplicates noted)
  • Unmapped accounts detector: all 70 non-mapped accounts are balance-sheet type (no P&L gaps)
  • Pull 2024 actuals monthly (account × month, 1,801 rows); Net Sales diff = $9,640 (0.019%)
  • Load 2026 monthly budget data into budget.db SQLite (pending — needs further extraction from Excel)
  • Fiscal year confirmed: calendar year Jan–Dec

Phase 2: Backend — FastAPI P&L Engine (Days 6–11) ✅ COMPLETE

All backend infrastructure live at https://rb.alpineanalytica.com/financials-api/

  • Historical data pulled: 2023, 2024, 2025 actuals from NetSuite (via 12 monthly SuiteQL queries per year)
  • SQLite database seeded (/var/www/html/financials-dashboard/backend/data/financials.db):
  • gl_accounts — 325 rows (324 active + 50903 inactive)
  • gl_mapping — 180 rows (multi-dept non-payroll deduplication applied)
  • ns_actuals — 3,019 rows (2023: 628, 2024: 1,779, 2025: 612)
  • budget — 1,261 rows (2026 monthly, 106 expense GLs from Projections sheet)
  • budget_summary — 144 rows (2026 dept-level from Summary sheet; revenue/COGS formula-driven)
  • payroll_allocations — 16 rows (4 depts × 4 years, 2023–2026; per-fiscal-year, updatable via API)
  • P&L engine — applies GL mapping + payroll allocation + sign convention; dept × line-item × GL structure; monthly breakdown
  • FastAPI routers:
  • GET /api/pl?year=&months= — full GL-level P&L with monthly breakdown
  • GET /api/pl/summary?year= — dept-level totals only
  • GET /api/budget?year=&months= — GL-level budget
  • GET /api/budget/vs-actuals?year=&months= — variance table
  • GET /api/actuals/years — available years in DB
  • POST /api/actuals/refresh/{year} — pull fresh year from NetSuite (background task)
  • GET /api/admin/payroll-allocations?fiscal_year= — get allocation ratios
  • PUT /api/admin/payroll-allocations — update ratios (validates sum = 1.0)
  • GET /api/admin/config
  • GET /health
  • Auth — API key protection:
  • X-API-Key header required on all data endpoints (read)
  • X-Admin-Key header required on all write/admin endpoints
  • Keys stored in encrypted secrets (~/ai-projects-local/shared-knowledge/secrets.env.enc)
  • /health remains open for monitoring
  • Systemd servicefinancials-api.service, auto-restarts, 2 workers
  • Apache reverse proxy/financials-api/ path on rb.alpineanalytica.com SSL vhost

Key technical decisions made:

  • GL 50903 (Outbound Freight): IS in NetSuite, isinactive=T — must never filter inactive in Account queries
  • Payroll allocation (60050/60200/60400): stored per fiscal year in DB, updatable via PUT /api/admin/payroll-allocations; admin UI will expose this
  • Non-payroll multi-dept GLs (60225, 60235): seeded only in dept where Ken has non-zero actual; prevents double-counting
  • OthIncome accounts (80150, 80200): sign-flip applied (same as Income)
  • GL 61940 Supplier Term Charge: sign_flip=1 in gl_mapping — displayed as negative per Ken's convention
  • Revenue/COGS 2026 budget: formula-driven in Excel → pulled from Summary sheet dept totals (not GL-level); LibreOffice headless noted as solution for future full Excel ingestion
  • Forecast strategy: Excel re-ingestion recommended (LibreOffice headless preprocessing); in-app forecast engine deferred — discuss with Brock

Remaining Phase 2 items (deferred to post-Brock):

  • Snapshot/aggregation layer for closed periods (nightly cron → pl_snapshots table)
  • Period close status query (AccountingPeriod table)
  • Budget re-upload endpoint (POST /api/budget/upload with LibreOffice preprocessing)
  • Excel export endpoint (GET /api/pl/export)

Phase 3: Frontend — React Dashboard (Days 12–16) ⏸ PAUSED — Awaiting Brock conversation

  • Scaffold: ~/ai-projects-local/financials-dashboard/frontend/ (Vite + React + Tailwind)
  • Login page (API key auth for MVP; upgrade to per-user JWT post-Brock)
  • P&L table — departmental view, expandable to line-item detail
  • Columns: row label | 2023A | 2024A | 2025A | 2026B | 2026YTD | Variance $ | Variance %
  • % of Net Sales rows below each major subtotal (confirm "net sales" is the denominator with Ken)
  • Period status badge per month ("Open" months show with a warning indicator)
  • Monthly view: budget vs. actual bar chart + variance table (Jan–Dec)
  • Historical trend view: line chart for Sales, Gross Profit, EBITDA, Net Income (2021–2026)
  • YoY comparison: same-period prior year (e.g., YTD 2026 vs YTD 2025, not full-year 2025)
  • Year/period selector
  • Number formatting: $#,##0 with parentheses for negatives; % at 0.1% precision
  • Export to Excel (Must-Have — Ken is an Excel user)
  • Admin panel: payroll allocation ratios editor (per fiscal year; validates sum = 1.0)
  • Forecast re-ingestion: Excel upload → LibreOffice headless → budget table upsert

Decisions pending Brock conversation:

  • Forecast strategy: Excel re-ingestion vs. in-app editor vs. hybrid
  • Auth model: API key (MVP) vs. per-user JWT login vs. SSO (Google Workspace?)
  • Domain: financials.rileyblakedesigns.com (needs DNS) vs. rb.alpineanalytica.com/financials
  • Scope: Ken + Brett only, or broader internal access?

Phase 4: Deploy & Handoff (Days 17–18)

  • Write deploy.sh following rbd-reorder-tool pattern (rsync backend + frontend dist)
  • Nginx config: financials.rileyblakedesigns.com → uvicorn on port 8003
  • SSL: Let's Encrypt certbot
  • DNS: add CNAME financials.rileyblakedesigns.comrb.alpineanalytica.com
  • Systemd service: rbd-financials.service + nightly snapshot cron
  • Create user accounts + TOTP setup for Ken, Brett, Cole
  • Smoke test all views with real data
  • Walk Ken through dashboard (or record a Loom)
  • Handoff doc: how to re-import updated budget Excel, how to add a user, what to do if numbers drift

Acceptance Criteria

Must Have

  • Ken can log in at financials.rileyblakedesigns.com from any browser, any machine (with MFA)
  • Departmental P&L matches Ken's Summary tab structure (2 COGS lines + 6 opex groups)
  • Line-item P&L matches Ken's second table (16 expense categories)
  • Closed-period historical data served from snapshots; current month from live SuiteQL
  • Open/Closed period status visible in UI so Ken knows which months are finalized
  • 2023, 2024, 2025 historical actuals accessible
  • 2026 monthly budget vs. actual with variance columns and correct favorable/unfavorable sign
  • Key totals (Sales, Gross Profit, EBITDA) match Ken's Excel for 2024 within rounding (verified with Ken)
  • Export to Excel (Ken is an Excel user — this will be used constantly)
  • Unmapped accounts alert in admin panel — flags any GL account in NetSuite not in gl_mapping.json

Should Have

  • MoM and YoY same-period comparisons (YTD 2026 vs YTD 2025, not full-year)
  • EBITDA % and Gross Margin % prominently displayed
  • Page loads in <5 seconds (closed periods from cache; open period <10 seconds acceptable)
  • Mobile-readable layout
  • Password reset via email

Nice to Have

  • Monthly automated email to Ken + Brett with P&L summary

Key Risks (Revised after Opus 4.7 Review)

Risk Likelihood Mitigation
WarehouseAPI's NetSuite role lacks GL/TransactionAccountingLine permissions Medium Phase 0 spike tests this specifically before any build begins — gate
Department field NULL on expense transactions → departmental view impossible Medium Phase 0 query checks NULL rate; >5% NULL changes the approach
SuiteQL P&L numbers don't tie to Ken's Excel on first pass High Budget 2 full days for tie-out iteration; schedule Ken call to identify manual reclasses
ECDSA JWT signature serialization mismatch (Python vs PHP) Medium Test JWT auth against NetSuite immediately in Phase 2 before building on top of it
Budget revised mid-year (Ken provides v2 Excel) High Budget re-import script built in Phase 1; POST /admin/budget/reload endpoint
GL accounts added to NetSuite but not in gl_mapping.json Medium Unmapped accounts detector; alerts visible in admin panel
Open period data changes unpredictably (ongoing JEs) High Period close status shown in UI; nightly snapshot for closed periods
NetSuite SuiteQL rate limits on 5-year historical pull Medium Snapshot/aggregation architecture; don't run live SuiteQL for historical periods
DNS for rileyblakedesigns.com not accessible to Cole Low-Medium alpineanalytica.com fallback subdomain while DNS sorted

Open Questions for Ken / Brett (Required Before Phase 1)

  1. Real need check: Would fixing Ken's Tableau PC setup solve the problem? If so, this whole project is the wrong answer. Confirm Ken actually wants a browser-based dashboard before building.
  2. DNS: Who controls the DNS for rileyblakedesigns.com? Cole needs to add a CNAME record.
  3. Department dimension: Do expense GL transactions in NetSuite have a department/class field? This is required for the departmental P&L view.
  4. Fiscal year: Calendar year (Jan–Dec) or a NetSuite fiscal year variant?
  5. % of sales denominator: Is the "% of sales" row in Ken's P&L calculated vs. net sales or gross sales?
  6. Variance sign convention: For expenses, is "over budget" shown as positive or negative variance? Ken to confirm.
  7. Budget data currency: Is the downloaded Excel the current 2026 budget, or has it been revised since?
  8. Ken's login email: What email for his dashboard account?
  9. Multi-currency: Does RBD have any foreign-currency transactions in NetSuite?

Questions for Ken — Post Phase 1 Validation (2026-05-20)

Compiled after full 2024 GL cross-check (171 accounts, 137 matched within $50, 0.08% total expense gap)

Q1 — GL 62000: Computer Equipment Under CAP ($34K gap)

  • Excel (Ken): $70,257.85 — NetSuite: $104,427.10 — Diff: +$34,169
  • Is your $70K figure the net-of-depreciation additions, or did you exclude a specific category of equipment?
  • NetSuite shows all capitalized additions hitting GL 62000 in 2024; Ken's number appears to be net or partial.

Q2 — GL 61940: Supplier Term Charge — display sign

  • Excel (Ken): -$150,529.14 (shown as negative) — NetSuite: +$150,529.14 (positive expense)
  • Absolute values match exactly — this is a display convention question only.
  • Do you want this shown as negative in the dashboard (as a credit against expenses, which reduces total OpEx), or as a standard positive expense line?

Q3 — Payroll allocation methodology (for projections)

  • Accounts 60050 (Earned Wages), 60200 (Bonus), 60400 (Payroll Tax) split across 4 departments.
  • NetSuite has no department dimension on these transactions — Ken manually allocates in Excel using fixed ratios: Selling 52.90% / Product Creation 14.10% / Warehouse 21.10% / G&A 11.90%.
  • Are these ratios fixed for the dashboard, or do they update annually with headcount changes?
  • Do the same ratios apply to 2026 budget projections?

Q4 — GL 67000: T&E ($5K gap, minor)

  • Excel: $69,979 — NetSuite: $75,136 — Diff: $5,157
  • Low priority, but worth a quick check — are there T&E entries you excluded from your projection base?

Verification Steps

  1. SuiteQL totals cross-check: pull 2024 Net Sales from SuiteQL → compare to Ken's Excel (row 27 = $42.2M for 2021; 2024 should be in column 8 of Projections sheet)
  2. GL mapping validation: sum all mapped accounts per category, verify vs. Excel subtotals
  3. Budget load validation: Jan–Dec 2026 budget totals sum to annual projection in Excel
  4. Auth test: Ken's login from a different machine + browser
  5. Page load time: < 5 seconds with SuiteQL query + mapping computation

Execution Log

2026-05-21 — Phase 2 complete, auth added

2023 + 2025 historical data:

  • Pulled via same 12-monthly-query pattern as 2024
  • 2023: 628 GL-month rows | 2025: 612 GL-month rows
  • All three years seeded into SQLite ns_actuals table

Phase 2 backend — fully live:

  • FastAPI app deployed to rb.alpineanalytica.com as financials-api.service (systemd, 2 workers)
  • Apache reverse proxy at /financials-api/ on SSL vhost
  • Validated: GET /api/pl/summary?year=2024 returns $51,040,839 net sales — matches cross-check
  • All department totals match Phase 1 validation within previously identified tolerances

Auth wired (30 min):

  • X-API-Key (read) + X-Admin-Key (write) pattern
  • No-key → 401, wrong-key → 401, correct-key → 200 — all verified live
  • Keys stored in encrypted secrets file
  • /health remains open

2026 budget loaded:

  • GL-level expense budget: 106 GLs × 12 months from Projections sheet
  • Dept-level revenue/COGS budget: from Summary sheet (formula-driven values captured)
  • Revenue budget 2026: $80,500,000 annual

Design decision — forecast ingestion:

  • Recommendation: Excel re-ingestion via LibreOffice headless (Ken keeps his workflow)
  • In-app forecast engine deferred — requires Brock/Ken alignment on process change
  • Data model already supports cell-level overrides if needed later

Files on server: /var/www/html/financials-dashboard/ Local code: ~/ai-projects-local/financials-dashboard/backend/

2026-05-15 — Plan created and reviewed

  • Analyzed 2026 Projections - Tableau Groupings.xlsx: 4 sheets, 199 GL accounts, 2-view P&L structure confirmed
  • WarehouseAPI on rb.alpineanalytica.com uses OAuth 2.0 JWT (ECDSA ES256) for NetSuite — credentials exist, no new integration record needed but GL permissions unverified
  • rbd-reorder-tool pattern confirmed as template (FastAPI + React, rsync deploy, rb.alpineanalytica.com)
  • Opus 4.7 review completed — major revisions: added Phase 0 go/no-go spike, snapshot architecture, period-close model, MFA auth, tie-out budget, revised timeline to 18 days, moved Export to Excel to Must-Have

2026-05-19 — Phase 0 + Phase 1 Complete

Phase 0 — GO:

  • OAuth 2.0 JWT auth confirmed working against NetSuite 4582045
  • TransactionAccountingLine accessible (GL permissions confirmed)
  • Department field: 99.7% NULL on expense transactions — departmental P&L uses GL-account-to-category mapping (Ken's approach), NOT NetSuite department dimension. Not a blocker.
  • Fiscal year: calendar year Jan–Dec confirmed

Phase 1 — Complete:

  • gl_mapping.json: 171 unique GL numbers extracted from Excel (199 rows, 28 duplicates across dept splits)
  • Some accounts (60050 Earned Wages, 60200 Bonus, 60400 Payroll Tax) appear in 4 dept categories — NetSuite has no dept dimension to split these; need to discuss allocation with Ken
  • ns_accounts.json: 324 active NS accounts fetched; 311 have numeric GL numbers
  • ns_monthly_2024.json: 1,801 rows (account × month) for all of 2024
  • actuals_2024.json: structured P&L with GL mapping context

Net Sales Cross-Check:

  • NetSuite SuiteQL: $51,040,838.91
  • Excel (Ken's): $51,031,198.79
  • Difference: $9,640.12 (0.019%) — within acceptable rounding/timing tolerance

2024 P&L Summary (NetSuite, not yet Ken-verified):

  • Net Sales: $51,040,838.91
  • Gross Profit: $24,453,639.04 (47.9%)
  • Operating Income: $4,766,165.10 (9.3%)

Key Finding — Department Dimension:

  • Accounts like 60050 (Earned Wages), 60200 (Bonus) appear across all 4 expense departments in Excel
  • NetSuite stores no department split on these — Ken manually allocates them in Excel
  • For Phase 2, these multi-dept accounts will need allocation ratios from Ken, or will be shown as undivided totals

Files saved to ~/ai-projects-local/financials-dashboard/:

  • gl_mapping.json (43KB)
  • ns_accounts.json (39KB)
  • ns_monthly_2024.json (166KB)
  • actuals_2024.json (structured output)

References

  • GL mapping source: /Users/colegorringe/Downloads/2026 Projections - Tableau Groupings.xlsx
  • NetSuite account ID: 4582045 (Riley Blake Designs)
  • NetSuite SuiteQL docs: https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section_156257770590.html
  • NetSuite OAuth 1.0a: https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section_157729198954.html
  • Suitelet deployment reference: ~/ai-projects-local/shared-knowledge/netsuite-suitelet-deployment.md
  • Infrastructure: ~/ai-projects-local/shared-knowledge/infrastructure.md
  • Reorder tool (deploy pattern): ~/ai-projects-local/rbd-reorder-tool/deploy.sh