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
`quest_progress`	Player quest state tracking	QuestRepository
`quest_audit_log`	Quest action audit trail	QuestRepository
`world_chunks`	Persistent world modifications	WorldRepository
`player_sessions`	Login/logout tracking	SessionRepository
`npc_kills`	Kill statistics	StatsRepository
`death_locks`	Death state for reconnects	PlayerDeathSystem
`quest_progress`	Player quest progression	QuestRepository
`quest_audit_log`	Quest action audit trail	QuestRepository
`activity_log`	Player activity logging	ActivityLogRepository
`trades`	Trade history	TradingSystem
`user_bans`	Moderation bans	AdminSystem

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),

  // Combat skill levels
  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),
  
  // Prayer skill (added in PR #563)
  prayerLevel: integer('prayerLevel').default(1),
  
  // Gathering skill levels
  woodcuttingLevel: integer('woodcuttingLevel').default(1),
  miningLevel: integer('miningLevel').default(1),
  fishingLevel: integer('fishingLevel').default(1),
  
  // Artisan skill levels
  firemakingLevel: integer('firemakingLevel').default(1),
  cookingLevel: integer('cookingLevel').default(1),
  smithingLevel: integer('smithingLevel').default(1),
  agilityLevel: integer('agilityLevel').default(1),

  // Combat skill XP
  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),
  
  // Prayer skill XP (added in PR #563)
  prayerXp: integer('prayerXp').default(0),
  
  // Gathering skill XP
  woodcuttingXp: integer('woodcuttingXp').default(0),
  miningXp: integer('miningXp').default(0),
  fishingXp: integer('fishingXp').default(0),
  
  // Artisan skill XP
  firemakingXp: integer('firemakingXp').default(0),
  cookingXp: integer('cookingXp').default(0),
  smithingXp: integer('smithingXp').default(0),
  agilityXp: integer('agilityXp').default(0),

  // Prayer
  prayerPoints: integer('prayerPoints').default(1),
  prayerMaxPoints: integer('prayerMaxPoints').default(1),
  activePrayers: text('activePrayers').default('[]'),

  // Quest progression
  questPoints: integer('questPoints').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' }),

  // Quest progression
  questPoints: integer('questPoints').default(0).notNull(),

  // 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(),
});

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'),
});

Quest System Tables

Quest Progress

Tracks player quest state:

export const questProgress = pgTable('quest_progress', {
  id: serial('id').primaryKey(),
  playerId: text('playerId').notNull()
    .references(() => characters.id, { onDelete: 'cascade' }),
  questId: text('questId').notNull(),              // Quest identifier from manifest
  status: text('status').default('not_started').notNull(),
  currentStage: text('currentStage'),              // Current stage ID
  stageProgress: jsonb('stageProgress').default({}), // e.g., {"kills": 7}
  startedAt: bigint('startedAt', { mode: 'number' }),
  completedAt: bigint('completedAt', { mode: 'number' }),
}, (table) => ({
  uniquePlayerQuest: unique().on(table.playerId, table.questId),
  playerIdx: index('idx_quest_progress_player').on(table.playerId),
  statusIdx: index('idx_quest_progress_status').on(table.playerId, table.status),
}));

Status Values:

not_started — Quest not started
in_progress — Quest active
completed — Quest finished

ready_to_complete is a derived status computed at runtime when objectives are met. It’s not stored in the database.

Quest Audit Log

Immutable audit trail for security and debugging:

export const questAuditLog = pgTable('quest_audit_log', {
  id: serial('id').primaryKey(),
  playerId: text('playerId').notNull()
    .references(() => characters.id, { onDelete: 'cascade' }),
  questId: text('questId').notNull(),
  action: text('action').notNull(),                // "started", "progressed", "completed"
  questPointsAwarded: integer('questPointsAwarded').default(0),
  stageId: text('stageId'),
  stageProgress: jsonb('stageProgress').default({}),
  timestamp: bigint('timestamp', { mode: 'number' }).notNull(),
  metadata: jsonb('metadata').default({}),
}, (table) => ({
  playerIdx: index('idx_quest_audit_log_player').on(table.playerId),
  questIdx: index('idx_quest_audit_log_quest').on(table.questId),
  timestampIdx: index('idx_quest_audit_log_timestamp').on(table.timestamp),
  actionIdx: index('idx_quest_audit_log_action').on(table.action),
}));

Features:

Immutable log (no updates or deletes)
Used for fraud detection and debugging
Indexed for efficient queries by player, quest, and timestamp

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),
};

Quest System Tables

quest_progress

Tracks player quest state:

