-- Migration: Create user_news_preferences table
-- Allows users to customize their news feed in the visualizer
CREATE TABLE IF NOT EXISTS user_news_preferences (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
-- Source preferences (stores IDs of disabled sources)
-- Empty array = all sources enabled (default)
disabled_sources TEXT[] DEFAULT '{}',
-- Regional preferences (stores province codes that are DISABLED)
-- Empty array = all regions enabled (default)
disabled_regions TEXT[] DEFAULT '{}',
-- Paywall subscriptions (PRO only - hides paywall badge for these sources)
subscribed_sources TEXT[] DEFAULT '{}',
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
UNIQUE(user_id)
);
-- Create indexes for efficient lookups
CREATE INDEX IF NOT EXISTS idx_user_news_preferences_user_id ON user_news_preferences(user_id);
-- Enable RLS
ALTER TABLE user_news_preferences ENABLE ROW LEVEL SECURITY;
-- Users can read their own preferences
CREATE POLICY "Users can view own news preferences"
ON user_news_preferences FOR SELECT
TO authenticated
USING (auth.uid() = user_id);
-- Users can insert their own preferences
CREATE POLICY "Users can insert own news preferences"
ON user_news_preferences FOR INSERT
TO authenticated
WITH CHECK (auth.uid() = user_id);
-- Users can update their own preferences
CREATE POLICY "Users can update own news preferences"
ON user_news_preferences FOR UPDATE
TO authenticated
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);
-- Users can delete their own preferences
CREATE POLICY "Users can delete own news preferences"
ON user_news_preferences FOR DELETE
TO authenticated
USING (auth.uid() = user_id);
-- Auto-update timestamp
CREATE OR REPLACE FUNCTION update_user_news_preferences_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER user_news_preferences_updated_at
BEFORE UPDATE ON user_news_preferences
FOR EACH ROW
EXECUTE FUNCTION update_user_news_preferences_updated_at();
-- Comments
COMMENT ON TABLE user_news_preferences IS 'User preferences for customizing news feed sources and regions';
COMMENT ON COLUMN user_news_preferences.disabled_sources IS 'Array of news source IDs the user has disabled';
COMMENT ON COLUMN user_news_preferences.disabled_regions IS 'Array of province codes the user has disabled';
COMMENT ON COLUMN user_news_preferences.subscribed_sources IS 'Array of source IDs user has subscriptions to (PRO only, hides paywall badge)';
