# Hathor Database Schema

Hathor is the Worldbuilding and Simulation domain. Its database stores worlds
with genre/scope/technology classification, entities (characters, factions,
locations, events), entity relationships, branching story graphs, dialogue
trees, quest systems, simulation runs, and a Git-like versioning system with
branches and merge requests.

**Database:** PostgreSQL **ORM:** Prisma **Schema Location:**
`libs/hathor/database/prisma/schema.prisma` **Environment Variable:**
`HATHOR_DATABASE_URL` **Generated Client:**
`libs/hathor/database/src/generated/client`

## ER Diagram

```
+---------------------+
|       World         |
|---------------------|
| id (PK)             |
| name (UQ w/userId)  |
| description?        |
| status              |
| genre               |
| scope               |
| technologyLevel     |
| magicLevel          |
| userId              |
| organizationId?     |
| settings (JSON)     |
| stats (JSON)        |
| tags[]              |
+--+--+--+--+--+--+--+
   |  |  |  |  |  |
   |  |  |  |  |  +----------1-----------+
   |  |  |  |  +--------1--------+       |
   |  |  |  +-----1-----+       |       |
   |  |  +---1---+       |       |       |
   |  +-1-+       |       |       |       |
   |1     |       |       |       |       |
   |*     |*      |*      |*      |*      |*
+--+---+ ++-+--+ ++----+ ++----+ ++----+ ++--------+
|Branch| |WorldV| |Entity| |Quest| |Story| |Dialogue |
|------| |ersion| |------| |-----| |Graph| |Tree     |
|id    | |------| |id    | |id   | |-----| |---------|
|name  | |id    | |name  | |name | |id   | |id       |
|world | |ver   | |descr?| |descr| |name | |name     |
|Id(FK)| |world | |type  | |world| |world| |worldId  |
|isMain| |Id(FK)| |world | |Id   | |Id   | |(FK)     |
+------+ |branch| |Id(FK)| |categ| |(FK) | |speakers |
         |Id?   | |props | |prior| |ver  | |location?|
         |parent| |tags[]| |stat | |activ| +--+--+---+
         |Ver?  | |alias | +--+--+ +--+--+    |  |
         |change| +--+---+    |       |       |  |
         |snap  |    |        |1      |1      |1 |1
         |isCur | +--+---+   |       |       |  |
         +--+---+ |Entity |   |*      |*      |* |*
            |     |Relat. |   |       |       |  |
            |1    |-------|  ++----+ ++----+ +-++ +--------+
            |     |id     |  |Quest| |Story| |Dia | |Dialogue|
            |*    |srcId  |  |Obj. | |Node | |log | |Session |
         +--+---+ |tgtId  |  |-----| |-----| |Node| |--------|
         |Merge | |type   |  |quest| |graph| |----| |treeId  |
         |Req.  | |props  |  |Id   | |Id   | |tree| |current |
         |------| |strngth|  |type | |type | |Id  | |Node?   |
         |id    | |bidir  |  |descr| |name | |type| |history |
         |title | +-------+  |tgt  | |cont?| |cont| |context |
         |srcVer|             |prog | |posX | |emot| |isComp  |
         |tgtVer|             +-----+ |posY | |isS | +--------+
         |status|                     |cond | |isE |
         |confl.|                     |acts | |choi|
         +------+                     +--+--+ +----+
                                         |
                              +----------+----------+
                              |1                    |1
                              |                     |
                              |*                    |*
                           +--+------+         +----+---+
                           |StoryArc |         |QuestRew|
                           |---------|         |--------|
                           |id       |         |id      |
                           |graphId  |         |questId |
                           |sourceId |         |type    |
                           |targetId |         |amount? |
                           |label?   |         |itemId? |
                           |condtns  |         +--------+
                           |priority |
                           +---------+

+-----------------+       +--------------------+
|SimulationRun    |       | ValidationResult   |
|-----------------|       |--------------------|
|id (PK)          |       | id (PK)            |
|worldId (FK)     |       | worldId            |
|type             |       | entityId?          |
|status           |       | validationType     |
|priority         |       | isValid            |
|input (JSON)     |       | issues (JSON)      |
|output (JSON)?   |       | suggestions (JSON) |
|progress         |       +--------------------+
|currentTick      |
|totalTicks?      |       +--------------------+
|workerId?        |       |    AuditLog        |
|startVersionId?  |       |--------------------|
|endVersionId?    |       | id (PK)            |
+--+---------+----+       | entityType         |
   |         |            | entityId           |
   |1        |1           | action             |
   |         |            | userId?            |
   |*        |*           | changes (JSON)     |
+--+------+ ++---------+  | previousState?    |
|Sim      | |Sim       |  | createdAt         |
|Event    | |Snapshot   |  +--------------------+
|---------| |----------|
|id       | |id        |
|simId    | |simId     |
|type     | |tick      |
|tick     | |state     |
|data     | +----------+
|affected |
+---------+
```

