# Database Schema & ER Diagram — Locker Management System

**Stack:** Laravel 11 + MySQL 8 + React (admin SPA via Sanctum)
**Scope:** Admin-only, physical keys with deposits, multi-channel notifications, Bangladesh payment gateways
**Conventions:** snake_case table & column names, plural tables, `id` primary keys (BIGINT UNSIGNED), Laravel timestamps (`created_at`, `updated_at`), soft deletes (`deleted_at`) on entities that carry historical value.

---

## 1. Design Principles

The schema is built around five principles. **First**, every entity that carries history (clients, lockers, rentals, payments) uses soft-deletes so audit and reporting stay intact even after "deletion." **Second**, money is never reused across concerns — `payments` records collections, `deposits` is a separate ledger for refundable key deposits (they are liabilities, not revenue), `invoices` is the formal billing document. **Third**, the rental is the central join — almost every operational query (who has a locker, when does it expire, how much do they owe) starts at `rentals`. **Fourth**, configuration is data, not code — pricing plans, notification templates, zones, categories, and payment methods all live in tables so the gym can change them without a redeploy. **Fifth**, audit is mandatory — every meaningful write goes through `activity_log` (Spatie package), and every notification dispatch is recorded in `notifications` so you can prove delivery and track cost.

---

## 2. ER Diagram (Mermaid)

```mermaid
erDiagram
    users ||--o{ rentals : "assigned_by"
    users ||--o{ payments : "collected_by"
    users ||--o{ maintenance_tickets : "reports"
    users ||--o{ activity_log : "performs"
    users }o--o{ roles : "has"
    roles }o--o{ permissions : "grants"

    branches ||--o{ zones : "contains"
    branches ||--o{ lockers : "contains"

    zones ||--o{ lockers : "groups"
    locker_categories ||--o{ lockers : "categorizes"
    locker_categories ||--o{ pricing_plans : "prices"
    zones ||--o{ pricing_plans : "prices"

    lockers ||--o{ keys : "has"
    lockers ||--o{ rentals : "rented_as"
    lockers ||--o{ maintenance_tickets : "needs"
    lockers ||--o{ lost_found_items : "found_in"

    clients ||--o{ rentals : "rents"
    clients ||--o{ payments : "pays"
    clients ||--o{ notifications : "receives"
    clients ||--o{ lost_key_incidents : "reports"
    clients ||--o{ client_documents : "uploads"
    clients }o--o{ tags : "tagged_with"

    pricing_plans ||--o{ rentals : "priced_by"

    rentals ||--o{ renewals : "extended_by"
    rentals ||--o{ payments : "billed_in"
    rentals ||--o| invoices : "documented_by"
    rentals ||--o{ deposits : "secured_by"
    rentals ||--o{ lost_key_incidents : "raises"
    rentals ||--o{ coupon_redemptions : "applies"
    keys ||--o{ rentals : "issued_to"

    invoices ||--o{ invoice_items : "contains"
    invoices ||--o{ payments : "settled_by"

    payment_methods ||--o{ payments : "via"
    payments ||--o| deposits : "collects"

    discount_coupons ||--o{ coupon_redemptions : "redeemed_as"

    notification_templates ||--o{ notifications : "renders"
```

> The diagram above lists relationships only. The complete column list for each entity follows in section 4. If your Markdown viewer doesn't render Mermaid, open `03_er_diagram.mermaid` — it renders inline in the Cowork app.

---

## 3. Logical Module Grouping

The 25 tables fall into six logical modules:

**Identity & Access** — `users`, `roles`, `permissions`, `model_has_roles`, `model_has_permissions`, `role_has_permissions` (Spatie tables).

**Reference / Configuration** — `branches`, `zones`, `locker_categories`, `pricing_plans`, `payment_methods`, `notification_templates`, `settings`, `tags`.

**Client Management** — `clients`, `client_documents`, `client_tag` (pivot).

**Locker Inventory** — `lockers`, `keys`, `maintenance_tickets`, `lost_found_items`.

