Datastores and database pools

Three things are kept separate, and a request touches all three:

Thing	What it is	Where you declare it
Backend	A database driver — pool, SQL driver, transaction factory, query compiler, DDL dialect	Not declared; selected by type
Database pool	A physical connection endpoint for one tenant: host, port, database, credentials, sizing	hives.tenant[].dbpools.<name>
Datastore	A named logical binding that points at exactly one pool	hives.tenant[].datastores.<name>

Your schema declares entities against a datastore name. At request time data.svc resolves tenant → datastore → pool, builds a connection pool, and injects it into an engine:

X-Customer / X-Product / X-Env / X-Tenant   (the CPET request headers)
        └── tenant config
                ├── datastores.main.dbpool: main
                └── dbpools.main            → host:port/database, schema, isolation
                                            → backend type → dialect + compiler

Nothing here is built at boot. The pool, the schema, the hook chain, and the engine are constructed on the first request that names a given (tenant, datastore) pair, cached per CPET, and torn down as one unit on config change or shutdown.

Declaring pools and datastores

Both blocks are maps keyed by name, under the tenant’s config node. The map key is the identity — an inline name: field is overwritten by the key after binding.

hives:
  tenant:
    - path: ""
      name: dev
      active: true
      domain:
        - localhost
        - 127.0.0.1
      datastores:
        main:
          dbpool: main
      dbpools:
        main:
          type: postgres
          dbserver: localhost:5432
          dbuser: erpadmin
          database: erpdb
          password: erpadmin
          schema: erptest       # this tenant's physical PostgreSQL schema
          maxconnections: 10

The datastore key main and the pool key main are independent names that happen to match here. The binding is the dbpool: main line, and dbpool is a child of the datastore, never a sibling of it.

Database pool keys

Key	Type	Default	Notes
type	string	postgres	Empty and postgresql both canonicalise to postgres. Only postgres and clickhouse pass validation
dbserver	string	localhost:5432	host:port. A bare host gets port 5432. A non-numeric port fails the build
dbuser	string	—	Required unless dsn is set
database	string	—	Required unless dsn is set
password	string	""	A literal, or a key resolved through Vault
schema	string	the tenant’s own name	The physical PostgreSQL schema this tenant’s objects live in
isolation	string	dedicated	dedicated or shared
tlsconfig	string	driver default	disable, require, verify-ca, verify-full. Passed through as the driver’s sslmode
maxconnections	int	10	Maximum open connections. Applied only when greater than zero
connection_life_time	duration	unlimited	e.g. 30m. Maximum lifetime of a connection
connect_timeout	duration or int	5s	A bare integer is read as seconds
dsn	string	""	Explicit connection-string escape hatch

password is resolved through the registered vault client before the pool is built. When no vault client is registered — the local dev path — the value is used as a literal password. The config shape is the same either way.

Caution

Malformed durations are dropped silently. connection_life_time: 30min does not parse, produces no warning, and leaves connections with unlimited lifetime. An unparseable connect_timeout falls back to 5s.

Caution

Setting dsn returns success from validation immediately — dbuser, dbserver and database are no longer checked. Nothing parses the DSN back into discrete fields either, so a DSN-only pool has an empty database name in its runtime config, cannot produce an endpoint key, and silently falls back to a dedicated pool even when isolation: shared is declared. A shared-endpoint deployment must use the discrete fields.

Several knobs exist on the runtime pool object but have no configuration key: idle connections, idle time, health-check period, pool-level statement timeout, and arbitrary driver options beyond sslmode. They stay at their zero value and the driver default applies. Do not expect to tune them from YAML.

Datastore keys

Key	Type	Required	Notes
dbpool	string	yes	Name of the entry under dbpools. The sole binding to a physical pool
remoteconnections	map	no	Non-database (HTTP-style) backends attached to this datastore
remoteconnections.<name>.url	string	no	Remote endpoint URL
remoteconnections.<name>.protocol	string	no	Remote endpoint protocol
remoteconnections.<name>.authorization	string	no	Authorization header value sent to the remote endpoint

