Data Archive Runbook

• Status: DRAFT — CURRENT 11-TABLE IMPLEMENTED SCOPE
• Owns: prod retention rules, table classification, archive family boundaries and guarded execution order
• Does not own: Production write authorization, deployment authorization, backup infrastructure or application archive-search UI
• Update when: a table changes classification, a family policy changes, or an archive/restore verification changes
• Runtime: MySQL prod -> prod_archive; policy timezone America/Los_Angeles

Global Policy

Data class	Policy
Master, configuration or current state	Keep in prod; never age out automatically
Completed business history	Archive after the family business date is at least two years old
Soft-deleted business data	Do not archive; hard-delete only after dependent rows and files are handled
Parent and child data	Copy/read-back/delete as one family transaction
Active T_FILE row	Move with its parent family
File whose parent is already in prod_archive	Move active metadata to prod_archive; purge only soft-deleted metadata
True orphan T_FILE row	Purge only when the mapped parent is absent from both prod and prod_archive
Unknown T_FILE.TABLE_NAME	Keep; never infer a parent table or auto-delete
Log or audit data	Default: keep two years in prod and ten years in archive; T_SUPER_ADMIN_LOG is an explicitly deleted-table exception
Missing reliable terminal state or business date	Keep in prod; do not substitute CREATED_AT by guesswork

All age comparisons use America/Los_Angeles. Rows updated within the previous 24 elapsed hours are excluded. prod source rows are deleted only after archive copy read-back and an unchanged-row final check. Schema synchronization adds or modifies prod_archive columns to match prod and adds the required T_FILE active-object lookup index when missing; it does not drop archive-only columns or indexes. CLIENT_ID=2 is temporarily excluded from candidate, child and final-delete checks until that tenant is explicitly enabled. Existing Client 2 archive rows are disposable test data and are not restored.

Active file metadata moved with its parent never deletes the S3/EFS object. A purge candidate is first soft-deleted in the family transaction; S3/EFS cleanup runs only after that commit, under the same normalized STORAGE+PATH lock used by metadata registration. Successful cleanup hard-deletes the soft-deleted metadata immediately; previously completed application cleanup rows are also purged in a bounded set, independent of whether their current/master parent remains in prod. Application cleanup failures remain identifiable by T_FILE.DELETED_AT IS NOT NULL AND OBJECT_CLEANUP_AT IS NULL; each weekly apply retries a bounded set before family processing. Reference checks are prefetched once per retry batch, and one storage failure is isolated to that row. A row whose storage or path cannot produce the shared object lock is attempt-stamped, logged as failed and left pending; it is not reinterpreted, deleted or marked complete, and valid EFS/S3 rows in the same batch continue. Prepared-only archive batches and interrupted post-commit restore markers are reconciled from exact source/archive row state before another restore is allowed. Parent-family restore also includes active file metadata archived later by a separate stranded_files batch.

Production Inventory Snapshot

Read-only snapshot captured 2026-08-01:

Item	Value
prod base tables	71
Current Production archive tables	9
Estimated prod size	551.5 MB
Prior 9-table Production scope	418.1 MB / 75.8%
Current plus classified archive scope	532.9 MB / 96.6%

Table Classification

Current implementation — 11

T_FILE, T_TRANSLOAD, T_TRANSACTION, T_DELIVERY, T_TRANSACTION_LIST, T_SCALE_TICKET, T_EQUIPMENT, T_EQUIPMENT_LOAD, T_EQUIPMENT_COMMODITY, T_COMMERCIAL_ORDER, T_COMMERCIAL_ORDER_ITEM.

The commercial-order family is implemented locally but is not yet synchronized, dry-run, applied or deployed in Production.

Classified archive expansion — remaining 23

T_LOADING_PLANNER, T_YARD_DISPATCH_QUEUE, T_LOAD, T_LOAD_ITEM, T_LOAD_ALLOCATION, T_INVENTORY_LOT, T_INVENTORY_LOT_LEDGER, T_DRIVER_LOG, T_FUEL, T_STAFF_TIME_EVENT, T_OPERATION_EVENT, T_PROCESS_TRANSITION_AUDIT, T_PROCESS_TRANSITION_ENTITY, T_PROCESS_TRANSITION_EVIDENCE, T_MAIL_MESSAGE, T_MAIL_REQUEST_ITEM, T_EQUIPMENT_REPAIR_LOG, T_WAREHOUSE_GRID_OCCUPANCY, T_INVENTORY_LOT_SNAPSHOT, T_DOMESTIC_PICKUP_POOL.

T_DELIVERY_ORDER, T_CUSTOMER_PO, T_CUSTOMER_PO_ITEM.

These tables are documented future scope. They are not accepted by the current archive script and must not be added without a family-specific local archive/restore test and Production dry-run review.

The three order tables above remain confirmed archive targets, but the current schema has no reliable terminal state and business date. They stay in prod until those criteria are defined; CREATED_AT is not used as an inferred substitute.

Deleted from prod — 1 — not counted in the current 71

T_SUPER_ADMIN_LOG: table deletion is complete. Do not recreate or archive it.

Confirming — 2

T_TRANSLOAD_APPOINTMENT_BLOCK, T_EQUIPMENT_ACTIVITY.

Conditional — keep until the missing terminal rule is explicit — 4

T_BOOKING, T_AI_VOICE_CASE, T_AI_VOICE_CALL, T_AI_VOICE_TRANSCRIPT_SEGMENT.

