Motherduck Build Cfa App

Build a Customer-Facing Analytics App

Use this skill when the user is embedding analytics into a product for external users and needs a concrete serving architecture, not just a dashboard.

This is a use-case skill. It orchestrates motherduck-connect, motherduck-explore, motherduck-model-data, motherduck-query, and motherduck-load-data.

Start Here: Is a MotherDuck Server Active?

Always determine this first.

• If a remote MotherDuck MCP server or local MotherDuck server is active, use it.
• If the user has not said which database backs the project, ask for the target database or workspace before designing the app.
• Then inspect the live data model:
• databases and schemas
• tables and views
• columns and types
• join keys
• time dimensions
• core serving metrics
• Use that discovery to shape the serving pattern, tenant boundaries, and example code.

Do not jump straight to an architecture diagram if live data discovery is available.

If no server is active, ask for a representative schema excerpt or table list and keep assumptions explicit.

Use This Skill When

• The user needs embedded or product-facing analytics.
• Tenant isolation or blast radius matters.
• Read concurrency and latency matter.
• The project needs a backend contract, not just a Dive.
• The requirement is stronger than an internal dashboard or a read-only embed.

Default Serving Choices

• 3-tier CFA is the default:
• browser -> backend API -> MotherDuck
• Keep customer routing, connection selection, service-account usage, and embed-session creation on the backend.
• Embedded Dives are acceptable when:
• the requirement is read-only
• the product needs a live Dive surface shipped into an app
• app-side policy and UX control are limited
• a backend can create embed sessions and keep admin tokens server-side
• DuckDB-Wasm is acceptable only for small, browser-side, read-only workloads.
• Single shared tenant_id filtering is the fallback, not the recommendation.

Workflow

1. Confirm whether live MotherDuck discovery is available. 2. Explore the actual data model that will back the app. 3. Choose the serving pattern:

• 3-tier app
• embedded Dive
• browser-only prototype

4. Design the isolation model:

• per customer database
• per workload or service-account boundary

5. Define the API contract with allowlisted metrics, dimensions, filters, and customer boundaries. 6. Choose the connection path and read-scaling posture. 7. Produce the implementation plan, API contract, and rollout sequence.

When this skill produces a native DuckDB (md:) connection, watermark it with custom_user_agent=agent-skills/2.3.0(harness-<harness>;llm-<llm>). If metadata is missing, fall back to harness-unknown and llm-unknown.

Output

The output of this skill should be:

• a recommended serving architecture
• the isolation model
• the connection strategy
• the first implementation slice
• the validation and rollout plan

If the caller explicitly asks for structured JSON, return raw JSON only with no Markdown fences or prose before/after it. This is mainly for automated tests, regression checks, or downstream tooling that needs a stable machine-readable shape. Normal human-facing use of the skill can stay in prose unless JSON is explicitly requested.

Use this exact top-level shape when JSON is requested:

{
  "summary": {},
  "assumptions": [],
  "implementation_plan": [],
  "validation_plan": [],
  "risks": []
}

References

• references/CFA_IMPLEMENTATION_GUIDE.md -- preserved detailed implementation content that used to live in this skill
• references/CFA_ARCHITECTURE.md -- architecture comparison, isolation model, and connection-path detail

Runnable Artifact

• artifacts/customer_routing_example.py -- MotherDuck-backed Python example showing per-customer routing with separate database namespaces
• artifacts/customer_routing_example.ts -- TypeScript companion artifact with the same routing contract and output shape

Run it with:

uv run --with duckdb python skills/motherduck-build-cfa-app/artifacts/customer_routing_example.py

Run the same artifact against temporary MotherDuck databases:

MOTHERDUCK_ARTIFACT_USE_MOTHERDUCK=1 \
uv run --with duckdb python skills/motherduck-build-cfa-app/artifacts/customer_routing_example.py

Validate the TypeScript companion artifact:

uv run scripts/test_typescript_artifacts.py

Related Skills

• motherduck-connect -- choose the correct PG endpoint or native DuckDB path
• motherduck-explore -- inspect the live database and schema before choosing an architecture
• motherduck-model-data -- design analytics-ready per-customer tables
• motherduck-query -- validate serving queries and latency-sensitive aggregations
• motherduck-load-data -- build ingestion paths for customer-facing data refresh

CFA Architecture Reference

Detailed architecture patterns, complete code examples, and scaling playbook for building customer-facing analytics applications on MotherDuck.

Contents

• Choose the Connection Posture First
• 3-Tier Architecture Diagram
• Complete Python Backend Example (FastAPI + psycopg2)
• Node.js Backend Example (Express + pg)
• 1.5-Tier Architecture with DuckDB-Wasm
• Service Account Management
• Scaling Playbook
• Multi-Tenant Data Loading Patterns
• Connection Pooling
• Monitoring and Observability
• Troubleshooting

---

Choose the Connection Posture First

There are two valid backend shapes for customer-facing analytics on MotherDuck:

• Thin-client / backend API: the browser talks to your backend, and the backend talks to MotherDuck through the PG endpoint. This is the practical default for most product teams because it fits existing API stacks, auth middleware, and connection-pooling patterns.
• Native DuckDB backend: the backend already runs on duckdb or @duckdb/node-api, and uses MotherDuck through the native API. Use this when the service also needs local files, hybrid local/cloud execution, or direct DuckDB control.

This reference leads with the thin-client 3-tier pattern because it is the most common multi-tenant production shape. Keep the native backend path in play when the application is already DuckDB-native.

---

3-Tier Architecture Diagram

┌──────────┐     ┌──────────────┐     ┌─────────────────────────┐
│ Browser  │────>│ Backend API  │────>│ MotherDuck              │
│ (React/  │<────│ (FastAPI/    │<────│                         │
│  Vue/etc)│     │  Express)    │     │ Duckling A (customer_a) │
└──────────┘     │              │     │   Primary + Replica x4  │
                 │ 1. Auth      │     │                         │
                 │ 2. Route     │     │ Duckling B (customer_b) │
                 │ 3. Validate  │     │   Primary + Replica x4  │
                 │ 4. Execute   │     │                         │
                 └──────────────┘     │ Duckling C (customer_c) │
                                      │   Primary + Replica x8  │
                                      └─────────────────────────┘

Each Duckling is an isolated DuckDB instance. The primary handles writes; read replicas handle CFA query traffic via Read Scaling tokens. The backend authenticates each request, routes to the correct customer Duckling, validates the query, and returns results.

---

Complete Python Backend Example (FastAPI + psycopg2)

A production-ready backend that routes customer queries to their isolated MotherDuck databases.

"""
CFA Backend -- FastAPI + psycopg2
Routes authenticated customer requests to per-customer MotherDuck databases.

Install: pip install fastapi uvicorn psycopg2-binary certifi pyjwt
Run:     uvicorn cfa_backend:app --host 0.0.0.0 --port 8000
"""

import os
import json
from contextlib import contextmanager
from typing import Any

import certifi
import psycopg2
import psycopg2.extras
from fastapi import FastAPI, HTTPException, Depends, Header
from pydantic import BaseModel
import jwt

app = FastAPI(title="CFA Analytics API")

# --- Configuration ---

# Customer registry: maps customer_id to database and Read Scaling token.
# In production, load this from a secrets manager (AWS Secrets Manager, Vault).
CUSTOMER_REGISTRY: dict[str, dict[str, str]] = {
    "acme": {
        "database": "customer_acme",
        "read_token": os.environ.get("ACME_READ_TOKEN", ""),
        "write_token": os.environ.get("ACME_WRITE_TOKEN", ""),
    },
    "globex": {
        "database": "customer_globex",
        "read_token": os.environ.get("GLOBEX_READ_TOKEN", ""),
        "write_token": os.environ.get("GLOBEX_WRITE_TOKEN", ""),
    },
}

JWT_SECRET = os.environ.get("JWT_SECRET", "change-me-in-production")
MD_HOST = "pg.us-east-1-aws.motherduck.com"
MD_PORT = 5432

# --- Allowed query patterns ---
# In production, maintain an allowlist of query templates or use parameterized queries.
ALLOWED_PREFIXES = ("SELECT", "WITH", "FROM", "SUMMARIZE", "DESCRIBE")


# --- Auth ---

def get_customer_id(authorization: str = Header(...)) -> str:
    """Extract and validate customer_id from JWT token."""
    try:
        token = authorization.replace("Bearer ", "")
        payload = jwt.decode(token, JWT_SECRET, algorithms=["HS256"])
        customer_id = payload.get("customer_id")
        if customer_id not in CUSTOMER_REGISTRY:
            raise HTTPException(status_code=403, detail="Unknown customer")
        return customer_id
    except jwt.InvalidTokenError:
        raise HTTPException(status_code=401, detail="Invalid token")


# --- Database ---

@contextmanager
def get_connection(customer_id: str, write: bool = False):
    """Get a psycopg2 connection to the customer's MotherDuck database."""
    customer = CUSTOMER_REGISTRY[customer_id]
    token = customer["write_token"] if write else customer["read_token"]
    conn = psycopg2.connect(
        host=MD_HOST,
        port=MD_PORT,
        dbname=customer["database"],
        user="postgres",
        password=token,
        sslmode="verify-full",
        sslrootcert=certifi.where(),
    )
    try:
        yield conn
    finally:
        conn.close()


def validate_query(sql: str) -> None:
    """Reject queries that are not read-only SELECT statements."""
    normalized = sql.strip().upper()
    if not any(normalized.startswith(prefix) for prefix in ALLOWED_PREFIXES):
        raise HTTPException(
            status_code=400,
            detail="Only SELECT, WITH, FROM, SUMMARIZE, and DESCRIBE queries are allowed",
        )


# --- API ---

class QueryRequest(BaseModel):
    sql: str
    params: list[Any] | None = None


