Transaction Archival and Retention Plan

Status: Phase 6 accelerated server-side export running; guarded automatic deletion remains scheduled daily Shared branch: feat/archive-catchup Production timezone: Asia/Jakarta
Last production checkpoint: 2026-10-13 17:20 WIB

Objective

Keep recent operational data in MySQL, preserve older transaction records as verified weekly archives in private object storage, and expose those archives to authorized administrators without delaying pay-in, payout, callback, reconciliation, or balance processing.

The initial retention target is at least 30 days of hot data. Archives use complete Monday-to-Monday WIB weeks, so practical hot retention is normally 30–37 days.

Non-negotiable invariants

• Archive export must be non-locking and should run against a read replica.
• Production deletion is disabled until a complete restore drill passes.
• No unresolved transaction may be removed.
• No row may be deleted unless its exact ID belongs to a verified archive batch.
• A record changed after export must be skipped, reported, and retained.
• Idempotency must survive deletion of the source transaction.
• Financial balance reconstruction and refund behavior must remain available.
• Dashboard, transaction, callback, reconciliation, and permission contracts remain backward compatible.
• Every CSV contains at most 1,000 data rows and at most 5 MiB uncompressed data.
• Object files are private, encrypted, checksummed, and downloaded only through short-lived authorized URLs.
• Archival and deletion must never overlap the dashboard reconciliation or each other.
• No OPTIMIZE TABLE or blocking table rebuild is part of this project.

Repository alignment

All repositories in scope use the same branch name.

Repository	Responsibility	Phase 0 base commit
kingspay_docs	Plan, runbooks, operator documentation	b40b8f0
kingspay_admin	Archive catalog, lookup, download, and status UI	57862be
pga-be-admin	Archive metadata, APIs, worker orchestration, permissions	1d90d1f
pga-api-merchant	Pay-in idempotency and late-callback compatibility	4e2b68c
pga-api-partner	Payout idempotency and late-callback compatibility	de02957

pga-be-client is out of scope unless a later design explicitly adds merchant-portal archive access.

Production baseline

Runtime load

Measurement	Observed value
Kubernetes node CPU	17–24%
Kubernetes node memory	60–70%
MySQL active connections	26 of 601
MySQL running threads	5
Database queries	approximately 90/second
InnoDB inserts	approximately 7/second
InnoDB updates	approximately 10/second
Current row-lock waits	0
Historical row-lock waits	42,117
Historical slow queries	28,424
InnoDB buffer pool	approximately 1.13 GiB

The application cluster has CPU headroom. MySQL memory and I/O are the limiting resources because the transaction-related working set is much larger than the buffer pool.

Large tables

Table	Estimated rows	Data	Indexes
transaksi	2,620,866	2.65 GiB	1.63 GiB
webhook_outbox	2,513,226	1.45 GiB	461 MiB
bot_public_qris_job_logs	356,661	965 MiB	44 MiB
bot_public_qris_deposit_jobs	25,653	415 MiB	17 MiB
webhook_outbox_attempts	2,549,822	223 MiB	130 MiB
recon_upload_items	1,072,594	115 MiB	171 MiB
disbursement	295,703	76 MiB	118 MiB
disbursement_data	154,842	154 MiB	17 MiB
balance_history	166,054	20 MiB	39 MiB

Initial backlog at the 2026-08-24 cutoff

• Pay-in records before the cutoff: 1,520,040.
• Terminal pay-in records eligible for further validation: 1,412,494.
• Old unresolved pay-in records retained in MySQL: 107,546.
• Successful payouts: 47,349.
• Failed payouts: 658.
• Old inquiries requiring an explicit expiry rule: 49,259.
• Old payout records still in process: 1; this record is ineligible.

The most recent midnight dashboard reconciliation took 169,759 ms. Archival must not run at 00:15 WIB. A 30-complete-day transaction-volume review selected 05:00–05:59 WIB as the default replica-export window. Separately approved deletion remains restricted to an off-peak window and is not enabled.

The previous seven complete WIB days contained 370,049 pay-in records and 87,169 disbursement records. The quietest common hours were 04:00–06:59 WIB; 05:00 averaged approximately 719 pay-ins and 232 disbursements per day. Peak pay-in volume occurred around 20:00–22:59 WIB, while payout volume was also high around 14:00 and 21:00–23:59 WIB. At the baseline snapshot, the webhook outbox had one processing row and no pending or retrying backlog.

The later 30-complete-day review covered 2026-08-30 through 2026-09-29. At 05:00, production averaged 494 pay-ins and 116 payouts per hour; the 95th percentiles were 799 and 329, and the observed maxima were 1,042 and 339. This was the lowest average hour for both transaction families. By comparison, 12:00 averaged 2,255 pay-ins and 266 payouts per hour. Future archive export jobs should therefore default to 05:00 WIB unless a newer recorded baseline selects another window.

Retention and eligibility

Pay-in family

Eligible parent statuses:

• success
• failed
• refund

Ineligible statuses:

• pending

Related data is archived together:

• transaksi
• pay-in webhook_outbox
• corresponding webhook_outbox_attempts
• public_qris_pool
• bot_public_qris_deposit_jobs
• bot_public_qris_job_logs
• corresponding public_qris_cancellation_audits

Payout family

Eligible parent statuses:

• success
• failed
• an inquiry explicitly classified as expired, with no active Redis inquiry and no corresponding transfer

Ineligible statuses:

• pending
• process
• recent inquiry

Related data is archived together:

• disbursement
• disbursement_data
• disbursement webhook_outbox
• corresponding webhook_outbox_attempts

Reconciliation family

Reconciliation is archived by completed batch lifecycle, not transaction date:

• recon_batches
• recon_batch_merchants
• recon_upload_items

Only approved or cancelled batches that cannot receive additional action are eligible.

Retained data

The initial deletion release retains:

• balance_history
• unresolved transactions and disbursements
• dashboard_daily_summary
• dashboard_daily_merchant_summary
• merchant, user, channel, bank, and configuration data
• audit records governed by a longer retention policy
• compact idempotency tombstones
• archive metadata and events

