bb38d354e5
The tg_ticket_geom trigger resolved feed coords -> cluster centroid -> none, never consulting tickets.geo_locations, so every 20-min delta ingest re-upserted changed rows and downgraded previously-resolved 'location' geoms back to the cluster centroid. Live effect: only 51 of 114k INC (and 0 of 42k CRQ) rows kept the precise geocode the LocationIQ budget paid for. - migration 18: trigger now resolves feed -> geo_locations (precise) -> cluster -> none, mirroring resolve_ticket_geoms() precedence; ends with one resolve pass to repair the backlog. Dry-run against the live DB (rolled back) repaired 7,481 rows: INC location 51 -> 5,339, CRQ 0 -> 2,193. - pipeline.ingest(): re-resolve after every applied run that ingested files, so geoms self-heal even before migration 18 lands. - run_ingest.sh: chain an incremental --geocode-clusters pass (0 API calls when no new clusters) so new clusters map without a manual command (FT-BUG-02). - Dockerfile/.dockerignore: pinned installs from uv.lock, non-root user (FT-SEC-02). - 20260618_bug.txt removed (stale review of a since-rewritten file). Numbered 18 to coexist with 17_drop_unused_geo_indexes.sql (parallel 260702 change). Audit + plan + work log in docs/260702_*. Local only; not applied to prod. Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
2.8 KiB
2.8 KiB
fleettickets — Fix Plan (2026-07-02)
Companion to 260702_audit_report.md (findings) and 260702_work_done.md
(execution log).
Phase A — repo changes (implemented in this session)
| Step | Finding | Change |
|---|---|---|
| A1 | FT-BUG-01 | Migration 18_trigger_location_geom.sql: tg_ticket_geom resolves feed coords → geo_locations (precise) → cluster centroid → none, so upserts stop clobbering precise geoms; the migration ends with one resolve_ticket_geoms() call to repair the existing 114k+42k rows immediately. |
| A2 | FT-BUG-01 | pipeline.ingest(): after an --apply run that ingested files, call _resolve() — belt-and-braces so location geoms self-heal even on a DB where migration 18 hasn't landed yet. |
| A3 | FT-BUG-02 | run_ingest.sh: chain an incremental --geocode-clusters --apply after the two ingests (0 API calls when no new clusters). Location geocoding stays manual (budget control). |
| A4 | FT-SEC-02 | Dockerfile: install pinned deps from uv.lock (uv export --frozen), stop pip install . (runtime imports from /app via -m), run as a non-root user. Remove uv.lock from .dockerignore. |
| A5 | FT-OPS-01 | Delete the stale 20260618_bug.txt (its findings are fixed or superseded). |
Phase B — operational actions (need operator confirmation)
| # | Finding | Action |
|---|---|---|
| B1 | FT-SEC-01 | In the Coolify app env, change DATABASE_URL to postgresql://tracksolid_owner:<pw>@timescale_db:5432/tracksolid_db and attach the container to the tracksolid stack's Docker network. Must land before/with the tracksolid stack's loopback port-binding deploy. Verify tracksolid_owner has the needed GRANTs on the tickets schema (it should own it; check \dn+ tickets). |
| B2 | FT-BUG-01/02 | After merging Phase A: python run_migrations.py (applies 18), redeploy the container, and update the Coolify Scheduled Tasks to also run the chained cluster geocode (or keep using run_ingest.sh). One-time: run python -m inc.import_inc --geocode-locations --apply to extend location coverage now that it will actually stick. |
| B3 | FT-PERF-01 | The index-drop migration landed as 17_drop_unused_geo_indexes.sql (background task, committed on branch chore/drop-unused-geo-indexes); this audit's trigger fix is renumbered to 18_trigger_location_geom.sql so the two coexist. |
Verification
python -m py_compile pipeline.py shared.py inc/import_inc.py crq/import_crq.py;ruff check ..- Migration 18 dry-run in a rolled-back transaction against the live DB; after real
apply, expect
SELECT geo_source, count(*) FROM tickets.inc GROUP BY 1to showlocation≫ 51 (should approach the number of tickets whose location_name is in the 808-key gazetteer). - Next scheduled ingest run: confirm
locationcounts don't regress after deltas.