Documentation / Experiences

ERP & Bank Reconciliation

A full end-to-end walkthrough of reconciling an ERP against a bank or payment processor in Nexus — connect both systems as bridges, stage each side with a report, match in a Python pipeline, hold exceptions in the internal workspace, post governed write-backs, and schedule it — all inside your own Azure tenant with a complete audit trail and no warehouse.

Finance and operations teams lose days every close matching what the ERP says against what a second system shows — the bank statement, the payment processor, a subledger, or a partner's records. The usual fix is to export both sides to spreadsheets or stand up a warehouse and an ETL job, then reconcile stale copies. This page walks the Nexus pattern end to end: reconcile both systems live, in your own Azure tenant, with the matching logic in a real Python pipeline and any corrections written back through governed, audited adapters — no third platform in the middle.

It is written for controllers, FP&A, and ops engineers who own a recurring reconciliation and want it automated, repeatable, and audit-ready. It uses NetSuite and a Postgres bank ledger as the concrete example, but the shape is identical for any pair of supported connectors.

The business problem

Take a month-end cash reconciliation. The ERP records customer payments as they are applied. The bank records deposits and settlements as money actually lands. The two should agree — but in practice they drift: a payment is recorded in NetSuite before it clears, a bank fee nets against a deposit, a reference number is transposed, a refund posts on only one side. Someone has to find every gap, explain it, and either correct the ERP or flag it for the bank.

Done by hand, this means two CSV exports, a VLOOKUP, and a lot of judgment that leaves no trail. When someone asks in an audit "how did you decide these two lines matched?", the answer is a spreadsheet nobody kept. The goal here is a reconciliation that runs on a schedule, matches deterministically, records every exception with a reason, posts corrections under RBAC, and leaves a reviewable history of exactly what it did.

The systems and the building blocks

Layer	Building block	Role in this reconciliation
Source of truth (ERP)	NetSuite bridge	Read applied payments; write corrections back
Counter-system	Postgres bridge	Read cleared bank transactions
Staging read	Report	Pull both sides at query time, live, no copy
Matching logic	Pipeline (`async def run(ctx)`)	Deterministic match + exception detection
Exception state	Internal workspace	Tenant-private Postgres to hold results between runs
Governance	RBAC + audit	Who can run/write, and the trail of what happened
Automation	Scheduler	Fire it nightly with exactly-once cron

Nexus reaches each source where it lives. Nothing is copied into a Stingray service or a separate warehouse; the ERP is queried in NetSuite, the bank ledger in Postgres, and the only durable copy is the exception list you deliberately keep in the internal workspace.

Step 1 — Connect the two systems as bridges

Each source is a bridge: a configured, credential-isolated connection to one system. You install the adapter for your ERP and your counter-system, store credentials in the tenant's Key Vault, and confirm both are reachable. Credentials never leave your tenant, are resolved per call by the bridge layer, and are never embedded in a report or pipeline definition. A run can only reach the bridges its run identity is granted — a user's bank credentials can never resolve for another user. See Connections & Bridges and Secrets.

Before writing any logic, inspect both schemas with discover_schema (from the desktop client, the MCP, or the Excel add-in) so you author against real field names.

Step 2 — Stage both sides with one report

A Nexus report is a JSON definition that can query multiple bridges in one execution. Reports are read-only declarations — not executable code — so they are parameterizable and safe to share with non-admins, and every run is bounded by the caller's RBAC. Use one to stage the two record sets the pipeline will compare:

{
  "report_name": "ERP vs Bank — candidates",
  "parameters": [
    { "name": "since", "type": "string", "default": "2026-06-01" }
  ],
  "queries": [
    {
      "id": "erp",
      "system": "netsuite",
      "bridge_name": "NETSUITE.prod",
      "containers": ["transactions"],
      "collection": "payments",
      "fields": ["doc_id", "amount", "trandate", "memo"],
      "where": { "trandate": { "$gte": "{{parameters.since}}" } }
    },
    {
      "id": "bank",
      "system": "postgres",
      "bridge_name": "POSTGRES.bank",
      "containers": ["public"],
      "collection": "transactions",
      "fields": ["txn_id", "amount", "posted_at", "reference"],
      "where": { "posted_at": { "$gte": "{{parameters.since}}" } }
    }
  ]
}

The NetSuite where pushes down to SuiteQL; the Postgres where pushes down to SQL. Each source filters server-side and returns only the candidate rows — you are not dragging the whole ledger across the wire.

Step 3 — Match in a pipeline

A Nexus pipeline is a Python module with one entrypoint, async def run(ctx). It reaches every system through the injected ctx SDK — ctx.adapters, ctx.workspace, ctx.logger, ctx.secrets, ctx.files — so there are no driver imports and no credentials in code. Pipelines run under an AST-level sandbox (no exec/eval, no raw file or network I/O), so the matching logic is ordinary Python but the blast radius is constrained. See the Pipeline SDK.

Here the pipeline reads both sides, matches on reference plus amount, and records every unmatched or mismatched line with a reason:

# nexus:constraints max_read=20000 max_created=20000
async def run(ctx):
    erp = await ctx.adapters.netsuite.read(
        {"collection": "payments", "limit": 5000})
    bank = await ctx.adapters.postgres.read(
        {"collection": "transactions", "limit": 5000})

    bank_by_ref = {b["reference"]: b for b in bank}
    matched, exceptions = [], []

    for p in erp:
        b = bank_by_ref.get(p.get("memo"))
        if b and round(float(b["amount"]), 2) == round(float(p["amount"]), 2):
            matched.append({"doc_id": p["doc_id"], "txn_id": b["txn_id"]})
        else:
            exceptions.append({
                "doc_id": p["doc_id"],
                "amount": p["amount"],
                "reason": "no_bank_match" if not b else "amount_mismatch",
            })

    await ctx.logger.info("reconciled", matched=len(matched),
                          exceptions=len(exceptions))

    if exceptions:
        # Replace this run's exception set atomically.
        await ctx.workspace.query("DELETE FROM recon_exceptions")
        await ctx.workspace.insert("recon_exceptions", exceptions)

    return {"matched": len(matched), "exceptions": len(exceptions)}

A few things worth calling out for a skeptical reader:

The exceptions land in the internal workspace — a tenant-private Postgres database baked into Nexus, with its own managed identity and structurally isolated from Nexus's own operational tables. You get durable reconciliation state between runs without provisioning an external store. See Internal Workspace.
The # nexus:constraints line is defense-in-depth. If a bad filter or a source glitch tries to read or write far more rows than a reconciliation ever should, the run trips the cap instead of hammering NetSuite. It is a guardrail, not the primary control.
Return values are persisted with the run. The {"matched": …, "exceptions": …} summary shows up in run history, so you can chart exception counts over time.

Step 4 — Govern the write-back

When you are ready to post corrections, the same ctx.adapters.<name>.write(...) call applies updates back to the ERP. This is where governance earns its keep:

Writes require an explicit grant. Read access to the NetSuite bridge never implies write access; posting corrections needs a per-resource write grant on that bridge. See Roles & RBAC.
Writes run as the pipeline's bound identity, and every run captures the full structured log and — on failure — the complete traceback.
You test before you touch production. While developing, uql_test_pipeline does a dry run with write-interception: adapter writes are captured, not performed, so you see exactly what would change before anything hits NetSuite. See Monitoring Pipelines.

Because the match logic isolates its writes to a known record type (payment applications, say) via the targeted NetSuite Record API, corrections are explicit and auditable rather than a bulk statement nobody can review.

Step 5 — Schedule it

Declare a trigger and a timeout at the top of the file and the scheduler runs it for you, firing exactly once per scheduled tick even across multiple server replicas (cron firing uses a compare-and-swap claim plus cross-replica advisory locks):

# nexus:trigger scheduled 0 7 * * *
# nexus:timeout 300

For what the scheduler does and does not guarantee — cron firing is exactly-once, but there is no automatic run-level retry or dead-letter queue today — see Reliability & Operations. The honest pattern for a reconciliation is to make the run idempotent (as above, the workspace table is replaced each run), so a re-run after a failure simply recomputes the current truth.

Handling failure honestly

Reconciliations fail in boring ways: NetSuite rate-limits, the bank export is late, a reference format changes. Nexus gives you the primitives to cope, and it is worth being precise about them:

Adapter HTTP 429s are retried with backoff inside the adapter layer, so a transient NetSuite throttle usually rides through.
A run-level failure is not automatically retried. There is no dead-letter queue. You get the failed run, its full traceback, and its logs; you re-run it (idempotently) or fix the cause. The next scheduled tick will also try again.
Cancellation is best-effort. Requesting cancellation cooperatively stops an in-memory run or terminates the isolated worker; treat it as "requests cancellation," not a guaranteed kill.

Designing the pipeline to be idempotent is what turns these honest limits into a non-issue for reconciliation specifically.

The governance payoff

Concern	How Nexus handles it
No warehouse	Both systems are queried live in your tenant; no ETL copy to maintain
Audit trail	Every run, log line, and write is recorded; pipeline source is version-controlled per tenant
Safe changes	Dry-run testing intercepts writes; write-back requires an explicit RBAC grant
Data residency	Everything executes inside the customer's own Azure subscription
Access control	Reports are RBAC-bounded; adapters are credential-isolated per run identity
Repeatability	Idempotent design + exactly-once cron firing = a clean re-run any time

The result is a reconciliation that runs on a schedule, explains its exceptions, posts governed corrections, and leaves a complete, reviewable trail — without ever moving your financial data out of your tenant.