Proposed archive metadata

archive_batches

One record per weekly entity group. Its state machine is:

planned -> exporting -> uploaded -> verifying -> verified
verified -> deleting -> completed
any pre-completion state -> failed
deleting -> deletion_paused -> deleting

Required fields include the archive type, WIB range, snapshot timestamp, expected/exported/verified/deleted counts, manifest key, timestamps, worker version, and last error.

archive_files

One record per object, including table name, part number, object key, row count, minimum and maximum IDs, uncompressed and compressed sizes, SHA-256, object checksum, and verification state.

archive_delete_candidates

A temporary operational ledger containing the exact verified source IDs, expected status, expected update timestamp, deletion timestamp, attempts, and last error. Deletion joins this ledger; it never selects a broad date range directly.

Candidate rows may be purged after the archive batch and restore window complete. The immutable object manifest remains the long-term source of truth.

archive_transaction_lookup

A narrow retained index containing transaction type, owner, merchant/platform/partner transaction IDs, final status, transaction date, and archive file reference. It preserves idempotency and supports exact archive lookup without scanning object files.

Before source deletion, each lookup must also contain a bounded JSON response snapshot with the original fields required by merchant inquiry and finalized partner-callback matching. API reads remain live-first and use this snapshot only after the live parent row is absent. New transaction admission performs an indexed tombstone existence check so a merchant transaction ID cannot be reused after its source row is deleted. A null or malformed snapshot is a hard deletion blocker, not permission to return a partial response.

Object format

Object keys use a versioned, partitioned structure:

archives/v2/year=2026/week=2026-W34/type=payin/<table>/part-000001.csv.gz
archives/v2/year=2026/week=2026-W34/type=payin/manifest.json

CSV requirements:

• UTF-8 with a stable, schema-versioned header.
• At most 1,000 data rows per file.
• At most 5 MiB uncompressed data per file.
• RFC-compatible quoting of commas, quotes, CR, and LF.
• An unquoted \N represents SQL NULL; every non-null value is quoted, so an empty string remains distinguishable from NULL.
• Spreadsheet formula-injection protection in operator downloads without corrupting the immutable raw archive.
• Gzip compression.
• SHA-256 calculated while streaming.
• No local unencrypted staging file.

The manifest contains schema version, period, snapshot, table/file counts, row counts, amount totals by status and owner, checksums, worker version, and verification results.

Export workflow

1. Create a planned batch for one complete eligible week.
2. Confirm no overlapping archive, deletion, or reconciliation job.
3. Read from a replica with a consistent, read-only REPEATABLE READ snapshot using primary-key keyset pagination.
4. Stream rows into CSV/gzip and multipart object upload.
5. Close a file at 1,000 rows or 5 MiB, whichever occurs first.
6. Record file metadata and exact deletion candidates.
7. Complete the manifest and object upload.
8. Verify object existence, size, checksum, row count, and financial control totals.
9. Mark the batch verified only when every comparison matches.
10. Leave deletion disabled pending explicit approval.

A production sample returned primary-key-keyset chunks of 1,000 pay-ins in 112–124 ms. A naive date/status query took 17.8 seconds and is prohibited.

Deletion workflow

Deletion is operator-approved per verified batch. Operators invoke a bounded command rather than editing date ranges:

npm run archive:delete -- \
  --batch-id <verified-batch-id> \
  --maximum-parent-rows 10000 \
  --transaction-size 250

The command must:

• refuse any batch not in verified, deleting, or deletion_paused state;
• refuse an unverified or missing manifest;
• use one dedicated database connection;
• set a 1–2 second InnoDB lock wait timeout;
• claim deterministic candidate IDs;
• compare status and update timestamp with the archived values;
• skip and report changed rows;
• delete children before parents;
• commit every 100–250 parent records;
• pause briefly between transactions;
• stop at the daily limit or end of the approved window;
• update candidate and batch counters transactionally;
• be safe to resume after interruption.

Initial dependency order:

Pay-in:
webhook_outbox_attempts
webhook_outbox
public_qris_cancellation_audits
bot_public_qris_job_logs
bot_public_qris_deposit_jobs
public_qris_pool
transaksi

Payout:
webhook_outbox_attempts
webhook_outbox
disbursement_data
disbursement

balance_history is not deleted in the initial release.

Runtime stop conditions

The worker stops before starting another transaction when any condition is true:

• a database row-lock wait is active;
• replica lag exceeds 10 seconds;
• callback backlog or oldest-callback age exceeds its normal threshold;
• pay-in or payout admission latency exceeds twice its seven-day baseline for two consecutive checks;
• MySQL running threads exceed the calibrated ceiling;
• the dashboard reconciliation is active;
• the off-peak execution window ends;
• archive checksum, count, or amount verification fails.

The initial latency and thread thresholds below are derived from the recorded baseline and must be reviewed again after each canary.

For the first deletion canary, the calibrated database ceiling is Threads_running <= 10 (twice the observed baseline of five). There must be no due pending/retrying callback and no processing callback older than 30 seconds. External log review must show two consecutive one-minute windows below twice the recorded pre-canary p95 values: pay-in at most 586 ms on merchant API and 442 ms on partner API; disbursement at most 1,346 ms on merchant API and 1,336 ms on partner API. The worker receives the resulting health-approval timestamp and refuses it after ten minutes. It independently rechecks database threads, callback health, lock waits, dashboard advisory locks, and primary/ replica latest IDs before deletion and between chunks where applicable.

Admin behavior

The admin archive feature provides:

• weekly archive catalog and state;
• table/file counts and sizes;
• checksum and control-total verification;
• exact archived transaction lookup;
• short-lived private downloads;
• paginated preview capped below the file's 1,000 rows;
• immutable archive event history;
• separate list/download/delete/restore permissions.

The existing live transaction pages keep their current API and permission behavior. Archive APIs are additive. CSV files are never loaded completely into an API process or browser page for ordinary browsing.

Rollback and recovery

Before deletion

