Scale & Database Scaled to Millions of Users Lead Backend Engineer · 6 min architectural read

Multi-Level Affiliate & Commission Platform

Built a high-throughput multi-level affiliate and referral commission platform scaled to millions of registered accounts, cutting hierarchical tree traversal from O(N²) to O(1) via MySQL closure tables and asynchronous distributed Laravel queues.

Millions
Registered Accounts
Deep multi-tier referral trees
O(1)
Tree Lookup Complexity
Pre-indexed MySQL closure table
0ms
Checkout Blocking
Async distributed queue batches
Real-Time
Fraud Velocity Guard
Circular & spike protection
01 // Engineering Context

The Production Challenge

Computing cascading multi-tier referral commissions across arbitrarily deep sponsor trees in real time caused recursive SQL bottlenecks, table locks on user accounts, and slow checkout completions.

The Engineered Solution

Replaced recursive adjacency-list traversal with a pre-indexed MySQL Closure Table storing ancestor-descendant depths, decoupled commission payouts into distributed Laravel queue batches, and embedded automated fraud velocity detection.

Multi-level referral trees look deceptively simple with a single `referred_by_id` column—until a platform scales past hundreds of thousands of users and 5 to 15+ commission tiers. Fetching an entire upline or aggregating downline sales using adjacency-list loops turns every purchase into dozens of round-trip queries.

By introducing a dedicated MySQL Closure Table (`ancestor_id`, `descendant_id`, `depth`), every ancestor-descendant relationship is materialized once at registration time. Reading an entire 10-tier upline for commission distribution or querying a 50,000-node downline becomes a single flat indexed `SELECT`.

Before Refactor Bottleneck State

Recursive Loops & Checkout Timeouts

  • Walking parent_id pointers in PHP or deep recursive CTEs caused O(N²) query amplification.
  • Calculating multi-tier commissions inline during checkout blocked customer payment responses.
  • Circular referral rings or rapid self-referral abuse could slip through before payout settlement.
  • Affiliate dashboards timed out aggregating downline volume across thousands of descendants.
After Architecture Production Verified

Closure Tables & Asynchronous Queue Pipelines

  • A single indexed JOIN on user_tree_paths retrieves all upline tiers and depths in under 3ms.
  • Checkout commits the order immediately and dispatches an idempotent commission job onto Redis queues.
  • Velocity checks inspect IP, payment fingerprint, and graph cycles before marking commissions payable.
  • Sub-second affiliate dashboards query pre-indexed lineage totals without tree recursion.
02 // Architecture & Sequence Diagrams

Commission Pipeline — Checkout Decoupling & Fraud Guard

Orders settle immediately while distributed Laravel queue workers resolve closure-table tiers and fraud rules asynchronously.

flowchart TD
   Order["Confirmed Order Event"] --> Queue["Laravel Distributed Queue (Redis)"]
   Queue --> Fraud["Fraud Velocity & Cycle Detector"]
   Fraud -->|"Flagged"| Hold["Manual Compliance Hold"]
   Fraud -->|"Verified"| Closure[("MySQL Closure Table (O(1) Upline)")]
   Closure --> Tiers["Tier Rate & Qualification Engine"]
   Tiers --> Ledger[("Idempotent Commission Ledger")]
   Ledger --> Dashboard["Sub-Second Affiliate Dashboard"]

Tree Modeling — Adjacency Recursion vs. Closure Table

Materializing path depths at signup reduces commission tier resolution from multi-query recursion to a single indexed lookup.

flowchart LR
   subgraph Before["Adjacency List (Slow O(N^2))"]
      B1["User C"] -->|"Query 1"| B2["User B"]
      B2 -->|"Query 2"| B3["User A"]
      B3 -->|"Query N..."| B4["Root"]
   end
   subgraph After["Closure Table (Single Query O(1))"]
      A1[("user_tree_paths")] -->|"WHERE descendant_id = C AND depth BETWEEN 1 AND 10"| A2["Instant Upline Tiers 1..10"]
   end
03 // Implementation & Schema Snippets
database/migrations/create_user_tree_paths.sql sql
Closure table DDL and single-query O(1) upline lookup returning ordered commission tiers.
CREATE TABLE user_tree_paths (
   ancestor_id   BIGINT UNSIGNED NOT NULL,
   descendant_id BIGINT UNSIGNED NOT NULL,
   depth         SMALLINT UNSIGNED NOT NULL,
   PRIMARY KEY (ancestor_id, descendant_id),
   KEY idx_descendant_depth (descendant_id, depth)
) ENGINE=InnoDB;

-- Insert new user ($childId) under sponsor ($parentId) in one SQL statement:
INSERT INTO user_tree_paths (ancestor_id, descendant_id, depth)
SELECT ancestor_id, ?, depth + 1
FROM user_tree_paths
WHERE descendant_id = ?
UNION ALL
SELECT ?, ?, 0;

-- Fetch entire 10-tier upline for commission payout in <3ms:
SELECT ancestor_id, depth
FROM user_tree_paths
WHERE descendant_id = ?
  AND depth BETWEEN 1 AND 10
ORDER BY depth ASC;
04 // Key Decisions & Tech Stack

Architectural Highlights

  • MySQL Closure Table architecture reducing multi-tier upline and downline traversal from O(N²) recursion to O(1) indexed queries.
  • Asynchronous distributed Laravel queue batches isolating commission calculations from customer checkout latency.
  • Real-time fraud velocity detection catching circular referral loops, duplicate device signatures, and abnormal payout spikes.
  • Idempotent payout ledger entries ensuring retry safety across distributed Horizon/Redis queue workers.

Closure Table over Nested Sets

Nested sets (lft/rgt) require locking and rewriting huge slices of the tree on every new signup. Closure tables only append O(depth) rows per registration, allowing millions of concurrent signups with zero global table locks.

Eventual Payout Finality via Queues

Decoupling commission distribution from the checkout HTTP request kept checkout conversion fast and allowed clean batching and automatic retry recovery.

Technologies: Laravel Queues MySQL 8 Closure Tables Redis Event-Driven RESTful APIs
Available for UK & Remote Engagements

Designing or rescuing a system like this?

I work directly with engineering teams and startup founders on high-concurrency Laravel, MySQL performance engineering, payment infrastructure, and edge caching.