Spaces:

stylevoice
/

talk

Sleeping

talk / backend /sql /020_care_messages.sql

user

Deploy StyleVoiceChat backend

aa9892b 4 months ago

3.56 kB

-- 안부 메시지 (`/message`) — 본인 음색으로 가족/친구에게 보낼 짧은 음성 메시지.
--
-- 흐름:
--   1) 사용자가 음성/텍스트로 "이런 안부 보내줘" 지시
--   2) LLM 이 1~2문장 본문 생성 + 사용자 음색 ICL TTS 합성
--   3) wav 를 Supabase Storage (scheduled-audio bucket 재사용) 에 업로드
--   4) 즉시 공유: 사용자가 카카오 JS SDK 로 본인 카톡에서 가족 채팅방 선택 → /listen/<id> 링크 발송
--   5) 예약: 시각 도달 시 사용자 본인에게 알림톡 ("준비됐어요. 공유하기") → 클릭 → /listen/<id> 페이지의
--           [공유하기] 버튼 → 카톡 공유
--
-- 발신은 항상 사용자 본인 — 알림톡은 사용자 자신에게만 발송 (정책 안전), 가족에게는 사용자 카톡으로 직접.
-- 받는 사람 번호 수집 없음 (PIPA 부담 회피). recipient_label 은 사용자가 자기만 알아보는 메모.

