MySQL Subscription Billing Project: Plans, Invoices, MRR

Project goal: the database of a software-as-a-service business: customers subscribe to plans, are invoiced each month, and management watches monthly recurring revenue (MRR) and churn. It pulls together valid-from/valid-to periods, idempotent jobs, calendar CTEs and reporting.

Requirements

A plan has a name and a monthly price.
A customer has at most one active subscription at a time. A subscription has a plan, a start date and an end date (empty while it is active). Changing plan ends the old subscription and starts a new one.
Each subscription is invoiced once per month. Running the invoicing job twice must not create duplicates.
Reports: MRR per month, customers who left (churn), unpaid invoices and revenue per plan.

Tables and relationships

Table	Relationship
`plans` 1 — N `subscriptions`	A subscription uses one plan
`customers` 1 — N `subscriptions`	Over time, a customer has several (one active)
`subscriptions` 1 — N `invoices`	One invoice per subscription per month

The schema

query.sql

SQL

CREATE TABLE plans (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    monthly_price DECIMAL(8,2) NOT NULL,
    UNIQUE KEY uq_plans_name (name),
    CONSTRAINT chk_plan_price CHECK (monthly_price >= 0)
);

CREATE TABLE customers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(60) NOT NULL,
    email VARCHAR(100) NOT NULL,
    UNIQUE KEY uq_customers_email (email)
);

CREATE TABLE subscriptions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id INT UNSIGNED NOT NULL,
    plan_id INT UNSIGNED NOT NULL,
    started_on DATE NOT NULL,
    ended_on DATE NULL,
    active_customer_id INT UNSIGNED GENERATED ALWAYS AS (IF(ended_on IS NULL, customer_id, NULL)) STORED,
    UNIQUE KEY uq_one_active_subscription (active_customer_id),
    KEY idx_subscriptions_customer (customer_id, started_on),
    CONSTRAINT chk_subscription_period CHECK (ended_on IS NULL OR ended_on > started_on),
    CONSTRAINT fk_subscriptions_customer FOREIGN KEY (customer_id) REFERENCES customers (id),
    CONSTRAINT fk_subscriptions_plan FOREIGN KEY (plan_id) REFERENCES plans (id)
);

CREATE TABLE invoices (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    subscription_id INT UNSIGNED NOT NULL,
    period_start DATE NOT NULL,
    amount DECIMAL(8,2) NOT NULL,
    paid_at DATETIME NULL,
    UNIQUE KEY uq_invoice_per_period (subscription_id, period_start),
    CONSTRAINT fk_invoices_subscription FOREIGN KEY (subscription_id) REFERENCES subscriptions (id)
);

Output

OUTPUT

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

Two constraints carry the business rules: uq_one_active_subscription (a generated column plus a unique index) allows only one open subscription per customer, and uq_invoice_per_period allows only one invoice per subscription and month, which is what makes the invoicing job safe to repeat. ended_on is exclusive: a subscription ending on 1 March was active through 28 February.

Sample data

query.sql

SQL

INSERT INTO plans (name, monthly_price) VALUES ('Basic', 10.00), ('Pro', 25.00), ('Business', 60.00);
INSERT INTO customers (name, email) VALUES ('Maya', 'maya@example.com'), ('Rohan', 'rohan@example.com'), ('Isha', 'isha@example.com'), ('Kabir', 'kabir@example.com'), ('Zoya', 'zoya@example.com');

INSERT INTO subscriptions (customer_id, plan_id, started_on, ended_on) VALUES
(1, 1, '2026-01-01', '2026-03-01'),
(1, 2, '2026-03-01', NULL),
(2, 2, '2026-01-01', NULL),
(3, 1, '2026-02-01', '2026-04-01'),
(4, 3, '2026-02-01', NULL),
(5, 1, '2026-03-01', NULL);

Output

OUTPUT

Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

Query OK, 5 rows affected
Records: 5  Duplicates: 0  Warnings: 0

Query OK, 6 rows affected
Records: 6  Duplicates: 0  Warnings: 0

Maya started on Basic, and upgraded to Pro on 1 March (the first subscription ended, and the second started the same day). Isha cancelled on 1 April.

