Data Models

Overview

BoxBilling uses 50+ SQLAlchemy models organized into logical domains. All entities use UUID primary keys (stored as String(36)) and include audit timestamps (created_at, updated_at) with timezone-aware DateTime. Monetary amounts use Numeric(12,4) for precision, with column names suffixed _cents. All tenant-scoped models include an organization_id foreign key for multi-tenancy isolation.

Entity relationship diagram

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

Organization & accounts

Organization

The root tenant entity. All resources are scoped to an organization.

Column	Type	Description
`id`	UUID	Primary key
`name`	String(255)	Organization name
`default_currency`	String(3)	Default currency (default: `USD`)
`timezone`	String(50)	Default timezone (default: `UTC`)
`hmac_key`	String(255)	Key used for webhook signature signing
`logo_url`	String(2048)	Organization logo URL
`portal_accent_color`	String(7)	Hex color for customer portal
`portal_welcome_message`	String(500)	Portal welcome text

BillingEntity

Supports multi-entity billing — each entity can have its own invoice numbering, address, and payment terms.

Column	Type	Description
`id`	UUID	Primary key
`organization_id`	UUID	FK → organizations
`code`	String(100)	Unique entity code
`name`	String(255)	Display name
`legal_name`	String(255)	Legal entity name
`address_line1`, `address_line2`	String(255)	Street address
`city`, `state`, `country`, `zip_code`	String	Location details (`country` is ISO 3166 alpha-2)
`tax_id`	String(100)	Tax identification number
`email`	String(255)	Contact email
`currency`	String(3)	Default currency (default: `USD`)
`timezone`	String(50)	Timezone (default: `UTC`)
`document_locale`	String(10)	Invoice locale (default: `en`)
`invoice_prefix`	String(20)	Invoice number prefix
`next_invoice_number`	Integer	Next sequential invoice number (default: 1)
`invoice_grace_period`	Integer	Grace period days (default: 0)
`net_payment_term`	Integer	Days until invoice due (default: 30)
`invoice_footer`	String(1024)	Footer text on invoices
`is_default`	Boolean	Whether this is the default billing entity

Customer

Column	Type	Description
`id`	UUID	Primary key
`organization_id`	UUID	FK → organizations
`external_id`	String(255)	Unique external identifier
`name`	String(255)	Customer name
`email`	String(255)	Contact email
`currency`	String(3)	Billing currency (default: `USD`)
`timezone`	String(50)	Customer timezone (default: `UTC`)
`billing_metadata`	JSON	Custom key-value metadata (default: `{}`)
`invoice_grace_period`	Integer	Override grace period (default: 0)
`net_payment_term`	Integer	Override payment term (default: 30)
`billing_entity_id`	UUID	FK → billing_entities (optional)

ApiKey

Column	Type	Description
`id`	UUID	Primary key
`organization_id`	UUID	FK → organizations
`key_hash`	String(255)	SHA-256 hash of the API key (unique)
`key_prefix`	String(20)	Display prefix (e.g. `bxb_live_abc...`)
`name`	String(255)	Human-readable key name
`status`	String(50)	`active` or `revoked`
`expires_at`	DateTime	Optional expiration
`last_used_at`	DateTime	Last successful auth

Subscription & billing

Plan

Column	Type	Description
`id`	UUID	Primary key
`organization_id`	UUID	FK → organizations
`code`	String(255)	Unique plan code
`name`	String(255)	Display name
`description`	Text	Plan description
`interval`	String(20)	`weekly`, `monthly`, `quarterly`, `yearly`
`amount_cents`	Integer	Base subscription fee (default: 0)
`currency`	String(3)	Plan currency (default: `USD`)
`trial_period_days`	Integer	Default trial length (default: 0)

Subscription

