Customer Guide to Auditing Databases

Owned by Bryan Alborough
May 02, 2024
10 min read

This guide is designed to empower restaurant owners with the knowledge and tools necessary to conduct a preliminary audit of their Aura POS database. By following these steps, you can identify potential areas of concern and take proactive measures to protect your business against fraud or financial losses. Please note that this guide is intended to serve as a preliminary assessment tool only. Should you have any concerns or questions about your findings, please consult with Aura Support.

Contents

Reasons for an audit

There are several reasons you may choose to audit your database. A typical reason is to investigate a decline in sales as compared to previous years - which may indicate underlying issues affecting revenue. Additionally, discrepancies between total stock costs and turnover trends could signal irregularities (or even just inefficiencies) in stock management practices. When turnover figures deviate significantly from expected norms, a database audit can help uncover the root causes and identify areas for improvement.

Furthermore, the audit process is useful in uncovering specific employee activities that may indicate fraudulent behaviour, which will allow you to introduce safeguards to prevent it happening again in future.

Lastly, having a comprehensive audit report on hand can provide valuable documentation for mediation purposes in the event of disputes or legal proceedings.

1. Review Activity Reports

Activity reports are one of the best ways to keep track of user behaviour on the Aura system and are our primary resource for database audits. Every action that is performed in Aura that uses an access permission or alters data is recorded to Aura’s Activity Log. This activity can be reviewed using the Activity Reports in Backoffice under Reports > Activity.

image-20240429-081143.png

There are three types of activity reports:

  • Activity Report by Activity Code: This report will group activities by the activity itself. This is very useful to identify which user has been making a particular action i.e To Modify Cashup Entries.

AR02-Activity by Code.png

  • Activity Report by Activity Filter: This report will filter activity history by the keyword you type in. If you type in “Stock”, for example, it will give you results from multiple activities such as “To Perform a Stock Take” and “To Discard Stock”. This is useful if you’re trying to identify activity in a more general area.

  • Activity Report by Employee: This will group activities by employee. Activities will be listed in chronological order. This report is good for investigating the actions of a particular user and identifying patterns of behaviour by that user over time.

Analysing Activities

It’s important to be able to interpret the information that the reports display correctly. Due to the volume of information that is displayed on the reports, some of the data may be abbreviated. Look at the following breakdown of some activity reports to see the way in which the information is presented:

Key Activities

In the audit process, you will be analysing the reports based on the type of activity you are investigating. In a general audit, we look for irregularities in the following activities. An ‘irregularity’ may be that an employee may be using them excessively, or that they have access to these actions when they should not.

Invoicing/Sales

Activity

Description

Activity

Description

To abandon an invoice

This allows a user to cancel an invoice in progress. A cashier may cancel a sale instead of processing it in order to pocket the cash.*

To edit or delete an invoice line

This indicates that the invoice was modified and that one or more items were modified or deleted.*

To Delete unsaved Invoice line

This is specifically to note deletions of items from an incomplete invoice. It’s common for cashier use as customers change their minds, but excessive use could indicate deliberately removing items to change an invoice total before completing the order.*

To open the till

This indicates that the till is being opened via the Invoicing main screen instead of automatically at the end of a cash sale. Excessive use of this action after to abandon an invoice may indicate an action where the cashier is making change for a customer but not recording the sale.*

to reprint an invoice

Excessive use of reprinting may indicate that a cashier is using a previous invoice to supply the customer with a slip for an old sale and not processing a new one.

To over ring an Invoice

Excessive over-rings could indicate a problem where invoices are processed and then cancelled.

To Modify credit card entries

This activity refers to changing the credit card entries in the shift cashup. Credit card sales are recorded automatically in the cashup, so constant editing of the entries could indicate manipulation.

To Authorize discounts

Discounts should be used sparingly - excessive use of discounts could be an indication that sales totals are being manipulated.

To modify a clock period

This will indicate if a clock-in/out entry has been altered.

* Note that these actions would require either the slip printer and kitchen printer/screen to be off or at fault. If there have been times where the store has had faults with printers/screens, it may be worth investigating these activities.

Activity Patterns - Invoicing

When investigating the reports, the following questions should be at the forefront:

  • Are there specific days/ time periods where you suspect wrongdoing?

  • Do you suspect a specific staff member or multiple staff members?

  • Do you notice patterns of re-printing slips and then opening the cash drawer in your activity reports?

  • Do you notice patterns of items being rung up and then later the line item is deleted and the invoice is canceled?

  • Do you notice a higher-than-normal prevalence of overrings?

  • Do you notice a higher-than-normal prevalence of Modifying credit card entries?

  • Do you notice a higher-than-normal prevalence of cash drawer opening as a pattern with cash/credit card entries changes?

These questions should guide what activity reports you’re running, which employees you’re running them for, and what period you are running them. The activity reports contain a lot of information so it’s better to narrow them down in order to get more meaningful results.

Backoffice/Stock

Activity

 

Activity

 

To edit an existing Stock take

