Lookups

Lookups

A lookup is a named query that API Dock runs itself, at startup and then on a schedule or on demand. Its rows fill in config: one database/version or remote version per row, or a single value such as a table URI. Use lookups when what you serve is recorded somewhere else, for example a catalog of model runs with the location of each run's output.

# api_dock_config/databases/config.yaml
lookups:
  model_runs:
    sql: |
      SELECT name, version, replace(version, '.', 'p') AS schema, detections_uri
      FROM [[core.model_runs]] WHERE published
    refresh: 7d
    allow: ["s3://dse-soundhub/artifacts/"]

slugs:
  - from: model_runs                    # one database/version per row
    name: "{{row.name}}"
    version: "{{row.version}}"
    schema:
      name: "{{row.name}}_{{row.schema}}"
      tables:
        detections: {uri: "{{row.detections_uri}}"}

# api_dock_config/config.yaml
databases:
  - from: model_runs                    # every database the lookup generates

Generated databases/versions are ordinary config. latest, the /<name>/ version listing, unions such as [[*.detections]], schema groups, catalog endpoints and the startup checks all treat them like databases written by hand.

Defining lookups

Lookups go under lookups: in databases/config.yaml or remotes/config.yaml. Both files share one set of names, so a name can't be defined in both. Names are letters, digits and underscores.

Each lookup has exactly one source: sql, url or remote.

Key	Applies to	Meaning
sql	SQL	The query. [[table]] references work as in routes; {{variables}} aren't allowed
url	HTTP	URL to GET
remote	HTTP	A remote's name; its url and injected cookies are used
version	remote	The remote's version, for a versioned remote
path	HTTP	Appended to url or the remote's url
params	HTTP	Query parameters to send
headers	HTTP	Request headers; a value env:NAME is read from the environment
rows	HTTP	Dotted path to the row list in the JSON response, e.g. data.items (default: the whole response)
timeout	HTTP	Seconds to wait (default 30)
refresh	all	How often to re-run: 30s, 15m, 12h, 7d, 2w or a number of seconds. Omit it, or set false, to run only at startup (and on demand)
required	all	true: a failure at startup stops startup (default false)
allow	all	Prefixes that URIs and URLs filled from this lookup must start with (see Safety)
description	all	Notes; not used by API Dock

SQL lookups run on DuckDB and can read any table the shared config defines:

• Parquet, CSV and JSON files (local, S3, GCS, Azure, HTTPS)
• PostgreSQL tables, which DuckDB attaches read-only
• a mix of both
• unions across schemas

The SQL is always DuckDB SQL, even when every table is in PostgreSQL. A lookup can read only tables written in the config, not tables that lookups generate.

HTTP lookups send a GET request and expect JSON:

• rows must point at a list of objects. An object on its own counts as one row.
• A response with status 400 or higher, a body that isn't JSON, or a missing rows path makes the lookup fail.

Rows are lists of columns. Values come through as JSON values: dates and times become ISO strings, and decimals become numbers.

Catalog examples

The catalog can live wherever is convenient; only the database entry for it changes.

