Refunding linked subscriptions

Today I wanted to write about another work project that’s taken up a lot of my time: how to properly refund subscriptions that were paid for over multiple transactions.

Refunding an upgraded subscription

Let’s say you sign up for a subscription at a service’s Basic tier for $10, and then later upgrade that subscription to a Pro tier which costs $30. When you upgrade, you get a discount based on the cost of the original subscription so you only pay $20.

1. Buy Basic for $10
2. Upgrade to Pro for $30 $20

Now, if you cancel and refund the Pro subscription, how much should you get back?

The naive answer is to refund the amount you paid for the subscription being refunded, but technically you only paid $20 for the Pro plan you currently own. You should actually get back $30 (assuming the original plan is still within its refund period) because its cost was subsumed into the new plan.

Refund: Pro plan for $20 + Basic plan for $10 = $30

It turns out that doing this is a little tricky because that extra $10 you paid was in a different transaction and it was for a subscription which has already been cancelled. This means that we need a reliable way to be able to find that previous subscription. How can we do this?

Time to search

One way would be performing a database search for cancelled subscriptions that:

• Were on the same site and owned by the same user as the current subscription.
• Match the types of products that can be upgraded to the current subscription.
• Were cancelled within a short window before the current subscription was activated.

Well, that’s a complex query to make but it should find most upgrades of this sort.

But what if the subscription was transferred to a different user before it was refunded? To find those we’d need to remove the condition that the user is the same, which would likely cause a lot of false positives if other users happened to cancel a subscription around the same time.

We’d need to therefore also query for transfer records for the current subscription to make sure we can limit the search to the correct users and sites. But what if the previous subscription had been transferred to another user after it was purchased and was also an upgrade?

Wait, this is getting complicated!

In the billing system I work with, there’s the added complexity that the only way to search for transfer records involves using an SQL LIKE query, which is extremely inefficient. We also need to perform this search recursively, meaning it could get expensive fast.

Ok, slow, but it does work, right?

But wait, there’s more. What if a user purchases a subscription on site A, then moves it to site B, then back to site A, then to site C. You probably see where this is going. And that’s just for one of the above conditions.

Does that find every upgrade?

All of the above also relies on the search function knowing what can and cannot be considered an upgrade. New products are added all the time and the developers don’t always correctly encode them for the refund system to understand. Further, sometimes the complex logic that determines if something is an upgrade will change, which means that a user might have purchased an upgrade but the code may no longer consider that an upgrade when it comes time to refund it.

This is how our refund system worked before. No thanks.

Tracking migrations

I decided that there had to be a better way. All the above subscription searching was only a problem because it was implicit: we were trying to find out something based on records that weren’t designed to know about it, in this case the concept of an “upgrade”.

And it’s not just upgrades where this can happen; downgrades work the same way, as well as purchasing new licenses for a tiered subscription, or purchasing a domain transfer (a one-time purchase product that becomes a domain registration subscription when it completes). All of these situations and more involve paying for a subscription in more than one transaction.

We needed a system was explicit instead: one that understood that a subscription might be partially paid by other subscriptions.

I called this new system “migrations”. It involved creating a new database table which could track three pieces of information:

• The previous subscription.
• The next subscription.
• The type of migration.

Something like this:

CREATE TABLE subscription_migrations (
    `subscription_migration_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `migration_date` DATETIME NOT NULL,
    `from_subscription_id` BIGINT(20) UNSIGNED NOT NULL,
    `to_subscription_id` BIGINT(20) UNSIGNED NOT NULL,
    `migration_type` VARCHAR(255) NOT NULL COMMENT 'The type of migration (eg: downgrade, upgrade)',
    PRIMARY KEY (`subscription_migration_id`),
    UNIQUE KEY `subscription_ids` (`from_subscription_id`, `to_subscription_id`),
    KEY `to_subscription_id` (`to_subscription_id`)
);

Here’s the PHP record class I created to manipulate these rows:

final class Subscription_Migration {
	public int $previous_subscription_id;
	public int $next_subscription_id;
	public string $migration_type;
}

With this table, we are able to record a row for every one of the events which “migrates” one subscription to another: upgrades, downgrades, domain transfers, license purchases, etc.

Then I wrote a function to collect these records and return their IDs.

function get_previous_subscriptions(int $subscription_id): array {
	$migrations = get_subscription_migrations($subscription_id);
	$previous_subscription_ids = array_map(
		fn($migration) => $migration->previous_subscription_id,
		$migrations,
	);
	$all_previous_ids = [];
	foreach ($previous_subscription_ids as $previous_subscription_id) {
		$all_previous_ids = array_merge(
			$all_previous_ids,
			get_previous_subscriptions($previous_subscription_id),
		);
	}
	sort($all_previous_ids);
	return $all_previous_ids;
}

Now when the system goes to perform a refund for a subscription, it’s able to quickly find every linked subscription that may also need a refund. There’s no question about the validity of the results; they were accurate at the time they were made and will never change no matter how many new products may be added or what refactors may occur.

Implementing this system allowed us to remove about 4000 lines of very complex code – all of the searching and inefficient database queries described above – in addition to solving a pile of long-standing refund bugs that we had been unable to fix for years.

While there are definitely some advantages to the implicit search-based system – with it we don’t have to worry about missing recording a migration – for billing purposes we want to be able to rely on explicit records rather than guesswork. When money is on the line, be clear about what things are happening!

Photo by Julia Craice on Unsplash