• Confirm managed MySQL point-in-time recovery.
• Take a fresh snapshot.
• Complete an end-to-end restore into shadow tables.
• Confirm object versioning or immutability.
• Confirm idempotency lookup and late-callback behavior.

Export rollback

Disable export, mark the batch failed, abort incomplete multipart objects, retain MySQL data, and resume from the last verified part.

Deletion pause

Disable deletion, roll back the current small transaction, mark the batch deletion_paused, and retain all verified archive objects and undeleted candidates.

Data restore

Restore objects into shadow tables, verify counts/checksums/control totals, identify conflicts, and reinsert only missing rows in dependency-safe order. For uncertain or broad damage, restore PITR into a separate database and copy the required rows; never overwrite production directly with an older snapshot.

Delivery phases and checkpoints

Phase 0 — plan and prerequisites

• Use feat/transaction-archival across repositories in scope.
• Record the production size and load baseline.
• Define retention, eligibility, file, and deletion invariants.
• Document the guarded deletion runbook template.
• Confirm the read-replica endpoint and connectivity.
• Provision dedicated least-privilege archive read credentials.
• Confirm managed MySQL PITR and snapshot retention.
• Retain financial and audit data outside the initial deletion policy.
• Capture seven days of hourly traffic and a callback-backlog baseline.

Phase 0 exit requires every unchecked prerequisite above.

Current evidence: the managed three-node MySQL cluster exposes a public read-only endpoint that is reachable from the production Kubernetes cluster. A simultaneous check returned the same latest transaction ID and timestamp on the primary and replica. The private replica endpoint timed out from the current cluster network, so the public replica endpoint with TLS and the existing egress allowlist is the verified path. The current shared database account has broad grants even on the read-only endpoint; a dedicated SELECT-only archive identity was subsequently provisioned with mandatory TLS and a two-connection account ceiling. Its separate Kubernetes secret is not mounted into any running workload. A disposable Job verified the secret, private CA chain, read-only endpoint, and exact SELECT-only grants before the Job was removed.

Akamai Cloud Manager confirms daily full backups, continuous binary logs, and point-in-time recovery for the previous 14 days. The log store provided only about three days of endpoint latency history, so latency thresholds must use a new seven-day observation before deletion Phase 5; database transaction counts cover the full seven-day baseline recorded above.

Phase 1 — schema and disabled feature skeleton

• Add archive metadata migrations.
• Add canonical backend and matching frontend permission catalogs.
• Add disabled export and deletion feature flags.
• Add structured batch counters and immutable audit-event metadata.
• Keep all archive jobs suspended.
• Pass backend tests, syntax checks, Prisma validation, frontend tests/build, lint on touched files, and migration review.

Phase 1 was completed on feat/transaction-archival. The additive metadata migration was applied to production on 2026-09-29 and the five archive tables were verified. No existing transaction table was altered. The API deployment was not changed, and no export or deletion ran as part of the migration.

Phase 2 — one-week export-only canary

• Inventory the latest eligible week through the read-only planner.
• Validate CSV partitioning, gzip, checksums, object keys, and remote read-back locally.
• Verify private object storage with a disposable synthetic object.
• Implement the complete parent/child source graph and atomic per-file metadata ledger.
• Export one old week from the replica.
• Enforce 1,000-row/5-MiB file limits.
• Verify all object checksums, counts, and financial totals.
• Confirm no observed payment, callback, lock, or replica-read regression.
• Perform no deletion.

The read-only planner ran against 2026-08-17 00:00 through 2026-08-24 00:00 WIB without writing metadata or uploading objects. It scanned 153,078 pay-ins: 141,554 were terminal candidates and 11,524 pending rows were retained. It also scanned 24,172 disbursements: 11,993 success/failed candidates and 12,179 inquiry rows retained. Candidate pay-in value was Rp 22,333,507,899.00 and candidate disbursement value was Rp 24,124,905,041.00. The bounded replica scan completed in approximately 42 seconds. Immediately afterward, the replica matched the primary's latest pay-in, callback backlog and processing were both zero, MySQL had four running threads, and current row-lock waits were zero.

The artifact layer enforces RFC-compatible UTF-8 CSV quoting, repeats the stable header in every part, stops at 1,000 rows or 5 MiB uncompressed data, uses deterministic gzip, and records SHA-256 for compressed and raw bytes. A synthetic production object was uploaded, read back byte-for-byte, confirmed private through an anonymous HTTP 403, and removed. The Linode-compatible endpoint rejected the AWS AES256 request header, so archive uploads omit that unsupported header and rely on the object-storage provider's managed at-rest encryption; checksums and private access remain independently verified.

The source graph now derives every child from keys captured from an eligible terminal parent: pay-in webhook/outbox attempts, public-QRIS pool, automation jobs/logs, and cancellation audits; and payout detail plus webhook/outbox attempts. Reads use 500-key parameterized batches inside a repeatable-read, read-only replica snapshot. After each object is remotely verified, one small primary transaction records the file, its exact source candidates, parent archive lookup rows, batch counters, and audit event. Any failure rolls that metadata transaction back; it does not mark the batch verified.

Production has no sandbox environment, so the first runtime canary is a single-use Kubernetes Job rather than a recurring CronJob. It is pinned to backend image ghcr.io/kingspaypga/pga-be-admin:90541e10c24d4b4dea86e6321bd5adf2a642d855 and guarded not to begin before 2026-09-30 05:00 WIB. The canary exports only the smaller disbursement family for 2026-08-17 through 2026-08-24. It has ARCHIVE_EXPORT_ENABLED=true, ARCHIVE_DELETE_ENABLED=false, no retry, a two-connection read-only replica identity, and bounded CPU/memory. At the 2026-09-29 11:26 WIB checkpoint the replacement Job transaction-archive-canary-disbursement-0500 was healthy and waiting, and archive_batches contained zero rows, confirming that no export started early. The previous 04:00 Job was removed only after the 05:00 replacement pod became ready. The running API deployment remains on its prior image.

