# Data Model: Customer Management (SP-04 Re-execution)

**Date**: 2026-06-07
**Spec**: [./spec.md](./spec.md)
**Plan**: [./plan.md](./plan.md)

This document describes the entities involved in the Customer Management feature. No new tables are introduced; the feature operates exclusively on the existing schema defined in `doc/DB_Schema.md`. The two domain entities that participate are `Client` (table: `clients`) and the read-only `CustomerListingMv` (PostgreSQL Materialized View: `customer_listing_mv`).

---

## Entity 1: Client (table: `clients`)

The unified human entity. Holds customers, agents, and investors; distinguished by entries in the `client_type_flags` JSONB column.

### Fields

| Field | Type | Constraints | Notes |
|---|---|---|---|
| `id` | `BIGSERIAL` | `PRIMARY KEY` | Eloquent auto-incrementing; used as `client_id` everywhere. |
| `name` | `VARCHAR(150)` | `NOT NULL` | Required on create; optional on update. |
| `phone` | `VARCHAR(20)` | `NOT NULL`, `UNIQUE` | Syrian local format (10 digits, optional leading `0`); uniqueness across all `clients` (not just customers). |
| `description` | `TEXT` | `NULLABLE` | Optional. |
| `avatar` | `VARCHAR(255)` | `NULLABLE` | Relative path under the public disk's `avatars/` directory; NULL if no image. |
| `client_type_flags` | `JSONB` | `NOT NULL`, `DEFAULT '[]'` | Set to `["customer"]` on create; never modified by update; never removed. |
| `created_at` | `TIMESTAMPTZ` | `NOT NULL` | Eloquent timestamp. |
| `updated_at` | `TIMESTAMPTZ` | `NOT NULL` | Eloquent timestamp. |

### Eloquent Mapping

