Database Management - Hyperscape

Overview

Hyperscape uses Drizzle ORM for database access:

Development: SQLite (zero config)
Production: PostgreSQL with pgvector extension

PostgreSQL 16+ with pgvector: The production database requires PostgreSQL 16 or higher with the pgvector extension. Use the pgvector/pgvector:pg16 Docker image for local development (updated in commit a9e9003, Feb 2026).

Schema Location

Database schema is defined in:

packages/server/src/database/schema.ts

Tables

Table	Purpose
`users`	User accounts (Privy auth)
`characters`	Character data (multi-character support)
`players`	Active player sessions
`inventory`	Player inventory items
`bank`	Banked items with tab support
`bank_tabs`	Bank tab configuration
`equipment`	Equipped items
`npc_kills`	Kill tracking
`player_deaths`	Death history
`world_chunks`	World state persistence
`sessions`	Active game sessions

Characters Table Schema

The characters table stores all character data including skills:

CREATE TABLE characters (
  id TEXT PRIMARY KEY,
  userId TEXT NOT NULL,
  name TEXT NOT NULL,
  -- Position
  x REAL DEFAULT 0,
  y REAL DEFAULT 0,
  z REAL DEFAULT 0,
  -- Skills (levels)
  attackLevel INTEGER DEFAULT 1,
  strengthLevel INTEGER DEFAULT 1,
  defenseLevel INTEGER DEFAULT 1,
  constitutionLevel INTEGER DEFAULT 10,
  rangedLevel INTEGER DEFAULT 1,
  magicLevel INTEGER DEFAULT 1,
  prayerLevel INTEGER DEFAULT 1,
  woodcuttingLevel INTEGER DEFAULT 1,
  miningLevel INTEGER DEFAULT 1,
  fishingLevel INTEGER DEFAULT 1,
  firemakingLevel INTEGER DEFAULT 1,
  cookingLevel INTEGER DEFAULT 1,
  smithingLevel INTEGER DEFAULT 1,
  -- Skills (XP)
  attackXp INTEGER DEFAULT 0,
  strengthXp INTEGER DEFAULT 0,
  defenseXp INTEGER DEFAULT 0,
  constitutionXp INTEGER DEFAULT 1154,
  rangedXp INTEGER DEFAULT 0,
  magicXp INTEGER DEFAULT 0,
  prayerXp INTEGER DEFAULT 0,
  woodcuttingXp INTEGER DEFAULT 0,
  miningXp INTEGER DEFAULT 0,
  fishingXp INTEGER DEFAULT 0,
  firemakingXp INTEGER DEFAULT 0,
  cookingXp INTEGER DEFAULT 0,
  smithingXp INTEGER DEFAULT 0,
  -- Prayer system
  prayerPoints INTEGER DEFAULT 1,
  prayerMaxPoints INTEGER DEFAULT 1,
  activePrayers TEXT DEFAULT '[]',  -- JSON array: '["thick_skin", "burst_of_strength"]'
  -- Combat preferences
  attackStyle TEXT DEFAULT 'accurate',
  autoRetaliate INTEGER DEFAULT 1,
  selectedSpell TEXT,              -- Autocast spell ID (null = no autocast)
  -- Status
  health INTEGER DEFAULT 100,
  maxHealth INTEGER DEFAULT 100,
  coins INTEGER DEFAULT 0,
  -- Metadata
  createdAt TEXT DEFAULT CURRENT_TIMESTAMP,
  updatedAt TEXT DEFAULT CURRENT_TIMESTAMP
);

Prayer Columns:

prayerLevel / prayerXp — Prayer skill progression
prayerPoints — Current prayer points (0 to prayerLevel)
prayerMaxPoints — Maximum prayer points (equals prayerLevel)
activePrayers — JSON array of active prayer IDs (e.g., '["thick_skin"]')

Prayer points are stored as integers in the database but tracked with fractional precision in-memory for accurate drain calculations.

SKIP_MIGRATIONS Environment Variable

When SKIP_MIGRATIONS=true, the server skips:

Built-in migration execution
hasRequiredPublicTables validation check
Migration recovery loop

Use Cases:

CI/testing environments using drizzle-kit push for declarative schema creation
External schema management (avoids FK ordering issues in migration files)
Integration tests that create schema before server startup

