Short answer: an Express endpoint should record an edtech cleanup request and its outbox event in one Postgres transaction, return 202 Accepted, and let a separate worker publish and consume a small job. The worker should claim work with a lease, make each deletion decision idempotent, and write the audit event before acknowledging the delivery. That arrangement keeps a web request short while making recovery inspectable.
The constraint comes first. A student-record cleanup can touch thousands of rows, object references, and retention exceptions; keeping the browser request open turns a normal timeout or deploy into an ambiguous business event. The API is therefore an admission point, not the executor. The queue is delivery machinery, not the system of record.
There is no magic exactly-once execution guarantee. The useful guarantee is that repeating a delivery cannot create a second externally visible cleanup, and that an operator can tell the difference between queued, running, retryable, completed, and rejected work.
How does a Node.js Express API request become a recoverable Postgres background job?
Give each request an idempotency key scoped to the tenant and cleanup operation. In the same transaction, insert a job row and an outbox row. A unique constraint on (tenant_id, idempotency_key) makes a retried request return the original job ID instead of creating another cleanup. The response can be small:
package enqueue
import (
"context"
"database/sql"
"fmt"
)
type Request struct {
TenantID string
IdempotencyKey string
CourseID string
}
func CreateCleanup(ctx context.Context, db *sql.DB, r Request) (string, error) {
tx, err := db.BeginTx(ctx, nil)
if err != nil {
return "", fmt.Errorf("begin transaction: %w", err)
}
defer tx.Rollback()
var jobID string
err = tx.QueryRowContext(ctx, `
INSERT INTO cleanup_jobs
(tenant_id, idempotency_key, course_id, status)
VALUES ($1, $2, $3, 'queued')
ON CONFLICT (tenant_id, idempotency_key)
DO UPDATE SET idempotency_key = EXCLUDED.idempotency_key
RETURNING id`,
r.TenantID, r.IdempotencyKey, r.CourseID,
).Scan(&jobID)
if err != nil {
return "", fmt.Errorf("create cleanup job: %w", err)
}
_, err = tx.ExecContext(ctx, `
INSERT INTO job_outbox (job_id, event_type)
VALUES ($1, 'cleanup.requested')
ON CONFLICT (job_id, event_type) DO NOTHING`, jobID)
if err != nil {
return "", fmt.Errorf("create outbox event: %w", err)
}
if err = tx.Commit(); err != nil {
return "", fmt.Errorf("commit cleanup request: %w", err)
}
return jobID, nil
}
The HTTP layer can call this function and send the ID in a 202 response. It must not publish first and hope the database commit follows: a worker could consume a message for a transaction that later rolls back. It must not commit the job and publish directly afterward either, because a process exit between those statements leaves a job that nobody knows to deliver. A relay reads committed outbox rows and retries publication until the delivery record is reconciled.
The outbox payload should contain the job ID, tenant ID, cleanup type, and a versioned scope, not a snapshot of every row to delete. The worker fetches current state under the application's retention rules. That makes the message small and lets the audit record explain which policy version made each decision.
Short request. Durable handoff.
The lease is the deadline.
The API key and the worker effect key are related but distinct. The first deduplicates admission. The second prevents a repeated message from applying the same deletion or issuing the same downstream command twice. For a cleanup job, use a stable effect key such as cleanup:<job-id>:<resource-id>, with a unique database constraint; timestamps are not an idempotency mechanism.
What evidence should a consumer worker write before acknowledgement?
The worker needs a lease rather than a vague running flag. A lease owner and expiry let another worker recover a job after the original process disappears. The worker claims a queued or expired job, performs bounded batches, and renews the lease only while it is making progress. It acknowledges the queue delivery after the database transaction that records the batch result commits.
For every candidate resource, classify the decision: deleted, retained by policy, already absent, or failed with a retryable reason. “Already absent” is a successful idempotent outcome, not an exception. Record the policy version, actor or worker identity, resource identifier, and timestamp in an append-only audit table subject to the organization's access and retention controls. A queue message may vanish after acknowledgement; it is not an audit archive.
The critical crash window is easy to test. A worker commits the effect row and cleanup status, then dies before acknowledgement. Delivery occurs again. The second attempt finds the effect key, records no second deletion, and acknowledges. If it dies before the commit, the lease eventually expires and another attempt performs the transaction. Both paths converge on one business result.
Run the drill with a deliberately awkward batch, not a toy row. Suppose a course has 18,000 expired attachments, a legal hold covers 37 of them, and the deployment terminates the worker after its fourth commit. The replacement worker must see the previous lease as expired, skip the four committed batches by effect key, retain the held objects, and continue from the remaining resource IDs without guessing from queue age. The API should still report one cleanup job, while the audit query should show the same policy version for every evaluated resource. That is a longer path than “publish and forget,” but it is the path an operator can explain to a school administrator or an auditor when the cleanup spans a deploy window. I don't need the queue to promise a philosophical exactly-once property; I need the database, lease, and audit trail to produce one answer after interruption.
This is also where compliance limits matter. A cleanup policy may require deletion, but legal hold, enrollment records, or an audit-retention rule may require a resource to remain. The worker must represent that decision explicitly instead of silently treating every row as disposable. Deletion logs can contain personal data too, so their retention and access policy should be designed with the same care as the data they describe.
This design is not suitable when the work needs long-lived joins, human approval steps, or replayable multi-consumer history; a workflow engine or event log is a better fit then. It is also a poor choice when Postgres is already the bottleneck and cannot absorb the job ledger and audit writes. Stick with a simpler synchronous operation for a tiny, bounded cleanup that can finish inside the request timeout.
package worker
import (
"context"
"database/sql"
"fmt"
)
func RecordBatch(ctx context.Context, db *sql.DB, jobID, tenantID string, resourceIDs []string) error {
tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
if err != nil {
return fmt.Errorf("begin batch: %w", err)
}
defer tx.Rollback()
for _, resourceID := range resourceIDs {
effectKey := "cleanup:" + jobID + ":" + resourceID
_, err = tx.ExecContext(ctx, `
INSERT INTO cleanup_effects (effect_key, job_id, tenant_id, resource_id, outcome)
VALUES ($1, $2, $3, $4, 'deleted')
ON CONFLICT (effect_key) DO NOTHING`,
effectKey, jobID, tenantID, resourceID)
if err != nil {
return fmt.Errorf("record effect for %s: %w", resourceID, err)
}
_, err = tx.ExecContext(ctx, `
INSERT INTO cleanup_audit (job_id, resource_id, event_type)
VALUES ($1, $2, 'cleanup.evaluated')
ON CONFLICT (job_id, resource_id, event_type) DO NOTHING`,
jobID, resourceID)
if err != nil {
return fmt.Errorf("record audit for %s: %w", resourceID, err)
}
}
return tx.Commit()
}
The SQL here is deliberately generic. The resource deletion itself must be in the same correctness design: either the effect row and the deletion are one transaction, or the resource store needs its own idempotent operation and reconciliation state. An audit insert alone does not make an external side effect reversible.
Delivery semantics are a capacity decision
Start by writing down the delivery contract: at-least-once or deduplicated delivery, visibility or lease duration, retry behavior, retention, maximum message size, ordering, and acknowledgement timing. Then derive the worker around that contract. A visibility timeout hides a message temporarily; it does not prove that the previous worker stopped. AWS documents the need to extend that timeout when processing takes longer, and RabbitMQ's priority queues document that priority affects which ready messages are delivered, not whether a business operation is idempotent.
| Decision | Engineering consequence |
|---|---|
| Work can run longer than the delivery lease | Renew the lease, split into bounded batches, or choose a workflow system; never let the lease silently expire during an unbounded transaction. |
| A second delivery is possible | Put a unique effect key at the database boundary and test the crash-before-ack path. |
| Urgent compliance cleanup must jump ahead | Use a documented priority policy, while preserving fairness so ordinary student work does not starve. |
| A job must fan out to several independent consumers | Publish separate durable work items and separate effect keys, or adopt an event log with replay semantics. |
| The schedule can be paused or delayed | Store the intended window and last successful run in Postgres; do not infer business history from scheduler output. |
Do not use priority as a substitute for capacity planning. If every job is urgent, the field carries no useful information. Likewise, do not confuse a five-minute deduplication window or a broker's message identity with an idempotency policy that must survive a student's full data-retention lifecycle.
Your mileage may vary: the right lease length depends on the largest bounded batch, database lock duration, and deployment policy. Measure those values in staging with realistic course and enrollment volumes, then leave enough margin for a deploy without turning a slow worker into a duplicate worker.
Rollout starts with a failed deploy
Before production, inject failures at four boundaries: after the API commits, after the relay publishes, after the worker commits a batch, and while the worker holds a lease. Assert that a retry yields one job, one effect per resource, and a complete audit trail. Also test an idempotency-key collision across tenants, a repeated key with a different course scope, a legal hold discovered mid-run, and a malformed message that should become terminal rather than retry forever.
Operational recovery needs metrics that describe work, not just infrastructure. Track queue age, oldest outbox row, lease expirations, batch duration, retry count, terminal failures, and the difference between requested and audited resources. Alert on a growing oldest-job age and on reconciliation gaps. A dashboard that only says “worker process is healthy” can be green while cleanup work is stranded.
Roll out with a versioned job schema and a small tenant cohort. Keep the API response contract stable, make the worker accept both the current and previous message version during migration, and provide an operator action to retry a terminal job only after its reason is understood. The system is ready when interruption is an ordinary tested state, not an incident that requires someone to guess whether the request ran.
Top comments (0)