RPG Vault: Database Migration Plan

Status: In Progress (Phases 1-5 Complete) Author: Claude Code Date: 2026-01-02 Reviewer: User

---

Progress Tracker

Phase	Status	Date	Notes
1. Schema Creation	✅ Complete	2026-01-02	supabase/migrations/010_vault_to_database.sql
2. Migration Script	✅ Complete	2026-01-02	scripts/migrate_vault_to_db.py
3. Seagate Data Migration	✅ Complete	2026-01-02	32 entities, 13 threads, 98 timeline events
4. DatabaseVault Class	✅ Complete	2026-01-02	lib/db_vault.py - full CRUD implementation
5. API Integration	✅ Complete	2026-01-02	Tools, context builder, dependencies updated
6. K8s Config	✅ Complete	2026-01-02	RPG_VAULT_BACKEND toggle added
7. Verification	⏳ Pending	-	End-to-end test with play session
8. Remove PVC	⏳ Pending	-	After verification

Migration Results (Seagate)

Entities:
  - pc:       2 (jake, pip)
  - npc:      8 (marlena, elara, gareth, jorah, etc.)
  - location: 11 (gilded-quill, the-salty-sigil, etc.)
  - item:     8 (whispering-shard, muffle-token, etc.)
  - faction:  3 (silent-circle, seagate-conclave, etc.)

PC State:
  - jake: HP 13/13, gold 15, location the-salty-sigil

Campaign State:
  - Day 5, morning, Session 5

Threads: 13 active
Timeline: 98 events
Entity Links: 1 (pip bonded_to jake)

---

Executive Summary

Migrate the RPG campaign vault from file-based storage (markdown on PVC) to database storage (PostgreSQL/Supabase). This eliminates sync issues between local files and the PVC, provides a single source of truth, and enables better querying and real-time updates.

Key design principle: Structure what you must query, update atomically, and reason about. Everything else stays flexible (markdown/JSON).

---

1. Problem Statement

Current Pain Points

1. Dual source of truth: Local files and PVC can diverge silently
2. State inconsistency: Sidebar shows "Gilded Quill" while DM narrates "Salty Sigil"
3. No atomic updates: File writes can partially fail
4. Location drift: Freeform strings cause "Salinmoor vs Seagate" confusion
5. Manual sync required: Must kubectl cp after local edits

Root Cause

Two independent copies of data with no automatic synchronization, plus insufficient structure on critical fields.

---

2. Design Principles

What to Structure (Query/Validate/Constrain)

• Entity identity: campaign_id, entity_type, id, name
• Status lifecycle: active, inactive, dead, missing, archived
• Location references: Always an entity ID, never freeform text
• Hot state: HP, gold, conditions, inventory (changes every turn)
• Time: Day number, time-of-day enum
• Relationships: Typed links between entities

What to Keep Flexible (LLM Creativity)

• Entity content (markdown prose)
• Secrets, backstory, personality
• Arbitrary metadata in meta JSONB
• Tags for ad-hoc categorization

Rule for LLM Tools

The model may invent new entities, but must produce: (entity_type, id, name) + optional aliases/tags. Everything else can be prose.

Creation gated behind explicit create_entity tool to prevent accidental canonization of hallucinations.

---

3. Canonical Schema

3.1 Enums

-- Entity types (start small, expand later)
CREATE TYPE rpg.entity_type AS ENUM (
  'pc',
  'npc',
  'location',
  'item',
  'faction'
);

-- Universal status (applies to all entity types)
CREATE TYPE rpg.entity_status AS ENUM (
  'active',      -- In play, can be interacted with
  'inactive',    -- Not currently in play (traveling, sleeping, etc.)
  'dead',        -- Deceased
  'missing',     -- Unknown whereabouts
  'archived'     -- Retired from canon
);

-- Time of day
CREATE TYPE rpg.time_of_day AS ENUM (
  'morning',
  'afternoon',
  'evening',
  'night'
);

-- Thread priority
CREATE TYPE rpg.thread_priority AS ENUM (
  'high',
  'medium',
  'low'
);

-- Thread status
CREATE TYPE rpg.thread_status AS ENUM (
  'active',
  'resolved',
  'abandoned'
);