The schema side of a datastore

A schema file declares the datastores its entities target as a list, and names the one used when a request selects none:

default-datastore: main
datastores:
  - name: main
    version: "18"
    extensions:
      - pg_trgm
      - pgvector
    init:
      scripts:
        - path: sql/extensions.sql
          description: extensions and shared functions
          kind: ddl
          transactional: false

Key	Type	Notes
name	string	Must match a key in the tenant’s datastores: map
type	string	Optional dialect hint. Omit it — see below
version	string	Backend version. Not enforced — see Versions
extensions	list	Installed database extensions; registers their types and operators with the compiler
init.scripts	list	Raw SQL run once per (tenant, datastore)
database	string	Parses, never read
pool	string	Parses, never read
schemas	list	Parses, never read
path	string	Parses, never read
settings	map	Parses, never read
logging	map	Parses, never read

Caution

Only name, type, version, extensions and init.scripts are consumed. The last six keys load onto the datastore and no code path reads them — database and pool in particular do not redirect the connection. The physical endpoint comes from the pool the tenant’s datastores.<name>.dbpool names, and nothing else.

default-datastore is the fallback when a caller passes no datastore selector. Requests select another with the datastore query parameter — see the Data API HTTP reference. When a schema declares exactly one datastore and no default-datastore, that one is used. If the requested name is absent from the loaded schema the build fails with schema has no datastore <name>, and the message enumerates the default and every available name.

Init scripts

init.scripts[] is raw SQL run once per (tenant, datastore) pair on the first engine build — extensions, base functions, default triggers, type definitions. Each entry takes path (required), description, kind (ddl or dml), and transactional (default true; set it false for statements PostgreSQL refuses to run inside a transaction).

The runner records applied checksums in a ledger and skips any script whose content checksum is already there, so a new process boot applies only newly-added scripts. Script bodies must be idempotent on their own — CREATE EXTENSION IF NOT EXISTS, CREATE OR REPLACE FUNCTION.

Caution

An init-script failure is non-fatal. It is logged at error level and the engine still boots and serves traffic. Nothing in /ready, /health or /checktenant reflects it. data init list shows the declared scripts and their applied state; data init run forces a re-pass.

Dialect resolution

The datastore’s backend type selects the SQL dialect and compiler. It may be declared on the schema’s datastores: entry, but that is redundant with the pool’s type:. When the schema omits type, the service infers it from the resolved pool and logs that it did so. The inference is copy-on-write; the cached schema is never mutated.

Caution

Inference fires only when the schema-side type is empty. If a schema pins one backend type and the bound pool declares another, the schema’s value wins for dialect and compiler selection while the pool still selects the physical driver — and nothing checks the mismatch. Declare the backend in one place: the pool.

Spelling is not uniform across the surfaces. The canonical pool type and the shared-endpoint key use postgres; the backend driver reports itself as postgresql; schema-side dialect lookup accepts both, as does engine assembly. Match the spelling to the field rather than assuming one canonical string.

Backend vocabulary

symbols: and functions: are top-level schema keys — siblings of entities:, not children of a datastores: entry. Both describe SQL vocabulary that belongs to a specific database backend, and both are attached to the dialect built for the resolved datastore.

Key	Shape	Declares
symbols	map keyed by backend name	Database function and keyword names exposed for that backend
functions	list of named entries	User-defined functions the schema refers to by name

Symbols

A symbols: block is keyed by backend. Each backend bucket takes one key, systemfunctions, a list of names:

symbols:
  postgres:
    systemfunctions:
      - min
      - max
      - count
  clickhouse:
    systemfunctions:
      - multiMatchAllIndices
      - multiFuzzyMatchAllIndices

Each name is flattened into one symbol entry with category function and one mapping per backend that lists it. The mapping value is the name itself, so a symbol is never rewritten into different SQL — listing it exposes the name for that backend, and nothing more. A name listed under two backends produces a single entry carrying both mappings.