- **Model location**: `src/app/Domains/Customer/Models/Client.php`
- **Namespace**: `App\Domains\Customer\Models\Client`
- **Base class**: `Illuminate\Database\Eloquent\Model` (NOT `Authenticatable` — that's a fix from the previous execution)
- **Table**: `clients`
- **`$fillable`**: `name`, `phone`, `description`, `avatar`, `client_type_flags`
- **Casts**:
  - `client_type_flags` => `'array'` (PostgreSQL JSONB ↔ PHP array)

### Relationships

| Method | Returns | Foreign Key | Notes |
|---|---|---|---|
| `contractsAsCustomer()` | `HasMany<Contract>` | `contracts.customer_id` | Customer-side contracts. |
| `contractsAsAgent()` | `HasMany<Contract>` | `contracts.agent_id` | Agent-side contracts (nullable). |
| `sharesLogs()` | `HasMany<AgentSharesLog>` | `agent_shares_logs.client_id` | Shares log entries (this client as investor). |

### Behaviors (mutators + scope methods)

| Method | Returns | Purpose |
|---|---|---|
| `isCustomer(): bool` | bool | True if `'customer' ∈ client_type_flags`. |
| `isAgent(): bool` | bool | True if `'agent' ∈ client_type_flags`. |
| `isInvestor(): bool` | bool | True if `'investor' ∈ client_type_flags`. |
| `markAsCustomer(): void` | void | Adds `'customer'` to flags (if not present) and calls `save()`. Used by other domains (e.g., contract creation) to promote a client to customer status. |
| `markAsAgent(): void` | void | Symmetric helper. |
| `markAsInvestor(): void` | void | Symmetric helper (irreversible per spec). |

### Immutability Guards (Model-Level)

| Method | Behavior |
|---|---|
| `boot()` | Registers `static::deleting` listener that throws `ClientImmutableException`. |
| `delete()` | Overridden to throw `ClientImmutableException`. |
| `destroy($ids)` | Overridden to throw `ClientImmutableException`. |

Exception: `App\Exceptions\Business\ClientImmutableException`
Message (Arabic): `"لا يمكن حذف العميل"`
Status: maps to `403 Forbidden` via `ExceptionMappings.php`.

### Validation Rules (FormRequest-derived)

#### StoreCustomerRequest (Create)

| Field | Rule | Error message (Arabic) |
|---|---|---|
| `name` | `required`, `string`, `max:150` | الاسم مطلوب / لا يتجاوز 150 حرفاً |
| `phone` | `required`, `string`, `regex:/^0?9\d{8}$/`, `unique:clients,phone` | رقم الهاتف غير صالح / مستخدم مسبقاً |
| `description` | `nullable`, `string`, `max:1000` | الوصف لا يتجاوز 1000 حرف |
| `avatar` | `nullable`, `file`, `image`, `mimes:jpg,jpeg,png,webp`, `max:5120` (KB) | يجب أن يكون صورة بصيغة مدعومة / لا يتجاوز 5 ميغابايت |

#### UpdateCustomerRequest (Update)

| Field | Rule | Error message (Arabic) |
|---|---|---|
| `name` | `sometimes`, `string`, `max:150` | الاسم لا يتجاوز 150 حرفاً |
| `phone` | `sometimes`, `string`, `regex:/^0?9\d{8}$/`, `unique:clients,phone` (ignore current id) | رقم الهاتف غير صالح / مستخدم مسبقاً |
| `description` | `sometimes`, `nullable`, `string`, `max:1000` | الوصف لا يتجاوز 1000 حرف |
| `avatar` | `sometimes`, `nullable`, `file`, `image`, `mimes:jpg,jpeg,png,webp`, `max:5120` | يجب أن يكون صورة بصيغة مدعومة / لا يتجاوز 5 ميغابايت |

#### ListCustomersRequest (Index)

| Field | Rule | Error message (Arabic) |
|---|---|---|
| `search` | `nullable`, `string`, `max:150` | — |
| `from_date` | `nullable`, `date_format:Y-m-d` | صيغة التاريخ يجب أن تكون YYYY-MM-DD |
| `to_date` | `nullable`, `date_format:Y-m-d`, `after_or_equal:from_date` | — |
| `per_page` | `nullable`, `integer`, `min:1`, `max:100` | — |
| `cursor` | `nullable`, `string` | — |

---

## Entity 2: CustomerListingMv (PostgreSQL Materialized View)

A pre-computed, read-only projection of customer data used by the list endpoint. No writes happen through this entity.

### Definition (canonical SQL from constitution Section 14)

```sql
CREATE MATERIALIZED VIEW customer_listing_mv AS
SELECT
    c.id AS client_id,
    c.name,
    c.phone,
    c.avatar,
    MIN(ct.start_date) AS first_contract_date,
    MIN(i.due_date)    AS next_due_date,
    SUM(i.amount) AS total_installment_amount,
    SUM(CASE WHEN i.status = 'paid' THEN i.amount ELSE 0 END) AS total_collected_amount,
    SUM(CASE WHEN i.status IN ('pending', 'overdue') THEN i.amount ELSE 0 END) AS total_remaining_amount,
    COUNT(CASE WHEN i.status = 'pending' THEN 1 END) AS total_pending_installments,
    COUNT(CASE WHEN i.status = 'overdue' THEN 1 END) AS total_overdue_installments
FROM clients c
JOIN contracts ct ON ct.customer_id = c.id
    AND ct.status IN ('active', 'completed')
JOIN installments i ON i.contract_id = ct.id
    AND i.status IN ('pending', 'overdue', 'paid')
WHERE 'customer' = ANY(c.client_type_flags)
GROUP BY c.id, c.name, c.phone, c.avatar
WITH DATA;
```

### Required Indexes

```sql
CREATE UNIQUE INDEX idx_customer_listing_mv_client_id
    ON customer_listing_mv(client_id);  -- required for REFRESH CONCURRENTLY

CREATE INDEX idx_customer_listing_mv_next_due
    ON customer_listing_mv(next_due_date ASC NULLS LAST);

CREATE INDEX idx_customer_listing_mv_first_contract
    ON customer_listing_mv(first_contract_date);
```

### Fields (Model representation)

| Field | Eloquent cast | Used by |
|---|---|---|
| `client_id` | `int` (PK, not auto-incrementing) | All scopes |
| `name` | `string` | List response |
| `phone` | `string` | List response |
| `avatar` | `string` | List response |
| `first_contract_date` | `date` | `scopeFirstContractFrom`, `scopeFirstContractTo` |
| `next_due_date` | `date` | `scopeOrderByNextDue` |
| `total_installment_amount` | `decimal:2` | Detail response (not used in list) |
| `total_collected_amount` | `decimal:2` | Detail response (not used in list) |
| `total_remaining_amount` | `decimal:2` | List response (key field per spec) |
| `total_pending_installments` | `integer` | Detail response (not used in list) |
| `total_overdue_installments` | `integer` | Detail response (not used in list) |

### Eloquent Mapping

- **Model location**: `src/app/Domains/Customer/Models/CustomerListingMv.php`
- **Namespace**: `App\Domains\Customer\Models\CustomerListingMv`
- **Table**: `customer_listing_mv`
- **`$primaryKey`**: `client_id`
- **`$incrementing`**: `false`
- **`$timestamps`**: `false` (the MV has no created_at/updated_at)

### Scopes

| Scope | Signature | SQL produced |
|---|---|---|
| `search` | `scopeSearch($query, string $term)` | `where(fn($q) => $q->where('name', 'ILIKE', "%{$term}%")->orWhere('phone', 'ILIKE', "%{$term}%"))` |
| `firstContractFrom` | `scopeFirstContractFrom($query, Carbon $from)` | `where('first_contract_date', '>=', $from->toDateString())` |
| `firstContractTo` | `scopeFirstContractTo($query, Carbon $to)` | `where('first_contract_date', '<=', $to->toDateString())` |
| `orderByNextDue` | `scopeOrderByNextDue($query)` | `orderByRaw('next_due_date ASC NULLS LAST')->orderBy('client_id')` (the secondary `client_id` sort guarantees deterministic pagination) |

### Refresh Strategy

- **Schedule**: every 5 minutes (production) — see `app/Console/Kernel.php` or `bootstrap/app.php`'s schedule registration.
- **Command**: `php artisan analytics:refresh-customer-listing` (or equivalent); the existing scheduler should already be wired in SP-03.
- **Method**: `REFRESH MATERIALIZED VIEW CONCURRENTLY customer_listing_mv;` — uses the unique index on `client_id` to avoid locks.
- **Tests**: Feature tests call `DB::statement('REFRESH MATERIALIZED VIEW customer_listing_mv')` after seeding to ensure the MV is current.

---

## Supporting Entity 3: Customer Permission (Spatie)

Not a table — a Spatie permission record. Created by the seeder.

| Name | Guard | Assigned to | Used by route |
|---|---|---|---|
| `customers.view` | `admin` | Admin (via `syncPermissions`) | `GET /api/v1/customers`, `GET /api/v1/customers/{id}` |
| `customers.create` | `admin` | Admin | `POST /api/v1/customers` |
| `customers.update` | `admin` | Admin | `PUT /api/v1/customers/{id}` |

### Seeder

**File**: `src/database/seeders/CustomerPermissionsSeeder.php`
**Registered in**: `src/database/seeders/DatabaseSeeder.php` (added to the `run()` method).
**Idempotent**: yes — uses `Permission::firstOrCreate(['name' => ..., 'guard_name' => 'admin'])` and `Admin::first()->syncPermissions([...])`.

---

## Cross-Entity: Storage Layout (avatars)

| Concern | Value |
|---|---|
| Filesystem disk | `public` (Laravel Storage; resolves to `storage/app/public`) |
| Directory | `avatars/` |
| Filename pattern | Laravel's auto-generated name (e.g., `avatar_abc123.jpg`) — preserves extension |
| Database column | `clients.avatar` (VARCHAR 255) stores the relative path returned by `Storage::putFileAs('avatars', $file, $name)`, e.g., `avatars/abc123.jpg` |
| Public URL | `asset('storage/' . $client->avatar)` (i.e., `http://host/storage/avatars/abc123.jpg`) |
| Old file cleanup on update | Yes — `Storage::disk('public')->delete($oldAvatar)` after the new file is stored and the row is updated. Best-effort; not transactional. |

---

## State Transitions

The `Client` entity is intentionally simple — there are no explicit state columns on the customer (the entity's "state" is the `client_type_flags` array). The transitions are:

```
[any state] --POST /customers--> Client with client_type_flags = ['customer']
[any state] --PUT /customers/{id}--> same Client with name/phone/description/avatar updated
                                              (client_type_flags unchanged)
[any state] --any code path attempting delete--> ClientImmutableException
[customer] --contract creation (SP-07)--> client_type_flags = ['customer'] (already present, no change)
[customer,agent] --shares add (SP-05)--> client_type_flags = ['customer', 'agent', 'investor']
```

The "Investor becomes permanent" rule is enforced by `markAsInvestor()` and is irreversible (per DB_Schema.md Section 1). This is mentioned for completeness but is out of SP-04 scope.

---

## ER Summary

```
                  ┌─────────────┐
                  │   clients   │
                  ├─────────────┤
                  │ id (PK)     │
                  │ name        │
                  │ phone (U)   │
                  │ description │
                  │ avatar      │
                  │ client_     │
                  │  type_flags │
                  └──────┬──────┘
                         │
        ┌────────────────┼────────────────┐
        │                │                │
        │ customer_id    │ agent_id       │ client_id
        ▼                ▼                ▼
  ┌──────────┐    ┌──────────┐    ┌───────────────┐
  │contracts │    │contracts │    │agent_shares_  │
  │          │    │          │    │logs           │
  └────┬─────┘    └──────────┘    └───────────────┘
       │
       │ (active|completed only)
       ▼
  ┌──────────┐
  │installm- │
  │ents      │
  └──────────┘
       │
       │ (joined at runtime for detail view)
       ▼
  customer_listing_mv (read-only MV, refreshed every 5 min)
```

---

## Migration Strategy

**No new migrations are required for SP-04.**

The `clients` table, its indexes, and the `customer_listing_mv` materialized view are already in place from SP-02 and SP-03. The re-execution only writes Eloquent code; the database is a fixed input.

If verification reveals any missing index (e.g., the `idx_customer_listing_mv_next_due` index was not created in SP-02), a follow-up migration is acceptable per constitution Section 3.4 ("no modification to existing migration; instead: new migration"). This will be verified during implementation.