# Vertex WiFi — Database Design (ERD) > Deliverable 1 of 13. Normalised schema for the ISP Customer Management System. --- ## 1. Design decisions Three decisions shape this schema. Each is a deliberate deviation from a naive "one big customer table" and each one is what makes the billing, reporting and SMS features actually possible. ### 1.1 The customer record is split into three tables The spec groups customer fields into **Client Details**, **WiFi Details** and **Subscription Details**. Those are three different lifecycles, so they are three tables: | Table | Holds | Lifecycle | | --- | --- | --- | | `customers` | Who the subscriber is (name, address, contact) | Changes rarely | | `customer_connections` | The physical line (router, ONU, PPPoE, NAP, port) | Changes on hardware swap / re-splice | | `subscriptions` | The commercial agreement (plan, price, dates, cycle) | Changes on upgrade / downgrade | `customer_connections` is 1:1 with `customers` today but is modelled as a separate table so a subscriber can later hold a second line without a migration. `subscriptions` is 1:N — an upgrade from 50 Mbps to 100 Mbps closes the old row and opens a new one, which preserves **price history**. Without this, last quarter's revenue report silently rewrites itself every time someone changes plan. ### 1.2 `invoices` exists as its own table (added beyond the requested list) The requested table list has `payments` but no `invoices`. The dashboard and reports cannot be built that way: - **"Bills Due Today"**, **"Bills Due in 3 Days"**, **"Unpaid Bills"** and **"Expected Revenue"** all describe a bill that has *no payment yet*. A payments-only schema has no row to select. - The requested payment statuses include **Overdue** — a property of a *bill* that has passed its due date, not of a money transfer. - The SMS reminder needs an `{Amount}` and a `{Due Date}` three days *before* anyone pays. So a charge and the money that settles it are separate: ``` subscriptions ──generates──▶ invoices ──settled by──▶ payments ``` An invoice carries `amount_due` / `amount_paid` / `balance` and a status of `unpaid → partially_paid → paid` (or `overdue` / `void`). `payments` keeps the `invoice_number`, `amount`, `payment_date`, `payment_method`, `reference_number`, `remarks` and `status` fields exactly as specified, plus an `invoice_id` foreign key. Nothing was removed; one table was added. ### 1.3 `customers.status` is authoritative and denormalised on purpose Service status (`pending → installed → active → suspended → disconnected`) is listed under Subscription Details, but it is queried on nearly every screen: dashboard counters, the customer list filter, and the SMS job's eligibility check. Resolving it through a join to the current subscription on every one of those queries is needless cost. Status therefore lives on `customers` with an index, and the **service layer is the only writer** (`CustomerStatusService`). `subscriptions.status` separately tracks whether that particular commercial agreement is `active` or `ended`. These answer different questions and are not duplicates. --- ## 2. Entity relationship diagram ```mermaid erDiagram users ||--o{ customers : "created_by" users ||--o{ payments : "recorded_by" users ||--o{ customer_notes : "author" users ||--o{ activity_logs : "actor" users }o--o{ roles : "model_has_roles" roles }o--o{ permissions : "role_has_permissions" plans ||--o{ subscriptions : "priced by" customers ||--|| customer_connections : "has line" customers ||--o{ subscriptions : "has history" customers ||--o{ invoices : "is billed" customers ||--o{ payments : "pays" customers ||--o{ customer_notes : "annotated by" customers ||--o{ sms_logs : "messaged" subscriptions ||--o{ invoices : "generates" invoices ||--o{ payments : "settled by" invoices ||--o{ sms_logs : "reminded about" sms_templates ||--o{ sms_logs : "rendered from" users { bigint id PK string name string email UK string password string phone boolean is_active datetime last_login_at string last_login_ip datetime deleted_at } plans { bigint id PK string name UK string slug UK int speed_mbps decimal monthly_price decimal installation_fee text description enum status "active|inactive" int sort_order } customers { bigint id PK string account_number UK "VTX-2026-00001" string full_name IDX text address string barangay IDX string city IDX string province decimal gps_latitude decimal gps_longitude string contact_number IDX string alternate_contact_number string email IDX string facebook_profile text notes enum status IDX "pending|installed|active|suspended|disconnected" datetime status_changed_at bigint created_by FK bigint updated_by FK datetime deleted_at } customer_connections { bigint id PK bigint customer_id FK_UK string router_brand string router_model string router_serial_number IDX string mac_address IDX string onu_serial_number IDX string pppoe_username UK text pppoe_password "encrypted" string ip_address string nap_location IDX string port_number string installation_team } subscriptions { bigint id PK bigint customer_id FK bigint plan_id FK decimal monthly_price "snapshot" decimal installation_fee decimal discount enum billing_cycle "monthly|quarterly|semi_annual|annual" tinyint billing_day "1-31" date installation_date date activation_date date due_date "next due" date started_at date ended_at enum status "active|ended" boolean is_current IDX } invoices { bigint id PK string invoice_number UK bigint customer_id FK bigint subscription_id FK date period_start date period_end date issued_date date due_date IDX decimal amount_due decimal discount decimal installation_fee decimal total_amount decimal amount_paid decimal balance enum status IDX "unpaid|partially_paid|paid|overdue|void" datetime paid_at boolean reminder_sent } payments { bigint id PK string receipt_number UK bigint invoice_id FK bigint customer_id FK string invoice_number decimal amount date payment_date IDX enum payment_method "cash|gcash|maya|bank_transfer|paymongo|xendit|other" string reference_number text remarks enum status IDX "paid|pending|failed|overdue" bigint recorded_by FK } sms_templates { bigint id PK string key UK "due_reminder|overdue|suspended" string name text body "supports {placeholders}" boolean is_active } sms_logs { bigint id PK bigint customer_id FK bigint invoice_id FK bigint sms_template_id FK string recipient IDX text message string driver enum type "reminder|overdue|manual|test|welcome" enum status IDX "queued|sent|delivered|failed" text gateway_response string gateway_message_id int attempts datetime sent_at datetime delivered_at text error_message } customer_notes { bigint id PK bigint customer_id FK bigint user_id FK text body enum category "general|technical|billing|complaint" boolean is_pinned } settings { bigint id PK string key UK text value string group IDX "sms|billing|branding|general" enum type "string|integer|boolean|json|encrypted" boolean is_public } activity_logs { bigint id PK bigint user_id FK string event IDX "created|updated|deleted|login" string subject_type IDX bigint subject_id IDX string description json old_values json new_values string ip_address text user_agent } ``` --- ## 3. Table reference ### 3.1 `users` — staff accounts only Subscribers never get a row here. There is no registration endpoint; a Super Admin creates every account. `is_active = false` blocks login without destroying the audit trail, which is why we prefer it to deletion. | Column | Type | Notes | | --- | --- | --- | | `id` | bigint PK | | | `name` | varchar(120) | | | `email` | varchar(180) UNIQUE | Login identifier | | `password` | varchar(255) | bcrypt, 12 rounds | | `phone` | varchar(20) nullable | | | `avatar_path` | varchar(255) nullable | | | `is_active` | boolean default true | Gate checked at login | | `last_login_at` | timestamp nullable | | | `last_login_ip` | varchar(45) nullable | IPv6-safe width | | `deleted_at` | timestamp nullable | Soft delete | Roles come from `spatie/laravel-permission` (`roles`, `permissions`, `model_has_roles`, `model_has_permissions`, `role_has_permissions`). See [04-RBAC.md](04-RBAC.md) for the role → permission matrix. ### 3.2 `plans` `slug` is derived from `name` and used in URLs and report groupings. `sort_order` controls display order so 20 Mbps lists before 100 Mbps regardless of insertion order. Plans are **soft-deleted, never hard-deleted** — an archived plan must stay resolvable for historical invoices. ### 3.3 `customers` `account_number` is generated as `VTX-{YYYY}-{00001}` inside a database transaction (see `CustomerService::generateAccountNumber`). `full_name` is a single indexed column, matching the specified "Full Name" field. Search covers `full_name`, `contact_number`, `address`, `account_number` and — through joins — router serial and plan. `gps_latitude` / `gps_longitude` use `decimal(10,7)` / `decimal(10,7)`, which resolves to ~1 cm and is enough for the future Fiber Map feature. ### 3.4 `customer_connections` `pppoe_password` is stored with Laravel's `encrypted` cast — it is a credential the technician must be able to read back, so it cannot be hashed. It is stripped from API responses unless the caller holds `customers.view_credentials`. `router_serial_number`, `mac_address` and `onu_serial_number` are indexed because they are how a technician looks up "whose box is this?" in the field. ### 3.5 `subscriptions` `monthly_price`, `installation_fee` and `discount` are **snapshots** taken from the plan at signup. Raising a plan's price must never retroactively change what existing subscribers are billed, and `invoices` are computed from the subscription, not the plan. `billing_day` (1–31) is the anchor. `due_date` caches the next due date so the dashboard's "due today / due in 3 days" queries and the SMS job stay index-only. Day 29–31 clamps to the last day of short months. Exactly one row per customer may have `is_current = true`. `SubscriptionService` is the single writer and demotes the previous current row inside the same transaction that promotes the new one; `(customer_id, is_current)` is indexed so resolving the current plan stays a single index seek. ### 3.6 `invoices` `total_amount = amount_due - discount + installation_fee`, and `balance = total_amount - amount_paid`, both maintained by `InvoiceService` on every payment. `reminder_sent` is the idempotency guard that stops the nightly scheduler from sending the same reminder twice. ### 3.7 `payments` `receipt_number` (`RCP-{YYYY}-{00001}`) is what prints on the receipt. `invoice_number` is denormalised onto the row so a receipt reprint is self-contained even if the invoice is later voided. ### 3.8 `sms_logs` Stores recipient, timestamp, full rendered message, driver used, gateway response and status — the complete audit trail the spec requires. `attempts` supports the retry/backoff policy. ### 3.9 `settings` Key–value store, grouped. The SMS gateway URL and API key live here (type `encrypted`) so the admin can change gateways from the Settings page without a redeploy. `is_public` marks values safe to expose to the frontend. ### 3.10 `activity_logs` Written by an observer trait on every create / update / delete of a customer, plan, payment, subscription and user, plus login and logout events. `old_values` / `new_values` are JSON diffs, not full snapshots. --- ## 4. Index strategy | Table | Index | Serves | | --- | --- | --- | | `customers` | `status` | Dashboard counters, list filter | | `customers` | `barangay` | Barangay filter, coverage report | | `customers` | `full_name`, `contact_number` | Search | | `customers` | `(status, created_at)` | "New installs this month" | | `customer_connections` | `router_serial_number`, `mac_address`, `onu_serial_number` | Field lookup | | `subscriptions` | `(customer_id, is_current)` | Resolve current plan | | `subscriptions` | `(status, due_date)` | Due-date tracking | | `invoices` | `(status, due_date)` | Due today / due in 3 days / unpaid | | `invoices` | `(customer_id, issued_date)` | Billing history | | `payments` | `(payment_date, status)` | Monthly revenue, collection report | | `sms_logs` | `(status, created_at)` | SMS log screen | | `activity_logs` | `(subject_type, subject_id)` | Per-record audit trail | All monetary columns are `decimal(12,2)` — never float. All FKs are `ON DELETE RESTRICT` except `customer_connections`, `subscriptions`, `customer_notes` and `invoices`, which cascade from `customers` so a purge is clean. --- ## 5. Future features already accommodated | Planned feature | Existing accommodation | | --- | --- | | GCash / Maya / PayMongo / Xendit | `payments.payment_method` enum already carries them; `reference_number` holds the gateway txn id | | Customer Portal / Mobile App | `customers` has no auth columns by design; a future `customer_users` table attaches credentials without touching this schema | | Ticketing System | `customer_notes.category` is the seed of a ticket type; tickets become their own table referencing `customer_id` | | Fiber Map / NAP Management | `gps_latitude`/`gps_longitude` on customers, `nap_location`/`port_number` on connections — a `naps` table can later be extracted and FK'd | | Router / ONU Inventory | Serial numbers already isolated in `customer_connections`; an `assets` table FKs to it | | Outage Notification | `sms_logs.type` enum extends; recipient sets come from `customer_connections.nap_location` | | Network Monitoring | `customer_connections.ip_address` is the polling target | --- Next: [02-BACKEND-STRUCTURE.md](02-BACKEND-STRUCTURE.md)