Skip to content

Instantly share code, notes, and snippets.

@julienbourdeau

julienbourdeau/billing_service.sql

Created June 4, 2024 16:59
Show Gist options
  • Save julienbourdeau/e6fec237755d742f3f54cd5c0b7befba to your computer and use it in GitHub Desktop.
Save julienbourdeau/e6fec237755d742f3f54cd5c0b7befba to your computer and use it in GitHub Desktop.
Download ZIP
Lago Billing SQL
Raw
billing_service.sql
WITH billable_subscriptions AS (
-- Calendar subscriptions
(
SELECT
subscriptions.id AS subscription_id
FROM
subscriptions
INNER JOIN plans ON plans.id = subscriptions.plan_id
INNER JOIN customers ON customers.id = subscriptions.customer_id
INNER JOIN organizations ON organizations.id = customers.organization_id
WHERE
subscriptions.status = 1
AND subscriptions.billing_time = 0
AND plans.interval = 0
AND EXTRACT(ISODOW FROM ('2024-06-04 13:29:43.137622'::timestamptz AT TIME ZONE COALESCE(customers.timezone,
organizations.timezone,
'UTC'))) = 1
GROUP BY
subscriptions.id)
UNION (
SELECT
subscriptions.id AS subscription_id
FROM
subscriptions
INNER JOIN plans ON plans.id = subscriptions.plan_id
INNER JOIN customers ON customers.id = subscriptions.customer_id
INNER JOIN organizations ON organizations.id = customers.organization_id
WHERE
subscriptions.status = 1
AND subscriptions.billing_time = 0
AND plans.interval = 1
AND DATE_PART('day',
('2024-06-04 13:29:43.137622'::timestamptz AT TIME ZONE COALESCE(customers.timezone,
organizations.timezone,
'UTC'))) = 1
GROUP BY
subscriptions.id)
UNION (
SELECT
subscriptions.id AS subscription_id
FROM
subscriptions
INNER JOIN plans ON plans.id = subscriptions.plan_id
INNER JOIN customers ON customers.id = subscriptions.customer_id
INNER JOIN organizations ON organizations.id = customers.organization_id
WHERE
subscriptions.status = 1
AND subscriptions.billing_time = 0
AND plans.interval = 3
AND(DATE_PART('month',
('2024-06-04 13:29:43.137622'::timestamptz AT TIME ZONE COALESCE(customers.timezone,
organizations.timezone,
'UTC')))
IN(1,
4,
7,
10))
AND(DATE_PART('day',
('2024-06-04 13:29:43.137622'::timestamptz AT TIME ZONE COALESCE(customers.timezone,
organizations.timezone,
'UTC'))) = 1)
GROUP BY
subscriptions.id)
UNION (
SELECT
subscriptions.id AS subscription_id
FROM
subscriptions
INNER JOIN plans ON plans.id = subscriptions.plan_id
INNER JOIN customers ON customers.id = subscriptions.customer_id
INNER JOIN organizations ON organizations.id = customers.organization_id
WHERE
subscriptions.status = 1
AND subscriptions.billing_time = 0
AND plans.interval = 2
AND DATE_PART('day',
('2024-06-04 13:29:43.137622'::timestamptz AT TIME ZONE COALESCE(customers.timezone,
organizations.timezone,
'UTC'))) = 1
AND plans.bill_charges_monthly = 't'
GROUP BY
subscriptions.id)
UNION (
SELECT
subscriptions.id AS subscription_id
FROM
subscriptions
INNER JOIN plans ON plans.id = subscriptions.plan_id
INNER JOIN customers ON customers.id = subscriptions.customer_id
INNER JOIN organizations ON organizations.id = customers.organization_id
WHERE
subscriptions.status = 1
AND subscriptions.billing_time = 0
AND plans.interval = 2
AND DATE_PART('month',
('2024-06-04 13:29:43.137622'::timestamptz AT TIME ZONE COALESCE(customers.timezone,
organizations.timezone,
'UTC'))) = 1
AND DATE_PART('day',
('2024-06-04 13:29:43.137622'::timestamptz AT TIME ZONE COALESCE(customers.timezone,
organizations.timezone,
'UTC'))) = 1
GROUP BY
subscriptions.id)
UNION
-- Anniversary subscriptions
(
SELECT
subscriptions.id AS subscription_id
FROM
subscriptions
INNER JOIN plans ON plans.id = subscriptions.plan_id
INNER JOIN customers ON customers.id = subscriptions.customer_id
INNER JOIN organizations ON organizations.id = customers.organization_id
WHERE
subscriptions.status = 1
AND subscriptions.billing_time = 1
AND plans.interval = 0
AND EXTRACT(ISODOW FROM (subscriptions.subscription_at::timestamptz AT TIME ZONE COALESCE(customers.timezone,
organizations.timezone,
'UTC'))) = EXTRACT(ISODOW FROM ('2024-06-04 13:29:43.137622'::timestamptz AT TIME ZONE COALESCE(customers.timezone,
organizations.timezone,
'UTC')))
GROUP BY
subscriptions.id)
UNION (
SELECT
subscriptions.id AS subscription_id
FROM
subscriptions
INNER JOIN plans ON plans.id = subscriptions.plan_id
INNER JOIN customers ON customers.id = subscriptions.customer_id
INNER JOIN organizations ON organizations.id = customers.organization_id
WHERE
subscriptions.status = 1
AND subscriptions.billing_time = 1
AND plans.interval = 1
AND DATE_PART('day',
(subscriptions.subscription_at::timestamptz AT TIME ZONE COALESCE(customers.timezone,
organizations.timezone,
'UTC'))) = ANY (
-- Check if today is the last day of the month
CASE WHEN DATE_PART('day',
((DATE_TRUNC('month',
'2024-06-04 13:29:43.137622'::timestamptz AT TIME ZONE COALESCE(customers.timezone,
organizations.timezone,
'UTC')) + INTERVAL '1 month - 1 day')::date)) = DATE_PART('day',
'2024-06-04 13:29:43.137622'::timestamptz AT TIME ZONE COALESCE(customers.timezone,
organizations.timezone,
'UTC')) THEN
-- If so and if it counts less than 31 days, we need to take all days up to 31 into account
(
SELECT
ARRAY (
SELECT
generate_series(DATE_PART('day',
'2024-06-04 13:29:43.137622'::timestamptz AT TIME ZONE COALESCE(customers.timezone,
organizations.timezone,
'UTC'))::integer,
31)))
ELSE
-- Otherwise, we just need the current day
(
SELECT
ARRAY [DATE_PART('day', '2024-06-04 13:29:43.137622' ::timestamptz AT TIME ZONE COALESCE(customers.timezone, organizations.timezone, 'UTC')
)])
END)
GROUP BY
subscriptions.id)
UNION (
SELECT
subscriptions.id AS subscription_id
FROM
subscriptions
INNER JOIN plans ON plans.id = subscriptions.plan_id
INNER JOIN customers ON customers.id = subscriptions.customer_id
INNER JOIN organizations ON organizations.id = customers.organization_id
WHERE
subscriptions.status = 1
AND subscriptions.billing_time = 1
AND plans.interval = 3
AND(
-- We need to avoid zero and instead of it use 12. E.g.: (3 + 9) % 12 = 0 -> 12
CASE WHEN MOD(CAST(DATE_PART('month',
(subscriptions.subscription_at::timestamptz AT TIME ZONE COALESCE(customers.timezone,
organizations.timezone,
'UTC'))) AS INTEGER),
3) = 0 THEN
(DATE_PART('month',
'2024-06-04 13:29:43.137622'::timestamptz AT TIME ZONE COALESCE(customers.timezone,
organizations.timezone,
'UTC'))
IN(3,
6,
9,
12))
ELSE
(DATE_PART('month',
(subscriptions.subscription_at::timestamptz AT TIME ZONE COALESCE(customers.timezone,
organizations.timezone,
'UTC'))) = DATE_PART('month',
'2024-06-04 13:29:43.137622'::timestamptz AT TIME ZONE COALESCE(customers.timezone,
organizations.timezone,
'UTC'))
OR MOD(CAST(DATE_PART('month',
(subscriptions.subscription_at::timestamptz AT TIME ZONE COALESCE(customers.timezone,
organizations.timezone,
'UTC'))) + 3 AS INTEGER),
12) = DATE_PART('month',
'2024-06-04 13:29:43.137622'::timestamptz AT TIME ZONE COALESCE(customers.timezone,
organizations.timezone,
'UTC'))
OR MOD(CAST(DATE_PART('month',
(subscriptions.subscription_at::timestamptz AT TIME ZONE COALESCE(customers.timezone,
organizations.timezone,
'UTC'))) + 6 AS INTEGER),
12) = DATE_PART('month',
'2024-06-04 13:29:43.137622'::timestamptz AT TIME ZONE COALESCE(customers.timezone,
organizations.timezone,
'UTC'))
OR MOD(CAST(DATE_PART('month',
(subscriptions.subscription_at::timestamptz AT TIME ZONE COALESCE(customers.timezone,
organizations.timezone,
'UTC'))) + 9 AS INTEGER),
12) = DATE_PART('month',
'2024-06-04 13:29:43.137622'::timestamptz AT TIME ZONE COALESCE(customers.timezone,
organizations.timezone,
'UTC')))
END)
AND DATE_PART('day',
(subscriptions.subscription_at::timestamptz AT TIME ZONE COALESCE(customers.timezone,
organizations.timezone,
'UTC'))) = ANY (
-- Check if today is the last day of the month
CASE WHEN DATE_PART('day',
((DATE_TRUNC('month',
'2024-06-04 13:29:43.137622'::timestamptz AT TIME ZONE COALESCE(customers.timezone,
organizations.timezone,
'UTC')) + INTERVAL '1 month - 1 day')::date)) = DATE_PART('day',
'2024-06-04 13:29:43.137622'::timestamptz AT TIME ZONE COALESCE(customers.timezone,
organizations.timezone,
'UTC')) THEN
-- If so and if it counts less than 31 days, we need to take all days up to 31 into account
(
SELECT
ARRAY (
SELECT
generate_series(DATE_PART('day',
'2024-06-04 13:29:43.137622'::timestamptz AT TIME ZONE COALESCE(customers.timezone,
organizations.timezone,
'UTC'))::integer,
31)))
ELSE
-- Otherwise, we just need the current day
(
SELECT
ARRAY [DATE_PART('day', '2024-06-04 13:29:43.137622' ::timestamptz AT TIME ZONE COALESCE(customers.timezone, organizations.timezone, 'UTC')
)])
END)
GROUP BY
subscriptions.id)
UNION (
SELECT
subscriptions.id AS subscription_id
FROM
subscriptions
INNER JOIN plans ON plans.id = subscriptions.plan_id
INNER JOIN customers ON customers.id = subscriptions.customer_id
INNER JOIN organizations ON organizations.id = customers.organization_id
WHERE
subscriptions.status = 1
AND subscriptions.billing_time = 1
AND plans.interval = 2
AND plans.bill_charges_monthly = 't'
AND DATE_PART('day',
(subscriptions.subscription_at::timestamptz AT TIME ZONE COALESCE(customers.timezone,
organizations.timezone,
'UTC'))) = ANY (
-- Check if today is the last day of the month
CASE WHEN DATE_PART('day',
((DATE_TRUNC('month',
'2024-06-04 13:29:43.137622'::timestamptz AT TIME ZONE COALESCE(customers.timezone,
organizations.timezone,
'UTC')) + INTERVAL '1 month - 1 day')::date)) = DATE_PART('day',
'2024-06-04 13:29:43.137622'::timestamptz AT TIME ZONE COALESCE(customers.timezone,
organizations.timezone,
'UTC')) THEN
-- If so and if it counts less than 31 days, we need to take all days up to 31 into account
(
SELECT
ARRAY (
SELECT
generate_series(DATE_PART('day',
'2024-06-04 13:29:43.137622'::timestamptz AT TIME ZONE COALESCE(customers.timezone,
organizations.timezone,
'UTC'))::integer,
31)))
ELSE
-- Otherwise, we just need the current day
(
SELECT
ARRAY [DATE_PART('day', '2024-06-04 13:29:43.137622' ::timestamptz AT TIME ZONE COALESCE(customers.timezone, organizations.timezone, 'UTC')
)])
END)
GROUP BY
subscriptions.id)
UNION (
SELECT
subscriptions.id AS subscription_id
FROM
subscriptions
INNER JOIN plans ON plans.id = subscriptions.plan_id
INNER JOIN customers ON customers.id = subscriptions.customer_id
INNER JOIN organizations ON organizations.id = customers.organization_id
WHERE
subscriptions.status = 1
AND subscriptions.billing_time = 1
AND plans.interval = 2
AND -- Ensure we are on the billing month
DATE_PART('month',
(subscriptions.subscription_at::timestamptz AT TIME ZONE COALESCE(customers.timezone,
organizations.timezone,
'UTC'))) = DATE_PART('month',
'2024-06-04 13:29:43.137622'::timestamptz AT TIME ZONE COALESCE(customers.timezone,
organizations.timezone,
'UTC'))
AND -- Check if we are not in a leap year when today is february the 28th
DATE_PART('day',
(subscriptions.subscription_at::timestamptz AT TIME ZONE COALESCE(customers.timezone,
organizations.timezone,
'UTC'))) = ANY (
CASE WHEN (DATE_PART('month',
'2024-06-04 13:29:43.137622'::timestamptz AT TIME ZONE COALESCE(customers.timezone,
organizations.timezone,
'UTC')) = 2
AND DATE_PART('day',
'2024-06-04 13:29:43.137622'::timestamptz AT TIME ZONE COALESCE(customers.timezone,
organizations.timezone,
'UTC')) = 28
AND DATE_PART('day',
((DATE_TRUNC('month',
'2024-06-04 13:29:43.137622'::timestamptz AT TIME ZONE COALESCE(customers.timezone,
organizations.timezone,
'UTC')) + INTERVAL '1 month - 1 day')::date)) = 28) THEN
-- If not a leap year, we have to tale february the 29th into account
ARRAY [28, 29]
ELSE
-- Otherwise, we just need the current day
ARRAY [DATE_PART('day', '2024-06-04 13:29:43.137622' ::timestamptz AT TIME ZONE COALESCE(customers.timezone, organizations.timezone, 'UTC')
)]
END)
GROUP BY
subscriptions.id)
),
-- Filter subscriptions already billed today (in customer's applicable timezone)
already_billed_today AS (
SELECT
invoice_subscriptions.subscription_id,
COUNT(invoice_subscriptions.id) AS invoiced_count
FROM
invoice_subscriptions
INNER JOIN subscriptions AS sub ON invoice_subscriptions.subscription_id = sub.id
INNER JOIN customers AS cus ON sub.customer_id = cus.id
INNER JOIN organizations AS org ON cus.organization_id = org.id
WHERE
invoice_subscriptions.recurring = 't'
AND invoice_subscriptions.timestamp IS NOT NULL
AND DATE((invoice_subscriptions.timestamp)::timestamptz AT TIME ZONE COALESCE(cus.timezone,
org.timezone,
'UTC')) = DATE('2024-06-04 13:29:43.137622'::timestamptz AT TIME ZONE COALESCE(cus.timezone,
org.timezone,
'UTC'))
GROUP BY
invoice_subscriptions.subscription_id
) SELECT DISTINCT
(subscriptions.*)
FROM
subscriptions
INNER JOIN billable_subscriptions ON billable_subscriptions.subscription_id = subscriptions.id
INNER JOIN customers ON customers.id = subscriptions.customer_id
INNER JOIN organizations ON organizations.id = customers.organization_id
LEFT JOIN already_billed_today ON already_billed_today.subscription_id = subscriptions.id
WHERE
-- Exclude subscriptions already billed today
already_billed_today.invoiced_count IS NULL
-- Do not bill subscriptions that started this day, they are billed by another job
AND DATE(subscriptions.started_at::timestamptz AT TIME ZONE COALESCE(customers.timezone, organizations.timezone, 'UTC')) != DATE('2024-06-04 13:29:43.137622'::timestamptz AT TIME ZONE COALESCE(customers.timezone, organizations.timezone, 'UTC'))
AND(subscriptions.ending_at IS NULL
OR DATE(subscriptions.ending_at::timestamptz AT TIME ZONE COALESCE(customers.timezone, organizations.timezone, 'UTC')) != DATE('2024-06-04 13:29:43.137622'::timestamptz AT TIME ZONE COALESCE(customers.timezone, organizations.timezone, 'UTC')))
GROUP BY
subscriptions.id
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment