Spaces:

stylevoice
/

talk

Sleeping

talk / backend /sql /010_subscriptions.sql

user

Deploy StyleVoiceChat backend

aa9892b 4 months ago

4.46 kB

-- 구독 + 결제 이벤트 (2026-05-03)
--
-- CCBill / Epoch 계약 전 스켈레톤. 계약 후 webhook 통합으로 채워넣음.
--
-- 플랜 구조:
--   free:     $0/월   · 일 100 크레딧
--   standard: $20/월  · 일 1,000 크레딧 + 🔞 hard 모드 활성화
--   pro:      $100/월 · 일 5,000 크레딧 + 우선 처리
--
-- 결제사 (processor):
--   'ccbill'  : 메인 (어덜트 카드 결제)
--   'epoch'   : 백업 (어덜트 카드 결제)
--   'manual'  : 관리자 직접 부여 (베타·프로모션)
--   'coupon'  : 쿠폰 코드로 부여된 임시 구독

CREATE TABLE IF NOT EXISTS public.subscriptions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    plan VARCHAR(16) NOT NULL CHECK (plan IN ('free', 'standard', 'pro')),
    status VARCHAR(16) NOT NULL DEFAULT 'pending'
        CHECK (status IN ('pending', 'active', 'past_due', 'canceled', 'expired')),
    processor VARCHAR(16) NOT NULL DEFAULT 'manual'
        CHECK (processor IN ('ccbill', 'epoch', 'manual', 'coupon')),
    processor_subscription_id VARCHAR(128),  -- CCBill subscription ID 등 외부 식별자
    started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    current_period_start TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    current_period_end TIMESTAMPTZ,           -- 다음 결제일 (구독 종료 예정 시점)
    canceled_at TIMESTAMPTZ,                  -- 사용자가 취소 요청한 시점 (period_end 까진 활성)
    metadata JSONB NOT NULL DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 한 사용자에 대해 active 구독은 최대 1개 (DB 레벨 enforcement)
CREATE UNIQUE INDEX IF NOT EXISTS idx_subscriptions_user_active
    ON public.subscriptions(user_id)
    WHERE status = 'active';

-- 사용자별 구독 이력 조회 빈도 높음
CREATE INDEX IF NOT EXISTS idx_subscriptions_user_created
    ON public.subscriptions(user_id, created_at DESC);

-- 결제사 webhook ID 조회 (중복 처리 방지용)
CREATE INDEX IF NOT EXISTS idx_subscriptions_processor_id
    ON public.subscriptions(processor, processor_subscription_id)
    WHERE processor_subscription_id IS NOT NULL;

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