Backend keys are matched by alias when a symbol is resolved: postgresql, postgres and pg all match PostgreSQL; clickhouse and ch match ClickHouse; sqlite and sqlite3 match SQLite; duckdb matches only itself. A key outside those sets matches no dialect and the block has no effect.

Schema-declared symbols are consulted before the dialect’s own built-in symbol table, so listing a name the dialect already provides overrides the built-in.

Across multiple schema files the merge is last-write-wins per backend key, on the whole bucket. A second file declaring symbols: with a postgres: key replaces the first file’s systemfunctions list rather than appending to it. Keep one backend’s symbols in one file. In a layered schema the key is dropped entirely — the base’s symbols are carried and a layer’s are ignored, see Schema sources.

Caution

Example schemas bundled in the repository declare symbols as a list of {name, category, mappings} entries with per-backend SQL templates. That is the other YAML front end, not the one the deployable service uses. The service requires the map-keyed-by-backend form above; a sequence under symbols: is a YAML type error and fails the load of the whole document.

Database functions

functions: declares user-defined functions by name so the schema can refer to them. Entries are a list:

functions:
  - name: tax
    kind: computed
    runtime: sql
    entity: invoice
    source: tax(amount, rate)

Key	Values	Notes
name	string	The lookup key. Names are unique per schema; a later file with the same name replaces the earlier entry
kind	trigger, validation, transform	Anything else, including an omitted value, is read as computed
runtime	sql, expr, cel, js, lua, starlark	sql marks the function inlinable into compiled SQL; every other runtime is executed outside the query
entity	string	The entity the function applies to
source	string	The expression or file path
trigger.event	string	e.g. before_insert, after_update
trigger.entity	string	The entity the trigger fires on

Caution

A user-defined function is written in one database’s procedural dialect, so a schema that declares one is bound to that backend. There is no per-backend keying on functions: the way there is on symbols: — the entry applies to whatever backend the datastore resolves to. Keep UDFs out of a schema you intend to run on more than one engine.

Caution

Declaring a function does not create it. No CREATE FUNCTION DDL is emitted for a functions: entry; the function must already exist in the database, which means creating it yourself through an init script or a migration.

Both keys parse, load into the schema, and populate the registries attached to the dialect — but no query-compiler path resolves through those registries today. Declaring symbols or functions changes nothing about the SQL the service emits. The other form of definition: seen in older example files is not a key the service reads at all; it is dropped silently.

Physical schema qualification

The PostgreSQL schema a tenant’s objects live in comes from that tenant’s pool schema: property, never from a process constant. Resolution is: the explicit schema: when set, otherwise the tenant’s own name.

That value is written into the composed schema’s name, so the query compiler qualifies all DML as "<schema>"."<table>", and it is also sent as the pool’s search_path startup parameter. The same namespace drives the migration pipeline — the dialect’s schema name so DDL is emitted qualified, the discovery inputs so introspection inspects the right namespace, and the ledger so plan and step rows land in the right place.

Qualification applies in both isolation modes. An earlier design had shared pools clear the schema name and rely on the per-checkout search_path flip; that was reversed, because the engine’s non-transactional read path uses the raw database handle, which never passes through connection checkout. With unqualified SQL, two tenants sharing an endpoint wrote into one schema. The checkout flip and app.tenant_id remain as defence in depth.

Caution

Declaring dbpools: once at a shared prefix config node and letting tenants inherit it gives every tenant the same explicit schema: — one shared set of tables, with no error anywhere. Restate schema: at each tenant leaf.

An omitted schema: is convenient — schema-per-tenant isolation in a shared database for free — but it ties the physical namespace to the tenant name, so a tenant rename moves the namespace. The identifier used in the search_path SET is quoted; a value containing a double quote or semicolon is discarded and replaced with a fixed placeholder identifier, so a schema name can never inject into the statement.

Schema qualification is PostgreSQL-only. ClickHouse, DuckDB and SQLite suppress the schema prefix in compiled SQL, so the whole physical-namespace mechanism does not apply to them.

Pool isolation

	dedicated (default)	shared
