Spaces:

stylevoice
/

talk

Sleeping

talk / backend /sql /047_voice_letters.sql

user

Deploy StyleVoiceChat backend

aa9892b 4 months ago

3.6 kB

-- 카페 음성 선물쿠폰 MVP — voice_letters (2026-05-28, P1a Phase B)
--
-- 손님이 작성한 음성편지 (텍스트 + 보이스 + 모더레이션 + 합성된 audio path).
-- 요청서: doc/runbook/req_cafe_gift_mvp.md §1
-- 설계: doc/runbook/cafe_gift_p1a.md §2 sql/047
--
-- 흐름:
--   1) 손님 텍스트 입력 (≤400자, 백엔드 검증)
--   2) moderation_service → approved/blocked
--   3) approved 면 TTS 합성 → 'voice-letters' Storage 업로드 → audio_path 세팅
--   4) gift_orders 가 voice_letter_id FK 로 연결 (1:1)
--
-- 비로그인 손님이 만들 수 있어야 하므로 백엔드 service-role 만 INSERT.
-- RLS 공개 SELECT 없음 — reveal 은 별도 token (gift_coupons.reveal_token) 으로 백엔드 라우터가 service-role 조회.
--
-- ⛔ 회귀 가드:
--   - 신규 테이블, 기존 CHECK 영향 0 (sql/043 사고 가드)
--   - voice_clone 호출 0 (시스템 UID + 내장화자만, P2 확장)
--   - is_preview=True (chat broadcast leak 차단)

