# Database Performance Runbook

This runbook covers the repository-controlled database performance baseline and the staging/production observations that still require production-like data.

## Reporting indexes

The performance migration adds composite indexes for the query shapes used repeatedly by the management dashboard and reporting services:

- `financial_account_transactions (branch_id, type, transaction_date)` for collection/disbursement flow series and typed financial reports;
- `accounting_entries (branch_id, category, entry_date)` for income/expense and profit/loss reporting;
- `loans (branch_id, status, completed_at)` for completed-loan reports;
- `loans (branch_id, status, written_off_at)` for written-off portfolio reports;
- `loans (branch_id, status, total_overdue)` for active overdue counts and portfolio monitoring;
- `customers (branch_id, created_at)` for new-customer counts by dashboard period.

Existing indexes already cover the base branch/status and branch/date paths for loans, customers, transactions, accounting entries, management alerts, and audit activity. Do not add overlapping indexes without evidence from real query plans because every additional index increases write and storage cost.

## Automated preflight

After migrations run:

```bash
php artisan database:performance-check
```

For machine-readable output and representative MySQL query plans:

```bash
php artisan database:performance-check --json --explain
```

The command fails when a required reporting index is missing. `--explain` captures representative MySQL `EXPLAIN` output for dashboard/report query shapes but does not fail solely because the optimizer chooses a table scan on a tiny CI dataset.

CI runs this preflight against MySQL after a fresh migration/seed and again in the release-verification path.

## Slow-query logging

Production enables application-side slow-query logging with:

```text
DB_SLOW_QUERY_MS=500
```

A query at or above that threshold is logged as `database.slow_query` with:

- connection name;
- duration in milliseconds;
- SQL template.

Bindings are deliberately excluded so customer names, NINs, phone numbers, references, and other values are not copied into performance logs.

Set `DB_SLOW_QUERY_MS=0` to disable the listener. If the threshold changes, rebuild Laravel configuration cache.

## Staging / production-like observation

Repository CI proves schema/index availability, not production workload performance. Before go-live, use a staging dataset whose loan, payment, transaction, accounting-entry, and audit-log volumes are representative of expected operation.

Run:

```bash
php artisan database:performance-check --json --explain
```

Then exercise at minimum:

1. management dashboard for today, this month, this year, and a custom date range;
2. active loan portfolio and overdue-loan reports;
3. collections, disbursements, profit/loss, and account statement reports;
4. completed-loan and written-off-loan reports;
5. multi-branch dashboard/report views for the largest expected branch scope.

Review `database.slow_query` events and the captured `EXPLAIN` plans. If the database host also exposes the MySQL slow query log, correlate it with the application log before adding or changing indexes.

## Acceptance criteria

The repository-side gate is complete when:

- fresh MySQL migrations succeed;
- `database:performance-check --json --explain` exits successfully;
- automated tests stay green.

The environment-side performance check is complete only after production-like dashboard/report traffic has been observed and any sustained slow queries have been investigated. Record the dataset scale, selected report/period, query duration, plan/index used, and any remediation performed.
