Cross-system join

What it is

In a database, a join combines rows from two tables. The PostgreSQL documentation describes join queries as ones that access multiple tables at once, pairing rows by an expression such as city = name. A cross-system join does the same thing across separate products, where there is no shared database and no shared key. The accounting system calls the customer "Acme Ltd" with one ID; the PSA calls them "ACME Limited" with another.

Why it matters

The useful business questions span systems. Which clients are overdue on an invoice and have an open urgent ticket? No single vendor can answer it. An agent without a join has to pull every row from both systems into its context and match them itself, which costs tokens, hits context limits and invites mistakes.

How it works

1. Fetch each side with a bounded number of rows or pages.
2. Resolve identity: map each record to a shared entity, such as a company, using normalised names, domains or IDs.
3. Match, exactly or with a similarity threshold for near-misses.
4. Filter and aggregate: where, group by, sums and counts.
5. Return the answer, not the rows.

Example

ask: which clients are 30+ days overdue on an invoice and have an open urgent ticket?

4 clients · $18,340 overdue · 6 tickets · 1 call billed · 236 ms

This is an illustrative run: invoices fetched live from the accounting system, tickets served from cache, joined on customer.

Common pitfalls

• Name drift. "Acme Ltd" and "ACME Limited" do not match exactly; fuzzy matching helps but can pair the wrong records if the threshold is loose.
• Partial data. A join over the first page of each list silently misses customers.
• Rows in the context. Passing thousands of records to the model to match is slow, expensive and error-prone.
• Any-field joins. Two systems only join well on an entity both actually describe.

How actionlake handles it

ask takes fetch steps and one join step. The join names a shared entity, such as crm.company, crm.contact, finance.invoice, service.ticket or work.project, not a pair of raw fields, and supports where, group_by and select with sum, count, min, max and avg. match: "fuzzy" with a threshold (0.85 by default) catches near-misses. The join runs in memory on our side and is free; you pay only the upstream calls of the fetches, and cached fetches cost nothing. The rows never enter the model; only the answer does. Set max_rows or max_pages on each fetch to bound what a question can spend. See the platform page.