MLM Tree Implementation (Closure Table)

Schema Design

-- Partners table (adjacency list for direct parent)
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
    rank_id UUID REFERENCES mlm.ranks(id),
    status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    joined_at TIMESTAMP NOT NULL DEFAULT NOW(),

    -- Denormalized counters for performance
    direct_referrals_count INT DEFAULT 0,
    total_network_size INT DEFAULT 0,

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

-- Closure table for efficient tree queries
CREATE TABLE mlm.partner_tree_paths (
    ancestor_id UUID NOT NULL REFERENCES mlm.partners(id),
    descendant_id UUID NOT NULL REFERENCES mlm.partners(id),
    depth INT NOT NULL,  -- Distance between nodes

    PRIMARY KEY (ancestor_id, descendant_id)
);

-- Indexes for common 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_partners_sponsor ON mlm.partners(sponsor_id) WHERE status = 'ACTIVE';

---

Tree Operations (Critical)

INSERT: Adding a New Partner

When a new partner joins, we must:

1. Insert the partner record with sponsor_id
2. Insert self-reference path (depth = 0)
3. Copy all ancestor paths from sponsor and increment depth

-- Function: Add new partner to tree
CREATE OR REPLACE FUNCTION mlm.add_partner_to_tree(
    p_partner_id UUID,
    p_sponsor_id UUID
) RETURNS VOID AS $$
BEGIN
    -- 1. Insert self-reference (every node is its own ancestor at depth 0)
    INSERT INTO mlm.partner_tree_paths (ancestor_id, descendant_id, depth)
    VALUES (p_partner_id, p_partner_id, 0);

    -- 2. If sponsor exists, copy all sponsor's ancestors and add sponsor itself
    IF p_sponsor_id IS NOT NULL THEN
        INSERT INTO mlm.partner_tree_paths (ancestor_id, descendant_id, depth)
        SELECT ancestor_id, p_partner_id, depth + 1
        FROM mlm.partner_tree_paths
        WHERE descendant_id = p_sponsor_id;
    END IF;

    -- 3. Update denormalized counters on sponsor
    IF p_sponsor_id IS NOT NULL THEN
        UPDATE mlm.partners
        SET direct_referrals_count = direct_referrals_count + 1,
            updated_at = NOW()
        WHERE id = p_sponsor_id;

        -- Update total_network_size for all ancestors
        UPDATE mlm.partners p
        SET total_network_size = total_network_size + 1,
            updated_at = NOW()
        FROM mlm.partner_tree_paths t
        WHERE t.ancestor_id = p.id
          AND t.descendant_id = p_sponsor_id;
    END IF;
END;
$$ LANGUAGE plpgsql;

Trigger: Auto-maintain Closure Table on Partner Insert

CREATE OR REPLACE FUNCTION mlm.trg_partner_after_insert()
RETURNS TRIGGER AS $$
BEGIN
    PERFORM mlm.add_partner_to_tree(NEW.id, NEW.sponsor_id);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER partner_after_insert
    AFTER INSERT ON mlm.partners
    FOR EACH ROW
    EXECUTE FUNCTION mlm.trg_partner_after_insert();

MOVE: Transfer Partner to New Sponsor (Rare Operation)

Warning: This is a complex operation that affects commission history. Should require admin approval and audit logging.

-- Function: Move partner (and entire subtree) to new sponsor
CREATE OR REPLACE FUNCTION mlm.move_partner_to_sponsor(
    p_partner_id UUID,
    p_new_sponsor_id UUID,
    p_admin_id UUID,
    p_reason TEXT
) RETURNS VOID AS $$
DECLARE
    v_old_sponsor_id UUID;
    v_subtree_size INT;