The canary began at 05:00 WIB on 2026-09-30 and completed its business work at 05:00:23. Batch 1 is verified: 11,993 disbursement parents and 35,159 related rows were exported into 48 gzip CSV objects (47,152 graph rows total). The objects contain 23,102,722 uncompressed bytes and 3,235,510 compressed bytes. Every part has at most 1,000 rows; the largest uncompressed part is 967,575 bytes. The verified manifest is archives/v1/year=2026/week=2026-W34/type=disbursement/manifest.json, with SHA-256 9d85787ec0cd3483633cef0e2d2c6edbed479df91d2c6651928f96ba6a6f1def. An independent read-back downloaded all 48 objects and matched their object lengths, row metadata, compressed and raw lengths, and SHA-256 values. The manifest control totals matched 11,872 successful rows worth Rp 23,542,140,544.00 and 121 failed rows worth Rp 582,764,497.00. No source row was deleted or skipped.

Production API logs were compared across 04:40–04:55, 04:55–05:05, and 05:05–05:20 WIB. The canary window had zero pay-in/disbursement HTTP 5xx, zero deadlock or lock-wait-timeout logs, and no callback backlog. Pay-in p95 was 339 ms at the merchant API and 266 ms at the partner API during the window, versus 293 ms and 221 ms before it. Disbursement p95 was 727 ms and 721 ms, versus 673 ms and 668 ms before it. Afterward, database checks showed zero current lock waits, four running threads, no pending/retrying webhook rows, and only two webhook rows actively processing.

Kubernetes nevertheless marked the Job Failed/DeadlineExceeded at 07:27 WIB. This is a false infrastructure result, not an archive failure: the worker logged its verified result at 05:00:23 but remained alive because importing the shared database module also creates the callback-style CSV streaming pool, which the worker did not close. Backend commit 5bc4e6b closes all three database pools and tests that every close is attempted even when another one fails. The fix is included in the production backend image deployed during Phase 3. The verified week must not be rerun merely to replace the Kubernetes status; its database ledger and object evidence are authoritative.

Phase 3 — admin catalog and lookup

• Deploy backend additions before the frontend consumer.
• Add permissions, routes, translations, responsive views, and audit history.
• Verify short-lived private downloads and paginated previews.
• Preserve existing live transaction screens and API contracts.

The Phase 3 implementation is deployed. The backend adds permission-protected, paginated batch listing, batch detail, exact transaction-reference lookup, and verified-file download URL endpoints. Download URLs expire after five minutes, are masked from application logs, and generate an immutable archive event. The frontend adds a Reports menu entry guarded by archive:list, responsive desktop/mobile catalog views, exact lookup, file integrity detail, and download actions guarded separately by archive:download. Export, delete, and restore controls are intentionally absent.

Backend PR 181 was merged first and deployed as immutable image ghcr.io/kingspaypga/pga-be-admin:f01d86896e5ddb5fc485cb8dfb87d8d7c04290de. Both replicas became ready with zero restarts. Health, unauthenticated 401 enforcement for archive and bulk-callback routes, metadata reads for batch 1, exact lookup counts, and a complete short-lived signed object read were verified before frontend deployment. The final database check showed zero lock waits and no pending, processing, or retrying webhook backlog.

Frontend PR 84 was then merged and production Vercel deployment 123d6086b179b8f0a74bea0fa7c80987c2c7e205 completed successfully. The live route denies sessions without archive:list, and the operator editor exposes the archive permission catalog. The existing nokia owner browser session predates these permission keys, so its cached frontend permissions require a logout/login before the authorized page smoke can be completed. No operator permissions were changed automatically.

After the owner logged in again, the production UI exposed Reports → Transaction Archives and loaded batch 1, its 48 verified files, checksums, and complete lifecycle timeline. Exact lookup for a known archived POUT reference returned the correct batch and transaction metadata. Requesting the first verified CSV created audited download_url_issued event 51 for admin 19; the five-minute signed object was independently retrieved with HTTP 200. The computer-use Chrome extension blocked its own final navigation to the credential-bearing signed URL with ERR_BLOCKED_BY_CLIENT, so no local file was left in Downloads during automation. This is a client/extension navigation policy rather than an API, authorization, object-storage, or checksum failure. The post-smoke database check again showed zero lock waits and no pending, processing, or retrying webhook backlog.

Rollback remains low risk because the database migration is additive and deletion is disabled. Backend rollback restores image 3c470a8f10bb10aa0ce5e3c31844e0ff22c5190a; frontend rollback restores the pre-archive main deployment at 0a374b3. The archive metadata tables and verified objects remain in place under either rollback and no transaction source row is affected.

Phase 4 — restore drill

• Restore the canary archive into shadow tables.
• Match every checksum, row count, and control total.
• Validate relationships and idempotency behavior.
• Record restore duration and operator evidence.

Backend PR 182 deployed immutable image ghcr.io/kingspaypga/pga-be-admin:6500d7966f55188e2c38c86c43695bab04db855a. The restore worker is disabled by default, refuses to run while deletion is enabled, uses one database connection, accepts only a verified batch, validates the manifest and every compressed and raw object checksum, and writes only to batch-scoped shadow tables. Existing shadow tables cause a hard failure and are never dropped automatically.

The first canary was created under schema version 1, where a nullable empty string was not distinguishable from SQL NULL. A production preflight found no legitimate nullable empty strings in the four archived source tables. The v1 drill therefore converts empty nullable fields to NULL and then requires an exact null-safe comparison against every still-retained source row. Any source absence or mismatch fails the drill. Future exports use schema version 2 and the explicit unquoted \N null marker under the archives/v2 prefix.

One-off Job transaction-archive-restore-b1-20261001-0500 started at 05:00 WIB and restored all 47,152 graph rows in approximately 23 seconds. Every object checksum, exact retained-source comparison, relationship, row count, and financial total was independently confirmed. The automated Job nevertheless reported failure because its final relationship query compared utf8mb4_general_ci and utf8mb4_0900_ai_ci columns without an explicit binary comparison. Independent verification also found that the following control-total query used rows, which is reserved by the production MySQL version, as an alias. These were validation-query defects; no operational row was changed or deleted, and payment/callback processing remained healthy.

