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

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 a useful function the fetches the player balance and house bankroll in a single query while locking both rows.

import {
  dbLockPlayerBalanceAndHouseBankroll,
  withPgPoolTransaction,
  superuserPool,
} from "@moneypot/hub/db";

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

    if (!found) {
      throw new Error("Player balance or 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.
  });
}