Skip to main content

​
Overview

BoxBilling uses 40 SQLAlchemy models organized into logical domains. All entities (except the legacy Item) use UUID primary keys and include audit timestamps (created_at, updated_at). Monetary amounts use Numeric(12,4) for precision.

​
Entity relationship diagram

Organization
├── Customer
│   ├── Subscription ──→ Plan ──→ Charge ──→ BillableMetric
│   │   ├── Invoice ──→ Fee
│   │   │   ├── Payment
│   │   │   ├── InvoiceSettlement
│   │   │   └── CreditNote ──→ CreditNoteItem
│   │   ├── DailyUsage
│   │   └── AppliedUsageThreshold
│   ├── Wallet ──→ WalletTransaction
│   ├── AppliedCoupon ──→ Coupon
│   ├── AppliedAddOn ──→ AddOn
│   └── PaymentRequest ──→ PaymentRequestInvoice
├── Tax ──→ AppliedTax (polymorphic)
├── UsageThreshold
├── Commitment
├── Integration ──→ IntegrationCustomer, IntegrationMapping
├── DunningCampaign ──→ DunningCampaignThreshold
├── WebhookEndpoint ──→ Webhook
├── DataExport
├── Event
└── ApiKey

​
Organization & accounts

​
Organization

The root tenant entity. All resources are scoped to an organization.
ColumnTypeDescription
idUUIDPrimary key
nameString(255)Organization name
default_currencyString(3)Default currency (e.g. USD)
timezoneString(50)Default timezone
net_payment_termIntegerDays until invoice due (default: 30)
invoice_grace_periodIntegerGrace period days (default: 0)
document_number_prefixString(20)Invoice number prefix
email, legal_name, address_*StringOrganization details

​
Customer

ColumnTypeDescription
idUUIDPrimary key
organization_idUUIDFK → organizations
external_idString(255)Unique external identifier
nameString(255)Customer name
emailString(255)Contact email
currencyString(3)Billing currency
billing_metadataJSONCustom key-value metadata
net_payment_termIntegerOverride org default

​
ApiKey

ColumnTypeDescription
idUUIDPrimary key
organization_idUUIDFK → organizations
key_hashString(255)SHA-256 hash of the API key
key_prefixString(20)Display prefix (e.g. bxb_live_abc...)
statusString(50)active or revoked
expires_atDateTimeOptional expiration
last_used_atDateTimeLast successful auth

​
Subscription & billing

​
Plan

ColumnTypeDescription
idUUIDPrimary key
codeString(255)Unique plan code
nameString(255)Display name
intervalString(20)weekly, monthly, quarterly, yearly
amount_centsIntegerBase subscription fee
currencyString(3)Plan currency
trial_period_daysIntegerDefault trial length

​
Subscription

ColumnTypeDescription
idUUIDPrimary key
external_idString(255)Unique external identifier
customer_idUUIDFK → customers
plan_idUUIDFK → plans
statusString(20)pending, active, canceled, terminated
billing_timeString(20)calendar or anniversary
pay_in_advanceBooleanBill at start of period
trial_period_daysIntegerTrial duration
previous_plan_idUUIDPending downgrade target
started_atDateTimeActivation timestamp
ending_atDateTimeTermination timestamp
canceled_atDateTimeCancellation timestamp

​
Commitment

Minimum spend commitments attached to plans.
ColumnTypeDescription
plan_idUUIDFK → plans
commitment_typeString(50)minimum_commitment
amount_centsNumeric(12,4)Minimum amount

​
Usage & metrics

​
BillableMetric

ColumnTypeDescription
codeString(255)Unique metric code
nameString(255)Display name
aggregation_typeString(20)count, sum, max, unique_count, weighted_sum, latest, custom
field_nameString(255)Event property to aggregate
recurringBooleanPersists across billing periods
expressionTextCustom aggregation expression
rounding_functionString(10)round, ceil, floor

​
BillableMetricFilter

Filters that segment events by property values.
ColumnTypeDescription
billable_metric_idUUIDFK → billable_metrics
keyString(255)Property key to filter on
valuesJSONAllowed values
Constraint: Unique on (billable_metric_id, key)

​
Event

ColumnTypeDescription
transaction_idString(255)Unique idempotency key
external_customer_idString(255)Customer reference
codeString(255)Billable metric code
timestampDateTimeEvent timestamp
propertiesJSONEvent-specific data
Index: Compound on (external_customer_id, code, timestamp)

​
DailyUsage

Pre-aggregated daily usage for performance optimization.
ColumnTypeDescription
subscription_idUUIDFK → subscriptions
billable_metric_idUUIDFK → billable_metrics
usage_dateDateAggregation date
usage_valueNumeric(12,4)Aggregated value
events_countIntegerEvent count

​
Charges & pricing

​
Charge

Links a billable metric to a plan with a pricing model.
ColumnTypeDescription
plan_idUUIDFK → plans (CASCADE)
billable_metric_idUUIDFK → billable_metrics
charge_modelString(30)Pricing model type
propertiesJSONModel-specific config
Charge models: standard, graduated, volume, package, percentage, graduated_percentage, custom, dynamic

​
ChargeFilter / ChargeFilterValue

Override charge properties for specific filter values (e.g., different pricing per region).

