# ISP360 Database Design

**Version:** 1.0  
**Date:** 2026-06-13  
**Prepared by:** Polakis Technologies  
**Deliverable:** Companion to [`sql/database_schema.sql`](../sql/database_schema.sql)

---

## Overview

ISP360 uses a **shared-database, shared-schema multi-tenant model**. Every tenant-scoped table carries a `tenant_id` foreign key to `tenants`. The schema is designed for:

- 1,000,000+ customers
- 10,000+ MikroTik routers
- High-volume RADIUS accounting (`radacct`)
- Multi-country mobile money reconciliation
- Commercial SaaS tenant isolation

### Schema Statistics

| Object Type | Count |
|-------------|-------|
| Tables | 210 |
| Views | 6 |
| Functions | 4 |
| Stored Procedures | 5 |
| Triggers | 5 |
| Seed rows | Countries, currencies, gateways, modules, permissions, roles |

---

## ER Overview

```mermaid
erDiagram
    tenants ||--o{ tenant_domains : has
    tenants ||--o{ tenant_settings : has
    tenants ||--o{ users : employs
    tenants ||--o{ customers : serves
    tenants ||--o{ routers : manages
    tenants ||--o{ packages : offers

    customers ||--o{ customer_subscriptions : subscribes
    customers ||--o{ invoices : billed
    customers ||--o{ payments : pays
    customers ||--o{ pppoe_accounts : connects

    packages ||--o{ package_limits : defines
    packages ||--o{ customer_subscriptions : assigned

    invoices ||--o{ invoice_items : contains
    payments ||--o{ payment_allocations : allocates
    payment_allocations }o--|| invoices : settles

    routers ||--o| router_status : monitors
    routers ||--o{ router_commands : executes
    routers ||--o| nas : radius_nas

    pppoe_accounts ||--o{ pppoe_sessions : sessions
    hotspots ||--o{ hotspot_sessions : sessions

    radius_profiles ||--o{ radius_profile_attributes : attrs
    radius_profiles ||--o{ radcheck : syncs

    voucher_batches ||--o{ vouchers : contains
    vouchers ||--o{ voucher_usage : redeems

    tickets ||--o{ ticket_messages : contains
    customers ||--o{ tickets : raises
```

---

## Tenant Isolation Rules

### Rule 1 — Mandatory `tenant_id`

Every business table (except global lookups) includes:

```sql
tenant_id BIGINT UNSIGNED NOT NULL,
CONSTRAINT fk_*_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (tenant_id)
```

**Global tables (no tenant_id):** `countries`, `currencies`, `timezones`, `payment_gateways`, `system_modules`, `platform_settings`, `permissions`

**Platform-scoped tables (nullable tenant_id):** `roles` (NULL = platform role), `users` (NULL = super admin), `api_tokens`, `dashboard_widgets`

### Rule 2 — Composite Unique Constraints

All natural keys are unique **per tenant**, never globally:

```sql
UNIQUE KEY uk_customers_phone (tenant_id, phone)
UNIQUE KEY uk_packages_code (tenant_id, package_code)
UNIQUE KEY uk_routers_code (tenant_id, router_code)
```

### Rule 3 — Application Query Enforcement

All PHP queries via `includes/db.php` must:

1. Resolve `tenant_id` from session, domain, or API token
2. Append `AND tenant_id = :tenant_id` to every SELECT/UPDATE/DELETE
3. Include `tenant_id` in every INSERT
4. Reject cross-tenant FK references at application layer

### Rule 4 — Super Admin Exception

Users with `roles.role_code = 'super_admin'` and `tenant_id IS NULL` may query across tenants using explicit `tenant_id` parameter — never unscoped queries.

### Rule 5 — FreeRADIUS Isolation

- `nas.tenant_id` + `nas.router_id` link routers to RADIUS
- Usernames provisioned as `{tenant_code}.{username}` or stored with `tenant_id` in shadow tables (`radius_profiles`)
- `radacct` rows correlated to tenant via `nasipaddress` → `nas` → `routers.tenant_id`

### Rule 6 — Soft Deletes

Tables with `deleted_at` column use soft delete. Queries default to `WHERE deleted_at IS NULL`.

---

## Module Index

### Global / Platform (11 tables)

| Table | Purpose |
|-------|---------|
| `countries` | African market country reference |
| `currencies` | KES, UGX, TZS, SSP, etc. |
| `timezones` | Tenant timezone reference |
| `payment_gateways` | M-Pesa, MoMo, Airtel, Tigo |
| `system_modules` | Feature module registry |
| `platform_settings` | SaaS platform config |
| `permissions` | Global permission catalog |
| `roles` | Platform + tenant roles |
| `role_permissions` | Role-permission mapping |
| `api_rate_limits` | API throttling rules |
| `audit_log_archive` | Cold audit storage |

