-- ============================================
-- Post Visit Tracking Migration
-- ============================================
-- Tracks when users last visited posts to show "new replies" badges
-- Version: 1.0.0
-- Date: 2025-01-16
-- ============================================
-- 1. POST VISIT TRACKING TABLE
-- ============================================
CREATE TABLE IF NOT EXISTS public.post_visit_tracking (
user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL,
post_id UUID REFERENCES public.forum_posts(id) ON DELETE CASCADE NOT NULL,
last_visited_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
PRIMARY KEY (user_id, post_id)
);
-- Index for efficient queries
CREATE INDEX IF NOT EXISTS idx_post_visit_user_time ON public.post_visit_tracking(user_id, last_visited_at DESC);
CREATE INDEX IF NOT EXISTS idx_post_visit_post ON public.post_visit_tracking(post_id);
-- ============================================
-- 2. ROW-LEVEL SECURITY (RLS)
-- ============================================
ALTER TABLE public.post_visit_tracking ENABLE ROW LEVEL SECURITY;
-- Users can only access their own visit data
CREATE POLICY post_visit_own ON public.post_visit_tracking
FOR ALL USING (user_id = auth.uid());
-- ============================================
-- 3. HELPER FUNCTIONS
-- ============================================
-- Function to get new reply count for a single post
CREATE OR REPLACE FUNCTION public.get_new_reply_count(
p_post_id UUID,
p_user_id UUID DEFAULT auth.uid()
)
RETURNS INT AS $$
DECLARE
v_last_visited TIMESTAMPTZ;
v_new_count INT;
v_thread_root_id UUID;
BEGIN
-- Get last visit time
SELECT last_visited_at INTO v_last_visited
FROM public.post_visit_tracking
WHERE user_id = p_user_id AND post_id = p_post_id;
-- If never visited, return 0 (user hasn't seen the post yet)
IF v_last_visited IS NULL THEN
RETURN 0;
END IF;
-- Get thread root ID for this post
SELECT COALESCE(thread_root_id, id) INTO v_thread_root_id
FROM public.forum_posts
WHERE id = p_post_id;
-- Count replies in this thread created after last visit
SELECT COUNT(*) INTO v_new_count
FROM public.forum_posts
WHERE thread_root_id = v_thread_root_id
AND created_at > v_last_visited
AND is_deleted = FALSE
AND id != p_post_id; -- Exclude the post itself
RETURN v_new_count;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Batch function to get new reply counts for multiple posts
CREATE OR REPLACE FUNCTION public.get_new_reply_counts_batch(
post_ids UUID[],
p_user_id UUID DEFAULT auth.uid()
)
RETURNS TABLE(post_id UUID, new_count INT) AS $$
BEGIN
RETURN QUERY
SELECT
p.id AS post_id,
(
SELECT COUNT(*)::INT
FROM public.forum_posts replies
LEFT JOIN public.post_visit_tracking pvt ON pvt.post_id = p.id AND pvt.user_id = p_user_id
WHERE replies.thread_root_id = COALESCE(p.thread_root_id, p.id)
AND replies.created_at > COALESCE(pvt.last_visited_at, '1970-01-01'::TIMESTAMPTZ)
AND replies.is_deleted = FALSE
AND replies.id != p.id
) AS new_count
FROM public.forum_posts p
WHERE p.id = ANY(post_ids);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Function to track a post visit (upsert)
CREATE OR REPLACE FUNCTION public.track_post_visit(p_post_id UUID)
RETURNS VOID AS $$
BEGIN
INSERT INTO public.post_visit_tracking (user_id, post_id, last_visited_at)
VALUES (auth.uid(), p_post_id, NOW())
ON CONFLICT (user_id, post_id)
DO UPDATE SET last_visited_at = NOW();
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- ============================================
-- 4. CLEANUP FUNCTION (Optional)
-- ============================================
-- Function to clean up old visit tracking data (>90 days)
-- Can be called manually or via a scheduled job
CREATE OR REPLACE FUNCTION public.cleanup_old_visit_tracking()
RETURNS INT AS $$
DECLARE
v_deleted_count INT;
BEGIN
DELETE FROM public.post_visit_tracking
WHERE last_visited_at < NOW() - INTERVAL '90 days';
GET DIAGNOSTICS v_deleted_count = ROW_COUNT;
RETURN v_deleted_count;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- ============================================
-- SUCCESS MESSAGE
-- ============================================
DO $$
BEGIN
RAISE NOTICE 'Post visit tracking migration completed successfully!';
RAISE NOTICE 'Created post_visit_tracking table with composite primary key';
RAISE NOTICE 'Created helper functions: get_new_reply_count(), get_new_reply_counts_batch(), track_post_visit()';
RAISE NOTICE 'Created cleanup function: cleanup_old_visit_tracking() (call manually to remove >90 day old data)';
RAISE NOTICE 'Storage estimate: ~40 bytes per row, ~40MB for 10K users × 100 posts';
END $$;
