Database Schema

Hyperscape uses PostgreSQL in production and SQLite for local development, with Drizzle ORM for type-safe database access.

Database schema is defined in packages/server/src/database/schema.ts.

Overview

Table	Purpose	Repository
`config`	System-wide key-value configuration	-
`users`	User accounts (Privy/Farcaster auth)	UserRepository
`characters`	Player character data	CharacterRepository
`inventory`	Character inventory items	InventoryRepository
`equipment`	Equipped items	EquipmentRepository
`bank_storage`	Bank storage (480 slots)	BankRepository
`bank_tabs`	Custom bank tab configuration	BankRepository
`world_chunks`	Persistent world modifications	WorldRepository
`player_sessions`	Login/logout tracking	SessionRepository
`npc_kills`	Kill statistics	StatsRepository
`death_locks`	Death state for reconnects	PlayerDeathSystem
`operations_log`	Write-ahead logging for crash recovery	PersistenceService
`activity_log`	Player activity tracking	ActivityLogRepository
`trades`	Trade history	TradingSystem
`user_bans`	Ban management	ModerationSystem
`action_bar_storage`	Action bar configurations	ActionBarSystem

Core Tables

Users

// From schema.ts
export const users = pgTable('users', {
  id: text('id').primaryKey(),                    // UUID
  privyUserId: text('privy_user_id').unique(),    // Privy auth ID
  farcasterFid: text('farcaster_fid'),            // Farcaster ID
  role: text('role').default('player'),           // player, admin, moderator
  createdAt: bigint('created_at', { mode: 'number' }).notNull(),
  updatedAt: bigint('updated_at', { mode: 'number' }).notNull(),
});

// Indexes for fast lookup
export const usersIndexes = {
  privyUserIdIdx: index('privy_user_id_idx').on(users.privyUserId),
  farcasterFidIdx: index('farcaster_fid_idx').on(users.farcasterFid),
};

Characters

export const characters = pgTable('characters', {
  id: text('id').primaryKey(),                    // UUID
  accountId: text('account_id').notNull()         // References users.id
    .references(() => users.id),
  name: text('name').notNull(),                   // Character name

  // Position (stored as floats for sub-tile precision)
  positionX: real('position_x').notNull().default(0),
  positionY: real('position_y').notNull().default(0),
  positionZ: real('position_z').notNull().default(0),

  // Skill levels (individual columns for efficient queries)
  attackLevel: integer('attackLevel').default(1),
  strengthLevel: integer('strengthLevel').default(1),
  defenseLevel: integer('defenseLevel').default(1),
  constitutionLevel: integer('constitutionLevel').default(10),
  rangedLevel: integer('rangedLevel').default(1),
  magicLevel: integer('magicLevel').default(1),
  prayerLevel: integer('prayerLevel').default(1),
  woodcuttingLevel: integer('woodcuttingLevel').default(1),
  miningLevel: integer('miningLevel').default(1),
  fishingLevel: integer('fishingLevel').default(1),
  firemakingLevel: integer('firemakingLevel').default(1),
  cookingLevel: integer('cookingLevel').default(1),
  smithingLevel: integer('smithingLevel').default(1),
  agilityLevel: integer('agilityLevel').default(1),

  // Skill XP (individual columns)
  attackXp: integer('attackXp').default(0),
  strengthXp: integer('strengthXp').default(0),
  defenseXp: integer('defenseXp').default(0),
  constitutionXp: integer('constitutionXp').default(1154),
  rangedXp: integer('rangedXp').default(0),
  magicXp: integer('magicXp').default(0),
  prayerXp: integer('prayerXp').default(0),
  woodcuttingXp: integer('woodcuttingXp').default(0),
  miningXp: integer('miningXp').default(0),
  fishingXp: integer('fishingXp').default(0),
  firemakingXp: integer('firemakingXp').default(0),
  cookingXp: integer('cookingXp').default(0),
  smithingXp: integer('smithingXp').default(0),
  agilityXp: integer('agilityXp').default(0),

  // Combat state
  health: integer('health').default(100),
  maxHealth: integer('maxHealth').default(100),
  combatState: text('combat_state').default('idle'), // idle, fighting, dead
  lastCombatTick: bigint('last_combat_tick', { mode: 'number' }),

  // Combat preferences
  attackStyle: text('attackStyle').default('accurate'),
  autoRetaliate: integer('autoRetaliate').default(1).notNull(), // 1=ON (default), 0=OFF
  selectedSpell: text('selectedSpell'), // Autocast spell ID (null = no autocast)

  // Timestamps
  lastLogin: bigint('last_login', { mode: 'number' }),
  createdAt: bigint('created_at', { mode: 'number' }).notNull(),
  updatedAt: bigint('updated_at', { mode: 'number' }).notNull(),
});

