thrilltrack-explorer/supabase/migrations/20251103002422_b1cf5175-1a89-4e0e-b86b-b126745acd63.sql

-- Migration: Add notification idempotency and duplicate prevention

-- Add idempotency columns to notification_logs
ALTER TABLE public.notification_logs
ADD COLUMN IF NOT EXISTS idempotency_key TEXT,
ADD COLUMN IF NOT EXISTS is_duplicate BOOLEAN NOT NULL DEFAULT false;

-- Create immutable function for date extraction (required for index)
CREATE OR REPLACE FUNCTION public.immutable_date(ts TIMESTAMPTZ)
RETURNS DATE AS $$
BEGIN
  RETURN ts::DATE;
END;
$$ LANGUAGE plpgsql IMMUTABLE SECURITY DEFINER SET search_path = public;

-- Create unique index to prevent duplicates (same key per user per day)
DROP INDEX IF EXISTS idx_notification_logs_idempotency_unique;
CREATE UNIQUE INDEX idx_notification_logs_idempotency_unique
ON public.notification_logs (user_id, idempotency_key, immutable_date(created_at))
WHERE idempotency_key IS NOT NULL;

-- Index for monitoring duplicates
CREATE INDEX IF NOT EXISTS idx_notification_logs_duplicates
ON public.notification_logs(is_duplicate, created_at)
WHERE is_duplicate = true;

-- Create duplicate tracking stats table
CREATE TABLE IF NOT EXISTS public.notification_duplicate_stats (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  date DATE NOT NULL DEFAULT CURRENT_DATE,
  total_attempts INTEGER DEFAULT 0,
  duplicates_prevented INTEGER DEFAULT 0,
  prevention_rate DECIMAL(5,2) GENERATED ALWAYS AS (
    CASE 
      WHEN total_attempts > 0 THEN (duplicates_prevented::DECIMAL / total_attempts * 100)
      ELSE 0
    END
  ) STORED,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(date)
);

-- Enable RLS on duplicate stats
ALTER TABLE public.notification_duplicate_stats ENABLE ROW LEVEL SECURITY;

-- Drop and recreate policy
DROP POLICY IF EXISTS "Admins can view duplicate stats" ON public.notification_duplicate_stats;
CREATE POLICY "Admins can view duplicate stats"
ON public.notification_duplicate_stats FOR SELECT
USING (is_moderator(auth.uid()));

-- Function to generate idempotency key
CREATE OR REPLACE FUNCTION public.generate_notification_idempotency_key(
  p_notification_type TEXT,
  p_entity_id UUID,
  p_recipient_id UUID,
  p_event_data JSONB DEFAULT NULL
)
RETURNS TEXT AS $$
BEGIN
  RETURN encode(
    digest(
      p_notification_type || 
      COALESCE(p_entity_id::TEXT, '') || 
      p_recipient_id::TEXT || 
      CURRENT_DATE::TEXT ||
      COALESCE(p_event_data::TEXT, ''),
      'sha256'
    ),
    'hex'
  );
END;
$$ LANGUAGE plpgsql IMMUTABLE SECURITY DEFINER SET search_path = public;

-- Create monitoring view for notification health
CREATE OR REPLACE VIEW public.notification_health_dashboard AS
SELECT
  date,
  total_attempts,
  duplicates_prevented,
  prevention_rate,
  CASE 
    WHEN prevention_rate > 10 THEN 'critical'
    WHEN prevention_rate > 5 THEN 'warning'
    ELSE 'healthy'
  END as health_status
FROM public.notification_duplicate_stats
ORDER BY date DESC
LIMIT 30;