**Rentals & Billing** — `rentals`, `renewals`, `payments`, `deposits`, `invoices`, `invoice_items`, `discount_coupons`, `coupon_redemptions`, `lost_key_incidents`.

**Notifications & Audit** — `notifications`, `activity_log`, `backups`.

---

## 4. Table Schemas

### 4.1 Identity & Access

#### `users` (admin staff only — never clients)

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `name` | VARCHAR(255) | |
| `email` | VARCHAR(255) UNIQUE | |
| `phone` | VARCHAR(20) NULL | |
| `email_verified_at` | TIMESTAMP NULL | |
| `password` | VARCHAR(255) | bcrypt hashed |
| `two_factor_secret` | TEXT NULL | encrypted |
| `two_factor_recovery_codes` | TEXT NULL | encrypted |
| `is_active` | BOOLEAN DEFAULT TRUE | |
| `last_login_at` | TIMESTAMP NULL | |
| `last_login_ip` | VARCHAR(45) NULL | IPv6 capable |
| `remember_token` | VARCHAR(100) NULL | |
| `created_at`, `updated_at`, `deleted_at` | TIMESTAMP | |

Indexes: `email` (unique), `is_active`.

Spatie's `roles`, `permissions`, `model_has_roles`, `model_has_permissions`, `role_has_permissions` ship as-is from the package — no schema customization needed.

---

### 4.2 Reference / Configuration

#### `branches` (single row for one gym; multi-row when multi-branch lands)

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `name` | VARCHAR(255) | |
| `address` | TEXT NULL | |
| `phone` | VARCHAR(20) NULL | |
| `email` | VARCHAR(255) NULL | |
| `is_active` | BOOLEAN DEFAULT TRUE | |
| `created_at`, `updated_at` | | |

#### `zones` (Men's, Women's, Family, VIP, Staff)

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `branch_id` | FK → branches.id | |
| `name` | VARCHAR(100) | "Men's Section A" |
| `code` | VARCHAR(20) UNIQUE | "MEN-A" |
| `gender_restriction` | ENUM('male','female','any') DEFAULT 'any' | enforced at assignment time |
| `description` | TEXT NULL | |
| `display_order` | INT DEFAULT 0 | |
| `is_active` | BOOLEAN DEFAULT TRUE | |
| `created_at`, `updated_at` | | |

Indexes: `branch_id`, `code` (unique).

#### `locker_categories` (Small / Medium / Large / Premium)

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `name` | VARCHAR(50) | |
| `code` | VARCHAR(20) UNIQUE | |
| `description` | TEXT NULL | |
| `base_price` | DECIMAL(10,2) | reference price; pricing_plans can override |
| `display_order` | INT DEFAULT 0 | |
| `is_active` | BOOLEAN DEFAULT TRUE | |
| `created_at`, `updated_at` | | |

#### `pricing_plans` (category × zone × duration → price)

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `category_id` | FK → locker_categories.id NULL | NULL = all categories |
| `zone_id` | FK → zones.id NULL | NULL = all zones |
| `name` | VARCHAR(100) | "Standard Monthly" |
| `duration_days` | INT | 1, 7, 30, 90, 180, 365, or custom |
| `price` | DECIMAL(10,2) | rental fee before tax/discount |
| `deposit_amount` | DECIMAL(10,2) DEFAULT 0 | refundable key deposit |
| `tax_rate` | DECIMAL(5,2) DEFAULT 0 | percentage |
| `is_default` | BOOLEAN DEFAULT FALSE | |
| `is_active` | BOOLEAN DEFAULT TRUE | |
| `created_at`, `updated_at` | | |

Indexes: `(category_id, zone_id, duration_days)`, `is_active`.

#### `payment_methods` (Cash, bKash, Nagad, SSLCommerz)

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `name` | VARCHAR(100) | "bKash" |
| `code` | VARCHAR(50) UNIQUE | "bkash" |
| `type` | ENUM('manual','gateway') | |
| `config` | JSON NULL | encrypted API keys, merchant numbers |
| `is_active` | BOOLEAN DEFAULT TRUE | |
| `display_order` | INT DEFAULT 0 | |
| `created_at`, `updated_at` | | |

