Spaces:

stylevoice
/

talk

Sleeping

talk / backend /sql /026_care_recipients.sql

user

Deploy StyleVoiceChat backend

aa9892b 4 months ago

6.07 kB

-- 안부 메시지 가족 수신자 + care_messages 확장 (2026-05-12, care_redesign Phase 2)
--
-- Phase 2 핵심:
--   - 가족 수신자 (`care_recipients`) — 라벨/전화번호/음성슬롯/발송시각/1탭|0탭 모드.
--   - 매일 KST T-30분 자동 본문생성+TTS → care_messages 행 INSERT (status='ready').
--   - 1탭 모드: T-0 분 사용자에게 T1 알림톡 → 사용자가 [보내기] 누르면 T3 알림톡 발송.
--   - 가족이 /listen/<care_id> 클릭 → listened_at 채워짐 → 발신자에게 T4 알림톡.
--
-- 회귀 가드 변경:
--   - 가족 번호 수집 OK (이전 정책 폐기, doc/runbook/care_redesign.md 참조)
--   - 우리 사업자 알림톡으로 가족에게 직발송 OK
--
-- 안티-스토킹 (Phase 4 에서 강화): care_blacklist 추가 예정, 본 migration 에선
-- 구조만 준비.

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

    -- 가족 식별
    label                    TEXT NOT NULL,           -- '엄마', '아빠', '시어머니' 등 자유
    phone_e164               TEXT NOT NULL,           -- +8210xxxxxxxx (정규화 후 저장)

    -- 발송 설정
    voice_slot_kind          TEXT NOT NULL DEFAULT 'self'
                             CHECK (voice_slot_kind IN ('self','son','daughter')),
    schedule_mode            TEXT NOT NULL DEFAULT '1tap'
                             CHECK (schedule_mode IN ('1tap','0tap')),
    send_time_kst            TIME NOT NULL DEFAULT '09:00',  -- KST 기준 일일 발송 시각

    -- 가족 동의
    consent_status           TEXT NOT NULL DEFAULT 'active'
                             CHECK (consent_status IN ('active','opted_out','blocked')),
    social_consent_confirmed BOOLEAN NOT NULL DEFAULT FALSE,  -- 등록 시 "부모님께 양해 구함" 체크

    -- 옵트아웃 (Phase 4)
    optout_token             TEXT NOT NULL DEFAULT encode(gen_random_bytes(16), 'hex') UNIQUE,
    opted_out_at             TIMESTAMPTZ,

    -- 활성 토글 (사용자가 일시 정지 가능)
    active                   BOOLEAN NOT NULL DEFAULT TRUE,

    -- 다음 발송 예정 시각 (UTC). cron tick 이 이걸 보고 due 발사.
    next_prep_at             TIMESTAMPTZ,             -- 본문생성+TTS 시작 시각 (T-30분)

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