Column	Type	Description
`id`	UUID	Primary key
`organization_id`	UUID	FK → organizations
`external_id`	String(255)	Unique external identifier
`customer_id`	UUID	FK → customers
`plan_id`	UUID	FK → plans
`status`	String(20)	`pending`, `active`, `paused`, `canceled`, `terminated`
`billing_time`	String(20)	`calendar` or `anniversary`
`pay_in_advance`	Boolean	Bill at start of period (default: false)
`trial_period_days`	Integer	Trial duration (default: 0)
`trial_ended_at`	DateTime	When trial ended
`subscription_at`	DateTime	Subscription anchor date
`previous_plan_id`	UUID	FK → plans — pending downgrade target
`downgraded_at`	DateTime	When downgrade was scheduled
`billing_entity_id`	UUID	FK → billing_entities (optional)
`on_termination_action`	String(30)	`generate_invoice`, `generate_credit_note`, `skip` (default: `generate_invoice`)
`started_at`	DateTime	Activation timestamp
`ending_at`	DateTime	Scheduled termination timestamp
`canceled_at`	DateTime	Cancellation timestamp
`paused_at`	DateTime	When subscription was paused
`resumed_at`	DateTime	When subscription was resumed

Commitment

Minimum spend commitments attached to plans.

Column	Type	Description
`id`	UUID	Primary key
`organization_id`	UUID	FK → organizations
`plan_id`	UUID	FK → plans
`commitment_type`	String(50)	`minimum_commitment`
`amount_cents`	Numeric(12,4)	Minimum amount
`invoice_display_name`	String(255)	Custom display name on invoices

Feature

Feature definitions for entitlement-based access control.

Column	Type	Description
`id`	UUID	Primary key
`organization_id`	UUID	FK → organizations
`code`	String(100)	Unique feature code (unique per org)
`name`	String(255)	Display name
`description`	String	Feature description
`feature_type`	String(20)	`boolean`, `quantity`, `custom`

Constraint: Unique on (organization_id, code)

Entitlement

Links features to plans, defining what access each plan grants.

Column	Type	Description
`id`	UUID	Primary key
`organization_id`	UUID	FK → organizations
`plan_id`	UUID	FK → plans
`feature_id`	UUID	FK → features
`value`	String	Entitlement value (e.g. `true`, `100`)

Constraint: Unique on (plan_id, feature_id)

Usage & metrics

BillableMetric

Column	Type	Description
`id`	UUID	Primary key
`organization_id`	UUID	FK → organizations
`code`	String(255)	Unique metric code
`name`	String(255)	Display name
`description`	Text	Metric description
`aggregation_type`	String(20)	`count`, `sum`, `max`, `unique_count`, `weighted_sum`, `latest`, `custom`
`field_name`	String(255)	Event property to aggregate (for `sum`, `max`, `unique_count`)
`recurring`	Boolean	Persists across billing periods (default: false)
`expression`	Text	Custom aggregation expression
`rounding_function`	String(10)	`round`, `ceil`, `floor`
`rounding_precision`	Integer	Decimal places for rounding

BillableMetricFilter

Filters that segment events by property values.

Column	Type	Description
`id`	UUID	Primary key
`billable_metric_id`	UUID	FK → billable_metrics (CASCADE)
`key`	String(255)	Property key to filter on
`values`	JSON	Allowed values (default: `[]`)

Constraint: Unique on (billable_metric_id, key)

Event

Column	Type	Description
`id`	UUID	Primary key
`organization_id`	UUID	FK → organizations
`transaction_id`	String(255)	Unique idempotency key
`external_customer_id`	String(255)	Customer reference
`code`	String(255)	Billable metric code
`timestamp`	DateTime	Event timestamp
`properties`	JSON	Event-specific data (default: `{}`)

Indexes: external_customer_id, code, timestamp, and composite on (external_customer_id, code, timestamp)

DailyUsage

Pre-aggregated daily usage for performance optimization.

