Billing

prices

subscriptions

invoices

payments

Enums

billing_interval

subscription_status

invoice_status

payment_status

Row-Level Security

All five tables are scoped to the current user's organization via get_user_org_id(). Any org member can select, insert, and update rows. Only admins (checked via is_admin()) can delete products, prices, subscriptions, invoices, or payments.

Dependencies

• crm -- contacts, companies
• identity (transitive via crm) -- organizations, users, get_user_org_id(), is_admin(), set_updated_at()

Example Queries

Calculate monthly recurring revenue (MRR) across all active subscriptions:

SELECT
  sum(
    CASE p.interval
      WHEN 'month' THEN p.amount * s.quantity
      WHEN 'year'  THEN (p.amount * s.quantity) / 12
      ELSE 0
    END
  ) / 100.0 AS mrr_dollars
FROM subscriptions s
JOIN prices p ON p.id = s.price_id
WHERE s.status = 'active';

Break down annual recurring revenue (ARR) by product:

SELECT
  pr.name AS product,
  count(s.id) AS active_subscriptions,
  sum(s.quantity) AS total_seats,
  sum(
    CASE p.interval
      WHEN 'month' THEN p.amount * s.quantity * 12
      WHEN 'year'  THEN p.amount * s.quantity
      ELSE p.amount * s.quantity
    END
  ) / 100.0 AS arr_dollars
FROM products pr
JOIN prices p ON p.product_id = pr.id
JOIN subscriptions s ON s.price_id = p.id AND s.status = 'active'
GROUP BY pr.id, pr.name
ORDER BY arr_dollars DESC;

List outstanding invoices with urgency classification:

SELECT
  i.number,
  co.name AS company,
  i.total / 100.0 AS total_dollars,
  i.amount_due / 100.0 AS due_dollars,
  i.due_at,
  CASE
    WHEN i.due_at < now() THEN 'overdue'
    WHEN i.due_at < now() + interval '7 days' THEN 'due_soon'
    ELSE 'upcoming'
  END AS urgency
FROM invoices i
LEFT JOIN companies co ON co.id = i.company_id
WHERE i.status IN ('open', 'past_due')
ORDER BY i.due_at ASC;

Show payment history for a specific invoice:

SELECT
  p.id,
  p.amount / 100.0 AS amount_dollars,
  p.currency,
  p.status,
  p.method,
  p.reference,
  p.paid_at
FROM payments p
WHERE p.invoice_id = '<invoice_id>'
ORDER BY p.created_at ASC;

List subscriptions expiring within the next 30 days:

SELECT
  s.id,
  co.name AS company,
  pr.name AS product,
  s.quantity AS seats,
  s.status,
  s.current_period_end
FROM subscriptions s
JOIN prices p ON p.id = s.price_id
JOIN products pr ON pr.id = p.product_id
LEFT JOIN companies co ON co.id = s.company_id
WHERE s.status = 'active'
  AND s.current_period_end < now() + interval '30 days'
ORDER BY s.current_period_end ASC;