# Bellona Database Schema

Bellona is the engine integration and build pipeline domain. Its database stores
build jobs, artifacts, export packages, engine project generation records,
bridge sessions for engine communication, build workers, asset caches, and build
templates.

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

## ER Diagram

```
+------------------+       +-------------------+       +------------------+
|      Build       |1----*|   BuildArtifact   |       |    ExportJob     |
|------------------|       |-------------------|       |------------------|
| id (PK)          |       | id (PK)           |       | id (PK)          |
| projectId        |       | buildId (FK)      |       | projectId        |
| status           |       | name              |       | buildId (FK)?    |
| priority         |       | platform          |       | status           |
| progress         |       | path              |       | format           |
| targetPlatforms[]|       | size              |       | targetEngine?    |
| optimizationLevel|       | checksum          |       | downloadUrl?     |
| compressionLevel |       | storageBucket     |       | size?            |
| incremental      |       | storageKey        |       | userId           |
| assetIds[]       |       | mimeType?         |       | organizationId?  |
| errorMessage?    |       | metadata          |       +--------+---------+
| workerId?        |       | createdAt         |                |
| userId           |       | expiresAt?        |                |
| organizationId?  |       +-------------------+                |
| metadata         |                                            |
| createdAt        |1----*+-------------------+                |
| startedAt?       |       |     BuildLog      |                |
| completedAt?     |       |-------------------|                |
+--------+---------+       | id (PK)           |                |
         |                 | buildId (FK)      |                |
         |*---1            | level             |                |
         |                 | message           |                |
         +-----------------| source?           |                |
                           | step?             |                |
                           | timestamp         |                |
                           +-------------------+                |
                                                                |
+------------------+       +-------------------+                |
|  EngineProject   |       |  BridgeSession    |1----*+-------------------+
|------------------|       |-------------------|       |  SyncConflict     |
| id (PK)          |       | id (PK)           |       |-------------------|
| projectId        |       | projectId         |       | id (PK)           |
| engine           |       | engine            |       | sessionId (FK)    |
| engineVersion    |       | engineVersion?    |       | assetId           |
| modules[]        |       | status            |       | localVersion      |
| plugins[]        |       | hostname?         |       | remoteVersion     |
| platforms[]      |       | port?             |       | resolution?       |
| outputPath       |       | sessionToken?     |       | resolvedAt?       |
| status           |       | lastSyncAt?       |       | resolvedBy?       |
| userId           |       | syncDirection?    |       +-------------------+
| organizationId?  |       | userId            |
+------------------+       | organizationId?   |
                           | lastHeartbeat?    |
                           +-------------------+

+------------------+       +-------------------+       +-------------------+
|   BuildWorker    |       |    AssetCache      |       |  BuildTemplate    |
|------------------|       |-------------------|       |-------------------|
| id (PK)          |       | id (PK)           |       | id (PK)           |
| name             |       | projectId         |       | name              |
| hostname (UQ)    |       | assetId           |       | description?      |
| jobTypes[]       |       | name              |       | platforms[]       |
| platforms[]      |       | type              |       | engines[]         |
| engines[]        |       | path              |       | optimizationLevel |
| concurrency      |       | size              |       | compressionLevel  |
| status           |       | checksum          |       | isActive          |
| currentJobId?    |       | tags[]            |       | isDefault         |
| lastHeartbeat?   |       | metadata          |       | organizationId?   |
| totalJobsProc.   |       | engineData        |       | createdBy         |
| successfulJobs   |       +-------------------+       +-------------------+
| failedJobs       |
| avgJobDuration?  |       +-------------------+
+------------------+       |     AuditLog      |
                           |-------------------|
                           | id (PK)           |
                           | action            |
                           | entityType        |
                           | entityId          |
                           | previousState?    |
                           | newState?         |
                           | changes?          |
                           | userId?           |
                           | ipAddress?        |
                           | createdAt         |
                           +-------------------+
```

## Models

### Build

