-- ============================================
-- Inline Advertisements System
-- ============================================
-- Self-hosted advertisement system with Canadian geo-targeting
-- Date: 2026-01-29
-- Create advertisements table
CREATE TABLE IF NOT EXISTS public.advertisements (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-- Content
title TEXT NOT NULL,
description TEXT,
image_url TEXT,
cta_text TEXT DEFAULT 'Learn More',
cta_url TEXT NOT NULL,
advertiser_name TEXT NOT NULL,
-- Targeting
target_provinces TEXT[] DEFAULT '{}', -- Empty = all provinces (e.g., ['ON', 'BC', 'QC'])
target_postal_prefixes TEXT[] DEFAULT '{}', -- FSA codes like 'M5V', 'K1A'
target_languages TEXT[] DEFAULT ARRAY['en', 'fr'],
-- Scheduling
start_date TIMESTAMPTZ,
end_date TIMESTAMPTZ,
is_active BOOLEAN DEFAULT true,
-- Placement
placement TEXT DEFAULT 'feed_inline', -- feed_inline, feed_top, carousel
priority INT DEFAULT 50, -- Higher = shown first (0-100)
frequency INT DEFAULT 7, -- Show every N items
show_to_all_tiers BOOLEAN DEFAULT false, -- Override tier restrictions (show to PRO users)
-- Tracking
impressions INT DEFAULT 0,
clicks INT DEFAULT 0,
-- Timestamps
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
created_by UUID REFERENCES auth.users(id),
updated_by UUID REFERENCES auth.users(id)
);
-- Enable RLS
ALTER TABLE public.advertisements ENABLE ROW LEVEL SECURITY;
-- Index for efficient targeting queries
CREATE INDEX IF NOT EXISTS idx_ads_active_targeting
ON public.advertisements(is_active, start_date, end_date)
WHERE is_active = true;
CREATE INDEX IF NOT EXISTS idx_ads_placement
ON public.advertisements(placement)
WHERE is_active = true;
-- RLS Policies
-- Anyone can read active ads (for displaying in feed)
CREATE POLICY "Anyone can read active advertisements"
ON public.advertisements FOR SELECT
USING (
is_active = true
AND (start_date IS NULL OR start_date <= NOW())
AND (end_date IS NULL OR end_date >= NOW())
);
-- Admins can do everything
CREATE POLICY "Admins can manage all advertisements"
ON public.advertisements FOR ALL
USING (
EXISTS (
SELECT 1 FROM public.user_profiles
WHERE id = auth.uid() AND is_admin = true
)
);
-- Function to track ad events (impression or click)
CREATE OR REPLACE FUNCTION public.track_ad_event(
p_ad_id UUID,
p_event_type TEXT -- 'impression' or 'click'
)
RETURNS void AS $$
BEGIN
IF p_event_type = 'impression' THEN
UPDATE public.advertisements
SET impressions = impressions + 1,
updated_at = NOW()
WHERE id = p_ad_id;
ELSIF p_event_type = 'click' THEN
UPDATE public.advertisements
SET clicks = clicks + 1,
updated_at = NOW()
WHERE id = p_ad_id;
END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Grant execute to authenticated users (for tracking)
GRANT EXECUTE ON FUNCTION public.track_ad_event(UUID, TEXT) TO authenticated;
-- Function to get targeted ads for a user
CREATE OR REPLACE FUNCTION public.get_targeted_ads(
p_postal_code TEXT DEFAULT NULL,
p_province TEXT DEFAULT NULL,
p_locale TEXT DEFAULT 'en',
p_placement TEXT DEFAULT 'feed_inline',
p_limit INT DEFAULT 5
)
RETURNS TABLE (
id UUID,
title TEXT,
description TEXT,
image_url TEXT,
cta_text TEXT,
cta_url TEXT,
advertiser_name TEXT,
priority INT,
show_to_all_tiers BOOLEAN
) AS $$
DECLARE
v_fsa TEXT;
BEGIN
-- Extract FSA (first 3 chars) from postal code
v_fsa := UPPER(SUBSTRING(p_postal_code FROM 1 FOR 3));
RETURN QUERY
SELECT
a.id,
a.title,
a.description,
a.image_url,
a.cta_text,
a.cta_url,
a.advertiser_name,
a.priority,
a.show_to_all_tiers
FROM public.advertisements a
WHERE a.is_active = true
AND a.placement = p_placement
AND (a.start_date IS NULL OR a.start_date <= NOW())
AND (a.end_date IS NULL OR a.end_date >= NOW())
-- Language targeting
AND (array_length(a.target_languages, 1) IS NULL OR p_locale = ANY(a.target_languages))
-- Geographic targeting (if any targeting is set, must match)
AND (
-- No geo targeting = show to everyone
(array_length(a.target_provinces, 1) IS NULL AND array_length(a.target_postal_prefixes, 1) IS NULL)
-- Province targeting
OR (p_province IS NOT NULL AND p_province = ANY(a.target_provinces))
-- Postal prefix targeting
OR (v_fsa IS NOT NULL AND v_fsa = ANY(a.target_postal_prefixes))
)
ORDER BY a.priority DESC, random()
LIMIT p_limit;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Grant execute to everyone (ads are public)
GRANT EXECUTE ON FUNCTION public.get_targeted_ads(TEXT, TEXT, TEXT, TEXT, INT) TO anon, authenticated;
-- Add comments
COMMENT ON TABLE public.advertisements IS 'Self-hosted inline advertisement campaigns with Canadian geo-targeting';
COMMENT ON COLUMN public.advertisements.target_provinces IS 'Province codes for targeting (e.g., ON, BC, QC). Empty = all provinces';
COMMENT ON COLUMN public.advertisements.target_postal_prefixes IS 'FSA postal code prefixes for targeting (e.g., M5V, K1A). Empty = all areas';
COMMENT ON COLUMN public.advertisements.priority IS 'Display priority (0-100). Higher = shown first';
COMMENT ON COLUMN public.advertisements.frequency IS 'Show ad every N items in feed';
COMMENT ON COLUMN public.advertisements.show_to_all_tiers IS 'If true, show to all users including PRO subscribers (for important announcements)';
