My client has implemented an audit solution to store an audit entry every time a customer profile is changed. This will be stored in a table, lets say(cust_profile_audit). This audit record will contain information of what actually got changed as part of that update.
The client is noticing audit records table is growing at a significant speed and the table count is already in millions. Now, they want to control the table size and want to archive older records(more than 6 month old).
Couple of options discussed are -
moving the older records into a archive/clone table(cust_profile_audit_archive) and delete from main table.
Or, generate a pdf file based on audit record and store it in pega cloud repository, and then delete the record from main table.
Just wondering if there is a preferred approach (s3/db) based on similar use cases.
It really depends on client defined policy for data retention. if they need this to be stored in DB for long period of time, you can move to another table but if there is no such policy, moving to a file on S3 make more sense.
I believe keeping the archived audit data in a separate archive table/database rather than converting each record into a PDF works.
Since the audit data is structured, keeping it in DB makes it easier to search or retrieve later if needed. The archival can be done periodically in batches for records older than 6 months, followed by deleting them from the main table.
The best approach is keeping the archived audit data in a separate table.Additionally, ensure an index is maintained on the archive table for key fields (e.g., Customer ID, Update Date) so archived audit records can be retrieved efficiently when required for compliance or investigations.