database:
  # a PostgreSQL table (see PostgreSQL for connections)
  connections:
    core: {host: db.example.com, dbname: catalog, user: readonly, password: env:DB_PASSWORD}
  runs_pg: {connection: core, table: public.model_runs}
  # a Parquet file (local, s3://, gs://, az://, https://)
  runs_parquet: {uri: s3://my-bucket/catalog/model_runs.parquet, region: us-west-2}
  # a CSV file: easy to edit by hand. A relative path is relative to where api-dock starts
  runs_csv: local_catalog/model_runs.csv

lookups:
  model_runs:
    sql: |
      SELECT name, version, description, detections_uri, revisions_uri,
             replace(name, '-', '_') || '_' || replace(version, '.', 'p') AS schema
      FROM [[runs_pg]]                  -- or [[runs_parquet]], [[runs_csv]]
      WHERE published
    allow: ["s3://my-bucket/runs/"]

  # the same rows from an HTTP API
  deployments:
    url: https://deploy.example.com/api
    path: runs
    params: {published: "true"}
    headers: {Authorization: "env:DEPLOY_TOKEN"}
    rows: data.runs
    allow: ["s3://my-bucket/runs/"]

The schema expression builds a valid schema name from the name and version (birdnet-bullfrog 2.4v0.5 becomes birdnet_bullfrog_2p4v0p5). You can store schema names in the catalog instead if you want to choose them.

Using rows

from: entries

An entry with from: <lookup> is a template. It produces one entry per row, with {{row.<column>}} filled in. Nested JSON values are read with dots: {{row.links.data}}. from: works in three places:

# databases/config.yaml
slugs:
  # 1. whole database/version entries (several databases)
  - from: model_runs
    name: "{{row.name}}"
    version: "{{row.version}}"
    description: "Run {{row.run_id}}"
    schema: "{{row.schema}}"          # an existing schema, or an inline schema (below)

  # 2. versions of one database, next to static versions
  - name: birdnet
    description: BirdNET            # defaults for every version
    versions:
      - version: "2.4"
        schema: birdnet_2p4
      - from: birdnet_runs
        version: "{{row.version}}"
        schema: {name: "birdnet_{{row.schema}}", tables: {detections: "{{row.uri}}"}}

# remotes/config.yaml
remotes:
  wolf:
    description: Wolves
    versions:
      # 3. remote versions (the whole list, or one entry in it)
      - from: wolf_deployments
        version: "{{row.version}}"
        url: "{{row.url}}"

versions: {from: ...} (a mapping instead of a list) is the same as a one-entry list. A lookup with no rows yet generates nothing. A database whose versions all come from a lookup with no rows isn't defined until rows arrive.

Inline schemas

A slug's schema: can define the schema in place as {name, tables}, instead of naming one under database.schema. The schema is added to the shared schemas, so [[*.table]] unions and schema groups can include it. Inline schemas also work in static slugs.

Schema names must be plain identifiers (letters, digits, underscores), but versions usually contain dots. Compute the name in the lookup, as in replace(version, '.', 'p') AS schema.

Serving generated databases

As with any database, a generated one is served only when it's listed in the main config's databases:. Write - from: <lookup> to list every name the lookup's from: entries generate. The list follows the rows, so after a refresh new databases are served and removed ones aren't. A plain name works too, for example - birdnet when only that database's versions come from a lookup.

A remote's versions can be generated, but remote names can't: list the remote by name as usual.

Single values: {{lookup.<name>.<column>}}

A lookup that returns exactly one row can fill any value outside routes:

• version files
• slugs
• shared tables
• remote files
• remotes/config.yaml

# databases/observations/2.0.yaml
tables:
  readings: {uri: "{{lookup.current_release.data_uri}}"}

A lookup with zero or several rows is an error, and so is one that has never run successfully.

Template rules

• Type: a value that is exactly one template keeps the value's type, so numbers stay numbers. A value mixing text and templates is written as text, with null as an empty string and booleans as true/false.
• Null values: a key whose value is exactly one template that is null is left out. So a row can leave out an optional table: revisions: {uri: "{{row.revisions_uri}}"} disappears when revisions_uri is null.
• Where templates are allowed: templates can't be used in routes, query_params, queries, sql, sql_append, response or conditional. This holds everywhere, so lookup values never become SQL. {{row.*}} is allowed only inside a from: entry. A misplaced template stops startup.
• Request values are separate: {{variables}} filled from requests are a different thing, and lookups leave them alone.

Safety

Lookup values come from outside the config, so they are checked before use:

• URIs and URLs need allow:. A table uri, a plain-string table definition, or a remote url filled from a lookup must start with one of that lookup's allow prefixes, at a boundary. s3://bucket/pub matches s3://bucket/pub/x but not s3://bucket/pub-secret/x. A prefix ending in / or : matches anything after it.
• Unsafe values are refused: values containing quotes, backslashes, control characters or a .. segment (also when percent-encoded).
• No allow at all for a lookup that fills a URI is a config error.
• Generated names and versions must be letters, digits, ., _ or -.
• Generated schema names must be identifiers.

A row that fails one of these checks is skipped, and the server logs a warning naming the lookup and the row. A missing column skips the row the same way.

Precedence

• A version file (databases/<name>/<version>.yaml, remotes/<name>/<version>.yaml) wins over a slug or remotes/config.yaml entry for the same version.
• A static entry wins over a generated one.
• When two rows generate the same database/version, the first one wins and a warning is logged.

When lookups run

• At startup. Every lookup runs once before the startup checks. A failing lookup is logged and the server starts without its rows, unless it's required: true, which stops startup. Rows that produce an invalid config also stop startup: a generated version whose [[table]] doesn't resolve, for example, or a schema group that names a missing schema.
• On refresh. Lookups with refresh re-run when due. The FastAPI server checks in the background. Flask checks on each request and refreshes in a background thread. A RouteMapper you create yourself refreshes after await mapper.start().
• On demand. Use the refresh endpoint, the CLI or mapper.refresh_lookups().

A refresh is all or nothing. The new rows are checked with the startup checks first, and if the config they produce is invalid, every lookup in that refresh keeps its previous rows. A lookup that fails to run also keeps its previous rows. Requests keep using the current rows while a refresh runs, and only one refresh runs at a time.

Refresh endpoint

Off by default. Turn it on in the main config:

settings:
  lookups:
    refresh_route: /admin/lookups
    token: env:API_DOCK_ADMIN_TOKEN     # read at startup; must be set

Request	Result
GET /admin/lookups	Each lookup's status: rows (count), fetched_at, attempted_at, next_refresh (Unix epoch seconds) and the last error (null when the last run succeeded)
POST /admin/lookups	Refresh every lookup. Returns refreshed, failed (lookup → message), rejected (why the new rows were refused, or null) and the new status
POST /admin/lookups?name=model_runs&name=other	Refresh only these

Requests need Authorization: Bearer <token>; anything else gets 401. An unknown name gets 404. A POST waits until the refresh finishes.

curl -H "Authorization: Bearer $API_DOCK_ADMIN_TOKEN" http://localhost:8000/admin/lookups
curl -X POST -H "Authorization: Bearer $API_DOCK_ADMIN_TOKEN" "http://localhost:8000/admin/lookups?name=model_runs"

CLI

api-dock lookups                           # run every lookup locally and print its first rows
api-dock lookups --name model_runs --rows 20
api-dock lookups --url http://localhost:8000/admin/lookups              # server status
api-dock lookups --url http://localhost:8000/admin/lookups --refresh    # refresh on the server

A local run uses the config in api_dock_config/ (or api-dock lookups CONFIG_NAME) and doesn't start a server, which makes it handy for checking a lookup while you write it. It exits with code 1 if a lookup fails. With --url, the token comes from --token or API_DOCK_ADMIN_TOKEN.

Python

mapper = RouteMapper("api_dock_config/config.yaml")   # runs lookups once
report = mapper.refresh_lookups(["model_runs"])        # blocking; returns the report
mapper.lookups.status()                                # same as GET on the endpoint
mapper.lookups.set_rows("model_runs", rows)            # supply rows yourself (no checks)

See Python API and Deployment for using RouteMapper in your own app.

Limits

• Lookups don't run per request, so they can't depend on request values. Data that changes picks up new rows at the next refresh.
• A lookup can't read tables that lookups generate.
• api-dock describe shows the config as written, without lookup rows.
• Response headers and hidden ("internal") databases from the earlier route-chaining proposal aren't part of this feature.