Database Auditing Architecture Using Asynchronous Queues and Delta Storage
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional project management tools lack the ability to efficiently record and audit historical changes to custom fields in a way that ensures data integrity and compliance with regulatory requirements, while also minimizing the impact on server performance and database size as databases grow.
Innovation Solution
The auditing architecture employs an asynchronous priority queue to defer auditing tasks during system inactivity, uses a baseline and differences table system to store data efficiently, and triggers to capture database changes, allowing for rollback to previous versions with minimal performance impact.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If synchronous auditing operations are performed immediately after database changes, then data integrity and compliance are ensured, but server performance deteriorates and database operations are slowed down
Solution Approach 1:
The patent implements preliminary action by capturing database change events through triggers at the moment they occur, storing them in an auditing messages table with metadata (username, timestamp, table name) before actual auditing processing begins. This preliminary capture ensures no data changes are missed while deferring the resource-intensive auditing operations to later when server resources are available.
Solution Approach 2:
The patent applies dynamics by implementing an asynchronous priority queue system that dynamically adjusts auditing operation timing based on server resource availability. The queue timer job continuously monitors and processes auditing messages when resources permit, allowing the system to flexibly balance between maintaining data integrity and preserving server performance under varying load conditions.
2Loss of information
If complete historical data is stored for every database change, then comprehensive audit trails are created, but database size increases significantly
Solution Approach 1:
The patent extracts only the essential auditing information from complete database records, storing in the differences table only the specific attributes that changed (attribute name and previous value) rather than duplicating entire records. This selective extraction maintains comprehensive audit trails while minimizing the storage overhead in the auditing database.
Solution Approach 2:
The patent segments the auditing data storage into two distinct tables: a baseline table storing only the current state of audited data, and a differences table storing only the changes. This segmentation allows the system to reconstruct historical states on-demand by applying differences to the baseline, eliminating the need to store complete historical copies of every record.
3Reliability
If auditing operations are performed frequently to ensure data accuracy, then compliance with regulatory standards is improved, but system resources are consumed excessively
Solution Approach 1:
The patent implements periodic action through the queue timer job that processes auditing messages at scheduled intervals when server resources are available, rather than performing auditing operations continuously or synchronously with every database change. This periodic processing ensures compliance requirements are met while significantly reducing overall system resource consumption compared to continuous auditing.
Data Source
AI summary
A server auditing process that stores only a single up-to-date data record along with the differences relative to previous changes in the record that allow the user to move “backward in time” to recreate previous values. The auditing feature introduces a baseline database table and a difference database table for each existing database table. The baseline table stores the most recent auditing data and the differences table complements the baseline table by storing pairs in the form of attribute name and previous value. By applying difference rows to baseline data, the user is able to recreate the data as it existed at any given timestamp and at a minimal storage cost. The architecture minimizes the impact of auditing on system performance by employing an asynchronous priority queue so that task processing is deferred until the system has adequate resources and other work is not affected.