Backend PR 184 deployed corrected image ghcr.io/kingspaypga/pga-be-admin:bd8a1f8933f891497be3150894f1b9bb1b95d3de. Regression tests require binary-safe cross-table reference comparison and the non-reserved rowCount alias. Both corrected production functions passed directly against the first restore's shadow data: zero orphans, 11,872 success rows totalling 23,542,140,544.00, and 121 failed rows totalling 582,764,497.00.

After recording cleanup-started and cleanup-completed audit events, the four temporary shadow tables were dropped with their exact row counts rechecked. The failed Job and audit event remain retained as evidence. One-off Job transaction-archive-restore-b1-20261002-0500 performed the clean retry at 2026-10-02 05:00 WIB with export and deletion explicitly disabled. Audit event 68 records successful verification in 22,413 ms: all 47,152 rows restored, zero source mismatches, and zero orphan detail, outbox, or attempt rows. Independent post-run checks repeated the source comparisons, relationship checks, and financial totals with identical results. Pay-in and disbursement creation continued through every minute of the 04:55–05:05 WIB observation window, with zero current lock waits and no callback backlog at postflight.

Kubernetes later marked that Job DeadlineExceeded because the shared Loki transport kept the already-completed Node process alive. This did not invalidate the durable restore evidence, but it had to be fixed before another one-shot archive worker could be trusted operationally. Backend PR 185 deployed image ghcr.io/kingspaypga/pga-be-admin:72d565b66fbb22d74acfe4b430a691583e6cc2a7, which flushes final stdout evidence and explicitly terminates successful one-shot archive workers after database and S3 cleanup. Production lifecycle Job transaction-archive-worker-exit-smoke-20261005 initialized the same Loki/S3 path and completed Succeeded in nine seconds.

The successful retry's disposable shadow tables were removed only after their exact counts and audit event were rechecked. Cleanup events 69 and 70 preserve the operation. The tables were:

archive_restore_b1_disbursement
archive_restore_b1_disbursement_data
archive_restore_b1_webhook_outbox
archive_restore_b1_webhook_outbox_attempts

Phase 4 is complete. Deletion remains disabled: Phase 5 requires a separately reviewed worker, a fresh snapshot/PITR check, explicit batch approval, and a new preflight of locks, callbacks, replica lag, and payment latency.

Phase 5 — deletion canary

• Deploy the additive archived-transaction response snapshot schema.
• Backfill and verify snapshots for every deletion candidate in the approved batch.
• Deploy archived-ID idempotency and live-first inquiry/callback fallbacks.
• Prove archived inquiry/callback responses against retained live rows before deleting either sample.
• Take a fresh snapshot and confirm PITR.
• Approve one verified batch.
• Implement and test the disabled, one-shot deletion worker.
• Delete no more than 10,000 parent rows that day.
• Use 100–250 parent rows per database transaction.
• Monitor payment latency, locks, callbacks, and replica lag.
• Verify retained balances and archive lookup.

Compatibility deployment order is mandatory: deploy the nullable schema column first, deploy the archive worker, then run the guarded 05:00 WIB backfill for the exact verified batch:

npm run archive:backfill-snapshots -- \
  --execute-snapshot-backfill --batch-id 1 --chunk-size 250

The worker uses READ COMMITTED, a two-second lock timeout, exact candidate status/update-time guards, transactions of at most 250 metadata rows, and an advisory lock. It changes only archive_transaction_lookup; source transaction rows remain read-only. Only after every snapshot is valid and response-compatible may the merchant and partner API fallbacks be deployed. Deletion remains a separate later approval.

The 2026-10-05 22:00 WIB read-only preflight confirmed that production still has no snapshot column, so neither API fallback was deployed early. Batch 1 remains verified with deleted_rows=0 and skipped_rows=0; all 11,993 lookup rows still have exact source rows whose status and update timestamp match the candidate ledger. Three are platform-owned records with null merchant/agent ownership, which the backfill now handles explicitly. The preflight also found the expected legacy collation difference (utf8mb4_unicode_ci metadata versus utf8mb4_general_ci source tables); indexed identifier joins now collate only the metadata operand, while status guards use exact binary comparison. No callback backlog or lock wait was present, MySQL had four running threads, and the APIs continued processing traffic. Deployment was therefore deferred to the 05:00 WIB window.

The compatibility prerequisite was deployed during the 2026-10-06 05:00 WIB window. The nullable JSON column was added with ALGORITHM=INSTANT and LOCK=NONE. The first one-off backfill Job failed before any database write because its pod did not mount the production MySQL CA secret; the retained failed Job and an immediate 0 populated-row check provide the evidence. A cleanup-path defect exposed by that failure was fixed in admin backend PR 187. The retry mounted prod-mysql-ca and verified all 11,993 lookup rows in 5,755 ms using 250-row transactions. Independent comparison against retained live disbursements found zero missing, invalid, incompatible, or field-mismatched snapshots. Merchant and partner compatibility APIs were then deployed and their read-only production model probes returned complete archived records. During the observation period all three deployments remained 2/2 with zero restarts, the callback backlog remained empty, source traffic continued, and no application lock wait or archive-schema error was observed. Batch 1 remains verified with deleted_rows=0 and skipped_rows=0.

Admin backend PR 188 then deployed the fail-closed one-shot deletion worker. Its production preflight Job transaction-archive-delete-preflight-b1-20261006 ran with both archive export and deletion flags explicitly false. It verified the batch manifest and file totals, every deletion candidate, archived response snapshots, replica position, callback health, and lock/traffic gates in 704 ms. The result was ready with deletionEnabled=false and windowEligible=false. An independent post-check confirmed all 47,152 candidate source rows still exist, all candidate deletion/error counters remain zero, batch 1 is still verified, and deletion_started_at is null. A delayed health sample found four MySQL running threads, zero callback backlog, zero lock waits, zero open transactions, and continued traffic (20 pay-ins and 9 payouts in two minutes), with no matching errors in the merchant, partner, or admin API logs. A real canary remains blocked pending a fresh backup/PITR confirmation, an explicit batch approval, and a new health approval inside the 05:00–05:59 WIB execution window.

