kianiadee/fleettickets
1
0
Fork 0
Code Issues Pull requests Projects Releases Packages Wiki Activity Actions
fleettickets/docs/260702_work_done.md
david kiania bb38d354e5 fix(geocode): precise location geoms survive delta re-upserts (FT-BUG-01) 
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>
2026-07-02 09:47:15 +03:00

3.1 KiB
Raw Permalink Blame History

fleettickets — Work Done (2026-07-02)

Execution log for 260702_fix_plan.md Phase A. Local changes only — nothing committed, pushed, or applied to the live DB (dry-runs were wrapped in rolled-back transactions).

Changes

Finding Files What changed
FT-BUG-01 migrations/18_trigger_location_geom.sql (new) tg_ticket_geom now resolves feed coords → precise geo_locations → cluster centroid → none (was skipping geo_locations entirely, so 20-min delta upserts clobbered precise geoms back to cluster centroids). Migration ends with one resolve_ticket_geoms() to repair the backlog. Dry-run verified against the live DB (rolled back): 7,481 rows repaired — INC geo_source='location' 51 → 5,339; CRQ 0 → 2,193.
FT-BUG-01 pipeline.py ingest() now calls _resolve() after every applied run that ingested files — geoms self-heal when a location was geocoded after a ticket's last upsert, and on any DB where migration 18 hasn't landed yet. No-op runs skip it (no extra load on the 20-min quiet cycles).
FT-BUG-02 run_ingest.sh Chains python -m inc.import_inc --geocode-clusters --apply after the two ingests. Incremental (NOT EXISTS-guarded): zero geocoder calls when no new clusters. Location-level geocoding stays manual for budget control. If you use Coolify Scheduled Tasks instead of this wrapper, add the same command as a third task (or switch the tasks to run_ingest.sh).
FT-SEC-02 Dockerfile, .dockerignore Dependencies now install pinned from uv.lock (uv export --frozen → hash-checked; build fails on lockfile drift; verified locally: 12 pinned packages). uv.lock removed from .dockerignore so it reaches the build. Container now runs as non-root tickets-user. Dropped the unnecessary pip install . (runtime imports from /app via -m).
FT-OPS-01 20260618_bug.txt (deleted) Stale review of the pre-rewrite import_tickets.py; its live findings were fixed by the pipeline.py rewrite (watermark drain replaced the ETag skip; meta now commits in the upsert txn).

Verification

  • python -m py_compile clean on all five modules; ruff check . shows the same 14 pre-existing style items as before the changes (nothing new introduced).
  • Migration 18 executed against the live DB inside BEGIN … ROLLBACK: applies cleanly, resolve repaired 7,481 rows (counts above).
  • uv export --frozen succeeds against the committed lockfile.

NOT done — operational (Phase B, needs operator confirmation)

  1. Change the Coolify app's DATABASE_URL off the postgres superuser / public twala:5433 route → tracksolid_owner@timescale_db:5432 on the internal network (FT-SEC-01). Required anyway before the tracksolid stack's loopback-only port binding deploys.
  2. Merge + push, python run_migrations.py (applies 18), redeploy the container.
  3. One-time --geocode-locations --apply run to extend location coverage now that precise geoms persist.
  4. The separately-spawned background task is preparing the unused-geo-index drop (~134 MB) landed as migration 17; this trigger fix was renumbered to 18 so both coexist.