System requirements

Functional:

1. Data under audit are database transactions
2. Support both ad-hoc audit and scheduled audit
3. Each audit will look at up to 100k transactions in total

Non-Functional:

1. Generally there is no hard deadline of when a transaction needs to be completed auditing.
2. A same database transaction may be audited multiple times, depending on need.
3. Ok for audit to pause or fail in the middle. Resume or retry will pick up from where it paused or failed last time.

Capacity estimation

Assume each transaction is 1 KB, and we audit 100k transactions in one audit. Total size = 1 KB * 100k = 100 MB / audit.

Assume we run scheduled audit once a day, and may run ad-hoc audits.

Assume on average, audit of one transaction performs 20 different checks, and they totally take 1 millisec to complete. Total time = 1 millisec * 100k = 100 seconds to complete one audit.

It's easily to fit the job in one machine.

Assume we keep one year of auditing history, and the auditing result log size is similar compared to the original transactions. 100 MB * 365 = 36.5 GB. Assume ad-hoc audits are less than scheduled audit, we will need, say 50 GB total. The result can also be easily fit into one disk storage.

API design

We don't expose APIs externally. Internally, an audit requires the following inputs:

1. schedule when to start an audit
2. input source where transactions are located
3. number of transactions to audit
4. auditing configurations that indicate what kind of checks to perform, e.g., consistency of amount in a financial transaction, ordering between dependent events, user ACLs, etc.

Database design

We store audit results into database. Result data size is small, and checks performed are well predefined. SQL database may be used.

Database: audits

Table: rules. This to store what rules of checks we perform in audits.

Schema:

• uid: unique id of the rule
• name: human readable name of the rule
• target: what kind of transaction to check against
• created_at: creation timestamp of the rule
• last_updated_at: last update timestamp of the rule

Table: rules_history. This to store the history of all changes to the rules table. Schema is similar to the rules table.

Table: results.

Schema:

• uid: unique id of this result
• trans_id: id of the transaction that this result is associated with
• rule_id: id of the rule that was performed to produce this result
• result: audit result, e.g., pass, fail, flagged, etc.
• created_at: when the result is created

Table: schedules.

Schema:

• uid: unique id of the schedule
• rule_ids: rule ids to perform check
• schedule: airflow-style schedule string
• created_at
• last_updated_at

Table: schedules_history. Schema similar to schedules table.

High-level design

See diagram

Request flows

Scheduled audit:

1. Audit is triggered at schedule time
2. Reads the schedule configurations, i.e., what rules to use in audit
3. Determine which transactions to audit
4. Run audit
5. Write audit results to database
6. Alert and/or take actions if any transactions failed the audit

Ad-hoc audit:

similar to scheduled audit, but user provides audit rules and which transactions need to be audited.

Detailed component design

1. for scheduling, we can use airflow, which also manages dependency if we have multiple sub-audits that depend on each other
2. for actually running the audit, the amount of data to be processed is small such that one single thread is capable of completing the task in short period of time.
3. if an audit is paused or terminated unexpectedly in the middle, we may either rerun the full batch or resume from where it stopped. As audit itself do not change the original transactions, it's generally safe to just rerun the full batch.

Trade offs/Tech choices

1. if the amount of transactions is huge, such that one audit may run too long by using a single thread, we may consider run in multiple threads. We need to figure out which auditing rules do not have dependency on each other, and those may be run in parallel. As we figured out dependencies, we may store such info in the rules database table, so we don't have to manually do this every time. Also, as different audit steps may be run in parallel or one after another, we may use Airflow to create a DAG for such dependencies.
2. We may need a backup on the audit result database. In case the main database fails, we could use the backup to run audits, and also look up old results from the backup.
3. If the audit schedule is daily, generally we don't need a server backup for the machine which performs audit, because a server can typically be brought back in much less than the time period of a day.

Failure scenarios/bottlenecks

As mentioned earlier, the audit machine may fail, the result database may fail. See previous for mitigations.

In addition, the database or storage which contains the transactions to be audited may also fail. We need backups for such as well.

Future improvements

If the audit will be performed on a much larger scale, we should think of distributing different batches to different machines, and potentially store results into different database shards. This will result in a distributed system.