shadowjoin

# shadowjoin

**Measure the matches your exact join is silently dropping.**

Your pipeline joins two sources on a name, an email, a SKU, a company. It matches
what matches. Everything else just... isn't there. No error, no exception, no
alert — the query returns, the dashboard renders, and a quarter of the rows that
should have matched quietly don't.

shadowjoin runs a shadow matcher alongside your real join key and tells you how
many matches you are losing, which normalization rule would recover each one, and
— if you have any ground truth at all — proves the recoveries are real.

```
npx shadowjoin scan \
  --left  customers.csv   --left-key  company_name \
  --right crm_export.csv  --right-key account_name
```

[![shadowjoin measuring a country-name join: 205 of 249 matched exactly, which looks fine, while five real matches were being dropped including Vietnam, spelled Viet Nam by the UN](assets/brag.gif)](https://github.com/harsh-chandak/shadowjoin/blob/main/assets/brag.mp4)

<sub>Fourteen seconds, no sound. Every figure in it is verbatim CLI output from
the committed example below — [higher-resolution
version](https://github.com/harsh-chandak/shadowjoin/blob/main/assets/brag.mp4).</sub>

The progress output above is real: shadowjoin shows what it is doing while it
works. All of it is suppressed automatically when stdout is not a terminal, so
piping to a file, to `jq`, or running in CI produces byte-identical output to
before — and there is **no postinstall script**, because a tool you point at a
production database has no business running code at install time.

## What it found on public data

Joining the **S&P 500 constituent list** against **NYSE + Nasdaq listings** on
company name. Both files carry ticker symbols, so every recovered match can be
checked rather than believed:

```
  left rows 503   right rows 6,709

  exact join matches                1   0.2%
  recovered by shadow join        428   85.09%
  still unexplained                74

  LOWER BOUND ON LOST MATCHES: 85.09% of left rows
  verified against ground truth: 419 correct, 1 wrong, 8 ambiguous  (precision 99.76%)

  rules worth adopting, in order (greedy by marginal recovery):
  1.  strip security/listing boilerplate (common stock, ADS, ...)   +68       68
  2.  strip corporate suffixes (inc, corp, ltd, plc, gmbh ...)     +319      387
  3.  strip punctuation                                             +33      420
  4.  sort tokens (order-insensitive match)                          +4      424
  5.  remove all whitespace                                          +1      425
```

The exact join found **one** match. There were **four hundred and twenty-eight**,
and 419 of them are confirmed correct by ticker.

Reproduce it yourself — the data ships with the repo:

```bash
git clone https://github.com/harsh-chandak/shadowjoin && cd shadowjoin
npm install && npm run build
node dist/cli.js scan \
  --left  examples/sp500.csv    --left-key  Security --left-id Symbol --left-truth Symbol \
  --right examples/listings.json --right-key name    --right-id symbol --right-truth symbol
```

### A join you would actually ship

The example above is deliberately unflattering — a short index name against a
full security description. Here is the realistic case.

**Unicode CLDR country names** (what your UI displays) joined to **UN official
country names** (what your reference data uses), on the country name, with ISO
3166 alpha-3 as ground truth. That is a join people write every week.

```
  exact join matches              205 / 249   82.33%
  recovered by shadow join          5          2.01%   precision 100%
  still unexplained                39

  rules worth adopting:
  1.  & -> and                 +4
  2.  remove all whitespace    +1
```

It looks healthy. 82% matched, no errors, nobody investigating. And it is
silently dropping `Antigua & Barbuda`, `Trinidad & Tobago`,
`São Tomé & Príncipe`, `Turks & Caicos Islands` and `Vietnam` — the last one
because the UN spells it `Viet Nam`.

Two things this example shows that the first one does not:

**Applying every rule finds nothing.** The full rule set recovers zero here; the
two-rule subset recovers five. Over-normalization collapses keys that should stay
distinct, and shadowjoin names the better subset instead of letting you assume
more rules is better.

**It separates mechanical misses from semantic ones.** The 39 it cannot explain
are not normalization problems at all:

```
  Vatican City    vs  Holy See
  North Korea     vs  Democratic People's Republic of Korea
  Cape Verde      vs  Cabo Verde
  Bosnia          vs  Bosnia and Herzegovina
  Hong Kong       vs  China, Hong Kong Special Administrative Region
```

No string transform recovers those. They need an alias table or a real entity
resolution library. Knowing *which* of your misses are mechanical and which are
semantic is the difference between a one-line fix and a project.

```bash
node dist/cli.js scan \
  --left  examples/countries_ui.csv        --left-key  country --left-truth  iso3 \
  --right examples/countries_reference.csv --right-key country --right-truth iso3
```

> That particular join is deliberately unflattering — a short index name against a
> full security description. It is a worked example of the *measurement*, not a
> claim that anyone ships that join. Point it at your own data to get a figure
> that means something about you.

## The number is a floor, not an estimate

You cannot compute recall without labels. The entity-resolution literature calls
the metric **pair completeness**, and it assumes an oracle you do not have in
production.

So shadowjoin reports a **lower bound**: matches the shadow matcher found that
exact matching missed. The shadow matcher has unknown recall of its own, so the
true loss is *at least* this large and may be larger. Every figure ships with
sample pairs so a human can verify them by hand, and when ground truth is
available on both sides, the report carries a precision figure too.

This is deliberately the weaker claim. It is also the one that survives being
questioned.

## Why not just ask an LLM to check?

For a one-off look at a small table, do that — it is faster and this tool is
redundant. It stops working when:

- **You need the same number twice.** An agent writes different SQL every run, so
  you cannot tell whether the pipeline changed or the query did.
- **You need rules nobody thought of.** Asked "is this join lossy," an agent tries
  `lower()` and `trim()`. It does not systematically try NFKD folding, diacritics,
  leading zeros, corporate suffixes, token reordering or listing boilerplate. The
  exhaustive rule set *is* the product.
- **You need it in CI.** `--max-loss` exits non-zero. "Ask Claude" has no exit code.
- **Exploratory SQL on production is expensive.** This does hash joins on
  precomputed keys, under a statement timeout, in a read-only transaction.

## Install

```bash
npm install shadowjoin      # or run it with npx, no install
```

Zero runtime dependencies. `pg` and `@modelcontextprotocol/sdk` are optional
peers, pulled in only if you use the Postgres adapter or the MCP server.

## Use it

### CLI

```bash
# CSV or JSON, no database involved
npx shadowjoin scan --left a.csv --left-key name --right b.json --right-key title

# with ground truth on both sides, so recoveries are verified
npx shadowjoin scan \
  --left  a.csv --left-key  name  --left-truth  customer_id \
  --right b.csv --right-key title --right-truth customer_id

# straight at Postgres
npx shadowjoin scan \
  --left  'postgres://ro@host/db' --left-table  orders    --left-key  customer_name \
  --right 'postgres://ro@host/db' --right-table customers --right-key name

# in CI: fail the build if the floor climbs above 2%
npx shadowjoin scan --left a.csv --left-key name --right b.csv --right-key name --max-loss 2

# MySQL and SQLite work the same way
npx shadowjoin scan \
  --left  'mysql://ro@host/db' --left-table  accounts --left-key  company_name \
  --right ./reference.sqlite   --right-table vendors  --right-key name

npx shadowjoin rules     # list the normalization rules
```

**Sources:** CSV, JSON, Postgres, MySQL and SQLite. SQLite uses Node's built-in
`node:sqlite` (Node 22.5+), so it needs nothing installed; Postgres and MySQL
need `pg` or `mysql2`, which are optional peers.

**Anything else** works through the library in three lines, because the core
takes two arrays rather than a connection:

```js
import { scan } from "shadowjoin";
const [rows] = await anyDriver.query("SELECT id, company_name FROM accounts");
const left = rows.map((r) => ({ id: String(r.id), key: r.company_name }));
scan(left, right);
```

### Library

```js
import { scan, normalize, rulesByIds, toSql } from "shadowjoin";

const report = scan(leftRows, rightRows);        // rows are { id, key, truth? }
console.log(report.lowerBoundLossPct, report.stack);

// adopt the recommended rules in your own pipeline
const rules = rulesByIds(report.stack.map((s) => s.rule));
const key = (value) => normalize(value, rules);
toSql("company_name", rules);                     // the same thing as Postgres SQL
```

### Claude Code plugin (easiest)

One command installs the skill and the MCP server together, and prompts for your
config path:

```bash
claude plugin marketplace add harsh-chandak/shadowjoin
claude plugin install shadowjoin@harsh-chandak
```

Also listed in the [official MCP registry](https://registry.modelcontextprotocol.io)
as `io.github.harsh-chandak/shadowjoin`.

### MCP server

Lets an agent measure a join, look at real examples, propose a fix, and then
**prove the fix worked** — a loop it cannot fake, because the measurement is
not something it writes.

```json
{
  "mcpServers": {
    "shadowjoin": {
      "command": "npx",
      "args": ["-y", "-p", "shadowjoin", "-p", "@modelcontextprotocol/sdk", "shadowjoin-mcp"],
      "env": { "SHADOWJOIN_CONFIG": "/abs/path/to/shadowjoin.config.json" }
    }
  }
}
```

```json
{
  "source