[Backend / API] Fix FastAPI exception handling, SQL join deduplication, OpenAPI schema, and rate limiter memory leak

[yav02]

Issue Importance: CRITICAL (P0)

Summary

An audit of src/mk_tracking/ui_app/ revealed several backend bugs affecting error handling, SQL query determinism, OpenAPI schema compliance, JSON parsing, and middleware memory safety.

Sub-Issues & Sub-Task Importance Ratings

1. [HIGH - P1] Unhandled GCP Exceptions (HTTP 500):
   • api.py (lines 34-73): Endpoints invoke BigQuery synchronous functions without catching GoogleCloudError. Database connection drops cause HTTP 500 tracebacks instead of controlled 503 errors.
   • Fix: Wrap BQ queries in try...except GoogleCloudError and return HTTPException(status_code=503, detail="Database unavailable").
2. [CRITICAL - P0] SQL Join Deduplication Bug:
   • bigquery_repository.py (lines 659-677): In get_mk_posts, LEFT JOIN on multi-issue tagged posts causes duplicate rows. ROW_NUMBER() OVER (...) omits tag_confidence from ORDER BY, making tag selection non-deterministic.
   • Fix: Add ORDER BY tag.confidence DESC inside the window function partition.
3. [HIGH - P1] Undocumented OpenAPI Schema Fields:
   • models.py (line 15) vs bigquery_repository.py (line 188): BigQueryRepository._get_all_mks returns wikiTitle, seats, bloc, imageUrl, account, and bio, but MkResponse in models.py omits them, breaking OpenAPI TS generator schema.
   • Fix: Explicitly declare all returned fields in MkResponse.
4. [HIGH - P1] Unparsed BQ JSON String Columns:
   • bigquery_repository.py (line 378): Passes BigQuery engagement JSON string directly into Python dicts without json.loads(), causing PostResponse.metrics to serialize as a literal string payload.
   • Fix: Parse JSON defensively: json.loads(val) if isinstance(val, str) else val.
5. [CRITICAL - P0] Memory Leak & Proxy IP Bug in Rate Limiter:
   • app.py (lines 47-55): RateLimitMiddleware uses an unbounded defaultdict(list) that only prunes timestamps on active requests. One-off client requests leak memory permanently.
   • Fix: Replace with a bounded TTL cache (cachetools.TTLCache) and read X-Forwarded-For.
6. [HIGH - P1] Fail-Open Performance Degradation:
   • app.py (lines 90-92): If preload_summaries() fails during startup, every subsequent request triggers heavy synchronous BigQuery executions per request, overwhelming thread pools.
   • Fix: Add retries in startup lifespan and fail fast or run background cache refresh.

Affected Files

• src/mk_tracking/ui_app/api.py
• src/mk_tracking/ui_app/repositories/bigquery_repository.py
• src/mk_tracking/ui_app/models.py
• src/mk_tracking/ui_app/app.py