Enforce a Data Retention Policy with TTL Jobs
Schedule a job that deletes records past their retention window so the database does not hold data longer than the policy allows.
Task
Implement DELETE FROM events WHERE created_at < NOW() - INTERVAL '90 days'; to close a known attack surface before the next security review finds it.
When to use this
A penetration test report flags the missing DELETE FROM events WHERE created_at < NOW() - INTERVAL '90 days'; as a medium-severity finding on a public endpoint.
Prerequisites
- A running application with the vulnerable endpoint
- Access to security headers or middleware config
Steps
1. Configure DELETE FROM events WHERE created_at < NOW() - INTERVAL '90 days'; in the project configuration file with the recommended defaults.
DELETE FROM events WHERE created_at < NOW() - INTERVAL '90 days';
Expected output: The configuration is accepted and DELETE FROM events WHERE created_at < NOW() - INTERVAL '90 days'; runs without errors on the first invocation.
2. Run the check locally to verify DELETE FROM events WHERE created_at < NOW() - INTERVAL '90 days'; produces the expected result before pushing.
pnpm test
Expected output: Local run matches CI behavior; no surprises after push.
3. Add the gate to the CI pipeline so DELETE FROM events WHERE created_at < NOW() - INTERVAL '90 days'; blocks merge on failure.
- name: Run add data retention policy
run: pnpm test
Expected output: Pull requests with failing DELETE FROM events WHERE created_at < NOW() - INTERVAL '90 days'; are blocked from merging.
Troubleshooting
Problem
The check passes locally but fails in CI
Solution
Pin dependency versions and the runtime to match CI exactly; reproduce with the same Docker image or act.
Problem
Configuration is rejected with a schema error
Solution
Check the tool version matches the config schema; older versions may not support newer fields.
Limitations
- The gate only runs on events that trigger the workflow; locally pushed commits bypass it.
- Configuration drift is possible if someone edits settings outside version control.
Close the attack surface
Start by measuring the problem. Schedule a job that deletes records past their retention window so the database does not hold data longer than the policy allows. The most common mistake is adding the gate without first confirming it catches the issue you care about. Run DELETE FROM events WHERE created_at < NOW() - INTERVAL ‘90 days’; against the current codebase and inspect the output before wiring it into CI.
DELETE FROM events WHERE created_at < NOW() - INTERVAL '90 days';Verify and monitor
Once the gate is in place, make failures actionable. A failing DELETE FROM events WHERE created_at < NOW() - INTERVAL ‘90 days’; should point to the exact file and line, not a generic error. Pin the tool version in the lockfile so a upstream release does not silently change behavior. Review the gate output in every pull request just like a test result.