-- 결제 이벤트 로그 — webhook 수신·재시도·refund·chargeback 모두 추적
CREATE TABLE IF NOT EXISTS public.billing_events (
    id BIGSERIAL PRIMARY KEY,
    subscription_id UUID REFERENCES public.subscriptions(id) ON DELETE SET NULL,
    user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
    event_type VARCHAR(32) NOT NULL,
        -- 'subscription_created', 'subscription_renewed', 'subscription_canceled',
        -- 'payment_success', 'payment_failed', 'refund_issued', 'chargeback',
        -- 'webhook_received', 'webhook_processed', 'webhook_failed'
    processor VARCHAR(16) NOT NULL DEFAULT 'manual',
    processor_event_id VARCHAR(128),  -- 외부 결제사 이벤트 ID (idempotency)
    amount_usd_cents INTEGER,         -- 금액 (USD 센트, 결제 이벤트만)
    payload JSONB NOT NULL DEFAULT '{}',
    error_message TEXT,
    occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_billing_events_user
    ON public.billing_events(user_id, occurred_at DESC);
CREATE INDEX IF NOT EXISTS idx_billing_events_subscription
    ON public.billing_events(subscription_id, occurred_at DESC);
-- webhook 중복 수신 차단용
CREATE UNIQUE INDEX IF NOT EXISTS idx_billing_events_processor_event
    ON public.billing_events(processor, processor_event_id)
    WHERE processor_event_id IS NOT NULL;

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

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

DROP TRIGGER IF EXISTS trg_subscriptions_updated_at ON public.subscriptions;
CREATE TRIGGER trg_subscriptions_updated_at
  BEFORE UPDATE ON public.subscriptions
  FOR EACH ROW EXECUTE FUNCTION public.tg_subscriptions_updated_at();

	-- 구독 + 결제 이벤트 (2026-05-03)
	--
	-- CCBill / Epoch 계약 전 스켈레톤. 계약 후 webhook 통합으로 채워넣음.
	--
	-- 플랜 구조:
	-- free: $0/월 · 일 100 크레딧
	-- standard: $20/월 · 일 1,000 크레딧 + 🔞 hard 모드 활성화
	-- pro: $100/월 · 일 5,000 크레딧 + 우선 처리
	--
	-- 결제사 (processor):
	-- 'ccbill' : 메인 (어덜트 카드 결제)
	-- 'epoch' : 백업 (어덜트 카드 결제)
	-- 'manual' : 관리자 직접 부여 (베타·프로모션)
	-- 'coupon' : 쿠폰 코드로 부여된 임시 구독

	CREATE TABLE IF NOT EXISTS public.subscriptions (
	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
	plan VARCHAR(16) NOT NULL CHECK (plan IN ('free', 'standard', 'pro')),
	status VARCHAR(16) NOT NULL DEFAULT 'pending'
	CHECK (status IN ('pending', 'active', 'past_due', 'canceled', 'expired')),
	processor VARCHAR(16) NOT NULL DEFAULT 'manual'
	CHECK (processor IN ('ccbill', 'epoch', 'manual', 'coupon')),
	processor_subscription_id VARCHAR(128), -- CCBill subscription ID 등 외부 식별자
	started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
	current_period_start TIMESTAMPTZ NOT NULL DEFAULT NOW(),
	current_period_end TIMESTAMPTZ, -- 다음 결제일 (구독 종료 예정 시점)
	canceled_at TIMESTAMPTZ, -- 사용자가 취소 요청한 시점 (period_end 까진 활성)
	metadata JSONB NOT NULL DEFAULT '{}',
	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
	updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
	);

	-- 한 사용자에 대해 active 구독은 최대 1개 (DB 레벨 enforcement)
	CREATE UNIQUE INDEX IF NOT EXISTS idx_subscriptions_user_active
	ON public.subscriptions(user_id)
	WHERE status = 'active';

	-- 사용자별 구독 이력 조회 빈도 높음
	CREATE INDEX IF NOT EXISTS idx_subscriptions_user_created
	ON public.subscriptions(user_id, created_at DESC);

	-- 결제사 webhook ID 조회 (중복 처리 방지용)
	CREATE INDEX IF NOT EXISTS idx_subscriptions_processor_id
	ON public.subscriptions(processor, processor_subscription_id)
	WHERE processor_subscription_id IS NOT NULL;

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

	-- 결제 이벤트 로그 — webhook 수신·재시도·refund·chargeback 모두 추적
	CREATE TABLE IF NOT EXISTS public.billing_events (
	id BIGSERIAL PRIMARY KEY,
	subscription_id UUID REFERENCES public.subscriptions(id) ON DELETE SET NULL,
	user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
	event_type VARCHAR(32) NOT NULL,
	-- 'subscription_created', 'subscription_renewed', 'subscription_canceled',
	-- 'payment_success', 'payment_failed', 'refund_issued', 'chargeback',
	-- 'webhook_received', 'webhook_processed', 'webhook_failed'
	processor VARCHAR(16) NOT NULL DEFAULT 'manual',
	processor_event_id VARCHAR(128), -- 외부 결제사 이벤트 ID (idempotency)
	amount_usd_cents INTEGER, -- 금액 (USD 센트, 결제 이벤트만)
	payload JSONB NOT NULL DEFAULT '{}',
	error_message TEXT,
	occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
	);

	CREATE INDEX IF NOT EXISTS idx_billing_events_user
	ON public.billing_events(user_id, occurred_at DESC);
	CREATE INDEX IF NOT EXISTS idx_billing_events_subscription
	ON public.billing_events(subscription_id, occurred_at DESC);
	-- webhook 중복 수신 차단용
	CREATE UNIQUE INDEX IF NOT EXISTS idx_billing_events_processor_event
	ON public.billing_events(processor, processor_event_id)
	WHERE processor_event_id IS NOT NULL;

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

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

	DROP TRIGGER IF EXISTS trg_subscriptions_updated_at ON public.subscriptions;
	CREATE TRIGGER trg_subscriptions_updated_at
	BEFORE UPDATE ON public.subscriptions
	FOR EACH ROW EXECUTE FUNCTION public.tg_subscriptions_updated_at();