class QueryResponse(BaseModel):
    columns: list[str]
    rows: list[list[Any]]
    row_count: int


@app.post("/query", response_model=QueryResponse)
def run_query(
    request: QueryRequest,
    customer_id: str = Depends(get_customer_id),
):
    """Execute a read-only query against the customer's MotherDuck database."""
    validate_query(request.sql)

    with get_connection(customer_id) as conn:
        cur = conn.cursor()
        try:
            cur.execute(request.sql, request.params)
            columns = [desc[0] for desc in cur.description]
            rows = [list(row) for row in cur.fetchall()]
            return QueryResponse(columns=columns, rows=rows, row_count=len(rows))
        except psycopg2.Error as e:
            raise HTTPException(status_code=400, detail=str(e))
        finally:
            cur.close()


@app.get("/health")
def health():
    return {"status": "ok"}

Key design decisions in this example:

• Read Scaling tokens are used for the /query endpoint. Write tokens are reserved for data ingestion and data transformation.
• Query validation rejects non-SELECT statements. In production, use a more sophisticated allowlist or parameterized query templates.
• Connections are not pooled. For higher throughput, add connection pooling with psycopg2.pool.ThreadedConnectionPool or switch to psycopg (v3) with async support.
• JWT authentication maps each request to a customer_id. Replace with your product's auth system.

---

Node.js Backend Example (Express + pg)

The same pattern as the Python example, implemented in Node.js. Install: npm install express pg jsonwebtoken

import express from "express";
import pg from "pg";
import jwt from "jsonwebtoken";

const app = express();
app.use(express.json());

const JWT_SECRET = process.env.JWT_SECRET || "change-me-in-production";
const CUSTOMER_REGISTRY = {
  acme: {
    database: "customer_acme",
    readToken: process.env.ACME_READ_TOKEN || "",
  },
  globex: {
    database: "customer_globex",
    readToken: process.env.GLOBEX_READ_TOKEN || "",
  },
};

function authenticate(req, res, next) {
  try {
    const token = (req.headers.authorization || "").replace("Bearer ", "");
    const payload = jwt.verify(token, JWT_SECRET);
    if (!CUSTOMER_REGISTRY[payload.customer_id])
      return res.status(403).json({ error: "Unknown customer" });
    req.customerId = payload.customer_id;
    next();
  } catch {
    return res.status(401).json({ error: "Invalid token" });
  }
}

app.post("/query", authenticate, async (req, res) => {
  const { sql, params } = req.body;
  if (!sql) return res.status(400).json({ error: "Missing sql field" });

  const allowed = ["SELECT", "WITH", "FROM", "SUMMARIZE", "DESCRIBE"];
  if (!allowed.some((p) => sql.trim().toUpperCase().startsWith(p)))
    return res.status(400).json({ error: "Read-only queries only" });

  const customer = CUSTOMER_REGISTRY[req.customerId];
  const client = new pg.Client({
    host: "pg.us-east-1-aws.motherduck.com",
    port: 5432,
    user: "postgres",
    password: customer.readToken,
    database: customer.database,
    ssl: { rejectUnauthorized: true },
  });

  try {
    await client.connect();
    const result = await client.query(sql, params || []);
    res.json({
      columns: result.fields.map((f) => f.name),
      rows: result.rows,
      row_count: result.rowCount,
    });
  } catch (err) {
    res.status(400).json({ error: err.message });
  } finally {
    await client.end();
  }
});

app.listen(process.env.PORT || 8000);

For production, replace new pg.Client() with a per-customer pg.Pool for connection reuse.

---

1.5-Tier Architecture with DuckDB-Wasm

When to Use

Use the 1.5-tier pattern only when ALL of these conditions are true:

• Datasets are under 1GB per user.
• The use case is a read-only dashboard (no writes from the browser).
• You do not need strict, server-enforced data isolation.
• You accept that the token is visible in the browser (lower security).

How It Works

┌──────────────────────────────────────────────────┐
│                 BROWSER                           │
│                                                   │
│  ┌─────────────┐    ┌─────────────────────────┐  │
│  │ DuckDB-Wasm │───>│ MotherDuck (via md:)     │  │
│  │ (in-browser) │<───│ per-customer database    │  │
│  └─────────────┘    └─────────────────────────┘  │
│                                                   │
│  - Queries execute locally in the browser         │
│  - Data syncs from MotherDuck to Wasm instance    │
│  - Sub-millisecond latency for cached data        │
│  - No backend server needed                       │
└──────────────────────────────────────────────────┘

Limitations and Tradeoffs

Do not use 1.5-tier for production CFA with sensitive data. The token is visible in the browser, and there is no server-side query validation layer.

---

Service Account Management

Creating Service Accounts

1. Go to MotherDuck UI > Settings > Service Accounts. 2. Click Create Service Account. 3. Name the account descriptively: svc_<customer_slug> (e.g., svc_acme). 4. Assign the service account access to the customer's database. 5. Generate tokens for the service account.