CREATE INDEX IF NOT EXISTS idx_care_recipients_user
    ON public.care_recipients (user_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_care_recipients_due_prep
    ON public.care_recipients (active, next_prep_at)
    WHERE active = TRUE AND consent_status = 'active' AND next_prep_at IS NOT NULL;

-- 한 사용자 + 같은 전화번호 중복 등록 방지 (라벨만 다르면 별도 등록 가능하게 두는 게
-- 보통이지만, 안티-스토킹 관점에서 같은 가족에게 2개 row 보낼 일은 없음).
CREATE UNIQUE INDEX IF NOT EXISTS care_recipients_user_phone_uniq
    ON public.care_recipients (user_id, phone_e164);

ALTER TABLE public.care_recipients ENABLE ROW LEVEL SECURITY;

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

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

DROP TRIGGER IF EXISTS care_recipients_touch_updated_at ON public.care_recipients;
CREATE TRIGGER care_recipients_touch_updated_at
    BEFORE UPDATE ON public.care_recipients
    FOR EACH ROW EXECUTE FUNCTION public._care_recipients_touch_updated_at();

-- ───────── care_messages 확장 ─────────
--
-- 기존 컬럼 (recipient_label, generated_text, audio_file_*, scheduled_at, active 등) 유지.
-- 새 흐름은 `recipient_id` 가 채워지면 "Phase 2 자동 흐름" 으로 간주, NULL 이면 기존
-- 즉시-생성 흐름 (사용자 카톡 picker) 으로 호환 유지.

ALTER TABLE public.care_messages
    ADD COLUMN IF NOT EXISTS recipient_id   UUID REFERENCES public.care_recipients(id) ON DELETE SET NULL,
    ADD COLUMN IF NOT EXISTS scheduled_for  TIMESTAMPTZ,                     -- 가족 발송 예정 시각 (UTC)
    ADD COLUMN IF NOT EXISTS listened_at    TIMESTAMPTZ,                     -- 가족 클릭 → /listen 진입 시각
    ADD COLUMN IF NOT EXISTS status         TEXT NOT NULL DEFAULT 'ready'
                                            CHECK (status IN ('ready','sent','canceled','failed')),
    ADD COLUMN IF NOT EXISTS sent_at        TIMESTAMPTZ,                     -- T3 발송 성공 시각
    ADD COLUMN IF NOT EXISTS listened_notified_at TIMESTAMPTZ;              -- T4 발신자 알림 발송 시각

CREATE INDEX IF NOT EXISTS idx_care_messages_recipient_status
    ON public.care_messages (recipient_id, status, created_at DESC)
    WHERE recipient_id IS NOT NULL;

-- 가족별 + 날짜별 1행만 유지 (같은 날 중복 prep 방지).
-- KST 자정 기준이 아니라 created_at 의 date 단위 — 매일 cron 이 한 번만 만든다.
CREATE UNIQUE INDEX IF NOT EXISTS care_messages_recipient_day_uniq
    ON public.care_messages (recipient_id, (date_trunc('day', created_at AT TIME ZONE 'Asia/Seoul')))
    WHERE recipient_id IS NOT NULL;

COMMENT ON TABLE public.care_recipients IS
    '안부 메시지 가족 수신자. 우리 사업자 알림톡으로 직접 발송. 옵트아웃 토큰 포함.';
COMMENT ON COLUMN public.care_messages.recipient_id IS
    'NULL = 기존 즉시-생성 흐름 (사용자 카톡 picker). NOT NULL = Phase 2 자동 흐름.';
COMMENT ON COLUMN public.care_messages.status IS
    'ready: prep 완료, 발송 대기 | sent: T3 발송 완료 | canceled: 사용자 취소 | failed: 발송 실패';

	-- 안부 메시지 가족 수신자 + care_messages 확장 (2026-05-12, care_redesign Phase 2)
	--
	-- Phase 2 핵심:
	-- - 가족 수신자 (`care_recipients`) — 라벨/전화번호/음성슬롯/발송시각/1탭\|0탭 모드.
	-- - 매일 KST T-30분 자동 본문생성+TTS → care_messages 행 INSERT (status='ready').
	-- - 1탭 모드: T-0 분 사용자에게 T1 알림톡 → 사용자가 [보내기] 누르면 T3 알림톡 발송.
	-- - 가족이 /listen/<care_id> 클릭 → listened_at 채워짐 → 발신자에게 T4 알림톡.
	--
	-- 회귀 가드 변경:
	-- - 가족 번호 수집 OK (이전 정책 폐기, doc/runbook/care_redesign.md 참조)
	-- - 우리 사업자 알림톡으로 가족에게 직발송 OK
	--
	-- 안티-스토킹 (Phase 4 에서 강화): care_blacklist 추가 예정, 본 migration 에선
	-- 구조만 준비.

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

	-- 가족 식별
	label TEXT NOT NULL, -- '엄마', '아빠', '시어머니' 등 자유
	phone_e164 TEXT NOT NULL, -- +8210xxxxxxxx (정규화 후 저장)

	-- 발송 설정
	voice_slot_kind TEXT NOT NULL DEFAULT 'self'
	CHECK (voice_slot_kind IN ('self','son','daughter')),
	schedule_mode TEXT NOT NULL DEFAULT '1tap'
	CHECK (schedule_mode IN ('1tap','0tap')),
	send_time_kst TIME NOT NULL DEFAULT '09:00', -- KST 기준 일일 발송 시각

	-- 가족 동의
	consent_status TEXT NOT NULL DEFAULT 'active'
	CHECK (consent_status IN ('active','opted_out','blocked')),
	social_consent_confirmed BOOLEAN NOT NULL DEFAULT FALSE, -- 등록 시 "부모님께 양해 구함" 체크

	-- 옵트아웃 (Phase 4)
	optout_token TEXT NOT NULL DEFAULT encode(gen_random_bytes(16), 'hex') UNIQUE,
	opted_out_at TIMESTAMPTZ,

	-- 활성 토글 (사용자가 일시 정지 가능)
	active BOOLEAN NOT NULL DEFAULT TRUE,

	-- 다음 발송 예정 시각 (UTC). cron tick 이 이걸 보고 due 발사.
	next_prep_at TIMESTAMPTZ, -- 본문생성+TTS 시작 시각 (T-30분)

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

	CREATE INDEX IF NOT EXISTS idx_care_recipients_user
	ON public.care_recipients (user_id, created_at DESC);

	CREATE INDEX IF NOT EXISTS idx_care_recipients_due_prep
	ON public.care_recipients (active, next_prep_at)
	WHERE active = TRUE AND consent_status = 'active' AND next_prep_at IS NOT NULL;

	-- 한 사용자 + 같은 전화번호 중복 등록 방지 (라벨만 다르면 별도 등록 가능하게 두는 게
	-- 보통이지만, 안티-스토킹 관점에서 같은 가족에게 2개 row 보낼 일은 없음).
	CREATE UNIQUE INDEX IF NOT EXISTS care_recipients_user_phone_uniq
	ON public.care_recipients (user_id, phone_e164);

	ALTER TABLE public.care_recipients ENABLE ROW LEVEL SECURITY;

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

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

	DROP TRIGGER IF EXISTS care_recipients_touch_updated_at ON public.care_recipients;
	CREATE TRIGGER care_recipients_touch_updated_at
	BEFORE UPDATE ON public.care_recipients
	FOR EACH ROW EXECUTE FUNCTION public._care_recipients_touch_updated_at();

	-- ───────── care_messages 확장 ─────────
	--
	-- 기존 컬럼 (recipient_label, generated_text, audio_file_*, scheduled_at, active 등) 유지.
	-- 새 흐름은 `recipient_id` 가 채워지면 "Phase 2 자동 흐름" 으로 간주, NULL 이면 기존
	-- 즉시-생성 흐름 (사용자 카톡 picker) 으로 호환 유지.

	ALTER TABLE public.care_messages
	ADD COLUMN IF NOT EXISTS recipient_id UUID REFERENCES public.care_recipients(id) ON DELETE SET NULL,
	ADD COLUMN IF NOT EXISTS scheduled_for TIMESTAMPTZ, -- 가족 발송 예정 시각 (UTC)
	ADD COLUMN IF NOT EXISTS listened_at TIMESTAMPTZ, -- 가족 클릭 → /listen 진입 시각
	ADD COLUMN IF NOT EXISTS status TEXT NOT NULL DEFAULT 'ready'
	CHECK (status IN ('ready','sent','canceled','failed')),
	ADD COLUMN IF NOT EXISTS sent_at TIMESTAMPTZ, -- T3 발송 성공 시각
	ADD COLUMN IF NOT EXISTS listened_notified_at TIMESTAMPTZ; -- T4 발신자 알림 발송 시각

	CREATE INDEX IF NOT EXISTS idx_care_messages_recipient_status
	ON public.care_messages (recipient_id, status, created_at DESC)
	WHERE recipient_id IS NOT NULL;

	-- 가족별 + 날짜별 1행만 유지 (같은 날 중복 prep 방지).
	-- KST 자정 기준이 아니라 created_at 의 date 단위 — 매일 cron 이 한 번만 만든다.
	CREATE UNIQUE INDEX IF NOT EXISTS care_messages_recipient_day_uniq
	ON public.care_messages (recipient_id, (date_trunc('day', created_at AT TIME ZONE 'Asia/Seoul')))
	WHERE recipient_id IS NOT NULL;

	COMMENT ON TABLE public.care_recipients IS
	'안부 메시지 가족 수신자. 우리 사업자 알림톡으로 직접 발송. 옵트아웃 토큰 포함.';
	COMMENT ON COLUMN public.care_messages.recipient_id IS
	'NULL = 기존 즉시-생성 흐름 (사용자 카톡 picker). NOT NULL = Phase 2 자동 흐름.';
	COMMENT ON COLUMN public.care_messages.status IS
	'ready: prep 완료, 발송 대기 \| sent: T3 발송 완료 \| canceled: 사용자 취소 \| failed: 발송 실패';