Plan: Financials Dashboard — Forecast Model + Monthly % of Sales

Repo: ~/ai-projects-local/financials-dashboard Live URL: rb.alpineanalytica.com/financials/

Resume Instructions (read first)

1. Read this file end-to-end.
2. Read the full design plan at /Users/colegorringe/.claude/plans/expressive-frolicking-eich.md — it has every code block, file path, and acceptance check.
3. Start with Build Order step 1 (kpiMonths partial-month fix in Dashboard.tsx) — smallest, lowest-risk, validates the dev loop still works.
4. Do not start a new design conversation. The plan is approved. Execute it.

Why this exists

Ken (controller at RBD) builds his annual P&L forecast with a driver model:

• Set an annual revenue target.
• Distribute it across months using prior-year seasonality.
• Forecast each cost dept as either % of revenue or flat $/month.

The dashboard currently has:

• A budget_summary table and a Budget vs Actual view (good — the storage layer matches).
• A budget generator that only does "growth rate from prior year" (wrong model for Ken).
• A % of Net Sales tab that only shows YTD totals (not useful — needs monthly breakdown).

So we need to (a) add Ken's forecast model, (b) make the % of Sales view monthly, and (c) stop counting partial months in the KPI tiles.

What's in the plan (3 features)

Feature 1 — % of Net Sales becomes a monthly view

• Frontend only. Dashboard.tsx.
• Replace the two YTD columns with one column per displayed month showing that month's % of net sales, plus a YTD % at the end.
• Also fixes an existing denominator bug (nsTotal uses kpiMonths, ytdActual uses displayMonths — they need to agree).

Feature 2 — Forecast Model

• Backend: new GET /api/admin/forecast-setup and PUT /api/admin/generate-forecast in backend/routers/admin.py. Writes to existing budget_summary table.
• Frontend: new types and functions in frontend/src/api/settings.ts; new "Forecast Model" card in frontend/src/pages/Settings.tsx.
• Inputs: annual revenue target + per-dept driver (pct_sales or fixed_monthly).
• Outputs land in the existing Budget vs Actual tab — no new dashboard view.

Feature 3 — KPI partial-month detection

• Frontend only. Dashboard.tsx.
• kpiMonths filters out months with < 50% of top-3 month revenue. Stops Jun–Sep 2026 (only $200–370K each, vs $5–7M closed months) from polluting the KPI tiles.

Build Order (from the design plan)

1. Dashboard.tsx — kpiMonths partial-month fix
2. Dashboard.tsx — revMonthly memo + fix nsTotal + pct view monthly columns
3. backend/routers/admin.py — forecast-setup + generate-forecast endpoints
4. frontend/src/api/settings.ts — new types + functions
5. frontend/src/pages/Settings.tsx — Forecast Model section
6. Build + deploy + smoke test

Each step is small and independently verifiable. Don't batch.

Files that will be touched

Context that informs this work (from the paused session)

• Today: 2026-06-12. May 2026 books are not yet closed per Cole/accounting — the dashboard's May numbers (Revenue $6.33M, COGS $2.76M, GP $3.58M, OpEx $1.01M, NI ≈$2.57M) faithfully reflect NetSuite, but reflect partial postings, not final close.
• NetSuite client in backend/core/netsuite.py uses OAuth 2.0 / JWT ES256 (CLIENT_ID ac8bd8..., ACCOUNT_ID 4582045, CERTIFICATE_ID Zhjk..., private key /var/www/html/warehouseApi/private-key.pem). SuiteQL hits TransactionAccountingLine filtered by tal.posting='T' and t.trandate window.
• Sign convention: revenue is stored negative (credit), expenses positive (debit). Existing _seed_year writes raw amount; UI flips for display.
• Auth is bcrypt + JWT HS256 with FINANCIALS_JWT_SECRET env var. Tokens last 30 days. Cole's login: cole@alpineanalytica.com / rbd2026 (reset earlier today).
• DB file ownership: /var/www/html/financials-dashboard/backend/data/financials.db is owned by www-data. Any write from a shell needs sudo -u www-data.

Risk / things to be careful about when resuming

• Don't touch existing budget generator — the forecast generator is a new endpoint, not a rewrite. Both should coexist (Ken can pick which model to use per year).
• Revenue dept identification — query gl_mapping JOIN gl_accounts WHERE acct_type IN ('Income','OthIncome'). Don't hardcode dept names.
• DELETE before INSERT in generate-forecast — wipe existing budget_summary rows for each affected dept+year before writing the new forecast, or you'll double-up with old budget rows.
• INSERT OR REPLACE only replaces rows it has — preserves rows the new pull omits. This is fine for the actuals refresh, but think carefully when applying the same pattern to forecast.
• import.meta.env in TypeScript — frontend/tsconfig.json already has "types": ["vite/client"] (added in the deploy work earlier today). Don't remove it.
• Deploy steps for the financials dashboard: rsync backend with --exclude='data/' (critical, or you'll wipe the prod SQLite); rm -rf /tmp/financials-backend/ before rsync; apache2ctl configtest before any reload; use apache2ctl graceful not systemctl restart apache2.

Acceptance / verification checks (from design plan)

1. npm run build — clean TypeScript compile, zero errors.
2. Dashboard 2026 → KPI "Xmo posted" shows 5, not 7 (Jun–Sep excluded).
3. % of Sales tab → monthly % columns, no YTD dollar column.
4. Toggle Full year on in pct mode → percentages stay correct.
5. Settings → year 2026 → Forecast Model section loads with prior-year reference.
6. Enter $80.5M revenue → monthly preview sums to $80.5M.
7. Set drivers → Generate → success message shows depts updated.
8. Dashboard → Budget vs Actual tab → forecast figures show in budget column for Jun–Dec.
9. KPI Net Sales tile shows "Budget: $X" or attainment badge.

Execution Log

2026-06-12 — Paused

Design plan written and approved (/Users/colegorringe/.claude/plans/expressive-frolicking-eich.md). No code written yet. Resume from Build Order step 1.