#### `notification_templates`

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `code` | VARCHAR(100) | e.g. "rental_assigned", "expiry_7d" |
| `name` | VARCHAR(255) | |
| `channel` | ENUM('email','sms','whatsapp','inapp') | |
| `language` | ENUM('en','bn') DEFAULT 'bn' | |
| `subject` | VARCHAR(500) NULL | email only |
| `body` | TEXT | with `{{placeholders}}` |
| `variables` | JSON NULL | list of available variables for UI hint |
| `is_active` | BOOLEAN DEFAULT TRUE | |
| `created_at`, `updated_at` | | |

Indexes: `UNIQUE(code, channel, language)`.

#### `settings` (key-value system config)

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `key` | VARCHAR(100) UNIQUE | "company.name", "reminders.days_before" |
| `value` | TEXT | |
| `type` | ENUM('string','number','boolean','json','encrypted') | |
| `group` | VARCHAR(50) | "company", "reminders", "pricing", "invoice" |
| `description` | TEXT NULL | |
| `updated_by` | FK → users.id NULL | |
| `created_at`, `updated_at` | | |

#### `tags`

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `name` | VARCHAR(100) UNIQUE | "VIP", "Corporate" |
| `color` | VARCHAR(20) NULL | hex |
| `created_at`, `updated_at` | | |

---

### 4.3 Client Management

#### `clients`

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `client_code` | VARCHAR(20) UNIQUE | "C-2026-00001" |
| `full_name` | VARCHAR(255) | |
| `gender` | ENUM('male','female','other') | drives zone assignment |
| `date_of_birth` | DATE NULL | |
| `phone` | VARCHAR(20) | required for SMS/WhatsApp |
| `email` | VARCHAR(255) NULL | |
| `nid_or_passport` | VARCHAR(50) NULL | |
| `address` | TEXT NULL | |
| `emergency_contact_name` | VARCHAR(255) NULL | |
| `emergency_contact_phone` | VARCHAR(20) NULL | |
| `photo_path` | VARCHAR(500) NULL | profile photo |
| `signature_path` | VARCHAR(500) NULL | T&C signature |
| `preferred_language` | ENUM('en','bn') DEFAULT 'bn' | drives notification template |
| `status` | ENUM('active','inactive','banned') DEFAULT 'active' | |
| `ban_reason` | TEXT NULL | |
| `referral_source` | VARCHAR(255) NULL | |
| `notes` | TEXT NULL | |
| `registered_by` | FK → users.id | |
| `registered_at` | TIMESTAMP | |
| `created_at`, `updated_at`, `deleted_at` | | |

Indexes: `client_code` (unique), `phone`, `email`, `nid_or_passport`, `status`, `gender`, `full_name` (FULLTEXT for search).

#### `client_documents`

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `client_id` | FK → clients.id ON DELETE CASCADE | |
| `document_type` | ENUM('nid','passport','photo','signature','other') | |
| `file_path` | VARCHAR(500) | |
| `original_filename` | VARCHAR(255) | |
| `mime_type` | VARCHAR(100) | |
| `file_size` | INT | bytes |
| `uploaded_by` | FK → users.id | |
| `created_at`, `updated_at` | | |

#### `client_tag` (pivot)

| Column | Type | Notes |
|---|---|---|
| `client_id` | FK → clients.id ON DELETE CASCADE | |
| `tag_id` | FK → tags.id ON DELETE CASCADE | |

Primary key: `(client_id, tag_id)`.

---

### 4.4 Locker Inventory

#### `lockers`

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `branch_id` | FK → branches.id | |
| `zone_id` | FK → zones.id | |
| `category_id` | FK → locker_categories.id | |
| `locker_number` | VARCHAR(50) UNIQUE | "M-001" |
| `floor` | VARCHAR(50) NULL | |
| `position` | VARCHAR(50) NULL | "Row 2, Col 5" |
| `status` | ENUM('available','assigned','reserved','maintenance','cleaning','out_of_service') DEFAULT 'available' | |
| `notes` | TEXT NULL | |
| `is_active` | BOOLEAN DEFAULT TRUE | |
| `created_at`, `updated_at`, `deleted_at` | | |