Pool scope	One private pool per (CPET, datastore)	One reference-counted base pool per physical endpoint
Endpoint key	n/a	<backend>://<host>:<port>/<database>
schema:	Optional	Required — build fails without it
Connectivity check	Pinged once after creation; a failed ping fails the build	No probe ping; the first query validates
Failure surface	Credential and network errors at pool build	Credential and network errors at first query
Engine, schema, hooks	Per tenant	Per tenant — only the physical pool is shared

The endpoint key deliberately excludes schema and user, because the schema flips per checkout and including it would partition the pool. Two consequences:

• All tenants on a shared endpoint connect as the same database role. Varying dbuser across tenants on one endpoint does not partition the pool — they all connect as whichever tenant built it first.
• The base pool is sized by the first tenant’s maxconnections. Size a shared endpoint for the whole fleet, not for one tenant.

Acquire on an endpoint builds the base pool on first miss and increments a refcount; each tenant’s router releases it on invalidation, and the base closes when the last tenant releases. Shutdown force-closes every base pool regardless of refcount, because every engine is being torn down anyway.

Caution

isolation: shared with no schema: fails the engine build outright — shared pooling without a per-tenant namespace provides no isolation at all. The symptom is a 500 on every request for that tenant.

A misspelled value fails the other way. Only shared is compared against; every other value, including a parse failure like isolation: shred, takes the dedicated branch silently. dedicated and shared are the only meaningful values.

Per-checkout tenant session

Tenant identity is not stored on the pool object. It flows through the request context and is applied per connection checkout. On PostgreSQL the hook emits, in order:

SET search_path TO "<schema>";      -- shared isolation only
SET app.tenant_id = '<cpet key>';
SET app.user_id = '<user>';         -- when non-empty
SET statement_timeout = 30000;

app.tenant_id and app.user_id are session variables for database-native policies you create yourself, through an init script or a migration. The Data API’s own row-level security does not read them: it compiles a predicate from the request principal and ANDs it into the SQL, and no CREATE POLICY DDL is emitted for an entity’s RLS rules.

A failure anywhere in this setup closes the connection instead of handing it to the caller, so no partial session state leaks.

Caution

This hook runs on connection acquire only, and the engine’s non-transactional read path uses the raw database handle. Plain reads therefore run with no server-side statement timeout: the pool-level timeout has no configuration key and is always zero, and the 30s ceiling above applies only to acquired and transactional connections.

The raw handle bypasses the tenant session deliberately, for migrations and introspection. Every raw-SQL subsystem — migration ledger, lock manager, retention eraser, archive handoff queue, materialised-refresh task table, init scripts — is therefore explicitly namespace-qualified. This is why qualification, not the checkout flip, is the isolation guarantee on a shared pool.

Session reset

Every connection hand-off on PostgreSQL runs DISCARD ALL, wired through both the pool’s after-release hook and the driver’s session-reset option. It wipes search_path, timeouts, prepared statements, temp tables, cursors, LISTEN channels and SET variables. A failed reset drops the connection rather than returning it to the pool — under a persistent failure such as revoked privileges this shows up as constant connection churn and pool exhaustion, not an obvious error.

Because DISCARD ALL implies DEALLOCATE ALL, prepared-statement caching is disabled and the driver runs in simple-protocol mode. That is a deliberate per-query cost traded for correctness: a cached statement name the server has dropped fails with prepared statement does not exist.

Backends

data.svc serves PostgreSQL only for tenant datastores. Three independent chokepoints enforce it, and they do not agree with each other:

Stage	Accepts	Rejects with
Pool config validation	postgres, postgresql, clickhouse	unknown db pool type "<type>" for pool "<name>"
Pool factory lookup	postgres	no v2 pool factory for DBPool type "<type>" (datastore=<name>)
Engine component assembly	postgresql, postgres, sqlite, sqlite3	unsupported backend type "<type>" (supported: postgresql, sqlite)

