Best practices

1. Do not modify internal hub tables
2. Put your custom tables in the app schema
3. Do not delete records
4. Lock and update hub.balance and hub.bankroll on every bet
5. Lock hub.bankroll as short-lived as possible
6. Lock rows in the same order
7. Use the audit log when player balance or house bankroll changes

Do not modify internal hub tables

Hub’s internal tables are in these Postgres namespaces:

1. hub: Tables available to postgraphile / graphql API
2. hub_hidden
3. hub_secret

If you modify them (i.e. add columns, modify RLS policies on them), then you might make it hard to receive hub server updates when we publish them.

If you find yourself wanting to add a column to an internal hub table, consider creating your own table in the app schema and giving the table an id of id uuid primary key references hub.user(id).

That primary key will guarantee that only one custom record will exist for each hub-internal hub.user record.

Advanced: You can even write a Postgraphile plugin that exposes that field in the GraphQL API (TODO, write the docs for this).

Put your custom tables in the app schema

TODO

Do not delete records

Deleting records from the database (delete from table X where ...) has various downsides:

1. You lose the paper trail of the record ever existing
2. Other rows might reference the record
3. It’s a destructive, error-prone operation

Instead, use soft delete.

Soft delete is when you use a column like deleted_at timestamptz null to mark whether something is deleted or not.

Hub automatically implements soft delete at the GraphQL API level for any table with a nullable deleted_at field.

By default, soft-deleted records aren’t returned from the GraphQL API, but you can use includeDeleted: YES | NO | EXCLUSIVELY to control this.

TODO: Show what the GraphQL looks like.

Lock and update hub.balance and hub.bankroll on every bet

The hub server exposes functions that fetch and lock the player balance and house bankroll.

These functions use Postgres’ SELECT ... FOR UPDATE to lock the row for the duration of the transaction.

import {
  dbLockPlayerBalance,
  dbLockHouseBankroll,
  withPgPoolTransaction,
} from "@moneypot/hub/db";

async function example() {
  await withPgPoolTransaction(superuserPool, async (pgClient) => {
    const dbLockedPlayerBalance = await dbLockPlayerBalance(pgClient, {
      userId: "...",
      experienceId: "...",
      casinoId: "...",
      currencyKey: "HOUSE",
    });

    if (!dbLockedPlayerBalance) {
      throw new Error("Player balance not found");
    }

    const dbLockedHouseBankroll = await dbLockHouseBankroll(pgClient, {
      userId: "...",
      experienceId: "...",
      casinoId: "...",
      currencyKey: "HOUSE",
    });

    if (!dbLockedHouseBankroll) {
      throw new Error("House bankroll not found");
    }

    // Both rows were found and locked for the duration of the transaction
    // Make sure you short-circuit ASAP to unlock the rows for other bets.
  });
}

More database best practices: Extending database

Lock hub.bankroll as short-lived as possible

Locking hub.bankroll creates a global bottleneck that every transaction has to wait for.

You should do everything you can to hold the lock as short-lived as possible inside of a transaction so that it is released as soon as possible.

So, always make your less-contenious locks and queries first and only lock hub.bankroll when you absolutely need to towards the end of your transaction.

This looks something like this:

async function makeBet(options) {
  await withPgPoolTransaction(superuserPool, async (pgClient) => {
    // 1. Validate input
    // 2. Lock and validate user balance
    // 3. ...<Do as much work here as possible>...
    // 4. Update user balance
    // 4. Lock, validate, and update house bankroll
  });
}

More database best practices: Extending database

Lock rows in the same order

To avoid Postgres deadlocks, always lock rows in the same order for each transaction.

Some guidelines to be compatible with hub’s internal queries:

• Lock player balance (hub.balance) before the hash chain (hub.hash_chain)
• Lock house bankroll (hub.bankroll) last
• If locking two rows of the same table, lock them in ascending ID order

More database best practices: Extending database

Use the audit log when player balance or house bankroll changes

Read more: Audit log