Indexes: `locker_number` (unique), `(zone_id, status)`, `(category_id, status)`, `status`.

#### `keys` (physical keys, one or more per locker over time)

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `locker_id` | FK → lockers.id | |
| `key_code` | VARCHAR(50) | physical tag number |
| `status` | ENUM('available','issued','lost','damaged','replaced','retired') DEFAULT 'available' | |
| `notes` | TEXT NULL | |
| `created_at`, `updated_at` | | |

Indexes: `(locker_id, status)`, `key_code`.

**Note:** "currently issued to which rental" is tracked on `rentals.key_id`, not duplicated here — single source of truth.

#### `maintenance_tickets`

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `ticket_number` | VARCHAR(50) UNIQUE | "MT-2026-00001" |
| `locker_id` | FK → lockers.id | |
| `title` | VARCHAR(255) | |
| `description` | TEXT | |
| `priority` | ENUM('low','medium','high','urgent') DEFAULT 'medium' | |
| `status` | ENUM('open','in_progress','resolved','closed','cancelled') DEFAULT 'open' | |
| `reported_by` | FK → users.id | |
| `assigned_to` | FK → users.id NULL | |
| `resolved_at` | TIMESTAMP NULL | |
| `resolution_notes` | TEXT NULL | |
| `attachment_path` | VARCHAR(500) NULL | photo of the issue |
| `created_at`, `updated_at` | | |

Indexes: `(locker_id, status)`, `(status, priority)`.

#### `lost_found_items` (belongings from expired/abandoned lockers)

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `locker_id` | FK → lockers.id NULL | |
| `rental_id` | FK → rentals.id NULL | the rental that expired |
| `client_id` | FK → clients.id NULL | last assigned client |
| `item_description` | TEXT | |
| `photo_path` | VARCHAR(500) NULL | |
| `found_date` | DATE | |
| `storage_location` | VARCHAR(255) NULL | "Front desk box 3" |
| `status` | ENUM('stored','claimed','disposed') DEFAULT 'stored' | |
| `claimed_by` | VARCHAR(255) NULL | client name or other |
| `claimed_date` | DATE NULL | |
| `disposed_date` | DATE NULL | |
| `notes` | TEXT NULL | |
| `recorded_by` | FK → users.id | |
| `created_at`, `updated_at` | | |

Indexes: `(client_id)`, `(status, found_date)`.

---

### 4.5 Rentals & Billing — the heart of the system

#### `rentals` (one row per locker assignment instance)

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `rental_code` | VARCHAR(20) UNIQUE | "R-2026-00001" |
| `client_id` | FK → clients.id | |
| `locker_id` | FK → lockers.id | |
| `key_id` | FK → keys.id NULL | physical key currently issued |
| `pricing_plan_id` | FK → pricing_plans.id | |
| `start_date` | DATE | |
| `expiry_date` | DATE | |
| `grace_until` | DATE NULL | expiry + grace days (from settings) |
| `rental_fee` | DECIMAL(10,2) | base price |
| `deposit_amount` | DECIMAL(10,2) DEFAULT 0 | refundable |
| `discount_amount` | DECIMAL(10,2) DEFAULT 0 | |
| `tax_amount` | DECIMAL(10,2) DEFAULT 0 | |
| `total_amount` | DECIMAL(10,2) | rental_fee + tax - discount + deposit |
| `paid_amount` | DECIMAL(10,2) DEFAULT 0 | sum of completed payments |
| `due_amount` | DECIMAL(10,2) | total_amount - paid_amount; can be GENERATED column |
| `payment_status` | ENUM('paid','partial','due','overdue','refunded') DEFAULT 'due' | |
| `status` | ENUM('reserved','active','expired','cancelled','completed') DEFAULT 'reserved' | |
| `key_issued_at` | TIMESTAMP NULL | when physical key handed over |
| `key_returned_at` | TIMESTAMP NULL | |
| `deposit_refunded_amount` | DECIMAL(10,2) DEFAULT 0 | |
| `cancellation_reason` | TEXT NULL | |
| `assigned_by` | FK → users.id | staff who created |
| `closed_by` | FK → users.id NULL | staff who closed |
| `notes` | TEXT NULL | |
| `created_at`, `updated_at`, `deleted_at` | | |

