Understanding WooCommerce Sub-Affiliate Tracking Architecture

The Database Layer: Beyond User Meta

Affiliate Engine doesn't rely solely on wp_usermeta for sponsor relationships. While it does store the immediate sponsor ID in user meta for quick access, the core structure lives in a dedicated table: naff_mlm_sponsors. This table maps each affiliate to their sponsor, creating a non-volatile chain that survives cookie expiration and session resets. The relationship is established at registration, via a tracked link or coupon, and remains fixed, ensuring every future sale by that affiliate triggers the correct upline commission chain.

The naff_mlm_sponsors table uses a simple but effective schema:

• affiliate_id: The WordPress user ID of the sub-affiliate.
• sponsor_id: The WordPress user ID of their direct parent.
• level: Their position in the hierarchy (e.g., Level 1, Level 2).

This separation from user meta avoids bloating the wp_usermeta table and allows for efficient queries when filtering affiliates by sponsor or network depth.

Commission Generation: The Referral Multiplier Effect

When a sub-affiliate generates a sale, the system doesn't just create one referral record, it creates multiple. The base referral (direct commission) is stored in the naff_referrals table with a source field set to direct. Simultaneously, the plugin generates additional records for each upline level, each marked with source: mlm. These MLM records include:

• A reference to the original base referral (base_referral_id).
• The level in the chain (e.g., level: 1 for the direct parent).
• The affiliate ID of the parent earning the commission.

This structure lets you trace any parent commission back to its originating sale, even in complex multi-level networks. The source field is critical for filtering; admins can isolate direct referrals from MLM commissions in the Affiliate Engine Referrals tab, while the base_referral_id ensures data integrity across the chain.

Refund Protection: Cascading Status Updates

Refunds are where many multi-tier systems fail. Affiliate Engine handles them via WooCommerce order status hooks. When an order is refunded or canceled, the plugin:

1. Sets the base referral's status to rejected.
2. Queries all MLM records linked to that base_referral_id.
3. Updates their status to rejected if unpaid.

This cascade is triggered by the woocommerce_order_status_changed hook, ensuring no manual intervention is needed. The hold period (configured in settings) acts as a buffer, preventing payouts until after the refund window closes.

Frontend Integration: My Account Downline View

Affiliates see their network via a custom endpoint added to WooCommerce's My Account page. The plugin registers a new tab using the woocommerce_account_menu_items filter, then loads a template that queries:

• Their direct recruits (sub-affiliates) from naff_mlm_sponsors.
• Their upline chain by recursively querying sponsor relationships.
• Earnings breakdown by source (direct vs. MLM) from naff_referrals.

This avoids external dashboards, keeping everything within WordPress's native UI while providing real-time data.

Key Takeaways for Developers

1. Persistent relationships: Sponsor assignments are stored in a dedicated table, not just user meta.
2. Referral multiplication: Each sale generates a base referral + MLM records, all linked by base_referral_id.
3. Refund safety: The woocommerce_order_status_changed hook ensures upline commissions are rejected in sync.
4. Frontend efficiency: Custom My Account endpoints avoid external API calls.

For a deeper dive, explore the Affiliate Engine documentation or inspect the plugin's use of WordPress hooks for sponsor assignment and commission tracking.