BreakEscape/planning_notes/rails-engine-migration-json/03_DATABASE_SCHEMA.md

BreakEscape - Database Schema Reference

Overview

3 tables using JSONB for flexible storage

---

Tables

1. break_escape_scenarios

Stores scenario definitions with complete game data.

Column	Type	Null	Default	Notes
id	bigint	NO	AUTO	Primary key
name	string	NO	-	Unique identifier (e.g., 'ceo_exfil')
display_name	string	NO	-	Human-readable name
description	text	YES	-	Scenario brief
scenario_data	jsonb	NO	-	Complete scenario with solutions
published	boolean	NO	false	Visible to players
difficulty_level	integer	NO	1	1-5 scale
created_at	timestamp	NO	NOW()	-
updated_at	timestamp	NO	NOW()	-

Indexes:

• name (unique)
• published
• scenario_data (gin)

scenario_data structure:

{
  "startRoom": "room_reception",
  "scenarioName": "CEO Exfiltration",
  "scenarioBrief": "Break into the CEO's office...",
  "rooms": {
    "room_reception": {
      "type": "reception",
      "connections": {"north": "room_office"},
      "locked": false,
      "objects": [...]
    },
    "room_office": {
      "type": "office",
      "connections": {"south": "room_reception"},
      "locked": true,
      "lockType": "password",
      "requires": "admin123",  // Server only!
      "objects": [...]
    }
  },
  "npcs": [
    {"id": "guard", "displayName": "Security Guard", ...}
  ]
}

---

2. break_escape_npc_scripts

Stores Ink dialogue scripts per NPC per scenario.

Column	Type	Null	Default	Notes
id	bigint	NO	AUTO	Primary key
scenario_id	bigint	NO	-	Foreign key → scenarios
npc_id	string	NO	-	NPC identifier
ink_source	text	YES	-	Original .ink file (optional)
ink_compiled	text	NO	-	Compiled .ink.json
created_at	timestamp	NO	NOW()	-
updated_at	timestamp	NO	NOW()	-

Indexes:

• scenario_id
• [scenario_id, npc_id] (unique)

Foreign Keys:

• scenario_id → break_escape_scenarios(id)

---

3. break_escape_game_instances

Stores player game state (one JSONB column!).

Column	Type	Null	Default	Notes
id	bigint	NO	AUTO	Primary key
player_type	string	NO	-	Polymorphic (User/DemoUser)
player_id	bigint	NO	-	Polymorphic
scenario_id	bigint	NO	-	Foreign key → scenarios
player_state	jsonb	NO	{...}	All game state here!
status	string	NO	'in_progress'	in_progress, completed, abandoned
started_at	timestamp	YES	-	When game started
completed_at	timestamp	YES	-	When game finished
score	integer	NO	0	Final score
health	integer	NO	100	Current health
created_at	timestamp	NO	NOW()	-
updated_at	timestamp	NO	NOW()	-

Indexes:

• [player_type, player_id, scenario_id] (unique)
• player_state (gin)
• status

Foreign Keys:

• scenario_id → break_escape_scenarios(id)

player_state structure:

{
  "currentRoom": "room_office",
  "position": {"x": 150, "y": 200},
  "unlockedRooms": ["room_reception", "room_office"],
  "unlockedObjects": ["desk_drawer_123"],
  "inventory": [
    {
      "type": "key",
      "name": "Office Key",
      "key_id": "office_key_1",
      "takeable": true
    }
  ],
  "encounteredNPCs": ["security_guard"],
  "globalVariables": {
    "alarm_triggered": false,
    "player_favor": 5
  }
}

---

4. break_escape_demo_users (Standalone Mode Only)

Simple user model for standalone/testing.

Column	Type	Null	Default	Notes
id	bigint	NO	AUTO	Primary key
handle	string	NO	-	Username
role	string	NO	'user'	admin, pro, user
created_at	timestamp	NO	NOW()	-
updated_at	timestamp	NO	NOW()	-

Indexes:

• handle (unique)

---

Relationships

Scenario (1) ──→ (∞) GameInstance
Scenario (1) ──→ (∞) NpcScript

GameInstance (∞) ←── (1) Player [Polymorphic]
  - User (Hacktivity)
  - DemoUser (Standalone)

---

Migration Commands

# Generate migrations
rails generate migration CreateBreakEscapeScenarios
rails generate migration CreateBreakEscapeNpcScripts
rails generate migration CreateBreakEscapeGameInstances
rails generate migration CreateBreakEscapeDemoUsers

# Run migrations
rails db:migrate

# Import scenarios
rails db:seed

---

Querying Examples

# Find player's active games
GameInstance.where(player: current_user, status: 'in_progress')

# Get unlocked rooms for a game
game.player_state['unlockedRooms']

# Check if room is unlocked
game.room_unlocked?('room_office')

# Unlock a room
game.unlock_room!('room_office')

# Add inventory item
game.add_inventory_item!({'type' => 'key', 'name' => 'Office Key'})

# Query scenarios
Scenario.published.where("scenario_data->>'startRoom' = ?", 'room_reception')

# Complex JSONB queries
GameInstance.where("player_state @> ?", {unlockedRooms: ['room_ceo']}.to_json)

---

Benefits of JSONB Approach

1. Flexible Schema - Add new fields without migrations
2. Fast Queries - GIN indexes on JSONB
3. Matches Game Data - Already in JSON format
4. Simple - One table vs many joins
5. Atomic Updates - Update entire state in one transaction

---

Performance Considerations

• GIN indexes on all JSONB columns
• Unique index on [player, scenario] prevents duplicates
• player_state updates are atomic (PostgreSQL JSONB)
• Scenarios cached in memory after first load