🎯 봉이 회원 100만 플랫폼 — 개발 기획서 V1.1 (보완판)

-- ① 회원 (PASS 본인인증 기반 1인1계정) CREATE TABLE bongi_members ( id bigserial PK, phone text UNIQUE NOT NULL, name text, ci text UNIQUE -- PASS CI = 중복가입 차단 키, verified_at timestamptz -- PASS 인증시각 (NULL=미인증 → 보상 제외), referral_code text UNIQUE -- 내 추천코드, referrer_id bigint REFERENCES bongi_members(id) -- 나를 추천한 회원, point_balance int DEFAULT 0 -- 표시용 캐시(원장 합계와 동기화), created_at timestamptz DEFAULT now() ); -- ② 적립 정책 (어드민 관리 · 하드코딩 금지 NO_HARDCODE) CREATE TABLE bongi_point_rules ( reason text PK -- signup·attendance·referral_signup·open_phone·internet·used_phone·rental·ticket_buy, point int NOT NULL, daily_cap int, active boolean DEFAULT true ); -- ③ 포인트 원장 (단일 진실원천 · 잔액 = SUM(delta)) CREATE TABLE bongi_point_ledger ( id bigserial PK, member_id bigint NOT NULL REFERENCES bongi_members(id), delta int NOT NULL -- +적립 / −사용(응모권구매·현금전환), reason text, ref_type text, ref_id bigint, device_fp text, ip text -- 어뷰징 추적, created_at timestamptz DEFAULT now() ); -- ④ 응모권 (월별 · 보유 = SUM(qty) WHERE period) CREATE TABLE bongi_tickets ( id bigserial PK, member_id bigint NOT NULL REFERENCES bongi_members(id), period text NOT NULL -- 'YYYYMM', qty int NOT NULL, source text NOT NULL -- auto(기본1장)·buy(P구매)·referral, created_at timestamptz DEFAULT now() ); -- ⑤ 추천 (1인 1추천인 · PASS 인증 회원만) CREATE TABLE bongi_referrals ( id bigserial PK, referrer_id bigint NOT NULL REFERENCES bongi_members(id), referee_id bigint NOT NULL UNIQUE REFERENCES bongi_members(id), status text DEFAULT 'joined' -- joined·rewarded, reward_point int, reward_ticket int, created_at timestamptz DEFAULT now() ); -- ⑥ 앰배서더(추천왕) 월별 점수·랭킹 CREATE TABLE bongi_ambassador_score ( id bigserial PK, member_id bigint NOT NULL REFERENCES bongi_members(id), period text NOT NULL, score int DEFAULT 0, breakdown jsonb -- {referral_signup:n, open_phone:n,...}, rank int, UNIQUE(member_id, period) ); -- ⑦ 경품 이벤트 (월 1회) CREATE TABLE bongi_prize_events ( id bigserial PK, period text UNIQUE NOT NULL, budget int, prizes jsonb -- [{rank:1,name:'골드바',count:1},...], draw_at timestamptz, status text DEFAULT 'open' -- open·drawn·paid ); -- ⑧ 응모/당첨 (회원 1행 · 응모수 = 자동1+구매+추천) CREATE TABLE bongi_prize_entries ( id bigserial PK, event_id bigint NOT NULL REFERENCES bongi_prize_events(id), member_id bigint NOT NULL REFERENCES bongi_members(id), ticket_qty int NOT NULL, won_rank int -- NULL=미당첨, payout_status text -- pending·paid, tax_withheld int -- 제세공과금, UNIQUE(event_id, member_id) );