Column	Type	Description
`id`	UUID	Primary key
`subscription_id`	UUID	FK → subscriptions
`billable_metric_id`	UUID	FK → billable_metrics
`external_customer_id`	String(255)	Customer reference
`usage_date`	Date	Aggregation date
`usage_value`	Numeric(12,4)	Aggregated value (default: 0)
`events_count`	Integer	Event count (default: 0)

Constraint: Unique on (subscription_id, billable_metric_id, usage_date)

UsageThreshold

Triggers progressive billing invoices when cumulative usage crosses a threshold.

Column	Type	Description
`id`	UUID	Primary key
`organization_id`	UUID	FK → organizations
`plan_id`	UUID	FK → plans (exactly one of plan_id or subscription_id required)
`subscription_id`	UUID	FK → subscriptions
`amount_cents`	Numeric(12,4)	Threshold amount
`currency`	String(3)	Currency (default: `USD`)
`recurring`	Boolean	Reset each billing period (default: false)
`threshold_display_name`	String(255)	Display name on invoices

Constraint: Exactly one of plan_id or subscription_id must be set.

UsageAlert

Monitors usage against a threshold and triggers notifications.

Column	Type	Description
`id`	UUID	Primary key
`organization_id`	UUID	FK → organizations
`subscription_id`	UUID	FK → subscriptions
`billable_metric_id`	UUID	FK → billable_metrics
`threshold_value`	Numeric(12,4)	Alert threshold
`recurring`	Boolean	Re-trigger each period (default: false)
`name`	String(255)	Alert name
`times_triggered`	Integer	Total trigger count (default: 0)
`triggered_at`	DateTime	Last trigger timestamp

UsageAlertTrigger

Records of individual alert trigger events.

Column	Type	Description
`id`	UUID	Primary key
`usage_alert_id`	UUID	FK → usage_alerts (CASCADE)
`current_usage`	Numeric(16,4)	Usage at trigger time
`threshold_value`	Numeric(12,4)	Threshold that was crossed
`metric_code`	String(255)	Billable metric code
`triggered_at`	DateTime	When triggered

AppliedUsageThreshold

Tracks when a subscription crosses a usage threshold, linking to the generated invoice.

Column	Type	Description
`id`	UUID	Primary key
`organization_id`	UUID	FK → organizations
`usage_threshold_id`	UUID	FK → usage_thresholds
`subscription_id`	UUID	FK → subscriptions
`invoice_id`	UUID	FK → invoices (optional)
`crossed_at`	DateTime	When the threshold was crossed
`lifetime_usage_amount_cents`	Numeric(12,4)	Cumulative usage at crossing time

Charges & pricing

Charge

Links a billable metric to a plan with a pricing model.

Column	Type	Description
`id`	UUID	Primary key
`organization_id`	UUID	FK → organizations
`plan_id`	UUID	FK → plans (CASCADE)
`billable_metric_id`	UUID	FK → billable_metrics
`charge_model`	String(30)	Pricing model type
`properties`	JSON	Model-specific config (default: `{}`)

Charge models: standard, graduated, volume, package, percentage, graduated_percentage, custom, dynamic

ChargeFilter

Override charge properties for specific metric filter combinations.

Column	Type	Description
`id`	UUID	Primary key
`charge_id`	UUID	FK → charges (CASCADE)
`properties`	JSON	Override pricing config (default: `{}`)
`invoice_display_name`	String(255)	Custom line item name

ChargeFilterValue

Maps a charge filter to a specific billable metric filter value.

Column	Type	Description
`id`	UUID	Primary key
`charge_filter_id`	UUID	FK → charge_filters (CASCADE)
`billable_metric_filter_id`	UUID	FK → billable_metric_filters (CASCADE)
`value`	String(255)	Selected filter value

Constraint: Unique on (charge_filter_id, billable_metric_filter_id)

Invoicing

Invoice

