Scheduled Report Worker

Project roadmap

1. 01

   Design the worker

   ~2h

   Write DESIGN.md: report definitions in a config file (name, a SQL query with named parameters such as :from and :to, a schedule such as daily at 02:00, and the period covered), the run records (report, period, status, attempts, row count, file paths and checksums), the output layout (reports/NAME/PERIOD/v1.csv, v1.json and manifest.json), how duplicates are prevented, how missed periods are caught up, and the retry rules. Done when: DESIGN.md covers definitions, runs, outputs and failure handling.

2. 02

   Run a report with Doobie

   ~2.5h

   Set up an sbt project with Cats Effect, fs2, Doobie and a database (PostgreSQL or SQLite) with sample data. Execute a report's query with its parameters bound safely (never by building SQL strings), stream the rows with Doobie's .stream, and write them to CSV and JSON files with fs2, writing to temporary files and renaming them when complete. Record row counts and SHA-256 checksums in the manifest. Done when: running a report for a period produces correct files and a manifest.

3. 03

   Record runs and prevent duplicates

   ~1.5h

   Store runs in a table with a unique constraint on report plus period; before running, insert a run as running (skipping it if one already succeeded), and update it with the result. When a report is re-run on purpose for the same period, write a new version (v2) instead of overwriting. Done when: running the same report and period twice does nothing the second time, and forced re-runs create a new version.

4. 04

   Schedule and catch up

   ~2h

   Write the scheduler as an fs2 stream that wakes up every minute, works out which report periods are due (including any missed while the worker was stopped, up to a limit), and runs them with limited parallelism (parEvalMapUnordered). Use Cats Effect's Clock so time can be controlled in tests. Shut down cleanly, finishing reports in progress. Done when: starting the worker after a gap catches up missed periods, and new periods run on time.

5. 05

   Retry failures

   ~1.5h

   Retry failed runs with exponential backoff up to a maximum number of attempts, recording each attempt's error, and mark runs as failed after the last attempt. Put a timeout on each report. Log every run with the report, period, attempt, duration and row count. Done when: a report that fails twice then succeeds completes, and one that always fails ends as failed with its errors recorded.

6. 06

   Test it

   ~2.5h

   Write munit-cats-effect tests with TestControl to move time forward: due periods and catch-up, duplicate prevention, forced re-run versioning, retries with backoff, timeouts, and output files with correct content and checksums, using a test database. Done when: sbt test passes.

Resources

• DocsCats Effect: getting started
• DocsCats Effect: TestControl
• Docsfs2 guide
• DocsDoobie documentation
• ArticleExponential backoff and jitter (AWS)