newsletter-campaign-workflow

Comprehensive guide for working with the AIProDaily newsletter platform's campaign workflow system, including RSS processing, article generation, multi-tenant data management, and automated publication.

Automatically activates when working with:

• Campaign creation and management
• RSS feed processing and article generation
• Workflow steps and automation
• Newsletter publication and sending
• MailerLite integration
• Multi-tenant campaign operations
• Advertorial and ad management
• Campaign status transitions

---

Multi-Tenant Structure

```

Newsletter (slug: "accounting")

→ publication_id (UUID)

→ Campaigns (daily)

→ RSS Posts (scored, assigned)

→ Articles (generated from posts)

→ Email (sent via MailerLite)

```

CRITICAL: ALL database queries MUST filter by publication_id

Issue Status Lifecycle

```

draft → processing → ready → approved → sent

↓ (if error)

failed

```

Status Meanings:

• draft: Issue created, ready for workflow
• processing: Workflow actively running
• ready: Content generated, ready for review
• approved: Manual approval for sending
• sent: Published to subscribers
• failed: Workflow error occurred

Note: "Issue" replaced "campaign" in the codebase. The issues table was formerly newsletter_campaigns.

---

Location: src/lib/workflows/process-rss-workflow.ts

Architecture: Vercel Workflows

Timeout: 800 seconds per step

Trigger: /api/cron/trigger-workflow (every 5 minutes)

Workflow Steps

1. Setup (800s)

- Create tomorrow's campaign

- Select AI apps/prompts

- Assign top 24 posts (12 primary + 12 secondary)

- Run deduplication

1. Generate Primary Titles (800s)

- Generate 6 primary headlines

3-4. Generate Primary Bodies (800s each)

- Batch 1: Generate 3 primary articles

- Batch 2: Generate 3 more primary articles

1. Fact-Check Primary (800s)

- Fact-check all 6 primary articles

- Store fact_check_score (0-10)

1. Generate Secondary Titles (800s)

- Generate 6 secondary headlines

7-8. Generate Secondary Bodies (800s each)

- Batch 1: Generate 3 secondary articles

- Batch 2: Generate 3 more secondary articles

1. Fact-Check Secondary (800s)

- Fact-check all 6 secondary articles

1. Finalize (800s)

- Auto-select top 3 per section

- Generate welcome section

- Generate subject line

- Set status to draft

- Unassign unused posts

Workflow Best Practices

✅ Error Handling Pattern:

```typescript

let retryCount = 0

const maxRetries = 2

while (retryCount <= maxRetries) {

try {

await processStep()

return // Success

} catch (error) {

retryCount++

if (retryCount > maxRetries) {

console.error('[Step X/10] Failed after retries')

throw error

}

console.log([Step X/10] Retrying (${retryCount}/${maxRetries})...)

await new Promise(resolve => setTimeout(resolve, 2000))

}

}

```

✅ Logging Pattern:

```typescript

// One-line summaries with prefixes

console.log('[Workflow] Step 1/10: Setup complete, 24 posts assigned')

console.log('[AI] Batch 1/4: Scored 3 posts, avg: 7.2')

console.error('[DB] Query failed:', error.message)

```

Log Prefixes:

• [Workflow] - Vercel Workflow orchestration
• [RSS] - RSS processing
• [AI] - OpenAI/Claude API calls
• [DB] - Database operations
• [CRON] - Cron job execution

---

1. Multi-Tenant Isolation

ALWAYS filter by publication_id:

```typescript

// ✅ CORRECT

const { data } = await supabaseAdmin

.from('articles')

.select('*')

.eq('campaign_id', campaignId)

.eq('publication_id', newsletterId) // REQUIRED

// ❌ WRONG - Data leakage!

const { data } = await supabaseAdmin

.from('articles')

.select('*')

.eq('campaign_id', campaignId)

```

2. Date/Time Handling

NEVER use UTC conversions for date comparisons:

```typescript

// ✅ CORRECT: Local date comparison

const dateStr = date.split('T')[0] // "2025-01-07"

const today = new Date().toISOString().split('T')[0]

if (dateStr === today) { / ... / }

// ❌ FORBIDDEN: UTC conversion shifts dates

date.toISOString() // Wrong timezone!

date.toUTCString() // Breaks comparisons!

```

Why: UTC conversion shifts dates by timezone. Users expect Central Time.

3. Performance & Limits

Hard Limits (Vercel):

• Workflow step timeout: 800 seconds (13 minutes per step)
• API route timeout: 600 seconds (10 minutes max)
• Log size: 10MB maximum
• Memory: 1024MB default

---

Standard Pattern: callAIWithPrompt()

Location: src/lib/openai.ts

```typescript

import { callAIWithPrompt } from '@/lib/openai'

const result = await callAIWithPrompt(

'ai_prompt_primary_article_title', // Key in app_settings

newsletterId,

{

title: post.title,

description: post.description,

content: post.full_article_text

}

)

// result = { headline: "Your Generated Title" }

```

How it works:

1. Loads complete JSON prompt from app_settings table
2. Replaces placeholders (e.g., {{title}}, {{content}})
3. Calls AI API (OpenAI or Claude)
4. Returns parsed JSON response

Prompt Storage Format

```sql

INSERT INTO app_settings (key, value, publication_id, ai_provider)

VALUES (

'ai_prompt_primary_article_title',

'{

"model": "gpt-4o",

"temperature": 0.7,

"max_output_tokens": 500,

"response_format": { "type": "json_schema", "json_schema": {...} },

"messages": [

{"role": "system", "content": "You are a headline writer..."},

{"role": "user", "content": "Title: {{title}}\n\nWrite a headline."}

]

}',

'newsletter-uuid',

'openai'

);

```