The 2026-10-11 follow-up used the then-current admin image 41d0f53e84c891c57f1d723626dc50edd3916dc4. The disabled one-off Job transaction-archive-delete-preflight-b1-20261011 again returned ready, this time in 1,723 ms, with deletionEnabled=false and windowEligible=false. Independent checks confirmed batch 1 is still verified, all 47,152 exact candidates remain present, no candidate has a deletion timestamp or error, and MySQL had four running threads with zero callback backlog and zero lock waits.

The same checkpoint identified a new safety blocker unrelated to the archive queries: both admin API pods had previously terminated with JavaScript heap exhaustion, and the single Loki pod had accumulated 175 restarts with its last termination recorded as OOMKilled. Loki was still close to its 1 GiB memory limit during the check. Production response logging includes response bodies and sends them through both container log collection and a direct Loki transport; Promtail also has overlapping Kubernetes and fallback scrape paths. This is consistent with excessive logging pressure, although a dedicated fix must prove the exact contribution. Do not execute the deletion canary until Loki and the admin pods remain restart-free through an observation period, the logging path is bounded, and the fresh backup/PITR gate is confirmed. Cloud Manager was logged out at this checkpoint, so backup confirmation remains open.

The 2026-10-11 07:28–08:13 WIB mitigation removed direct in-process Loki delivery from the production admin, merchant, and partner APIs and retained a single stdout-to-Promtail path. HTTP middleware logs now contain bounded body metadata rather than payload values, local files are disabled by default and have fixed rotation when explicitly enabled, and Promtail uses one container source with persistent positions and a ten-minute replay cutoff. Loki also has a 4 MiB/s local ingestion limit, an 8 MiB burst limit, and a 768 MiB Go memory target. Production changes were delivered by rolling updates; all six API pods remained ready with zero restarts.

The first observation found one additional source: stale sandbox and manual IP-canary pods still used winston-loki against the shared production logging service. They accounted for periodic approximately 94 MiB / 230,000-line bursts even though Promtail had sent only about 7.4 MiB since its rollout. The same bounded change was deployed to the three sandbox APIs, and the manual canary was moved to the bounded production merchant image. All seven current production/sandbox/canary API workloads then reported no winston-loki dependency. Over the next 119 seconds Loki accepted approximately 817 KiB and 2,445 lines instead of another burst; its Kubernetes memory fell to 180–233 MiB. Production samples covered 88 merchant responses at 297 ms average and 78 partner responses at 237 ms average, with zero HTTP 5xx, zero pod restarts, four MySQL running threads, zero lock waits, and zero open transactions.

Linode Cloud Manager confirmed automatic daily full backups, continuous binary logs, and point-in-time recovery for the previous 14 days. This closes the backup/PITR confirmation gate. It does not authorize deletion: keep the canary disabled until a longer restart-free logging observation, a fresh in-window health check, and explicit batch approval are recorded.

The 2026-10-12 operator-approved daytime attempt added a fail-closed, dual-control window override in admin backend PR 199. The override requires both a one-shot Job environment flag and CLI switch, records its use in archive events, and does not weaken manifest, replica, callback, lock, approval-age, or row-limit gates. The first attempt stopped before deletion when the callback backlog became non-zero. A retry then exposed a MySQL timestamp representation defect: candidate DATETIME(3) strings ending in .000 were compared literally with equivalent live DATETIME strings without the suffix. It deleted zero rows and marked 100 parents skipped. PR 200 normalized only equivalent zero-fraction timestamps; 117 tests passed. Independent SQL proved all 100 statuses, timestamps, and snapshots matched, after which their skip markers and batch counter were reset in one audited transaction. A corrected-image preflight returned ready, but the final canary again stopped at its initial callback-health gate. Final state: batch 1 is deletion_paused, deleted_rows=0, skipped_rows=0, all 47,152 candidates have no deletion timestamp or error, and the one-shot override is no longer active. Do not retry during active daytime callbacks; use a fresh 05:00 WIB preflight and the standard window.

The 2026-10-13 05:07 WIB standard-window preflight returned ready in 1,415 ms with windowEligible=true and no override. The approved canary then deleted exactly 100 disbursement parents and 300 related rows in one 100-parent transaction in 4,289 ms. It skipped zero candidates and left batch 1 in deletion_paused with 11,893 clean parents and 46,752 clean graph rows remaining. Independent verification found no deleted parent still present, no missing or invalid archived snapshot, archive inquiry fallback working from both merchant and partner production APIs, and balance-history coverage for all 100 deleted payouts (101 retained ledger rows). Two post-delete observations showed zero callback backlog, zero current lock waits, no increase in the cumulative lock-wait counter, four MySQL running threads, continued pay-in and payout traffic, zero targeted lock/5xx log matches, and zero API pod restarts. Stop at the 100-parent boundary until a separate approval expands the deletion volume.

The operator provided that separate approval at 05:15 WIB in the same standard window. A fresh baseline and full preflight preceded each additional batch. Canary 2 deleted 100 parents and 298 related rows in 4,575 ms; canary 3 deleted 100 parents and 300 related rows in 4,320 ms. Both skipped zero candidates. The controlled first-day total is therefore 300 parents and 898 related rows (1,198 graph rows), with 11,693 clean parents and 45,954 clean graph rows remaining. All 300 source parents are absent, all archived snapshots are valid, 303 retained balance-history rows cover all 300 payouts, and a third-batch transaction resolves successfully through both merchant and partner archive fallbacks. Post-run checks showed zero callback backlog, zero current lock waits, no increase in the cumulative lock-wait counter, four MySQL running threads, zero targeted lock/5xx log matches, and zero API pod restarts. Batch 1 remains deletion_paused; do not run a fourth batch on the first deletion day.

