Spaces:

stylevoice
/

talk

Sleeping

talk / backend /sql /009_age_verification.sql

user

Deploy StyleVoiceChat backend

aa9892b 4 months ago

2.88 kB

-- 18+ 연령 인증 (2026-05-03)
--
-- 성인 대화 모드(spice_level=hot) 및 자유대화 채팅·음성 진입 시 필요한
-- 자가 인증(self-attestation) + 동의 로그 보관.
--
-- 향후 정식 본인인증(다날·PASS 등) 통합 자리 마련. method 컬럼이 인증 수단을 구분.
--
-- 한국 정보통신망법 / 일본 특상법(JP 확장 시) 모두 만족하는 최소 요건:
--   - 명시적 동의 시점 기록 (verified_at)
--   - 동의 시 IP·user_agent 기록 (분쟁 시 입증)
--   - 동의 본문의 버전 추적 (consent_text_version) — 약관 변경 시 재동의 트리거
--   - 철회(revoke) 가능성 — 사용자 요청 시 즉시 무효화

CREATE TABLE IF NOT EXISTS public.user_age_verifications (
    user_id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
    verified BOOLEAN NOT NULL DEFAULT false,
    verified_at TIMESTAMPTZ,
    method VARCHAR(32) NOT NULL DEFAULT 'self_attestation',
        -- 'self_attestation' | 'danal_phone' | 'pass' | 'ipin' (확장 자리)
    ip_address INET,
    user_agent TEXT,
    consent_text_version VARCHAR(16) NOT NULL DEFAULT 'v1',
    revoked_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- RLS: 본인만 조회, service_role(백엔드)만 수정
ALTER TABLE public.user_age_verifications ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can read own age verification" ON public.user_age_verifications
    FOR SELECT USING (auth.uid() = user_id);

-- 동의 이력 로그 (감사용 — 동의·철회·재동의 모두 추적)
CREATE TABLE IF NOT EXISTS public.age_verification_logs (
    id BIGSERIAL PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    event VARCHAR(16) NOT NULL,  -- 'verified' | 'revoked' | 're_verified'
    method VARCHAR(32) NOT NULL DEFAULT 'self_attestation',
    ip_address INET,
    user_agent TEXT,
    consent_text_version VARCHAR(16) NOT NULL DEFAULT 'v1',
    detail JSONB NOT NULL DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_age_verification_logs_user
    ON public.age_verification_logs(user_id, created_at DESC);

ALTER TABLE public.age_verification_logs ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can read own age logs" ON public.age_verification_logs
    FOR SELECT USING (auth.uid() = user_id);

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

DROP TRIGGER IF EXISTS trg_age_verifications_updated_at ON public.user_age_verifications;
CREATE TRIGGER trg_age_verifications_updated_at
  BEFORE UPDATE ON public.user_age_verifications
  FOR EACH ROW EXECUTE FUNCTION public.tg_age_verifications_updated_at();

	-- 18+ 연령 인증 (2026-05-03)
	--
	-- 성인 대화 모드(spice_level=hot) 및 자유대화 채팅·음성 진입 시 필요한
	-- 자가 인증(self-attestation) + 동의 로그 보관.
	--
	-- 향후 정식 본인인증(다날·PASS 등) 통합 자리 마련. method 컬럼이 인증 수단을 구분.
	--
	-- 한국 정보통신망법 / 일본 특상법(JP 확장 시) 모두 만족하는 최소 요건:
	-- - 명시적 동의 시점 기록 (verified_at)
	-- - 동의 시 IP·user_agent 기록 (분쟁 시 입증)
	-- - 동의 본문의 버전 추적 (consent_text_version) — 약관 변경 시 재동의 트리거
	-- - 철회(revoke) 가능성 — 사용자 요청 시 즉시 무효화

	CREATE TABLE IF NOT EXISTS public.user_age_verifications (
	user_id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
	verified BOOLEAN NOT NULL DEFAULT false,
	verified_at TIMESTAMPTZ,
	method VARCHAR(32) NOT NULL DEFAULT 'self_attestation',
	-- 'self_attestation' \| 'danal_phone' \| 'pass' \| 'ipin' (확장 자리)
	ip_address INET,
	user_agent TEXT,
	consent_text_version VARCHAR(16) NOT NULL DEFAULT 'v1',
	revoked_at TIMESTAMPTZ,
	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
	updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
	);

	-- RLS: 본인만 조회, service_role(백엔드)만 수정
	ALTER TABLE public.user_age_verifications ENABLE ROW LEVEL SECURITY;
	CREATE POLICY "Users can read own age verification" ON public.user_age_verifications
	FOR SELECT USING (auth.uid() = user_id);

	-- 동의 이력 로그 (감사용 — 동의·철회·재동의 모두 추적)
	CREATE TABLE IF NOT EXISTS public.age_verification_logs (
	id BIGSERIAL PRIMARY KEY,
	user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
	event VARCHAR(16) NOT NULL, -- 'verified' \| 'revoked' \| 're_verified'
	method VARCHAR(32) NOT NULL DEFAULT 'self_attestation',
	ip_address INET,
	user_agent TEXT,
	consent_text_version VARCHAR(16) NOT NULL DEFAULT 'v1',
	detail JSONB NOT NULL DEFAULT '{}',
	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
	);

	CREATE INDEX IF NOT EXISTS idx_age_verification_logs_user
	ON public.age_verification_logs(user_id, created_at DESC);

	ALTER TABLE public.age_verification_logs ENABLE ROW LEVEL SECURITY;
	CREATE POLICY "Users can read own age logs" ON public.age_verification_logs
	FOR SELECT USING (auth.uid() = user_id);

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

	DROP TRIGGER IF EXISTS trg_age_verifications_updated_at ON public.user_age_verifications;
	CREATE TRIGGER trg_age_verifications_updated_at
	BEFORE UPDATE ON public.user_age_verifications
	FOR EACH ROW EXECUTE FUNCTION public.tg_age_verifications_updated_at();