Billing
Plans & Features
Billing Providers
Billing varies greatly by each business. As a result, we do not provide any default billing providers. As inspriation, we have adapted partly Basejump's Stripe database implementation below. This approach can be tweaked for other providers, such as Stripe
You will need to add webhook handling to the database yourself.
/**
* -------------------------------------------------------
* Section - Billing (Stripe)
* -------------------------------------------------------
*/
-- Largely derived from basejump
-- This can be optionally used if Stripe is used for billing
-- Otherwise, you would need to adjust for your provider. Or if you handle billing manually, would recommend using a custom approach.
create type api.billing_status as enum ('trialing', 'active', 'canceled', 'incomplete', 'incomplete_expired', 'past_due', 'unpaid');
create cast (text as api.billing_status) with inout as implicit;
create cast (char varying as api.billing_status) with inout as implicit;
create table if not exists api.billing_customers
(
tenant_id uuid references api.tenants on delete cascade not null,
-- The user's customer ID in Stripe/external platform. User must not be able to update this.
id text primary key,
-- The email address the customer wants to use for invoicing
email text,
-- The active status of a customer
active boolean,
-- The billing provider the customer is using
provider text
);
grant select, insert, update, delete on table api.billing_customers to service_role;
grant select on table api.billing_customers to authenticated;
alter table
api.billing_customers
enable row level security;
/**
* Billing subscriptions
* This is a private table that contains a mapping of account IDs to your billing providers subscription IDs
*/
create table if not exists api.billing_subscriptions
(
-- Subscription ID from Stripe/external platform, e.g. sub_1234.
id text primary key,
tenant_id uuid references api.tenants (id) on delete cascade not null,
billing_customer_id text references api.billing_customers (id) on delete cascade not null,
-- The status of the customer's billing object, one of api.billing_status type.
status api.billing_status,
-- Set of key-value pairs, used to store additional information about the object in a structured format.
metadata jsonb,
-- ID of the price that created this subscription.
price_id text,
plan_name text,
-- Quantity multiplied by the unit amount of the price creates the amount of the subscription. Can be used to charge multiple seats.
quantity integer,
-- If true the subscription has been canceled by the user and will be deleted at the end of the billing period.
cancel_at_period_end boolean,
-- Time at which the subscription was created.
created timestamp with time zone default timezone('utc' :: text, now()) not null,
-- Start of the current period that the subscription has been invoiced for.
current_period_start timestamp with time zone default timezone('utc' :: text, now()) not null,
-- End of the current period that the subscription has been invoiced for. At the end of this period, a new invoice will be created.
current_period_end timestamp with time zone default timezone('utc' :: text, now()) not null,
-- If the subscription has ended, the timestamp of the date the subscription ended.
ended_at timestamp with time zone default timezone('utc' :: text, now()),
-- A date in the future at which the subscription will automatically get canceled.
cancel_at timestamp with time zone default timezone('utc' :: text, now()),
-- If the subscription has been canceled, the date of that cancellation. If the subscription was canceled with `cancel_at_period_end`, `canceled_at` will still reflect the date of the initial cancellation request, not the end of the subscription period when the subscription is automatically moved to a canceled state.
canceled_at timestamp with time zone default timezone('utc' :: text, now()),
-- If the subscription has a trial, the beginning of that trial.
trial_start timestamp with time zone default timezone('utc' :: text, now()),
-- If the subscription has a trial, the end of that trial.
trial_end timestamp with time zone default timezone('utc' :: text, now()),
provider text
);
grant select, insert, update, delete on table api.billing_subscriptions to service_role;
grant select on table api.billing_subscriptions to authenticated;
alter table
api.billing_subscriptions
enable row level security;
/**
* Allow service accounts to upsert the billing data for an account
*/
create or replace function public.service_role_upsert_customer_subscription(_tenant_id uuid,
_customer jsonb default null,
_subscription jsonb default null)
returns void
set search_path = ''
as
$$
begin
-- if the customer is not null, upsert the data into billing_customers, only upsert fields that are present in the jsonb object
if _customer is not null then
insert into api.billing_customers (id, tenant_id, email, provider)
values (_customer ->> 'id', service_role_upsert_customer_subscription._tenant_id, _customer ->> 'billing_email',
(_customer ->> 'provider'))
on conflict (id) do update
set email = _customer ->> 'billing_email';
end if;
-- if the subscription is not null, upsert the data into billing_subscriptions, only upsert fields that are present in the jsonb object
if _subscription is not null then
insert into api.billing_subscriptions (id, tenant_id, billing_customer_id, status, metadata, price_id,
quantity, cancel_at_period_end, created, current_period_start,
current_period_end, ended_at, cancel_at, canceled_at, trial_start,
trial_end, plan_name, provider)
values (_subscription ->> 'id', service_role_upsert_customer_subscription._tenant_id,
_subscription ->> 'billing_customer_id', (_subscription ->> 'status')::api.billing_status,
_subscription -> 'metadata',
_subscription ->> 'price_id', (_subscription ->> 'quantity')::int,
(_subscription ->> 'cancel_at_period_end')::boolean,
(_subscription ->> 'created')::timestamptz, (_subscription ->> 'current_period_start')::timestamptz,
(_subscription ->> 'current_period_end')::timestamptz, (_subscription ->> 'ended_at')::timestamptz,
(_subscription ->> 'cancel_at')::timestamptz,
(_subscription ->> 'canceled_at')::timestamptz, (_subscription ->> 'trial_start')::timestamptz,
(_subscription ->> 'trial_end')::timestamptz,
_subscription ->> 'plan_name', (_subscription ->> 'provider'))
on conflict (id) do update
set billing_customer_id = _subscription ->> 'billing_customer_id',
status = (_subscription ->> 'status')::api.billing_status,
metadata = _subscription -> 'metadata',
price_id = _subscription ->> 'price_id',
quantity = (_subscription ->> 'quantity')::int,
cancel_at_period_end = (_subscription ->> 'cancel_at_period_end')::boolean,
current_period_start = (_subscription ->> 'current_period_start')::timestamptz,
current_period_end = (_subscription ->> 'current_period_end')::timestamptz,
ended_at = (_subscription ->> 'ended_at')::timestamptz,
cancel_at = (_subscription ->> 'cancel_at')::timestamptz,
canceled_at = (_subscription ->> 'canceled_at')::timestamptz,
trial_start = (_subscription ->> 'trial_start')::timestamptz,
trial_end = (_subscription ->> 'trial_end')::timestamptz,
plan_name = _subscription ->> 'plan_name';
end if;
end;
$$ language plpgsql;
grant execute on function public.service_role_upsert_customer_subscription(uuid, jsonb, jsonb) to service_role;
-- billing
create policy "billing can be read by members" on api.billing_customers
for select
to authenticated
using (api.has_permission_in_tenant(id) = true);
create policy "billing subscriptions can be read by members" on api.billing_subscriptions
for select
to authenticated
using (api.has_permission_in_tenant(id) = true);