Live-DB reconciliation before seeding CRQ revealed two divergences:
- tickets.crq did NOT exist: 01_tickets_schema.sql was applied 2026-06-15 from a
version predating its crq section, so the IF-NOT-EXISTS ledger guard has blocked
it ever since (fn_tickets_for_map + resolve_ticket_geoms already reference crq, so
they errored if called — masked because the live INC view uses fn_inc_dashboard).
- The live ledger carries un-versioned 13_inc_search_fn.sql / 14_inc_filter_options.sql
(applied 2026-06-19, absent from this repo).
So 13_crq_columns.sql (ALTER-only, number 13) is replaced by 15_crq_table.sql, which
CREATEs tickets.crq self-containedly (table + geom trigger + raw/typed indexes) and
adds the typed STORED generated columns. Deterministic + idempotent on both the live DB
(crq missing) and a fresh DB (crq minimal from 01). Numbered 15 to sit after the live
ledger's max. Docs/CLI references updated 13->15.
Applied + seeded on the live DB out-of-band (running container, INC image untouched):
39,240 crq rows, 99.99% geocoded (cluster + shared location cache), watermark current,
crq now renders on fn_tickets_for_map.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
Split the INC-only loader into a dataset-agnostic engine (pipeline.py, renamed
from import_tickets.py) parameterized by a Dataset config, with thin per-type
entrypoints inc/import_inc.py and crq/import_crq.py. CRQ shares INC's identical
32-column source schema and CDC change stream, so the engine is fully shared.
- pipeline.py: Dataset config (name/table/prefixes/key_regex/post_apply); INC
keeps the capture_history post-apply hook, CRQ has none yet. geocode_locations
now unions tickets.crq (geocoding is cross-dataset: one gazetteer/budget).
- crq/import_crq.py: drains automations/crq/changes/ from isptickets into
tickets.crq (data layer + map; SLA/dashboard/history deferred).
- migrations/13_crq_columns.sql: CRQ mirror of 03 — typed STORED generated
columns + indexes on tickets.crq (reuses tickets.eat_ts()).
- Deployment: Dockerfile/run_ingest.sh run both via `python -m`; pyproject
packages inc/crq. Docs (README, implementation, deployment-and-operations,
n8n export ref, phase-1) updated for the split + the one-time CRQ seed runbook.
tickets.crq already exists (mig 01, LIKE tickets.inc) and is unioned into
reporting.fn_tickets_for_map + resolve_ticket_geoms, so CRQ appears on the
existing Tickets map once seeded. Verified locally: ruff-clean new files, engine
lists/parses both streams against live S3 (crq=52 files, inc unaffected).
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
Capture the operational knowledge from the isptickets cutover: Coolify app/container,
env management (encrypted — UI or artisan tinker), cron, the Forgejo->Coolify auto-deploy
webhook (config + recreate/verify; it was missing), manual deploy trigger, the
source-bucket cutover procedure, and verification queries. Link it from README; refresh
stale tickets-bucket/ETag references in implementation.md to the isptickets CDC model.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
Was hourly at :15 (15 7-19 * * *); now */20 6-20 * * * for fresher ticket
data through the working day. Updates the documented schedule in the Coolify
Scheduled Task command, run_ingest.sh, Dockerfile, README, and implementation
notes (the live schedule is set in the Coolify UI).
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
Provider moved the INC CDC feed to a new bucket (tickets -> isptickets, new
per-bucket creds; same s3.rahamafresh.com endpoint, identical 32-col schema).
This is config + a one-time reseed, not a rewrite — the loader already drains
automations/inc/changes/ oldest->newest with a source_max_key watermark.
- default _BUCKET -> isptickets (TICKETS_BUCKET still overrides)
- add --reseed: ignore the stored watermark and drain every changes/ file once
(the old-bucket watermark may post-date the new bucket's first file). Crash-safe
via the existing per-file watermark-advance + archive loop.
- refresh stale "newest-file / full-snapshot-per-hour" docstring/comments to the
CDC reality; .env.example + README updated (new bucket + reseed runbook).
Verified live dry-run: 41/41 files drained (watermark None), alarm/sentinel
filter active, exit 0.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
- tickets.closure_events: append-only observed closures (PK ticket_id, closed_at;
observed_at = first sighting; survives row churn).
- tickets.inc_daily_snapshot: one row per EAT day — open backlog (+ SLA split, by
cluster/status) and created/closed flow; upserted each run.
- tickets.capture_history(): appends new closures + upserts today's snapshot.
- import_tickets calls it after each --apply run (ingest or skip); add
--capture-history CLI flag for standalone runs.
Verified: backfilled 21,282 closures; today's snapshot recorded (open_total 30).
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
One parameterized function returns {window, open GeoJSON, closed GeoJSON, metrics,
freshness} for the FleetOps live INC map:
- open = all is_actionable tickets (live), filtered by cluster/status, with
sla_state/hours_open (from tickets.inc_open_sla)
- closed= closed_at within the selected window (EAT calendar today/week/month or
custom [from,to)), filtered by cluster/status
- metrics= open/closed counts, SLA split (open derived, closed source), by status/
cluster, closure rate + daily series, avg mttr (minutes)
Filters combine with AND; grants to dashboard_ro/grafana_ro. Verified live
(today/month/cluster/status/custom; last-7d closed=913 matches raw).
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
- Add tickets.inc_open_sla: open tickets with derived SLA (hours_open, sla_state
vs 48h; clock = created_at_service or first_seen_at fallback) + team/cluster/geog
for dispatch. (One-time legacy region->lowercase backfill applied to live data.)
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
service_type is always 'inc' (cardinality 1) — zero info, redundant in an INC-only
table. Drop the generated column; stays in raw for audit.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
mttr generated column is now integer minutes (source raw.mttr is decimal hours),
analytics-friendly. Drop is_alarm/is_auto_created/is_auto_closed generated columns
— all constant `false` in tickets.inc since alarms are filtered at ingest (still
present in raw for audit; loader still filters on raw->>'is_alarm'). is_actionable
kept.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
geom is geometry(Point,4326) (planar degrees); add geog = geom::geography (STORED
generated) + GiST index so ST_Distance/ST_DWithin/KNN work in real metres for
nearest-vehicle and radius queries.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
Feed coords are always empty; redefine the latitude/longitude generated columns to
COALESCE(feed, ST_Y/ST_X(geom)) so they carry the resolved/geocoded position for
every geocoded ticket (precision indicated by geo_source). STORED, recomputes when
geom changes.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
Add STORED generated columns derived from raw (text/numeric/bool/double + EAT
timestamptz via an IMMUTABLE tickets.eat_ts() wrapper). Computed for all existing
rows and auto-populated on every future ingest — raw stays the source of truth,
no loader change. Indexes on status/cluster/team/closed_at/is_actionable for the
SLA/team/closure queries.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
- Replace the aws-CLI subprocess calls with boto3 (list_objects_v2 paginator,
get_object, copy_object+delete_object) using path-style addressing + RUSTFS_*
env. Removes the external aws-CLI dependency so it runs in a slim container.
- Add boto3 to pyproject dependencies.
- Add Dockerfile (python:3.12-slim, deps, TZ=Africa/Nairobi, keep-alive CMD) and
.dockerignore for Coolify; document Coolify Scheduled Task setup in README.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
Rework import_tickets.py from the retired JSON `latest.json` model to the new
hourly full-snapshot CSV export. Strictly INC (CRQ out of scope).
- Ingest the newest automations/inc/<EAT-timestamp>.csv; skip-if-unchanged by
comparing S3 ETag to tickets.import_meta.metadata.source_etag.
- Upsert on ticket_id (PK; no dups, never delete -> closure history accrues).
No truncate. On success, move processed files to automations/inc/processed/.
- Clean at ingest: drop is_alarm=true + the "EXPORT STOPPED..." sentinel; drop
week_*, source_s3_*/source_snapshot_id, department/source_type; lowercase
region, uppercase raw_status; keep service_type + bucket.
- Force path-style S3 addressing; --inc-csv for local dev; --from-bucket for cron.
- Add migrations/02 (import_meta + freshness); refresh README/.env.example/docs.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
