Ad Engine: CAC & ROAS Alerts for DTC Telehealth

Polls Meta + Google every 15 min. Fires 5 alert rules. Claude Sonnet 4.6 returns structured diagnoses. Built on Aurora Serverless v2 + Vercel Fluid Compute.

Comment

We chose Amazon Aurora Serverless v2 over DynamoDB because every alert rule requires a multi-table join with a 7-day rolling window — AVG(ctr) OVER (PARTITION BY entity_meta_id ... ROWS 336 PRECEDING) — a query DynamoDB can't express without scatter-gather across pre-built GSIs.

Inspiration

On February 14, 2026, Meta began enforcing health-data restrictions that killed pixel-based lower-funnel conversion events for DTC telehealth advertisers. No purchase events. No lead events. Attribution for GLP-1, ED, hair-loss, and compounding pharmacy campaigns went dark overnight.

I was running Bliss Health's ad account. At 11 PM I watched a single ad set — frequency at 4.7, CTR collapsing, spend still running — burn $4,200 while the agency slept. The alert that should have fired four hours earlier didn't exist.

When attribution dies, you need leading indicators: frequency saturation, CTR trend against a rolling baseline, CAC vs. median. These metrics are computable from raw delivery data available in real time via the Meta Marketing API. The tool I wanted didn't exist. I built it.

What It Does

Ad Engine polls Meta Ads and Google Ads every 15 minutes via Vercel Cron. Each poll writes raw delivery metrics to an append-only insights_hourly table on Amazon Aurora PostgreSQL Serverless v2 (us-east-2, 0.5–1.0 ACU). A rules engine evaluates every active ad set against five alert rules:

  • spend_no_conversions — spend exceeds threshold, zero attributed conversions in the window. First signal of a broken pixel or non-converting creative.
  • frequency_high — frequency > 4.0. Audience exhaustion before CTR collapses; caught one cycle earlier than CTR alone.
  • ctr_collapse — CTR dropped > 40% vs. 7-day rolling baseline. Directional creative fatigue, platform-agnostic.
  • cac_spike — CAC > 1.8× the 30-day per-ad-set median. Flags efficiency regression without requiring a hard dollar threshold.
  • roas_low — ROAS < 1.5. Floor threshold; any DTC telehealth ad set below this is acquiring patients at a loss.

Concrete example. Ad set GLP-1_OldHook_Static: frequency hit 4.5, CTR collapsed from 1.8% to 0.43% over 72 hours. Both frequency_high and ctr_collapse fired. A Vercel Function on Fluid Compute sent the alert context to Claude Sonnet 4.6, which returned:

{
  "diagnosis": "GLP-1_OldHook_Static has been in-feed 4.5× on average. CTR collapsed from 1.8% to 0.43% over 72h — classic creative fatigue. The hook is recognized, not acted on.",
  "actions": [
    { "label": "Pause ad set immediately", "rationale": "0.43% CTR burns budget with no conversion path.", "urgency": "now" },
    { "label": "Duplicate with new creative hook, same audience", "rationale": "Audience intent is valid; creative is exhausted.", "urgency": "today" },
    { "label": "Set frequency cap at 3.5 across this campaign", "rationale": "Prevents recurrence. Structural fix, not just tactical pause.", "urgency": "this_week" }
  ]
}

The urgency field is a Zod-enforced enum ('now' | 'today' | 'this_week'). The UI sorts alerts by urgency, color-codes severity (red / amber / blue), and ships each action with a one-click "Open in Meta Ads Manager" deep link to the exact ad set. No parsing. No hallucinated fields.

Delivery: web dashboard at runadengine.com/dashboard, email digest, Slack webhook.

Target audience: DTC telehealth brands (GLP-1, compounding pharmacy, longevity clinics), their paid media agencies, and any operator spending > $5K/day on Meta or Google who lost attribution signal in February 2026.

How We Built It

Decision 1 — AWS Database: Aurora PostgreSQL Serverless v2 (not DynamoDB)

The original architecture used DynamoDB. 24 hours before submission, I swapped it. Here is the decision matrix:

Workload characteristic DynamoDB Aurora SLv2 Winner
Multi-entity joins (accounts × campaigns × ad_sets × insights × alerts) Single-item ops; joins require scatter-gather or denormalization Native SQL JOIN Aurora
7-day CTR baseline windowing LSI/GSI workaround; requires pre-baked baseline writes AVG(ctr) OVER (PARTITION BY entity_meta_id ... ROWS 336 PRECEDING) Aurora
Read-heavy analytical dashboard RCU consumed per attribute read; no native aggregation Server-side aggregation at query layer Aurora
Idle cost at 15-min poll cadence ~$0 + RCU per read/write 0.5 ACU ≈ $43/mo Even
Burst write when alerts fire (Claude diagnosis) High RCU per concurrent item ACU autoscales to 1.0 Aurora
Query shape as new rules are added Requires GSI redesign per new access pattern Ad-hoc SQL; zero schema migration for new query shapes Aurora

DynamoDB wins for high-velocity single-item writes with pre-planned, stable access patterns. This workload is the opposite: multi-table joins with time-series windowing and continuously evolving query shapes as new alert rules are added. The relational model fits.

Acknowledgment: DynamoDB would have been the correct choice if the alert pipeline were a pure event-append stream with known access keys (e.g., GetItem by alert_id, no joins needed). That's not this system. The rule engine's requirement for 7-day windowed baselines and multi-entity correlation made the relational choice inevitable.

Schema — insights_hourly:

CREATE TABLE insights_hourly (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  level           TEXT NOT NULL,          -- 'campaign' | 'adset' | 'ad'
  entity_meta_id  TEXT NOT NULL,          -- Meta object ID
  window_start    TIMESTAMPTZ NOT NULL,
  spend           NUMERIC(12,4),
  impressions     BIGINT,
  clicks          BIGINT,
  conversions     BIGINT,
  frequency       NUMERIC(6,4),
  ctr             NUMERIC(8,6),
  cac             NUMERIC(12,4),
  roas            NUMERIC(8,4),
  created_at      TIMESTAMPTZ DEFAULT now(),
  UNIQUE (level, entity_meta_id, window_start)  -- idempotent Cron re-runs
);

7-day baseline query (Drizzle + tagged SQL):

const baselines = await db.execute(sql`
  SELECT
    entity_meta_id,
    AVG(ctr)           AS avg_ctr_7d,
    AVG(cac)           AS avg_cac_30d,
    STDDEV(ctr)        AS stddev_ctr,
    MAX(frequency)     AS max_frequency
  FROM insights_hourly
  WHERE window_start >= NOW() - INTERVAL '7 days'
    AND level = 'adset'
  GROUP BY entity_meta_id
`);

Connection config: postgres-js with prepare: false, max: 1. No RDS Proxy — Vercel Fluid Compute instance reuse handles connection lifetime. prepare: false disables prepared statement caching (incompatible with stateless pooling); max: 1 ensures each Fluid Compute instance holds exactly one Aurora connection, released on cold. ACU floor: 0.5 in us-east-2 (~$43/mo idle). Cluster: ad-engine-prod, writer instance ad-engine-writer, ACU range 0.5–1.0.

Decision 2 — Vercel Deployment (not a basic setup)

Three Vercel primitives doing real work:

Vercel Cron — two routes on */15 * * * *:

  • /api/cron/sync-meta — fetches Meta Marketing API insights, appends to insights_hourly, ON CONFLICT DO NOTHING on the composite unique. Re-runs are safe by design.
  • /api/cron/detect-alerts — reads baselines, evaluates 5 rules, writes to alerts table with a 4-hour dedup window per (alert_type, entity_id).

Both validate Authorization: Bearer [CRON_SECRET] — missing header returns 401. Blocks accidental local execution from hitting the production Aurora instance.

Vercel Fluid Compute — /api/diagnose (Claude calls, 8–12 sec). Instance reuse: the postgres-js connection from the previous cron tick is already open. No cold start. No connection re-handshake. Standard Vercel Functions would cold-start on every 15-min cron invocation; Fluid Compute amortizes that overhead and reuses the Aurora connection within the instance lifetime.

Why not Vercel Edge Functions: The AI SDK and postgres-js both require Node.js APIs unavailable in the V8 isolate runtime. Evaluated at architecture phase; ruled out.

Next.js 16 App Router:

  • Dashboard: Server Components + export const dynamic = 'force-dynamic' — Aurora query results are React props, no client-side fetch.
  • Alert acknowledgment: Server Action → Aurora UPDATE → revalidatePath('/dashboard'). One round-trip.
  • Signup: Server Action → Aurora INSERT → Calendly redirect with prefilled params. One round-trip, no separate API route.
  • useActionState (React 19) for client-side validation feedback — no state library.
  • Apex redirect via next.config.ts rewrites. No landing page infrastructure.

