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

In the world of AI assistants, a schema catalog is an essential artifact that defines the structure of the underlying database. Regardless of whether the database is Postgres, MySQL, SQL Server, or Redshift, the catalog captures table names, column names, types, keys, and relationships, enabling the AI to generate accurate queries.

This catalog lives between the database and the assistant, staying in sync with the constantly changing data. However, maintaining this catalog proves to be an ongoing challenge. The connection layer, responsible for the assistant's communication with the database, often receives more attention due to concerns such as credentials, network isolation, and query cost.

Yet, the knowledge layer, which contains information about the database's shape, is where the quality of the assistant truly resides. Unfortunately, this layer tends to decay over time and becomes increasingly difficult to maintain. Teams typically build various solutions to address these challenges, such as JSON catalog files, nightly refreshes, custom MCP servers, semantic layers, or read-only replicas.

While each of these recipes delivers a working solution on day one, they all tend to break down around the same point in time, typically within three weeks. The most common failure modes include renaming columns, addition of new columns, outdated semantic notes, and outdated typed queries. These issues arise because the catalog is often manually maintained, leading to inconsistencies between the catalog and the actual database structure.

To build a catalog that survives longer, it is crucial to automate the regeneration process from the running database and treat the catalog as a snapshot of the current database structure. This approach ensures that the catalog remains up-to-date and eliminates the need for manual maintenance.