Generating the invoices: an idempotent job

For a given month, invoice every subscription that was active on the first day of that month. The ON DUPLICATE KEY UPDATE invoices.id = invoices.id makes the statement do nothing for invoices that already exist, so the job can be run again after a crash (Idempotent Requests):

query.sql

SQL

INSERT INTO invoices (subscription_id, period_start, amount)
SELECT s.id, '2026-03-01', p.monthly_price
FROM subscriptions s JOIN plans p ON p.id = s.plan_id
WHERE s.started_on <= '2026-03-01' AND (s.ended_on IS NULL OR s.ended_on > '2026-03-01')
ON DUPLICATE KEY UPDATE invoices.id = invoices.id;
SELECT ROW_COUNT() AS rows_affected_first_run;

Output

OUTPUT

+-------------------------+
| rows_affected_first_run |
+-------------------------+
|                       5 |
+-------------------------+
1 row in set

Run the same statement a second time:

query.sql

SQL

INSERT INTO invoices (subscription_id, period_start, amount)
SELECT s.id, '2026-03-01', p.monthly_price
FROM subscriptions s JOIN plans p ON p.id = s.plan_id
WHERE s.started_on <= '2026-03-01' AND (s.ended_on IS NULL OR s.ended_on > '2026-03-01')
ON DUPLICATE KEY UPDATE invoices.id = invoices.id;
SELECT ROW_COUNT() AS rows_affected_second_run, (SELECT COUNT(*) FROM invoices) AS invoices_total;

Output

OUTPUT

+--------------------------+----------------+
| rows_affected_second_run | invoices_total |
+--------------------------+----------------+
|                        0 |              5 |
+--------------------------+----------------+
1 row in set

The second run changed nothing, and there are still only five invoices for March: Maya’s Basic subscription ended on 1 March, so it is not invoiced for March. (ROW_COUNT() is 5 on the first run: five new rows. On the second run it is 0, because each “update” sets the row to its own value.) Add January and February the same way:

query.sql

SQL

INSERT INTO invoices (subscription_id, period_start, amount)
SELECT s.id, m.period_start, p.monthly_price
FROM (SELECT DATE '2026-01-01' AS period_start UNION ALL SELECT DATE '2026-02-01') m
JOIN subscriptions s ON s.started_on <= m.period_start AND (s.ended_on IS NULL OR s.ended_on > m.period_start)
JOIN plans p ON p.id = s.plan_id
ON DUPLICATE KEY UPDATE invoices.id = invoices.id;

UPDATE invoices SET paid_at = '2026-03-03 10:00:00' WHERE period_start <= '2026-02-01';
UPDATE invoices SET paid_at = '2026-03-04 09:00:00' WHERE period_start = '2026-03-01' AND subscription_id IN (2, 3);

SELECT period_start, COUNT(*) AS invoices, SUM(amount) AS billed, SUM(paid_at IS NOT NULL) AS paid FROM invoices GROUP BY period_start ORDER BY period_start;

Output

OUTPUT

+--------------+----------+--------+------+
| period_start | invoices | billed | paid |
+--------------+----------+--------+------+
| 2026-01-01   |        2 |  35.00 |    2 |
| 2026-02-01   |        4 | 105.00 |    4 |
| 2026-03-01   |        5 | 130.00 |    2 |
+--------------+----------+--------+------+
3 rows in set

Changing a plan

An upgrade ends the current subscription and starts a new one, in one transaction. If it fails half way, the customer must not be left with none, or with two active ones (the unique key would refuse the second):

query.sql

SQL

DELIMITER //

CREATE PROCEDURE change_plan(IN p_customer INT UNSIGNED, IN p_plan INT UNSIGNED, IN p_effective DATE)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;
    UPDATE subscriptions SET ended_on = p_effective WHERE customer_id = p_customer AND ended_on IS NULL;
    INSERT INTO subscriptions (customer_id, plan_id, started_on) VALUES (p_customer, p_plan, p_effective);
    COMMIT;
END//

DELIMITER ;