Column	Type	Description
`id`	UUID	Primary key
`organization_id`	UUID	FK → organizations
`invoice_number`	String(50)	Unique invoice number
`customer_id`	UUID	FK → customers
`subscription_id`	UUID	FK → subscriptions (nullable)
`billing_entity_id`	UUID	FK → billing_entities (nullable)
`status`	String(20)	`draft`, `finalized`, `paid`, `voided`
`invoice_type`	String(30)	`subscription`, `add_on`, `credit`, `one_off`, `progressive_billing`
`billing_period_start`	DateTime	Start of billing period
`billing_period_end`	DateTime	End of billing period
`subtotal_cents`	Numeric(12,4)	Pre-tax amount (default: 0)
`tax_amount_cents`	Numeric(12,4)	Total tax (default: 0)
`total_cents`	Numeric(12,4)	Final amount (default: 0)
`prepaid_credit_amount_cents`	Numeric(12,4)	Wallet credits applied (default: 0)
`coupons_amount_cents`	Numeric(12,4)	Coupon discounts applied (default: 0)
`progressive_billing_credit_amount_cents`	Numeric(12,4)	Progressive billing credits (default: 0)
`currency`	String(3)	Invoice currency (default: `USD`)
`line_items`	JSON	Line item details (default: `[]`)
`due_date`	DateTime	Payment due date
`issued_at`	DateTime	When invoice was issued
`paid_at`	DateTime	When invoice was paid

Fee

Individual charge line items on an invoice.

Column	Type	Description
`id`	UUID	Primary key
`organization_id`	UUID	FK → organizations
`invoice_id`	UUID	FK → invoices (nullable)
`charge_id`	UUID	FK → charges (nullable)
`subscription_id`	UUID	FK → subscriptions (nullable)
`customer_id`	UUID	FK → customers
`commitment_id`	UUID	FK → commitments (nullable)
`fee_type`	String(20)	`charge`, `subscription`, `add_on`, `credit`, `commitment`
`amount_cents`	Numeric(12,4)	Fee amount (default: 0)
`taxes_amount_cents`	Numeric(12,4)	Tax on this fee (default: 0)
`total_amount_cents`	Numeric(12,4)	Amount + taxes (default: 0)
`units`	Numeric(12,4)	Usage units (default: 0)
`events_count`	Integer	Events counted (default: 0)
`unit_amount_cents`	Numeric(12,4)	Per-unit price (default: 0)
`payment_status`	String(20)	`pending`, `succeeded`, `failed`, `refunded`
`description`	String(500)	Fee description
`metric_code`	String(255)	Associated billable metric code
`properties`	JSON	Additional fee properties (default: `{}`)

InvoiceSettlement

Tracks how invoices are paid (multiple settlement types per invoice).

Column	Type	Description
`id`	UUID	Primary key
`invoice_id`	UUID	FK → invoices
`settlement_type`	String(20)	`payment`, `credit_note`, `wallet_credit`
`source_id`	UUID	Polymorphic reference to payment/credit note/wallet
`amount_cents`	Numeric(12,4)	Amount settled

Index: Composite on (settlement_type, source_id)

CreditNote

Column	Type	Description
`id`	UUID	Primary key
`organization_id`	UUID	FK → organizations
`number`	String(50)	Unique (e.g. `CN-20250214-0001`)
`invoice_id`	UUID	FK → invoices
`customer_id`	UUID	FK → customers
`credit_note_type`	String(20)	`credit`, `refund`, `offset`
`status`	String(20)	`draft`, `finalized`
`credit_status`	String(20)	`available`, `consumed`, `voided`
`refund_status`	String(20)	`pending`, `succeeded`, `failed`
`reason`	String(30)	`duplicated_charge`, `product_unsatisfactory`, `order_change`, `order_cancellation`, `fraudulent_charge`, `other`
`description`	Text	Additional description
`credit_amount_cents`	Numeric(12,4)	Available credit (default: 0)
`refund_amount_cents`	Numeric(12,4)	Refund amount (default: 0)
`balance_amount_cents`	Numeric(12,4)	Remaining credit (default: 0)
`total_amount_cents`	Numeric(12,4)	Total credit note amount (default: 0)
`taxes_amount_cents`	Numeric(12,4)	Tax amount (default: 0)
`currency`	String(3)	Currency
`issued_at`	DateTime	When credit note was issued
`voided_at`	DateTime	When credit note was voided

CreditNoteItem

Column	Type	Description
`id`	UUID	Primary key
`credit_note_id`	UUID	FK → credit_notes
`fee_id`	UUID	FK → fees
`amount_cents`	Numeric(12,4)	Credited amount

Payments

Payment

Column	Type	Description
`id`	UUID	Primary key
`organization_id`	UUID	FK → organizations
`invoice_id`	UUID	FK → invoices
`customer_id`	UUID	FK → customers
`amount_cents`	Numeric(12,4)	Payment amount
`currency`	String(3)	Payment currency (default: `USD`)
`status`	String(20)	`pending`, `processing`, `succeeded`, `failed`, `refunded`, `canceled`
`provider`	String(50)	`stripe`, `manual`, `ucp`, `gocardless`, `adyen`
`provider_payment_id`	String(255)	External payment reference
`provider_checkout_id`	String(255)	Provider checkout session ID
`provider_checkout_url`	Text	Checkout redirect URL
`failure_reason`	Text	Reason for payment failure
`payment_metadata`	JSON	Provider-specific metadata (default: `{}`)
`completed_at`	DateTime	When payment completed

PaymentMethod

Stored payment methods for customers.

Column	Type	Description
`id`	UUID	Primary key
`organization_id`	UUID	FK → organizations
`customer_id`	UUID	FK → customers
`provider`	String(50)	`stripe`, `gocardless`, `adyen`
`provider_payment_method_id`	String(255)	External payment method ID
`type`	String(50)	`card`, `bank_account`, `direct_debit`
`is_default`	Boolean	Default payment method (default: false)
`details`	JSON	Method details (last4, brand, exp, etc.) (default: `{}`)

PaymentRequest

Manual payment requests linking multiple invoices, used by dunning campaigns.

Column	Type	Description
`id`	UUID	Primary key
`organization_id`	UUID	FK → organizations
`customer_id`	UUID	FK → customers
`dunning_campaign_id`	UUID	FK → dunning_campaigns (nullable)
`amount_cents`	Numeric(12,4)	Total request amount
`amount_currency`	String(3)	Currency
`payment_status`	String(20)	`pending` (default)
`payment_attempts`	Integer	Number of attempts (default: 0)
`ready_for_payment_processing`	Boolean	Ready to process (default: true)

PaymentRequestInvoice

Links payment requests to invoices.

Column	Type	Description
`id`	UUID	Primary key
`payment_request_id`	UUID	FK → payment_requests (CASCADE)
`invoice_id`	UUID	FK → invoices

Wallets

Wallet

Column	Type	Description
`id`	UUID	Primary key
`organization_id`	UUID	FK → organizations
`customer_id`	UUID	FK → customers
`name`	String(255)	Wallet name
`code`	String(255)	Wallet code
`status`	String(20)	`active`, `terminated`
`balance_cents`	Numeric(12,4)	Available balance (default: 0)
`credits_balance`	Numeric(12,4)	Credit units (default: 0)
`consumed_amount_cents`	Numeric(12,4)	Total consumed in cents (default: 0)
`consumed_credits`	Numeric(12,4)	Total consumed credits (default: 0)
`rate_amount`	Numeric(12,4)	Credits-to-cents conversion rate (default: 1)
`currency`	String(3)	Wallet currency (default: `USD`)
`expiration_at`	DateTime	Optional expiration
`priority`	Integer	Consumption priority — lower = first (default: 1)

Constraint: Unique on (customer_id, code)

WalletTransaction

