Database Management

Drizzle ORM, migrations, and schema management

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:

1. Checks for required public tables
2. Runs pending migrations from src/database/migrations/
3. Populates migration journal table
4. 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

1. Edit schema

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

2. Generate migration

   cd packages/server
   bunx drizzle-kit generate
   
3. Apply migration

   bunx drizzle-kit migrate
   
4. 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