> ## Documentation Index
> Fetch the complete documentation index at: https://hyperscape-ai.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

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

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

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

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

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

## Drizzle Commands

Run from `packages/server/`:

### Push Schema

Apply schema changes directly (development):

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

### Generate Migrations

Create migration files for changes:

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

### Run Migrations

Apply pending migrations:

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

### Studio

Open Drizzle Studio to browse data:

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

## Configuration

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

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

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

```json theme={"theme":{"light":"github-light","dark":"css-variables"}}
{
  "entries": [
    {
      "idx": 18,
      "version": "7",
      "when": 1768728921620,
      "tag": "0018_add_agility_skill",
      "breakpoints": true
    }
  ]
}
```

<Warning>
  If you create a migration file but forget to add it to `_journal.json`, it will not run automatically.
</Warning>

<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":"css-variables"}}
    cd packages/server
    bunx drizzle-kit generate
    ```
  </Step>

  <Step title="Apply migration">
    ```bash theme={"theme":{"light":"github-light","dark":"css-variables"}}
    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":"css-variables"}}
rm packages/server/hyperscape.db
bun run dev
```

### Docker PostgreSQL Reset

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

## Backup and Restore

### PostgreSQL Backup

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

### PostgreSQL Restore

```bash theme={"theme":{"light":"github-light","dark":"css-variables"}}
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":"css-variables"}}
cd packages/server
bunx drizzle-kit push
```

### Connection Refused

Ensure PostgreSQL is running:

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

If not running:

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