# Data Model: SP-02 Database Foundation

## Entity Relationship Diagram

```
┌─────────────┐       ┌─────────────┐       ┌──────────────────┐
│   users     │       │   admins    │       │notification_templates│
├─────────────┤       ├─────────────┤       ├──────────────────┤
│ BIGSERIAL PK│       │ BIGSERIAL PK│       │ BIGSERIAL PK    │
│ name        │       │ name        │       │ type (UNIQUE)   │
│ phone (U)   │       │ email (U)   │       │ body_template   │
│ description │       │ password    │       │ created_at      │
│ avatar      │       │ created_at  │       │ updated_at      │
│ created_at  │       │ updated_at  │       └──────────────────┘
│ updated_at  │       └─────────────┘
└──────┬──────┘              │
       │                     │ Spatie 'admin' guard
       │ FK RESTRICT         │
       ├─────────────────────┼──────────────────┐
       │                     │                  │
       ▼                     ▼                  ▼
┌─────────────────┐   ┌─────────────────┐   ┌─────────────────────┐
│   contracts     │   │  installments    │   │      payments       │
├─────────────────┤   ├─────────────────┤   ├─────────────────────┤
│ BIGSERIAL PK   │   │ BIGSERIAL PK    │   │ BIGSERIAL PK        │
│ customer_id (FK)│◄──│ contract_id (FK)│   │ contract_id (FK)    │
│ agent_id (FK)  │   │ installment_num │   │ amount              │
│ product_name   │   │ due_date        │   │ payment_date        │
│ product_desc   │   │ amount         │   │ type                │
│ purchase_amount │   │ status         │   │ notes               │
│ initial_payment │   │ paid_amount    │   │ created_at          │
│ profit_pct     │   │ paid_at        │   │ updated_at          │
│ total_after_prf│   │ created_at     │   └──────────┬──────────┘
│ months         │   │ updated_at     │              │
│ monthly_inst   │   └─────────────────┘              │ FK CASCADE
│ start_date     │                                    │
│ is_signed      │                                    │
│ signed_at      │                                    │
│ created_at     │                                    │
│ updated_at     │                                    │
└───────┬────────┘                                    │
        │ FK RESTRICT                                │
        │ (preserve audit logs)                      │
        ▼                                            │
┌─────────────────────┐                              │
│ payment_audit_logs  │                              │
├─────────────────────┤                              │
│ BIGSERIAL PK        │◄─────────────┐               │
│ contract_id (FK)    │  (SET NULL   │               │
│ payment_id (FK)     │   if deleted)│               │
│ action_type         │              │               │
│ narrative_text      │              │               │
│ old_values (JSONB)  │              │               │
│ new_values (JSONB)  │              │               │
│ created_at          │              │               │
└─────────────────────┘              │               │
                                     │ FK SET NULL   │
                                     └───────────────┘

┌─────────────────────┐
│ agent_shares_logs   │
├─────────────────────┤
│ BIGSERIAL PK        │
│ user_id (FK)        │◄──── users
│ shares_count (INT)  │
│ transaction_type    │
│ created_at          │
└─────────────────────┘
```

## Table Definitions

### 1. users (Unified Human Entity)

| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGSERIAL | PRIMARY KEY |
| name | VARCHAR(150) | NOT NULL |
| phone | VARCHAR(20) | NOT NULL, UNIQUE |
| description | TEXT | NULLABLE |
| avatar | VARCHAR(255) | NULLABLE |
| created_at | TIMESTAMPTZ | NOT NULL |
| updated_at | TIMESTAMPTZ | NOT NULL |

**Indexes:**
- `idx_users_phone` ON (phone)
- `idx_users_name` ON (name)

**Notes:** No type/role column - type inferred from relationships. Hard delete prohibited at Model level + ON DELETE RESTRICT as double protection.

---

### 2. admins (System Administrators)

| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGSERIAL | PRIMARY KEY |
| name | VARCHAR(100) | NOT NULL |
| email | VARCHAR(150) | NOT NULL, UNIQUE |
| password | VARCHAR(255) | NOT NULL |
| created_at | TIMESTAMPTZ | NOT NULL |
| updated_at | TIMESTAMPTZ | NOT NULL |

**Integration:** Spatie Laravel Permission with `guard_name = 'admin'`

---

### 3. contracts (Installment Agreements)

| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGSERIAL | PRIMARY KEY |
| customer_id | BIGINT | NOT NULL, FK(users.id) ON DELETE RESTRICT |
| agent_id | BIGINT | NULLABLE, FK(users.id) ON DELETE RESTRICT |
| product_name | VARCHAR(255) | NOT NULL |
| product_description | TEXT | NULLABLE |
| purchase_amount | DECIMAL(10,2) | NOT NULL |
| initial_payment | DECIMAL(10,2) | NOT NULL, CHECK (initial_payment < purchase_amount) |
| profit_percentage | DECIMAL(5,2) | NOT NULL |
| total_after_profit | DECIMAL(10,2) | NOT NULL |
| months | SMALLINT | NOT NULL, CHECK (months >= 1 AND months <= 60) |
| monthly_installment_amount | DECIMAL(10,2) | NOT NULL |
| start_date | DATE | NOT NULL |
| is_signed | BOOLEAN | NOT NULL, DEFAULT FALSE |
| signed_at | TIMESTAMPTZ | NULLABLE |
| created_at | TIMESTAMPTZ | NOT NULL |
| updated_at | TIMESTAMPTZ | NOT NULL |

