v1.0
Powered by

# Scheduled Reports

Scheduled reports allow organizations to automatically receive CSV exports of shared analytics links via Email and Telegram at custom intervals.

# Overview 

flowchart TB
    subgraph Producer["notifications-worker"]
        CRON[Cron: Every 5 min]
        API[API: CRUD schedules]
        SRC[scheduledReportsCron]
    end

    subgraph Queue["Cloudflare Queue"]
        NQ[NOTIFICATIONS_QUEUE]
    end

    subgraph Consumer["notifications-consumer"]
        SRS[sharedReportService]
        CSV[Generate CSV]
        EMAIL[Loops API]
        TG[Telegram API]
    end

    subgraph DB[(Supabase)]
        SCHED[shared_report_schedules]
        RECIPIENTS[shared_report_schedule_recipients]
        HISTORY[shared_report_notifications]
        LINKS[shared_links]
        VIDEOS[videos_with_statistics_fast]
    end

    CRON --> SRC
    SRC -->|Find due| SCHED
    SRC -->|Enqueue| NQ
    API --> SCHED

    NQ --> SRS
    SRS -->|Get filters| LINKS
    SRS -->|Query| VIDEOS
    SRS --> CSV
    CSV --> EMAIL
    CSV --> TG
    EMAIL --> HISTORY
    TG --> HISTORY

# How It Works

# 1. Schedule Creation

Users create schedules via API with:

  • shared_link_id - Which shared analytics view to export
  • cadence - daily, weekly, or monthly
  • scheduled_time - Time in HH:MM format (stored as UTC)
  • timezone - IANA timezone (e.g., Europe/Moscow)
  • send_email / send_telegram - Delivery channels
  • recipients - Optional per-schedule recipients

# 2. Cron Check (Every 5 Minutes)

scheduledReportsCron queries for due schedules:

SELECT * FROM shared_report_schedules
WHERE enabled = true
AND next_run_at <= NOW()

# 3. Job Enqueue

For each due schedule:

  1. Get recipients (schedule-specific or fallback to org-wide)
  2. Enqueue shared_report job to NOTIFICATIONS_QUEUE
  3. Update next_run_at using cron expression

# 4. Consumer Processing

sharedReportService in consumer:

  1. Fetches latest filters from shared_links
  2. Queries videos_with_statistics_fast with filters
  3. Paginates through all matching videos (1000 per page)
  4. Generates CSV with columns: title, video_url, platform, status, campaign_tag, channel_username, published_at, created_at, views, likes, comments, reposts
  5. Sends via Email (Loops) and/or Telegram (sendDocument)
  6. Logs delivery to shared_report_notifications

# Database Schema 

erDiagram
    shared_report_schedules ||--o{ shared_report_schedule_recipients : has
    shared_report_schedules ||--o{ shared_report_notifications : generates
    shared_links ||--o| shared_report_schedules : "scheduled by"
    users ||--o{ shared_report_schedule_recipients : receives

    shared_report_schedules {
        uuid id PK
        uuid organization_id FK
        uuid shared_link_id FK
        uuid created_by FK
        string cron_expression
        string timezone
        string scheduled_time
        boolean send_email
        boolean send_telegram
        boolean enabled
        timestamp next_run_at
        timestamp last_run_at
        string last_run_status
        timestamp created_at
        timestamp updated_at
    }

    shared_report_schedule_recipients {
        uuid id PK
        uuid schedule_id FK
        string channel "email or telegram"
        uuid user_id FK
        bigint telegram_chat_id
    }

    shared_report_notifications {
        uuid id PK
        uuid schedule_id FK
        string channel
        uuid recipient_user_id
        bigint telegram_chat_id
        string status "sent or failed"
        jsonb payload
        timestamp created_at
    }

# Recipients Resolution 

1. Check schedule-specific recipients (shared_report_schedule_recipients)
   ↓ if none found
2. Fallback to organization-wide recipients
   - organization_notification_email_recipients
   - organization_notification_telegram_recipients

# Cron Expression Generation

Cadence Generated Cron Description
daily 0 {HH} * * * Every day at HH:00 UTC
weekly 0 {HH} * * 1 Every Monday at HH:00 UTC
monthly 0 {HH} 1 * * 1st of month at HH:00 UTC

Custom cron_expression can be provided instead of cadence.

# CSV Format 

title,video_url,platform,status,campaign_tag,channel_username,published_at,created_at,views,likes,comments,reposts
"Video Title","https://...","tiktok","active","Campaign A","@creator","2024-01-01","2024-01-01",10000,500,100,50

Filename format: {slug}-{YYYY-MM-DD}.csv

# Error Handling

Stage Behavior
Cron check failure Logged to Sentry, cron continues
Individual schedule failure last_run_status: 'failed', next run scheduled
Consumer CSV generation failure Job retried via queue
Email send failure Logged to shared_report_notifications with status: 'failed'
Telegram send failure Logged to shared_report_notifications with status: 'failed'

# Files Reference

File Purpose
notifications-worker/src/services/scheduledReportsCron.ts Cron handler
notifications-worker/src/endpoints/report-schedules/handler.ts API endpoints
notifications-worker/src/utils/cronHelpers.ts Cron parsing utilities
notifications-consumer/src/services/sharedReportService.ts CSV generation + delivery

© Influtics