create extension if not exists pgcrypto;
create table if not exists public.notice_admins (
user_id uuid primary key references auth.users(id) on delete cascade,
created_at timestamptz not null default now()
);
alter table public.notice_admins enable row level security;
revoke all on public.notice_admins from anon, authenticated;
create or replace function public.is_notice_admin()
returns boolean
language sql
stable
security definer
set search_path = ''
as $$
select exists (
select 1 from public.notice_admins
where user_id = (select auth.uid())
);
$$;
revoke all on function public.is_notice_admin() from public, anon;
grant execute on function public.is_notice_admin() to authenticated;
create table if not exists public.notices (
id uuid primary key default gen_random_uuid(),
title text not null check (char_length(title) between 1 and 180),
description text not null check (char_length(description) between 1 and 700),
category text not null check (category in ('latest_news','upcoming_event','general','academic','fees','meeting','urgent')),
event_date date,
publish_date timestamptz not null default now(),
expiry_date date,
priority text not null default 'normal' check (priority in ('normal','important','urgent')),
status text not null default 'draft' check (status in ('draft','published','archived')),
sort_order integer not null default 0 check (sort_order >= 0),
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
created_by uuid references auth.users(id),
updated_by uuid references auth.users(id)
);
create index if not exists notices_public_index on public.notices (status, category, priority, publish_date desc);
create index if not exists notices_expiry_index on public.notices (expiry_date);
create table if not exists public.notice_activity_log (
id bigint generated always as identity primary key,
admin_user uuid,
admin_email text,
action text not null check (action in ('create','edit','publish','unpublish','archive','delete')),
notice_id uuid,
notice_title text not null,
performed_at timestamptz not null default now()
);
alter table public.notices enable row level security;
alter table public.notice_activity_log enable row level security;
revoke all on public.notices from anon, authenticated;
grant select on public.notices to anon, authenticated;
grant insert, update, delete on public.notices to authenticated;
revoke all on public.notice_activity_log from anon, authenticated;
grant select on public.notice_activity_log to authenticated;
drop policy if exists "Public can read current published notices" on public.notices;
create policy "Public can read current published notices" on public.notices
for select to anon, authenticated
using (
status = 'published'
and publish_date <= now()
and (expiry_date is null or expiry_date >= current_date)
);
drop policy if exists "Admins can read all notices" on public.notices;
create policy "Admins can read all notices" on public.notices
for select to authenticated using ((select public.is_notice_admin()));
drop policy if exists "Admins can create notices" on public.notices;
create policy "Admins can create notices" on public.notices
for insert to authenticated with check ((select public.is_notice_admin()));
drop policy if exists "Admins can update notices" on public.notices;
create policy "Admins can update notices" on public.notices
for update to authenticated
using ((select public.is_notice_admin()))
with check ((select public.is_notice_admin()));
drop policy if exists "Admins can delete notices" on public.notices;
create policy "Admins can delete notices" on public.notices
for delete to authenticated using ((select public.is_notice_admin()));
drop policy if exists "Admins can read notice activity" on public.notice_activity_log;
create policy "Admins can read notice activity" on public.notice_activity_log
for select to authenticated using ((select public.is_notice_admin()));
create or replace function public.set_notice_audit_fields()
returns trigger language plpgsql set search_path = '' as $$
begin
new.updated_at = now();
new.updated_by = (select auth.uid());
return new;
end;
$$;
drop trigger if exists set_notice_audit_fields on public.notices;
create trigger set_notice_audit_fields before update on public.notices
for each row execute function public.set_notice_audit_fields();
create or replace function public.log_notice_activity()
returns trigger language plpgsql security definer set search_path = '' as $$
declare
activity_action text;
activity_notice_id uuid;
activity_notice_title text;
begin
if tg_op = 'INSERT' then
activity_action := 'create'; activity_notice_id := new.id; activity_notice_title := new.title;
elsif tg_op = 'DELETE' then
activity_action := 'delete'; activity_notice_id := old.id; activity_notice_title := old.title;
else
activity_notice_id := new.id; activity_notice_title := new.title;
if old.status is distinct from new.status and new.status = 'published' then activity_action := 'publish';
elsif old.status is distinct from new.status and old.status = 'published' and new.status = 'draft' then activity_action := 'unpublish';
elsif old.status is distinct from new.status and new.status = 'archived' then activity_action := 'archive';
else activity_action := 'edit';
end if;
end if;
insert into public.notice_activity_log (admin_user, admin_email, action, notice_id, notice_title)
values ((select auth.uid()), (select auth.jwt()->>'email'), activity_action, activity_notice_id, activity_notice_title);
if tg_op = 'DELETE' then return old; end if;
return new;
end;
$$;
revoke all on function public.log_notice_activity() from public, anon, authenticated;
drop trigger if exists log_notice_activity on public.notices;
create trigger log_notice_activity after insert or update or delete on public.notices
for each row execute function public.log_notice_activity();
do $$ begin
if not exists (
select 1 from pg_publication_tables
where pubname = 'supabase_realtime' and schemaname = 'public' and tablename = 'notices'
) then
alter publication supabase_realtime add table public.notices;
end if;
end $$;
-- After creating the administrator in Authentication > Users, replace the UUID below and run this line separately:
-- insert into public.notice_admins (user_id) values ('AUTH_USER_UUID');