Always keep in prod — 31

T_CARRIER_MASTER, T_COMPANY, T_COMPANY_EMAIL, T_MASTER, T_COMMODITY, T_EQUIPMENT_INFORMATION, T_CHASSIS, T_TRANSLOAD_RATE, T_TRUCKING_RATE, T_CHASSIS_RATE, T_STORAGE_RATE, T_COMMODITY_RATE, T_BILLING_PRESET, T_WAREHOUSE_GRID, T_WAREHOUSE_LOCATION, T_YARD_LOCATION, T_TERMINAL_STATUS, T_CLIENT, T_USER, T_ROLE, T_SYSTEM, T_MODULE, T_MODULE_PAGE, T_PACKAGE_TIER, T_MENU, T_TENANT_CONFIG, T_TENANT_MODULE, T_USER_MENU_FAVORITE, T_USER_OPERATION_EVENT_PREFERENCE, T_MAILBOX_WATCH_STATE, T_TRANSACTION_TYPE.

Expansion Family Rules

Family	Root condition	Required relationship handling
Loading planner	FINISHED or CANCELED; JOB_END_TIME, then appointment fallback, is two years old	Planner files move or purge with planner
Yard dispatch queue	COMPLETED or CANCELLED; terminal timestamp is two years old	No claimed/current queue reference remains
Load/order	COMPLETED or CANCELLED; final movement timestamp is two years old	T_LOAD_ITEM and related allocations move atomically; no active transload, equipment, inventory or commercial-order dependency
Inventory lot	CLOSED; closed date is two years old; remaining, reserved and net weight are zero	No active allocation or warehouse occupancy; ledger and finalized snapshots move with lot
Driver log	FINISHED or CHECK_OUT; finish date is two years old	No prod.T_DELIVERY reference remains, or delivery moves in the same family
Fuel	POST_DATE is two years old	No live financial reference remains
Process audit	OCCURRED_AT is two years old and LEGAL_HOLD_AT IS NULL	Entity and evidence rows move with audit root
Mail	Message is two years old and every request item is terminal	Message and request items move together; mailbox watch state stays in prod
Warehouse occupancy	RELEASED; release time is two years old	Grid and location master rows stay in prod
Commercial order	FULFILLED or CANCELLED, or soft-deleted; CONTRACT_DATE is two years old	Parent/item files, items, then parent; block linked orders and serialize load, load-item, link and file writers through parent-row CAS

Current Delivery Sequence

Until their own archive families are implemented, T_MAIL_REQUEST_ITEM, AI voice rows, yard-dispatch rows, staff-time events, operation events, warehouse-placement rows and T_DOMESTIC_PICKUP_POOL block a referenced T_TRANSLOAD. Delivery-linked staff-time events block T_DELIVERY. Yard-dispatch container rows block T_EQUIPMENT, and T_LOAD.TICKET_NUMBER blocks the matching T_SCALE_TICKET. The reference-guard migration serializes new or changed references through the same parent-row CAS used by archive deletion.

T_SCALE_TICKET_EVENT is not part of the current qa 11-table archive scope. Before its separate schema change is promoted, it must either move with T_SCALE_TICKET or block the ticket through the same parent-row serialization rule.

1. The current 11-table implementation has been verified in isolated local MySQL, including schema drift, active children/files, soft-deleted rows, parentless files, restore and replay refusal.
2. Synchronize the two commercial-order archive tables, then run Production dry-run and review exact candidates and blocked references.
3. Apply one bounded Production batch, read back archive/source/manifest state, and exercise restore when the canary requires it.
4. Complete the eligible current run, read back zero remaining candidates, then deliver and deploy the current 11-table scope.
5. Expand only from the classified remaining 23-table list in independent family waves.

Verification Record — 2026-08-02

Check	Result
Isolated MySQL archive/delete/restore across the current 11 tables	PASS
Schema add/modify sync and archive-only column retention	PASS
Active, soft-deleted, deleted-parent, stranded and true-orphan file fixtures	PASS
Commercial-order child-first archive, parent-first restore and parent-CAS rollback	PASS
Commercial-order outbound-link exclusion and item-reference CAS rollback	PASS
Concurrent commercial-order writer/archive parent-row CAS serialization	PASS
Reference guards including mail/AI/yard/staff/event/warehouse/domestic-pickup and order-ticket rules	PASS; rollback 0, forward 42/42, invalid references rejected with SQLSTATE 45000
Delivery staff-time and domestic-pickup policy exclusion fixtures	PASS; blocked with reference, eligible after reference removal
Transaction CANCELLED terminal archive and restore timestamp retention	PASS; CONFIRMED remained in prod
File writer parent-before-object lock order	PASS
Partial restore external dependency UPDATED_AT preservation	PASS
Prior 9-table Production schema drift and policy SQL	0; 7/7 validated
11-table Production schema sync and dry-run	NOT_RUN
Production business-family candidates	0
Production stranded files	1,354 moved from prod.T_FILE to prod_archive.T_FILE; purge 0
Production true orphans	0
Production manifests	20260801T114117Z-eafaea1ffa6a (500), 20260801T114204Z-6e7ce16c647a (854)
Final Production read-back	candidate 0; manifest IDs 1,354; source conflicts 0; archive-parent missing 0

---

Source: docs/runbooks/DATA_ARCHIVE_RUNBOOK.md — ctc repository · This page is an English translation of the source document. Identifiers, file paths and code symbols are unchanged.