## Models

### World

Top-level container for a simulation universe. Classified by genre, scope,
technology level, and magic level.

| Field             | Type              | Default    | Description          |
| ----------------- | ----------------- | ---------- | -------------------- |
| `id`              | `String` (cuid)   | auto       | Primary key          |
| `name`            | `String`          | -          | World name           |
| `description`     | `String?`         | -          | Description          |
| `status`          | `WorldStatus`     | `DRAFT`    | Lifecycle status     |
| `genre`           | `WorldGenre`      | `FANTASY`  | Genre classification |
| `scope`           | `WorldScope`      | `MEDIUM`   | World size/scope     |
| `technologyLevel` | `TechnologyLevel` | `MEDIEVAL` | Technology era       |
| `magicLevel`      | `MagicLevel`      | `MEDIUM`   | Magic prevalence     |
| `userId`          | `String`          | -          | Owner user ID        |
| `organizationId`  | `String?`         | -          | Organization         |
| `settings`        | `Json`            | `{}`       | World settings       |
| `stats`           | `Json`            | `{}`       | Aggregate statistics |
| `tags`            | `String[]`        | `[]`       | Tags                 |

**Unique:** `[userId, name]` **Relations:** `versions` (WorldVersion[]),
`branches` (Branch[]), `entities` (Entity[]), `quests` (Quest[]), `storyGraphs`
(StoryGraph[]), `dialogueTrees` (DialogueTree[]), `simulations`
(SimulationRun[]) **Table:** `world`

### WorldVersion

Immutable snapshot of world state. Supports Git-like parent-child version chains
with branch tracking.

| Field             | Type      | Default | Description               |
| ----------------- | --------- | ------- | ------------------------- |
| `id`              | `String`  | auto    | Primary key               |
| `version`         | `Int`     | `1`     | Version number            |
| `name`            | `String?` | -       | Version name              |
| `description`     | `String?` | -       | Description               |
| `worldId`         | `String`  | -       | FK to World               |
| `branchId`        | `String?` | -       | FK to Branch              |
| `parentVersionId` | `String?` | -       | FK to parent WorldVersion |
| `changes`         | `Json`    | `[]`    | List of changes           |
| `snapshot`        | `Json`    | `{}`    | Full state snapshot       |
| `createdBy`       | `String?` | -       | Creator user ID           |
| `isCurrent`       | `Boolean` | `false` | Whether this is the head  |

**Unique:** `[worldId, version]`, `[worldId, branchId, version]` **Relations:**
`world`, `branch`, `parentVersion`, `childVersions`, `mergeRequestsSource`,
`mergeRequestsTarget` **Table:** `world_version`

### Branch

Alternate timeline/version track within a world. One branch per world is marked
as the main branch.

| Field                  | Type      | Default | Description                |
| ---------------------- | --------- | ------- | -------------------------- |
| `id`                   | `String`  | auto    | Primary key                |
| `name`                 | `String`  | -       | Branch name                |
| `description`          | `String?` | -       | Description                |
| `worldId`              | `String`  | -       | FK to World                |
| `branchPointVersionId` | `String?` | -       | Version where branch split |
| `isMain`               | `Boolean` | `false` | Whether main branch        |

**Unique:** `[worldId, name]` **Table:** `branch`

### MergeRequest

