A database model for SAAS subscriptions in Postgres

When I first started off with my side project, Moddox, I wanted to handle subscriptions using Stripe. However, I quickly found it difficult to get it to work exactly the way I wanted. And I started to believe that subscriptions are such a core piece of a SAAS business, that they shouldn’t be handled by anyone other than yourself.

In this post I share the database model I came up with in order to handle my subscriptions for Moddox.

Overview

This ER-diagram will give you a quick overview of the structure of the database.

The tables

Currencies

A table containing all accepted currencies.

CREATE TABLE IF NOT EXISTS currencies
(
    code CHARACTER(3) NOT NULL PRIMARY KEY,
    name CHARACTER VARYING(320) NOT NULL,
    created_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
)

Customers

This table contains the billable entity that has subscriptions. If you are doing a B2B SAAS, these will be companies.

• Customers have a currency in which they will be charged. It’s a foreign key referencing the currencies table.

CREATE TABLE IF NOT EXISTS customers
(
    id BIGSERIAL NOT NULL PRIMARY KEY,
    name CHARACTER VARYING(320) NOT NULL,
    phone CHARACTER VARYING(32) NOT NULL,
    email CHARACTER VARYING(320) NOT NULL,
    currency CHARACTER(3) NOT NULL,
    address1 CHARACTER VARYING(255) NOT NULL,
    address2 CHARACTER VARYING(255),
    city CHARACTER VARYING(255) NOT NULL,
    postal_code CHARACTER VARYING(12) NOT NULL,
    created_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at timestamp with time zone,
    CONSTRAINT customer_currency_fkey FOREIGN KEY (currency)
        REFERENCES currencies (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE RESTRICT
)

Products

Represents anything your SAAS is selling. You might be confused by this table at first glance; but the rationale behind it is that it keeps the invoicing more flexible. If you, for instance, are selling subscriptions, and also maybe a physical product, then you will want to have these as seperate products so that each can be referenced on an invoice line. However, it adds some complexity as all plans will need to reference a product so that it can be added to an invoice line.

CREATE TABLE IF NOT EXISTS products
(
    id BIGSERIAL NOT NULL PRIMARY KEY,
    name CHARACTER varying(255) NOT NULL,
    description CHARACTER varying(1000),
    created_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at timestamp with time zone
)

Product pricing

This table contains the pricing of a product in a specific currency. It has a start and an end date. Only one price per product and currency is allowed in a specific date range. We prevent two active prices for a product, in the same currency, within the same date range with the constraint unique_price_in_interval.

• Product id: Id of the product the price is for
• Price: An integer representing the price in cents (1/100 of the currency).
• Start date: The date the price starts applying
• End date: The date the price stops applying

CREATE EXTENSION btree_gist; -- Needed to exlude using gist

CREATE TABLE IF NOT EXISTS products_pricing
(
    id BIGSERIAL PRIMARY KEY,
    product_id BIGINT NOT NULL,
    from_date DATE NOT NULL,
    to_date DATE NOT NULL,
    price INTEGER NOT NULL,
    currency CHARACTER(3) NOT NULL,
    created_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at timestamp with time zone,
    CONSTRAINT products_pricing_currency_fkey FOREIGN KEY (currency)
        REFERENCES currencies (code) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE RESTRICT,
    CONSTRAINT products_pricing_product_id_fkey FOREIGN KEY (product_id)
        REFERENCES products (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE,
    CONSTRAINT unique_price_in_interval EXCLUDE USING gist (
        product_id WITH =,
        currency WITH =,
        daterange(from_date, to_date, '[]'::text) WITH &&)
        WHERE (deleted_at IS NULL)
)

Plans

• Product id: Foreign key referencing a product, so that the plan can be put on an invoice line
• Billing interval: An integer determining how long each billing cycle is in months. If the plan is paid monthly, this column should be 1.

CREATE TABLE IF NOT EXISTS plans
(
    id BIGSERIAL PRIMARY KEY,
    product_id BIGINT NOT NULL,
    billing_interval integer NOT NULL DEFAULT 1,
    created_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at timestamp with time zone,
    CONSTRAINT plans_product_id_fkey FOREIGN KEY (product_id)
        REFERENCES products (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE RESTRICT
)

Invoices

CREATE TYPE invoice_status AS ENUM ('draft', 'unpaid', 'paid');

CREATE TABLE IF NOT EXISTS invoices
(
    id BIGSERIAL PRIMARY KEY,
    status invoice_status NOT NULL DEFAULT 'unpaid'::invoice_status,
    invoice_number integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1000 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    customer_id BIGINT NOT NULL,
    email character varying(320) NOT NULL,
    name character varying(320) NOT NULL,
    country character varying(2) NOT NULL,
    currency character varying(3) NOT NULL DEFAULT 'USD',
    address1 character varying(255) NOT NULL,
    address2 character varying(255),
    city character varying(255) NOT NULL,
    postal_code character varying(12) NOT NULL,
    phone character varying(24),
    invoice_date timestamp with time zone NOT NULL,
    due_date timestamp with time zone NOT NULL,
    paid_at timestamp with time zone,
    created_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at timestamp with time zone,
    CONSTRAINT invoices_invoice_number_key UNIQUE (invoice_number),
    CONSTRAINT invoices_currency_fkey FOREIGN KEY (currency)
        REFERENCES currencies (code) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE RESTRICT,
    CONSTRAINT invoices_customer_id_fkey FOREIGN KEY (customer_id)
        REFERENCES customers (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE RESTRICT
)

Invoice line items

CREATE TABLE IF NOT EXISTS invoice_line_items
(
    id BIGSERIAL PRIMARY KEY,
    invoice_id INTEGER NOT NULL,
    product_id BIGINT NOT NULL,
    line_amount integer NOT NULL DEFAULT 0,
    vat_amount integer NOT NULL DEFAULT 0,
    vat_percentage integer NOT NULL DEFAULT 0,
    unit_price numeric(12,2) NOT NULL DEFAULT 0,
    quantity numeric(12,2) NOT NULL DEFAULT 1,
    created_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at timestamp with time zone,
    CONSTRAINT invoice_line_items_invoice_id_fkey FOREIGN KEY (invoice_id)
        REFERENCES invoices (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE,
    CONSTRAINT invoice_line_items_product_id_fkey FOREIGN KEY (product_id)
        REFERENCES products (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE RESTRICT
)

Subscriptions

Last but not least: the subscriptions table. This is the most complex table. It will contain all the necessary data to handle subscription upgrades, downgrades and canceling. Only one row with status ‘active’ per customer is allowed within a start-end timestamp interval. This prevents a customer from having two active subscriptions at the same time.

When a subscription is renewed for a another period, a new row is created in this table.

• Customer id: Foreign key referencing a customer.
• Plan id: Foreign key referencing a plan.
• Start timestamp: The timestamp the subscription period starts
• End timestamp: The timestamp the subscription period ends. Is equal to the start timestamp + the months per interval of the plan.
• Renewed at Timestamp this subscription period was renewed, if it has been renewed.
• Renewed subscription id A reference to the ID of the renewed subscription period, if this has been renewed.
• Downgraded to plan id A foreign key referencing the ID of a plan a customers want to downgrade to, for the next subscription period.
• Downgraded at The timestamp when the customer asked to be downgraded
• Upgraded to plan id A foreign key referencing the ID of a plan a customers has upgraded to.
• Upgraded at The timestamp when the customer asked to be upgraded

CREATE TYPE subscription_status AS ENUM ('inactive', 'active', 'upgraded');

CREATE TABLE IF NOT EXISTS subscriptions
(
    id BIGSERIAL PRIMARY KEY,
    status subscription_status NOT NULL,
    customer_id BIGINT NOT NULL,
    plan_id BIGINT NOT NULL,
    invoice_id BIGINT NOT NULL,
    starts_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ends_at timestamp with time zone,
    renewed_at timestamp with time zone,
    renewed_subscription_id BIGINT,
    downgraded_at timestamp with time zone,
    downgraded_to_plan_id BIGINT,
    upgraded_at timestamp with time zone,
    upgraded_to_plan_id BIGINT,
    cancelled_at timestamp with time zone,
    created_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at timestamp with time zone,
    CONSTRAINT subscriptions_downgraded_to_plan_id_fkey FOREIGN KEY (downgraded_to_plan_id)
        REFERENCES plans (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT subscriptions_invoice_id_fkey FOREIGN KEY (invoice_id)
        REFERENCES invoices (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT subscriptions_customer_id_fkey FOREIGN KEY (customer_id)
        REFERENCES customers (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT subscriptions_plan_id_fkey FOREIGN KEY (plan_id)
        REFERENCES plans (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT subscriptions_renewed_subscription_id_fkey FOREIGN KEY (renewed_subscription_id)
        REFERENCES subscriptions (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT subscriptions_upgraded_to_plan_id_fkey FOREIGN KEY (upgraded_to_plan_id)
        REFERENCES plans (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT unique_subscription_in_interval EXCLUDE USING gist (
        customer_id WITH =,
        tstzrange(starts_at, ends_at, '[]'::text) WITH &&)
        WHERE (deleted_at IS NULL AND status = 'active')
)

Working with the database

Now that we have our database ready, how do we start using it?

Setting up the plans

First, we must create a currency, then a product, price and plan. And also a customer that can subscribe.

INSERT INTO currencies (code, name)
VALUES ('USD', 'United States dollar');

-- A product representing our monthly plan
INSERT INTO products (name, description)
VALUES ('Monthly basic plan', 'Monthly subscription to our basic service');

-- A product representing our monthly premium plan
INSERT INTO products (name, description)
VALUES ('Monthly premium plan', 'Monthly subscription to our premium service');

-- A product representing proration; used when we need to deduct money from the invoice when upgrading a subscription
INSERT INTO products (name, description)
VALUES ('Proration', '');

-- Then we create the plan with a reference to our new product with id 1.
INSERT INTO plans (product_id, billing_interval)
VALUES (1, 1);
INSERT INTO plans (product_id, billing_interval)
VALUES (2, 1);

-- Then we create two prices in USD, 5 dollars (500 cent) for the basic plan and 10 for premium.
-- These will last from today to 1000 years in the future
INSERT INTO products_pricing (product_id, from_date, to_date, price, currency)
VALUES (1, CURRENT_DATE, CURRENT_DATE + INTERVAL '1000 YEARS', 500, 'USD');
INSERT INTO products_pricing (product_id, from_date, to_date, price, currency)
VALUES (2, CURRENT_DATE, CURRENT_DATE + INTERVAL '1000 YEARS', 1000, 'USD');

-- Lets also create a customer which we can subscribe to our plans
INSERT INTO customers (name, phone, email, currency, address1, address2, city, postal_code)
VALUES ('Dunder Mifflin Paper Company', '123-1234567', '[email protected]', 'USD', '1725 Slough Avenue', 'Suite 200 Scranton Business Park', 'Scranton', '12345');

Displaying available plans with descriptions and prices

When your application displays the available plans to a user, it should be in the appropriate currency for the customer. Our example customer will see prices in USD:

SELECT
    plan.id,
	product.name,
	product.description,
	price.price,
	price.currency
FROM plans AS plan
LEFT JOIN products AS product ON plan.product_id = product.id
LEFT JOIN products_pricing AS price ON product.id = price.product_id
WHERE CURRENT_DATE BETWEEN price.from_date AND price.to_date
AND plan.deleted_at IS NULL AND product.deleted_at IS NULL
AND price.deleted_at IS NULL AND price.currency = 'USD';

Creating a subscription

Now, our customer wants to subscribe to the basic plan, with id 1. This is how we do it:

-- Create an invoice and a line item
INSERT INTO invoices (status, customer_id, email, name, country, currency, address1, address2, city, postal_code, phone, invoice_date, due_date)
VALUES ('draft', 1, '[email protected]', 'Dunder Mifflin Paper Company', 'US', 'USD', '1725 Slough Avenue', 'Suite 200 Scranton Business Park', 'Scranton', '12345', '123456', CURRENT_DATE, CURRENT_DATE );

-- Lets say the VAT is 12%
INSERT INTO invoice_line_items (invoice_id, product_id, line_amount, vat_amount, vat_percentage, unit_price, quantity) VALUES (1, 1, 500, 60, 12, 500, 1);

-- Create the subscription with a reference to the invoice
-- ends_at should be set to starts_at + the billing interval of the plan, which is 1 in our example
INSERT INTO subscriptions (status, customer_id, plan_id, invoice_id, starts_at, ends_at)
VALUES('inactive', 1, 1, 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP + INTERVAL '1 month');

-- Once the invoice is paid, we update the status of the invoice and subscription
-- There are multiple ways of finding out if an invoice is paid.
-- With Stripe, we could use a webhook.
UPDATE invoices SET STATUS = 'paid'
WHERE id = 1;

-- We also set the starts_at to the current timestamp, because we want the
-- period to start the moment the customer pays the invoice. ends_at is
-- starts_at + the billing interval of the plan.
UPDATE subscriptions SET
    status = 'active',
    starts_at = CURRENT_TIMESTAMP,
    ends_At = CURRENT_TIMESTAMP + INTERVAL '1 month'
WHERE id = 1;

Checking if our customer has and active subscription

We can use this query to find out if a customer has an active subscription

SELECT *
FROM subscriptions
WHERE customer_id = 1
AND CURRENT_TIMESTAMP BETWEEN starts_at AND ends_at
AND deleted_at IS NULL AND status = 'active';

If the query returns a row, the customer has an active subscription.

Upgrading a subscription

Upgrading to a more expensive plan should happen instantly. The customer should be billed a prorated amount and immediately be put on the new plan.

In our example, the customer wants to upgrade to the plan with id 2.

-- Then we create a new invoice with proration and the new subscription
INSERT INTO invoices (status, customer_id, email, name, country, currency, address1, address2, city, postal_code, phone, invoice_date, due_date)
VALUES ('draft', 1, '[email protected]', 'Dunder Mifflin Paper Company', 'US', 'USD', '1725 Slough Avenue', 'Suite 200 Scranton Business Park', 'Scranton', '12345', '123456', CURRENT_DATE, CURRENT_DATE );

-- Invoice line that deducts the prorated amount.
-- We calculate the unused time on the old plan like this, which will be deducted from the invoice:
SELECT
    100 - EXTRACT(EPOCH FROM CURRENT_TIMESTAMP - starts_at) / EXTRACT(EPOCH FROM ends_at - starts_at) AS usage
FROM subscriptions
WHERE customer_id = 1
AND current_timestamp BETWEEN starts_at AND ends_at
AND deleted_at IS NULL AND status = 'active';

-- If the query above returned 0.8, we deduct 0.8 * 500 = 400 (price of basic plan)
INSERT INTO invoice_line_items (invoice_id, product_id, line_amount, vat_amount, vat_percentage, unit_price, quantity) VALUES (2, 2, -400, 0, 0, 0, 1);

-- Invoice line for the new plan
INSERT INTO invoice_line_items (invoice_id, product_id, line_amount, vat_amount, vat_percentage, unit_price, quantity) VALUES (2, 2, 1000, 120, 12, 1000, 1);

-- Then we update the current subscription with id 1
UPDATE subscriptions SET
    upgraded_at = CURRENT_TIMESTAMP,
    upgraded_to_plan_id = 2,
    status = 'upgraded'
WHERE customer_id = 1
    AND CURRENT_TIMESTAMP BETWEEN starts_at AND ends_at
    AND deleted_at IS NULL AND status = 'active';

-- The next step is to create the new subscription row
INSERT INTO subscriptions (status, customer_id, plan_id, invoice_id, starts_at, ends_at)
VALUES('active', 1, 2, 2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP + INTERVAL '1 month');

When upgrading, we wont require the customer to pay before getting access to the service. The invoice is however due on the same day, and if the customer doesn’t pay before it’s due, we will restrict the usage and mark their account as “past due”. This is inspired by Digitalocean.

Downgrading a subscription

Downgrading does not happen instantly. The customer is put on the cheaper plan once the next billing period starts.
We run this query to downgrade a plan.

UPDATE subscriptions SET
    downgraded_at = CURRENT_TIMESTAMP,
    downgraded_to_plan_id = 1
WHERE customer_id = 1
    AND CURRENT_TIMESTAMP BETWEEN starts_at AND ends_at
    AND deleted_at IS NULL AND status = 'active';

Then we deal with the remaining steps of the downgrade when running renewals.

Renewing a subscription

Subscription renewals run inside a background job that selects the subscriptions that are up for renewal, and performs the neccessary actions. We require the customer to cancel their subscription one week prior to renewal, otherwise they will have to pay for another period.

-- Fetch all subscriptions up for renewal
SELECT *
FROM subscriptions A
WHERE
    A.ends_at - INTERVAL '7 DAYS' <= CURRENT_TIMESTAMP AND
    A.deleted_at IS NULL AND
    A.status = 'active' AND
    A.renewed_at IS NULL AND
    a.cancelled_at IS NULL;

Let’s say that the subscription with id 2 that we just upgraded to is up for renewal.

What we then do is create a new invoice with an invoice line representing the plan for the next period. Then we create a new subscription row, with value starts_at being equal to the ends_at value of the current subscription row, plus 1 milisecond so that they don’t overlap.

If the customer has downgraded their plan, we simply bill for the less expensive plan (in the column downgraded_to_plan_id) and create a subscription row with that plan instead of the previous plan.

Once a subscription row has been renewed, we run the following update query:

UPDATE subscriptions SET
    renewed_at = CURRENT_TIMESTAMP,
    renewed_subscription_id = 2 -- Reference to the new subscription row we just create
WHERE customer_id = 1
    AND CURRENT_TIMESTAMP BETWEEN starts_at AND ends_at
    AND deleted_at IS NULL AND status = 'active';

Canceling a subscription

Canceling a subscription simply means that the subscription should not be renewed. This is accomplished by setting cancelled_at to the current timestamp:

UPDATE subscriptions SET
    cancelled_at = CURRENT_TIMESTAMP
WHERE customer_id = 1
    AND CURRENT_TIMESTAMP BETWEEN starts_at AND ends_at
    AND deleted_at IS NULL AND status = 'active';

By doing so, the query to list renewable subscriptions wont select the row.

Final thoughts

This is by no means a complete setup for dealing with all aspects of running a subscription-oriented business. If you have suggestions on how to improve this, or any questions, please let me know in the comment section below!