Important: When using SKIP_MIGRATIONS=true, you MUST create the database schema externally (e.g., via drizzle-kit push) before starting the server. The server will not create tables or run migrations. Example CI Workflow (commits eb8652a, 6a5f4ee):

# Create schema declaratively (no migration journal)
bunx drizzle-kit push

# Start server with migrations AND table validation skipped
SKIP_MIGRATIONS=true bun run start

Why This Exists:

Server’s built-in migrations have FK ordering issues (migration 0050 references arena_rounds from older migrations)
drizzle-kit push creates schema declaratively without these problems
Prevents “relation already exists” errors on fresh databases
Allows CI to use declarative schema creation instead of sequential migrations
Fixed in commits: eb8652a (CI integration), 6a5f4ee (table validation skip)

Behavior Changes (commit 6a5f4ee): Previously, SKIP_MIGRATIONS=true only skipped migration execution but still ran:

hasRequiredPublicTables check
Migration recovery loop

This caused failures when schema was created externally via drizzle-kit push because the migration journal wasn’t populated. Now, SKIP_MIGRATIONS=true skips ALL migration-related checks:

// From packages/server/src/startup/database.ts
if (process.env.SKIP_MIGRATIONS === 'true') {
  console.log('[Database] SKIP_MIGRATIONS=true, bypassing migration system');
  // Skip hasRequiredPublicTables check
  // Skip migration execution
  // Skip migration recovery loop
  return;
}

Implementation Details: The SKIP_MIGRATIONS check is placed at the beginning of the migration function, before any database validation or migration execution:

// From packages/server/src/startup/database.ts (inferred from commit 6a5f4ee)
export async function runMigrations(db: Database): Promise<void> {
  // Check SKIP_MIGRATIONS first, before any validation
  if (process.env.SKIP_MIGRATIONS === 'true') {
    console.log('[Database] SKIP_MIGRATIONS=true, bypassing migration system');
    return; // Exit early, skip all migration-related code
  }
  
  // Normal migration flow (only runs when SKIP_MIGRATIONS is not set)
  await hasRequiredPublicTables(db);
  await executeMigrations(db);
  await recoveryLoop(db);
}

This ensures that when SKIP_MIGRATIONS=true, the server assumes the schema is already created and valid, and proceeds directly to application startup.

Do NOT run drizzle-kit push then start the server without SKIP_MIGRATIONS=true. This creates tables without populating the migration journal, causing the server’s migration code to fail on re-creation attempts.

Drizzle Commands

Run from packages/server/:

Push Schema

Apply schema changes directly (development):

bunx drizzle-kit push

Generate Migrations

Create migration files for changes:

bunx drizzle-kit generate

Run Migrations

Apply pending migrations:

bunx drizzle-kit migrate

Studio

Open Drizzle Studio to browse data:

bunx drizzle-kit studio

Configuration

packages/server/drizzle.config.ts:

export default {
  schema: './src/database/schema.ts',
  out: './drizzle',
  driver: 'pg',
  dbCredentials: {
    connectionString: process.env.DATABASE_URL
  }
};

Development vs Production

Development (SQLite)

No configuration needed—uses local SQLite file:

packages/server/hyperscape.db

Production (PostgreSQL)

Set DATABASE_URL in environment:

DATABASE_URL=postgresql://user:pass@host:5432/hyperscape

pgvector Extension Required: Production deployments must use PostgreSQL 16+ with the pgvector extension. For Docker deployments, use pgvector/pgvector:pg16 instead of postgres:16-alpine (updated in commit a9e9003).

Connection Pooling (Supabase)

When using Supabase with the Supavisor connection pooler, prepared statements must be disabled (fixed in commits 8aaaf28, f7ab9f7):

// From packages/server/src/database/client.ts
const client = postgres(connectionString, {
  prepare: false,  // Disable prepared statements for Supavisor compatibility
  max: 10,
  idle_timeout: 20,
  connect_timeout: 10
});

Why This Is Needed:

Supavisor operates in transaction mode for connection pooling
Transaction mode doesn’t support prepared statements (PostgreSQL limitation)
Attempting to use prepared statements causes XX000 errors: “prepared statement does not exist”
Disabling prepared statements trades minor performance for compatibility