Token Types and When to Use Each

Rule: use Read Scaling tokens for CFA query endpoints when the workload is concurrent and read-heavy. Read/Write tokens are for data pipelines that load data into customer databases and for other writer workflows.

Token Rotation Strategy

Set 90-day expiration on all tokens. At day 80, generate a new token and store it in the secrets manager. At day 85, verify the application uses the new token. At day 90, the old token expires. Automate this with your secrets manager's rotation feature. Revoke compromised tokens immediately -- do not wait for expiration.

---

Scaling Playbook

Phase 1: Launch (1-50 customers)

• One Duckling per customer. Each customer gets a separate database and service account.
• Start simple on reads. Add read scaling only when concurrency is real; the default pool size is 4 replicas and can be increased up to 16 as a soft limit.
• Single backend instance. One API server routes requests to customer databases.
• Monitor: Query latency (p50, p95, p99), error rates, connection counts.

Phase 2: Growth (50-500 customers)

• Increase read scaling capacity for high-traffic customers. Identify customers with the most concurrent users and scale their replica pools.
• Add connection pooling. Use psycopg2.pool.ThreadedConnectionPool (Python) or pg.Pool (Node.js) to reuse connections.
• Multiple backend instances behind a load balancer. Scale the API layer horizontally.
• Automate customer provisioning. Script database creation, service account setup, and token generation.
• Monitor: Per-customer query volume, replica utilization, connection pool saturation.

Phase 3: Scale (500+ customers)

• Scale read replicas for top-tier customers. The highest-traffic customers may need the documented soft limit or a higher limit coordinated with support.
• Tiered customer configs. Group customers by usage tier (free, pro, enterprise) with different replica counts and query rate limits.
• Per-customer rate limiting. Protect the system from runaway query volume by enforcing per-customer request limits.
• Dedicated backend pools. Route enterprise customers to dedicated backend instances for guaranteed capacity.
• Monitor: Per-customer cost, replica lag, query queue depth, overall system utilization.

Scaling Decision Matrix

---

Multi-Tenant Data Loading Patterns

Loading Data Per Customer

Each customer has its own database. Load data into the correct database using the customer's Read/Write token.

def load_customer_data(customer_id: str, data_path: str):
    """Load data into a customer's MotherDuck database."""
    customer = CUSTOMER_REGISTRY[customer_id]
    conn = psycopg2.connect(
        host="pg.us-east-1-aws.motherduck.com",
        port=5432,
        dbname=customer["database"],
        user="postgres",
        password=customer["write_token"],  # Use Write token for ingestion
        sslmode="verify-full",
        sslrootcert=certifi.where(),
    )
    try:
        cur = conn.cursor()
        # Rebuild the analytics table from fresh data
        cur.execute(f"""
            CREATE OR REPLACE TABLE "main"."analytics_events" AS
            SELECT * FROM read_parquet('{data_path}')
        """)
        conn.commit()
        # Create a snapshot so read replicas pick up the new data
        cur.execute("CREATE SNAPSHOT")
        conn.commit()
    finally:
        conn.close()

Scheduling Data Refreshes

Use a task scheduler (cron, Airflow, Dagster, Prefect) to refresh customer data on a cadence.

# Example: Airflow-style pseudocode for per-customer data refresh

def refresh_all_customers():
    """Refresh analytics data for every customer."""
    for customer_id, config in CUSTOMER_REGISTRY.items():
        data_path = f"s3://data-lake/{customer_id}/latest/*.parquet"
        load_customer_data(customer_id, data_path)
        print(f"Refreshed data for {customer_id}")

# Schedule: run daily at 02:00 UTC
# In Airflow: @daily with a PythonOperator
# In cron:    0 2 * * * python refresh_customers.py

Handling Schema Evolution Across Customers

When the analytics schema changes, apply the change to every customer database. Use idempotent DDL patterns.

-- Add a new column to every customer's analytics_events table.
-- Run this against each customer database.

ALTER TABLE "main"."analytics_events"
    ADD COLUMN IF NOT EXISTS session_id VARCHAR;

-- If the column requires backfilling:
UPDATE "main"."analytics_events"
    SET session_id = 'unknown'
    WHERE session_id IS NULL;

Automate schema migrations by iterating over all customer databases, connecting with each customer's Write token, and executing the migration SQL. Wrap each customer's migration in a try/except to continue on failure and log which customers succeeded or failed.

Data Loading Best Practices

• Use `CREATE OR REPLACE TABLE ... AS SELECT` for full refreshes. This is idempotent and atomic.
• Use Parquet format for source data. Parquet is columnar, compressed, and loads significantly faster than CSV.
• Load only needed columns. Select specific columns during load to reduce transfer and storage.
• Create a snapshot after loading. Run CREATE SNAPSHOT so read replicas pick up the new data promptly.
• Pre-aggregate during load. Build summary tables at load time rather than aggregating at query time. This keeps CFA query latency under 1 second.
• Use the `motherduck-load-data` skill patterns for format-specific options (CSV, JSON, Parquet, Delta Lake, Iceberg).

