-- ============================================================
-- The Potter's College — Content Tables (News, Events, Gallery)
-- Migration: 005_content.sql
--
-- PURPOSE:
-- Replace hardcoded placeholder data on:
-- /media/news → news_articles
-- /media/events → events
-- /gallery → gallery_items
--
-- Plus a Supabase Storage bucket "media" where uploaded
-- photos live. Public can READ published rows + view all
-- images. Only admins can create/edit/delete.
-- ============================================================
-- ══════════════════════════════════════════════════════════════
-- TABLE: news_articles
-- One row per news article. Admin writes via /portal/admin/content.
-- ══════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS public.news_articles (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-- URL-safe identifier used in /media/news/[slug]
slug TEXT NOT NULL UNIQUE,
-- Headline & preview
title TEXT NOT NULL,
excerpt TEXT, -- short summary shown on the list page
body TEXT, -- full article text (plain or basic HTML)
-- Visual + categorisation
category TEXT NOT NULL DEFAULT 'announcement'
CHECK (category IN ('announcement', 'achievement', 'event', 'academic', 'general')),
image_url TEXT, -- public URL of the hero image in storage
-- Publishing workflow — admin toggles draft ↔ published
status TEXT NOT NULL DEFAULT 'draft'
CHECK (status IN ('draft', 'published', 'archived')),
-- When the article should appear publicly (allows scheduling later)
published_at TIMESTAMPTZ,
-- Authorship
author_id UUID REFERENCES public.profiles(id) ON DELETE SET NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_news_status_published
ON public.news_articles (status, published_at DESC NULLS LAST);
-- ══════════════════════════════════════════════════════════════
-- TABLE: events
-- One row per upcoming or past event in the school calendar.
-- ══════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS public.events (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
slug TEXT NOT NULL UNIQUE,
title TEXT NOT NULL,
description TEXT,
location TEXT, -- e.g. "School Hall, Secondary Campus"
-- Timing
start_date DATE NOT NULL,
end_date DATE, -- nullable for single-day events
start_time TIME, -- optional ("9:00 AM")
end_time TIME,
category TEXT NOT NULL DEFAULT 'general'
CHECK (category IN ('academic', 'sports', 'cultural', 'general', 'religious')),
image_url TEXT,
-- Workflow
status TEXT NOT NULL DEFAULT 'published'
CHECK (status IN ('draft', 'published', 'cancelled', 'archived')),
created_by UUID REFERENCES public.profiles(id) ON DELETE SET NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_events_status_start
ON public.events (status, start_date DESC);
-- ══════════════════════════════════════════════════════════════
-- TABLE: gallery_items
-- One row per photo in the public gallery.
-- ══════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS public.gallery_items (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
title TEXT NOT NULL,
caption TEXT, -- optional longer description
image_url TEXT NOT NULL, -- public URL in storage
category TEXT NOT NULL DEFAULT 'campus_life'
CHECK (category IN ('events', 'sports', 'academic', 'campus_life', 'cultural')),
-- Manual ordering — lower numbers appear first
sort_order INTEGER NOT NULL DEFAULT 0,
-- Hide without deleting
visible BOOLEAN NOT NULL DEFAULT true,
uploaded_by UUID REFERENCES public.profiles(id) ON DELETE SET NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_gallery_visible_sort
ON public.gallery_items (visible, sort_order, created_at DESC);
-- ══════════════════════════════════════════════════════════════
-- TRIGGERS: auto-touch updated_at
-- (Reuses touch_updated_at() from 004_forms.sql)
-- ══════════════════════════════════════════════════════════════
DROP TRIGGER IF EXISTS news_touch_updated_at ON public.news_articles;
CREATE TRIGGER news_touch_updated_at
BEFORE UPDATE ON public.news_articles
FOR EACH ROW EXECUTE FUNCTION public.touch_updated_at();
DROP TRIGGER IF EXISTS events_touch_updated_at ON public.events;
CREATE TRIGGER events_touch_updated_at
BEFORE UPDATE ON public.events
FOR EACH ROW EXECUTE FUNCTION public.touch_updated_at();
DROP TRIGGER IF EXISTS gallery_touch_updated_at ON public.gallery_items;
CREATE TRIGGER gallery_touch_updated_at
BEFORE UPDATE ON public.gallery_items
FOR EACH ROW EXECUTE FUNCTION public.touch_updated_at();
-- ══════════════════════════════════════════════════════════════
-- ROW LEVEL SECURITY
-- Pattern: public can SELECT only published/visible rows.
-- Admins can do everything (server uses service role anyway).
-- ══════════════════════════════════════════════════════════════
ALTER TABLE public.news_articles ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.events ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.gallery_items ENABLE ROW LEVEL SECURITY;
-- Reusable admin check expression — duplicated here so policies are self-contained
-- ── news_articles ─────────────────────────────────────────────
DROP POLICY IF EXISTS news_public_read ON public.news_articles;
CREATE POLICY news_public_read
ON public.news_articles
FOR SELECT
TO public
USING (status = 'published');
DROP POLICY IF EXISTS news_admin_all ON public.news_articles;
CREATE POLICY news_admin_all
ON public.news_articles
FOR ALL
TO authenticated
USING (
EXISTS (SELECT 1 FROM public.profiles WHERE id = auth.uid() AND role = 'admin')
)
WITH CHECK (
EXISTS (SELECT 1 FROM public.profiles WHERE id = auth.uid() AND role = 'admin')
);
-- ── events ────────────────────────────────────────────────────
DROP POLICY IF EXISTS events_public_read ON public.events;
CREATE POLICY events_public_read
ON public.events
FOR SELECT
TO public
USING (status IN ('published', 'cancelled')); -- show cancelled so users see "Cancelled" badge
DROP POLICY IF EXISTS events_admin_all ON public.events;
CREATE POLICY events_admin_all
ON public.events
FOR ALL
TO authenticated
USING (
EXISTS (SELECT 1 FROM public.profiles WHERE id = auth.uid() AND role = 'admin')
)
WITH CHECK (
EXISTS (SELECT 1 FROM public.profiles WHERE id = auth.uid() AND role = 'admin')
);
-- ── gallery_items ─────────────────────────────────────────────
DROP POLICY IF EXISTS gallery_public_read ON public.gallery_items;
CREATE POLICY gallery_public_read
ON public.gallery_items
FOR SELECT
TO public
USING (visible = true);
DROP POLICY IF EXISTS gallery_admin_all ON public.gallery_items;
CREATE POLICY gallery_admin_all
ON public.gallery_items
FOR ALL
TO authenticated
USING (
EXISTS (SELECT 1 FROM public.profiles WHERE id = auth.uid() AND role = 'admin')
)
WITH CHECK (
EXISTS (SELECT 1 FROM public.profiles WHERE id = auth.uid() AND role = 'admin')
);
-- ══════════════════════════════════════════════════════════════
-- STORAGE BUCKET: "media"
-- Holds all uploaded images for news, events, and gallery.
-- Public bucket — files are world-readable once their URL is shared.
-- Writes are admin-only (server uses service role, but RLS adds defence-in-depth).
-- ══════════════════════════════════════════════════════════════
INSERT INTO storage.buckets (id, name, public)
VALUES ('media', 'media', true)
ON CONFLICT (id) DO NOTHING;
-- Anyone can read media files
DROP POLICY IF EXISTS media_public_read ON storage.objects;
CREATE POLICY media_public_read
ON storage.objects
FOR SELECT
TO public
USING (bucket_id = 'media');
-- Only admins can upload
DROP POLICY IF EXISTS media_admin_insert ON storage.objects;
CREATE POLICY media_admin_insert
ON storage.objects
FOR INSERT
TO authenticated
WITH CHECK (
bucket_id = 'media' AND
EXISTS (SELECT 1 FROM public.profiles WHERE id = auth.uid() AND role = 'admin')
);
-- Only admins can update / delete
DROP POLICY IF EXISTS media_admin_update ON storage.objects;
CREATE POLICY media_admin_update
ON storage.objects
FOR UPDATE
TO authenticated
USING (
bucket_id = 'media' AND
EXISTS (SELECT 1 FROM public.profiles WHERE id = auth.uid() AND role = 'admin')
);
DROP POLICY IF EXISTS media_admin_delete ON storage.objects;
CREATE POLICY media_admin_delete
ON storage.objects
FOR DELETE
TO authenticated
USING (
bucket_id = 'media' AND
EXISTS (SELECT 1 FROM public.profiles WHERE id = auth.uid() AND role = 'admin')
);
All articlesPublished 15/05/2026