Call an AI gateway from a Flight

You have text in a MotherDuck table and want a model to label it, summarize it, or extract fields from it on a schedule. OpenRouter, Cloudflare AI Gateway, Vercel AI Gateway, and Together AI all expose the same OpenAI-compatible /chat/completions endpoint, so one Flight covers all four: the provider is a config value and the API key is a Flight secret.

This guide labels three short reviews with a sentiment word and writes the labels back to MotherDuck. The Flight only sends requests for rows it hasn't scored yet, so a repeat run costs nothing.

Before you start

You need an account and an API key with one of these providers. Each one bills you per request, so start with a small, cheap model.

Provider	AI_BASE_URL	Example AI_MODEL
OpenRouter	`https://openrouter.ai/api/v1`	`openai/gpt-4.1-mini`
Cloudflare AI Gateway	`https://api.cloudflare.com/client/v4/accounts/<account_id>/ai`	`@cf/meta/llama-3.1-8b-instruct`
Vercel AI Gateway	`https://ai-gateway.vercel.sh/v1`	`openai/gpt-4.1-mini`
Together AI	`https://api.together.ai/v1`	`meta-llama/Llama-3.3-70B-Instruct-Turbo`

Model catalogs differ between providers. Replace the example model with one your account can reach.

note

Cloudflare's base URL contains your account ID, and its token needs the Account > Workers AI > Read permission. A Workers AI model whose name starts with @cf/ also needs a cf-aig-gateway-id header naming the gateway to route through. See Switch providers.

The Flight runtime injects MOTHERDUCK_TOKEN for you, so duckdb.connect("md:") needs no configuration. To run a scheduled Flight as a service account instead, see Authentication, config, and secrets.

Store the provider key as a Flight secret

The API key is the one value that must not sit in config or in the Python source, both of which are readable by anyone who can read the Flight. Put it in a Flight secret instead. Run this in the MotherDuck UI SQL editor with your own key:

CREATE SECRET ai_gateway IN MOTHERDUCK (
    TYPE FLIGHTS,
    PARAMS MAP {'AI_API_KEY': '<your_provider_api_key>'}
);

At run time MotherDuck injects each key in the secret as an environment variable, both under its bare name (AI_API_KEY) and namespaced as ai_gateway_AI_API_KEY. The Flight below reads the bare name. If you attach several secrets to one Flight, read the namespaced form, which can't collide.

Create the source table

The demo reads from a three-row table so you can see the whole flow for a few cents:

Create the demo review schema

CREATE SCHEMA IF NOT EXISTS docs_playground.flights_demo;

SQL Editor loading...

Seed three reviews to label

CREATE OR REPLACE TABLE docs_playground.flights_demo.ai_gateway_reviews AS
SELECT *
FROM (
  VALUES
      (1, 'Queries that took four minutes on our old warehouse come back in seconds.'),
      (2, 'I lost an afternoon to a token error and the message told me nothing.'),
      (3, 'It does what the docs say it does.')
) AS t(review_id, body);

SQL Editor loading...

Create the Flight

AI_BASE_URL and AI_MODEL live in config, so switching providers is a config change rather than a source edit. Create the Flight without a schedule and run it by hand first.

Create the AI gateway Flight

SELECT flight_id, flight_name, current_version
FROM MD_CREATE_FLIGHT(
  name := 'docs_ai_gateway_chat',
  config := MAP {
      'AI_BASE_URL': 'https://openrouter.ai/api/v1',
      'AI_MODEL': 'openai/gpt-4.1-mini'
  },
  requirements_txt := array_to_string([
      'duckdb==1.5.3',
      'httpx==0.28.1'
  ], chr(10)),
  source_code := $flight$
import os
import duckdb
import httpx

SYSTEM_PROMPT = "Reply with exactly one word: positive, negative, or neutral."

def main():
  base_url = os.environ["AI_BASE_URL"].rstrip("/")
  model = os.environ["AI_MODEL"]
  api_key = os.environ["AI_API_KEY"]

  con = duckdb.connect("md:")
  con.execute("CREATE SCHEMA IF NOT EXISTS docs_playground.flights_demo")
  con.execute("""
      CREATE TABLE IF NOT EXISTS docs_playground.flights_demo.ai_gateway_sentiment (
          scored_at TIMESTAMPTZ,
          review_id INTEGER,
          returned_model VARCHAR,
          sentiment VARCHAR
      )
  """)

  pending = con.execute("""
      SELECT review_id, body
      FROM docs_playground.flights_demo.ai_gateway_reviews
      WHERE review_id NOT IN (
          SELECT review_id FROM docs_playground.flights_demo.ai_gateway_sentiment
      )
      ORDER BY review_id
  """).fetchall()

  with httpx.Client(timeout=60) as client:
      for review_id, body in pending:
          response = client.post(
              f"{base_url}/chat/completions",
              headers={"Authorization": f"Bearer {api_key}"},
              json={
                  "model": model,
                  "messages": [
                      {"role": "system", "content": SYSTEM_PROMPT},
                      {"role": "user", "content": body},
                  ],
                  "max_tokens": 8,
                  "stream": False,
              },
          )
          response.raise_for_status()
          payload = response.json()
          sentiment = payload["choices"][0]["message"]["content"].strip().lower()[:32]
          con.execute(
              """
              INSERT INTO docs_playground.flights_demo.ai_gateway_sentiment
              VALUES (current_timestamp, ?, ?, ?)
              """,
              [review_id, payload.get("model", model), sentiment],
          )

  print(f"scored {len(pending)} rows with {model}")

if __name__ == "__main__":
  main()
$flight$
);