Column	Type	Description
`id`	UUID	Primary key
`organization_id`	UUID	FK → organizations
`wallet_id`	UUID	FK → wallets
`customer_id`	UUID	FK → customers
`transaction_type`	String(20)	`inbound`, `outbound`
`transaction_status`	String(20)	`purchased`, `granted`, `voided`, `invoiced` (default: `granted`)
`source`	String(20)	`manual`, `interval`, `threshold`
`status`	String(20)	`pending`, `settled`, `failed` (default: `pending`)
`amount`	Numeric(12,4)	Transaction amount (default: 0)
`credit_amount`	Numeric(12,4)	Credit units (default: 0)
`invoice_id`	UUID	FK → invoices (for consumption)

Discounts

Coupon

Column	Type	Description
`id`	UUID	Primary key
`organization_id`	UUID	FK → organizations
`code`	String(255)	Unique coupon code
`name`	String(255)	Display name
`description`	Text	Coupon description
`coupon_type`	String(20)	`fixed_amount`, `percentage`
`amount_cents`	Numeric(12,4)	Discount amount (for `fixed_amount`)
`amount_currency`	String(3)	Currency (for `fixed_amount`)
`percentage_rate`	Numeric(5,2)	Discount percentage (for `percentage`)
`frequency`	String(20)	`once`, `recurring`, `forever`
`frequency_duration`	Integer	Number of periods (for `recurring`)
`reusable`	Boolean	Can be applied to multiple customers (default: true)
`expiration`	String(20)	`no_expiration`, `time_limit`
`expiration_at`	DateTime	Expiration date (for `time_limit`)
`status`	String(20)	`active`, `terminated`

AppliedCoupon

Tracks a coupon applied to a specific customer.

Column	Type	Description
`id`	UUID	Primary key
`organization_id`	UUID	FK → organizations
`coupon_id`	UUID	FK → coupons
`customer_id`	UUID	FK → customers
`amount_cents`	Numeric(12,4)	Override discount amount
`amount_currency`	String(3)	Override currency
`percentage_rate`	Numeric(5,2)	Override percentage
`frequency`	String(20)	Applied frequency
`frequency_duration`	Integer	Total periods
`frequency_duration_remaining`	Integer	Remaining periods
`status`	String(20)	`active`, `terminated`
`terminated_at`	DateTime	When terminated

AddOn

One-time charges applied to customers.

Column	Type	Description
`id`	UUID	Primary key
`organization_id`	UUID	FK → organizations
`code`	String(255)	Unique add-on code
`name`	String(255)	Display name
`description`	Text	Add-on description
`amount_cents`	Numeric(12,4)	Default price
`amount_currency`	String(3)	Currency (default: `USD`)
`invoice_display_name`	String(255)	Custom name on invoices

AppliedAddOn

Tracks an add-on applied to a customer.

Column	Type	Description
`id`	UUID	Primary key
`add_on_id`	UUID	FK → add_ons
`customer_id`	UUID	FK → customers
`amount_cents`	Numeric(12,4)	Charged amount
`amount_currency`	String(3)	Currency

Taxes

Tax

Column	Type	Description
`id`	UUID	Primary key
`organization_id`	UUID	FK → organizations
`code`	String(255)	Unique tax code
`name`	String(255)	Display name
`description`	Text	Tax description
`rate`	Numeric(5,4)	Tax rate (e.g. `0.0875` for 8.75%)
`category`	String(100)	Tax category
`applied_to_organization`	Boolean	Default org-wide tax (default: false)

AppliedTax

Polymorphic association — can be applied to any entity type.

Column	Type	Description
`id`	UUID	Primary key
`tax_id`	UUID	FK → taxes
`taxable_type`	String(50)	Entity type (e.g. `customer`, `plan`, `invoice`)
`taxable_id`	UUID	Entity ID
`tax_rate`	Numeric(5,4)	Snapshot of rate at apply time
`tax_amount_cents`	Numeric(12,4)	Calculated amount (default: 0)

