-- 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$;