Commercial basis
Users, Customers, BillingProducts, PricePlans, PriceTiers and UsageMeters. Define customer identity, products, published price versions and meters.
- Users
- Customers
- BillingProducts
- PricePlans
- PriceTiers
- UsageMeters
Data model
Follow accepted subscription terms from metering through invoices and payment evidence.
23 tables · 63 relationships
How the model is organised
Accepted subscriptions
Subscriptions, SubscriptionChanges and PaymentMethods. Preserve accepted terms, effective changes and payment mandates.
- Subscriptions
- SubscriptionChanges
- PaymentMethods
Metering evidence
UsageEvents, UsageAggregates and IntegrationEvents. Keep usage events as append-only evidence and freeze each aggregate's exact accepted input set.
- UsageEvents
- UsageAggregates
- IntegrationEvents
Billing documents and corrections
BillingRuns, Invoices, InvoiceLines, CreditNotes, CreditLines, BillingAdjustments and BillingAdjustmentApplications. Prepare reviewed invoices, retain line calculations and link corrections to their original documents.
- BillingRuns
- Invoices
- InvoiceLines
- CreditNotes
- CreditLines
- BillingAdjustments
- BillingAdjustmentApplications
Collection and follow-up
PaymentAttempts, RefundRequests, DunningCases and DunningAttempts. Preserve the distinct outcomes of payment attempts, refunds and collection follow-up.
- PaymentAttempts
- RefundRequests
- DunningCases
- DunningAttempts
Keys and references
Use customer and seller-entity scope on every relationship. Preserve provider event plus payload digest, subscription interval, published plan version and issued document identity. Corrections link to their original source; they do not erase it.
Migration order
- Verify users, customers, products and accepted commercial history.
- Publish reviewed price and meter versions.
- Reconcile subscriptions, effective changes and payment mandates.
- Import source usage and outstanding issued invoice history without charging again.
- Reconcile credits, settled payments, unresolved instructions and dunning before enabling automation.
Tables
23 tables
UsersAuthenticated staff and customer principals; role and customer scope are server-controlled.master
| Field | Type | Required | Details |
|---|---|---|---|
UserNumber | text | yes | Stable identity |
PrincipalId | text | yes | Authenticated identity binding |
FullName | text | yes | |
Customer | reference | → Customers; Required for customer principals | |
Active | boolean | yes |
CustomersEntity-scoped billing accounts with reviewed terms and source identifiers.master
| Field | Type | Required | Details |
|---|---|---|---|
CustomerNumber | text | yes | Unique business account identity |
Name | text | yes | |
LegalEntity | text | yes | Seller entity |
BillingCurrency | text | yes | Immutable for active subscription terms |
BillingEmail | text | Verified billing destination | |
TaxProfile | longtext | Effective tax treatment from configured provider or reviewed rules; no inferred universal nexus | |
Status | select | yes | Active, Suspended, Closed |
BillingProductsProduct and service identity used by published price plans.master
| Field | Type | Required | Details |
|---|---|---|---|
ProductNumber | text | yes | |
Name | text | yes | |
Description | longtext | ||
Status | select | yes | Draft, Active, Retired |
PricePlansVersioned subscription terms; published price revisions are immutable.master
| Field | Type | Required | Details |
|---|---|---|---|
PlanNumber | text | yes | |
Product | reference | yes | → BillingProducts; |
Name | text | yes | |
Version | number | yes | Unique plan version |
Currency | text | yes | |
Interval | select | yes | Monthly, Quarterly, Annual |
PricingMode | select | yes | Flat, PerUnit, Graduated, Volume |
BaseAmount | decimal | yes | Nonnegative fixed recurring charge |
IncludedQuantity | decimal | yes | Nonnegative included metered quantity per billing interval |
UnitPrice | decimal | Used for PerUnit mode only | |
ProrationPolicy | select | yes | None, ActualSeconds |
UsageMeter | reference | → UsageMeters; Required for metered plans | |
PreparedBy | reference | yes | → Users; |
ApprovedBy | reference | → Users; Independent Billing Manager | |
EffectiveFrom | date | yes | |
Status | select | yes | Draft, Submitted, Published, Retired |
QuantityBasis | select | yes | Contracted, Metered |
PriceTiersExplicit contiguous usage bands inside one plan revision.line
| Field | Type | Required | Details |
|---|---|---|---|
PricePlan | reference | yes | → PricePlans; |
Sequence | number | yes | |
LowerBound | decimal | yes | Inclusive nonnegative billable-quantity bound |
UpperBound | decimal | Exclusive upper bound; empty only for final open-ended band | |
UnitPrice | decimal | yes | Nonnegative price per billable unit |
FlatAmount | decimal | yes | Additional band amount under the documented selected pricing mode |
UsageMetersVersioned event aggregation contract for one measurable product quantity.master
| Field | Type | Required | Details |
|---|---|---|---|
MeterNumber | text | yes | |
Name | text | yes | |
Unit | text | yes | Requests, seats, storage or another explicit unit |
Aggregation | select | yes | Sum, Maximum, Latest |
Version | number | yes | |
Status | select | yes | Draft, Active, Retired |
SubscriptionsAccepted customer terms and billing anchor; cancellation preserves prior invoices.transaction
| Field | Type | Required | Details |
|---|---|---|---|
SubscriptionNumber | text | yes | |
Customer | reference | yes | → Customers; |
PricePlan | reference | yes | → PricePlans; Published frozen version at acceptance |
StartedAt | datetime | yes | |
CurrentPeriodStart | datetime | yes | Inclusive UTC instant |
CurrentPeriodEnd | datetime | yes | Exclusive UTC instant |
BillingAnchor | datetime | yes | Customer-agreed anniversary anchor |
Quantity | decimal | yes | Nonnegative contracted units |
Owner | reference | yes | → Users; Assigned billing operator |
ConsentEvidence | attachment | yes | Accepted current terms and any recurring-payment mandate |
Status | select | yes | Pending, Active, Paused, PastDue, CancelAtPeriodEnd, Cancelled |
EndedAt | datetime | ||
Revision | number | yes |
SubscriptionChangesEffective plan, quantity, pause or cancellation proposals, preserving prior terms.transaction
| Field | Type | Required | Details |
|---|---|---|---|
ChangeNumber | text | yes | |
Subscription | reference | yes | → Subscriptions; |
ChangeType | select | yes | Plan, Quantity, Pause, Resume, Cancel |
NewPricePlan | reference | → PricePlans; Required for plan change | |
NewQuantity | decimal | Required for quantity change | |
EffectiveAt | datetime | yes | |
RequestedBy | reference | yes | → Users; |
ConsentEvidence | attachment | Required for changed commercial terms or mandate | |
ExpectedRevision | number | yes | |
Status | select | yes | Draft, Submitted, Accepted, Applied, Rejected, Cancelled |
AppliedAt | datetime |
UsageEventsAppend-only metering evidence; repeated source identities cannot increase usage.transaction
| Field | Type | Required | Details |
|---|---|---|---|
EventNumber | text | yes | |
Subscription | reference | yes | → Subscriptions; |
Meter | reference | yes | → UsageMeters; |
ExternalEventId | text | yes | Unique provider plus subscription plus meter key |
OccurredAt | datetime | yes | Business event time |
ReceivedAt | datetime | yes | Observed ingestion time |
Quantity | decimal | yes | Nonnegative measurement; correction uses a linked replacement |
CorrectionOf | reference | → UsageEvents; Replaces an earlier event through a reviewed correction; prior event retained | |
PayloadDigest | text | yes | Same key with changed payload is a conflict |
Status | select | yes | Received, Accepted, Rejected, Superseded |
RejectionReason | text | ||
CorrectionApprovedBy | reference | → Users; independent Billing Manager required for a replacement of accepted usage | |
CorrectionPreparedBy | reference | → Users; resolved preparer required for a correction |
UsageAggregatesFrozen, explainable metered quantities for one subscription interval and run.transaction
| Field | Type | Required | Details |
|---|---|---|---|
AggregateNumber | text | yes | |
Subscription | reference | yes | → Subscriptions; |
Meter | reference | yes | → UsageMeters; |
BillingRun | reference | yes | → BillingRuns; |
PeriodStart | datetime | yes | Inclusive boundary |
PeriodEnd | datetime | yes | Exclusive boundary |
Quantity | decimal | yes | Server aggregation of accepted non-superseded input set |
InputDigest | text | yes | Exact event identities and configured aggregation version |
Status | select | yes | Draft, Reviewed, Billed, Superseded |
CorrectsAggregate | reference | → UsageAggregates; original frozen billed basis retained for a late correction |
BillingRunsOne candidate invoice interval per subscription; reviewed inputs and retry identity.transaction
| Field | Type | Required | Details |
|---|---|---|---|
RunNumber | text | yes | |
Subscription | reference | yes | → Subscriptions; |
PeriodStart | datetime | yes | |
PeriodEnd | datetime | yes | |
CutoffAt | datetime | yes | Received-event cutoff for this frozen candidate |
SourceDigest | text | yes | Plan, changes, usage inputs, tax result and terms |
PreparedBy | reference | yes | → Users; |
ApprovedBy | reference | → Users; Independent Billing Manager | |
PreparedAt | datetime | yes | |
Status | select | yes | Draft, Submitted, Approved, Issued, Failed, Cancelled |
Invoice | reference | → Invoices; Single issued invoice for this subscription interval/revision | |
Purpose | select | yes | Regular, Correction |
Correction | reference | → BillingAdjustments; required only for a standalone Correction run |
InvoicesIssued customer billing document; frozen lines, currency and tax evidence.transaction
| Field | Type | Required | Details |
|---|---|---|---|
InvoiceNumber | text | yes | |
Customer | reference | yes | → Customers; |
Subscription | reference | yes | → Subscriptions; |
BillingRun | reference | yes | → BillingRuns; |
InvoiceDate | date | yes | |
DueDate | date | yes | |
Currency | text | yes | |
Subtotal | decimal | yes | Sum net InvoiceLines.LineSubtotal |
TaxTotal | decimal | yes | Sum InvoiceLines.LineTax under preserved provider result |
Total | decimal | yes | Subtotal plus TaxTotal |
AmountPaid | decimal | yes | Server computed from verified settled gross less confirmed refunds and chargebacks; pending and authorisation contribute zero |
CreditsApplied | decimal | yes | Issued approved credit amounts allocated once |
AmountDue | decimal | yes | Total less CreditsApplied less AmountPaid; preserve excess credit separately |
Status | select | yes | Draft, Issued, PartPaid, Paid, Overdue, Void |
DocumentDigest | text | Required only when Status is Issued; retain for later collection states |
InvoiceLinesFrozen billed sources and net/tax/gross amounts.line
| Field | Type | Required | Details |
|---|---|---|---|
Invoice | reference | yes | → Invoices; |
Description | text | yes | |
LineType | select | yes | Recurring, Usage, ProrationCredit, ProrationCharge, Adjustment |
UsageAggregate | reference | → UsageAggregates; Required for metered usage | |
SubscriptionChange | reference | → SubscriptionChanges; Required for plan-change proration | |
Quantity | decimal | yes | |
UnitPrice | decimal | yes | |
LineSubtotal | decimal | yes | Server-rated net amount; signed for authorised proration credit |
LineTax | decimal | yes | Preserved tax service or reviewed rule calculation |
LineTotal | decimal | yes | LineSubtotal plus LineTax |
CalculationEvidence | longtext | yes | Plan version, tiers, bounds, time fraction, rounding and tax result |
BillingAdjustment | reference | → BillingAdjustments; required for a post-issue usage/term correction line |
PaymentMethodsProvider-hosted payment tokens and accepted recurring mandates; no raw card details.master
| Field | Type | Required | Details |
|---|---|---|---|
MethodNumber | text | yes | |
Customer | reference | yes | → Customers; |
ProviderAccount | text | yes | |
TokenReference | text | yes | Restricted provider token |
MandateEvidence | attachment | yes | |
ValidFrom | datetime | yes | |
Status | select | yes | Pending, Active, Revoked, Expired |
PaymentAttemptsStable collection instruction and observed provider result, separate from invoice issue.transaction
| Field | Type | Required | Details |
|---|---|---|---|
AttemptNumber | text | yes | |
Invoice | reference | yes | → Invoices; |
PaymentMethod | reference | yes | → PaymentMethods; |
IdempotencyKey | text | yes | One instruction identity retained through unknown outcomes |
ProviderReference | text | ||
Amount | decimal | yes | Positive amount no greater than collectible available balance |
Currency | text | yes | |
AttemptedAt | datetime | yes | |
ReconciledAt | datetime | ||
Status | select | yes | Prepared, Pending, Authorized, Settled, Failed, Unknown, Refunded, Disputed |
SettledAmount | decimal | yes | Verified provider settled gross |
RefundedAmount | decimal | yes | Verified linked refunded gross; cannot exceed settled amount |
DisputedAmount | decimal | yes | Verified amount under unresolved dispute; preserve collection hold until outcome |
ChargebackAmount | decimal | yes | Confirmed provider loss reducing net receipt; retain source outcome |
CreditNotesIndependently reviewed correction to an issued invoice; cash refund is separate.transaction
| Field | Type | Required | Details |
|---|---|---|---|
CreditNumber | text | yes | |
Invoice | reference | yes | → Invoices; |
Customer | reference | yes | → Customers; |
CreditDate | date | yes | |
Currency | text | yes | |
Reason | longtext | yes | |
Amount | decimal | yes | Server sum of CreditLines.Total within remaining creditable source-line net and tax amounts |
PreparedBy | reference | yes | → Users; |
ApprovedBy | reference | → Users; Independent Billing Manager | |
AppliedAt | datetime | ||
Status | select | yes | Draft, Submitted, Approved, Issued, Rejected, Cancelled |
CreditLinesExplicit reference to the original billed line and remaining creditable amount.line
| Field | Type | Required | Details |
|---|---|---|---|
CreditNote | reference | yes | → CreditNotes; |
InvoiceLine | reference | yes | → InvoiceLines; |
NetAmount | decimal | yes | Nonnegative correction within remaining net amount |
TaxAmount | decimal | yes | Corresponding original tax correction under reviewed policy |
Total | decimal | yes | NetAmount plus TaxAmount |
DunningCasesInvoice follow-up state; communication never manufactures collection.transaction
| Field | Type | Required | Details |
|---|---|---|---|
CaseNumber | text | yes | |
Invoice | reference | yes | → Invoices; |
Customer | reference | yes | → Customers; |
Owner | reference | yes | → Users; |
NextActionAt | datetime | yes | |
OpenedAt | datetime | yes | |
Stage | select | yes | Reminder, RetryReview, Escalated, FinalNotice |
Status | select | yes | Open, Paused, Resolved, Cancelled |
DunningAttemptsOne scheduled notice or authorised payment retry with retained outcome.line
| Field | Type | Required | Details |
|---|---|---|---|
DunningCase | reference | yes | → DunningCases; |
AttemptNumber | text | yes | |
ActionType | select | yes | Email, PaymentRetry, InternalTask |
ScheduledAt | datetime | yes | |
PerformedAt | datetime | ||
PaymentAttempt | reference | → PaymentAttempts; Required for retry action | |
ProviderReceipt | text | ||
Status | select | yes | Pending, Sent, Failed, Skipped |
IntegrationEventsVerified inbound receipt and deduplication envelope for metering, tax and payments.transaction
| Field | Type | Required | Details |
|---|---|---|---|
ReceiptNumber | text | yes | |
Provider | text | yes | |
ExternalEventId | text | yes | Unique provider-account event id |
PayloadDigest | text | yes | |
ReceivedAt | datetime | yes | |
VerifiedAt | datetime | ||
Status | select | yes | Received, Verified, Applied, Rejected, Conflict |
OutcomeReference | text | Observed source record; retries return the same applied effect |
RefundRequestsIndependently reviewed refund instruction against an observed settled payment, preserving unknown outcomes.transaction
| Field | Type | Required | Details |
|---|---|---|---|
RefundNumber | text | yes | |
PaymentAttempt | reference | yes | → PaymentAttempts; Original settled provider transaction |
CreditNote | reference | → CreditNotes; Linked issued correction when applicable | |
Amount | decimal | yes | Positive and no greater than remaining refundable settlement |
Currency | text | yes | |
Reason | longtext | yes | |
PreparedBy | reference | yes | → Users; |
ApprovedBy | reference | → Users; Independent Billing Manager | |
RequestedAt | datetime | yes | |
CompletedAt | datetime | ||
IdempotencyKey | text | yes | Stable provider instruction through reconciliation |
ProviderReference | text | ||
Status | select | yes | Draft, Submitted, Approved, Pending, Settled, Failed, Unknown, Rejected |
BillingAdjustmentsReviewed difference between a corrected rated source and the amounts already billed for the original line. Retain both evidence and signed delta.transaction
| Field | Type | Required | Details |
|---|---|---|---|
AdjustmentNumber | text | yes | Stable correction identity |
Subscription | reference | yes | → Subscriptions |
OriginalInvoiceLine | reference | yes | → InvoiceLines; same subscription, customer, seller and currency |
CorrectedAggregate | reference | → UsageAggregates; frozen corrected event set when usage changes | |
SubscriptionChange | reference | → SubscriptionChanges; accepted term correction when applicable | |
CorrectedSourceDigest | text | yes | Corrected source set, original price version and tax evidence |
CorrectedRatedNet | decimal | yes | Full correct cumulative net amount for the original source line basis |
CorrectedRatedTax | decimal | yes | Full correct tax amount under the reviewed original treatment |
NetDelta | decimal | yes | CorrectedRatedNet less original LineSubtotal less prior applied net adjustments |
TaxDelta | decimal | yes | CorrectedRatedTax less original LineTax less prior applied tax adjustments |
Reason | longtext | yes | Source correction explanation |
PreparedBy | reference | yes | → Users |
ApprovedBy | reference | → Users; independent Billing Manager | |
CreatedAt | datetime | yes | |
AppliedAt | datetime | Required only when Status is Applied | |
Status | select | yes | Draft, Submitted, Approved, Applied, Rejected, Cancelled |
ResultInvoiceLine | reference | → InvoiceLines; unique positive debit application | |
ResultCreditNote | reference | → CreditNotes; convenience pointer only for a single-document credit; BillingAdjustmentApplications is authoritative |
BillingAdjustmentApplicationsPer-document application of an approved signed correction; supports crediting original and earlier positive correction invoices without exceeding any line balance.line
| Field | Type | Required | Details |
|---|---|---|---|
BillingAdjustment | reference | yes | → BillingAdjustments |
SourceInvoiceLine | reference | yes | → InvoiceLines; original positive charge or a previously issued positive correction for the same source basis |
ResultInvoiceLine | reference | → InvoiceLines; positive debit application | |
ResultCreditNote | reference | → CreditNotes; negative application against SourceInvoiceLine's actual invoice | |
NetAmount | decimal | yes | Signed share of the correction; negative for credit |
TaxAmount | decimal | yes | Signed corresponding tax share |
ApplicationKey | text | yes | Unique adjustment plus source document plus signed application identity |
Status | select | yes | Reserved, Issued, Failed, Cancelled |