export const questProgress = pgTable('quest_progress', {
  id: serial('id').primaryKey(),
  playerId: text('playerId').notNull()
    .references(() => characters.id, { onDelete: 'cascade' }),
  questId: text('questId').notNull(),
  status: text('status').default('not_started').notNull(),
  currentStage: text('currentStage'),
  stageProgress: jsonb('stageProgress').default({}),
  startedAt: bigint('startedAt', { mode: 'number' }),
  completedAt: bigint('completedAt', { mode: 'number' }),
}, (table) => ({
  uniquePlayerQuest: unique().on(table.playerId, table.questId),
  playerIdx: index('idx_quest_progress_player').on(table.playerId),
  statusIdx: index('idx_quest_progress_status').on(table.playerId, table.status),
}));

Key Features:

UNIQUE constraint on (playerId, questId) prevents duplicate progress entries
CASCADE DELETE cleans up on character deletion
stageProgress JSONB stores flexible progress data (e.g., {"kills": 7, "copper_ore": 3})
status values: not_started, in_progress, completed

ready_to_complete is a derived status computed by QuestSystem when status === "in_progress" AND the current stage objective is met. It’s not stored in the database.

quest_audit_log

Immutable audit trail for security and analytics:

export const questAuditLog = pgTable('quest_audit_log', {
  id: serial('id').primaryKey(),
  playerId: text('playerId').notNull()
    .references(() => characters.id, { onDelete: 'cascade' }),
  questId: text('questId').notNull(),
  action: text('action').notNull(),  // "started", "progressed", "completed"
  questPointsAwarded: integer('questPointsAwarded').default(0),
  stageId: text('stageId'),
  stageProgress: jsonb('stageProgress').default({}),
  timestamp: bigint('timestamp', { mode: 'number' }).notNull(),
  metadata: jsonb('metadata').default({}),
}, (table) => ({
  playerIdx: index('idx_quest_audit_log_player').on(table.playerId),
  questIdx: index('idx_quest_audit_log_quest').on(table.questId),
  playerQuestIdx: index('idx_quest_audit_log_player_quest').on(table.playerId, table.questId),
  timestampIdx: index('idx_quest_audit_log_timestamp').on(table.timestamp),
  actionIdx: index('idx_quest_audit_log_action').on(table.action),
}));

Use Cases:

