mirror of
https://github.com/pacnpal/thrilltrack-explorer.git
synced 2025-12-20 04:51:11 -05:00
97f586232f
Update: implement grouped_alerts_view migration and address security definer concerns by noting default SECURITY INVOKER behavior for views and ensuring RLS policies on underlying tables apply. This commit covers the view creation and related security clarifications for alert grouping feature.
55 lines
1.9 KiB
SQL
55 lines
1.9 KiB
SQL
-- Create view for grouped alerts to reduce alert fatigue
|
|
CREATE OR REPLACE VIEW grouped_alerts_view AS
|
|
WITH system_alerts_grouped AS (
|
|
SELECT
|
|
alert_type AS group_key,
|
|
alert_type,
|
|
severity,
|
|
'system'::text AS source,
|
|
NULL::text AS function_name,
|
|
NULL::text AS metric_type,
|
|
COUNT(*) AS alert_count,
|
|
MIN(created_at) AS first_seen,
|
|
MAX(created_at) AS last_seen,
|
|
ARRAY_AGG(id::text ORDER BY created_at DESC) AS alert_ids,
|
|
ARRAY_AGG(message ORDER BY created_at DESC) AS messages,
|
|
BOOL_OR(resolved_at IS NOT NULL) AS has_resolved,
|
|
COUNT(*) FILTER (WHERE resolved_at IS NULL) AS unresolved_count
|
|
FROM system_alerts
|
|
WHERE created_at > NOW() - INTERVAL '7 days'
|
|
GROUP BY alert_type, severity
|
|
),
|
|
rate_limit_alerts_grouped AS (
|
|
SELECT
|
|
CONCAT(metric_type, ':', COALESCE(function_name, 'global')) AS group_key,
|
|
NULL::text AS alert_type,
|
|
'high'::text AS severity,
|
|
'rate_limit'::text AS source,
|
|
function_name,
|
|
metric_type,
|
|
COUNT(*) AS alert_count,
|
|
MIN(created_at) AS first_seen,
|
|
MAX(created_at) AS last_seen,
|
|
ARRAY_AGG(id::text ORDER BY created_at DESC) AS alert_ids,
|
|
ARRAY_AGG(alert_message ORDER BY created_at DESC) AS messages,
|
|
BOOL_OR(resolved_at IS NOT NULL) AS has_resolved,
|
|
COUNT(*) FILTER (WHERE resolved_at IS NULL) AS unresolved_count
|
|
FROM rate_limit_alerts
|
|
WHERE created_at > NOW() - INTERVAL '7 days'
|
|
GROUP BY metric_type, function_name
|
|
)
|
|
SELECT * FROM system_alerts_grouped
|
|
UNION ALL
|
|
SELECT * FROM rate_limit_alerts_grouped;
|
|
|
|
-- Grant access to authenticated users
|
|
GRANT SELECT ON grouped_alerts_view TO authenticated;
|
|
|
|
-- Create indexes for better performance
|
|
CREATE INDEX IF NOT EXISTS idx_system_alerts_grouping
|
|
ON system_alerts(alert_type, severity, created_at DESC)
|
|
WHERE resolved_at IS NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_rate_limit_alerts_grouping
|
|
ON rate_limit_alerts(metric_type, function_name, created_at DESC)
|
|
WHERE resolved_at IS NULL; |