Decision 3 — Claude Sonnet 4.6 with Schema-Enforced Structured Output

Zod schema:

export const alertDiagnosisSchema = z.object({
  diagnosis: z.string().min(20).max(400),
  actions: z.array(z.object({
    label:     z.string().max(80),
    rationale: z.string().max(200),
    urgency:   z.enum(['now', 'today', 'this_week']),
  })).min(1).max(4),
});
export type AlertDiagnosis = z.infer<typeof alertDiagnosisSchema>;

AI SDK call:

const { experimental_output } = await generateText({
  model: anthropic('claude-sonnet-4-6'),
  output: Output.object({ schema: alertDiagnosisSchema }),
  prompt: buildDiagnosisPrompt(alert, contextWindow),
});
// experimental_output is typed as AlertDiagnosis. No casting. No parsing.

The urgency enum is simultaneously the React sort key, the CSS color selector, the SQL ORDER BY value, and the Slack priority flag. Claude's output is data the rest of the system already knows how to use. If the model deviates from the enum, the SDK throws before the response touches application code. Zero hallucinated fields at the application boundary.

Decision 4 — Drizzle ORM + postgres-js

Drizzle schema doubles as the migration source (drizzle-kit push). postgres-js is Aurora-native; no node-postgres connection storms in serverless. prepare: false is required when running without a persistent pool — disables server-side prepared statement caching that would fail across new connections.

Decision 5 — Design as a Direct Rendering of Database State

Every UI element maps 1:1 to a query:

  • 5 KPI cards → 5 aggregate queries on insights_hourly + alerts; computed server-side, no client aggregation.
  • 14-day chart → SELECT date_trunc('day', window_start), SUM(spend) GROUP BY 1 ORDER BY 1 LIMIT 14
  • Alert sort → ORDER BY CASE urgency WHEN 'now' THEN 1 WHEN 'today' THEN 2 ELSE 3 END — the Zod enum from Claude is the SQL sort key.
  • Alert colors → CSS custom properties driven by the urgency field. Zero client logic.
  • Tailwind v4 @custom-variant dark — class-driven dark mode, SSR-safe. Geist Mono for all metric values — visual alignment signals data provenance.

Challenges We Ran Into

Aurora swap 24h before deadline. DynamoDB's access-pattern constraints locked every new alert rule behind a GSI redesign. Swap to Aurora Serverless v2: full Drizzle schema from scratch, drizzle-kit push to new cluster, 14-day insights_hourly reseed. Done in 6 hours. The composite unique index on (level, entity_meta_id, window_start) made the reseed idempotent — re-running it never double-wrote data.

postgres-js TCP keepalive blocking process exit. seed-history.ts completed all inserts but hung indefinitely. Root cause: postgres-js holds the Node.js event loop open via TCP keepalive unless explicitly closed. Fix: await sql.end() as the final line in every seed script. Same pattern applies in Vercel Functions — module-level connections must be closed inside the handler, not at module scope.

Vercel Cron + local seeding write collision. Running seed-history.ts locally while production cron executed caused concurrent writes to insights_hourly. The composite unique index + ON CONFLICT DO NOTHING absorbed both writes without corruption — the collision silently no-oped. The idempotency constraint was the correct primary defense, not just a nice-to-have.

The structured output moment. First diagnosis endpoint returned a 200-word paragraph displayed as <p> text. Adding urgency sorting required NLP parsing. Switched to Output.object() + Zod schema: Claude's response became a typed object. Urgency drove sort order. Color-coding was a CSS lookup. No parsing. The shift: structured output isn't a formatting choice — it's the boundary between "AI as text generator" and "AI as data source."

Demo data → real data lifecycle. Dashboard needed to look credible for design partners before live API credentials were connected. Seeder generates statistically plausible synthetic campaigns (CTR decay curves, frequency ramp, ROAS spikes) using the same Drizzle schema as production. Switching to real Meta API data is a single env var toggle — no code path divergence.

