How to Design an Effective Subscription Management System

Subscriptions offer a recurring revenue stream, making them a highly effective monetization strategy for SaaS applications. This model encourages customer loyalty and provides a predictable income stream, allowing businesses to invest in product development and customer support. Additionally, subscriptions can often lead to higher customer lifetime value as customers are more likely to continue using a service they find valuable.

Lets Design a subscription Management system for a SAAS application.

We'll cover:

Core features: Plans, billing, customer support.
Stripe integration: Secure payments and easy management.
Customer experience: Make it seamless.
Analytics: Track performance and make data-driven decisions.
Scalability: Build for growth.

Database Schema Overview

Currency

It stores information about different currencies used in the system. Each currency has a unique 3-letter code and a name.

CREATE TABLE IF NOT EXISTS currency (
    code VARCHAR(3) NOT NULL PRIMARY KEY,
    name VARCHAR(320) NOT NULL
);

-- sample currency data
INSERT INTO currency (code, name) VALUES
    ('INR', 'Indian Rupee'),
    ('USD', 'United States Dollar'),
    ('EUR', 'Euro');

Customer

It represents individual customers or users of the system. It stores personal information such as name, contact details, and address. Each customer needs to specify a currency in order to carry out any subscription-related operation.

CREATE TABLE IF NOT EXISTS customer (
    id BIGSERIAL PRIMARY KEY NOT NULL,
    name VARCHAR(255) NOT NULL,
    phone VARCHAR(10) NOT NULL UNIQUE,
    email VARCHAR(255) UNIQUE,
    address VARCHAR(255),
    city VARCHAR(255),
    currency_id VARCHAR(3),
    postal_code VARCHAR(12),
    created_at BIGINT NOT NULL,
    deleted_at BIGINT,

    CONSTRAINT fk_currency_id FOREIGN KEY(currency_id)
        REFERENCES currency(code)
        ON UPDATE NO ACTION ON DELETE RESTRICT
);

Only the name and phone fields are made mandatory (not null constraint) because this is the only information taken while registering a customer.

Product

It represents the products or services offered in the system. Each product has a name, description, and timestamps for creation and deletion.

CREATE TABLE IF NOT EXISTS product (
    id BIGSERIAL PRIMARY KEY NOT NULL,
    name VARCHAR(255) NOT NULL,
    description VARCHAR(1000),

    created_at BIGINT NOT NULL,
    deleted_at BIGINT
);

-- sample product data
INSERT INTO product (name, description, created_at)
VALUES ('Product A', 'This is Product A description', EXTRACT(EPOCH FROM NOW())),
       ('Product B', 'This is Product B description', EXTRACT(EPOCH FROM NOW())),
       ('Product C', 'This is Product C description', EXTRACT(EPOCH FROM NOW()));

Product Pricing

It handles the pricing information for products. It allows for time-based pricing (from a start date to an end date) in different currencies, including tax information.

Constraint: There are no overlapping price intervals for the same product and currency. This means you can't have two different prices for the same product in the same currency during overlapping time periods.

CREATE TABLE IF NOT EXISTS product_pricing (
    id BIGSERIAL PRIMARY KEY NOT NULL,
    from_date BIGINT NOT NULL,
    to_date BIGINT NOT NULL,
    price NUMERIC(10, 3) NOT NULL,
    tax_percentage DOUBLE PRECISION NOT NULL,
    currency_id VARCHAR(3) NOT NULL,
    product_id BIGINT NOT NULL,

    created_at BIGINT NOT NULL,
    deleted_at BIGINT,

    CONSTRAINT fk_currency_id FOREIGN KEY(currency_id)
        REFERENCES currency(code)
        ON UPDATE NO ACTION ON DELETE RESTRICT,
    CONSTRAINT fk_product_id FOREIGN KEY(product_id)
        REFERENCES product(id)
        ON UPDATE NO ACTION ON DELETE CASCADE
);

CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE product_pricing
ADD CONSTRAINT unique_price_in_interval EXCLUDE USING gist (
    product_id WITH =,
    currency_id WITH =,
    tstzrange(
        to_timestamp(from_date), 
        to_timestamp(to_date), 
        '[]'
    ) WITH &&
)
WHERE (deleted_at IS NULL);