CALL change_plan(5, 2, '2026-04-01');
SELECT s.id, p.name AS plan, s.started_on, s.ended_on FROM subscriptions s JOIN plans p ON p.id = s.plan_id WHERE s.customer_id = 5 ORDER BY s.started_on;

Output

OUTPUT

+----+-------+------------+------------+
| id | plan  | started_on | ended_on   |
+----+-------+------------+------------+
|  6 | Basic | 2026-03-01 | 2026-04-01 |
|  7 | Pro   | 2026-04-01 | NULL       |
+----+-------+------------+------------+
2 rows in set

A second active subscription for the same customer is refused by the database, whatever the application does:

query.sql

SQL

INSERT INTO subscriptions (customer_id, plan_id, started_on) VALUES (2, 1, '2026-04-01');

Output

OUTPUT

ERROR 1062 (23000): Duplicate entry '2' for key 'subscriptions.uq_one_active_subscription'

Query 1: MRR by month

MRR is the sum of the monthly prices of the subscriptions active at the start of each month. A calendar CTE gives the months:

query.sql

SQL

WITH RECURSIVE months AS (
    SELECT DATE '2026-01-01' AS month_start
    UNION ALL
    SELECT month_start + INTERVAL 1 MONTH FROM months WHERE month_start < '2026-04-01'
)
SELECT m.month_start, COUNT(s.id) AS active_subscriptions, COALESCE(SUM(p.monthly_price), 0) AS mrr
FROM months m
LEFT JOIN subscriptions s ON s.started_on <= m.month_start AND (s.ended_on IS NULL OR s.ended_on > m.month_start)
LEFT JOIN plans p ON p.id = s.plan_id
GROUP BY m.month_start
ORDER BY m.month_start;

Output

OUTPUT

+-------------+----------------------+--------+
| month_start | active_subscriptions | mrr    |
+-------------+----------------------+--------+
| 2026-01-01  |                    2 |  35.00 |
| 2026-02-01  |                    4 | 105.00 |
| 2026-03-01  |                    5 | 130.00 |
| 2026-04-01  |                    4 | 135.00 |
+-------------+----------------------+--------+
4 rows in set

The LEFT JOINs keep months with no subscriptions. Maya’s upgrade shows as a change from 10 to 25 in her contribution in March, and the cancellations show up in April.

Query 2: MRR movement: new, expansion, and churn

Compare each customer’s MRR in two consecutive months:

query.sql

SQL

WITH RECURSIVE months AS (
    SELECT DATE '2026-01-01' AS month_start
    UNION ALL
    SELECT month_start + INTERVAL 1 MONTH FROM months WHERE month_start < '2026-04-01'
),
customer_mrr AS (
    SELECT m.month_start, s.customer_id, SUM(p.monthly_price) AS mrr
    FROM months m
    JOIN subscriptions s ON s.started_on <= m.month_start AND (s.ended_on IS NULL OR s.ended_on > m.month_start)
    JOIN plans p ON p.id = s.plan_id
    GROUP BY m.month_start, s.customer_id
),
movement AS (
    SELECT month_start, customer_id, mrr, LAG(mrr) OVER (PARTITION BY customer_id ORDER BY month_start) AS previous_mrr FROM customer_mrr
)
SELECT month_start,
       SUM(CASE WHEN previous_mrr IS NULL THEN mrr ELSE 0 END) AS new_mrr,
       SUM(CASE WHEN previous_mrr IS NOT NULL AND mrr > previous_mrr THEN mrr - previous_mrr ELSE 0 END) AS expansion_mrr
FROM movement
GROUP BY month_start
ORDER BY month_start;

Output

OUTPUT

+-------------+---------+---------------+
| month_start | new_mrr | expansion_mrr |
+-------------+---------+---------------+
| 2026-01-01  |   35.00 |          0.00 |
| 2026-02-01  |   70.00 |          0.00 |
| 2026-03-01  |   10.00 |         15.00 |
| 2026-04-01  |    0.00 |         15.00 |
+-------------+---------+---------------+
4 rows in set

New customers add new MRR, and an upgrade is expansion. (A full report also shows contraction and churn. Churn needs the customers that were present in a month and absent the next, which is Query 3.)

