Schema analysis goes stale, so only changed tables get reanalyzed

Suppose that after schema analysis (“DDL doesn't say what a column means, so we read its values first”) finishes, someone adds a return flag column, RETURN_YN, to the orders table. The table card doesn't have that column, so when you ask for last month's revenue, the model returns a total that still includes returns. The SQL runs without an error and only the number is wrong. Now suppose instead that a DBA revokes the connection account's read access to the payroll table. SQL against it fails. Unless the cards are checked again, the sample rows and frequent values saved during analysis stay on the card, where the model can still read them.

Analysis results describe the database as it was when it was analyzed, and the database keeps changing. This post covers the two checks that find changed tables. Whether a table still exists is checked against the list of tables the connection account sees right now, and tables that disappeared are removed from the tools the model uses. Whether columns or code values changed is checked by comparing a per-table schema fingerprint, and only tables whose fingerprint changed are reanalyzed. When the list can't be verified, nothing changes.

#Stale cards produce wrong answers through SQL that runs

Changes after analysis reach the model in three ways.

Code values can be handled without touching the analysis results. Chat uses the code mapping only as a hint and always reads the actual values by joining the code table (“DDL doesn't say what a column means, so we read its values first”). Table search results and cards also carry the last analysis time, so the model knows when a card is old. What the model can't know is which columns are missing from a card, and it can't decide on its own that a vanished table's card should not be read. Those two cases require finding the tables that changed.

#Reading the catalog on every question, or reanalyzing everything on a schedule

The simplest approach is to read the source database's catalog on every question and reconcile it with the cards. Table and column lists are always current. But the catalog only gives names and types; what a new column means takes another analysis to learn. And the source database receives a catalog query with every question.

The other approach is to reanalyze every table on a fixed schedule. Meanings stay current too, but analysis cost scales with the number of tables (“DDL doesn't say what a column means, so we read its values first”), and most tables didn't change in the meantime. On a database with thousands of tables, that spends model cost on tables that are exactly as they were.

So we separated finding changed tables from reanalyzing them. Finding runs often, with cheap queries. Reanalysis runs only on the tables that were found. A table that disappeared and a table whose shape changed are also detected differently, so they are checked separately.

#Whether a table exists is judged by one list: what the connection account sees

The criterion is the list of tables the connection account sees right now. That list is every table OwlAI can use at this moment. Whether a table missing from it was DROPped at the source or removed from the account's grants, it is treated the same way: as a table that can't be used.

The list comparison runs at these points:

• when a connection is registered, edited, or turned back on
• when an analysis starts
• when an administrator opens the database screen
• when the periodic check described below runs

When an analyzed table disappears from the list, its analysis results are not deleted. It is marked Unavailable, and from that moment it drops out of all three tools the model uses: the table name list, table search, and table card lookup. The database summary also stops using unavailable tables' analysis results as input.

One place decides whether a table has disappeared, and all three tools share that decision. A separate condition in each tool would make each tool easier to change. But if one tool missed the condition, cards for vanished tables would go out through that tool alone, and a gap like that mixes into answers with no error and no log. Table card lookup takes a table name directly, so even if the model passes a name it saw earlier in the conversation, it gets no card for an unavailable table.

When the table becomes visible again, the mark is cleared. The analysis results are still there, so the table is back in answers without reanalysis. That is why briefly changing an account's grants and then restoring them doesn't force a full reanalysis. Analysis results are permanently deleted only when an administrator clicks Clean up unavailable on the database screen, which also deletes its table notes. For a table like payroll that won't be opened again, that button is how the sample rows get deleted.

A table that was never analyzed drops off the list when it disappears, because there are no analysis results to delete. A newly created table shows up as Not analyzed at the next comparison, and chat doesn't use it until it has been analyzed.

#When the list can't be verified, nothing changes

The case this logic treats most carefully is a list that can't be verified. If the list query fails or returns an empty list, no table is marked Unavailable, and the next comparison decides.

Reading an empty list as "every table is gone" would pull every table in that database out of chat at once. A comparison that runs while the source database is restarting, or during a brief network drop, can get exactly that response. When hundreds of tables have been analyzed and the list comes back with 0 rows, a misconfigured account or schema is far more likely than the whole database having been dropped. A list missing only some tables counts as a normal response and is compared.

The cost of this rule is that when a database really does vanish entirely, the mark comes late. If the model writes SQL against it in the meantime, the database reports an error right away, so no wrong number goes out as an answer.

#Tables whose columns or code values changed are found by schema fingerprints

The list comparison doesn't catch a table that is still there but has new columns, changed types, or new values in a code table. Schema fingerprints find those changes. A fingerprint is a hash of the following ingredients, normalized in a fixed order, for each table.

Column names and types come from a single catalog query. For value distributions, aggregate queries go to code columns only. Recounting every column's distribution each time would catch more changes, but it would load the source database as heavily as analysis does. The ingredients are kept alongside the hash so the change can be explained.

When a table analysis finishes, the fingerprint at that moment is saved as the baseline. A worker builds the current fingerprint for each database connection at an interval the administrator sets and compares it with the baseline. Matching hashes mean nothing happens. If they differ, the worker compares the ingredients, writes a change summary such as "1 column added (RETURN_YN)" or "1 value added to STATUS_CODE," and marks the table Schema changed. The change summary also appears in table search results and on the card. Even before reanalysis, then, the model knows the card is missing columns and the code mapping is missing values.

The periodic check also runs the list comparison. A table confirmed as missing becomes Unavailable on the spot and leaves the tools. If the catalog query fails or returns an empty list, fingerprints aren't compared and no state changes.

#Only tables the administrator selects are reanalyzed

Schema changed tables are counted on the Tables needing action card of the database screen, together with not-analyzed and failed tables. The administrator selects tables there and analyzes them again. Only tables whose fingerprint changed get reanalyzed, so cost follows the number of changed tables, not the size of the database. When reanalysis finishes, the new fingerprint becomes the baseline and the Schema changed mark is cleared.

To stop an analysis, the administrator clicks Cancel analysis on the progress panel. Tables that have not started go back to their previous state, and tables already being analyzed are analyzed to the end. Those tables have already started their model calls, so stopping them would leave the cost spent so far without a card to show for it. That is why Cancelling analysis stays on screen until the tables in progress finish.

The database summary is regenerated separately. If a table analysis finished after the summary was built, or a reset or unavailable-table cleanup changed the number of tables the summary drew on, the summary is marked Database summary stale. The summary also costs model usage, so the administrator uses that mark to decide when to rebuild it.

#Design choices

Instead of running reanalysis automatically, we leave it to the administrator. Analysis costs model usage, and each organization runs one analysis request at a time. If the periodic check queued changed tables on its own, the analysis an administrator wants to run now would wait behind it. The change summary already says what changed, so the administrator who reads it decides when to reanalyze and how much. Until then, the model uses the old card with the change summary attached.

We kept the fingerprint narrow. It looks only at column names and types and the value distributions of code columns. So it misses a column whose type stays the same but whose use changes, for example AMT starting to hold amounts that include VAT. The business side usually learns about changes like that first, and the administrator selects that table and reanalyzes it.

The check interval trades how soon changes show up against load on the source database, so the administrator sets it per database connection. A short interval marks changes sooner; a long one sends fewer catalog and aggregate queries to the source database. If the model writes SQL against a table that disappeared between checks, the database reports an error.

Keeping the analysis results of vanished tables has a cost: until someone clicks Clean up unavailable, the sample rows and table notes stay stored in OwlAI. The model can't read them, but if access was revoked so that no copy of the data remains, the administrator has to run the cleanup too. For a business database whose schema changes often after analysis, set the check interval to match your release cycle and look at the Tables needing action card right after each release.