feat(reports): scheduled report delivery worker (PMS-478) #348
No reviewers
Labels
No labels
No milestone
No project
No assignees
1 participant
Notifications
Due date
No due date set.
Dependencies
No dependencies set
Reference
psa-systems/mokosh-server!348
Loading…
Reference in a new issue
No description provided.
Delete branch "feat/PMS-478-scheduled-reports"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
PMS-457 phase 1 + PMS-477 phase 2 made saved reports definable and executable on demand. Phase 3 closes the loop: a user can mark a saved report "deliver this weekly to my email" and a background worker materialises it on the cron tick, dispatching via the existing notifications queue so SMTP retry / backoff are reused rather than re-implemented.
What landed:
Migration
075_scheduled_reports.sqladds thescheduled_reportstable:(id, tenant_id, saved_report_id, user_id, cron_expr, channel='email', format='csv', recipient_email, is_active, last_run_at, next_run_at, last_error, created_at, updated_at). Thechannel+formatcolumns ship with CHECK constraints scoped to today's surface (email + csv only) but are wide enough to host future SMS / PDF without a schema migration. The hot-path indexidx_scheduled_reports_next_run_activeis partial onis_active = trueso a tenant that disables every schedule keeps its rows out of the worker scan.DTOs in
saved_reports/models.rs:ScheduledReportResponse,CreateScheduledReportRequest,UpdateScheduledReportRequest. The cron expression is validated via the existingutils::validation::validate_cron(cron crate, 6-field form with seconds); the recipient override is validated as an email.Service additions in
saved_reports/service.rs:schedule_createruns the existinggetvisibility check first so a private report can only be scheduled by its owner;schedule_get/list/update/deleteround out the CRUD with owner-only mutation;compute_next_runexposes the cron-advance helper to both the service (on create / update) and the worker (after each tick).New
saved_reports/worker.rs:ScheduledReportsWorkerimplementsJoband ticks every 60s. Each tick locks up to 10 due rows viaSELECT ... FOR UPDATE SKIP LOCKED, materialises each report through the existing executor atper_page = 10_000, renders the result set to RFC 4180-quoted CSV, and INSERTs anemailrow intonotificationsso the DispatcherWorker handles the SMTP send + 1m/5m/30m/2h/6h retry backoff. The cadence advances regardless of outcome - a failing report stampslast_errorbut does NOT stall the next firing.New schedule routes registered on the saved_reports router:
GET/POST /api/v1/reports/saved/{id}/schedules(list / create under the parent),GET/PATCH/DELETE /api/v1/reports/schedules/{id}(per-schedule mutation lives on a flat path so the SPA's "pause this schedule" PATCH does not need the parent id in the URL).main.rsregisters the worker on the sharedSchedulerat 60s intervals, mirroring the pattern used by the dispatcher / rmm / sla / calendar-reminder workers.Integration tests at
tests/scheduled_reports.rs:schedule_create_returns_next_run_at: POSTing a schedule returns a row withis_active=trueand a populatednext_run_at.worker_tick_materialises_due_schedule: a back-dated schedule is picked up on the next tick, onenotificationsrow of channelemaillands, and the schedule'slast_run_atis populated whilenext_run_atadvances into the future with nolast_error.worker_tick_skips_disabled_schedule: a schedule withis_active=falseis NOT examined by the tick, produces no notification, and is left alone.#PMS-478
PMS-457 phase 1 + PMS-477 phase 2 made saved reports definable and executable on demand. Phase 3 closes the loop: a user can mark a saved report "deliver this weekly to my email" and a background worker materialises it on the cron tick, dispatching via the existing notifications queue so SMTP retry / backoff are reused rather than re-implemented. What landed: * Migration `075_scheduled_reports.sql` adds the `scheduled_reports` table: `(id, tenant_id, saved_report_id, user_id, cron_expr, channel='email', format='csv', recipient_email, is_active, last_run_at, next_run_at, last_error, created_at, updated_at)`. The `channel` + `format` columns ship with CHECK constraints scoped to today's surface (email + csv only) but are wide enough to host future SMS / PDF without a schema migration. The hot-path index `idx_scheduled_reports_next_run_active` is partial on `is_active = true` so a tenant that disables every schedule keeps its rows out of the worker scan. * DTOs in `saved_reports/models.rs`: `ScheduledReportResponse`, `CreateScheduledReportRequest`, `UpdateScheduledReportRequest`. The cron expression is validated via the existing `utils::validation::validate_cron` (cron crate, 6-field form with seconds); the recipient override is validated as an email. * Service additions in `saved_reports/service.rs`: `schedule_create` runs the existing `get` visibility check first so a private report can only be scheduled by its owner; `schedule_get/list/update/delete` round out the CRUD with owner-only mutation; `compute_next_run` exposes the cron-advance helper to both the service (on create / update) and the worker (after each tick). * New `saved_reports/worker.rs`: `ScheduledReportsWorker` implements `Job` and ticks every 60s. Each tick locks up to 10 due rows via `SELECT ... FOR UPDATE SKIP LOCKED`, materialises each report through the existing executor at `per_page = 10_000`, renders the result set to RFC 4180-quoted CSV, and INSERTs an `email` row into `notifications` so the DispatcherWorker handles the SMTP send + 1m/5m/30m/2h/6h retry backoff. The cadence advances regardless of outcome - a failing report stamps `last_error` but does NOT stall the next firing. * New schedule routes registered on the saved_reports router: `GET/POST /api/v1/reports/saved/{id}/schedules` (list / create under the parent), `GET/PATCH/DELETE /api/v1/reports/schedules/{id}` (per-schedule mutation lives on a flat path so the SPA's "pause this schedule" PATCH does not need the parent id in the URL). * `main.rs` registers the worker on the shared `Scheduler` at 60s intervals, mirroring the pattern used by the dispatcher / rmm / sla / calendar-reminder workers. Integration tests at `tests/scheduled_reports.rs`: * `schedule_create_returns_next_run_at`: POSTing a schedule returns a row with `is_active=true` and a populated `next_run_at`. * `worker_tick_materialises_due_schedule`: a back-dated schedule is picked up on the next tick, one `notifications` row of channel `email` lands, and the schedule's `last_run_at` is populated while `next_run_at` advances into the future with no `last_error`. * `worker_tick_skips_disabled_schedule`: a schedule with `is_active=false` is NOT examined by the tick, produces no notification, and is left alone. #PMS-478