Scheduled report delivery and exports

Context

Reports that come to you: ReportSchedule (#9) drives recurring email delivery, and exports produce CSV/XLSX files via ts-spreadsheets (~/Code/Libraries/ts-spreadsheets). No PDF dependency exists in our library set, so delivery is a designed HTML email summary + link (and attachments) - not a half-baked PDF.

Scope

• Scheduling: app/Scheduler.ts + a queued job scans due ReportSchedule rows (cadence, hour, timezone-correct) and dispatches per-schedule delivery jobs; failures retry with backoff and surface on the schedule row (last_run_at, last_status).
• Email: an app/Mail/ report delivery mail using the email framework's stx templates - report name, period, per-block headline numbers (BigNumber values and deltas computed via #10), and a deep link to the viewer (#13). Design the email properly (tokens from #3 translated to email-safe styles); test in the buddy email:* tooling.
• Exports: CSV and XLSX generation from a report's block result sets via ts-spreadsheets (one sheet/section per block, typed columns, currency formats). Improve ts-spreadsheets upstream if formatting gaps appear (bun run release:patch).
• On-demand export from the viewer (#13): ReportExport row, queued generation, stored via @stacksjs/storage, expiring signed download; UI shows progress and history.
• Schedule management UI on the report: create/edit/pause schedules, recipients (must be project members or explicitly confirmed addresses - no open relay), format choice.
• Plan gating hooks: schedules and XLSX are tier-gated; enforce through the #18 limits API (soft-fail with an upgrade prompt, never a 500).
• Feature tests: due-schedule scan correctness across timezones/DST, email content numbers match engine output, export file correctness (parse the XLSX back and assert), gating.

Acceptance

• A daily schedule on the seeded report delivers a correct email (inspected via the local email tooling) and a downloadable XLSX whose numbers match the viewer.

Depends on #9, #10, #13, #18. Part of #1 (M7 Launch). Ground rules: see #1.

Substantially built in 2bbc357. CI green, 454 tests. Leaving open for the UI and on-demand export listed at the end.

Scheduling. An hourly job finds due schedules, and the whole difficulty is timezones. A schedule stored as a UTC hour drifts by an hour twice a year, and a report that starts arriving at seven instead of eight is annoying rather than obviously broken, so it gets tolerated rather than reported. The hour is compared in the schedule own zone using the runtime timezone database, with no offset arithmetic anywhere. Two cases have tests of their own: the same local hour still fires after the clocks change, and an hour that happens twice on the day they go back does not send twice, which comparing local dates rather than subtracting hours is what makes safe.

last_run_at is what makes a re-run safe, so the job can be retried or catch up after an outage without anybody receiving the same report twice. Every outcome is recorded on the row, including a refusal, so somebody can see why their report did not arrive without reading a log. Recipients are filtered to things that are actually addresses, since one stray fragment handed to a mail server fails the send for the good ones too.

Email. Numbers, not a picture of them: a chart image is blocked by default in most clients, and a report arriving as a grey box with a broken-image icon is worse than four numbers somebody can read on a phone. The headlines come from the report big-number blocks in page order, because those are the ones somebody already decided were the headline.

Exports are long format - one row per point with the block name in a column - rather than a sheet per block. ts-spreadsheets models a spreadsheet as one headings array, so a sheet per block is not expressible, and a report is heterogeneous by design: a big number, a funnel and a daily line share no columns, so a wide format would be mostly empty cells that change shape whenever somebody adds a block. Tests parse the CSV back to the numbers that went in and assert the xlsx is a real workbook.

Gating is soft, through #18. A plan without schedules stops sending and records why rather than erroring, and asking for XLSX on a plan without it delivers CSV instead of refusing to deliver at all.

Verified against the live mail server: a scheduled delivery arrived with the xlsx attached and the right MIME type, and was correctly refused first on a plan without schedules.

The acceptance criterion found a real bug, which is the most useful thing to come out of this issue. It asks that the email numbers match the viewer. They did not: 138.69 against 138.20, same report, same range, same moment. The viewer read report.timezone; the reports table has no such column, so it silently resolved to UTC for every report on the instance while the project was Europe/Lisbon. Day buckets, period boundaries and every comparison were being computed in the wrong zone, and the numbers looked plausible enough that nothing ever complained. Both views now read the project timezone and the two agree. It is in AGENTS.md.

Not done.

• Schedule management UI on the report. The scan, delivery, gating and recording all work and are tested; nothing renders them, so a schedule has to be inserted by hand today.
• On-demand export from the viewer: the ReportExport row, queued generation, storage and an expiring signed download. exportCsv/exportXlsx are ready to be called; the queueing and storage half is not built.
• Recipient confirmation. Recipients are validated as addresses but not checked against project membership, so this is not yet the no-open-relay guarantee the scope asks for. Worth doing before any schedule UI ships.

Schedule management shipped in a622738, along with the no-open-relay rule I flagged as outstanding above. CI green, 460 tests.

Recipients must be people on the project, checked on every write rather than only on create - an address added by editing would otherwise skip the check, which is the whole of the problem. Without it a scheduled report is an open relay: anyone with a free account could point a daily schedule at a stranger and have our server deliver it with our domain reputation behind it. Members can already open the report in the app, so mailing it to them tells them nothing they could not read anyway.

The refusal names the addresses to remove, because "some recipients are not allowed" leaves somebody guessing which of six to delete. The form offers a picker of members rather than a text field, so the rule is a fact rather than a validation error met afterwards.

A schedule takes the project timezone, not the browser one - a laptop in another country must not silently reschedule everybody else report. Monthly days are capped at 28 so a schedule cannot pick a day February does not have and quietly never run.

The list shows the last run and its status including a refusal, so a failing schedule says so where somebody looks rather than only in a log.

Verified in a browser end to end: created a schedule that stored Europe/Lisbon, paused it, confirmed the pause removed it from the due scan, and watched a Free account be told "Scheduled delivery is part of Hobby" in the panel.

Still open on this issue: on-demand export from the viewer. exportCsv/exportXlsx are ready to call, but the ReportExport row, queued generation, storage and an expiring signed download are not built - that is the last piece.

Done in 597e7f4, completing the work from 2bbc357 and a622738. CI green, 469 tests.

On-demand export from the viewer was the last piece: CSV and XLSX, with a signed expiring download link.

Generated synchronously rather than queued, which is a deliberate departure from the scope. An export of a report this shape is a handful of engine queries and a few thousand rows - milliseconds. A queue for a millisecond operation is a moving part that can be down while the thing it protects cannot, and it turns an immediate download into a spinner and a polling loop. The ReportExport row still carries a status, so the day a report is large enough to need a worker, that is a change of who calls generateExport rather than a migration. Say the word if you would rather have the worker regardless.

The link is signed and expiring rather than a guessable path, because an export is a snapshot of somebody business numbers sitting on disk and a URL that stays valid forever is one that ends up in a chat log. The signature covers the expiry, so a lifetime cannot be extended by editing the URL, and it is bound to one export, so a valid link opens no other. Compared with timingSafeEqual, since byte-by-byte comparison on an HMAC leaks how much of a forgery was right.

A session is still required - the route sits under the auth registry and project access is checked as well. That is narrower than "signed download" implies, and better here: nothing needs to email a link, because a scheduled delivery attaches the file itself.

Expired exports and their files are pruned by the existing daily job.

Everything else on this issue landed earlier: the timezone-correct due scan with its DST cases, delivery with headline numbers and attachments, long-format CSV/XLSX, plan gating that degrades XLSX to CSV rather than refusing to deliver, the management UI, and membership-only recipients.

Verified over HTTP: the download returns the right content type and disposition with Cache-Control: no-store, the CSV opens with the expected numbers, and a forged signature 404s.

Worth repeating from above, since it is the most useful thing this issue produced: the acceptance criterion that email numbers match the viewer found a real bug. The viewer read report.timezone, the reports table has no such column, and every chart on the instance was silently bucketed in UTC instead of the project zone.