Database Management - Hyperscape

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

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.

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

​Quest Tables

​Characters Table Schema

​Drizzle Commands

​Push Schema

​Generate Migrations

​Run Migrations

​Studio

​Configuration

​Development vs Production

​Development (SQLite)

​Production (PostgreSQL)

​Migrations

​Recent Migrations

​Migration Format

​Migration Journal

​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

Quest Tables

Characters Table Schema

Drizzle Commands

Push Schema

Generate Migrations

Run Migrations

Studio

Configuration

Development vs Production

Development (SQLite)

Production (PostgreSQL)

Migrations

Recent Migrations

Migration Format

Migration Journal

Schema Changes

Reset Database

Development Reset

Docker PostgreSQL Reset

Backup and Restore

PostgreSQL Backup

PostgreSQL Restore

Troubleshooting

Schema Out of Sync

Connection Refused