Insights — Architecture

Overview

The Insights system introduces one new Prisma model (ChannelInsight), eight new BullMQ agent queues, and a new API route group. It reuses all existing infrastructure: the same BullMQ + Redis worker pattern, the same provider packages for data fetching, and the same Claude adapter for LLM calls.

Database Schema

New model: ChannelInsight

Add to packages/db/prisma/schema.prisma:

model ChannelInsight {
  id                 String    @id @default(cuid())
  tenantId           String
  connectedChannelId String
  channelType        String    // "Google Search Console" | "Google Analytics" | ...
  period             String    @default("last_6_months")
                               // "last_3_months" | "last_6_months" | "last_12_months"
  status             String    @default("pending")
                               // "pending" | "generating" | "done" | "failed"
  generatedAt        DateTime  @default(now())
  completedAt        DateTime?
  summary            String?   @db.Text      // Markdown narrative
  insights           Json?
  // Shape: {
  //   strengths:        { title: string; detail: string }[]
  //   weaknesses:       { title: string; detail: string }[]
  //   opportunities:    { title: string; detail: string }[]
  //   recommendations:  { title: string; detail: string; priority: "high"|"medium"|"low" }[]
  // }
  errorMessage       String?
  agentRole          String    // "gsc-insights" | "ga-insights" | ...
  costUsd            Float?
  durationMs         Int?
  inputTokens        Int?
  outputTokens       Int?
 
  tenant           Tenant           @relation(fields: [tenantId], references: [id], onDelete: Cascade)
  connectedChannel ConnectedChannel @relation(fields: [connectedChannelId], references: [id], onDelete: Cascade)
 
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
 
  @@index([tenantId])
  @@index([connectedChannelId])
  @@index([tenantId, channelType])
  @@map("channel_insight")
}

Also add the reverse relations:

// Inside ConnectedChannel model:
insights  ChannelInsight[]
 
// Inside ChannelInsight model (new field):
acceptances  ChannelInsightAcceptance[]

New model: ChannelInsightAcceptance

Stores individual insight items that a human reviewer has accepted. Each record is ingested into the channel_insights RAG dataset so agents can retrieve verified observations in future runs.

Full schema and field descriptions: accepted-learnings.md

New Agent Roles

Add InsightAgentRole to packages/queue/src/types.ts:

export type InsightAgentRole =
  | "gsc-insights"
  | "ga-insights"
  | "google-ads-insights"
  | "meta-ads-insights"
  | "facebook-insights"
  | "instagram-insights"
  | "linkedin-insights"
  | "gbp-insights";

Extend AgentRole to include InsightAgentRole.

Job Data Shape

interface InsightJobData {
  insightId: string;           // pre-created ChannelInsight.id
  tenantId: string;
  connectedChannelId: string;
  channelType: string;         // matches ChannelMaster.type
  agentRole: InsightAgentRole;
  period: "last_3_months" | "last_6_months" | "last_12_months";
}

Queue Names

Worker Pattern

Each insight worker lives under:

packages/agents/src/workers/insights/
  gsc-insights.worker.ts
  ga-insights.worker.ts
  google-ads-insights.worker.ts
  meta-ads-insights.worker.ts
  facebook-insights.worker.ts
  instagram-insights.worker.ts
  linkedin-insights.worker.ts
  gbp-insights.worker.ts

Duplicate-run prevention

triggerInsightGeneration() in apps/api/src/routers/insights.ts queries for any ChannelInsight with status IN (pending, generating) for the same connectedChannelId before creating a new record. If one is already in flight, it returns the existing insightId without enqueueing a new job. This matches the same guard in insights-scheduler.ts (weekly cron).

Standard worker flow (per job):

1. Read InsightJobData from BullMQ job
2. Load AgentConfig where role = agentRole — throw if systemPrompt is null (no hardcoded fallback — prompt must be seeded in the agent_config table)
3. Update ChannelInsight.status = "generating"
4. Read ConnectedChannel — decrypt tokenInfo via @leadmetrics/crypto
5. Refresh OAuth token if expired (handled per provider)
6. Call provider API to fetch metrics for the requested period
7. Load ClientContext for the tenant from the client_context table — always injected in full, same as every other agent. Gives Claude business context (industry, products, audience) so analysis is relevant rather than generic. 7a. Search channel_insights RAG dataset for prior accepted observations on this channel type (top 10 chunks). Injected as a PRIOR ACCEPTED INSIGHTS block — Claude builds on verified past observations rather than repeating them. See accepted-learnings.md for the prompt assembly pattern.
8. Build prompt (same assembly pattern as all other agent workers):

   {systemPrompt from agent_config}
   
   CLIENT: {tenantName}
   CHANNEL: {channelType}
   PERIOD: {period}
   
   CLIENT CONTEXT FILE:
   {clientContext.content}
   
   CHANNEL METRICS:
   {structured metrics block fetched from provider}

9. Call Claude via @leadmetrics/adapter-claude-local
10. Parse JSON response → validate output shape
11. Update ChannelInsight:
    • status: "done", completedAt, insights (Json), summary (Text), costUsd, durationMs, inputTokens, outputTokens
