Selva Earth · internal guide

How the sales data pipeline works

The short version: this is an ETL data pipeline. It extracts raw invoice data from Zoho Books and Xero, reshapes each line into a common Sales format, stores it in Grist, then enriches the rows with product SKUs so Grist can calculate acai usage.

The important distinction: the Python files are not usually the code running inside n8n. The n8n Code nodes run JavaScript. Some Python files contain the same business rules in a second implementation for backfills, audits, and maintenance.

The plain-language terms

ETL pipeline

Extract, transform, load. Read the source system, turn its data into our common shape, and load it into Grist.

Raw ingest

Save what Zoho or Xero actually sent, including the original item code and source line ID. Do not guess a SKU at this stage.

Enrichment or resolution

Use a separate step to match a raw item code or product description to our catalog SKU. This is where variant_sku_resolved is filled.

Upsert

Insert a new Sales row or update the existing row with the same invoice_line_id. This prevents daily pulls from creating duplicates.

The current workflow at a glance

The live sales workflow runs daily and pulls a rolling 45-day window. The three source branches run in parallel, then meet at the SKU-resolution step.

Source 1

Zoho Books MY

Invoices and line items from the Malaysia organisation.

Source 2

Zoho Books TH

Invoices and line items from the Thailand organisation.

Source 3

Xero

Invoices across connected Xero tenants, including Terra and Selva.

Extract

Fetch API data

n8n calls each accounting API for the rolling date window.

Extract

Fetch invoice details

Zoho invoice lists are followed by detail requests so the line items are complete.

Transform

Process line items

JavaScript turns different source shapes into the shared Sales row shape.

Load

Save to Grist

Grist upserts rows using an account-scoped invoice line key.

Enrich

Resolve Sales SKUs

Unresolved rows are fetched, matched, and patched with a resolved SKU.

Calculate

Grist formulas

Product-linked formulas derive values such as acai_used_kg.

The workflow is named Selva stock and sales (daily pull, rolling 45d). The separate customer-sync workflow is not the source of Sales rows.

What runs where

File	Runs where	Plain-language job
`process-zoho-my.code.js`	n8n Code node: Process Zoho	Turns MY Zoho invoice details into common Sales rows.
`process-zoho-th.code.js`	n8n Code node: Process Zoho1	Turns TH Zoho invoice details into common Sales rows.
`process-xero-invoices.code.js`	n8n Code node: Process Xero Invoices	Turns Xero invoice line items from all connected tenants into common Sales rows.
`process-resolve-sales-skus.code.js`	n8n Code node: Resolve Sales SKUs	Builds the list of SKU patches after the three Grist saves.
`sales_sku_resolver.py`	Python scripts and tests	Python implementation of the same resolver rules. Useful for manual repairs and audits. It is not automatically executed by an n8n Code node.
`resolve_sales_skus.py`	Manual Python catch-up	Reads unresolved Grist Sales rows, applies the Python resolver, and PATCHes the result back to Grist.
`backfill_zoho_sales_window.py` `backfill_xero_sales_window.py`	Manual Python recovery tools	Re-fetch source invoice lines when an n8n run missed or failed to save them.
`backfill_sales_account_id.py`	Manual Python repair tool	Repairs account IDs, account labels, and legacy invoice-line keys. It is not part of the normal daily n8n path.
`patch_selva_workflow.py` `patch_selva_sales_resolve_step.py`	Developer maintenance	Updates the local workflow export and injects JavaScript into the live n8n workflow when pushed. They do not process invoices themselves.

How Zoho raw inputs are handled

1. Fetch the invoice list

n8n fetches MY or TH invoices for the rolling date window, then splits the list into individual invoices.

2. Fetch invoice details

Each invoice detail response is read because the detail response contains the full line_items array.

3. Preserve source identity

line_item.sku becomes source_item_code and the initial variant_sku. line_item.line_item_id becomes source_line_id.

4. Attach the correct organisation

The MY and TH organisation lookups supply the account ID and account name. The current IDs are carried into each row, so the account is not inferred from the chart.

Zoho line item
  sku            → source_item_code, variant_sku
  line_item_id   → source_line_id
  name           → item_name
  description    → item_description
  quantity       → quantity_sold
  rate           → unit_price
  item_total     → total_value
  tax_amount     → item_tax_amount

How Xero raw inputs are handled

1. Get connected tenants

n8n first gets the connected Xero tenants. The tenant ID and tenant name travel alongside the invoice response.

2. Read each line item

The processor reads invoice.LineItems from every Xero invoice and emits one common Sales row per line.

3. Preserve source identity

LineItem.ItemCode becomes source_item_code and initial variant_sku. LineItem.LineItemID becomes source_line_id.

4. Batch the Grist writes

Xero rows are split into groups of 50 before saving. The current Grist HTTP node sends one request per item with one-second pacing and retries transient failures.

Xero line item
  ItemCode       → source_item_code, variant_sku
  LineItemID     → source_line_id
  Item.Name      → item_name
  Description    → item_description
  Quantity       → quantity_sold
  UnitAmount     → unit_price
  LineAmount     → total_value
  TaxAmount      → item_tax_amount
  tenantId       → account_id
  tenantName     → account

Why the raw SKU can be blank

Blank source SKU does not automatically mean a lost sale. Zoho and Xero sometimes send a description but no item code. The pipeline keeps the description and source line ID, marks the row for review, and attempts enrichment later. This is called a raw-ingest design: preserve first, resolve second.

The resolver checks in this order:

Use the accounting API's raw item code if present.
Use a legacy SKU if one already exists and is not UNKNOWN.
Extract a clear catalog code from the item description.
Match a normalized product name against the Products table and curated aliases.
If no safe match exists, leave the resolved SKU blank and set needs_sku_review = true.

How a row reaches the chart

Raw fields

Source line saved

Account, invoice, date, quantity, amount, source code, and source line ID are stored.

Resolved field

SKU linked

variant_sku_resolved identifies the Products row used for product calculations.

Formula output

Acai kg calculated

Grist uses the linked product data and quantity to calculate acai_used_kg.

If the SKU is unresolved, the Sales line can still exist and its quantity can still be counted, but product-based measures such as acai kg may be zero or incomplete. That is why account mapping and SKU resolution are separate checks.

Current safety rules

45 daysRolling source window

5 triesGrist write/read retries

1 secXero write pacing

The workflow runs daily and is active.
Grist write failures are hard failures, so the error workflow can alert instead of silently continuing.
The unresolved-Sales fetch also fails visibly. It cannot quietly turn a resolver failure into a successful-looking run.
The error workflow remains attached to the sales workflow.
Source fields are not overwritten by the resolver. The resolver writes variant_sku_resolved and needs_sku_review.

One-sentence summary

Zoho and Xero are raw sources; n8n JavaScript translates their different invoice formats into the same Grist Sales format; Python tools repair and audit the data; and a separate SKU-resolution step links descriptions to products before the chart calculates acai usage.