ESRA — Data Sources

A source is one governed searchable database. Modelling a source is the work of describing what it contains in business terms, which of its tables or views are searchable, and which of their columns may be searched, returned, filtered, or never seen at all.

Requires ESRA_DATA_MANAGE to create or edit; ESRA_DATA_READ to view.

Working in the Portal

ESRA & Data → Data sources. The grid carries identity, provider, selection mode, one status cell (lifecycle, health, enablement and entity count together) and a row of icon actions: view, edit, test, enable or disable, publish or unpublish, delete. Each action names itself in its tooltip, which is also its accessible name.

New source opens a seven-step wizard: identity and provider, connection, a connection test, relation discovery, column governance, what a model should be told, and a summary. It reads the database's own catalog rather than asking the operator to retype it — relations, columns, their types, primary keys and existing full-text indexes — and proposes a mapping from what it finds: which column is the record key, which is the title, which is a business timestamp, and which columns are worth searching.

What it deliberately does not propose is which columns are filterable or restricted. Both are governance decisions, and a security control set by heuristic is worse than one left unset. Those stay the operator's to make, on the column-governance step, with the consequence stated.

The last substantive step, before the summary, is selection metadata — the business description, usage and exclusion guidance, domain tags, content languages and example prompts that are the only source data a selection prompt ever sees. Ceilings and result governance are not part of the wizard at all: they are configured afterward, on the Ceilings and Result governance tabs of the same tabbed editor used to edit a source once it exists.

Everything the wizard collects is applied in one composite call at the end, so a wizard abandoned midway leaves nothing behind. There is no half-built source to find and delete.

A source that is serving cannot be edited. A published, enabled source is what the runtime is serving, so editing it in place would change what live audiences search with nothing recording that it happened. Publish is replaced by Unpublish on a published row, and Edit is disabled with the reason on it. Test connection stays available throughout — probing is operational, not configuration.

There are two ways to stop a source serving, and the tooltips name both.

Unpublish returns the source to draft, at which point it edits normally and can be published again. It is refused while a published exposure still reaches the source, and says which one — which is precisely the situation you are in when a source in real use turns out to need a correction.

Disable is the way through that. A disabled source is dropped from every scope resolution, so it reaches no caller and its configuration is editable again for the same reason a draft's is. The editor says the corrections are not live, because they are not: they take effect when you enable the source again, which is the deliberate act that puts it back in front of callers. The exposures that bind it are never touched, so nothing has to be republished.

View and edit are tabbed modals over the same six faces of a source: overview, selection metadata, connection, ceilings, result governance, and the searchable entities with their columns. Every field carries inline help stating what it means and what values are reasonable.

Saving is one action. Every change across every tab — including adding a column or removing an entity — is staged and committed together when you press Save, and Cancel discards all of it. Editors previously applied collection edits immediately and carried a disclaimer saying so; the disclaimer was accurate, which was the problem. Underneath, the save still applies only the sections that changed, each against its own endpoint, so an unrelated save never rewrites a connection whose secret bindings another administrator may have changed. If one section fails, the message names it.

Field weights are chosen as four named bands — highest, high, normal, lowest — rather than as a decimal. A free decimal invites tuning that nobody can evaluate. An operator who has already stored a non-standard weight keeps it, and sees it verbatim rather than having it silently rounded into a band.

Credentials are bound by choosing an existing secret, never by typing an identifier. The picker lists name and identity only; no secret value is returned by any Portal endpoint. Creating or rotating the secret itself remains SECURITY_MANAGE work on the secret store screen.

The internal document store

A source of provider type InternalPostgreSql is the tenant's own document repository. Its connection and its searchable structure are fixed by the platform and configured on the Document store screen, so both tabs are hidden here and the connection test is absent rather than disabled — there is nothing on this screen to test. Selection metadata, ceilings and result governance remain fully editable, because those are genuine per-source decisions whatever the storage is. A stale form cannot write the connection either: the save path skips it for internal sources.

Such a source is created with its canonical mapping already in place — one entity over the store's documents, keyed by a stable document identifier, with title and content searchable and document type returnable. The publication rule is unchanged for it: a source still cannot be published with nothing to search. The mapping simply is not the operator's to author, because the schema behind it is not theirs to change. A store source created before this existed acquires its mapping on the next publish rather than needing a migration.

