[Deferred] SQL backend for durable task storage

[RedZapdos123]

Design Constraints:

• Keep execution local to Rage workers. This is not a distributed job executor.
• Queue can depend on backend. Backend must not depend on queue.
• Keep scheduling, execution, retries, and task removal in the queue layer.
• Use Active Record connection handling for all SQL backend database access. Do not use raw pg connections.
• Keep :sql as the logical backend identifier. Version one requires Active Record and supports PostgreSQL only.
• Validate Active Record availability and PostgreSQL compatibility when :sql is selected. MySQL and SQLite are follow up work.
• Do not add Active Record as a global Rage dependency. Applications not using :sql must keep current behavior.
• Do not create or migrate tables at boot.
• Preserve at least once delivery semantics.
• Store only authenticated encrypted task payloads in SQL tables. Verify and decrypt before calling Marshal.load.
• Derive a Deferred specific encryption key from Rage.config.secret_key_base and support fallback secret keys for rotation.
• Support current deferred backend contract and add dead task APIs without changing :disk or nil behavior.
• Treat PostgreSQL as the initial correctness target for task claiming and recovery.

Implementation Plan:

Phase 1. Internal Deferred hooks and backend contract:

• Add internal startup and teardown hooks needed by storage backends. Backend must not depend on queue.
• Add shared backend contract coverage for add, remove, and pending_tasks.
• Add contract coverage for add_dead_task, list_dead_tasks, find_dead_task, and remove_dead_tasks.
• Keep :disk and nil behavior unchanged.

Phase 2. SQL backend scaffold and configuration:

• Add Rage::Deferred::Backends::Sql.
• Add config.deferred.backend = :sql, ... parsing and option validation.
• Require Active Record only when :sql is selected.
• Validate that the active database adapter is PostgreSQL and raise a clear configuration error otherwise.
• Use ActiveRecord::Base.connection_pool.with_connection for every database operation.
• Add initial SQL backend options for claim cadence and worker liveness tuning.
• Keep schema installation out of runtime boot.

Phase 3. SQL schema, payload codec, and backend private models:

• Add schema for rage_deferred_workers.
• Add schema for rage_deferred_tasks.
• Add schema for rage_deferred_dead_tasks.
• Add indexes needed for due task lookup, owner lookup, and dead task listing.
• Add backend private Active Record models used only by SQL backend.
• Add a backend private payload codec that serializes with Marshal.dump, then encrypts and authenticates the payload before storage.
• Require secret_key_base only when :sql is enabled and use fallback keys when reading existing payloads after key rotation.

Phase 4. Core live task persistence:

• Implement add(context, publish_at:, task_id:).
• Implement remove(task_id).
• Implement boot time pending_tasks recovery for due rows.
• Decrypt and authenticate every payload before Marshal.load.
• Preserve task_id across retries so another enqueue updates the same logical task row.

Phase 5. Claiming and worker lifecycle:

• Register worker record on boot.
• Start heartbeat updates through Deferred lifecycle hooks.
• Claim bounded batches of due rows that are unowned or owned by stale workers.
• Use transactional claiming that fits Active Record and PostgreSQL row locking semantics.
• Return claimed rows to the queue for scheduling. The backend does not schedule tasks.
• Release worker ownership on graceful shutdown.
• Reclaim rows from stale or missing workers after crashes.

Phase 6. Dead task integration:

• Implement add_dead_task before live row removal, matching queue ordering.
• Implement list_dead_tasks, find_dead_task, and remove_dead_tasks.
• Move poison pills, authentication or decryption failures, deserialization failures, and terminal retries into separate dead task storage.
• Never call Marshal.load for a payload that fails authentication or decryption.
• Keep live and dead state separate instead of overloading one status column.

Phase 7. Validation, docs, and follow up:

• Add PostgreSQL backed external specs using existing test infrastructure.
• Cover encrypted round trips, key rotation, tampered payload handling, dead task moves, multi worker claims, delayed tasks, and stale worker recovery.
• Add docs for PostgreSQL only version one support, Active Record setup, migrations, key management, failure model, and operational notes.
• Add changelog entry once feature is complete end to end.
• Evaluate MySQL support only after the PostgreSQL backed path is correct and merged.

[RedZapdos123]

This picks up the GSoC 2026 idea from #195. I have read the related proposal discussions in #230 and #240.

A few uestions before I start Phase 1:

• Should v1 keep fixed table names or should configurable table names be part of the first PR?
• Should MySQL stay out of the initial merge scope and come later after the PostgreSQL backed path is validated?

If this direction looks good, I will start with a Phase 1 PR.

[rsamoilov]

Hi @RedZapdos123 ,

Really good work, and it's great you checked previous proposals.

I think it looks good, and looking at the log redaction issue, I think you can pull it off. Still, bear in mind that it's a very complicated issue, and I might not be able to provide you with a lot of support along the way. If you start with the implementation, I'd recommend focusing on the happy path for the first iteration.

config.deferred.backend = :sql

One question here - the configuration says SQL, the feature depends on Active Record, and the backend is only compatible with Postgres. These three facts seem to be conflicting - how are you planning to reconcile them?

[rsamoilov]

Also, considering Rage::Deferred relies on Marshal, and the tasks are coming from the external system (the DB), you might want to consider adding the encryption layer.

[RedZapdos123]

About the configuration question:

Updated the plan to keep :sql as the logical backend identifier. Version one will use Active Record for all database access and support PostgreSQL only. The backend will validate Active Record availability and PostgreSQL compatibility when :sql is selected. MySQL and SQLite are follow up work.

[RedZapdos123]

Also, considering Rage::Deferred relies on Marshal, and the tasks are coming from the external system (the DB), you might want to consider adding the encryption layer.

I added authenticated payload encryption to the plan. The SQL backend will encrypt payloads before storage and verify and decrypt them before Marshal.load, using a Deferred specific key derived from secret_key_base with fallback key support. Invalid payloads will move to the dead task table without deserialization.