Resolve the name before writing the SQL

A value index is enough for small vocabularies. When a column has a few hundred distinct values — lines of business, states, statuses — the engine keeps them and matches a question's words against them. A book of 25,000 insureds, 430 broker offices and every street address on a schedule is a different thing: no value index holds it, and no prompt can. Before this change the model met those names for the first time inside the question, and did what anyone would: wrote a LIKE around a fragment and hoped.

Look up the record, then write the query

Before the SQL engine plans a query, it now takes the phrases in the question that are not vocabulary it already knows — not the words of table and column names, not stop words — and looks them up in the deployment's search index. A phrase that matches one record well enough becomes a resolved name: its type, its id, and the table and column it is filtered by. The engine is told to filter on that id, and never on a LIKE.

sequenceDiagram
  autonumber
  participant U as Person
  participant E as SQL engine
  participant S as Search index
  participant W as Warehouse
  U->>E: "premium for timbrlin hme centres last year"
  E->>E: Phrases left after vocabulary: "timbrlin hme centres"
  E->>S: Look it up, within the person's scope
  S-->>E: One insured, clearly the best match: ins-000038
  E->>W: ... WHERE insured_id = 'ins-000038'
  W-->>U: Rows for that insured
                

The score is about spelling, not similarity of meaning. Each word is compared with the record's words; legal suffixes such as Inc. or LLC cost nothing when they are left out, address abbreviations are expanded (St, Ave), and a word of the record's name that was not typed costs a little. A match must clear a threshold and beat the next candidate by a margin; both are settings.

When two records share a name, it asks

Names repeat. Five insureds are called Cumberland Excavating, in five states. Picking one would be a confident wrong answer, so when the best candidates are too close the engine does not write SQL at all: it answers with the candidates, each with the line that tells them apart and its id, and asks which was meant. Reply with the city, or paste the id, and the question goes on. That took four seconds on our test book. In a scheduled job, where there is no one to ask, the setting is assume: the best match is taken.

The lookup runs inside the asker's scope. A person whose grant confines them to one broker group can only resolve names in that broker group — a name outside it is simply not found, which is the same answer the warehouse query would have given them. A resolved name also gates the reuse of stored queries: a remembered query for one client is not replayed for another just because the sentences look alike.

Where the names come from: the entity catalogue

Resolution needs to know which things in the warehouse are named things: which table is one row per insured, which column is its name, which other columns it is also known by, which number it goes by, which parent it belongs to. On the SQL engine › Entities tab, Detect entities reads that from what indexing already learned — keys, names, joins, current-row flags, lifetime dates — with hints from your domain pack binding first. It reads metadata only; it does not query the warehouse.

SQL engine › Entities — what the warehouse is about, in one list

The catalogue is curated, not generated and forgotten. Add an entity detection missed, correct a column, remove one that is not a real thing; your edits survive the next detection. The same list feeds three things: the scope axes that grants and data API parameters are bound to, the entity types a search index starts from (From entities in the editor), and the names index — a search index built from the entities you tick, for a connection that has no search index of its own.

Nothing reads millions of rows unless someone asks

A client's warehouse holds millions of policies. Detecting entities never reads them, and indexing a source neither detects nor builds anything. The names index is built when a curator ticks the entities worth resolving and presses Index names — not after indexing, not after an edit, not at start-up and not overnight. The card says how many entities are ticked and roughly how many names that will read, before anyone presses it.