Scheduled Report Generator
Build a report generator in Spring Boot that runs SQL reports on a schedule, such as daily sales per region or weekly new customers, fills in their parameters (like the date range), and exports the results as CSV files with a version number and a checksum, so every file can be traced back to the exact run that produced it. Report definitions are stored in PostgreSQL, runs are recorded with their status, row count and duration, a report is never run twice for the same period, and failed runs are retried. Results can also be downloaded as JSON through a small API. You'll learn scheduling in Spring, safe parameterized SQL, streaming large results to files, idempotent runs, and testing time-based jobs.
- Estimate
- ~11.5h
- Steps
- 6
- Completed by
- 0
- Proposed by
- codeseed.app
Spring Boot · PostgreSQL · Jackson
Project roadmap
- 01
Design the reports
~1.5hWrite DESIGN.md: a report definition (name, a SQL query with named parameters, a schedule as a cron expression, the period it covers such as the previous day, and the output columns), a run (report, period, status, attempts, row count, file path, checksum, version and error), the rule that each report runs once per period, and where files are stored (reports/NAME/PERIOD/vN.csv). Done when: DESIGN.md covers definitions, runs, files and failure rules.
- 02
Store definitions and runs
~2hCreate the tables in PostgreSQL with Flyway, with a unique index on report plus period for runs. Only allow report queries that are SELECT statements, run them with a read-only database user, and pass parameters only as bind variables with NamedParameterJdbcTemplate. Seed a couple of reports over sample sales data. Done when: report definitions load, and a non-SELECT query is refused.
- 03
Run reports and export CSV
~2.5hWrite a ReportRunner that creates a run (skipping it if one already succeeded for that period), executes the query with a timeout, and streams the rows straight to a CSV file (with a fetch size, so large results don't fill memory), writing to a temporary file, computing a SHA-256 checksum, then renaming it. Record the row count, duration and checksum. Done when: running a report produces a correct CSV and a run record, and running it again for the same period does nothing.
- 04
Schedule and retry
~2hSchedule each report according to its cron expression (with Spring's scheduling and a TaskScheduler, or a job that checks every minute which reports are due). Retry failed runs with increasing waits up to a limit, and prevent the same report running twice at once, even with several instances, using a database lock (for example with ShedLock or SELECT ... FOR UPDATE SKIP LOCKED). Done when: reports run on schedule, failures are retried, and two instances never run the same report together.
- 05
Add the API
~1.5hAdd endpoints to list reports and their recent runs, trigger a run for a chosen period (with validation), download a run's CSV, and get its rows as JSON (serialized with Jackson, streamed for large results). Re-running a period on request creates a new version file instead of overwriting the old one. Done when: runs can be listed, triggered and downloaded through the API.
- 06
Test it
~2hWrite tests with Testcontainers PostgreSQL and a controllable Clock: parameter binding, refusing non-SELECT queries, CSV content and quoting, checksums, skipping already finished periods, retries after a simulated failure, versioning on re-run, and the locking between two runners. Done when: all tests pass.
Ready to build this?
Get a GitHub repo and start building. Your AI reviewer checks each step as you go.
Tech stack