**Indexes:**
- `idx_contracts_customer_id` ON (customer_id)
- `idx_contracts_agent_id` ON (agent_id)
- `idx_contracts_is_signed` ON (is_signed)

---

### 4. installments (State Table)

| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGSERIAL | PRIMARY KEY |
| contract_id | BIGINT | NOT NULL, FK(contracts.id) ON DELETE CASCADE |
| installment_number | SMALLINT | NOT NULL |
| due_date | DATE | NOT NULL |
| amount | DECIMAL(10,2) | NOT NULL |
| status | VARCHAR(20) | NOT NULL, DEFAULT 'pending' |
| paid_amount | DECIMAL(10,2) | NOT NULL, DEFAULT 0 |
| paid_at | TIMESTAMPTZ | NULLABLE |
| created_at | TIMESTAMPTZ | NOT NULL |
| updated_at | TIMESTAMPTZ | NOT NULL |

**Indexes:**
- `idx_installments_contract_due_status` ON (contract_id, due_date ASC, status)

**Notes:** Pre-generated on contract creation. No direct FK to payments table - linkage is calculated in PaymentService.

---

### 5. payments (Financial Ledger)

| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGSERIAL | PRIMARY KEY |
| contract_id | BIGINT | NOT NULL, FK(contracts.id) ON DELETE CASCADE |
| amount | DECIMAL(10,2) | NOT NULL |
| payment_date | DATE | NOT NULL |
| type | VARCHAR(30) | NOT NULL |
| notes | TEXT | NULLABLE |
| created_at | TIMESTAMPTZ | NOT NULL |
| updated_at | TIMESTAMPTZ | NOT NULL |

**Indexes:**
- `idx_payments_contract_date` ON (contract_id, payment_date)
- `idx_payments_payment_date` ON (payment_date)

**Types:** `company_payout`, `initial_payment`, `installment_payment`

---

### 6. payment_audit_logs (Append-only Narrative Log)

| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGSERIAL | PRIMARY KEY |
| contract_id | BIGINT | NOT NULL, FK(contracts.id) ON DELETE RESTRICT |
| payment_id | BIGINT | NULLABLE, FK(payments.id) ON DELETE SET NULL |
| action_type | VARCHAR(20) | NOT NULL |
| narrative_text | TEXT | NOT NULL |
| old_values | JSONB | NULLABLE |
| new_values | JSONB | NULLABLE |
| created_at | TIMESTAMPTZ | NOT NULL |

**Indexes:**
- `idx_audit_logs_contract_id` ON (contract_id)
- `idx_audit_logs_created_at` ON (created_at DESC)

**Notes:** Append-only - no updated_at column. action_type: `create`, `update`, `delete`.

---

### 7. agent_shares_logs (Append-only Transaction Log)

| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGSERIAL | PRIMARY KEY |
| user_id | BIGINT | NOT NULL, FK(users.id) ON DELETE RESTRICT |
| shares_count | INT | NOT NULL, CHECK (shares_count > 0) |
| transaction_type | VARCHAR(20) | NOT NULL |
| created_at | TIMESTAMPTZ | NOT NULL |

**Indexes:**
- `idx_shares_logs_user_id` ON (user_id)

**Notes:** Append-only - no updated_at column. transaction_type: `add`, `withdraw`. Last entry modifiable within 30 days.

---

### 8. notification_templates (Standalone)

| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGSERIAL | PRIMARY KEY |
| type | VARCHAR(50) | NOT NULL, UNIQUE |
| body_template | TEXT | NOT NULL |
| created_at | TIMESTAMPTZ | NOT NULL |
| updated_at | TIMESTAMPTZ | NOT NULL |

**Types:** `overdue`, `due_soon_1day`, `due_later`, `paid_receipt`

---

## Spatie Permission Tables

Standard Spatie tables created by migration:
- `roles`
- `permissions`
- `role_has_permissions`
- `model_has_roles`
- `model_has_permissions`

**Configuration:**
- Guard: `admin` for `admins` table
- Provider: `admins` model

---

## Migration Execution Order

1. `create_users_table`
2. `create_admins_table` (already exists, needs modification)
3. `create_contracts_table`
4. `create_installments_table`
5. `create_payments_table`
6. `create_payment_audit_logs_table`
7. `create_agent_shares_logs_table`
8. `create_notification_templates_table`
9. (Spatie tables via package)

**Note:** Laravel will handle FK constraint order automatically within transactions.