idtrace

Trace a record ID across multiple databases.

Give idtrace one starting point — a table, a column, a value — and it finds every related record across all of your configured database servers (PostgreSQL and MySQL today). It follows declared foreign keys inside each database and bridges between databases with an explicit, documented naming heuristic, because foreign keys can't cross servers but your data does.

Built for:

• Support engineers — "what actually happened to transaction 12345?" One command shows the order, its items, the payment, and the shipment events sitting in a completely different database.
• GDPR / deletion-impact analysis — before you delete a user, see every row across every service database that hangs off their records.
• Microservice data forensics — when each service owns its own database and the only thing connecting them is an order_id column and hope.

idtrace is read-only by design: it only ever issues parameterized SELECT statements, and it additionally asks the server for a read-only session where the driver supports it.

Install

go install github.com/peterphoenix/idtrace/cmd/idtrace@latest

(Homebrew tap and prebuilt release binaries are on the roadmap.)

Quickstart: the two-database demo

The repo ships a mini e-commerce system split across two servers, the way real microservices split it: PostgreSQL owns the shop (users, orders, order_items, payments), MySQL owns fulfillment (shipments, shipment_events). Nothing but a column named order_id connects them.

docker compose -f demo/docker-compose.yaml up -d --wait
go build -o idtrace ./cmd/idtrace
./idtrace trace --config demo/idtrace.yaml --table orders --column id --value 1

Real output — note the trace crossing from the shop (PostgreSQL) database into the shipping (MySQL) database over a heuristic edge:

trace orders.id = 1  (11 row(s) found)

[shop] orders id=1  status=shipped total=59.90 created_at=2026-07-11 22:40:30.673837 +0900 WIT
├─ [shipping] shipments id=1  status=in_transit carrier=DHL tracking_number=DHL-4711-XY  via orders.id <- shipments.order_id (heuristic)
│  ├─ [shipping] shipment_events id=1  occurred_at=2026-07-11 13:40:34 event=label_created location=Berlin DE  via shipments.id <- shipment_events.shipment_id
│  ├─ [shipping] shipment_events id=2  occurred_at=2026-07-11 13:40:34 event=picked_up location=Berlin DE  via shipments.id <- shipment_events.shipment_id
│  └─ [shipping] shipment_events id=3  occurred_at=2026-07-11 13:40:34 event=in_transit location=Leipzig Hub DE  via shipments.id <- shipment_events.shipment_id
├─ [shop] order_items id=1  sku=KEYB-MECH-87 price=49.90 quantity=1  via orders.id <- order_items.order_id
├─ [shop] order_items id=2  sku=CABLE-USBC-2M price=5.00 quantity=2  via orders.id <- order_items.order_id
├─ [shop] users id=1  name=Ada Lovelace email=ada@example.com created_at=2026-07-11 22:40:30.673092 +0900 WIT  via orders.user_id -> users.id
│  └─ [shop] orders id=2  status=pending total=12.00 created_at=2026-07-11 22:40:30.673837 +0900 WIT  via users.id <- orders.user_id
│     └─ [shop] order_items id=3  sku=MOUSE-TRK-01 price=12.00 quantity=1  via orders.id <- order_items.order_id
└─ [shop] payments id=1  status=captured amount=59.90 method=card  via orders.id <- payments.order_id

Tear down with docker compose -f demo/docker-compose.yaml down -v.

Commands

idtrace trace

idtrace trace --table orders --column id --value 12345 \
    [--config idtrace.yaml] [--max-depth 5] [--max-rows 1000] \
    [--timeout 30s] [--format tree|json|dot] [--no-heuristics] \
    [--pg-schema public]

Finds the seed row(s) where table.column = value in every connection that has such a table, then breadth-first expands in both directions: parents the row references, and children that reference it — across FK edges and heuristic cross-database edges. Rows are deduplicated by (connection, table, primary key), so cyclic schemas are safe. Traversal is bounded by --max-depth (default 5 hops) and --max-rows (default 1000 rows total); hitting either cap prints a warning so you know the picture is incomplete.

Every lookup carries a per-query timeout (--timeout, default 30s, 0 disables). A single slow or locked table can't hang the whole trace: its lookup times out, becomes a warning, and the rest of the trace completes. Ctrl-C cancels in-flight queries server-side instead of abandoning them.

idtrace schema

Prints what a trace would follow, without touching any data rows: every connection's tables, declared foreign keys, and the heuristic cross-database links idtrace inferred. Great for sanity-checking the graph first. Real output from the demo:

connection shipping (mysql)
  shipment_events (5 columns, pk: id)
    fk shipment_events(shipment_id) -> shipments(id)
  shipments (6 columns, pk: id)

connection shop (postgres)
  order_items (5 columns, pk: id)
    fk order_items(order_id) -> orders(id)
  orders (5 columns, pk: id)
    fk orders(user_id) -> users(id)
  payments (5 columns, pk: id)
    fk payments(order_id) -> orders(id)
  regions (3 columns, pk: country, code)
  users (4 columns, pk: id)
  warehouses (3 columns, pk: id)
    fk warehouses(country,code) -> regions(country,code)