When to Disable:

Using Supabase with Supavisor pooler
Getting XX000 errors on database queries
Connection string contains pooler.supabase.com

When to Keep Enabled:

Local PostgreSQL development
Direct PostgreSQL connections (no pooler)
PgBouncer in statement mode
Neon, Railway, or other providers without transaction pooling

If you see XX000 errors, check if your connection string uses a pooler and set prepare: false.

Migration Workflow

Automatic Migrations (Default)

By default, the server automatically runs migrations on startup:

Checks for required public tables
Runs pending migrations from src/database/migrations/
Populates migration journal table
Validates schema integrity

Skip Migrations (CI/Testing)

For CI environments where schema is created externally via drizzle-kit push, set:

SKIP_MIGRATIONS=true

Behavior when enabled (commit 6a5f4ee):

Skips migration execution
Skips hasRequiredPublicTables check
Skips migration recovery loop
Assumes schema is already created and valid

Use cases:

CI integration tests using drizzle-kit push for declarative schema creation
Avoids foreign key ordering issues in migration files
Prevents migration journal conflicts when schema created externally

Why this is needed:

Server’s built-in migration has FK ordering issues (migration 0050 references arena_rounds from older migrations)
drizzle-kit push creates schema declaratively without these problems
Allows CI to use push for clean schema creation, then skip server migration

Only use SKIP_MIGRATIONS=true in CI/testing environments. Production should always run migrations normally.

Migrations

Migrations are stored in packages/server/src/database/migrations/:

Recent Migrations

Migration	Description
`0051_add_arena_composite_indexes.sql`	Added composite indexes for arena queries
`0050_broken_krista_starr.sql`	Arena betting and staking tables (fixed in commit e4b6489)
`0049_make_arena_fee_share_bet_unique.sql`	Unique constraint on arena fee shares
`0048_restore_arena_multiplier_floor_at_1k.sql`	Arena points multiplier floor
`0047_add_arena_staking_points_wallet_period_unique.sql`	Unique constraint on staking points
`0046_fix_arena_multiplier_defaults.sql`	Arena points multiplier defaults
`0045_add_arena_chain_fees_and_wallet_links.sql`	Arena chain fees and wallet linking
`0044_add_arena_staking_points.sql`	Arena staking points system
`0043_add_arena_invite_and_fee_share_tracking.sql`	Arena invite and fee share tracking
`0042_convert_active_prayers_to_jsonb.sql`	Convert activePrayers to JSONB
`0041_add_failed_transactions.sql`	Failed transaction tracking
`0040_validate_constraints_and_add_agent_checks.sql`	Constraint validation and agent checks
`0039_add_agent_duel_stats.sql`	Agent duel statistics tracking
`0038_odd_luke_cage.sql`	Combat stats and onchain outbox
`0037_track_player_combat_stats_and_onchain_outbox.sql`	Player combat stats tracking
`0027_messy_dorian_gray.sql`	Added operations_log table for write-ahead logging and crash recovery
`0026_add_magic_skill.sql`	Added magicLevel, magicXp, and selectedSpell columns for magic combat
`0023_rapid_scorpion.sql`	Added action_bar_storage table for action bar persistence
`0022_warm_spacker_dave.sql`	Added activity_log, trades, and user_bans tables
`0016_add_prayer_system.sql`	Added prayer skill, points, and active prayers tracking
`0015_add_smithing_skill.sql`	Added smithingLevel and smithingXp columns
`0014_add_mining_skill.sql`	Added miningLevel and miningXp columns

Migration Format

-- 0016_add_prayer_system.sql
ALTER TABLE "characters" ADD COLUMN IF NOT EXISTS "prayerLevel" integer DEFAULT 1;
ALTER TABLE "characters" ADD COLUMN IF NOT EXISTS "prayerXp" integer DEFAULT 0;
ALTER TABLE "characters" ADD COLUMN IF NOT EXISTS "prayerPoints" integer DEFAULT 1;
ALTER TABLE "characters" ADD COLUMN IF NOT EXISTS "prayerMaxPoints" integer DEFAULT 1;
ALTER TABLE "characters" ADD COLUMN IF NOT EXISTS "activePrayers" text DEFAULT '[]';

