-- ============================================
-- Email Digest System Migration
-- ============================================
-- Creates table for tracking sent email digests
-- Leverages existing notification_preferences.email_digest_frequency field
-- Version: 1.0.0
-- Date: 2025-01-15
-- ============================================
-- 1. EMAIL DIGEST LOG TABLE
-- ============================================
CREATE TABLE IF NOT EXISTS public.email_digest_log (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL,
digest_type VARCHAR(10) NOT NULL CHECK (digest_type IN ('daily', 'weekly')),
sent_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
post_count INT NOT NULL CHECK (post_count >= 0),
email_to TEXT NOT NULL,
status VARCHAR(20) DEFAULT 'sent' CHECK (status IN ('sent', 'failed', 'bounced')),
error_message TEXT,
created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);
-- Indexes for efficient queries
CREATE INDEX IF NOT EXISTS idx_email_digest_user ON public.email_digest_log(user_id, sent_at DESC);
CREATE INDEX IF NOT EXISTS idx_email_digest_status ON public.email_digest_log(status, sent_at DESC);
CREATE INDEX IF NOT EXISTS idx_email_digest_type ON public.email_digest_log(digest_type, sent_at DESC);
-- ============================================
-- 2. ROW-LEVEL SECURITY (RLS)
-- ============================================
ALTER TABLE public.email_digest_log ENABLE ROW LEVEL SECURITY;
-- Users can only view their own digest log
CREATE POLICY email_digest_log_select ON public.email_digest_log
FOR SELECT USING (user_id = auth.uid());
-- Only system (via service role) can insert digest logs
-- This will be done by the Cloud Run job
-- ============================================
-- 3. HELPER FUNCTIONS
-- ============================================
-- Function to get last digest sent date for a user
CREATE OR REPLACE FUNCTION public.get_last_digest_sent(
p_user_id UUID,
p_digest_type VARCHAR DEFAULT 'weekly'
)
RETURNS TIMESTAMPTZ AS $$
DECLARE
v_last_sent TIMESTAMPTZ;
BEGIN
SELECT MAX(sent_at) INTO v_last_sent
FROM public.email_digest_log
WHERE user_id = p_user_id
AND digest_type = p_digest_type
AND status = 'sent';
RETURN v_last_sent;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Function to check if user is due for a digest
CREATE OR REPLACE FUNCTION public.is_user_due_for_digest(
p_user_id UUID,
p_digest_type VARCHAR DEFAULT 'weekly'
)
RETURNS BOOLEAN AS $$
DECLARE
v_last_sent TIMESTAMPTZ;
v_threshold TIMESTAMPTZ;
BEGIN
-- Get last sent time
v_last_sent := public.get_last_digest_sent(p_user_id, p_digest_type);
-- If never sent, user is due
IF v_last_sent IS NULL THEN
RETURN TRUE;
END IF;
-- Calculate threshold based on digest type
IF p_digest_type = 'daily' THEN
v_threshold := NOW() - INTERVAL '20 hours'; -- Allow some buffer
ELSIF p_digest_type = 'weekly' THEN
v_threshold := NOW() - INTERVAL '6 days 20 hours';
ELSE
RETURN FALSE;
END IF;
-- User is due if last sent was before threshold
RETURN v_last_sent < v_threshold;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- ============================================
-- SUCCESS MESSAGE
-- ============================================
DO $$
BEGIN
RAISE NOTICE 'Email digest system migration completed successfully!';
RAISE NOTICE 'Created email_digest_log table with RLS policies';
RAISE NOTICE 'Created helper functions: get_last_digest_sent(), is_user_due_for_digest()';
RAISE NOTICE 'Leverages existing notification_preferences.email_digest_frequency field';
END $$;