---

Connection Pooling

For production, use per-customer connection pools instead of creating a new connection per request.

Python: Use psycopg2.pool.ThreadedConnectionPool with minconn=2, maxconn=10 per customer. Store pools in a dictionary keyed by customer_id. Call pool.getconn() before each query and pool.putconn(conn) in a finally block.

Node.js: Use pg.Pool with max: 10, idleTimeoutMillis: 30000 per customer. Store pools in a Map keyed by customerId. Call pool.connect() to get a client and client.release() in a finally block.

In both cases, create the pool lazily on first request for each customer.

---

Monitoring and Observability

Key Metrics

Log every CFA query with customer_id, duration_ms, row_count, and error details. Use structured logging (JSON) so metrics can be aggregated per customer.

---

Troubleshooting

Connection refused or timeout

• Verify the host is pg.us-east-1-aws.motherduck.com and port is 5432.
• Confirm SSL is enabled (sslmode=verify-full).
• Check that the token is valid and not expired.
• Verify the database name is correct and the service account has access.

Query returns stale data after a write

• Read Scaling tokens route to replicas, which are eventually consistent.
• Run CREATE SNAPSHOT on the writer connection after the write completes.
• Run REFRESH DATABASE <db_name> on the reader connection to force a sync.

High query latency

• Check if the customer's data needs pre-aggregation. Build summary tables during data loading.
• Verify the query uses column selection (not SELECT *).
• Check replica count -- increase read replicas for high-concurrency customers.
• Use EXPLAIN to inspect the query plan and identify bottlenecks.

Connection pool exhaustion

• Increase the pool maxconn setting.
• Reduce query execution time by pre-aggregating data.
• Add per-customer rate limiting to prevent runaway query volume.
• Scale the backend horizontally with additional API instances.

<!-- Preserved detailed implementation guidance moved from SKILL.md so the main skill can stay concise. -->

Build a Customer-Facing Analytics App

Use this skill when embedding analytics directly into your product for external users -- customers, partners, or end users who need to query their own data through your application. Customer-facing analytics (CFA) requires sub-second query latency, high concurrency, strict per-customer data isolation, and predictable performance under load.

This is a use-case skill. It ties together motherduck-connect, motherduck-model-data, motherduck-query, motherduck-load-data, and motherduck-explore into a production architecture.

Contents

• Source Of Truth
• Verified Delivery Defaults
• Validation Signals
• Language Focus: TypeScript/Javascript and Python
• Prerequisites
• What Is Customer-Facing Analytics
• Choose an Architecture
• Step-by-Step: Build a 3-Tier CFA App
• Hypertenancy Explained
• Read Scaling Deep Dive
• Security
• Key Rules
• Common Mistakes
• Related Skills

Source Of Truth

• Prefer current MotherDuck docs for service accounts, connection paths, read scaling, and the Hypertenancy product guidance.
• If the MotherDuck MCP ask_docs_question feature is available, use it before falling back to public docs.
• Keep the CFA guidance aligned with the documented posture:
• structural isolation first
• dedicated compute or service-account boundaries where blast radius matters
• read scaling for truly concurrent read-heavy workloads
• native storage first unless an explicit DuckLake requirement exists

Verified Delivery Defaults

The repeated repo runs point to a stable CFA posture:

• start from the live MotherDuck workspace or target database before picking a serving pattern
• default to a 3-tier app with an API layer between the browser and MotherDuck
• default to structural isolation such as per-customer databases or service-account boundaries
• use native DuckDB md: connections when the backend needs direct MotherDuck control
• keep any PostgreSQL-compatible path as an integration tactic, not the primary CFA architecture

Validation Signals

Use these signals for testing, review, and regression checks. They are not an instruction to include a separate "Validation Signals" section in normal user-facing replies.

• run artifacts/customer_routing_example.py against temporary MotherDuck databases
• verify the result reports routing_mode as per-customer database namespace
• verify separate customer database names are present in the backend.databases payload
• treat any shared-database shortcut as a regression unless the task explicitly calls for it

Language Focus: TypeScript/Javascript and Python

• Prefer TypeScript/Javascript for:
• the backend API layer in Node.js
• Next.js, Express, or serverless app integration
• product-side auth, routing, and request shaping
• Prefer Python for:
• FastAPI backends
• analytics-heavy backend services
• provisioning or operational scripts around the app
• For customer-facing analytics, default to showing both when useful:
• TypeScript/Javascript for the product request path
• Python for operational or backend alternatives

Prerequisites

• MotherDuck connection established (see motherduck-connect skill)
• Data model designed (see motherduck-model-data skill)
• Familiarity with DuckDB SQL (see motherduck-query skill)

---

What Is Customer-Facing Analytics

CFA means your product exposes analytics capabilities to external users. Unlike internal BI dashboards, CFA has hard requirements:

• Sub-second latency. Users expect interactive speed. Queries returning in 2-5 seconds feel broken.
• High concurrency. Hundreds or thousands of users querying simultaneously.
• Per-customer data isolation. Customer A must never see Customer B's data. This is a security requirement, not a nice-to-have.
• Predictable performance. One customer's heavy query must not degrade another customer's experience.

MotherDuck's Hypertenancy architecture addresses all four requirements with per-customer or per-workload compute boundaries, dedicated ducklings, and read scaling when the serving workload is highly concurrent.

---

Choose an Architecture

Use the 3-tier architecture for production CFA. The other options exist for specific, narrower use cases.

Production CFA (recommended):
  Browser ──> Backend API ──> MotherDuck (per-customer databases)

Lightweight dashboards only (<1GB per user):
  Browser (DuckDB-Wasm) ──> MotherDuck

Simple multi-tenant (weak isolation, low security):
  Browser ──> Backend API ──> MotherDuck (single database, tenant_id filtering)

3-Tier Architecture (Default for Production)

┌──────────┐     ┌──────────────┐     ┌─────────────────┐
│ Browser  │────>│ Backend API  │────>│  MotherDuck     │
│ (React/  │<────│ (FastAPI/    │<────│  (per-customer  │
│  Vue/etc)│     │  Express)    │     │   databases)    │
└──────────┘     └──────────────┘     └─────────────────┘

• Per-customer service accounts and databases provide strong data isolation.
• Backend handles authentication, authorization, and query routing.
• Add Read Scaling tokens for high-concurrency read workloads.
• Tokens never leave the backend. The browser talks only to your API.

1.5-Tier Architecture (DuckDB-Wasm)

Use only when datasets are under 1GB per user and the use case is a lightweight, read-only dashboard. The browser runs DuckDB-Wasm and connects directly to MotherDuck. No backend needed, but data isolation is harder to enforce and datasets must be small enough for browser-side execution.

Embedded Dives

Embedded Dives sit between a standalone Dive and a full CFA app:

• good for read-only live Dives inside an existing site or product
• backend still creates the embed session
• browser receives only the short-lived session string
• not a substitute for a full app backend when you need customer-specific routing, richer write paths, or tighter policy enforcement
• server mode runs through the Postgres endpoint and is the default embed query mode
• dual mode adds browser-side DuckDB-Wasm behavior and requires cross-origin isolation headers

If the requirement is "show a live MotherDuck dashboard inside our product," this can be enough. If the requirement is "serve each customer through our own application contract and backend controls," stay with the 3-tier CFA architecture.

Single Service Account (Weak Isolation)

One service account, one database, data filtered by tenant_id in every query. Less secure because a bug in query construction can leak data across tenants. Use only for internal tools or low-sensitivity analytics where simplicity outweighs isolation.

For anything customer-facing, use the 3-tier architecture. The rest of this skill assumes the 3-tier pattern.

---

Step-by-Step: Build a 3-Tier CFA App

Step 1: Design Per-Customer Schema

Create one database per customer. This is the strongest isolation model -- each customer's data lives in a completely separate namespace with its own compute resources.

Use the motherduck-model-data skill for schema design within each customer database.

-- Create a database for each customer
CREATE DATABASE customer_acme;
CREATE DATABASE customer_globex;

-- Create analytics tables in each customer database
CREATE TABLE "customer_acme"."main"."analytics_events" (
    event_id VARCHAR NOT NULL,
    event_type VARCHAR NOT NULL,
    event_timestamp TIMESTAMP NOT NULL,
    user_id VARCHAR NOT NULL,
    properties JSON,
    created_at TIMESTAMP DEFAULT current_timestamp
);
COMMENT ON TABLE "customer_acme"."main"."analytics_events"
    IS 'Raw analytics events for customer Acme Corp';

-- Repeat the same schema for each customer
CREATE TABLE "customer_globex"."main"."analytics_events" (
    event_id VARCHAR NOT NULL,
    event_type VARCHAR NOT NULL,
    event_timestamp TIMESTAMP NOT NULL,
    user_id VARCHAR NOT NULL,
    properties JSON,
    created_at TIMESTAMP DEFAULT current_timestamp
);

Use a consistent naming convention: customer_<slug> for database names. This makes routing straightforward.

Step 2: Create Service Accounts

Create service accounts per customer or per workload boundary when isolation, sizing, or revocation blast radius matters. Service accounts can be created in the MotherDuck UI or programmatically via the Admin API.

1. Go to MotherDuck UI > Settings > Service Accounts. 2. Create a service account for each customer (e.g., svc_acme, svc_globex). 3. Generate a Read Scaling token for each service account only when the CFA workload is read-heavy and concurrent. 4. Generate a Read/Write token only for accounts that need write access (data ingestion). 5. Store all tokens in a secrets manager (AWS Secrets Manager, HashiCorp Vault, Doppler). Never store tokens in application config files or environment variables on shared machines.

Step 3: Connect the Backend to MotherDuck

Use the motherduck-connect skill patterns. Each incoming customer request routes to that customer's database using their dedicated token. Choose the connection approach that fits your backend.