Indexes: `rental_code` (unique), `(client_id, status)`, `(locker_id, status)`, `(expiry_date, status)`, `payment_status`, `(status, expiry_date)`.

**Status lifecycle:** `reserved` (assignment recorded, awaiting payment/key handover) → `active` (paid + key issued) → `expired` (past expiry, in grace) → `completed` (key returned, deposit settled) OR `cancelled` (closed before expiry).

#### `renewals`

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `rental_id` | FK → rentals.id ON DELETE CASCADE | |
| `previous_expiry_date` | DATE | |
| `new_expiry_date` | DATE | |
| `duration_days` | INT | |
| `amount` | DECIMAL(10,2) | |
| `tax_amount` | DECIMAL(10,2) DEFAULT 0 | |
| `discount_amount` | DECIMAL(10,2) DEFAULT 0 | |
| `total_amount` | DECIMAL(10,2) | |
| `payment_id` | FK → payments.id NULL | |
| `processed_by` | FK → users.id | |
| `notes` | TEXT NULL | |
| `created_at` | | renewal date |

Indexes: `(rental_id, created_at)`.

#### `payments`

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `payment_code` | VARCHAR(30) UNIQUE | "P-2026-00001" |
| `client_id` | FK → clients.id | |
| `rental_id` | FK → rentals.id NULL | |
| `invoice_id` | FK → invoices.id NULL | |
| `payment_method_id` | FK → payment_methods.id | |
| `type` | ENUM('rental','renewal','deposit','lost_key_fee','other','refund') | |
| `amount` | DECIMAL(10,2) | positive; refunds use type='refund' |
| `status` | ENUM('pending','completed','failed','refunded','cancelled') DEFAULT 'pending' | |
| `transaction_reference` | VARCHAR(255) NULL | gateway txn ID |
| `gateway_response` | JSON NULL | raw API response |
| `paid_at` | TIMESTAMP NULL | |
| `collected_by` | FK → users.id | for cash; nullable for gateway |
| `notes` | TEXT NULL | |
| `created_at`, `updated_at` | | |

Indexes: `payment_code` (unique), `(client_id, paid_at)`, `(rental_id, status)`, `(status, paid_at)`, `transaction_reference`.

#### `deposits` (separate ledger for key deposits — liabilities, not revenue)

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `rental_id` | FK → rentals.id | |
| `client_id` | FK → clients.id | |
| `amount` | DECIMAL(10,2) | |
| `payment_id` | FK → payments.id NULL | the collecting payment |
| `refunded_amount` | DECIMAL(10,2) DEFAULT 0 | |
| `refunded_at` | TIMESTAMP NULL | |
| `refund_payment_id` | FK → payments.id NULL | the refund payment |
| `deducted_amount` | DECIMAL(10,2) DEFAULT 0 | e.g. lost-key fee |
| `deduction_reason` | TEXT NULL | |
| `status` | ENUM('held','refunded','partially_refunded','forfeited') DEFAULT 'held' | |
| `created_at`, `updated_at` | | |

Indexes: `(rental_id)`, `(client_id, status)`.

#### `invoices`

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `invoice_number` | VARCHAR(50) UNIQUE | "INV-2026-00001" |
| `client_id` | FK → clients.id | |
| `rental_id` | FK → rentals.id NULL | |
| `issued_date` | DATE | |
| `due_date` | DATE NULL | |
| `subtotal` | DECIMAL(10,2) | |
| `discount_amount` | DECIMAL(10,2) DEFAULT 0 | |
| `tax_amount` | DECIMAL(10,2) DEFAULT 0 | |
| `total_amount` | DECIMAL(10,2) | |
| `paid_amount` | DECIMAL(10,2) DEFAULT 0 | |
| `status` | ENUM('draft','issued','paid','partial','overdue','cancelled','refunded') DEFAULT 'draft' | |
| `pdf_path` | VARCHAR(500) NULL | cached generated PDF |
| `notes` | TEXT NULL | |
| `created_by` | FK → users.id | |
| `created_at`, `updated_at` | | |

