Skip to main content

Scheduled Reports API

Developer guide for implementing and extending scheduled reports functionality.

Architecture Overview​

The scheduled reports system consists of four main layers:

1. Database Layer​

  • report_templates - Pre-built templates
  • scheduled_reports - Report configurations
  • report_executions - Execution history

2. Service Layer​

  • ReportDataAggregator - Data collection
  • ReportGenerator - HTML generation
  • EmailService - Email delivery
  • ScheduledReportsService - Orchestration

3. API Layer​

  • Server Actions (app/actions/scheduled-reports.ts)
  • Cron Endpoint (app/api/cron/reports/route.ts)

4. UI Layer​

  • List page (app/dashboard/settings/reports/page.tsx)
  • Form component (components/scheduled-reports/new-report-form.tsx)

Server Actions​

All client-to-server communication uses Next.js Server Actions.

Creating a Report​

import { createScheduledReport } from '@/app/actions/scheduled-reports';

const result = await createScheduledReport({
  orgId: 'org-uuid',
  createdBy: 'user-uuid',
  name: 'Weekly Summary',
  description: 'Executive overview',
  templateType: 'executive_summary',
  dateRangeType: 'last_7_days',
  frequency: 'weekly',
  dayOfWeek: 1, // Monday
  timeOfDay: '09:00',
  timezone: 'America/New_York',
  format: 'html',
  recipients: ['user@example.com'],
  ccRecipients: ['manager@example.com'],
  includeCsv: false,
});

if (result.success) {
  console.log('Report created:', result.report);
} else {
  console.error('Error:', result.error);
}

Listing Reports​

import { getScheduledReports } from '@/app/actions/scheduled-reports';

const result = await getScheduledReports(orgId);

if (result.success) {
  result.reports?.forEach(report => {
    console.log(report.name, report.status);
  });
}

Executing a Report​

import { executeReportNow } from '@/app/actions/scheduled-reports';

const result = await executeReportNow(reportId, true); // true = send email

if (result.success) {
  console.log('Execution:', result.execution);
  console.log('Status:', result.execution?.status);
  console.log('Recipients sent:', result.execution?.recipients_sent);
}

Updating a Report​

import { updateScheduledReport } from '@/app/actions/scheduled-reports';

const result = await updateScheduledReport(reportId, {
  name: 'Updated Name',
  recipients: ['new@example.com'],
  is_active: false, // pause
});

Deleting a Report​

import { deleteScheduledReport } from '@/app/actions/scheduled-reports';

const result = await deleteScheduledReport(reportId);

Service Layer​

ReportDataAggregator​

Collects and aggregates analytics data.

import { ReportDataAggregator } from '@/lib/services/report-data-aggregator';

const aggregator = new ReportDataAggregator(
  orgId,
  {
    start_date: '2025-01-01',
    end_date: '2025-01-31',
  }
);

// Get summary statistics
const stats = await aggregator.getSummaryStats();
console.log('Total queries:', stats.metrics[0].value);

// Get trend data
const trends = await aggregator.getTrendChart();
console.log('Data points:', trends.chart.labels.length);

// Get top performers
const topQueries = await aggregator.getTopPerformers(10);
console.log('Top queries:', topQueries.items);

// Generate complete report data
const reportData = await aggregator.generateReportData([
  'summary_stats',
  'trend_chart',
  'top_performers',
  'recommendations'
]);

ReportGenerator​

Generates HTML reports from aggregated data.

import { ReportGenerator } from '@/lib/services/report-generator';

const generator = new ReportGenerator(orgId);

const report = await generator.generateReport(
  'executive_summary',
  'last_30_days'
);

console.log('HTML length:', report.html?.length);
console.log('Data:', report.data);

EmailService​

Sends reports via email.

import { emailService } from '@/lib/services/email-service';

