Concepts

Concepts

The ideas the other pages build on: what API Dock serves, how a URL maps to a source, and how configuration is organized. Read this once; the other pages assume it.

One endpoint, two kinds of source

API Dock is a gateway configured with YAML files. Behind one host it serves:

• Remotes: other HTTP APIs, proxied. Requests are forwarded and responses streamed back, with allow-lists, restrictions and optional route mapping in between.
• Databases: routes that run SQL. Tables are files (Parquet, CSV, ... on local disk, S3, GCS, Azure or HTTP) or PostgreSQL tables, and each route's SQL is filled in from the request.

flowchart LR
    C[Client] --> D[API Dock]
    D -->|"/remote/..."| R[Remote HTTP API]
    D -->|"/database/version/route"| E{Engine}
    E -->|files, or a mix| K[(DuckDB)]
    E -->|all tables on one connection| P[(PostgreSQL)]
    K -.->|reads| F[Parquet / CSV on disk, S3, GCS, Azure, HTTP]
    K -.->|attaches read-only| P

Loading

URLs

/<name>/<version or latest>/<route path>?<query params>

• <name> is a remote or database: listed in the main config (remotes: / databases:), or found in the config folder when there's no list. Names may contain / (birdnet/2.4/bullfrog); a request goes to the longest name its path starts with. See Configuration.
• <version> appears only for versioned remotes and databases; latest picks the highest, by rules described in Versioning.
• <route path> matches a route in that remote or database config; {{name}} segments become path parameters.
• Query parameters become filters, sorting, limits and so on, but only those a route declares. See Query Parameters.

GET / returns API metadata. Optional catalog endpoints such as /databases list what's available; see Catalog Endpoints.

Configuration files

api_dock_config/
├── config.yaml               main config: what is served, settings
├── remotes/
│   ├── config.yaml           shared remote config (optional): remotes and
│   │                         their versions written inline
│   ├── weather.yaml          an unversioned remote
│   └── core/                 a versioned remote: one file per version
│       └── 0.5.0.yaml
└── databases/
    ├── config.yaml           shared database config (optional): tables, schemas,
    │                         connections, slugs, shared routes
    ├── inventory.yaml        an unversioned database
    └── birdnet/              a versioned database: one file per version
        └── 2.4.yaml

The main config.yaml is read once, when the server starts. Remote files, database files and the shared remote and database configs are read again on each request, so route edits take effect without a restart (database configs are only checked at startup, though, and PostgreSQL connections are fixed then). See Configuration.

Database routes, values and tables

A database route maps a URL to SQL:

routes:
  - route: recordings/{{recording_id}}/detections
    sql: SELECT * FROM [[detections]] WHERE recording_id = {{recording_id}}

• {{recording_id}} is a value from the path, query string, a default or a cookie. Values are sent to the database as bound parameters, never pasted into SQL, so they can't change the query. Write them without quotes.
• [[detections]] is a table reference, replaced by the table's source. After FROM/JOIN it becomes, for example, 's3://bucket/detections.parquet' AS detections, and elsewhere just detections.
• A route's sql can also be a decision tree that picks SQL from the request: see Conditional SQL.

A table is defined by a URI (a file table) or by a connection plus a table name (a PostgreSQL table). Tables can be defined in three places, looked up in this order:

1. the database/version file's own tables
2. the schema that file names with schema: (a named group of tables in the shared config)
3. global tables in the shared config

Schemas and the shared config

The shared databases/config.yaml holds what many databases and versions have in common:

• Schemas: named groups of tables. A version picks its schema with schema: birdnet_2p4, and any route can reach another schema's table as [[birdnet_2p4.detections]].
• meta: default table settings (S3 region, public, a default PostgreSQL connection).
• connections: named PostgreSQL connections.
• slugs: database/version definitions written inline instead of as files.
• Shared routes and query params: added to every database/version, with include/exclude lists.

See Shared Database Config.

Unions read the same table from several schemas at once: [[*.detections]] (every schema), [[*!.detections]] (every schema except the current version's) and [[group.detections]] (a named list of schemas). Rows can be tagged with the schema, name and version they came from. See Cross-Schema Queries.

Engines: DuckDB and PostgreSQL

Each route runs on one engine, chosen from the tables it can reference:

The route's tables	Engine
Files only, or none	DuckDB
All on one PostgreSQL connection (unions included)	PostgreSQL, natively
Files and PostgreSQL mixed, or several connections	DuckDB, with PostgreSQL attached read-only

The engine decides the SQL dialect. A route can check or force it with engine: postgres | duckdb. See PostgreSQL.

Versions and slugs

A versioned database or remote is a folder of version files (birdnet/2.4.yaml). Versions can also be defined inline: database versions as slugs in databases/config.yaml, remotes in remotes/config.yaml. Files and inline versions combine, and a file wins when both define the same version. latest resolves to the highest version, comparing numbers as numbers (0.10 beats 0.9). See Versioning.

Lookups generate versions instead of you writing them: a lookup is a query (over your tables or an HTTP API) that API Dock runs at startup and on a schedule, and a from: entry turns each of its rows into a database/version or remote version. See Lookups.

Checked at startup, read per request

When the server starts, API Dock checks every database and version as requests will see them, including shared routes. It checks route shapes, variables inside quotes or comments, table references and unions that don't resolve, source_columns, PostgreSQL connections, and each route's engine. A bad config stops startup with a message naming the database, version and route. See SQL Database Support.

Glossary

Term	Meaning
remote	An HTTP API that API Dock proxies
database	A set of SQL routes over tables, optionally versioned
route	A URL pattern mapped to an upstream path (remotes) or SQL (databases)
value / variable	{{name}}: a request value bound into SQL
table reference	[[name]]: replaced by a table's source in SQL
schema	A named group of tables in the shared config
union	[[*.t]], [[*!.t]], [[group.t]]: one table read from several schemas
slug	A database/version defined inline in the shared database config
inline remote	A remote (and its versions) defined in remotes/config.yaml
lookup	A query API Dock runs itself; its rows generate config (from:) or fill values ({{lookup.name.column}})
connection	A named PostgreSQL connection (one pool per connection)
engine	DuckDB or PostgreSQL: where a route's SQL runs