adscope

_{paid-media analytics platform — from ad-platform APIs to natural-language answers}

adscope is a complete paid-media analytics platform that anyone can run with docker compose up. It ships its own ad-platform simulator (a fake Meta/Google Ads API), collects that data with Rust collectors, schedules the collection with Airflow, models it with dbt into local DuckDB marts (ROAS, CPA, CAC), streams events through Redpanda for near-real-time metrics, and closes the loop with a dashboard and an AI layer that answers questions about your ad spend in plain language. No cloud warehouse, no paid accounts, no API keys to beg for — the whole pipeline, analytics included, is reproducible on a laptop.

Architecture

Target system. The status section below says what is actually built today.

┌───────────────────────────────┐
│     ad-platform simulator     │  fake Meta / Google Ads API (FastAPI)
└─────────┬──────────────┬──────┘
          │ REST         │ events
          ▼              ▼
┌──────────────┐   ┌──────────────┐
│  collectors  │   │   Redpanda   │──▶ near-real-time metrics
│    (Rust)    │   │   (Kafka)    │
└──────┬───────┘   └──────────────┘
       ▼
┌──────────────┐
│   Postgres   │  raw layer
└──────┬───────┘
       │  orchestrated by Airflow
       ▼
┌──────────────┐
│ dbt → DuckDB │  marts: ROAS · CPA · CAC
└──────┬───────┘
       ▼
┌───────────────────────────────┐
│   dashboard   ·   AI layer    │  RAG → agents — "talk to your ads data"
└───────────────────────────────┘

Status

Seven layers exist today:

• Foundation — docker-compose.yml with Postgres 18 (the raw layer every later stage lands data into), env-driven config, healthchecks, and a Makefile wrapping the common operations.