Skills are stored as individual columns (e.g., smithingLevel, smithingXp) rather than JSONB for efficient querying and indexing.

Inventory

export const inventory = pgTable('inventory', {
  id: text('id').primaryKey(),
  characterId: text('character_id').notNull()
    .references(() => characters.id, { onDelete: 'cascade' }),
  itemId: text('item_id').notNull(),              // Item definition ID
  quantity: integer('quantity').notNull().default(1),
  slot: integer('slot').notNull(),                // Inventory slot (0-27)
  createdAt: bigint('created_at', { mode: 'number' }).notNull(),
});

// Unique constraint: one item per slot per character
export const inventoryIndexes = {
  characterSlotIdx: uniqueIndex('character_slot_idx')
    .on(inventory.characterId, inventory.slot),
};

Equipment

export const equipment = pgTable('equipment', {
  id: text('id').primaryKey(),
  characterId: text('character_id').notNull()
    .references(() => characters.id, { onDelete: 'cascade' }),
  itemId: text('item_id').notNull(),
  slotType: text('slot_type').notNull(),          // weapon, shield, helmet, etc.
  createdAt: bigint('created_at', { mode: 'number' }).notNull(),
});

// Equipment slot types
type EquipmentSlotType =
  | 'weapon'
  | 'shield'
  | 'helmet'
  | 'body'
  | 'legs'
  | 'boots'
  | 'gloves'
  | 'cape'
  | 'ring'
  | 'amulet'
  | 'ammo';

Bank System

Bank Storage

export const bankStorage = pgTable('bank_storage', {
  id: text('id').primaryKey(),
  characterId: text('character_id').notNull()
    .references(() => characters.id, { onDelete: 'cascade' }),
  itemId: text('item_id').notNull(),
  quantity: integer('quantity').notNull().default(1),
  slot: integer('slot').notNull(),                // Bank slot (0-479)
  tabIndex: integer('tab_index').default(0),      // Bank tab (0-9)
  createdAt: bigint('created_at', { mode: 'number' }).notNull(),
  updatedAt: bigint('updated_at', { mode: 'number' }).notNull(),
});

// 480 bank slots total (OSRS-style)
export const BANK_CONSTANTS = {
  MAX_SLOTS: 480,
  MAX_TABS: 10,
  SLOTS_PER_TAB: 48,
};

Bank Tabs

export const bankTabs = pgTable('bank_tabs', {
  id: text('id').primaryKey(),
  characterId: text('character_id').notNull()
    .references(() => characters.id, { onDelete: 'cascade' }),
  tabIndex: integer('tab_index').notNull(),       // 0-9
  name: text('name'),                             // Custom tab name
  icon: text('icon'),                             // Icon item ID
  createdAt: bigint('created_at', { mode: 'number' }).notNull(),
});

Persistence & Crash Recovery

Operations Log

The operations_log table provides write-ahead logging for critical operations:

export const operationsLog = pgTable(
  "operations_log",
  {
    id: text("id").primaryKey(), // UUID
    playerId: text("playerId").notNull(),
    operationType: text("operationType").notNull(), // 'trade', 'bank', 'equipment', 'inventory'
    operationState: jsonb("operationState").notNull(), // Full operation data for replay
    completed: boolean("completed").default(false),
    timestamp: bigint("timestamp", { mode: "number" }).notNull(),
    completedAt: bigint("completedAt", { mode: "number" }),
  },
  (table) => ({
    // Index for recovery queries - find incomplete operations for a player
    incompleteIdx: index("idx_operations_log_incomplete").on(
      table.playerId,
      table.completed,
    ),
    // Index for cleanup queries - find old completed operations
    timestampIdx: index("idx_operations_log_timestamp").on(table.timestamp),
  }),
);

Write-Ahead Log Pattern:

Log operation intent with state
Execute operation
Mark operation complete
On startup, replay any incomplete operations

Use Cases:

Trade completions
Bank transactions
Equipment changes
Inventory modifications

Phase 2 Status: The operations_log table and PersistenceService are scaffolding for future integration. They are not currently wired up to TradingSystem or BankSystem.

See Persistence Architecture for complete details on write-ahead logging, transactional saves, and crash recovery.

World Persistence

World Chunks

export const worldChunks = pgTable('world_chunks', {
  id: text('id').primaryKey(),                    // "chunk_x_z" format
  chunkX: integer('chunk_x').notNull(),
  chunkZ: integer('chunk_z').notNull(),
  data: jsonb('data').$type<ChunkModifications>(),
  lastModified: bigint('last_modified', { mode: 'number' }).notNull(),
});

interface ChunkModifications {
  harvestedResources: Array<{
    resourceId: string;
    harvestedAt: number;
    respawnAt: number;
  }>;
  placedObjects: Array<{
    type: string;
    position: { x: number; y: number; z: number };
    placedBy: string;
    placedAt: number;
  }>;
}