Request to merge changes from one version into another, analogous to a Git merge
request. Tracks conflicts and resolution.

| Field             | Type                 | Default | Description               |
| ----------------- | -------------------- | ------- | ------------------------- |
| `id`              | `String`             | auto    | Primary key               |
| `title`           | `String`             | -       | Merge request title       |
| `description`     | `String?`            | -       | Description               |
| `sourceVersionId` | `String`             | -       | FK to source WorldVersion |
| `targetVersionId` | `String`             | -       | FK to target WorldVersion |
| `status`          | `MergeRequestStatus` | `OPEN`  | Status                    |
| `conflicts`       | `Json`               | `[]`    | Conflict details          |
| `resolution`      | `Json?`              | -       | Resolution details        |
| `createdBy`       | `String?`            | -       | Creator user ID           |
| `reviewedBy`      | `String?`            | -       | Reviewer user ID          |
| `mergedAt`        | `DateTime?`          | -       | Merge timestamp           |

**Table:** `merge_request`

### Entity

Base entity for all objects in a world: characters, factions, locations, events,
items, cultures, religions. Type-specific properties are stored in the
`properties` JSON field.

| Field          | Type         | Default | Description              |
| -------------- | ------------ | ------- | ------------------------ |
| `id`           | `String`     | auto    | Primary key              |
| `name`         | `String`     | -       | Entity name              |
| `description`  | `String?`    | -       | Description              |
| `type`         | `EntityType` | -       | Entity type              |
| `worldId`      | `String`     | -       | FK to World              |
| `versionId`    | `String?`    | -       | Version reference        |
| `properties`   | `Json`       | `{}`    | Type-specific properties |
| `tags`         | `String[]`   | `[]`    | Tags                     |
| `aliases`      | `String[]`   | `[]`    | Alternate names          |
| `searchVector` | `tsvector?`  | -       | Full-text search         |

**Unique:** `[worldId, type, name]` **Relations:** `outgoingRelations`,
`incomingRelations` **Table:** `entity`

### EntityRelation

Relationships between entities with type, strength, and optional
bidirectionality.

| Field           | Type      | Default | Description           |
| --------------- | --------- | ------- | --------------------- |
| `id`            | `String`  | auto    | Primary key           |
| `sourceId`      | `String`  | -       | FK to source Entity   |
| `targetId`      | `String`  | -       | FK to target Entity   |
| `type`          | `String`  | -       | Relationship type     |
| `properties`    | `Json`    | `{}`    | Additional properties |
| `strength`      | `Float`   | `1.0`   | Relationship strength |
| `bidirectional` | `Boolean` | `false` | Whether bidirectional |

**Unique:** `[sourceId, targetId, type]` **Table:** `entity_relation`

### StoryGraph

Branching narrative graph containing story nodes connected by arcs.

| Field         | Type       | Default | Description            |
| ------------- | ---------- | ------- | ---------------------- |
| `id`          | `String`   | auto    | Primary key            |
| `name`        | `String`   | -       | Graph name             |
| `description` | `String?`  | -       | Description            |
| `worldId`     | `String`   | -       | FK to World            |
| `version`     | `Int`      | `1`     | Graph version          |
| `isActive`    | `Boolean`  | `true`  | Whether active version |
| `tags`        | `String[]` | `[]`    | Tags                   |

**Unique:** `[worldId, name, version]` **Relations:** `nodes` (StoryNode[]),
`arcs` (StoryArc[]) **Table:** `story_graph`

### StoryNode

A node in the story graph (scene, choice, condition, action, start, or end).

| Field          | Type            | Default | Description         |
| -------------- | --------------- | ------- | ------------------- |
| `id`           | `String`        | auto    | Primary key         |
| `storyGraphId` | `String`        | -       | FK to StoryGraph    |
| `type`         | `StoryNodeType` | `SCENE` | Node type           |
| `name`         | `String`        | -       | Node name           |
| `content`      | `String?`       | -       | Narrative content   |
| `positionX`    | `Float`         | `0`     | X position (for UI) |
| `positionY`    | `Float`         | `0`     | Y position (for UI) |
| `conditions`   | `Json`          | `[]`    | Entry conditions    |
| `actions`      | `Json`          | `[]`    | Triggered actions   |
| `metadata`     | `Json`          | `{}`    | Metadata            |

