# Data Model: SP-07 — Contract Management

**Date**: 2026-06-18
**Source**: spec.md + research.md
**Purpose**: Document entity schemas, relationships, state machine, and the **complete due-date generation pseudo-code** with month-end handling — addressing the user's precision concern about date edge cases.

---

## 1. Entity Schemas (Authoritative)

> All schemas come from `doc/04_DATABASE/04_01_TABLES.md`. SP-07 introduces **no schema changes** — only writes to existing tables.

### 1.1 Contract

| Column | Type | Nullable | Source | SP-07 Use |
|---|---|---|---|---|
| `id` | BIGSERIAL PK | No | auto | Read/write in all 4 endpoints |
| `customer_id` | BIGINT FK(clients.id) | No | FormRequest input | Written on create |
| `agent_id` | BIGINT FK(clients.id) | Yes | FormRequest input | Written on create (optional) |
| `product_name` | VARCHAR(255) | No | FormRequest input | Written on create |
| `product_description` | TEXT | Yes | FormRequest input | Written on create (optional) |
| `purchase_amount` | DECIMAL(10,2) | No | FormRequest input | Written on create |
| `initial_payment` | DECIMAL(10,2) | No | FormRequest input | Written on create |
| `profit_percentage` | DECIMAL(5,2) | No | FormRequest input | Written on create |
| `total_after_profit` | DECIMAL(10,2) | No | FormRequest input | Written on create |
| `months` | SMALLINT | No | FormRequest input | Written on create |
| `monthly_installment_amount` | DECIMAL(10,2) | No | FormRequest input | Written on create |
| `start_date` | DATE | No | FormRequest input | Written on create |
| `status` | VARCHAR(20) | No | default `'draft'` | Read in all 4 endpoints; transitioned on sign |
| `signed_at` | TIMESTAMPTZ | Yes | — | Set on sign |
| `reference_number` | VARCHAR(20) UNIQUE | Yes (auto) | HasReferenceNumber | Generated on create |
| `created_at` | TIMESTAMPTZ | No | auto | Set on create |
| `updated_at` | TIMESTAMPTZ | No | auto | Set on every write |

**CHECK constraints** (DB-level, enforced regardless of FormRequest):
- `initial_payment >= 0 AND initial_payment <= purchase_amount` (BR-002-1)
- `months >= 1 AND months <= 60` (BR-002-2)

**State machine** (BR-002-3, ADR-024):

```text
                ┌─────────┐
                │  draft  │  ← initial state
                └────┬────┘
                     │
         ┌───────────┼───────────┐
         │                       │
   sign()│                       │delete()
         ▼                       ▼
   ┌─────────┐              [DELETED]
   │ active  │
   └────┬────┘
        │  (SP-08: last installment paid)
        ▼
   ┌───────────┐
   │ completed │
   └───────────┘
```

**Valid transitions:**
- `draft → active` (PATCH `/sign`, FR-026, AC-015)
- `draft → [deleted]` (DELETE, FR-029, AC-018, hard delete with CASCADE)
- `active → completed` (SP-08 only, not in this spec)
- Any other transition is **rejected** with 403 (`ContractImmutableException` or `cannot_sign`/`cannot_delete` keys).

### 1.2 Installment

