[bug] Subscribe a second time with a different plan causes "You are not currently subscribed to any plan."

[XiChenn]

To reproduce:

• Subscribe to "Hobby" plan
• Subscribe to "Freelancer" plan
• Click "Account", you will see "You are not currently subscribed to any plan."
• Click "Pricing", buttons are "subscribe" instead of "manage"

[k-thornton]

I also hit this early on in this template. It's due to the maybeSingle() in the query that gets the active subscriptions

nextjs-subscription-payments/utils/supabase/queries.ts

Lines 11 to 16 in 25a1950

	export const getSubscription = cache(async (supabase: SupabaseClient) => {
	const { data: subscription, error } = await supabase
	.from('subscriptions')
	.select('*, prices(*, products(*))')
	.in('status', ['trialing', 'active'])
	.maybeSingle();

0 subscriptions is ok, 1 subscription is ok, but >1 subscriptions throws an error and returns null. It's not terribly difficult to handle this by customizing the template, but I agree this was the biggest earliest roadblock I hit when using it too.

[Veeeetzzzz]

+1 to having the same issue - it's what was said above but also, by default when a subscription ends it gets removed from your Stripe dashboard, but the subscriptions table doesn't delete the subscription record. It just changes the status to cancelled

I thought of two possible solutions and you can use either or both depending on your preference.

• Modify utils/supabase/queries.ts with a new query that filters by user ID & then use it to determine if there's a valid sub.

  const { data: subscription, error } = await supabase
    .from('subscriptions')
    .select('*, prices(*, products(*))')
    .eq('user_id', user.id)  // Add this line to filter by user_id
    .in('status', ['trialing', 'active'])
    .maybeSingle();

  if (error) {
    console.error('Error fetching subscription:', error);
    throw error;
  }

  console.log('Fetched subscription:', subscription);
  return subscription;
});

2. In Supabase set up a query to see all your cancelled subscriptions (i.e everyone who shouldn't have access)

SELECT
  *
FROM
  subscriptions
WHERE
  status NOT IN ('trialing', 'active');

Replace with the below to get rid of any cancelled subscriptions. Run manually or set up a job using pg_cron

DELETE FROM subscriptions
WHERE
  status NOT IN ('trialing', 'active');

[gbopola]

I think it's best to just create some logic that will cancel any current subscription and replace with the new one. That way you will always have just one subscription and this error won't persist.

[k-thornton]

• Modify utils/supabase/queries.ts with a new query that filters by user ID & then use it to determine if there's a valid sub.

  const { data: subscription, error } = await supabase
    .from('subscriptions')
    .select('*, prices(*, products(*))')
    .eq('user_id', user.id)  // Add this line to filter by user_id
    .in('status', ['trialing', 'active'])
    .maybeSingle();

  if (error) {
    console.error('Error fetching subscription:', error);
    throw error;
  }

  console.log('Fetched subscription:', subscription);
  return subscription;
});

This change actually shouldn't have any effect, because row-level security is set up on this table to only allow people to see their own entries, and no others. This means the .eq('user_id', user.id) is implicit and always happens.

Similarly, the record changing to "canceled" status will also remove it from the query (since it's filtered on ['trialing','active'], which means deletion should be unnecessary. The .maybeSingle() only cares about the outcome of the query, so as long as a given user doesn't have more than 1 subscription in 'trialing' or 'active' state, then it won't error out.

[k-thornton]

I'd say the closest thing to a bug here is that there's no guard at all around allowing the user to trigger a Stripe checkout when they already have an active subscription.

Because the template breaks in a couple of places when this state occurs, some guard code should be put here to catch that state and return a getErrorRedirect instead

nextjs-subscription-payments/components/ui/Pricing/Pricing.tsx

Line 52 in 1623fb9

if (!user) {

[k-thornton]

posted a PR for how I'd fix this

[gbopola]

• Modify utils/supabase/queries.ts with a new query that filters by user ID & then use it to determine if there's a valid sub.

  const { data: subscription, error } = await supabase
    .from('subscriptions')
    .select('*, prices(*, products(*))')
    .eq('user_id', user.id)  // Add this line to filter by user_id
    .in('status', ['trialing', 'active'])
    .maybeSingle();

  if (error) {
    console.error('Error fetching subscription:', error);
    throw error;
  }

  console.log('Fetched subscription:', subscription);
  return subscription;
});

This change actually shouldn't have any effect, because row-level security is set up on this table to only allow people to see their own entries, and no others. This means the .eq('user_id', user.id) is implicit and always happens.

Similarly, the record changing to "canceled" status will also remove it from the query (since it's filtered on ['trialing','active'], which means deletion should be unnecessary. The .maybeSingle() only cares about the outcome of the query, so as long as a given user doesn't have more than 1 subscription in 'trialing' or 'active' state, then it won't error out.

Oh I see what you mean. Makes more sense.

[naimRahman08]

I am having the same issue. I am trying in my local development and when I do multiple subscription, the old one does not get cancelled status. I think for me this is the reason. Here is how it looks in db

I thought it might be something I am doing wrong so I have tried the same process here https://subscription-payments.vercel.app/ but same result. So I am wondering if something is wrong or not

[naimRahman08]

To fix my issue I have canceled previous subscription when successful new subscription session created in app/api/webhooks/route file under checkout.session.completed.

case 'checkout.session.completed':
    const checkoutSession = event.data.object;
    if (checkoutSession.mode === 'subscription') {
        const subscriptionId = checkoutSession.subscription;
        await cancelOldSubscription(
          subscriptionId,
          checkoutSession.customer
        );
        await manageSubscriptionStatusChange(
          subscriptionId,
          checkoutSession.customer,
          true
        );
    }
    break;

const cancelOldSubscription = async (
    subscriptionId,
    customerId
) => {
    // Get customer's UUID from mapping table.
    const { data: customerData, error: noCustomerError } = await supabaseAdmin
        .from('customers')
        .select('id')
        .eq('stripe_customer_id', customerId)
        .single();

    if (noCustomerError)
        throw new Error(`Customer lookup failed: ${noCustomerError.message}`);

    const { data: subscription, error: noSubscriptionError } = await supabaseAdmin
        .from('subscriptions')
        .select('*, prices(*, products(*))')
        .in('status', ['trialing', 'active'])
        .neq('id', subscriptionId)
        .single();

    try {
        await stripe.subscriptions.cancel(subscription.id);
        await manageSubscriptionStatusChange(
            subscription.id,
            customerId,
            true
        );
    } catch (err) {
        console.error(err);
        throw new Error('Unable to cancel customer previous subscription');
    }
};

If there is better away to do this, would be great to look at it

[k-thornton]

@naimRahman08 I feel like cancelling their subscription isn't the way to go, since they presumably paid you to start that subscription. You can check my PR for the way I ended up handling this. I'm just guarding against the ability for the user to trigger a second subscription if they already have one.
e3a5e04

[naimRahman08]

@k-thornton That's also makes perfect sense and simplify the workflow.

My thinking behind cancelling and adding new was, the customer would have had all benefits of old subscription and new subscription will be added in addition to the old one for the current month or year. So benefits would have stayed because of the payment. I think its a matter how you want to serve customer's benefits.