Marketing Analyser (MCP + DuckDB)

Giving Claude Safe SQL Access to Our Marketing Data with MCP and DuckDB

25 September 2026 by

Client: International Salon Supplies

Role: Engineer (design & build)

Period: Sep 2026 – Present

Status: In progress (core pipeline live)

Tech stack: Python 3.12, DuckDB, Model Context Protocol (MCP), FastAPI, n8n, Docker Compose, Caddy, Cloudflare Tunnel, GA4, Omnisend, pytest

A small marketing data warehouse that lets Claude answer questions like 'what did we spend last week, by campaign?' across GA4, Omnisend and ad platforms. Data is loaded by scheduled n8n workflows into DuckDB and exposed through a read-only, sandboxed MCP server, with atomic publishes, self-healing incremental sync and 42 tests.

The idea

Marketing questions at ISS span half a dozen tools: Omnisend for email, GA4 for the website, Google Ads, Meta Ads and Merchant Center. Getting a cross-channel answer meant exporting CSVs and building spreadsheets.

Instead, I wanted to ask Claude in plain English and have it write and run the SQL itself, without handing an LLM the keys to anything it could damage.

Architecture

n8n (scheduled + manual triggers)
  ├─ GET  /watermark/{source}        → compute each table's sync window
  ├─ GA4 node / Omnisend HTTP        → pack rows into batches, drop all-zero rows
  └─ POST /ingest/{source}/{table}   (shared-key header)
          │
  Ingest service (FastAPI): the ONLY process that writes to DuckDB
    ├─ stage batch → landing NDJSON (deleted once durable)
    ├─ append raw_{source}.{table} (payload JSON + load metadata)
    ├─ record load in _meta.load_log
    └─ publish: staging views (type + dedupe) → marts → CHECKPOINT
               → copy → os.replace() into serving/
  MCP server (stdio or HTTP + bearer token)
    └─ read-only, sandboxed DuckDB on serving/warehouse.duckdb
       reconnects automatically when the file is swapped
Claude ⇄ tools: list_tables · describe_table · query · data_freshness

Engineering highlights

1. `read_only=True` is not a sandbox

This was the most surprising finding. Opening DuckDB in read-only mode protects the database file, but DuckDB can still read arbitrary host files (read_csv('/etc/passwd')) and write them (COPY … TO). Neither is acceptable for a connection an LLM controls.

The serving connection therefore sets:

enable_external_access = false
extension auto-install and auto-load disabled
lock_configuration = true, so the model can't undo any of the above

Tests confirm each escape route stays blocked. Query results come back as compact markdown with a row cap. SQL errors are returned as text rather than exceptions, so Claude can read the error and fix its own query.

2. One writer, many readers

DuckDB allows only one read-write process per file. Rather than fight that, the design embraces it:

A FastAPI ingest service is the only writer.
Publishing builds a fresh copy of the warehouse, checkpoints it and swaps it into place with an atomic os.replace().
The MCP server notices the swap by watching the file's inode and mtime and reconnects. This avoids a subtle bug where a long-lived connection keeps reading the old, unlinked file forever.

Queries never block during a load and never see half-loaded tables.

3. Incremental sync that fixes itself

Ad platforms revise historical numbers: GA4 takes about 48 hours to finalise, and conversions get attributed late. A naive "resume from the last timestamp" sync freezes wrong numbers in place. So:

Raw data is append-only. Staging views keep the newest version of each row with QUALIFY row_number() OVER (PARTITION BY … ORDER BY _loaded_at DESC, _row_index DESC), and older versions stay queryable. The partition key depends on the data: dated metrics use date + entity, catalogue data uses the entity alone.
A 7-day lookback re-fetches recent days on every run.
90-day chunks. A one-shot backfill crashed n8n's GA4 node with "Maximum call stack size exceeded" at around 100k rows.
The window end is stored, so the cursor can move past date ranges that legitimately have no data.
Only successful loads advance the cursor, so a failed window is retried, never skipped.

4. Designing around a tiny API budget

Omnisend's reporting API allows only 55 requests per day. The workflow bundles all three reports into a single request of up to four queries, sizes chunks to fit the daily-query cap, handles the exclusive end date, and drops the ~95% of rows that are all-zero padding before they reach the warehouse.

5. Operational hardening

Bearer tokens are compared in constant time, and the HTTP server refuses to start without one.
Backend ports bind to loopback only, and the write endpoint is deliberately never exposed through the proxy.
Public access goes through Cloudflare Tunnel and Caddy.
A watchdog restarts the container when Docker Desktop's macOS bind mount goes stale, which happened twice in development.

Built for maintainability

The codebase has 13 SOURCE OF TRUTH markers and a short invariants document listing the rules that would cause silent wrongness if broken. That documentation is as much for future AI-assisted edits as it is for people.

Status and numbers

~1,460 lines of Python, ~450 lines of SQL across 11 models, ~820 lines of tests
42 pytest tests covering ingest idempotency, dedupe tie-breaks, failure logging, watermark behaviour, publish/swap, HTTP auth and sandbox escapes
The GA4 steady state is about 2.7k rows per run in about 10 seconds
Working end to end: the warehouse, ingest, MCP over stdio and HTTP, and GA4 sync. Next up: Omnisend activation, then Google Ads, Meta and Merchant Center.

Where this came from

My first MCP experiment, in July 2025, was a toy TypeScript server over an in-memory mock database that happily exposed a write tool. A year later this one is read-only, sandboxed, authenticated, runs over two transports and has tests proving it. The protocol hasn't changed much. What I learned is how much has to sit around it before it's safe.

in Projects