The central model representing a build job. Each build targets one or more
platforms, references a set of asset IDs, and produces artifacts and logs.

| Field                   | Type                | Default   | Description                          |
| ----------------------- | ------------------- | --------- | ------------------------------------ |
| `id`                    | `String` (cuid)     | auto      | Primary key                          |
| `projectId`             | `String`            | -         | Reference to the project             |
| `status`                | `BuildStatus`       | `PENDING` | Current build status                 |
| `priority`              | `JobPriority`       | `NORMAL`  | Queue priority                       |
| `progress`              | `Int`               | `0`       | Progress percentage (0-100)          |
| `targetPlatforms`       | `Platform[]`        | `[]`      | Target platforms for the build       |
| `optimizationLevel`     | `OptimizationLevel` | `BASIC`   | Optimization level                   |
| `includeDebugSymbols`   | `Boolean`           | `false`   | Include debug symbols                |
| `compressionLevel`      | `CompressionLevel`  | `MEDIUM`  | Output compression level             |
| `incremental`           | `Boolean`           | `false`   | Whether this is an incremental build |
| `assetIds`              | `String[]`          | `[]`      | IDs of assets to include             |
| `errorMessage`          | `String?`           | -         | Error message if failed              |
| `errorCode`             | `String?`           | -         | Error code                           |
| `retryCount`            | `Int`               | `0`       | Number of retries attempted          |
| `maxRetries`            | `Int`               | `3`       | Maximum retry attempts               |
| `workerId`              | `String?`           | -         | Assigned worker ID                   |
| `queuePosition`         | `Int?`              | -         | Position in queue                    |
| `userId`                | `String`            | -         | Owner user ID                        |
| `organizationId`        | `String?`           | -         | Owner organization ID                |
| `metadata`              | `Json`              | `{}`      | Arbitrary metadata                   |
| `createdAt`             | `DateTime`          | `now()`   | Creation timestamp                   |
| `updatedAt`             | `DateTime`          | auto      | Last update timestamp                |
| `startedAt`             | `DateTime?`         | -         | When processing started              |
| `completedAt`           | `DateTime?`         | -         | When processing completed            |
| `estimatedCompletionAt` | `DateTime?`         | -         | Estimated completion time            |
| `deletedAt`             | `DateTime?`         | -         | Soft delete timestamp                |
| `duration`              | `Int?`              | -         | Duration in milliseconds             |

**Relations:** `artifacts` (BuildArtifact[]), `logs` (BuildLog[]), `exports`
(ExportJob[])

**Table:** `builds`

### BuildArtifact

Output files produced by a build. Each artifact belongs to one build and
represents a single output file stored in object storage.

| Field           | Type        | Default | Description           |
| --------------- | ----------- | ------- | --------------------- |
| `id`            | `String`    | auto    | Primary key           |
| `buildId`       | `String`    | -       | FK to Build           |
| `name`          | `String`    | -       | Artifact filename     |
| `platform`      | `Platform`  | -       | Target platform       |
| `path`          | `String`    | -       | File path             |
| `size`          | `BigInt`    | -       | File size in bytes    |
| `checksum`      | `String`    | -       | File checksum         |
| `storageBucket` | `String`    | -       | Object storage bucket |
| `storageKey`    | `String`    | -       | Object storage key    |
| `mimeType`      | `String?`   | -       | MIME type             |
| `metadata`      | `Json`      | `{}`    | Arbitrary metadata    |
| `createdAt`     | `DateTime`  | `now()` | Creation timestamp    |
| `expiresAt`     | `DateTime?` | -       | Expiration timestamp  |
| `deletedAt`     | `DateTime?` | -       | Soft delete timestamp |

**Unique:** `[storageBucket, storageKey]` **Table:** `build_artifacts`

### BuildLog

Log entries emitted during a build. Tracks level, message, source, and pipeline
step.