-- Relationship types (extensible)
CREATE TYPE rpg.link_type AS ENUM (
  'bonded_to',      -- familiar/companion bond
  'knows',          -- has met
  'ally_of',
  'enemy_of',
  'affiliated_with', -- faction membership
  'owns',           -- item ownership
  'employs',
  'related_to'      -- family/blood relation
);

-- Item kinds (light categorization)
CREATE TYPE rpg.item_kind AS ENUM (
  'weapon',
  'armor',
  'potion',
  'scroll',
  'tool',
  'treasure',
  'key_item',
  'magical',
  'mundane'
);

-- Item rarity
CREATE TYPE rpg.item_rarity AS ENUM (
  'common',
  'uncommon',
  'rare',
  'very_rare',
  'legendary',
  'artifact'
);

3.2 Core Tables

-- Campaigns (already exists, minor additions)
ALTER TABLE rpg.campaigns ADD COLUMN IF NOT EXISTS settings JSONB DEFAULT '{}';

-- Entities: Universal storage for all canon content
CREATE TABLE rpg.entities (
  -- Identity (structured, queryable)
  campaign_id UUID NOT NULL REFERENCES rpg.campaigns(id) ON DELETE CASCADE,
  entity_type rpg.entity_type NOT NULL,
  id TEXT NOT NULL,  -- slug: 'marlena', 'gilded-quill'

  -- Universal fields (structured)
  name TEXT NOT NULL,  -- display name: 'Marlena', 'The Gilded Quill'
  status rpg.entity_status NOT NULL DEFAULT 'active',
  location_id TEXT,  -- FK to another entity (type='location'), NULL if N/A

  -- Discovery/resolution
  aliases TEXT[] DEFAULT '{}',  -- ['component dealer', 'gardener']
  tags TEXT[] DEFAULT '{}',     -- ['merchant', 'quest_giver']

  -- Flexible content
  content TEXT,  -- markdown body (bio, description, secrets)
  meta JSONB DEFAULT '{}',  -- catch-all for type-specific data

  -- Audit
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),

  PRIMARY KEY (campaign_id, entity_type, id)
);

-- Indexes for common queries
CREATE INDEX idx_entities_location ON rpg.entities(campaign_id, location_id) WHERE location_id IS NOT NULL;
CREATE INDEX idx_entities_status ON rpg.entities(campaign_id, entity_type, status);
CREATE INDEX idx_entities_aliases ON rpg.entities USING GIN (aliases);
CREATE INDEX idx_entities_tags ON rpg.entities USING GIN (tags);
CREATE INDEX idx_entities_meta ON rpg.entities USING GIN (meta);

-- Trigger to auto-update updated_at
CREATE OR REPLACE FUNCTION rpg.update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER entities_updated_at
  BEFORE UPDATE ON rpg.entities
  FOR EACH ROW EXECUTE FUNCTION rpg.update_timestamp();

3.3 Hot State Tables (Separated from Lore)

-- PC mutable state (changes every turn)
-- Separated from entities to avoid JSONB merge issues and enable constraints
CREATE TABLE rpg.pc_state (
  campaign_id UUID NOT NULL,
  pc_id TEXT NOT NULL,

  -- Combat state
  hp_current INT NOT NULL DEFAULT 0,
  hp_max INT NOT NULL DEFAULT 0,
  temp_hp INT NOT NULL DEFAULT 0,

  -- Resources
  gold INT NOT NULL DEFAULT 0 CHECK (gold >= 0),
  silver INT NOT NULL DEFAULT 0 CHECK (silver >= 0),
  copper INT NOT NULL DEFAULT 0 CHECK (copper >= 0),

  -- Conditions (structured array)
  conditions TEXT[] DEFAULT '{}',  -- ['poisoned', 'exhaustion:1']

  -- Inventory (JSONB for flexibility, but dedicated table)
  inventory JSONB DEFAULT '{"equipped": [], "carried": [], "stored": []}',

  -- Location (references entities)
  location_id TEXT,

  -- Class-specific resources (flexible)
  class_resources JSONB DEFAULT '{}',  -- spell_slots, ki_points, etc.

  -- Audit
  updated_at TIMESTAMPTZ DEFAULT now(),

  PRIMARY KEY (campaign_id, pc_id),
  FOREIGN KEY (campaign_id, 'pc'::rpg.entity_type, pc_id)
    REFERENCES rpg.entities(campaign_id, entity_type, id) ON DELETE CASCADE,

  CONSTRAINT hp_valid CHECK (hp_current <= hp_max + temp_hp)
);

