# Database

MySQL 8.0.16+ (production target) or MariaDB 10.4+ (XAMPP), InnoDB, `utf8mb4_unicode_ci`.
All schema changes are Laravel migrations (`database/migrations`) — no manual phpMyAdmin steps.
Every migration has a working `down()`.

**Conventions**

* `id` BIGINT auto-increment = internal key. `ulid` CHAR(26) = public identifier in URLs and forms.
* Public business numbers: `customers.customer_number` (CUS-000001), `sellers.seller_code` (SLR-000001),
  `work_orders.work_order_number` (WO-2026-000001). Prefixes are configurable in Settings.
* Money is `DECIMAL(10,2)` — never FLOAT. Prices are informational (no payments).
* Timestamps are stored in **UTC** (the DB session is forced to `+00:00`). Operational dates (work order date,
  deadline, installation, completion) are calendar `DATE`s in the business timezone.
* Status / type columns are short strings backed by PHP enums **and** CHECK constraints.
* Referenced master records are **deactivated** (`status`), not deleted. History tables reference their parent
  with `RESTRICT`, so business history can never be cascade-deleted.

## 1. Entity relationships

```mermaid
erDiagram
    organizations ||--o{ organizations : "parent_id (main -> sub)"
    organizations ||--o{ users : employs
    roles ||--o{ users : "role_id"
    roles }o--o{ permissions : permission_role
    organizations ||--o{ vendors : "owns catalog (main reseller)"
    organizations }o--o{ vendors : "organization_vendor (allowed vendors)"
    vendors ||--o{ packages : offers
    packages ||--o{ package_services : contains
    services ||--o{ package_services : "Service Master"
    packages ||--o{ package_price_histories : "price versions"
    organizations ||--o{ sellers : has
    users |o--o| sellers : "optional login (sellers.user_id)"
    organizations ||--o{ customers : has
    sellers ||--o{ customers : "assigned seller"
    customers ||--o{ customer_addresses : "service / mailing"
    customers ||--o{ work_orders : orders
    sellers ||--o{ work_orders : sells
    vendors ||--o{ work_orders : fulfils
    packages ||--o{ work_orders : ordered
    work_orders ||--o{ work_order_services : "services snapshot"
    work_orders ||--o{ work_order_status_histories : timeline
    work_orders ||--o{ work_order_notes : "internal notes"
    work_orders ||--o{ work_order_attachments : files
```

## 2. Tables

### Identity, tenancy & access

| Table | Purpose / key columns |
|---|---|
| `organizations` | Main resellers (`parent_id` NULL) and sub-resellers (`parent_id` → main). `type`, unique `code`, contact & address, `vendor_access` (`all` / `selected`), `status`. CHECK: hierarchy consistency. |
| `organization_vendor` | Vendors a sub-reseller may sell when `vendor_access = selected`. PK (organization_id, vendor_id). |
| `roles` | `name` (slug), `label`, `level` (privilege, 1–100), `scope` (`platform` / `main_reseller` / `sub_reseller` / `any`), `restrict_to_own_records` (seller logins), `is_system`. |
| `permissions` / `permission_role` | Granular permissions synced from `App\Enums\Permission`. |
| `users` | Login identity (unique `email`), `organization_id` (NULL = platform admin), `role_id`, `status`, `must_change_password`, `last_login_at/ip`, encrypted 2FA secret & recovery codes. Users are deactivated, never deleted. |
| `sessions`, `password_reset_tokens` | Laravel standard (database sessions; reset tokens expire after 30 minutes). |

### Catalog

| Table | Purpose / key columns |
|---|---|
| `vendors` | Upstream provider, owned by a main reseller. Unique (`organization_id`, `code`) and (`organization_id`, `name`). `account_reference` is **encrypted** at rest. |
| `services` | Service Master: unique `code` & `name`, `category` (`internet`/`tv`/`voice`/`mobile`/`other` — drives the package detail fields), `icon`, `status`, `display_order`. |
| `packages` | Vendor package: `code` (unique per catalog), `name` (unique per vendor), pricing (`price`, `price_frequency`, `standard_monthly_price`, `promotional_price` + dates, `installation_price`, `activation_price`), **calculated** `service_count` + `play_type`, `effective_from/to`, `status`. |
| `package_services` | Services in a package + `details` JSON (speeds, TV tier, lines, …) validated per category. |
| `package_price_histories` | One row per pricing version (`effective_at`, `changed_by`, `reason`). |

### People

| Table | Purpose / key columns |
|---|---|
| `sellers` | Sales agents per organization: generated `seller_code`, names, contact, address, `start_date`, optional `user_id` (portal login), `status`. Unique (`organization_id`, `email`). |
| `customers` | `customer_number`, `customer_type` (residential/business — company required for business), names, phones (10-digit NANP), e-mail, `preferred_contact_method`, `seller_id`, `status`, soft deletes (only customers without work orders). |
| `customer_addresses` | `type` = `service` / `mailing` (one of each per customer), line 1/2, unit, city, state (USPS), ZIP / ZIP+4, county. |