12. Emit WebSocket event via publishAgentEvent() — topic: insight:completed
13. On uncaught error: set status: "failed", write errorMessage

System Prompt — DB Only, No Fallbacks

This follows the same rule as all other agents in the system. The systemPrompt field on agent_config is the only source. Workers throw if it is missing — no hardcoded strings anywhere in worker code.

Each insight agent role must have a seed row in packages/db/src/seed.ts:

{ role: "gsc-insights",          model: "claude-sonnet-4-6", systemPrompt: "..." },
{ role: "ga-insights",           model: "claude-sonnet-4-6", systemPrompt: "..." },
{ role: "google-ads-insights",   model: "claude-sonnet-4-6", systemPrompt: "..." },
{ role: "meta-ads-insights",     model: "claude-sonnet-4-6", systemPrompt: "..." },
{ role: "facebook-insights",     model: "claude-sonnet-4-6", systemPrompt: "..." },
{ role: "instagram-insights",    model: "claude-sonnet-4-6", systemPrompt: "..." },
{ role: "linkedin-insights",     model: "claude-sonnet-4-6", systemPrompt: "..." },
{ role: "gbp-insights",          model: "claude-sonnet-4-6", systemPrompt: "..." },

These prompts are editable at runtime via the manage portal (same AgentConfig admin UI used for all other agents). No code deploy is needed to update an insight agent’s behaviour.

Skills (when the skills system is built)

Insight agents are analysis agents, not content creation agents. Their skill needs are narrower than content agents (no brand voice, no platform format guides, no output templates). The one category that genuinely changes output quality is benchmark reference data.

Without benchmarks, Claude can only report a number: “Your GSC CTR is 1.8%.” With a benchmark skill, it can judge it: “Your GSC CTR is 1.8% — the non-branded average is 1.9%, so you’re at the threshold; branded terms are 14%, which is healthy.”

Phase 3 approach (before skills system ships): Benchmark data lives inside the systemPrompt in agent_config. Still fully DB-managed and editable via the manage portal—no code deploy needed to update benchmarks.

When skills system ships: Benchmark content moves from individual system prompts into shared reference skills (e.g. one social-benchmarks skill shared across facebook-insights, instagram-insights, linkedin-insights). System prompts become shorter and benchmark data is updated once rather than per-agent.

Expected LLM Output Shape

The system prompt instructs Claude to respond with valid JSON matching this shape:

interface InsightOutput {
  sections: {
    strengths: { title: string; detail: string }[];
    weaknesses: { title: string; detail: string }[];
    opportunities: { title: string; detail: string }[];
    recommendations: { title: string; detail: string; priority: "high" | "medium" | "low" }[];
  };
  summary: string; // 2–4 paragraph markdown narrative
}

The worker validates the parsed shape before writing to the DB. If validation fails, the job retries up to 2 times before setting status "failed".

Trigger Mechanisms

Manual (API)

POST /tenant/v1/insights/generate
Body: { connectedChannelId, period? }

• Validates the channel is connected (isConnected: true) and owned by the tenant
• Creates ChannelInsight { status: "pending" }
• Enqueues InsightJobData to the correct queue based on channelType
• Returns { insightId } immediately; client polls or listens via WebSocket

On First Channel Connect (API)

When a channel is successfully connected for the first time, the channels API route automatically triggers insight generation for that channel — no manual action required from the tenant.

Where this fires: Inside the channel connect handler in apps/api/src/routers/channels.ts, immediately after isConnected is set to true and the ConnectedChannel record is saved.

Behaviour:

• Only fires when the channel transitions to isConnected: true for the first time (i.e. no prior ChannelInsight record exists for this connectedChannelId)
• Uses period: "last_6_months" (the default)
• Follows the same path as the manual trigger: creates ChannelInsight { status: "pending" } and enqueues the job
• If the channel type is not in the supported insight set, the auto-trigger is silently skipped (no error)

Scheduled (weekly cron)

A BullMQ repeatable job registered in apps/servers/agents/src/index.ts. Runs every Monday at 03:00 UTC.

For each tenant → each ConnectedChannel where isConnected: true and channelType is in the supported insight set, it enqueues one InsightJobData with period: "last_6_months".

API Routes

All under /tenant/v1/insights in apps/api/src/routes/insights.routes.ts:

Full acceptance API spec: accepted-learnings.md

Data Flow Diagram

┌─ Trigger sources ──────────────────────────────────────┐
│  Manual button       POST /tenant/v1/insights/generate │
│  First connect       channels.ts connect handler       │
│  Weekly cron         agents server (Monday 03:00 UTC)  │
└────────────────────────────────────────────────────────┘
                            │
                            ▼
                  API creates ChannelInsight { status: "pending" }
                  enqueues InsightJobData
                            │
                            ▼
             BullMQ Queue (agent__<channel>-insights)
                            │
                            ▼
             Insight Worker (e.g. gsc-insights.worker.ts)
                  │ ── provider API call ──► Google / Meta / LinkedIn API
                  │ ── LLM call ──────────► Claude (adapter-claude-local)
                  │ writes ChannelInsight { status: "done", insights: Json, summary: Text }
                  │ publishAgentEvent("insight:completed")
                            │
                            ▼
                  Socket.io ──► Dashboard real-time update