Business Budget & Cash Flow Forecaster

You are a senior full-stack engineer specializing in financial management systems for Malaysian SMEs. Build a complete Business Budget & Cash Flow Forecaster that replaces Excel spreadsheets with a professional digital finance control system.

## Company Context
- Company Name: {{company_name}}
- Fiscal Year Start: {{fiscal_year_start}}
- Departments / Cost Centers: {{departments}}
- Budget Categories: {{budget_categories}}
- Currency: Malaysian Ringgit (RM)

## Tech Stack
- Framework: Next.js 14 (App Router) with TypeScript
- Styling: Tailwind CSS + shadcn/ui component library
- Database: Supabase (PostgreSQL with Row Level Security)
- Charts: Recharts (waterfall, line, bar, pie charts)
- Excel Export: xlsx library (for accountant handoff)
- Date Utilities: date-fns
- Number Formatting: Intl.NumberFormat with RM currency locale

## Database Schema

Create the following tables with all columns, indexes, and RLS policies:

### budget_periods
- id uuid PRIMARY KEY DEFAULT gen_random_uuid()
- fiscal_year integer NOT NULL
- start_date date NOT NULL
- end_date date NOT NULL
- status text CHECK (status IN ('draft', 'approved', 'locked', 'closed')) DEFAULT 'draft'
- approved_by uuid REFERENCES auth.users(id)
- approved_at timestamptz
- created_at timestamptz DEFAULT now()
- notes text

### budget_categories
- id uuid PRIMARY KEY DEFAULT gen_random_uuid()
- name text NOT NULL
- name_en text
- type text CHECK (type IN ('revenue', 'expense')) NOT NULL
- department text
- sort_order integer DEFAULT 0
- is_active boolean DEFAULT true
- created_at timestamptz DEFAULT now()

### budget_entries
- id uuid PRIMARY KEY DEFAULT gen_random_uuid()
- period_id uuid NOT NULL REFERENCES budget_periods(id) ON DELETE CASCADE
- category_id uuid NOT NULL REFERENCES budget_categories(id)
- month integer NOT NULL CHECK (month BETWEEN 1 AND 12)
- budgeted_amount numeric(15,2) DEFAULT 0
- actual_amount numeric(15,2) DEFAULT 0
- notes text
- updated_at timestamptz DEFAULT now()
- UNIQUE (period_id, category_id, month)
- CREATE INDEX ON budget_entries(period_id, month)
- CREATE INDEX ON budget_entries(category_id)

### cash_flow_entries
- id uuid PRIMARY KEY DEFAULT gen_random_uuid()
- entry_date date NOT NULL
- type text CHECK (type IN ('inflow', 'outflow')) NOT NULL
- category text NOT NULL
- amount numeric(15,2) NOT NULL CHECK (amount > 0)
- description text
- reference_no text
- is_recurring boolean DEFAULT false
- recurring_item_id uuid REFERENCES recurring_items(id)
- created_at timestamptz DEFAULT now()
- CREATE INDEX ON cash_flow_entries(entry_date)
- CREATE INDEX ON cash_flow_entries(type, entry_date)

### recurring_items
- id uuid PRIMARY KEY DEFAULT gen_random_uuid()
- category text NOT NULL
- type text CHECK (type IN ('inflow', 'outflow')) NOT NULL
- amount numeric(15,2) NOT NULL
- frequency text CHECK (frequency IN ('monthly', 'quarterly', 'annually')) NOT NULL
- start_date date NOT NULL
- end_date date
- description text
- is_active boolean DEFAULT true
- created_at timestamptz DEFAULT now()

### forecasts
- id uuid PRIMARY KEY DEFAULT gen_random_uuid()
- forecast_month date NOT NULL UNIQUE
- projected_inflow numeric(15,2) DEFAULT 0
- projected_outflow numeric(15,2) DEFAULT 0
- projected_balance numeric(15,2) DEFAULT 0
- actual_balance numeric(15,2)
- confidence_level text CHECK (confidence_level IN ('low', 'medium', 'high')) DEFAULT 'medium'
- generated_at timestamptz DEFAULT now()
- CREATE INDEX ON forecasts(forecast_month)

## Core Features

### Feature 1: Annual Budget Creation
- Create new budget year with configurable fiscal year start month
- Spreadsheet-style entry grid: rows = budget categories, columns = months (Jan–Dec)
- Inline cell editing — click a cell, type amount, Tab to move to next cell
- Bulk distribution options: split annual total evenly, weight by seasonality, or copy from prior year
- Copy prior year budget as baseline with percentage uplift option
- Save draft vs submit for approval — approved budgets lock all entries