CREATE TABLE IF NOT EXISTS public.voice_letters (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- gift_orders.id 와 양방향 1:1 (FK 는 sql/048 에서 보강)
    -- 편지 먼저 → 모더레이션 통과 → 주문 attach 가능. 또는 동시 생성.
    order_id            UUID,

    sender_name         TEXT NOT NULL DEFAULT '',
    -- 백엔드가 400자 cap 검증 (사용자 결정 #3). DB 에 hard cap CHECK 안 둠 — 추후 정책 변경 시 마이그레이션 없이 가능.
    text                TEXT NOT NULL,
    voice_kind          TEXT NOT NULL DEFAULT '__default__',
                        -- '__default__' (Sohee) | '__spk__<code>' (내장화자 8종)

    -- 합성 결과 (모더레이션 approved 시에만 채워짐)
    audio_path          TEXT,                       -- Storage 'voice-letters' 버킷 path
    audio_duration_sec  NUMERIC(6,2),

    moderation_status   TEXT NOT NULL DEFAULT 'pending'
                        CHECK (moderation_status IN ('pending','approved','blocked','manual_review')),
    moderation_reason   TEXT,                       -- blocked 시 사용자 노출용 짧은 이유

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

CREATE INDEX IF NOT EXISTS idx_voice_letters_status
    ON public.voice_letters (moderation_status, created_at DESC);

ALTER TABLE public.voice_letters ENABLE ROW LEVEL SECURITY;

-- 공개 INSERT/UPDATE 정책 없음. 백엔드 service-role 키만 작업.
-- (라우터가 비로그인 손님 입력 받아 service-role 로 INSERT, owner_user_id 컬럼 없음.)

CREATE OR REPLACE FUNCTION public._voice_letters_touch_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS voice_letters_touch_updated_at ON public.voice_letters;
CREATE TRIGGER voice_letters_touch_updated_at
    BEFORE UPDATE ON public.voice_letters
    FOR EACH ROW EXECUTE FUNCTION public._voice_letters_touch_updated_at();

COMMENT ON TABLE public.voice_letters IS
    '카페 선물쿠폰 음성편지. ≤400자, 내장화자만 (P1a). 모더레이션 통과 후 audio 합성.';
COMMENT ON COLUMN public.voice_letters.voice_kind IS
    '__default__ (Sohee) 또는 __spk__<code> (내장화자 8종). voice_clone(❤️) 은 P2.';
COMMENT ON COLUMN public.voice_letters.moderation_status IS
    'pending: 검사 전 | approved: 통과 (audio 합성 OK) | blocked: 차단 (결제 진입 불가) | manual_review: 운영자 검토 (Phase E)';

	-- 카페 음성 선물쿠폰 MVP — voice_letters (2026-05-28, P1a Phase B)
	--
	-- 손님이 작성한 음성편지 (텍스트 + 보이스 + 모더레이션 + 합성된 audio path).
	-- 요청서: doc/runbook/req_cafe_gift_mvp.md §1
	-- 설계: doc/runbook/cafe_gift_p1a.md §2 sql/047
	--
	-- 흐름:
	-- 1) 손님 텍스트 입력 (≤400자, 백엔드 검증)
	-- 2) moderation_service → approved/blocked
	-- 3) approved 면 TTS 합성 → 'voice-letters' Storage 업로드 → audio_path 세팅
	-- 4) gift_orders 가 voice_letter_id FK 로 연결 (1:1)
	--
	-- 비로그인 손님이 만들 수 있어야 하므로 백엔드 service-role 만 INSERT.
	-- RLS 공개 SELECT 없음 — reveal 은 별도 token (gift_coupons.reveal_token) 으로 백엔드 라우터가 service-role 조회.
	--
	-- ⛔ 회귀 가드:
	-- - 신규 테이블, 기존 CHECK 영향 0 (sql/043 사고 가드)
	-- - voice_clone 호출 0 (시스템 UID + 내장화자만, P2 확장)
	-- - is_preview=True (chat broadcast leak 차단)

	CREATE TABLE IF NOT EXISTS public.voice_letters (
	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

	-- gift_orders.id 와 양방향 1:1 (FK 는 sql/048 에서 보강)
	-- 편지 먼저 → 모더레이션 통과 → 주문 attach 가능. 또는 동시 생성.
	order_id UUID,

	sender_name TEXT NOT NULL DEFAULT '',
	-- 백엔드가 400자 cap 검증 (사용자 결정 #3). DB 에 hard cap CHECK 안 둠 — 추후 정책 변경 시 마이그레이션 없이 가능.
	text TEXT NOT NULL,
	voice_kind TEXT NOT NULL DEFAULT '__default__',
	-- '__default__' (Sohee) \| '__spk__<code>' (내장화자 8종)

	-- 합성 결과 (모더레이션 approved 시에만 채워짐)
	audio_path TEXT, -- Storage 'voice-letters' 버킷 path
	audio_duration_sec NUMERIC(6,2),

	moderation_status TEXT NOT NULL DEFAULT 'pending'
	CHECK (moderation_status IN ('pending','approved','blocked','manual_review')),
	moderation_reason TEXT, -- blocked 시 사용자 노출용 짧은 이유

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

	CREATE INDEX IF NOT EXISTS idx_voice_letters_status
	ON public.voice_letters (moderation_status, created_at DESC);

	ALTER TABLE public.voice_letters ENABLE ROW LEVEL SECURITY;

	-- 공개 INSERT/UPDATE 정책 없음. 백엔드 service-role 키만 작업.
	-- (라우터가 비로그인 손님 입력 받아 service-role 로 INSERT, owner_user_id 컬럼 없음.)

	CREATE OR REPLACE FUNCTION public._voice_letters_touch_updated_at()
	RETURNS TRIGGER AS $$
	BEGIN
	NEW.updated_at = NOW();
	RETURN NEW;
	END;
	$$ LANGUAGE plpgsql;

	DROP TRIGGER IF EXISTS voice_letters_touch_updated_at ON public.voice_letters;
	CREATE TRIGGER voice_letters_touch_updated_at
	BEFORE UPDATE ON public.voice_letters
	FOR EACH ROW EXECUTE FUNCTION public._voice_letters_touch_updated_at();

	COMMENT ON TABLE public.voice_letters IS
	'카페 선물쿠폰 음성편지. ≤400자, 내장화자만 (P1a). 모더레이션 통과 후 audio 합성.';
	COMMENT ON COLUMN public.voice_letters.voice_kind IS
	'__default__ (Sohee) 또는 __spk__<code> (내장화자 8종). voice_clone(❤️) 은 P2.';
	COMMENT ON COLUMN public.voice_letters.moderation_status IS
	'pending: 검사 전 \| approved: 통과 (audio 합성 OK) \| blocked: 차단 (결제 진입 불가) \| manual_review: 운영자 검토 (Phase E)';