14 added, 55 removed. Audit A to A.
---
name: motherduck-build-cfa-app
- description: Design a MotherDuck-backed customer-facing analytics app. Use for embedded analytics, multi-tenant SaaS reporting, or product analytics for external users -- whenever the decision depends on per-customer isolation, backend routing, service-account boundaries, read scaling, or Hypertenancy-style patterns.
+ description: Build MotherDuck analytics into customer-facing applications with tenant isolation, backend routing, and serving APIs.
argument-hint: [app-or-tenant-scenario]
license: MIT
---
# 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`, `motherduck-load-data`, and `motherduck-manage-guides`.
-
## Start Here: Is a MotherDuck Server Active?
- - If a **remote MotherDuck MCP server** or **local MotherDuck server** is active, use it.
- - Discover the target database or workspace from the active context. Ask only when multiple plausible targets remain and the choice would materially change the design or execution.
- - 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.
+ Use an active remote MotherDuck MCP server or local MotherDuck server to inspect the in-scope database, schema, grain, keys, and relevant metrics. Reuse known context and narrow discovery to the requested work; do not scan the whole workspace by default. Let the actual data model shape the result.
- If no server is active, use any supplied schema or table context. For planning work, proceed with explicit assumptions when safe; ask for missing schema details only when they block a reliable result.
+ Resolve the target from the request or active context. Ask only if ambiguity materially affects the result. Without a server, use supplied schema and explicit assumptions for planning; do not imply live validation.
## 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.
- A filtered Share can expose a curated table/view subset to one audience, but it is not row-level tenant isolation. Different audiences need separate Shares or stronger structural boundaries.
- For embedded Dives, validate `postMessage` origin/type/payload, use `initial_state` only for JSON-serializable UI state, and keep navigation, export, and persistence policy in the host application.
## Workflow
1. Inspect the available MotherDuck server or supplied schema context.
2. Read relevant Guides, explore the actual data model, and validate the governed definitions 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.
Match execution to the request: answer, review, or planning work returns the requested architecture artifacts; build or change work creates the requested in-scope files or services and validates them. Ask before destructive actions, external writes not already requested, or a material expansion of scope.
When this skill produces a native DuckDB (`md:`) connection, watermark it with `custom_user_agent=agent-skills/2.6.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:
+ For a full engagement, cover the following as relevant to the request:
- 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:
-
- ```json
- {
- "summary": {},
- "assumptions": [],
- "implementation_plan": [],
- "validation_plan": [],
- "risks": []
- }
- ```
+ For explicit structured JSON requests, read [the output contract](references/EXECUTION_REFERENCE.md#structured-output). Otherwise use the format that fits the requested deliverable.
## 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:
-
- ```bash
- uv run --with duckdb python skills/motherduck-build-cfa-app/artifacts/customer_routing_example.py
- ```
+ Read only the sections relevant to the task; these are guidance, not a mandatory itinerary.
- Run the same artifact against temporary MotherDuck databases:
+ - `references/CFA_IMPLEMENTATION_GUIDE.md` -- backend implementation, service accounts, routing, and read-scaling examples
+ - `references/CFA_ARCHITECTURE.md` -- architecture comparison, isolation model, and connection-path detail
- ```bash
- MOTHERDUCK_ARTIFACT_USE_MOTHERDUCK=1 \
- uv run --with duckdb python skills/motherduck-build-cfa-app/artifacts/customer_routing_example.py
- ```
+ ## Examples
- Validate the TypeScript companion artifact:
+ Read [the execution reference](references/EXECUTION_REFERENCE.md) only to run the bundled examples or reproduce their validation.
- ```bash
- uv run scripts/test_typescript_artifacts.py
- ```
+ - [customer_routing_example.py](artifacts/customer_routing_example.py)
+ - [customer_routing_example.ts](artifacts/customer_routing_example.ts)
## Related Skills
+
+ Load related skills only for missing capabilities; reuse established context.
- `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