Player Sessions

export const playerSessions = pgTable('player_sessions', {
  id: text('id').primaryKey(),
  characterId: text('character_id').notNull()
    .references(() => characters.id),
  loginTime: bigint('login_time', { mode: 'number' }).notNull(),
  logoutTime: bigint('logout_time', { mode: 'number' }),
  duration: integer('duration'),                   // Seconds played
  ipAddress: text('ip_address'),
});

Statistics Tables

NPC Kills

export const npcKills = pgTable('npc_kills', {
  id: text('id').primaryKey(),
  characterId: text('character_id').notNull()
    .references(() => characters.id),
  npcId: text('npc_id').notNull(),                // NPC definition ID
  killCount: integer('kill_count').notNull().default(0),
  lastKillAt: bigint('last_kill_at', { mode: 'number' }),
});

// Track kills per NPC type
export const npcKillsIndexes = {
  characterNpcIdx: uniqueIndex('character_npc_idx')
    .on(npcKills.characterId, npcKills.npcId),
};

Repository Pattern

All database access goes through typed repositories:

// From repositories/CharacterRepository.ts
export class CharacterRepository {
  constructor(private db: DrizzleDB) {}

  async findById(id: string): Promise<Character | null> {
    const result = await this.db
      .select()
      .from(characters)
      .where(eq(characters.id, id))
      .limit(1);
    return result[0] || null;
  }

  async findByAccountId(accountId: string): Promise<Character[]> {
    return this.db
      .select()
      .from(characters)
      .where(eq(characters.accountId, accountId));
  }

  async create(data: NewCharacter): Promise<Character> {
    const [character] = await this.db
      .insert(characters)
      .values({
        ...data,
        createdAt: Date.now(),
        updatedAt: Date.now(),
      })
      .returning();
    return character;
  }

  async updatePosition(id: string, position: Position3D): Promise<void> {
    await this.db
      .update(characters)
      .set({
        positionX: position.x,
        positionY: position.y,
        positionZ: position.z,
        updatedAt: Date.now(),
      })
      .where(eq(characters.id, id));
  }

  async updateStats(id: string, stats: CharacterStats): Promise<void> {
    await this.db
      .update(characters)
      .set({
        stats,
        updatedAt: Date.now(),
      })
      .where(eq(characters.id, id));
  }
}

Performance Optimizations

Inventory Write Coalescing

The DatabaseSystem uses write coalescing to prevent connection pool starvation during batch operations:

// From DatabaseSystem/index.ts
/**
 * Write coalescing for inventory persistence.
 * When multiple savePlayerInventoryAsync calls arrive for the same player,
 * only the LATEST snapshot is written. At most 2 DB transactions run per
 * player: one active + one queued batch with the newest data.
 * Prevents both PostgreSQL deadlocks and connection pool starvation.
 */
private inventoryWriteActive = new Map<string, Promise<void>>();
private inventoryWriteQueued = new Map<string, {
  items: InventorySaveItem[];
  waiters: Array<{ resolve: () => void; reject: (err: unknown) => void; }>;
}>();

How It Works:

First write executes immediately - No active write for this player, execute directly
Concurrent writes queue - If a write is active, queue the latest snapshot
Latest snapshot wins - New writes replace queued data (only newest matters)
All waiters resolve together - When batch completes, all waiting promises resolve
Recursive drain - After active write completes, drain queued batch if any

Performance Impact:

Scenario	Before (Per-Player Lock)	After (Write Coalescing)
Fletching 100 arrows	200 sequential transactions	2 transactions per player
Batch smithing	N sequential transactions	≤2 transactions per player
Connection pool usage	Starves pool (200+ pending)	Minimal (2 active per player)

Code Example:

async savePlayerInventoryAsync(
  playerId: string,
  items: InventorySaveItem[],
): Promise<void> {
  // If a write is already running for this player, coalesce into the queued batch
  if (this.inventoryWriteActive.has(playerId)) {
    return new Promise<void>((resolve, reject) => {
      const queued = this.inventoryWriteQueued.get(playerId);
      if (queued) {
        // Replace items with the latest snapshot — only the newest matters
        queued.items = items;
        queued.waiters.push({ resolve, reject });
      } else {
        this.inventoryWriteQueued.set(playerId, {
          items,
          waiters: [{ resolve, reject }],
        });
      }
    });
  }

  // No active write — execute immediately
  await this.executeInventoryWrite(playerId, items);
}

