# Tenant Analytics Architecture (10M+)

## Goals

- Per-tenant isolation for schema, cache, and queues
- Fast route response for `sphf-ageing` and `sphf-stage-bottlenecks`
- Scalable reads via pre-aggregated summaries + Redis
- Write-heavy imports handled asynchronously

## Schema Design

### beneficiaries

- Keep one master row per beneficiary/IP pair: `UNIQUE (da_uuid, ip_name)`
- Use typed counters and amounts:
  - `adult_*`, `child_*`, `disable_*` -> `INTEGER`
  - `monthly_income` -> `NUMERIC(14,2)`
- Preserve text fields for CNIC/phone to avoid leading-zero loss

### financials

- Keep typed milestone snapshot fields:
  - `*_date` -> `DATE`
  - `*_withdrawl`, `*_approved_*` -> `BOOLEAN`
- Key with `UNIQUE (uuid, ip_name)`

### beneficiary_payment_events (normalized)

- One row per installment event:
  - `installment_no` (`1..4`), `stage_name`, `released_at`, `withdrawn_at`
- Enables stage-wise, timeline, and KPI expansion without adding columns

### banks

- Allow multiple bank accounts per beneficiary
- Unique account identity by `(uuid, ip_name, bank_account_number, iban, bank_name, bank_account_title)`
- `is_primary` supports preferred account logic

### dashboard_summary

- Precomputed route-level metrics for dashboard reads
- Uniqueness:
  - `(route_name, ageing_mode, filter_hash, metric_group, metric_key)`

## Indexing

- Hot filter indexes on `beneficiaries` and `sphf_normalized_kpi`
- Stage/alarm/pair indexes for bottleneck and ageing charts
- Optional `pg_trgm` GIN on lower(name) for fuzzy name search

## Redis Key Strategy

### Versioned dashboard cache

- `sphf:tenant:{tenantId}:dashboard:active_version`
- `sphf:tenant:{tenantId}:dashboard:{route}:{mode}:v{version}:{md5(filters)}`

### Beneficiary filter cache

- `sphf:tenant:{tenantId}:beneficiary:filters:v{version}:{md5(filters)}`

## Queue Flow

After import:

1. `ProcessExcelImportJob`
2. `ComputeSphfKpiJob`
3. `RebuildDashboardSummaryJob`
4. `RefreshTenantDashboardCacheJob`

This avoids synchronous dashboard recomputation and keeps request latency stable.

## Meilisearch Plan

Use Laravel Scout + Meilisearch for global text search:

- Index documents from `beneficiaries` + selected `sphf_normalized_kpi` fields
- Keep DB for exact filters and aggregations
- Use Meilisearch for full-text + typo-tolerant lookups