Database Archaeology: Turning Legacy Systems into Self-Explaining Maps

The Problem Hiding in Every Legacy Database

Every database that has been in production for more than a few years is basically an ancient city. Layers of structures built by generations of developers, many of whom vanished without leaving a map. Tables record the data. SQL records the logic. Comments record the hopes. And nobody currently on the team can tell you why a specific view filters out certain customers — or what would break if you dropped a column that "looks unused."

The standard tools for understanding a database — SchemaSpy, ER diagrams, dbt lineage graphs — show you the skeleton. They show you what's there. They don't tell you what it does, why it does it, or what's suspicious about it.

What if you could build an automated archaeologist that walks through the ruins and says: "Here is what this temple probably did, and by the way, this column looks like it was added during a panic in 2017."

The Core Insight: LLMs Should Explain, Not Decide

The key architectural decision is this: the LLM should never be the engine. It should be the narrator.

Most "AI for databases" tools today try to use the LLM to parse SQL, extract dependencies, and determine correctness. That's fragile — LLMs are pattern recognizers, not compilers. They hallucinate joins, miss edge cases, and can't be trusted with structural correctness.

The right split is:

• Rule engine → deterministic checks (finds the problems)
• LLM → reasoning and explanation (explains why they matter)

The rule engine detects: LEFT JOIN without WHERE filter. The LLM explains: "This view joins orders to customers without filtering for active customers, which means deleted customer records may appear in downstream reports."

The result feels magical to developers — but it's grounded in actual structural analysis, not AI vibes.

The 4-Layer Architecture

The system is built as four layers, each with a clear boundary:

Layer 1 — Discovery (deterministic extraction)

This layer extracts raw metadata from databases and code:

• SQL Server: sys.objects, sys.sql_modules, dependency trees, foreign keys, columns, proc parameters
• C#: parsed with Roslyn — controllers, services, repositories, Dapper/EF calls, route mappings
• JavaScript/TypeScript: parsed with tree-sitter or Babel AST — fetch/axios calls, route ownership, dataset bindings
• Java: parsed with JavaParser — JDBC calls, repository/service/controller links

This layer never touches the LLM. It's pure structural extraction.

Layer 2 — Normalization (the canonical graph)

All findings are converted into one shared model: nodes and edges. Every object — whether it's a SQL table, a C# repository method, or a JavaScript fetch call — becomes a node. Every relationship (calls, reads from, writes to, joins to, selects column, invokes proc) becomes an edge.

This is where the product becomes stable. Without this layer, you just have disconnected scanners.

Layer 3 — Rule Engine (hard checks, no guessing)

Deterministic rules catch the problems that matter:

• SELECT * in persisted views
• Unused procedure parameters
• Non-SARGable predicates (LIKE '%text')
• Comment/code mismatch (the comment says "returns all active customers" but the SQL filters WHERE status = 'P')
• Views that narrow enum values inconsistently
• Objects with no inbound references (dead code)
• Circular dependencies
• Endpoints calling deprecated procs

This is where trust comes from. These checks are deterministic — they either fire or they don't. No hallucination.

Layer 4 — Reasoning (grounded AI)

The LLM sits on top and only sees structured packets, not raw chaos:

{
  "object": "sp_monthly_revenue",
  "type": "StoredProcedure",
  "depends_on": ["vw_valid_transactions"],
  "called_by": ["RevenueRepository.GetMonthlyRevenueAsync"],
  "rules_triggered": [
    "Enum mismatch on status filter",
    "Unused parameter @IncludeSettled"
  ]
}

The LLM then produces: plain-English purpose, likely business intent, impact summary, suspicious logic explanation, and a confidence score. The LLM should explain, not decide.

The Killer Feature: End-to-End Lineage Tracing

The most valuable capability isn't browsing a graph — it's tracing a full path through the system:

UI page → calls API endpoint → invokes C# repository → executes stored procedure → reads view → filters table

A user asks: "Why does this dashboard exclude some customers?"

The engine returns the full chain: the UI page calls an endpoint, the endpoint invokes a repository method, the repository executes a stored procedure, the proc reads a view, the view filters status = 'COMPLETE', and the source table also contains status = 'SETTLED'. The likely exclusion occurs in the view — revenue may be underreported.

That is far more valuable than a pretty dependency graph. It's a forensic trace from the symptom to the root cause.

Visualization: From Graph to Command Center

The dependency graph is only one panel. The real UI should feel like a command center for the organism, not a generic node-link diagram.

• Semantic zoom: At far zoom, you see domains as glowing clusters. At medium zoom, schemas and repositories. At close zoom, exact tables, procs, methods, and issue markers.
• Animated lineage traces: When a user asks "where does this number come from?", the system animates a route from screen to table. That one animation sells the whole product.
• Impact shockwave: Drop a column or select a proc, and downstream dependencies light up outward like a ripple. Immediate visual feedback on blast radius.
• Mode overlays: The same graph, different lenses — structure, risk, logic, narrative.
• Focus mode: Click a node and everything else dims. Only the path-in and path-out stay bright.

Evidence Everywhere

Every narrative the system produces points back to: object name, file path, method name, line span, SQL excerpt or AST rule. Without evidence, the tool becomes "AI vibes." With evidence, it feels like forensics. That distinction is what separates a toy from a product.

Try the Live Demo

The discovery graph below is interactive. Click any object to inspect its dependencies, columns, and rule-engine findings. Use the filter buttons to isolate tables, views, or procedures. The search box finds objects by name.

Open the Discovery Demo in a new tab

What a First Engagement Looks Like

This isn't a plug-and-play SaaS tool. A discovery engagement runs on your infrastructure — your SQL Server, your code repositories, your network. Here's what the first conversation actually involves:

1. Access assessment — we review what systems are in scope (SQL Server instances, C# solutions, JS/TS repos, Java projects) and what authorizations are needed: read access to system catalogs, source code repository access, and network access to the relevant environments.
2. Infrastructure planning — the discovery engine is deployed on your systems. Depending on your stack, that's an IIS-deployed service, a Visual Studio-integrated tool, or a local service running on a development server. We plan where the scanner runs, where the graph is stored, and how the reasoning layer connects.
3. Scope definition — we identify which databases and codebases to include in the first scan, which rules to enable, and what questions your team wants answered.
4. Initial scan — the discovery engine extracts the metadata graph, runs the rule engine, and produces the first system map with findings and narratives.
5. Review session — we walk through the graph together, trace lineage paths, review flagged issues, and prioritize what to address first.

The first engagement typically takes 1–2 weeks from access setup to initial results. The discovery engine then stays running on your infrastructure — subsequent scans are faster, drift detection kicks in, and the graph becomes a living map of your system.