DEV Community: Vincenzo Nudo

Schema catalogs for AI assistants: the layer nobody wants to maintain

Vincenzo Nudo — Wed, 26 Aug 2026 03:41:52 +0000

The schema catalog for an AI assistant is the artefact that answers the question "what does this database look like right now". Whether the database is Postgres, MySQL, SQL Server or Redshift, the shape of the problem is the same: the catalog carries table names, column names, types, keys, and enough relationships to let the assistant write a query that resolves. It lives somewhere between the database and the assistant, has to stay in sync with a database that changes underneath it, and is almost always built the same weekend the team decides they want an AI assistant reading their data. It runs fine for the first three tables. The problems start around the fourth week, and none of them look like the same problem twice.

The distinction worth naming early is between the connection layer (how the assistant reaches the database) and the knowledge layer (what the assistant knows about the database's shape). The connection layer receives most of the attention, because credentials, network isolation and query cost are visible failure modes and easy to argue about. The knowledge layer is where most of the actual quality of the assistant lives, and it decays quietly. The AI database context page covers why this second layer matters at all when the first one exists.

Why not just point the assistant at the database

Connecting the AI directly to production is the shortest path and the one most teams reject after five minutes of thinking about it. The assistant would get read access on tables it should not see, its queries can be arbitrarily expensive, its credentials would live somewhere they should not, and the audit trail becomes hard to reason about. What most teams end up building is a layer in between: a representation of the database that the assistant can read cheaply and safely without ever touching production.

That layer is what this article is about. It is not the connection. It is the catalog.

The five recipes teams build

Ask fifteen senior developers how to build that layer and you get roughly five recipes. Most teams end up combining two or three.

Recipe 1: the JSON catalog file. A script dumps the schema (table names, column names, types) into a JSON file, committed to the same repo as the assistant's prompt config. The assistant reads the file at startup. The simplest thing that works.

Recipe 2: the nightly refresh. Same as recipe 1, but a cron job regenerates the JSON every night from the running database. Sometimes on demand from a slash command in Slack.

Recipe 3: the custom MCP server. A small Model Context Protocol server sits between the AI and the database, exposing a set of typed queries. The assistant calls these instead of writing raw SQL. The MCP process holds the credentials.

Recipe 4: the semantic layer. The catalog is enriched with human notes: which column is a foreign key in disguise, what status = 'C' actually means in this business, which tables are archived. This layer is typically a YAML file or rows in a table.

Recipe 5: the read-only replica. A separate replica of the production database, sometimes with masked columns, that the assistant is allowed to query directly. Solves the credential problem. Does not solve the knowledge problem.

None of these are bad. All of them ship. Most of them stop working around the same point in the same way.

Why all five recipes work in week one

The recipes deliver the same thing on day one: the assistant answers a question about the database, correctly, in front of someone who wanted to see it work. The team walks away with the impression that the layer is done. The layer is not done. The layer is at day zero of a maintenance timeline that nobody has planned for.

Why all five recipes start breaking around week three

Four failure modes, in the order teams usually hit them.

A column gets renamed and nobody updates the catalog. A developer renames user_email to contact_email in a migration. The migration ships. The catalog file still says user_email. The assistant asks for user_email and gets a "column does not exist" error, or worse, silently gets zero rows back and confidently reports "there are no users with an email".

A new column appears and the assistant never learns about it. The nightly refresh (recipe 2) picks it up in fifteen hours. In those fifteen hours the assistant is asked something that would have needed the new column, and answers based on the old shape of the world. No error, no warning, just a wrong answer delivered with the same confidence as a right one.

The semantic notes drift out of sync with reality. The YAML file (recipe 4) said status = 'C' means "cancelled". Someone changed it to mean "completed" six months ago as part of a business logic overhaul. The YAML was never updated. The assistant answers questions about cancelled orders with rows for completed ones.

The MCP typed queries stop matching the schema. A typed query (recipe 3) was written for a table with three columns. The table now has five. The query still runs, still returns rows, and skips the two new columns entirely. The assistant works around a phantom limitation of the database that only exists in the code between the two.

None of these failure modes are exotic. All of them show up on any database older than a year, staffed by more than three developers, without anyone doing anything wrong.

What a layer that survives looks like

Three properties separate a catalog that ages well from one that goes stale in two weeks.

It is regenerated from the running database on a schedule, not from a file. The source of truth for what the assistant sees has to be what the database currently is, not what someone wrote down when they built the layer. If the regeneration is not automated, it will not happen. This is the difference between "the catalog is code" (fragile) and "the catalog is a snapshot" (self healing). The mechanics of doing this well are discussed in more detail in the schema drift detection piece.

It records structural changes as typed events, not as raw diffs. When a column is renamed, someone needs to see "column user_email was renamed to contact_email on 2026-08-14" instead of two hundred lines of unified diff between two JSON files. This is what makes drift readable and reviewable rather than noise to scroll past. The schema change history page shows what this looks like when the events are typed and severity-classified.

It carries human annotations alongside the metadata, and versions them. The semantic notes (which enum values mean what, which two column joins are load bearing, which tables are archived) live next to the raw catalog, versioned together, so that when the schema changes the annotations get flagged for review instead of quietly outliving their referent.

A layer with these three properties is not built in a weekend. Which is why almost nobody has one, and why most teams are running a catalog file that was accurate three weeks ago.

Where Taavik fits

Taavik is one implementation of the three properties above, packaged as a Model Context Protocol (MCP) server that AI assistants and coding agents consume directly. Recipe 3 up in the list (write your own MCP server) is the DIY version of what Taavik ships in the box: catalog scans on the team's cadence, typed rename events in the change history, and human annotations at the column and table level versioned alongside the metadata. Postgres, MySQL, SQL Server and Redshift are supported. Database credentials never leave the private network, which is the topic of a separate piece and not what this one is about.

The choice is not Taavik versus nothing. The choice is whether those three properties end up in the stack, by any route, before the fourth week runs out. Building them in-house costs a few hundred lines of code and one person willing to own them for as long as the database lives. Buying them costs a subscription and one decision meeting. The right answer depends on how many databases are in scope and how many engineers can be pulled away from product work to look after a layer that is technically boring and operationally load bearing.

Originally published on the Taavik blog.

How I turned approved SQL into governed business KPIs

Vincenzo Nudo — Mon, 16 Mar 2026 15:36:32 +0000

In a lot of companies, executives and business teams want answers from company data, but they do not know SQL.

That part is obvious.

What is less obvious is that SQL is not the real problem.

The real problem is this:

How do you let non-technical users ask business questions about company data without exposing raw SQL, direct database access, or completely uncontrolled AI generated queries?

That was the problem I wanted to solve.

The naive solution looks attractive

The first idea is always the same:

Connect an AI assistant directly to the database and let people ask questions in natural language.

At first, this sounds great.

In practice, it creates a different set of problems:

• the business definition of a metric is not stable

• different prompts may produce different SQL for the same question

• there is no strong boundary between approved and unapproved logic

• scheduling, monitoring, and delivery workflows are still missing

• auditability becomes weak very quickly

• private environments become painful to manage

In other words, query generation is only one small part of the problem.

The harder part is making the answers reliable.

The pattern I ended up using

Instead of letting AI write arbitrary SQL for business users, I flipped the model.

The system starts from real SQL written and approved by analysts.

The flow looks like this:

An analyst writes a real SQL query.
They define only the minimal input parameters needed for the business question.
That query becomes a governed KPI.
The KPI can contain multiple query variants.
Business users never see SQL.
They only see KPI cards and ask follow-up questions in plain language.
AI maps the question to the right KPI variant.
The backend executes only approved query paths.
The UI renders the result as a scalar, a short list, or a chart.

That design changes everything.

The SQL remains controlled.

The business experience becomes flexible.

Why query variants matter

This was one of the most important parts of the design.

A single KPI often needs more than one query behind it.

For example, imagine a fintech KPI about money movement.

The same KPI may need:

• a default comparison variant for today versus yesterday

• a trend variant for a daily bar chart this week

• a breakdown variant for operational exceptions like refunds or failed payments

From the business user’s point of view, this still feels like one KPI.

From the backend point of view, it is a governed set of approved query variants.

That means the user can ask:

• How are we doing versus yesterday

• Show the daily trend this week

• Are refunds rising

But the system is not improvising SQL every time.

It is resolving the question to a predefined execution path.

That is the difference between flexibility and chaos.

What the AI actually does

This is the part I think many teams get wrong.

In my flow, AI does not generate arbitrary SQL against the database.

Its role is narrower and much more useful:

• interpret the user’s question

• map it to the correct KPI

• select the correct query variant

• resolve the right time context and parameters

• explain the result in business language

So the AI is acting as a language and intent layer, not as an unrestricted database operator.

That matters because it gives business users a natural interface without giving up control, auditability, or execution safety.

Why this works better for business users

Business users do not want to think about joins, schemas, or prompt engineering.

They want answers like:

• How did onboarding perform last week

• Show daily wires and P2P transfers this week

• Are failed payments increasing

They also want charts, lists, and short explanations.

If the underlying SQL is already approved and versioned, you can give them that experience safely.

The UI becomes simple because the backend is strict.

That is a much better tradeoff than giving everyone direct AI to database access.

Execution still matters

Even with this model, execution is still the real backbone.

In my case, query execution, scheduling, and monitoring all follow the same deployment model.

They can run:

• in the cloud

• or on-prem through a dedicated installed agent

In general, on-prem is the preferable setup for sensitive environments, because the data never needs to be exposed outside the customer environment.

The platform orchestrates the workflow, but execution stays close to the database.

That turned out to be a very important distinction.

A lot of teams do not just need answers.

They need answers without opening up their data environment too much.

What this unlocked

This approach gave me a few things at the same time:

• business users can ask follow-up questions in plain language

• analysts still control business logic

• the results stay tied to approved SQL

• charts and tables stay consistent with the same KPI definition

• scheduling and monitoring remain part of the same operational system

• cloud and on-prem execution both fit naturally into the model

So instead of treating natural language as a replacement for data workflows, I ended up using it as an access layer on top of governed workflows.

That feels much more robust.

Final thought

I think a lot of teams are focusing on the wrong question.

The question is not:

Can AI generate SQL

The more important question is:

How much execution freedom should AI have around company data

For business-facing analytics, I have become convinced that natural language works best when the SQL underneath is already approved, versioned, and operationally controlled.

The hard part is not letting AI write SQL.

The hard part is making business answers reliable.

I’m building this approach in DataPilot, where approved SQL becomes governed business KPIs and business users can ask follow-up questions without touching raw SQL.

If you want to see the product context behind this model, it’s here:
https://getdatapilot.com/product/business-kpis