Fraud detection and investigation
Debugging quest progression bugs
Analytics for game design
Customer support inquiries

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));
  }
}

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
`0021_add_quest_audit_log.sql`	Added quest audit logging table
`0020_add_trading_activity_bans.sql`	Added trading, activity log, and user bans tables
`0019_add_quest_progress.sql`	Added quest progression tracking
`0018_add_agility_skill.sql`	Added `agilityLevel` and `agilityXp` columns
`0016_add_prayer_system.sql`	Added prayer points and active prayers
`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
`0012_add_bank_placeholders.sql`	Added bank placeholder system
`0011_add_bank_tabs.sql`	Added custom bank tabs
`0010_add_player_deaths.sql`	Added death tracking

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

Quest System Tables

quest_progress

Tracks player quest progression:

export const questProgress = pgTable('quest_progress', {
  id: serial('id').primaryKey(),
  playerId: text('playerId').notNull()
    .references(() => characters.id, { onDelete: 'cascade' }),
  questId: text('questId').notNull(),
  status: text('status').default('not_started').notNull(),
  currentStage: text('currentStage'),
  stageProgress: jsonb('stageProgress').default({}),
  startedAt: bigint('startedAt', { mode: 'number' }),
  completedAt: bigint('completedAt', { mode: 'number' }),
}, (table) => ({
  uniquePlayerQuest: unique().on(table.playerId, table.questId),
  playerIdx: index('idx_quest_progress_player').on(table.playerId),
  statusIdx: index('idx_quest_progress_status').on(table.playerId, table.status),
}));

Status Values:

not_started — Quest not yet started
in_progress — Quest active, objectives incomplete
completed — Quest finished, rewards claimed

Note: ready_to_complete is a derived status (computed when status === "in_progress" AND current stage objective is met). Stage Progress Format:

{
  "kills": 7,              // Kill stage progress
  "copper_ore": 3,         // Gather stage progress (by item ID)
  "fire_created": 1        // Interact stage progress
}

quest_audit_log

Immutable audit trail for quest actions:

export const questAuditLog = pgTable('quest_audit_log', {
  id: serial('id').primaryKey(),
  playerId: text('playerId').notNull()
    .references(() => characters.id, { onDelete: 'cascade' }),
  questId: text('questId').notNull(),
  action: text('action').notNull(),  // "started", "progressed", "completed"
  questPointsAwarded: integer('questPointsAwarded').default(0),
  stageId: text('stageId'),
  stageProgress: jsonb('stageProgress').default({}),
  timestamp: bigint('timestamp', { mode: 'number' }).notNull(),
  metadata: jsonb('metadata').default({}),
}, (table) => ({
  playerIdx: index('idx_quest_audit_log_player').on(table.playerId),
  questIdx: index('idx_quest_audit_log_quest').on(table.questId),
  timestampIdx: index('idx_quest_audit_log_timestamp').on(table.timestamp),
  actionIdx: index('idx_quest_audit_log_action').on(table.action),
}));

Use Cases:

Fraud detection and investigation
Debugging quest progression bugs
Analytics for game design
Customer support inquiries

Activity & Moderation Tables

activity_log

Tracks player actions for admin panel:

export const activityLog = pgTable('activity_log', {
  id: serial('id').primaryKey(),
  playerId: text('playerId').notNull()
    .references(() => characters.id, { onDelete: 'cascade' }),
  eventType: text('eventType').notNull(),
  action: text('action').notNull(),
  entityType: text('entityType'),
  entityId: text('entityId'),
  details: jsonb('details').default({}),
  position: jsonb('position'),
  timestamp: bigint('timestamp', { mode: 'number' }).notNull(),
}, (table) => ({
  playerIdx: index('idx_activity_log_player').on(table.playerId),
  timestampIdx: index('idx_activity_log_timestamp').on(table.timestamp),
  eventTypeIdx: index('idx_activity_log_event_type').on(table.eventType),
}));

trades

Trade transaction history:

export const trades = pgTable('trades', {
  id: serial('id').primaryKey(),
  initiatorId: text('initiatorId')
    .references(() => characters.id, { onDelete: 'set null' }),
  receiverId: text('receiverId')
    .references(() => characters.id, { onDelete: 'set null' }),
  status: text('status').notNull(),  // "pending", "accepted", "declined", "completed"
  initiatorItems: jsonb('initiatorItems').default([]),
  receiverItems: jsonb('receiverItems').default([]),
  initiatorCoins: integer('initiatorCoins').default(0),
  receiverCoins: integer('receiverCoins').default(0),
  timestamp: bigint('timestamp', { mode: 'number' }).notNull(),
}, (table) => ({
  initiatorIdx: index('idx_trades_initiator').on(table.initiatorId),
  receiverIdx: index('idx_trades_receiver').on(table.receiverId),
  timestampIdx: index('idx_trades_timestamp').on(table.timestamp),
}));

user_bans

Moderation ban records:

export const userBans = pgTable('user_bans', {
  id: serial('id').primaryKey(),
  bannedUserId: text('bannedUserId').notNull()
    .references(() => users.id, { onDelete: 'cascade' }),
  bannedByUserId: text('bannedByUserId').notNull()
    .references(() => users.id, { onDelete: 'set null' }),
  reason: text('reason'),
  expiresAt: bigint('expiresAt', { mode: 'number' }),
  createdAt: bigint('createdAt', { mode: 'number' }).notNull(),
  active: integer('active').default(1).notNull(),
}, (table) => ({
  bannedUserIdx: index('idx_user_bans_banned_user').on(table.bannedUserId),
  activeIdx: index('idx_user_bans_active').on(table.active),
  activeBannedIdx: index('idx_user_bans_active_banned').on(table.active, table.bannedUserId),
}));

​Database Schema

​Overview

​Core Tables

​Users

​Characters

​Inventory

​Equipment

​Bank System

​Bank Storage

​Bank Tabs

​World Persistence

​World Chunks

​Player Sessions

​Quest System Tables

​Quest Progress

​Quest Audit Log

​Statistics Tables

​NPC Kills

​Quest System Tables

​quest_progress

​quest_audit_log

​Repository Pattern

​Data Types

​Timestamps

​Positions

​IDs

​Migrations

​Recent Migrations

​Quest System Tables

​quest_progress

​quest_audit_log

​Activity & Moderation Tables

​activity_log

​trades

​user_bans

​Related Documentation

Database Schema

Overview

Core Tables

Users

Characters

Inventory

Equipment

Bank System

Bank Storage

Bank Tabs

World Persistence

World Chunks

Player Sessions

Quest System Tables

Quest Progress

Quest Audit Log

Statistics Tables

NPC Kills

Quest System Tables

quest_progress

quest_audit_log

Repository Pattern

Data Types

Timestamps

Positions

IDs

Migrations

Recent Migrations

Quest System Tables

quest_progress

quest_audit_log

Activity & Moderation Tables

activity_log

trades

user_bans

Related Documentation