ERP integration

Most multi-shop companies already run an ERP, and what they want from the Transactions API is for the ERP to see the sales without anyone typing them in.

Architecture

Transactions API        your integration                 ERP
────────────────────────────────────────────────────────────────
GET /sales      ──►   staging table (upsert on uuid)
GET /payments   ──►   staging table (upsert on uuid)
                          │
                          ▼
                      transformation
                      (shop, payment method,
                       tax mapping)
                          │
                          ▼
                      outbound queue  ──►  ERP API

The piece not to skip is staging. Writing straight from the API to the ERP looks simpler until the first day the ERP is down: without staging, that day is lost and has to be rebuilt by hand.

Step 1. Incremental sync

window_from = cursor_state["last_synced_at"] - timedelta(minutes=15)
window_to = datetime.now()

The 15-minute overlap replays a few rows, which the upsert absorbs, and protects

Step 2. Staging with upsert

CREATE TABLE staging_sales (
  uuid      BIGINT PRIMARY KEY,
  shop_code    INTEGER       NOT NULL,
  created_at    TIMESTAMP     NOT NULL,
  updated_at   TIMESTAMP     NOT NULL,
  total        NUMERIC(14,2) NOT NULL,
  currency     CHAR(3)       NOT NULL,
  payload      JSONB         NOT NULL,
  erp_status   TEXT          NOT NULL DEFAULT 'pending',
  erp_sent_at  TIMESTAMP,
  erp_document TEXT
);

Two columns that look redundant and are not:

• payload keeps the raw response. The day the ERP needs a field you discard today, you have it without re-reading a year of history.
• erp_status turns "we sent it" into queryable data.

When a sale changes it comes back with a new updated_at, and the upsert must reopen the delivery:

ON CONFLICT (uuid) DO UPDATE SET
  total      = EXCLUDED.total,
  updated_at = EXCLUDED.updated_at,
  payload    = EXCLUDED.payload,
  erp_status = 'pending'
WHERE staging_sales.updated_at < EXCLUDED.updated_at;

Step 3. Map shops and payment methods

The ERP knows nothing about Bistro shop_code values or payment methods. That mapping belongs in a configuration table, never hardcoded: opening shop 1011 next month should be a row, not a deployment.

An unmapped shop or method leaves the row as erp_status = 'unmapped' and raises an alert. Do not invent a default: a sale posted to the wrong cost centre is worse than one not posted at all, because nobody goes looking for it.

Step 4. Push to the ERP

for row in db.fetch_pending(limit=200):
    try:
        document = erp_client.create_invoice(transform(row))
    except ErpTemporaryError:
        continue                       # stays pending for the next run
    except ErpValidationError as error:
        db.mark(row["uuid"], status="rejected", detail=str(error))
        continue

    db.mark(row["uuid"], status="sent", document=document["id"])

Three terminal states and no grey zone: sent, rejected, or still pending.

Step 5. Daily controls

What not to do

• Do not purge staging.
• Do not use the ERP as the record of what you synced.
• Do not sync one sale at a time in real time: The Transactions API is paginated and built for batches.
• Do not run several instances under the same user: they share the rate limit. See