**Relations:** `outgoingArcs`, `incomingArcs` **Table:** `story_node`

### StoryArc

Directed edge connecting story nodes. Arcs can have labels (choice text),
conditions, and priority for auto-selection.

| Field          | Type      | Default | Description             |
| -------------- | --------- | ------- | ----------------------- |
| `id`           | `String`  | auto    | Primary key             |
| `storyGraphId` | `String`  | -       | FK to StoryGraph        |
| `sourceId`     | `String`  | -       | FK to source StoryNode  |
| `targetId`     | `String`  | -       | FK to target StoryNode  |
| `label`        | `String?` | -       | Arc label (choice text) |
| `conditions`   | `Json`    | `[]`    | Traversal conditions    |
| `priority`     | `Int`     | `0`     | Auto-selection priority |

**Unique:** `[storyGraphId, sourceId, targetId]` **Table:** `story_arc`

### DialogueTree

Tree structure for NPC conversations, associated with speakers and a location.

| Field         | Type       | Default | Description        |
| ------------- | ---------- | ------- | ------------------ |
| `id`          | `String`   | auto    | Primary key        |
| `name`        | `String`   | -       | Tree name          |
| `description` | `String?`  | -       | Description        |
| `worldId`     | `String`   | -       | FK to World        |
| `speakers`    | `Json`     | `[]`    | Speaker entity IDs |
| `locationId`  | `String?`  | -       | Location entity ID |
| `tags`        | `String[]` | `[]`    | Tags               |

**Unique:** `[worldId, name]` **Relations:** `nodes` (DialogueNode[]),
`sessions` (DialogueSession[]) **Table:** `dialogue_tree`

### DialogueNode

A node in a dialogue tree with type, speaker, emotion, conditions, and
branching.

| Field            | Type               | Default     | Description        |
| ---------------- | ------------------ | ----------- | ------------------ |
| `id`             | `String`           | auto        | Primary key        |
| `dialogueTreeId` | `String`           | -           | FK to DialogueTree |
| `type`           | `DialogueNodeType` | `STATEMENT` | Node type          |
| `speakerId`      | `String?`          | -           | Speaker entity ID  |
| `content`        | `String?`          | -           | Dialogue text      |
| `emotion`        | `String?`          | -           | Emotion/tone       |
| `conditions`     | `Json`             | `[]`        | Display conditions |
| `actions`        | `Json`             | `[]`        | Triggered actions  |
| `isStart`        | `Boolean`          | `false`     | Whether start node |
| `isEnd`          | `Boolean`          | `false`     | Whether end node   |
| `choices`        | `Json`             | `[]`        | Player choices     |
| `nextNodes`      | `Json`             | `[]`        | Next node IDs      |

**Table:** `dialogue_node`

### DialogueSession

Active instance of a dialogue being navigated by a participant.

| Field            | Type        | Default | Description           |
| ---------------- | ----------- | ------- | --------------------- |
| `id`             | `String`    | auto    | Primary key           |
| `dialogueTreeId` | `String`    | -       | FK to DialogueTree    |
| `currentNodeId`  | `String?`   | -       | Current node ID       |
| `history`        | `Json`      | `[]`    | Visited node history  |
| `context`        | `Json`      | `{}`    | Session context/state |
| `participantId`  | `String?`   | -       | Participant user ID   |
| `isComplete`     | `Boolean`   | `false` | Whether completed     |
| `startedAt`      | `DateTime`  | `now()` | Start time            |
| `completedAt`    | `DateTime?` | -       | Completion time       |

**Table:** `dialogue_session`

### Quest

Quest definitions with category, priority, prerequisites, and location/giver
references.