• Ad-platform simulator (services/simulator/) — a deterministic fake paid-media API: one seeded world, two faithful dialects. Meta-style (/meta/v25.0/, modeled on Marketing API v25.0): act_ account IDs, campaigns → ad sets → ads, a daily insights endpoint with string-typed metrics, cursor pagination, expiring OAuth tokens, and rate limiting the way Meta really does it (X-Business-Use-Case-Usage header, HTTP 400 + code 80000 — not a 429). Google Ads-style (/google/v24/, modeled on Google Ads API v24): a GAQL subset via googleAds:search (a whitelist of the collector's query shapes, not a parser — stated honestly in the service README), camelCase rows with money in micros (costMicros as an int64 string), fixed-size pages + nextPageToken under the identical-query rule, triple auth (Bearer + developer-token + login-customer-id), and google.rpc.Status errors with 429 RESOURCE_EXHAUSTED + retryDelay. Everything is a pure function of SIM_SEED (default 42): 2 accounts per channel, 2 years (730 days) of history at ~300 delivering ad-day rows/day, campaigns launching continuously across the window, conversions restating over 3 days like real attribution. 93 tests cover the engine, both dialects, and cross-dialect agreement — the same canonical row surfaces as "spend": "150.00" on one side and "costMicros": "150000000" on the other, proven exact (Decimal equality, no float tolerance).

• Collectors (collectors/) — a Rust workspace (domain core, meta + google adapters, storage, cli) that reads both simulator dialects and lands them in Postgres raw, idempotently. The pipeline now runs end to end from simulator → collectors → Postgres raw. One binary, two adapters over one core: full-jitter exponential backoff lives in the domain crate and both adapters draw on it for transport and 5xx retries, while each computes its own throttle wait from its dialect's real signal — Meta's X-Business-Use-Case-Usage header (HTTP 400 + code 80000), Google's retryDelay (HTTP 429 RESOURCE_EXHAUSTED). The raw layer is per-dialect native tables — raw.meta_insights with money as NUMERIC and counts as BIGINT, raw.google_ad_metrics with cost_micros as BIGINT and conversions as DOUBLE PRECISION — entities kept whole as jsonb, and a raw.sync_runs ledger that persists each chunk's cursor so interrupted runs resume where they stopped. Loading is a change-detecting ON CONFLICT … DO UPDATE … WHERE … IS DISTINCT FROM … upsert: a repeat collect touches zero rows, a restated day rewrites exactly the rows that changed, in place. Subcommands migrate, collect --source meta|google --from --to, backfill --source … --days N. 54 tests (44 unit + 10 integration against the live stack).

• Orchestration (airflow/) — collection is now scheduled and observable, and runs unattended. Apache Airflow 2.10.5 (LocalExecutor) sits behind an opt-in compose orchestration profile — so the default docker compose up stays exactly postgres + simulator — with the UI on localhost:8082. The adscope_collect DAG (@daily, catchup=False, max_active_runs=1) migrates the raw schema, then fans out to two parallel per-source tasks (meta + google) that run the frozen collector image via DockerOperator — docker-out-of-docker: the scheduler drives the host Docker daemon over its mounted socket, and each collector container joins the compose network to reach postgres/simulator by service name on their internal ports. Each task pulls a 7-day trailing window with retries=3 and exponential backoff, so a transient simulator or database blip recovers on the next attempt with no one watching. Pipeline today: simulator → collectors → Postgres raw, orchestrated by Airflow.

• Warehouse (warehouse/) — a dbt project on DuckDB (local file, no cloud) that turns the raw layer into analytics marts. DuckDB attaches the Postgres raw schema read-only — no copy — and dbt builds staging → intermediate → marts. The star is the conformed cross-dialect fact int_ad_performance_daily: Meta's decimal spend and Google's cost_micros / 1e6 meet at one grain, one metric vocabulary, one fact — 372,411 rows across 730 days, meta and google unioned losslessly. On top sit the money marts — ROAS, CPA, CAC, CTR, CPC, CVR by campaign/channel/day/month, ratios recomputed at every grain (never averaged). Accounts span BRL and USD, so money is conformed to a USD reporting currency via a fixed-FX seed while dimensionless ratios stay native. 114 tests (generic + custom: both channels present, no duplicate grain, finite ROAS, non-negative spend); every model and column documented (this feeds the RAG layer's retrieval corpus). make dbt-build.

• AI layer (rag/) — talk to your ads data: ask a paid-media question in plain English, get the answer plus the exact SQL that produced it and which marts it read. The flow is retrieve → generate → guard → execute — dbt-doc schema cards embedded in pgvector (a dedicated Postgres; the default alpine image ships without the extension) are pulled for the question, a provider-agnostic LLM writes DuckDB SQL, that SQL clears the guardrails (see The guardrail is the point), then runs read-only against the DuckDB marts. Generation defaults to a local Ollama model (qwen2.5-coder:3b), offline and R$0; anthropic/openai are opt-in behind a key. Embeddings default to sentence-transformers all-MiniLM-L6-v2 on CPU, with a dependency-free hashing fallback for CI. 54 deterministic tests pin the engine: the retrieval-to-guarded-SQL path is proven end to end with a faked model over the real marts (best channel by ROAS → Meta 3.87 vs Google 1.26), and 30 adversarial inputs are refused. The whole layer sits behind the opt-in compose ai profile — the default docker compose up is unchanged. Pulling the model and running live CPU inference is the one slower, opt-in path; the engine and its guardrails are proven deterministically, no model required. The layer is graded by an eval suite (make eval — 16/16 execution-match on a golden set, 14/14 adversarial refusals) and extended by a spend-anomaly agent (make anomaly): a seasonality-aware statistical detector flags genuine outliers — not the LLM, so it never cries wolf on normal weekend dips or month-end budget flush — and only then does the LLM write a grounded markdown alert, gathering its context through the same read-only guardrails.

• Dashboard (dashboard/) — a Streamlit UI over the marts, brand-exact (every chart is hand-drawn inline SVG — no plotting lib, no CDN), with the RAG's talk to your data panel embedded and its generated SQL on show. KPI row, spend-vs-ROAS trend, Meta-vs-Google comparison, campaign leaderboard — all reading the real marts, deltas honest (a genuine month-over-month dip is a red arrow, not a fabricated green one). Opt-in dashboard profile (make dashboard, port 8084); the default docker compose up is unchanged.

What's left — Redpanda streaming, Terraform provisioning, and a final polish pass — is not built yet; it arrives in the order laid out in the roadmap.

Why the simulator is interesting

Most portfolio pipelines ingest a static CSV. This one ingests a misbehaving API, on purpose:

• Determinism. Every value derives from hash(seed, entity, field, date) — no stateful RNG. Two clean clones with the same .env produce byte-identical data, so the collectors' integration tests can assert exact values and every demo is reproducible.
• Restatement. Conversions for day D observed on day T equal final × maturity(T−D) on a fixed schedule (0.60 → 0.85 → 0.95 → 1.00 over 3 days). Yesterday's numbers change today. A collector that appends rows produces wrong marts; the only correct design is re-fetching a trailing window and upserting — idempotent ingestion is forced by the data, not by a style guide.
• Operational friction. Tokens expire mid-run (401 code 190), reporting windows are capped at 92 days (backfills must chunk), and throttling mirrors Meta's real trap: HTTP 400 with the retry math hidden in a JSON header. Metrics arrive as strings ("spend": "150.00"), so downstream code parses decimals, not floats.
• Two error regimes, one collector. The dialects disagree on purpose, exactly where the real APIs do: Meta throttles with a 400 + header math, Google with a 429 RESOURCE_EXHAUSTED + retryDelay; Meta auth is one Bearer token, Google demands the Bearer + developer-token + login-customer-id triple; Meta paginates with cursor URLs, Google with nextPageToken bound to the identical query. Generic "retry on error" code passes one side and fails the other — the asymmetry forces one backoff module with two genuine adapters.

The generated spend isn't uniform noise either — weekly seasonality plus a month-end budget-flush spike, which later feeds the anomaly-detection story:

And it is one advertiser on two platforms: a single script authenticates against both dialects — Meta insights on one side, GAQL on the other — and plots their spend on one chart:

Idempotent by construction

The simulator restates conversions for the trailing three days (attribution maturity), so yesterday's numbers legitimately change today — and an append-only load would produce wrong marts. The collector takes the only correct route: every run re-fetches a trailing window and upserts on the natural key (account_id, ad_id, date_start) behind a change gate, so replaying an identical window writes zero rows while a restated day rewrites exactly the rows that moved, in place. The crisp way to see it: change a value in raw.meta_insights by hand, re-run the collect, and the row heals back to the API's truth — a direct consequence of the same change-detecting upsert the integration tests pin down as no-op replay and in-place restatement. F3 hands that proof to the scheduler: adscope_collect re-fetches a 7-day trailing window every day, so the change gate turns each run's overlap with the last into a zero-row no-op while rewriting only genuinely restated rows — idempotency now proven by the scheduler, not just the test suite, and restatement-correct by construction.

The guardrail is the point

An LLM will, eventually, emit DROP TABLE dim_account, or a stacked SELECT 1; DELETE …, or a read_csv('/etc/passwd'). The interesting engineering in the AI layer isn't the prompt — it's that none of those ever reaches the warehouse. Every model-generated string passes guardrails.check() before it is allowed to run: sqlglot parses it in the DuckDB dialect and admits it only if it is a single statement (blocks ;-stacked injection), a read-only SELECT (no DML/DDL/PRAGMA/ATTACH/COPY/CALL/SET), touching only the 7 allow-listed main.* marts (blocks raw.*, information_schema, sqlite_master, and table functions like read_csv/postgres_scan), with a LIMIT forced on. The text that actually executes is re-rendered from the validated AST, so any smuggled comment or formatting trick is gone — and the DuckDB connection is opened read-only, so even a hypothetical bypass cannot write. The allow-list isn't a hand-kept constant: it's derived from the same dbt artifacts that feed retrieval, so a mart is documented, embedded, and queryable in one step and nothing else ever is. 30 adversarial inputs — injections, DML, off-schema reads, file-loaders — are pinned rejected in the test suite. The model proposes; the guardrail disposes. That is the production-AI signal, not the chatbot.

Quickstart

Requires Docker with the Compose plugin.

git clone https://github.com/NicksonIndiani/adscope.git
cd adscope
cp .env.example .env      # defaults work out of the box
docker compose up -d      # or: make up
docker compose ps         # or: make ps — services report (healthy)

Postgres listens on localhost:5442 (override via POSTGRES_PORT in .env — the non-obvious default avoids colliding with a local Postgres on 5432). The simulator listens on localhost:8080 (SIMULATOR_PORT), with OpenAPI docs at /docs and an unauthenticated /healthz. make logs tails the services, make down stops them, make clean also drops the data volume. To browse the raw tables in a browser, make db-ui starts a pgweb instance at localhost:8081 (a loopback-only dev tool behind the compose tools profile — never part of the default stack).

Talk to the simulator like you would to the real Marketing API — token, discovery, paginated insights (curl + jq):

TOKEN=$(curl -s -X POST localhost:8080/meta/oauth/token \
  -d 'grant_type=client_credentials&client_id=adscope-collector&client_secret=local-dev-secret' | jq -r .access_token)
ACT=$(curl -s -H "Authorization: Bearer $TOKEN" localhost:8080/meta/v25.0/me | jq -r '.accounts[0]')
curl -s -g -H "Authorization: Bearer $TOKEN" \
  "localhost:8080/meta/v25.0/$ACT/insights?level=campaign&limit=3&time_range={\"since\":\"2026-06-01\",\"until\":\"2026-06-30\"}"

Each page's paging.next is a ready-to-curl URL; the last page simply omits it.

The same world answers in Google Ads dialect — token plus developer-token, discover customers, then GAQL:

TOKEN=$(curl -s -X POST localhost:8080/google/oauth/token \
  -d 'grant_type=client_credentials&client_id=adscope-collector&client_secret=local-dev-secret' | jq -r .access_token)
AUTH=(-H "Authorization: Bearer $TOKEN" -H "developer-token: ADSCOPEDEVTOKEN0000000")
MGR=$(curl -s "${AUTH[@]}" localhost:8080/google/v24/customers:listAccessibleCustomers | jq -r '.resourceNames[0]' | cut -d/ -f2)
CID=$(curl -s "${AUTH[@]}" -H "login-customer-id: $MGR" \
  "localhost:8080/google/v24/customers/$MGR/customerClients" | jq -r '.results[0].customerClient.id')
curl -s "${AUTH[@]}" -H "login-customer-id: $MGR" -H 'Content-Type: application/json' \
  "localhost:8080/google/v24/customers/$CID/googleAds:search" \
  -d '{"query":"SELECT campaign.id, segments.date, metrics.cost_micros FROM campaign WHERE segments.date BETWEEN '\''2026-06-01'\'' AND '\''2026-06-30'\'' LIMIT 3"}'

Rows come back camelCase with "costMicros": "150000000" — money as an int64 string in micros. More rows than a page holds → nextPageToken; resend the identical query with pageToken set. Hammer it and the throttle answers 429 RESOURCE_EXHAUSTED with a retryDelay — unlike Meta's 400. Both idioms, and both throttle styles back to back, in fifteen seconds:

services/simulator/scripts/demo.sh runs the full Meta walk — including hammering the API until it throttles:

With the stack still up, run the collectors against it — a stable Rust toolchain is all you add. They read both dialects and load the raw schema in Postgres:

cd collectors
cargo run -p adscope-collector -- migrate
cargo run -p adscope-collector -- collect --source meta   --from 2026-06-01 --to 2026-06-30
cargo run -p adscope-collector -- collect --source google --from 2026-06-01 --to 2026-06-30

Config comes from flags or environment; the defaults match compose, so a clean clone needs nothing — DATABASE_URL defaults to postgres://adscope:adscope@localhost:5442/adscope and the simulator to http://localhost:8080. backfill --source meta --days 90 is the same code path with a wider window, chunked to the API's 92-day cap. Then peek at what landed:

psql postgres://adscope:adscope@localhost:5442/adscope \
  -c "SELECT date_start, count(*), sum(spend) AS spend FROM raw.meta_insights GROUP BY 1 ORDER BY 1 LIMIT 5;" \
  -c "SELECT segments_date, count(*), sum(cost_micros) AS cost_micros FROM raw.google_ad_metrics GROUP BY 1 ORDER BY 1 LIMIT 5;"

Run the same collect twice and the second pass reports zero rows upserted — the change-detecting upsert makes re-ingestion a no-op, exactly what conversion restatement demands.

Warehouse (marts)

The collectors fill raw; dbt turns it into marts. DuckDB reads the Postgres raw tables directly (attached read-only), so nothing is copied and no cloud warehouse is involved. It runs behind the compose warehouse profile, never in the default stack:

make dbt-build     # docker compose --profile warehouse run --rm dbt build
make dbt-docs      # lineage docs: staging → intermediate → marts

The marts land in a local DuckDB file (warehouse/adscope.duckdb, git-ignored, reproducible). Two dialects become one leaderboard — top campaigns by ROAS, Meta and Google ranked side by side even though one reports decimal spend and the other cost_micros:

campaign	channel	ROAS	CPA	CTR
2025-Q3 · Remarketing · US	meta	8.28	$39.36	1.63%
2025-Q4 · Prospecting · US	meta	7.78	$31.92	1.36%
2025-Q2 · Lookalike · US	meta	7.57	$34.59	1.34%

At the channel level the blended numbers read the way real paid media does — Meta ROAS 3.87 on $9.2M spend; Google 1.26 on $2.8M, Search-heavy with a higher CTR and lower ROAS. Money is conformed to USD across BRL and USD accounts via a fixed-FX seed.

The whole model graph, left to right — six raw.* sources through staging into the cross-dialect union int_ad_performance_daily (green), then out to the marts:

Orchestration (optional)

Everything above runs the collector by hand; Airflow runs it on a schedule. It is opt-in — gated behind the compose orchestration profile, so the plain docker compose up never builds or pulls it:

make airflow-up          # docker compose --profile orchestration up -d

Open the UI at localhost:8082 (login airflow / airflow), unpause the adscope_collect DAG, and trigger a run: it migrates the schema, then collects both sources over a 7-day trailing window, each in its own container. Trigger it again and the task logs report zero rows changed — idempotency, proven by the scheduler instead of by you.

The collect task's own log, on a re-run over an already-collected window — rows_upserted=0, nothing touched:

When the simulator is unreachable, the collect tasks fail their first attempt and recover on retry — no one watching:

Two costs ride along with the profile, both absent from the default stack:

• A large first pull. The Airflow image is ~2 GB, so the first make airflow-up on a clean clone spends a few minutes fetching and building before the UI answers; later starts are quick.

• The docker socket's group. Each collect task runs the collector as its own container by driving the host Docker daemon (docker-out-of-docker), so the Airflow scheduler has to belong to the socket's group. If the stack comes up but the collect tasks can't launch containers, set DOCKER_GID in .env to your host's value and re-run make airflow-up:

  stat -c '%g' /var/run/docker.sock    # → put this number in DOCKER_GID

AI layer (optional)

Talk to your ads data: ask in plain English, get the answer plus the SQL. The whole layer is gated behind the compose ai profile — the plain docker compose up never pulls it — and the default path is free and local (sentence-transformers embeddings on CPU, a small model via Ollama). The questions run against the DuckDB marts, so build those first:

make dbt-build     # the marts every answer runs against
make dbt-docs      # manifest + catalog: the retrieval corpus and the guard allow-list
make rag-up        # pgvector Postgres + Ollama + rag service (ai profile)
make ollama-pull   # one-time: pull the local gen model (qwen2.5-coder:3b, a few GB)
make rag-index     # embed the dbt docs into pgvector
make rag-ask Q="which channel had the best ROAS?"

make ollama-pull is the one real cost — a few-GB model download, once, into a named volume; after that, inference is local, offline, and R$0. First responses are slow: it's CPU inference on your machine, not a hosted endpoint. Prefer a hosted model instead? Set RAG_LLM_PROVIDER=anthropic (or openai) with a key in .env and the same flow uses it. Either way the guarded, deterministic core — retrieval, validation, read-only execution — is the part proven in the test suite; the live model is the one opt-in, non-deterministic step.

Roadmap

• Foundation — compose skeleton, Postgres raw layer
• Ad-platform simulator, Meta-style dialect — deterministic engine, restatement, rate limits
• Ad-platform simulator, Google-style dialect — GAQL, micros, nextPageToken
• Rust collectors — idempotent ingestion + backfill, one codebase against both dialects
• Airflow orchestration — scheduled daily collection via DockerOperator, retries + backoff
• dbt + DuckDB marts — ROAS, CPA, CAC, conformed across both dialects
• Analytics dashboard — Streamlit over the marts, brand-exact, with the RAG panel embedded (make dashboard)
• Redpanda streaming — near-real-time metrics
• RAG layer — "talk to your ads data": guarded NL→SQL over the marts
• Eval suite — golden execution-match + guardrail regression on the NL→SQL (make eval: 16/16 answers, 14/14 refusals)
• Anomaly-detection agents — seasonality-aware detector + a grounded LLM alert (make anomaly)
• Terraform + CI
• Polish — docs, demos, benchmarks

License

MIT — see LICENSE.