A nightly rollup should use a narrow database credential and two durable keys: one on each accepted usage event, another on each tenant-period billing row. For a marketplace that must keep prepaid balances from running out unattended, this limits both duplicate charges and the damage a leaked job credential can do. Don't give the worker the same key as the application.
| Choice | Replay behavior | Credential blast radius | Operational cost |
|---|---|---|---|
| Transactional database rollup | Deterministic upsert behind a unique key | One schema and a small verb set | One scheduled worker |
| Append-only ledger plus projector | Rebuildable from immutable entries | Separate write and projection roles | More storage and moving parts |
| In-memory aggregation before insert | Lost state must be reconstructed | Often inherits broad app access | Low initially, expensive under failure |
Recommendation: start with the transactional rollup when the source timeseries and billing rows share a database. Use half-open time windows, freeze a watermark, and make the database uniqueness constraint the final idempotency guard. This is the shortest path I would trust while shipping weekly; the ledger is the runner-up when audit and replay requirements justify its extra machinery.
How should a nightly usage rollup turn timeseries into tenant billing rows?
Treat the job as a deterministic projection, not a midnight counter. At the start of a run, choose windowStart, windowEnd, and a watermark representing the newest source event eligible for that run. Read events where time is in [windowStart, windowEnd) and ingestion position is no later than the watermark. Group by tenant, meter, and billing period. Then write one row per group inside a transaction. That half-open interval matters. Adjacent runs can share a boundary without counting an event twice: one owns 00:00:00 and the previous one does not. The watermark solves a different problem. Without it, events arriving while the query is paging can make two attempts observe different input, so a retry may produce a different amount even though its nominal date is unchanged. The two keys serve different failure domains. event_id rejects duplicate ingestion before aggregation. A composite uniqueness constraint such as (tenant_id, meter, period_start, period_end) rejects duplicate output. The job can crash after committing and before recording success; a replay reaches the same conflict target and updates the same logical row. Good. I would keep the row in an open state while late events are still allowed, then close it under a separate policy. I'm not sure what lateness window fits every marketplace because settlement timing and upstream delivery guarantees decide that. Measure arrival delay, publish the cutoff, and never silently mutate a closed invoice.
Replay, then stop.
The first criterion is credential blast radius
A cron worker needs surprisingly little authority. It must read eligible usage, insert or update open billing rows, and record its run. It doesn't need customer administration, payout access, secret rotation, or unrestricted table ownership. Revenue per engineering hour gets worse fast when one convenient application credential turns every background task into an account-wide incident surface.
Make the worker identity separate from the web process. Store it in a secrets manager, deliver it only to the job runtime, rotate it, and log use without logging the secret. OWASP recommends least privilege, automated rotation where possible, expiration, and attribution of secret use. Those controls are not paperwork here; they define how much of the marketplace a compromised nightly process can touch.
One sharp test is enough: if the rollup credential were exposed for 30 minutes, could it change a seller's payout destination? If yes, the role is too broad.
The catch is operational friction. Separate identities add provisioning and rotation work, and a one-person SaaS has no spare security team. Outsource the undifferentiated storage and rotation mechanism if that saves time, but keep the permission model explicit and testable in deployment. A managed secret store doesn't repair an overpowered database role.
The second criterion is deterministic replay
Idempotency isn't a boolean property of the scheduler. It comes from stable input, stable arithmetic, and a database-enforced output identity. A distributed lock can reduce overlap, but it cannot prove that a retry after process death won't duplicate a row. The unique constraint can.
Use integer quantities for metering units, and keep rating out of this aggregation step unless the tariff version is part of the row's identity. Mixing current prices into usage compaction makes historical replay depend on mutable configuration. The rollup should preserve the evidence needed by a later rating step: tenant, meter, bounded period, quantity, and source watermark.
Short runs win.
A useful run record stores a deterministic run key, its window, its watermark, attempt state, and timestamps. That is control-plane evidence, not the billing result itself. Don't use a successful-run flag as the only guard: the process can commit rows and fail before flipping that flag, which is precisely the boundary a retry must survive.
A TypeScript transaction for one nightly window
The following Node.js example keeps infrastructure behind a tiny database interface. It assumes usage_events.event_id is unique at ingestion and billing_rows has a unique constraint on (tenant_id, meter, period_start, period_end). The SQL uses a single INSERT ... SELECT so the selected input and output mutation live in one transaction snapshot.
type QueryResult<T> = { rows: T[] };
interface Transaction {
query<T>(sql: string, values?: readonly unknown[]): Promise<QueryResult<T>>;
}
interface Database {
transaction<T>(work: (tx: Transaction) => Promise<T>): Promise<T>;
}
type RollupWindow = {
start: Date;
end: Date;
watermark: bigint;
};
export async function rollUpUsage(
db: Database,
window: RollupWindow,
): Promise<number> {
return db.transaction(async (tx) => {
const result = await tx.query<{ tenant_id: string }>(
`
INSERT INTO billing_rows (
tenant_id, meter, period_start, period_end, quantity, source_watermark
)
SELECT
tenant_id,
meter,
$1,
$2,
SUM(quantity),
$3
FROM usage_events
WHERE occurred_at >= $1
AND occurred_at < $2
AND ingestion_sequence <= $3
GROUP BY tenant_id, meter
ON CONFLICT (tenant_id, meter, period_start, period_end)
DO UPDATE SET
quantity = EXCLUDED.quantity,
source_watermark = EXCLUDED.source_watermark
WHERE billing_rows.status = 'open'
RETURNING tenant_id
`,
[window.start, window.end, window.watermark],
);
return result.rows.length;
});
}
The WHERE billing_rows.status = 'open' clause makes closure an explicit boundary. A retry with the same watermark reproduces the quantity. A later approved catch-up can use a higher watermark and update only an open row. If a tenant's prepaid balance monitor consumes these rows, it should also retain its own notification key, such as tenant plus threshold plus balance version; idempotent aggregation does not automatically make downstream alerts idempotent.
Deploy this with three tests before the first weekly release: execute the same window twice and compare rows byte for byte; inject a failure after the transaction commits and rerun; insert an event exactly at windowEnd and confirm that only the next window owns it. Then expose counts for input events, output groups, rejected duplicates, late arrivals, and closed-row update attempts. Alert on disagreements, not merely on process exit status.
There is a subtle money boundary here. Usage quantity can be replayed; a prepaid debit should normally reference a durable billing-row version and enforce its own uniqueness constraint. Otherwise an operator retry could correctly rebuild the same row and still trigger a second debit through an at-least-once queue. Keep projection, rating, and collection as separate state transitions even if one service owns all three.
When is the append-only runner-up better?
Choose an append-only ledger when auditors need the complete correction history, when multiple projectors consume the same metering facts, or when billing policy must be replayed across old data. Each correction becomes a new entry rather than an overwrite, and projectors derive current balances. That model makes provenance clearer, but it asks you to operate ordering, checkpoints, retention, and rebuilds. It is not my default for a small marketplace trying to ship every week.
Stick with a transactional database rollup when one database is the system of record, the lateness rule is bounded, and closed rows are immutable. Do not use it when data spans stores that cannot share a transaction and the business requires atomic cross-store effects; an outbox plus an idempotent consumer, or a ledger projector, is the better boundary. In-memory aggregation is suitable only as a disposable optimization over durable events, never as the billing record.
The decision is less glamorous than picking a scheduler. Protect each input with an event key, each output with a tenant-period key, and the worker with a credential whose permissions fit on one short list. That keeps a missed acknowledgment boring, which is exactly what unattended prepaid balance protection needs.
Top comments (0)