Schedule effective-dated changes

Applying a change at a future effective date (plan/tier/price changes, scheduled deactivations, end-of-billing-cycle updates) without mutating now. The Redis delayed job alternative (and the cron + overlap lock) is background-jobs-and-caching; transactions are write-service-code §9.

Core principle

Persist the intended change as a row with an effective date — don't mutate now, and don't bury the timer in memory. A pending-changes table makes a future change queryable ("what's scheduled?"), cancellable (the user changes their mind), and auditable (history of what applied when). A scheduled sweep applies the due ones transactionally and records history.

1. Store the intent, not the mutation

A pending_changes table holds the change payload + when it takes effect + its lifecycle status — instead of writing the change to the live row immediately:

@Entity() class PendingChange extends BaseEntity {
  @Column() targetId!: string;                 // the entity to change
  @Column({ type: 'jsonb' }) data!: ChangePayload;   // WHAT changes (new tier, new price, …)
  @Column() effectiveAt!: Date;                // WHEN it takes effect
  @Column({ type: 'enum', enum: PendingStatus, default: PendingStatus.PENDING }) status!: PendingStatus;
  @Column({ nullable: true }) reason!: Nullable<string>;   // why it FAILED / was CANCELLED
}
// PENDING → COMPLETED | FAILED | CANCELLED

Now "show my scheduled changes" is a query, and cancelling is status = CANCELLED — no timer to chase.

2. Sweep DUE changes (push the filter into SQL, in batches)

A scheduled job (a cron processor — background-jobs-and-caching) fetches only what's due and still pending, ordered by effective date, paginated for large sets. Never fetch everything then filter in code (global Database & Migration Rules).

this.repo.find({
  where: { effectiveAt: LessThanOrEqual(now), status: PendingStatus.PENDING },  // due + pending, in SQL
  order: { effectiveAt: 'ASC', createdAt: 'ASC' },
  take: BATCH,
});

Guard the sweep against overlap (a slow run must not double-apply) with the Redis SET NX lock from background-jobs-and-caching.

3. Apply transactionally + snapshot prior state to history

In one transaction: snapshot the current state into a *_history table (audit/rollback trail), apply the change, then mark the pending row COMPLETED. On failure, mark FAILED + reason (keep the record — don't lose it) so it's visible and retriable. Make apply idempotent (a re-run of a COMPLETED row is a no-op).

await this.dataSource.transaction(async (em) => {
  const prior = await em.findBy(Target, { id: In(ids) });
  await em.insert(TargetHistory, prior.map(({ id, ...rest }) => rest));   // snapshot BEFORE overwrite
  await applyChanges(em, dueChanges);                                     // the actual mutation(s)
  await em.update(PendingChange, { id: In(dueIds) }, { status: PendingStatus.COMPLETED });
});

4. When to use this vs a delayed job

• Redis delayed job (background-jobs-and-caching): ephemeral, fire-and-forget timers — send a reminder, expire a hold, retry. No need to see/cancel/report it.
• Pending-changes table (this skill): a business state change that must be visible, cancellable, reportable, or audited, or applied at a wall-clock boundary for many records at once (end of cycle). The change is data you query, not a job you can't see. They compose — the table is the source of truth; a cron is just the trigger. ▸ Other stacks: SQL temporal / effective-dated (bitemporal) tables; an "outbox"-style scheduled- changes table + a worker; an effective-dated rate/price table read at now. Principle: persist the future change as data with an effective date; a sweep applies due rows + keeps history.

Verification

• A future change is a row (data + effectiveAt + status), not an immediate mutation or an in-memory timer.
• The sweep filters due + pending in SQL, batches, and is overlap-guarded.
• Apply is transactional, snapshots prior state to history, marks COMPLETED, and records FAILED + reason on error; re-runs are idempotent.
• Cancelling is a status change; "what's scheduled" is a query.

Related

• background-jobs-and-caching — the cron + Redis overlap lock that triggers the sweep; the delayed-job alternative (§4).
• write-service-code §9 (the transaction + multi-repo write) · database-migrations (the pending + history tables; push filters into SQL).
• release-safety (effective-dating a change is a safe way to roll one out) · use-feature-flags (the runtime-toggle alternative).