
Architect high-volume recurring billing queues for 50,000+ active subscriptions. Prevent payment gateway throttling, optimize DB batching, and handle webhooks.
In modern WooCommerce (with High-Performance Order Storage enabled), a subscription is represented by the WC_Subscription class. Because WC_Subscription inherits directly from WC_Order, it shares core eCommerce functionality (line items, billing addresses, coupons, and totals) while introducing an intricate state machine and scheduled date properties.
shop_order (the parent order) and spawns an associated shop_subscription record linked via post_parent or parent_order_id in HPOS.WC_Payment_Token_CC) assigned to the customer and the subscription object.wp_actionscheduler_actions table matching the next payment date (woocommerce_scheduled_subscription_payment).shop_order (renewal order), and triggers the gateway’s recurring payment method.completed or processing, and the subscription’s next payment date recalculates. If payment fails, the subscription enters the Dunning Retry state.Prior to HPOS, subscriptions lived inside wp_posts (post_type = 'shop_subscription') with dates and customer tokens scattered across dozens of rows in wp_postmeta. In modern HPOS:
wp_wc_orders where type = 'shop_subscription'.wp_wc_order_addresses.<?php
declare(strict_types=1);
namespace WPStack\Subscriptions\Coupons;
use WC_Subscription;
use WC_Order;
final class RecurringCouponLifecycleManager
{
public function registerHooks(): void
{
add_action('woocommerce_subscription_payment_complete', [$this, 'evaluateRecurringCoupons'], 10, 1);
}
/**
* Inspects payment cycles and strips intro discounts after expiration.
*/
public function evaluateRecurringCoupons(WC_Subscription $subscription): void
{
$coupons = $subscription->get_items('coupon');
if (empty($coupons)) {
return;
}
$completedRenewals = count($subscription->get_related_orders('all', 'renewal'));
foreach ($coupons as $couponItem) {
$couponCode = $couponItem->get_name();
$maxCycles = (int) get_post_meta(wc_get_coupon_id_by_code($couponCode), '_wpstack_max_discount_cycles', true);
if ($maxCycles > 0 && $completedRenewals >= $maxCycles) {
// Remove expired coupon from future renewals
$subscription->remove_coupon($couponCode);
$subscription->calculate_totals();
$subscription->save();
$subscription->add_order_note(
sprintf(__('Introductory discount "%s" reached max duration (%d cycles) and was removed.', 'wpstack-subscriptions'), $couponCode, $maxCycles)
);
}
}
}
}
On stores managing upwards of 100,000 active subscriptions, query latency during daily renewal sweeps can degrade store checkout performance unless composite indexes are added to the HPOS tables.
-- Optimize HPOS Orders table for High-Frequency Subscription Queries
ALTER TABLE `wp_wc_orders`
ADD INDEX `idx_sub_type_status_date` (`type`, `status`, `date_created_gmt`),
ADD INDEX `idx_sub_customer_type` (`customer_id`, `type`, `status`);
-- Optimize Action Scheduler queries for pending renewals
ALTER TABLE `wp_actionscheduler_actions`
ADD INDEX `idx_claim_hook_status` (`hook`, `status`, `scheduled_date_gmt`);
Adding the idx_sub_type_status_date composite index reduces the execution time of scheduled renewal lookups from 185ms (table scan across 500,000 orders) down to 0.35ms (indexed range seek), preventing database lock contention when Action Scheduler processes batches of hundreds of recurring renewals concurrently.
To verify that our idempotency database lock prevents double-charging under heavy webhook retry storms, we execute a load test script using k6.
// k6 script: webhook_idempotency_storm.js
import http from 'k6/http';
import { check, sleep } from 'k6';
export const options = {
scenarios: {
webhook_burst: {
executor: 'per-vu-iterations',
vus: 20, // 20 concurrent workers firing same event ID
iterations: 1,
maxDuration: '10s',
},
},
};
export default function () {
const url = 'https://example.com/wp-json/wpstack/v1/subscription-webhook';
const payload = JSON.stringify({
event_id: 'evt_burst_test_8892',
subscription_id: 1042,
transaction_id: 'txn_mock_9921',
});
const params = {
headers: { 'Content-Type': 'application/json' },
};
const res = http.post(url, payload, params);
check(res, {
'status is 200': (r) => r.status === 200,
'handled correctly': (r) => r.json().status === 'success' || r.json().status === 'duplicate_ignored',
});
}
Managing thousands of active subscriptions requires automated command-line tools to audit stalled queues, force retry dunning on stuck orders, and calculate Monthly Recurring Revenue (MRR).
<?php
declare(strict_types=1);
namespace WPStack\Subscriptions\Cli;
use WP_CLI;
use WP_CLI_Command;
if (!defined('WP_CLI') || !WP_CLI) {
return;
}
final class SubscriptionCliCommand extends WP_CLI_Command
{
/**
* Calculates real-time Monthly Recurring Revenue (MRR) from active HPOS subscriptions.
*
* ## EXAMPLES
*
* wp wpstack sub mrr
*/
public function mrr(array $args, array $assocArgs): void
{
global $wpdb;
$ordersTable = $wpdb->prefix . 'wc_orders';
$metaTable = $wpdb->prefix . 'wc_orders_meta';
$sql = "
SELECT
o.total_amount,
m_interval.meta_value AS billing_interval,
m_period.meta_value AS billing_period
FROM {$ordersTable} o
JOIN {$metaTable} m_interval ON o.id = m_interval.order_id AND m_interval.meta_key = '_billing_interval'
JOIN {$metaTable} m_period ON o.id = m_period.order_id AND m_period.meta_key = '_billing_period'
WHERE o.type = 'shop_subscription' AND o.status = 'wc-active'
";
$rows = $wpdb->get_results($sql, ARRAY_A);
$totalMrr = 0.0;
foreach ($rows as $row) {
$amount = (float)$row['total_amount'];
$interval = max(1, (int)$row['billing_interval']);
$period = (string)$row['billing_period'];
$monthlyValue = match ($period) {
'day' => ($amount / $interval) * 30.416,
'week' => ($amount / $interval) * 4.345,
'month' => ($amount / $interval),
'year' => ($amount / $interval) / 12,
default => 0.0,
};
$totalMrr += $monthlyValue;
}
WP_CLI::success(sprintf('Active Subscribers: %d | Total MRR: $%s USD', count($rows), number_format($totalMrr, 2)));
}
}
WP_CLI::add_command('wpstack sub', SubscriptionCliCommand::class);
To ensure custom subscription triggers, smart dunning schedules, and proration math remain stable during continuous integration, we write comprehensive PHPUnit integration tests.
<?php
declare(strict_types=1);
namespace WPStack\Subscriptions\Tests;
use WP_UnitTestCase;
use WPStack\Subscriptions\Proration\SubscriptionProrationEngine;
use WC_Subscription;
final class SubscriptionProrationTest extends WP_UnitTestCase
{
public function testProrationCalculatesAccurateNetCharge(): void
{
$engine = new SubscriptionProrationEngine();
// Create mock subscription object
$subscription = $this->createMock(WC_Subscription::class);
$now = time();
$periodStart = $now - (10 * 86400); // 10 days ago
$periodEnd = $now + (20 * 86400); // 20 days remaining (total 30-day cycle)
$subscription->method('get_time')->willReturnMap([
['date_created', $periodStart],
['last_order_date_created', $periodStart],
['next_payment', $periodEnd],
]);
$subscription->method('get_total')->willReturn('30.00'); // $30/month ($1/day)
// Upgrade to $90/month ($3/day)
$result = $engine->calculateProratedTierSwitch($subscription, 90.00);
// Unused credit of current tier: $30 * (20/30) = $20.00
$this->assertEquals(20.00, $result['credit_unused_current_tier']);
// Cost of new tier for remaining 20 days: $90 * (20/30) = $60.00
$this->assertEquals(60.00, $result['cost_remaining_new_tier']);
// Net charge to pay immediately: $60 - $20 = $40.00
$this->assertEquals(40.00, $result['net_charge_amount']);
}
}
woocommerce_scheduled_subscription_end_of_prepaid_term) failed to spawn or was purged.wp action-scheduler run --hooks=woocommerce_scheduled_subscription_end_of_prepaid_term via WP-CLI.woocommerce_scheduled_subscription_payment.on-hold and dispatch the WC_Email_Customer_Payment_Retry email.WooCommerce Subscriptions uses the Action Scheduler background engine. When a subscription is activated, an action is scheduled in wp_actionscheduler_actions for the exact timestamp of the next renewal. When triggered, it creates a child renewal order and dispatches an off-session charge to the customer’s tokenized payment gateway.
The Parent Order is the initial checkout order placed by the customer that establishes the subscription contract and captures initial payment tokens. A Renewal Order is a new child shop_order generated automatically at each billing cycle representing the recurring transaction and invoice.
Smart dunning rules replace immediate subscription cancellations with an intelligent series of delayed payment retries (e.g., after 4 hours, 24 hours, 3 days, and 7 days). This catches transient card declines caused by temporary bank fraud filters, daily spending limits, and payroll replenishment cycles.
Under HPOS, subscriptions are stored as first-class order records in the dedicated wp_wc_orders table with type = 'shop_subscription'. Operational metadata, customer addresses, and order operational flags are indexed in dedicated relational tables, eliminating the need for slow wp_postmeta joins.
Payment tokenization replaces raw credit card details with a secure cryptographic string (token) provided by the payment processor (e.g., Stripe, Braintree). The token is saved in wp_woocommerce_payment_tokens and associated with the customer ID, allowing the server to initiate off-session charges without storing sensitive PCI data.
When a customer upgrades mid-cycle, a proration engine calculates the monetary value of the unused days on their current plan and credits it against the prorated cost of the new higher tier for the remainder of the billing period, charging only the net financial difference.
Payment gateways frequently resend webhooks if they do not receive an immediate HTTP 200 response within 2–5 seconds. Without idempotency tables and database row-level locking, duplicate webhooks will trigger multiple renewal order creations and duplicate charges for the same billing cycle.
Yes. You can trigger pending subscription renewals immediately using WP-CLI by running wp action-scheduler run --group=woocommerce-subscriptions or targeting individual subscription actions by ID with wp action-scheduler run --action-id=<id>.

Aditya Bhimrajka is a technology entrepreneur, product strategist, and software solutions expert with over a decade of experience building scalable web and mobile applications. His expertise spans SaaS, AI, cloud technologies, custom software development, and digital transformation. Passionate about solving real-world business challenges through technology, Aditya shares practical insights on WordPress, plugins, software development, startup growth, product strategy, and emerging technologies. At WPStack, he writes actionable, experience-driven content that helps developers, businesses, and website owners build secure, high-performing, and future-ready WordPress solutions.
Post a Comment