| Column | Type | Nullable | Source | SP-07 Use |
|---|---|---|---|---|
| `id` | BIGSERIAL PK | No | auto | Read in detail; written in bulk on create |
| `contract_id` | BIGINT FK(contracts.id) | No | auto | Written on create (parent contract's id) |
| `installment_number` | SMALLINT | No | derived | 1 to `months` |
| `due_date` | DATE | No | **derived from `DueDateGenerator`** | The user's precision focus |
| `amount` | DECIMAL(10,2) | No | = `monthly_installment_amount` | All installments equal (BR-003-2) |
| `status` | VARCHAR(20) | No | default `'pending'` | `'pending'` at create; SP-08 flips to `'paid'` |
| `paid_amount` | DECIMAL(10,2) | No | default `0` | 0 at create |
| `paid_at` | TIMESTAMPTZ | Yes | — | NULL at create |
| `created_at` | TIMESTAMPTZ | No | auto | Set on create |
| `updated_at` | TIMESTAMPTZ | No | auto | Set on every write |

**Immutability of `due_date`** (BR-003-5, AC-014): `due_date` is **never** updated by SP-07 after creation. Even if a payment is recorded early, the `due_date` of subsequent installments does not change.

**Status transitions** (this spec is responsible only for the initial `'pending'` state):
- `'pending'` ← creation
- `'pending' → 'overdue'` ← daily cron job (out of scope, exists already)
- `'pending' → 'paid'` ← SP-08 payment registration
- `'overdue' → 'paid'` ← SP-08 payment registration
- `'paid' → 'pending'/'overdue'` ← SP-08 LIFO reversal

### 1.3 Payment

| Column | Type | Nullable | Source | SP-07 Use |
|---|---|---|---|---|
| `id` | BIGSERIAL PK | No | auto | Written (auto-created) |
| `contract_id` | BIGINT FK(contracts.id) | No | auto | Written on create |
| `installment_id` | BIGINT FK(installments.id) | Yes | auto | **NULL** for `company_payout` and `initial_payment` (ADR-037) |
| `amount` | DECIMAL(10,2) | No | input | `purchase_amount` for `company_payout`; `initial_payment` for `initial_payment` |
| `payment_date` | DATE | No | input | `start_date` for both auto-created types |
| `type` | VARCHAR(30) | No | derived | `'company_payout'` or `'initial_payment'` (NOT `'installment_payment'` — that's SP-08) |
| `notes` | TEXT | Yes | — | NULL for auto-created rows |
| `created_at` | TIMESTAMPTZ | No | auto | Set on create |
| `updated_at` | TIMESTAMPTZ | No | auto | Set on create |

### 1.4 PaymentAuditLog

| Column | Type | Nullable | Source | SP-07 Use |
|---|---|---|---|---|
| `id` | BIGSERIAL PK | No | auto | Written (auto-created) |
| `contract_id` | BIGINT FK(contracts.id) | No | auto | Written on create |
| `payment_id` | BIGINT FK(payments.id) | Yes | auto | Set to the payment id of the auto-created row |
| `action_type` | VARCHAR(20) | No | derived | `'create'` for both auto-created types |
| `narrative_text` | TEXT | No | **derived per `05_05_AUDIT_NARRATIVE_GENERATION.md`** | `"تم دفع مبلغ من الشركة بقيمة {amount}"` for `company_payout`; `"تم استلام دفعة مقدمة بقيمة {amount}"` for `initial_payment` |
| `old_values` | JSONB | Yes | — | NULL for create |
| `new_values` | JSONB | Yes | — | NULL for create |
| `created_at` | TIMESTAMPTZ | No | auto | Set on create |

### 1.5 Client (Reused, No Changes)

The `Client` model is touched **only** via two methods:
- `Client::markAsCustomer()` (FR-017, on the contract's `customer_id`)
- `Client::markAsAgent()` (FR-018, on the contract's `agent_id` if non-null)

Both use `save()` (not `saveQuietly()`) per ADR-041.

---

## 2. Relationships (Authoritative)

```text
┌────────────┐                  ┌──────────────┐
│  Contract  │ ──────────────── │ Installment  │  1:N
│            │                  │              │
│ customer_id├──────────┐       │ contract_id  │
│ agent_id   ├────┐     │       └──────┬───────┘
└────┬───────┘    │     │              │
     │            │     │              │ 1:N
     │ N:1        │ N:1 │              ▼
     ▼            ▼     │       ┌──────────────┐
┌────────────┐  ┌────────────┐  │   Payment    │
│  Client    │  │  Client    │  │              │
│ (customer) │  │  (agent)   │  │ installment_id (nullable)
└────────────┘  └────────────┘  └──────┬───────┘
                                       │ 1:N
                                       ▼
                                ┌──────────────┐
                                │PaymentAuditLog│
                                │              │
                                │ payment_id (nullable)
                                │ contract_id   │
                                └──────────────┘
```

**ON DELETE rules** (per `04_04_RELATIONSHIPS_AND_CONSTRAINTS.md`):

| Source | Target | ON DELETE | SP-07 Impact |
|---|---|---|---|
| `contracts.customer_id` | `clients.id` | `RESTRICT` | Customer cannot be deleted; SP-07 never tries |
| `contracts.agent_id` | `clients.id` | `RESTRICT` | Agent cannot be deleted; SP-07 never tries |
| `installments.contract_id` | `contracts.id` | `CASCADE` | Draft delete cascades to installments |
| `payments.contract_id` | `contracts.id` | `CASCADE` | Draft delete cascades to payments |
| `payments.installment_id` | `installments.id` | `SET NULL` | Installment deletion nullifies the link |
| `payment_audit_logs.contract_id` | `contracts.id` | `CASCADE` | Draft delete cascades to audit logs (ADR-029) |
| `payment_audit_logs.payment_id` | `payments.id` | `SET NULL` | Payment deletion nullifies the link (preserves narrative) |

---

## 3. Due-Date Generation — The Complete Algorithm

> **The user's input emphasized precision in date edge cases. This section is the authoritative specification. Every month-end overflow case is enumerated.**

### 3.1 The Invariant

**Per Clarification Q1 (Option B), the first installment's `due_date` equals `start_date + 1 month`. Each subsequent installment's `due_date` equals the previous installment's `due_date + 1 month`. Month-end overflow is handled by PHP's native `\DateTimeImmutable::modify('+1 month')`.**

### 3.2 The Algorithm (Pseudo-Code, Implementation-Ready)

```text
function generate(start_date_string, months) -> array<string>:
    """
    Args:
        start_date_string: 'Y-m-d' format (e.g., '2024-01-31')
        months: integer 1-60 (BR-002-2)
    
    Returns:
        array of N 'Y-m-d' strings, indexed 0..months-1
        dates[0] is the FIRST installment's due_date
        dates[months-1] is the LAST installment's due_date
    
    Raises:
        InvalidArgumentException if start_date_string is malformed
        InvalidArgumentException if months < 1 or months > 60
    """
    
    # Step 1: Validate inputs
    parsed = DateTimeImmutable.createFromFormat('Y-m-d', start_date_string)
    if parsed === false:
        throw InvalidArgumentException("start_date must be 'Y-m-d' format")
    if months < 1 or months > 60:
        throw InvalidArgumentException("months must be 1-60")
    
    # Step 2: Compute the first installment's due_date
    # Per Clarification Q1 (Option B): first = start_date + 1 month
    # PHP's modify('+1 month') handles month-end overflow NATIVELY:
    #   Jan 31 + 1 month = Feb 29 (leap) or Feb 28 (non-leap)
    #   Mar 31 + 1 month = Apr 30
    #   May 31 + 1 month = Jun 30
    #   Aug 31 + 1 month = Sep 30
    #   Oct 31 + 1 month = Nov 30
    #   Dec 31 + 1 month = Jan 31 (next year)
    #   Jan 30 + 1 month = Feb 28/29
    #   Jan 29 + 1 month = Feb 28/29
    dates = []
    current = parsed.modify('+1 month')
    dates.append(current.format('Y-m-d'))
    
    # Step 3: For each subsequent installment, advance by 1 month
    for i from 2 to months:
        current = current.modify('+1 month')
        dates.append(current.format('Y-m-d'))
    
    # Step 4: Return
    return dates
```

### 3.3 PHP Implementation Sketch

```php
namespace App\Domains\Contract\Services;

use DateTimeImmutable;
use InvalidArgumentException;

class DueDateGenerator
{
    public function generate(string $startDate, int $months): array
    {
        // Step 1: Validate inputs
        $parsed = DateTimeImmutable::createFromFormat('Y-m-d', $startDate);
        if ($parsed === false) {
            throw new InvalidArgumentException(
                "start_date must be 'Y-m-d' format, got: {$startDate}"
            );
        }
        if ($months < 1 || $months > 60) {
            throw new InvalidArgumentException(
                "months must be 1-60, got: {$months}"
            );
        }
        
        // Step 2: First installment = start_date + 1 month
        $current = $parsed->modify('+1 month');
        $dates = [$current->format('Y-m-d')];
        
        // Step 3: Subsequent installments
        for ($i = 2; $i <= $months; $i++) {
            $current = $current->modify('+1 month');
            $dates[] = $current->format('Y-m-d');
        }
        
        return $dates;
    }
}
```

### 3.4 Exhaustive Edge Case Test Matrix

**This matrix is the source of truth for the `DueDateGeneratorTest`. Every cell is a test method.**

#### 3.4.1 Leap Year (2024) — Day 31 Starts

| # | start_date | Inst 1 | Inst 2 | Inst 3 | Overflow Notes |
|--:|---|---|---|---|---|
| 1 | 2024-01-31 | 2024-02-29 | 2024-03-29 | 2024-04-29 | Feb overflow (29-day Feb) |
| 2 | 2024-03-31 | 2024-04-30 | 2024-05-30 | 2024-06-30 | Apr overflow (30-day Apr) |
| 3 | 2024-05-31 | 2024-06-30 | 2024-07-30 | 2024-08-30 | Jun overflow |
| 4 | 2024-07-31 | 2024-08-31 | 2024-09-30 | 2024-10-30 | Aug OK, then Sep overflow |
| 5 | 2024-08-31 | 2024-09-30 | 2024-10-30 | 2024-11-30 | Sep overflow |
| 6 | 2024-10-31 | 2024-11-30 | 2024-12-30 | 2025-01-30 | Nov overflow, then year boundary |
| 7 | 2024-12-31 | 2025-01-31 | 2025-02-28 | 2025-03-28 | Jan OK, then Feb overflow (28) |

#### 3.4.2 Leap Year (2024) — Day 30 Starts

| # | start_date | Inst 1 | Inst 2 | Inst 3 | Overflow Notes |
|--:|---|---|---|---|---|
| 8 | 2024-01-30 | 2024-02-29 | 2024-03-29 | 2024-04-29 | Feb overflow (29) |
| 9 | 2024-03-30 | 2024-04-30 | 2024-05-30 | 2024-06-30 | No overflow (all months have ≥30) |
| 10 | 2024-04-30 | 2024-05-30 | 2024-06-30 | 2024-07-30 | No overflow |
| 11 | 2024-05-30 | 2024-06-30 | 2024-07-30 | 2024-08-30 | No overflow |
| 12 | 2024-06-30 | 2024-07-30 | 2024-08-30 | 2024-09-30 | No overflow |
| 13 | 2024-08-30 | 2024-09-30 | 2024-10-30 | 2024-11-30 | No overflow |
| 14 | 2024-09-30 | 2024-10-30 | 2024-11-30 | 2024-12-30 | No overflow |
| 15 | 2024-10-30 | 2024-11-30 | 2024-12-30 | 2025-01-30 | No overflow |
| 16 | 2024-11-30 | 2024-12-30 | 2025-01-30 | 2025-02-28 | No overflow, then Feb (28) at inst 3 |

#### 3.4.3 Leap Year (2024) — Day 29 Starts

| # | start_date | Inst 1 | Inst 2 | Inst 3 | Overflow Notes |
|--:|---|---|---|---|---|
| 17 | 2024-01-29 | 2024-02-29 | 2024-03-29 | 2024-04-29 | Feb (29) — exact match |
| 18 | 2024-02-29 | 2024-03-29 | 2024-04-29 | 2024-05-29 | No overflow |
| 19 | 2024-12-29 | 2025-01-29 | 2025-02-28 | 2025-03-28 | Year boundary, then Feb (28) |

#### 3.4.4 Non-Leap Year (2023) — Day 31 Starts

| # | start_date | Inst 1 | Inst 2 | Inst 3 | Overflow Notes |
|--:|---|---|---|---|---|
| 20 | 2023-01-31 | 2023-02-28 | 2023-03-28 | 2023-04-28 | Feb (28) — non-leap year |
| 21 | 2023-03-31 | 2023-04-30 | 2023-05-30 | 2023-06-30 | Apr overflow |
| 22 | 2023-05-31 | 2023-06-30 | 2023-07-30 | 2023-08-30 | Jun overflow |
| 23 | 2023-08-31 | 2023-09-30 | 2023-10-30 | 2023-11-30 | Sep overflow |
| 24 | 2023-10-31 | 2023-11-30 | 2023-12-30 | 2024-01-30 | Nov overflow, year boundary |
| 25 | 2023-12-31 | 2024-01-31 | 2024-02-29 | 2024-03-29 | Jan OK, then Feb (29) — **leap year boundary** |

#### 3.4.5 Non-Leap Year (2023) — January 30/29 → February 28

| # | start_date | Inst 1 | Inst 2 | Inst 3 | Overflow Notes |
|--:|---|---|---|---|---|
| 26 | 2023-01-30 | 2023-02-28 | 2023-03-28 | 2023-04-28 | Feb (28) — non-leap |
| 27 | 2023-01-29 | 2023-02-28 | 2023-03-28 | 2023-04-28 | Feb (28) — non-leap |
| 28 | 2023-01-28 | 2023-02-28 | 2023-03-28 | 2023-04-28 | No overflow (28 ≤ 28) |

#### 3.4.6 No-Overflow Baselines (Sanity)

| # | start_date | Inst 1 | Inst 2 | Inst 3 | Overflow Notes |
|--:|---|---|---|---|---|
| 29 | 2024-01-15 | 2024-02-15 | 2024-03-15 | 2024-04-15 | No overflow |
| 30 | 2024-06-15 | 2024-07-15 | 2024-08-15 | 2024-09-15 | No overflow |
| 31 | 2024-12-15 | 2025-01-15 | 2025-02-15 | 2025-03-15 | Year boundary, no overflow |

#### 3.4.7 Boundary: `months = 1` and `months = 60`

| # | Input | Expected Output | Notes |
|--:|---|---|---|
| 32 | `generate('2024-01-31', 1)` | `['2024-02-29']` | Single installment, leap-year Feb |
| 33 | `generate('2024-12-15', 60)` | Array of 60 dates starting `2025-01-15`, ending `2029-12-15` | Maximum, year-crossing |

### 3.5 The Test Count

- **Exhaustive matrix above**: 33 distinct test methods
- **`months` parameterization**: PHPUnit `dataProvider` with 60 values × 1 representative start_date = 60 more tests
- **Year-leapness parameterization**: 2 leap years (2024, 2028) × 2 non-leap years (2023, 2025) for sanity
- **Total**: **~100 test methods** in `DueDateGeneratorTest` for complete coverage of the due-date algorithm

### 3.6 Why This Is Sufficient

The algorithm is **deterministic and stateless** — given the same `(start_date, months)`, it always returns the same dates. The test matrix in 3.4 covers:

- ✅ Every "day 31" month in both leap and non-leap years (cells 1-7, 20-25)
- ✅ Every "day 30" month in a leap year (cells 8-16)
- ✅ Day 29/30/31 in January with Feb overflow in non-leap year (cells 20, 26-28)
- ✅ Year-boundary transitions (cells 6, 16, 19, 24, 25, 31)
- ✅ No-overflow baselines (cells 29-31)
- ✅ Edge cases at parameter boundaries (cells 32-33)

A failure in any cell indicates a PHP `modify('+1 month')` behavior change (extremely rare) or an implementation bug. Either way, the failure is immediate and localized.

---

## 4. Atomic Transaction Boundary

```text
DB::transaction(function () {
    // 1. Math validation (delegated to SP-06)
    $mathValidator->validate(...)
        // throws PaymentMismatchException or InvalidInitialPaymentException
        // → rolls back transaction → 422 response
    
    // 2. Create contract (HasReferenceNumber auto-generates CTR-XXXXXXXXXX)
    $contract = Contract::create([...])
        // unique constraint on reference_number → may throw on rare collision
    
    // 3. Bulk create installments (uses DueDateGenerator)
    $contract->installments()->createMany([
        ['installment_number' => 1, 'due_date' => $dates[0], ...],
        ...
    ])
    
    // 4. Create company_payout + audit log (always)
    $payout = Payment::create([type='company_payout', amount=purchase_amount, ...])
    PaymentAuditLog::create([narrative_text="تم دفع مبلغ...", ...])
    
    // 5. Create initial_payment + audit log (conditional)
    if ($initialPayment > 0) {
        $ip = Payment::create([type='initial_payment', amount=initial_payment, ...])
        PaymentAuditLog::create([narrative_text="تم استلام دفعة مقدمة...", ...])
    }
    
    // 6. Flag updates
    $contract->customer->markAsCustomer()
    if ($contract->agent_id) {
        $contract->agent->markAsAgent()
    }
    
    return $contract
})

// POST-COMMIT (outside the transaction)
RefreshCustomerListingJob::dispatch()
```

**Atomicity guarantee**: Any throw at any step rolls back the entire transaction. No partial state. Verified by AC-014.

---

## 5. CASCADE Deletion Boundary

```text
Contract::destroy($id)  // single SQL DELETE
    ↓
ON DELETE CASCADE on installments.contract_id
    ↓ all N installments deleted
ON DELETE CASCADE on payments.contract_id
    ↓ all payments deleted (company_payout, initial_payment, future installment_payments)
ON DELETE CASCADE on payment_audit_logs.contract_id (per ADR-029)
    ↓ all audit logs deleted
```

**No trace remains** (BR-002-6, AC-018). Verified by post-deletion query asserting 0 rows in each of the 4 tables for the deleted contract id.

---

## 6. Eager Loading Pattern (for GET `/contracts/{id}`)

```php
$contract = Contract::with([
    'customer:id,name,phone',  // projection — saves bandwidth
    'agent:id,name',
    'installments',           // no projection (all 7 columns returned)
    'auditLogs',              // for narrative_timeline
])
    ->findOrFail($id);

// In-PHP sort of audit logs (acceptable for ~120 entries per contract)
$timeline = $contract->auditLogs
    ->sortBy('created_at')
    ->pluck('narrative_text')
    ->values();
```

**Index utilization**:
- `installments` → uses `idx_installments_contract_id`
- `auditLogs` → uses `idx_audit_logs_contract_created` (composite `(contract_id, created_at)`)

**Performance target**: < 200ms (NFR-005).

---

## 7. Validation Rules Summary

| Field | Rule | Source |
|---|---|---|
| `customer_id` | required, integer, exists:clients,id | FR-001 |
| `agent_id` | nullable, integer, exists:clients,id, different:customer_id | FR-001 + Decision 13 |
| `product_name` | required, string, max:255 | FR-001 |
| `product_description` | nullable, string | FR-001 |
| `purchase_amount` | required, numeric, min:0.01 | FR-001 |
| `initial_payment` | required, numeric, min:0 (≤ purchase_amount enforced by validator) | FR-001, BR-002-1 |
| `profit_percentage` | required, numeric, min:0 | FR-001 |
| `total_after_profit` | required, numeric | FR-001 |
| `months` | required, integer, min:1, max:60 | BR-002-2 |
| `monthly_installment_amount` | required, numeric | FR-001 |
| `start_date` | required, date (any past/present/future per Q2) | FR-001, Clarification Q2 |

**No FormRequest-level duplicate detection** (per Clarification Q3 = Option A).

---

## 8. Indexes Used (Authoritative)

| Index | Used by | Query |
|---|---|---|
| `idx_contracts_reference_number` (UNIQUE) | HasReferenceNumber trait | `WHERE reference_number LIKE 'CTR-%' ORDER BY reference_number DESC` |
| `idx_installments_contract_id` | GET detail | `WHERE contract_id = ?` |
| `idx_installments_payment_lookup` (composite) | (Future SP-08; this spec doesn't query by status) | — |
| `idx_payments_contract_id` | GET detail (no payment fetch needed in this spec) | — |
| `idx_audit_logs_contract_created` (composite) | GET detail narrative_timeline | `WHERE contract_id = ? ORDER BY created_at` |

All indexes already exist in the database (`04_03_INDEXES.md`). SP-07 adds no new indexes.

---

## 9. Foreign Key Cascade Verification (for AC-018 / AC-020)

After `DELETE /contracts/{id}` succeeds:

```sql
-- All 4 queries should return 0 rows
SELECT COUNT(*) FROM contracts WHERE id = :deleted_id;             -- → 0
SELECT COUNT(*) FROM installments WHERE contract_id = :deleted_id;  -- → 0
SELECT COUNT(*) FROM payments WHERE contract_id = :deleted_id;     -- → 0
SELECT COUNT(*) FROM payment_audit_logs WHERE contract_id = :deleted_id;  -- → 0
```

This is the integration test assertion. The cascade is handled by DB FK constraints; SP-07 just issues one `DELETE FROM contracts WHERE id = ?` statement.