# SPECIFICATIONS/04_DATABASE/03_INDEXES.md

**PURPOSE:** Defines every index in the system — its name, columns, and which queries it supports. This file answers "what indexes exist and why?" — it contains zero application-level query logic.

> **Performance strategy that motivates these indexes:** `../03_ARCHITECTURE/03_PERFORMANCE_STRATEGY.md`
> **Algorithms that use these indexes:** `../05_ALGORITHMS/03_PAYMENT_ENGINE.md`

---

## clients

| Index | Columns | Purpose |
|:---|:---|:---|
| `idx_clients_phone` | `phone` | Exact match and search lookups |
| `idx_clients_name` | `name` | ILIKE search |
| `idx_clients_type_flags` | `client_type_flags` (GIN) | Role filtering: `'customer' = ANY(client_type_flags)` |

**Usage examples:**
```sql
-- Role filtering
SELECT * FROM clients WHERE client_type_flags @> '["customer"]'::jsonb;

-- Multiple roles
SELECT * FROM clients WHERE client_type_flags @> '["agent", "investor"]'::jsonb;

-- Exclude a role
SELECT * FROM clients WHERE NOT client_type_flags @> '["investor"]'::jsonb AND id = ?;
```

---

## admins

| Index | Columns | Purpose |
|:---|:---|:---|
| `idx_admins_email` | `email` | Login lookup |

---

## contracts

| Index | Columns | Purpose |
|:---|:---|:---|
| `idx_contracts_customer_id` | `customer_id` | Customer's contracts queries |
| `idx_contracts_customer_status` | `customer_id, status` | Customer financial queries (active/completed only) |
| `idx_contracts_agent_id` | `agent_id` | Agent's contracts queries |
| `idx_contracts_agent_status` | `agent_id, status` | Agent financial summary |
| `idx_contracts_status` | `status` | Dashboard counts |
| `idx_contracts_reference_number` | `reference_number` | Document search |

---

## installments

| Index | Columns | Purpose |
|:---|:---|:---|
| `idx_installments_contract_id` | `contract_id` | JOIN operations |
| `idx_installments_due_date_status` | `due_date, status` | Overdue detection, analytics queries |
| `idx_installments_payment_lookup` | `contract_id, status, installment_number` | **Critical:** Payment engine — find next due installment |
| `idx_installments_status` | `status` | Status counts and aggregations |
| `idx_installments_due_date` | `due_date` | Date-range queries |

**Critical index usage (payment registration):**
```sql
-- Find next due installment — O(1) via composite index
SELECT * FROM installments 
WHERE contract_id = ? 
  AND status IN ('pending', 'overdue') 
ORDER BY installment_number 
LIMIT 1;

-- Check for remaining installments (completion check)
SELECT EXISTS(
    SELECT 1 FROM installments 
    WHERE contract_id = ? 
      AND status IN ('pending', 'overdue')
);
```

---

## payments

| Index | Columns | Purpose |
|:---|:---|:---|
| `idx_payments_contract_date` | `contract_id, payment_date` | Analytics queries |
| `idx_payments_date_type` | `payment_date, type` | Analytics queries |
| `idx_payments_contract_created` | `contract_id, created_at DESC` | **Critical:** LIFO verification — find latest payment |
| `idx_payments_type` | `type` | Type-based aggregations |
| `idx_payments_contract_id` | `contract_id` | CASCADE operations |

**Critical index usage (LIFO check):**
```sql
-- Find most recent payment — O(1) via DESC index
SELECT * FROM payments 
WHERE contract_id = ? 
ORDER BY created_at DESC 
LIMIT 1;
```

---

## payment_audit_logs

| Index | Columns | Purpose |
|:---|:---|:---|
| `idx_audit_logs_contract_id` | `contract_id` | Timeline queries |
| `idx_audit_logs_created_at` | `created_at DESC` | Latest entries |
| `idx_audit_logs_contract_created` | `contract_id, created_at` | Optimized timeline fetch |

---

## client_shares_logs

| Index | Columns | Purpose |
|:---|:---|:---|
| `idx_client_shares_logs_client_id` | `client_id` | Fetch investor's shares (via FK constraint) |

---

## notification_templates

No additional indexes required — the `UNIQUE` constraint on `type` provides lookup coverage.

---

## daily_contract_metrics

| Index | Type | Columns | Purpose |
|:---|:---|:---|:---|
| `idx_daily_contract_metrics_date` | UNIQUE | `snapshot_date` | Primary lookup + upsert conflict detection |

---

## daily_investment_metrics

| Index | Type | Columns | Purpose |
|:---|:---|:---|:---|
| `idx_daily_investment_metrics_date` | UNIQUE | `snapshot_date` | Primary lookup + upsert conflict detection |

---

## daily_collection_metrics

| Index | Type | Columns | Purpose |
|:---|:---|:---|:---|
| `idx_daily_collection_metrics_date` | UNIQUE | `snapshot_date` | Primary lookup + upsert conflict detection |
| `idx_daily_collection_metrics_snapshot_date` | Regular | `snapshot_date` | Date range queries for charts |

---

## customer_listing_mv

| Index | Type | Columns | Purpose |
|:---|:---|:---|:---|
| `idx_customer_listing_mv_client_id` | UNIQUE | `client_id` | Required for CONCURRENTLY refresh |
| `idx_customer_listing_mv_next_due` | Regular | `next_due_date ASC NULLS LAST` | **Critical:** Default sort by nearest due |
| `idx_customer_listing_mv_first_contract` | Regular | `first_contract_date` | Time range filtering |
| `idx_customer_listing_mv_customer_created` | Regular | `customer_created_at` | Alternate sort by creation date |
| `idx_customer_listing_mv_has_overdue` | Partial | `has_overdue` WHERE `has_overdue = true` | Status filter |
| `idx_customer_listing_mv_has_due_this_month` | Partial | `has_due_this_month` WHERE `has_due_this_month = true` | Status filter |
| `idx_customer_listing_mv_paid_this_month` | Partial | `paid_this_month` WHERE `paid_this_month = true` | Status filter |