At 05:25 WIB, a new preflight-only postflight returned ready in 966 ms with the standard window eligible, the override disabled, and deletion disabled. From the end of canary 3 through 05:26 WIB, the merchant API completed 117 responses and the partner API completed 95 responses with zero HTTP 5xx responses. Pay-in and payout traffic continued; merchant and partner p95 response latency was 789 ms and 697 ms respectively, and their maximum response latency was 1,056 ms and 795 ms. All six API pods remained ready with zero restarts. One merchant inquiry returned 502 at 05:14 WIB, before canaries 2 and 3: Payara answered at the 12-second upstream timeout boundary, with no deadlock or lock-wait signature. Treat it as an unrelated provider-latency event, not a deletion regression.

The next eligible deletion proposal is a future standard low-traffic window, after a fresh preflight and separate approval. Keep each database transaction at 100 parents. If the first transaction and its postflight are clean, the proposal may repeat up to five 100-parent transactions, with the existing pause, health gates, and immediate stop conditions between transactions. Do not convert this proposal into a single 500-parent transaction.

The operator approved guarded automatic deletion on 2026-10-13. Backend PR 201 deploys the production-only pga-be-admin-archive-delete CronJob at 05:10 WIB every day. It selects the oldest eligible verified or paused archive batch and processes at most 500 parents as five separately committed 100-parent transactions. Runtime callback, lock, and load gates run before deletion and again between transactions; replica lag, manifest, checksum, and source integrity are verified before deletion, with replica health checked again after the final transaction. The job has concurrencyPolicy: Forbid, no retry, a 45-minute deadline, no window override, and a separate ARCHIVE_SCHEDULED_DELETE_ENABLED kill switch. The worker also enforces the 500/100 limits and no-override rule in code, so a manifest-only change cannot weaken them. A failure leaves the batch paused and records its error; no eligible batch is a successful no-op.

The production image and CronJob were healthy at 05:38 WIB. A 05:43 WIB preflight-only smoke test exercised the new automatic oldest-batch selector, selected batch 1, and returned ready in 1,275 ms without deleting rows. The schedule was created after today's start deadline, so its first destructive run is 2026-10-14 at 05:10 WIB. This automation covers deletion of already verified archive batches; export/upload/verification remains a separate prerequisite.

The operator approved an accelerated export catch-up on 2026-10-13 without changing the daily deletion schedule or its 500-parent ceiling. Backend PR 202 adds a resumable server-side catch-up worker which invokes the existing weekly exporter sequentially. It does not define a second CSV format: every batch continues to use schema v2 CSV.gz parts, the 1,000-row and 5 MiB uncompressed file limits, object read-back verification, checksums, manifests, control totals, exact delete candidates, transaction lookup snapshots, and archive events. A verified, deleting, paused, or completed week is skipped. An existing planned, exporting, or failed week stops the catch-up for operator review rather than overwriting or duplicating it.

Before each week, the orchestrator requires database concurrency to be within its configured ceiling, zero due callback backlog, and zero active lock waits. It then waits 60 seconds before the next week. Source planning and graph reads continue to use the dedicated read-only replica; the primary receives only the same short archive metadata transactions used by the proven exporter. The automatic deletion selector now prioritizes a batch already in deleting or deletion_paused before an older newly verified batch, so batch 1 will continue rather than being displaced by the catch-up output.

One-off production Job transaction-archive-catchup-20261013 started at 17:18 WIB using image 77aec89c65702d506f8325cb0784427359c87077. It covers 19 disbursement weeks from 2026-04-27 through 2026-08-31, followed by 20 pay-in weeks from 2026-04-20 through 2026-08-31. The existing disbursement week of 2026-08-17 will be skipped. Initial evidence is clean: batches 2 and 3 verified the first two disbursement weeks, callback backlog and lock waits were zero, MySQL had four running threads, all service pods remained ready with zero restarts, and the catch-up pod used 153 millicores and 58 MiB. A 10-second primary sample during the run observed 59.6 queries/s, 3 inserts/s, 4.1 updates/s, zero deletes/s, zero row-lock waits, and zero callback backlog.

Progress and stop controls:

kubectl -n kingspay-production logs -f job/transaction-archive-catchup-20261013
kubectl -n kingspay-production get job transaction-archive-catchup-20261013
kubectl -n kingspay-production delete job transaction-archive-catchup-20261013

Deleting the Job is the export rollback/stop action. It does not remove any verified object, manifest, lookup, candidate, or audit record. A partially created batch remains non-deletable and requires review. The independent daily 05:10 WIB deletion CronJob remains the only automatic deletion path and still fails closed on health, replica, manifest, checksum, and source-integrity checks.

Phase 6 — controlled weekly operation

• Start with one week per night.
• Start an accelerated sequential export catch-up after the weekly exporter and production health gates passed.
• Review evidence after each of the first three batches.
• Automate deletion only after successful guarded canaries.
• Keep automatic transactions fixed at 100 parents and the daily ceiling at 500.
• Stop automatically on callback, lock, replica, integrity, or load failures.
• Increase the nightly limit only through a recorded decision.
• Replace per-run operator approval only after automation is separately approved.

Estimates

Scope	Estimate
One weekly export/upload/verification on replica	15–45 minutes
One weekly controlled deletion	45–120 minutes
Initial export and verification backlog	4–10 active hours
Initial deletion backlog	15–40 active hours
One week per night	approximately 3–4 weeks
Two weeks per night after successful canaries	approximately 10–14 nights

Estimates are recalibrated after the one-week canary. Deleted InnoDB pages become reusable but physical database files do not shrink without a separate table rebuild.

Dashboard relationship

Archival does not change dashboard business calculations because current summaries use today and yesterday. It reduces historical table pressure but does not eliminate the INSERT ... SELECT locking mechanism. The current-day five-minute refresh remains suspended, and the dashboard reconciliation must be converted to a non-locking read followed by a short upsert as a separate resilience change.

Decision log

