Build OpenAI Sites with live MotherDuck data

Build a dashboard with OpenAI Sites. Server-side code queries MotherDuck and returns results to the browser, keeping the token and SQL private.

Prerequisites

• A ChatGPT plan that includes Sites. See OpenAI's Sites documentation for availability and limits.
• A MotherDuck account with access to the data the Site needs.
• Permission to create a service account for the application.

Sites supports HTTPS but not raw TCP connections. Use the hosted MotherDuck MCP server rather than connecting directly to the MotherDuck Postgres endpoint.

Connect the site to MotherDuck

1. Create a service account and give it access to the intended databases.
2. Bootstrap the account before its first read scaling connection. Connect once as the service account with a read/write token, or impersonate the service account in the MotherDuck UI. Read scaling connections can't attach databases, so attach everything the Site reads during this session.
3. Verify in that same session that the account can query sample_data.nyc.taxi. Follow the sample datasets guide to restore the region-specific share if it's missing.
4. Create an expiring read scaling token for the Site if your plan supports it. Otherwise, use a read/write token with the service account's permissions limited to the application's needs.
5. Build the Site with the prompt below. Add MOTHERDUCK_READ_TOKEN as a secret in Sites → More actions → Settings or through your secret manager. Don't paste it into chat or source code.

Build the dashboard

Ask ChatGPT or Codex:

Build a private NYC taxi dashboard with OpenAI Sites.

Run all MotherDuck access in server-side routes using an MCP client.
Connect to https://api.motherduck.com/mcp and read the token from the
MOTHERDUCK_READ_TOKEN server environment variable.

Use the read-only query tool. Query sample_data.nyc.taxi when the page opens
and when the user selects Refresh. Return current_timestamp from MotherDuck,
the trip count, average total amount, and average trip distance.
Show loading, empty, and error states. Do not poll while the page is idle.
Keep the token and SQL out of browser code.

Add the server-side query helper

Install these dependencies in the Site project:

npm install @modelcontextprotocol/sdk@1.30.0 zod@4

The MCP SDK handles initialization, protocol negotiation, and response formats. This helper runs a fixed query and closes the client in finally.

import { Client } from '@modelcontextprotocol/sdk/client/index.js';
import { StreamableHTTPClientTransport } from '@modelcontextprotocol/sdk/client/streamableHttp.js';
import { z } from 'zod';

const queryData = z.object({
  success: z.literal(true),
  columns: z.array(z.string()),
  rows: z.array(z.array(z.unknown())),
});

export async function queryTaxiMetrics(token: string) {
  if (!token) throw new Error('MotherDuck token is not configured');

  const client = new Client({ name: 'taxi-dashboard', version: '1.0.0' });
  const transport = new StreamableHTTPClientTransport(
    new URL('https://api.motherduck.com/mcp'),
    { requestInit: { headers: { Authorization: `Bearer ${token}` } } },
  );

  try {
    await client.connect(transport, { timeout: 30_000 });
    const result = await client.callTool(
      {
        name: 'query',
        arguments: {
          database: 'sample_data',
          sql: `
            SELECT
              current_timestamp AS queried_at,
              count(*) AS trips,
              round(avg(total_amount), 2) AS average_total,
              round(avg(trip_distance), 2) AS average_distance
            FROM nyc.taxi
          `,
        },
      },
      undefined,
      { timeout: 60_000 },
    );

    if (result.isError) throw new Error('MotherDuck query failed');
    const data = queryData.parse(result.structuredContent);
    return data.rows.map((row) => {
      if (row.length !== data.columns.length) {
        throw new Error('Unexpected MotherDuck row shape');
      }
      return Object.fromEntries(
        data.columns.map((column, index) => [column, row[index]]),
      );
    });
  } finally {
    await client.close();
  }
}

After authorizing the viewer, call queryTaxiMetrics from a server route with the environment token. Return JSON with Cache-Control: no-store. Catch errors and return a generic message, never raw errors or credentials.

The SDK throws on HTTP and JSON-RPC errors. The helper checks tool errors and validates the response. Counts may remain strings. Convert them only within JavaScript's safe integer range.

Deploy and verify

1. Save a version and deploy it privately.
2. Open the deployment and complete ChatGPT sign-in.
3. Select Refresh and confirm that the query timestamp changes.
4. Close and reopen the page. Confirm that it runs a new query.
5. Test loading, empty, authorization, and query-error states.

Set runtime variables before deploying and redeploy after changing them. See Sites environment configuration.

Run these checks on the deployed Site, not only locally. Every Sites deployment URL is a production deployment.

Share the site

Choose the audience in Sites sharing settings. All viewers use the Site's MotherDuck token. ChatGPT sign-in controls Site access, not per-row database permissions.

For per-viewer data access, derive account or tenant scope from trusted server-side identity and apply it to every query. Test authorized and unauthorized viewers before sharing.

Add writes only when needed

Store a separate read/write token as MOTHERDUCK_WRITE_TOKEN. A read scaling token can't write. Use a dedicated database or table for application writes and expose fixed server actions, such as Add comment or Mark reviewed.

For each action:

1. Authenticate the viewer and check their permission to change the target row.
2. Validate inputs, including identifiers, text lengths, and allowed status transitions. Use parameter binding where supported. Otherwise, use a reviewed SQL literal encoder and fixed identifiers.
3. Run a fixed query_rw statement restricted to the authorized rows. Don't accept SQL from the browser.
4. Return the affected row identifier and confirm the expected number of affected rows.
5. Update the page from the write result or a read through the writer connection.

Confirm writes with business impact. Use idempotency keys to prevent duplicate writes on retries and cross-site request protection for cookie-authenticated writes.

Test an insert and a targeted update in a test database. Verify the results from another MotherDuck client and confirm that invalid or unauthorized requests leave the data unchanged.

Choose read scaling and refresh behavior

Read scaling distributes concurrent connections across a pool of Ducklings. Use it when concurrent readers cause queueing. It doesn't make an individual query faster by itself.

Read scaling replicas are eventually consistent. For immediate feedback after a write, return the affected row with RETURNING or read through the writer connection. Separate MCP requests may reach different replicas, so don't assume a refresh request and a later query use the same reader connection.

Use aggregate queries, bounded date ranges, and an explicit Refresh control. Without polling, leaving the page open doesn't trigger additional queries.

A completed request doesn't mean the Duckling has stopped. The MCP server can reuse database instances, and other clients can use the same account. Check query history, the Duckling overview, and the cooldown configuration to inspect compute activity.

Related guides

• Claude artifacts: Build a tool inside Claude using each viewer's connector.
• MotherDuck Dives: Keep the visualization in MotherDuck with versioning, sharing, and embedding.