All parameters stored in database, not hardcoded.

---

Note: "Issues" replaced "campaigns" in the database. The table issues was formerly newsletter_campaigns.

```

publications (formerly newsletters)

├── issues (status: draft → processing → ready → sent)

│ ├── issue_articles (primary section, 6 generated, 3 active)

│ ├── secondary_articles (secondary section, 6 generated, 3 active)

│ └── rss_posts (assigned posts)

│ └── post_ratings (multi-criteria scores)

│

├── rss_feeds (active/inactive, section assignment)

├── publication_settings (key-value config, scoped by publication_id)

├── advertisements (advertorials for rotation)

├── issue_advertisements (tracks ad usage per issue)

└── archived_articles, archived_rss_posts (historical data)

```

---

```typescript

// app/api/[feature]/route.ts

import { NextRequest, NextResponse } from 'next/server'

import { supabaseAdmin } from '@/lib/supabase'

export async function POST(request: NextRequest) {

try {

const body = await request.json()

if (!body.campaignId) {

return NextResponse.json(

{ error: 'Missing campaignId' },

{ status: 400 }

)

}

const result = await processData(body)

return NextResponse.json({ data: result })

} catch (error: any) {

console.error('[API] Error:', error.message)

return NextResponse.json(

{ error: 'Internal server error' },

{ status: 500 }

)

}

}

export const maxDuration = 600 // 10 minutes for long operations

```

---

Create New Campaign

```typescript

const { data: campaign } = await supabaseAdmin

.from('newsletter_campaigns')

.insert({

publication_id: newsletterId,

date: tomorrowDate, // YYYY-MM-DD format

status: 'draft',

subject_line: null

})

.select()

.single()

```

Assign RSS Posts to Campaign

```typescript

await supabaseAdmin

.from('rss_posts')

.update({

campaign_id: campaignId,

assigned_at: new Date().toISOString(),

section: 'primary' // or 'secondary'

})

.in('id', topPostIds)

.eq('publication_id', newsletterId) // REQUIRED

```

Generate Article Content

```typescript

const result = await callAIWithPrompt(

'ai_prompt_primary_article_body',

newsletterId,

{

title: post.title,

description: post.description,

content: post.full_article_text

}

)

await supabaseAdmin

.from('articles')

.insert({

campaign_id: campaignId,

publication_id: newsletterId, // REQUIRED

rss_post_id: post.id,

headline: result.headline,

article_text: result.body,

fact_check_score: null,

is_active: false

})

```

---

Configuration: vercel.json

Active Crons

| Cron | Schedule | Purpose |

|------|----------|---------|

| /api/cron/trigger-workflow | Every 5 min | Trigger RSS workflow if scheduled |

| /api/cron/ingest-rss | Every 15 min | Fetch & score new RSS posts |

| /api/cron/create-campaign | Every 5 min | Create issue if schedule permits |

| /api/cron/send-review | Every 5 min | Send review emails (status: ready) |

| /api/cron/send-final | Every 5 min | Send final issues (status: approved) |

| /api/cron/send-secondary | Every 5 min | Send secondary newsletter |

| /api/cron/monitor-workflows | Every 5 min | Check for failed/stuck workflows |

| /api/cron/process-mailerlite-updates | Every 5 min | Process MailerLite webhooks |

| /api/cron/cleanup-pending-submissions | Daily 7 AM | Clear stale ad submissions |

| /api/cron/import-metrics | Daily 6 AM | Sync MailerLite metrics |

| /api/cron/health-check | Every 5 min (8AM-10PM) | System health check |

Not Implemented (registered in vercel.json but empty)

| Cron | Schedule | Notes |

|------|----------|-------|

| /api/cron/populate-events | Every 5 min | Events system not implemented |

| /api/cron/sync-events | Daily midnight | Events system not implemented |

| /api/cron/generate-weather | Daily 8 PM | Route file missing |

| /api/cron/collect-wordle | Daily 7 PM | Route file missing |

---

Campaign Stuck in "processing"

```sql

-- Check workflow status

SELECT id, status, date, created_at, updated_at

FROM newsletter_campaigns

WHERE status = 'processing'

AND publication_id = 'your-newsletter-id'

ORDER BY created_at DESC;

-- Reset to draft (if needed)

UPDATE newsletter_campaigns

SET status = 'draft'

WHERE id = 'campaign-id'

AND publication_id = 'your-newsletter-id';

```

Posts Not Scoring

1. Check RSS ingestion: /api/cron/ingest-rss logs
2. Verify criteria config: SELECT * FROM app_settings WHERE key LIKE 'criteria_%' AND publication_id = ?
3. Check prompts exist: SELECT * FROM app_settings WHERE key LIKE 'ai_prompt_criteria_%' AND publication_id = ?
4. Verify feeds active: SELECT * FROM rss_feeds WHERE active = true AND publication_id = ?

Workflow Failures

1. Check Vercel logs: vercel logs --since 1h
2. Check workflow monitor cron: /api/cron/monitor-workflows
3. Look for timeout errors (step > 800s)
4. Check retry count in logs

---

See claude.md in project root for:

• Complete workflow details
• Multi-criteria scoring system
• RSS feed management
• MailerLite integration
• Advertorial rotation
• Section management

---

Skill Status: ACTIVE ✅

Line Count: < 500 (following best practices) ✅

Project-Specific: Tailored for AIProDaily tech stack ✅