Audit log

The hub database has a hub.audit_log table that stores a record of every player balance and house bankroll update.

What is the audit log?

The audit log lets us track the history of the player balance and house bankroll over time.

With it, we can easily answer questions like:

• How did User123 get their current balance?
• Why did the house bankroll double in size overnight?

We can sometimes piece this info together by looking at various tables, like the hub.outcome_bet table, but the audit log makes it trivial, and it gives us a place to log info that might not be reflected in the other tables.

The audit log table

The audit log looks something like this:

column name	type	nullable	description
id	uuid	false	The unique identifier for the audit log entry.
action	text	false	The action that was performed.
metadata	jsonb	true	Arbitrary metadata about the action.
ref_type	text	true	The type of the reference that was updated.
ref_id	text	true	The unique identifier for the reference that was updated.
balance_id	uuid	true	The unique identifier for the balance row that was updated.
balance_old	float	true	The old balance value.
balance_new	float	true	The new balance value.
balance_delta	float	true	The difference between the old and new balance values.
bankroll_id	uuid	true	The unique identifier for the bankroll row that was updated.
bankroll_old	float	true	The old bankroll value.
bankroll_new	float	true	The new bankroll value.
bankroll_delta	float	true	The difference between the old and new bankroll values.

Hub’s internal audit logs have actions like this:

• hub:deposit
• hub:outcome_bet
• hub:take_request:pending
• hub:take_request:refund

Feel free to use your own actions when adding entries to the audit log. We recommend using the app: prefix like app:mines_game:start, app:mines_game:win, app:mines_game:lose, etc.

The ref_type refers to the table name, like hub.outcome_bet or app.mines_game. And when ref_type is given, then ref_id is the primary key of the relevant row.

The metadata column is a JSONB column that can be used to store arbitrary metadata about the action. It’s optional, and you can use it to store any info you want that you think will help you understand the action later.

How do I add entries to the audit log?

Hub exposes an insertAuditLog helper that gives you some TypeScript support for ensuring you’re inserting a valid record.

Here’s an example of what the full pattern looks like:

import {
  withPgTransaction,
  dbLockPlayerBalanceAndHouseBankroll,
} from "@moneypot/hub/db";
import { insertAuditLog } from "@moneypot/hub/audit-log";

withPgTransaction(pool, async (pgClient) => {
  // Note: Always remember to lock the balance and bankroll before updating them.
  const { dbPlayerBalance, dbHouseBankroll, found } =
    await dbLockPlayerBalanceAndHouseBankroll(pgClient, {
      userId: session.user_id,
      casinoId: session.casino_id,
      experienceId: session.experience_id,
      currencyKey: dbCurrency.key,
    });

  if (!found) {
    throw new GraphQLError("No balance entry found for player or house");
  }

  // ... Pretend we update the balance and bankroll ...

  const balanceDelta = 123;
  const bankrollDelta = -123;

  await insertAuditLog(pgClient, "both", {
    action: "app:mines_game:start",

    // Player balance info (required if type is "both" or "player-balance")
    balanceId: dbPlayerBalance.id,
    balanceOld: dbPlayerBalance.amount,
    balanceNew: dbPlayerBalance.amount + balanceDelta,
    balanceDelta,

    // House bankroll info (required if type is "both" or "house-bankroll")
    bankrollId: dbHouseBankroll.id,
    bankrollOld: dbHouseBankroll.amount,
    bankrollNew: dbHouseBankroll.amount + bankrollDelta,
    bankrollDelta,

    refType: "app.mines_game",
    refId: "123",
  });
});

Remember to always do it inside the transaction that modifies the balance and/or bankroll.