Схема 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);

Ранги

sql

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 = самый низкий, по возрастанию

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

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

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

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

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

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

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

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

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

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

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

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

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

    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 >= 100)  -- Минимум 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);

-- КРИТИЧНО: Предотвращаем множественные ожидающие выплаты для одного партнера (защита от двойного расходования)
-- Только одна выплата может быть в статусе 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) ​

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

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

Ранги ​

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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