Spaces:

stylevoice
/

talk

Sleeping

talk / backend /sql /049_gift_coupons.sql

user

Deploy StyleVoiceChat backend

aa9892b 4 months ago

3.25 kB

-- 카페 음성 선물쿠폰 MVP — gift_coupons (2026-05-28, P1a Phase B)
--
-- 발행된 쿠폰 = 결제 완료 → 받는이 펼침 → 매장 사용처리.
-- 요청서: doc/runbook/req_cafe_gift_mvp.md §1
-- 설계: doc/runbook/cafe_gift_p1a.md §2 sql/049
--
-- 분리된 토큰 (Plan 결정 #2, #6):
--   - code: 8자 영숫자 (O/0/I/1 제외, 알파벳 32) — 매장 직원 수기 입력용
--   - reveal_token: 32 byte hex (DB default) — 받는이 /g/<token> reveal 진입용
--   - 둘 다 unguessable + 분리 = code 노출 시 reveal_token 안전, reveal 시 code 안 노출 가능
--
-- 만료:
--   - expires_at = gift_orders.expires_at 복사 (백엔드가 set, 정책 변경 추적)
--   - status='expired' 자동 전이는 cron (후속), P1a 는 라이브 조회 시 만료 검사
--
-- ⛔ 회귀 가드: 신규 테이블. RLS cafe owner only (사장 redeem 권한).

CREATE TABLE IF NOT EXISTS public.gift_coupons (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    order_id        UUID NOT NULL UNIQUE REFERENCES public.gift_orders(id) ON DELETE CASCADE,
    cafe_id         UUID NOT NULL REFERENCES public.cafe_merchants(id) ON DELETE RESTRICT,

    -- 매장 직원 수기 입력용 (사람이 읽기 쉬운 코드)
    code            TEXT NOT NULL UNIQUE,
    -- 받는이 펼침용 (백엔드가 생성, 추측 불가)
    reveal_token    TEXT NOT NULL UNIQUE
                    DEFAULT encode(gen_random_bytes(20), 'hex'),

    status          TEXT NOT NULL DEFAULT 'issued'
                    CHECK (status IN ('issued','viewed','redeemed','expired','refunded')),
    viewed_at       TIMESTAMPTZ,                       -- 받는이가 /g/<token> 첫 진입 시각
    redeemed_at     TIMESTAMPTZ,
    redeemed_by     UUID REFERENCES auth.users(id) ON DELETE SET NULL,  -- 사용처리한 매장 직원

    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at      TIMESTAMPTZ NOT NULL              -- order.expires_at 복사 (백엔드 세팅)
);

CREATE INDEX IF NOT EXISTS idx_gift_coupons_cafe_status
    ON public.gift_coupons (cafe_id, status, created_at DESC);

ALTER TABLE public.gift_coupons ENABLE ROW LEVEL SECURITY;

-- 사장: 본인 카페 쿠폰 SELECT + UPDATE (redeem)
DROP POLICY IF EXISTS gift_coupons_cafe_rw ON public.gift_coupons;
CREATE POLICY gift_coupons_cafe_rw ON public.gift_coupons FOR ALL
    USING (cafe_id IN (
        SELECT id FROM public.cafe_merchants WHERE owner_user_id = auth.uid()
    ))
    WITH CHECK (cafe_id IN (
        SELECT id FROM public.cafe_merchants WHERE owner_user_id = auth.uid()
    ));

-- INSERT 는 백엔드 service-role (gift_order_service.mock_pay 가 발급)
-- reveal_token 조회는 백엔드 service-role (gift_reveal 라우터 — Phase C)

COMMENT ON TABLE public.gift_coupons IS
    '발행 쿠폰. code = 매장 직원 입력용 8자 영숫자. reveal_token = 받는이 펼침용 hex.';
COMMENT ON COLUMN public.gift_coupons.code IS
    '8자 영숫자 알파벳 ABCDEFGHJKLMNPQRSTUVWXYZ23456789 (O/0/I/1 제외, 가독성). 백엔드 생성 + UNIQUE 충돌 재시도.';
COMMENT ON COLUMN public.gift_coupons.reveal_token IS
    '32 byte hex. 받는이 /g/<token> 진입. 백엔드만 service-role 로 조회.';

	-- 카페 음성 선물쿠폰 MVP — gift_coupons (2026-05-28, P1a Phase B)
	--
	-- 발행된 쿠폰 = 결제 완료 → 받는이 펼침 → 매장 사용처리.
	-- 요청서: doc/runbook/req_cafe_gift_mvp.md §1
	-- 설계: doc/runbook/cafe_gift_p1a.md §2 sql/049
	--
	-- 분리된 토큰 (Plan 결정 #2, #6):
	-- - code: 8자 영숫자 (O/0/I/1 제외, 알파벳 32) — 매장 직원 수기 입력용
	-- - reveal_token: 32 byte hex (DB default) — 받는이 /g/<token> reveal 진입용
	-- - 둘 다 unguessable + 분리 = code 노출 시 reveal_token 안전, reveal 시 code 안 노출 가능
	--
	-- 만료:
	-- - expires_at = gift_orders.expires_at 복사 (백엔드가 set, 정책 변경 추적)
	-- - status='expired' 자동 전이는 cron (후속), P1a 는 라이브 조회 시 만료 검사
	--
	-- ⛔ 회귀 가드: 신규 테이블. RLS cafe owner only (사장 redeem 권한).

	CREATE TABLE IF NOT EXISTS public.gift_coupons (
	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

	order_id UUID NOT NULL UNIQUE REFERENCES public.gift_orders(id) ON DELETE CASCADE,
	cafe_id UUID NOT NULL REFERENCES public.cafe_merchants(id) ON DELETE RESTRICT,

	-- 매장 직원 수기 입력용 (사람이 읽기 쉬운 코드)
	code TEXT NOT NULL UNIQUE,
	-- 받는이 펼침용 (백엔드가 생성, 추측 불가)
	reveal_token TEXT NOT NULL UNIQUE
	DEFAULT encode(gen_random_bytes(20), 'hex'),

	status TEXT NOT NULL DEFAULT 'issued'
	CHECK (status IN ('issued','viewed','redeemed','expired','refunded')),
	viewed_at TIMESTAMPTZ, -- 받는이가 /g/<token> 첫 진입 시각
	redeemed_at TIMESTAMPTZ,
	redeemed_by UUID REFERENCES auth.users(id) ON DELETE SET NULL, -- 사용처리한 매장 직원

	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
	expires_at TIMESTAMPTZ NOT NULL -- order.expires_at 복사 (백엔드 세팅)
	);

	CREATE INDEX IF NOT EXISTS idx_gift_coupons_cafe_status
	ON public.gift_coupons (cafe_id, status, created_at DESC);

	ALTER TABLE public.gift_coupons ENABLE ROW LEVEL SECURITY;

	-- 사장: 본인 카페 쿠폰 SELECT + UPDATE (redeem)
	DROP POLICY IF EXISTS gift_coupons_cafe_rw ON public.gift_coupons;
	CREATE POLICY gift_coupons_cafe_rw ON public.gift_coupons FOR ALL
	USING (cafe_id IN (
	SELECT id FROM public.cafe_merchants WHERE owner_user_id = auth.uid()
	))
	WITH CHECK (cafe_id IN (
	SELECT id FROM public.cafe_merchants WHERE owner_user_id = auth.uid()
	));

	-- INSERT 는 백엔드 service-role (gift_order_service.mock_pay 가 발급)
	-- reveal_token 조회는 백엔드 service-role (gift_reveal 라우터 — Phase C)

	COMMENT ON TABLE public.gift_coupons IS
	'발행 쿠폰. code = 매장 직원 입력용 8자 영숫자. reveal_token = 받는이 펼침용 hex.';
	COMMENT ON COLUMN public.gift_coupons.code IS
	'8자 영숫자 알파벳 ABCDEFGHJKLMNPQRSTUVWXYZ23456789 (O/0/I/1 제외, 가독성). 백엔드 생성 + UNIQUE 충돌 재시도.';
	COMMENT ON COLUMN public.gift_coupons.reveal_token IS
	'32 byte hex. 받는이 /g/<token> 진입. 백엔드만 service-role 로 조회.';