MLM Schema (Partners, Commissions, Ranks)

Partners (Main Entity)

CREATE TABLE mlm.partners (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL UNIQUE REFERENCES core.users(id),
    sponsor_id UUID REFERENCES mlm.partners(id),  -- Direct upline

    referral_code VARCHAR(20) NOT NULL UNIQUE,
    status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    -- PENDING, ACTIVE, SUSPENDED, TERMINATED

    current_rank_id UUID REFERENCES mlm.ranks(id),
    highest_rank_id UUID REFERENCES mlm.ranks(id),

    -- Denormalized counters
    direct_referrals_count INT DEFAULT 0,
    total_network_size INT DEFAULT 0,
    tree_depth INT DEFAULT 0,

    joined_at TIMESTAMP NOT NULL DEFAULT NOW(),
    activated_at TIMESTAMP,

    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_partners_user ON mlm.partners(user_id);
CREATE INDEX idx_partners_sponsor ON mlm.partners(sponsor_id);
CREATE INDEX idx_partners_code ON mlm.partners(referral_code);
CREATE INDEX idx_partners_status ON mlm.partners(status);
CREATE INDEX idx_partners_rank ON mlm.partners(current_rank_id);

Partner Tree Paths (Closure Table)

CREATE TABLE mlm.partner_tree_paths (
    ancestor_id UUID NOT NULL REFERENCES mlm.partners(id) ON DELETE CASCADE,
    descendant_id UUID NOT NULL REFERENCES mlm.partners(id) ON DELETE CASCADE,
    depth INT NOT NULL,

    PRIMARY KEY (ancestor_id, descendant_id)
);

-- Critical indexes for tree queries
CREATE INDEX idx_tree_ancestor_depth ON mlm.partner_tree_paths(ancestor_id, depth);
CREATE INDEX idx_tree_descendant ON mlm.partner_tree_paths(descendant_id);
CREATE INDEX idx_tree_depth ON mlm.partner_tree_paths(depth) WHERE depth BETWEEN 1 AND 10;

Referral Links

CREATE TABLE mlm.referral_links (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    partner_id UUID NOT NULL REFERENCES mlm.partners(id) ON DELETE CASCADE,

    code VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(100),  -- "Instagram", "YouTube", etc.
    target_url VARCHAR(500),  -- Optional specific landing page

    utm_source VARCHAR(100),
    utm_medium VARCHAR(100),
    utm_campaign VARCHAR(100),

    -- Denormalized stats
    clicks_count INT DEFAULT 0,
    registrations_count INT DEFAULT 0,
    conversions_count INT DEFAULT 0,

    is_active BOOLEAN DEFAULT TRUE,
    expires_at TIMESTAMP,

    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_ref_links_partner ON mlm.referral_links(partner_id);
CREATE INDEX idx_ref_links_code ON mlm.referral_links(code);

Referral Attributions

CREATE TABLE mlm.referral_attributions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL UNIQUE REFERENCES core.users(id),

    partner_id UUID NOT NULL REFERENCES mlm.partners(id),
    link_id UUID REFERENCES mlm.referral_links(id),

    attribution_type VARCHAR(20) NOT NULL,  -- FIRST_TOUCH, LAST_TOUCH

    first_touch_at TIMESTAMP,
    last_touch_at TIMESTAMP,
    converted_at TIMESTAMP,

    cookie_id VARCHAR(100),
    ip_address INET,
    user_agent TEXT,

    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_attributions_partner ON mlm.referral_attributions(partner_id);
CREATE INDEX idx_attributions_link ON mlm.referral_attributions(link_id);

Ranks

CREATE TABLE mlm.ranks (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(100) NOT NULL,
    code VARCHAR(50) NOT NULL UNIQUE,
    level INT NOT NULL UNIQUE,  -- 1 = lowest, ascending

    description TEXT,
    badge_url VARCHAR(500),
    color_code VARCHAR(20),

    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_ranks_level ON mlm.ranks(level);

Rank Requirements

CREATE TABLE mlm.rank_requirements (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    rank_id UUID NOT NULL REFERENCES mlm.ranks(id) ON DELETE CASCADE,

    requirement_type VARCHAR(30) NOT NULL,
    -- CAREER_POINTS_TOTAL, CAREER_POINTS_PERIOD, PERSONAL_VOLUME,
    -- GROUP_VOLUME, DIRECT_REFERRALS, ACTIVE_REFERRALS, TEAM_SIZE,
    -- REFERRALS_AT_RANK

    threshold_value DECIMAL(20,2) NOT NULL,
    time_period VARCHAR(20),  -- LIFETIME, MONTHLY, QUARTERLY
    is_mandatory BOOLEAN DEFAULT TRUE,
    description TEXT,

    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_rank_reqs_rank ON mlm.rank_requirements(rank_id);

Partner Rank History

CREATE TABLE mlm.partner_rank_history (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    partner_id UUID NOT NULL REFERENCES mlm.partners(id) ON DELETE CASCADE,

    from_rank_id UUID REFERENCES mlm.ranks(id),
    to_rank_id UUID NOT NULL REFERENCES mlm.ranks(id),

    change_type VARCHAR(20) NOT NULL,
    -- PROMOTION, DEMOTION, INITIAL

    qualification_snapshot JSONB,
    period_id VARCHAR(20),  -- e.g., "2024-01"

    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_rank_history_partner ON mlm.partner_rank_history(partner_id, created_at DESC);

Commission Plans

CREATE TABLE mlm.commission_plans (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(100) NOT NULL,
    code VARCHAR(50) NOT NULL UNIQUE,

    source_type VARCHAR(20) NOT NULL,  -- INVESTMENT, PRODUCT, ALL
    max_levels INT NOT NULL DEFAULT 10,

    is_active BOOLEAN DEFAULT TRUE,
    valid_from TIMESTAMP NOT NULL DEFAULT NOW(),
    valid_to TIMESTAMP,

    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

Commission Tiers

CREATE TABLE mlm.commission_tiers (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    plan_id UUID NOT NULL REFERENCES mlm.commission_plans(id) ON DELETE CASCADE,

    level_depth INT NOT NULL,
    percentage DECIMAL(5,2) NOT NULL,
    career_points_percentage DECIMAL(5,2) DEFAULT 0,

    min_rank_id UUID REFERENCES mlm.ranks(id),
    volume_threshold DECIMAL(20,2),

    UNIQUE(plan_id, level_depth)
);

CREATE INDEX idx_tiers_plan ON mlm.commission_tiers(plan_id);

Reward Distribution Config

CREATE TABLE mlm.reward_distribution_configs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    partner_id UUID NOT NULL REFERENCES mlm.partners(id) ON DELETE CASCADE,

    reward_type VARCHAR(20) NOT NULL,  -- MONETARY, CAREER_POINTS

    to_self_percentage DECIMAL(5,2) NOT NULL DEFAULT 100,
    to_referral_percentage DECIMAL(5,2) NOT NULL DEFAULT 0,

    is_default BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),

    CONSTRAINT valid_percentages CHECK (to_self_percentage + to_referral_percentage = 100),
    UNIQUE(partner_id, reward_type, is_default)
);

CREATE INDEX idx_reward_config_partner ON mlm.reward_distribution_configs(partner_id);

Partner Balances

CREATE TABLE mlm.partner_balances (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    partner_id UUID NOT NULL UNIQUE REFERENCES mlm.partners(id) ON DELETE CASCADE,

    available_balance DECIMAL(20,2) NOT NULL DEFAULT 0,
    pending_balance DECIMAL(20,2) NOT NULL DEFAULT 0,
    total_earned DECIMAL(20,2) NOT NULL DEFAULT 0,
    total_withdrawn DECIMAL(20,2) NOT NULL DEFAULT 0,

    career_points_total DECIMAL(20,2) NOT NULL DEFAULT 0,
    career_points_period DECIMAL(20,2) NOT NULL DEFAULT 0,

    currency VARCHAR(3) NOT NULL DEFAULT 'RUB',
    version INT NOT NULL DEFAULT 1,  -- Optimistic locking

    last_calculated_at TIMESTAMP,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),

    CONSTRAINT non_negative_available CHECK (available_balance >= 0),
    CONSTRAINT non_negative_pending CHECK (pending_balance >= 0)
);

Balance Operations (Concurrency-Safe)

The following functions provide concurrency-safe balance operations. See Concurrency Patterns for usage guidelines.

Optimistic Locking Pattern

Use for low-contention scenarios where retries are acceptable.

-- Function: Update balance with optimistic locking (version check)
CREATE OR REPLACE FUNCTION mlm.update_balance_optimistic(
    p_partner_id UUID,
    p_amount DECIMAL(20,2),
    p_operation VARCHAR(20),  -- 'ADD_PENDING', 'CONFIRM_PENDING', 'WITHDRAW', 'ADD_AVAILABLE'
    p_expected_version INT
) RETURNS TABLE(
    success BOOLEAN,
    new_version INT,
    new_available DECIMAL(20,2),
    new_pending DECIMAL(20,2),
    error_message TEXT
) AS $$
DECLARE
    v_updated INT;
    v_new_version INT;
    v_new_available DECIMAL(20,2);
    v_new_pending DECIMAL(20,2);
BEGIN
    CASE p_operation
        WHEN 'ADD_PENDING' THEN
            UPDATE mlm.partner_balances
            SET pending_balance = pending_balance + p_amount,
                version = version + 1,
                updated_at = NOW()
            WHERE partner_id = p_partner_id
              AND version = p_expected_version
            RETURNING version, available_balance, pending_balance
            INTO v_new_version, v_new_available, v_new_pending;

        WHEN 'ADD_AVAILABLE' THEN
            UPDATE mlm.partner_balances
            SET available_balance = available_balance + p_amount,
                total_earned = total_earned + p_amount,
                version = version + 1,
                updated_at = NOW()
            WHERE partner_id = p_partner_id
              AND version = p_expected_version
            RETURNING version, available_balance, pending_balance
            INTO v_new_version, v_new_available, v_new_pending;

        WHEN 'CONFIRM_PENDING' THEN
            UPDATE mlm.partner_balances
            SET pending_balance = pending_balance - p_amount,
                available_balance = available_balance + p_amount,
                total_earned = total_earned + p_amount,
                version = version + 1,
                updated_at = NOW()
            WHERE partner_id = p_partner_id
              AND version = p_expected_version
              AND pending_balance >= p_amount
            RETURNING version, available_balance, pending_balance
            INTO v_new_version, v_new_available, v_new_pending;

        WHEN 'WITHDRAW' THEN
            UPDATE mlm.partner_balances
            SET available_balance = available_balance - p_amount,
                total_withdrawn = total_withdrawn + p_amount,
                version = version + 1,
                updated_at = NOW()
            WHERE partner_id = p_partner_id
              AND version = p_expected_version
              AND available_balance >= p_amount
            RETURNING version, available_balance, pending_balance
            INTO v_new_version, v_new_available, v_new_pending;

        ELSE
            RETURN QUERY SELECT FALSE, NULL::INT, NULL::DECIMAL(20,2), NULL::DECIMAL(20,2),
                'Invalid operation'::TEXT;
            RETURN;
    END CASE;

    GET DIAGNOSTICS v_updated = ROW_COUNT;

    IF v_updated = 0 THEN
        -- Determine failure reason
        IF NOT EXISTS (SELECT 1 FROM mlm.partner_balances WHERE partner_id = p_partner_id) THEN
            RETURN QUERY SELECT FALSE, NULL::INT, NULL::DECIMAL(20,2), NULL::DECIMAL(20,2),
                'Partner balance not found'::TEXT;
        ELSIF NOT EXISTS (SELECT 1 FROM mlm.partner_balances WHERE partner_id = p_partner_id AND version = p_expected_version) THEN
            RETURN QUERY SELECT FALSE, NULL::INT, NULL::DECIMAL(20,2), NULL::DECIMAL(20,2),
                'Version mismatch (concurrent modification)'::TEXT;
        ELSE
            RETURN QUERY SELECT FALSE, NULL::INT, NULL::DECIMAL(20,2), NULL::DECIMAL(20,2),
                'Insufficient balance'::TEXT;
        END IF;
        RETURN;
    END IF;

    RETURN QUERY SELECT TRUE, v_new_version, v_new_available, v_new_pending, NULL::TEXT;
END;
$$ LANGUAGE plpgsql;

Pessimistic Locking Pattern

Use for high-contention scenarios or when operation cannot be retried.

-- Function: Update balance with pessimistic locking (SELECT FOR UPDATE)
CREATE OR REPLACE FUNCTION mlm.update_balance_pessimistic(
    p_partner_id UUID,
    p_amount DECIMAL(20,2),
    p_operation VARCHAR(20)  -- 'ADD_PENDING', 'CONFIRM_PENDING', 'WITHDRAW', 'ADD_AVAILABLE'
) RETURNS TABLE(
    success BOOLEAN,
    new_version INT,
    new_available DECIMAL(20,2),
    new_pending DECIMAL(20,2),
    error_message TEXT
) AS $$
DECLARE
    v_balance RECORD;
    v_new_version INT;
    v_new_available DECIMAL(20,2);
    v_new_pending DECIMAL(20,2);
BEGIN
    -- Lock the row first (will wait if another transaction holds lock)
    SELECT * INTO v_balance
    FROM mlm.partner_balances
    WHERE partner_id = p_partner_id
    FOR UPDATE;

    IF NOT FOUND THEN
        RETURN QUERY SELECT FALSE, NULL::INT, NULL::DECIMAL(20,2), NULL::DECIMAL(20,2),
            'Partner balance not found'::TEXT;
        RETURN;
    END IF;

    -- Validate and perform operation
    CASE p_operation
        WHEN 'ADD_PENDING' THEN
            UPDATE mlm.partner_balances
            SET pending_balance = pending_balance + p_amount,
                version = version + 1,
                updated_at = NOW()
            WHERE partner_id = p_partner_id
            RETURNING version, available_balance, pending_balance
            INTO v_new_version, v_new_available, v_new_pending;

        WHEN 'ADD_AVAILABLE' THEN
            UPDATE mlm.partner_balances
            SET available_balance = available_balance + p_amount,
                total_earned = total_earned + p_amount,
                version = version + 1,
                updated_at = NOW()
            WHERE partner_id = p_partner_id
            RETURNING version, available_balance, pending_balance
            INTO v_new_version, v_new_available, v_new_pending;

        WHEN 'CONFIRM_PENDING' THEN
            IF v_balance.pending_balance < p_amount THEN
                RETURN QUERY SELECT FALSE, v_balance.version::INT, v_balance.available_balance,
                    v_balance.pending_balance, 'Insufficient pending balance'::TEXT;
                RETURN;
            END IF;

            UPDATE mlm.partner_balances
            SET pending_balance = pending_balance - p_amount,
                available_balance = available_balance + p_amount,
                total_earned = total_earned + p_amount,
                version = version + 1,
                updated_at = NOW()
            WHERE partner_id = p_partner_id
            RETURNING version, available_balance, pending_balance
            INTO v_new_version, v_new_available, v_new_pending;

        WHEN 'WITHDRAW' THEN
            IF v_balance.available_balance < p_amount THEN
                RETURN QUERY SELECT FALSE, v_balance.version::INT, v_balance.available_balance,
                    v_balance.pending_balance, 'Insufficient available balance'::TEXT;
                RETURN;
            END IF;

            UPDATE mlm.partner_balances
            SET available_balance = available_balance - p_amount,
                total_withdrawn = total_withdrawn + p_amount,
                version = version + 1,
                updated_at = NOW()
            WHERE partner_id = p_partner_id
            RETURNING version, available_balance, pending_balance
            INTO v_new_version, v_new_available, v_new_pending;

        ELSE
            RETURN QUERY SELECT FALSE, NULL::INT, NULL::DECIMAL(20,2), NULL::DECIMAL(20,2),
                'Invalid operation'::TEXT;
            RETURN;
    END CASE;

    RETURN QUERY SELECT TRUE, v_new_version, v_new_available, v_new_pending, NULL::TEXT;
END;
$$ LANGUAGE plpgsql;

Batch Balance Update (for Commission Approval)

-- Function: Confirm multiple pending commissions atomically
CREATE OR REPLACE FUNCTION mlm.confirm_pending_commissions(
    p_commission_ids UUID[],
    p_approved_by UUID
) RETURNS TABLE(
    success BOOLEAN,
    processed_count INT,
    error_message TEXT
) AS $$
DECLARE
    v_commission RECORD;
    v_processed INT := 0;
    v_partner_ids UUID[];
BEGIN
    -- 1. Get unique partner IDs and lock their balances in order
    SELECT ARRAY_AGG(DISTINCT partner_id ORDER BY partner_id)
    INTO v_partner_ids
    FROM mlm.commission_transactions
    WHERE id = ANY(p_commission_ids)
      AND status = 'PENDING';

    IF v_partner_ids IS NULL OR array_length(v_partner_ids, 1) = 0 THEN
        RETURN QUERY SELECT FALSE, 0, 'No pending commissions found'::TEXT;
        RETURN;
    END IF;

    -- 2. Lock all affected balances (sorted order prevents deadlock)
    PERFORM 1 FROM mlm.partner_balances
    WHERE partner_id = ANY(v_partner_ids)
    ORDER BY partner_id
    FOR UPDATE;

    -- 3. Process each commission
    FOR v_commission IN
        SELECT ct.id, ct.partner_id, ct.net_amount, ct.career_points
        FROM mlm.commission_transactions ct
        WHERE ct.id = ANY(p_commission_ids)
          AND ct.status = 'PENDING'
        ORDER BY ct.partner_id  -- Process in same order as locks
    LOOP
        -- Update commission status
        UPDATE mlm.commission_transactions
        SET status = 'APPROVED',
            approved_by = p_approved_by,
            processed_at = NOW()
        WHERE id = v_commission.id;

        -- Move from pending to available
        UPDATE mlm.partner_balances
        SET pending_balance = pending_balance - v_commission.net_amount,
            available_balance = available_balance + v_commission.net_amount,
            version = version + 1,
            updated_at = NOW()
        WHERE partner_id = v_commission.partner_id;

        v_processed := v_processed + 1;
    END LOOP;

    RETURN QUERY SELECT TRUE, v_processed, NULL::TEXT;
END;
$$ LANGUAGE plpgsql;

Commission Transactions

CREATE TABLE mlm.commission_transactions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    partner_id UUID NOT NULL REFERENCES mlm.partners(id),

    source_type VARCHAR(20) NOT NULL,  -- ORDER, INVESTMENT, BONUS, RANK_BONUS
    source_id UUID NOT NULL,
    source_partner_id UUID REFERENCES mlm.partners(id),

    level_depth INT,
    plan_id UUID REFERENCES mlm.commission_plans(id),

    gross_amount DECIMAL(20,2) NOT NULL,
    net_amount DECIMAL(20,2) NOT NULL,
    career_points DECIMAL(20,2) DEFAULT 0,

    currency VARCHAR(3) NOT NULL DEFAULT 'RUB',
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    -- PENDING     = Calculated, awaiting approval
    -- APPROVED    = Approved, will be paid in next payout cycle
    -- PAID        = Paid to partner balance
    -- HELD        = On hold (investigation, suspicious activity)
    -- REVERSED    = Reversed due to refund/chargeback
    -- CLAWBACK    = Clawed back from partner balance
    -- CANCELLED   = Cancelled before payment

    -- Reversal tracking
    reversed_from_id UUID REFERENCES mlm.commission_transactions(id),
    reversal_reason TEXT,
    reversed_at TIMESTAMP,
    reversed_by UUID REFERENCES core.users(id),

    period_id VARCHAR(20),
    processed_at TIMESTAMP,
    approved_by UUID REFERENCES core.users(id),

    idempotency_key VARCHAR(255) UNIQUE,

    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_comm_trans_partner ON mlm.commission_transactions(partner_id, created_at DESC);
CREATE INDEX idx_comm_trans_source ON mlm.commission_transactions(source_type, source_id);
CREATE INDEX idx_comm_trans_status ON mlm.commission_transactions(status);
CREATE INDEX idx_comm_trans_period ON mlm.commission_transactions(period_id);
CREATE INDEX idx_comm_trans_status_date ON mlm.commission_transactions(status, created_at DESC);

Commission Reversal Logic

When an order is refunded or a chargeback occurs:

-- Function: Reverse commissions for a refunded order
CREATE OR REPLACE FUNCTION mlm.reverse_commissions_for_order(
    p_order_id UUID,
    p_reason TEXT,
    p_admin_id UUID
) RETURNS INT AS $$
DECLARE
    v_reversed_count INT := 0;
    v_commission RECORD;
BEGIN
    -- Find all commissions tied to this order
    FOR v_commission IN
        SELECT * FROM mlm.commission_transactions
        WHERE source_type = 'ORDER'
          AND source_id = p_order_id
          AND status IN ('PENDING', 'APPROVED', 'PAID')
    LOOP
        -- If already paid, create clawback transaction
        IF v_commission.status = 'PAID' THEN
            INSERT INTO mlm.commission_transactions (
                partner_id, source_type, source_id, source_partner_id,
                level_depth, plan_id, gross_amount, net_amount, career_points,
                currency, status, reversed_from_id, reversal_reason,
                reversed_at, reversed_by, period_id
            ) VALUES (
                v_commission.partner_id, 'CLAWBACK', p_order_id, v_commission.source_partner_id,
                v_commission.level_depth, v_commission.plan_id,
                -v_commission.gross_amount, -v_commission.net_amount, -v_commission.career_points,
                v_commission.currency, 'CLAWBACK', v_commission.id, p_reason,
                NOW(), p_admin_id, v_commission.period_id
            );

            -- Deduct from partner balance
            UPDATE mlm.partner_balances
            SET available_balance = available_balance - v_commission.net_amount,
                career_points_total = career_points_total - v_commission.career_points,
                version = version + 1,
                updated_at = NOW()
            WHERE partner_id = v_commission.partner_id;
        END IF;

        -- Mark original as reversed
        UPDATE mlm.commission_transactions
        SET status = 'REVERSED',
            reversal_reason = p_reason,
            reversed_at = NOW(),
            reversed_by = p_admin_id
        WHERE id = v_commission.id;

        v_reversed_count := v_reversed_count + 1;
    END LOOP;

    RETURN v_reversed_count;
END;
$$ LANGUAGE plpgsql;

Payout Requests

CREATE TABLE mlm.payout_requests (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    partner_id UUID NOT NULL REFERENCES mlm.partners(id),

    amount DECIMAL(20,2) NOT NULL,
    currency VARCHAR(3) NOT NULL DEFAULT 'RUB',

    payout_method_type VARCHAR(20) NOT NULL,  -- BANK_CARD, BANK_TRANSFER, EWALLET
    payout_details JSONB NOT NULL,  -- Encrypted account details

    status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    -- PENDING, APPROVED, PROCESSING, COMPLETED, REJECTED, CANCELLED

    rejection_reason TEXT,
    processed_by UUID REFERENCES core.users(id),
    processed_at TIMESTAMP,
    completed_at TIMESTAMP,

    external_reference VARCHAR(255),
    external_idempotency_key VARCHAR(255) UNIQUE,  -- For payment processor idempotency

    created_at TIMESTAMP NOT NULL DEFAULT NOW(),

    -- Minimum payout amount constraint
    CONSTRAINT min_payout_amount CHECK (amount >= 100)  -- Minimum 100 RUB
);

CREATE INDEX idx_payouts_partner ON mlm.payout_requests(partner_id, created_at DESC);
CREATE INDEX idx_payouts_status ON mlm.payout_requests(status);

-- CRITICAL: Prevent multiple pending payouts per partner (double-spend protection)
-- Only one payout can be in PENDING, APPROVED, or PROCESSING state at a time
CREATE UNIQUE INDEX idx_single_pending_payout
ON mlm.payout_requests(partner_id)
WHERE status IN ('PENDING', 'APPROVED', 'PROCESSING');

Payout Request Flow (Concurrency-Safe)

-- Function: Create payout request with balance deduction (atomic)
CREATE OR REPLACE FUNCTION mlm.create_payout_request(
    p_partner_id UUID,
    p_amount DECIMAL(20,2),
    p_currency VARCHAR(3),
    p_method_type VARCHAR(20),
    p_payout_details JSONB
) RETURNS TABLE(
    success BOOLEAN,
    payout_id UUID,
    error_code VARCHAR(50),
    error_message TEXT
) AS $$
DECLARE
    v_balance RECORD;
    v_payout_id UUID;
    v_existing_pending UUID;
BEGIN
    -- 1. Lock partner balance
    SELECT * INTO v_balance
    FROM mlm.partner_balances
    WHERE partner_id = p_partner_id
    FOR UPDATE;

    IF NOT FOUND THEN
        RETURN QUERY SELECT FALSE, NULL::UUID, 'PARTNER_NOT_FOUND'::VARCHAR(50),
            'Partner balance record not found'::TEXT;
        RETURN;
    END IF;

    -- 2. Check for existing pending payout (redundant with unique index, but provides better error)
    SELECT id INTO v_existing_pending
    FROM mlm.payout_requests
    WHERE partner_id = p_partner_id
      AND status IN ('PENDING', 'APPROVED', 'PROCESSING')
    LIMIT 1;

    IF v_existing_pending IS NOT NULL THEN
        RETURN QUERY SELECT FALSE, v_existing_pending, 'PAYOUT_ALREADY_PENDING'::VARCHAR(50),
            'A payout request is already pending'::TEXT;
        RETURN;
    END IF;

    -- 3. Check sufficient balance
    IF v_balance.available_balance < p_amount THEN
        RETURN QUERY SELECT FALSE, NULL::UUID, 'INSUFFICIENT_BALANCE'::VARCHAR(50),
            format('Insufficient balance: available %s, requested %s',
                   v_balance.available_balance, p_amount)::TEXT;
        RETURN;
    END IF;

    -- 4. Deduct from available balance
    UPDATE mlm.partner_balances
    SET available_balance = available_balance - p_amount,
        version = version + 1,
        updated_at = NOW()
    WHERE partner_id = p_partner_id;

    -- 5. Create payout request
    INSERT INTO mlm.payout_requests (
        partner_id, amount, currency, payout_method_type, payout_details, status
    ) VALUES (
        p_partner_id, p_amount, p_currency, p_method_type, p_payout_details, 'PENDING'
    ) RETURNING id INTO v_payout_id;

    -- 6. Create audit log entry
    INSERT INTO mlm.financial_audit_log (
        event_type, partner_id, amount, currency,
        balance_before, balance_after, source_type, source_id,
        checksum, previous_checksum
    ) VALUES (
        'PAYOUT_REQUESTED', p_partner_id, -p_amount, p_currency,
        v_balance.available_balance, v_balance.available_balance - p_amount,
        'PAYOUT_REQUEST', v_payout_id,
        encode(sha256(concat(p_partner_id::text, p_amount::text, NOW()::text)::bytea), 'hex'),
        (SELECT checksum FROM mlm.financial_audit_log
         WHERE partner_id = p_partner_id ORDER BY created_at DESC LIMIT 1)
    );

    RETURN QUERY SELECT TRUE, v_payout_id, NULL::VARCHAR(50), NULL::TEXT;
END;
$$ LANGUAGE plpgsql;


-- Function: Cancel payout and restore balance
CREATE OR REPLACE FUNCTION mlm.cancel_payout_request(
    p_payout_id UUID,
    p_cancelled_by UUID,
    p_reason TEXT
) RETURNS TABLE(
    success BOOLEAN,
    error_message TEXT
) AS $$
DECLARE
    v_payout RECORD;
BEGIN
    -- 1. Lock the payout request
    SELECT * INTO v_payout
    FROM mlm.payout_requests
    WHERE id = p_payout_id
    FOR UPDATE;

    IF NOT FOUND THEN
        RETURN QUERY SELECT FALSE, 'Payout request not found'::TEXT;
        RETURN;
    END IF;

    -- 2. Check if cancellable
    IF v_payout.status NOT IN ('PENDING', 'APPROVED') THEN
        RETURN QUERY SELECT FALSE,
            format('Cannot cancel payout in status: %s', v_payout.status)::TEXT;
        RETURN;
    END IF;

    -- 3. Lock partner balance
    PERFORM 1 FROM mlm.partner_balances
    WHERE partner_id = v_payout.partner_id
    FOR UPDATE;

    -- 4. Restore balance
    UPDATE mlm.partner_balances
    SET available_balance = available_balance + v_payout.amount,
        version = version + 1,
        updated_at = NOW()
    WHERE partner_id = v_payout.partner_id;

    -- 5. Update payout status
    UPDATE mlm.payout_requests
    SET status = 'CANCELLED',
        rejection_reason = p_reason,
        processed_by = p_cancelled_by,
        processed_at = NOW()
    WHERE id = p_payout_id;

    -- 6. Audit log
    INSERT INTO mlm.financial_audit_log (
        event_type, partner_id, amount, currency,
        source_type, source_id,
        checksum
    )
    SELECT
        'PAYOUT_CANCELLED', v_payout.partner_id, v_payout.amount, v_payout.currency,
        'PAYOUT_REQUEST', p_payout_id,
        encode(sha256(concat(v_payout.partner_id::text, v_payout.amount::text, NOW()::text)::bytea), 'hex');

    RETURN QUERY SELECT TRUE, NULL::TEXT;
END;
$$ LANGUAGE plpgsql;

See Also: Concurrency Patterns for application-level payout handling.

---

Related Documents

• Core Schema
• MLM Tree Implementation
• Commission Engine