113 lines
5.3 KiB
SQL
113 lines
5.3 KiB
SQL
-- Migration: 004_affiliate_tables.sql
|
|
-- Affiliate system: referral codes, conversions, payouts, wallets
|
|
-- Created: 2026-04-12
|
|
|
|
-- ============================================================
|
|
-- Table 1: referral_codes
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS referral_codes (
|
|
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
code TEXT NOT NULL UNIQUE,
|
|
owner_user_id UUID NOT NULL REFERENCES auth.users(id),
|
|
affiliate_tier TEXT NOT NULL DEFAULT 'standard' CHECK (affiliate_tier IN ('standard', 'premium', 'founder')),
|
|
commission_pct NUMERIC(5,2) NOT NULL DEFAULT 15.00,
|
|
max_uses INTEGER,
|
|
current_uses INTEGER NOT NULL DEFAULT 0,
|
|
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
expires_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- ============================================================
|
|
-- Table 2: wallet_addresses (before payouts — FK dependency)
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS wallet_addresses (
|
|
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
user_id UUID NOT NULL REFERENCES auth.users(id),
|
|
chain TEXT NOT NULL CHECK (chain IN ('ethereum', 'polygon', 'solana', 'bitcoin', 'base', 'arbitrum')),
|
|
address TEXT NOT NULL,
|
|
label TEXT,
|
|
is_primary BOOLEAN NOT NULL DEFAULT FALSE,
|
|
is_verified BOOLEAN NOT NULL DEFAULT FALSE,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- ============================================================
|
|
-- Table 3: referral_conversions
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS referral_conversions (
|
|
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
referral_code_id UUID NOT NULL REFERENCES referral_codes(id),
|
|
referred_user_id UUID NOT NULL REFERENCES auth.users(id),
|
|
referrer_user_id UUID NOT NULL REFERENCES auth.users(id),
|
|
conversion_type TEXT NOT NULL CHECK (conversion_type IN ('signup', 'upgrade', 'renewal')),
|
|
subscription_tier TEXT NOT NULL CHECK (subscription_tier IN ('analyst', 'desk')),
|
|
revenue_amount NUMERIC(10,2) NOT NULL DEFAULT 0,
|
|
commission_amount NUMERIC(10,2) NOT NULL DEFAULT 0,
|
|
status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'confirmed', 'paid', 'disputed', 'refunded')),
|
|
converted_at TIMESTAMPTZ DEFAULT NOW(),
|
|
confirmed_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- ============================================================
|
|
-- Table 4: affiliate_payouts
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS affiliate_payouts (
|
|
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
affiliate_user_id UUID NOT NULL REFERENCES auth.users(id),
|
|
payout_amount NUMERIC(10,2) NOT NULL,
|
|
payout_method TEXT NOT NULL CHECK (payout_method IN ('crypto', 'paypal', 'bank_transfer', 'stripe')),
|
|
wallet_address_id UUID REFERENCES wallet_addresses(id),
|
|
status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'completed', 'failed')),
|
|
transaction_hash TEXT,
|
|
period_start DATE NOT NULL,
|
|
period_end DATE NOT NULL,
|
|
conversions_included INTEGER NOT NULL DEFAULT 0,
|
|
requested_at TIMESTAMPTZ DEFAULT NOW(),
|
|
processed_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- ============================================================
|
|
-- Indexes
|
|
-- ============================================================
|
|
CREATE INDEX IF NOT EXISTS idx_referral_codes_owner ON referral_codes(owner_user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_referral_codes_code ON referral_codes(code);
|
|
CREATE INDEX IF NOT EXISTS idx_referral_conversions_code ON referral_conversions(referral_code_id);
|
|
CREATE INDEX IF NOT EXISTS idx_referral_conversions_referrer ON referral_conversions(referrer_user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_referral_conversions_referred ON referral_conversions(referred_user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_referral_conversions_status ON referral_conversions(status);
|
|
CREATE INDEX IF NOT EXISTS idx_affiliate_payouts_user ON affiliate_payouts(affiliate_user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_affiliate_payouts_status ON affiliate_payouts(status);
|
|
CREATE INDEX IF NOT EXISTS idx_wallet_addresses_user ON wallet_addresses(user_id);
|
|
|
|
-- ============================================================
|
|
-- Row Level Security
|
|
-- ============================================================
|
|
ALTER TABLE referral_codes ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE referral_conversions ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE affiliate_payouts ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE wallet_addresses ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- referral_codes: public read, service role full access
|
|
CREATE POLICY "referral_codes_public_read" ON referral_codes
|
|
FOR SELECT USING (true);
|
|
|
|
CREATE POLICY "referral_codes_service_write" ON referral_codes
|
|
FOR ALL USING (auth.role() = 'service_role');
|
|
|
|
-- referral_conversions: service role full access only
|
|
CREATE POLICY "referral_conversions_service_access" ON referral_conversions
|
|
FOR ALL USING (auth.role() = 'service_role');
|
|
|
|
-- affiliate_payouts: service role full access only
|
|
CREATE POLICY "affiliate_payouts_service_access" ON affiliate_payouts
|
|
FOR ALL USING (auth.role() = 'service_role');
|
|
|
|
-- wallet_addresses: service role full access only
|
|
CREATE POLICY "wallet_addresses_service_access" ON wallet_addresses
|
|
FOR ALL USING (auth.role() = 'service_role');
|