3.6 Data Retention, Legal Hold, and Audit Export Pack

Data has mass. Accumulating terabytes of high-frequency sensor data without a purge strategy ensures system paralysis. Conversely, deleting compliance records prematurely ensures legal liability. You must implement a Data Lifecycle Management (DLM) strategy that balances performance, cost, and risk.

Retention Policy by Data Class

Not all bytes are created equal. Partition your data into classes with distinct expiration dates.

Class A: Compliance & Genealogy (The "Forever" Data)

• Content: Serial Numbers, Pass/Fail Results, Operator IDs, RMA History.
• Retention: Warranty Period + 1 Year (Consumer) or 15 Years (Automotive/Medical).
• Action: Never delete until policy expires.

Class B: High-Frequency Telemetry (The "Engineering" Data)

• Content: Waveforms, Torque curves, Temperature profiles (at 100ms resolution).
• Retention: 12 Months.
• Logic: If you haven't analyzed a temperature spike within a year, the data is noise. After 12 months, down-sample to "Min/Max/Avg" summaries and purge the raw rows.

Class C: IT & Security Logs (The "Forensic" Data)

• Content: Login attempts, API calls, Firewall logs, User Access changes.
• Retention: 1 Year (Standard Cybersecurity requirement).
• Action: Rolling purge.

Class D: Large Binary Objects (BLOBs)

• Content: AOI Images, X-Ray Tiffs, PCB Schematics.
• Retention: 6 Months (unless linked to a confirmed Defect).
• Logic: Storing terabytes of "Good" images is a waste of capital. Keep "Fail" images for Class A duration.

Storage Strategy: Hot, Warm, Cold

Do not keep 10 years of history in your production SQL database. It kills query performance.

• Hot (Tier 1): Production DB (NVMe SSD).
  • Age: 0 – 12 Months.
  • Purpose: Instant operations, dashboards, active reporting.
• Warm (Tier 2): Data Warehouse / Read-Replica (HDD).
  • Age: 1 – 3 Years.
  • Purpose: Monthly/Yearly analytics.
• Cold (Tier 3): Archive (S3 Glacier / Tape).
  • Age: 3+ Years.
  • Purpose: "In case of lawsuit." Low cost, high latency (hours to retrieve).
• The Purge Script: Automate the movement. If Record_Age > 12 Months → Move to Cold → Delete from Hot.

The Legal Hold (The "Stop Shredding" Button)

When litigation or a major recall begins, automated purging becomes evidence destruction. You need a "Kill Switch."

• Trigger: Legal Department notifies IT of a specific Lot, Product, or Date Range.
• Mechanism:
  1. Create a Legal_Hold flag in the database.
  2. Logic: If Legal_Hold = True → Then Skip Purge Script.
• Scope: The hold must apply to all associated data (Emails, Logs, Genealogy, Telemetry).

The Audit Export Pack

When an auditor (FDA, ISO, UL, Customer) asks for data, they are testing your retrieval capability. They expect a coherent story, not a raw SQL dump.

The SLA: You must generate this pack in < 30 Minutes.

The Structure

The "Export Pack" is a zipped folder containing four distinct artifacts for a specific Serial Number (SN):

1. The Genealogy Report (PDF)

• Tree view of all child components (Lots/SNs).
• List of all equipment used (Asset IDs).
• List of all operators involved (User IDs).

2. The Process History (CSV)

• Parametric data for every step.
• Columns: Step, Machine, Timestamp, Setpoint, Actual_Value, Result, Upper_Limit, Lower_Limit.
• Why CSV? So the auditor can load it into Minitab/Excel for their own analysis.

3. The Master Data Snapshot (PDF)

• Evidence of the configuration at that moment in time.
• BOM Revision and Process Routing Revision active on the production date.

4. The Compliance Certificates (PDF)

• Links to Calibration Certs for the tools used.
• Links to Training Records for the operators involved.

Validation: The "Restore" Drill

Backups are theoretical; Restores are reality.

• Frequency: Quarterly.
• Drill: IT selects a random archived Lot from 3 years ago.
• Objective: Retrieve the data from Cold Storage and generate the Audit Export Pack.
• Fail Condition: If retrieval takes > 24 hours or data is corrupt.

Final Checklist

Category	Metric / Control	Threshold / Rule
Class A	Compliance Retention	Keep Genealogy/Quality data for Warranty + 1 Year (Min).
Class B	Telemetry Purge	Purge raw high-freq sensor data after 12 Months.
Performance	Tiering	Move data > 1 year old to Cold Storage to protect Prod DB speed.
Legal	Hold Mechanism	"Legal Hold" flag overrides all purge scripts.
Audit	Speed	Audit Export Pack generation time < 30 Minutes.
Format	Legibility	Export Pack must include human-readable (PDF) and machine-readable (CSV) files.
Validation	Restore Test	Verify Cold Storage retrieval capability every 90 days.