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.comrequires 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:
- Live data is the explicit ask — Ken wants to "refresh the income statement anytime." Excel and Tableau can't do this cleanly.
- The FastAPI + React pattern on rb.alpineanalytica.com is proven and the deploy scripts already exist. This is not a greenfield infrastructure problem.
- Custom GL mapping lives in a JSON config file — trivial to update when accounts change vs. Tableau's mapping table maintenance.
financials.rileyblakedesigns.comwith a Ken-specific login is a professional, durable solution with no per-seat license cost.- 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.pemlocation on rb.alpineanalytica.com; confirm WarehouseAPI role has GL/finance permissions (not just inventory/orders) -
☐ Test SuiteQL query specifically against
TransactionAccountingLine(not justAccounttable — 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:
TransactionAccountingLineposting=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.dbSQLite (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 breakdownGET /api/pl/summary?year=— dept-level totals onlyGET /api/budget?year=&months=— GL-level budgetGET /api/budget/vs-actuals?year=&months=— variance tableGET /api/actuals/years— available years in DBPOST /api/actuals/refresh/{year}— pull fresh year from NetSuite (background task)GET /api/admin/payroll-allocations?fiscal_year=— get allocation ratiosPUT /api/admin/payroll-allocations— update ratios (validates sum = 1.0)GET /api/admin/configGET /health- ☑ Auth — API key protection:
X-API-Keyheader required on all data endpoints (read)X-Admin-Keyheader required on all write/admin endpoints- Keys stored in encrypted secrets (
~/ai-projects-local/shared-knowledge/secrets.env.enc) /healthremains open for monitoring- ☑ Systemd service —
financials-api.service, auto-restarts, 2 workers - ☑ Apache reverse proxy —
/financials-api/path onrb.alpineanalytica.comSSL 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=1in 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_snapshotstable) - ☐ Period close status query (
AccountingPeriodtable) - ☐ Budget re-upload endpoint (
POST /api/budget/uploadwith 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.shfollowing 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.com→rb.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.comfrom 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)
- 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.
- DNS: Who controls the DNS for rileyblakedesigns.com? Cole needs to add a CNAME record.
- Department dimension: Do expense GL transactions in NetSuite have a department/class field? This is required for the departmental P&L view.
- Fiscal year: Calendar year (Jan–Dec) or a NetSuite fiscal year variant?
- % of sales denominator: Is the "% of sales" row in Ken's P&L calculated vs. net sales or gross sales?
- Variance sign convention: For expenses, is "over budget" shown as positive or negative variance? Ken to confirm.
- Budget data currency: Is the downloaded Excel the current 2026 budget, or has it been revised since?
- Ken's login email: What email for his dashboard account?
- 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
- 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)
- GL mapping validation: sum all mapped accounts per category, verify vs. Excel subtotals
- Budget load validation: Jan–Dec 2026 budget totals sum to annual projection in Excel
- Auth test: Ken's login from a different machine + browser
- 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_actualstable
Phase 2 backend — fully live:
- FastAPI app deployed to
rb.alpineanalytica.comasfinancials-api.service(systemd, 2 workers) - Apache reverse proxy at
/financials-api/on SSL vhost - Validated:
GET /api/pl/summary?year=2024returns $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
/healthremains 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 numbersns_monthly_2024.json: 1,801 rows (account × month) for all of 2024actuals_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
~/ai-projects/mission-control/plans/rbd-financial-dashboard.md