Схема MLM (Партнеры, Комиссии, Ранги)

Партнеры (Основная сущность)

sql

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),  -- Прямой спонсор

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

    -- Денормализованные счетчики
    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);

Пути партнерского дерева (Closure Table)

sql

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

-- Критические индексы для запросов по дереву
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;

Реферальные ссылки

sql

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" и т.д.
    target_url VARCHAR(500),  -- Опциональная целевая страница

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

    -- Денормализованная статистика
    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);

Реферальные атрибуции

sql

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

Ранги

Система использует 21 ранг (Консультант 0-11, с PRO вариантами для рангов 4-11).

sql

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' и т.д.
    level INT NOT NULL UNIQUE,  -- 1 = самый низкий, по возрастанию

    -- Флаг PRO варианта
    is_pro BOOLEAN NOT NULL DEFAULT FALSE,
    base_rank_code VARCHAR(50),  -- Для PRO рангов, ссылается на базовый код ранга

    -- Требование квалификации (USD)
    turnover_requirement_usd DECIMAL(20,2) NOT NULL DEFAULT 0,

    -- Комиссионные ставки (проценты)
    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_eligible BOOLEAN NOT NULL DEFAULT FALSE,

    description TEXT,
    display_name_ru VARCHAR(100),  -- Русское отображаемое имя
    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;

Требования к рангам

sql

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       = Общий оборот структуры в USD
    -- PERSONAL_PURCHASE_ACTIVATION = Личная покупка для активации (Ранг 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);

Примечание: Квалификация на ранг основана исключительно на Общем Обороте Структуры (USD). Единственное исключение — переход с Ранга 0 на Ранг 1, который требует личной покупки на $1,100.

История рангов партнера

sql

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),  -- например, "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);

Лидерские пулы

Лидерские пулы распределяют процент от оборота компании между квалифицированными партнерами высоких рангов.

sql

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

    eligible_ranks VARCHAR[] NOT NULL,  -- ['5', '5_PRO'] для Пула 5
    percentage_of_turnover DECIMAL(5,4) NOT NULL,  -- 0.01 = 1%, 0.005 = 0.5%
    distribution_frequency VARCHAR(20) NOT NULL,  -- WEEKLY, MONTHLY

    -- Квалификационные объемы по рангам (JSONB)
    -- Пример: {"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_5	5, 5_PRO	1%	Еженедельно	$5,000 / $10,000 недельный объём
POOL_6	6, 6_PRO	0.5%	Еженедельно	$20,000 / $30,000 недельный объём
POOL_7	7, 7_PRO	0.5%	Еженедельно	$45,000 / $60,000 недельный объём
POOL_8	8, 8_PRO	0.5%	Еженедельно	$90,000 / $120,000 недельный объём
POOL_9	9, 9_PRO	1%	Ежемесячно	Только достижение ранга*
POOL_10	10, 10_PRO	1%	Ежемесячно	Только достижение ранга*
POOL_11	11, 11_PRO	1%	Ежемесячно	Только достижение ранга*

*Пулы 9-11 не требуют отдельного квалификационного объёма. Требование ранга по обороту ($10M-$800M) служит квалификацией.

Распределения пулов

Записывает каждое событие распределения пула.

sql

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

Участие в пулах

Отслеживает индивидуальное участие партнера в каждом распределении пула.

sql

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

    -- Объемы веток для проверки правила 50%
    -- Пример: {"branch_1": 50000, "branch_2": 30000, "branch_3": 20000}
    branch_volumes JSONB,

    disqualification_reason TEXT,  -- Если не квалифицирован, почему

    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% веток

Ни одна ветка не может вносить более 50% требуемого квалификационного объема:

sql

-- Функция: Проверка квалификации партнера для пула (с правилом 50%)
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;

    -- Получаем объем от каждой прямой ветки
    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;

        -- Применяем ограничение 50% на ветку
        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('Ограниченный объем %s < требуемого %s', v_capped_total, p_required_volume)::TEXT;
    END IF;
END;
$$ LANGUAGE plpgsql;

Комиссионные планы

sql

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

Комиссионные уровни

sql

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

Конфигурация распределения вознаграждений

sql

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

Балансы партнеров

sql

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 (основная валюта)
    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,

    -- Оборот структуры (для квалификации на ранг)
    total_structure_turnover_usd DECIMAL(20,2) NOT NULL DEFAULT 0,
    personal_sales_usd DECIMAL(20,2) NOT NULL DEFAULT 0,

    -- Разбивка дохода по типам
    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,  -- Оптимистичная блокировка

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

Операции с балансом (Безопасные для конкурентного доступа)

Следующие функции обеспечивают безопасные операции с балансом при конкурентном доступе. См. Паттерны конкурентности для рекомендаций по использованию.

Паттерн оптимистичной блокировки

Используйте для сценариев с низкой конкуренцией, где допустимы повторные попытки.

sql

-- Функция: Обновление баланса с оптимистичной блокировкой (проверка версии)
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
        -- Определяем причину неудачи
        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;

Паттерн пессимистичной блокировки

Используйте для сценариев с высокой конкуренцией или когда операция не может быть повторена.

sql

-- Функция: Обновление баланса с пессимистичной блокировкой (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
    -- Сначала блокируем строку (будет ждать, если другая транзакция держит блокировку)
    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;

    -- Проверяем и выполняем операцию
    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;

Пакетное обновление баланса (для подтверждения комиссий)

sql

-- Функция: Атомарное подтверждение нескольких ожидающих комиссий
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. Получаем уникальные ID партнеров и блокируем их балансы по порядку
    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. Блокируем все затронутые балансы (отсортированный порядок предотвращает взаимоблокировку)
    PERFORM 1 FROM mlm.partner_balances
    WHERE partner_id = ANY(v_partner_ids)
    ORDER BY partner_id
    FOR UPDATE;

    -- 3. Обрабатываем каждую комиссию
    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  -- Обрабатываем в том же порядке, что и блокировки
    LOOP
        -- Обновляем статус комиссии
        UPDATE mlm.commission_transactions
        SET status = 'APPROVED',
            approved_by = p_approved_by,
            processed_at = NOW()
        WHERE id = v_commission.id;

        -- Переносим из ожидающих в доступные
        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;

Комиссионные транзакции

sql

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

    -- Тип дохода (7 типов)
    income_type VARCHAR(30) NOT NULL,
    -- PERSONAL_SALES    = Тип 1: Прямые продажи собственным клиентам
    -- TEAM_SALES        = Тип 2: Дифференциал от продаж нижестоящих
    -- REPEAT_SALES      = Тип 3: Повторные покупки клиентов
    -- PORTFOLIO_RETURNS = Тип 4: Доходы от собственных инвестиций
    -- CLIENT_PROFITS    = Тип 5: Доля от прибыли клиентов от инвестиций
    -- NETWORK_PROFITS   = Тип 6: Дифференциал от пассивного дохода сети
    -- LEADERSHIP_POOL   = Тип 7: Доля от распределения пула

    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),  -- Партнер, который инициировал (для дифференциала)

    -- Детали расчета дифференциала
    own_rate DECIMAL(5,2),      -- Ваша комиссионная ставка на момент расчета
    source_rate DECIMAL(5,2),   -- Ставка партнера-источника (для дифференциала)
    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     = Рассчитано, ожидает подтверждения
    -- APPROVED    = Подтверждено, будет выплачено в следующем цикле выплат
    -- PAID        = Выплачено на баланс партнера
    -- HELD        = Заморожено (расследование, подозрительная активность)
    -- REVERSED    = Отменено из-за возврата/чарджбэка
    -- CLAWBACK    = Возвращено с баланса партнера
    -- CANCELLED   = Отменено до выплаты

    -- Отслеживание отмен
    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);

