Legal Hold Discovery Index Sync and Purge Automation

Role: Senior Legal Operations Database Architect specializing in regulatory e-discovery pipelines and statutory compliance.

Context

• Primary transactional database: {{source_rdbms}}
• Destination discovery search index: {{target_index}}
• Active legal matter identifier: {{discovery_case_id}}
• Jurisdiction data retention limit in days: {{jurisdiction_retention_days}}
• Excluded legal hold tag identifiers: {{excluded_hold_tags}}
• Processing batch size limit: {{batch_size}}

Task

Build an idempotent database synchronization and regulatory purge script that extracts active matter records for {{discovery_case_id}} from {{source_rdbms}}, syncs them into {{target_index}}, and safely purges unindexed records exceeding {{jurisdiction_retention_days}} days unless protected by {{excluded_hold_tags}}.

Method

1. Define connection and session isolation parameters for {{source_rdbms}} to avoid locking active litigation matter tables.
2. Construct cursor-based pagination logic using {{batch_size}} to process discovery records incrementally.
3. Implement filter logic isolating records matching {{discovery_case_id}} for replication to {{target_index}}.
4. Design a strict protective check verifying records containing any tag in {{excluded_hold_tags}} are marked immutable.
5. Write the batch purge deletion statement targeting expired records older than {{jurisdiction_retention_days}} days without active hold tags.
6. Add audit log insertion statements capturing timestamp, deleted row count, and matter ID per batch execution.
7. Wrap synchronization and deletion sequences in explicit transactional savepoints with exception hooks.

Constraints

• MUST NOT delete or modify any record associated with {{excluded_hold_tags}} under any execution branch.
• MUST execute mutations in batches limited strictly to {{batch_size}} rows to prevent lock escalation.
• Script syntax must be directly executable against {{source_rdbms}}.
• Error handlers must log failed record identifiers without terminating the entire batch cycle.

Output format

• Section 1: Transaction and Lock Management Strategy (bulleted overview, under 100 words).
• Section 2: Complete Executable Maintenance Script (fully formatted SQL or PL/SQL script).
• Section 3: Audit Trail Verification Query (SQL statements to check compliance logging).
• Section 4: Dry-Run and Safety Verification Instructions (step-by-step checklist).

Self-review

• Verify that {{excluded_hold_tags}} bypasses all destructive DELETE and UPDATE operations.
• Ensure batch looping correctly handles the {{batch_size}} parameter without infinite loop conditions.
• Confirm retention calculations accurately convert {{jurisdiction_retention_days}} against UTC timestamps.