@Component
public class SalesRollupScheduler {
private static final Logger log = LoggerFactory.getLogger(SalesRollupScheduler.class);
private static final int ROLLUP_WINDOW_DAYS = 3;
private final SalesRollupRepository repository;
public SalesRollupScheduler(SalesRollupRepository repository) {
this.repository = repository;
}
@Scheduled(cron = "0 15 1 * * *", zone = "UTC")
@SchedulerLock(name = "dailySalesRollup", lockAtMostFor = "10m", lockAtLeastFor = "1m")
public void runDailyRollup() {
LocalDate today = LocalDate.now(ZoneOffset.UTC);
LocalDate from = today.minusDays(ROLLUP_WINDOW_DAYS);
List<DailySalesTotal> totals = repository.aggregateDailyTotals(from, today);
int written = repository.upsertDailyTotals(totals);
log.info("Sales rollup complete: {} days aggregated between {} and {}", written, from, today);
}
}@Repository
public class SalesRollupRepository {
private final JdbcTemplate jdbc;
public SalesRollupRepository(JdbcTemplate jdbc) {
this.jdbc = jdbc;
}
public List<DailySalesTotal> aggregateDailyTotals(LocalDate from, LocalDate to) {
String sql = "SELECT (created_at AT TIME ZONE 'UTC')::date AS sale_date, " +
"COUNT(*) AS order_count, " +
"COALESCE(SUM(gross_cents), 0) AS gross_cents, " +
"COALESCE(SUM(gross_cents - discount_cents), 0) AS net_cents " +
"FROM orders " +
"WHERE status = 'PAID' " +
"AND created_at >= ? AND created_at < ? " +
"GROUP BY 1 ORDER BY 1";
return jdbc.query(sql, (rs, i) -> new DailySalesTotal(
rs.getObject("sale_date", LocalDate.class),
rs.getLong("order_count"),
rs.getLong("gross_cents"),
rs.getLong("net_cents")
), Timestamp.valueOf(from.atStartOfDay()), Timestamp.valueOf(to.plusDays(1).atStartOfDay()));
}
@Transactional
public int upsertDailyTotals(List<DailySalesTotal> totals) {
String sql = "INSERT INTO daily_sales_totals " +
"(sale_date, order_count, gross_cents, net_cents, updated_at) " +
"VALUES (?, ?, ?, ?, now()) " +
"ON CONFLICT (sale_date) DO UPDATE SET " +
"order_count = EXCLUDED.order_count, " +
"gross_cents = EXCLUDED.gross_cents, " +
"net_cents = EXCLUDED.net_cents, " +
"updated_at = now()";
int[] result = jdbc.batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
DailySalesTotal t = totals.get(i);
ps.setObject(1, t.saleDate());
ps.setLong(2, t.orderCount());
ps.setLong(3, t.grossCents());
ps.setLong(4, t.netCents());
}
@Override
public int getBatchSize() {
return totals.size();
}
});
return result.length;
}
}public record DailySalesTotal(
LocalDate saleDate,
long orderCount,
long grossCents,
long netCents
) {
public long averageOrderCents() {
return orderCount == 0 ? 0 : grossCents / orderCount;
}
}This snippet shows a common reporting pattern: a scheduled background job that aggregates raw transactional rows into a pre-computed summary table so dashboards can read fast, denormalized totals instead of scanning the whole orders table on every request. The work is split across three collaborating files that tell one story — a Spring @Configuration that defines the periodic trigger, the repository that performs the aggregate query and the batch upsert, and a lightweight DTO that carries the rolled-up figures between them.
In SalesRollupScheduler, @Scheduled fires runDailyRollup on a cron expression, and @SchedulerLock (from ShedLock) guarantees only one node in a clustered deployment executes the job at a time — without it, every instance would race to recompute the same days. The scheduler asks SalesRollupRepository for a window of recent days rather than the whole history, because re-aggregating only the trailing few days cheaply corrects late-arriving or amended orders while keeping the job bounded.
SalesRollupRepository is where the interesting SQL lives. aggregateDailyTotals runs a GROUP BY over orders to produce one DailySalesTotal per day, and upsertDailyTotals writes them back using batchUpdate. The write is an idempotent INSERT ... ON CONFLICT (sale_date) DO UPDATE, which is the key design choice: because the daily rollup is derived data, re-running the job must never create duplicates or drift. The unique constraint on sale_date makes the upsert the single source of truth, so the job is safe to retry, replay, or overlap-recover after a crash.
Using JdbcTemplate.batchUpdate sends all rows in one round trip with a BatchPreparedStatementSetter, which matters when a wide backfill window produces many rows — per-row INSERTs would multiply network latency. The whole runDailyRollup method is wrapped in @Transactional so the delete-and-repopulate style upsert commits atomically.
DailySalesTotal is an immutable carrier holding the saleDate, orderCount, grossCents, and netCents, keeping money as integer cents to avoid floating-point rounding errors. A trade-off worth noting: this trailing-window approach assumes corrections land within the recomputed range, so a very old amended order outside the window would require a manual wider backfill.
Related snips
package dbutil
import (
"context"
"github.com/jackc/pgconn"Retry Postgres serialization failures with bounded attempts
require "csv"
class PeopleCsvStream
include Enumerable
HEADERS = %w[id full_name email signed_up_at plan].freezeResilient CSV Export as a Streamed Response
type User {
id: ID!
name: String!
email: String!
posts: [Post!]!
createdAt: String!GraphQL API with Spring Boot
class SignupForm
include ActiveModel::Model
include ActiveModel::Attributes
attribute :account_name, :string
attribute :email, :stringShallow Controller, Deep Params: Form Object Pattern
package com.example.starter.config;
import com.example.starter.properties.CustomProperties;
import com.example.starter.service.CustomService;
import org.springframework.boot.autoconfigure.AutoConfiguration;
import org.springframework.boot.autoconfigure.condition.ConditionalOnClass;Custom Spring Boot starters
class AddSettingsToAccounts < ActiveRecord::Migration[7.1]
disable_ddl_transaction!
def change
add_column :accounts, :settings, :jsonb, null: false, default: {}Postgres JSONB Partial Index for Feature Flags
Share this code
Here's the card — post it anywhere.