-- sample data
INSERT INTO product_pricing (product_id, from_date, to_date, price, currency_id, tax_percentage, created_at) VALUES 
(1, EXTRACT(EPOCH FROM TIMESTAMP '2024-01-01 00:00:00'), EXTRACT(EPOCH FROM TIMESTAMP '2025-03-01 00:00:00'), 1000, 'USD', 5.0, EXTRACT(EPOCH FROM NOW())),
(2, EXTRACT(EPOCH FROM TIMESTAMP '2024-01-01 00:00:00'), EXTRACT(EPOCH FROM TIMESTAMP '2025-06-01 00:00:00'), 1500, 'EUR', 10.0, EXTRACT(EPOCH FROM NOW())),
(3, EXTRACT(EPOCH FROM TIMESTAMP '2024-02-01 00:00:00'), EXTRACT(EPOCH FROM TIMESTAMP '2025-12-31 00:00:00'), 2000, 'INR', 18.0, EXTRACT(EPOCH FROM NOW()));

Plan

It defines subscription plans for products. Each plan is associated with a product and specifies a billing interval (e.g., monthly, annually).

CREATE TABLE IF NOT EXISTS plan (
    id BIGSERIAL PRIMARY KEY NOT NULL,
    billing_interval INTEGER NOT NULL,
    product_id BIGINT NOT NULL,

    created_at BIGINT NOT NULL,
    deleted_at BIGINT,

    CONSTRAINT fk_product_id FOREIGN KEY(product_id)
        REFERENCES product(id)
        ON UPDATE NO ACTION ON DELETE RESTRICT
);

-- sample data
INSERT INTO plan (product_id, billing_interval, created_at) VALUES 
(1, 1, EXTRACT(EPOCH FROM NOW())),   -- plan for Product A (monthly)
(2, 3, EXTRACT(EPOCH FROM NOW())),   -- plan for Product B (quarterly)
(3, 12, EXTRACT(EPOCH FROM NOW())),  -- plan for Product C (yearly)
(2, 6, EXTRACT(EPOCH FROM NOW()));   -- plan for Product B (half-yearly)

Invoice

It records financial transactions related to customer subscriptions. It includes details such as tax amount, total amount, due date, and payment status.

CREATE TABLE IF NOT EXISTS invoice (
    id BIGSERIAL PRIMARY KEY NOT NULL,
    tax_amount INTEGER NOT NULL,
    total_amount INTEGER NOT NULL,
    status VARCHAR(7) NOT NULL,
    due_at BIGINT NOT NULL,
    paid_at BIGINT,
    customer_id BIGINT NOT NULL,
    plan_id BIGINT NOT NULL,

    -- checkout session id generated by Stripe client, or
    -- order id in case of Razorpay
    provider_session_or_order_id VARCHAR(255),

    created_at BIGINT NOT NULL,
    deleted_at BIGINT,

    CONSTRAINT fk_customer_id FOREIGN KEY(customer_id)
        REFERENCES customer(id)
        ON UPDATE NO ACTION ON DELETE RESTRICT,
    CONSTRAINT fk_plan_id FOREIGN KEY(plan_id)
        REFERENCES plan(id)
        ON UPDATE NO ACTION ON DELETE RESTRICT,
    CONSTRAINT check_invoice_status
        CHECK (status IN ('DRAFT', 'PAID', 'UNPAID'))
);

Subscription

It tracks customer subscriptions to specific plans. It manages the lifecycle of a subscription including start and end dates, renewals, upgrades, downgrades, and cancellations.

Constraint: A customer cannot have overlapping active subscriptions. This means a customer can't have two active subscriptions with overlapping time periods.