Indexes: `invoice_number` (unique), `(client_id, issued_date)`, `(status, due_date)`.

#### `invoice_items`

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `invoice_id` | FK → invoices.id ON DELETE CASCADE | |
| `description` | VARCHAR(500) | |
| `quantity` | INT DEFAULT 1 | |
| `unit_price` | DECIMAL(10,2) | |
| `discount_amount` | DECIMAL(10,2) DEFAULT 0 | |
| `tax_amount` | DECIMAL(10,2) DEFAULT 0 | |
| `line_total` | DECIMAL(10,2) | |
| `created_at`, `updated_at` | | |

#### `discount_coupons`

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `code` | VARCHAR(50) UNIQUE | "NEWYEAR2026" |
| `description` | VARCHAR(255) NULL | |
| `discount_type` | ENUM('percent','fixed') | |
| `discount_value` | DECIMAL(10,2) | |
| `min_amount` | DECIMAL(10,2) NULL | minimum rental to qualify |
| `max_redemptions` | INT NULL | NULL = unlimited |
| `redemptions_count` | INT DEFAULT 0 | |
| `valid_from` | DATE NULL | |
| `valid_to` | DATE NULL | |
| `is_active` | BOOLEAN DEFAULT TRUE | |
| `created_at`, `updated_at` | | |

#### `coupon_redemptions`

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `coupon_id` | FK → discount_coupons.id | |
| `rental_id` | FK → rentals.id | |
| `client_id` | FK → clients.id | |
| `discount_amount` | DECIMAL(10,2) | actual amount applied |
| `redeemed_at` | TIMESTAMP | |

Indexes: `(coupon_id, redeemed_at)`, `(client_id)`.

#### `lost_key_incidents`

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `rental_id` | FK → rentals.id | |
| `client_id` | FK → clients.id | |
| `key_id` | FK → keys.id | the lost key |
| `replacement_key_id` | FK → keys.id NULL | new key issued |
| `replacement_fee` | DECIMAL(10,2) | |
| `deducted_from_deposit` | BOOLEAN DEFAULT FALSE | |
| `fee_payment_id` | FK → payments.id NULL | if paid out-of-pocket |
| `status` | ENUM('reported','replaced','closed') DEFAULT 'reported' | |
| `reported_at` | TIMESTAMP | |
| `resolved_at` | TIMESTAMP NULL | |
| `notes` | TEXT NULL | |
| `reported_by` | FK → users.id | |
| `created_at`, `updated_at` | | |

Indexes: `(client_id, status)`, `(rental_id)`.

---

### 4.6 Notifications & Audit

#### `notifications` (delivery log — every message sent)

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `client_id` | FK → clients.id NULL | recipient if client |
| `user_id` | FK → users.id NULL | recipient if admin in-app |
| `template_id` | FK → notification_templates.id NULL | |
| `channel` | ENUM('email','sms','whatsapp','inapp') | |
| `recipient` | VARCHAR(255) | email/phone actually used |
| `subject` | VARCHAR(500) NULL | |
| `body` | TEXT | rendered final content |
| `status` | ENUM('queued','sending','sent','delivered','failed','read') DEFAULT 'queued' | |
| `failure_reason` | TEXT NULL | |
| `external_message_id` | VARCHAR(255) NULL | provider's message ID |
| `cost` | DECIMAL(10,4) NULL | per-message cost in BDT |
| `related_type` | VARCHAR(100) NULL | polymorphic: "Rental","Payment","Invoice" |
| `related_id` | BIGINT UNSIGNED NULL | polymorphic ID |
| `sent_at` | TIMESTAMP NULL | |
| `delivered_at` | TIMESTAMP NULL | |
| `read_at` | TIMESTAMP NULL | in-app only |
| `created_at`, `updated_at` | | |

