Data model
Identify the grain and join keys before combining transactions, impacts, customers or catalogue data.
Transactions and their children
| Relation | Join and grain |
|---|---|
| Transaction header | <type>s.id identifies one transaction of that type |
| Item and non-inventory lines | <type>lines and <type>linesnoninv carry <type>_id back to the header |
| Header classification | <type>classifications.<type>_id links the computed header flags |
| Line classification | Links to its line through <type>line_id; use the actual schema for the type |
| Related documents | createdfrom is the upstream relationship; classification resolves the order into salesorder_id |
This family shape has exceptions: prepayments have a header and header classification only; fulfillments and receipts have lines but no discount or tax tables. Financial document types also carry discount and tax rows. Do not generate table names for every possible suffix and assume they exist. See transaction objects.
Contribution margin
| Table | One row per | Key |
|---|---|---|
cm_impact_<group> | Rule, transaction and account combination | id, formed from those ids; interpret within the group table |
cm_impact_salesorders | Order's realised totals | salesorder_id |
cm_expectedimpact_salesorders | Order's expected totals and refresh state | salesorder_id |
A rule's matching line impacts are summed before a per-group row is stored. Consequently a stored impact is not necessarily a single line charge. The Preview breakdown can be more detailed than the stored row. Zero totals are not written by the calculate jobs.
Join an order's totals with salesorders.id = cm_impact_salesorders.salesorder_id. Start with a LEFT JOIN when diagnosing missing calculation: an INNER JOIN silently drops orders without a queue row. A missing row is different from a computed zero; inspect processed as well as the amount.
Never sum order totals after joining them to lines or per-group impacts. Either aggregate the children to one row per order first, or query the order totals directly. Expected and realised are alternative measures, not components to add together.
Customers
salesorders.id = customersalesorders.salesorder_id gives an order's current customer assignment and numbering. customersalesorders.customer_id = customers.id gives its customer summary. Fingerprint tables describe keys supporting that assignment; joining their rows onto orders will multiply orders unless each side is reduced to the intended grain.
vkd_customersalesorders joins on salesorder_id for comparison with the external pipeline. It is not CCC's numbering source. Match missing rows, NULL sequence and deliberate zero separately. Resolve historical customer ids through mergedinto, and read only after a completed reconcile when a consistent published grouping is required. See merges and splits.
Products and reference dimensions
A transaction SKU resolves through productskus.sku to products.id; NetSuite's item reference is ns_items.id. A product may have several SKUs, domains, translations and prices. Choose the SKU, domain and language before joining those children; an unqualified join multiplies product rows.
CCC retains product ids through the productids CID mapping across catalogue swaps. Do not assume all other auto-increment ids survive a replacement. Imported NetSuite ids and locally assigned ids have different owners; see products.
accounts.gl_account = gl_accounts.id maps a CCC account to the internal GL identifier, not the human account number. saleschannels.ns_saleschannel_id = ns_saleschannels.id maps CCC's reporting channel to the imported dimension. Missing mappings can remove rows from an INNER JOIN even when the transaction itself exists.
Traffic, discounts and ledger totals
Traffic is per date, domain, source and medium. Cart-discount applications are per order and cart discount, with currency retained. Neither is interchangeable with a transaction line. Aggregate to compatible grains before combining them with order data.
Ledger actual totals are per subsidiary, account and accounting period; extended totals add category and sales channel. Budgets use the plain dimensions but come from CSVs. See ledger listings before comparing these with transaction contribution.
Schema ownership and refreshes
app/jobs/install/install.php describes the clean-install schema for the tables it owns. It does not migrate existing tables and does not refresh all their data. Some catalogue, NetSuite reference and ledger-total tables are defined by their import or reset job instead. AGENTS.md, Models and database, lists the ownership exceptions.
A staged refresh builds _new, then renames it into place and keeps the previous copy as _old. The live table is not filled incrementally, but multi-table reads are not a snapshot, and the separate rename statements are not a transaction. Some swaps refuse a smaller replacement; the NetSuite reference jobs do not all have that check. _old is the previous copy, not a maintained backup. Ledger full resets rebuild in place, and customer reconciliation updates in place.
Related
- Field meaning, including the generated definitions readable without database access
- Consumption contracts
- Glossary, including table prefixes
- Sales orders
Evidence
Code-checked: table keys in app/jobs/install/install.php, transaction joins in app/models/salesorders.php and sibling getters, impact grouping and writes in app/jobs/calculate/invoices.php, and customer identity in app/models/customers.php. Catalogue joins and the CID map are in app/jobs/akeneo/getproducts.php and app/models/products.php; reference mappings are in app/models/accounts.php and app/models/saleschannels.php. Refresh ownership is recorded in AGENTS.md and implemented in the named import/reset jobs. This is a logical join guide, not a live schema dump.
- Type
reference - Status
active - Updated
2026-09-14 - Created
2026-09-13