| Field       | Type       | Default | Description        |
| ----------- | ---------- | ------- | ------------------ |
| `id`        | `String`   | auto    | Primary key        |
| `buildId`   | `String`   | -       | FK to Build        |
| `level`     | `LogLevel` | -       | Log level          |
| `message`   | `String`   | -       | Log message        |
| `details`   | `Json?`    | -       | Structured details |
| `source`    | `String?`  | -       | Source component   |
| `step`      | `String?`  | -       | Pipeline step name |
| `timestamp` | `DateTime` | `now()` | Timestamp          |

**Table:** `build_logs`

### ExportJob

Export job that packages assets or build artifacts into a downloadable format
for game engines.

| Field            | Type               | Default   | Description              |
| ---------------- | ------------------ | --------- | ------------------------ |
| `id`             | `String`           | auto      | Primary key              |
| `projectId`      | `String`           | -         | Project reference        |
| `buildId`        | `String?`          | -         | Optional FK to Build     |
| `status`         | `ExportStatus`     | `PENDING` | Export status            |
| `progress`       | `Int`              | `0`       | Progress percentage      |
| `format`         | `ExportFormat`     | -         | Target format            |
| `targetEngine`   | `EngineType?`      | -         | Target game engine       |
| `includeSource`  | `Boolean`          | `false`   | Include source files     |
| `compression`    | `CompressionLevel` | `MEDIUM`  | Compression level        |
| `assetIds`       | `String[]`         | `[]`      | Asset selection          |
| `downloadUrl`    | `String?`          | -         | Download URL when ready  |
| `size`           | `BigInt?`          | -         | Output size in bytes     |
| `checksum`       | `String?`          | -         | Output checksum          |
| `storageBucket`  | `String?`          | -         | Object storage bucket    |
| `storageKey`     | `String?`          | -         | Object storage key       |
| `errorMessage`   | `String?`          | -         | Error message if failed  |
| `errorCode`      | `String?`          | -         | Error code               |
| `userId`         | `String`           | -         | Owner user ID            |
| `organizationId` | `String?`          | -         | Owner organization ID    |
| `metadata`       | `Json`             | `{}`      | Arbitrary metadata       |
| `createdAt`      | `DateTime`         | `now()`   | Creation timestamp       |
| `updatedAt`      | `DateTime`         | auto      | Last update timestamp    |
| `completedAt`    | `DateTime?`        | -         | Completion timestamp     |
| `expiresAt`      | `DateTime?`        | -         | Expiration timestamp     |
| `deletedAt`      | `DateTime?`        | -         | Soft delete timestamp    |
| `duration`       | `Int?`             | -         | Duration in milliseconds |

**Table:** `export_jobs`

### EngineProject

Record of engine project generation -- creating a Unity, Unreal, Godot, or other
engine project scaffold with selected assets and plugins.

| Field            | Type          | Default   | Description               |
| ---------------- | ------------- | --------- | ------------------------- |
| `id`             | `String`      | auto      | Primary key               |
| `projectId`      | `String`      | -         | Source project reference  |
| `engine`         | `EngineType`  | -         | Target engine             |
| `engineVersion`  | `String`      | -         | Engine version string     |
| `modules`        | `String[]`    | `[]`      | Engine modules to include |
| `plugins`        | `String[]`    | `[]`      | Engine plugins to include |
| `platforms`      | `Platform[]`  | `[]`      | Target platforms          |
| `outputPath`     | `String`      | -         | Output directory path     |
| `templateId`     | `String?`     | -         | Build template reference  |
| `includeAssets`  | `Boolean`     | `true`    | Include project assets    |
| `assetIds`       | `String[]`    | `[]`      | Specific asset selection  |
| `settings`       | `Json`        | `{}`      | Engine-specific settings  |
| `status`         | `BuildStatus` | `PENDING` | Generation status         |
| `errorMessage`   | `String?`     | -         | Error message if failed   |
| `fileCount`      | `Int?`        | -         | Number of generated files |
| `totalSize`      | `BigInt?`     | -         | Total size in bytes       |
| `dependencies`   | `String[]`    | `[]`      | External dependencies     |
| `warnings`       | `String[]`    | `[]`      | Generation warnings       |
| `userId`         | `String`      | -         | Owner user ID             |
| `organizationId` | `String?`     | -         | Owner organization ID     |
| `metadata`       | `Json`        | `{}`      | Arbitrary metadata        |
| `createdAt`      | `DateTime`    | `now()`   | Creation timestamp        |
| `updatedAt`      | `DateTime`    | auto      | Last update timestamp     |
| `completedAt`    | `DateTime?`   | -         | Completion timestamp      |
| `deletedAt`      | `DateTime?`   | -         | Soft delete timestamp     |
| `duration`       | `Int?`        | -         | Duration in milliseconds  |

**Table:** `engine_projects`

### BridgeSession

A live connection session between the Bellona platform and a game engine editor
(Unity, Unreal, Godot, Blender). Tracks connection state, sync progress, and
heartbeats.

| Field              | Type               | Default        | Description                  |
| ------------------ | ------------------ | -------------- | ---------------------------- |
| `id`               | `String`           | auto           | Primary key                  |
| `projectId`        | `String`           | -              | Project reference            |
| `engine`           | `EngineType`       | -              | Connected engine             |
| `engineVersion`    | `String?`          | -              | Engine version               |
| `status`           | `ConnectionStatus` | `DISCONNECTED` | Connection status            |
| `hostname`         | `String?`          | -              | Engine hostname              |
| `port`             | `Int?`             | -              | Engine port                  |
| `pid`              | `Int?`             | -              | Engine process ID            |
| `sessionToken`     | `String?`          | -              | Session authentication token |
| `lastSyncAt`       | `DateTime?`        | -              | Last sync timestamp          |
| `syncDirection`    | `SyncDirection?`   | -              | Sync direction               |
| `syncedAssetCount` | `Int`              | `0`            | Number of synced assets      |
| `userId`           | `String`           | -              | Owner user ID                |
| `organizationId`   | `String?`          | -              | Owner organization ID        |
| `metadata`         | `Json`             | `{}`           | Arbitrary metadata           |
| `createdAt`        | `DateTime`         | `now()`        | Creation timestamp           |
| `updatedAt`        | `DateTime`         | auto           | Last update timestamp        |
| `connectedAt`      | `DateTime?`        | -              | Connection timestamp         |
| `disconnectedAt`   | `DateTime?`        | -              | Disconnection timestamp      |
| `deletedAt`        | `DateTime?`        | -              | Soft delete timestamp        |
| `lastHeartbeat`    | `DateTime?`        | -              | Last heartbeat timestamp     |

**Relations:** `conflicts` (SyncConflict[]) **Table:** `bridge_sessions`

### SyncConflict

Conflict detected during asset synchronization between local and remote (engine)
versions.

| Field            | Type        | Default | Description                             |
| ---------------- | ----------- | ------- | --------------------------------------- |
| `id`             | `String`    | auto    | Primary key                             |
| `sessionId`      | `String`    | -       | FK to BridgeSession                     |
| `assetId`        | `String`    | -       | Conflicting asset ID                    |
| `assetPath`      | `String?`   | -       | Asset file path                         |
| `localVersion`   | `String`    | -       | Local version identifier                |
| `remoteVersion`  | `String`    | -       | Remote version identifier               |
| `localChecksum`  | `String?`   | -       | Local file checksum                     |
| `remoteChecksum` | `String?`   | -       | Remote file checksum                    |
| `resolution`     | `String?`   | -       | Resolution choice (local/remote/manual) |
| `resolvedAt`     | `DateTime?` | -       | Resolution timestamp                    |
| `resolvedBy`     | `String?`   | -       | User who resolved                       |
| `createdAt`      | `DateTime`  | `now()` | Creation timestamp                      |
| `updatedAt`      | `DateTime`  | auto    | Last update timestamp                   |

**Table:** `sync_conflicts`

### BuildWorker

Registration record for a build worker node. Tracks capabilities (job types,
platforms, engines), status, and performance statistics.

| Field                | Type           | Default   | Description                 |
| -------------------- | -------------- | --------- | --------------------------- |
| `id`                 | `String`       | auto      | Primary key                 |
| `name`               | `String`       | -         | Worker display name         |
| `hostname`           | `String`       | -         | Worker hostname (unique)    |
| `jobTypes`           | `JobType[]`    | `[]`      | Supported job types         |
| `platforms`          | `Platform[]`   | `[]`      | Supported platforms         |
| `engines`            | `EngineType[]` | `[]`      | Supported engines           |
| `concurrency`        | `Int`          | `1`       | Max concurrent jobs         |
| `status`             | `WorkerStatus` | `OFFLINE` | Current worker status       |
| `currentJobId`       | `String?`      | -         | Currently processing job ID |
| `lastHeartbeat`      | `DateTime?`    | -         | Last heartbeat              |
| `totalJobsProcessed` | `Int`          | `0`       | Total jobs processed        |
| `successfulJobs`     | `Int`          | `0`       | Successful job count        |
| `failedJobs`         | `Int`          | `0`       | Failed job count            |
| `averageJobDuration` | `Float?`       | -         | Average duration (ms)       |
| `version`            | `String?`      | -         | Worker software version     |
| `metadata`           | `Json`         | `{}`      | Arbitrary metadata          |
| `createdAt`          | `DateTime`     | `now()`   | Creation timestamp          |
| `updatedAt`          | `DateTime`     | auto      | Last update timestamp       |

**Unique:** `hostname` **Table:** `build_workers`

### AssetCache

Cached metadata for assets, allowing quick lookups without querying the main
asset store. Scoped per project.

| Field            | Type        | Default | Description                 |
| ---------------- | ----------- | ------- | --------------------------- |
| `id`             | `String`    | auto    | Primary key                 |
| `projectId`      | `String`    | -       | Project reference           |
| `assetId`        | `String`    | -       | Asset reference             |
| `name`           | `String`    | -       | Asset name                  |
| `type`           | `AssetType` | -       | Asset type                  |
| `path`           | `String`    | -       | Asset file path             |
| `size`           | `BigInt`    | -       | File size in bytes          |
| `checksum`       | `String`    | -       | File checksum               |
| `tags`           | `String[]`  | `[]`    | Tags                        |
| `metadata`       | `Json`      | `{}`    | Arbitrary metadata          |
| `engineData`     | `Json`      | `{}`    | Engine-specific cached data |
| `createdAt`      | `DateTime`  | `now()` | Creation timestamp          |
| `updatedAt`      | `DateTime`  | auto    | Last update timestamp       |
| `lastAccessedAt` | `DateTime?` | -       | Last access timestamp       |

**Unique:** `[projectId, assetId]` **Table:** `asset_cache`

### BuildTemplate

Reusable build configuration templates that define default settings for
platform, engine, optimization, and compression choices.

| Field                 | Type                | Default  | Description                 |
| --------------------- | ------------------- | -------- | --------------------------- |
| `id`                  | `String`            | auto     | Primary key                 |
| `name`                | `String`            | -        | Template name               |
| `description`         | `String?`           | -        | Template description        |
| `platforms`           | `Platform[]`        | `[]`     | Target platforms            |
| `engines`             | `EngineType[]`      | `[]`     | Target engines              |
| `optimizationLevel`   | `OptimizationLevel` | `BASIC`  | Default optimization        |
| `includeDebugSymbols` | `Boolean`           | `false`  | Include debug symbols       |
| `compressionLevel`    | `CompressionLevel`  | `MEDIUM` | Default compression         |
| `settings`            | `Json`              | `{}`     | Additional settings         |
| `isActive`            | `Boolean`           | `true`   | Whether template is active  |
| `isDefault`           | `Boolean`           | `false`  | Whether this is the default |
| `organizationId`      | `String?`           | -        | Owning organization         |
| `createdBy`           | `String`            | -        | Creator user ID             |
| `createdAt`           | `DateTime`          | `now()`  | Creation timestamp          |
| `updatedAt`           | `DateTime`          | auto     | Last update timestamp       |

