Loading...
Design a system that can efficiently audit batches of database transactions to ensure data integrity, compliance with predefined rules, and historical correctness. The service should be able to handle large volumes of data and provide detailed reports on the audits.
-Perform auditing with predefined rules/compliance
-Historical correctness on audits is important
-Should be able to a predictable increase in load throughout the day from auditor activity but do not expect large bursts of traffic
-Create reports on audits (amazon athena?)
-ACL system for different auditor levels (nice to have)
- support 50 concurrent auditors
-Process 5 million weekly raw data transactions in batch mode
-Support up to 500 audit rules
-Batch processing must be complete in 12 hours
-Retain audit data for 10 years
-Must handle 3x normal load during month end audits
-Must be highly consistent to ensure auditing process runs with most up to date data
-Must have high partition tolerance, since we are dealing with lots of data
-Must reject some requests in order to prevent auditing being done on stale data during a partition event (database downtime, network connectivity loss etc)
I will choose to use a relational database since we have well defined data and strong consistency is important for this system to work correctly. We also can leverage the indexing features of something like sql server to optimize reads from highly populated tables like the audit table.
CAPACITY ESTIMATION:
Audit table in total is 130bytes per audit. If we have a 10 year life span and have 5 million weekly audits being generated by this system we have 5000000 * 52 * 130 = 34gb
As our database grows, we can partition our data into different database shards. We can even partition this data according to the index, storing parts of our audit data in appropriate database shards to optimize our queries
DATABASE DESIGN:
Assuming the raw data that is transactions is a log of database transactions:
RawDataTransaction{
id: bigInt,
transaction_table: string,
transaction_date: timestamp,
transaction_type: string,
to_delete: boolean,
upload_date: timestamp,
batch_id: bigint
}
This is used to seperate raw data uploads into manageable chunks and to track upload process
DataChunk{
id:bigInt,
start_row: int,
end_row: int,
upload_batch_id: bigInt,
status: enum('pending','processing','completed','failed')
}
After weekly(or whatever timeframe we want) data ingestion, temporarily store this data in the database with a false to_delete flag, once this data is processed by the audit service, we can soft delete it by making to_delete true and deleting off business hours or during low traffic times. We soft delete because ideally we don't want to delete it right away and want to follow data deletion policy and allow developers to refer back to failing uploads for a period of time
Comment{
id:bigInt,
auditor_id: bigInt,
audit_id: bigInt,
comment_text: string,
date: timestamp
}
Audit{
id: bigInt,
auditor_Id: bigInt,
transaction_id: bigInt, -> Id of original DB transaction
transaction_date: timestamp,
transaction_table: string,
transaction_type: string (delete, update, etc)
broken_rule_id: bigInt,
audit_status: ENUM('unassigned', 'assigned', 'in_progress', 'resolved'),
severity: string,
flagged_date: timestamp,
assigned_to: bigInt,
assigned_at: timestamp,
version: int
}
For the audit table, I purposefully leave out the value that caused the flag and instead provide the raw data table name and transaction id, this lets an auditor know where the data is coming from and what flag is being raised. I do this on purpose because it is very broad what data value is being flagged (money, date, multiple claim etc) and that value would have to be very specific to the audit system we are building.
For multiple flags on one transaction, we will create multiple audit records on the same raw data transaction. This is because it makes it much easier to query as an auditor since having an array of transactions on one audit row will be slow to query.
Auditor{
id: bigInt,
role: string,
access_control_level: string,
name: string,
bucket_id: string,
email: string
}
Report{
id: bigInt,
athena_report_id: string,
date: timestamp,
name: string
}
Bucket{
id: bigInt,
tokens: int,
auditor_id: bigInt,
refill_per_minute: int
} -> Used to rate limit auditing for auditors, this is because we don't want an auditor to make many changes to an audit in a short period of time which may affect data consistency
Event{
id: bigInt,
event_type: string,
date: timestamp,
payload?: RawDataTransaction | comment| etc
}
Rule{
id: bigInt,
rule_name: string,
rule_details: string,
drafted_ai: timestamp
}
This will be an event that will be handled in parallel by a worker, it could have many different types of payload, for example it could be a Generate Audit event with a payload of RawDataTransaction which will create an audit for us based off raw data, or it could be an auditor leaving a comment on an audit etc
I will use a relational database like SQL server which can leverage its built in row level security to limit which audits an auditor can see in the database. We can limit what audits they see off some feature we can further build out in the future but for now maybe it can be based off the transaction table column in the Audit table. Limiting users by what table they are able to access and maybe certain values in the table such as policy numbers etc based on a user's ACL from something like azure active directory. Not enough information has been given but this is an example of an added level of security we can add since this is an internal application.
Login(username, password) -> This can be done in a few ways since it is an internal application, using a user's login credentials to eliminate a traditional login screen and their ACL level to enforce row level security on which audits they see, or we have a traditional user login where they type in their credentials and we check this against a hash+salted DB to see if they have access, (use something like OAuth to generate a login token for them)
UploadRawData(RawDataTransaction,date) -> This will handle weekly data uploads for our audit generation service from our raw data source. This will be done in chunks for tracking of any potential errors or failures
AuditTransaction(RawDataTransaction) -> This will be the main function of our batch job that will be review a raw data transaction, compare it with our predefined rules and raise any flags if needed. We will create rows in our Audit table for each rule violated. After audit generation completion, emit soft delete updates on the raw data transaction table to initiate soft delete on audited rows.
AuditBatchTransactions(date) -> This will be a weekly job that will create an AuditTransaction event for each raw data transaction in the raw data transaction table after a certain upload date. If this job fails, then that is fine since the soft delete column would not have been changed. Our weekly audit job will rerun to create audits for any audits that don't have a True to_delete column value
EscalateAudit(audit_id,auditor_id) -> This creates a notification event that will notify admins in the auditor table via email about an audit that requires attention (Good idea to do this inside the audit service in the future via a notification system). This is a protected/rate limited route
CreateComment(auditor_id, audit_id) -> Creates a comment for a specific audit, this captures the author's id (auditor id) and the date for a comment thread under an audit. This is a protected/rate limited route
LoadAudit(audit_id) -> This loads an audit for an auditor to view as well as all the associated comments
SubmitAudit(audit_id) -> When the assigned auditor has made changes to an audit/left comments etc, they can submit the audit and save its changes. This creates an event to update the data.
AssignAudit(audit_id, auditor_id, admin_id) -> This assigns an audit to a specific user, only they can work on this audit. This is triggered in two ways, one if a user clicks a fetch audit button on the UI, we create an event that checks the the audit table for unassigned audits, it also checks the version field and increments the version field after the database updates the audit with the auditor's id. This prevents a race condition situation since if two people fetched the same audit at the same time, only one would get the audit and the other person would get an error once the system realized the version was already updated. The other way its triggered is by an admin or someone with a higher role that can manually change this audit's assigned_to value to someone else
GenerateReport() -> This will load the weekly(or whatever timeframe we want) report based on the audits in the table, we will feed audit data by date to amazon athena to generate a report then store the url that amazon athena returns for user's to access the report.
SendReport() -> Sends audit report to email inboxes as per send schedule
Error handling:
Pre Ingestion checks:
Instead of just giving our system a large chunk of data for it to ingest, we can determine what is a a reasonable amount of data our system can handle during ingestion without errors. If the amount of data we are uploading is larger than our ingestion max, we can separate our upload into chunks, uploading only in chunks of data that our system can handle, the next chunk only being uploaded after the first chunk has successfully been uploaded.
This also includes checking if the data we upload will cause storage issues. If the amount of rows we are about to upload exceeds the amount of space, reject the upload and send a notification. Send notifications if we are critically close to running out of space
Have circuit breakers set up to check the health of the database, if database is unhealthy open circuit breakers until problem is resolved
AuditTransaction(RawDataTransaction) -> This will be the main function of our batch job that will be review a raw data transaction, compare it with our predefined rules and raise any flags if needed. We will create rows in our Audit table for each rule violated. After audit generation completion, emit soft delete updates on the raw data transaction table to initiate soft delete on audited rows.
GenerateReport() -> This function does not require any parameters since it will be an automated weekly job that sends weekly audit data to amazon athena. Athena will also have automated jobs that process this data into meaningful data showing type of rule violations and how many are occurring the the weekly timeframe. This then is processed by tableau or power bi or some data visualization software that will provide a readable report. We store the link of this report in our database along with report title and date , then create an event that triggers our notification service to notify certain inboxes that the weekly report is ready