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

The system uses 21 ranks (Consultant 0-11, with PRO variants for ranks 4-11).

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

    -- PRO variant flag
    is_pro BOOLEAN NOT NULL DEFAULT FALSE,
    base_rank_code VARCHAR(50),  -- For PRO ranks, references base rank code

    -- Qualification requirement (USD)
    turnover_requirement_usd DECIMAL(20,2) NOT NULL DEFAULT 0,

    -- Commission rates (percentages)
    personal_sales_rate DECIMAL(5,2) NOT NULL,  -- 3% - 20%
    entrance_fee_rate DECIMAL(5,2) NOT NULL,    -- 10.5% - 20%
    passive_income_rate DECIMAL(5,2) NOT NULL,  -- 0% - 20%

    -- Leadership pool eligibility
    leadership_pool_eligible BOOLEAN NOT NULL DEFAULT FALSE,

    description TEXT,
    display_name_ru VARCHAR(100),  -- Russian display name
    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);
CREATE INDEX idx_ranks_is_pro ON mlm.ranks(is_pro);
CREATE INDEX idx_ranks_pool_eligible ON mlm.ranks(leadership_pool_eligible) WHERE leadership_pool_eligible = TRUE;

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,
    -- STRUCTURE_TURNOVER_USD      = Total structure turnover in USD
    -- PERSONAL_PURCHASE_ACTIVATION = Personal purchase required (Rank 0→1)

    threshold_value_usd DECIMAL(20,2) NOT NULL,
    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);

Note: Rank qualification is based solely on Total Structure Turnover (USD). The only exception is Rank 0→1 which requires a $1,100 personal purchase.

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);

Leadership Pools

Leadership pools distribute a percentage of company turnover to qualified high-rank partners.

CREATE TABLE mlm.leadership_pools (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    pool_code VARCHAR(20) NOT NULL UNIQUE,  -- 'POOL_5', 'POOL_6', etc.

    eligible_ranks VARCHAR[] NOT NULL,  -- ['5', '5_PRO'] for Pool 5
    percentage_of_turnover DECIMAL(5,4) NOT NULL,  -- 0.01 = 1%, 0.005 = 0.5%
    distribution_frequency VARCHAR(20) NOT NULL,  -- WEEKLY, MONTHLY

    -- Qualification volumes per rank (JSONB)
    -- Example: {"5": 5000, "5_PRO": 10000}
    qualification_volumes JSONB NOT NULL,

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

CREATE INDEX idx_pools_frequency ON mlm.leadership_pools(distribution_frequency);

Pool Configuration Data

Pool Code	Ranks	% of Turnover	Frequency	Qualification
POOL_5	5, 5_PRO	1%	Weekly	$5,000 / $10,000 weekly volume
POOL_6	6, 6_PRO	0.5%	Weekly	$20,000 / $30,000 weekly volume
POOL_7	7, 7_PRO	0.5%	Weekly	$45,000 / $60,000 weekly volume
POOL_8	8, 8_PRO	0.5%	Weekly	$90,000 / $120,000 weekly volume
POOL_9	9, 9_PRO	1%	Monthly	Rank achievement only*
POOL_10	10, 10_PRO	1%	Monthly	Rank achievement only*
POOL_11	11, 11_PRO	1%	Monthly	Rank achievement only*

*Pools 9-11 do not require separate volume qualification. The rank's turnover requirement ($10M-$800M) serves as qualification.

Pool Distributions

Records each pool distribution event.

CREATE TABLE mlm.pool_distributions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    pool_id UUID NOT NULL REFERENCES mlm.leadership_pools(id),

    period_start TIMESTAMP NOT NULL,
    period_end TIMESTAMP NOT NULL,

    total_turnover_usd DECIMAL(20,2) NOT NULL,
    pool_amount_usd DECIMAL(20,2) NOT NULL,
    qualified_participants INT NOT NULL,
    per_person_amount_usd DECIMAL(20,2) NOT NULL,

    status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    -- PENDING, PROCESSING, DISTRIBUTED, FAILED

    distributed_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_pool_dist_pool ON mlm.pool_distributions(pool_id);
CREATE INDEX idx_pool_dist_period ON mlm.pool_distributions(period_start, period_end);
CREATE INDEX idx_pool_dist_status ON mlm.pool_distributions(status);

Pool Participations

Tracks individual partner participation in each pool distribution.

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

    qualified BOOLEAN NOT NULL,
    qualification_volume_usd DECIMAL(20,2),
    amount_received_usd DECIMAL(20,2),

    -- Branch volumes for 50% rule validation
    -- Example: {"branch_1": 50000, "branch_2": 30000, "branch_3": 20000}
    branch_volumes JSONB,

    disqualification_reason TEXT,  -- If not qualified, why

    created_at TIMESTAMP DEFAULT NOW(),

    UNIQUE(distribution_id, partner_id)
);

CREATE INDEX idx_pool_part_distribution ON mlm.pool_participations(distribution_id);
CREATE INDEX idx_pool_part_partner ON mlm.pool_participations(partner_id);
CREATE INDEX idx_pool_part_qualified ON mlm.pool_participations(qualified) WHERE qualified = TRUE;

50% Branch Rule

No single branch can contribute more than 50% of the required qualification volume:

-- Function: Check if partner qualifies for pool (with 50% rule)
CREATE OR REPLACE FUNCTION mlm.check_pool_qualification(
    p_partner_id UUID,
    p_required_volume DECIMAL(20,2)
) RETURNS TABLE(
    qualified BOOLEAN,
    total_volume DECIMAL(20,2),
    branch_volumes JSONB,
    reason TEXT
) AS $$
DECLARE
    v_branch_volumes JSONB := '{}';
    v_total_volume DECIMAL(20,2) := 0;
    v_max_per_branch DECIMAL(20,2);
    v_capped_total DECIMAL(20,2) := 0;
    v_branch RECORD;
BEGIN
    v_max_per_branch := p_required_volume * 0.5;

    -- Get volume from each direct downline branch
    FOR v_branch IN
        SELECT
            p.id as branch_root_id,
            COALESCE(SUM(turnover.amount_usd), 0) as branch_volume
        FROM mlm.partners p
        LEFT JOIN mlm.partner_turnovers turnover ON turnover.partner_id IN (
            SELECT descendant_id FROM mlm.partner_tree_paths
            WHERE ancestor_id = p.id
        )
        WHERE p.sponsor_id = p_partner_id
        GROUP BY p.id
    LOOP
        v_branch_volumes := v_branch_volumes ||
            jsonb_build_object(v_branch.branch_root_id::text, v_branch.branch_volume);
        v_total_volume := v_total_volume + v_branch.branch_volume;

        -- Apply 50% cap per branch
        v_capped_total := v_capped_total + LEAST(v_branch.branch_volume, v_max_per_branch);
    END LOOP;

    IF v_capped_total >= p_required_volume THEN
        RETURN QUERY SELECT TRUE, v_total_volume, v_branch_volumes, NULL::TEXT;
    ELSE
        RETURN QUERY SELECT FALSE, v_total_volume, v_branch_volumes,
            format('Capped volume %s < required %s', v_capped_total, p_required_volume)::TEXT;
    END IF;
END;
$$ LANGUAGE plpgsql;

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,

    -- USD Balances (primary currency)
    available_balance_usd DECIMAL(20,2) NOT NULL DEFAULT 0,
    pending_balance_usd DECIMAL(20,2) NOT NULL DEFAULT 0,
    total_earned_usd DECIMAL(20,2) NOT NULL DEFAULT 0,
    total_withdrawn_usd DECIMAL(20,2) NOT NULL DEFAULT 0,

    -- Structure turnover (for rank qualification)
    total_structure_turnover_usd DECIMAL(20,2) NOT NULL DEFAULT 0,
    personal_sales_usd DECIMAL(20,2) NOT NULL DEFAULT 0,

    -- Income breakdown by type
    income_personal_sales_usd DECIMAL(20,2) NOT NULL DEFAULT 0,
    income_team_sales_usd DECIMAL(20,2) NOT NULL DEFAULT 0,
    income_repeat_sales_usd DECIMAL(20,2) NOT NULL DEFAULT 0,
    income_portfolio_returns_usd DECIMAL(20,2) NOT NULL DEFAULT 0,
    income_client_profits_usd DECIMAL(20,2) NOT NULL DEFAULT 0,
    income_network_profits_usd DECIMAL(20,2) NOT NULL DEFAULT 0,
    income_leadership_pool_usd DECIMAL(20,2) NOT NULL DEFAULT 0,

    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_usd >= 0),
    CONSTRAINT non_negative_pending CHECK (pending_balance_usd >= 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),

    -- Income type (7 types)
    income_type VARCHAR(30) NOT NULL,
    -- PERSONAL_SALES    = Type 1: Direct sales to own clients
    -- TEAM_SALES        = Type 2: Differential from downline sales
    -- REPEAT_SALES      = Type 3: Repeat purchases from clients
    -- PORTFOLIO_RETURNS = Type 4: Own investment returns
    -- CLIENT_PROFITS    = Type 5: Share of client investment profits
    -- NETWORK_PROFITS   = Type 6: Differential from network passive income
    -- LEADERSHIP_POOL   = Type 7: Pool distribution share

    source_type VARCHAR(20) NOT NULL,  -- ORDER, INVESTMENT, INVESTMENT_PROFIT, POOL_DISTRIBUTION
    source_id UUID NOT NULL,
    source_partner_id UUID REFERENCES mlm.partners(id),  -- Partner who triggered this (for differential)

    -- Differential calculation details
    own_rate DECIMAL(5,2),      -- Your commission rate at time of calculation
    source_rate DECIMAL(5,2),   -- Source partner's rate (for differential)
    differential_rate DECIMAL(5,2),  -- own_rate - source_rate

    plan_id UUID REFERENCES mlm.commission_plans(id),

    gross_amount_usd DECIMAL(20,2) NOT NULL,
    net_amount_usd DECIMAL(20,2) NOT NULL,

    currency VARCHAR(3) NOT NULL DEFAULT 'USD',
    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_usd DECIMAL(20,2) NOT NULL,
    currency VARCHAR(3) NOT NULL DEFAULT 'USD',

    payout_method_type VARCHAR(20) NOT NULL,  -- BANK_CARD, BANK_TRANSFER, EWALLET, CRYPTO
    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_usd >= 10)  -- Minimum $10 USD
);

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