Query 3: churned customers

Customers whose last subscription ended and who have no active one:

query.sql

SQL

SELECT c.name, MAX(s.ended_on) AS left_on
FROM customers c JOIN subscriptions s ON s.customer_id = c.id
GROUP BY c.id, c.name
HAVING SUM(s.ended_on IS NULL) = 0
ORDER BY left_on;

Output

OUTPUT

+------+------------+
| name | left_on    |
+------+------------+
| Isha | 2026-04-01 |
+------+------------+
1 row in set

Isha is the only churned customer: her one subscription ended and she has no active one. Maya’s first subscription ended too, but she has an active one, so she is not counted. SUM(s.ended_on IS NULL) counts the active subscriptions per customer.

Query 4: revenue per plan and unpaid invoices

query.sql

SQL

SELECT p.name AS plan, COUNT(*) AS invoices, SUM(i.amount) AS billed, SUM(CASE WHEN i.paid_at IS NOT NULL THEN i.amount ELSE 0 END) AS collected
FROM invoices i JOIN subscriptions s ON s.id = i.subscription_id JOIN plans p ON p.id = s.plan_id
GROUP BY p.id, p.name
ORDER BY billed DESC;

SELECT c.name, i.period_start, i.amount
FROM invoices i JOIN subscriptions s ON s.id = i.subscription_id JOIN customers c ON c.id = s.customer_id
WHERE i.paid_at IS NULL
ORDER BY i.period_start, c.name;

Output

OUTPUT

+----------+----------+--------+-----------+
| plan     | invoices | billed | collected |
+----------+----------+--------+-----------+
| Business |        2 | 120.00 |     60.00 |
| Pro      |        4 | 100.00 |    100.00 |
| Basic    |        5 |  50.00 |     30.00 |
+----------+----------+--------+-----------+
3 rows in set

+-------+--------------+--------+
| name  | period_start | amount |
+-------+--------------+--------+
| Isha  | 2026-03-01   |  10.00 |
| Kabir | 2026-03-01   |  60.00 |
| Zoya  | 2026-03-01   |  10.00 |
+-------+--------------+--------+
3 rows in set

Common mistakes

Using BETWEEN or an inclusive end date for subscription periods. A customer who cancels on the 1st then counts for the whole month. Use ended_on as an exclusive end.
A job that creates invoices without a unique key. Running it twice (a retry, a scheduler glitch) bills customers twice. The UNIQUE (subscription_id, period_start) makes it safe.
Storing the price on the plan only. When a plan’s price changes, old invoices must not change: the amount is stored on the invoice, and a plan price history (Price History) records what the price was.
Counting a customer twice in MRR because of an overlapping upgrade. The uq_one_active_subscription index stops two open subscriptions.
Pro-rating forgotten. This model bills full months. A mid-month change needs a rule, and an invoice line table.
Time zones. Use plain dates for billing periods, in one agreed time zone.

Final result

query.sql

SQL

SELECT (SELECT COUNT(*) FROM subscriptions WHERE ended_on IS NULL) AS active_subscriptions,
       (SELECT SUM(p.monthly_price) FROM subscriptions s JOIN plans p ON p.id = s.plan_id WHERE s.ended_on IS NULL) AS current_mrr,
       (SELECT COUNT(*) FROM invoices) AS invoices,
       (SELECT COUNT(*) FROM invoices WHERE paid_at IS NULL) AS unpaid;

Output

OUTPUT

+----------------------+-------------+----------+--------+
| active_subscriptions | current_mrr | invoices | unpaid |
+----------------------+-------------+----------+--------+
|                    4 |      135.00 |       11 |      3 |
+----------------------+-------------+----------+--------+
1 row in set

Next steps: invoice line items and taxes, payment attempts with idempotency keys, trials and discounts, and a daily snapshot of MRR for fast dashboards (Summary Tables).

You have now designed ten complete schemas. The skills they share are the real lesson: model the facts, let the database enforce the rules, keep history append-only, keep related changes in one transaction, and test with data that tries to break it. The last chapter is practice and interview preparation (SQL Practice).