# Data Model — SP-05 Agent Management & Investor Logic

**Branch**: `006-agent-investor` | **Date**: 2026-06-15
**Spec**: [spec.md](spec.md)
**Plan**: [plan.md](plan.md)

This document describes the data model for SP-05 in entity-by-entity detail. All tables referenced here are **existing** from SP-02 (PostgreSQL Foundation); SP-05 introduces **no new migrations**.

---

## Entity 1: Agent (sub-role of `Client`)

**Source table**: `clients` (existing from SP-02)
**Inheritance**: Agent is a *sub-role* of the unified `Client` entity. The "agent" identity is determined by the presence of the `agent` string in `client_type_flags` (BR-001-3, BR-001-5).

### Fields Used by SP-05

| Field | Type | Required | Notes |
|---|---|---|---|
| `id` | `BIGSERIAL` | Yes | Primary key |
| `name` | `VARCHAR(150)` | Yes | Display name; sort key for list |
| `phone` | `VARCHAR(20)` | Yes | UNIQUE constraint; search/contact |
| `description` | `TEXT` | No | Free-form notes |
| `avatar` | `VARCHAR(255)` | No | Relative path; **NOT used in SP-05** (per OQ-8) |
| `reference_number` | `VARCHAR(20)` | Yes (auto) | `CUS-XXXXXXXXXX`; **NEVER returned in API** (data minimization, SP-05-FR-030) |
| `client_type_flags` | `JSONB` | Yes (default `[]`) | Contains `"agent"` for an agent; `"investor"` added permanently on first share addition (BR-001-6) |
| `created_at` | `TIMESTAMPTZ` | Yes | Used by `HasReferenceNumber` trait |
| `updated_at` | `TIMESTAMPTZ` | Yes | Auto-maintained by Eloquent |

### Validation Rules

| Field | Rule | Source |
|---|---|---|
| `name` | `required`, `string`, `max:150` | FR-3.2, `07_06` |
| `phone` | `required`, `string`, `max:20`, `unique:clients,phone` (ignore on update) | FR-3.2, `01_02` |
| `description` | `nullable`, `string`, `max:65535` | FR-3.2 |
| `shares_count` (create only) | `nullable`, `integer`, `min:0` (default 0) | FR-3.2 |

### State Transitions

The `clients` table itself has no state column; state is implicit in the flag composition:

```
Empty flags []  →  POST /agents  →  ["agent"]                                  (creation, no shares)
Empty flags []  →  POST /agents with shares_count=N  →  ["agent", "investor"]   (creation with shares)
["agent"]       →  POST /agents/{id}/shares action=add  →  ["agent", "investor"] (first add)
["agent","investor"]  →  (no further flag changes possible; investor is permanent)
```

**Critical invariant** (BR-001-6, AC-022): The `"investor"` flag, once added, is NEVER removed — even when share balance returns to 0, when all share logs are in `deleted` state, or under any other code path.

### Relationships (from `clients` table)

| Relationship | Method | Type | Notes |
|---|---|---|---|
| `contractsAsAgent` | `hasMany(Contract::class, 'agent_id')` | HasMany | Used to compute agent summary |
| `contractsAsCustomer` | `hasMany(Contract::class, 'customer_id')` | HasMany | Not used in SP-05 |
| `sharesLogs` | `hasMany(AgentSharesLog::class, 'client_id')` | HasMany | Used to compute share balance and history |

### Inference Methods (existing from SP-03)

```php
public function isCustomer(): bool
public function isAgent(): bool
public function isInvestor(): bool
```

### Mutation Methods (existing from SP-03)

```php
public function markAsRole(string $role): void    // appends to flags, saves (no saveQuietly)
public function markAsCustomer(): void
public function markAsAgent(): void
public function markAsInvestor(): void
```

### Immutability (existing from SP-03)

```php
public function delete() { throw new ClientImmutableException(); }
public static function destroy($ids) { throw new ClientImmutableException(); }
protected static function booted() {
    static::deleting(function (Client $c) { throw new ClientImmutableException(); });
}
```

Database-level: `ON DELETE RESTRICT` on `contracts.customer_id`, `contracts.agent_id`, `agent_shares_logs.client_id` (existing from SP-02, `04_04`).

---

## Entity 2: Investor (sub-role of `Client`)

The Investor is not a separate table; it is a *flag state* of the `Client` entity. The "investor" property is computed via the `isInvestor()` inference method on `Client`.

### Definition

A Client is an Investor **if and only if** `"investor"` is present in `client_type_flags` (BR-001-3, BR-001-6).