SQL Editor loading...

Three details in that code are worth keeping when you adapt it:

No retry loop. A retried completion is a second billable request, and a Flight run that fails halfway has already written the rows it finished. Let the run fail and rerun it.
Bound the output. max_tokens caps what you pay for, and the slice on content caps what lands in the column.
Bind the values. Model output goes into INSERT as a parameter, never as an f-string.

Run and inspect it

The MD_* Flight table functions only accept literal parameters, not subqueries, so store the Flight ID in a SQL variable first. The next cells reuse it through getvariable:

Set the AI gateway Flight ID

SET VARIABLE ai_gateway_flight_id = (
  SELECT flight_id
  FROM MD_LIST_FLIGHTS()
  WHERE flight_name = 'docs_ai_gateway_chat'
  ORDER BY created_at DESC
  LIMIT 1
);

SQL Editor loading...

Attach the secret, then trigger a manual run:

Attach the provider secret

CALL MD_UPDATE_FLIGHT(
  flight_id := getvariable('ai_gateway_flight_id'),
  flight_secret_names := ['ai_gateway']
);

SQL Editor loading...

Run the AI gateway Flight

SELECT *
FROM MD_RUN_FLIGHT(
  flight_id := getvariable('ai_gateway_flight_id')
);

SQL Editor loading...

Runs are asynchronous. Poll until the latest run reaches a terminal status:

Check the run status

SELECT run_number, status, flight_version, created_at
FROM MD_LIST_FLIGHT_RUNS(
  flight_id := getvariable('ai_gateway_flight_id')
)
ORDER BY run_number DESC
LIMIT 5;

SQL Editor loading...

When the run succeeds, the labels are queryable alongside the text they came from:

Read the labeled reviews

SELECT
  s.review_id,
  s.sentiment,
  s.returned_model,
  r.body
FROM
  docs_playground.flights_demo.ai_gateway_sentiment s
  JOIN docs_playground.flights_demo.ai_gateway_reviews r USING (review_id)
ORDER BY
  s.review_id;

SQL Editor loading...

A second run of the same Flight sends no requests, because every row already has a label. Insert a new review and run it again to score only that row.

If the run fails, read the traceback with MD_GET_FLIGHT_LOGS. A 401 means the secret isn't attached or holds the wrong key; a 404 usually means the model name isn't one your account can reach.

Switch providers

Point the Flight at a different gateway by replacing the two config values. config is replaced on update rather than merged, so send both keys:

Point the Flight at Together AI

CALL MD_UPDATE_FLIGHT(
  flight_id := getvariable('ai_gateway_flight_id'),
  config := MAP {
      'AI_BASE_URL': 'https://api.together.ai/v1',
      'AI_MODEL': 'meta-llama/Llama-3.3-70B-Instruct-Turbo'
  }
);

SQL Editor loading...

Replace the secret's value with a key for the new provider, then update the Flight once so the change is redeployed:

CREATE OR REPLACE SECRET ai_gateway IN MOTHERDUCK (
    TYPE FLIGHTS,
    PARAMS MAP {'AI_API_KEY': '<your_provider_api_key>'}
);

Cloudflare needs one extra header for Workers AI models. Add the gateway ID to config as CF_AI_GATEWAY_ID and send it with the request:

headers = {"Authorization": f"Bearer {api_key}"}
gateway_id = os.environ.get("CF_AI_GATEWAY_ID")
if gateway_id:
    headers["cf-aig-gateway-id"] = gateway_id

warning

The Flight sends its API key to whatever host AI_BASE_URL names. Anyone who can update the Flight's config can therefore redirect that credential. On a shared or production Flight, hard-code the base URL in the source and keep only the model in config.

Schedule it

After a manual run succeeds, add a schedule. Schedule updates are metadata-only and don't create a new Flight version:

Score new rows every hour

CALL MD_UPDATE_FLIGHT(
  flight_id := getvariable('ai_gateway_flight_id'),
  schedule_cron := '0 * * * *'
);

SQL Editor loading...

Because the Flight skips rows it already scored, the hourly run is a no-op when nothing new arrived.

Adapt the pattern

Swap the system prompt and the result column for what you need: a summary, an extracted field, a category from a fixed list.
Ask for JSON in the system prompt and store the response as JSON, then read fields out with DuckDB's JSON functions instead of parsing in Python.
Add a LIMIT to the pending-rows query to cap spend per run. The next run picks up where this one stopped.
Batch several short rows into one request when per-request overhead dominates, and keep the row IDs in the prompt so you can map the answers back.
Record the response's usage object in a column to track token spend per run.
Move the prompt into config so you can change it for a single run with the config argument of MD_RUN_FLIGHT.

Model output is untrusted text. Grant access to the results table according to what the model was asked to generate, and don't interpolate a completion into SQL, shell, or HTML without escaping it.

Authentication, config, and secrets — how secrets become environment variables, and the precedence rules when names collide.
Monitoring and debugging — reading run logs when a request fails.
prompt — MotherDuck's built-in AI function, for when you'd rather stay in SQL than call a gateway.
Packages and recommended libraries
MD_CREATE_FLIGHT

Before you start​

Store the provider key as a Flight secret​

Create the source table​

Create the Flight​

Run and inspect it​

Switch providers​

Schedule it​

Adapt the pattern​

Related resources​

Before you start

Store the provider key as a Flight secret

Create the source table

Create the Flight

Run and inspect it

Switch providers

Schedule it

Adapt the pattern

Related resources