These cookies are required for the website to function properly. They ensure security, enable basic features like page navigation, and store user session data. You cannot disable these cookies.
Data model
Trace invoices, receipts and adjustments back to their governing source.
20 tables · 44 relationships
Migration order
- Customers, BillingTerms, InvoiceTemplates and DunningPolicies establish the reviewed billing basis.
- Invoices and InvoiceLines preserve local obligations or separately identified external collection references.
- PaymentsReceived, PaymentApplications and BankFeedTransactions preserve receipt identities and accepted allocations.
- CreditMemos, CreditMemoLines, Disputes, WriteOffs, LateFees, CreditHolds and CollectionsCases retain adjustments and decisions.
- DunningEvents and ARAgingSnapshots preserve communications and cutoff evidence.
Balance rules
20 tables
Local OpenBalance deducts accepted cash, applied credits and posted write-offs once. Proposals cannot reduce it. External collection references use their confirmed ExternalOpenBalance and SourceVersion instead of mixing in local financial actions. Keep all corrections attributable to the original source.
Local OpenBalance deducts accepted cash, applied credits and posted write-offs once. Proposals cannot reduce it. External collection references use their confirmed ExternalOpenBalance and SourceVersion instead of mixing in local financial actions. Keep all corrections attributable to the original source.
CustomersThe AR-grain customer record. May shadow a CRM Account; this rowmaster
| Field | Type | Required | Details |
|---|---|---|---|
CustomerNumber | text | yes | |
Name | text | yes | |
CRMAccount | text | Sync key from crm-pipeline | |
BillingEmail | text | yes | |
BillingAddress | text | ||
BillingState | select | US state | |
TaxRegistrationID | text | Resale / exemption certificate ID | |
DefaultBillingTerm | reference | yes | → BillingTerms |
DefaultDunningPolicy | reference | → DunningPolicies | |
DefaultTemplate | reference | → InvoiceTemplates | |
CreditLimit | currency | Masked except AR Manager, Controller, Admin | |
OpenARBalance | formula | yes | fx: SUM(Invoices.OpenBalance where Invoices.Customer = self AND Invoices.Status NOT IN (Paid, Voided, WrittenOff)) |
OnCreditHold | formula | yes | fx: COUNT(CreditHolds where Customer = self AND Status = Active) > 0 |
AccountOwner | reference | → Employees (sales rep) | |
StatementCadence | select | None, Monthly, Quarterly | |
Status | select | yes | Active, Inactive, Prospect, Suspended |
BillingTermsCatalog of payment terms. Net 15, Net 30, Net 60, Net 90, EOM,master
| Field | Type | Required | Details |
|---|---|---|---|
TermCode | text | yes | |
Name | text | yes | |
NetDays | number | yes | 0 = due on receipt |
AnchorRule | select | yes | NetDaysFromInvoiceDate, EndOfMonth, FifteenthOfMonthAfter, Prepay |
EarlyPayDiscountPercent | number | e.g. 2 for 2/10 net 30 | |
EarlyPayDiscountDays | number | e.g. 10 for 2/10 net 30 | |
LateFeePolicy | select | None, FlatAmount, PercentOfBalance | |
LateFeeAmount | currency | When LateFeePolicy = FlatAmount | |
LateFeePercent | number | When LateFeePolicy = PercentOfBalance | |
Active | toggle | yes |
InvoiceTemplatesBranded invoice render formats. Picks the logo, tax footnotemaster
| Field | Type | Required | Details |
|---|---|---|---|
TemplateCode | text | yes | |
Name | text | yes | |
LegalEntity | text | yes | Issuing entity name |
LogoURL | text | ||
RemitToAddress | text | yes | |
RemitToBank | text | ACH / wire instructions block | |
FooterTerms | text | Legal footer block | |
DefaultDueRule | select | UseBillingTerm, Net30Override | |
Active | toggle | yes |
DunningPoliciesPer-Customer or default dunning policy. Defines aging-bandmaster
| Field | Type | Required | Details |
|---|---|---|---|
PolicyCode | text | yes | |
Name | text | yes | |
Band1Days | number | yes | First reminder, days past due |
Band1Template | text | yes | Email template ID — reminder |
Band2Days | number | yes | Escalation, days past due |
Band2Template | text | yes | Email template ID — escalation |
Band3Days | number | yes | Demand, days past due |
Band3Template | text | yes | Email template ID — demand |
Band4Days | number | Collections-case open trigger | |
EscalationContact | text | CC'd from Band2 onward | |
AppliesTo | select | yes | Default, ByCustomer, ByCustomerSegment |
Active | toggle | yes |
InvoicesThe customer invoice — the AR atom. Header carries totals;transaction
| Field | Type | Required | Details |
|---|---|---|---|
InvoiceNumber | text | yes | |
SourceApp | select | local, pm-billing, manual | |
SourceInvoiceRef | text | When SourceApp != local, foreign key to source app's invoice number for cross-app aging | |
Customer | reference | yes | → Customers |
Template | reference | → InvoiceTemplates; required for locally issued invoices | |
BillingTerm | reference | yes | → BillingTerms |
Type | select | yes | Standard, Recurring, ProjectMilestone, LateFee, Adjustment |
RecurringSchedule | reference | → RecurringInvoiceSchedules (when Type = Recurring) | |
OriginalInvoice | reference | → Invoices (self — when Type = LateFee or Adjustment) | |
IssueDate | date | yes | |
DueDate | date | yes | |
Subtotal | formula | yes | fx: SUM(InvoiceLines.LineTotal); local invoices only |
TaxTotal | formula | yes | fx: SUM(InvoiceLines.TaxAmount) |
Total | formula | yes | fx: Subtotal + TaxTotal |
AmountApplied | formula | yes | fx: SUM(PaymentApplications.AmountApplied where Invoice = self AND ApplicationType = InvoicePayment AND parent Status IN (Applied, PartiallyApplied)); excludes proposals and returned or refunded receipt effects |
CreditApplied | formula | yes | fx: SUM(CreditMemos.Amount where AppliedToInvoice = self AND Status = Applied) |
WriteOffApplied | formula | yes | fx: SUM(WriteOffs.Amount where Invoice = self AND Status = Posted) |
ExternalOpenBalance | currency | Latest confirmed open balance for a pm-billing collection reference; never combined with local applications | |
SourceVersion | text | Governing external invoice version or event; reject stale updates | |
OpenBalance | formula | yes | fx: IF(SourceApp = pm-billing, ExternalOpenBalance, Total − AmountApplied − CreditApplied − WriteOffApplied) |
AgingBucket | formula | yes | fx: IF(OpenBalance <= 0, "Paid", IF(today() <= DueDate, "Current", IF(today() − DueDate <= 30, "1-30", IF(today() − DueDate <= 60, "31-60", IF(today() − DueDate <= 90, "61-90", "90+"))))) |
Currency | select | yes | USD only at this size tier; reserved for future multi-currency |
PONumber | text | Customer purchase order ref | |
Status | select | yes | Draft, Sent, PartiallyPaid, Paid, Overdue, Disputed, WrittenOff, Voided |
SentAt | date | ||
PDFURL | text | Frozen at send | |
GLJournalEntry | text | fin-accounting JE ref written by gl-post-on-invoice-send |
InvoiceLinesOne line per item billed — product, service, milestone, or Line item of Invoices.line item
| Field | Type | Required | Details |
|---|---|---|---|
Invoice | reference | yes | → Invoices |
LineNumber | number | yes | |
Description | text | yes | |
ProductSKU | text | ||
Quantity | number | yes | |
UnitPrice | currency | yes | |
LineTotal | formula | yes | fx: Quantity × UnitPrice |
RevenueAccount | text | yes | GL revenue account code |
TaxRatePercent | number | Sourced from fin-tax-compliance | |
TaxAmount | formula | yes | fx: LineTotal × TaxRatePercent / 100 |
ProjectRef | text | Pass-through from pm-billing |
RecurringInvoiceSchedulesSubscription / retainer billing config. The recurring-invoicemaster
| Field | Type | Required | Details |
|---|---|---|---|
ScheduleNumber | text | yes | |
Customer | reference | yes | → Customers |
Template | reference | yes | → InvoiceTemplates |
Frequency | select | yes | Weekly, Monthly, Quarterly, Annual |
StartDate | date | yes | |
EndDate | date | ||
NextRunDate | date | yes | |
BillingTerm | reference | yes | → BillingTerms |
TemplateLines | text | Frozen line-items snapshot | |
Status | select | yes | Active, Paused, Ended, Cancelled |
CreditMemosA credit issued against a Customer or a specific Invoice. Reducestransaction
| Field | Type | Required | Details |
|---|---|---|---|
CreditMemoNumber | text | yes | |
Customer | reference | yes | → Customers |
AppliedToInvoice | reference | → Invoices | |
IssueDate | date | yes | |
Amount | currency | yes | |
Reason | select | yes | Return, PricingAdjustment, Goodwill, BillingError, Cancellation, Other |
ApprovedBy | reference | → Employees (AR Manager) | |
ApprovedAt | date | ||
Status | select | yes | Draft, Approved, Applied, Voided |
GLJournalEntry | text | fin-accounting JE ref |
CreditMemoLinesPer-line detail of a credit memo. Line item of CreditMemos.line item
| Field | Type | Required | Details |
|---|---|---|---|
CreditMemo | reference | yes | → CreditMemos |
LineNumber | number | yes | |
Description | text | yes | |
Quantity | number | ||
UnitPrice | currency | ||
LineTotal | currency | yes | |
RevenueAccount | text | yes |
PaymentsReceivedAn incoming payment from a Customer. May be auto-created by thetransaction
| Field | Type | Required | Details |
|---|---|---|---|
PaymentNumber | text | yes | |
Customer | reference | yes | → Customers |
Method | select | yes | ACH, Check, Wire, CardOnFile |
Amount | currency | yes | |
ReceivedAt | date | yes | |
BankAccount | text | Receiving bank account — masked except Controller, AR Manager, Admin | |
Reference | text | Check number, ACH trace, wire reference | |
ProcessorTransactionRef | text | Stable processor event identity. Reconcile its confirmed bank settlement through an explicit source mapping, never by memo similarity alone. | |
BankFeedTransaction | reference | → BankFeedTransactions (when matched) | |
AmountApplied | formula | yes | fx: SUM(PaymentApplications.AmountApplied where ApplicationType = InvoicePayment); proposals do not reduce invoice balances |
UnappliedAmount | formula | yes | fx: Amount − AmountApplied |
Status | select | yes | Draft, ProposedMatch, Applied, PartiallyApplied, Returned, Refunded |
AppliedBy | reference | → Employees (AR Clerk) | |
AppliedAt | date | ||
GLJournalEntry | text | fin-accounting JE ref |
PaymentApplicationsDistributes a PaymentReceived across one or more Invoices (or Line item of PaymentsReceived.line item
| Field | Type | Required | Details |
|---|---|---|---|
PaymentReceived | reference | yes | → PaymentsReceived |
Invoice | reference | → Invoices | |
AmountApplied | currency | yes | |
ApplicationType | select | yes | InvoicePayment, EarlyPayDiscount, ShortPay, Unapplied; only InvoicePayment allocates receipt cash. Discount and short-pay suggestions require a separately approved credit or write-off; Unapplied is explanatory and not another allocation. |
Notes | text | Free text — short-pay reason, discount reference |
BankFeedTransactionsRead-stream from the bank API. Source for cash auto-application.transaction
| Field | Type | Required | Details |
|---|---|---|---|
FeedTransactionID | text | yes | Bank-side unique ID |
BankAccount | text | yes | Masked except Controller, AR Manager, Admin |
TransactionDate | date | yes | |
Amount | currency | yes | |
Type | select | yes | CreditACH, CreditWire, Check, DebitACH, Fee, Other |
Memo | text | Bank-side memo / reference | |
CounterpartyName | text | ||
MatchStatus | select | yes | Unmatched, ProposedMatch, Matched, Ignored |
MatchedPayment | reference | → PaymentsReceived | |
MatchConfidence | number | 0–100 score from auto-match |
DunningEventsThe audit trail of every dunning communication sent. One row Line item of Invoices.line item
| Field | Type | Required | Details |
|---|---|---|---|
Invoice | reference | yes | → Invoices |
Customer | reference | yes | → Customers |
Policy | reference | yes | → DunningPolicies |
Band | select | yes | Band0Acknowledge, Band1Reminder, Band2Escalation, Band3Demand, Band4CollectionsHandoff |
SentAt | date | yes | |
RecipientEmail | text | yes | |
TemplateUsed | text | yes | |
Outcome | select | Delivered, Bounced, Replied, PaymentMade, DisputeOpened | |
EmailMessageID | text | Provider-side ID for thread tracking |
DisputesA customer-raised dispute against an Invoice. Pauses dunningtransaction
| Field | Type | Required | Details |
|---|---|---|---|
DisputeNumber | text | yes | |
Invoice | reference | yes | → Invoices |
Customer | reference | yes | → Customers |
RaisedBy | text | yes | Customer-side contact |
RaisedAt | date | yes | |
Reason | select | yes | PricingError, QuantityError, ServiceNotDelivered, DuplicateBill, TaxError, Other |
AmountInDispute | currency | ||
Resolution | select | InvoiceUpheld, CreditMemoIssued, PartialCreditIssued, Voided | |
ResolvedBy | reference | → Employees (AR Manager) | |
ResolvedAt | date | ||
Status | select | yes | Open, UnderReview, Resolved, Closed |
WriteOffsUncollectible AR written off the books. Requires Controllertransaction
| Field | Type | Required | Details |
|---|---|---|---|
WriteOffNumber | text | yes | |
Invoice | reference | yes | → Invoices |
Customer | reference | yes | → Customers |
Amount | currency | yes | |
WriteOffDate | date | yes | |
Reason | select | yes | Bankruptcy, Uncollectible, BelowThreshold, SettlementCompromise, Other |
RequestedBy | reference | yes | → Employees (AR Manager) |
ApprovedBy | reference | → Employees (Controller / CFO) | |
ApprovedAt | date | ||
Status | select | yes | Draft, PendingApproval, Approved, Posted, Reversed |
GLJournalEntry | text | fin-accounting JE ref |
LateFeesLate fees applied per BillingTerm.LateFeePolicy. Each LateFeetransaction
| Field | Type | Required | Details |
|---|---|---|---|
LateFeeNumber | text | yes | |
OriginalInvoice | reference | yes | → Invoices |
Customer | reference | yes | → Customers |
AssessedDate | date | yes | |
FeeAmount | currency | yes | |
Basis | select | yes | FlatAmount, PercentOfBalance |
BasisPercent | number | ||
FollowOnInvoice | reference | → Invoices | |
Status | select | yes | Assessed, Invoiced, Waived, Paid |
WaivedBy | reference | → Employees (AR Manager) |
ARAgingSnapshotsPeriod-frozen aging buckets per Customer. Written by thetransaction
| Field | Type | Required | Details |
|---|---|---|---|
SnapshotNumber | text | yes | |
Customer | reference | yes | → Customers |
AsOfDate | date | yes | |
CurrentAmount | currency | yes | |
Bucket1To30 | currency | yes | |
Bucket31To60 | currency | yes | |
Bucket61To90 | currency | yes | |
Bucket90Plus | currency | yes | |
TotalOpen | formula | yes | fx: CurrentAmount + Bucket1To30 + Bucket31To60 + Bucket61To90 + Bucket90Plus |
Period | text | yes | e.g. 2026-04 |
CollectionsCasesEscalated collections workflow. Opened when an Invoice crossestransaction
| Field | Type | Required | Details |
|---|---|---|---|
CaseNumber | text | yes | |
Customer | reference | yes | → Customers |
LeadInvoice | reference | yes | → Invoices (the trigger invoice) |
OpenedAt | date | yes | |
OpenedBy | reference | → Employees (AR Manager) | |
Owner | reference | → Employees (assigned AR Clerk or AR Manager) | |
TotalInScope | currency | Sum of in-case Invoice OpenBalance | |
Stage | select | yes | Internal, ExternalAgency, LegalReview, Litigation, SettlementOffered, Closed |
ResolutionType | select | FullPayment, PartialSettlement, WrittenOff, Bankruptcy, OnHoldIndefinite | |
ResolvedAt | date | ||
Status | select | yes | Open, OnHold, Resolved, Closed |
CreditHoldsCustomer-level credit hold. While Active, no new Invoices maytransaction
| Field | Type | Required | Details |
|---|---|---|---|
HoldNumber | text | yes | |
Customer | reference | yes | → Customers |
Reason | select | yes | AgingBreach, CreditLimitExceeded, ManualReview, BankruptcyFiling, DisputeOpenOverThreshold |
HeldAt | date | yes | |
HeldBy | reference | → Employees (AR Manager) | |
ReleasedAt | date | ||
ReleasedBy | reference | → Employees (AR Manager) | |
Notes | text | ||
Status | select | yes | Active, Released, Auto-Cleared |
EmployeesA scoped employee identity directory synchronized from the organization identity or HR source. This reference supports ownership and approval attribution; it does not add payroll or HR administration. Role membership alone never grants access to every directory field. External auditors and portal users receive only the identities needed for their granted work.master
| Field | Type | Required | Details |
|---|---|---|---|
EmployeeNumber | text | yes | Stable source identifier; unique within the organization |
Name | text | yes | Display name for an assigned owner or approver |
UserKey | text | yes | Bound to the authenticated platform user; never supplied as authority by a client |
Department | text | Organization-scoped department key | |
Manager | reference | → Employees; synchronized reporting relationship | |
Active | boolean | yes | Inactive identities retained for historical attribution; cannot receive new work |