**Table:** `build_templates`

### AuditLog

Audit trail for all entity changes in the Bellona database.

| Field           | Type       | Default | Description            |
| --------------- | ---------- | ------- | ---------------------- |
| `id`            | `String`   | auto    | Primary key            |
| `action`        | `String`   | -       | Action performed       |
| `entityType`    | `String`   | -       | Entity type changed    |
| `entityId`      | `String`   | -       | Entity ID changed      |
| `previousState` | `Json?`    | -       | State before change    |
| `newState`      | `Json?`    | -       | State after change     |
| `changes`       | `Json?`    | -       | Diff of changes        |
| `userId`        | `String?`  | -       | Actor user ID          |
| `ipAddress`     | `String?`  | -       | Actor IP address       |
| `userAgent`     | `String?`  | -       | Actor user agent       |
| `requestId`     | `String?`  | -       | Correlation request ID |
| `metadata`      | `Json`     | `{}`    | Arbitrary metadata     |
| `createdAt`     | `DateTime` | `now()` | Timestamp              |

**Table:** `audit_log`

## Enums

### BuildStatus

Build job lifecycle: `PENDING` -> `QUEUED` -> `RUNNING` -> `COMPLETED` |
`FAILED` | `CANCELLED`

### JobPriority

Queue priority: `LOW`, `NORMAL`, `HIGH`, `CRITICAL`

### Platform

Target build platforms: `WINDOWS`, `MACOS`, `LINUX`, `IOS`, `ANDROID`, `WEBGL`,
`PLAYSTATION`, `XBOX`, `SWITCH`

### EngineType

Supported game engines: `UNITY`, `UNREAL`, `GODOT`, `BLENDER`, `WEB`

### ExportFormat

Package formats: `ZIP`, `TAR_GZ`, `UNITYPACKAGE`, `UASSET`, `GODOT_PCK`

### ExportStatus

Export lifecycle: `PENDING`, `PROCESSING`, `COMPLETED`, `FAILED`, `EXPIRED`

### OptimizationLevel

Build optimization: `NONE`, `BASIC`, `FULL`

### CompressionLevel

Compression intensity: `LOW`, `MEDIUM`, `HIGH`

### LogLevel

Log severity: `DEBUG`, `INFO`, `WARNING`, `ERROR`

### ConnectionStatus

Engine bridge connection: `CONNECTED`, `DISCONNECTED`, `SYNCING`, `ERROR`

### SyncDirection

Asset sync direction: `PUSH`, `PULL`, `BIDIRECTIONAL`

### AssetType

Asset classification: `TEXTURE`, `MODEL`, `AUDIO`, `ANIMATION`, `MATERIAL`,
`PREFAB`, `SCRIPT`, `CONFIG`, `OTHER`

### JobType

Build worker job types: `ASSET_BAKE`, `VALIDATE`, `ENGINE_PROJECT_GENERATE`,
`EXPORT_PACKAGE`

### WorkerStatus

Worker lifecycle: `IDLE`, `BUSY`, `PAUSED`, `STOPPING`, `STOPPED`, `OFFLINE`

## Migration Instructions

### Generate a Migration

```bash
cd libs/bellona/database
npx prisma migrate dev --name <migration_name>
```

### Apply Migrations

```bash
# Development
npx prisma migrate dev

# Production
npx prisma migrate deploy
```

### Generate Prisma Client

```bash
npx prisma generate
```

### Reset Database (Development Only)

```bash
npx prisma migrate reset
```

### View Database

```bash
npx prisma studio
```

The Prisma Studio UI opens at `http://localhost:5555` and allows browsing and
editing all tables.