Constraint: Unique on (tax_id, taxable_type, taxable_id) Index: Composite on (taxable_type, taxable_id)

Integrations

Integration

Column	Type	Description
`id`	UUID	Primary key
`organization_id`	UUID	FK → organizations
`integration_type`	String(30)	`payment_provider`, `accounting`, `crm`, `tax`
`provider_type`	String(50)	`stripe`, `gocardless`, `adyen`, `netsuite`, `xero`, `hubspot`, `salesforce`, `anrok`, `avalara`
`status`	String(20)	`active`, `inactive`, `error`
`settings`	JSON	Provider-specific configuration (default: `{}`)
`last_sync_at`	DateTime	Last successful sync
`error_details`	JSON	Error information (if status is `error`)

Constraint: Unique on (organization_id, provider_type) — one integration per provider per org.

IntegrationCustomer

Maps a BoxBilling customer to an external system customer.

Column	Type	Description
`id`	UUID	Primary key
`integration_id`	UUID	FK → integrations (CASCADE)
`customer_id`	UUID	FK → customers (CASCADE)
`external_customer_id`	String(255)	External system customer ID
`settings`	JSON	Customer-specific integration settings

Constraint: Unique on (integration_id, customer_id)

IntegrationMapping

Polymorphic mapping between BoxBilling entities and external system resources.

Column	Type	Description
`id`	UUID	Primary key
`integration_id`	UUID	FK → integrations (CASCADE)
`mappable_type`	String(50)	Entity type
`mappable_id`	UUID	Entity ID
`external_id`	String(255)	External resource ID
`external_data`	JSON	Cached external data
`last_synced_at`	DateTime	Last sync timestamp

Constraint: Unique on (integration_id, mappable_type, mappable_id)

IntegrationSyncHistory

Tracks individual sync operations for auditing and debugging.

Column	Type	Description
`id`	UUID	Primary key
`integration_id`	UUID	FK → integrations (CASCADE)
`resource_type`	String(50)	Type of resource synced
`resource_id`	UUID	BoxBilling resource ID
`external_id`	String(255)	External resource ID
`action`	String(50)	Sync action performed
`status`	String(20)	Sync result status
`error_message`	String(1000)	Error details
`details`	JSON	Additional sync data
`started_at`	DateTime	When sync started
`completed_at`	DateTime	When sync finished

Webhooks

WebhookEndpoint

Column	Type	Description
`id`	UUID	Primary key
`organization_id`	UUID	FK → organizations
`url`	String(2048)	Delivery URL
`signature_algo`	String(50)	Signature algorithm (default: `hmac`)
`status`	String(50)	`active` or `inactive`

Webhook

Column	Type	Description
`id`	UUID	Primary key
`webhook_endpoint_id`	UUID	FK → webhook_endpoints
`webhook_type`	String(100)	Event type (e.g. `invoice.finalized`)
`object_type`	String(50)	Type of the related object
`object_id`	UUID	ID of the related object
`payload`	JSON	Event payload
`status`	String(50)	`pending`, `succeeded`, `failed`
`retries`	Integer	Current retry count (default: 0)
`max_retries`	Integer	Maximum retry attempts (default: 5)
`last_retried_at`	DateTime	Last retry timestamp
`http_status`	Integer	Last response status code
`response`	Text	Last response body

WebhookDeliveryAttempt

Tracks individual delivery attempts for each webhook.

Column	Type	Description
`id`	UUID	Primary key
`webhook_id`	UUID	FK → webhooks (CASCADE)
`attempt_number`	Integer	Sequential attempt number
`http_status`	Integer	Response status code
`response_body`	Text	Response body
`success`	Boolean	Whether delivery succeeded (default: false)
`error_message`	String(1000)	Error details
`attempted_at`	DateTime	When attempt was made

Dunning

DunningCampaign