CREATE TABLE IF NOT EXISTS subscription (
    id BIGSERIAL PRIMARY KEY NOT NULL,
    status VARCHAR(9) NOT NULL,
    invoice_id BIGINT NOT NULL UNIQUE,
    customer_id BIGINT NOT NULL,
    starts_at BIGINT NOT NULL,
    ends_at BIGINT NOT NULL,
    renewed_at BIGINT,
    renewed_subscription_id BIGINT,
    upgraded_at BIGINT,
    upgraded_to_plan_id BIGINT,
    downgraded_at BIGINT,
    downgraded_to_plan_id BIGINT,
    cancelled_at BIGINT,

    created_at BIGINT NOT NULL,
    deleted_at BIGINT,

    CONSTRAINT fk_customer_id FOREIGN KEY(customer_id)
        REFERENCES customer(id),
    CONSTRAINT fk_downgraded_to_plan_id
        FOREIGN KEY(downgraded_to_plan_id) REFERENCES plan(id)
        ON DELETE SET NULL,
    CONSTRAINT fk_invoice_id FOREIGN KEY(invoice_id)
        REFERENCES invoice(id),
    CONSTRAINT fk_renewed_subscription_id FOREIGN KEY(renewed_subscription_id)
        REFERENCES subscription(id)
        ON DELETE SET NULL,
    CONSTRAINT fk_upgraded_to_plan_id FOREIGN KEY(upgraded_to_plan_id)
        REFERENCES plan(id)
        ON DELETE SET NULL,
    CONSTRAINT check_subscription_status
        CHECK (status IN ('INACTIVE', 'ACTIVE', 'UPGRADED'))
);

CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE subscription
ADD CONSTRAINT unique_subscription_in_interval EXCLUDE USING gist (
    customer_id WITH =,
    tstzrange(
        to_timestamp(starts_at), 
        to_timestamp(ends_at), 
        '[]'
    ) WITH &&
)
WHERE (deleted_at IS NULL AND status = 'ACTIVE');

Upgrade

It defines upgrade paths between different plans. It shows whether moving from one plan to another is an would be considered an upgrade or downgrade.

