Spaces:

stylevoice
/

talk

Sleeping

talk / backend /sql /008_laugh_kinds.sql

user

Deploy StyleVoiceChat backend

aa9892b 4 months ago

4.15 kB

-- Phase 4 (laugh register): 웃음 종류 (kind) 메타 + 키워드 매핑.
--
-- 배경: 기존 admin_laugh_templates 는 length_class 만으로 분류했는데,
-- ㅋㅋㅋ 같은 짧은 웃음에 2초 wav 가 과해서 "짧은/긴" 길이 분할 + "기본/수줍은/박장대소/킥킥"
-- 같은 톤별 분류가 필요해졌다. 이 마이그레이션은 길이 분할은 일단 두고
-- (현재 length_class 컬럼은 그대로 유지) **종류** 축만 새로 도입한다.
--
-- 키워드는 admin UI 에서 편집 가능 (laugh_kinds.keywords text[]). 런타임 분류기
-- (`laugh_split.classify_laugh_kind`) 가 이 테이블을 캐시해서 텍스트 → kind 매핑.
-- priority 가 높을수록 먼저 매칭 (예: 'shy' 의 'ㅎㅎ' 가 'default' 의 일반 매칭보다
-- 먼저 잡히도록).

CREATE TABLE IF NOT EXISTS public.laugh_kinds (
  kind TEXT PRIMARY KEY,                            -- 'default' | 'shy' | 'burst' | 'chuckle' (확장 가능)
  label TEXT NOT NULL,                              -- 한글 표시명 (admin UI)
  keywords TEXT[] NOT NULL DEFAULT '{}',            -- 매칭 토큰 (예: ['ㅎㅎ','헤헤','흐흐'])
  priority INT NOT NULL DEFAULT 0,                  -- 매칭 우선순위 (높은 값 먼저)
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  description TEXT,                                 -- admin 메모
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

ALTER TABLE public.laugh_kinds ENABLE ROW LEVEL SECURITY;
-- 정책 없음: service_role 만 접근 (admin endpoint 통해서만 수정).

COMMENT ON TABLE public.laugh_kinds IS
  'Phase 4 (laugh register): 웃음 종류 메타 + 키워드 매핑. admin UI 에서 편집 가능.';
COMMENT ON COLUMN public.laugh_kinds.priority IS
  '매칭 우선순위 — burst/shy/chuckle 처럼 좁은 매칭이 default 보다 먼저 잡혀야 한다.';

-- 초기 시드 — 4종 + 합의된 키워드.
-- priority: burst > chuckle > shy > default (좁은 매칭 우선)
INSERT INTO public.laugh_kinds (kind, label, keywords, priority, description) VALUES
  ('burst',   '박장대소', ARRAY['푸하하','푸하','으하하','으하','와하하','깔깔','캬캬','캬하'], 30,
   '터지는 웃음 — 큰 소리로 갑작스럽게 터져 나옴.'),
  ('chuckle', '킥킥/참는웃음', ARRAY['킥킥','낄낄','쿡쿡','푸핫','풉','피식'], 20,
   '참다 새는 짧은 웃음.'),
  ('shy',     '수줍은',  ARRAY['ㅎㅎ','ㅎㅎㅎ','헤헤','헤헷','흐흐','흐흣','히히','히힛'], 10,
   '부드럽고 작은 웃음.'),
  ('default', '기본',    ARRAY['하하','하하하','ㅋㅋ','ㅋㅋㅋ','ㅋㅋㅋㅋ','크크','호호'], 0,
   '일반 웃음 — 매칭 안된 모든 웃음의 폴백.')
ON CONFLICT (kind) DO NOTHING;

-- admin_laugh_templates: kind 컬럼 추가 (FK 는 굳이 안 검 — 가벼운 lookup).
ALTER TABLE public.admin_laugh_templates
  ADD COLUMN IF NOT EXISTS kind TEXT NOT NULL DEFAULT 'default';

CREATE INDEX IF NOT EXISTS admin_laugh_templates_kind_active_idx
  ON public.admin_laugh_templates (kind, is_active)
  WHERE is_active = TRUE;

-- user_laugh_cache: kind 컬럼 추가 (admin_laugh_templates.kind 동기 — 조인 비용 줄이려고
-- 미러링). 새 행은 _convert_one 이 채우고, 기존 행은 아래 백필 + 추후 rebuild_for_voice
-- 자동 갱신.
ALTER TABLE public.user_laugh_cache
  ADD COLUMN IF NOT EXISTS kind TEXT NOT NULL DEFAULT 'default';

-- 기존 cache 행의 kind 백필 — 템플릿에서 끌어옴.
UPDATE public.user_laugh_cache c
SET kind = t.kind
FROM public.admin_laugh_templates t
WHERE c.template_id = t.id
  AND (c.kind IS NULL OR c.kind = 'default')
  AND t.kind <> 'default';

CREATE INDEX IF NOT EXISTS user_laugh_cache_kind_idx
  ON public.user_laugh_cache (user_id, voice_storage_path, kind)
  WHERE ready = TRUE;

COMMENT ON COLUMN public.admin_laugh_templates.kind IS
  '웃음 종류 (laugh_kinds.kind 참조). 채팅 chunker 가 텍스트→kind 분류 후 매칭.';
COMMENT ON COLUMN public.user_laugh_cache.kind IS
  'admin_laugh_templates.kind 미러 — 조회 단순화용.';

	-- Phase 4 (laugh register): 웃음 종류 (kind) 메타 + 키워드 매핑.
	--
	-- 배경: 기존 admin_laugh_templates 는 length_class 만으로 분류했는데,
	-- ㅋㅋㅋ 같은 짧은 웃음에 2초 wav 가 과해서 "짧은/긴" 길이 분할 + "기본/수줍은/박장대소/킥킥"
	-- 같은 톤별 분류가 필요해졌다. 이 마이그레이션은 길이 분할은 일단 두고
	-- (현재 length_class 컬럼은 그대로 유지) 종류 축만 새로 도입한다.
	--
	-- 키워드는 admin UI 에서 편집 가능 (laugh_kinds.keywords text[]). 런타임 분류기
	-- (`laugh_split.classify_laugh_kind`) 가 이 테이블을 캐시해서 텍스트 → kind 매핑.
	-- priority 가 높을수록 먼저 매칭 (예: 'shy' 의 'ㅎㅎ' 가 'default' 의 일반 매칭보다
	-- 먼저 잡히도록).

	CREATE TABLE IF NOT EXISTS public.laugh_kinds (
	kind TEXT PRIMARY KEY, -- 'default' \| 'shy' \| 'burst' \| 'chuckle' (확장 가능)
	label TEXT NOT NULL, -- 한글 표시명 (admin UI)
	keywords TEXT[] NOT NULL DEFAULT '{}', -- 매칭 토큰 (예: ['ㅎㅎ','헤헤','흐흐'])
	priority INT NOT NULL DEFAULT 0, -- 매칭 우선순위 (높은 값 먼저)
	is_active BOOLEAN NOT NULL DEFAULT TRUE,
	description TEXT, -- admin 메모
	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
	updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
	);

	ALTER TABLE public.laugh_kinds ENABLE ROW LEVEL SECURITY;
	-- 정책 없음: service_role 만 접근 (admin endpoint 통해서만 수정).

	COMMENT ON TABLE public.laugh_kinds IS
	'Phase 4 (laugh register): 웃음 종류 메타 + 키워드 매핑. admin UI 에서 편집 가능.';
	COMMENT ON COLUMN public.laugh_kinds.priority IS
	'매칭 우선순위 — burst/shy/chuckle 처럼 좁은 매칭이 default 보다 먼저 잡혀야 한다.';

	-- 초기 시드 — 4종 + 합의된 키워드.
	-- priority: burst > chuckle > shy > default (좁은 매칭 우선)
	INSERT INTO public.laugh_kinds (kind, label, keywords, priority, description) VALUES
	('burst', '박장대소', ARRAY['푸하하','푸하','으하하','으하','와하하','깔깔','캬캬','캬하'], 30,
	'터지는 웃음 — 큰 소리로 갑작스럽게 터져 나옴.'),
	('chuckle', '킥킥/참는웃음', ARRAY['킥킥','낄낄','쿡쿡','푸핫','풉','피식'], 20,
	'참다 새는 짧은 웃음.'),
	('shy', '수줍은', ARRAY['ㅎㅎ','ㅎㅎㅎ','헤헤','헤헷','흐흐','흐흣','히히','히힛'], 10,
	'부드럽고 작은 웃음.'),
	('default', '기본', ARRAY['하하','하하하','ㅋㅋ','ㅋㅋㅋ','ㅋㅋㅋㅋ','크크','호호'], 0,
	'일반 웃음 — 매칭 안된 모든 웃음의 폴백.')
	ON CONFLICT (kind) DO NOTHING;

	-- admin_laugh_templates: kind 컬럼 추가 (FK 는 굳이 안 검 — 가벼운 lookup).
	ALTER TABLE public.admin_laugh_templates
	ADD COLUMN IF NOT EXISTS kind TEXT NOT NULL DEFAULT 'default';

	CREATE INDEX IF NOT EXISTS admin_laugh_templates_kind_active_idx
	ON public.admin_laugh_templates (kind, is_active)
	WHERE is_active = TRUE;

	-- user_laugh_cache: kind 컬럼 추가 (admin_laugh_templates.kind 동기 — 조인 비용 줄이려고
	-- 미러링). 새 행은 _convert_one 이 채우고, 기존 행은 아래 백필 + 추후 rebuild_for_voice
	-- 자동 갱신.
	ALTER TABLE public.user_laugh_cache
	ADD COLUMN IF NOT EXISTS kind TEXT NOT NULL DEFAULT 'default';

	-- 기존 cache 행의 kind 백필 — 템플릿에서 끌어옴.
	UPDATE public.user_laugh_cache c
	SET kind = t.kind
	FROM public.admin_laugh_templates t
	WHERE c.template_id = t.id
	AND (c.kind IS NULL OR c.kind = 'default')
	AND t.kind <> 'default';

	CREATE INDEX IF NOT EXISTS user_laugh_cache_kind_idx
	ON public.user_laugh_cache (user_id, voice_storage_path, kind)
	WHERE ready = TRUE;

	COMMENT ON COLUMN public.admin_laugh_templates.kind IS
	'웃음 종류 (laugh_kinds.kind 참조). 채팅 chunker 가 텍스트→kind 분류 후 매칭.';
	COMMENT ON COLUMN public.user_laugh_cache.kind IS
	'admin_laugh_templates.kind 미러 — 조회 단순화용.';