private async executeInventoryWrite(
  playerId: string,
  items: InventorySaveItem[],
): Promise<void> {
  const writePromise = this.inventoryRepository.savePlayerInventoryAsync(playerId, items);
  this.inventoryWriteActive.set(playerId, writePromise);

  try {
    await writePromise;
  } finally {
    this.inventoryWriteActive.delete(playerId);

    // Drain the queued batch if any calls arrived while we were writing
    const queued = this.inventoryWriteQueued.get(playerId);
    if (queued) {
      this.inventoryWriteQueued.delete(playerId);
      try {
        await this.executeInventoryWrite(playerId, queued.items);
        for (const w of queued.waiters) w.resolve();
      } catch (err) {
        for (const w of queued.waiters) w.reject(err);
      }
    }
  }
}

Graceful Shutdown: On DatabaseSystem.destroy(), orphaned waiters are rejected to prevent hanging promises:

destroy(): void {
  this.inventoryWriteActive.clear();
  // Reject any orphaned waiters so their promises don't hang forever
  for (const [, queued] of this.inventoryWriteQueued) {
    for (const w of queued.waiters) {
      w.reject(new Error("DatabaseSystem destroyed"));
    }
  }
  this.inventoryWriteQueued.clear();
}

This optimization was added in PR #823 to fix “200 pending operations” warnings and game freezes during batch crafting operations like fletching.

Data Types

Timestamps

All timestamps use bigint storing Unix milliseconds for precision:

// Store
const timestamp = Date.now(); // e.g., 1704067200000

// Retrieve
const date = new Date(timestamp);

Positions

Positions use real (float) for sub-tile precision:

// Tile center is (x + 0.5, z + 0.5)
positionX: 125.5,
positionY: 10.0,
positionZ: -42.5,

IDs

All IDs are text storing UUIDs as strings:

import { uuid } from '@hyperscape/shared';

const id = uuid(); // "550e8400-e29b-41d4-a716-446655440000"

Migrations

Drizzle handles migrations automatically. Run from packages/server/:

# Generate migration from schema changes
bunx drizzle-kit generate

# Apply migrations
bunx drizzle-kit migrate

# Push schema directly (dev only)
bunx drizzle-kit push

Recent Migrations

Migration	Description
`0027_messy_dorian_gray.sql`	Added `operations_log` table, `magicLevel`, `magicXp`, `selectedSpell` columns
`0026_add_magic_skill.sql`	Added `magicLevel`, `magicXp`, `selectedSpell` columns
`0025_thin_toad_men.sql`	Added agility skill columns
`0024_natural_luckman.sql`	Added duel system tables
`0023_rapid_scorpion.sql`	Added `action_bar_storage` table
`0022_warm_spacker_dave.sql`	Added `activity_log`, `trades`, `user_bans` tables
`0021_add_agility_skill.sql`	Added `agilityLevel` and `agilityXp` columns
`0020_charming_revanche.sql`	Added quest system tables
`0019_greedy_sabretooth.sql`	Added friends system tables
`0018_add_death_recovery_columns.sql`	Added death recovery state columns
`0017_conscious_azazel.sql`	Added bank placeholders
`0016_add_prayer_system.sql`	Added `prayerLevel` and `prayerXp` columns
`0015_add_smithing_skill.sql`	Added `smithingLevel` and `smithingXp` columns
`0014_add_mining_skill.sql`	Added `miningLevel` and `miningXp` columns
`0013_add_auto_retaliate.sql`	Added auto-retaliate preference

Migrations are located in packages/server/src/database/migrations/.

Overview

Engine Architecture

Client Application

Game Systems

AI Agents

Data Manifests

Technical Reference

Database Schema

Database Schema

Overview

Core Tables

Users

Characters

Inventory

Equipment

Bank System

Bank Storage

Bank Tabs

Persistence & Crash Recovery

Operations Log

World Persistence

World Chunks

Player Sessions

Statistics Tables

NPC Kills

Repository Pattern

Performance Optimizations

Inventory Write Coalescing

Data Types

Timestamps

Positions

IDs

Migrations

Recent Migrations

Overview

Engine Architecture

Client Application

Game Systems

AI Agents

Data Manifests

Technical Reference

​Database Schema

​Overview

​Core Tables

​Users

​Characters

​Inventory

​Equipment

​Bank System

​Bank Storage

​Bank Tabs

​Persistence & Crash Recovery

​Operations Log

​World Persistence

​World Chunks

​Player Sessions

​Statistics Tables

​NPC Kills

​Repository Pattern

​Performance Optimizations

​Inventory Write Coalescing

​Data Types

​Timestamps

​Positions

​IDs

​Migrations

​Recent Migrations

​Related Documentation

Database Schema

Overview

Core Tables

Users

Characters

Inventory

Equipment

Bank System

Bank Storage

Bank Tabs

Persistence & Crash Recovery

Operations Log

World Persistence

World Chunks

Player Sessions

Statistics Tables

NPC Kills

Repository Pattern

Performance Optimizations

Inventory Write Coalescing

Data Types

Timestamps

Positions

IDs

Migrations

Recent Migrations

Related Documentation