# Performance and optimization

## Database indexes

Indexes are in place to keep common queries fast as data grows:

- **visits**: `(mr_id, visit_date)`, `visit_date`, `import_batch_id`, `assignment_status`
- **visits_raw_staging**: `(import_batch_id, resolved_mr_id)`, `(import_batch_id, match_confidence)`
- **mapping_decisions**: `staging_row_id`
- **mr_area_assignments**: `(mr_id, type)`

Use these columns in filters and joins; avoid ad-hoc filters on unindexed columns where possible.

## Compliance queries

- **List (compliance index)**: A single aggregate query groups visits by `mr_id` and `assignment_status` for the date range. No per-MR N+1.
- **Detail (compliance show)**: One aggregate for counts, then a limited query (up to 100 rows) for that MR when building reason evidence. Only needed columns and relations are selected.

## Caching

- **Compliance scores** (per date range): Cached for 5 minutes. Key is versioned; when new visits are committed (mapping approve/exception), the version is bumped so the next request recomputes.
- **Dashboard**: Stats counts and “recent” lists are cached for 2 minutes. Invalidated on visit import, location import, and when mapping approves or marks an exception.

Cache driver is the application default (`CACHE_STORE`). For production under load, use Redis or Memcached and consider cache tags if you need to invalidate only compliance or only dashboard.

## Compliance “no visits” behaviour

- If there are **no visits in the database**: The compliance page shows an alert and explains to import visit sheets, complete manual mapping, and run `php artisan visits:classify`.
- If there are visits but **none in the selected date range**: The page suggests widening the range and, when available, shows the actual visit date range in the database.
- **Default date range**: When at least one visit exists, the default range is the min/max visit date in the DB so something is shown without changing filters.