### Multi-Tenant (11 tables)

`tenants`, `tenant_domains`, `tenant_settings`, `tenant_themes`, `tenant_modules`, `tenant_billing`, `tenant_subscriptions`, `tenant_contacts`, `tenant_api_keys`, `tenant_webhooks`, `tenant_usage_snapshots`

### Security (9 tables)

`users`, `user_roles`, `login_history`, `audit_logs`, `sessions`, `api_tokens`, `password_reset_tokens`, `ip_whitelist`, `security_events`, `user_preferences`

### Customers (14 tables)

`customers`, `customer_groups`, `customer_contacts`, `customer_addresses`, `customer_documents`, `customer_devices`, `customer_notes`, `customer_tags`, `customer_tag_map`, `customer_status_history`, `customer_subscriptions`, `customer_kyc`, `customer_notification_prefs`, `customer_import_batches`, `customer_import_rows`

### Packages (7 tables)

`package_groups`, `packages`, `package_pricing`, `package_limits`, `package_fup_rules`, `package_features`, `package_promotions`

### Billing (14 tables)

`tax_rates`, `invoice_sequences`, `invoices`, `invoice_items`, `payments`, `payment_allocations`, `credit_notes`, `debit_notes`, `billing_cycles`, `billing_run_log`, `dunning_rules`, `dunning_log`, `payment_plans`, `payment_plan_installments`, `invoice_status_history`, `payment_status_history`

### Router Management (19 tables)

`routers`, `router_groups`, `router_group_members`, `router_backups`, `router_commands`, `router_status`, `router_logs`, `router_metrics`, `router_interfaces`, `router_queues`, `router_firewall_rules`, `router_scripts`, `router_schedules`, `router_alerts`, `router_credentials`, `router_discovery`, `router_sync_status`, `router_health_checks`, `router_config_snapshots`, `queue_tree_templates`, `bandwidth_profiles`, `service_locations`

### VPN (10 tables)

`vpn_servers`, `vpn_peers`, `vpn_keys`, `vpn_tunnels`, `vpn_logs`, `vpn_routes`, `vpn_acl`, `vpn_server_interfaces`, `vpn_peer_status`, `vpn_key_rotation_history`

### FreeRADIUS (11 tables)

`nas`, `radcheck`, `radreply`, `radgroupcheck`, `radgroupreply`, `radacct`, `radpostauth`, `radius_profiles`, `radius_profile_attributes`, `radius_group_members`, `radius_sync_log`, `radius_accounting_summary`

### Hotspot (14 tables)

`hotspots`, `hotspot_profiles`, `hotspot_users`, `hotspot_sessions`, `hotspot_usage`, `hotspot_login_methods`, `hotspot_portals`, `hotspot_portal_templates`, `hotspot_bandwidth_limits`, `hotspot_mac_auth`, `hotspot_coas_log`, `hotspot_walled_garden`, `hotspot_advertisements`, `hotspot_profile_packages`

### PPPoE (9 tables)

`pppoe_accounts`, `pppoe_profiles`, `pppoe_sessions`, `pppoe_ip_pools`, `pppoe_pool_assignments`, `pppoe_static_ips`, `pppoe_disconnect_log`, `pppoe_profile_packages`, `pppoe_account_history`, `fibre_connections`, `mac_auth_devices`, `ip_address_reservations`

### Vouchers (9 tables)

`voucher_batches`, `vouchers`, `voucher_usage`, `voucher_prints`, `voucher_templates`, `voucher_pin_formats`, `voucher_resellers`, `voucher_batch_status_log`, `voucher_serial_ranges`

### Mobile Money (12 tables)

`mpesa_transactions`, `momo_transactions`, `airtel_transactions`, `tigo_transactions`, `payment_callbacks`, `payment_gateway_configs`, `payment_reconciliation`, `payment_reconciliation_items`, `payment_refunds`, `payment_disbursements`, `gateway_webhook_log`, `payment_idempotency_keys`

### Notifications (11 tables)

`sms_logs`, `email_logs`, `notification_templates`, `notification_queue`, `notification_channels`, `notification_rules`, `notification_subscriptions`, `push_logs`, `whatsapp_logs`, `notification_bounces`, `notification_provider_configs`

### Ticketing (12 tables)

`tickets`, `ticket_messages`, `ticket_attachments`, `ticket_categories`, `ticket_priorities`, `ticket_statuses`, `ticket_assignments`, `ticket_sla_rules`, `ticket_sla_log`, `ticket_escalations`, `ticket_tags`, `ticket_tag_map`

### Reporting (12 tables)