CREATE TABLE IF NOT EXISTS upgrade (
    id BIGSERIAL PRIMARY KEY NOT NULL,
    from_plan_id BIGINT NOT NULL,
    to_plan_id BIGINT NOT NULL,

    CONSTRAINT unique_plan_pair UNIQUE (from_plan_id, to_plan_id),
    CONSTRAINT fk_from_plan_id FOREIGN KEY(from_plan_id)
        REFERENCES plan(id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_to_plan_id FOREIGN KEY(to_plan_id)
        REFERENCES plan(id)
        ON UPDATE CASCADE ON DELETE CASCADE
);

-- sample data
INSERT INTO upgrade (from_plan_id, to_plan_id) VALUES (2, 4);

Subscription Renewal Reminder

It keeps track of renewal reminders sent to customers for their subscriptions.

CREATE TABLE IF NOT EXISTS subscription_renewal_reminder (
    id BIGSERIAL PRIMARY KEY NOT NULL,
    created_at BIGINT NOT NULL,
    customer_id BIGINT NOT NULL,

    CONSTRAINT fk_customer_id FOREIGN KEY(customer_id)
        REFERENCES customer(id) 
        ON UPDATE CASCADE ON DELETE CASCADE
);

Stripe Workflow

Setting up Stripe account for handling payments

Create a Stripe account (if not already created) at https://dashboard.stripe.com/register. Fill in your details and verify your email to finish creating the account.

You can begin using your Stripe account in test mode as soon as create it. In test mode, you can simulate using all of Stripe’s features without moving real money. After you activate your account, you can accept payments, create additional accounts, start a team, and set up a custom email domain.
Stripe authenticates your API requests using your account’s API keys. If a request doesn’t include a valid key, Stripe returns an invalid request error. If a request includes a deleted or expired key, Stripe returns an authentication error.

Visit the developers dashboard at https://dashboard.stripe.com/test/apikeys to create, reveal, delete, and roll API keys.

These keys as used in your application when using Stripe client.

Use the required Stripe SDK in your app as per the app’s requirements at https://docs.stripe.com/sdks.

Setting up Stripe Checkout Session

A Checkout Session represents your customer’s session as they pay for one-time purchases or subscriptions through Checkout or Payment Links. We create a Checkout Session on our server and redirect to its URL to begin Checkout.

Follow the documentation at https://docs.stripe.com/api/checkout/sessions/create to set up session creation in your app.
In our case, we follow the following configuration when creating a session

{
  payment_method_types: ['card'], // payment methods can be modified according to requirements
  line_items: [{
      'price_data': {
          'currency': customer.currency.code,  // currency code in lowercase
          'product_data': { // can be modifed as per requirements
              'name': 'Subscription Plan',
              'description': f'Plan {plan_id} for {billing_interval} month(s)',
          },
          'unit_amount': total_amount,  // in smallest currency units
      },
      'quantity': 1,
  }],
  mode:'payment',
  // provide frontend payment success or failure URLs (can be set as any dummy name while testing)
  // these are the URLs to where the page will be redirected in case of payment success or cancellation/failure
  success_url: '<FRONTEND_URL>/success',
  cancel_url: '<FRONTEND_URL>/cancel',
}

The created Session object has an URL for the Checkout session.
Redirect customers to this URL to take them to Checkout. If you’re using Custom Domains, the URL will use your subdomain. Otherwise, it’ll use checkout.stripe.com. This value is only present when the session is active.

To confirm that your integration works correctly, simulate transactions without moving any money using special values in test mode available at https://docs.stripe.com/testing.

Setting webhook endpoint for receiving Stripe events

Stripe webhooks can be used to notify an external application whenever specific events occur in your Stripe account.

Follow the documentation at https://docs.stripe.com/webhooks to set up a webhook endpoint handler to receive event data POST requests.

You can decide the operations to be performed when the payment is successful and when it is cancelled or failed. In our case, if the payment is successful, we activate the subscription.

We will discuss about subscription activation logic when subscribing to a plan in further sections.

Stripe requires your application to be available through an HTTPS endpoint.
In order to integrate and test Strike webhooks with your application without deploying, ngrok can be used as a hack.

Setting up ngrok on your local machine

Install ngrok through https://ngrok.com/download.
Create an ngrok account (if not already created) on https://ngrok.com/signup.
Set up ngrok locally by using your ngrok authtoken which can be accessed at https://dashboard.ngrok.com/get-started/your-authtoken.
Use the following command in your local machine’s terminal to configure ngrok agent:
```
  ngrok config add-authtoken <YOUR-AUTHTOKEN>
```

Integrating Stripe webhook with ngrok

References:

https://docs.stripe.com/webhooks
https://ngrok.com/docs/integrations/stripe/webhooks/
Start your app locally in which you want to integrate Stripe payment handling. Start ngrok by running the following command on the same port on which your app is running:
```
  ngrok http <YOUR-APP-PORT>
```
ngrok will display a URL where your localhost application is exposed to the internet.
This URL will be used for Stripe webhook endpoint.
Visit the developers dashboard at https://dashboard.stripe.com/test/workbench/webhooks to access the Webhooks page.
Create a new endpoint for the webhook callback.
Select events that you want to receive notifications about (checkout.session.completion in our case).

Add the endpoint of the ngrok hosted URL with route to your Stripe webhook endpoint defined previously.
Save the configuration.
The signing secret will be accessible after the configuration is saved. This will be required to be added to the webhook endpoint defined previously in your application.

Subscribing to a plan

The customer selects a plan that they want to subscribe to.

For the associated product, we get the price, tax percentage and billing interval information for the currency associated with the customer.

SELECT
    pricing.price,
    pricing.tax_percentage,
    plan.billing_interval
FROM product_pricing pricing
JOIN plan ON pricing.product_id = plan.product_id
WHERE plan.id = <plan-id> AND pricing.currency_id = <currency>;

We calculate the total amount to be paid along with the tax amount associated with it.

tax_amount = (tax_percentage / 100) * (price * billing_interval)
total_amount = (price * billing_interval) + tax_amount

Important Note
The amount is converted to the smallest unit of the currency. For example, if the total amount is ₹300.00, the amount in the smallest currency (paise in this case) would be 30000.
This practice is used by payment providers such as Razorpay and Stripe in their payment handling.

References:

We create a Stripe Checkout Session with the configuration discussed previously in Stripe setup.

We create an invoice (draft) for the subscription with a due time set to 2 hours after the creation time stamp.

INSERT INTO invoice (status, customer_id, plan_id, tax_amount, total_amount, created_at, due_at)
    VALUES ('DRAFT', customer_id, plan_id, tax_amount, total_amount, EXTRACT(EPOCH FROM NOW()), EXTRACT(EPOCH FROM NOW() + INTERVAL '2 hours'))
    RETURNING id;

We calculate the subscription start and end time stamps based on the billing interval.

start_timestamp = current_timestamp
end_timestamp = start_timestamp + (billing_interval * 30) days

We create an entry for the subscription (inactive).

INSERT INTO subscription (status, invoice_id, customer_id, starts_at, ends_at, created_at)
    VALUES ('INACTIVE', invoice_id, customer_id, start_timestamp, end_timestamp, EXTRACT(EPOCH FROM NOW()))

The Stripe Checkout Session URL is sent as a response which is used to redirect the customer to the payment page.

Endpoint	Type	Purpose	Request body	Response (example)
`api/v1/subscriptions`	`POST`	Create subscription for the customer with specified plan.	`{ plan_id: 1 }`	`{ "checkout_url": "https://checkout.stripe.com/xyx-xyz...", "message": "invoice and subscription created successfully" }`

Subscription activation will be discussed in a later section after understanding subscription upgrade logic.

Upgrading a subscription

Upgrading to a plan happens immediately. The unused amount for the time remaining in the current plan is calculated and subtracted from the total amount when creating the invoice for the new plan.
The new subscription gets activated immediately.

We get the start and end timestamps for the current subscription along with the total amount and the tax amount.

SELECT invoice.total_amount, invoice.tax_amount, subscription.starts_at, subscription.ends_at
    FROM subscription
    JOIN invoice ON invoice.id = subscription.invoice_id
    WHERE invoice.customer_id = <customer-id>    
    AND EXTRACT(EPOCH FROM NOW()) BETWEEN subscription.starts_at AND subscription.ends_at    
    AND subscription.deleted_at IS NULL 
    AND subscription.status = 'ACTIVE';

We calculate the unused amount like this.

unused_percentage = 1 - (current_timestamp - current_subscription_starts_at) /
    (current_subscription_ends_at - current_subscription_starts_at)
unused_amount = (current_subscription_total_amount - current_subscription_tax_amount) * unused_percentage

For the associated product in the next plan, we get the price, tax percentage and billing interval information for the currency associated with the customer.

SELECT
    pricing.price,
    pricing.tax_percentage,
    plan.billing_interval
FROM product_pricing pricing
JOIN plan ON pricing.product_id = plan.product_id
WHERE plan.id = <new-plan-id> AND pricing.currency_id = <currency>;

We calculate the total amount to be paid (deducting the unused amount from the last subscription) along with the tax amount associated with it.

tax_amount = (tax_percentage / 100) * (price * billing_interval)
total_amount = (price * billing_interval) + tax_amount - unused_amount

The amount is converted to the smallest unit of the currency.

We create a Stripe Checkout Session with the configuration discussed previously in Stripe setup.

We create an invoice (draft) for the subscription with a due time set to 2 hours after the creation time stamp.

INSERT INTO invoice (status, customer_id, plan_id, tax_amount, total_amount, created_at, due_at)
    VALUES ('DRAFT', customer_id, plan_id, tax_amount, total_amount, EXTRACT(EPOCH FROM NOW()), EXTRACT(EPOCH FROM NOW() + INTERVAL '2 hours'))
    RETURNING id;

We calculate the subscription start and end time stamps based on the billing interval.

start_timestamp = current_timestamp
end_timestamp = start_timestamp + (billing_interval * 30) days

We create an entry for the subscription (inactive).

INSERT INTO subscription (status, invoice_id, customer_id, starts_at, ends_at, created_at)
    VALUES ('INACTIVE', invoice_id, customer_id, start_timestamp, end_timestamp, EXTRACT(EPOCH FROM NOW()))

The Stripe Checkout Session URL is sent as a response which is used to redirect the customer to the payment page.

We update the upgrade information in the current subscription.

UPDATE subscription SET
    upgraded_to_plan_id = <new-plan-id>
    WHERE customer_id = <customer-id>
    AND EXTRACT(EPOCH FROM NOW()) BETWEEN starts_at AND ends_at
    AND deleted_at IS NULL AND status = 'ACTIVE';

Endpoint	Type	Purpose	Request body	Response (example)
`api/v1/subscriptions/upgrade`	`POST`	Upgrade current subscription of the customer to the specified plan.	`{ plan_id: 1 }`	`{ "checkout_url": "https://checkout.stripe.com/xyx-xyz...", "message": "invoice and subscription created successfully" }`

Activating a subscription

Subscription activation is handled by the Stripe webhook callback when the payment is successful.

We get the invoice with which the checkout session id in the callback is associated. We also get the subscription associated with the corresponding invoice.

We update the payment of the invoice status.

UPDATE invoice SET status = 'PAID'
    WHERE provider_session_or_order_id = <checkout-session-id>
    RETURNING id;

Renewal identification
In order to identify if the subscription activation is for a new plan or is a renewal for the current plan, we get the start timestamp for the new subscription.

SELECT starts_at 
    FROM subscription 
    WHERE invoice_id = <invoice-id>;

First time activation (or Upgrade) case
If the current timestamp is greater than the start timestamp of the subscription, it means that it is a new subscription and not the case of renewal.
We get the billing interval details.

SELECT plan.billing_interval  
    FROM invoice    
    JOIN plan ON invoice.plan_id = plan.id 
    WHERE invoice.id = <invoice-id>;

We calculate the new start and end timestamps for the subscription.

start_timestamp = current_timestamp
end_timestamp = start_timestamp + (billing_interval * 30) days

We check if there is a currently active subscription which is supposed to be upgraded to the plan associated with the invoice.

SELECT id FROM subscription
    WHERE customer_id = <customer-id>
    AND EXTRACT(EPOCH FROM NOW()) BETWEEN starts_at AND ends_at
    AND deleted_at IS NULL AND status = 'ACTIVE'
    AND upgraded_to_plan_id = <new-plan-id>;

If there is such subscription, we update its upgrade information.

UPDATE subscription SET
    upgraded_at = EXTRACT(EPOCH FROM NOW()),
    status = 'UPGRADED'
    WHERE id = <current-subscription-id>;

We update the timestamps and the status of the subscription.

UPDATE subscription    
    SET status='ACTIVE', 
    starts_at = <start-timestamp>, 
    ends_at = <end-timestamp>    
    WHERE invoice_id = <invoice-id>;

Renewal case
Otherwise, in case of renewal, we update the status of the subscription without any change in start and end timestamps.

UPDATE subscription
    SET status = 'ACTIVE'    
    WHERE invoice_id = <invoice-id>
    RETURNING id;

We update the renewal information in the current subscription.

UPDATE subscription SET
    renewed_at = EXTRACT(EPOCH FROM NOW()),
    renewed_subscription_id = <next-subscription-id>
    WHERE EXTRACT(EPOCH FROM NOW()) BETWEEN starts_at AND ends_at
    AND deleted_at IS NULL AND status = 'ACTIVE';

Downgrading a subscription

Downgrading to a plan does not happen immediately. We wait for the current subscription to end and then change to the new plan.
We only update the downgrade information in the current subscription.

UPDATE subscription 
    SET downgraded_at = EXTRACT(EPOCH FROM NOW()),    
    downgraded_to_plan_id = <new-plan-id>
    WHERE customer_id = <customer-id> 
    AND EXTRACT(EPOCH FROM NOW()) BETWEEN starts_at AND ends_at    
    AND deleted_at IS NULL 
    AND status = 'ACTIVE';

The subscription change is handled using a background job (to be discussed in the later sections) at the end of the current subscription.

Cancelling a subscription

The subscription which gets cancelled will not be considered for renewal.

UPDATE subscription SET
    cancelled_at = EXTRACT(EPOCH FROM NOW())
    WHERE customer_id = <customer-id>
    AND EXTRACT(EPOCH FROM NOW()) BETWEEN starts_at AND ends_at
    AND deleted_at IS NULL AND status = 'active';

Background CRON jobs

Cleaning up unpaid invoices

The invoices which are not paid and the subscriptions associated with them are marked deleted every 2 hours.

UPDATE subscription AS s
    SET deleted_at = EXTRACT(EPOCH FROM NOW())
    FROM invoice i
    WHERE s.invoice_id = i.id
    AND i.deleted_at IS NOT NULL OR (EXTRACT(EPOCH FROM NOW()) > i.due_at AND i.status <> 'PAID');

UPDATE invoice i
    SET deleted_at = EXTRACT(EPOCH FROM NOW())
    WHERE (i.deleted_at IS NOT NULL OR (EXTRACT(EPOCH FROM NOW()) > i.due_at AND i.status <> 'PAID'));

Sending subscription renewal reminders

The customers are notified about their subscription renewal reminders via email which also contains the Stripe checkout link (valid for 24 hours) for making payment for the subscription renewal. The emails are spent once in every 48 hours when the currently active subscription have less than 7 days before ending.

We get the customers who have not been reminded in the last 48 hours and have their currently active subscriptions ending in less than 7 days.

WITH latest_reminders AS (
    SELECT DISTINCT ON (r.customer_id) r.customer_id, r.created_at
    FROM subscription_renewal_reminder r
    ORDER BY r.customer_id, r.created_at DESC
)
SELECT s.customer_id
FROM subscription s
LEFT JOIN latest_reminders lr ON lr.customer_id = s.customer_id
WHERE
    s.ends_at - EXTRACT(EPOCH FROM NOW()) <= EXTRACT(EPOCH FROM INTERVAL '7 days') AND
    s.deleted_at IS NULL AND
    s.status = 'ACTIVE' AND
    s.renewed_at IS NULL AND
    s.cancelled_at IS NULL AND
    (lr.created_at IS NULL OR EXTRACT(EPOCH FROM NOW()) - lr.created_at >= EXTRACT(EPOCH FROM INTERVAL '48 hours'))
ORDER BY s.customer_id, s.ends_at;

For each customer, we obtain the next plan (whether it is the same as the current plan or a downgraded plan).

WITH current_subscription AS (
    SELECT s.downgraded_to_plan_id, s.downgraded_at,
           s.starts_at, s.ends_at, i.plan_id
    FROM subscription s
    JOIN invoice i ON s.invoice_id = i.id
    WHERE s.customer_id = <customer-id>
    AND EXTRACT(EPOCH FROM NOW()) BETWEEN s.starts_at AND s.ends_at
    AND s.deleted_at IS NULL
    AND s.status = 'ACTIVE'
)
SELECT CASE
    WHEN downgraded_to_plan_id IS NOT NULL THEN downgraded_to_plan_id
    ELSE plan_id
END AS next_plan_id
FROM current_subscription;

For the associated product, we get the price, tax percentage and billing interval information for the currency associated with the customer.

SELECT
    pricing.price,
    pricing.tax_percentage,
    plan.billing_interval
FROM product_pricing pricing
JOIN plan ON pricing.product_id = plan.product_id
WHERE plan.id = plan_id AND pricing.currency_id = <currency>;

We calculate the total amount to be paid along with the tax amount associated with it.

tax_amount = (tax_percentage / 100) * (price * billing_interval)
total_amount = (price * billing_interval) + tax_amount

The amount is converted to the smallest unit of the currency.

We create a Stripe Checkout Session with the configuration discussed previously in Stripe setup.

We create an invoice (draft) for the subscription with a due time set to 2 hours after the creation time stamp.

INSERT INTO invoice (status, customer_id, plan_id, tax_amount, total_amount, created_at, due_at)
    VALUES ('DRAFT', customer_id, plan_id, tax_amount, total_amount, EXTRACT(EPOCH FROM NOW()), EXTRACT(EPOCH FROM NOW() + INTERVAL '2 hours'))
    RETURNING id;

We calculate the subscription start and end time stamps based on the billing interval.

start_timestamp = current_timestamp
end_timestamp = start_timestamp + (billing_interval * 30) days

We create an entry for the subscription (inactive).

INSERT INTO subscription (status, invoice_id, customer_id, starts_at, ends_at, created_at)
    VALUES ('INACTIVE', invoice_id, customer_id, start_timestamp, end_timestamp, EXTRACT(EPOCH FROM NOW()))

The Stripe Checkout Session URL is sent in the reminder email.

We add the currently emailed customers into the subscription renewal reminder table.

INSERT INTO subscription_renewal_reminder (customer_id, created_at)
    VALUES (<customer-id>, EXTRACT(EPOCH FROM NOW()));

Explore the complete implementation

You can find the complete code implementation at:
https://github.com/girmantech/subscription-management

The Postman collection is available at:
https://github.com/girmantech/subscription-management/tree/main/dev

How to Design an Effective Subscription Management System

Database Schema Overview

Currency

Customer

Product

Product Pricing

Plan

Invoice

Subscription

Upgrade

Subscription Renewal Reminder

Stripe Workflow

Setting up Stripe account for handling payments

Setting up Stripe Checkout Session

Setting webhook endpoint for receiving Stripe events

Setting up ngrok on your local machine

Integrating Stripe webhook with ngrok

Subscribing to a plan

Upgrading a subscription

Activating a subscription

Downgrading a subscription

Cancelling a subscription

Background CRON jobs

Cleaning up unpaid invoices

Sending subscription renewal reminders

Explore the complete implementation

Subscribe to my newsletter

Manish Dipankar

Manish Dipankar