The Accounting data models in Smart Insights hold the money: job payments, storage payments, expenses, storage charges, invoices, and payroll. This article covers each Accounting model, what a single row represents, the columns people report on most, and the nuanced fields to watch. For the shared concepts these models have in common (grain, dates, payments and refunds), see Understanding Smart Insights Data Models.
Before You Start
Smart Insights is a paid add-on. Contact your Account Manager to enable it.
You need Accounting reporting permission for your role.
Open a model by clicking Create Insight, then picking the matching table template (for example, Job Payment Table).
Job Payment
Grain: one row per job payment. Template name: Job Payment Table.
The Job Payment model holds every payment recorded on a deal: deposits, balance-due payments, and other payment types, including the amount, how it was paid, and any refund applied.
Key columns
Column | What it means |
Amount | The payment amount. Refunds can post as a negative amount. |
Payment Date | When the payment was recorded. |
Payment Type | How it was paid, or your custom payment-type name. |
Payment Category | Deposit, Balance Due, or Other. |
Amount Refunded | The dollar amount refunded on this payment. |
Opportunity ID | The deal the payment belongs to. The reliable join key. |
Job ID | The job the payment belongs to. Often blank, because payments are recorded against the opportunity, not a specific job. |
Job Number | The job number, if any. |
Opportunity Number | The quote/deal number. |
Branch Name | The branch the deal belongs to. |
Credit Card Type | The card brand, for card payments. |
Is Outstanding | Whether the payment is still outstanding. |
Other columns in this model
Job Payment ID, Company ID, Payment ID, Credit Card Fee Amount, Is Credit Card Fee Waived, Postal Code, Custom Payment Description, Original Payment Amount, Retracted Payment Description, Opportunity Type, Service Type, Job Date.
Good to know
Payments are recorded against the opportunity, not a specific job, so Job ID and Job Number are often blank. Join payments on Opportunity ID to capture every payment, including deposits.
Refunds can appear two ways: as a negative Amount row, or as an Amount Refunded value on the original payment. Check both before you total a customer's payments.
Payment Type shows your custom payment-type name if configured, otherwise a plain code.
Payment ID and Job Payment ID are the same value, not two separate keys.
Storage Payment
Grain: one row per storage payment. Template name: Storage Payment Table.
The Storage Payment model holds every payment recorded against a storage account: how much, what type, and where it came from (the mobile app, web, customer portal, or an external system).
Key columns
Column | What it means |
Amount | The payment amount. |
Payment Date | See Good to know. This is the account's creation date, not the payment date. |
Payment Type | How it was paid, or your custom payment-type name. |
Source | Where the payment came from: Mobile App, Web App, Customer Portal, or External API. |
Account Number | The storage account number. |
Customer Name | The customer on the account. |
Storage Account ID | The account the payment belongs to. |
Other columns in this model
Storage Account Payment ID, Company ID, Custom Payment Description, Job Date.
Good to know
Payment Date is the storage account's creation date, not when the payment happened. Do not read it as the payment timestamp.
This model is separate from the Job Payment model. Storage payments do not appear in job payments and vice versa. Combine them deliberately.
Expenses
Grain: one row per expense record. Template name: Expenses Table.
The Expenses model holds each cost logged against a deal or job, with its category, total amount, and description.
Key columns
Column | What it means |
Total Amount | The total expense amount. |
Expense Category | The expense category. |
Description | The expense description. |
Opportunity ID | The deal the expense belongs to. |
Job ID | The job the expense belongs to. |
Job Number | The job number, if any. |
Opportunity Number | The quote/deal number. |
Service Type | The service type, or your custom service-type name. |
Opportunity Type | Local, Intrastate, or Interstate. |
Other columns in this model
Expense ID, Company ID, Job Date.
Good to know
Expense Category is the raw category from the source, so it reflects however your company set categories up.
Storage Charges
Grain: one row per storage account charge. Template name: Storage Charges Table.
The Storage Charges model holds every line-item charge on a storage account: rent, fees, and add-ons, with unit pricing, proration, discounts, and tax applied.
Key columns
Column | What it means |
Total Amount Post Discount | The charge total after any discount. |
Total Amount Before Discount | The charge total before any discount. |
Description | The charge description. |
Charge Type | The type or category of charge. |
Is Recurring | Whether the charge recurs. |
Is Unbilled | Whether the charge has not been billed yet. |
Is Taxed | Whether the charge is taxed. |
Tax Amount | The tax applied to this charge. |
Storage Account ID | The account the charge belongs to. |
Invoice ID | The invoice this charge belongs to. |
Other columns in this model
ID, Company ID, Last Modified By ID, Storage Account Payment ID, Units, Unit Amount Post Proration, Unit Amount Before Proration, Unit Amount Label, Units Label, Unit Multiplier, Created At Utc, Last Modified At Utc.
Good to know
There are two amount pairs: before versus after discount, and before versus after proration. Pick the one your calculation needs.
Units Label carries the unit of measure (for example, "sq ft" or "months").
Storage Account Invoices
Grain: one row per storage account invoice. Template name: Storage Account Invoices Table.
The Storage Account Invoices model holds each invoice generated for a storage account: the billing period, totals, taxes, discounts, balance, and its payment and auto-charge status.
Key columns
Column | What it means |
Invoice Number | The invoice number. |
Total | The invoice total. |
Balance | The outstanding balance on the invoice. |
Invoice Start Date | The start of the billing period. |
Invoice End Date | The end of the billing period. |
Due At | When the invoice is due. |
Paid At | When the invoice was paid. |
Taxes | The tax total on the invoice. |
Sales Tax Rate | The sales tax rate applied. |
Discounts Total | Total discounts applied. |
Is Prorated | Whether the invoice is prorated. |
Storage Account ID | The account the invoice belongs to. |
Other columns in this model
IDs: ID, Company ID.
Subtotals and taxable bases: Grand Subtotal After Discounts, Grand Subtotal Before Discounts, Non-Recurring Subtotal Before Discounts, Recurring Subtotal Before Discounts, Taxable Amount After Discount, Taxable Amount Before Discounts.
Dates and lifecycle: Created At Utc, Last Sent At, Late At, Invoice First Viewed At.
Auto-charge and payment status: Is Ready For Auto Charging, Last Payment Failed At Utc, Last Payment Failure Message, Payment Failure Count, Should Send Invoice Override, Has Late Fee Processing Been Applied.
Good to know
Invoice Start Date and Invoice End Date can be blank when no billing period is stored, so do not assume every invoice has a populated period.
The payment-failure fields relate to auto-charge attempts, not manual payments.
Payroll
Grain: one row per payroll transaction (one row per pay category per job). Template name: Payroll Table.
The Payroll model holds every payroll line for an employee: hourly wages, commissions, tips, bonuses, and deductions, with the amount paid and the job it is tied to.
Key columns
Column | What it means |
Amount | The amount paid on the line. Deductions are stored as a negative. |
Sub Type Name | The kind of pay: Hourly Wage, Commission, Tip, Other, Deduction, or Bonus. |
Type | The context: Job Wage (tied to a job), Other Wage (not job-tied), or Review Bonus Wage. |
Commission | The commission category, for commission lines (blank otherwise). |
Eligible Amount | The base amount a commission is calculated on. |
Commission Rate | The commission rate applied to the line. |
Employee Name | The employee paid. |
Employee ID | Links to the employee in the Employees model. |
Job ID | The job the line is tied to, if any. |
Job Number | The job number, if any. |
Start Date | The transaction date. |
Other columns in this model
ID, Company ID, Review ID, Crew Event Crew Rate ID, Hourly Rate, Minutes, Bonus, Description, Notes, Closed Date, Job Date.
Good to know
Sub Type Name is the field you filter on to separate wages, commissions, tips, bonuses, and deductions.
Deductions are stored as a negative Amount so they net correctly. Do not flip the sign.
Commission lines carry their category in the Commission column. It has around 20 categories (Moving Labor, Materials, Transportation, Packing, Valuation, and so on) and is blank on non-commission lines. For a single commission rate when categories have their own rates, divide total commission Amount by total Eligible Amount rather than reading one rate.
The Bonus sub-type is performance-review bonuses, not sales lump sums.
Accounting Overview (Combined Model)
The Accounting Overview Tables template pre-joins payment, charge, invoice, and expense data so a report can cross payments with charges or expenses in one view. Because it combines models, some column names (like Company ID) appear more than once. When a report touches only one area (expenses only, for example), the single model is cleaner.
Accounting-Area Tips
Payments are recorded against the opportunity, not a specific job, so Job ID is often blank. Join job payments on Opportunity ID, not Job ID.
Refunds can post as a negative Amount or as an Amount Refunded value. Check both before totaling.
Deductions on Payroll are stored as negative amounts; do not flip the sign.
Payments reconcile at the account or deal level, not the job level. A single account payment can span multiple jobs.
Storage payments and job payments are separate models; combine them deliberately.
On the Storage Payment model, Payment Date is the account's creation date, not the payment date.
FAQs
How do I total every payment on a deal, including deposits?
Join the Job Payment model on Opportunity ID. Payments are recorded against the opportunity, not a specific job, so Job ID is often blank and joining on Job ID alone misses payments.
How are refunds recorded?
A refund can appear as a negative Amount row, or as an Amount Refunded value on the original payment. Check both before totaling.
Which model shows what an employee was actually paid?
The Payroll model. The Employees model in Operations holds pay settings, not amounts paid.