### Work orders

| Table | Purpose / key columns |
|---|---|
| `work_orders` | Relationships (`organization_id`, `customer_id`, `seller_id`, `vendor_id`, `package_id`, `assigned_user_id`), `work_order_number`, `external_order_number`, `status`, `priority`, **snapshots** (`vendor_name_snapshot`, `package_code/name/description_snapshot`, `service_count`, `play_type`, `package_price`, `price_frequency`, `monthly_price`, `promotional_price`, `promotion_end_date`, `installation_charge`, `activation_charge`, `service_*` address), dates (`work_order_date`, `requested_installation_date`, `scheduled_installation_date` + `scheduled_time_window`, `deadline_date`, `completion_date`), event timestamps (`submitted_at`, `completed_at`, `cancelled_at`), `cancellation_reason`, `completion_notes`, `customer_notes`, alert markers. |
| `work_order_services` | Service snapshot per order (`service_code`, `service_name`, `service_category`, `details`). |
| `work_order_status_histories` | Immutable timeline: `from_status`, `to_status`, `changed_by`, `remarks`, `ip_address`, `user_agent`, `created_at`. |
| `work_order_notes` | Append-only internal notes. |
| `work_order_attachments` | `original_name` (display only), server-generated `path`, detected `mime_type`, `extension`, `size`, SHA-256 `checksum`, soft deletes with `deleted_by`. |

### System

| Table | Purpose |
|---|---|
| `audit_logs` | Append-only trail: `organization_id`, `user_id`, `actor_name`, `category`, `event`, subject type/id/label, `old_values`/`new_values` (JSON, secrets redacted), `ip_address`, `user_agent`, `created_at`. **No foreign keys and no soft deletes** — history survives any change. |
| `settings` | Key / JSON value / `updated_by`. |
| `sequences` | Atomic counters (`name`, `period`, `last_value`); PK (name, period). Work order numbers restart each year. |
| `notifications` | Laravel database notifications (in-app alerts). |
| `cache`, `cache_locks`, `jobs`, `job_batches`, `failed_jobs` | Laravel database drivers (no Redis). |

## 3. Integrity guarantees in the database

**CHECK constraints** (enforced even if application code is bypassed):

| Table | Constraint |
|---|---|
| `packages`, `work_orders` | `play_type` matches `service_count` (1 single · 2 double · 3 triple · 4 quad · 5+ multi) |
| `work_orders` | `deadline_date >= work_order_date`; `completion_date >= work_order_date`; status `completed` ⇒ `completion_date` NOT NULL; status `cancelled` ⇒ non-empty `cancellation_reason`; all prices ≥ 0; valid status / priority / frequency values |
| `packages` | prices ≥ 0, `effective_to >= effective_from`, promotion end ≥ start, valid frequency / status |
| `organizations` | main resellers have no parent, sub-resellers must have one; valid type / status / vendor access |
| `customers` | business customers require a company name; valid type / contact method / status |
| others | valid enum values for statuses, categories, address types, role scopes |

**Triggers:** `audit_logs_block_update` and `audit_logs_block_delete` reject any UPDATE/DELETE on `audit_logs`.

**Foreign keys:** `RESTRICT` from all business history to its parent (customers, sellers, vendors, packages,
work orders, users referenced by history). `CASCADE` only for owned detail rows that have no independent
history value (customer addresses, package services and price history of a deleted *unused* package,
the organization ↔ vendor availability pivot, role-permission pivots). `SET NULL` for optional
"created/updated by", assignee, seller-login and price-change-author references.

## 4. Indexes (query-driven)

* Tenant + status / date combinations: `work_orders(organization_id, status)`, `(organization_id, work_order_date)`,
  `(organization_id, deadline_date)`, `(status, deadline_date)`; `customers(organization_id, status)`,
  `(organization_id, created_at)`; `sellers(organization_id, status)`; `packages(vendor_id, status)`.
* Lookups and filters: unique business numbers and ULIDs, `play_type`, `completion_date`, `created_at`,
  `customer_addresses(state, zip)`, `zip`, `city`, `work_orders(service_state, service_zip)`, names, phone, e-mail.
* Audit: `(auditable_type, auditable_id)`, `(event, created_at)`, `(category, created_at)`, `created_at`.

## 5. Snapshots (history preservation)

A work order stores what was ordered **at that time**: vendor name, package code/name/description, services
with their package details, service count and play type, all prices (with the promotional price only when the
promotion applied on the work order date) and the service address. Later changes to vendors, packages, prices,
customers or sellers never rewrite existing orders. Editing a non-finished order refreshes the price snapshot
only when the package itself is changed (allowed while Draft / Submitted / On Hold) and the address only on
explicit request; both are audited.

## 6. Backup

Back up the database (`mysqldump --single-transaction --routines --triggers`) **together with**
`storage/app/private/attachments`, `storage/app/private/branding` and the `APP_KEY` (needed to decrypt
encrypted columns). See [INSTALLATION.md](INSTALLATION.md#5-backup--recovery).