Datasources Specification

Status: v2.40 (frozen contract — additive-only changes after this point) Owner: datapipelines.co core Depends on: Type System spec · Enums · Configuration · Metadata DB · Pipeline Contract Last updated: 2026-09-17

1. Purpose

A Datasource is the environment-specific connection to an external database. Pipelines reference datasources by stable name (e.g., pg-prod); the Datasource Registry resolves that name to actual connection details (JDBC URL, credentials, pool size) per environment.

This separation is what makes pipelines portable across environments: the same pipeline JSON runs in dev, staging, and prod, with each environment's Datasource Registry providing different actual connections for the same names.

This spec defines:

• The Datasource entity model.
• Per-dialect JDBC adapter behavior.
• The Datasource Registry API.
• Connection pool configuration and lifecycle.
• Credential storage (encryption at rest).
• Connection testing and health checks.

2. Design Principles

1. Name-stable, env-resolved. Datasource names are the contract between pipelines and connections. Names like pg-prod are stable across envs; their underlying JDBC URLs differ.
2. Credentials never in pipeline JSON. Pipelines reference names only. Datasource credentials live in the Datasource Registry, encrypted at rest, never returned in GET responses.
3. One dialect per datasource. The dialect declares the JDBC driver, type mapper, SQL behavior. Pipelines validate at write time that their template's dialect matches the referenced datasource's dialect. The Dialect value set has a single authority: Type System §5 (mirrored, non-normatively, by Enums §5). This spec is a consumer of that list — §4.1 below maps each value to its driver, it does not define the set.
4. Pooled connections, not per-query. Each datasource has its own HikariCP connection pool. Pipeline node executions lease connections from the pool and return them.
5. Health-checkable. Every datasource can be tested via POST /api/v1/datasources/{name}/test. Pipeline execution pre-checks that all referenced datasources are reachable.
6. Fail loudly on missing datasource. A pipeline referencing a datasource name not in the registry fails validation at write time (pipeline.validation.unknown_datasource). A datasource removed at runtime causes execution to fail (pipeline.node.datasource_not_found).
7. Validate on write, universally. No invalid datasource ever reaches the database. Every create/update runs the full §9 rule set plus a test pool build (§5.4) before the row is written — the same cross-cutting principle as Pipeline Contract §2, principle 8. Connection properties are therefore validated by HikariCP and the driver themselves, not by an allowlist maintained in this doc.
8. Passthrough over allowlist. Pool and driver tuning is expressed as two namespaced passthrough maps (properties.hikari.*, properties.jdbc.*). Every HikariCP property and every driver connection property is reachable without a spec change; correctness is enforced at pool build, not by enumeration.

3. Datasource Entity

3.1 JSON structure (request — POST /api/v1/datasources)

{
  "name": "pg-prod",
  "display_name": "Production Postgres",
  "description": "Primary OLTP database.",
  "dialect": "POSTGRES",
  "jdbc_url": "jdbc:postgresql://pg-prod.internal:5432/app_db",
  "credential": {                          // §3.4 — WHAT the credential is
    "kind": "password",                    // password | token | private_key | service_account_json | none
    "username": "datapipelines_app",
    "secret": "..."                        // write-only; never returned in GET
  },
  "query_timeout_seconds": 60,
  "global": false,                         // OPTIONAL (workspaces D8) — admin-only; true = shared
                                           // infrastructure (workspace_id NULL)
  "workspace": "team-etl",                 // OPTIONAL (workspaces D8) — a workspace the caller can
                                           // access; default = the ACTIVE workspace
  "readonly": true,                        // OPTIONAL (§5.7) — forbids the three write-shaped uses
  "introspection_include_schemas": ["apex_reporting"],   // OPTIONAL — §7A escape hatch for the
                                                          // system-schema exclusion floors
  "properties": {
    "hikari": {
      "maximumPoolSize": 10,
      "minimumIdle": 2,
      "connectionTimeout": 30000,
      "idleTimeout": 600000,
      "maxLifetime": 1800000
    },
    "jdbc": {
      "ssl": "true",
      "sslmode": "verify-full",
      "sslrootcert": "/etc/ssl/certs/pg-prod-ca.pem",
      "ApplicationName": "datapipelines"
    }
  }
}

The legacy credential pair — top-level "username" and "password" — is still accepted and means credential: {kind: "password", username, secret}. It is frozen in v1 (§12.1): every client, bootstrap file and example written before the credential block keeps working. A payload carrying BOTH is refused (datasource.validation.properties_invalid) rather than resolved by precedence — the two can disagree and there is no defensible winner.

properties has exactly two reserved namespaces — hikari (pool properties, applied verbatim to HikariConfig) and jdbc (driver connection properties). Both are optional, both default to {}, and neither is allowlisted by this spec. See §5.

3.2 JSON structure (response — GET /api/v1/datasources/{name})

Identical to request, except:

• The credential secret is never returned — neither as password nor as credential.secret. The response carries credential: {kind, username?} and password_set: true | false, which is DERIVED from the kind: V13's chk_datasource_credential_present makes credential_kind = 'none' and "no stored ciphertext" the same fact, so password_set is false exactly for kind: "none".

• Top-level username is still returned (frozen shape) and is null for the kinds that have none.

• jdbc_url is included (operators need it for debugging).

• workspace (string, additive — workspaces design §9): the bound workspace's NAME, null = global.

• readonly (boolean, additive): the §5.7 flag, machine-readable.

• last_test (object or null, additive — §8.1B): the outcome of the LAST connection test — {"tested_at": "2026-08-30T09:15:00Z", "ok": false, "message": "FATAL: password authentication failed …"}. null means never tested, which is what every datasource registered before this field existed is. Never supplied on write: it is an observation, recorded by the probe.

