Build a Keyword Rank Tracker

Cron Job (daily 3am UTC)
    ↓
Rank Checker Worker
    ├── SerpAPI / ValueSERP calls (batched)
    └── Parse rank for target URL in results
         ↓
Prisma: store RankSnapshot
         ↓
Compare vs yesterday → trigger alerts if change ≥ 5
         ↓
Resend: alert email to client

// schema.prisma
model Project {
  id       String    @id @default(cuid())
  name     String
  domain   String    // e.g. "example.com"
  clientEmail String
  keywords Keyword[]
  competitors ProjectCompetitor[]
  createdAt DateTime @default(now())
}

model Keyword {
  id        String   @id @default(cuid())
  projectId String
  project   Project  @relation(fields: [projectId], references: [id], onDelete: Cascade)
  keyword   String
  targetUrl String   // specific URL to track
  country   String   @default("us")
  language  String   @default("en")
  device    String   @default("desktop") // desktop | mobile
  snapshots RankSnapshot[]
  createdAt DateTime @default(now())

  @@index([projectId])
}

model RankSnapshot {
  id        String   @id @default(cuid())
  keywordId String
  keyword   Keyword  @relation(fields: [keywordId], references: [id], onDelete: Cascade)
  position  Int?     // null = not in top 100
  url       String?  // actual ranking URL (may differ from target)
  date      DateTime @default(now())
  checkedAt DateTime @default(now())

  @@index([keywordId, date])
}

model ProjectCompetitor {
  id        String  @id @default(cuid())
  projectId String
  project   Project @relation(fields: [projectId], references: [id], onDelete: Cascade)
  domain    String
}

// workers/rankChecker.ts
import { prisma } from '@/lib/prisma'

const SERP_API_KEY = process.env.SERPAPI_KEY!
const BATCH_SIZE = 10 // process 10 keywords at a time

export async function runDailyRankCheck() {
  const keywords = await prisma.keyword.findMany({
    include: { project: true }
  })

  console.log(`Checking ${keywords.length} keywords...`)

  // Process in batches to respect API rate limits
  for (let i = 0; i < keywords.length; i += BATCH_SIZE) {
    const batch = keywords.slice(i, i + BATCH_SIZE)
    await Promise.all(batch.map(checkKeyword))
    // Small delay between batches
    if (i + BATCH_SIZE < keywords.length) {
      await sleep(1000)
    }
  }

  console.log('Rank check complete. Running alerts...')
  await runAlerts()
}

async function checkKeyword(keyword: any) {
  try {
    const result = await fetchSerp(keyword.keyword, keyword.country, keyword.language, keyword.device)
    const position = findPosition(result.organic_results, keyword.targetUrl, keyword.project.domain)

    await prisma.rankSnapshot.create({
      data: {
        keywordId: keyword.id,
        position,
        url: position ? findUrl(result.organic_results, keyword.project.domain) : null
      }
    })
  } catch (err) {
    console.error(`Failed to check keyword ${keyword.id}:`, err)
  }
}

async function fetchSerp(query: string, country: string, language: string, device: string) {
  const params = new URLSearchParams({
    q: query,
    gl: country,
    hl: language,
    device,
    num: '100',
    api_key: SERP_API_KEY
  })

  const res = await fetch(`https://serpapi.com/search.json?${params}`)
  return res.json()
}

function findPosition(results: any[], targetUrl: string, domain: string): number | null {
  for (let i = 0; i < results.length; i++) {
    const link: string = results[i].link ?? ''
    if (link.includes(domain) || link.includes(targetUrl)) {
      return i + 1
    }
  }
  return null
}

// workers/alerts.ts
import { prisma } from '@/lib/prisma'
import { Resend } from 'resend'

const resend = new Resend(process.env.RESEND_API_KEY!)
const ALERT_THRESHOLD = 5

export async function runAlerts() {
  const keywords = await prisma.keyword.findMany({
    include: {
      project: true,
      snapshots: {
        orderBy: { checkedAt: 'desc' },
        take: 2  // today and yesterday
      }
    }
  })

  const alerts: Record<string, any[]> = {} // clientEmail → alerts

  for (const kw of keywords) {
    if (kw.snapshots.length < 2) continue

    const today = kw.snapshots[0].position
    const yesterday = kw.snapshots[1].position

    if (today === null || yesterday === null) continue

    const change = yesterday - today // positive = improved (lower number is better)

    if (Math.abs(change) >= ALERT_THRESHOLD) {
      const email = kw.project.clientEmail
      if (!alerts[email]) alerts[email] = []
      alerts[email].push({
        keyword: kw.keyword,
        domain: kw.project.domain,
        yesterday,
        today,
        change,
        improved: change > 0
      })
    }
  }

  // Send one email per client (batch alerts)
  for (const [email, keywordAlerts] of Object.entries(alerts)) {
    await sendAlertEmail(email, keywordAlerts)
  }
}

async function sendAlertEmail(clientEmail: string, alerts: any[]) {
  const improved = alerts.filter(a => a.improved)
  const dropped = alerts.filter(a => !a.improved)

  await resend.emails.send({
    from: 'ranks@yourseo.com',
    to: clientEmail,
    subject: `Rank Alert: ${alerts.length} keyword${alerts.length !== 1 ? 's' : ''} moved significantly`,
    html: `
      <h2>Keyword Position Alerts</h2>
      ${improved.length > 0 ? `
        <h3>🟢 Improved (${improved.length})</h3>
        <ul>${improved.map(a => `<li><strong>${a.keyword}</strong>: #${a.yesterday} → #${a.today} (+${a.change})</li>`).join('')}</ul>
      ` : ''}
      ${dropped.length > 0 ? `
        <h3>🔴 Dropped (${dropped.length})</h3>
        <ul>${dropped.map(a => `<li><strong>${a.keyword}</strong>: #${a.yesterday} → #${a.today} (${a.change})</li>`).join('')}</ul>
      ` : ''}
    `
  })
}

// app/api/keywords/[id]/history/route.ts
export async function GET(req: Request, { params }: { params: { id: string } }) {
  const { searchParams } = new URL(req.url)
  const days = Number(searchParams.get('days') ?? 30)

  const since = new Date()
  since.setDate(since.getDate() - days)

  const snapshots = await prisma.rankSnapshot.findMany({
    where: {
      keywordId: params.id,
      checkedAt: { gte: since }
    },
    orderBy: { checkedAt: 'asc' },
    select: { position: true, checkedAt: true }
  })

  return Response.json({
    data: snapshots.map(s => ({
      date: s.checkedAt.toISOString().split('T')[0],
      position: s.position
    }))
  })
}

// app/api/cron/rank-check/route.ts
// Vercel cron or external cron hitting this endpoint daily
export async function GET(req: Request) {
  const authHeader = req.headers.get('authorization')
  if (authHeader !== `Bearer ${process.env.CRON_SECRET}`) {
    return new Response('Unauthorized', { status: 401 })
  }

  // Run async, respond immediately
  runDailyRankCheck().catch(console.error)

  return Response.json({ started: true, timestamp: new Date() })
}

{
  "crons": [{ "path": "/api/cron/rank-check", "schedule": "0 3 * * *" }]
}