Option A: Native DuckDB (recommended for Python backends)

Native DuckDB gives full SQL support, cross-database queries, and no driver translation. Use this for FastAPI, Flask, or any Python service.

# Python backend example (FastAPI + duckdb)
import duckdb

CFA_USER_AGENT = "agent-skills/2.3.0(harness-<harness>;llm-<llm>)"

def get_customer_connection(customer_db: str, customer_token: str):
    """Create a native DuckDB connection to a customer's MotherDuck database."""
    return duckdb.connect(
        f"md:{customer_db}?motherduck_token={customer_token}"
        f"&custom_user_agent={CFA_USER_AGENT}"
    )

Install: pip install duckdb

Option B: PG Endpoint (for existing PostgreSQL stacks)

Use the PG endpoint when your backend already has PostgreSQL drivers, connection pooling, or runs in a serverless environment where installing native DuckDB is impractical.

// TypeScript backend example (Express + pg)
import pg from "pg";

function getCustomerPool(database: string, token: string) {
  return new pg.Pool({
    host: "pg.us-east-1-aws.motherduck.com",
    port: 5432,
    database,
    user: "postgres",
    password: token,
    ssl: { rejectUnauthorized: true },
  });
}

# Python backend example (FastAPI + psycopg2)
import psycopg2
import certifi

def get_customer_connection(customer_db: str, customer_token: str):
    """Create a PG endpoint connection to a customer's MotherDuck database."""
    return psycopg2.connect(
        host="pg.us-east-1-aws.motherduck.com",
        port=5432,
        dbname=customer_db,
        user="postgres",
        password=customer_token,
        sslmode="verify-full",
        sslrootcert=certifi.where()
    )

Install: pip install psycopg2-binary certifi

Step 4: Implement Query Routing

Map each authenticated customer to their database name and token. Execute queries against the correct customer database and return results to the frontend.

# Customer registry -- in production, load from secrets manager
CUSTOMER_REGISTRY = {
    "acme": {
        "database": "customer_acme",
        "token": os.environ["ACME_MD_TOKEN"],
    },
    "globex": {
        "database": "customer_globex",
        "token": os.environ["GLOBEX_MD_TOKEN"],
    },
}

def execute_customer_query(customer_id: str, query: str):
    """Route a query to the correct customer database."""
    customer = CUSTOMER_REGISTRY[customer_id]
    conn = get_customer_connection(customer["database"], customer["token"])
    try:
        cur = conn.cursor()
        cur.execute(query)
        columns = [desc[0] for desc in cur.description]
        rows = cur.fetchall()
        return {"columns": columns, "rows": rows}
    finally:
        conn.close()

const CUSTOMER_REGISTRY = {
  acme: { database: "customer_acme", token: process.env.ACME_MD_TOKEN! },
  globex: { database: "customer_globex", token: process.env.GLOBEX_MD_TOKEN! },
};

async function executeCustomerQuery(customerId: keyof typeof CUSTOMER_REGISTRY, sql: string, values: unknown[] = []) {
  const customer = CUSTOMER_REGISTRY[customerId];
  const pool = getCustomerPool(customer.database, customer.token);
  const result = await pool.query(sql, values);
  await pool.end();
  return result.rows;
}

Validate and sanitize all queries before execution. Never pass raw user input directly to cur.execute(). Use parameterized queries or an allowlist of permitted query templates.

Step 5: Set Up Read Scaling

Enable read scaling for each customer's service account when concurrent read workloads justify it.

• Default pool size: read scaling starts with a default pool size of 4 replicas and can be increased up to 16 as a soft limit.
• Use Read Scaling tokens to distribute load across replicas automatically.
• Read Scaling tokens are read-only. Write operations require a Read/Write token.
• Use `session_hint` on native DuckDB connections so repeated requests from the same end user land on the same replica when possible.

Use Read Scaling tokens for concurrent CFA read paths. Reserve Read/Write tokens for backend data loading processes, schema changes, and other writer workflows.

Step 6: Handle Consistency

Read replicas are eventually consistent. There is typically a lag between a write and its visibility on replicas. For most CFA workloads this is acceptable -- analytics data is inherently slightly behind real-time.

When you need strict consistency after a write (e.g., after a data load completes and a customer should see the new data immediately):

-- On the writer connection (Read/Write token):
-- After loading new data, create a snapshot
CREATE SNAPSHOT;

-- On the reader side, refresh to pick up the snapshot:
REFRESH DATABASE customer_acme;

Use this pattern sparingly. For most CFA use cases, eventual consistency with a few minutes of delay is sufficient and performs better.

---

Hypertenancy Explained

Hypertenancy is MotherDuck's multi-tenant architecture. It provides stronger isolation than traditional shared-database multi-tenancy.

