# Database scaling readiness

**Context:** MySQL/MariaDB-style OLTP primary; reporting and analytics should not starve transactional paths as tenants grow.

---

## 1. Hot tables (expected growth)

| Table / area | Growth driver | Risk |
|--------------|---------------|------|
| `attendance_records` | Daily rows × employees | Large range scans without matching indexes |
| `leave_requests` | Workflow + history | Status filters per tenant |
| `integration_webhook_deliveries` | Every outbound attempt | Retry + dead-letter accumulation |
| `api_audit_logs` | Per-request API traffic | High insert rate |
| `exports` | User + scheduled exports | Metadata + status churn |
| `payroll_employee_snapshots` | Per period × employees | Bulk insert/delete on regeneration |
| `platform_audit_events` | Productization / ops | Append-mostly |
| `activity_logs` (if used) | User actions | Retention-managed |

---

## 2. Index coverage (principles)

Existing migrations include composite indexes for common SaaS patterns (e.g. `tenant_id` + status, `tenant_id` + date ranges). **Ongoing discipline:**

1. Every **tenant-scoped** list or report query should filter on `tenant_id` first (matches composite leading column).
2. Avoid unindexed `OR` across columns on large tables; split queries or use union patterns when needed.
3. Revisit indexes when adding **new filters** (status, date range) to high-volume tables.

---

## 3. Large-table growth risks

- **Webhook deliveries** and **API audit** tables are append-heavy; growth is proportional to integration adoption, not seat count alone.
- **Attendance** grows linearly with headcount × days worked.
- **Payroll snapshots** grow per finalized period; regeneration deletes draft-era rows but finalized history remains.

---

## 4. Archival candidates

| Data | Strategy |
|------|----------|
| Completed/failed exports | `RetentionPolicyService` (files + rows) — see `config/retention.php` |
| Old API audit rows | Add retention job + legal hold policy (not yet standardized) |
| Old webhook deliveries | Archive or partition after N months; keep dead-letters queryable for disputes |
| Activity logs | Already trimmed by retention job where configured |

---

## 5. Future partitioning candidates

**When:** single-table row counts exceed operational comfort (typically 50–100M+ rows depending on instance class) *or* backup/restore windows become unacceptable.

| Table | Partition key candidate |
|-------|-------------------------|
| `attendance_records` | `tenant_id` + `attendance_date` (or hash tenant + month) |
| `api_audit_logs` | Time-based (`created_at` month) |
| `integration_webhook_deliveries` | Time-based |

**Note:** MySQL partitioning tradeoffs (partition pruning vs. secondary indexes) require a dedicated migration design per version.

---

## 6. Reporting isolation opportunities

- **Read replicas:** point heavy internal reporting (business metrics, long exports) at a replica when RPO/RTO allows slight lag.
- **Snapshot tables:** payroll finalized snapshots are already an analytics-friendly boundary (see payroll immutability ADR).
- **Pre-aggregates:** optional nightly rollups per tenant for dashboard KPIs if `OperationalMetricsRepository` becomes hot.

---

## References

- `database/migrations/*` (per-table indexes)
- `app/Modules/Admin/Operations/Services/RetentionPolicyService.php`
- [ADR 0002: Immutable payroll snapshots](../architecture/adr/0002-immutable-payroll-snapshots.md)