# Build Flights and a Dive with an AI agent
> Use the MotherDuck MCP and an AI coding agent to build two daily Flights — a Postgres ingest and a Hacker News API ingest — plus a Dive, from a single prompt.
<!-- cspell:ignore Algolia backfills backfill -->

In this guide, you'll do a little setup (a secret and the MCP connection), send one prompt, and watch an AI agent build three things:

- A **data pipeline ([Flight](/key-tasks/flights/))** that copies your Postgres database into MotherDuck every day.
- A second **data pipeline ([Flight](/key-tasks/flights/))** that pulls an outside signal from a public API (Hacker News) every day.
- A **data visualization ([Dive](/key-tasks/ai-and-motherduck/dives/))** that reads both and shows the daily picture.

Then you'll walk through each piece the agent built.

You run a small e-commerce shop. Your orders live in Postgres, and you want a daily picture of how the business is doing — mixed with an outside signal: what Hacker News is saying about the tools you care about. This guide builds that end to end with an AI coding agent and the [MotherDuck MCP Server](/key-tasks/ai-and-motherduck/mcp-setup/): two scheduled [Flights](/key-tasks/flights/) (one from a database, one from an API) and a [Dive](/key-tasks/ai-and-motherduck/dives/) over the result — all from a single prompt. The agent writes the Python, deploys and schedules both Flights, and iterates until the runs succeed.

You can watch a video walkthrough if you prefer:

## What you'll build

![Two daily Flights — a Postgres e-commerce mirror and a Hacker News signal — write to your data, and a Dive queries it live. All built and scheduled by a coding agent through the MotherDuck MCP.](./img/high_level_design.png)

## Before you start

You need:

