feat(storage): implement PostgreSQL outbound stage backends

[lykakis]

Parent storage epic: #337
Delivery step 8; follows the contract and filesystem SMS work #338/#343/#344/#339/#345, file-backed DLR #340, and memory DLR #334.
Depends on architecture #338, pending-message contract/CBOR #343, and selected-router/routed-work contract #344. Filesystem implementation issues #339/#345 establish the initial supported profiles; PostgreSQL adds opt-in backend choices.

Goal

Implement PostgreSQL-backed outbound pending, selected-router and routed-work stages where their combinations have defined recovery/handoff behavior. Operators may select postgresql for the globally configured outbound stages, alongside supported memory/file choices. Document exactly which combinations are safe and enabled; unsupported mixes fail startup rather than silently weakening guarantees.

The existing PostgreSQL DlrStorage remains responsible for provider correlation and downstream DLR handling; this issue does not redesign that contract.

Scope and behavior

• Admit complete messages and multipart parts transactionally before HTTP 202 or SMPP submit_sm_resp STATUS_OK; preserve the accepted envelope and multipart deadlines/identities defined in feat(storage): define the outbound pending-message contract and CBOR envelope #343. Fail admission on capacity/database/codec errors without memory fallback.
• Select a bounded batch of eligible pending messages with indexed priority/validity/eligibility access, transactionally recording the selected membership for router-queue=postgresql. A restart restores a selected batch rather than repeating its expensive selection. Avoid table-wide scans on each refill; measure plans/performance against a large backlog.
• For routed-work=postgresql, record all chosen destinations and per-destination terminal progress. Restore unfinished destinations without rerouting completed branches. When a configured upstream/downstream stage uses memory, follow feat(storage): define outbound stage abstractions and memory baseline #338/feat(storage): define selected-router and routed-work contracts #344's recovery rules while retaining the authoritative pending source until safe completion.
• Reconcile interrupted transitions by stable IDs, including any supported cross-backend handoff. A PostgreSQL transaction can cover co-located database stages; it cannot automatically cover a file or memory stage. Implement and test only combinations whose transition/recovery rules are explicit.
• Preserve provider/DLR handoff ordering: do not complete the source until required provider processing and existing PostgreSQL DLR correlation/rejection handoff are terminal. Provider submission remains at least once after a crash.
• Recover non-terminal records before opening durable protocol admission. Backend changes with outstanding records require an explicit migration/drain policy rather than silently stranding work.

Configuration and observability

• Add postgresql as a supported value for each of the global sendium.sms.pending.backend, sendium.sms.router-queue.backend, and sendium.sms.routed-work.backend settings where implemented. Document a supported-profile table, including any PostgreSQL/memory mixes, and reject unsupported file/PostgreSQL mixes.
• Validate schema and datasource at startup; expose the selected backends via shared sendium-sms-storage readiness, operation latency/error metrics, recovery summaries and sanitized errors without per-message metric labels or SMS payloads.
• Document retention, schema migration/rollback, backup and what happens if a backend selection changes while durable records exist.

Acceptance criteria

• Ingress never acknowledges an uncommitted PostgreSQL pending record. Multipart duplicates and deadline recovery follow feat(storage): define the outbound pending-message contract and CBOR envelope #343.
• PostgreSQL-selected batches and destinations restore without repeating the corresponding selection/routing; volatile stages repeat only the work their documented profile allows.
• Forced restart tests cover batch selection, partial commit, router dequeue, route publication, worker processing, copied-route partial completion, DLR handoff failure and provider-send ambiguity.
• Large-backlog selection remains bounded in memory and uses an indexed query plan. Storage failure yields safe readiness/backpressure rather than silent fallback.
• The existing non-durable memory/memory/memory default and explicitly configured file-backed profiles continue to work; documentation differentiates accepted-message, selection and destination durability from retry timing and exactly-once provider submission.

Out of scope

Active-active or multi-replica processing, guaranteed retry count/delay preservation, provider-outcome checkpoints, arbitrary cross-backend transactions or online migration, and exactly-once provider delivery.

[lykakis]

Parent epic: #337.

This issue is the PostgreSQL-backed outbound-message implementation for the epic's postgresql profile. #337 owns the shared storage abstractions, supported profile matrix, configuration model, and eventual defaults. Statements in this issue that imply PostgreSQL is the global default should be interpreted within the PostgreSQL profile; the epic targets the local file profile as the eventual default for single-node installations.