### Permanence Rule (BR-001-6, BR-005-1)

The `investor` flag, once set, MUST NEVER be removed. This is enforced at three levels:

1. **Model layer**: `markAsRole()` only appends; there is no `removeAsRole()` method.
2. **No code path**: no service, controller, or model method invokes flag removal.
3. **Spec test (AC-022)**: integration test verifies that after share balance returns to 0, `isInvestor()` still returns `true`.

### Triggers for `investor` Flag Setting

| Operation | Source | Effect |
|---|---|---|
| `POST /api/v1/agents` with `shares_count > 0` | SP-05-FR-007 | Adds `"investor"` flag transactionally |
| `POST /api/v1/agents/{id}/shares` with `action='add'` (first such operation) | SP-05-FR-022, BR-001-6 | Adds `"investor"` flag transactionally |
| Withdrawal of all shares | none | No effect; flag remains |
| Logical deletion of all share log rows | none | No effect; flag remains (and there is no physical delete anyway) |

---

## Entity 3: Share Movement (`AgentSharesLog` row)

**Source table**: `agent_shares_logs` (existing from SP-02, `04_01`)
**Purpose**: Append-only historical record of share `add` and `withdraw` events for an agent.

### Fields

| Field | Type | Required | Notes |
|---|---|---|---|
| `id` | `BIGSERIAL` | Yes | Primary key |
| `client_id` | `BIGINT` | Yes, FK → `clients.id` | The agent; `ON DELETE RESTRICT` |
| `shares_count` | `INT` | Yes | Always positive; `CHECK (shares_count > 0)`; sign determined by `transaction_type` |
| `transaction_type` | `VARCHAR(20)` | Yes | `'add'` or `'withdraw'` (lowercase) |
| `status` | `VARCHAR(20)` | Yes (default `'active'`) | `'active'`, `'modified'`, or `'deleted'` |
| `created_at` | `TIMESTAMPTZ` | Yes | **Immutable** — used for the 30-day lock calculation (BR-005-5) |
| `updated_at` | `TIMESTAMPTZ` | No (null until modified) | Set when status transitions to `modified` or `deleted` |

### Validation Rules

| Field | Rule | Source |
|---|---|---|
| `action` (input) | `required`, `in:add,withdraw` | FR-3.5 |
| `shares_count` (input) | `required`, `integer`, `min:1` | FR-3.5, `04_01` CHECK constraint |

### State Transitions

```
            +------------+
            |   active   |  <-- initial state on creation
            +-----+------+
                  |
   PATCH (within 30d, most recent)    DELETE (within 30d, most recent)
                  |                              |
                  v                              v
            +------------+                +------------+
            |  modified  |                |  deleted   |
            +-----+------+                +-----+------+
                  |                              |
                  +--------------+---------------+
                                 |
                  (no transitions out;
                   excluded from total_shares)
```

### Status Semantics (BR-005-4)

| Status | Counted in `total_shares`? | Visible in `GET /shares-log`? | Mutable? |
|---|---|---|---|
| `active` | Yes | Yes | Only if most recent active and within 30 days |
| `modified` | No | Yes | No (already mutated) |
| `deleted` | No | Yes | No (already deleted) |

### Most-Recent-Active Tie-Breaker (NFR-D-004)

When multiple rows share the same `created_at`, the "most recent active" is the row with the largest `id`. Implemented via:
```sql
SELECT * FROM agent_shares_logs
WHERE client_id = ? AND status = 'active'
ORDER BY created_at DESC, id DESC
LIMIT 1
```

### 30-Day Lock (BR-005-5)

A PATCH/DELETE is permitted only if the target row satisfies BOTH:
1. `created_at >= now() − 30 days` (24-hour duration, inclusive of the boundary instant — Q3)
2. The target row IS the most recent active row (per tie-breaker above)

The check is performed INSIDE the transaction, AFTER `lockForUpdate()` is acquired.

### Negative Balance Prevention (BR-005-8, Q2 clarification)

For `POST /shares` with `action='withdraw'`, the projected balance MUST be ≥ 0:
```
projected = current_total_shares − shares_count
if projected < 0: reject with 422 (shares.insufficient_balance)
if projected == 0: accept (boundary case)
```

This check is performed BEFORE the share log insert; no row is created on rejection.

### Concurrency Safety (NFR-D-006, Q1)

PATCH/DELETE acquire `lockForUpdate()` on the target row. The second writer blocks, then re-evaluates the guards after the first commits. The second writer is rejected with 403 because the row's `status` is no longer `'active'`.