Authority

Every control is gated individually. A holder with ESRA_DATA_READ can open any source and read all six tabs; every write action is disabled with the missing role named in its tooltip, rather than the screen quietly omitting the functionality.

Identity and Lifecycle

Disabled at source level is absolute — no capability and no exposure can override it upward.

Connection

The connection record never contains credentials. Its shape depends on the provider type — there are three:

• ADO/SQL — PostgreSQL, SQL Server, MySQL/MariaDB and Oracle. A connection-string template plus references to secrets resolved per use through the platform secret store, described below.
• HTTP — Elasticsearch/OpenSearch, reached over REST rather than a database driver. A credential-free base URL (embedded userinfo such as https://user:pass@host is rejected) plus an authentication mode — API key or HTTP basic, each resolved from a secret — a connect timeout, a request timeout, TLS requirement and a per-host connection-pool ceiling.
• MongoDB — reached over the native binary wire protocol rather than ADO or REST. A credential-free mongodb:// or mongodb+srv:// connection-string template, a username/password secret pair, an authentication database (admin by default, since MongoDB credentials are commonly stored separately from the target database), the target database name, an optional replica set name, a configurable read preference (default SecondaryPreferred, to keep search load off the primary), connect and server-selection timeouts, a per-query execution-time budget and TLS requirement.

The ADO/SQL shape:

Every host in the composed connection string, base URL or Mongo connection string is validated against the platform's outbound host allowlist before a connection is opened — the same control that governs audit delivery targets. A source pointing at a host that is not allowlisted for this tenant cannot connect, and the failure names the host, not the credential.

The database principal must be read-only. ESRA never issues DDL or DML against an external source. On SQL Server this is load-bearing rather than advisory — the engine has no session-level read-only transaction — so the connection test attempts a trivial write inside a rolled-back transaction and fails if it succeeds. This "Require read-only" field and the write-probe test apply to the ADO/SQL providers; MongoDB has no equivalent session-level concept either, so its read-only guarantee is instead a fixed, non-configurable ReadConcern.Majority applied to every read.

Test connection

Bounded, audited, and never echoes the connection string. It reports: reachability, whether the configured relation exists and is visible to the principal, whether a suitable full-text index is present, whether the text-search configuration exists, and whether the principal is genuinely read-only.

Transaction isolation — SQL Server only

ESRA's reads run inside a transaction, and which isolation level that transaction uses is configured per source rather than detected.

ALLOW_SNAPSHOT_ISOLATION is off by default on SQL Server, which is why read committed is the default here: it is the mode that works against an untouched database without its owner enabling anything.

Choosing snapshot against a database that forbids it fails, with the classification SNAPSHOT_ISOLATION_UNAVAILABLE and a connection test naming both remedies. It does not quietly fall back. A silent downgrade would begin taking shared locks on someone's production database with nobody having agreed to it — and the whole point of the setting is that this is the database owner's decision, not the platform's. The connection test does detect the database's setting, but only to advise: a source on read committed against a database that permits snapshot is told so, once, where the operator is already looking.

ReadUncommitted is deliberately absent and should stay absent. It would let ESRA hand an AI client a row that is subsequently rolled back, and there is no configuration under which returning data that never existed is acceptable.

The setting is hidden for every other provider type — PostgreSQL (including the internal document store), MySQL/MariaDB, Elasticsearch, MongoDB and Oracle. PostgreSQL's MVCC already guarantees readers never block writers, so there is nothing to choose there; the non-relational and non-ADO providers have no comparable session-level isolation concept to configure either.

Selection Metadata

This is the only source data that ever reaches a source-selection prompt. It is business language, by design: the model choosing sources never sees a schema, a relation name, a column, a host or a credential.

Search Mappings

A source exposes one or more search mappings — each one a governed searchable entity backed by a table or, preferably, a view.

Prefer a view over a table. Complex joins, computed columns and pre-applied business filters belong in a governed database view maintained by the DBA — not in dynamically generated SQL. ESRA never generates a join.

Adding an entity from the database

The Searchable entities tab offers Read from the database beside Add entity. It asks the source's own catalog what it contains, lists the relations, and proposes a mapping for each one you pick — record key, title, business timestamp and the columns worth searching — exactly as the onboarding wizard does, because it is the same implementation. Everything is editable before it is staged, and nothing is written until you save the source.

Two things it will not do. It never proposes which columns are filterable or restricted: both are governance decisions, and a security control set by heuristic is worse than one left unset. And it can only show what the connection's database principal may select from — so if a table you expect is missing, grant that principal read privilege on it, or on the catalog views, and read the structure again. An empty list is far more often a missing GRANT than an empty database.

The control is withheld, with the reason on it, when the source has no connection or its last probe found it unreachable.

All six external providers — PostgreSQL, SQL Server, MySQL/MariaDB, Elasticsearch/OpenSearch, MongoDB and Oracle — can be read this way, each against its own catalog (relations and columns for the SQL-shaped providers, indices and mappings for Elasticsearch, collections for MongoDB). Where a relation carries no full-text index, the list says so — and says what it costs on that engine, because it is not the same thing on every one of them. On PostgreSQL the relation is searchable and slow. On SQL Server, MySQL, MongoDB (in $text mode) and Oracle (absent Oracle Text licensing or an index) it cannot be searched at all: the entity models cleanly and every query against it fails until the engine-appropriate index exists. Neither blocks the choice; both are worth knowing before you make it.

Fields

Field-level governance is where most of the security of a source actually lives.

A field that is neither searchable nor returnable nor filterable is invisible to ESRA entirely.

Limits

Per-source ceilings that no capability or exposure can exceed: max queries per source, max terms per query, max filters per query, max rows per query, max candidates, max concurrency, max retries, command timeout.

Result Governance

Record-level filters

A template such as:

tenant_scope = @claim:tenant_id AND department = ANY(@claim:departments)

is validated at publication — no statement separators, no comment sequences, balanced parentheses, every placeholder resolvable — and bound at request time from the caller's claims.

A missing claim is fail-closed. The query is not executed, the source is dropped from the invocation, and the response carries a source-unavailable warning. ESRA never substitutes a default and never omits the predicate.

Health

Sources are probed on a schedule, not on the readiness path. A failing optional source degrades a retrieval; it does not fail the Runtime.

The scheduled probe matters more than it looks. Scope resolution excludes an unhealthy source, so without it a database that went away overnight keeps being selected — and keeps failing every invocation that selects it — until somebody happens to press Test connection. The sweep turns that into a source that quietly drops out of scope and a row that says why. A probe that throws is an unhealthy source, not a failed sweep, so one broken source never prevents the others being checked. The sweep can be switched off for a deployment behind a connection-rate-limiting gateway; the Portal then shows when the probe last actually ran, rather than implying the health value is current.

An unhealthy source whose effective mode is Optional is dropped from the effective scope. An unhealthy Mandatory source is retained and handled by the mandatory-failure policy at execution time. Degraded gates nothing — not scope, not publication, not execution. It is what the operator is told, never what a caller can reach.

A full-text index still crawling is not degraded. On a corpus under continuous ingestion an index is catching up more often than not, so treating it as a fault would leave a warning nobody can clear — and a warning that never clears is one nobody reads, which is how a real problem goes unnoticed. The crawl is still reported, as an informational note on the entity, because results from it may be incomplete until it finishes. An index whose state could not be read stays degraded: that one is not known to be fine, and granting the connected principal metadata visibility on the table resolves it.

Which entity is at fault. A source's state is a roll-up of a probe per entity, and the connection test now names the entities that produced it rather than reporting that at least one of them did. The Searchable entities tab carries the same answer where you can act on it: a warning triangle on the entity, a sentence saying what is wrong and whose job it is to fix — correcting a text-search configuration is a change on the entity, adding a full-text index is a change on a table ESRA does not own — and, where the engine can attribute it that precisely, a triangle on the individual columns. SQL Server can name the columns its index does not cover; a PostgreSQL index may be an expression over several columns, so there the finding names the relation and lists the entity's searchable columns as affected rather than blaming one of them.

Findings come from the last probe, so they appear without testing the connection — the scheduled sweep establishes them — and each probe replaces them entirely. If a finding disappears after a sweep, it was fixed.