c980f3edd0
Brings CRQ to FleetOps-dashboard parity with INC for the Tickets tab's CRQ sub-tab: - 16_crq_dashboard.sql: tickets.crq_open_sla view (mirror of inc_open_sla, no geog; lat/lng from geom) + reporting.fn_crq_dashboard / fn_crq_search / fn_crq_filter_options (mirrors of the inc functions over tickets.crq) + grants to dashboard_ro/grafana_ro. - Recover the previously un-versioned 13_inc_search_fn.sql + 14_inc_filter_options.sql into the repo (verbatim from the live defs) so a fresh DB rebuilds faithfully; the live ledger already lists them so run_migrations skips them there. Consumed by dashboard_api GET /webhook/crq-dashboard|crq-search|crq-filter-options. Not yet applied to prod (pending go-ahead). Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
89 lines
4.3 KiB
PL/PgSQL
89 lines
4.3 KiB
PL/PgSQL
-- 13_inc_search_fn.sql — fleettickets · INC ticket explorer (search) function
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
-- reporting.fn_inc_search — ad-hoc ticket lookup by id / engineer / cluster /
|
|
-- status / state / time, for the FleetOps "Ticket explorer" card. Returns
|
|
-- { count, truncated, limit, state, rows }. Consumed by dashboard_api
|
|
-- GET /webhook/inc-search.
|
|
--
|
|
-- RECOVERED INTO VERSION CONTROL 2026-06-26: this migration was applied to the live
|
|
-- DB on 2026-06-19 but the file was never committed. Recovered verbatim from the live
|
|
-- definition (pg_get_functiondef) so a fresh DB rebuilds faithfully; the live ledger
|
|
-- already lists it, so run_migrations skips it there. The crq mirror is in 16.
|
|
-- Idempotent (CREATE OR REPLACE).
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
|
|
SET search_path = tickets, public;
|
|
|
|
CREATE OR REPLACE FUNCTION reporting.fn_inc_search(
|
|
p_ticket_id text DEFAULT NULL,
|
|
p_owner text DEFAULT NULL,
|
|
p_cluster text DEFAULT NULL,
|
|
p_status text DEFAULT NULL,
|
|
p_state text DEFAULT 'closed',
|
|
p_from timestamptz DEFAULT NULL,
|
|
p_to timestamptz DEFAULT NULL,
|
|
p_limit integer DEFAULT 500
|
|
)
|
|
RETURNS jsonb LANGUAGE plpgsql STABLE AS $function$
|
|
DECLARE
|
|
v_state text := lower(COALESCE(NULLIF(p_state, ''), 'closed'));
|
|
v_limit integer := LEAST(GREATEST(COALESCE(p_limit, 500), 1), 5000);
|
|
v_result jsonb;
|
|
BEGIN
|
|
p_ticket_id := NULLIF(trim(p_ticket_id), '');
|
|
p_owner := NULLIF(trim(p_owner), '');
|
|
p_cluster := NULLIF(p_cluster, '');
|
|
p_status := NULLIF(p_status, '');
|
|
|
|
WITH hits AS (
|
|
SELECT ticket_id, normalized_status, cluster, region, location_name,
|
|
initcap(lower(NULLIF(owner, ''))) AS owner, assigned_team,
|
|
sla_status, mttr, closed_at, created_at_service, is_actionable,
|
|
CASE WHEN geom IS NOT NULL THEN ST_Y(geom) END AS lat,
|
|
CASE WHEN geom IS NOT NULL THEN ST_X(geom) END AS lng
|
|
FROM tickets.inc
|
|
WHERE (p_ticket_id IS NULL OR ticket_id ILIKE '%' || p_ticket_id || '%')
|
|
AND (p_owner IS NULL OR lower(owner) LIKE '%' || lower(p_owner) || '%')
|
|
AND (p_cluster IS NULL OR cluster = p_cluster)
|
|
AND (p_status IS NULL OR normalized_status = p_status)
|
|
AND CASE v_state
|
|
WHEN 'open' THEN COALESCE(is_actionable, false)
|
|
WHEN 'all' THEN COALESCE(is_actionable, false)
|
|
OR (closed_at IS NOT NULL
|
|
AND (p_from IS NULL OR closed_at >= p_from)
|
|
AND (p_to IS NULL OR closed_at < p_to))
|
|
ELSE NOT COALESCE(is_actionable, false) -- 'closed'
|
|
AND closed_at IS NOT NULL
|
|
AND (p_from IS NULL OR closed_at >= p_from)
|
|
AND (p_to IS NULL OR closed_at < p_to)
|
|
END
|
|
),
|
|
total AS (SELECT count(*) AS n FROM hits),
|
|
page AS (
|
|
SELECT * FROM hits
|
|
ORDER BY closed_at DESC NULLS LAST, created_at_service DESC NULLS LAST
|
|
LIMIT v_limit
|
|
)
|
|
SELECT jsonb_build_object(
|
|
'count', (SELECT n FROM total),
|
|
'truncated', (SELECT n FROM total) > v_limit,
|
|
'limit', v_limit,
|
|
'state', v_state,
|
|
'rows', COALESCE((SELECT jsonb_agg(to_jsonb(page)
|
|
ORDER BY page.closed_at DESC NULLS LAST,
|
|
page.created_at_service DESC NULLS LAST)
|
|
FROM page), '[]'::jsonb)
|
|
) INTO v_result;
|
|
|
|
RETURN v_result;
|
|
END $function$;
|
|
|
|
DO $grants$
|
|
BEGIN
|
|
IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'dashboard_ro') THEN
|
|
GRANT EXECUTE ON FUNCTION reporting.fn_inc_search(text, text, text, text, text, timestamptz, timestamptz, integer) TO dashboard_ro;
|
|
END IF;
|
|
IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'grafana_ro') THEN
|
|
GRANT EXECUTE ON FUNCTION reporting.fn_inc_search(text, text, text, text, text, timestamptz, timestamptz, integer) TO grafana_ro;
|
|
END IF;
|
|
END $grants$;
|