Accomplishments We're Proud Of

  • Blank repo → runadengine.com SSL-live (5 alert rules, Claude diagnosis, Slack webhook, pricing) in 7 days
  • Aurora Serverless v2 swap replacing DynamoDB with zero downtime, 6 hours before submission deadline
  • Structured-output pattern: zero parsing in the UI — Claude's urgency enum is simultaneously the SQL ORDER BY and the CSS color key
  • 24/7 polling on production Aurora via Vercel Cron — 96 poll cycles/day, idempotent, 4-hour alert dedup
  • End-to-end: ad data → Aurora → Claude diagnosis → operator action in < 30 seconds from alert fire

What We Learned

Structured output is a different category of building, not a better version of free text. When Claude returns { urgency: 'now' | 'today' | 'this_week' }, that field is simultaneously the React sort key, the CSS color selector, the SQL ORDER BY value, and the Slack priority flag — with no parsing step in between. The AI SDK's Output.object() with Zod schemas is the mechanism that makes this work at the type level. If the model deviates, the SDK throws before the response reaches application code. After this I'll never call a model without a Zod schema again. Free-text outputs feel like working barehanded. It stopped feeling like "wrapping a chatbot" and started feeling like calling a function that happens to be probabilistic.

The DynamoDB vs. Aurora decision is a workload shape question, not a defaults question. DynamoDB is the correct choice for high-velocity single-item writes with pre-planned, stable access patterns — if you can draw the complete GSI layout before writing the first line of application code, DynamoDB. If your primary query requires joining five normalized tables followed by a time-series window function, use a relational engine. Aurora Serverless v2's 0.5 ACU floor (~$43/mo idle in us-east-2) makes this practical at pre-revenue scale. The diagnostic framework: enumerate your access patterns before choosing the database. Don't choose the database and then design around it.

Vercel Fluid Compute changes the connection-pooling calculus for serverless + RDBMS. Standard serverless functions are stateless — connections are created and destroyed per-request, creating cold-start overhead and connection churn against a relational database. Fluid Compute instance reuse means the postgres-js connection created on the first invocation persists for subsequent invocations within the same instance lifetime. At a 15-minute cron cadence, the diagnosis endpoint stays warm between polls, the connection is already established, and Aurora query latency drops materially. No RDS Proxy required at this scale — Fluid Compute's instance model handles the pooling.

What's Next for Ad Engine

Q3 2026 — Multi-tenant Meta OAuth. Current deployment uses a single service account. Next: full OAuth 2.0 flow — operators connect their own Meta Ads accounts in under 2 minutes. Each account gets isolated row-level security in Aurora. No cross-tenant data access; Aurora handles the tenancy boundary at the query layer.

Google Ads parity. Scaffolded in the codebase. Full rule parity (same 5 rules, same Claude diagnosis flow, same urgency enum) ships within 30 days of multi-tenant launch. Aurora's schema handles both platforms without table changes — platform is a column.

Predictive alerts. Today's rules fire when a metric crosses a threshold. Next version: Claude analyzes trend trajectories and flags ad sets about to break — frequency trending toward 3.8 over 48 hours, CTR declining 12% day-over-day for 3 consecutive days. Aurora's window functions make the trend data cheap to compute; the model call is the same pattern.

Channel distribution. Active conversations with Bliss × Medibera pharmacy network — 100+ telehealth clinics. Distribution model: white-label dashboard per clinic, consolidated agency-layer alerts. Aurora's multi-tenant schema handles this without architectural changes.

Where this needs to be: 25 paying clinics, ~$30K MRR by Q1 2027. We're already onboarding 5 founding partners at $3K/year as our forcing function. Blended ARPU ~$1,200/mo across tiers ($499 / $1,499 / $4,500–9,000/mo). ICP: any DTC health operator spending > $5K/day on paid media who lost attribution signal in February 2026. Distribution edge: the Bliss × Medibera pharmacy network's 100+ telehealth clinic relationships.

Built With

  • amazon-aurora
  • amazon-web-services
  • anthropic
  • aurora-serverless-v2
  • claude-sonnet
  • drizzle-orm
  • geist-font
  • google-ads-api
  • lucide
  • meta-marketing-api
  • nextjs
  • postgres-js
  • postgresql
  • react-19
  • recharts
  • tailwindcss
  • typescript
  • vercel
  • vercel-ai-sdk
  • vercel-analytics
  • vercel-cron
  • vercel-fluid-compute
  • vercel-functions
  • zod
Share this project:

Updates