collective-lists/supabase/migrations/023_section_visibility.sql

-- Migration 023: section visibility / feature flags (Fase 12)
--
-- Lets each user hide top-level sections they don't use, and lets admins of a
-- collective hide them for every member. The control surface is intentionally
-- typed-rigid via `public.known_sections()` instead of being a free-form bag
-- of strings — adding a new section requires a migration (which is the same
-- diff that adds the section's routes anyway, so no extra friction).
--
-- ── Precedence ───────────────────────────────────────────────────────────────
-- Effective layers, MOST restrictive wins:
--   1. (Fase 13) server   — server_settings.default_sections jsonb
--   2. collective         — collectives.feature_flags jsonb (admin override)
--   3. user               — users.feature_flags jsonb       (per-user override)
--   4. default            — true (ON)
-- Fase 12 implements layers 2–4. Fase 13 will extend `public.section_enabled()`
-- with a server layer above layer 2. The function body below is structured as
-- a single `coalesce(...)` so that adding the server layer is a one-line diff
-- (prepend the new SELECT to the coalesce) — no callers change shape.
--
-- ── Data shape ───────────────────────────────────────────────────────────────
-- `feature_flags jsonb` (object) where each key is a section name (matching
-- `known_sections()`) and the value is the boolean override. Missing key →
-- "no opinion" → defer to the next layer. Default for both columns is `{}`
-- so existing rows opt in to nothing and behave exactly as before.
--
-- RLS: write-paths on `users.feature_flags` and `collectives.feature_flags`
-- piggyback on the existing `users_update_own` and `collectives_update`
-- policies (migration 003). No new policy is required — the JSONB column is
-- covered by the row-level UPDATE policy automatically.

ALTER TABLE public.users
    ADD COLUMN feature_flags jsonb NOT NULL DEFAULT '{}'::jsonb;

ALTER TABLE public.collectives
    ADD COLUMN feature_flags jsonb NOT NULL DEFAULT '{}'::jsonb;

COMMENT ON COLUMN public.users.feature_flags IS
    'Per-user section visibility overrides. Keys must be in public.known_sections().';
COMMENT ON COLUMN public.collectives.feature_flags IS
    'Per-collective section visibility overrides set by an admin. Outranks the user layer.';

-- ── known_sections() ────────────────────────────────────────────────────────
-- IMMUTABLE: the result is a compile-time literal of the source rows in this
-- migration. Adding/removing a section is a fresh migration, not a runtime
-- change, so it's safe to inline as IMMUTABLE.
CREATE OR REPLACE FUNCTION public.known_sections()
RETURNS text[]
LANGUAGE sql
IMMUTABLE
AS $$
    SELECT ARRAY['lists', 'tasks', 'notes', 'search']::text[]
$$;

COMMENT ON FUNCTION public.known_sections() IS
    'Canonical list of top-level sections that participate in feature_flags. '
    'Each new section requires a migration that updates this function + a '
    'toggle row in /settings + /collective/manage.';

-- ── section_enabled() ───────────────────────────────────────────────────────
-- Resolves the effective ON/OFF state for one section, in one statement, with
-- the layered precedence documented at the top of this file.
--
-- The `coalesce(NULL, NULL, default)` pattern relies on `(jsonb ->> key)`
-- returning NULL when the key is missing — so a layer with "no opinion"
-- transparently falls through. An unknown section key returns NULL from every
-- layer and the trailing `true` wins, matching the documented default-ON
-- behaviour (a new feature is visible by default until someone opts out).
CREATE OR REPLACE FUNCTION public.section_enabled(
    p_section    text,
    p_user       uuid,
    p_collective uuid
)
RETURNS boolean
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
    SELECT COALESCE(
        -- Fase 13 will prepend a server layer here:
        --   (SELECT (value ->> p_section)::boolean
        --    FROM   public.server_settings
        --    WHERE  key = 'default_sections'),
        (SELECT (feature_flags ->> p_section)::boolean
           FROM   public.collectives
          WHERE   id = p_collective),
        (SELECT (feature_flags ->> p_section)::boolean
           FROM   public.users
          WHERE   id = p_user),
        true
    );
$$;

COMMENT ON FUNCTION public.section_enabled(text, uuid, uuid) IS
    'Effective ON/OFF of a top-level section for (user, collective). '
    'Precedence: collective override > user override > default true. '
    'Fase 13 will prepend a server-settings layer above the collective.';

-- ── Realtime ────────────────────────────────────────────────────────────────
-- The client-side `features.ts` store subscribes to `users` (own row) and
-- `collectives` (active row) so admin/user toggles propagate without a
-- reload. Without the publication membership those subscriptions receive
-- zero events — the realtime test (Playwright SV-02) was the catch.
--
-- REPLICA IDENTITY FULL is required so the OLD row carries all columns on
-- DELETE events; we only consume UPDATE here today, but `users` rows can be
-- deleted by the account-deletion flow (Fase 10.5) and a future Fase 13
-- "admin removes collective" will delete `collectives`. Filtering both
-- subscriptions still works correctly on DELETE this way.
ALTER PUBLICATION supabase_realtime ADD TABLE public.users;
ALTER PUBLICATION supabase_realtime ADD TABLE public.collectives;

ALTER TABLE public.users       REPLICA IDENTITY FULL;
ALTER TABLE public.collectives REPLICA IDENTITY FULL;