Extending the database

Extending the Database

Any non-trivial game needs its own database tables. This guide explains how to add custom tables to your hub implementation.

Database Schema Organization

When you use the @moneypot/hub server, it creates several Postgres schemas with different visibility levels:

For your own application, follow this convention by creating:

• app schema for tables you want to expose via the GraphQL API
• app_hidden for internal tables that shouldn't be directly accessible
• app_secret for security-sensitive data that needs strict protection

The app_postgraphile database user will only have access to the app schema, ensuring proper separation of concerns.

Adding Custom Tables

Grant select privs to app_postgraphile user

If your table is in the app schema that's exposed to the startAndListen({ schemas: ['app' ], ...}), then you will want to let the app_postgraphile user read it.

grant select on app.bet to app_postgraphile;

Use the Automigration System

The recommended approach for managing database schema changes is to use the built-in automigration system:

1. Create migration files in your project
2. Define your schema changes in SQL
3. Let the system track and apply migrations automatically

For detailed instructions, see the Database Migrations guide.

Generating a GraphQL API

Once you've created your custom tables, you'll need to expose them through GraphQL:

Learn more about this process in the Extending GraphQL guide.

Example migration

Here's an example of how you might create a dice bet table with all this in mind:

drop schema if exists app cascade;

create schema app;
grant usage on schema app to app_postgraphile;

CREATE TABLE app.dice_bet (
  id          uuid primary key,
  wager       float8 not null,
  net         float8 not null, -- negative if lost, wager*(target-1) if won
  target      float8 not null, -- 2.0 means user was going for 2x payout
  actual      float8 not null, -- e.g. actual roll was 1.8x (loss) or 3x (win)
  currency_key text not null,  -- 'HOUSE', 'BTC', etc.

  -- let us easily look up the lastest bets for users, casinos, experiences
  user_id uuid not null references hub.user(id),
  casino_id uuid not null references hub.casino(id),
  experience_id uuid not null references hub.experience(id),

  -- currencies are specific to each casino
  foreign key (currency_key, casino_id) references hub.currency(key, casino_id)
);

-- GRANT
grant select on table app.dice_bet to app_postgraphile;

-- RLS

alter table app.dice_bet enable row level security;

create policy select_dice_bet on app.dice_bet for select using (
  hub_hidden.is_operator() or
  user_id = hub_hidden.current_user_id()
);

Best practices

Use singular table names

For example, create table app.bet instead of create table app.bets.

They tend to make more sense in functions and graphql logic, and they are automatically inflected into plural forms in GraphQL.

Enable Row Level Security (RLS) on every table in the app schema

This way, all access is prohibited by default until you write RLS policies that control otherwise.

You also usually want to allow access for these cases:

1. hub_hidden.is_operator() lets your api key access everything. Ex: this lets your admin dashboard see all records of each table.
2. user_id = hub_hidden.current_user_id() lets the user access their own records.

alter table app.bet enable row level security;

create policy select_bet on app.bet for select using (
  hub_hidden.is_operator() or
  user_id = hub_hidden.current_user_id()
);

Use automigrations

This makes your schema changes incremental and easy to reverse manually.

Use UUID v7 for ID primary keys

UUID v7 are chronological, so they are sortable. But they also have a random component so that your rows can't be enumerated.

SQL hub_hidden.uuid_generate_v7() will generate one:

create table app.example(
  id uuid primary key default hub_hidden.uuid_generate_v7()
);

You don't need a created_at timestamptz column because you can extract the timestamptz from the UUID v7 with: hub_hidden.extract_timestamp_from_uuid_v7(uuid).

Add indexes on foreign keys

This is especially important for user_id, casino_id, and experience_id columns.

Of course, it improves query performance when you're filtering by foreign keys.

But it also generates the GraphQL that lets you query through foreign key relations. For example:

query {
  userById(42) {
    diceBetsByUserId {
      nodes {
        id
        wager
        net
      }
    }
  }
}

Use Postgres transactions

TODO

Avoid race conditions with row-locking and/or SERIALIZABLE transactions

(While these docs don't plan on doing a deep dive into writing concurrency-safe SQL code, this is such an important topic that we will at least scratch the surface here.)

Any time you want to update the database, especially based on information that's already in the database, you need to be wary of race conditions if your logic were run in parallel by multiple queries at the same time.

For example, here's a common makeBet implementation:

1. Get the user's balance
2. Ensure the user can afford the bet
3. Get the bankroll's balance
4. Ensure the bankroll can afford the bet
5. Roll the dice and move the money between the user and the bankroll

If you write this naively, then it can fail in a few different ways:

• The user's balance can be changed by another query right after the user's balance is checked
• The bankroll's balance can be changed by another query right after the bankroll's balance is checked

Imagine if 100 of these queries were run at the same time. Every query would see the same user balance. The user could end up making bets that they couldn't afford if the queries were instead run sequentially, one at a time.

One way to handle this problem is to use Postgres' SERIALIZABLE isolation level for transactions which locks the table for the duration of the transaction and lets you write SQL as if it were run sequentially.

While this has significant performance implications, one way you can use it is to start with SERIALIZED transactions first while you're getting things working and then transition to better row-locking strategies.

Another way to handle this problem is to use row locking (SELECT ... FOR UPDATE) to lock the row for the duration of the transaction.

The goal is to lock rows

1. Get the user's balance (FOR UPDATE)
2. Ensure the user can afford the bet
3. Get the bankroll's balance (FOR UPDATE)
4. Ensure the bankroll can afford the bet
5. Roll the dice and move the money between the user and the bankroll

Use consistent Postgres row-locking order

When using FOR UPDATE, you need to consider the order in which you lock the rows to avoid deadlocks.

If some queries lock row A and then row B, but other queries lock row B and then row A, they will "deadlock" because they are waiting for each other to release the lock.

Here are some tips:

1. Lock less-contended rows (like user balance) before more-contended rows (like bankroll).

   Less-contended rows are rows that are accessed by fewer queries, such as those specific to a single user. More-contended rows are accessed by many queries from different users.

2. Lock rows in the same order for each transaction.

   A classic deadlock scenario is when you transfer funds between two users, e.g. transferFunds(sender, receiver, amount), and two users transfer funds to each other at the same time.

   If both queries lock senders balance first, then they will deadlock because they are waiting for each other to release the lock.

   Instead, you could write the transaction so that it always locks the user rows in the same order based on user ID:

   async function transferFunds(
     senderId: string,
     receiverId: string,
     amount: number
   ) {
     // Sort the IDs senderId to maintain a consistent locking order
     const [firstId, secondId] =
       senderId < receiverId ? [senderId, receiverId] : [receiverId, senderId];
   
     return withPgTransaction(pool, async (pgClient) => {
       // Lock the rows
       await client.query(
         "SELECT balance FROM users WHERE id = $1 FOR UPDATE",
         [firstId]
       );
       await client.query(
         "SELECT balance FROM users WHERE id = $1 FOR UPDATE",
         [secondId]
       );
   
       // Here, we know that any other transaction that wants to lock these
       // rows will block until our transaction is complete, so we are safe
       // to query the balances.
     });
   }