So type: duckdb and type: sqlite are rejected at config validation; type: clickhouse passes validation and then fails at pool-factory lookup. ClickHouse, DuckDB and SQLite are implemented as engine backends — pool, driver, transactions, compiler, dialect — but they are not reachable from tenant configuration. Treat PostgreSQL as the deployment target.

The tables below are what each backend declares about itself. They are the portability reference for the SQL the compiler emits per backend. The schema-refusal gates built on those declarations are a separate matter — see Capability gates for what actually runs in data.svc.

Transactions

	PostgreSQL	ClickHouse	DuckDB	SQLite
Transactions	Yes	24.8+ only	Yes	Yes
Savepoints	Yes	No	Yes	Yes
Transactional DDL	Yes	No	Yes	Yes
Isolation levels	Read committed, repeatable read, serializable	Snapshot (24.8+)	Read committed, repeatable read, serializable	Serializable

Query and mutation model

	PostgreSQL	ClickHouse	DuckDB	SQLite
Joins, subqueries, set ops	Yes	Yes	Yes	Yes
Window functions	Yes	Yes	Yes	Yes
Lateral joins	Yes	No	Yes	No
Writable CTEs	Yes	No	Yes	Yes
Upsert (ON CONFLICT)	Yes	No	Yes	3.35+
RETURNING	Yes	No	Yes	3.35+
Regex operators	Yes	Yes	Yes	No
Information schema	Yes	No	Yes	No
Placeholder style	$1	?	$1	?

Storage, DDL and security

	PostgreSQL	ClickHouse	DuckDB	SQLite
Orientation	Row	Column	Hybrid	Row
Default engine	heap	MergeTree	—	—
Row-level security	Yes (incl. per-role)	No	No	No
Triggers, sequences, identity, functions	Yes	No	No	No
Online add column	Yes	Yes	Yes	Yes
Online schema change	Yes	No	No	No
Concurrent index build	Yes	No	No	No
Tablespaces	Yes	No	No	No
Schema qualification	Yes	Suppressed	Suppressed	Suppressed

ClickHouse also offers ReplacingMergeTree, SummingMergeTree and AggregatingMergeTree; all of them require an ORDER BY.

Indexes, partitioning and views

	PostgreSQL	ClickHouse	DuckDB	SQLite
Index types	BTree, Hash, GIN, GiST, BRIN, full-text, partial, covering, expression, concurrent, multicolumn	minmax data-skipping (reported as BTree), multicolumn, Bloom at 24.1+	BTree, Hash, partial, expression, multicolumn	BTree, partial, expression, multicolumn
Max index columns	32	—	—	—
Partitioning	Range, list, hash, nested, detach/attach	Range, auto-create, detach/attach, compression	None	None
Materialized views	Yes, concurrent refresh	Yes, incremental	No	No
Regular views	Yes	Yes	Yes	Yes
Recursive views	Yes	No	Yes	Yes

History and soft delete

	PostgreSQL	ClickHouse	DuckDB	SQLite
History strategies	Audit, Type 3, Type 4, Type 2, Type 6	None	Audit, Type 3, Type 4, Type 2, Type 6	Audit, Type 3, Type 4, Type 2, Type 6
Point-in-time (asof) queries	Yes	No	Yes	Yes
Soft delete	Yes	No	Yes	Yes

ClickHouse declares no support for history or soft delete because its updates and deletes are asynchronous background mutations: the “delete returned, next read hides the row” guarantee cannot hold, and with no transactional rollback an audit side-table write can succeed while the main mutation fails. Model temporal semantics there explicitly with a deduplicating table engine instead.

Extensions

Declared under datastores[].extensions. On PostgreSQL the list reaches the compiler dialect, which registers the extension’s types and operators and unlocks the features gated on them:

Extension	What declaring it adds
pgvector	vector, halfvec, sparsevec types; distance operators; vector-operation feature gate
postgis	geometry, geography, box2d, box3d types; spatial operators; geospatial feature gate
pg_trgm	trigram operators and similarity functions
citext	case-insensitive text type
uuid-ossp	UUID generation functions