BEGIN
    -- 0. Validate: Cannot move to self or own descendant
    IF p_partner_id = p_new_sponsor_id THEN
        RAISE EXCEPTION 'Cannot move partner to self';
    END IF;

    IF EXISTS (
        SELECT 1 FROM mlm.partner_tree_paths
        WHERE ancestor_id = p_partner_id AND descendant_id = p_new_sponsor_id
    ) THEN
        RAISE EXCEPTION 'Cannot move partner to own descendant (circular reference)';
    END IF;

    -- 1. Get current sponsor and subtree size
    SELECT sponsor_id INTO v_old_sponsor_id FROM mlm.partners WHERE id = p_partner_id;
    SELECT COUNT(*) INTO v_subtree_size
    FROM mlm.partner_tree_paths WHERE ancestor_id = p_partner_id;

    -- 2. Log the move for audit (before making changes)
    INSERT INTO mlm.partner_move_audit (
        partner_id, old_sponsor_id, new_sponsor_id,
        subtree_size, moved_by, reason, created_at
    ) VALUES (
        p_partner_id, v_old_sponsor_id, p_new_sponsor_id,
        v_subtree_size, p_admin_id, p_reason, NOW()
    );

    -- 3. Delete old paths (from ancestors above partner to partner's subtree)
    DELETE FROM mlm.partner_tree_paths
    WHERE descendant_id IN (
        SELECT descendant_id FROM mlm.partner_tree_paths WHERE ancestor_id = p_partner_id
    )
    AND ancestor_id IN (
        SELECT ancestor_id FROM mlm.partner_tree_paths
        WHERE descendant_id = p_partner_id AND ancestor_id != p_partner_id
    );

    -- 4. Insert new paths (from new sponsor's ancestors to partner's subtree)
    INSERT INTO mlm.partner_tree_paths (ancestor_id, descendant_id, depth)
    SELECT
        super.ancestor_id,
        sub.descendant_id,
        super.depth + sub.depth + 1
    FROM mlm.partner_tree_paths super
    CROSS JOIN mlm.partner_tree_paths sub
    WHERE super.descendant_id = p_new_sponsor_id
      AND sub.ancestor_id = p_partner_id;

    -- 5. Update sponsor_id in partners table
    UPDATE mlm.partners
    SET sponsor_id = p_new_sponsor_id, updated_at = NOW()
    WHERE id = p_partner_id;

    -- 6. Update denormalized counters
    -- Decrement old sponsor's direct count
    IF v_old_sponsor_id IS NOT NULL THEN
        UPDATE mlm.partners
        SET direct_referrals_count = direct_referrals_count - 1
        WHERE id = v_old_sponsor_id;
    END IF;

    -- Increment new sponsor's direct count
    UPDATE mlm.partners
    SET direct_referrals_count = direct_referrals_count + 1
    WHERE id = p_new_sponsor_id;

    -- Note: total_network_size recalculation should be done via batch job
    -- as it's expensive and rarely needed in real-time
END;
$$ LANGUAGE plpgsql;

Audit Table for Partner Moves

CREATE TABLE mlm.partner_move_audit (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    partner_id UUID NOT NULL REFERENCES mlm.partners(id),
    old_sponsor_id UUID REFERENCES mlm.partners(id),
    new_sponsor_id UUID REFERENCES mlm.partners(id),
    subtree_size INT NOT NULL,
    moved_by UUID NOT NULL REFERENCES core.users(id),
    reason TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_move_audit_partner ON mlm.partner_move_audit(partner_id);

Circular Reference Prevention

The MOVE function validates against circular references, but we also add a database constraint:

-- Constraint: Partner cannot be their own sponsor
ALTER TABLE mlm.partners
ADD CONSTRAINT no_self_sponsorship CHECK (id != sponsor_id);

---

Common Queries

-- Get direct referrals (level 1)
SELECT p.* FROM mlm.partners p
JOIN mlm.partner_tree_paths t ON p.id = t.descendant_id
WHERE t.ancestor_id = :partner_id AND t.depth = 1;

-- Get downline up to level 5
SELECT p.*, t.depth FROM mlm.partners p
JOIN mlm.partner_tree_paths t ON p.id = t.descendant_id
WHERE t.ancestor_id = :partner_id
  AND t.depth BETWEEN 1 AND 5
ORDER BY t.depth;

-- Get upline chain
SELECT p.*, t.depth FROM mlm.partners p
JOIN mlm.partner_tree_paths t ON p.id = t.ancestor_id
WHERE t.descendant_id = :partner_id AND t.depth > 0
ORDER BY t.depth;

-- Count per level
SELECT t.depth, COUNT(*) as count
FROM mlm.partner_tree_paths t
WHERE t.ancestor_id = :partner_id AND t.depth > 0
GROUP BY t.depth;

---

Related Documents

• Commission Engine
• Database Schema - MLM