// Check if configured
if (emailService.isConfigured()) {
  // Send report
  const result = await emailService.sendReport({
    to: ['recipient@example.com'],
    cc: ['manager@example.com'],
    subject: 'Weekly Report - Jan 2025',
    html_content: '<html>...</html>',
    pdf_attachment: {
      filename: 'report.pdf',
      url: 'https://storage.example.com/report.pdf',
    },
  });

  console.log('Sent to:', result.sent_to);
  console.log('Failed:', result.failed_to);
}

// Send test email
const testResult = await emailService.sendTestEmail('test@example.com');

ScheduledReportsService​

Main orchestration service.

import { scheduledReportsService } from '@/lib/services/scheduled-reports';

// Get templates
const templates = await scheduledReportsService.getReportTemplates();

// Execute report
const execution = await scheduledReportsService.executeReportNow(
  reportId,
  true // sendEmail
);

Database Schema​

report_templates​

CREATE TABLE report_templates (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  description TEXT,
  category TEXT NOT NULL,
  sections JSONB NOT NULL,
  default_config JSONB DEFAULT '{}',
  is_system BOOLEAN DEFAULT false,
  is_public BOOLEAN DEFAULT true,
  created_by UUID REFERENCES auth.users(id),
  org_id UUID REFERENCES organizations(id),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

scheduled_reports​

CREATE TABLE scheduled_reports (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID NOT NULL REFERENCES organizations(id),
  created_by UUID NOT NULL REFERENCES auth.users(id),
  name TEXT NOT NULL,
  description TEXT,
  template_id UUID REFERENCES report_templates(id),
  template_type TEXT NOT NULL,
  date_range_type TEXT NOT NULL,
  frequency TEXT NOT NULL,
  day_of_week INTEGER,
  day_of_month INTEGER,
  time_of_day TIME DEFAULT '09:00:00',
  timezone TEXT DEFAULT 'UTC',
  format TEXT DEFAULT 'html',
  recipients TEXT[] NOT NULL,
  cc_recipients TEXT[] DEFAULT '{}',
  include_csv BOOLEAN DEFAULT false,
  config JSONB DEFAULT '{}',
  is_active BOOLEAN DEFAULT true,
  last_sent_at TIMESTAMP WITH TIME ZONE,
  next_scheduled_at TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

report_executions​

CREATE TABLE report_executions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  report_id UUID NOT NULL REFERENCES scheduled_reports(id),
  executed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  status TEXT NOT NULL,
  report_data JSONB,
  html_content TEXT,
  pdf_url TEXT,
  csv_url TEXT,
  recipients_sent TEXT[],
  recipients_failed TEXT[],
  email_subject TEXT,
  error_message TEXT,
  error_details JSONB,
  generation_time_ms INTEGER,
  delivery_time_ms INTEGER,
  total_time_ms INTEGER,
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

Cron Job​

The automated scheduler runs every hour via Vercel Cron.

Endpoint​

// app/api/cron/reports/route.ts
export async function GET(request: Request) {
  // Verify CRON_SECRET
  const authHeader = request.headers.get('authorization');
  if (authHeader !== `Bearer ${process.env.CRON_SECRET}`) {
    return NextResponse.json({ error: 'Unauthorized' }, { status: 401 });
  }

  // Find due reports
  const { data: dueReports } = await supabase
    .from('scheduled_reports')
    .select('*')
    .eq('is_active', true)
    .lte('next_scheduled_at', now.toISOString());

  // Execute each report
  for (const report of dueReports) {
    await executeReport(report);
  }
}

Configuration​

// vercel.json
{
  "crons": [{
    "path": "/api/cron/reports",
    "schedule": "0 * * * *"
  }]
}

Testing Locally​

# Development test endpoint (no auth required)
curl http://localhost:3000/api/cron/reports/test

# Production-like test (requires CRON_SECRET)
curl -H "Authorization: Bearer your-secret" \
  http://localhost:3000/api/cron/reports

Type Definitions​

All types are defined in lib/types/scheduled-reports.ts.

Key Types​

export type ReportFrequency = 'daily' | 'weekly' | 'monthly';

export type ReportTemplateType =
  | 'executive_summary'
  | 'detailed_analytics'
  | 'competitive_intel'
  | 'content_performance'
  | 'cost_analysis'
  | 'custom';

export type ReportDateRangeType =
  | 'last_7_days'
  | 'last_30_days'
  | 'last_week'
  | 'last_month'
  | 'month_to_date'
  | 'quarter_to_date'
  | 'year_to_date'
  | 'custom';

export type ReportExecutionStatus =
  | 'pending'
  | 'generating'
  | 'sending'
  | 'success'
  | 'failed'
  | 'partial';

export interface GeneratedReportData {
  generated_at: string;
  date_range: {
    start_date: string;
    end_date: string;
    label: string;
  };
  organization: {
    id: string;
    name: string;
  };
  sections: {
    type: string;
    title: string;
    data: unknown;
  }[];
  summary: {
    total_queries: number;
    total_citations: number;
    avg_citation_rate: number;
    cost_total: number;
  };
}

Extending the System​

Adding a New Template​

  1. Create template in database:
INSERT INTO report_templates (name, description, category, sections, is_system)
VALUES (
  'Custom Template',
  'My custom template',
  'analytics',
  '[
    {"type": "summary_stats", "title": "Overview"},
    {"type": "custom_section", "title": "Custom Data"}
  ]'::jsonb,
  false
);
  1. Add section renderer in report-generator.ts:
function renderCustomSection(data: CustomSectionData): string {
  return `
    <div class="section">
      <h2 class="section-title">${data.title}</h2>
      <div class="content">
        ${data.content}
      </div>
    </div>
  `;
}
  1. Update generateHTML switch:
case 'custom_section':
  html += renderCustomSection(section.data as CustomSectionData);
  break;

Adding a New Data Source​

  1. Add method to ReportDataAggregator:
async getCustomData(): Promise<CustomData> {
  const supabase = await createClient();

  const { data } = await supabase
    .from('custom_table')
    .select('*')
    .gte('created_at', this.dateRange.start_date)
    .lte('created_at', this.dateRange.end_date);

  return { items: data || [] };
}
  1. Update generateReportData:
case 'custom_data':
  data = await this.getCustomData();
  break;

Environment Variables​

# Required for email delivery
RESEND_API_KEY=re_xxxxxxxxxxxxxxxxxxxxx

# Required for cron authentication
CRON_SECRET=random_secret_here

# Optional
RESEND_FROM_EMAIL=reports@yourdomain.com
RESEND_REPLY_TO_EMAIL=noreply@yourdomain.com

Error Handling​

All service methods return a standardized response:

{
  success: boolean;
  data?: T;
  error?: string;
}

Example error handling:

const result = await createScheduledReport(params);

if (!result.success) {
  // Handle error
  console.error('Failed:', result.error);
  toast.error(result.error);
  return;
}

// Success
console.log('Created:', result.report);

Performance Considerations​

Report Generation​

  • Average: 2-5 seconds
  • Large reports (>100 queries): 5-10 seconds
  • Use database indexes on timestamp columns
  • Aggregate data efficiently

Email Delivery​

  • 1-3 seconds per recipient
  • Send to recipients individually for tracking
  • Handle failures gracefully

Cron Execution​

  • 10-minute timeout on Vercel
  • Process reports sequentially
  • Log all executions
  • Implement retry logic for failures

Testing​

Unit Tests​

// __tests__/services/report-generator.test.ts
import { ReportGenerator } from '@/lib/services/report-generator';

describe('ReportGenerator', () => {
  it('generates HTML report', async () => {
    const generator = new ReportGenerator('org-id');
    const report = await generator.generateReport(
      'executive_summary',
      'last_7_days'
    );

    expect(report.html).toBeDefined();
    expect(report.data.sections).toHaveLength(5);
  });
});

Integration Tests​

// __tests__/api/cron/reports.test.ts
import { GET } from '@/app/api/cron/reports/route';

describe('Cron Endpoint', () => {
  it('requires authentication', async () => {
    const request = new Request('http://localhost/api/cron/reports');
    const response = await GET(request);

    expect(response.status).toBe(401);
  });
});