• properties.dialect (additive, 109 §B): the row's dialect configuration, non-secret keys only, filtered through the SAME §5.6 classification that validates properties.jdbc (the union + the secret-valued suffix predicate — a secret-named key can never be stored under one carrier and shown under another). The filtering is over KEYS; values pass through untouched, because a value that is itself a secret belongs under credential, which never reaches properties at all. The shown/hidden contract, per dialect:

  Dialect	Key	Shown?	Why
  LAKE	catalog.kind	shown	which object-store catalog backs Iceberg tables — an operator debugging a REST-catalog refusal needs to see it
  LAKE	catalog.ref	shown	the SSRF allowed-root marker (a file:// mirror root or a REST catalog URL); never a credential — its grammar admits no secret
  LAKE	region, endpoint, url_style	shown	addressing; an operator debugging signature/region mismatches needs all three
  LAKE	unsigned	shown	whether reads skip the credential chain — the demo bucket's public-read posture
  LAKE	memory_limit, threads, temp_directory	shown	the §8C.4 engine limits the row actually runs with
  LAKE	attach	shown	extra catalogs to attach at connect
  any	a key the §5.6 classification refuses (password-suffixed, access_key-shaped, the SECRET_VALUED_KEYS set, CREDENTIALS, SERVER_MANAGED)	hidden	defense in depth: no such key is accepted under dialect today, and the filter exists so a future key cannot reopen the hole
  JDBC dialects	properties.hikari.* (the §5 pool tunables)	shown via the existing pool object	the pool's EFFECTIVE settings (value + unit + source layer), which is what an operator reads; the raw stored properties.hikari map has been on the wire since 094

  A declared dialect property whose value is empty, whitespace-only or null is REFUSED at register/update with datasource.validation.property_empty (§13.8) — an empty string is never a configuration. At bootstrap a field whose whole value is one ${VAR} that resolves SET-BUT-EMPTY is OMITTED (the operator's off-switch — the shipped catalog.ref: placeholder stays empty in production and means "not declared"); a literal "" in a file fails the boot with the key named.

3.3 Field reference

Visibility (workspaces design §5.3, normative): every listing and by-name read sees the ACTIVE workspace's bound datasources plus all global ones — enforced in the repository's SQL (findAllVisible/findVisibleByName), never post-filtered, so paging totals are exact. A workspace-bound datasource of another workspace behaves as not-found on every surface (REST, MCP, introspection). Datasource NAMES remain a flat GLOBAL namespace (§11.1, workspaces design §3): name is the primary key and the GCM AAD anchor, so a create colliding with another workspace's datasource name is datasource.validation.duplicate_name — by design. Member CUD is gated by datapipelines.workspaces.member-datasources-enabled (configuration §3.17); refusals are datasource.validation.workspace_forbidden (400).

Normalize-on-read, second instance (050/R3): the introspection_include_schemas rule above has a sibling — every §5.6 SERVER_MANAGED key is stripped from properties.hikari when a row is read (DatasourceRow.toDatasource, the one boundary behind GET, PUT-revalidation and pool build), so a row written outside the API cannot fail an unmodified GET→PUT round-trip with 400 nor flip the real pool flag at build time. Normalize at both boundaries; the same rule, twice.

3.4 Credential kinds

A credential travels ONLY in credential — never in jdbc_url, never in properties.*. credential.secret is encrypted at rest (§7.1); jdbc_url and properties_json are stored plaintext and returned to read-scope principals (§3.2). §5.6 refuses credential-bearing keys in both of those carriers, in both directions. There is no third carrier and no exception.

credential.kind says WHAT the stored secret is. It decides which fields may be present, and the adapter — not the caller — decides where the driver wants it (§4.2 applyCredential). A caller says "this is a token"; it never says "put it in the password slot", because that is a fact about a pinned driver.

Values are catalogued in Enums §5A.

Which kinds a dialect accepts is a property of its pinned DRIVER, declared by the adapter (DialectAdapter.supportedCredentialKinds) and enforced at save: a kind outside the set is refused with datasource.validation.properties_invalid naming what the dialect does accept. Fail-closed, because the alternative is a stored row whose kind the pool build silently ignores.

private_key and service_account_json are in no shipped dialect's set. They are catalogued for the reference targets — Snowflake key-pair auth, BigQuery service accounts — and are refused by every adapter that exists today. Adding the dialect adds the kind to its set; nothing else about the contract, the column, the wire or the encryption changes, which is the point of the seam.

On update, a kind CHANGE requires a secret. An update that omits the secret keeps the stored one (§11) — but only while the kind is the same. Changing the kind without supplying a new secret would relabel the stored one as something it is not, and none → any other kind would leave credential_encrypted NULL against V13's chk_datasource_credential_present, turning a refusal the caller can act on into a constraint violation they cannot. Refused with datasource.validation.password_missing. Moving TO none is the exception and needs nothing: it clears the column.

Storage is kind-agnostic (§7.2): whatever the secret is, it is one blob under the same versioned AES-GCM envelope with the datasource name as AAD. Key rotation (§7.3) does not care what the plaintext means, so a new kind needs no crypto work at all.

4. Supported Dialects

4.1 Dialect catalog

The Dialect value set is owned by Type System §5; this table is the driver/licensing view of it. Which drivers actually ship in the published image versus opt-in profile versus lib/ drop-in is stated once in the Deployment spec driver matrix.

4.2 Dialect adapter interface

interface DialectAdapter {
    val dialect: Dialect
    val jdbcDriverClassName: String
    val defaultProperties: Map<String, String>          // driver-level defaults; overridable by properties.jdbc.*
    val typeMapper: IngressTypeMapper                   // JDBC types → canonical types
    val refusedPropertyKeys: Set<String>                // dialect additions to the §5.6 refusal set (may add, never shrink)
    val supportedCredentialKinds: Set<CredentialKind>   // §3.4 — what this dialect's pinned driver can authenticate with
    fun applyCredential(config: HikariConfig, datasource: Datasource, secret: String?)  // §3.4 — kind → driver slot
    fun validateJdbcUrl(url: String): ValidationResult  // dialect-specific URL validation (§6.1)
    fun buildHikariConfig(datasource: Datasource): HikariConfig   // entity fields + defaults + properties.* (§5)
}

Namespace shapes

namespaceShape replaces the boolean schemaArrivesInCatalog, which could express exactly two shapes — "MySQL" and "everyone else" — and therefore could not describe any of the connectors on the roadmap.

data class NamespaceShape(
    val labels: List<String>,               // the engine's own words, OUTERMOST first; size = depth
    val levels: Int,                        // how many of them a caller can browse and filter on
    val innermostArrivesInCatalog: Boolean, // the former schemaArrivesInCatalog (Connector/J)
)

labels is not decoration: an agent told to pass catalog.schema on Databricks and project.dataset on BigQuery writes correct SQL; one told to pass "the schema" guesses. levels is often smaller than the depth — Postgres and H2 are [database, schema] but a connection can only ever see the database its URL named, so exactly one level is browsable.

Reference shapes — the contract, not an implementation. These are the four connectors this seam was designed against; none of them ships today, and each is one NamespaceShape away:

Every one of them is TWO browsable levels, which is what the wire, the filters and the introspector now carry.

applyCredential is the ONE place a credential KIND becomes a driver slot. The default covers every shipped dialect: none sets neither field (an embedded file database has no login, and Hikari must not be handed a placeholder), and every other supported kind goes into the standard username/password pair. A dialect whose driver spells a kind differently overrides it, and the caller never learns the difference.

buildHikariConfig is the single place the two passthrough maps are applied, so the save-time test pool build (§5.4) and the runtime pool build (§5.2) cannot diverge.

Each dialect has an implementation:

• PostgresDialectAdapter
• OracleDialectAdapter
• MssqlDialectAdapter
• MysqlDialectAdapter
• H2DialectAdapter
• DuckdbDialectAdapter
• SqliteDialectAdapter
• LakeDialectAdapter — §4.1's LAKE: the DuckDB driver without the embedded lock, plus the §4.2A connect-time setup (extensions, the S3 secret, ATTACHes, the §8C.4 limits).

4.2A Connect-time setup and typed dialect configuration

Two seams, added in 087 for the same reason: a warehouse or lake connector needs the engine put into a state before any query runs, and that state is CONFIGURATION, not SQL somebody types.

connectionInit(datasource): List<String> — statements HikariCP runs on every new connection in the pool, via connectionInitSql. That property existed and was unreferenced; it is now the seam. The list is joined with ; into Hikari's single slot, so the sequence stays inside the config the save-time test pool build (§5.4) checks.

Two rules make it safe:

1. Generated from typed fields, never from operator or author text. On an embedded engine this SQL runs inside the app's own process; a free-text connect hook would re-open the §5.6 INIT / session_init_sql_file surface under a friendlier name.
2. connectionInitSql is server-managed (§5.6, DS-SEC-22). It was reachable through properties.hikari before 087 — arbitrary connect-time SQL, in-process on DuckDB and SQLite — and nobody had set it, which is luck rather than containment. It is now refused in both carriers and derived by the adapter.

properties.dialect.* — a third reserved namespace beside hikari and jdbc (§12.1's frozen shape, amended additively). Unlike those two it is typed and adapter-validated: DialectAdapter.validateDialectProperties refuses unknown keys and bad values key by key, and the DEFAULT implementation refuses the whole namespace. A dialect gains dialect.* keys by declaring them, never by an adapter forgetting to look — silently ignoring an unrecognized key would let a typo look like a working setting, which is the failure this namespace exists to avoid.

Reference uses, none of them implemented: Snowflake dialect.warehouse / dialect.role, Databricks dialect.http_path / dialect.catalog, and the shipped lake adapter's dialect.catalog.kind / catalog.ref / region / endpoint / url_style / unsigned / attach.

The LAKE adapter's setup, in the order it runs:

A lake with no catalog.kind — data on a mounted volume, an NFS export, files a sidecar syncs — emits neither extensions nor a secret. That conditional is not a convenience: INSTALL httpfs needs egress to DuckDB's extension repository, and emitting it for a datasource that never touches the network would turn an air-gapped deployment's working configuration into a connect failure.

What 087 proved, and what it did not. The local path is proven end to end against a real pool (a LAKE datasource ATTACHing two DuckDB files opens, runs its connectionInit, and reports both catalogs — while the same datasource on the DUCKDB adapter cannot, because the lock forbids it). The S3 path is asserted at the level of the SQL the adapter generates, not against a bucket: a real S3 needs cloud credentials, and whether duckdb_jdbc can INSTALL/LOAD the three extensions from the app's container — or whether they should be bundled into the image — is spike work the lake connector owns.

4.3 Type mapper integration

Each dialect's typeMapper implements the per-dialect mapping tables in Type System §5. Adding a new dialect = writing a new DialectAdapter + IngressTypeMapper (~100–200 lines).

5. Connection Pool Configuration

HikariCP is the connection pool. Tuning is expressed as two namespaced passthrough maps under properties — there is no allowlist of supported keys in this spec.

properties.hikari.* — pool properties. Every entry is applied verbatim to HikariConfig using HikariCP's own property names and units (camelCase names; all durations in milliseconds, as HikariCP defines them). Any property HikariCP supports is therefore usable without a spec change. Illustrative — not exhaustive, not an allowlist:

The eight tunable keys (094). These are what the create/edit dialog renders and what the range rules below police. Every one is HikariCP's own property name; the "Default" column is the EFFECTIVE default this server applies, and the "From" column says which layer supplied it — dialect (a DialectAdapter.defaultHikariProperties declaration), server (this product's), or HikariCP (the library's own, read from a fresh HikariConfig rather than transcribed). No shipped dialect overrides a pool setting today, so the dialect layer is empty and the table below is the same for every dialect; the layer exists because the resolution is the adapter's to make and a form that hard-coded today's answer would be silently wrong on the first dialect that disagrees.

The ranges are REFUSALS, and that is the point (094). HikariConfig.validateNumerics() enforces almost all of these floors by logging a WARN and overwriting the value: maxLifetime = 5000 silently becomes 1 800 000, keepaliveTime = 10000 silently becomes 0 (disabled), leakDetectionThreshold = 500 silently becomes 0, minimumIdle = 50 against maximumPoolSize = 10 silently becomes 10, and an idleTimeout within a second of maxLifetime is silently disabled. (Only connectionTimeout, validationTimeout, a maximumPoolSize below 1 and a negative minimumIdle are hard refusals in HikariCP's own setters.) The value the row stores and the screen shows would then not be the value the pool runs with, and nothing would say so — so every one of them is refused at save with datasource.validation.properties_invalid, naming the key and the floor. None of these rules is invented: each restates a rewrite HikariCP would otherwise perform, and the test that justifies them builds a real pool with each out-of-range value and asserts the rewrite.

A field left at its default is not persisted. The dialog prefills every field with the effective default; only values that DIFFER are written to properties.hikari. Freezing today's defaults into every datasource created through the form would mean a later change to a product default reached nothing.

Reading the effective settings. GET /api/v1/datasources/{name} and the datasources_get MCP tool return a pool object beside properties: each key's effective value, its unit, and its source (configured / dialect_default / application_default / hikari_default). properties.hikari is what the row STORES — empty for almost every datasource; pool is what the pool RUNS with.

Server-managed keys. jdbcUrl, username, password, driverClassName, dataSourceClassName, poolName, metricRegistry, and healthCheckRegistry are derived from the entity and from the dialect adapter. Supplying any of them under properties.hikari is a validation failure (datasource.validation.properties_invalid) rather than a silent override.

properties.jdbc.* — driver connection properties. Every entry is passed through as a JDBC connection property (HikariConfig.addDataSourceProperty), i.e. what the driver would read from the Properties argument of DriverManager.getConnection. Values are strings. This is where TLS and driver-specific behavior live; the meaningful keys are the driver's, and each dialect adapter contributes defaultProperties (§4.2) that callers may override.

Postgres TLS example (sslmode values are the SslMode catalog):

Other dialects use their own equivalents (MSSQL encrypt/trustServerCertificate, MySQL useSSL/sslMode, Oracle wallet properties); this spec does not restate driver documentation.

Rationale. An allowlist of pool keys guarantees the one property an operator needs is the one we forgot. Passthrough plus save-time pool construction (§5.4) gives full coverage and fails invalid configuration before the row is written.

5.1 Pool sizing guidance

Per datasource, properties.hikari.maximumPoolSize should be sized to:

• Support concurrent pipeline node executions against this datasource.
• Stay under the source DB's connection limit.

Default 10 is conservative. High-traffic datasources can be tuned up. datapipelines.executor.max-parallel-nodes (Configuration §3.2, default 4) means up to 4 simultaneous queries against the same datasource within one execution; across executions, the pool is shared, so the practical upper bound is max-parallel-nodes × concurrent executions touching this datasource. With N replicas the bound of §5.7 applies — N pools per datasource: maximumPoolSize × replicas ≤ the source DB's connection limit.

5.2 Pool lifecycle

• Pool created lazily on first lease.
• Pool kept alive for the datasource's lifetime.
• On datasource update (PUT), the old pool is retired and the next lease builds a new one from the new row.
• On datasource delete (soft), the pool is retired and new leases miss it.

Retire, then close (094)

Until 094 a save or delete removed the pool from the map and close()d it in the same breath — and HikariDataSource.close() aborts the connections still in use once its shutdown grace elapses. An execution that happened to be mid-statement against that datasource lost its connection and failed. Retirement splits the two halves that were being conflated:

1. Retire. The pool leaves the live map at once (new leases miss it and build a fresh pool from the new row, exactly as before), its minimumIdle is set to 0 so the house-keeper stops refilling a pool nobody may lease from, and HikariPoolMXBean.softEvictConnections() runs: idle connections close now, in-use connections are marked and close when their borrower returns them — never mid-statement.
2. Reap. A per-instance scheduled tick closes each retired pool once its activeConnections reaches 0 — or at the ceiling (datapipelines.datasources.retire-ceiling-seconds, Configuration §3.26; default = node-query-timeout-seconds + 30 s), whichever comes first. A genuinely hung statement must not pin a deleted datasource's pool forever. A ceiling close logs one WARN naming the datasource and the connections it took down (Observability §3.4A) and increments datapipelines.datasource.pool.hard_closed; every retirement increments datapipelines.datasource.pool.retired.

Retiring pools are held in a queue, not a map keyed by name: a datasource saved twice in quick succession, with a lease in between, legitimately has two pools draining at once, and a name-keyed map would drop the first one un-closed. Application shutdown closes everything at once — ExecutionDrainLifecycle has already cancelled the live statements by then.

A LAKE pool generation owns its engine (152, #128)

For a LAKE datasource the pool is more than a set of connections: it OWNS one embedded DuckDB instance. Each pool build — the first lease, and every rebuild after a retire — is a generation: it opens ONE retained owner connection on the datasource's URL, initializes the instance on it exactly once (extensions, the S3 secret, ATTACHes, the engine limits of §8C.4, the per-table views of §8C.2), and keeps that connection open, never leased, for the generation's life. Every physical connection HikariCP opens is the driver's duplicate() of that owner — a second connection to the SAME instance — plus the session-scoped search path. Hikari's own lifecycle is unchanged: leases, validation, maxLifetime and idle replacement all still happen, but replacing a physical connection no longer discards the engine, its catalog or its external-file cache — until 152 every physical connection was its own instance, so a routine replacement (every 22.5–30 min by Hikari's defaults) sent the next scan of the same objects back to a cold read (the #128 timeouts).

The ownership rules, each pinned by a test that its inverse turns red:

• One owner = one generation = one instance. A rebuild opens a NEW owner, so a retiring generation and its replacement never share catalog, secret or cache state, and two datasources never share an instance — the anonymous jdbc:duckdb::memory: URL has no process-global name to collide on. An in-flight reader on the old generation keeps its snapshot; new leases see the new rows.
• Retire leaves the owner alive; reap closes it — and ONE caller owns the whole close. The old generation's instance lives while its leases drain under the rules above and is released by the reaper's close, which is a fixed sequence: the owner is marked retiring (no new physical connection may join the generation from here on), Hikari shuts down (its bounded wait for a connection mid-creation, then its existing abort-at-the-ceiling policy for a stuck borrower), then the owner is released. The first close() runs all three steps; a concurrent or later close() returns at once without touching the owner — the same contract HikariDataSource.close has for its own second caller, and the reason the guard sits before Hikari's close rather than around the release. Every physical connection the generation creates stays registered with it until its closure is CONFIRMED — the driver has returned from close() without throwing, through the connection's own wrapper (a throwing close, an abort() — asynchronous by JDBC contract, refused outright by the pinned DuckDB driver — or a close still in flight on another thread leave it registered). At the generation's release, whatever is still registered is released by the generation itself: a creation that straddled the shutdown and was refused by Hikari's already-closed bag (HikariCP does not close what it refuses — its JDBC setup and bag admission run after the datasource has returned the connection, past any retire check), a borrower Hikari abandoned at its own ceiling, and a handle whose earlier close the driver refused are all closed there, and counted in the lake.instance_handles_closed WARN as closed / already_closed / in_flight / close_failures (Observability §3.4C). Ownership is a per-handle protocol (one metadata lock per handle, one monitor for the registry, no driver call under either, nothing waits): at every instant an identifiable actor owes the next close attempt — the holder while it is inside the driver, the generation until its release has passed, the in-flight closer after the release found it inside the driver (the hand-off is decided in that closer's own failure transition, so it cannot be missed), and the creator for a duplicate refused at registration (registered for accounting; closed by the creator; left to the release if it has not run, retried once by the creator if it has). The attempt bound, exactly: every explicit close() makes at most one driver attempt of its own plus at most one hand-off retry over the handle's life; a concurrent close that finds another close in flight returns immediately without making an attempt; the generation makes at most one per handle; a SQLException and a nonfatal RuntimeException from the driver are the same failure transition and never leave a handle mid-close with its actor gone, never skip a sibling, never skip the retained owner. If the generation wins the close and a concurrent holder returns while it runs, a refusal of that sole generation attempt is a counted, reported residual even if the refusal was transient; there is no guaranteed second attempt in that order. The retained owner connection itself gets one close attempt under the same policy — an SQLException or a nonfatal RuntimeException is contained and reported (lake.instance_owner_close_failed, and lake.instance_closed says retained_owner_closed=false), never propagated: the release runs inside the shared pool manager's loop over every retired pool, and one lake's driver fault must not stop the cleanup of the pool behind it. So no duplicate outlives its generation and the instance is freed with it; the residuals are a duplicate whose every permitted close the driver refuses (registered, counted, reported once as lake.instance_handle_exhausted) and a retained owner connection the driver refuses to close (reported once) — neither is ever called closed.
• Fully initialized or not at all. A strict-init failure (adapter init, prelude) or a failed Hikari construction closes the owner before the error propagates; no half-initialized instance is ever handed to a pool. A per-table view failure is isolated as before (§8C.2).
• No silent reconnect. If the owner is ever lost, new leases fail with a connection exception (SQLSTATE 08003) naming the datasource and generation, logged once — the pool never opens a fresh engine behind the caller's back, because that engine would carry none of the generation's views or secrets. Retire-and-rebuild is the remedy.
• The owner is not a borrower. It occupies no maximumPoolSize slot and is not an active connection in the reaper's count or the metrics.

Scope of the shared instance. Every shipped and documented LAKE URL is the anonymous in-memory form, and the isolation above is exactly that form's. A jdbc:duckdb:memory:<label> or file-backed jdbc:duckdb:/path.db URL passes validation and takes the same owner path, but those URLs name a JVM-wide instance the driver shares by name/path — two datasources on the same label, or a retiring and a fresh generation of one, share it. That sharing pre-dates 152 and is not changed by it; the anonymous URL is the supported shape. Saved URLs are never rewritten. The registration pre-flight, the §8.1 probe and the save-time test build (§5.4) each open their own scratch engine and never join a live generation.

Reconcile on (re)subscribe. §5.7's Redis channel is fire-and-forget: a message published while an instance was disconnected is gone, and pub/sub has no replay. So every instance also implements the subscription callback its listener container fires on the initial subscribe AND after every reconnect, and each callback compares the updated_at its live pools were built from against the rows — retiring every pool whose row has moved or gone. One two-column query, no Redis key, no TTL, nothing to expire wrong. A missed ping is caught at reconnect, by comparison rather than by replay.

Concurrency. poolFor(datasource) (§6.1) is called from many executor coroutines at once, so lazy initialization must be atomic: pools live in a ConcurrentHashMap<String, ConnectionPool> keyed by datasource name and are created with computeIfAbsent, so exactly one HikariDataSource is constructed per datasource even under a concurrent first-lease burst. Two consequences the implementation must respect:

• The mapping function does no blocking I/O beyond HikariDataSource construction (Hikari fills the pool asynchronously; initializationFailTimeout is left at Hikari's default for runtime pools, so an unreachable DB surfaces as a lease failure, not a map-wide stall).
• Replacement on update/delete is remove()-then-softEvict() on the retired pool, never anything at all on a pool still reachable from the map — in-flight leases drain against the old instance while new leases go to the new one. The close() belongs to the reaper above, not to the save.

5.3 Lease lifecycle

suspend fun <T> withConnection(datasourceName: String, block: (Connection) -> T): T {
    val datasource = registry.get(datasourceName) ?: throw DatasourceNotFoundException(...)
    val pool = poolFor(datasource)
    val connection = pool.connection    // blocks up to hikari connectionTimeout
    return try {
        block(connection)               // caller sets Statement.setQueryTimeout per §5.5
    } finally {
        connection.close()              // returns to pool
    }
}

No credential decryption happens on this path — the pool already holds the credential from its build (§7.4).

Acquisition timeout (properties.hikari.connectionTimeout, 30 000 ms default) exceeded → pipeline.node.datasource_connection_failed.

5.4 Test pool build (save-time validation)

Datasource create and update run a test pool build before the row is written — this is how the passthrough model of §5 stays safe without an allowlist, and it is this entity's instance of the universal validate-on-write principle (Pipeline Contract §2).

Sequence:

1. The dialect adapter builds a HikariConfig from the entity fields plus defaultProperties.
2. Every properties.hikari.* entry is applied to that HikariConfig. HikariCP resolves property names reflectively — an unknown name, a wrong value type, or an out-of-range value throws here.
3. Every properties.jdbc.* entry is added via addDataSourceProperty.
4. HikariConfig.validate() runs, then a HikariDataSource is constructed with initializationFailTimeout = -1 so no connection to the source database is required to save the entity.
5. The test pool is closed. Nothing from it is retained.

Any failure in steps 2–4 rejects the save with datasource.validation.properties_invalid; the error details carry the offending key and the underlying message.

Limits of this check, stated honestly: it validates pool configuration completely and driver property names only to the extent the driver rejects unknowns at Properties parse time. Driver properties that are only interpreted while opening a socket (a bad sslrootcert path, an unsupported sslmode value) surface at first connection and via POST /api/v1/datasources/{name}/test (§8.1) — not at save. Save-time validation guarantees a constructible pool, not a reachable database; reachability is deliberately not a save precondition (a datasource may legitimately be registered before its network path exists).

5.5 Query timeout precedence

Per-node JDBC statement timeouts resolve in one order (156, #2 — the full five-tier precedence, including the pipeline- and node-authored overrides above this datasource tier and the operator's per-dialect default below it, is stated once in Configuration §3.2 and pipeline-contract §4.11/§5.3; this datasource's own place in that order is):

1. The datasource's query_timeout_seconds, when set — it overrides the pipeline's own setting and every operator default for every node executing against this datasource, but is itself overridden by that node's or its pipeline's own settings.query_timeout_seconds.
2. Otherwise the operator's per-dialect default (node-query-timeout-seconds-by-dialect.<dialect>), else datapipelines.executor.node-query-timeout-seconds (default 60), both defined in Configuration §3.2.

The executor applies the resolved value with Statement.setQueryTimeout per node. Note this is a per-statement timeout and is independent of datapipelines.executor.execution-timeout-seconds, which bounds the whole execution.

5.6 Refused property keys (normative security exception to passthrough)

Passthrough (§2 principle 8) has one bounded exception: a key is refused when the pinned driver treats its value as a class name to instantiate, a file path to read or write, connect-time SQL, or a TLS-verification switch. Refusal applies to both carriers identically: a key rejected under properties.jdbc.* (datasource.validation.properties_invalid) must also be rejected when smuggled into jdbc_url's query/property segment (datasource.validation.jdbc_url_malformed) — the URL and the property map are validated against the same union of the server-managed set and the dialect's refusal set.

Credentials are refused in the URL outright: user, password, and driver aliases (e.g. MSSQL userName) must arrive via the dedicated username/password fields — jdbc_url is stored plaintext and returned to read-scope principals (§3.2), so a credential embedded there defeats §7.1 encryption at rest. This covers a credential smuggled as a query/property key and a userinfo authority in any position — not only a leading //user:pw@host but Oracle's native jdbc:oracle:thin:user/pw@//host and H2's jdbc:h2:tcp://user:pw@host forms, whose scheme prefix precedes the authority. The authority scan must find the user[:/]…@ segment wherever it appears, not key off a // prefix.

Secret-valued properties are refused in both carriers, regardless of whether they also load a class or name a file. properties.jdbc is stored plaintext in properties_json and returned to read scope (§3.2) exactly like jdbc_url, so any property whose value is credential material is a plaintext-secret exposure. Beyond the enumerated per-dialect keys, a suffix predicate over the key name refuses *password, *passwd, *pwd, *secret, and *clientkey (case-insensitive) — layered on top of the tabled sets so a new driver version's secret key is covered by construction. Named instances that the predicate would otherwise miss (e.g. MSSQL keyVaultProviderClientKey) are also listed in the dialect set.

The TLS-verification switches refused below (trustServerCertificate, verifyServerCertificate, allowPublicKeyRetrieval) are best-effort: the sslmode / useSSL / sslMode family is deliberately left operator-controlled (§5, Enums §14) because operators legitimately select TLS modes. Refusing a hard "trust anything" switch on one dialect while permitting a mode selector on another is intended, not an inconsistency.

The authoritative enumeration is the module's per-dialect refusal sets, pinned by tests against the driver versions in libs.versions.toml; a driver upgrade must re-review its dialect's set. Each set must at minimum refuse:

The credential-carrier rule (087), stated once: a credential travels ONLY in credential (§3.4) — never in jdbc_url, never in properties.*. credential.secret is encrypted at rest (§7.1); jdbc_url and properties_json are stored plaintext and returned to read-scope principals (§3.2). There is no third carrier and no exception.

The suffix predicate below covers …password, …passwd, …pwd, …secret and …clientkey, which is narrow on purpose — a legitimate property cannot end in one of those. Narrowness has a cost the 2026-09-07 contract audit found: it misses every secret whose name ends in a bare key or token, and both spellings are real in the drivers of the connectors on the roadmap. Verified against the vendors' current documentation, read 2026-09-07: BigQuery's OAuthPvtKey is "the service account key … a raw JSON keyfile object or a path", and OAuthAccessToken/OAuthRefreshToken are the pre-generated-token credentials; Databricks's OAuth token-passthrough flow is Auth_AccessToken=<token>. Those names are ENUMERATED (RefusedPropertyKeys.SECRET_VALUED_KEYS) rather than fixed by widening the suffix list, because key/token as suffixes would refuse future properties that merely end in those words and destroy the predicate's justification. The rule that follows: before a dialect ships, every driver property whose value is credential material and whose name escapes the suffixes is added to that set. The names are cross-dialect, so the guard exists before the dialect does and nobody has to remember.

Under properties.hikari, exceptionOverrideClassName joins the server-managed refusal set (arbitrary class instantiation). connectionInitSql joins it too (DS-SEC-22, 087): HikariCP runs it on every new connection, so it is connect-time SQL — the same §5.6 category as H2's INIT and DuckDB's session_init_sql_file, and it executes IN THIS PROCESS on the embedded engines. It is additionally server-DERIVED since 087 (§4.2A), so an operator passthrough would also silently replace the adapter's own setup. readOnly joins it too (workspaces design §6 layer 2b): the flag is the entity's — the server derives HikariConfig.isReadOnly from is_readonly — so operator passthrough must not flip it EITHER way on ANY datasource (datasource.validation.properties_invalid for true on a writable datasource and for false on a readonly one alike; silently hardening a writable one would be as much a lie as silently un-hardening a readonly one, and a properties-derived flag is a second source of truth the executor never sees). The refusal sets are part of every dialect adapter's contract — an adapter without a reviewed set is a defect, and the validation path must fail closed (an unknown or non-conforming adapter yields no exemption from refusal, never an empty set).

Embedded in-process dialects harden at the adapter, not just the refusal set (normative). DuckDB and SQLite run inside the server JVM, so author-authored SQL against such a datasource executes in-process — a loaded native extension is arbitrary code in the server, not in a remote database. The refusal set governs properties.jdbc/jdbc_url keys, but DuckDB autoloads known/community extensions with no property involvement at all (allow_community_extensions and autoload_known_extensions default true). Therefore DuckdbDialectAdapter.defaultProperties sets, at connect (exact set verified against the pinned driver): allow_unsigned_extensions=false, allow_community_extensions=false, autoload_known_extensions=false, autoinstall_known_extensions=false, enable_external_access=false. The load-bearing lock is enable_external_access=false: it is non-overridable by session SQL (a SET … = true from author SQL fails — "cannot enable external access while database is running"), and with the filesystem and network off, no INSTALL/LOAD/ATTACH/read_csv/COPY path is reachable regardless of the autoload toggles. (Verified: autoload_known_extensions/autoinstall_known_extensions remain settable at runtime, but are inert — every actual load path is closed by the external-access lock; a LOAD json succeeds only because that extension is statically linked into the pinned jar, not fetched.) These five keys are additionally refused in properties.jdbc / jdbc_url (the DUCKDB entry of the §5.6 refusal set) — because properties.jdbc is applied after defaultProperties (§4.2), an operator could otherwise set enable_external_access=true and re-open the RCE surface; for an in-process engine that operator foot-gun is refused, not merely defaulted. SqliteDialectAdapter.defaultProperties sets, at connect: enable_load_extension=false (explicit hardening; already the driver default) and limit_attached=0. The load-bearing lock is limit_attached=0: this sets SQLITE_LIMIT_ATTACHED via the xerial driver's sqlite3_limit() call, which runs before author SQL and prevents any ATTACH DATABASE — a filesystem-access primitive that would let an attacker open and query any file on the server filesystem. Both keys are additionally refused in properties.jdbc / jdbc_url (the SQLITE entry of the §5.6 refusal set) so an operator cannot set limit_attached=10 and re-open the surface. This is the datasource analogue of Staging §9.5's de-privileging: an in-process engine must not give author SQL — or an operator's properties.jdbc — a code-execution or filesystem-access primitive.

5.7 Readonly datasources (flag semantics and enforcement layers)

A datasource flagged is_readonly (metadata-db §4.10, V4; workspaces design 2026-08-16 §6, D6) forbids the three and only three write-shaped uses in the pipeline contract: a DML node's source, a DDL node's source, and any node's output.target: "datasource". DQL reads and everything tempdb are untouched — the check is on the use, never on the datasource alone. Enforcement is layered, each layer with its own raise site:

1. Save-time validation (primary UX): pipeline.validation.datasource_readonly (HTTP 400, Pipeline Contract §12.5) — one code, details carrying node id + datasource name + which shape fired. Reads the LIVE row, past the §6.3 metadata cache (044 F4): the resolver behind the contract registry (getVisibleLive / getLive) answers from the same row the executor's live backstop answers from, so a row-level flag flip — either direction — is honored by the next save, not by the next cache expiry. A cached read here would have refused VALID saves in the un-flip direction with a wrong 400 that no other layer covered. Cost: one indexed PK read per referenced datasource per save; the REST GET hot path keeps the §6.3 cache.
2. Executor backstop (the D10 flip window): pipeline.node.datasource_readonly (HTTP 500, Pipeline Contract §13.4) — the executor re-checks the live registry entry at node execution time, past the §6.3 metadata cache, so a datasource flipped readonly after a pipeline version was saved fails at the NEXT execution, not at the next cache expiry. Covers all three shapes and a composed PIPELINE node's child nodes (each child node passes the same backstop in its own execution). The read is flag-only (isReadonlyLive — one indexed SELECT is_readonly, no credential ciphertext, no properties parse) per write-shaped node execution, and a DQL node whose output.target: "datasource" names a readonly datasource is refused at the CONNECT phase, before its source query runs (044 F9) — the write-back shell re-checks at write time and remains authoritative.
3. Pool-level connections (defense in depth): the dialect adapter builds every pool for a readonly datasource with Hikari readOnly = true — the pool-level flag, proven on the real pool build. Treated as defense in depth, not proof, for two stated reasons: JDBC read-only enforcement strength varies by driver (the Postgres driver enforces read-only transactions; others are advisory), and pool→connection propagation of the flag is not guaranteed either (verified against the pinned HikariCP 6.3.0 + H2 2.3.232: the flag reaches the pool, not the leased connection). properties.hikari.readOnly is §5.6-refused in both directions for exactly this reason (§5.6, above). DuckDB-family exception (089, corrected 2026-09-08): the DuckDB driver refuses to CHANGE a connection's read-only state, and HikariCP calls setReadOnly exactly when the connection's own isReadOnly() differs from the pool flag — so for DUCKDB and LAKE the pool flag MIRRORS the state the connection opens in (properties.jdbc.access_mode: READ_ONLY ⇒ true, otherwise false) instead of following readonly; the D6 layer-2a executor backstop is the enforcement that remains. For LAKE that opening state is the pool generation's (§5.2, 152): every physical connection is a duplicate of the generation's owner and reports the access mode the owner opened with, so the flag is one value per generation, never per connection. Forcing it either way fails the whole pool build: forced on broke the :memory: lake (089 §F), forced off broke every --demo trade datasource (093 §2). Cross-instance eviction (050/R1, M3 closed for registry-mediated writes): every registry save/delete publishes the datasource name on the Redis channel dp:datasource-invalidated after the row commits, beside the synchronous local eviction; every instance subscribes (Spring's RedisMessageListenerContainer, subscribed before the instance serves traffic) and evicts its pool for that name, so the next use rebuilds from the row — an operator repointing a datasource or rotating a password through instance A no longer leaves instance B on stale credentials/URL until restart. The publishing instance does not act on its own message (local eviction already ran); a Redis fault at publish degrades to a WARN, never a failed save. Global datasources (workspace = null) ride the same channel — instances stay symmetric, deliberately (R1 rejected workspace→instance affinity). Sizing under replication: with N instances a datasource has N pools, so properties.hikari.maximumPoolSize × replicas ≤ the customer database's connection limit must hold for every datasource. Known residual window (044 F5, narrowed): a row-level flip written out of band (manual SQL/restore, the D10 channel — not a registry save) still publishes nothing and leaves pre-flip pools serving until the next registry save/delete on that datasource or a restart; the window is bounded in practice: every write-shaped path crosses layer 2's live check first, so a stale writable pool cannot ship a write layer 2 refuses — the stale pool only serves reads the flag never forbade. This is the same mechanism the architecture audit tracked as M3, resolved 050 (ARCH-AUDIT-2026-08).

The backstop's null semantics (044, normative). The live read is three-valued, and all three values are decisions — "I could not read the row" is never "there is no restriction":

• live row readonly → refuse (pipeline.node.datasource_readonly);
• live row writable → proceed;
• no live row (soft-deleted by manual SQL — the D10 channel — or unknown) → refuse (pipeline.node.datasource_not_found): a cached entry and a warm pool must not carry a write to a datasource the live registry no longer holds;
• the read itself failed (metadata database unavailable) → refuse with pipeline.execution.aborted, the message naming the metadata database, never the healthy target datasource.

Metadata-DB dependency (044 F3, stated): every write-shaped node execution — and, since F4, every save-time validation — performs the live read above, so both depend on metadata-DB availability at that moment. This is the cost of fail-closed: an unreachable metadata DB refuses writes rather than guessing the flag, and the refusal says which database to page.

Normative deployment guidance (layer 3): the flag is contract, not containment. A datasource whose data must not change gets a SELECT-only database user regardless of the flag — the flag gives agents fast machine-readable feedback and the executor a deterministic refusal, but only the credential gives the guarantee (the datapipelines.co demo's seeded datasources are global + readonly AND run under SELECT-only users).

The flag is a first-class writable field since the workspaces surfaces slice. Whoever may edit a datasource may flip readonly on it — EXCEPT on a GLOBAL datasource, where only a global admin may (workspaces design §6 last paragraph, D8). Every flag write crosses the registry's save boundary, so the pool is drained on update and rebuilds under the new readOnly setting at the next lease; there is no column-level UPDATE path, by construction. The §5.2 pool lifecycle is the mechanism — this paragraph is the pointer, not a second statement of it.

6. Datasource Registry

6.1 Interface

interface DatasourceRegistry {
    fun list(dialect: Dialect? = null): List<Datasource>   // optional filter backs GET /datasources?dialect= (§11)
    fun get(name: String): Datasource?       // null if not registered or soft-deleted
    fun getLive(name: String): Datasource?               // full live row, past the §6.3 cache (044 F4/F6 — abstract)
    fun getVisibleLive(name: String, workspaceId: UUID): Datasource?   // save-time validation's live read (§5.7 layer 1, 044 F4)
    fun isReadonlyLive(name: String): Boolean?           // the executor backstop's flag-only read (§5.7 layer 2, 044 F2/F7 — abstract)
    fun exists(name: String): Boolean
    fun save(datasource: Datasource, actor: UUID): Datasource   // create or update; validates first (§5.4, §9). actor: created_by (Metadata DB §4.10) + the §7.4 audit actor
    fun delete(name: String): DeleteResult         // soft delete; fails if in use
    fun poolFor(datasource: Datasource): ConnectionPool   // lazy, thread-safe (§5.2)
    fun testConnection(name: String): TestResult?  // null = no such datasource (caller maps to 404); §8.1's "HTTP 200 always" applies only when it exists — records the outcome on the row (§8.1B)
    fun validate(datasource: Datasource): ValidationResult
    fun resyncBootstrapCredential(name: String, fileCredential: String): CredentialResync  // §8A.3 rule 3; defaults to NOT_APPLICABLE
}

(v1.4: save gained the required actor parameter — created_by is NOT NULL and §7.4 audit events record an actor, so the v1.1 signature was unimplementable; list gained the optional dialect filter §11 already promises; testConnection returns null for an unknown name instead of a synthetic failed TestResult whose errorClass was not an FQCN. 044: the live reads are first-class — getLive and isReadonlyLive are abstract (020 F6: a cached default was the exact hole the live read exists to close), getVisibleLive defaults to getVisible for in-memory fakes; visibility-scoped listVisible/getVisible (workspaces design §5.3) are elided from this sketch, not from the interface.)

Return types:

/** Outcome of a soft delete. Never throws for the in-use case — the caller needs the list. */
data class DeleteResult(
    val deleted: Boolean,
    val name: String,
    val errorCode: String? = null,          // 'datasource.in_use' when deleted = false
    val references: List<DatasourceReference> = emptyList()  // the ANY-VERSION scan (§6.2): one entry per referencing NODE
) {
    val referencingPipelines: List<String> get() = references.map { it.pipelineName }.distinct()
}

/** One node of one stored pipeline version referencing a datasource — the delete guard's evidence (§6.2). */
data class DatasourceReference(
    val pipelineName: String,
    val pipelineVersion: Int,               // WHICH stored version carries it — the field the old guard did not have
    val versionStatus: String,              // DRAFT | RELEASED | DISCARDED
    val nodeId: String
)

/** Which branch of §8A.3 rule 3 ran for one bootstrap entry. */
enum class CredentialResync {
    NO_LIVE_ROW, CREDENTIAL_MATCHES, STORED_WORKS, RESYNCED, BOTH_FAILED, STORED_UNREADABLE, NOT_APPLICABLE
}

/** The stored outcome of the last connection test (§8.1B) — the persisted twin of TestResult. */
data class DatasourceTestOutcome(
    val testedAt: Instant,
    val ok: Boolean,
    val message: String? = null             // driver message on failure, server version on success; scrubbed at the probe
)

/** Outcome of a live connectivity probe (§8.1). Failure is data, not an exception. */
data class TestResult(
    val connected: Boolean,
    val testedAt: Instant,
    val latencyMs: Long? = null,            // present when connected
    val serverVersion: String? = null,      // DatabaseMetaData.getDatabaseProductVersion(), when connected
    val error: String? = null,              // message, when not connected
    val errorClass: String? = null          // exception FQCN, when not connected
)

/** Outcome of save-time validation (§5.4, §9). Also returned by DialectAdapter.validateJdbcUrl. */
data class ValidationResult(
    val valid: Boolean,
    val errors: List<ValidationError> = emptyList()
) {
    data class ValidationError(
        val code: String,                   // a §9 code, e.g. 'datasource.validation.properties_invalid'
        val field: String?,                 // JSON pointer-ish path, e.g. 'properties.hikari.maximumPoolSize'
        val message: String                 // human-readable; safe to surface (never contains credentials)
    )
    companion object { fun ok() = ValidationResult(true) }
}

ValidationResult.errors is complete, not first-failure — a save returns every rule that failed so the UI can render one form pass. TestResult.error and ValidationResult.ValidationError.message are redaction-scrubbed: neither ever carries password or the credential portion of a JDBC URL (redaction rules: Observability spec).

6.2 In-use check on delete

DELETE /api/v1/datasources/{name} fails with datasource.in_use if any non-deleted pipeline references this name in any version it has ever stored — DRAFT, RELEASED and DISCARDED alike. Pipeline versions are immutable and executable by explicit version (Versioning §7.1), so a reference living only in a released v1 that a later v2 dropped is a live reference: deleting the datasource out from under it fails that version's next execution at connect.

This is the any-version scan, and it is deliberately not the one the pipelines listing uses. The two questions are different and 040 already split them for templates:

Conflating them is how the guard under-reports. It did: the delete guard read current_version only until 2026-09-03, so a released pipeline whose older version pinned the datasource was invisible and the delete succeeded.

The error response carries the distinct pipeline names AND one entry per referencing node with the pipeline version and that version's status — the shape template.in_use uses — because the version is what tells the operator which body to go and change.

The screen asks the same question, first (094). The datasources list's Delete action opens a dialog that runs THIS scan before it offers anything, and renders its rows as pipeline › node (v3 released). On the in-use branch there is no confirm button at all, so the refusal cannot be clicked past; only an unused datasource gets a confirm, and that confirm names it. The dialog and the 409 cannot disagree because they are the same scan — but the POST re-runs the guard anyway: a pipeline can start referencing the datasource between the dialog opening and the button being pressed, and the screen is never the authority. The readonly / global D8 rules apply exactly as they do for update (a member deleting a global datasource is told so before the scan even runs).

Deleting a datasource retires its pool on every instance rather than closing it (§5.2), so a query already running against it finishes on the connection it holds. That is what the dialog's "queries already running finish first" sentence promises, and what the two-instance harness measures.

6.3 Cache

• Datasource metadata cached in memory (low churn).
• Cache invalidated on create/update/delete — local instance only.
• Entries carry a short TTL (default 60s, matching the auth liveness cache) so a change made on one instance becomes visible on every other instance within the TTL. The local invalidation is an immediacy optimization for the instance that made the change; the TTL is what bounds cross-instance staleness in the multi-instance deployment model (auth §8) — without it, an operator repointing a datasource would be invisible to sibling instances until restart.
• Negative lookups are never cached (a miss re-reads), so the cache cannot be grown by GETs for non-existent names and is bounded by the number of real datasources.
• Connection pools cached separately (lazy init, see §5.2); the pool-build path bypasses this cache (it must reload the encrypted credential).

7. Credential Storage

7.1 Encryption at rest

Passwords are never stored in plaintext. Encryption approach:

• AES-256-GCM. Stored value = version ‖ nonce ‖ ciphertext ‖ auth tag; a fresh random 96-bit nonce per encryption.
• The first byte is the KEY VERSION the value was sealed under (1..255; 0 is reserved as "not a version", so a zeroed page cannot forge one). It selects which data key decrypts the row. It is not itself authenticated and does not need to be: it picks a key, and a wrong key fails the GCM tag — so flipping it is a tamper signal either way. A blob whose version this deployment holds no key for is refused with the same decryption error, which is the "wrong deployment / retired key" signal.
• The datasource name is bound as GCM associated data (AAD) on both encrypt and decrypt. A stored ciphertext therefore decrypts only under the name it was sealed with: a row copied or renamed at the database level fails the tag rather than silently decrypting, closing the lift-a-ciphertext-to-another-row attack (name is immutable, §11.1, so this never obstructs legitimate use). Any code path that decrypts — pool build, connection test, and the §7.3 rotation pass — MUST pass the row's name as AAD.
• Data keys come from a key provider (§7.1.1), selected by datapipelines.db.key-provider (Configuration §3.20) and defaulting to env.
• Keys are required and fail-fast: a missing key, invalid base64, a key that is not exactly 32 bytes, or a provider whose backing service is unreachable stops startup. There is no fallback chain — no implicit KMS lookup, no generated key file.

Why no fallback. A silently generated key file is how credentials become undecryptable on the next redeploy (new container, new file, every stored password now garbage) — the failure appears long after the deploy that caused it, and no backup of the metadata DB can repair it. Refusing to start is the cheap failure.

7.1.1 The key provider seam

The seam is the key, not the ciphertext. One CredentialEncryptor implements the crypto above; where its keys come from is an interface:

package co.datapipelines.datasources.crypto

/** A 32-byte AES key and the version the ciphertext will carry. */
class DataKey(val version: Int, val bytes: ByteArray)   // version 1..255

interface KeyProvider {
    val name: String                     // the config value that selects it: "env", "aws-kms", …
    fun current(): DataKey
    fun byVersion(version: Int): DataKey?
}

current() supplies the key for every new encryption; byVersion() the key for a row written under an earlier one. An unknown version returns null, which the encryptor turns into the decryption error. Three invariants every implementation owes, pinned by one shared suite (KeyProviderContractTest):

1. current() is stable for the life of the process — the encryptor caches it at construction.
2. byVersion(v) keeps answering for any v that current() ever returned on this deployment, for as long as a stored row carries it.
3. Both fail at boot, not at first decrypt, when the backing service is unreachable — a misconfigured pod must never start and serve.

A provider never sees a password, and never logs key material.

The shipped provider is env: datapipelines.db.encryption-key is version 1 forever; optional datapipelines.db.encryption-keys adds version → base64 entries for a rotation; datapipelines.db.encryption-key-current selects which one new writes use (default: the highest configured). All of it is Configuration §3.20.

Implementing another one — AWS KMS, GCP KMS, Azure Key Vault, Vault transit — is a written procedure, not a design exercise: see Key providers. It is an implementation of the contract above plus a config block, a validator branch and a subclass of the shared contract suite. Nothing in the crypto changes.

7.2 Schema

DDL authority: Metadata DB §4.10. No DDL block lives in this spec — metadata-db.md is the only doc that writes DDL, so there is exactly one table definition to keep true.

The semantics this spec depends on, which the DDL must satisfy:

• name is the primary key (TEXT), constrained to 63 characters and to the identifier regex of §9 via CHECK — pipelines reference datasources by this value, so it is also the immutability anchor (§11.1).
• description is optional (nullable / no NOT NULL requirement) — matching §3.3.
• credential_encrypted (V13; password_encrypted before it) is BYTEA — AES-256-GCM output per §7.1 (version ‖ nonce ‖ ciphertext ‖ tag), never plaintext, never returned by any endpoint. Rows written before the version byte existed were prefixed with 0x01 by a one-off migration; the application accepts ONLY versioned blobs and never guesses the old layout. NULLABLE since V13, and null exactly when credential_kind = 'none' — the blob itself is kind-agnostic (§3.4), so a token, a PEM and a service-account document are sealed exactly as a password is.
• credential_kind is TEXT NOT NULL DEFAULT 'password' with a CHECK over the Enums §5A set (V13, §3.4); username is nullable, and two further CHECKs pin the §3.4 field rules at the column.
• properties_json is JSONB and holds the §5 object verbatim ({"hikari": {...}, "jdbc": {...}}), defaulting to {}.
• dialect is TEXT with a CHECK constraint enumerating the Type System §5 dialect values — a database-level guard duplicating the §9 application check on purpose.
• created_at / updated_at are TIMESTAMPTZ (UTC); updated_at is set by the application in every UPDATE.
• created_by is a UUID foreign key to users(id).
• is_deleted supports soft delete (§6.2); lookups filter it.

7.3 Key rotation

The version byte (§7.1) makes rotation lazy-safe: there is no big-bang re-encrypt and no window in which two keys must be applied by hand.

The flow an operator runs:

1. Generate a key: openssl rand -base64 32.

2. Add it as the next version and make it current (Configuration §3.20):

   datapipelines:
     db:
       encryption-key: ${DATAPIPELINES_DB_ENCRYPTION_KEY}          # version 1, unchanged
       encryption-keys:
         2: ${DATAPIPELINES_DB_ENCRYPTION_KEY_V2}
       encryption-key-current: 2
   

3. Restart. Every NEW encryption carries version 2. Every existing row keeps decrypting under the version it carries, because version 1 is still configured.

4. Rows migrate to the current key as their passwords are next saved — no pass over the table, no downtime.

5. Retire version 1 only when no row still carries it. That is one query, and it is the only thing that answers the question:

   SELECT get_byte(credential_encrypted, 0) AS key_version, count(*)
   FROM datasources
   WHERE credential_encrypted IS NOT NULL
   GROUP BY 1 ORDER BY 1;
   

   While that reports any key_version = 1, removing the version-1 key makes those rows undecryptable. When it reports only 2, datapipelines.db.encryption-key may be replaced with the version-2 material (and encryption-keys emptied) at the next restart.

Deliberately NOT shipped: a rotation endpoint or CLI that decrypts and re-encrypts every row. It is not needed for correctness under versioned blobs, and it is a bulk-decrypt surface — every stored password in memory in one pass, behind a trigger an operator can be talked into pulling. The audit event datasource.key_rotation (Enums §15) stays registered and is emitted by nothing. An operator who needs every row moved NOW — a suspected key compromise — re-enters the affected credentials, which also rotates the database passwords, which is what a key compromise actually calls for.

A re-encrypt, whenever one happens, must carry the row's name through both halves as AAD (§7.1): a decrypt/re-encrypt that drops it fails every GCM tag.

7.4 Decryption points and audit log

Decryption happens once per pool build — not once per connection lease. HikariCP necessarily holds the credential for the pool's lifetime (it opens new physical connections on its own schedule, without the caller present), so a per-lease decrypt would be theatre: the plaintext is already resident in the pool. The credential is decrypted exactly at:

1. Pool build / rebuild — lazy first lease (§5.2), or a rebuild after datasource update or key rotation.
2. Connection test (§8.1) when it constructs a throwaway pool for a datasource with no live pool.
3. Key rotation (§7.3), which decrypts every row in one pass.

An audit event is written at each of those points — never per lease. The module emits them through an injected audit sink (a fun interface sibling of DatasourceReferences, with a no-op default so the module stays dependent on typesystem alone); the application wires the sink onto the shared audit_log writer at assembly (v1.4). The earlier "audit every lease" model produced unbounded event volume (one row per query, per node, per execution) that recorded nothing the execution record did not already contain, and it implied a decrypt that does not happen.

pool_build vs pool_rebuild timing. A datasource update evicts the live pool immediately (synchronously, with the operator's identity in hand) but decrypts nothing then — the lazy rebuild on the next lease is what decrypts, and it emits its own pool_build (actor = system, executor-initiated). So an update to a datasource with a live pool produces pool_rebuild (operator actor, marks the eviction) followed later by pool_build (system actor, marks the actual decryption). pool_rebuild exists to capture the operator who triggered the change — an identity that is gone by the time the lazy build runs — not to mark a decryption of its own. actor is the system principal for executor-initiated pool builds and the operator's user id for operator-initiated actions (update, connection test); cause (execution id + node id) is populated only for a pool_build triggered from an execution, where the executor supplies the context.

Each event records:

• Timestamp (TIMESTAMPTZ, UTC)
• Datasource name
• Event name: datasource.pool_build | datasource.pool_rebuild | datasource.connection_test | datasource.key_rotation (registered in Enums §15 alongside the auth audit events)
• Actor (user id for operator-initiated actions, or the system principal for executor-initiated pool builds)
• Cause, when the trigger is pool_build from an execution: the execution id and node id that took the first lease

Stored in the audit log table (Metadata DB §4.3); retained per datapipelines.audit.retention-days (Configuration §3.12).

Per-node datasource usage remains observable without any credential-audit event: it is already recorded on the execution and its per-node stats.

7A. Schema Introspection

Shipped in v1.1 (was datasources §14 future work). Read-only live schema metadata over a registered datasource's JDBC DatabaseMetaData, so agents can enumerate real schemas, tables and columns instead of hallucinating them when authoring SQL templates.

Three read operations, all served by the module's SchemaIntrospector through the existing DatasourceRegistry pool (poolFor, §5.2 — introspection opens a live connection, exactly like a connection test). They form the only introspection flow: schemas → tables → columns — list the schemas, list one schema's tables, then read columns for only the tables the SQL needs. Nothing bundles columns into a table listing; table listings stay lightweight so more tables fit in one response.

The table-type vocabulary and the system-schema exclusion are per-dialect properties on the DialectAdapter (next to the type mapper): every dialect that has an information_schema excludes it (case-insensitive); Postgres additionally lists PARTITIONED TABLE, MATERIALIZED VIEW and FOREIGN TABLE, and excludes pg_catalog as well. System catalogs that report under the dedicated JDBC types (SYSTEM TABLE, SYSTEM VIEW) are kept out by the type vocabulary itself — but that mechanism has holes: some engines report their system schemas under the plain types (MySQL's Connector/J reports sys, performance_schema and mysql as ordinary TABLE/VIEW rows), which is why the per-dialect schema lists carry more: MySQL excludes mysql, performance_schema, sys; Oracle excludes SYS, SYSTEM, OUTLN, XDB, CTXSYS, MDSYS, ORDSYS, DBSNMP, WMSYS, AUDSYS, OLAPSYS, XS$NULL and APEX_* (a prefix entry — Oracle versions its APEX schemas, e.g. APEX_240100); SQL Server excludes sys alongside INFORMATION_SCHEMA, plus the built-in fixed-role/special schemas every SQL Server database carries (db_owner, db_accessadmin, db_securityadmin, db_ddladmin, db_backupoperator, db_datareader, db_datawriter, db_denydatareader, db_denydatawriter, guest — dbo is deliberately NOT excluded: it is the database's default user schema); DuckDB, being Postgres-lineage, excludes pg_catalog beside information_schema. The MSSQL and DuckDB lists are floors, deliberately known-incomplete exactly like Oracle's — no arm64 containers exist for either dialect (pre-existing), so both are unit-verified against mocked metadata rather than container-verified. These lists are a floor, explicitly known-incomplete — they name the schemas the pinned drivers verifiably report as plain user rows, not every schema an engine ships; extending them is additive.

The floors can over-exclude, and introspection_include_schemas is the escape hatch. A prefix entry cannot tell the engine's schemas from a customer's own: any Oracle schema starting APEX_ — including a team's own APEX_REPORTING reporting schema — is invisible to all three operations, with no warning, and the authoring agent is then told the data does not exist. A datasource registered with introspection_include_schemas: ["apex_reporting"] (§3.3) exempts exactly that name from the exclusion in all three operations — every other floor entry, including the rest of the apex_* family, stays hidden. Exact names — or dotted NAMESPACES (087: a1.sales exempts one catalog's sales while leaving the other's excluded, which a bare name cannot express) — over the legal-identifier alphabet of the supported dialects: letters, digits, _, $, #, lowercase, in one or more dot-separated non-empty segments (the prefix language belongs to the floors; a pattern or quoted identifier would look like it exempts a family while exempting nothing, so anything outside the alphabet is rejected at save, while _ is an ordinary name character exempting the exactly-named schema). A single segment matches the schema level on every dialect, so every entry stored before 087 keeps working; normalized by the ONE rule — trim, lowercase, drop blank-after-trim entries, deduplicate (first-seen order) — at the registry's save boundary (every write path crosses it) and again at the repository's read boundary (restore and manual JSONB edits bypass save, and an unnormalized entry silently exempts nothing); matching case-insensitive like the exclusion itself; absent/empty = today's behavior.

Namespace filters (087). All three operations speak the dialect's NamespaceShape (§4.2), not "a schema".

• REST takes ?namespace=a1&namespace=sales (repeated) or ?namespace=a1.sales (dotted shorthand); MCP takes "namespace": ["a1", "sales"]. The pre-087 schema parameter keeps working forever (§12.1) and accepts the dotted form too, so an agent can pass back exactly what a listing gave it. namespace wins when both are present.
• Routing: a catalog-routing dialect takes its single level in the JDBC catalog argument and leaves the pattern null (Connector/J's default — otherwise a filter selects nothing and every table reports a null schema). Every other dialect takes the innermost segment in schemaPattern and the next-outer segment in the catalog argument. That last part is what 087 fixed: the catalog argument was a hard-coded null on all three operations, which is correct only while a connection has exactly one catalog.
• A filter DEEPER than the dialect's namespace matches nothing and returns an empty result — it cannot name a real place, and silently dropping its extra segments would answer a different question.

The merge this fixed was measured, not inferred. Verified 2026-09-07 against duckdb_jdbc 1.5.5.1 with two ATTACHed database files, each holding a sales.orders: the schemas listing reported two indistinguishable sales rows, and getColumns(null, "sales", "orders", "%") returned both tables' columns as one table's (id, one_col, id, two_col). The same read qualified by catalog returns each table's own columns. An authoring agent handed the merged list writes SQL against columns that do not exist in the table it named.

Rules:

• Scope: author on every surface (REST and MCP), matching the §8.1 connection-test precedent — introspection opens a live connection against a production datasource, and its stated consumer (authoring agents) holds author (Auth §7.6).

• Read-only by construction: these three operations issue only DatabaseMetaData calls, no statements. The statement-executing siblings landed later and are non-writing by their own construction: §7C's table statistics run bounded catalog queries, and §7D's probe runs one classified SELECT — neither writes, and neither relaxes anything said here.

• table, schema and namespace filters are exact-match identifiers, not LIKE patterns — _ and % in a name are escaped with the driver's getSearchStringEscape(), so a filter for order_items cannot match a sibling table like order1items. The escape applies only to the true pattern arguments (schemaPattern, tableNamePattern); the JDBC catalog argument is a literal ("must match the catalog name as it is stored") and is never escaped — an escaped catalog would match nothing for a MySQL database whose stored name carries _/%.

• An unknown schema or namespace FILTER on a listing is not an error — it matches nothing and returns an empty list (the house filter philosophy; see datasources_list's dialect filter in MCP §6.2.10). A table-ADDRESSED read is different — see the three-state rule below.

• A table-addressed read resolves its table first (123 §A). Every read that names one table — the columns operation, the §7C catalog statistics, and datasources_preview_rows — first resolves the table through the module's TableResolver, using the SAME catalog call the tables listing uses (same namespace routing, the dialect's full introspectionTableTypes so a VIEW is never "missing", exact-match identifiers). Three states:

  1. Present → the read proceeds exactly as before. An existing table with zero readable columns, or an engine holding no catalog stats for an existing table, stays an EMPTY result — empty is valid when the table exists.
  2. Absent → the read is refused with 404 datasource.table_not_found (Pipeline Contract §13.8). The message wording follows the dialect's introspectionCatalogIsComplete (table below): on a complete catalog it asserts "table <t> does not exist in <namespace>"; on a privilege-filtered one it says "table <t> was not found — it does not exist, or this datasource's credentials cannot see it". When a listed name is close — case-folded equality first, then an edit distance ≤ 2 over the names the namespace's listing returned — the message adds "Did you mean <name>?" and details carries table, namespace and suggestion. Note the corner this creates deliberately: a table-addressed read inside an UNKNOWN namespace reports the table as not found in that namespace (a namespace with no visible tables and one that does not exist are indistinguishable to the catalog read) — only the LISTING operations keep the empty-list rule for unknown filters.
  3. Present but unreadable — the read itself fails with a permission SQLSTATE → 403 datasource.table_forbidden: "table X exists but this datasource's credentials cannot read it". Classified per dialect — Postgres 42501; MySQL vendor codes 1142/1143 under 42000 (discriminated on the vendor code, not the state); SQL Server 229/230 under 42000; Oracle ORA-01031 (vendor 1031) — at the statement-executing boundaries (datasources_preview_rows, sql_probe; there is no REST stats/preview endpoint). A permission refusal on an absent table cannot happen, so translating only after resolution said present is sound. Oracle's ORA-00942 is deliberately ambiguous between absent and invisible and stays on the not-found path. The metadata reads themselves (columns, stats) need no translation: on the dialects whose drivers privilege-filter column metadata at all, a present-but-unreadable table simply yields zero rows — state 1's empty result.

• Catalog completeness is a per-dialect fact (DialectAdapter.introspectionCatalogIsComplete):

  Dialect	Complete?	Evidence
  POSTGRES	yes	pgjdbc 42.7.13 PgDatabaseMetaData.getTables selects from pg_catalog and appends AND has_table_privilege(...) ONLY when connection.getHideUnprivilegedObjects() is true; the property defaults to false and is set nowhere in this repo
  MYSQL	no	Connector/J reads information_schema, which lists only the tables the login has some privilege on
  ORACLE	no	ALL_TABLES lists only tables the user has any privilege on
  MSSQL	no	sys.tables is permission-filtered (Microsoft: a login sees only securables it owns or has some permission on)
  H2	yes	embedded engine; the catalog IS the database, no privilege filtering on metadata reads
  DUCKDB	yes	embedded engine, same reasoning as H2
  SQLITE	yes	embedded engine, no grants concept at all
  LAKE	n/a	resolves through its own registry branches (lake_table_not_found); the property is declared true (the underlying DuckDB catalog is complete) but is not consulted

• An unknown datasource name is datasource.not_found (Pipeline Contract §13.8).

• A connection failure during introspection is pipeline.execution.datasource_unreachable (Pipeline Contract §13.8; HTTP 502 on REST, an isError envelope on MCP) — a customer database being down is not a server error: no raw 500, no -32603, logged at WARN without a stack. The translation happens at the introspector's lease boundary and covers both failure families: the SQLException of a refused/timed-out lease or a connection that died mid-read, and the RuntimeException family of pool construction (PoolInitializationException at first lease on a down database, a missing driver class, a property rejected at parse time). Post-lease the SQLException translation narrows to the connection family only — SQLState class 08 (checked on the exception itself and along its cause/nextException chains, because some drivers carry the state only on a wrapped exception), the JDBC connection-exception subclasses, SQLRecoverableException, SQLTimeoutException, and the per-driver connection-loss knowledge: SQLite's result codes (BUSY, IOERR, CANTOPEN, NOTADB — the vendored driver reports SQLiteException with a null SQLState, so the state-based branches cannot see it; classification is by primary code, never a blanket "null SQLState means down"), h2's closed-connection codes (90007 closed object, 90098/90121 closed database — h2 carries the code as BOTH SQLState string and vendor code, outside the SQL-standard class range), and the DuckDB/SQLite JDBC-layer closed-connection lifecycle messages (both drivers report a closed connection as a plain SQLException with null state and code 0 — the message is the only discriminator, and the exact message + exact plain class keeps native errors out). Any other SQLException from a metadata read walk is a defect in this module or a driver bug and propagates as-is rather than being masked as "database unreachable". The current-schema read inside columns() is classified at the same place with the same family: feature-unsupported (the typed exception or SQLState 0A000) reads as "driver reports none"; connection loss is the 502 path above; anything left (a non-connection failure of the read itself — pgjdbc's getSchema() executes select current_schema() on the server, so a statement cancel 57014 or permission error lands here) is the catalogued pipeline.execution.parameter_required with the driver exception attached as cause — never a raw rethrow to either surface. Driver text never reaches the wire (the caller can run the §8.1 connection test for the scrubbed failure detail).

• Credentials are never part of any introspection payload — the operations read schema metadata only.

quoteIdentifier has a second consumer since 087: the executor's write-back identifiers (DAG Executor §6.4.3) route through it instead of quoting "…" unconditionally — the SQL standard's spelling, which MySQL rejects without ANSI_QUOTES and MSSQL spells with brackets.

Surfaces: REST GET /api/v1/datasources/{name}/schemas, /tables, /tables/{table}/columns (REST API §9.7); MCP datasources_get_schemas, datasources_get_tables, datasources_get_columns (MCP §6.2.16–18). The two statement-executing siblings are MCP-only: the catalog-statistics read datasources_get_table_stats (§7C) and the classified free-SQL probe sql_probe (§7D).

7C. Catalog table statistics

Shipped in 107 as the MCP tool datasources_get_table_stats (MCP §6.2.33; MCP-only — no REST twin). One table's statistics, always from the engine's OWN catalog — pg_class, information_schema, sqlite_stat1, Parquet footers — and never from scanning the table: there is no COUNT(*) and no COUNT(DISTINCT) anywhere on this path, by construction, so the read is safe at any table size. It rides the same SchemaIntrospector/registry-pool lease boundary as §7A (same visibility gate, same datasource.not_found / pipeline.execution.datasource_unreachable translations), with the dialect's per-engine recipe declared as a TableStatsPlan on the adapter (TableStatsPlans).

Payload: {row_estimate?, stats_as_of?, stats_source, indexes: [{name, columns, unique, primary, kind}], columns: [{name, n_distinct?, distinct_is_ratio, null_fraction?, min?, max?}]}. The stat fields follow the envelope's omitted-when-null convention — a missing row_estimate asserts "the catalog does not hold this", never a zero. stats_source names the catalog the numbers came from, or "none" — a valid answer, not an error. A LAKE table has no indexes; its registered partition column IS the access structure the engine prunes on, so it reports as a pseudo-index {name: "partition", kind: "partition"}. The per-column min/max are the catalog's estimates, not a scan — the planner's histogram ends from the last ANALYZE (the parquet footer's stored bounds for a lake), so a value can sit outside them: read them as bounds and sql_probe for the exact bound (139 §F — an agent read "max 260" as a data oddity when the true max was 265 and the rows between were live).

Per-dialect support (each row names the catalog the numbers come from):

Rules:

• An unknown TABLE is the §7A refusal, not empty stats (123 §A): the stats read is table-addressed, so it resolves the table first and an absent one is datasource.table_not_found. Empty stats now mean exactly one thing — the table EXISTS and the catalog holds nothing for it: the stat fields are null/absent, indexes/columns empty, and stats_source carries the dialect's own label (none when even the catalog is absent). An unknown DATASOURCE is datasource.not_found.
• The statement timeout is clamped to 10 s — the datasource's own query_timeout_seconds applies only when TIGHTER. Catalog reads never scan a table — that is the whole point of the section — so they must not wait on a table scan's budget. The one exception is the lake's footer read, clamped to 60 s: it is still metadata (no data pages are read), but a day-partitioned table is hundreds of Parquet footers over object storage — the demo's two-year hvfhv_trips (~700 files, unsigned S3) does not answer inside the catalog bound (measured live, 107). Footer rows are bounded at 65,536 so a million-file glob cannot stream its catalog through the wire.
• Namespace routing is §7A's: the caller's namespace filter, else the connection's current namespace — and a datasource reporting no current schema fails with pipeline.execution.parameter_required exactly like the columns read. A filter deeper than the dialect's namespace names no real place and answers empty stats. A lake's unfiltered read resolves only when exactly one namespace is registered; several refuse the same way.
• Scope: read on the MCP surface — the engine's own stored ESTIMATES about shape, never customer row data (the templates_used_by reasoning, Auth §7.6) — which is why it sits below the §7A tools' author floor.
• The LAKE location reaches parquet_metadata(...) only after the registry's location grammar is re-checked at the SQL-emission boundary (s3:///file://, no quotes/whitespace/control characters — the same total grammar the view generation enforces); JDBC-path identifiers are prepared-statement binds, never interpolated.

7D. The SQL probe

Shipped in 107 as the MCP tool sql_probe (MCP §6.2.34; MCP-only). The bounded, read-only free-SQL probe: ONE classified SELECT/WITH against a datasource, row-capped and timeboxed, answering rows + the canonical column schema + wall_ms of query time + the EXPLAIN plan captured BEFORE the query ran — a debug probe for authoring, not an export. tempdb is not a datasource read but a scratch check (since 2026-09-11; contract corrected 2026-09-16, #119): the statement is prepared against a fresh, empty in-memory H2 in the staging mode, and the payload's validation_status says how far the engine got — executed when a self-contained statement ran (rows), incomplete when H2 stopped at a staged table that exists only inside a full execution, in which case nothing after that table was checked and parsed is null. The contract, the H2 measurement behind it and the two ways to finish an incomplete check are in MCP §6.2.34.

The gate runs before anything is leased. There is deliberately no general SQL parser in this repo, so SqlStatementClassifier is a conservative token scan with one job: admit exactly one SELECT/WITH statement, and nothing carrying a statement or session verb that can write, mutate session state, or shell out to the engine — INSERT UPDATE DELETE MERGE UPSERT REPLACE DROP CREATE ALTER TRUNCATE GRANT REVOKE DENY CALL EXEC EXECUTE COPY ATTACH DETACH INSTALL LOAD PRAGMA SET USE VACUUM ANALYZE EXPLAIN IMPORT EXPORT CHECKPOINT INTO. (INTO earns its place one step past a plain keyword list: SELECT ... INTO t is the one statement that STARTS with SELECT and still writes. EXPLAIN/ANALYZE are denied because the probe wraps the statement itself and a nested one would defeat the timebox accounting.) The scan strips line comments (# on MySQL only — elsewhere # is an operator), nested block comments, quoted literals (MySQL backslash escapes included) and Postgres/DuckDB dollar-quoted strings first, so a denylisted word inside a string or comment never trips the gate and a ; inside one never counts as a statement separator. Refusals are conservative BY DESIGN: false positives err toward refusal — a column named merge trips the denylist — and the inverse gap is documented, not hidden: a statement-shaped trick the scanner cannot see through (a side-effecting function call such as pg_terminate_backend) is NOT caught. The gate is a shape check, not a side-effect proof; the datasource's own DB-user privileges remain the last line (§5.7).

The plan comes first. The dialect's EXPLAIN wrapper runs on the SAME lease, BEFORE the query, with the same binds and the same timeout (a plan can depend on the values) — so a timed-out probe still answers with the plan that explains it. Any EXPLAIN failure (unsupported shape, driver refusal, its own timeout) degrades to a null plan rather than failing the probe: the plan is best-effort, never the point of failure. plan carries {scan?, estimated_rows?, raw, partitions_scanned?, partitions_total?} — scan is the short per-dialect access description (seq, index:<name>, partition_prune x/y), raw the full plan text capped at 4096 chars (256 result rows), and every field but raw is a best-effort extraction that is omitted-when-null.

Parameters are the pipeline grammar's. :name placeholders bind through the same Spring NamedParameterUtils binder pipeline SQL uses, so probe SQL parses :name exactly the way a template's rendered output does; every referenced name must be supplied in parameters with its canonical type (type is a LogicalType — NULL is not declarable — and value its wire string: BIGINTEGER/BIGDECIMAL as decimal text, temporal in ISO forms, BINARY as padded base64; a null value binds SQL NULL). A reference with no supplied value is refused, never bound as null silently; a value that does not parse as its declared type is refused; both are -32602 argument faults — nothing was leased, nothing ran — and the refusal names the parameter, never the value text.

The clamps. limit defaults to 50, clamps to 500 (not refused — a probe asking for more is a sizing error, and truncated already says the rest exists); timeout_seconds defaults to 10, clamps to 30, and is the statement timeout. A driver timeout arrives as SQLTimeoutException OR as a server-side cancel (pgjdbc delivers queryTimeout as SQLState 57014, a plain PSQLException) — both classify as the timeout, surfaced as the catalogued pipeline.node.query_timeout (Pipeline Contract §13.4, since T202) with details carrying reason: "timeout", wall_ms and the pre-captured plan. Any other driver refusal is pipeline.node.query_execution_failed with the bounded driver message; an unreachable datasource is pipeline.execution.datasource_unreachable.

Scope: author — the probe returns arbitrary customer ROW DATA, the 037 F rule it shares with datasources_preview_rows. Audit: the sql argument never reaches the audit log — the dispatcher records sql_sha256 + sql_length, never the text (MCP §14).

7E. Learned facts (the semantic layer beside introspection)

Round 118 (design record v0.1 — the authority; this section is a pointer). What an agent learns about a datasource that JDBC metadata cannot say — a unit, a time zone, a sample rate, a grain, what a coded value means, a join that holds, a trap — is recorded as a structured row keyed by the object it describes (learned_facts, metadata-db §4.18) and served INLINE by the three §7A operations and GET /datasources/{name} (REST API §9.7A, MCP §6.2.18a), so the next session reads it where it is already looking.

The rules that touch this module:

• Nothing introspection provides is stored (D-S2): the closed kind list (Enums §19) has no type / nullable / key / comment / partition kind; §7A stays the source for those and is what facts are checked against.
• Refs are validated against live introspection at record time (§3.1) — one columns() read per referenced table, whose result is also the table's fingerprint (§3.2); a ref that does not resolve is semantics.ref_unresolved and the store never starts stale.
• A WORKSPACE rule may be a pure rule (136 §B / T278): a definition, exclusion or preference may carry NO refs — a rule that spans datasources ("busiest day = A + B combined") is recorded once, against the datasource the question is mostly about, and names no table; it stays bound to that datasource for visibility and for the definitions block the listing carries, its fingerprint is empty, and the drift check has nothing to compare (never stale). A DATASOURCE-scope kind keeps requiring a ref (semantics.fact_invalid); the database says the same in V26's chk_learned_facts_refs.
• Evidence runs through the §7D probe path — the same classifier, lease and timebox as sql_probe; a statement the classifier refuses or the database refuses does not record the fact.
• Trust follows the evidence, except for a choice (design §5, C.2): a fact recorded with evidence_sql is observed, without it asserted — but a definition, exclusion or preference is a workspace's choice and lands asserted whatever it carries (the evidence is still run and stored); verified is a person's confirmation, observed is for facts about the data. The derivation is LearnedFactRecorder's one expression.
• Drift is detected at read (§6): columns() recomputes each fact's per-table fingerprint and a complete tables() listing checks each fact's tables; a demotion (needs_review, stale) is written back. Nothing re-maps — a rename and a "drop + unrelated add" are indistinguishable to a machine.
• Visibility follows the grant (D-S1/D-R7): a DATASOURCE fact is visible wherever the datasource is; a WORKSPACE fact is one workspace's. The record verbs are the semantics_* MCP tools (MCP §6.2.37–39); the refusal codes are Pipeline Contract §13.15.

8. Connection Testing

8.1 POST /api/v1/datasources/{name}/test

// Response (200 OK):
{
  "schema_version": 1,
  "correlation_id": "uuid",
  "data": {
    "connected": true,
    "server_version": "PostgreSQL 16.2 on x86_64-pc-linux-gnu, ...",
    "tested_at": "2026-08-05T14:30:00Z",
    "latency_ms": 23
  }
}

Or on failure:

{
  "schema_version": 1,
  "correlation_id": "uuid",
  "data": {
    "connected": false,
    "tested_at": "2026-08-05T14:30:00Z",
    "error": "Could not acquire connection: Connection refused.",
    "error_class": "java.net.ConnectException"
  }
}

Note: connection test failure is not an HTTP error. The caller asked "can I connect?" and got an honest answer. HTTP 200 always (provided the datasource exists). The data object is the wire form of TestResult (§6.1).

8.1B The last test's outcome is stored, and listed

A connection test writes its outcome to the datasource row: last_test_at, last_test_ok and last_test_message (metadata-db §4.10, migration V9). Every read path projects them as the additive last_test object (§3.2), and the datasources screen renders them as a column — an ok / failed badge, the timestamp, and the driver's message.

Why this exists. Listing a datasource does not connect to it. On 2026-09-02 every multi-node demo pipeline failed at CONNECT with password authentication failed for user "dp_demo_ro" while the datasources screen showed sample-trips as fine — there was no surface on which a datasource could be wrong, only surfaces on which it could be silent. This is the column that would have said "auth failed since 2026-08-30".

Three paths write it, all through the same probe (§8.1): POST /datasources/{name}/test, the UI's Test button, and the §8A.3 rule-3 bootstrap credential check. last_test_message carries the driver's own sentence on failure (the DEEPEST cause with text — HikariCP's "Connection is not available, request timed out" names no database and diagnoses nothing) and the server version on success. It is redaction-scrubbed at the probe like every other TestResult message, so it is safe on the wire and on the screen.

The write is an observation, not an edit. It touches those three columns and nothing else — in particular it does not move updated_at. That is the single documented exception to metadata-db §2's "every UPDATE sets updated_at", and it exists so §8A.3 rule 1's promise (an operator's row survives a boot byte-untouched) stays a mechanical check rather than a claim.

This is not a health poller. It records the outcome of tests that already happen; the scheduled polling of §8.3 remains v1.1+.

8.2 Pre-execution check

Before pipeline execution begins, the executor pre-checks that every datasource referenced by the pipeline's nodes is configured and reachable. Failures here abort before any node runs, with error code pipeline.execution.datasource_unreachable (registered in the central catalog, Pipeline Contract §13.8 — HTTP 502).

Pre-check is a fast SELECT 1 (or dialect-equivalent) against each datasource. Cost: milliseconds per datasource, parallelizable.

8.3 Background health checks (optional)

In v1.1+, the system can poll datasources on a schedule and surface health in the UI. Not in v1.

8A. Bootstrap registration (config-declared datasources)

A deployment can declare datasources in a file and have the app register them at startup: datapipelines.bootstrap.datasources-file, unset = off. The mechanism is generic (IaC-style environment setup); the datapipelines.co demo is its first user, registering the sample databases as global + readonly.

8A.1 File shape

The POST /api/v1/datasources field vocabulary of §3.1, plus two flags. Unknown keys are a startup refusal, not a silent skip — a mistyped jdbc_ur would otherwise register a datasource with no URL that fails at first query.

The credential follows §3.4: a credential: {kind, username?, secret?} block, or the legacy top-level username/password pair which means kind: password and keeps every pre-087 file working. Declaring BOTH is a startup refusal rather than a precedence rule — they can disagree, and no silent winner is defensible. Declaring NEITHER is a refusal too, naming both shapes: that is the shape of a file whose password: key was mistyped, and it must not quietly become "no credential".

# /etc/datapipelines/bootstrap-datasources.yml
datasources:
  - name: sample-trips
    display_name: "NYC Taxi Trips (sample)"      # optional; defaults to `name`
    dialect: POSTGRES
    jdbc_url: jdbc:postgresql://postgres:5432/dp_sample_trips
    credential:                                  # §3.4
      kind: password
      username: dp_demo_ro
      secret: ${SAMPLE_PG_PASSWORD}
    readonly: true                               # writes `is_readonly` (§5.7)
    global: true                                 # required, and must be true in v1
  - name: sample-reference
    dialect: SQLITE
    jdbc_url: jdbc:sqlite:/srv/sample/nyc_reference.db
    # SQLite is a FILE: no server, no login, and the xerial driver ignores a
    # username and password entirely. Until 087 this entry carried
    #     username: "sqlite"
    #     password: "sqlite-file-datasource-has-no-authentication"
    # not because anything used them but because the contract had no way to say
    # "there is no credential": §9's `datasource.validation.password_missing`
    # rejects a null-or-empty password on create, and bootstrap registration runs
    # the full §9 validation with no startup shortcut (§8A.3) — an empty password
    # fail-fasted startup with "A password is required on create." (boot-verified,
    # T38). `credential.kind: none` (§3.4) is that way, and V13 stores it as a NULL
    # `credential_encrypted`.
    credential:
      kind: none
    properties:
      jdbc:
        open_mode: "1"                           # xerial read-only open mode — see §8A.4
    readonly: true
    global: true

readonly: true writes datasources.is_readonly, so every §5.7 enforcement layer applies to a bootstrapped datasource exactly as to one created over the API. global: true means workspace_id NULL.

global is required and must be true. The flag is written explicitly rather than defaulted because registration runs before any workspace exists, so there is no answer to "which workspace does a non-global entry bind to". global: false and a missing global are both parse-time refusals, with different messages; requiring the word means a file written for a later version cannot be silently read as global by this one.

A LAKE entry's registry seed (089 §E). A dialect: LAKE entry may additionally declare the dp-catalog rows its datasource serves (§8C.1), in ONE of two forms:

• tables: — inline registry rows, each the rest-api §9.8 register shape: {namespace?, name, format, location, partition_column?}.
• import_manifest: — the URL of an 088 manifest.json whose tables[] the bootstrap imports through the registry service's IDEMPOTENT path, so a re-boot re-asserts the registry instead of duplicating it. The fetch is server-side and passes the same SSRF boundary as POST …/tables/import: restricted to the datasource's own dialect.endpoint / catalog.ref, or a plain AWS S3 host — never an arbitrary URL.

Two qualifiers: namespace: is the shared namespace a seed row without its own lands in (the demo's manifest carries no namespaces; its tables land in [nyc, mobility]), and only_tables: filters an import to the named tables — the demo uses it to exclude the manifest's Iceberg copy as demo scope, the Iceberg read path being proven by the integration suite rather than the demo datasource. The parse-time refusals: both seed forms together (they can disagree — the credential-shape rule); either form on a non-LAKE entry (a JDBC database's tables are discovered, never registered — a tables: block under a Postgres entry is a paste error); namespace: / only_tables: with no seed (they qualify a seed, they are not one); and an explicitly EMPTY tables: block (a seed of zero tables is a mistake, not a policy). The demo entry is deploy/sample-data/bootstrap-datasources-lake.yml.

8A.2 ${ENV_VAR} resolution

Secrets never live in the file. Every string in the tree — not just password; a passphrase is as likely to sit in properties.jdbc — has its ${NAME} placeholders resolved against the process environment at read time. This is the app's own substitution, not Spring's: the file is runtime data named by a config key, read from disk after the context is built, so ${...} in it means nothing to Spring's placeholder resolution.

A placeholder whose variable is not set is a startup refusal naming the variable. It is never left as a literal, because a datasource registered with ${SAMPLE_PG_PASSWORD} as its credential fails much later, as an unintelligible authentication error against a database nobody suspects.

8A.3 Semantics: create-if-absent, never update

Applied once per startup, after Flyway and after the Auth §4.4 actor is resolved, before the server accepts traffic. created_by is that actor for every entry.

• Create-if-absent by name. An existing row is left byte-untouched and logged at INFO. This is the operator's guarantee: a restart never reverts an edit they made, and a datasource they deleted never resurrects itself. "Existing" includes soft-deleted rows — name is the primary key (§9 duplicate_name), so a soft-deleted name is permanently taken.

• Full §9 validation per entry, test pool build (§5.4) included. Registration goes through the same save path as the REST endpoint; there is no startup-only shortcut. Skipping the pool build because "it is slow at boot" would ship a demo that registers dead connections and discovers it at first query.

• One entry at a time, fail-fast. Entries are applied in file order, each fully validated and written before the next is attempted. The first failure refuses startup: a half-registered demo is worse than a loud one, and create-if-absent makes the operator's retry idempotent.

• Rule 3 — a credential desync is reconciled by CONNECTING, never by preferring a source (added 2026-09-03). For an entry whose row already exists AND whose file credential differs from the stored one — equal credentials are the ordinary boot and nothing is probed:

  Probe result	What happens
  the STORED credential authenticates	the row is left byte-untouched. Rule 1 stands: the operator's edit works, so it is kept
  stored fails, the FILE's authenticates	the credential alone is updated — not the name, the display name, the URL, the properties, the readonly flag or the binding — the pool is evicted, and a WARN names the datasource
  neither authenticates	the row is left alone and an ERROR names the datasource and the environment variable an operator would change
  the stored ciphertext cannot be DECRYPTED	the row is left alone and an ERROR names the encryption key. A credential that could not be READ is not one that failed a LOGIN, and overwriting it would paper over a key problem every other datasource shares

  A soft-deleted row is never reconciled: rule 1 promises a deleted datasource never resurrects, and writing its credential back would be a resurrection in all but the flag.

  Startup does not fail on a broken credential — the app has to boot for an operator to be able to fix the row — so the ERROR line is the loud part. Every branch that probed records its outcome on the row (§8.1B), which is what puts the answer on the screen.

  Why this is not "always update". Always-update would revert an operator's edit on every boot, which is exactly what rule 1 exists to prevent. Rule 3's narrowness is entirely in the fact that a probe, not a preference, decides: it fires only on a difference, writes only on a proven failure paired with a proven success, and writes only one column.

  The incident it closes. sample-trips was registered on 2026-08-30. SAMPLE_PG_PASSWORD later changed; the sample database's role got the new password at the next load, the stored row kept the old one, and nothing compared them. A clean-machine rehearsal cannot see this class of defect at all — it exists only on the upgrade path.

Failures here are startup refusals, not API errors — nobody submitted a request — so they carry no datasource.validation.* response code of their own. An entry that fails §9 raises that rule's code in the startup log, exactly as it would have in a 400.

8A.4 SQLite read-only open mode

readonly: true is a datapipelines-level flag (§5.7); making the SQLite driver open the file read-only is a properties.jdbc key, and the two are independent — set both for a sample database.

The key is open_mode: "1" (1 = SQLiteOpenMode.READONLY), verified against the pinned driver org.xerial:sqlite-jdbc:3.49.1.0 rather than recalled: with it, Connection.isReadOnly() is true and both INSERT and CREATE TABLE fail SQLITE_READONLY. The similarly-named jdbc.explicit_readonly is not a read-only switch — with it alone the same writes succeed; it only makes an explicit Connection.setReadOnly(true) meaningful. Re-verify the key against the driver on any xerial upgrade.

8A.5 DuckDB read-only open mode

The DuckDB analogue of §8A.4, used by the trade family's sample-trade-us entry. The mechanism is the DuckDB config access_mode = READ_ONLY, delivered as a connection property — verified against the pinned driver org.duckdb:duckdb_jdbc:1.5.5.1 (2026-09-04), two probed facts:

• the URL-parameter form (jdbc:duckdb:…?access_mode=READ_ONLY) is silently ignored — the connection opens read-write and writes succeed;
• the connection-Properties form (properties.jdbc.access_mode: READ_ONLY) is honored: writes on an existing file fail, and opening a read-only connection to a file that does not exist fails at connect ("Cannot open database … in read-only mode: database does not exist") — so the loader must place the file before the app registers the datasource, which the demo profile's service ordering guarantees.

access_mode is not in the DUCKDB §5.6 refusal set (only the five extension keys are), so the bootstrap entry may set it. The demo additionally mounts the sample volume read-only into the app container — the filesystem-level backstop under the driver lock and the §5.7 flag.

8C. dp-lake — the catalog, the per-table views, and registry introspection

dp-lake is the offering built on the LAKE dialect (§4.1): Parquet and Apache Iceberg tables on S3 or S3-compatible object storage, queried in place — no warehouse, no load step, nothing copied. DuckDB is the engine underneath (an in-memory instance per pooled connection); the catalog is dp-catalog, our own minimal registry, because the engine cannot LIST a bucket. A LAKE datasource is a READ connector: nothing here writes to the object store.

8C.1 dp-catalog — the registry

A LAKE datasource's tables are exactly the rows of lake_tables (V15; metadata-db §4.15) — the registry IS the catalog. datasource_id holds the datasource name, which is the datasources primary key; there is no surrogate id to point at.

• namespace — 1–9 segments, each following the pipeline/template segment grammar without . (the dotted shorthand on the wire must round-trip). The view mapping (§8C.2) supports one or two segments today; deeper namespaces are registered, recorded with a last_error and skipped at connect rather than being silently flattened.
• name — one segment of the same grammar. format — parquet | iceberg (CHECK-constrained: a third value would generate bad view SQL later).
• location — s3://bucket/prefix[/glob] or a file:// path; no other schemes, and no quotes, backslashes, whitespace or control characters anywhere — the value is later interpolated into the engine's CREATE VIEW, so the refusal is total (a value that would need escaping is refused, never escaped). Iceberg tables are registered by their current metadata FILE — the measured rule is §8C.7.
• partition_column — optional; the column the engine's partition pruning keys on.
• last_error / last_error_at (V22) — the connect-time view creation's recorded failure and when it was newly recorded (109 §A). NULL/NULL is the healthy spelling; recording is transition-only, so last_error_at reads as "broken since". Maintained by the pool's view application alone; cleared by the next successful view creation.

Registration pre-flight (109 §A). register and importTables prove a candidate table READABLE before its row is stored: on a scratch connection built exactly like the datasource's pool (same adapter init, same bundled-extension posture), the table's view is created and one row is scanned through it. A failure refuses the write with datasource.validation.lake_table_unreadable (400) carrying the engine's error text, bounded to 2000 characters — a table that cannot be read is never stored to fail every connect after. Import pre-flights only the triples not already registered: an idempotent re-import (bootstrap re-runs it on every boot) neither re-reads nor fails on tables that are already registered.

The surface:

• REST (rest-api §9.8): POST /api/v1/datasources/{name}/tables (register one), DELETE …/tables/{ns}/{table}, POST …/tables/import (an inline tables[] block, or a manifest_url fetched server-side and restricted to the datasource's OWN bucket/endpoint — the SSRF boundary), and GET …/lake-tables (the registry's own rows, read scope). The writes are author; mutating a GLOBAL datasource's registry is admin-only as a workspaces D8 rule.
• MCP: lake_tables_register, lake_tables_import, lake_tables_unregister (mcp-server §6.2.29–31) over the same application service, so validation, the duplicate refusal and the pool invalidation are identical on both surfaces.
• UI: a LAKE datasource's detail page shows the registry as a READ-ONLY namespace tree with each table's format and partition column. Registration stays REST/MCP — authoring is agent-first by design.

Every successful registry write evicts the datasource's connection pool and publishes the §5.7 invalidation (Redis channel dp:datasource-invalidated): every instance rebuilds the pool on the next lease — so a table registered on instance A is visible on instance B's next execution — and registry-backed introspection (§8C.3) catches up within its 60 s cache TTL.

8C.2 A view per registered table, built at connect — and isolated per table

("Built at connect" means at the generation's connect — once per pool build, not once per physical connection, since 152.) A raw open on jdbc:duckdb: / jdbc:duckdb::memory: is its OWN in-memory DuckDB instance (verified 2026-09-07 against duckdb_jdbc 1.5.5.1: objects created on one connection are invisible to a second, while both are open). Since 152 (#128) a LAKE pool generation opens exactly ONE such instance and every physical connection HikariCP creates is the driver's duplicate() of the generation's retained owner (§5.2): the catalog/schema/view set is built ONCE, on the owner, and shared by every connection of that generation. Isolation across datasources — and across a retiring generation and its replacement — is the generation's: each has its own instance, and the anonymous URL has no name for two to collide on. The one per-connection statement is the search path (below), which the engine keeps per session.

Per-table isolation (109 §A). The views do NOT ride HikariCP's connectionInitSql — one string, no try/catch, so a single failing CREATE VIEW (a bad prefix, a wrong format, a file that is not Parquet) used to fail the whole pool build and make EVERY registered table unreachable. Instead the generation's initializer (LakeInstanceInitializer, run on the owner before Hikari opens its first connection) applies the adapter's own connectionInit statements (§4.2A) and the shared prelude (extension loads, ATTACHes, schema creations) STRICTLY — a failure there is a datasource fault and fails the pool build as before — and then each table's view independently: a failing view is caught, recorded on the table's registry row (last_error / last_error_at, bounded to 2000 characters) and skipped, and the build succeeds with the surviving views. The same isolation covers the SQL-emission boundary's own refusals (an unmappable 3+-segment namespace, a location that fails the grammar, an unknown format): the refusal is captured per table and recorded, never thrown into the pool build — with ONE exception (158, #129): when EVERY registered table is emission-refused there is nothing to isolate, no schema exists and no view would serve, so the pool factory records each refusal on its registry row and refuses the build with datasource.validation.lake_no_healthy_tables (400), the message naming every table and its reason. (Pre-158 that registry built a pool whose search-path postlude named a schema that was never created, so the first physical connection failed with the engine's parser/catalog error — the all-tables-down outcome, without the reason.) Recording is otherwise transition-only — the initializer compares against the state the pool was built with, so an unchanged outcome writes nothing; a success after a failure CLEARS the row's last_error. A recorder that itself fails (the registry write) is logged and never fails the build. Because the statements run once per generation, no CREATE OR REPLACE VIEW and no global SET ever runs beside an active reader on routine connection creation; a registry change reaches readers through a NEW generation (§5.2's retire-and-rebuild), which is the supported refresh boundary — there is no cache TTL and no promise of automatic remote-object freshness inside a generation.

The broken table at query time. A node whose rendered SQL references a table with a recorded last_error fails at the CONNECT phase with the catalogued datasource.lake.table_unavailable (502), details carrying table and last_error — the recorded reason — instead of the engine's raw "table not found". The detail page's registry tree and GET …/lake-tables show the broken table with its recorded error (a view failed badge carrying the text on hover).

The namespace mapping follows DuckDB's exact three-level object space (catalog.schema.object; its parser refuses a deeper CREATE SCHEMA outright):

• two segments ["nyc", "mobility"] — the head becomes an in-memory ATTACHed catalog (ATTACH IF NOT EXISTS ':memory:' AS "nyc", the one writable catalog a view needs to live in) and the rest a schema inside it.
• one segment ["nyc"] — a schema in the connection's default catalog; no ATTACH.
• three or more — recorded as the table's last_error (the datasource.validation.lake_namespace_invalid message) and its view skipped: the engine cannot name the place, and flattening would alias two different registry namespaces onto one schema.

Then one view per registered row:

CREATE OR REPLACE VIEW "nyc"."mobility"."hvfhv_trips" AS
SELECT * FROM read_parquet('s3://bucket/hvfhv_trips/pickup_date=*/part-*.parquet', hive_partitioning = true);
-- format = iceberg instead:
CREATE OR REPLACE VIEW "nyc"."mobility"."trips_iceberg" AS
SELECT * FROM iceberg_scan('s3://bucket/iceberg/trips/metadata/00002-….metadata.json');

hive_partitioning = true is emitted UNCONDITIONALLY for Parquet: it is harmless on a non-partitioned layout (a plain file reads normally), and making it conditional on partition_column would let a registered-but-wrong partition column silently disable pruning for a table that IS partitioned. The engine's partition pruning then applies to the underlying read_parquet scan unchanged — a predicate on the partition column reads only the partitions it names.

The search-path rule — when bare table names resolve. When ALL of the datasource's registered tables share EXACTLY ONE distinct namespace, the last statement is SET search_path = '<catalog>.<schema>' (verified to resolve bare table names on duckdb_jdbc 1.5.5.1; the catalog-qualified spelling cannot drift into a same-named schema elsewhere) — provided at least one table in that namespace is emission-healthy (158, #129): the prelude creates no schema for an all-refused namespace, so a search path naming one would fail every physical connection, and the count of namespaces still reads ALL registered rows, so a broken table's namespace never silently changes what bare names mean. This is the documented choice, and the demo uses it: the sample-lake datasource's four tables all live in [nyc, mobility], so templates read FROM hvfhv_zone_day bare. With ZERO or MULTIPLE distinct namespaces nothing is set — there is no defensible default — and queries must use the fully qualified catalog.schema.table form.

8C.3 Introspection reads the registry, not JDBC metadata

For a LAKE datasource the §7A operations are served from dp-catalog (round 089 §C), with no new wire fields — 087's namespace carries everything:

• schemas = the registry's distinct namespaces.
• tables = the registry's rows, each reported as JDBC type VIEW (what the engine made it — §8C.2) with its format in remarks.
• columns = a zero-row SELECT * FROM <view> LIMIT 0 read through ResultSetMetaData and mapped by the adapter's DuckDB mapper exactly like a JDBC getColumns row — nested LIST/STRUCT/MAP arrive as STRING with the mapper's warning. (DESCRIBE SELECT … reports the same schema as STRINGS, so the mapper's DECIMAL precision/scale inputs would have to be re-parsed; the structured read is the faithful one.) With MULTIPLE registered namespaces an unfiltered columns read cannot pick one and fails with the §7A current-schema-unknown rule — pass the table's namespace, the same recovery as the JDBC path.

All three are cached per datasource for the §6.3 60 s TTL; on S3 the zero-row columns scan is a footer read over the network, which is what the cache is for.

8C.4 Engine limits — compute is on the app's box

Every lake pool generation gets the engine limits as SET statements, applied ONCE on the instance every pooled connection shares (152, §5.2) — they are ONE budget for the pool, not one per connection, and the pool size does not multiply them (configuration.md §3.24 is the operator paragraph): dialect.memory_limit (default 25 % of the container's memory as the cgroup-aware JVM reports it, floored at 64 MiB and hard-capped at 4 GiB — an explicit value is the operator's own number and is not capped), dialect.threads, dialect.temp_directory (explicit, or for an anonymous in-memory URL a unique absolute datapipelines-lake-<UUID> path under the JVM temporary directory; named/file URLs retain the engine setting when unset), and preserve_insertion_order = false ALWAYS — insertion order costs memory and temp-file discipline the engine would otherwise spend on a guarantee a read-only lake never asks for. DuckDB shares the box with the JVM, which is what the default's cap exists for. The existing row cap and node-query-timeout-seconds apply unchanged. Concurrent queries on one lake share that one budget: an over-budget query fails with the engine's out-of-memory error on its own connection while its siblings keep working, and a host running N lake datasources holds N such budgets (plus, briefly, a retiring generation's while it drains). The default spill path survives physical connection replacement, is isolated between independent in-memory generations, and is created and removed by DuckDB on spill and normal instance shutdown respectively. The JVM temporary directory must be writable and disk-backed; explicit overrides, crash cleanup, and sizing are described in configuration §3.24.

8C.5 Extensions, bundled in the image

A LAKE datasource whose data is on S3 needs DuckDB's httpfs and aws extensions, and the Iceberg catalog kinds additionally need iceberg (which requires avro). Where the binaries come from is the operator key datapipelines.duckdb.extension-directory (configuration.md §3.25):

• Set — the shipped image: the published image bundles the four extensions for DuckDB core v1.5.5 under /opt/duckdb/extensions/v1.5.5/<platform>/ (+108 MB uncompressed), the <platform> following the image build's architecture (linux_amd64 or linux_arm64 via the Dockerfile's TARGETARCH mapping), and exports the variable from the Dockerfile. Every lake connection then runs SET extension_directory plus BARE LOADs and never an INSTALL — in DuckDB v1.5.5 LOAD strictly loads already-present files (no download code path runs at all, measured in the 089 §7.3 spike with --network none), so a hardened dp-lake deployment needs no egress to extensions.duckdb.org at all.
• Unset: the explicit INSTALL+LOAD pairs developer machines rely on — which requires egress to DuckDB's extension repository at connect time.

Independently of the declared catalog.kind, the view seam (§8C.2) loads the iceberg extension whenever the registry holds an iceberg-format table, in either mode — a bare LOAD avro then LOAD iceberg against the bundled directory, or INSTALL iceberg (which pulls avro in as a dependency over the network) without one. catalog.kind: s3 loads only httpfs+aws on its own, and an Iceberg view would otherwise be the table that fails at connect. Parquet-only registries load nothing extra: an extension nothing will call is surface for nothing.

8C.6 Credentials and addressing

The S3 credential comes from the datasource's §3.4 credential, never from properties.*:

• credential.kind: none — the engine's credential_chain provider (IAM role, environment, shared config/profile). The self-hosted answer, with nothing to encrypt or rotate.
• credential.kind: password — an explicit key pair: the access key id as username, the secret as the stored credential, emitted as KEY_ID/SECRET in the connect-time CREATE OR REPLACE SECRET.
• A PUBLIC bucket declares dialect.unsigned: "true" and gets no S3 secret at all — the engine's reads go unsigned. This is not optional decoration: credential_chain VALIDATES at create time, so on a credentials-free box the CREATE SECRET fails and takes the pool down with it (found by the 089 live gate). unsigned beats every credential kind; the demo's sample-lake sets it.

dialect.region sets the secret's region; dialect.endpoint (host[:port], no scheme) points at an S3-compatible store — MinIO, on-prem — and a declared endpoint always emits USE_SSL false (MinIO and most on-prem S3 endpoints are plain HTTP; a declared endpoint is a deliberate non-AWS target, so TLS is not assumed for it); dialect.url_style is path or vhost for those endpoints. dialect.catalog.kind accepts s3 (what the demo and this round's suites use) and — accepted by the adapter since 087 — glue | s3_tables | rest, which the registry does NOT read (§8C.8).

8C.7 The Iceberg location rule — register the metadata FILE

Measured 2026-09-08 against duckdb_jdbc 1.5.5.1 (the 089 §F MinIO suite): DuckDB resolves an iceberg_scan location through version-hint.text by CONSTRUCTING v<n>.metadata.json / <n>.metadata.json filenames, which never match the Iceberg spec's %05d-<uuid>.metadata.json names pyiceberg actually writes — so scanning a table by its root (the directory holding metadata/) fails, and only the explicit metadata file scans. Register the table's CURRENT metadata FILE as location (s3://bucket/table/metadata/00042-<uuid>.metadata.json). This contradicts the design record's "the table root holding metadata/" — the tree won. The corollary for an Iceberg table that still receives commits: every commit writes a new metadata file, so the registry row must be re-registered to follow the table.

8C.8 Not in this round

• External catalogs as a registry source. catalog.kind glue | s3_tables | rest is accepted by the adapter (087) but dp-catalog does not read AWS Glue, S3 Tables or a REST catalog — a LAKE datasource's tables are exactly the registered rows (§8C.1). External catalogs remain a later, optional source.
• Writes to the lake. dp-lake is a read connector; there is no write path to the object store.
• Scheduler, dashboards. Pipelines run when executed (or when a published endpoint is called); nothing schedules them and no dashboard surface ships.
• Nested types. LIST/STRUCT/MAP columns introspect as STRING with the mapper's warning (§8C.3); they are queryable in SQL but not type-mapped.
• Athena / Glue as engines. Out by thesis — paid services; DuckDB on the app's box is the engine.

9. Validation Rules

Every rule below runs on create and update, before the row is written (§2 principle 7; Pipeline Contract §2). All failures are collected, not short-circuited (§6.1 ValidationResult). HTTP mappings live in the central catalog, Pipeline Contract §13.8.

datasource.driver_not_loaded is deliberately not in the datasource.validation.* namespace: it reports a deployment/packaging state (a missing driver JAR), not a defect in the submitted entity — the same payload becomes valid after the operator rebuilds with the right profile. The code is canonical in Enums §16 / Pipeline Contract §13.8.

datasource.in_use (delete blocked by referencing pipelines, §6.2) is a lifecycle error rather than a save-time rule, and is likewise catalogued in §13.8.

10. JDBC Driver Packaging

10.1 The licensing problem

Some JDBC drivers have licenses that complicate redistribution in an OSS project:

• Oracle ojdbc — OTN license; redistributable on Maven Central but license terms bind the distributor.
• MySQL Connector/J — GPL-2.0 with FOSS exception; depends on the project's license compatibility.

10.2 Strategy

Default distribution (clean licenses only):

• Postgres, MSSQL, H2, DuckDB, SQLite ship in the default build.
• Default deployment can connect to PG, MSSQL, MySQL (if user supplies driver), H2, DuckDB, SQLite out of the box.

Optional Gradle profile -Poracle adds the Oracle driver as a dependency:

• Operator builds with ./gradlew -Poracle build to include.
• Or: deploy-time, drop the JAR into lib/ (Spring Boot's loader picks it up).

Optional Gradle profile -Pmysql does the same for MySQL Connector/J.

For self-hosted deployments where the operator has accepted the relevant licenses, both options work. Default build stays clean.

10.3 Driver class lookup

object JdbcDrivers {
    private val drivers = mapOf(
        Dialect.POSTGRES to "org.postgresql.Driver",
        Dialect.ORACLE   to "oracle.jdbc.OracleDriver",
        Dialect.MSSQL    to "com.microsoft.sqlserver.jdbc.SQLServerDriver",
        Dialect.MYSQL    to "com.mysql.cj.jdbc.Driver",
        Dialect.H2       to "org.h2.Driver",
        Dialect.DUCKDB   to "org.duckdb.DuckDBDriver",
        Dialect.SQLITE   to "org.sqlite.JDBC"
    )

    fun classNameFor(dialect: Dialect): String =
        drivers[dialect] ?: error("No driver mapped for dialect $dialect")

    fun isAvailable(dialect: Dialect): Boolean = try {
        Class.forName(classNameFor(dialect)); true
    } catch (e: ClassNotFoundException) {
        false
    }
}

At datasource create/update time, validation calls isAvailable(dialect) and rejects with datasource.driver_not_loaded if the driver JAR is missing. This check runs before the test pool build (§5.4) — a missing driver would otherwise surface as a confusing properties_invalid.

11. CRUD Operations

Wire contracts (envelopes, status codes, examples) live in REST API §9; required scopes live in the Auth §7.6 scope matrix (read = read, test = author, create/update/delete = admin). This table is the operation inventory.

11.1 Rename semantics — name is immutable

name is the primary key and the reference pipelines carry in their JSON (§2 principle 1). Renaming it would silently break every pipeline pointing at the old value, in every environment, with no write-time signal — so there is no rename operation:

• PUT /api/v1/datasources/{name} ignores no field silently: a body whose name differs from the path segment is rejected with datasource.validation.name_invalid (field: "name", message stating immutability). Every other field, including dialect, jdbc_url, credentials, query_timeout_seconds and properties, is updatable.
• A rename is therefore delete + create: create the new datasource, repoint the referencing pipelines (a new pipeline version per Pipeline Contract §14), then delete the old one.
• The delete step is the guard that makes this safe: it is blocked while any non-deleted pipeline references the name (datasource.in_use, §6.2), so the old datasource cannot disappear until the repointing is complete. Order matters — create → repoint → delete.

12. Stability Promise

12.1 Frozen in v1

• Datasource entity JSON shape, including the properties namespaces. Amended additively in 087: hikari and jdbc are unchanged and stay passthrough; a third reserved namespace dialect joins them, TYPED and adapter-validated rather than passthrough (§4.2A). The amendment is additive in both directions — a payload with no dialect block behaves exactly as before, and a dialect block on a dialect that declares no keys is refused rather than silently ignored, so nothing that used to work stops working and nothing that used to be rejected starts being accepted.
• The legacy top-level username/password credential pair, which means credential.kind: password (§3.4). Additive means additive: the credential block joined it, it did not replace it.
• name immutability and its role as the pipeline-facing reference.
• The supported dialects and their identifiers (8 as of 087 — LAKE joined non-breakingly under §12.2's "new dialects added non-breakingly").
• The separation of pipeline-name from connection-details.
• The encryption-at-rest requirement, and the single required key source (fail-fast).
• Save-time validation including the test pool build.

12.2 Not frozen

• Pool implementation (HikariCP) — could swap to AGPL-licensed alternatives if license concerns arise. Note this would change the meaning of properties.hikari.*; a swap therefore requires a migration path, which is why the namespace is named after the pool rather than being generic.
• The exact encryption scheme (AES-256-GCM today; KMS as an additional explicit key source in v1.1 — ROADMAP §2).
• New dialects added non-breakingly.
• Which pool/driver properties are useful — the passthrough model means no spec change is needed to adopt new ones.

13. Implementation Notes

13.1 Where this lives

datasources Gradle module:

• co.datapipelines.datasources.Datasource data class
• co.datapipelines.datasources.Dialect enum
• co.datapipelines.datasources.DatasourceRegistry — service interface
• co.datapipelines.datasources.JdbcDrivers — driver class lookup
• co.datapipelines.datasources.pooling.ConnectionPoolManager — HikariCP wrapper
• co.datapipelines.datasources.crypto.CredentialEncryptor — AES-256-GCM
• Per-dialect: PostgresDialectAdapter, OracleDialectAdapter, etc.

13.2 Testing

• Unit tests per DialectAdapter covering validateJdbcUrl and buildHikariConfig.
• Passthrough tests: a valid properties.hikari entry reaches HikariConfig verbatim; an unknown key, a wrong-typed value, and a server-managed key each fail the test pool build with datasource.validation.properties_invalid and name the offending key.
• Test pool build does not require a reachable database (initializationFailTimeout = -1): saving a datasource pointing at a dead host succeeds; POST .../test on it returns connected: false.
• Pool concurrency: N coroutines calling poolFor() simultaneously for a cold datasource construct exactly one HikariDataSource.
• Query-timeout precedence: datasource query_timeout_seconds set → used; unset → global node-query-timeout-seconds.
• Immutability: PUT with a differing name is rejected; delete while referenced returns datasource.in_use with the pipeline list.
• Startup: a missing / malformed / wrong-length DATAPIPELINES_DB_ENCRYPTION_KEY fails application startup (no fallback path exists to test).
• Integration tests via Testcontainers: spin up real PG/MySQL/MSSQL/Oracle containers, register a datasource, test connection, run a query, verify type mapping matches Type System §5.
• Credential encryption tests: encrypt/decrypt round-trip, key-rotation flow, tamper detection (auth tag failure).
• Connection pool tests: lease timeout, max-pool-size enforcement, eviction on datasource delete.

14. Open Questions / Future Additions

Out of scope for v1 (v1.1 candidates are tracked in ROADMAP §2):

• KMS integration: AWS KMS / GCP KMS / HashiCorp Vault as an additional explicit master-key source (never an implicit fallback — §7.1).
• Background health checks: scheduled polling of datasources with UI health indicators.
• Pool settings beyond the eight (§5). The dialog offers the eight keys a person tunes; the rest of HikariCP stays REST-only passthrough. A dialect that wants its own pool defaults has the seam (DialectAdapter.defaultHikariProperties) and no shipped dialect uses it yet — an embedded engine wanting a single-connection pool is the first plausible caller.
• A retirement view. datapipelines.datasource.pool.retired / .hard_closed (§5.2) are counters; there is no surface that lists what is currently draining on this instance. The reaper's state is in memory and per-instance, which is the right place for it, but it means "why is this pool still open" is answered from logs today.
• Datasource groups / failover: pair primary + replica, fail over on connection failure.
• Read-only enforcement: some datasources should be read-only by contract (we never write to sources, but enforcing at the datasource level adds defense).
• SSH tunnel / bastion host support: for datasources reachable only via bastion. Common in enterprise.
• private_key / service_account_json credential kinds reaching a real dialect. The kinds are catalogued and refused by every shipped adapter (§3.4); Snowflake key-pair auth and BigQuery service accounts are what will declare them.
• LAKE dialect — an object store read in place: SHIPPED. The dialect and adapter landed in 087; round 089 added dp-lake (§8C): the dp-catalog registry, per-table views on connect, registry-backed introspection, engine limits, image-bundled extensions, and the sample-lake demo datasource reading the published s3://datapipelines-co/sample-data/lake/<version>/ objects in place (built by scripts/sample-data-lake/ — 088). What stays future: external catalogs as a registry source (§8C.8).

15. Adding a dialect — the checklist

Every engine touches the same places, and a new one is added by walking this list top to bottom, not by grepping for the last one. The code inventory below is mechanical: DialectChecklistDriftTest (modules/datasources) lists every main Kotlin source file under the modules tree that names three or more of the six customer engines (POSTGRES, MYSQL, ORACLE, MSSQL, SQLITE, DUCKDB) and fails when one of them is missing here — so a new switch point cannot appear without appearing in this section. The other tables are maintained by hand; the test checks only that every path they name still exists.

15.1 Code — per-dialect switch points (mechanical)

Also code, but guarded by the compiler rather than by this list: every exhaustive when (dialect) (modules/dag NodeRunner, staging, the H2 mappers) goes red on a new enum value — add the branch the compiler names, never an else.

15.2 Storage, deployment and demo

15.3 Docs and the skill

15.4 Tests that enumerate dialects

DialectEnumSchemaTest (MCP schema), DialectAdaptersTest, DialectRefusalSetsTest, TypeMappersTest, MappingTableReachabilityTest, FallbackTypeMapperTest, TableStatsPlanTest, PoolSettingsTest, DatasourcesTemplateRenderTest, DatasourceDetailRenderTest — each iterates Dialect.entries and will exercise the new value; the per-dialect integration suites (SchemaIntrospector*Test, SqlProbe*Test, TableStats*IntegrationTest, the DialectProbe fixture) get their Testcontainers twin.

Appendix A: Change Log