chore(db): evaluate reconstructing purchase_history rows lost to the VARCHAR(20) account_id truncation (#1603) #146

Summary

PR LeanerCloud/cloud-commitments-cli#1677 (closing LeanerCloud/cloud-commitments-cli#1603) widens purchase_history.account_id from VARCHAR(20) to VARCHAR(255), stopping further audit-row loss on Azure and long-project-ID GCP purchases. It deliberately does not attempt to recover the rows already lost, because those rows were never inserted — there is nothing in purchase_history to repair.

However, the lost rows are not entirely unreconstructable, and this issue tracks evaluating that.

Current behaviour

When SavePurchaseHistory failed with SQLSTATE 22001, recordHistoryAuditGap (internal/purchase/execution.go) stamped a marker on the execution rather than losing the event entirely:

history_write_failed: commitment <commitmentID> purchased but its history record failed to save

So for each lost row, purchase_executions still holds:

• error — the marker, carrying the provider-side commitment ID
• recommendations (JSONB) — the RecommendationRecord set, including per-rec PurchaseID, Purchased, service/region/resource-type/term/payment and cost fields
• cloud_account_id (where set), plan_id, source, executed_at, executed_by_user_id

That is most of the purchase_history column set. A backfill that joins the audit-gap marker to the matching recommendation entry is plausible.

Steps to verify the gap

SELECT execution_id, error FROM purchase_executions
WHERE error LIKE '%history_write_failed%';

Each match is a real, billed commitment with no purchase_history row. Note the double-sided wildcard: recordHistoryAuditGap appends the marker via appendErrNote, so it is not necessarily at the start of the field when the execution already carried an error (a retry successor carrying a predecessor's error, or a second gap in the same execution).

Expected behaviour

Either:

1. A one-shot reconciliation path (migration or operator job) that reconstructs purchase_history rows from the execution's recommendations JSON for every history_write_failed marker, writing only fields it can source and leaving the rest explicitly NULL rather than fabricating them; or
2. An explicit decision that reconstruction is not wanted, documented in known_issues/, so operators know the History view is permanently incomplete for those commitments.

Option 1 must not fabricate values. Fields not derivable from the execution row (e.g. revocation_window_closes_at) should stay NULL, and reconstructed rows should be distinguishable from natively-written ones.

Proposed fix

Scope out before implementing:

• Confirm the recommendations JSON reliably carries PurchaseID matching the commitment ID in the marker (internal/purchase/execution.go, aggregatePurchaseOutcomes).
• Decide how to represent account_id for the reconstructed row — the execution's cloud_account_id resolves to cloud_accounts.external_id, which is the value that failed to insert in the first place.
• Cross-check against chore(db): backfill purchase_history.cloud_account_id from (provider, external_id) #22 (backfill purchase_history.cloud_account_id from (provider, external_id)), which touches the same column pair and cannot repair rows that were never inserted.

References

• Parent issue: fix(db): purchase_history.account_id VARCHAR(20) drops the audit row after every Azure/GCP purchase cloud-commitments-cli#1603
• Fix PR: fix(db): widen purchase_history.account_id to VARCHAR(255) cloud-commitments-cli#1677
• Related: chore(db): backfill purchase_history.cloud_account_id from (provider, external_id) #22 (cloud_account_id backfill), bug(purchases): approved AWS purchase vanishes from History — 'approved'/'running' execution statuses hidden by the History list (P0) cloud-commitments-cli#621 (why the audit-gap marker exists rather than a swallowed error)

Severity

Medium. No further loss occurs once LeanerCloud/cloud-commitments-cli#1677 merges; this is recovery of historical data. Impact scales with how long an affected Azure/GCP deployment has been purchasing.