Value Statement

Refactor the data retention job to use PostgreSQL native triggers/functions for automatic partition table management, reducing application complexity and improving database-native integration.

Current Implementation

The current approach manages partition tables at the application layer through Go code in manager/pkg/processes/cronjob/task/data_retention.go. This periodically creates and deletes partition tables using application logic.

Proposed Improvement

Replace the application-layer partition management with PostgreSQL native triggers or functions to automatically manage partition table creation and deletion. This approach:

• Simplifies codebase by removing application-layer partition management logic
• Provides better database-native integration
• Reduces application complexity and maintenance burden
• Leverages PostgreSQL's built-in capabilities for partition management

Context

Related to ACM-27968 (Partition table missing bug fix), this refactoring will make partition management more robust and automatic.

Definition of Done for Engineering Story Owner (Checklist)

• Research and design PostgreSQL trigger/function approach for partition management
• Implement PostgreSQL trigger/function to replace current Go-based partition logic
• Remove obsolete partition management code from manager/pkg/processes/cronjob/task/data_retention.go
• Verify partition creation/deletion works automatically via database triggers

Development Complete

• The code is complete.
• Functionality is working.
• Any required downstream Docker file changes are made.

Tests Automated

• Unit/function tests have been automated and incorporated into the build.
• 100% automated unit/function test coverage for new or changed APIs.

Secure Design

• Security has been assessed and incorporated into your threat model.

Multidisciplinary Teams Readiness

• Create an informative documentation issue using the Customer Portal Doc template that you can access from The Playbook, and ensure doc acceptance criteria is met.
• Link the development issue to the doc issue.

Support Readiness

• The must-gather script has been updated.

This issue was generated with Claude Code