07. supabase-changelog-table.sql

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

-- =============================================================================
-- 변경 내역(changelog) 테이블 — 관리자 버전 등록/수정/삭제, 모든 사용자 조회
-- user_roles 적용 후 Supabase SQL Editor에서 실행하세요.
-- =============================================================================

CREATE TABLE IF NOT EXISTS public.changelog (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  version text NOT NULL,
  released_date text NOT NULL DEFAULT '',
  new_features_text text NOT NULL DEFAULT '',
  improvements_text text NOT NULL DEFAULT '',
  bug_fixes_text text NOT NULL DEFAULT '',
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz
);

COMMENT ON TABLE public.changelog IS '버전별 변경 내역. 관리자(super_admin/operator)만 등록/수정/삭제, 모든 사용자 조회 가능.';

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

-- SELECT: 누구나 읽기 (비로그인 사용자도 변경 내역 페이지 조회)
CREATE POLICY "Changelog is readable by everyone"
ON public.changelog FOR SELECT
TO public
USING (true);

-- INSERT: super_admin, operator 만 등록
CREATE POLICY "Only admins can insert changelog"
ON public.changelog 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 changelog"
ON public.changelog 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 changelog"
ON public.changelog 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 changelog_created_at_desc ON public.changelog (created_at DESC);

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