-- ============================================
-- Enhanced Moderation System Migration
-- ============================================
-- Adds auto-flagging capabilities and priority queue features
-- Version: 1.0.0
-- Date: 2025-01-18
-- ============================================
-- 1. UPDATE MODERATION_REPORTS TABLE
-- ============================================
-- Add enhanced moderation fields
ALTER TABLE public.moderation_reports ADD COLUMN IF NOT EXISTS priority VARCHAR(20) DEFAULT 'normal' CHECK (priority IN ('critical', 'high', 'normal', 'low'));
ALTER TABLE public.moderation_reports ADD COLUMN IF NOT EXISTS auto_flagged BOOLEAN DEFAULT FALSE;
ALTER TABLE public.moderation_reports ADD COLUMN IF NOT EXISTS auto_flag_reason TEXT;
ALTER TABLE public.moderation_reports ADD COLUMN IF NOT EXISTS confidence_score DECIMAL(3,2) CHECK (confidence_score >= 0 AND confidence_score <= 1);
-- Create index for priority queue (pending reports sorted by priority)
CREATE INDEX IF NOT EXISTS idx_moderation_priority_queue
ON public.moderation_reports(priority DESC, created_at ASC)
WHERE status = 'pending';
-- Create index for auto-flagged reports
CREATE INDEX IF NOT EXISTS idx_moderation_auto_flagged
ON public.moderation_reports(auto_flagged, created_at DESC)
WHERE status = 'pending' AND auto_flagged = TRUE;
-- ============================================
-- 2. MODERATION METRICS TABLE
-- ============================================
-- Track moderator performance and efficiency
CREATE TABLE IF NOT EXISTS public.moderation_metrics (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
moderator_id UUID REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL,
date DATE DEFAULT CURRENT_DATE NOT NULL,
-- Daily stats
reports_reviewed INT DEFAULT 0 CHECK (reports_reviewed >= 0),
posts_deleted INT DEFAULT 0 CHECK (posts_deleted >= 0),
posts_locked INT DEFAULT 0 CHECK (posts_locked >= 0),
reports_dismissed INT DEFAULT 0 CHECK (reports_dismissed >= 0),
avg_resolution_time_minutes DECIMAL(8,2) CHECK (avg_resolution_time_minutes >= 0),
created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
CONSTRAINT unique_moderator_date UNIQUE(moderator_id, date)
);
-- Index for moderator lookup
CREATE INDEX IF NOT EXISTS idx_moderation_metrics_moderator
ON public.moderation_metrics(moderator_id, date DESC);
-- RLS policies
ALTER TABLE public.moderation_metrics ENABLE ROW LEVEL SECURITY;
-- Moderators can view their own metrics
CREATE POLICY moderation_metrics_own ON public.moderation_metrics
FOR SELECT USING (moderator_id = auth.uid());
-- Admins can view all metrics
CREATE POLICY moderation_metrics_admin ON public.moderation_metrics
FOR SELECT USING (
EXISTS (
SELECT 1 FROM public.user_profiles
WHERE id = auth.uid() AND is_admin = TRUE
)
);
-- ============================================
-- 3. HELPER FUNCTIONS
-- ============================================
-- Function to track moderator action
CREATE OR REPLACE FUNCTION public.track_moderator_action(
p_moderator_id UUID,
p_action VARCHAR, -- 'reviewed', 'deleted', 'locked', 'dismissed'
p_resolution_time_minutes DECIMAL DEFAULT NULL
)
RETURNS VOID AS $$
DECLARE
v_existing_record RECORD;
BEGIN
-- Get or create today's metrics record
SELECT * INTO v_existing_record
FROM public.moderation_metrics
WHERE moderator_id = p_moderator_id
AND date = CURRENT_DATE;
IF NOT FOUND THEN
-- Create new record
INSERT INTO public.moderation_metrics (
moderator_id,
date,
reports_reviewed,
posts_deleted,
posts_locked,
reports_dismissed,
avg_resolution_time_minutes
) VALUES (
p_moderator_id,
CURRENT_DATE,
CASE WHEN p_action = 'reviewed' THEN 1 ELSE 0 END,
CASE WHEN p_action = 'deleted' THEN 1 ELSE 0 END,
CASE WHEN p_action = 'locked' THEN 1 ELSE 0 END,
CASE WHEN p_action = 'dismissed' THEN 1 ELSE 0 END,
p_resolution_time_minutes
);
ELSE
-- Update existing record
UPDATE public.moderation_metrics
SET
reports_reviewed = reports_reviewed + CASE WHEN p_action = 'reviewed' THEN 1 ELSE 0 END,
posts_deleted = posts_deleted + CASE WHEN p_action = 'deleted' THEN 1 ELSE 0 END,
posts_locked = posts_locked + CASE WHEN p_action = 'locked' THEN 1 ELSE 0 END,
reports_dismissed = reports_dismissed + CASE WHEN p_action = 'dismissed' THEN 1 ELSE 0 END,
-- Update running average for resolution time
avg_resolution_time_minutes =
CASE
WHEN p_resolution_time_minutes IS NOT NULL THEN
(COALESCE(avg_resolution_time_minutes, 0) * reports_reviewed + p_resolution_time_minutes)
/ (reports_reviewed + 1)
ELSE avg_resolution_time_minutes
END,
updated_at = NOW()
WHERE moderator_id = p_moderator_id
AND date = CURRENT_DATE;
END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Function to get moderation stats for a date range
CREATE OR REPLACE FUNCTION public.get_moderation_stats(
p_date_from DATE DEFAULT CURRENT_DATE - INTERVAL '30 days',
p_date_to DATE DEFAULT CURRENT_DATE
)
RETURNS TABLE(
total_reports_reviewed BIGINT,
total_posts_deleted BIGINT,
total_posts_locked BIGINT,
total_reports_dismissed BIGINT,
avg_resolution_time_minutes DECIMAL,
active_moderators BIGINT
) AS $$
BEGIN
RETURN QUERY
SELECT
COALESCE(SUM(reports_reviewed), 0)::BIGINT,
COALESCE(SUM(posts_deleted), 0)::BIGINT,
COALESCE(SUM(posts_locked), 0)::BIGINT,
COALESCE(SUM(reports_dismissed), 0)::BIGINT,
ROUND(AVG(avg_resolution_time_minutes), 2),
COUNT(DISTINCT moderator_id)::BIGINT
FROM public.moderation_metrics
WHERE date BETWEEN p_date_from AND p_date_to;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Function to get priority queue
CREATE OR REPLACE FUNCTION public.get_moderation_priority_queue(
p_limit INT DEFAULT 50
)
RETURNS TABLE(
id UUID,
post_id UUID,
reporter_id UUID,
reason TEXT,
priority VARCHAR,
auto_flagged BOOLEAN,
auto_flag_reason TEXT,
confidence_score DECIMAL,
status VARCHAR,
created_at TIMESTAMPTZ
) AS $$
BEGIN
RETURN QUERY
SELECT
mr.id,
mr.post_id,
mr.reporter_id,
mr.reason,
mr.priority,
mr.auto_flagged,
mr.auto_flag_reason,
mr.confidence_score,
mr.status,
mr.created_at
FROM public.moderation_reports mr
WHERE mr.status = 'pending'
ORDER BY
-- Priority order: critical > high > normal > low
CASE mr.priority
WHEN 'critical' THEN 1
WHEN 'high' THEN 2
WHEN 'normal' THEN 3
WHEN 'low' THEN 4
ELSE 5
END,
-- Then by creation time (oldest first)
mr.created_at ASC
LIMIT p_limit;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- ============================================
-- 4. UPDATE EXISTING FUNCTIONS
-- ============================================
-- Update the resolve_moderation_report function to track metrics
-- (This assumes a function exists from earlier migrations)
-- If it doesn't exist, this will be created in moderation actions
-- ============================================
-- SUCCESS MESSAGE
-- ============================================
DO $$
BEGIN
RAISE NOTICE 'Enhanced moderation system migration completed successfully!';
RAISE NOTICE 'Added priority, auto_flagged, confidence_score fields to moderation_reports';
RAISE NOTICE 'Created moderation_metrics table for tracking moderator performance';
RAISE NOTICE 'Created helper functions: track_moderator_action(), get_moderation_stats(), get_moderation_priority_queue()';
RAISE NOTICE 'Conservative auto-flagging ready to be implemented in application layer';
END $$;
