Database Management

Drizzle ORM, migrations, and schema management

Overview

Hyperscape uses Drizzle ORM for database access:

• Development: SQLite (zero config)
• Production: PostgreSQL

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
quest_progress	Player quest progress and completion
quest_audit_log	Quest action audit trail (security)
activity_log	Player activity logging (admin panel)
trades	Trade history
user_bans	User ban records (moderation)

Quest Tables

quest_progress - Tracks player quest state:

CREATE TABLE quest_progress (
  id SERIAL PRIMARY KEY,
  playerId TEXT NOT NULL REFERENCES characters(id) ON DELETE CASCADE,
  questId TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'not_started',
  currentStage TEXT,
  stageProgress JSONB DEFAULT '{}',
  startedAt BIGINT,
  completedAt BIGINT,
  CONSTRAINT quest_progress_player_quest_unique UNIQUE(playerId, questId)
);

quest_audit_log - Immutable audit trail for security:

CREATE TABLE quest_audit_log (
  id SERIAL PRIMARY KEY,
  playerId TEXT NOT NULL REFERENCES characters(id) ON DELETE CASCADE,
  questId TEXT NOT NULL,
  action TEXT NOT NULL,
  questPointsAwarded INTEGER DEFAULT 0,
  stageId TEXT,
  stageProgress JSONB DEFAULT '{}',
  timestamp BIGINT NOT NULL,
  metadata JSONB DEFAULT '{}'
);

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,
  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,
  agilityLevel 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,
  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,
  agilityXp INTEGER DEFAULT 0,
  -- Prayer
  prayerPoints INTEGER DEFAULT 1,
  prayerMaxPoints INTEGER DEFAULT 1,
  activePrayers TEXT DEFAULT '[]',
  -- Quest progression
  questPoints INTEGER DEFAULT 0,
  -- Status
  health INTEGER DEFAULT 100,
  maxHealth INTEGER DEFAULT 100,
  coins INTEGER DEFAULT 0,
  autoRetaliate INTEGER DEFAULT 1,
  -- 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.

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

Migrations

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

Recent Migrations

Migration	Description
0021_add_quest_audit_log.sql	Added quest audit logging for security
0020_add_trading_activity_bans.sql	Added trading, activity log, and user bans tables
0019_add_quest_progress.sql	Added quest progress tracking and quest points
0018_add_agility_skill.sql	Added agility skill
0017_add_prayer_skill.sql	Added prayer skill and prayer points
0016_add_cooking_skill.sql	Added cooking skill
0015_add_smithing_skill.sql	Added smithing skill
0014_add_mining_skill.sql	Added mining skill
0013_add_auto_retaliate.sql	Added autoRetaliate preference
0012_add_bank_placeholders.sql	Added bank placeholder support
0011_add_bank_tabs.sql	Added bank tab system
0010_add_player_deaths.sql	Added death tracking

Migration Format

-- 0018_add_agility_skill.sql
ALTER TABLE "characters" ADD COLUMN IF NOT EXISTS "agilityLevel" integer DEFAULT 1;--> statement-breakpoint
ALTER TABLE "characters" ADD COLUMN IF NOT EXISTS "agilityXp" integer DEFAULT 0;--> statement-breakpoint

Migrations use IF NOT EXISTS to be idempotent and safe to re-run. The --> statement-breakpoint comment is used by Drizzle to separate statements.

Migration Journal

Migrations must be registered in meta/_journal.json to run:

{
  "entries": [
    {
      "idx": 18,
      "version": "7",
      "when": 1768728921620,
      "tag": "0018_add_agility_skill",
      "breakpoints": true
    }
  ]
}

If you create a migration file but forget to add it to _journal.json, it will not run automatically.

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.

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