Fix N+1 query in nearby endpoint, add boundary test, fix photo media type

forgejo_admin commented

2026-03-16 19:36:30 +00:00

Contributor

Lineage

plan-mcd-tracker → Phase 5c → QA audit findings

Repo

forgejo_admin/mcd-tracker-api

User Story

auto-location: Nearby endpoint must be fast even with 20+ saved locations

Architecture

arch-dataflow-mcd-tracker#flow-5-auto-detect-location — nearby flow should be efficient

Context

Full repo audit (116 tests, 14 endpoints) found 3 issues. This PR fixes all three.

File Targets

BLOCKER: N+1 in nearby.py (lines 119-131)

src/mcd_tracker_api/routes/nearby.py — _build_slot_info() is called once per matched saved location inside the loop. With 20 OSM results and 10 matches → 10-20 extra queries.
Fix: collect all matched location IDs first, batch-load slot info in a single query, then reference from the loop.

MEDIUM: Rolling window boundary test

tests/test_locations.py — add test for code expiring at exactly now + 30 days. Query uses expires_at > func.now() (strict inequality). Verify code at exact boundary is NOT counted as active.

LOW: Photo media type

src/mcd_tracker_api/routes/receipts.py — FileResponse hardcodes media_type="image/jpeg" but accepts .jpg, .jpeg, .png, .webp. Fix: infer media type from file extension.

Acceptance Criteria

Nearby endpoint executes at most 2 DB queries regardless of how many OSM results match saved locations (1 for saved locations, 1 for slot info batch)
New test: code at exact 30-day boundary not counted as active
Photo endpoint returns correct Content-Type for .png and .webp files
All existing tests pass
No performance regression

Test Expectations

Existing nearby tests still pass
New boundary test passes
Photo content-type test (if not already covered)
Run: pytest tests/ -v

Constraints

Batch query should use WHERE location_id IN (...) pattern
Don't change the API response shape — internal optimization only
Keep _build_slot_info logic correct, just batch it

Checklist

PR opened with Closes #11
Tests pass
Ruff clean

phase-mcd-tracker-5c-gps-nearby — parent phase
plan-mcd-tracker — parent plan

### Lineage `plan-mcd-tracker` → Phase 5c → QA audit findings ### Repo `forgejo_admin/mcd-tracker-api` ### User Story - `auto-location`: Nearby endpoint must be fast even with 20+ saved locations ### Architecture - `arch-dataflow-mcd-tracker#flow-5-auto-detect-location` — nearby flow should be efficient ### Context Full repo audit (116 tests, 14 endpoints) found 3 issues. This PR fixes all three. ### File Targets **BLOCKER: N+1 in nearby.py (lines 119-131)** - `src/mcd_tracker_api/routes/nearby.py` — `_build_slot_info()` is called once per matched saved location inside the loop. With 20 OSM results and 10 matches → 10-20 extra queries. - Fix: collect all matched location IDs first, batch-load slot info in a single query, then reference from the loop. **MEDIUM: Rolling window boundary test** - `tests/test_locations.py` — add test for code expiring at exactly `now + 30 days`. Query uses `expires_at > func.now()` (strict inequality). Verify code at exact boundary is NOT counted as active. **LOW: Photo media type** - `src/mcd_tracker_api/routes/receipts.py` — `FileResponse` hardcodes `media_type="image/jpeg"` but accepts .jpg, .jpeg, .png, .webp. Fix: infer media type from file extension. ### Acceptance Criteria - [ ] Nearby endpoint executes at most 2 DB queries regardless of how many OSM results match saved locations (1 for saved locations, 1 for slot info batch) - [ ] New test: code at exact 30-day boundary not counted as active - [ ] Photo endpoint returns correct Content-Type for .png and .webp files - [ ] All existing tests pass - [ ] No performance regression ### Test Expectations - [ ] Existing nearby tests still pass - [ ] New boundary test passes - [ ] Photo content-type test (if not already covered) - Run: `pytest tests/ -v` ### Constraints - Batch query should use `WHERE location_id IN (...)` pattern - Don't change the API response shape — internal optimization only - Keep _build_slot_info logic correct, just batch it ### Checklist - [ ] PR opened with `Closes #11` - [ ] Tests pass - [ ] Ruff clean ### Related - `phase-mcd-tracker-5c-gps-nearby` — parent phase - `plan-mcd-tracker` — parent plan