Date	Decision	Status
2026-09-29	Use weekly archives with at least 30 days hot retention	Approved for planning
2026-09-29	Limit archive files to 1,000 rows and 5 MiB uncompressed	Approved for planning
2026-09-29	Require verified candidate IDs for deletion	Approved for planning
2026-09-29	Keep deletion operator-approved during initial rollout	Approved for planning
2026-09-29	Retain balance_history in the initial release	Approved for planning
2026-09-29	With no sandbox available, run the first canary as a guarded, one-off production Job during the off-peak window	Superseded by the 30-day 05:00 selection; deletion remains disabled
2026-09-29	Use 05:00–05:59 WIB as the default export window based on 30 complete days of production volume	Applied to the one-off canary; review periodically
2026-09-30	Accept the verified archive ledger and independent object read-back as the successful canary result	Complete; do not rerun the verified week
2026-09-30	Require the all-pool worker shutdown fix before another archive Job	Deployed in backend PR 181
2026-09-30	Use schema v2 with explicit \N nulls for all future exports; permit v1 only when exact retained-source comparison passes	Implemented in backend PR 182
2026-09-30	Run batch 1 restore only in isolated shadow tables at 05:00 WIB with deletion disabled	Scheduled for 2026-10-01
2026-10-01	Treat the first restore as a failed automated drill despite independently correct restored data because both validation queries must pass in-worker	Failure evidence retained; deletion remains disabled
2026-10-01	Fix mixed-collation and reserved-alias validation queries before retry	Deployed in backend PR 184; retry scheduled for 2026-10-02 05:00 WIB
2026-10-02	Accept restore audit event 68 plus independent exact comparison as the successful Phase 4 data result	Complete; 47,152 rows, zero mismatches, zero orphans
2026-10-05	Require successful one-shot process termination before Phase 5 worker development	Deployed in backend PR 185; lifecycle smoke Job succeeded
2026-10-05	Remove successful restore shadows only after retaining audit and independent verification evidence	Complete in audit events 69 and 70
2026-10-05	Block deletion until archived rows preserve merchant inquiry fields and partner callback lookup, not only transaction IDs/status	Compatibility implementation prepared; production deployment deferred from the 22:00 peak window
2026-10-05	Require production collation and owner-scope proof before snapshot backfill	11,993 of 11,993 candidates matched; binary/collated guards and platform ownership added before deployment
2026-10-06	Treat the first snapshot backfill Job as a safe pre-write failure because its pod lacked the production MySQL CA mount	Zero snapshots changed; failed Job retained as evidence
2026-10-06	Require absent optional pools to be safe during worker cleanup	Fixed and deployed in admin backend PR 187 before retry
2026-10-06	Accept the snapshot retry and live-source compatibility proof	11,993 of 11,993 verified in 5,755 ms; zero mismatches
2026-10-06	Deploy merchant and partner archived-transaction compatibility only after snapshot verification	Complete; deletion remains disabled pending a separately approved canary
2026-10-06	Add the deletion canary as a disabled one-shot worker rather than ad-hoc SQL or a schedule	Implemented with exact candidate IDs, 100–250 row transactions, runtime health gates, and explicit approval evidence; production deletion remains disabled
2026-10-06	Accept the disabled production deletion preflight for batch 1	Ready in 704 ms; all 47,152 source rows retained, health gates clean, and no delete state transition occurred
2026-10-11	Repeat the disabled preflight against the current production image	Ready in 1,723 ms; all candidates retained and database/callback gates clean
2026-10-11	Block the destructive canary on logging stability	Both admin pods had heap-exhaustion restarts and Loki had 175 restarts with an OOMKilled last state; deletion remains disabled
2026-10-11	Bound the shared logging path before reconsidering deletion	Production and sandbox/canary direct Loki clients removed; initial 119-second observation had normal ingestion, Loki at 180–233 MiB, zero API restarts/5xx, and zero DB lock waits; continue observation with deletion disabled
2026-10-11	Accept Linode backup/PITR evidence for the safety gate	Daily full backups plus continuous binary logs provide point-in-time recovery for 14 days; restore was not invoked
2026-10-12	Permit one audited, dual-control daytime window override without weakening other deletion gates	Implemented in backend PR 199; one-shot Jobs only, default remains 05:00–05:59 WIB
2026-10-12	Treat callback-health failures as a hard stop even with an approved time override	Two attempts aborted safely; no source rows were deleted
2026-10-12	Normalize equivalent MySQL zero-fraction timestamp representations before candidate comparison	Fixed in backend PR 200; 100 false skips were independently verified and reset, final counters are zero
2026-10-13	Accept the first destructive deletion canary for batch 1	100 parents plus 300 related rows deleted in 4,289 ms; archive fallbacks and retained balance history verified; no callback, lock, API, or throughput regression observed
2026-10-13	Pause after the successful 100-parent boundary	Batch remains deletion_paused; expanding volume requires a separate explicit approval
2026-10-13	Accept two additional 100-parent batches after a separate same-window approval	First-day total is 300 parents plus 898 related rows; all three batches passed archive, balance-history, callback, lock, API, and throughput checks
2026-10-13	Stop after three successful destructive canaries	Batch remains deletion_paused with 11,693 parents remaining; no fourth first-day batch
2026-10-13	Accept the 05:25 WIB read-only postflight observation	Ready in 966 ms; 212 API responses after canary 3 had zero 5xx, normal sub-second p95 latency, active pay-in/payout traffic, and zero pod restarts
2026-10-13	Keep the next deletion operation in a future standard window	Proposed ceiling is five separately gated 100-parent transactions; a fresh preflight and separate approval remain mandatory
2026-10-13	Approve guarded automatic deletion after the successful first-day canaries	Backend PR 201; daily at 05:10 WIB, oldest eligible batch, maximum 500 parents in five 100-parent transactions, fail-closed health gates, no overlap/retry/override
2026-10-13	Accept the production automation smoke test	New image and CronJob healthy; automatic selector chose batch 1 and returned ready in 1,275 ms without deletion; first scheduled run is 2026-10-14 05:10 WIB
2026-10-13	Run the historical export catch-up entirely on the server	Backend PR 202; sequential replica-backed weekly exports, health gate before every week, 60-second inter-week pause, existing schema v2 format and file limits unchanged, automatic daily deletion unchanged
2026-10-13	Preserve deletion continuity while new batches verify	Automatic selection prioritizes deleting and deletion_paused batches, then the oldest verified batch