# SPECIFICATIONS/03_ARCHITECTURE/03_PERFORMANCE_STRATEGY.md

**PURPOSE:** Defines the performance targets and the high-level strategies used to achieve them. This file answers "what performance level is required and what conceptual approach is used?" — it contains zero implementation code.

> **Materialized View SQL definitions:** `../04_DATABASE/02_MATERIALIZED_VIEWS.md`
> **Index definitions supporting these strategies:** `../04_DATABASE/03_INDEXES.md`
> **Performance requirements (The strict targets):** `../02_REQUIREMENTS/02_NON_FUNCTIONAL.md` → NFR-001

---

## Performance Targets

| Endpoint Category | Target | Strategy |
|:---|:---|:---|
| Customer List | < 50ms | Pre-computed Materialized View |
| Customer Detail | < 200ms | Live query with indexes |
| Payment Registration | < 100ms | Index-backed direct lookup |
| Payment Deletion | < 100ms | Index-backed LIFO check |
| Analytics KPIs | < 200ms | Daily snapshot tables + application cache |
| Analytics Charts | < 500ms | Live queries (no pre-computation) |
| Agent Detail | < 200ms | Computed fields with indexes |
| All Read APIs | < 500ms | Baseline requirement |

---

## Why Materialized Views?

Complex aggregations (customer sorting by nearest due date) cannot meet sub-500ms requirements with live queries at 100,000+ contracts. Materialized Views pre-compute these aggregations during off-peak hours, turning complex JOIN+GROUP BY queries into simple SELECTs.

> **Note:** Materialized Views are used only for customer listing. Analytics uses daily snapshot tables — see Analytics Strategy below.

---

## Customer Listing Strategy

**Problem:** Sorting customers by nearest due installment requires joining clients → contracts → installments, filtering for unpaid status, and finding the minimum due date per customer. This is too slow at scale.

**Solution:** Pre-compute all customer list data in `customer_listing_mv`, refreshed frequently.

| Aspect | Configuration |
|:---|:---|
| View | `customer_listing_mv` |
| Refresh frequency | Every 5 minutes |
| Refresh method | `REFRESH MATERIALIZED VIEW CONCURRENTLY` (no read blocking) |
| Data freshness | Up to 5 minutes stale (acceptable for list pages) |
| Detail pages | Use live queries (not the MV) |

**Sorting Behavior:**
- Customers without active installments (`next_due_date = NULL`) always appear last, regardless of sort direction
- When primary sort values are equal, a secondary sort on `client_id` (same direction) prevents pagination drift

**Filtering:** Status flags (`has_overdue`, `has_due_this_month`, `paid_this_month`) are pre-computed as boolean columns in the MV, enabling simple WHERE clauses.

**Time Filtering:** `first_contract_date` is pre-computed in the MV — not calculated at query time.

---

## Analytics Strategy

**Problem:** Analytics KPIs require aggregating contract signing counts, investment balances, and collection metrics over date ranges. Charts require live installment status distribution and overdue aging analysis that must reflect real-time data.

**Solution:** Two separate strategies based on data freshness requirements.

### KPIs: Daily Snapshot Tables + Application Cache

KPIs measure trends over time windows and can tolerate up to 24 hours of data staleness. They use pre-computed daily snapshot tables.

| Table | Purpose | Refresh |
|:---|:---|:---|
| `daily_contract_metrics` | Daily signed contract counts | Daily at 01:00 AM via `RefreshDailyMetricsJob` |
| `daily_investment_metrics` | Daily active shares balance | Daily at 01:00 AM via `RefreshDailyMetricsJob` |
| `daily_collection_metrics` | Daily collection and overdue metrics | Daily at 01:00 AM via `RefreshDailyMetricsJob` |

**Data freshness:** Up to 24 hours stale (acceptable for management KPIs).

**Application Cache Layer:**

| Aspect | Configuration |
|:---|:---|
| Cache key | `analytics:kpis:{from_date}:{to_date}:{comp_from}:{comp_to}` |
| Duration | Configured via `config('analytics.cache.kpis_ttl')` |
| Invalidation | TTL-based (automatic expiry) |

### Charts: Live Queries

Charts must reflect real-time data (no staleness). They query actual database tables directly.

| Chart Component | Data Source | Why Live |
|:---|:---|:---|
| Installments status distribution | `installments` + `payments` + `contracts` (live) | Must reflect current pending/collected/overdue/scheduled state exactly |
| Overdue aging | `installments` + `contracts` (live) | Must reflect current overdue days calculation exactly |
| Collection trend | `daily_collection_metrics` (snapshot) | Trend data can tolerate daily granularity from snapshots |

**Why no cache for charts?** Charts are explicitly requested for real-time analysis. Caching would defeat their purpose.

---

## Pagination Strategy

**Problem:** Offset-based pagination degrades at scale — `OFFSET 100000` requires scanning 100,000 rows before returning results.

**Solution:** Cursor-based pagination, which starts directly at the cursor position regardless of dataset size.

**Constraints:**
- Default page size: 20 records
- Maximum page size: 100 records
- No "jump to page N" functionality — clients must follow cursor chains
- Response includes `current_cursor`, `has_more`, `per_page`

---

## Scheduled Refresh Commands

| Command | Frequency | What it Refreshes |
|:---|:---|:---|
| `analytics:refresh-customer-listing` | Every 5 minutes | `customer_listing_mv` |
| `RefreshDailyMetricsJob` | Daily at 01:00 AM | `daily_contract_metrics`, `daily_investment_metrics`, `daily_collection_metrics` |
| `analytics:backfill` | Manual (development tool) | Rebuilds all daily snapshot tables for a specified number of days |

Both scheduled commands must use `withoutOverlapping()` and `onOneServer()` to prevent concurrent execution.