Compliance module triggers database capacity alerts, blocking workflow approvals

QMS Qualio
, , , , , , , ,
1

We’re hitting critical database capacity alerts in our Qualio compliance module and it’s completely blocking document approvals. The issue started after our Q1 audit cycle when we had a surge of compliance documents.

Checking Admin Center shows we’re at 94% capacity on the compliance_records table. Users get timeout errors when trying to approve documents:


ERROR: Lock wait timeout exceeded
Table: compliance_records
Operation: UPDATE approval_status

I’ve looked at our retention policies but nothing seems configured for automatic archiving. We have compliance records dating back to 2020 still in the active database. Is there a way to monitor storage growth patterns and set up archiving policies without losing audit traceability? Our compliance team needs those approval workflows functional by Monday.

2

For immediate workflow restoration, you need to address the table locks first, then implement proper archiving. Here’s the complete solution:

Database Storage Monitoring: Set up automated alerts in Admin Center before you hit capacity. Navigate to System Settings > Database Monitoring and configure alerts at 75%, 85%, and 90% thresholds. Enable daily growth tracking to predict when you’ll hit limits. This gives you proactive warning instead of emergency firefighting.

Table Lock Troubleshooting: Your lock timeouts are caused by missing indexes on the compliance_records table. Add these indexes immediately:

CREATE INDEX idx_approval_status ON compliance_records(approval_status, document_id);
CREATE INDEX idx_created_date ON compliance_records(created_date);
CREATE INDEX idx_record_status ON compliance_records(record_status, last_modified);

These indexes will eliminate full table scans during approval workflows. Your UPDATE operations will find target records instantly instead of locking the entire table.

Archiving and Retention Policies: Go to Compliance Module > Configuration > Retention Policies. Create a tiered retention strategy:

  • Active records (0-2 years): Live database, full indexing
  • Recent archive (2-5 years): Archive tables with read-only access
  • Long-term archive (5+ years): Compressed storage with audit trail intact

Configure automatic archiving rules:

AUTO_ARCHIVE_AGE=730 -- days (2 years)
ARCHIVE_BATCH_SIZE=1000
ARCHIVE_SCHEDULE=WEEKLY_SUNDAY_02:00

Maintaining Audit Traceability: Critical - when archiving, preserve the complete audit chain. The archive process must maintain:

  • Original timestamps and electronic signatures
  • Full approval history with user IDs
  • Document version links and change records
  • Regulatory compliance metadata

Test your archiving process on a small batch first. Verify that archived records are retrievable through the Compliance module’s search interface with full audit trail visible. Once confirmed, run the bulk archive for records older than 2 years.

For your immediate Monday deadline: Apply the indexes tonight (takes 15-30 minutes), which will resolve the approval workflow blocks. Then schedule the archiving process for this weekend to bring your capacity back under 70%.

3

Be careful with archiving compliance records - audit traceability is non-negotiable. Whatever solution you implement, make sure archived data maintains the complete approval chain, timestamps, and electronic signatures. I’d recommend keeping at least 2 years of records in the live database for active audits and regulatory inspections. Older records can archive but need quick retrieval capability.

4

Thanks for the input. I checked database metrics and we’re definitely seeing exponential growth - went from 2GB to 18GB in 6 months. The approval queries are indeed doing table scans on status updates. I need specific steps to set up archiving while maintaining audit integrity. What’s the recommended configuration approach?

5

94% capacity is definitely critical. You need immediate relief plus a long-term strategy. Short term: identify records older than 3 years that can be moved to archive tables. Long term: configure retention policies in Compliance Settings. The key is maintaining audit trail integrity - archived records must still be accessible for regulatory queries but don’t need to be in the live transaction tables.

6

I’ve seen this exact issue before. First thing - check your database monitoring setup. Admin Center should show you table growth trends over time. Navigate to System Health > Database Metrics and look at the compliance_records growth chart. If you’re seeing exponential growth without any archiving, that’s your culprit. The table locks are happening because the table is too large for efficient updates during high-concurrency approval workflows.