Twenty-one of the joins were invisible to the schema, so we extracted them from the code
Point a language model at a database schema and ask it to write SQL, and the thing it is least able to do is the thing it must do first: work out how two tables are actually joined in this system.
It can read the columns. It can read the declared foreign keys. What it cannot read
is the eight years of convention that decided Orders.representativeId refers to
EmployeeDetails.userId, with no constraint saying so, in code nobody has touched
since 2021.
So before building anything, we catalogued what the service really does.
The number that made the point
The extractor walked every raw SQL statement and every ORM read in the service and recorded the join conditions actually used.
Of 45 distinct join conditions, 24 are backed by a real foreign key. 21 are not.
Those 21 are load-bearing. Orders.representativeId = EmployeeDetails.userId.
DealerDetails.assignedRepresentative = UserDetails.userId. They run in production
every day, and they are completely invisible to anything reading the schema alone —
which includes every text-to-SQL tool, and every new engineer for their first
fortnight.
A model that does not know them will invent a join, and an invented join usually runs. It returns rows. They are the wrong rows, in a plausible quantity, and nothing errors.
Evidence, graded rather than asserted
Not every observed relationship deserves equal trust, so each edge in the matrix carries what kind of evidence produced it:
| Evidence | Meaning |
|---|---|
fk | a real foreign key exists |
sql_join | the code joins them, and the actual ON condition is recorded |
orm_include | an ORM association connects them |
co_occurrence | the same statement mentions both, with no join captured |
That last row is the honest one. Two tables appearing in one query is not a join path, and a catalogue that presented it as one would be teaching the model to invent exactly the kind of relationship we were trying to stop it inventing. It is recorded, labelled as the weakest thing it is, and excluded from the join paths.
One table is additionally marked for recursive traversal: EmployeeDetails is
walked by WITH RECURSIVE on reportingTo in seven separate places. That single
relationship is the reporting hierarchy the entire permission model rests on, and
it is the sort of thing that is obvious to the team and invisible to everyone else.
Both query styles, or you describe half the system
The service reads two ways: ORM model calls for ordinary reads, and raw SQL for the recursive hierarchy work. A catalogue built from only one describes half the system and misleads about which tables exist.
Both are extracted, and ORM aliases are resolved through the model definitions to the physical table name — because the name in the code and the name in the database are frequently not the same word, and a model given the first will write SQL against a table that does not exist.
Verified against the database, not trusted
Every extracted table name is checked against information_schema on a live
database. Names that do not exist are dropped and listed separately.
This mattered more than expected, because it caught two classes of error the extraction produced on its own:
- CTE names read as tables. A
WITH chain AS (...)clause looks exactly like a table reference to a regex. - A pluraliser inventing tables. Applied to models whose declared name was
already plural, it produced
UsersesandDealerUserses.
Either would have become a confident wrong reference in the model’s context. A wrong table name is worse than a missing one: missing produces a question, wrong produces an answer.
Five names are still reported as unresolvable, and correctly so — CTEs assembled from interpolated variables, a schema qualifier, and two query aliases. They are in the coverage report rather than quietly dropped, because the useful thing about a catalogue is knowing where it stops.
The model cannot filter on a value it has never seen
A schema tells you Orders.current_status is an integer. It does not tell you that
2 means approved.
So the reference values are pulled live from the database for the tables where they matter — order statuses, roles, designations, regions, depots. Small enumerations are inlined into the prompt; larger dimensions are embedded and retrieved. Which tables qualify is a curated decision with a written reason each, not a row-count threshold, and anything small that is in neither list is reported as unclassified so the list cannot silently rot.
That exercise surfaced three traps, each of which would have produced confidently wrong answers:
Two order-status tables. Statuses is live and is what Orders.current_status
points at. Status is legacy, has zero inbound foreign keys, and its ids collide
with the live one — id 2 is Approved in Statuses and Cancelled in Status.
There are 2,667 orders sitting on id 2. Choose the wrong table and the system
reports every one of them as cancelled.
Two things called region. One is geographic, one is a business grouping, and they are unrelated taxonomies. A bare question about “region” is genuinely ambiguous, so the correct behaviour is to ask rather than to pick.
A constant that disagrees with the database. A designation lookup in the code says id 2 is one role; the live table says it is another. Every id in that constant is shifted by one. The database wins, and the code is now known to be wrong — which it was before anybody looked, silently, for whatever it is used for elsewhere.
The audit nobody asked for
One more thing fell out of it. Cataloguing every endpoint with its permission showed that of 266 GET endpoints, 66 carry an explicit permission check and 200 do not — all authenticated, but with the authorisation decision living inside the handler where it has to be read case by case.
Nobody set out to audit that. It is a by-product of writing down what the system does in a form precise enough to hand to a machine, and it is the strongest argument for doing this exercise even if you never build the chatbot.
What this is, and what it is not
It is a generated catalogue plus a prototype, and it has known edges. The table hints on each endpoint are file-scoped rather than call-graph-scoped, so they over-report — a starting point for writing a function, not a contract. Routes assembled at runtime cannot be resolved statically. And it describes what the code does, not what it ought to do, which is why the authorisation finding is presented as a finding rather than quietly corrected in the output.
The general lesson holds well beyond AI work. Most systems of any age carry a large body of knowledge that exists only in the code and in the heads of the people who wrote it. Extracting it and checking it against the database is worth doing on its own merits. We only did it because a model needed it, which is a poor reason to have waited.
This catalogue is the first step of an engagement rather than a by-product of one, and it is delivered as a report you keep whatever you decide afterwards — see AI data assistants.