DB: review and add indexes for the audit_log analytics + admin queries

[Depo-dev]

Summary

Ensure audit_log has the indexes needed for the admin analytics + cleanup queries, with EXPLAIN evidence.

Background & current behaviour

services/api/handlers/admin.go runs COUNT/range queries against audit_log (per key, per time range) and services/api/main.go deletes old rows. Migration 0006 created idx_audit_log_key_ts (api_key_id, ts DESC) and idx_audit_log_ts (ts DESC); confirm these actually cover the live queries and add any missing composite/partial index.

Why this matters for MVP

Audit analytics run over a high-write table. Missing indexes turn dashboards and cleanup into sequential scans that compete with ingest.

Proposed implementation

1. Collect the exact queries admin analytics + cleanup run.
2. Run EXPLAIN ANALYZE for each; identify seq scans / poor plans.
3. Add missing indexes (e.g. (api_key_id, ts, status_code) for error-rate, or partial indexes) reflected in both a migration and schema.sql.
4. Attach before/after plans to the PR.

Acceptance criteria

• EXPLAIN ANALYZE for each admin/audit query attached.
• Indexes added where a hot query seq-scans; migration + schema.sql updated.
• Cleanup query is index-supported.

Affected files / components

• database/migrations/
• database/schema.sql
• services/api/handlers/admin.go (queries, if adjusted to hit indexes)

Testing & verification

Seed representative audit rows; assert index usage in EXPLAIN and acceptable latency.

Related / dependencies

Feeds admin analytics (#231) and usage metering (#300).

Out of scope

Moving audit to a separate analytics store.

---

Target branch

All work for this issue MUST target the dev branch. Cut your feature branch from dev and open the pull request against dev, never against main. Any PR opened against main for this issue will be closed and asked to re-target dev.