Spaces:

stylevoice
/

talk

Sleeping

talk / backend /sql /018_device_daily_usage.sql

user

Deploy StyleVoiceChat backend

aa9892b 4 months ago

2.3 kB

-- 단말 단위 일일 크레딧 사용량 추적 (2026-05-06).
--
-- 배경: "계정당 일 100 크레딧" 정책은 동일 사용자가 Google 계정을 다중
-- 생성해 단말 1개로 무한 크레딧 획득 가능. 어뷰즈 차단을 위해
-- (공인 IP + 단말 fingerprint) 단위로 일일 cap 적용.
--
-- 정책 (services/device_cap_service.py):
--   - Free 사용자: 단말당 일 100 크레딧 합산 cap
--   - Standard/Pro 결제 사용자: 단말 cap 면제 (계정 cap 만 적용)
--
-- 단말 식별:
--   - client_ip: 공인 IP (X-Forwarded-For 서버 측 추출)
--   - device_fp: frontend 가 X-Device-FP 헤더로 전송 (canvas+UA+screen
--     +timezone+lang+hardware 종합 SHA-256 hash)
--   - 보조: device_id (localStorage UUID, X-Device-ID 헤더) — fp 보강용

CREATE TABLE IF NOT EXISTS public.device_daily_usage (
    id BIGSERIAL PRIMARY KEY,
    client_ip TEXT NOT NULL,
    device_fp TEXT NOT NULL,
    device_id TEXT,                              -- localStorage UUID (선택)
    date DATE NOT NULL,                          -- KST 기준
    used_credits INTEGER NOT NULL DEFAULT 0,
    last_user_id UUID,                           -- 디버깅용: 최근 사용 계정
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX IF NOT EXISTS uq_device_daily_usage
    ON public.device_daily_usage (client_ip, device_fp, date);

CREATE INDEX IF NOT EXISTS idx_device_daily_usage_date
    ON public.device_daily_usage (date DESC);

-- 운영 안전: 30일 이상 된 row 자동 정리 cron 후속 (services 측). RLS 비활성
-- (서비스 롤만 접근, 사용자 직접 조회 안 함).
ALTER TABLE public.device_daily_usage ENABLE ROW LEVEL SECURITY;
-- 정책 미설정 = 서비스 롤만 통과. 사용자 SELECT 불가.

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

DROP TRIGGER IF EXISTS trg_device_daily_usage_updated_at ON public.device_daily_usage;
CREATE TRIGGER trg_device_daily_usage_updated_at
  BEFORE UPDATE ON public.device_daily_usage
  FOR EACH ROW EXECUTE FUNCTION public.tg_device_daily_usage_updated_at();

	-- 단말 단위 일일 크레딧 사용량 추적 (2026-05-06).
	--
	-- 배경: "계정당 일 100 크레딧" 정책은 동일 사용자가 Google 계정을 다중
	-- 생성해 단말 1개로 무한 크레딧 획득 가능. 어뷰즈 차단을 위해
	-- (공인 IP + 단말 fingerprint) 단위로 일일 cap 적용.
	--
	-- 정책 (services/device_cap_service.py):
	-- - Free 사용자: 단말당 일 100 크레딧 합산 cap
	-- - Standard/Pro 결제 사용자: 단말 cap 면제 (계정 cap 만 적용)
	--
	-- 단말 식별:
	-- - client_ip: 공인 IP (X-Forwarded-For 서버 측 추출)
	-- - device_fp: frontend 가 X-Device-FP 헤더로 전송 (canvas+UA+screen
	-- +timezone+lang+hardware 종합 SHA-256 hash)
	-- - 보조: device_id (localStorage UUID, X-Device-ID 헤더) — fp 보강용

	CREATE TABLE IF NOT EXISTS public.device_daily_usage (
	id BIGSERIAL PRIMARY KEY,
	client_ip TEXT NOT NULL,
	device_fp TEXT NOT NULL,
	device_id TEXT, -- localStorage UUID (선택)
	date DATE NOT NULL, -- KST 기준
	used_credits INTEGER NOT NULL DEFAULT 0,
	last_user_id UUID, -- 디버깅용: 최근 사용 계정
	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
	updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
	);

	CREATE UNIQUE INDEX IF NOT EXISTS uq_device_daily_usage
	ON public.device_daily_usage (client_ip, device_fp, date);

	CREATE INDEX IF NOT EXISTS idx_device_daily_usage_date
	ON public.device_daily_usage (date DESC);

	-- 운영 안전: 30일 이상 된 row 자동 정리 cron 후속 (services 측). RLS 비활성
	-- (서비스 롤만 접근, 사용자 직접 조회 안 함).
	ALTER TABLE public.device_daily_usage ENABLE ROW LEVEL SECURITY;
	-- 정책 미설정 = 서비스 롤만 통과. 사용자 SELECT 불가.

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

	DROP TRIGGER IF EXISTS trg_device_daily_usage_updated_at ON public.device_daily_usage;
	CREATE TRIGGER trg_device_daily_usage_updated_at
	BEFORE UPDATE ON public.device_daily_usage
	FOR EACH ROW EXECUTE FUNCTION public.tg_device_daily_usage_updated_at();