-- Section ratings: users rate sections on a 1-5 scale
-- One rating per user per section (upsert pattern)
CREATE TABLE IF NOT EXISTS section_ratings (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
module_id integer NOT NULL,
section_id text NOT NULL,
rating integer NOT NULL CHECK (rating BETWEEN 1 AND 5),
created_at timestamptz DEFAULT now(),
updated_at timestamptz DEFAULT now(),
UNIQUE(user_id, module_id, section_id)
);
CREATE INDEX IF NOT EXISTS idx_section_ratings_section ON section_ratings(module_id, section_id);
CREATE INDEX IF NOT EXISTS idx_section_ratings_user ON section_ratings(user_id);
ALTER TABLE section_ratings ENABLE ROW LEVEL SECURITY;
-- Users can read all ratings (for computing averages) but only write their own
CREATE POLICY "Anyone can read ratings"
ON section_ratings FOR SELECT
USING (true);
CREATE POLICY "Users can insert their own rating"
ON section_ratings FOR INSERT
WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can update their own rating"
ON section_ratings FOR UPDATE
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can delete their own rating"
ON section_ratings FOR DELETE
USING (auth.uid() = user_id);
-- Auto-update updated_at
CREATE OR REPLACE FUNCTION update_section_rating_timestamp()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS section_ratings_updated_at ON section_ratings;
CREATE TRIGGER section_ratings_updated_at
BEFORE UPDATE ON section_ratings
FOR EACH ROW
EXECUTE FUNCTION update_section_rating_timestamp();
-- Aggregated view for public display
CREATE OR REPLACE VIEW section_ratings_summary AS
SELECT
module_id,
section_id,
COUNT(*)::int AS count,
ROUND(AVG(rating)::numeric, 2)::float AS average,
COUNT(*) FILTER (WHERE rating = 5)::int AS count_5,
COUNT(*) FILTER (WHERE rating = 4)::int AS count_4,
COUNT(*) FILTER (WHERE rating = 3)::int AS count_3,
COUNT(*) FILTER (WHERE rating = 2)::int AS count_2,
COUNT(*) FILTER (WHERE rating = 1)::int AS count_1
FROM section_ratings
GROUP BY module_id, section_id;
GRANT SELECT ON section_ratings_summary TO anon, authenticated;
-- Pre/post training assessment
--
-- Stores learner responses to the pre-training and post-training
-- assessments. Each user takes each assessment type once.
-- Responses are stored as JSONB for flexibility (mix of Likert,
-- multiple choice, and scenario-based items).
CREATE TABLE IF NOT EXISTS public.assessments (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
type TEXT NOT NULL CHECK (type IN ('pre', 'post')),
responses JSONB NOT NULL DEFAULT '[]'::jsonb,
score INTEGER, -- computed total score (nullable until scored)
duration_s INTEGER, -- time taken in seconds
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
-- Each user can only take each assessment type once
UNIQUE (user_id, type)
);
CREATE INDEX IF NOT EXISTS idx_assessments_user
ON public.assessments (user_id, type);
-- RLS
ALTER TABLE public.assessments ENABLE ROW LEVEL SECURITY;
-- Users can read their own assessments
CREATE POLICY "Users read own assessments" ON public.assessments
FOR SELECT USING (user_id = auth.uid());
-- Users can insert their own assessment (once per type)
CREATE POLICY "Users insert own assessment" ON public.assessments
FOR INSERT WITH CHECK (user_id = auth.uid());
-- Users cannot update or delete assessments (immutable once submitted)
-- Admins can read all assessments (for reporting)
CREATE POLICY "Admins read all assessments" ON public.assessments
FOR SELECT USING (
EXISTS (SELECT 1 FROM admin_users WHERE user_id = auth.uid())
);
COMMENT ON TABLE public.assessments IS 'Pre/post training assessments for measuring learning delta';
COMMENT ON COLUMN public.assessments.type IS 'pre = before Module 0, post = after Module 6';
COMMENT ON COLUMN public.assessments.responses IS 'Array of {itemId, answer, correct?, score} objects';
-- Open section comments to all visitors
--
-- Previously, only admins could read or write section_comments. This
-- migration relaxes RLS so that:
-- - ANY visitor (even anonymous) can SELECT every comment
-- - Any logged-in (authenticated) user can INSERT comments under
-- their own user_id
-- - Authenticated users can UPDATE / DELETE only their own comments
--
-- Admins keep their elevated privileges:
-- - UPDATE any comment (used by the moderation UI to change status)
-- - DELETE any comment (moderation)
-- Drop the old admin-only policies
DROP POLICY IF EXISTS "Admins can read all comments" ON section_comments;
DROP POLICY IF EXISTS "Admins can insert comments" ON section_comments;
DROP POLICY IF EXISTS "Admins can update own comments" ON section_comments;
DROP POLICY IF EXISTS "Admins can delete own comments" ON section_comments;
-- New permissive policies
-- Anyone (anonymous or authenticated) can read comments
CREATE POLICY "Anyone can read comments" ON section_comments
FOR SELECT
TO anon, authenticated
USING (true);
CREATE POLICY "Authenticated users can insert their own comments" ON section_comments
FOR INSERT
TO authenticated
WITH CHECK (user_id = auth.uid());
CREATE POLICY "Users can update their own comments" ON section_comments
FOR UPDATE
TO authenticated
USING (user_id = auth.uid())
WITH CHECK (user_id = auth.uid());
CREATE POLICY "Users can delete their own comments" ON section_comments
FOR DELETE
TO authenticated
USING (user_id = auth.uid());
-- Admin override: admins can UPDATE / DELETE any comment (for moderation)
CREATE POLICY "Admins can update any comment" ON section_comments
FOR UPDATE
TO authenticated
USING (EXISTS (SELECT 1 FROM admin_users WHERE user_id = auth.uid()))
WITH CHECK (EXISTS (SELECT 1 FROM admin_users WHERE user_id = auth.uid()));
CREATE POLICY "Admins can delete any comment" ON section_comments
FOR DELETE
TO authenticated
USING (EXISTS (SELECT 1 FROM admin_users WHERE user_id = auth.uid()));
COMMENT ON TABLE section_comments IS 'User comments on module sections (anyone can read; authenticated users can post; admins can moderate)';
-- ============================================================================
-- 013_community_polls.sql
-- Community opinion polls (no-right-answer) — capture & aggregate user choices
-- across the platform. Used for the introductory poll, self-immunity check
-- aggregates, etc.
-- ============================================================================
-- ----------------------------------------------------------------------------
-- Table: one row per (poll, user). Anonymous to admins via the aggregate view.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS public.community_poll_responses (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
poll_id TEXT NOT NULL,
option_id TEXT NOT NULL,
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
locale TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
-- One vote per user per poll (a re-vote updates the option)
CONSTRAINT community_poll_responses_user_poll_unique UNIQUE (poll_id, user_id)
);
CREATE INDEX IF NOT EXISTS idx_cpr_poll ON public.community_poll_responses (poll_id);
CREATE INDEX IF NOT EXISTS idx_cpr_user ON public.community_poll_responses (user_id);
COMMENT ON TABLE public.community_poll_responses IS 'Per-user vote on community opinion polls (no-right-answer). One row per (poll_id, user_id).';
COMMENT ON COLUMN public.community_poll_responses.poll_id IS 'Logical poll identifier (e.g. "module0.responsibility")';
COMMENT ON COLUMN public.community_poll_responses.option_id IS 'Selected option key (e.g. "individuals")';
COMMENT ON COLUMN public.community_poll_responses.locale IS 'Locale at the time of voting — useful for cross-cultural analysis';
-- Auto-update updated_at on UPDATE
CREATE OR REPLACE FUNCTION public.touch_community_poll_responses()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$;
DROP TRIGGER IF EXISTS trg_touch_cpr ON public.community_poll_responses;
CREATE TRIGGER trg_touch_cpr
BEFORE UPDATE ON public.community_poll_responses
FOR EACH ROW EXECUTE FUNCTION public.touch_community_poll_responses();
-- ----------------------------------------------------------------------------
-- RLS: a user can read & write only their own row. Aggregated stats are
-- exposed via the SECURITY DEFINER view below (no individual data leaks).
-- ----------------------------------------------------------------------------
ALTER TABLE public.community_poll_responses ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Users see own vote" ON public.community_poll_responses;
CREATE POLICY "Users see own vote" ON public.community_poll_responses
FOR SELECT USING (user_id = auth.uid());
DROP POLICY IF EXISTS "Users insert own vote" ON public.community_poll_responses;
CREATE POLICY "Users insert own vote" ON public.community_poll_responses
FOR INSERT WITH CHECK (user_id = auth.uid());
DROP POLICY IF EXISTS "Users update own vote" ON public.community_poll_responses;
CREATE POLICY "Users update own vote" ON public.community_poll_responses
FOR UPDATE USING (user_id = auth.uid()) WITH CHECK (user_id = auth.uid());
DROP POLICY IF EXISTS "Users delete own vote" ON public.community_poll_responses;
CREATE POLICY "Users delete own vote" ON public.community_poll_responses
FOR DELETE USING (user_id = auth.uid());
-- ----------------------------------------------------------------------------
-- Aggregate view: anonymous counts per (poll, option). Readable by anyone
-- (including anon) — intentionally public so the bars can render before login.
-- ----------------------------------------------------------------------------
DROP VIEW IF EXISTS public.community_poll_results;
CREATE VIEW public.community_poll_results
WITH (security_invoker = true)
AS
SELECT
poll_id,
option_id,
COUNT(*)::INTEGER AS count
FROM public.community_poll_responses
GROUP BY poll_id, option_id;
GRANT SELECT ON public.community_poll_results TO anon, authenticated;
COMMENT ON VIEW public.community_poll_results IS 'Public aggregate of community_poll_responses. No individual user data exposed.';
-- ----------------------------------------------------------------------------
-- RPC: get the totals AND the caller''s own vote in a single round-trip
-- ----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.get_community_poll(p_poll_id TEXT)
RETURNS JSONB
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
v_results JSONB;
v_total INTEGER;
v_my_vote TEXT;
BEGIN
-- Aggregate counts per option
SELECT
COALESCE(jsonb_object_agg(option_id, count), '{}'::jsonb),
COALESCE(SUM(count), 0)
INTO v_results, v_total
FROM public.community_poll_results
WHERE poll_id = p_poll_id;
-- Caller''s own vote (if authenticated)
IF auth.uid() IS NOT NULL THEN
SELECT option_id INTO v_my_vote
FROM public.community_poll_responses
WHERE poll_id = p_poll_id AND user_id = auth.uid();
END IF;
RETURN jsonb_build_object(
'poll_id', p_poll_id,
'counts', v_results,
'total', v_total,
'my_vote', v_my_vote
);
END;
$$;
GRANT EXECUTE ON FUNCTION public.get_community_poll(TEXT) TO anon, authenticated;
-- ============================================================================
-- 012_notifications.sql
-- Notification system: in-app fan-out on new section_comments
-- - Per-user notifications with RLS
-- - Trigger fans out new comments to all admins (+ parent author on replies)
-- - Realtime publication enabled so the client can subscribe to inserts
-- ============================================================================
-- ----------------------------------------------------------------------------
-- Table
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS public.notifications (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
type TEXT NOT NULL, -- 'comment.new' | 'comment.reply'
payload JSONB NOT NULL DEFAULT '{}'::jsonb, -- structured event data
read_at TIMESTAMPTZ, -- NULL = unread
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_notifications_user_unread
ON public.notifications (user_id, created_at DESC)
WHERE read_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_notifications_user_all
ON public.notifications (user_id, created_at DESC);
COMMENT ON TABLE public.notifications IS 'Per-user in-app notifications (e.g. new comments, replies)';
COMMENT ON COLUMN public.notifications.type IS 'Event type, e.g. comment.new, comment.reply';
COMMENT ON COLUMN public.notifications.payload IS 'Structured event payload (comment_id, section_id, …)';
COMMENT ON COLUMN public.notifications.read_at IS 'Timestamp marking the notification as read; NULL = unread';
-- ----------------------------------------------------------------------------
-- Row Level Security
-- ----------------------------------------------------------------------------
ALTER TABLE public.notifications ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Users see own notifications" ON public.notifications;
CREATE POLICY "Users see own notifications" ON public.notifications
FOR SELECT USING (user_id = auth.uid());
DROP POLICY IF EXISTS "Users update own notifications" ON public.notifications;
CREATE POLICY "Users update own notifications" ON public.notifications
FOR UPDATE USING (user_id = auth.uid())
WITH CHECK (user_id = auth.uid());
DROP POLICY IF EXISTS "Users delete own notifications" ON public.notifications;
CREATE POLICY "Users delete own notifications" ON public.notifications
FOR DELETE USING (user_id = auth.uid());
-- Inserts are done by SECURITY DEFINER trigger only — no direct insert policy.
-- ----------------------------------------------------------------------------
-- Trigger function: fan out a new comment to admins (+ parent author on reply)
-- ----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.notify_on_new_comment()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
recipient_id UUID;
parent_author_id UUID;
notif_type TEXT;
notif_payload JSONB;
BEGIN
notif_type := CASE WHEN NEW.parent_id IS NOT NULL THEN 'comment.reply' ELSE 'comment.new' END;
notif_payload := jsonb_build_object(
'comment_id', NEW.id,
'section_id', NEW.section_id,
'module_id', NEW.module_id,
'author_id', NEW.user_id,
'parent_id', NEW.parent_id,
'locale', NEW.locale,
'content_excerpt', LEFT(NEW.content, 240),
'created_at', NEW.created_at
);
-- Fan out to every admin except the comment author
FOR recipient_id IN
SELECT user_id FROM public.admin_users WHERE user_id <> NEW.user_id
LOOP
INSERT INTO public.notifications (user_id, type, payload)
VALUES (recipient_id, notif_type, notif_payload);
END LOOP;
-- If this is a reply, also notify the parent comment's author when they
-- are not an admin (admins are already covered by the loop above) and not
-- the same person as the replier.
IF NEW.parent_id IS NOT NULL THEN
SELECT user_id INTO parent_author_id
FROM public.section_comments
WHERE id = NEW.parent_id;
IF parent_author_id IS NOT NULL
AND parent_author_id <> NEW.user_id
AND NOT EXISTS (SELECT 1 FROM public.admin_users WHERE user_id = parent_author_id)
THEN
INSERT INTO public.notifications (user_id, type, payload)
VALUES (parent_author_id, 'comment.reply', notif_payload);
END IF;
END IF;
RETURN NEW;
END;
$$;
DROP TRIGGER IF EXISTS section_comments_notify ON public.section_comments;
CREATE TRIGGER section_comments_notify
AFTER INSERT ON public.section_comments
FOR EACH ROW
EXECUTE FUNCTION public.notify_on_new_comment();
-- ----------------------------------------------------------------------------
-- Helper RPCs
-- ----------------------------------------------------------------------------
-- Mark a single notification as read (by its owner)
CREATE OR REPLACE FUNCTION public.mark_notification_read(p_notification_id UUID)
RETURNS BOOLEAN
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
UPDATE public.notifications
SET read_at = NOW()
WHERE id = p_notification_id
AND user_id = auth.uid()
AND read_at IS NULL;
RETURN FOUND;
END;
$$;
-- Mark all the caller's notifications as read
CREATE OR REPLACE FUNCTION public.mark_all_notifications_read()
RETURNS INTEGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
affected INTEGER;
BEGIN
UPDATE public.notifications
SET read_at = NOW()
WHERE user_id = auth.uid()
AND read_at IS NULL;
GET DIAGNOSTICS affected = ROW_COUNT;
RETURN affected;
END;
$$;
GRANT EXECUTE ON FUNCTION public.mark_notification_read(UUID) TO authenticated;
GRANT EXECUTE ON FUNCTION public.mark_all_notifications_read() TO authenticated;
-- ----------------------------------------------------------------------------
-- Realtime
-- ----------------------------------------------------------------------------
-- Add the notifications table to the supabase_realtime publication so clients
-- can subscribe to INSERTs in real time. Wrapped in DO block: ALTER PUBLICATION
-- fails if the publication does not exist (e.g. on local stripped instances).
DO $$
BEGIN
IF EXISTS (SELECT 1 FROM pg_publication WHERE pubname = 'supabase_realtime') THEN
BEGIN
ALTER PUBLICATION supabase_realtime ADD TABLE public.notifications;
EXCEPTION WHEN duplicate_object THEN
-- already added, ignore
NULL;
END;
END IF;
END $$;
# v2 git bundle
-54ff9eb82958f696a35bd98cfa6656195ea7584b feat: LanguageTool spell-checking in i18n edit mode (7 languages, auto + manual)
e19f342720708e60c72d00439002ed6d646a5ba0 HEAD
PACK �x����N�0E���YR������B� �ر���ƒkG~���q� X����ɑpk�aF�D�bB�Zu}/���~R�(81%�#� l0�*�V��pɱg���2�9�d�����`�s��%�#ث�3�|��7T� ��o�ݶm���bs��c�;���>Г���]^��0�<� ߶N*rD�?Cr�fx��V�i��PŐ8��gJ��9���/��m'�E�/%����н�}*]�
���-�vD���6��� ��w���6Q9�M��/��W��x�k�h��P�$���a�颷�_i��Bq�D�O
�������������P�S@�2�U�) �x����䚰Xd�쿳M"�l����c?G���ۃX �s�Y:>�Dƺ�,���lv���x���q�c���D� ��-�=U5��G���Z�� � g�x�340031QNM.���s���M�+)�++Me0h�t�k�Aϐ�v��I��D�7�*�LI
H�K�+L�H�vZ�'���t��
��op{O�* )J�+�I�횒Y�_� ��9����3v `�{ JM�|- ��4�<��ж��p��7qΜB0�!�x��g��v�MN
{�MM.��ԒҢ<��Ē� {
0#�(V��FA]_]s�w��" �t���l<��cO��y%#[��Ph-- Add browser_locale column to pre_registrations
ALTER TABLE public.pre_registrations
ADD COLUMN IF NOT EXISTS browser_locale VARCHAR(100);