# Vertex WiFi — Expense & Expansion Management > The module that answers: **which barangay is making us money, how much did we > invest there, what are we spending, and where should we expand next?** --- ## 0. Four decisions that shape everything else These are the calls that determine whether the numbers on the dashboard can be trusted. Three of them were not in the brief, and getting them wrong produces confident, precise, wrong answers — the worst kind for a decision this expensive. ### 0.1 Capital vs operating spend is a first-class distinction ROI is `net profit ÷ investment`. Break-even is `investment ÷ monthly contribution`. Both need to know what **investment** is — and a month's electricity bill is not investment. If every expense counts as investment, ROI falls every month you keep the lights on, and break-even never arrives. So `expense_categories.is_capital` is a required flag: | | Capital (investment) | Operating (running cost) | | --- | --- | --- | | Examples | Fiber cable, NAP, splitter, OLT, pole, installation labour | Electricity, upstream, rent, fuel, salaries | | Counts toward | Investment, ROI denominator, break-even numerator | Monthly cost, profit, contribution margin | | Recurs? | Rarely — a one-off build | Usually monthly | A barangay's **investment** is the sum of its capital expenses. Its **monthly cost** is the sum of its operating expenses. They are never added together into one "expenses" number without saying which. ### 0.2 Direct vs fully-loaded profit, always both, never silently one Minglanilla's ₱50,000 upstream link serves every barangay in Minglanilla. How much of it belongs to Tubod? Any single answer is a lie of omission: - **Ignore it** → Tubod looks wildly profitable, and you expand into a barangay whose true cost you have never seen. - **Allocate it** → Tubod's profit now moves when an unrelated barangay gains customers, which is baffling when you are comparing areas month to month. So the service returns **both**, labelled: ``` direct_profit = revenue − expenses tagged to THIS barangay allocated_overhead = share of municipality-wide + global spend loaded_profit = direct_profit − allocated_overhead ``` Allocation basis is **active customer count** by default (a barangay with 120 of a municipality's 200 subscribers carries 60% of its shared cost), switchable to revenue share. The basis used is returned in the payload, so a number can never be read without knowing how it was produced. Rule of thumb the UI states plainly: compare barangays on **direct**, judge whether the business works on **loaded**. ### 0.3 Financial records snapshot their location `payments.barangay_id` is written when the payment is recorded, rather than joined live through the customer. This looks like the denormalisation the brief warns against, and it is the opposite. A subscriber who moves from Tubod to Poblacion must not retroactively move two years of revenue with them — that silently rewrites the history you based an expansion decision on. It is the same reasoning as `subscriptions.price` already snapshotting the plan price: **records of what happened must not change when master data changes.** What is *not* stored is anything derived: no stored profit, no stored ROI, no stored margin. Those are computed from payments, expenses and customers on every read, per requirement 22. ### 0.4 Three tables, not a generic `locations` table The brief suggests both. A self-referencing `locations(parent_id)` table is the right shape for an arbitrary-depth tree; this hierarchy is exactly three levels and mirrors the PSGC. Concretely: - `customers.barangay_id` is a real foreign key to a real table, not "a location row that we hope is a barangay". - "All customers in Minglanilla" is one join, not a recursive CTE. - The levels genuinely differ: a barangay has population and household estimates; a province does not. Adding Tubod, or all of Naga City, is `INSERT` — no schema change, satisfying the brief's requirement to grow without migrations. --- ## 1. ERD ``` provinces id, name, psgc_code?, created_at, updated_at │ 1 │ ▼ n municipalities id, province_id, name, type(municipality|city), status, psgc_code?, latitude?, longitude?, notes │ 1 │ ▼ n barangays id, municipality_id, name, status, latitude?, longitude?, population_estimate?, household_estimate?, notes │ 1 ├──────────────────────────────┬─────────────────────────┐ ▼ n ▼ n ▼ n customers expenses expansion_projects barangay_id (FK, nullable) barangay_id? barangay_id? address, gps_* municipality_id? municipality_id (legacy varchar kept) scope enum status, estimates… │ category_id │ 1 │ 1 amount, expense_date │ ▼ n vendor, reference ▼ n payments payment_method expansion_budget_items barangay_id ◄── snapshot receipt_path category_id amount, payment_date recurring_expense_id? budgeted_amount ▲ label │ 1 recurring_expenses template fields + frequency, next_due_on, last_generated_on, is_active expense_categories id, name, slug, is_capital, is_active, sort_order, colour? ▲ n ▲ n └──── expenses ────────┘ expansion_budget_items ``` **Deliberately absent: `area_metrics`.** It appears in the brief's table list, but a stored metrics table is a cache, and a cache that can disagree with the ledger is how a dashboard starts lying. At this data volume (hundreds of subscribers, thousands of payments) live aggregation is milliseconds. §7 records the exact trigger for adding one later. ### Relationships in words | From | To | Cardinality | On delete | Why | | --- | --- | --- | --- | --- | | `province` | `municipalities` | 1 → n | restrict | Deleting a province with municipalities is a mistake, not an intention | | `municipality` | `barangays` | 1 → n | restrict | Same | | `barangay` | `customers` | 1 → n | **null** | A barangay must never be deletable in a way that deletes subscribers | | `barangay` | `expenses` | 1 → n | null | Expense survives as global spend rather than vanishing | | `municipality` | `expenses` | 1 → n | null | Municipality-scoped spend | | `expense_category` | `expenses` | 1 → n | restrict | A category with spend against it is archived, never deleted | | `recurring_expense` | `expenses` | 1 → n | null | Generated children outlive their template | | `expansion_project` | `budget_items` | 1 → n | cascade | Items are parts of the project, not independent records | | `customer` | `payments` | 1 → n | *(existing)* | Unchanged | `customers.barangay_id` is **nullable** on purpose: the existing table has free-text `barangay`/`city`/`province`, and a migration that demanded a clean mapping would fail on the first typo. The backfill matches what it can, leaves the rest null, and the UI surfaces "N unmapped subscribers" until somebody resolves them. The legacy varchar columns stay as written-once history. --- ## 2. Financial calculation logic All money is `decimal(12,2)` and all arithmetic uses **bcmath on strings**, matching the existing billing code. No floats touch a peso. Given a barangay `b` and a period `[from, to]`: ``` customers_total = customers WHERE barangay_id = b customers_active = … AND status IN (active, suspended) ← billable new_customers = … AND created_at BETWEEN from AND to revenue = Σ payments.amount WHERE barangay_id = b AND status = completed AND payment_date BETWEEN from AND to direct_capex = Σ expenses.amount WHERE barangay_id = b AND category.is_capital direct_opex = Σ expenses.amount WHERE barangay_id = b AND NOT is_capital (both within period, except investment which is all-time) allocation_share = customers_active(b) ÷ customers_active(municipality) (or revenue share, per settings) shared_opex = Σ municipality-scope opex × allocation_share + Σ global-scope opex × global_share where global_share = customers_active(b) ÷ customers_active(all) direct_profit = revenue − direct_opex loaded_profit = revenue − direct_opex − shared_opex margin = loaded_profit ÷ revenue × 100 (null when revenue = 0) arpu = revenue ÷ customers_active (null when 0 customers) investment = Σ ALL-TIME capex for b + allocated share of municipality capex roi = loaded_profit(period, annualised) ÷ investment × 100 cac = Σ (marketing + capital) expenses in period ÷ new_customers (null when new_customers = 0 — not zero, null) contribution/cust = arpu − (direct_opex + shared_opex) ÷ customers_active break_even_customers = ⌈ investment ÷ contribution_per_customer ⌉ remaining = break_even_customers − customers_active break_even_date = projected from the trailing 3-month net customer growth rate ``` **Division by zero returns `null`, never `0`.** A barangay with no customers has an *unknown* ARPU, not an ARPU of zero, and a chart that plots the difference as zero invites exactly the wrong conclusion. Every ratio in the payload is nullable and the UI renders `—`. **Annualisation for ROI** is stated in the response (`roi_basis: "annualised from 30-day profit"`). Comparing a one-month profit against an all-time investment without saying so produces an ROI that looks catastrophic and is meaningless. ### Expansion opportunity score (0–100) A weighted, **explainable** score — the payload returns every component with its contribution, because "87/100" that cannot be interrogated will not survive its first disagreement with a human. | Component | Weight | Source | | --- | --- | --- | | Addressable market | 25 | estimated_addressable ÷ best-in-set | | Projected ROI | 25 | est. annual profit ÷ est. investment | | Break-even speed | 20 | months to break even, inverted | | Network proximity | 15 | distance to nearest live barangay (haversine on lat/lng) | | Competition | 15 | operator-entered 1–5, inverted | Bands: **≥75 High potential · 50–74 Worth planning · 25–49 Marginal · <25 Not now.** Components with no data are excluded and the remaining weights renormalised, with `confidence` reporting how much of the weight was actually available. --- ## 3. API surface All under `/api/v1`, all inside the existing `auth:sanctum` + `active` + `abilities:*` group, all permission-gated. | Method | Route | Permission | | --- | --- | --- | | GET | `locations/tree` | `locations.view` | | GET/POST | `provinces`, `municipalities`, `barangays` | `locations.view` / `.create` | | PATCH/DELETE | `…/{id}` | `locations.update` / `.delete` | | GET | `barangays/{id}/performance` | `areas.view` | | GET | `expense-categories` | `expenses.view` | | POST/PATCH/DELETE | `expense-categories…` | `expenses.categories_manage` | | GET/POST | `expenses` | `expenses.view` / `.create` | | GET/PATCH/DELETE | `expenses/{id}` | `expenses.view` / `.update` / `.delete` | | GET | `expenses/{id}/receipt` | `expenses.view` | | GET/POST/PATCH/DELETE | `recurring-expenses…` | `expenses.*` | | GET | `areas/performance` | `areas.view` | | GET | `areas/expansion-decision` | `areas.view` | | GET/POST/PATCH/DELETE | `expansion-projects…` | `expansion.*` | | PUT | `expansion-projects/{id}/budget` | `expansion.update` | | GET | `expansion-projects/{id}/score` | `expansion.view` | | GET | `reports/{report}` + `/export` | existing `reports.*` | New permissions: `locations.{view,create,update,delete}`, `expenses.{view,create,update,delete,categories_manage}`, `expansion.{view,create,update,delete}`, `areas.view`. Role defaults — **Staff can see areas and record expenses but not approve expansion budgets**, because committing capital is an owner decision: | | Super Admin | Admin | Staff | | --- | --- | --- | --- | | locations.view / areas.view | ✅ | ✅ | ✅ | | locations.create/update/delete | ✅ | ✅ | ❌ | | expenses.view/create | ✅ | ✅ | ✅ | | expenses.update/delete | ✅ | ✅ | ❌ | | expenses.categories_manage | ✅ | ✅ | ❌ | | expansion.view | ✅ | ✅ | ✅ | | expansion.create/update/delete | ✅ | ✅ | ❌ | --- ## 4. Laravel architecture Mirrors what the codebase already does — services are the only writers of money columns, repositories wrap query composition, DTOs carry validated input. ``` app/ Enums/ LocationStatus, ExpenseScope, RecurringFrequency, ExpansionStatus, AllocationBasis Models/ Province, Municipality, Barangay, ExpenseCategory, Expense, RecurringExpense, ExpansionProject, ExpansionBudgetItem DTOs/ ExpenseData, RecurringExpenseData, ExpansionProjectData, AreaPeriod Services/ Locations/ LocationService — create/rename/archive, guards Expenses/ ExpenseService — sole writer of expenses.amount RecurringExpenseService — materialises due templates Areas/ AreaFinancialService — §2, the single source of every metric AreaAllocator — shared-cost apportionment ExpansionScoreService — §2 scoring Repositories/ BarangayRepository, ExpenseRepository, ExpansionRepository Jobs/ GenerateRecurringExpenses Console/ vertex:generate-recurring-expenses (daily 00:10) Policies/ LocationPolicy, ExpensePolicy, ExpansionPolicy ``` `AreaFinancialService` is deliberately the only place a peso is divided by anything. Every dashboard, report and export calls it, so there is exactly one definition of "profit" in the system and no chance of the dashboard and the PDF disagreeing. Scheduler: `vertex:generate-recurring-expenses` at **00:10** — after invoices (00:01) and before nothing that depends on it, `withoutOverlapping()->onOneServer()`. ## 5. React architecture ``` src/pages/ locations/ LocationManager.tsx tree + CRUD modals expenses/ ExpenseList.tsx filters, table, receipt preview ExpenseForm.tsx scope-aware location picker CategoryManager.tsx RecurringExpenses.tsx areas/ AreaPerformance.tsx the §15 table + drill-down AreaDetail.tsx one barangay: P&L, break-even, ROI expansion/ ExpansionList.tsx ExpansionProject.tsx budget vs actual, score breakdown ExpansionDecision.tsx the §24 "where next" board src/components/finance/ MoneyCell, MarginBadge, ScoreMeter, BudgetBar, AreaPicker, PeriodPicker src/hooks/ useAreaPerformance, useExpenses, useLocationTree, useExpansionProjects ``` Reuses the existing `DataTable`, `Modal`, `Field`, `StatCard`, TanStack Query conventions and `useFilters`. Charts follow the existing Recharts setup with `isAnimationActive={false}`. --- ## 6. Build order Each phase leaves the system working and verifiable. 1. **Locations** — tables, backfill from the existing varchar columns, CRUD, picker. 2. **Expenses** — categories, expenses, scopes, receipts, recurring + scheduler. 3. **Financial engine** — `AreaFinancialService` + the area performance API. 4. **Expansion** — projects, budgets, score. 5. **Dashboards, reports, exports.** 6. **React surface + navigation + permissions.** ## 7. When to add `area_metrics` Add the cache when live aggregation on the area dashboard exceeds ~300 ms, which at current shapes means roughly **50,000 payments or 5,000 subscribers**. It would be a scheduled rebuild, never written on the request path, and the live calculation stays as the reconciliation check. ## 8. Designed-for, not built-now The schema anticipates the brief's §26 list without paying for it yet: `latitude`/ `longitude` on municipalities and barangays (map view), `psgc_code` (government data import), `expenses.reference_number` + `payment_method` reusing the existing `PaymentMethod` enum (GCash/Maya/PayMongo reconciliation), and nullable `expansion_projects.barangay_id` so a project can target a municipality before its barangays exist.