Логика отмены комиссий

При возврате заказа или чарджбэке:

sql

-- Функция: Отмена комиссий для возвращенного заказа
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
    -- Находим все комиссии, связанные с этим заказом
    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 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
            );

            -- Вычитаем из баланса партнера
            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;

        -- Помечаем оригинал как отмененный
        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;

Запросы на выплату

sql

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,  -- Зашифрованные реквизиты счета

    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,  -- Для идемпотентности платежного процессора

    created_at TIMESTAMP NOT NULL DEFAULT NOW(),

    -- Ограничение минимальной суммы выплаты
    CONSTRAINT min_payout_amount CHECK (amount_usd >= 10)  -- Минимум $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);

-- КРИТИЧНО: Предотвращаем множественные ожидающие выплаты для одного партнера (защита от двойного расходования)
-- Только одна выплата может быть в статусе PENDING, APPROVED или PROCESSING одновременно
CREATE UNIQUE INDEX idx_single_pending_payout
ON mlm.payout_requests(partner_id)
WHERE status IN ('PENDING', 'APPROVED', 'PROCESSING');

Процесс запроса на выплату (Безопасный для конкурентного доступа)

sql

-- Функция: Создание запроса на выплату со списанием баланса (атомарно)
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. Блокируем баланс партнера
    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. Проверяем существующую ожидающую выплату (избыточно с уникальным индексом, но дает лучшее сообщение об ошибке)
    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. Проверяем достаточность баланса
    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. Списываем с доступного баланса
    UPDATE mlm.partner_balances
    SET available_balance = available_balance - p_amount,
        version = version + 1,
        updated_at = NOW()
    WHERE partner_id = p_partner_id;

    -- 5. Создаем запрос на выплату
    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. Создаем запись в журнале аудита
    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;


-- Функция: Отмена выплаты и восстановление баланса
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. Блокируем запрос на выплату
    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. Проверяем возможность отмены
    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. Блокируем баланс партнера
    PERFORM 1 FROM mlm.partner_balances
    WHERE partner_id = v_payout.partner_id
    FOR UPDATE;

    -- 4. Восстанавливаем баланс
    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 mlm.payout_requests
    SET status = 'CANCELLED',
        rejection_reason = p_reason,
        processed_by = p_cancelled_by,
        processed_at = NOW()
    WHERE id = p_payout_id;

    -- 6. Журнал аудита
    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;

См. также: Паттерны конкурентности для обработки выплат на уровне приложения.

Схема MLM (Партнеры, Комиссии, Ранги) ​

Партнеры (Основная сущность) ​

Пути партнерского дерева (Closure Table) ​

Реферальные ссылки ​

Реферальные атрибуции ​

Ранги ​

Требования к рангам ​

История рангов партнера ​

Лидерские пулы ​

Данные конфигурации пулов ​

Распределения пулов ​

Участие в пулах ​

Правило 50% веток ​

Комиссионные планы ​

Комиссионные уровни ​

Конфигурация распределения вознаграждений ​

Балансы партнеров ​

Операции с балансом (Безопасные для конкурентного доступа) ​

Паттерн оптимистичной блокировки ​

Паттерн пессимистичной блокировки ​

Пакетное обновление баланса (для подтверждения комиссий) ​

Комиссионные транзакции ​

Логика отмены комиссий ​

Запросы на выплату ​

Процесс запроса на выплату (Безопасный для конкурентного доступа) ​

Связанные документы ​