Explore best-effort revert plan output for apply

[hardbyte]

Problem

pgroles apply already executes in a single database transaction, so statement failures roll back automatically before commit. What users still lack is confidence about recovery after a successful apply has committed changes.

For trust-building and operational review, it may be useful to generate a best-effort revert plan from the pre-apply database snapshot.

This is not a guaranteed rollback mechanism.

Goal

Explore whether pgroles should be able to emit a best-effort revert plan before executing changes.

Potential CLI shapes:

• pgroles apply --write-revert-sql revert.sql
• pgroles diff --show-revert
• pgroles apply --emit-revert-plan

How it could work

At apply time:

1. inspect current database state
2. compute the forward plan from current -> desired
3. compute a reverse plan from the captured pre-apply state
4. render and optionally persist the reverse SQL before executing the forward plan
5. execute the forward plan transactionally

This would give operators a recovery artifact tied to the exact pre-apply snapshot.

Important limitations

Any design must be explicit that this is best effort only.

Known limits:

• passwords cannot be read back from PostgreSQL for comparison, so password reversal is not generally possible without re-supplying secrets
• DROP OWNED and other destructive retirement steps may be irreversible
• dropped objects/roles may not be fully reconstructible from pgroles state alone
• external changes after apply can invalidate the revert plan
• session termination side effects are not meaningfully reversible

Recommendation

If implemented, prefer language like:

• revert plan
• recovery SQL
• best-effort revert

Avoid implying a guaranteed rollback.

Initial scope

If this proceeds, CLI-first is the safest starting point.

Do not start with operator status storage for revert SQL; that is likely too large, easy to misinterpret, and harder to manage safely.

Open questions

• Which CLI UX is the least misleading?
• Should revert output be generated only for apply, or also for diff?
• How should destructive/irreversible changes be annotated in the output?
• Should revert output include metadata like database identity, manifest hash, and snapshot time?
• How should password-related changes be represented or redacted?
• Should revert output be suppressed entirely when the plan contains irreversible retirement actions?

Acceptance for exploration

• clear statement of guarantees vs non-guarantees
• proposed UX for CLI output/file emission
• examples of reversible vs non-reversible plans
• decision on whether this is worth building at all