08. supabase-support-table.sql

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

-- =============================================================================
-- 제보·문의(support) 테이블 — 오류 제보/1:1 문의 접수, 관리자 조회/상태 변경
-- user_roles 적용 후 Supabase SQL Editor에서 실행하세요.
-- =============================================================================

CREATE TABLE IF NOT EXISTS public.support (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  type text NOT NULL CHECK (type IN ('report', 'inquiry')),
  title text NOT NULL,
  content text NOT NULL,
  contact_email text NOT NULL DEFAULT '',
  status text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'resolved')),
  admin_note text NOT NULL DEFAULT '',
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz
);

COMMENT ON TABLE public.support IS '오류 제보(report) / 1:1 문의(inquiry). 비로그인 포함 누구나 INSERT, 관리자만 SELECT/UPDATE/DELETE.';

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

-- INSERT: 누구나 접수 가능 (anon + authenticated)
CREATE POLICY "Anyone can submit support"
ON public.support FOR INSERT
TO public
WITH CHECK (true);

-- SELECT: 관리자(super_admin, operator)만 조회
CREATE POLICY "Only admins can read support"
ON public.support FOR SELECT
TO authenticated
USING (
  EXISTS (
    SELECT 1 FROM public.user_roles r
    WHERE r.user_id = auth.uid() AND r.role IN ('super_admin', 'operator')
  )
);

-- UPDATE: 관리자만 수정 (상태, 관리자 메모)
CREATE POLICY "Only admins can update support"
ON public.support 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: 관리자만 삭제
CREATE POLICY "Only admins can delete support"
ON public.support 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 support_created_at_desc ON public.support (created_at DESC);
CREATE INDEX IF NOT EXISTS support_type ON public.support (type);
CREATE INDEX IF NOT EXISTS support_status ON public.support (status);

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