• Each customer gets a dedicated DuckDB instance ("Duckling"). Customer workloads run on separate compute. One customer's expensive query cannot slow down another customer.
• No resource contention. CPU, memory, and I/O are isolated per customer. Performance is predictable regardless of how many tenants exist.
• Independent scaling. High-traffic customers can get more compute or read replicas without affecting other customers' configurations.
• Database-level isolation. Each customer's data lives in a separate database. There is no shared table with a tenant_id column -- the isolation is structural, not query-dependent.

This model eliminates the "noisy neighbor" problem that plagues shared-database multi-tenant architectures.

---

Read Scaling Deep Dive

Read scaling distributes read queries across multiple replicas of a customer's Duckling instance.

• Default pool size is 4 replicas and can be increased up to 16 as a soft limit.
• Eventually consistent. Replicas sync from the primary within a few minutes. This delay is acceptable for analytics workloads.
• Automatic load distribution. When using a Read Scaling token, MotherDuck routes queries across available replicas automatically.
• Session affinity matters. When using native DuckDB connections, pass a stable session_hint so the same user stays on the same replica when possible.
• No query rewrite is required. The main change is token type and connection configuration, not a new SQL dialect.

When to Use CREATE SNAPSHOT and REFRESH DATABASE

---

Security

Per-customer databases are the foundation of CFA security. Follow these rules without exception.

• Per-customer databases eliminate cross-tenant data leakage by design. There is no query that can accidentally return another customer's data because the data is in a different database entirely.
• Use service accounts with minimum permissions. CFA query endpoints need Read Scaling tokens only. Do not use Read/Write tokens for serving queries.
• Never expose MotherDuck tokens to the frontend. Tokens stay in the backend. The browser communicates with your API, which holds the tokens server-side.
• Validate all queries before execution. Even with per-customer isolation, validate that incoming queries are well-formed and within allowed patterns. Use parameterized queries or an allowlist of query templates.
• Rotate tokens on a regular cadence. Set expiration dates on all service tokens and rotate them every 90 days or sooner.
• Revoke tokens immediately if compromised. Use the MotherDuck UI to revoke tokens. Generate new tokens and update your secrets manager.

---

Key Rules

• Use the 3-tier architecture for production CFA. Backend API between browser and MotherDuck. No exceptions for customer-facing products.
• One database per customer for isolation. This is a security requirement. Do not use a single database with tenant_id filtering for CFA.
• Pick the connection path by backend shape. Native DuckDB (md:) when the backend needs direct MotherDuck control; the PG endpoint when the stack already runs PostgreSQL drivers or installing DuckDB is impractical.
• Use Read Scaling tokens for concurrent reads. Reserve Read/Write tokens for data ingestion only.
• Keep serving tables lean and pre-aggregated. Do not push raw multi-billion-row scans through end-user request paths if a curated serving table can answer the question.
• Never expose service tokens to the frontend. Tokens live in the backend. The browser never sees them.
• Write DuckDB SQL, not PostgreSQL SQL. Even when connecting via the PG endpoint. See motherduck-duckdb-sql skill.
• Pre-aggregate data for dashboard queries. Use materialized summary tables (see motherduck-model-data skill) to keep query latency under 1 second.

---

Common Mistakes

Using a single database with tenant_id filtering

Wrong approach: one shared database where every query includes WHERE tenant_id = :customer_id. A single missing filter clause leaks data across tenants. This is a security vulnerability, not a design tradeoff.

Right approach: one database per customer. Data isolation is structural and cannot be bypassed by a query bug.

Exposing MotherDuck tokens to the frontend

Wrong approach: sending the MotherDuck token to the browser so it can query directly.

Right approach: the backend holds all tokens. The browser sends requests to your API, which executes queries server-side and returns results.

Not enabling read scaling before launch

If you launch with Read/Write tokens serving all CFA queries, you have no concurrency scaling. Add read scaling before launch if the expected traffic is genuinely concurrent and read-heavy; otherwise keep the simpler path until the workload proves it is needed.

Using Read/Write tokens for read-heavy workloads

Read/Write tokens route to the primary instance. Read Scaling tokens distribute load across replicas. Using the wrong token type means all read traffic hits a single instance.

Assuming strong consistency with read replicas

Read replicas are eventually consistent. If your application writes data and immediately queries for it via a Read Scaling token, the write may not be visible yet. Use CREATE SNAPSHOT + REFRESH DATABASE when strict consistency is required after a write. When using native DuckDB connections, pair this with a stable session_hint.

Skipping query validation

Even with per-customer database isolation, validate all incoming queries. Malformed or excessively expensive queries can consume resources. Use parameterized queries, query templates, or an allowlist to control what the CFA endpoint can execute.

---

Related Skills

• motherduck-connect -- Establish a MotherDuck connection and authenticate via PG endpoint or native API
• motherduck-model-data -- Design per-customer schemas and denormalized analytical tables
• motherduck-query -- Execute DuckDB SQL queries, CTEs, and performance optimization
• motherduck-explore -- Discover databases, tables, columns, and data shares
• motherduck-load-data -- Ingest data from files, APIs, and cloud storage into customer databases