# Scheduled Report Delivery

**Date:** 2026-06-01
**Status:** Draft
**Spec:** 174
**Tier:** Business+
**Depends on:** `admin-reports-analytics`, `financial-statements`, `israeli-tax-reports`, `system-communications-notifications`, `foundation-auth-rbac`
**Referenced by:** `admin-reports-analytics`

---

## Overview

Automated delivery of reports on a schedule — weekly P&L summaries, monthly VAT reports, or any other report type sent to configured recipients (staff members or external email addresses). Reports are generated at schedule time and delivered as PDF or Excel attachments via email.

---

## Data Model

```sql
CREATE TABLE report_schedules (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id       UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  created_by      UUID NOT NULL REFERENCES users(id) ON DELETE SET NULL,
  name            TEXT NOT NULL,                  -- e.g. "Monthly P&L to management"
  report_type     TEXT NOT NULL,
    -- 'revenue' | 'expenses' | 'pl' | 'cashflow' | 'vat' | 'time' | 'profitability'
    -- | 'annual_summary' | 'bad_debt' | 'leads' | 'proposals'
  format          TEXT NOT NULL DEFAULT 'xlsx',   -- 'xlsx' | 'pdf'
  frequency       TEXT NOT NULL,
    -- 'daily' | 'weekly' | 'monthly' | 'quarterly'
  day_of_week     INTEGER,                        -- 0–6 (for weekly; 0=Sunday)
  day_of_month    INTEGER,                        -- 1–28 (for monthly/quarterly)
  time_of_day     TIME NOT NULL DEFAULT '08:00',  -- local time (tenant timezone)
  period_type     TEXT NOT NULL DEFAULT 'previous',
    -- 'previous' = previous period (e.g. last month for monthly schedule)
    -- 'current'  = current period (month-to-date)
    -- 'ytd'      = year-to-date
  report_params   JSONB NOT NULL DEFAULT '{}',    -- extra filters (e.g. { project_id, user_id })
  recipients      JSONB NOT NULL DEFAULT '[]',
    -- [{ type: 'user', user_id: uuid }, { type: 'email', email: 'cfo@company.com' }]
  is_active       BOOLEAN NOT NULL DEFAULT true,
  last_run_at     TIMESTAMPTZ,
  next_run_at     TIMESTAMPTZ NOT NULL,           -- pre-computed
  created_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_report_schedules_tenant ON report_schedules(tenant_id, is_active, next_run_at);
```

---

## Features

### Scheduled Reports List (`/reports/scheduled`)

```
┌──────────────────────────────────────────────────────────────┐
│  Scheduled Reports                         [+ New schedule]  │
│                                                              │
│  Name                     Frequency  Format  Next run        │
│  ─────────────────────────────────────────────────────────── │
│  Monthly P&L              Monthly    Excel   Jul 01, 08:00   │
│  Weekly revenue summary   Weekly     PDF     Jun 08, 08:00   │
│  VAT report — bimonthly   Monthly    Excel   Jun 30, 09:00   │
│                                                              │
│  [Edit]  [Pause]  [Run now]  [Delete]                        │
└──────────────────────────────────────────────────────────────┘
```

### Create / Edit Schedule Modal

```
┌──────────────────────────────────────────────────────────────┐
│  New scheduled report                               [✕]      │
│                                                              │
│  Name *           [Monthly P&L to management_________]       │
│  Report type *    [Profit & Loss ▾]                          │
│  Format           ● Excel  ○ PDF                             │
│                                                              │
│  Frequency *      [Monthly ▾]                                │
│  Send on          [1st ▾] of each month at [08:00__]         │
│  Period           [Previous month ▾]                         │
│                   Previous period · Current period · YTD     │
│                                                              │
│  Recipients *                                                │
│  ☑ Alex (owner)   ☑ Dana (admin)   ☐ Yossi (member)         │
│  + Add external email:  [cfo@client.com___] [+ Add]          │
│                                                              │
│  [Cancel]                         [Create schedule]          │
└──────────────────────────────────────────────────────────────┘
```

### Report generation cron

Runs every 15 minutes; processes any `report_schedules` WHERE `next_run_at <= NOW()` AND `is_active = true`:

```ts
// apps/zync-api/src/cron/scheduled-reports.ts
export async function runScheduledReports(env: Env): Promise<void> {
  const due = await db.query(`
    SELECT * FROM report_schedules
    WHERE is_active = true AND next_run_at <= NOW()
    LIMIT 50
  `)

  for (const schedule of due) {
    // 1. Compute period dates from period_type + frequency + last_run_at
    const period = computeReportPeriod(schedule)

    // 2. Generate report UNDER THE CREATOR'S PERMISSION SCOPE — not raw tenant scope.
    //    Load the creator's current role + field-level permissions (spec 121) and run the
    //    report through the same authorization context as if they exported it manually.
    const ctx = await buildPermissionContext(schedule.tenant_id, schedule.created_by)
    if (!ctx || !ctx.can('reports:export') || !ctx.canRunReport(schedule.report_type)) {
      // Creator was removed or lost access → disable schedule + notify OWNER/ADMIN, skip delivery.
      await disableScheduleAndAlert(schedule, 'creator_lost_access', env)
      continue
    }
    const reportData = await generateReport(schedule.report_type, period, schedule.report_params, ctx)

    // 3. Render to file (Excel or PDF)
    const file = await renderToFile(reportData, schedule.format, schedule.report_type, period)

    // 4. Send to each recipient via email
    for (const recipient of schedule.recipients) {
      await sendReportEmail(recipient, file, schedule, period, env)
    }

    // 5. Update last_run_at + compute next_run_at
    await db.query(`
      UPDATE report_schedules
      SET last_run_at = NOW(), next_run_at = $1
      WHERE id = $2
    `, [computeNextRun(schedule), schedule.id])
  }
}
```

### "Run now" action

Triggers an immediate one-off run of the schedule (same generation + delivery flow). Useful for testing or on-demand delivery.

---

## Email Delivery

Report emails use the tenant's configured email adapter (Resend or custom SMTP):

**Subject:** `[Zync] {report_name} — {period label}` (e.g. `[Zync] Monthly P&L — June 2026`)

**Body:**
```
Hi {name},

Your scheduled {report_type} report for {period} is attached.

Generated by Zync on {date} at {time}.

Manage your scheduled reports: https://app.zync.is/reports/scheduled

— The Zync team
```

**Attachment:** `{report_type}-{period}.xlsx` or `.pdf`

---

## API

```
GET  /api/reports/scheduled              → list schedules for tenant
POST /api/reports/scheduled              → create schedule
GET  /api/reports/scheduled/:id          → schedule detail
PATCH /api/reports/scheduled/:id         → edit schedule
DELETE /api/reports/scheduled/:id        → delete schedule
POST /api/reports/scheduled/:id/pause    → pause (is_active → false)
POST /api/reports/scheduled/:id/resume   → resume (recalculates next_run_at)
POST /api/reports/scheduled/:id/run      → trigger immediate one-off run
     Response: 202 Accepted (async delivery)
```

**Permissions (this is a data-exfiltration surface — financial reports to arbitrary external emails):**
- View (`GET`): `reports:read`.
- Create/edit/delete + `run`: require **`reports:export`** (not generic `settings:write`). A user can only schedule a `report_type` they are themselves allowed to export — enforce `ctx.canRunReport(report_type)` at create time, mirroring the cron check.
- **Adding an external (non-user) recipient** requires `reports:export_external` (held by OWNER/ADMIN by default; grantable per role). Internal user recipients must be members of the tenant.
- Every create/edit and every recipient-list change is written to the audit log (spec 28) with before/after recipients.
- The generated report is always scoped to the creator's permission context at run time (see cron step 2); if the creator loses access, the schedule auto-disables.

---

## Architecture Decisions

| Decision | Choice | Reason |
|----------|--------|--------|
| Cron every 15 minutes | Not exact-time cron | CF Cron Triggers support minute-level scheduling but not arbitrary exact times; 15-minute polling ± 15min delivery is acceptable for scheduled reports |
| `next_run_at` pre-computed | Not compute on cron run | Single WHERE query to find due schedules is O(1) with index; computing "should this schedule run now?" for all schedules on every cron is O(n) |
| Recipients include external emails | Not users-only, but gated | Sending to a CFO/accountant without Zync access is common — but external recipients require `reports:export_external` and creation requires `reports:export`, because an ungated schedule is a financial-data exfiltration channel |
| Generate under creator scope | Not raw tenant scope | A schedule must never expose more than its creator could export manually; field-level permissions (spec 121) and role gates apply at run time, and the schedule disables if the creator loses access |
| Attachment not link | Not download link | Recipients may be external (no Zync account); link would require auth or time-limited URL with extra infrastructure; attachment is simpler and universally accessible |
| Business+ only | Not Freelancer | Scheduled reporting is an automation feature for multi-user teams; Freelancer tier is adequately served by manual report export |