- A [MotherDuck account](https://app.motherduck.com/)
- An AI coding agent connected to the [MotherDuck MCP Server](/key-tasks/ai-and-motherduck/mcp-setup/) — this is what lets the agent inspect your databases, create Flights, run them, and read the logs back (the video uses Claude Code, but Codex, Claude in the browser, and others work the same way)
- A Postgres database to ingest from, with its connection string stored as a Flight secret — see [Set up the Postgres connection](#set-up-the-postgres-connection) below

The Hacker News side is a public API, so there is nothing to set up there.

## Set up the Postgres connection

The agent never sees your Postgres credentials. You store the connection string once as a Flight secret, and the prompt refers to it by name.

### Get a Postgres to ingest from

The walkthrough mirrors `multishop_commerce`, a synthetic e-commerce dataset with `shops`, `categories`, `products`, `customers`, `orders`, and `order_items`. To follow along with the same data, use the [`postgres-vs-motherduck` MotherDuck Labs project](https://github.com/motherduckdb/labs/tree/main/projects/postgres-vs-motherduck): its `pipeline/seed_postgres.py` loads the public `multishop_commerce` share into a throwaway Postgres, giving you a database with that schema to mirror back into MotherDuck. Any Postgres with a few related tables works just as well, so you can point this at your own database instead.

### Store the connection string safely

Treat the connection string as a credential:

- **Connect with a read-only role.** The Flight only reads from Postgres, so use a role that has `SELECT` on the tables you mirror and nothing more.
- **Require TLS.** Add `?sslmode=require` so the connection is encrypted in transit: `postgresql://<user>:<password>@<host>:5432/<database>?sslmode=require`.
- **Keep it in a Flight secret, never in the prompt.** A Flight secret is encrypted at rest and referenced by name, so the value never appears in the prompt, the Flight source, or the Flight's metadata.

Create the secret in the MotherDuck UI (**Settings → Secrets**, **Flight** type, name it `e-commerce-insights`, and add the connection string), or with SQL:

```sql
CREATE SECRET "e-commerce-insights" IN MOTHERDUCK (
    TYPE FLIGHTS,
    PARAMS MAP {
        'POSTGRES_URL': 'postgresql://<user>:<password>@<host>:5432/<database>?sslmode=require'
    }
);
```

At run time the Flight reads it from the environment variable `e-commerce-insights_POSTGRES_URL` — MotherDuck joins the secret name and key. For a scheduled Flight, run it under a service account token rather than your personal one. See [Authentication, config, and secrets](/key-tasks/flights/flights-authentication-config-and-secrets) for the full mechanics.

## The prompt

Everything below is driven by one prompt. Each highlighted part tells the agent something specific — what to build, where the secret lives, how far back to backfill, and when to consider the job done.

<AnnotatedPrompt
  title="Two Flights and a Dive, from one prompt"
  categories={{
    architecture: { label: 'Architecture', color: 'sky' },
    ingestion: { label: 'Ingestion', color: 'garden' },
    schedule: { label: 'Schedule', color: 'sun' },
    secret: { label: 'Secret', color: 'ink' },
    bestpractice: { label: 'Best practice', color: 'garden-dark' },
    backfill: { label: 'Backfill', color: 'sky-dark' },
    guardrail: { label: 'Guardrail', color: 'watermelon' },
  }}
  segments={[
    { text: 'Use the MotherDuck MCP to set up ' },
    { text: 'two daily flights plus a Dive', category: 'architecture' },
    { text: ' for visualization of the results. ' },
    { text: 'Ingest my Postgres e-commerce data — orders and related tables — into MotherDuck', category: 'ingestion' },
    { text: ' and ' },
    { text: 'refresh it daily', category: 'schedule' },
    { text: '. The ' },
    { text: 'connection string is already stored as a Flight secret named e-commerce-insights', category: 'secret' },
    { text: '. Follow the ' },
    { text: 'cookbook in the docs for production-ready pipeline patterns', category: 'bestpractice' },
    { text: '. For the other flight, ' },
    { text: 'each day', category: 'schedule' },
    { text: ' ' },
    { text: 'pull what Hacker News is saying about DuckDB and MotherDuck — the stories and the comments', category: 'ingestion' },
    { text: '. ' },
    { text: 'Backfill over the past two weeks.', category: 'backfill' },
    { text: ' ' },
    { text: 'Run both flights and make sure they succeed.', category: 'guardrail' },
  ]}
/>

The cookbook hint matters: the docs ship many [Flight recipes](/cookbook/), so the agent picks up the established patterns for ingest, scheduling, and error handling instead of inventing its own. The guardrail at the end is what turns this into a hands-off build — the agent runs each Flight, reads the logs through the MCP, and fixes its own code if the first run fails.

## Ingest Postgres e-commerce data

The first Flight mirrors the Postgres tables into MotherDuck. The agent reads the connection string from the `e-commerce-insights` secret at run time, pulls `orders` and the related tables, and writes them into a database in your account. It deploys the Flight and sets a daily schedule without you touching SQL.

The first run can fail — and that is fine. The agent reads the run log through the MCP, corrects the code, and runs again until it succeeds. In the run history below, the first attempt failed and the next attempt succeeded, mirroring tens of millions of rows in about a minute. The database explorer on the left shows every table from Postgres loaded with the expected row counts, and you can inspect the generated source, `requirements.txt`, and schedule right there.

![The Postgres e-commerce sync Flight in the MotherDuck UI — a failed first run followed by a successful run, the generated source and requirements, a daily schedule, and the mirrored tables in the database explorer.](./img/inspect_postgres_ecommerce_sync_pipeline.png)

For the hand-written version of this pattern — a full-refresh Postgres mirror through the DuckDB Postgres extension — see the [Postgres ingest cookbook recipe](/cookbook/flight-postgres-ingest).

## Pull the Hacker News signal

The second Flight pulls the outside signal. The agent figures out from the public Hacker News API (Algolia search) what to look for, queries for stories and comments mentioning DuckDB and MotherDuck, backfills the last two weeks on the first run, and stores the results in their own database. It uses a common library like `requests` for the API calls.

This Flight is small and fast — the run completes in a few seconds. The log shows it fetching mentions over the backfill window and reporting how many genuine, unique mentions it inserted.

![The Hacker News mentions Flight in the MotherDuck UI — a successful run in a few seconds, the API ingest source, a daily schedule, and the log reporting inserted mentions.](./img/flight_hacker_news_logs_inspect.png)

For a related SQL-driven take that ingests tech feeds and summarizes them with `prompt()`, see [Build a daily briefing Flight and Dive](/key-tasks/flights/build-daily-briefing-flight-and-dive).

## Visualize with a Dive

With both Flights producing fresh tables daily, the agent builds a [Dive](/key-tasks/ai-and-motherduck/dives/) over the result. The prompt did not specify which metrics to show, so the agent inspects the schema and picks the KPIs that fit: the e-commerce side surfaces revenue, gross merchandise value, customers, and shops, while the Hacker News side surfaces mention counts, top stories, and mentions over time. The Dive queries your data live, so it reflects the latest run each day.

![The generated Dive — an e-commerce panel with revenue, GMV, customers, and shops, plus a Hacker News buzz panel with mentions, stories, comments, and top stories.](./img/dive_exploration_flight_demo.png)

This first result comes from a deliberately vague prompt. In practice you can be specific about the metrics you want, or iterate on the Dive after seeing the first version. To version and deploy Dives like the rest of your codebase, see [Managing Dives as Code](/key-tasks/ai-and-motherduck/dives/managing-dives-as-code).

## Related resources

- [Running Python with Flights](/key-tasks/flights/) — the Flights concept and the SQL and MCP control surface
- [Connect to MCP Server](/key-tasks/ai-and-motherduck/mcp-setup/) — set up the MCP Server with your AI assistant
- [Authentication, config, and secrets](/key-tasks/flights/flights-authentication-config-and-secrets) — how Flights read secrets at run time
- [Postgres ingest cookbook recipe](/cookbook/flight-postgres-ingest) — the hand-written Postgres mirror pattern
- [Build a daily briefing Flight and Dive](/key-tasks/flights/build-daily-briefing-flight-and-dive) — a SQL-driven Flight + Dive pattern
- [Creating Visualizations with Dives](/key-tasks/ai-and-motherduck/dives/) — build Dives from natural language
- [Managing Dives as Code](/key-tasks/ai-and-motherduck/dives/managing-dives-as-code) — version and deploy Dives with Git and CI/CD


---

## Docs feedback

MotherDuck accepts optional user-submitted feedback about this page at `POST https://motherduck.com/docs/api/feedback/agent`.
For agents and automated tools, feedback submission should be user-confirmed before sending.

Payload:

```json
{
  "page_path": "/key-tasks/flights/build-flights-and-a-dive-with-an-ai-agent/",
  "page_title": "Build Flights and a Dive with an AI agent",
  "text": "<the user's feedback, max 2000 characters>",
  "source": "<optional identifier for your interface, for example 'claude.ai' or 'chatgpt'>"
}
```

`page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": "<uuid>"}`, `400` for malformed payloads, and `429` when rate-limited.