> ## Documentation Index
> Fetch the complete documentation index at: https://docs.lerian.studio/llms.txt
> Use this file to discover all available pages before exploring further.
# Schema discovery
> How Fetcher reads the tables and fields of a datasource, when it serves a cached snapshot, and how it validates an extraction mapping before the first query runs.
Fetcher reads the shape of a datasource for you. A **schema snapshot** lists the tables of one datasource and the field names of each table. You maintain no separate catalog, and you upload no schema file.
Two uses depend on a snapshot. A caller reads one to learn what fields exist. Generic extraction validates selected tables and fields during planning before it queries data. That validation uses a cache-first snapshot, so a cache hit is not a live-schema check. The `plugin_crm` compatibility path is separate.
## What a snapshot holds
***
| Element | Content |
| ------------ | ------------------------------------------------------------ |
| `configName` | The connection the snapshot describes. |
| Tables | One entry per table or collection, under its qualified name. |
| Fields | The field names of each table, in sorted order. |
Names arrive qualified when the table sits outside the default namespace. PostgreSQL returns `accounting.invoices` for a table in another schema and plain `users` for one in `public`. SQL Server applies the same rule around `dbo`. Oracle returns `OWNER.TABLE` when the owner differs from the connected user.
A snapshot carries names and nothing else. It holds no rows, no credentials, and no connection string. System tables never reach it: the database adapter drops `pg_*`, `information_schema`, and the Oracle dictionary views before the snapshot leaves the adapter.
## Live discovery and cached discovery
***
The Manager exposes two schema surfaces, and they differ on purpose.
| Operation | Freshness |
| ------------------------------------------------- | --------------------------------------------------------------------------------------- |
| `GET /v1/management/connections/{id}/schema` | Always live. It never reads the cache and never writes to it. |
| `POST /v1/management/connections/validate-schema` | Cache first. It serves a cached snapshot when one exists, and discovers live otherwise. |
The split follows the two use cases. Somebody who asks for a schema wants the current truth, often right after a migration added a column. Validation runs on the way into every job, so a round trip to the database on each call would cost far more than it returns.
Discovery follows a fixed order, and each gate runs before the next one acquires anything:
Fetcher validates the tenant scope before it touches any resource.
Fetcher resolves the connection inside that scope. An unknown connection — or one that belongs to another tenant — stops here as `404 Not Found`.
On the cache-first path, a hit returns at once. Fetcher builds no connector and opens no database session.
On a miss, Fetcher resolves the driver for the datasource type, opens a connector, and reads the catalog. It closes the connector on every path, success or failure.
Fetcher stores the snapshot under the tenant and the config name, then returns it.
## What the schema cache buys
***
The cache turns a database round trip into a lookup. A hit skips the connector build and the catalog read together, so a job that validates twenty tables across three datasources pays for none of them a second time inside the window.
* **Key.** Every read and every write is scoped to the tenant and the config name. One tenant never sees another tenant's snapshot and never poisons it.
* **Lifetime.** Five minutes by default. `SCHEMA_CACHE_TTL_SECONDS` sets it on the Manager.
* **Backing store.** The Manager keeps the cache in Valkey or Redis, and falls back to process memory when that store is unreachable.
The cache is an optimization, and Fetcher treats it as one. A failed cache read degrades to a live discovery. A failed cache write still returns the discovered snapshot to the caller. Neither failure reaches your response.
### Running without a cache
The Engine schema-cache port is optional. Without one, discovery runs live from the datasource. Validation remains correct; it pays the database round trip each time.
Add a cache when validation traffic repeats against stable schemas. Leave it out when the host runs occasional extractions, or when a live read on every call is the behavior you want.
## Discovery per database
***
| Datasource | How Fetcher reads the catalog |
| ---------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| PostgreSQL | Reads `information_schema` for base tables and their columns. Falls back to the `public` schema when the connection names none. |
| MySQL | Reads `information_schema` for tables, columns, and primary-key constraints. |
| Oracle | Reads the `ALL_TABLES` and `ALL_TAB_COLUMNS` dictionary views for the owners you name, and the user's own tables otherwise. The connected user is the default owner. |
| SQL Server | Reads `information_schema` and falls back to the `dbo` schema. |
| MongoDB | Infers the shape. A collection declares none. |
Catalog reads carry a 30-second timeout.
### MongoDB inference
MongoDB has no declared schema, so Fetcher builds one in two passes. An aggregation over the collection produces field names. A sample of up to 50 documents then gives each field its type.
The field-name pass is bounded by collection size. For collections of up to 10,000 documents, the aggregation processes up to 1,000 documents through `$limit`; it does not guarantee which documents are selected. Above 10,000 documents, it takes a random sample instead:
| Collection size | Documents read for field names |
| -------------------- | ------------------------------ |
| Up to 1,000 | All of them |
| 1,001 to 10,000 | Up to 1,000 through `$limit` |
| 10,001 to 100,000 | A random sample of 2,000 |
| 100,001 to 1,000,000 | A random sample of 5,000 |
| Above 1,000,000 | A random sample of 10,000 |
The snapshot names fields carried by that limited set or sample. Generic extraction proceeds only when planning validates the selected field in its schema snapshot; do not rely on a field omitted from that snapshot extracting successfully. When aggregation fails on one collection, Fetcher falls back to sampling for that collection and continues discovery of the remaining collections.
## Validation before extraction
***
`POST /v1/management/connections/validate-schema` takes the same `mappedFields` map an extraction job carries. Send it before you submit the job.
A clean validation returns `200` with `status: success`. Schema inconsistencies return `422 application/problem+json` with code `FET-1060`; each issue is an `errors` detail with a location and message, and can include a structured value. If none of the requested datasources resolves, Fetcher instead returns top-level `400 FET-1062`.
The Engine checks selection shape and configured limits before it reads a schema. It then resolves each datasource inside the tenant scope and validates table and field membership against the cache-first snapshot.
## Next steps
***
Submit a job, follow it, and read the result.
Register, test, update, and delete a datasource connection.
What each of the five database engines does differently.
The Fetcher model in one place.