> ## Documentation Index
> Fetch the complete documentation index at: https://hyperscape-ai-mintlify-docs-update.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Database Management

> Drizzle ORM, migrations, and schema management

## Overview

Hyperscape uses [Drizzle ORM](https://orm.drizzle.team) for database access:

* **Development**: SQLite (zero config)
* **Production**: PostgreSQL with pgvector extension

<Info>
  **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).
</Info>

## 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:

```sql theme={"theme":{"light":"github-light","dark":"tokyo-night"}}
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"]'`)

<Info>
  Prayer points are stored as integers in the database but tracked with fractional precision in-memory for accurate drain calculations.
</Info>

## 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):**

```bash theme={"theme":{"light":"github-light","dark":"tokyo-night"}}
# 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:

```typescript theme={"theme":{"light":"github-light","dark":"tokyo-night"}}
// 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:

```typescript theme={"theme":{"light":"github-light","dark":"tokyo-night"}}
// 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.

<Warning>
  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.
</Warning>

## Drizzle Commands

Run from `packages/server/`:

### Push Schema

Apply schema changes directly (development):

```bash theme={"theme":{"light":"github-light","dark":"tokyo-night"}}
bunx drizzle-kit push
```

### Generate Migrations

Create migration files for changes:

```bash theme={"theme":{"light":"github-light","dark":"tokyo-night"}}
bunx drizzle-kit generate
```

### Run Migrations

Apply pending migrations:

```bash theme={"theme":{"light":"github-light","dark":"tokyo-night"}}
bunx drizzle-kit migrate
```

### Studio

Open Drizzle Studio to browse data:

```bash theme={"theme":{"light":"github-light","dark":"tokyo-night"}}
bunx drizzle-kit studio
```

## Configuration

`packages/server/drizzle.config.ts`:

```typescript theme={"theme":{"light":"github-light","dark":"tokyo-night"}}
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:

```bash theme={"theme":{"light":"github-light","dark":"tokyo-night"}}
DATABASE_URL=postgresql://user:pass@host:5432/hyperscape
```

<Warning>
  **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).
</Warning>

### Connection Pooling (Supabase)

When using Supabase with the Supavisor connection pooler, prepared statements must be disabled (fixed in commits 8aaaf28, f7ab9f7):

```typescript theme={"theme":{"light":"github-light","dark":"tokyo-night"}}
// 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

<Warning>
  If you see `XX000` errors, check if your connection string uses a pooler and set `prepare: false`.
</Warning>

## 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:

```bash theme={"theme":{"light":"github-light","dark":"tokyo-night"}}
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

<Warning>
  Only use `SKIP_MIGRATIONS=true` in CI/testing environments. Production should always run migrations normally.
</Warning>

## 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

```sql theme={"theme":{"light":"github-light","dark":"tokyo-night"}}
-- 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:**

```sql theme={"theme":{"light":"github-light","dark":"tokyo-night"}}
-- 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

<Info>
  All migrations should use `IF NOT EXISTS` guards to be idempotent and safe to re-run.
</Info>

<Info>
  The `activePrayers` column stores a JSON array of prayer IDs. Format: `'["thick_skin", "burst_of_strength"]'`. Empty array when no prayers are active: `'[]'`.
</Info>

## Schema Changes

<Steps>
  <Step title="Edit schema">
    Modify `packages/server/src/database/schema.ts`
  </Step>

  <Step title="Generate migration">
    ```bash theme={"theme":{"light":"github-light","dark":"tokyo-night"}}
    cd packages/server
    bunx drizzle-kit generate
    ```
  </Step>

  <Step title="Apply migration">
    ```bash theme={"theme":{"light":"github-light","dark":"tokyo-night"}}
    bunx drizzle-kit migrate
    ```
  </Step>

  <Step title="Verify">
    Check that migration file was created in `src/database/migrations/`
  </Step>
</Steps>

## Reset Database

### Development Reset

Delete the SQLite file:

```bash theme={"theme":{"light":"github-light","dark":"tokyo-night"}}
rm packages/server/hyperscape.db
bun run dev
```

### Docker PostgreSQL Reset

```bash theme={"theme":{"light":"github-light","dark":"tokyo-night"}}
docker stop hyperscape-postgres
docker rm hyperscape-postgres
docker volume rm hyperscape-postgres-data
docker volume rm server_postgres-data
bun run dev
```

<Warning>
  This permanently deletes all player data.
</Warning>

<Info>
  **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).
</Info>

## Backup and Restore

### PostgreSQL Backup

```bash theme={"theme":{"light":"github-light","dark":"tokyo-night"}}
docker exec hyperscape-postgres pg_dump -U postgres hyperscape > backup.sql
```

### PostgreSQL Restore

```bash theme={"theme":{"light":"github-light","dark":"tokyo-night"}}
docker exec -i hyperscape-postgres psql -U postgres hyperscape < backup.sql
```

## Troubleshooting

### Schema Out of Sync

If you see schema errors after pulling updates:

```bash theme={"theme":{"light":"github-light","dark":"tokyo-night"}}
cd packages/server
bunx drizzle-kit push
```

### Connection Refused

Ensure PostgreSQL is running:

```bash theme={"theme":{"light":"github-light","dark":"tokyo-night"}}
docker ps | grep postgres
```

If not running:

```bash theme={"theme":{"light":"github-light","dark":"tokyo-night"}}
bun run dev   # Auto-starts PostgreSQL
```