> ## 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 jobs depend on a snapshot. A caller reads one to learn what fields exist. Fetcher checks an extraction mapping against one before the first query runs.
## 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
An embedded host wires the schema cache as an optional port, and many hosts leave it out. Without one, every schema call discovers live from the datasource. Validation stays exactly as correct — it simply pays the 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 the field names. A sample of up to 50 documents then gives each field its type.
The field-name pass is bounded by collection size. On a collection of up to 10,000 documents, Fetcher reads at most the first 1,000. 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 | The first 1,000 |
| 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 the fields those documents carry. A field that appears only outside the read — after the first 1,000 documents, or outside the random sample — is not in the snapshot.
Name such a field in the job's `mappedFields` when you need it. Extraction projects the names you send against the collection itself, so a field the snapshot omits still extracts. `POST /v1/management/connections/validate-schema` answers from the snapshot, so it reports that field as `FIELD_NOT_FOUND`.
When the aggregation fails on one collection, Fetcher falls back to sampling for that collection and continues. Discovery of the remaining collections proceeds.
## 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.
```json theme={null}
{
"mappedFields": {
"my_postgres": {
"accounts": ["id", "email", "created_at"]
}
}
}
```
Fetcher checks three things, in this order:
1. **Shape and counts.** The request allows 10 datasources, 20 tables per datasource, and 50 fields per table. A breach reports before Fetcher touches a database.
2. **Connections.** Every named datasource must resolve to a connection inside the tenant scope.
3. **Membership.** Every table and every field must exist in the datasource's snapshot.
A clean mapping answers `success`. A mapping with problems answers `failure` and names each one:
```json theme={null}
{
"status": "failure",
"message": "Schema validation found inconsistencies.",
"errors": [
{
"type": "FIELD_NOT_FOUND",
"dataSourceId": "my_postgres",
"table": "accounts",
"field": "external_id"
}
]
}
```
| Type | Meaning |
| ----------------------- | -------------------------------------------------------------------- |
| `DATA_SOURCE_NOT_FOUND` | No connection carries that config name in this tenant. |
| `TABLE_NOT_FOUND` | The datasource has no such table. |
| `FIELD_NOT_FOUND` | The table has no such field. |
| `DATA_SOURCE_DOWN` | Fetcher reached the connection record but could not read the schema. |
A field name matches an exact column, or the parent of a dotted path. A request for `natural_person` validates when the snapshot holds `natural_person.mother_name`. MongoDB flattens nested documents into dotted names, so parent references are a common pattern there.
Validation separates two outcomes that a single status code would blur. A mapping problem comes back inside the report, one entry per problem, and the whole report arrives at once. A datasource that Fetcher cannot reach comes back as `DATA_SOURCE_DOWN` against that datasource alone, and the other datasources still validate.
## 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.