Dashboards & Reporting
S-Corp Finance & Ops Command Center
Built for a small S-corp agency that ran its books out of a shared Google Sheet, QuickBooks, and a stream of emailed bank and client statements, this is a local command center: one SQLite ledger drives payroll and W-2 prep, quarterly filings, banking, client and contractor tracking, a monthly owner report, and a 90-day cash-flow forecast. It deliberately has no auth and never leaves the machine — the tradeoff for holding SSNs and full financial detail — and an AI email pipeline reads incoming statements out of Gmail while holding sensitive documents back from the model entirely.
Stack
Concepts
How it works
The whole flow, traced from your first tap.
- 01
Gmail + email-ingest
A statement lands in the inbox
The classifier only ever sees what's safe to see — a regex pre-filter holds SSN, W-2, 1099, and K-1 documents back before anything reaches the model, while everything else is matched against a live snapshot of real accounts and client aliases pulled from the database itself.
Gmail API OAuth2sha256 dedupeSSN/W-2 pre-filter - 02
lib/ledger.ts
The ledger interprets messy reality
canonicalStatus and statusKind turn free-text into a fixed enum without misreading 'did not receive' as paid, parseDateLoose absorbs five different real-world date formats, and matchParty attributes a line to the right client or contractor by longest-alias match.
Negation-aware status regexLoose date parsingAlias matching - 03
lib/sheet-pull.ts / sheet-push.ts
Books stay in sync with the sheet, both ways
Pulling from the sheet diffs the entire workbook against the ledger and only touches months actually present in it; pushing back writes just the clean payments tab, one exact cell at a time, so the two directions never fight over the same data.
Full-workbook diffPer-cell A1 writesOpt-in removals - 04
lib/bank-paste.ts
Paste a bank balance, get it read and placed
Raw text copied off a bank website gets turned into structured balances by a local LLM call, and the sheet is only updated when exactly one cell unambiguously matches that account and period — otherwise it stays untouched rather than guessing.
LLM CLI extractionSingle-match write guard - 05
lib/monthly-report.ts
The month closes into an owner report
buildMonthlyReport composes the narrative purely from monthly_pnl, bank_statements, payroll_runs, and owner_distributions — loan and liability snapshots are gated to the latest month only, a fix that came out of a documented bug sweep that also caught a card-detection regex missing American Express.
Pure report builderisLatestMonth gatingDocumented bug sweep - 06
report-export/ + Vercel CLI
Publish without drift
Publishing copies the canonical report view into a second, isolated app with no database and no SSNs, gates it behind a hashed single-password cookie, and deploys it via a tracked Vercel CLI child process so an in-flight deploy can be cancelled cleanly mid-run.
File-synced twin appPassword-only cookie gateCancelable deploy
The problem
The books lived across a Google Sheet, QuickBooks, and whatever landed in an inbox, with no single source of truth and no fast way to answer did this month's numbers actually reconcile. Reconciling meant manually cross-checking a spreadsheet against bank emails and a QuickBooks snapshot that was already stale by the time anyone looked at it.
What we built
A Next.js app over a local SQLite ledger where monthly_pnl and bank_statements are the one source of truth that the dashboard, client/contractor views, and the owner-facing Monthly Report all derive from — the QuickBooks financials table is kept only as a labeled reference snapshot, never a dependency. lib/ledger.ts does the interpretive work: canonicalStatus and statusKind collapse messy free-text statuses into a fixed enum with negation-aware regex (so 'did not receive' never reads as paid), parseDateLoose handles five real-world date formats, and matchParty attributes a line to a client or contractor by longest-alias match.
Preview before write, everywhere
Every path that touches money follows the same shape: parse, diff against the database, hand the operator a server-held preview token, then apply only on explicit confirmation. Google Sheet import (lib/sheet-pull.ts) diffs the whole workbook into an add/change/remove preview before touching a row; the reverse sync (lib/sheet-push.ts) writes only the clean payments tab back cell-by-cell; and lib/bank-paste.ts lets the operator paste raw bank-website text, has an LLM extract structured balances, then only writes into the sheet if exactly one matching cell is found for that account and period.
Email intake that knows what not to show the model
lib/email-ingest/extract.ts turns a Gmail attachment into a signal for the classifier, but a regex pre-filter (looksSensitive) holds SSN, W-2, 1099, and K-1 documents back from the LLM entirely. Everything else is classified against a live snapshot of real bank accounts, brand aliases, and client names pulled straight from the database, then routed through the same preview-token-then-apply pattern — sha256 dedupe and Gmail labeling mean re-forwarding the same PDF twice is always a no-op.
Outcome
One operator runs the entire back office from a single local app: payroll actuals, quarterly filings, a bank-reconciled monthly close, and a cash-flow forecast that learns each recurring payer's typical pay-day from history instead of hand-entered assumptions. The owner-facing report publishes to a second, fully separate read-only app (report-export/) with no database and no SSNs, gated by a password-only cookie, and synced from the canonical view at publish time so the deployed report can never drift from what the operator actually saw.
Interested in something similar?
Tell us what you need and we'll figure out how to ship it.