​
Invoicing

​
Invoice

ColumnTypeDescription
invoice_numberString(50)Unique invoice number
customer_idUUIDFK → customers
subscription_idUUIDFK → subscriptions (nullable)
statusString(20)draft, finalized, paid, voided
invoice_typeString(30)subscription, add_on, credit, one_off, progressive_billing
subtotalNumeric(12,4)Pre-tax amount
tax_amountNumeric(12,4)Total tax
totalNumeric(12,4)Final amount
prepaid_credit_amountNumeric(12,4)Wallet credits applied
coupons_amount_centsNumeric(12,4)Coupon discounts applied
line_itemsJSONLine item details

​
Fee

Individual charge line items on an invoice.
ColumnTypeDescription
invoice_idUUIDFK → invoices
charge_idUUIDFK → charges (nullable)
customer_idUUIDFK → customers
fee_typeString(20)charge, subscription, add_on, credit, commitment
amount_centsNumeric(12,4)Fee amount
unitsNumeric(12,4)Usage units
events_countIntegerEvents counted
payment_statusString(20)pending, succeeded, failed, refunded

​
InvoiceSettlement

Tracks how invoices are paid (multiple settlement types per invoice).
ColumnTypeDescription
invoice_idUUIDFK → invoices
settlement_typeString(20)payment, credit_note, wallet_credit
source_idUUIDReference to payment/credit note/wallet
amount_centsNumeric(12,4)Amount settled

​
CreditNote / CreditNoteItem

ColumnTypeDescription
numberString(50)Unique (e.g. CN-20250214-0001)
invoice_idUUIDFK → invoices
statusString(20)draft, finalized
reasonString(30)duplicated_charge, order_change, etc.
credit_amount_centsNumeric(12,4)Available credit
balance_amount_centsNumeric(12,4)Remaining credit

​
Payments

​
Payment

ColumnTypeDescription
invoice_idUUIDFK → invoices
customer_idUUIDFK → customers
amountNumeric(12,4)Payment amount
statusString(20)pending, processing, succeeded, failed, refunded, canceled
providerString(50)stripe, manual, ucp, gocardless, adyen
provider_payment_idString(255)External payment reference

​
PaymentRequest / PaymentRequestInvoice

Manual payment requests linking multiple invoices for dunning campaigns.

​
Wallets

​
Wallet

ColumnTypeDescription
customer_idUUIDFK → customers
statusString(20)active, terminated
balance_centsNumeric(12,4)Available balance
credits_balanceNumeric(12,4)Credit units
rate_amountNumeric(12,4)Credits-to-cents conversion rate
priorityIntegerConsumption priority (lower = first)
expiration_atDateTimeOptional expiration

​
WalletTransaction

ColumnTypeDescription
wallet_idUUIDFK → wallets
transaction_typeString(20)inbound, outbound
sourceString(20)manual, interval, threshold
amountNumeric(12,4)Transaction amount
invoice_idUUIDFK → invoices (for consumption)

​
Discounts

​
Coupon

ColumnTypeDescription
codeString(255)Unique coupon code
coupon_typeString(20)fixed_amount, percentage
frequencyString(20)once, recurring, forever
reusableBooleanCan be applied to multiple customers
statusString(20)active, terminated

​
AddOn

One-time charges applied to customers.
ColumnTypeDescription
codeString(255)Unique add-on code
amount_centsNumeric(12,4)Default price

​
Taxes

​
Tax

ColumnTypeDescription
codeString(255)Unique tax code
rateNumeric(5,4)Tax rate (e.g. 0.0875 for 8.75%)
applied_to_organizationBooleanDefault org-wide tax

​
AppliedTax

Polymorphic association — can be applied to any entity type.
ColumnTypeDescription
tax_idUUIDFK → taxes
taxable_typeString(50)Entity type (e.g. customer, plan)
taxable_idUUIDEntity ID
tax_amount_centsNumeric(12,4)Calculated amount

​
Integrations

​
Integration

ColumnTypeDescription
integration_typeString(30)payment_provider, accounting, crm, tax
provider_typeString(50)stripe, netsuite, xero, hubspot, salesforce, etc.
settingsJSONProvider-specific configuration
statusString(20)active, inactive, error
Constraint: Unique on (organization_id, provider_type) — one integration per provider per org.

​
Webhooks

​
WebhookEndpoint

ColumnTypeDescription
urlString(2048)Delivery URL
signature_algoString(50)Signature algorithm (default: hmac)
statusString(50)active or inactive

​
Webhook

ColumnTypeDescription
webhook_endpoint_idUUIDFK → webhook_endpoints
webhook_typeString(100)Event type (e.g. invoice.finalized)
payloadJSONEvent payload
statusString(50)pending, succeeded, failed
retriesIntegerDelivery attempts
http_statusIntegerLast response status code

​
Design patterns

  • UUID primary keys on all models for distributed ID generation
  • Numeric(12,4) for monetary amounts — 4 decimal places of precision
  • Organization isolation via organization_id foreign key on all tenant-scoped models
  • Polymorphic associations for AppliedTax and IntegrationMapping
  • Soft deletes via status columns rather than row deletion
  • JSON columns for flexible storage (properties, settings, metadata, line_items)