Budget Diarization Platform
Monthly commercial budget split into daily store-category rows with audited calibration
The team needed to automate monthly multi-store commercial budget planning: turning monthly totals into daily amounts by store and category with fidelity to warehouse conventions, instead of fragile spreadsheet workflows.
- 01Enforcing accounting identities and signed deduction conventions aligned to the warehouse
- 02Deriving daily share weights from historical revenue when some store-days are closed or missing history
- 03Allowing manual calibration without breaking monthly gross-revenue and profit totals
- 04Keeping a strict dual-database boundary: read-only warehouse history vs. app persistence for versioned exports
Historical share-based diarization with uniform fallback
Daily weights come from historical gross revenue per store×category. When history is missing, uniform shares keep the cycle unblocked; closed days trigger renormalization so monthly totals still close.
Pluggable calendar reference strategies
Default mapping uses a fixed day offset that preserves weekday. An alternate strategy maps the N-th weekday occurrence in the prior year. A strategy registry keeps the UI and rematerialization path swappable without rewriting the share engine.
Calibration with locks, proportional redistribution, and audit log
Planners can adjust selected fields at row, day, or store-day scope. Locked rows are excluded from redistribution; complementary fields are rateio'd so monthly invariants hold. Every change stores justification and snapshots for undo.
Chunked versioned export persistence
Large warehouse-shaped exports are persisted in begin/append/finalize steps to avoid proxy size limits, with abort on failure and a current-version flag for history rehydrate.
Output quality depends on warehouse history coverage. Thin history falls back to uniform shares and needs more calibration. The multi-step wizard is heavier than a single spreadsheet, but critical validation gates and auditability outweigh the friction.
Full-stack TypeScript app (TanStack Start) with a Zustand-backed wizard: upload, validate, reference mapping, diarization, calibration, holidays, export, and history. Server functions talk to SQL Server for historical shares and MySQL for versioned exports via dbmate migrations. Docker image deployed to Kubernetes through a CI pipeline.
Replaced a spreadsheet-heavy monthly planning cycle with a guided wizard that validates inputs, applies historical share weights, preserves monthly totals under calibration, and exports versioned warehouse-shaped files.
- ›Warehouse sign conventions for taxes and cost deductions must be normalized at ingest or identity checks fire false positives
- ›Closed-store days require share renormalization; otherwise daily rows underfill the monthly total
- ›Chunked persistence is necessary once export payloads hit reverse-proxy body limits. Draft abort prevents orphan partial versions