collective-lists/supabase/migrations/009_notes.sql

-- Migration 009: notes
--
-- Notes have:
--   - color: one of 8 predefined values, or NULL (default)
--   - is_pinned + is_archived (mutually exclusive — enforced by CHECK)
--   - deleted_at: soft-delete; pg_cron purges after 7 days
--
-- Updated_by + updated_at are touched by an UPDATE trigger so the UI can show
-- "edited by X" without trusting the client.

-- ── Enum ─────────────────────────────────────────────────────────────────────

CREATE TYPE public.note_color AS ENUM (
    'yellow', 'green', 'blue', 'pink', 'purple', 'orange', 'gray', 'red'
);

-- ── notes ────────────────────────────────────────────────────────────────────

CREATE TABLE public.notes (
    id            uuid              PRIMARY KEY DEFAULT gen_random_uuid(),
    collective_id uuid              NOT NULL REFERENCES public.collectives(id) ON DELETE CASCADE,
    title         text              NULL,
    content       text              NOT NULL DEFAULT '',
    color         public.note_color NULL,
    is_pinned     boolean           NOT NULL DEFAULT false,
    is_archived   boolean           NOT NULL DEFAULT false,
    deleted_at    timestamptz       NULL,
    created_by    uuid              NOT NULL REFERENCES public.users(id) ON DELETE RESTRICT,
    created_at    timestamptz       NOT NULL DEFAULT now(),
    updated_by    uuid              NOT NULL REFERENCES public.users(id) ON DELETE RESTRICT,
    updated_at    timestamptz       NOT NULL DEFAULT now(),
    -- Pinned and archived are mutually exclusive (analysis §5.2)
    CONSTRAINT notes_pin_archive_exclusive CHECK (NOT (is_pinned AND is_archived))
);

ALTER TABLE public.notes ENABLE ROW LEVEL SECURITY;

-- Main board query: collective + status (active/pinned/archived) excluding trash
CREATE INDEX notes_collective_active_idx
    ON public.notes (collective_id, is_pinned DESC, updated_at DESC)
    WHERE deleted_at IS NULL AND is_archived = false;

CREATE INDEX notes_collective_archived_idx
    ON public.notes (collective_id, updated_at DESC)
    WHERE deleted_at IS NULL AND is_archived = true;

CREATE INDEX notes_collective_trash_idx
    ON public.notes (collective_id, deleted_at)
    WHERE deleted_at IS NOT NULL;

-- ── Trigger: refresh updated_at / updated_by on UPDATE ───────────────────────

CREATE OR REPLACE FUNCTION public.fn_notes_touch()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at := now();
    -- updated_by is set by the client (auth.uid()); fall back to created_by if absent.
    IF NEW.updated_by IS NULL THEN
        NEW.updated_by := COALESCE(auth.uid(), OLD.updated_by);
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_notes_touch
    BEFORE UPDATE ON public.notes
    FOR EACH ROW EXECUTE FUNCTION public.fn_notes_touch();

-- ── RLS: notes ───────────────────────────────────────────────────────────────

-- All members (including guests) can read notes within the 7-day trash window.
CREATE POLICY notes_select
    ON public.notes FOR SELECT
    USING (
        public.is_member(collective_id)
        AND (
            deleted_at IS NULL
            OR deleted_at > now() - INTERVAL '7 days'
        )
    );

-- Active members (admin + member) can create notes.
CREATE POLICY notes_insert
    ON public.notes FOR INSERT
    WITH CHECK (
        public.is_active_member(collective_id)
        AND created_by = auth.uid()
        AND updated_by = auth.uid()
    );

-- Active members can edit any note in the collective (per analysis §5.2).
CREATE POLICY notes_update
    ON public.notes FOR UPDATE
    USING  (public.is_active_member(collective_id))
    WITH CHECK (public.is_active_member(collective_id));

-- Active members can permanently hard-delete a note only once it has been soft-deleted.
-- pg_cron also purges old trash as superuser, bypassing RLS — intentional.
CREATE POLICY notes_delete
    ON public.notes FOR DELETE
    USING (
        public.is_active_member(collective_id)
        AND deleted_at IS NOT NULL
    );

-- ── pg_cron: purge notes from trash after 7 days ─────────────────────────────
-- Defensive: ensure the extension is present (Supabase self-hosted ships with it).
CREATE EXTENSION IF NOT EXISTS pg_cron;

SELECT cron.schedule(
    'purge-deleted-notes',
    '10 3 * * *',
    $$
        DELETE FROM public.notes
        WHERE  deleted_at < now() - INTERVAL '7 days';
    $$
);