`report_cache`, `dashboard_metrics`, `report_definitions`, `report_schedules`, `report_exports`, `analytics_events`, `usage_aggregates_daily`, `usage_aggregates_monthly`, `revenue_aggregates_daily`, `revenue_aggregates_monthly`, `network_metrics_hourly`, `customer_metrics_daily`, `dashboard_widgets`, `dashboard_widget_layouts`

### Captive Portal (8 tables)

`captive_portal_templates`, `captive_portal_themes`, `captive_portal_pages`, `captive_portal_assets`, `captive_portal_sessions`, `captive_portal_login_log`, `captive_portal_surveys`, `captive_portal_survey_responses`

### Customer Portal (2 tables)

`portal_sessions`, `portal_activity_log`

### Subscription / Misc (3 tables)

`subscription_renewal_log`, `webhook_delivery_log`

---

## Standard Audit Columns

All tenant business tables include:

| Column | Type | Purpose |
|--------|------|---------|
| `created_at` | DATETIME | Record creation timestamp |
| `updated_at` | DATETIME | Last modification (auto ON UPDATE) |
| `created_by` | BIGINT UNSIGNED NULL | FK to `users.user_id` |
| `updated_by` | BIGINT UNSIGNED NULL | FK to `users.user_id` |
| `deleted_at` | DATETIME NULL | Soft delete marker |

High-volume telemetry tables (`router_metrics`, `radacct`, `login_history`) omit `deleted_at` and some audit fields for write performance.

---

## Views

| View | Purpose |
|------|---------|
| `vw_tenant_dashboard_metrics` | Real-time tenant KPIs |
| `vw_active_pppoe_sessions` | Active PPPoE session listing |
| `vw_revenue_by_period` | Revenue aggregation by day/month |
| `vw_router_health` | Router health with open alerts |
| `vw_customer_balances` | Customer balance + outstanding invoices |
| `vw_voucher_inventory` | Voucher batch inventory counts |

---

## Functions

| Function | Returns | Purpose |
|----------|---------|---------|
| `fn_tenant_balance(tenant_id, customer_id)` | DECIMAL | Customer account balance |
| `fn_active_sessions(tenant_id, service_type)` | INT | Active PPPoE/hotspot session count |
| `fn_package_remaining_quota(tenant_id, customer_id, package_id)` | BIGINT | Remaining data quota in MB |
| `fn_generate_invoice_number(tenant_id)` | VARCHAR | Thread-safe invoice number |

---

## Stored Procedures

| Procedure | Purpose |
|-----------|---------|
| `sp_provision_tenant` | Create tenant + admin user + defaults |
| `sp_generate_invoice` | Create draft invoice with auto-number |
| `sp_allocate_payment` | Allocate payment to invoice with validation |
| `sp_redeem_voucher` | Voucher redemption with status checks |
| `sp_router_health_rollup` | Hourly router metrics aggregation |

---

## Triggers

| Trigger | Table | Purpose |
|---------|-------|---------|
| `trg_invoices_audit_insert` | `invoices` | Audit log on invoice creation |
| `trg_invoices_audit_update` | `invoices` | Audit log on status/balance change |
| `trg_payments_audit_insert` | `payments` | Audit log on payment recording |
| `trg_vouchers_status_update` | `vouchers` | Auto-set `used_at` on redemption |
| `trg_customers_status_history` | `customers` | Track customer status changes |

---

## Indexing Strategy

1. **Primary access pattern:** `(tenant_id, ...)` composite indexes on all list/query endpoints
2. **Status filters:** `(tenant_id, status)` on customers, invoices, routers, tickets
3. **Time-series:** `(tenant_id, created_at)` on logs, `radacct`, payments
4. **RADIUS accounting:** `(username, acctstarttime)` on `radacct` per FreeRADIUS recommendations
5. **Unique business keys:** Always prefixed with `tenant_id`

---

## Installation

```bash
mysql -u root -p < sql/database_schema.sql
```

The script:
1. Creates database `isp360`
2. Creates all tables, views, routines, triggers
3. Seeds countries, currencies, payment gateways, modules, permissions, default roles

---

## Partitioning Recommendations (Production)

For tables exceeding 50M rows at scale, apply range partitioning:

| Table | Partition Key |
|-------|---------------|
| `radacct` | `acctstarttime` (monthly) |
| `router_metrics` | `recorded_at` (weekly) |
| `audit_logs` | `created_at` (monthly) |
| `login_history` | `created_at` (monthly) |
| `sms_logs` / `email_logs` | `created_at` (monthly) |

Partitioning is not included in v1.0 schema — apply during deployment scaling per [`DEPLOYMENT_SPEC.md`](DEPLOYMENT_SPEC.md) (Phase 12).

---

## Change Log

| Version | Date | Change |
|---------|------|--------|
| 1.0 | 2026-06-13 | Initial schema — 210 tables, 6 views, 4 functions, 5 procedures, 5 triggers |
