19. supabase-conversations.sql

원본 파일: 'C:\Repository\loafacto-hub\docs\web-ui\19. supabase-conversations.sql'
sql
-- =============================================================================
-- 채팅 대화 기록 테이블
-- 사용자별 대화(conversations)와 메시지(messages)를 저장합니다.
-- Supabase SQL Editor에서 실행하세요.
-- =============================================================================

-- 1) conversations 테이블
CREATE TABLE IF NOT EXISTS public.conversations (
  id          uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id     uuid        NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  title       text        NOT NULL DEFAULT '새 대화',
  created_at  timestamptz NOT NULL DEFAULT now(),
  updated_at  timestamptz NOT NULL DEFAULT now()
);

COMMENT ON TABLE public.conversations IS '사용자별 채팅 대화 목록. 본인 데이터만 접근 가능.';

-- 2) messages 테이블
CREATE TABLE IF NOT EXISTS public.messages (
  id              uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
  conversation_id uuid        NOT NULL REFERENCES public.conversations(id) ON DELETE CASCADE,
  role            text        NOT NULL CHECK (role IN ('user', 'assistant')),
  content         text        NOT NULL,
  created_at      timestamptz NOT NULL DEFAULT now()
);

COMMENT ON TABLE public.messages IS '대화에 속한 개별 메시지. 부모 conversation의 소유자만 접근 가능.';

-- 3) 인덱스
CREATE INDEX IF NOT EXISTS conversations_user_id_idx
  ON public.conversations (user_id, updated_at DESC);

CREATE INDEX IF NOT EXISTS messages_conversation_id_idx
  ON public.messages (conversation_id, created_at ASC);

-- 4) RLS 활성화
ALTER TABLE public.conversations ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.messages      ENABLE ROW LEVEL SECURITY;

-- 5) conversations 정책: 본인 데이터만 전체 허용
CREATE POLICY "Users manage own conversations"
  ON public.conversations
  FOR ALL
  TO authenticated
  USING     (user_id = auth.uid())
  WITH CHECK (user_id = auth.uid());

-- 6) messages 정책: 부모 conversation 소유자만 전체 허용
CREATE POLICY "Users manage own messages"
  ON public.messages
  FOR ALL
  TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM public.conversations c
      WHERE c.id = conversation_id AND c.user_id = auth.uid()
    )
  )
  WITH CHECK (
    EXISTS (
      SELECT 1 FROM public.conversations c
      WHERE c.id = conversation_id AND c.user_id = auth.uid()
    )
  );

-- 7) conversations updated_at 자동 갱신 트리거
--    (set_updated_at 함수가 이미 존재한다면 CREATE OR REPLACE로 재사용)
CREATE OR REPLACE FUNCTION public.set_updated_at()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$;

CREATE TRIGGER conversations_updated_at
  BEFORE UPDATE ON public.conversations
  FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();
19. supabase-conversations.sql ​

19. supabase-conversations.sql
