Members with cancelled comp plans aren't listed with the free members filter

[matthanley]

Issue Summary

Members who have had complimentary plans removed don't appear in the free members filter on the members list.

To Reproduce

1. Create a new member in a members-enabled site with Stripe connected
2. Observe member appears in list of free members (at /ghost/#/members?paid=false)
3. Enable "Complimentary premium plan" for the member
4. Observe member appears in list of paid members (at /ghost/#/members?paid=true)
5. Disable "Complimentary premium plan" for the member
6. Observe member no longer appears in either free or paid filtered member list, but does appear in all members list

What did you expect to happen instead?
Member should be reverted to a free member, and appear in the free members filtered list.

We should also verify the behaviour for cancelled and expired paid members (they should also appear as free members).

Technical details:

• Ghost Version: 3.31.1 (on Pro)
• Browser/OS: Chrome

[kevinansfield]

The issue is that our "free member" query works on the assumption that members do not have a Stripe customer record if they are unpaid.

We can adjust the generated SQL query to something like this:

select
    `members`.*
from
    `members`
    left join `members_stripe_customers` on `members`.`id` = `members_stripe_customers`.`member_id`
    left join `members_stripe_customers_subscriptions` on `members_stripe_customers`.`customer_id` = `members_stripe_customers_subscriptions`.`customer_id`
where
    `members_stripe_customers`.`member_id` is null
    or `members_stripe_customers_subscriptions`.`customer_id` is null
    or `members_stripe_customers_subscriptions`.`status` not in ("active", "trialed");

However that query involves fetching all members/customers/subscriptions no matter what limit is applied meaning performance isn't great. For ~100k members with subscriptions and 3 test members with no customer, no subscription, or subscription that isn't active or trialed the query takes ~1sec locally even for paged result sets.

I'm not sure if there's a way to improve the query or if we'd need to look at a different way of fetching free members. We could possibly run multiple queries checking:

1. no customer
2. no subscription
3. subscription with inactive status

We'd need to re-architect how the ?paid=false query param is handled in that case.

[kevinansfield]

Alternative quick-fix would be to ensure that stripe customer data is cleared from the database when a member has a complimentary plan removed or otherwise has their subscription cancelled. That would still leave us with a potentially brittle handling of ?paid=false

[naz]

Was having a look at this issue and think the last solution proposed: "ensure that stripe customer data is cleared from the database" is not a good option because we rely on the members_stripe_customer record when for example "un-cancelling" the complimentary plan and still keeping it within same customer in Stripe. We don't rely on the same stripe_customers_subscriptions record which might be what was meant to be cleared in the first place 🤔, but this part is also not a good data to rely on because of the webhooks logic which would upcate/recreate a subscription record from Stripe's side.

Given following:

1. We want to keep the amount of possible joins to the minimum (ideally none)
2. Having to keep the records for both stripe_customers and stripe_subscriptions in place
3. Having to calculate isPaid flag in multiple places through db querying (ref 1, ref 2)

Maybe the most performant and reliable method would be introducing an is_paid flag on the members table? The main downside I see here is having to keep subscription status associated with the member in sync with the flag (it's only ever set here), the upside is solving possible performance problems and reducing querying logic.

[rorysaur]

I'm seeing a discrepancy where a member who started a checkout but didn't go through with it (in other words, a Stripe customer was created for them but no Stripe subscription) is also not registering as free or paid. So ideally the fix would address that case as well, and not only the case of a cancelled subscription.