| Field           | Type            | Default  | Description               |
| --------------- | --------------- | -------- | ------------------------- |
| `id`            | `String`        | auto     | Primary key               |
| `name`          | `String`        | -        | Quest name                |
| `description`   | `String?`       | -        | Description               |
| `worldId`       | `String`        | -        | FK to World               |
| `category`      | `QuestCategory` | `SIDE`   | Quest category            |
| `priority`      | `QuestPriority` | `NORMAL` | Priority                  |
| `status`        | `QuestStatus`   | `DRAFT`  | Lifecycle status          |
| `giverId`       | `String?`       | -        | Quest giver entity ID     |
| `locationId`    | `String?`       | -        | Quest location entity ID  |
| `minLevel`      | `Int?`          | -        | Minimum level requirement |
| `maxLevel`      | `Int?`          | -        | Maximum level             |
| `prerequisites` | `Json`          | `[]`     | Prerequisite quest IDs    |
| `tags`          | `String[]`      | `[]`     | Tags                      |

**Unique:** `[worldId, name]` **Relations:** `objectives` (QuestObjective[]),
`rewards` (QuestReward[]) **Table:** `quest`

### QuestObjective

Individual objectives within a quest with type, target count, and progress
tracking.

| Field             | Type            | Default  | Description           |
| ----------------- | --------------- | -------- | --------------------- |
| `id`              | `String`        | auto     | Primary key           |
| `questId`         | `String`        | -        | FK to Quest           |
| `type`            | `ObjectiveType` | `CUSTOM` | Objective type        |
| `description`     | `String`        | -        | Objective description |
| `targetCount`     | `Int`           | `1`      | Target count          |
| `currentProgress` | `Int`           | `0`      | Current progress      |
| `isCompleted`     | `Boolean`       | `false`  | Whether completed     |
| `isOptional`      | `Boolean`       | `false`  | Whether optional      |
| `orderIndex`      | `Int`           | `0`      | Display order         |
| `targetEntityId`  | `String?`       | -        | Target entity ID      |
| `metadata`        | `Json`          | `{}`     | Metadata              |

**Table:** `quest_objective`

### QuestReward

Rewards granted upon quest completion.

| Field         | Type      | Default | Description            |
| ------------- | --------- | ------- | ---------------------- |
| `id`          | `String`  | auto    | Primary key            |
| `questId`     | `String`  | -       | FK to Quest            |
| `type`        | `String`  | -       | Reward type            |
| `amount`      | `Int?`    | -       | Amount (XP, currency)  |
| `itemId`      | `String?` | -       | Item entity ID         |
| `description` | `String?` | -       | Reward description     |
| `isChoice`    | `Boolean` | `false` | Whether player chooses |

**Table:** `quest_reward`

### SimulationRun

A simulation job that runs economy, politics, culture, or full world simulations
over a series of ticks.

| Field            | Type                 | Default   | Description             |
| ---------------- | -------------------- | --------- | ----------------------- |
| `id`             | `String`             | auto      | Primary key             |
| `worldId`        | `String`             | -         | FK to World             |
| `type`           | `SimulationType`     | `FULL`    | Simulation type         |
| `status`         | `SimulationStatus`   | `PENDING` | Job status              |
| `priority`       | `SimulationPriority` | `NORMAL`  | Priority                |
| `input`          | `Json`               | `{}`      | Input parameters        |
| `output`         | `Json?`              | -         | Output results          |
| `progress`       | `Float`              | `0`       | Progress (0-1)          |
| `currentTick`    | `Int`                | `0`       | Current simulation tick |
| `totalTicks`     | `Int?`               | -         | Total ticks to run      |
| `errorMessage`   | `String?`            | -         | Error message           |
| `errorDetails`   | `Json?`              | -         | Error details           |
| `workerId`       | `String?`            | -         | Worker assignment       |
| `startVersionId` | `String?`            | -         | World version at start  |
| `endVersionId`   | `String?`            | -         | World version at end    |

**Relations:** `events` (SimulationEvent[]), `snapshots` (SimulationSnapshot[])
**Table:** `simulation_run`

### SimulationEvent

Events generated during simulation at specific ticks.

| Field              | Type     | Default | Description         |
| ------------------ | -------- | ------- | ------------------- |
| `id`               | `String` | auto    | Primary key         |
| `simulationId`     | `String` | -       | FK to SimulationRun |
| `type`             | `String` | -       | Event type          |
| `tick`             | `Int`    | -       | Simulation tick     |
| `data`             | `Json`   | `{}`    | Event data          |
| `affectedEntities` | `Json`   | `[]`    | Affected entity IDs |

