Prospect

Most large systems outlive their documentation. The schema declares almost nothing, the join keys live in application code, and nobody left knows which of the three thousand tables still matter. Prospect answers that from the database itself.

The idea

Let the database tell you about itself by watching it change. Capture it in time-ordered snapshots, run a real business process, snapshot again. What changed is the strongest signal for what is alive, what belongs together, and how a process moves through the data.

What it recovers

• Relationships the schema never declared, inferred from value overlap and from SQL parsed out of views and procedures, merged with any declared foreign keys. Every edge carries a source and a confidence.
• ERDs, scoped rather than unreadable: a table’s neighbourhood, a subsystem, the tables one run changed, or the whole model.
• Subsystems, by clustering the relationship and co-change graphs, plus the process modules a real run actually moved.
• Activity and usage: live, unchanged, baseline-only; read, never read, unknown.
• Change explanations: which tables changed, the mix of inserts, updates and deletes, before-to-after row detail, and the causal core separated from ambient churn.
• A browsable data dictionary, a static site with a page per table, schema and module, zoomable ERDs and scrubbed samples.

Read-only on site, analysed off site

The capture half runs inside the client’s environment and issues SELECTs only. No inserts, updates, deletes or schema changes on business data, and no ODBC driver to install.

Before anything leaves, a PII scan produces a reviewable map, and a deterministic, key-preserving scrub replaces personal data with consistent fakes. Keys are left untouched so the joins still work. The export refuses to ship a bundle where a mapped table was never scrubbed. Only the scrubbed bundle leaves; the analysis needs no database and no network.

Scale

Prospect was built against a production database of roughly 480 GB and 3,700 tables, and runs against any MSSQL instance. A survey takes minutes, a baseline of the keyed tables minutes more, and an incremental snapshot after a process seconds to a minute.

Where a migration or a platform move is on the table, this is the map that turns it from guesswork into work: which tables are live, which joins exist in practice, what a process depends on, and which bulk is deadwood.