CREATE TABLE IF NOT EXISTS public.care_messages (
    id                 UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id            UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,

    -- 입력
    recipient_label    TEXT NOT NULL DEFAULT '',  -- "어머니" / "아빠" 등 사용자 메모
    voice_input_text   TEXT,                       -- STT 또는 직접 입력한 지시문 (이력)

    -- 생성된 결과
    generated_text     TEXT NOT NULL DEFAULT '',  -- LLM 이 만든 본문 (실제 음성 텍스트)
    audio_file_path    TEXT,                       -- storage 경로 (삭제용)
    audio_file_url     TEXT,                       -- 1년 signed URL (목록 미리듣기)
    voice_storage_path TEXT,                       -- 합성에 사용한 사용자 음색 (재합성 추적)

    -- 예약 (NULL = 즉시 공유, 예약 없음)
    scheduled_at       TIMESTAMPTZ,
    timezone           TEXT NOT NULL DEFAULT 'Asia/Seoul',
    next_fire_at       TIMESTAMPTZ,
    last_fired_at      TIMESTAMPTZ,
    active             BOOLEAN NOT NULL DEFAULT TRUE,

    -- 분석
    shared_at          TIMESTAMPTZ,                -- 사용자가 [공유하기] 버튼 누른 시각

    created_at         TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at         TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_care_messages_user
    ON public.care_messages (user_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_care_messages_due
    ON public.care_messages (active, next_fire_at)
    WHERE next_fire_at IS NOT NULL;

ALTER TABLE public.care_messages ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS care_messages_owner ON public.care_messages;
CREATE POLICY care_messages_owner ON public.care_messages
    FOR ALL
    USING (user_id = auth.uid())
    WITH CHECK (user_id = auth.uid());

-- updated_at 자동 갱신
CREATE OR REPLACE FUNCTION public._care_messages_touch_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS care_messages_touch_updated_at ON public.care_messages;
CREATE TRIGGER care_messages_touch_updated_at
    BEFORE UPDATE ON public.care_messages
    FOR EACH ROW EXECUTE FUNCTION public._care_messages_touch_updated_at();

-- ───────── Storage 정책 ─────────
-- scheduled-audio bucket 재사용 (sql/016 에서 이미 생성). 같은 폴더 (<user_id>/)
-- 안에 care 메시지 wav 를 두므로 추가 정책 불필요.
-- care 파일명 컨벤션: <user_id>/care-<message_id>-<timestamp>.wav

	-- 안부 메시지 (`/message`) — 본인 음색으로 가족/친구에게 보낼 짧은 음성 메시지.
	--
	-- 흐름:
	-- 1) 사용자가 음성/텍스트로 "이런 안부 보내줘" 지시
	-- 2) LLM 이 1~2문장 본문 생성 + 사용자 음색 ICL TTS 합성
	-- 3) wav 를 Supabase Storage (scheduled-audio bucket 재사용) 에 업로드
	-- 4) 즉시 공유: 사용자가 카카오 JS SDK 로 본인 카톡에서 가족 채팅방 선택 → /listen/<id> 링크 발송
	-- 5) 예약: 시각 도달 시 사용자 본인에게 알림톡 ("준비됐어요. 공유하기") → 클릭 → /listen/<id> 페이지의
	-- [공유하기] 버튼 → 카톡 공유
	--
	-- 발신은 항상 사용자 본인 — 알림톡은 사용자 자신에게만 발송 (정책 안전), 가족에게는 사용자 카톡으로 직접.
	-- 받는 사람 번호 수집 없음 (PIPA 부담 회피). recipient_label 은 사용자가 자기만 알아보는 메모.

	CREATE TABLE IF NOT EXISTS public.care_messages (
	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,

	-- 입력
	recipient_label TEXT NOT NULL DEFAULT '', -- "어머니" / "아빠" 등 사용자 메모
	voice_input_text TEXT, -- STT 또는 직접 입력한 지시문 (이력)

	-- 생성된 결과
	generated_text TEXT NOT NULL DEFAULT '', -- LLM 이 만든 본문 (실제 음성 텍스트)
	audio_file_path TEXT, -- storage 경로 (삭제용)
	audio_file_url TEXT, -- 1년 signed URL (목록 미리듣기)
	voice_storage_path TEXT, -- 합성에 사용한 사용자 음색 (재합성 추적)

	-- 예약 (NULL = 즉시 공유, 예약 없음)
	scheduled_at TIMESTAMPTZ,
	timezone TEXT NOT NULL DEFAULT 'Asia/Seoul',
	next_fire_at TIMESTAMPTZ,
	last_fired_at TIMESTAMPTZ,
	active BOOLEAN NOT NULL DEFAULT TRUE,

	-- 분석
	shared_at TIMESTAMPTZ, -- 사용자가 [공유하기] 버튼 누른 시각

	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
	updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
	);

	CREATE INDEX IF NOT EXISTS idx_care_messages_user
	ON public.care_messages (user_id, created_at DESC);

	CREATE INDEX IF NOT EXISTS idx_care_messages_due
	ON public.care_messages (active, next_fire_at)
	WHERE next_fire_at IS NOT NULL;

	ALTER TABLE public.care_messages ENABLE ROW LEVEL SECURITY;

	DROP POLICY IF EXISTS care_messages_owner ON public.care_messages;
	CREATE POLICY care_messages_owner ON public.care_messages
	FOR ALL
	USING (user_id = auth.uid())
	WITH CHECK (user_id = auth.uid());

	-- updated_at 자동 갱신
	CREATE OR REPLACE FUNCTION public._care_messages_touch_updated_at()
	RETURNS TRIGGER AS $$
	BEGIN
	NEW.updated_at = NOW();
	RETURN NEW;
	END;
	$$ LANGUAGE plpgsql;

	DROP TRIGGER IF EXISTS care_messages_touch_updated_at ON public.care_messages;
	CREATE TRIGGER care_messages_touch_updated_at
	BEFORE UPDATE ON public.care_messages
	FOR EACH ROW EXECUTE FUNCTION public._care_messages_touch_updated_at();

	-- ───────── Storage 정책 ─────────
	-- scheduled-audio bucket 재사용 (sql/016 에서 이미 생성). 같은 폴더 (<user_id>/)
	-- 안에 care 메시지 wav 를 두므로 추가 정책 불필요.
	-- care 파일명 컨벤션: <user_id>/care-<message_id>-<timestamp>.wav