73 lines
2.9 KiB
SQL
73 lines
2.9 KiB
SQL
-- Migration: user_topic_preferences and reading_history
|
|
-- Timestamp: 20260402050000
|
|
|
|
-- ============================================================
|
|
-- 1. TABLES
|
|
-- ============================================================
|
|
|
|
-- User Topic Preferences: tracks topics a user is interested in, updated per article view
|
|
CREATE TABLE IF NOT EXISTS public.user_topic_preferences (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
topic TEXT NOT NULL,
|
|
view_count INTEGER DEFAULT 1,
|
|
last_viewed_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Reading History: tracks articles a user has viewed
|
|
CREATE TABLE IF NOT EXISTS public.reading_history (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
article_slug TEXT NOT NULL,
|
|
article_title TEXT NOT NULL,
|
|
article_excerpt TEXT,
|
|
article_image TEXT,
|
|
article_image_alt TEXT,
|
|
article_category TEXT,
|
|
article_author TEXT,
|
|
article_read_time TEXT,
|
|
article_date TEXT,
|
|
viewed_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- ============================================================
|
|
-- 2. INDEXES
|
|
-- ============================================================
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_user_topic_preferences_user_id ON public.user_topic_preferences(user_id);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_user_topic_preferences_user_topic ON public.user_topic_preferences(user_id, topic);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_reading_history_user_id ON public.reading_history(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_reading_history_article_slug ON public.reading_history(article_slug);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_reading_history_user_article ON public.reading_history(user_id, article_slug);
|
|
|
|
-- ============================================================
|
|
-- 3. ENABLE RLS
|
|
-- ============================================================
|
|
|
|
ALTER TABLE public.user_topic_preferences ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.reading_history ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- ============================================================
|
|
-- 4. RLS POLICIES
|
|
-- ============================================================
|
|
|
|
-- User Topic Preferences: users manage their own preferences
|
|
DROP POLICY IF EXISTS "users_manage_own_topic_preferences" ON public.user_topic_preferences;
|
|
CREATE POLICY "users_manage_own_topic_preferences"
|
|
ON public.user_topic_preferences
|
|
FOR ALL
|
|
TO authenticated
|
|
USING (user_id = auth.uid())
|
|
WITH CHECK (user_id = auth.uid());
|
|
|
|
-- Reading History: users manage their own reading history
|
|
DROP POLICY IF EXISTS "users_manage_own_reading_history" ON public.reading_history;
|
|
CREATE POLICY "users_manage_own_reading_history"
|
|
ON public.reading_history
|
|
FOR ALL
|
|
TO authenticated
|
|
USING (user_id = auth.uid())
|
|
WITH CHECK (user_id = auth.uid());
|