Spaces:

stylevoice
/

talk

Sleeping

talk / backend /sql /019_device_user_links.sql

user

Deploy StyleVoiceChat backend

aa9892b 4 months ago

1.55 kB

-- 단말 ↔ 사용자 매핑 (어뷰즈 의심 검출, 2026-05-06).
--
-- 배경: device_daily_usage 의 last_user_id 는 마지막 1개 계정만 저장하여
-- 동일 단말의 다중 계정 사용 추적이 안 됨. 별도 매핑 테이블로 (단말,
-- 사용자) unique 조합을 누적 → admin 화면에서 "이 단말이 N개 계정으로
-- 접속" 통계 가능.
--
-- 정책:
--   - PK = (client_ip, device_fp, user_id) 3 컬럼 unique
--   - upsert 시 last_seen_at 갱신, total_credits_used 누적
--   - admin 모니터링용 — 일반 사용자 영향 없음

CREATE TABLE IF NOT EXISTS public.device_user_links (
    client_ip TEXT NOT NULL,
    device_fp TEXT NOT NULL,
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    first_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    last_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    total_credits_used INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (client_ip, device_fp, user_id)
);

-- 단말별 사용자 수 집계용
CREATE INDEX IF NOT EXISTS idx_device_user_links_device
    ON public.device_user_links (client_ip, device_fp);

-- 사용자별 단말 수 집계용 (역방향)
CREATE INDEX IF NOT EXISTS idx_device_user_links_user
    ON public.device_user_links (user_id);

-- 최근 활동 정렬용
CREATE INDEX IF NOT EXISTS idx_device_user_links_seen
    ON public.device_user_links (last_seen_at DESC);

ALTER TABLE public.device_user_links ENABLE ROW LEVEL SECURITY;
-- 정책 미설정 = 서비스 롤만 통과. 사용자 직접 SELECT 불가.

	-- 단말 ↔ 사용자 매핑 (어뷰즈 의심 검출, 2026-05-06).
	--
	-- 배경: device_daily_usage 의 last_user_id 는 마지막 1개 계정만 저장하여
	-- 동일 단말의 다중 계정 사용 추적이 안 됨. 별도 매핑 테이블로 (단말,
	-- 사용자) unique 조합을 누적 → admin 화면에서 "이 단말이 N개 계정으로
	-- 접속" 통계 가능.
	--
	-- 정책:
	-- - PK = (client_ip, device_fp, user_id) 3 컬럼 unique
	-- - upsert 시 last_seen_at 갱신, total_credits_used 누적
	-- - admin 모니터링용 — 일반 사용자 영향 없음

	CREATE TABLE IF NOT EXISTS public.device_user_links (
	client_ip TEXT NOT NULL,
	device_fp TEXT NOT NULL,
	user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
	first_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
	last_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
	total_credits_used INTEGER NOT NULL DEFAULT 0,
	PRIMARY KEY (client_ip, device_fp, user_id)
	);

	-- 단말별 사용자 수 집계용
	CREATE INDEX IF NOT EXISTS idx_device_user_links_device
	ON public.device_user_links (client_ip, device_fp);

	-- 사용자별 단말 수 집계용 (역방향)
	CREATE INDEX IF NOT EXISTS idx_device_user_links_user
	ON public.device_user_links (user_id);

	-- 최근 활동 정렬용
	CREATE INDEX IF NOT EXISTS idx_device_user_links_seen
	ON public.device_user_links (last_seen_at DESC);

	ALTER TABLE public.device_user_links ENABLE ROW LEVEL SECURITY;
	-- 정책 미설정 = 서비스 롤만 통과. 사용자 직접 SELECT 불가.