mirror of
https://github.com/renee-png/acmcc.git
synced 2026-06-21 01:40:01 +00:00
61 lines
2.0 KiB
SQL
61 lines
2.0 KiB
SQL
|
|
-- Announcements table
|
|
CREATE TABLE public.announcements (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
title TEXT NOT NULL,
|
|
content TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'active',
|
|
visibility TEXT NOT NULL DEFAULT 'all',
|
|
created_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
ALTER TABLE public.announcements ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY "Authenticated users can read active announcements"
|
|
ON public.announcements FOR SELECT TO authenticated
|
|
USING (status = 'active');
|
|
|
|
CREATE POLICY "Authenticated users can insert announcements"
|
|
ON public.announcements FOR INSERT TO authenticated
|
|
WITH CHECK (true);
|
|
|
|
CREATE POLICY "Authors can update their announcements"
|
|
ON public.announcements FOR UPDATE TO authenticated
|
|
USING (created_by = auth.uid());
|
|
|
|
CREATE POLICY "Authors can delete their announcements"
|
|
ON public.announcements FOR DELETE TO authenticated
|
|
USING (created_by = auth.uid());
|
|
|
|
-- Reminders table
|
|
CREATE TABLE public.reminders (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
title TEXT NOT NULL,
|
|
description TEXT,
|
|
due_date DATE NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'pending',
|
|
created_by UUID REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
ALTER TABLE public.reminders ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY "Users can read own reminders"
|
|
ON public.reminders FOR SELECT TO authenticated
|
|
USING (created_by = auth.uid());
|
|
|
|
CREATE POLICY "Users can create reminders"
|
|
ON public.reminders FOR INSERT TO authenticated
|
|
WITH CHECK (created_by = auth.uid());
|
|
|
|
CREATE POLICY "Users can update own reminders"
|
|
ON public.reminders FOR UPDATE TO authenticated
|
|
USING (created_by = auth.uid());
|
|
|
|
CREATE POLICY "Users can delete own reminders"
|
|
ON public.reminders FOR DELETE TO authenticated
|
|
USING (created_by = auth.uid());
|