Automated payment recovery campaigns for failed or overdue invoices.

Column	Type	Description
`id`	UUID	Primary key
`organization_id`	UUID	FK → organizations
`code`	String(255)	Campaign code
`name`	String(255)	Display name
`description`	Text	Campaign description
`max_attempts`	Integer	Maximum retry attempts (default: 3)
`days_between_attempts`	Integer	Days between retries (default: 3)
`bcc_emails`	JSON	BCC recipients for dunning emails (default: `[]`)
`status`	String(20)	`active` or inactive

DunningCampaignThreshold

Currency-specific minimum amounts that trigger a dunning campaign.

Column	Type	Description
`id`	UUID	Primary key
`dunning_campaign_id`	UUID	FK → dunning_campaigns (CASCADE)
`currency`	String(3)	Currency code
`amount_cents`	Numeric(12,4)	Minimum amount to trigger dunning

System & operations

AuditLog

Tracks administrative actions for compliance and debugging.

Column	Type	Description
`id`	UUID	Primary key
`organization_id`	UUID	FK → organizations
`resource_type`	String(50)	Type of resource affected
`resource_id`	UUID	ID of resource affected
`action`	String(50)	Action performed (e.g. `create`, `update`, `delete`)
`changes`	JSON	Before/after diff (default: `{}`)
`actor_type`	String(50)	Who performed the action (e.g. `api_key`, `system`)
`actor_id`	String(255)	Actor identifier
`metadata`	JSON	Additional context

Indexes: resource_type, resource_id, action

DataExport

Bulk data export jobs.

Column	Type	Description
`id`	UUID	Primary key
`organization_id`	UUID	FK → organizations
`export_type`	String(30)	`invoices`, `customers`, `subscriptions`, `events`, `fees`, `credit_notes`, `audit_logs`
`status`	String(20)	`pending`, `processing`, `completed`, `failed`
`filters`	JSON	Export filter criteria
`file_path`	String(2048)	Path to exported file
`record_count`	Integer	Number of records exported
`progress`	Integer	Export progress percentage
`error_message`	Text	Error details (if failed)
`started_at`	DateTime	When export started
`completed_at`	DateTime	When export finished

IdempotencyRecord

Request deduplication to prevent duplicate operations.

Column	Type	Description
`id`	UUID	Primary key
`organization_id`	UUID	FK → organizations
`idempotency_key`	String(255)	Client-provided idempotency key
`request_method`	String(10)	HTTP method
`request_path`	String(500)	Request path
`response_status`	Integer	Cached response status
`response_body`	JSON	Cached response body

Constraint: Unique on (organization_id, idempotency_key)

Notification

Internal notifications for organization administrators.

Column	Type	Description
`id`	UUID	Primary key
`organization_id`	UUID	FK → organizations
`category`	String(50)	Notification category
`title`	String(255)	Notification title
`message`	String(1000)	Notification body
`resource_type`	String(50)	Related resource type
`resource_id`	UUID	Related resource ID
`is_read`	Boolean	Read status (default: false)

Design patterns

UUID primary keys — stored as String(36) for SQLite compatibility, generated with uuid.uuid4()
Numeric(12,4) for monetary amounts — 4 decimal places of precision, column names suffixed with _cents
Organization isolation via organization_id foreign key on all tenant-scoped models, with ondelete="RESTRICT"
Polymorphic associations for AppliedTax (taxable_type/taxable_id), IntegrationMapping (mappable_type/mappable_id), InvoiceSettlement (settlement_type/source_id), and AuditLog (resource_type/resource_id)
Status-based lifecycle via status columns rather than row deletion (e.g. active → terminated, draft → finalized → paid)
JSON columns for flexible storage (properties, settings, metadata, line_items, details)
Foreign key delete policies — RESTRICT by default; CASCADE for child/detail tables; SET NULL for optional references
Timezone-aware timestamps — all DateTime columns use timezone=True with server_default=func.now()