**Table:** `simulation_event`

### SimulationSnapshot

State snapshots captured at specific ticks during simulation.

| Field          | Type     | Default | Description         |
| -------------- | -------- | ------- | ------------------- |
| `id`           | `String` | auto    | Primary key         |
| `simulationId` | `String` | -       | FK to SimulationRun |
| `tick`         | `Int`    | -       | Simulation tick     |
| `state`        | `Json`   | `{}`    | Full state snapshot |

**Unique:** `[simulationId, tick]` **Table:** `simulation_snapshot`

### ValidationResult

Results from lore consistency validation checks.

| Field            | Type      | Default | Description             |
| ---------------- | --------- | ------- | ----------------------- |
| `id`             | `String`  | auto    | Primary key             |
| `worldId`        | `String`  | -       | World reference         |
| `entityId`       | `String?` | -       | Entity reference        |
| `validationType` | `String`  | -       | Validation type         |
| `isValid`        | `Boolean` | -       | Whether valid           |
| `issues`         | `Json`    | `[]`    | Issues found            |
| `suggestions`    | `Json`    | `[]`    | Improvement suggestions |

**Table:** `validation_result`

## Key Enums

### WorldStatus

`DRAFT`, `ACTIVE`, `ARCHIVED`, `DELETED`

### WorldGenre

`FANTASY`, `SCIENCE_FICTION`, `HISTORICAL`, `CONTEMPORARY`, `HORROR`, `MYSTERY`,
`MYTHOLOGY`, `CUSTOM`

### WorldScope

`SMALL` (village/city), `MEDIUM` (region/country), `LARGE` (continent), `EPIC`
(world), `COSMIC` (multi-world)

### TechnologyLevel

`PRIMITIVE`, `ANCIENT`, `MEDIEVAL`, `RENAISSANCE`, `INDUSTRIAL`, `MODERN`,
`FUTURISTIC`, `ADVANCED`, `MIXED`

### MagicLevel

`NONE`, `LOW`, `MEDIUM`, `HIGH`, `EPIC`

### EntityType

`CHARACTER`, `FACTION`, `LOCATION`, `EVENT`, `RELATIONSHIP`, `ERA`, `REGION`,
`ITEM`, `CULTURE`, `RELIGION`

### CharacterStatus

`ALIVE`, `DECEASED`, `UNKNOWN`, `MISSING`, `LEGENDARY`

### FactionType

`GOVERNMENT`, `MILITARY`, `RELIGIOUS`, `CRIMINAL`, `MERCHANT`, `GUILD`,
`SECRET_SOCIETY`, `FAMILY`, `TRIBAL`, `OTHER`

### LocationType

`CITY`, `TOWN`, `VILLAGE`, `FORTRESS`, `TEMPLE`, `DUNGEON`, `WILDERNESS`,
`LANDMARK`, `REGION`, `REALM`, `PLANE`

### QuestCategory

`MAIN`, `SIDE`, `EXPLORATION`, `FACTION`, `DAILY`, `WORLD_EVENT`

### ObjectiveType

`COLLECT`, `KILL`, `TALK`, `EXPLORE`, `ESCORT`, `DELIVER`, `CRAFT`, `CUSTOM`

### StoryNodeType

`START`, `SCENE`, `CHOICE`, `CONDITION`, `ACTION`, `END`

### DialogueNodeType

`GREETING`, `STATEMENT`, `QUESTION`, `RESPONSE`, `CHOICE`, `END`

### SimulationType

`ECONOMY`, `POLITICS`, `CULTURE`, `SCENARIO`, `FULL`

### SimulationStatus

`PENDING`, `QUEUED`, `RUNNING`, `COMPLETED`, `FAILED`, `CANCELLED`

### MergeRequestStatus

`OPEN`, `APPROVED`, `MERGED`, `REJECTED`, `CANCELLED`

### ValidationSeverity

`INFO`, `WARNING`, `ERROR`, `CRITICAL`