Indexes: `(client_id, created_at)`, `(status, created_at)`, `(related_type, related_id)`, `(channel, status)`.

#### `activity_log` (Spatie schema)

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `log_name` | VARCHAR(255) NULL | |
| `description` | TEXT | "Rental R-2026-00010 created" |
| `subject_type` | VARCHAR(255) NULL | polymorphic |
| `subject_id` | BIGINT UNSIGNED NULL | |
| `causer_type` | VARCHAR(255) NULL | usually "User" |
| `causer_id` | BIGINT UNSIGNED NULL | |
| `properties` | JSON NULL | before/after values |
| `event` | VARCHAR(255) NULL | "created", "updated", "deleted" |
| `batch_uuid` | CHAR(36) NULL | |
| `ip_address` | VARCHAR(45) NULL | |
| `user_agent` | VARCHAR(500) NULL | |
| `created_at`, `updated_at` | | |

Indexes: `(subject_type, subject_id)`, `(causer_type, causer_id)`, `(log_name)`, `created_at`.

#### `backups`

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `filename` | VARCHAR(255) | |
| `file_path` | VARCHAR(500) | |
| `file_size` | BIGINT | bytes |
| `type` | ENUM('manual','scheduled') | |
| `status` | ENUM('in_progress','completed','failed') | |
| `error_message` | TEXT NULL | |
| `started_at` | TIMESTAMP | |
| `completed_at` | TIMESTAMP NULL | |
| `created_by` | FK → users.id NULL | |
| `created_at`, `updated_at` | | |

---

## 5. Critical Indexes Summary

Beyond unique constraints and FKs, these composite indexes matter for performance:

The dashboard's "expiring this week" query hits `rentals(expiry_date, status)`. The locker grid filter relies on `lockers(zone_id, status)` and `lockers(category_id, status)`. Payment-due reports use `rentals(payment_status, expiry_date)` and `payments(status, paid_at)`. Client search by name uses a FULLTEXT index on `clients.full_name`; exact-match lookups use B-tree on `phone`, `email`, `nid_or_passport`. The notification log query "all messages for client X" uses `notifications(client_id, created_at)`, while the polymorphic "all notifications about rental Y" uses `notifications(related_type, related_id)`.

## 6. Status Lifecycle Diagrams

**Rental lifecycle:** `reserved` → (payment + key issued) → `active` → (expiry passes) → `expired` → (key returned, deposit settled) → `completed`. Side path: `reserved`/`active` → `cancelled` if the client backs out.

**Payment lifecycle:** For cash, payments go straight to `completed`. For gateway, `pending` → `completed` on callback (or `failed`).

**Deposit lifecycle:** `held` (while rental active) → on key return either `refunded` (full), `partially_refunded` (lost-key deduction), or `forfeited` (key never returned, abandoned).

**Key lifecycle:** `available` → `issued` (on rental) → `available` (on return) → optionally `lost` or `damaged` → `replaced` (when superseded by a new key) → `retired` (when the locker is decommissioned).

## 7. Things Intentionally NOT in the Schema

To keep the system focused: no `members`, `memberships`, `classes`, `trainers`, `workouts`, `attendance`, or `equipment` tables. If the gym needs those, they should keep their existing gym management software and we expose `clients` and `rentals` via API/webhook for cross-system sync. No `smart_locks` or `hardware_devices` table since you're using physical keys; if hardware is added later, a new module slots in without breaking this schema. No member-facing auth, sessions, or password resets for clients — admins manage everything.

## 8. Companion Files

- **`03_schema.sql`** — full MySQL DDL ready to run (or to convert to Laravel migrations)
- **`03_er_diagram.mermaid`** — standalone Mermaid file that renders in Cowork's viewer

---

## Next Steps After Approval

Once you sign off on this schema, the next deliverables would be **Laravel migration files** (one PHP migration per table, ordered by FK dependency), **Eloquent models** with relationships pre-wired, and **seed data** (zones, categories, default pricing plans, payment methods, notification templates in Bengali + English, an initial Super Admin user).