heuristic cross-database links (disable with --no-heuristics):
  shipping.shipments.order_id -> shop.orders.id

--format dot renders the relationship graph for Graphviz; --format json dumps it as data.

idtrace mcp

Stub for now — MCP server mode (so agents can run traces as a tool) ships in v0.2.

Configuration

idtrace reads idtrace.yaml from the current directory by default (--config to override):

connections:
  - name: shop                # unique label used in output
    driver: postgres          # postgres | mysql
    dsn: postgres://idtrace:${SHOP_DB_PASSWORD}@db.internal:5432/shop
    schema: public            # optional, postgres only (--pg-schema overrides)

  - name: shipping
    driver: mysql
    dsn: idtrace:${SHIPPING_DB_PASSWORD}@tcp(mysql.internal:3306)/shipping

• ${VAR} references in DSNs are expanded from the environment; an unset variable is a hard error (never a silently broken DSN).
• PostgreSQL DSNs accept both URL and key=value form (pgx). The introspected schema defaults to public; set schema: per connection or --pg-schema globally to override.
• MySQL DSNs use the go-sql-driver format; the database in the DSN is what gets introspected.

How the cross-database heuristic works

Foreign keys cannot span database servers, so links between databases only exist by naming convention. idtrace makes that convention explicit. For every table T with a single-column primary key pk in connection B, a column c in a different connection A becomes a heuristic edge A.c -> B.T.pk when either:

1. Prefixed-key match: c is named <singular(T)>_<pk>. Example: shipments.order_id -> orders.id ("orders" singularizes to "order", pk is "id").
2. Exact-name match: c has exactly the same name as pk, and pk is not a generic name (id, uuid, guid, pk, key) — so two unrelated id columns are never linked.

Singularization is deliberately naive (companies -> company, addresses -> address, orders -> order) — when it guesses wrong, the worst case is a missing edge, never a bogus join on the wrong type of key. Heuristic hops are always labeled (heuristic) in the output, drawn dashed in DOT, and can be disabled wholesale with --no-heuristics. Within a single database, declared foreign keys are the only source of truth; heuristics apply across connections only.

Safety

• Never writes. The tool issues only SELECT statements: against information_schema for discovery and against your tables for row lookups. There is no code path that executes anything else.
• Parameterized everywhere. The traced value is always bound as a query parameter — never interpolated into SQL. Table and column names are validated against introspection results and quoted per driver, so neither user input nor exotic schema names can inject SQL.
• Read-only sessions as defense in depth. DSNs are automatically extended with default_transaction_read_only=on (PostgreSQL) and transaction_read_only=1 (MySQL), so even a hypothetical bug could not write. For belt and suspenders, point idtrace at a read replica with a read-only database user.
• Bounded. --max-depth and --max-rows cap traversal, every lookup carries a LIMIT, and each query runs under a --timeout deadline (default 30s) so a locked table can never hang a trace.

Output formats

• tree (default): the human-readable tree shown above. Colorized on a TTY, plain when piped (or with NO_COLOR=1).

• json: the full result — every row with all column values, depth, parent link, and the edge it was found through. Excerpt:

  {
    "seed": { "table": "orders", "column": "id" },
    "value": "1",
    "rows": [
      {
        "connection": "shipping",
        "table": "shipments",
        "pk": "id=1",
        "values": {
          "carrier": "DHL",
          "created_at": "2026-07-11 13:40:34",
          "id": 1,
          "order_id": 1,
          "status": "in_transit",
          "tracking_number": "DHL-4711-XY"
        },
        "depth": 1,
        "parent": "shop/orders/id=1",
        "via": "orders.id <- shipments.order_id",
        "via_kind": "heuristic"
      }
    ]
  }

• dot: a Graphviz digraph of the traced records, clustered per connection, heuristic edges dashed:

  idtrace trace --table orders --column id --value 1 --format dot | dot -Tsvg -o trace.svg

Limitations (v0.1)

• Composite foreign keys are shown by idtrace schema (the demo's warehouses -> regions FK is one) but not followed during a trace.
• Tables without a primary key are traced, but deduplicated by their full column values.
• One PostgreSQL schema is introspected per connection (add the same server twice with different schema: values to span schemas).
• Foreign keys that point outside the introspected scope — another PostgreSQL schema, or another MySQL database on the same server — are ignored, since their target tables are not part of the graph.

Roadmap

• BigQuery driver — the tracer is written against a small RowSource interface precisely so warehouse backends can plug in.
• MCP server mode (idtrace mcp) — expose trace and schema as MCP tools for AI agents.
• Homebrew tap + prebuilt releases.

Development

go test ./...    # unit tests run against an in-memory schema/row source, no DB needed
go vet ./...
docker compose -f demo/docker-compose.yaml up -d --wait   # integration playground

License

MIT © 2026 Peter Phoenix