### Feature 2: Actual vs Budget Tracking
- Monthly actual revenue and expense entry form
- Auto-calculate variance: amount = actual − budget, percentage = (actual − budget) / budget × 100
- RAG status coloring: green (variance within ±5%), amber (5–10%), red (>10% over budget)
- Multi-dimensional analysis: by department, by category, by month
- Quarterly summaries and year-to-date cumulative comparison table
- Drill-down: click any cell to see individual transactions behind the actual figure

### Feature 3: Cash Flow Tracking
- Record cash inflows: customer payments, loan drawdowns, capital injections
- Record cash outflows: supplier payments, payroll, rent, tax payments, loan repayments
- Set up recurring items (monthly rent, monthly payroll) that auto-generate entries
- Running bank balance calculation updated in real time
- Attach reference number or invoice number to each entry for audit trail

### Feature 4: 3–6 Month Cash Flow Forecast
- Auto-generate forecast from active recurring items
- Overlay historical trend (3-month moving average) to adjust projected inflows
- Waterfall chart: each bar shows net monthly change, line shows cumulative balance
- Minimum cash threshold alert: configure safe minimum, system warns when forecast dips below
- Cash runway calculation: current balance ÷ average monthly net burn = months remaining
- Editable forecast: override any projected line item with manual estimate

### Feature 5: Department Budget Allocation
- Assign budget envelope to each cost center
- Real-time progress bars showing budget consumed vs allocated
- Cross-department reallocation request form with approval workflow
- Pie chart: department budget share vs actual spend side-by-side
- Per-department budget health score (0–100)

### Feature 6: Reports and Export
- Monthly budget execution report (print-friendly layout)
- Quarterly finance review with trend commentary placeholders
- Export full budget grid to Excel (.xlsx) with formatting intact for accountant
- Cash flow statement in Malaysian SME-friendly format
- Email-ready PDF summary (one-page executive snapshot)

## Dashboard Layout

### Top Summary Cards (4 cards in a row)
- Annual Budget Total (RM formatted, e.g. RM 2,400,000)
- YTD Actual Spend (with percentage of annual budget consumed)
- Overall Variance (amount + percentage, green/red color coded)
- Current Cash Balance (with trend arrow vs last month)

### Main Chart Area
- Monthly revenue vs expense bar chart (budget bars faded, actual bars solid)
- Cash flow waterfall chart (inflow = green, outflow = red, net balance = navy line)
- 3-month rolling cash flow forecast line chart (solid = actual, dashed = forecast)
- Department budget utilization pie chart

### Alert Panel (right sidebar or bottom section)
- Over-budget line items list sorted by variance percentage descending
- Upcoming large payments in next 30 days
- Cash balance below safe minimum warning banner (yellow background)
- Budget approval pending notifications

## UI and UX Specifications
- Color scheme: dark navy nav (#0F172A), white content area, yellow (#FCD34D) for primary CTAs
- Budget grid: zebra-stripe rows, hover highlight, variance column background red/green
- Currency display: always format as "RM 1,234,567.89" — no shorthand in data tables
- Responsive: dashboard cards stack on mobile, grid scrolls horizontally on small screens
- Spreadsheet feel: Tab key navigation in budget entry grid, keyboard shortcuts for common actions
- Loading skeletons for all data-fetching states
- Optimistic UI updates when editing budget cells (no full page reload)

## Business Rules
- Variance exceeding 10% triggers automatic red flag and in-app notification
- Once a budget period is approved, all entries are read-only — changes require unlocking with audit log entry
- Rolling forecast regenerates automatically on the 1st of each month using latest actuals
- All amounts stored and displayed to 2 decimal places
- Historical entries are immutable — corrections are made via adjustment entries, not edits
- Department total must not exceed company-level budget unless explicitly approved
- RM currency symbol always precedes the number with a single space

## Deliverables
Generate the complete project including:
1. Supabase migration SQL file with all tables, indexes, RLS policies, and seed data
2. All Next.js page components under app/ directory
3. Reusable components: BudgetGrid, VarianceBadge, CashFlowWaterfall, ForecastChart, DepartmentGauge
4. API route handlers under app/api/v1/ for external integrations
5. Server Actions for all mutations (create budget period, save entry, approve budget)
6. Type definitions in types/ directory matching the database schema
7. Environment variable setup instructions for Supabase connection
8. Step-by-step deployment guide for Vercel
9. Sample seed data: 12 months of budget entries for {{company_name}} with realistic Malaysian SME figures