-- Performance Monitoring System
-- Stores PageSpeed Insights results for trend analysis and anomaly detection
-- Create performance_scores table
CREATE TABLE IF NOT EXISTS performance_scores (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
url TEXT NOT NULL,
strategy TEXT NOT NULL CHECK (strategy IN ('mobile', 'desktop')),
timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
-- Deployment context
deployment_type TEXT CHECK (deployment_type IN ('frontend', 'api', 'manual')),
commit_sha TEXT,
deployment_id TEXT, -- Links scores from same deployment run
-- Core Lighthouse scores (0-100)
performance_score INTEGER,
accessibility_score INTEGER,
best_practices_score INTEGER,
seo_score INTEGER,
-- Core Web Vitals (in milliseconds except CLS)
lcp_ms INTEGER, -- Largest Contentful Paint
fid_ms INTEGER, -- First Input Delay (deprecated, using INP now)
inp_ms INTEGER, -- Interaction to Next Paint (replaces FID)
cls DECIMAL(5, 3), -- Cumulative Layout Shift (unitless)
fcp_ms INTEGER, -- First Contentful Paint
ttfb_ms INTEGER, -- Time to First Byte
tbt_ms INTEGER, -- Total Blocking Time
-- Speed Index (visual completeness)
speed_index_ms INTEGER,
-- Raw response for debugging (compressed)
raw_response JSONB,
-- Anomaly tracking
is_anomaly BOOLEAN DEFAULT FALSE,
anomaly_details JSONB, -- What triggered the anomaly (score drops, threshold violations)
-- GitHub issue tracking
github_issue_number INTEGER,
github_issue_url TEXT,
-- Tracking
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Indexes for efficient querying
CREATE INDEX idx_perf_scores_url_strategy ON performance_scores(url, strategy);
CREATE INDEX idx_perf_scores_timestamp ON performance_scores(timestamp DESC);
CREATE INDEX idx_perf_scores_deployment ON performance_scores(deployment_id);
CREATE INDEX idx_perf_scores_commit ON performance_scores(commit_sha);
CREATE INDEX idx_perf_scores_anomaly ON performance_scores(is_anomaly) WHERE is_anomaly = TRUE;
-- Index for dashboard queries (recent scores by page)
CREATE INDEX idx_perf_scores_recent ON performance_scores(url, strategy, timestamp DESC);
-- Enable RLS
ALTER TABLE performance_scores ENABLE ROW LEVEL SECURITY;
-- Admin-only read policy (only admins can view performance data)
CREATE POLICY "Admins can view performance scores"
ON performance_scores FOR SELECT
TO authenticated
USING (
EXISTS (
SELECT 1 FROM user_profiles
WHERE user_profiles.id = auth.uid()
AND user_profiles.is_admin = true
)
);
-- Service role can do everything (for Cloud Run jobs)
CREATE POLICY "Service role full access"
ON performance_scores FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
-- Function to get baseline stats for anomaly detection
CREATE OR REPLACE FUNCTION get_performance_baseline(
p_url TEXT,
p_strategy TEXT,
p_days INTEGER DEFAULT 7
)
RETURNS TABLE (
avg_performance DECIMAL,
avg_lcp DECIMAL,
avg_cls DECIMAL,
avg_fcp DECIMAL,
avg_tbt DECIMAL,
sample_count INTEGER
) AS $$
BEGIN
RETURN QUERY
SELECT
AVG(performance_score)::DECIMAL as avg_performance,
AVG(lcp_ms)::DECIMAL as avg_lcp,
AVG(cls)::DECIMAL as avg_cls,
AVG(fcp_ms)::DECIMAL as avg_fcp,
AVG(tbt_ms)::DECIMAL as avg_tbt,
COUNT(*)::INTEGER as sample_count
FROM performance_scores
WHERE url = p_url
AND strategy = p_strategy
AND timestamp > NOW() - (p_days || ' days')::INTERVAL
AND is_anomaly = FALSE; -- Exclude anomalies from baseline
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Function to get latest scores for all monitored pages
CREATE OR REPLACE FUNCTION get_latest_performance_scores()
RETURNS TABLE (
url TEXT,
strategy TEXT,
measured_at TIMESTAMPTZ,
performance_score INTEGER,
accessibility_score INTEGER,
best_practices_score INTEGER,
seo_score INTEGER,
lcp_ms INTEGER,
cls DECIMAL,
fcp_ms INTEGER,
tbt_ms INTEGER,
deployment_id TEXT,
commit_sha TEXT,
is_anomaly BOOLEAN
) AS $$
BEGIN
RETURN QUERY
SELECT DISTINCT ON (ps.url, ps.strategy)
ps.url,
ps.strategy,
ps."timestamp" as measured_at,
ps.performance_score,
ps.accessibility_score,
ps.best_practices_score,
ps.seo_score,
ps.lcp_ms,
ps.cls,
ps.fcp_ms,
ps.tbt_ms,
ps.deployment_id,
ps.commit_sha,
ps.is_anomaly
FROM performance_scores ps
ORDER BY ps.url, ps.strategy, ps."timestamp" DESC;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Function to compare before/after deployment scores
CREATE OR REPLACE FUNCTION compare_deployment_scores(
p_deployment_id TEXT
)
RETURNS TABLE (
url TEXT,
strategy TEXT,
current_performance INTEGER,
baseline_performance DECIMAL,
performance_change DECIMAL,
current_lcp INTEGER,
baseline_lcp DECIMAL,
lcp_change DECIMAL,
current_cls DECIMAL,
baseline_cls DECIMAL,
cls_change DECIMAL
) AS $$
BEGIN
RETURN QUERY
WITH deployment_scores AS (
SELECT
ps.url,
ps.strategy,
ps.performance_score,
ps.lcp_ms,
ps.cls
FROM performance_scores ps
WHERE ps.deployment_id = p_deployment_id
),
baselines AS (
SELECT
b.url,
b.strategy,
(get_performance_baseline(b.url, b.strategy, 7)).*
FROM (SELECT DISTINCT url, strategy FROM deployment_scores) b
)
SELECT
d.url,
d.strategy,
d.performance_score as current_performance,
b.avg_performance as baseline_performance,
(d.performance_score - b.avg_performance)::DECIMAL as performance_change,
d.lcp_ms as current_lcp,
b.avg_lcp as baseline_lcp,
(d.lcp_ms - b.avg_lcp)::DECIMAL as lcp_change,
d.cls as current_cls,
b.avg_cls as baseline_cls,
(d.cls - b.avg_cls)::DECIMAL as cls_change
FROM deployment_scores d
LEFT JOIN baselines b ON d.url = b.url AND d.strategy = b.strategy;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Grant execute on functions to authenticated users
GRANT EXECUTE ON FUNCTION get_performance_baseline TO authenticated;
GRANT EXECUTE ON FUNCTION get_latest_performance_scores TO authenticated;
GRANT EXECUTE ON FUNCTION compare_deployment_scores TO authenticated;
-- Add comment for documentation
COMMENT ON TABLE performance_scores IS 'Stores PageSpeed Insights results for performance monitoring and anomaly detection';
