Explore Rooms

Open your database project → SQL Editor → paste and run this:

-- 1) Add missing columns
ALTER TABLE public.group_rooms
  ADD COLUMN IF NOT EXISTS icon text DEFAULT '💬',
  ADD COLUMN IF NOT EXISTS theme text DEFAULT '#efeae2',
  ADD COLUMN IF NOT EXISTS guidelines text DEFAULT 'Be respectful.',
  ADD COLUMN IF NOT EXISTS admins jsonb DEFAULT '[]'::jsonb,
  ADD COLUMN IF NOT EXISTS banned_users jsonb DEFAULT '[]'::jsonb,
  ADD COLUMN IF NOT EXISTS pinned_message text,
  ADD COLUMN IF NOT EXISTS participants jsonb DEFAULT '[]'::jsonb,
  ADD COLUMN IF NOT EXISTS created_by text;

ALTER TABLE public.group_messages
  ADD COLUMN IF NOT EXISTS sender_id text;

-- 2) Allow realtime DELETE events with full row data
ALTER TABLE public.group_messages REPLICA IDENTITY FULL;
ALTER TABLE public.group_rooms    REPLICA IDENTITY FULL;

-- 3) RLS policies - REQUIRED so the anon key can read/write/delete.
--    If you don't want RLS, you can instead run:
--      ALTER TABLE public.group_rooms    DISABLE ROW LEVEL SECURITY;
--      ALTER TABLE public.group_messages DISABLE ROW LEVEL SECURITY;
ALTER TABLE public.group_rooms    ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.group_messages ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "rooms read"   ON public.group_rooms;
DROP POLICY IF EXISTS "rooms insert" ON public.group_rooms;
DROP POLICY IF EXISTS "rooms update" ON public.group_rooms;
DROP POLICY IF EXISTS "rooms delete" ON public.group_rooms;
CREATE POLICY "rooms read"   ON public.group_rooms FOR SELECT USING (true);
CREATE POLICY "rooms insert" ON public.group_rooms FOR INSERT WITH CHECK (true);
CREATE POLICY "rooms update" ON public.group_rooms FOR UPDATE USING (true) WITH CHECK (true);
CREATE POLICY "rooms delete" ON public.group_rooms FOR DELETE USING (true);

DROP POLICY IF EXISTS "msgs read"   ON public.group_messages;
DROP POLICY IF EXISTS "msgs insert" ON public.group_messages;
DROP POLICY IF EXISTS "msgs delete" ON public.group_messages;
CREATE POLICY "msgs read"   ON public.group_messages FOR SELECT USING (true);
CREATE POLICY "msgs insert" ON public.group_messages FOR INSERT WITH CHECK (true);
CREATE POLICY "msgs delete" ON public.group_messages FOR DELETE USING (true);

-- 4) Enable Realtime for both tables
ALTER PUBLICATION supabase_realtime ADD TABLE public.group_rooms;
ALTER PUBLICATION supabase_realtime ADD TABLE public.group_messages;

-- 5) Scheduled trim (runs every 2 days): for any room with >= 500 messages,
--    delete the oldest 100. Rooms with < 500 messages are left untouched.
--    If the currently pinned message is among the 100 deleted, the pin is cleared.
DROP TRIGGER IF EXISTS auto_trim_messages ON public.group_messages;
DROP FUNCTION IF EXISTS public.trim_messages_to_100() CASCADE;

CREATE OR REPLACE FUNCTION public.trim_overflowing_rooms() RETURNS void
LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE
  r record;
  deleted_contents text[];
  current_pin text;
BEGIN
  FOR r IN
    SELECT room_id FROM public.group_messages
    GROUP BY room_id HAVING count(*) >= 500
  LOOP
    -- Delete the 100 oldest, capturing their contents so we can clear a stale pin
    WITH del AS (
      DELETE FROM public.group_messages
      WHERE id IN (
        SELECT id FROM public.group_messages
        WHERE room_id = r.room_id
        ORDER BY created_at ASC
        LIMIT 100
      )
      RETURNING content
    )
    SELECT array_agg(content) INTO deleted_contents FROM del;

    SELECT pinned_message INTO current_pin FROM public.group_rooms WHERE id = r.room_id;
    IF current_pin IS NOT NULL AND current_pin = ANY(deleted_contents) THEN
      UPDATE public.group_rooms SET pinned_message = NULL WHERE id = r.room_id;
    END IF;
  END LOOP;
END $$;

-- 6) Dormant-group cleanup: groups with <=1 participant AND no messages
--    in the last 7 days get deleted (along with any leftover messages).
CREATE OR REPLACE FUNCTION public.cleanup_dormant_groups() RETURNS void
LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE cutoff timestamptz := now() - interval '7 days';
BEGIN
  DELETE FROM public.group_messages WHERE room_id IN (
    SELECT r.id FROM public.group_rooms r
    WHERE r.created_at < cutoff
      AND jsonb_array_length(coalesce(r.participants, '[]'::jsonb)) <= 1
      AND NOT EXISTS (
        SELECT 1 FROM public.group_messages m
        WHERE m.room_id = r.id AND m.created_at > cutoff
      )
  );
  DELETE FROM public.group_rooms r
  WHERE r.created_at < cutoff
    AND jsonb_array_length(coalesce(r.participants, '[]'::jsonb)) <= 1
    AND NOT EXISTS (
      SELECT 1 FROM public.group_messages m
      WHERE m.room_id = r.id AND m.created_at > cutoff
    );
END $$;

-- 7) Schedule jobs via pg_cron (Dashboard > Database > Extensions > enable pg_cron).
--    If pg_cron isn't available, the client-side fallbacks still run.
DO $$ BEGIN
  IF EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'pg_cron') THEN
    -- Daily dormant-group cleanup at 03:00 UTC
    IF EXISTS (SELECT 1 FROM cron.job WHERE jobname = 'cleanup-dormant-groups') THEN
      PERFORM cron.unschedule('cleanup-dormant-groups');
    END IF;
    PERFORM cron.schedule('cleanup-dormant-groups', '0 3 * * *',
                          'SELECT public.cleanup_dormant_groups();');

    -- Trim overflowing rooms every 2 days at 03:30 UTC
    IF EXISTS (SELECT 1 FROM cron.job WHERE jobname = 'trim-overflowing-rooms') THEN
      PERFORM cron.unschedule('trim-overflowing-rooms');
    END IF;
    PERFORM cron.schedule('trim-overflowing-rooms', '30 3 */2 * *',
                          'SELECT public.trim_overflowing_rooms();');
  END IF;
END $$;

-- 8) Reload the schema cache
NOTIFY pgrst, 'reload schema';

After running, click Reload. (If you only see the old error, wait ~10 seconds for the schema cache to refresh — or run the NOTIFY line again.)