CREATE TRIGGER pc_state_updated_at
  BEFORE UPDATE ON rpg.pc_state
  FOR EACH ROW EXECUTE FUNCTION rpg.update_timestamp();

-- Campaign temporal state
CREATE TABLE rpg.campaign_state (
  campaign_id UUID PRIMARY KEY REFERENCES rpg.campaigns(id) ON DELETE CASCADE,

  current_day INT NOT NULL DEFAULT 1,
  current_time rpg.time_of_day DEFAULT 'morning',
  session_number INT NOT NULL DEFAULT 1,

  -- Active scene context
  active_location_id TEXT,  -- where the "camera" is

  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE TRIGGER campaign_state_updated_at
  BEFORE UPDATE ON rpg.campaign_state
  FOR EACH ROW EXECUTE FUNCTION rpg.update_timestamp();

3.4 Relationship Links

-- Entity relationships (flexible graph)
CREATE TABLE rpg.entity_links (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  campaign_id UUID NOT NULL REFERENCES rpg.campaigns(id) ON DELETE CASCADE,

  -- Source entity
  from_type rpg.entity_type NOT NULL,
  from_id TEXT NOT NULL,

  -- Relationship
  link_type rpg.link_type NOT NULL,

  -- Target entity
  to_type rpg.entity_type NOT NULL,
  to_id TEXT NOT NULL,

  -- Optional metadata
  data JSONB DEFAULT '{}',  -- strength, notes, etc.

  created_at TIMESTAMPTZ DEFAULT now(),

  -- Ensure source and target exist
  FOREIGN KEY (campaign_id, from_type, from_id)
    REFERENCES rpg.entities(campaign_id, entity_type, id) ON DELETE CASCADE,
  FOREIGN KEY (campaign_id, to_type, to_id)
    REFERENCES rpg.entities(campaign_id, entity_type, id) ON DELETE CASCADE,

  -- Prevent duplicate links
  UNIQUE (campaign_id, from_type, from_id, link_type, to_type, to_id)
);

CREATE INDEX idx_entity_links_from ON rpg.entity_links(campaign_id, from_type, from_id);
CREATE INDEX idx_entity_links_to ON rpg.entity_links(campaign_id, to_type, to_id);

3.5 Narrative Tables

-- Plot threads
CREATE TABLE rpg.threads (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  campaign_id UUID NOT NULL REFERENCES rpg.campaigns(id) ON DELETE CASCADE,

  name TEXT NOT NULL,
  priority rpg.thread_priority NOT NULL DEFAULT 'medium',
  status rpg.thread_status NOT NULL DEFAULT 'active',

  description TEXT,
  next_step TEXT,
  resolution TEXT,

  -- Links to related entities
  related_entities JSONB DEFAULT '[]',  -- [{type: 'npc', id: 'marlena'}]

  created_at TIMESTAMPTZ DEFAULT now(),
  resolved_at TIMESTAMPTZ,

  UNIQUE (campaign_id, name)
);

CREATE INDEX idx_threads_campaign_status ON rpg.threads(campaign_id, status, priority);

-- Timeline events
CREATE TABLE rpg.timeline_events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  campaign_id UUID NOT NULL REFERENCES rpg.campaigns(id) ON DELETE CASCADE,

  day INT NOT NULL,
  time_of_day rpg.time_of_day,
  sequence INT,  -- order within day+time

  event_text TEXT NOT NULL,

  -- Optional links
  session_id UUID REFERENCES rpg.sessions(id) ON DELETE SET NULL,
  location_id TEXT,  -- where it happened

  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_timeline_campaign_day ON rpg.timeline_events(campaign_id, day, sequence);

3.6 Event Sourcing (Vault Events)

-- Existing table, updated for DB-native operations
-- This is the audit/undo layer for all DB writes

ALTER TABLE rpg.vault_events
  ADD COLUMN IF NOT EXISTS target_table TEXT,
  ADD COLUMN IF NOT EXISTS target_pk JSONB,  -- {campaign_id, entity_type, id} or similar
  ADD COLUMN IF NOT EXISTS old_values JSONB,
  ADD COLUMN IF NOT EXISTS new_values JSONB;

-- Event types for DB operations
-- 'entity_created', 'entity_updated', 'entity_deleted'
-- 'pc_state_updated', 'thread_created', 'thread_resolved', etc.

---

4. Meta JSONB Schemas by Entity Type

The meta column holds type-specific structured data that doesn't warrant its own column.

NPC Meta

{
  "disposition": "intimate-ally",  // ally, friendly, neutral, hostile
  "role": "Curio shop owner",
  "occupation": "merchant",
  "species": "human",
  "age": "middle-aged",
  "voice": "low drawl with coastal accent",
  "secrets": ["Knows about the Silent Circle"],
  "triggers": ["Marlena", "component dealer"]  // DEPRECATED: use aliases[]
}

PC Meta (Static Lore - State in pc_state)

{
  "class": "Wizard",
  "subclass": "Abjuration",
  "level": 3,
  "player": "Jake",
  "background": "Sage",
  "race": "Human",
  "alignment": "Neutral Good",
  "spell_list": ["shield", "magic missile", "detect magic"],
  "proficiencies": ["arcana", "history", "investigation"]
}

Location Meta

{
  "location_type": "shop",  // tavern, shop, residence, dungeon, outdoor
  "district": "dredgeworks",
  "owner_id": "marlena",  // npc entity id
  "ambient": "smells of incense and brine",
  "features": ["counter", "shelves of components", "back room"]
}

Item Meta

{
  "kind": "weapon",
  "rarity": "rare",
  "attunement": true,
  "damage": "1d6 piercing",
  "properties": ["finesse", "light"],
  "charges": 3,
  "max_charges": 3
}

Faction Meta

{
  "faction_type": "criminal",  // guild, religious, political, criminal
  "influence": "high",
  "headquarters_id": "silent-circle-salon",
  "leader_id": "unknown",
  "goals": ["Control arcane trade", "Eliminate competition"]
}

---

5. Authoring Workflow

Primary: Export/Import Scripts (Low Effort)

# Export DB vault to markdown files for local editing
scripts/export_vault_from_db.py --campaign seagate --output ./vault-export/

# Edit locally with VS Code, git versioning, etc.

# Re-import to DB (validates, updates)
scripts/import_vault_to_db.py --campaign seagate --input ./vault-export/

Export format (round-trips cleanly):

---
entity_type: npc
id: marlena
name: Marlena
status: active
location_id: the-salty-sigil
aliases: [component dealer, the gardener]
tags: [merchant, quest_giver]
meta:
  disposition: intimate-ally
  role: Curio shop owner
---

# Marlena

## Role
Proprietor of The Salty Sigil curio shop...

## Appearance
- Tall and thin, willowy movements
...

Secondary: Supabase Studio

For quick edits and debugging. Not recommended for prose authoring.

Future: rpg-web Vault Editor

Optional enhancement - markdown editor with preview, entity linking, validation.

---

6. Tool Interface Contracts

Tools produce structured patches, not prose. Server validates and persists.

update_pc_state

def update_pc_state(
    pc_id: str,
    hp_current: int | None = None,
    hp_delta: int | None = None,  # alternative: relative change
    location_id: str | None = None,
    gold_delta: int | None = None,
    add_conditions: list[str] | None = None,
    remove_conditions: list[str] | None = None,
    add_items: dict[str, list[str]] | None = None,
    remove_items: dict[str, list[str]] | None = None,
) -> PCState:
    """
    Atomic update to PC state.
    Server validates constraints (hp <= max, gold >= 0, location exists).
    Creates vault_event for undo capability.
    """

create_entity

def create_entity(
    entity_type: EntityType,
    id: str,  # slug, validated
    name: str,
    status: EntityStatus = 'active',
    location_id: str | None = None,
    aliases: list[str] = [],
    tags: list[str] = [],
    content: str | None = None,
    meta: dict = {},
) -> Entity:
    """
    Create new canon entity.
    Server generates id if not provided, validates uniqueness.
    """

resolve_name

def resolve_name(
    query: str,
    entity_type: EntityType | None = None,
) -> list[Entity]:
    """
    Resolve a mention to canonical entities.
    Searches: id, name, aliases (fuzzy).
    Returns ranked matches.
    """

---

7. Migration Plan

Phase 1: Schema Creation (1 hour)

• Create migration file with all tables, enums, indexes
• Apply to Supabase sandbox
• Verify with test inserts

Phase 2: Migration Scripts (3-4 hours)

• scripts/migrate_vault_to_db.py
• Parse existing markdown files
• Map frontmatter → columns + meta
• Insert into entities, pc_state, threads, timeline_events
• Capture raw_markdown in migration_artifacts for reference
• Handle edge cases (timeline parsing, thread parsing)

Phase 3: API Dual-Read Refactor (3-4 hours)

• Update lib/vault.py to read from DB
• Keep file fallback for transition period
• Add /health/vault endpoint to verify entity counts
• Single-write to DB only

Phase 4: Tool Updates (2 hours)

• Update update_pc → update_pc_state (uses pc_state table)
• Update get_entity to query DB
• Add create_entity tool
• Add resolve_name tool

Phase 5: Remove PVC (1 hour)

• Remove vault volume from deployment
• Archive files to git (read-only reference)
• Update deploy scripts

Phase 6: Verification (2 hours)

• Test all tools end-to-end
• Verify context injection
• Verify sidebar/banner display
• Run test play session
• Verify event sourcing/undo works

Total: ~12-14 hours

---

8. Cutover Strategy (Safe Rollout)

Since project is young, simplified cutover:

1. Create schema in Supabase (additive, no risk)
2. Run migration to populate DB from files
3. Deploy new API with DB reads
4. Verify with test session
5. Remove PVC once confident

Rollback: Files remain in git. Can re-deploy old code with PVC mount.

---

9. Success Criteria

Criteria	Measurement
No sync issues	Sidebar always matches DM narrative
State persists	HP/location changes survive pod restart
Location references work	No freeform location strings, always IDs
Atomic updates	PC state changes are transactional
Query performance	Entity lookups < 50ms
Authoring works	Can round-trip export/import
Event sourcing	Can replay/undo state changes

---

10. Open Questions (Resolved)

Question	Resolution
Audit history	Keep vault_events for tool-driven mutations. Add entity_versions later if needed for lore editing history.
RAG integration	Re-index from DB. Use entities.content + updated_at for change detection.
Multi-campaign	Schema supports it. Enforce campaign_id param on every Vault method.
Editor UI	Start with export/import scripts. Supabase Studio for emergencies.
Hot state location	Dedicated pc_state table, not JSONB in entities.

---

Appendix A: File → Table Mapping

Current File	New Table	Column Mapping
canon/npcs/*.md	rpg.entities	type='npc', frontmatter→meta, body→content
canon/locations/*.md	rpg.entities	type='location'
canon/items/*.md	rpg.entities	type='item'
canon/pcs/*.md	rpg.entities + rpg.pc_state	Static→entities, mutable→pc_state
canon/factions/*.md	rpg.entities	type='faction'
canon/temporal-index.json	rpg.campaign_state	Direct mapping
canon/timeline.md	rpg.timeline_events	Parse to rows
canon/open-threads.md	rpg.threads	Parse to rows

Appendix B: Example Queries

-- Find all NPCs at a location
SELECT id, name, meta->>'disposition' as disposition
FROM rpg.entities
WHERE campaign_id = $1
  AND entity_type = 'npc'
  AND location_id = 'the-salty-sigil'
  AND status = 'active';

-- Get PC with current state
SELECT e.*, ps.*
FROM rpg.entities e
JOIN rpg.pc_state ps ON ps.campaign_id = e.campaign_id AND ps.pc_id = e.id
WHERE e.campaign_id = $1 AND e.entity_type = 'pc' AND e.id = $2;

-- Get active high-priority threads
SELECT name, description, next_step
FROM rpg.threads
WHERE campaign_id = $1 AND status = 'active' AND priority = 'high';

-- Find companions bonded to a PC
SELECT e.*
FROM rpg.entities e
JOIN rpg.entity_links l ON l.campaign_id = e.campaign_id
  AND l.to_type = e.entity_type AND l.to_id = e.id
WHERE l.campaign_id = $1
  AND l.from_type = 'pc' AND l.from_id = $2
  AND l.link_type = 'bonded_to';

-- Resolve a name mention
SELECT id, name, entity_type,
  CASE
    WHEN id = $2 THEN 100
    WHEN name ILIKE $2 THEN 90
    WHEN $2 = ANY(aliases) THEN 80
    ELSE 0
  END as score
FROM rpg.entities
WHERE campaign_id = $1
  AND (id = $2 OR name ILIKE $2 OR $2 = ANY(aliases))
ORDER BY score DESC
LIMIT 5;