Add hot-path indexes for sequence matching, latest-bbox lookups and shared frame objects

[MateoLostanlen]

Summary

The detection hot path runs three query shapes with no index backing them. The two tables involved keep growing (~2.6M rows in detections, ~56k in sequences today), and the dominant query costs ~130 ms per call at that scale. Adding three indexes brings the whole DB side of POST /detections to ~1 ms.

These indexes were originally part of #624 and were pulled out (per review feedback) so the schema change can be reviewed, deployed and reverted on its own, and built with CREATE INDEX CONCURRENTLY.

Queries and measurements

Benchmarked on PostgreSQL (docker, NVMe) with production-scale synthetic data: 2.6M detections, 56k sequences. EXPLAIN (ANALYZE, BUFFERS), warm cache. Production hardware is likely 2-5x slower on the unindexed side.

Query	Call site	Without index	With index
Recently-seen sequences of a pose (camera_id, pose_id, last_seen_at >)	spatial matching + continuity pass, on every POST /detections	8 ms	0.07 ms
Latest real bbox of a sequence (sequence_id, created_at DESC, limit 1)	spatial matching (once per candidate sequence, every detection) and notifications	128 ms	0.08 ms
Sibling rows by bucket_key	shared-frame check in DELETE /detections/{id}	48 ms	0.10 ms

The second query is the standout: it already runs today on main, potentially several times per detection request (once per candidate sequence), and is very likely the dominant DB cost of POST /detections in production.

Proposed indexes

CREATE INDEX CONCURRENTLY ix_sequences_camera_pose_last_seen ON sequences (camera_id, pose_id, last_seen_at);
CREATE INDEX CONCURRENTLY ix_detections_sequence_id_created_at ON detections (sequence_id, created_at);
CREATE INDEX CONCURRENTLY ix_detections_bucket_key ON detections (bucket_key);

Implementation notes:

• Use CONCURRENTLY so index builds do not block camera writes on detections (a plain CREATE INDEX takes a write-blocking lock for the duration of the build). In Alembic this requires running outside a transaction (op.get_context().autocommit_block()).
• Declare the same indexes in models.py (__table_args__) so create_all-based test databases match production.
• A ready-made migration existed in feat(detections): accept empty bboxes and keep sequence frames continuous #624 before being reverted (2026_07_22_1000-e8f3a6c9d1b7_add_hot_path_indexes.py, non-concurrent version); see the PR history.

Dependencies

• None to function, but best merged after feat(detections): accept empty bboxes and keep sequence frames continuous #624 since the continuity pass doubles the frequency of the first query and its get_latest_with_bbox relies on the second shape.

[MateoLostanlen]

@Acruve15 si ca te branche de creuser ca 😄