Subscription billing

CREATE TYPE billing_interval AS ENUM ('month', 'year');

CREATE TYPE sub_state AS ENUM ('trialing', 'active', 'past_due', 'cancelled');

CREATE TYPE invoice_state AS ENUM ('draft', 'open', 'paid', 'void', 'uncollectible');

CREATE TABLE customers (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    email text NOT NULL UNIQUE,
    name text,
    country text
);

CREATE TABLE plans (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    name text NOT NULL UNIQUE,
    active boolean DEFAULT true NOT NULL
);

CREATE TABLE prices (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    plan_id uuid NOT NULL,
    currency char(3) NOT NULL,
    interval billing_interval NOT NULL,
    unit_amount bigint NOT NULL,
    UNIQUE (plan_id, currency, interval),
    CHECK (unit_amount >= 0)
);

CREATE TABLE subscriptions (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    customer_id uuid NOT NULL,
    price_id uuid NOT NULL,
    state sub_state DEFAULT 'active' NOT NULL,
    quantity int DEFAULT 1 NOT NULL,
    current_period_end timestamptz NOT NULL,
    cancel_at_period_end boolean DEFAULT false NOT NULL
);
CREATE INDEX subscriptions_customer_id_idx ON subscriptions (customer_id);
CREATE INDEX subscriptions_price_id_idx ON subscriptions (price_id);

CREATE TABLE invoices (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    customer_id uuid NOT NULL,
    subscription_id uuid,
    number text NOT NULL UNIQUE,
    state invoice_state DEFAULT 'draft' NOT NULL,
    total bigint DEFAULT 0 NOT NULL,
    issued_at timestamptz,
    due_at timestamptz
);
CREATE INDEX invoices_customer_idx ON invoices (customer_id, state);
CREATE INDEX invoices_subscription_id_idx ON invoices (subscription_id);

CREATE TABLE invoice_lines (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    invoice_id uuid NOT NULL,
    description text NOT NULL,
    quantity int DEFAULT 1 NOT NULL,
    amount bigint NOT NULL
);
CREATE INDEX invoice_lines_invoice_id_idx ON invoice_lines (invoice_id);

CREATE TABLE payments (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    invoice_id uuid NOT NULL,
    amount bigint NOT NULL,
    gateway text NOT NULL,
    gateway_reference text,
    paid_at timestamptz DEFAULT now() NOT NULL
);
CREATE INDEX payments_invoice_id_idx ON payments (invoice_id);

ALTER TABLE prices ADD FOREIGN KEY (plan_id) REFERENCES plans (id);

ALTER TABLE subscriptions ADD FOREIGN KEY (customer_id) REFERENCES customers (id);

ALTER TABLE subscriptions ADD FOREIGN KEY (price_id) REFERENCES prices (id);

ALTER TABLE invoices ADD FOREIGN KEY (customer_id) REFERENCES customers (id);

ALTER TABLE invoices ADD FOREIGN KEY (subscription_id) REFERENCES subscriptions (id) ON DELETE SET NULL;

ALTER TABLE invoice_lines ADD FOREIGN KEY (invoice_id) REFERENCES invoices (id) ON DELETE CASCADE;

ALTER TABLE payments ADD FOREIGN KEY (invoice_id) REFERENCES invoices (id);