av-planner/supabase/migrations/20260916000000_announcements.sql

-- Site-wide announcements (organized-ideas.md §3): a dismissible banner
-- every signed-in user sees, meant as a heads-up right after a Super Admin
-- (or a specially-flagged Admin) makes a compatibility-affecting catalog
-- change, so nobody's surprised by something that already shipped.
--
-- Decided shape (discussed directly, not just inferred from the plan doc):
--   * Posting permission: every Super Admin, plus any Admin individually
--     flagged for it — not the whole Admin role automatically. A narrow,
--     separately-grantable bit alongside the coarse role enum, same idea as
--     is_admin()/is_super_admin() but per-user rather than per-role.
--   * One current announcement at a time. Posting a new one automatically
--     retires whichever was previously active (a trigger, not something
--     every insert path has to remember to do) — history rows stick around
--     (never hard-deleted, same ethos as the catalog's "unpublish, don't
--     delete") but only the current one is ever shown.
--   * Dismissal is per-account, in the database, and per-announcement (not
--     a single "seen the banner" bit) — so dismissing the current one does
--     nothing to hide whatever gets posted next, on any device you sign
--     into.

alter table public.profiles add column can_post_announcements boolean not null default false;

-- profiles_update_self_or_super_admin (init schema) already stops a
-- self-update from changing your own `role` — extend that same guard to
-- this new column, or an Admin could just grant themselves posting rights
-- with a plain `update profiles set can_post_announcements = true`. Only a
-- Super Admin (the `is_super_admin()` branch, unconstrained) can flip it.
drop policy "profiles_update_self_or_super_admin" on public.profiles;

create policy "profiles_update_self_or_super_admin"
  on public.profiles for update
  using (id = auth.uid() or public.is_super_admin())
  with check (
    public.is_super_admin()
    or (
      id = auth.uid()
      and role = (select role from public.profiles where id = auth.uid())
      and can_post_announcements = (select can_post_announcements from public.profiles where id = auth.uid())
    )
  );

-- Shared by the announcements table's insert/update policies below —
-- mirrors is_admin()/is_super_admin()'s shape rather than inlining the
-- flag lookup twice.
create or replace function public.can_manage_announcements()
returns boolean
language sql
stable
as $$
  select
    public.is_super_admin()
    or (public.current_user_role() = 'admin' and coalesce((select can_post_announcements from public.profiles where id = auth.uid()), false));
$$;

create table public.announcements (
  id uuid primary key default gen_random_uuid(),
  message text not null,
  created_by uuid references public.profiles (id) on delete set null,
  created_at timestamptz not null default now(),
  -- null = this is the current banner. Set the moment a newer one is
  -- posted (see the trigger below), or early by whoever posted it.
  retired_at timestamptz
);

alter table public.announcements enable row level security;

-- Every signed-in user sees the current one; past ones are a Super-Admin-
-- only audit trail (nobody's asked to browse announcement history yet, but
-- the rows are there whenever that's wanted).
create policy "announcements_select" on public.announcements for select
  using (retired_at is null or public.is_super_admin());

create policy "announcements_insert" on public.announcements for insert
  with check (public.can_manage_announcements() and created_by = auth.uid());

-- Covers retiring the current one early, or editing its text — anyone
-- currently allowed to post is trusted to manage the current banner,
-- not just whoever originally wrote it.
create policy "announcements_update" on public.announcements for update
  using (public.can_manage_announcements())
  with check (public.can_manage_announcements());

create or replace function public.retire_previous_announcement()
returns trigger
language plpgsql
as $$
begin
  update public.announcements set retired_at = now() where retired_at is null;
  return new;
end;
$$;

create trigger retire_previous_announcement_trigger
  before insert on public.announcements
  for each row execute function public.retire_previous_announcement();

create table public.announcement_dismissals (
  user_id uuid not null references public.profiles (id) on delete cascade,
  announcement_id uuid not null references public.announcements (id) on delete cascade,
  dismissed_at timestamptz not null default now(),
  primary key (user_id, announcement_id)
);

alter table public.announcement_dismissals enable row level security;

create policy "announcement_dismissals_select" on public.announcement_dismissals for select
  using (user_id = auth.uid());

create policy "announcement_dismissals_insert" on public.announcement_dismissals for insert
  with check (user_id = auth.uid());

-- Extend the Super-Admin user list with the new flag, so Manage Users can
-- show/toggle it per Admin. `create or replace` can't change a function's
-- return-table shape, only drop-then-recreate can.
drop function public.list_users_for_admin();

create function public.list_users_for_admin()
returns table(id uuid, username text, email text, role text, can_post_announcements boolean, banned_until timestamptz, created_at timestamptz)
language plpgsql
stable
security definer
set search_path = public
as $$
begin
  if not public.is_super_admin() then
    raise exception 'insufficient_privilege' using errcode = '42501';
  end if;

  return query
    select p.id, p.username, u.email::text, p.role, p.can_post_announcements, u.banned_until, p.created_at
    from public.profiles p
    join auth.users u on u.id = p.id
    order by p.created_at desc;
end;
$$;