06. supabase-notices-table.sql

원본 파일: 'C:\Repository\loafacto-hub\docs\web-ui\06. supabase-notices-table.sql'

-- =============================================================================
-- 공지사항(notices) 테이블 — 관리자 공지 등록/수정/삭제, 모든 사용자 조회
-- user_roles 적용 후 Supabase SQL Editor에서 실행하세요.
-- =============================================================================

CREATE TABLE IF NOT EXISTS public.notices (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  title text NOT NULL,
  content text NOT NULL,
  type text NOT NULL CHECK (type IN ('normal', 'event', 'maintenance')),
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz
);

COMMENT ON TABLE public.notices IS '공지사항. 관리자(super_admin/operator)만 등록/수정/삭제, 모든 사용자 조회 가능.';

-- RLS 활성화
ALTER TABLE public.notices ENABLE ROW LEVEL SECURITY;

-- SELECT: 누구나 읽기 (비로그인 사용자도 공지 목록/상세 조회)
CREATE POLICY "Notices are readable by everyone"
ON public.notices FOR SELECT
TO public
USING (true);

-- INSERT: super_admin, operator 만 등록
CREATE POLICY "Only admins can insert notices"
ON public.notices FOR INSERT
TO authenticated
WITH CHECK (
  EXISTS (
    SELECT 1 FROM public.user_roles r
    WHERE r.user_id = auth.uid() AND r.role IN ('super_admin', 'operator')
  )
);

-- UPDATE: super_admin, operator 만 수정
CREATE POLICY "Only admins can update notices"
ON public.notices FOR UPDATE
TO authenticated
USING (
  EXISTS (
    SELECT 1 FROM public.user_roles r
    WHERE r.user_id = auth.uid() AND r.role IN ('super_admin', 'operator')
  )
)
WITH CHECK (
  EXISTS (
    SELECT 1 FROM public.user_roles r
    WHERE r.user_id = auth.uid() AND r.role IN ('super_admin', 'operator')
  )
);

-- DELETE: super_admin, operator 만 삭제
CREATE POLICY "Only admins can delete notices"
ON public.notices FOR DELETE
TO authenticated
USING (
  EXISTS (
    SELECT 1 FROM public.user_roles r
    WHERE r.user_id = auth.uid() AND r.role IN ('super_admin', 'operator')
  )
);

-- 인덱스: 목록 정렬용
CREATE INDEX IF NOT EXISTS notices_created_at_desc ON public.notices (created_at DESC);

GRANT SELECT ON public.notices TO anon;
GRANT SELECT ON public.notices TO authenticated;
GRANT INSERT, UPDATE, DELETE ON public.notices TO authenticated;