Names outside that set are accepted and ignored. A second, separate list — pgvector, pg_trgm, timescaledb, postgis, pg_cron on PostgreSQL, vss (or duckdb-vss) and fts on DuckDB, fts5 and rtree on SQLite, none on ClickHouse — feeds the capability matrix, which data.svc does not resolve. Declaring timescaledb or pg_cron therefore changes nothing in the deployed service.

Versions

Each backend declares support ranges. Comparison is numeric on major and minor only, so 24.3.1 compares as 24.3.

Backend	Supported	Deprecated	End of life
PostgreSQL	13.0+	12.0 – 13.0	below 12.0
ClickHouse	23.8+ (LTS)	23.3 – 23.8	below 23.3
DuckDB	0.10+	0.9 – 0.10	below 0.9
SQLite	3.37+	3.35 – 3.37	below 3.35

Caution

data.svc does not enforce these ranges. The range check — the deprecation warning, <backend> <version> is end-of-life, and <backend> version "<v>" is not in any supported range — lives in a backend-resolution step the deployed service never calls. datastores[].version reaches the PostgreSQL dialect but selects nothing there; it appears only in dialect error text. Setting it wrong is silent, and the ranges above are guidance for what to run, not a gate.

Capability gates

Four validators reject a schema that asks for something the resolved backend does not declare.

Check	Trigger	Message
Soft delete	An entity carries a deletedon field on a backend without synchronous row-level UPDATE	unsupported soft-delete: entity <name> uses kisai.softdelete but backend <b> does not support the contract
History	An entity’s history strategy is not in the backend’s list	unsupported history strategy: entity <name> declares History.Strategy=<s> but backend <b> supports only [<set>]
Partitioning	A partition key on a backend with no partitioning	unsupported partitioning: entity <name> declares Storage.PartitionBy=<k> but backend <b> has no partitioning support
Policy	A datastore policy enables RLS, triggers, concurrent indexes, exclusion constraints, transactional DDL, or a default engine the backend lacks	policy enables RLS but <b> does not support it (and siblings)

Soft-delete detection is purely the presence of a deletedon field.

Caution

None of these run in data.svc. They fire on the engine library’s own open path, which the deployed service does not use — it assembles the engine itself. Nothing gates a schema against backend capabilities at request time. In practice nothing would fire anyway: the service serves PostgreSQL only, and PostgreSQL declares every capability these four check. There is also no schema-side key for the datastore policy the fourth check reads, so the policy gate is unreachable from YAML.

Errors

Code or message	Meaning
datastore-not-found	The requested datastore name is not in the tenant’s datastores map
dbpool-not-found	A datastore’s dbpool: names no key under dbpools:
invalid-db-pool-config	The pool exists but failed validation
tenant-config-unavailable	The tenant’s config could not be bound to the pools/datastores shape at all
unknown db pool type "<t>" for pool "<n>"	type is not postgres/postgresql/clickhouse
invalid postgres db pool "<n>": dbuser, dbserver and database are required	A required field is missing and no dsn was supplied
no v2 pool factory for DBPool type "<t>" (datastore=<n>)	Type is valid but no factory is registered for it — anything other than postgres today
unsupported backend type "<t>" (supported: postgresql, sqlite)	Engine components could not be assembled for the resolved type
no dialect implementation for datastore type "<t>"	The migration path could not map the type to a dialect
DBPool declares isolation: shared but no schema: property	Shared isolation without a per-tenant namespace
no DStore config for "<tenant>":"<ds>" (pass ?datastore=<name>)	No datastore selected and none resolvable for the tenant
schema has no datastore "<n>" (default=…, available=…)	Name mismatch between tenant config and the loaded schema
pool: parse db port "<p>"	The port in dbserver is not an integer
postgres: parse config / postgres: connect	Malformed DSN, unreachable host, or bad credentials
pool/shared: tenantize	Per-checkout session setup failed; the connection is closed rather than returned

Pool and endpoint diagnostics — refcounts, endpoint snapshots, tenantize error counts — are collected internally but not exposed on any route.