Migrations use IF NOT EXISTS to be idempotent and safe to re-run.

Migration 0050 Fix (commit e4b6489)

Migration 0050 was fixed to add IF NOT EXISTS guards to prevent errors on fresh databases: Problem:

Migration 0050 duplicated CREATE TABLE statements from earlier migrations
agent_duel_stats table was created in both migration 0039 and 0050
On fresh databases, running all migrations sequentially caused 42P07 errors
Same table created twice without IF NOT EXISTS protection

Solution:

-- Before (caused errors)
CREATE TABLE agent_duel_stats (...);
CREATE INDEX idx_agent_duel_stats_character_id ON agent_duel_stats(character_id);

-- After (fixed in commit e4b6489)
CREATE TABLE IF NOT EXISTS agent_duel_stats (...);
CREATE INDEX IF NOT EXISTS idx_agent_duel_stats_character_id ON agent_duel_stats(character_id);

Affected Tables:

agent_duel_stats
arena_fee_shares
arena_staking_points
wallet_links

Impact:

Fresh database installations now complete all migrations successfully
Prevents “relation already exists” errors (42P07)
Allows clean database setup from scratch
Fixes CI integration test failures

All migrations should use IF NOT EXISTS guards to be idempotent and safe to re-run.

The activePrayers column stores a JSON array of prayer IDs. Format: '["thick_skin", "burst_of_strength"]'. Empty array when no prayers are active: '[]'.

Schema Changes

Edit schema

Modify packages/server/src/database/schema.ts

Generate migration

cd packages/server
bunx drizzle-kit generate

Apply migration

bunx drizzle-kit migrate

Verify

Check that migration file was created in src/database/migrations/

Reset Database

Development Reset

Delete the SQLite file:

rm packages/server/hyperscape.db
bun run dev

Docker PostgreSQL Reset

docker stop hyperscape-postgres
docker rm hyperscape-postgres
docker volume rm hyperscape-postgres-data
docker volume rm server_postgres-data
bun run dev

This permanently deletes all player data.

Docker Image Update: The local PostgreSQL container now uses pgvector/pgvector:pg16 instead of postgres:16-alpine to support vector similarity search features (commit a9e9003, Feb 2026).

Backup and Restore

PostgreSQL Backup

docker exec hyperscape-postgres pg_dump -U postgres hyperscape > backup.sql

PostgreSQL Restore

docker exec -i hyperscape-postgres psql -U postgres hyperscape < backup.sql

Troubleshooting

Schema Out of Sync

If you see schema errors after pulling updates:

cd packages/server
bunx drizzle-kit push

Connection Refused

Ensure PostgreSQL is running:

docker ps | grep postgres

If not running:

bun run dev   # Auto-starts PostgreSQL

Configuration

Operations

​Overview

​Schema Location

​Tables

​Characters Table Schema

​SKIP_MIGRATIONS Environment Variable

​Drizzle Commands

​Push Schema

​Generate Migrations

​Run Migrations

​Studio

​Configuration

​Development vs Production

​Development (SQLite)

​Production (PostgreSQL)

​Connection Pooling (Supabase)

​Migration Workflow

​Automatic Migrations (Default)

​Skip Migrations (CI/Testing)

​Migrations

​Recent Migrations

​Migration Format

​Migration 0050 Fix (commit e4b6489)

​Schema Changes

​Reset Database

​Development Reset

​Docker PostgreSQL Reset

​Backup and Restore

​PostgreSQL Backup

​PostgreSQL Restore

​Troubleshooting

​Schema Out of Sync

​Connection Refused

Overview

Schema Location

Tables

Characters Table Schema

SKIP_MIGRATIONS Environment Variable

Drizzle Commands

Push Schema

Generate Migrations

Run Migrations

Studio

Configuration

Development vs Production

Development (SQLite)

Production (PostgreSQL)

Connection Pooling (Supabase)

Migration Workflow

Automatic Migrations (Default)

Skip Migrations (CI/Testing)

Migrations

Recent Migrations

Migration Format

Migration 0050 Fix (commit e4b6489)

Schema Changes

Reset Database

Development Reset

Docker PostgreSQL Reset

Backup and Restore

PostgreSQL Backup

PostgreSQL Restore

Troubleshooting

Schema Out of Sync

Connection Refused