066d866b90
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>
101 lines
7.5 KiB
SQL
101 lines
7.5 KiB
SQL
-- 15_crq_table.sql — fleettickets · materialize tickets.crq + typed columns
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
-- Why a NEW migration (not an edit to 01): `01_tickets_schema.sql` was applied to the
|
|
-- live DB on 2026-06-15 from a version that PREDATED its `tickets.crq` section, so the
|
|
-- IF-NOT-EXISTS ledger guard has kept crq from ever being created there — even though
|
|
-- the live `reporting.fn_tickets_for_map` and `tickets.resolve_ticket_geoms` already
|
|
-- reference it (they error if called until crq exists). This migration creates
|
|
-- `tickets.crq` self-containedly (table + geom trigger + indexes) and adds the same
|
|
-- typed STORED generated columns INC got in `03_inc_columns.sql`, bringing CRQ to
|
|
-- data-layer parity.
|
|
--
|
|
-- Deterministic + idempotent — converges to the same shape on BOTH:
|
|
-- • the live DB (crq missing) -> CREATE makes it, ALTER adds typed cols
|
|
-- • a fresh DB (crq minimal, from 01) -> CREATE skipped, ALTER adds typed cols
|
|
-- Reuses shared objects already present: tickets.tg_ticket_geom() (01),
|
|
-- tickets.norm_cluster() (01), tickets.eat_ts() (03).
|
|
--
|
|
-- NOTE: the live DB also carries un-versioned migrations 13_inc_search_fn.sql /
|
|
-- 14_inc_filter_options.sql (applied 2026-06-19, absent from this repo) — INC dashboard
|
|
-- functions, unrelated to CRQ. Numbered 15 here to sit cleanly after the live ledger.
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
|
|
SET search_path = tickets, public;
|
|
|
|
-- ── table (base shape mirrors tickets.inc's original 01 base) ────────────────
|
|
CREATE TABLE IF NOT EXISTS tickets.crq (
|
|
ticket_id text PRIMARY KEY,
|
|
raw jsonb NOT NULL,
|
|
geom geometry(Point, 4326),
|
|
geo_source text, -- 'feed' | 'location' | 'cluster' | 'none'
|
|
ingested_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- ── geom trigger — read from raw; shared tickets.tg_ticket_geom() (from 01) ───
|
|
DROP TRIGGER IF EXISTS trg_crq_geom ON tickets.crq;
|
|
CREATE TRIGGER trg_crq_geom BEFORE INSERT OR UPDATE ON tickets.crq
|
|
FOR EACH ROW EXECUTE FUNCTION tickets.tg_ticket_geom();
|
|
|
|
-- ── raw-based indexes (mirror 01's inc/crq set) ──────────────────────────────
|
|
CREATE INDEX IF NOT EXISTS ix_crq_status_raw ON tickets.crq ((raw->>'normalized_status'));
|
|
CREATE INDEX IF NOT EXISTS ix_crq_actionable_raw ON tickets.crq (((raw->>'is_actionable')::boolean))
|
|
WHERE (raw->>'is_actionable')::boolean;
|
|
CREATE INDEX IF NOT EXISTS ix_crq_cluster_raw ON tickets.crq (tickets.norm_cluster(raw->>'cluster'));
|
|
CREATE INDEX IF NOT EXISTS ix_crq_loc_raw ON tickets.crq (tickets.norm_cluster(raw->>'location_name'));
|
|
CREATE INDEX IF NOT EXISTS ix_crq_geom ON tickets.crq USING gist (geom);
|
|
|
|
-- ── typed STORED generated columns (mirror of 03_inc_columns.sql) ────────────
|
|
-- Computed for ALL existing rows on creation + auto-recomputed on every insert/update;
|
|
-- `raw` stays the source of truth. tickets.eat_ts() (EAT->timestamptz, IMMUTABLE) is
|
|
-- reused from 03 — see that file's note on why IMMUTABLE is safe for Kenya (UTC+3, no DST).
|
|
ALTER TABLE tickets.crq
|
|
-- text
|
|
ADD COLUMN IF NOT EXISTS service_type text GENERATED ALWAYS AS (raw->>'service_type') STORED,
|
|
ADD COLUMN IF NOT EXISTS bucket text GENERATED ALWAYS AS (raw->>'bucket') STORED,
|
|
ADD COLUMN IF NOT EXISTS raw_status text GENERATED ALWAYS AS (raw->>'raw_status') STORED,
|
|
ADD COLUMN IF NOT EXISTS normalized_status text GENERATED ALWAYS AS (raw->>'normalized_status') STORED,
|
|
ADD COLUMN IF NOT EXISTS cluster text GENERATED ALWAYS AS (raw->>'cluster') STORED,
|
|
ADD COLUMN IF NOT EXISTS region text GENERATED ALWAYS AS (raw->>'region') STORED,
|
|
ADD COLUMN IF NOT EXISTS location_name text GENERATED ALWAYS AS (raw->>'location_name') STORED,
|
|
ADD COLUMN IF NOT EXISTS assigned_team text GENERATED ALWAYS AS (raw->>'assigned_team') STORED,
|
|
ADD COLUMN IF NOT EXISTS owner text GENERATED ALWAYS AS (raw->>'owner') STORED,
|
|
ADD COLUMN IF NOT EXISTS sla_status text GENERATED ALWAYS AS (raw->>'sla_status') STORED,
|
|
-- numeric / float
|
|
ADD COLUMN IF NOT EXISTS mttr numeric GENERATED ALWAYS AS (NULLIF(raw->>'mttr','')::numeric) STORED,
|
|
ADD COLUMN IF NOT EXISTS latitude double precision GENERATED ALWAYS AS (NULLIF(raw->>'latitude','')::double precision) STORED,
|
|
ADD COLUMN IF NOT EXISTS longitude double precision GENERATED ALWAYS AS (NULLIF(raw->>'longitude','')::double precision) STORED,
|
|
-- boolean
|
|
ADD COLUMN IF NOT EXISTS is_actionable boolean GENERATED ALWAYS AS (NULLIF(raw->>'is_actionable','')::boolean) STORED,
|
|
ADD COLUMN IF NOT EXISTS is_auto_created boolean GENERATED ALWAYS AS (NULLIF(raw->>'is_auto_created','')::boolean) STORED,
|
|
ADD COLUMN IF NOT EXISTS is_auto_closed boolean GENERATED ALWAYS AS (NULLIF(raw->>'is_auto_closed','')::boolean) STORED,
|
|
ADD COLUMN IF NOT EXISTS is_alarm boolean GENERATED ALWAYS AS (NULLIF(raw->>'is_alarm','')::boolean) STORED,
|
|
-- timestamps (EAT wall-clock -> timestamptz). created_at/updated_at are the EXPORT
|
|
-- pipeline's bookkeeping (not ticket lifecycle), hence the source_ prefix.
|
|
ADD COLUMN IF NOT EXISTS created_at_service timestamptz GENERATED ALWAYS AS (tickets.eat_ts(raw->>'created_at_service')) STORED,
|
|
ADD COLUMN IF NOT EXISTS scheduled_at timestamptz GENERATED ALWAYS AS (tickets.eat_ts(raw->>'scheduled_at')) STORED,
|
|
ADD COLUMN IF NOT EXISTS closed_at timestamptz GENERATED ALWAYS AS (tickets.eat_ts(raw->>'closed_at')) STORED,
|
|
ADD COLUMN IF NOT EXISTS last_seen_at timestamptz GENERATED ALWAYS AS (tickets.eat_ts(raw->>'last_seen_at')) STORED,
|
|
ADD COLUMN IF NOT EXISTS first_seen_at timestamptz GENERATED ALWAYS AS (tickets.eat_ts(raw->>'first_seen_at')) STORED,
|
|
ADD COLUMN IF NOT EXISTS source_created_at timestamptz GENERATED ALWAYS AS (tickets.eat_ts(raw->>'created_at')) STORED,
|
|
ADD COLUMN IF NOT EXISTS source_updated_at timestamptz GENERATED ALWAYS AS (tickets.eat_ts(raw->>'updated_at')) STORED;
|
|
|
|
-- ── typed-column indexes (serve cluster / team / closure queries) ────────────
|
|
CREATE INDEX IF NOT EXISTS ix_crq_norm_status_col ON tickets.crq (normalized_status);
|
|
CREATE INDEX IF NOT EXISTS ix_crq_cluster_col ON tickets.crq (cluster);
|
|
CREATE INDEX IF NOT EXISTS ix_crq_assigned_team ON tickets.crq (assigned_team);
|
|
CREATE INDEX IF NOT EXISTS ix_crq_closed_at ON tickets.crq (closed_at);
|
|
CREATE INDEX IF NOT EXISTS ix_crq_actionable_col ON tickets.crq (is_actionable) WHERE is_actionable;
|
|
|
|
-- ── grants (guarded: roles may not exist on a fresh DB) ──────────────────────
|
|
DO $grants$
|
|
BEGIN
|
|
IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'tracksolid_owner') THEN
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON tickets.crq TO tracksolid_owner;
|
|
END IF;
|
|
IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'dashboard_ro') THEN
|
|
GRANT SELECT ON tickets.crq TO dashboard_ro;
|
|
END IF;
|
|
IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'grafana_ro') THEN
|
|
GRANT SELECT ON tickets.crq TO grafana_ro;
|
|
END IF;
|
|
END $grants$;
|