This is a commonly used activity, but should be investigated if there are multiple cases of periods of greater than 24 hours being edited. Editing an existing stocktake without proper authorization could lead to unauthorized changes in the inventory records, leading to discrepancies in stock levels and causing problems with inventory management. *

To Edit a Balanced GRV older than 24 hours

Altering GRVs can lead to financial fraud, where employees or managers may manipulate records to misrepresent the actual costs or quantities of goods received. Editing GRVs can create discrepancies in inventory records, leading to inaccurate stock levels.

To maintain access control

This indicates if the user is accessing the Permissions window in Backoffice. They could be granting permissions to themselves or others.

Activity Patterns - Backoffice

  • Do you notice any patterns of GRVs/Stock Takes being edited and then Stock Variance/GP reports being opened in your activity reports?

  • Are there regular edits or consistent irregularities of specific stock items?

  • Do employees seem to be gaining access to otherwise restricted activities?

2. Review System Health Reports

The System Health reports have been designed to assist store owners and managers to detect potential problems in the store by analysing information and displaying results that are out of the norm, or that may significantly affect particular areas of the system. They are a useful starting point to discovering problems that may require deeper auditing.

Report Types

  • Cashup Health Report: This report identifies incorrect or incomplete cashups. If there are problems here, it’s recommended to rectify them and then checking on your Cashup Settings to prevent these problems from occurring again.

  • Employee Health Report: This report highlights potential security problems with employees, such as high commissions, and what categories have restricted access to sensitive information.

  • Stock Health Report: This reports looks for a number of important stock control information, such as Days missing a stock take and Stock Takes and GRV’s edited more than 24 hours after their posting.

3. Review System Settings

Aura has a number of settings relating to access and security on the system. These can be accessed in Invoicing under Settings, or in Backoffice under Preferences > System Settings. As with Access Permissions, most of these settings will be dependant on the measure of access that the store wants to allow the employees. However it is still important to know that these settings exist and what effect they have. 

Blind Cashup

Specific Computer > Invoicing Settings: Prevents the cashup screen from showing the Net Sales, Gross Sales, Credit Cards, GRVs paid from till, or whether the till is over/under. Essentially, the cashier doing the cashup on their own till will enter their cash on hand, their credit cards, and any petty cash payments. This can be set per till.

Cashups on tills with Blind Cashup enabled will look like this:

Allow Reprint

Specific Computer > Printer Settings: With this option disabled, Aura Invoicing will prevent anyone from re-printing any order's customer and production slip from the current day despite their access level.

Force password change

All Computers > Program Settings > General: Forces all employees passwords to expire after the set number of days, asking them at that point to enter their current password, and select a new password in order to continue. This is useful as it reduces the likelihood of stolen passwords.

Cashup Settings

All Computers > Program Settings > Cashup: This settings page controls how cashups are handled. The settings under the heading Tills cannot be LOCKED if set conditions that must be met before cashups can be completed. It’s highly recommended to have these settings always enabled to make sure that your cashups are done properly.

  • Invoices are outstanding - Check for any orders that have not been paid.

  • Any till shifts are open - Checks to see if any till shifts are not ended and posted.

  • Drivers are not cashed up - Checks for any drivers that have not yet been cashed up (posted into an active till shift).

  • One Shop cashup per day - This ensures that tills cannot be started again once the shop is posted.

  • Cannot open tills if previous days Shop cashup is unposted - This ensures that the previous day’s cashup is properly addressed before new shifts can be started.

4. Check Access Levels

To review store employee access permissions, open Backoffice, and click Preferences > Access Permissions.

To assign an access permission, select the Employee Category you want to edit in the left window. On the right, the permissions are divided into Allowed and Prohibited.

  1. Select the category you want to edit the permissions for

  2. If you want to deny a permission, select it from the Allowed (top) window and click the Deny AP05 - Deny.png button to move it down to Prohibited (DO NOT use Deny All).

  3. If you want to allow a permission, select it from the Prohibited (bottom) window and click the Grant button AP05 - Grant.png to move it up to Allowed (DO NOT use Grant All).

  4. The list of permissions can be filtered using the Search bar to find permissions regarding the same topic.

  1. These groups can be added individually as above or the entire group can be allowed or denied using  Deny all or Grant all.

  2. Any errors that are made can be undone using the Rollback button at the bottom of the screen to revert the changes to the point they were at when the screen was first opened.

A list of Minimum Recommended Access permissions can be found on Aura Support’s Knowledgebase

Check Restricted Employee Categories

Aura has the ability to restrict the access to edit or add some Employee categories from others. In Backoffice, click Preferences > Restricted Employee Categories.

  1. Select the category you want to apply restrictions to (i.e Manager)

  2. By default, all other categories will be listed under Unrestricted. Click [<] to move the categories to Restricted (i.e For our Manager we have made the categories Franchisee and Manager restricted)

  3. Click Close to save the changes.

 

Now if anyone in that category tries to modify the employee settings of an employee of the restricted category, they will see the following alert:

If someone from a category in which categories are restricted tries to create a new employee, they will only have unrestricted categories available to them: