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>
30 lines
2.8 KiB
Markdown
30 lines
2.8 KiB
Markdown
# 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 1` to show
|
|
`location` ≫ 51 (should approach the number of tickets whose location_name is in
|
|
the 808-key gazetteer).
|
|
- Next scheduled ingest run: confirm `location` counts don't regress after deltas.
|