### Scopes (planned)

```php
trait AgentSharesLogScopes
{
    public function scopeActive($q) { return $q->where('status', 'active'); }
    public function scopeForAgent($q, int $agentId) { return $q->where('client_id', $agentId); }
    public function scopeRecentFirst($q) { return $q->orderByDesc('created_at')->orderByDesc('id'); }
}
```

---

## Entity 4: Referred Customer (projection)

**NOT a table.** This is a *projection* computed on demand for the agent detail response (SP-05-FR-018..020).

### Definition

A "referred customer" of an agent is a `Client` who:
- Has the `customer` flag in `client_type_flags`, AND
- Is referenced by at least one `Contract` row where `agent_id = {the agent}` AND `status IN ('active', 'completed')`

### Computation

```sql
SELECT c.id, c.name,
       SUM(i.amount) AS total_installments,
       SUM(CASE WHEN i.status = 'paid' THEN i.amount ELSE 0 END) AS total_paid
FROM clients c
JOIN contracts ct ON ct.customer_id = c.id
LEFT JOIN installments i ON i.contract_id = ct.id
WHERE ct.agent_id = ?
  AND ct.status IN ('active', 'completed')
GROUP BY c.id, c.name
```

### Output Shape

| Field | Type | Source |
|---|---|---|
| `name` | string | `clients.name` |
| `total_installments` | decimal | `SUM(installments.amount)` for that customer's agent-referred contracts |
| `total_paid` | decimal | `SUM(installments.amount WHERE status='paid')` for that customer's agent-referred contracts |

**Not included** (data minimization): `client_id`, `phone`, `reference_number`, `client_type_flags`, `avatar`.

### Edge Cases

| Scenario | Behavior |
|---|---|
| Agent has no contracts | `referred_customers: []` (empty array) |
| Agent's contracts are all `draft` | `referred_customers: []` (only `active`/`completed` count) |
| A customer has multiple contracts with the same agent | Single entry in the array (GROUP BY customer) |
| A customer's only contracts are with other agents | Not in this agent's `referred_customers` |

---

## Entity 5: Agent Summary (computed)

**NOT a table.** Computed on demand for `GET /api/v1/agents/{id}` and `GET /api/v1/agents` (list).

### Fields

| Field | Formula | Source |
|---|---|---|
| `total_shares` | `SUM(CASE WHEN transaction_type='add' THEN +shares_count ELSE -shares_count END) WHERE status='active'` | ALG 5.2, BR-005-4, SP-05-FR-012 |
| `total_installments_via_agent` | `SUM(monthly_installment_amount × months) WHERE agent_id=? AND status IN ('active','completed')` | ALG 5.4, SP-05-FR-015 |
| `total_collected_via_agent` | `SUM(installments.amount) WHERE status='paid'` for agent's active/completed contracts | SP-05-FR-016 |
| `total_remaining_via_agent` | `SUM(installments.amount WHERE status IN ('pending','overdue'))` for agent's active/completed contracts | SP-05-FR-017 |
| `computed_profit` | `SUM(total_after_profit − purchase_amount) × 0.05` for agent's active/completed contracts | ALG 5.1, BR-005-3, SP-05-FR-013 |
| `referred_customers` | (see Entity 4) | SP-05-FR-018..020 |

### Decimal Precision

- All aggregates use `DECIMAL(10,2)` (existing column type)
- `computed_profit` is computed via `bcmath` (`bcmul(sum, '0.05', 2)`) — no float
- Returned values are strings in the response (per Laravel default for `DECIMAL` casts)

### Performance

- 10,000 agents target (AC-030)
- Indexes used: `idx_contracts_agent_status`, `idx_installments_contract_status`, `idx_shares_logs_client_status`
- p95 ≤ 200ms for list and detail (NFR-P-001, NFR-P-003)

---

## Schema Decisions

1. **No new migrations.** All tables are existing from SP-02. SP-05 operates exclusively on `clients`, `agent_shares_logs`, and `contracts` (read-only on `contracts`).
2. **No new columns.** No data-model changes to any existing table.
3. **No DTOs in the spec.** Per Constitution §I, a DTO is permitted only if reused ≥ 3 times. SP-05 has one consumer (`AgentDetailResource`) for the summary. Direct array return from `AgentSummaryService` is sufficient.
4. **The `investor` flag is the only investor state.** No separate table, no enum column, no junction table. This is the BR-001-3 rule.

---

## Migration Impact: NONE

Confirmed: no schema changes are required for SP-05.