SQL Reference

SlotsOne — PostgreSQL 16 Database Layer | v1.0 | Last updated: 2026-02-28

Complete reference of every SQL statement executed by the SlotsOne backend. All queries use parameterised placeholders ($1, $2, …) via the pg driver to prevent SQL injection. No raw string concatenation is ever used.

Database Schema (ERD)

Six tables across two migrations (001_init.sql, 002_game_history.sql), executed on every server startup via initDb(). All DDL uses IF NOT EXISTS for idempotency.

U users

id	UUID	PK	gen_random_uuid()
email	TEXT	UNIQUE NOT NULL
password_hash	TEXT	NOT NULL
created_at	TIMESTAMPTZ	NOT NULL	NOW()

ON DELETE CASCADE

T refresh_tokens

token	TEXT	PK
user_id	UUID	FK NOT NULL	→ users(id)
expires_at	TIMESTAMPTZ	NOT NULL
created_at	TIMESTAMPTZ	NOT NULL	NOW()

idx_refresh_tokens_user_id B-tree on user_id
idx_refresh_tokens_expires_at B-tree on expires_at

W wallets

id	UUID	PK	gen_random_uuid()
user_id	UUID	FK UNIQUE NOT NULL	→ users(id)
balance_cents	BIGINT	NOT NULL	100000
version	INTEGER	NOT NULL	1
created_at	TIMESTAMPTZ	NOT NULL	NOW()
updated_at	TIMESTAMPTZ	NOT NULL	NOW()

ON DELETE CASCADE

S seed_pairs

id	UUID	PK	gen_random_uuid()
user_id	UUID	FK NOT NULL	→ users(id)
server_seed	TEXT	NOT NULL
server_seed_hash	TEXT	NOT NULL
client_seed	TEXT	NOT NULL	'default'
nonce	INTEGER	NOT NULL	0
active	BOOLEAN	NOT NULL	TRUE
revealed_at	TIMESTAMPTZ
created_at	TIMESTAMPTZ	NOT NULL	NOW()

idx_seed_pairs_user_active B-tree on (user_id, active)

R game_rounds

id	UUID	PK	gen_random_uuid()
user_id	UUID	FK NOT NULL	→ users(id)
session_id	TEXT	NOT NULL
game_id	TEXT	NOT NULL
seed_pair_id	UUID	FK	→ seed_pairs(id)
nonce	INTEGER
bet_cents	BIGINT	NOT NULL
win_cents	BIGINT	NOT NULL	0
currency	TEXT	NOT NULL	'USD'
lines	INTEGER	NOT NULL
balance_before_cents	BIGINT	NOT NULL
balance_after_cents	BIGINT	NOT NULL
reel_matrix	JSONB	NOT NULL
win_breakdown	JSONB	NOT NULL	'[]'::jsonb
bonus_triggered	JSONB
outcome_hash	TEXT
created_at	TIMESTAMPTZ	NOT NULL	NOW()

idx_game_rounds_user_created B-tree on (user_id, created_at DESC)

ON DELETE CASCADE

X transactions

id	UUID	PK	gen_random_uuid()
round_id	UUID	FK	→ game_rounds(id) nullable
user_id	UUID	FK NOT NULL	→ users(id)
type	TEXT	NOT NULL	CHECK ('bet','win','topup')
amount_cents	BIGINT	NOT NULL
balance_after_cents	BIGINT	NOT NULL
created_at	TIMESTAMPTZ	NOT NULL	NOW()

idx_transactions_user_created B-tree on (user_id, created_at DESC)

Connection Pool

Driver: pg.Pool (node-postgres) — singleton, lazily initialised
Max Connections: 10 concurrent connections
Idle Timeout: 30 000 ms (30 s)
Connect Timeout: 5 000 ms (5 s)

Query Index

Click any row to jump to the full query detail.

Security & Best Practices

Parameterised Queries: Every user-supplied value is passed as a $N placeholder argument — never concatenated into SQL strings. This eliminates SQL injection vectors.
Atomic Token Rotation: DELETE … RETURNING consumes a refresh token and retrieves the owner in a single atomic statement, preventing race conditions and replay attacks.
Cascade Deletion: The refresh_tokens.user_id FK uses ON DELETE CASCADE — deleting a user automatically purges all their tokens.
Unique Constraints: The users.email column has a UNIQUE constraint. The app catches PostgreSQL error code 23505 and returns a 409 Conflict.
Server-Side TTL: Token expiration is computed by the database: NOW() + interval '7 days', avoiding clock-skew between Node.js and PostgreSQL.
Idempotent Migrations: All DDL uses CREATE TABLE IF NOT EXISTS and CREATE INDEX IF NOT EXISTS — safe to run repeatedly on every server start.
Index Coverage: B-tree indexes on refresh_tokens(user_id) and refresh_tokens(expires_at) ensure O(log n) lookups for validation and cleanup.
Graceful Shutdown: closePool() drains active connections and resets the singleton reference, preventing connection leaks on restart.
Optimistic Locking: Wallet debits use a version column check in the WHERE clause. If a concurrent request modifies the wallet between read and update, the version mismatch causes 0 rows affected — preventing lost updates without SELECT FOR UPDATE locks.
BIGINT Cents: All monetary values are stored as BIGINT in cents (1 USD = 100 cents). This eliminates IEEE 754 floating-point rounding errors that occur with DECIMAL or FLOAT in JavaScript.
Transactional Round Creation: Game round + transaction inserts are wrapped in BEGIN/COMMIT using pool.connect(). On failure, ROLLBACK ensures no orphaned records persist.
HMAC-SHA256 Seeds: Provably fair seeds use HMAC-SHA256(server_seed, client_seed:nonce) for spin derivation and SHA-256 for server seed hashing. The server seed is never exposed while the pair is active.
Composite Indexes: Composite B-tree indexes on (user_id, created_at DESC) and (user_id, active) eliminate sort steps and heap filters for the most frequent query patterns.

Source Files

Migration (Auth): backend/src/migrations/001_init.sql
Migration (Game): backend/src/migrations/002_game_history.sql
Connection Pool: backend/src/db.ts
User Store: backend/src/userStore.ts
Token Store: backend/src/auth/refreshTokenStore.ts
Wallet Store: backend/src/walletStore.ts
Seed Store: backend/src/seedStore.ts
Round Store: backend/src/roundStore.ts
Provably Fair: backend/src/provablyFair.ts