# Isis Database Schema

Isis is the Generative AI Factory domain. Its database stores generation jobs,
GPU worker assignments, workflow definitions, generated output files with full
provenance tracking, model/checkpoint registry, asset lineage graphs, and
retention policies.

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

## ER Diagram

```
+---------------------+       +---------------------+
|   ModelRegistry     |       |      Workflow        |
|---------------------|       |---------------------|
| id (PK)             |       | id (PK)             |
| name (UQ w/version) |       | name (UQ w/ownerId) |
| type                |       | engine              |
| format              |       | category            |
| version             |       | status              |
| storagePath         |       | visibility          |
| sizeBytes           |       | currentVersion      |
| hashAlgorithm       |       | definition          |
| hashValue           |       | parameters          |
| triggerWords[]      |       | runCount            |
| tags[]              |       | starCount           |
| isPublic            |       | ownerId             |
+--------+------------+       +--------+------------+
         |                             |
         |1                            |1
         |                             |
         |*                            |*
+--------+------------+       +--------+------------+
|   GenerationJob     |       |  WorkflowVersion    |
|---------------------|       |---------------------|
| id (PK)             |       | id (PK)             |
| type                |       | workflowId (FK)     |
| status              |       | version (UQ w/wf)   |
| priority            |       | changelog?          |
| prompt?             |       | definition          |
| negativePrompt?     |       | deprecated          |
| modelId (FK)?       |       +---------------------+
| workflowId (FK)?    |
| parameters          |       +---------------------+
| gpuType?            |       |   WorkflowStar      |
| gpuTimeSeconds?     |       |---------------------|
| costEstimate?       |       | id (PK)             |
| userId              |       | workflowId (FK)     |
| projectId?          |       | userId (UQ w/wf)    |
+--------+------------+       +---------------------+
         |
         |1                   +---------------------+
         |                    |  WorkflowTemplate   |
         |*                   |---------------------|
+--------+------------+       | id (PK)             |
|   GeneratedOutput   |       | name                |
|---------------------|       | engine              |
| id (PK)             |       | category            |
| name                |       | definition          |
| type                |       | featured            |
| mimeType            |       | usageCount          |
| size                |       +---------------------+
| hashAlgorithm       |
| hashValue           |
| storageBucket       |       +---------------------+
| storageKey (UQ w/b) |       |    Provenance       |
| storageTier         |       |---------------------|
| status              |       | id (PK)             |
| width?              |       | outputId (FK, UQ)   |
| height?             |       | jobId (FK, UQ)?     |
| duration?           |       | workflowInfo?       |
| jobId (FK)?         |       | modelInfo?          |
| workflowId (FK)?    |       | lorasUsed           |
| ownerId             |       | parameters          |
| tags[]              |       | inputs              |
+--------+------------+       | seed?               |
         |                    | gpu?                |
         |1                   | provider?           |
         |                    | cost?               |
         |*         1         +---------------------+
+--------+------------+
|    LineageEdge      |
|---------------------|
| id (PK)             |
| sourceId (FK)       | ---> GeneratedOutput (LineageSource)
| targetId (FK)       | ---> GeneratedOutput (LineageTarget)
| type                |
| transformation?     |
| weight?             |
+---------------------+

+---------------------+       +---------------------+
|     GpuWorker       |       |  RetentionPolicy    |
|---------------------|       |---------------------|
| id (PK)             |       | id (PK)             |
| name                |       | name (UQ)           |
| hostname (UQ)       |       | hotRetentionDays    |
| gpuType             |       | warmRetentionDays   |
| gpuMemoryMb         |       | coldRetentionDays   |
| gpuCount            |       | totalRetentionDays  |
| supportedTypes[]    |       | outputTypes[]       |
| supportedEngines[]  |       | isActive            |
| status              |       | organizationId?     |
| lastHeartbeat?      |       +---------------------+
| totalJobsProcessed  |
+---------------------+       +---------------------+
                              |     AuditLog        |
                              |---------------------|
                              | id (PK)             |
                              | action              |
                              | entityType          |
                              | entityId            |
                              | previousState?      |
                              | newState?           |
                              | userId?             |
                              | createdAt           |
                              +---------------------+
```

## Models

### GenerationJob

The core work unit. Each generation job represents a single AI generation
request (text-to-image, image-to-video, 3D generation, etc.) with full lifecycle
tracking.

| Field                   | Type             | Default   | Description                 |
| ----------------------- | ---------------- | --------- | --------------------------- |
| `id`                    | `String` (cuid)  | auto      | Primary key                 |
| `type`                  | `GenerationType` | -         | Generation type             |
| `status`                | `JobStatus`      | `PENDING` | Job lifecycle status        |
| `priority`              | `JobPriority`    | `NORMAL`  | Queue priority              |
| `progress`              | `Int`            | `0`       | Progress percentage (0-100) |
| `prompt`                | `String?`        | -         | Text prompt                 |
| `negativePrompt`        | `String?`        | -         | Negative prompt             |
| `inputUrl`              | `String?`        | -         | Input file URL              |
| `modelId`               | `String?`        | -         | FK to ModelRegistry         |
| `workflowId`            | `String?`        | -         | FK to Workflow              |
| `parameters`            | `Json`           | `{}`      | Generation parameters       |
| `outputData`            | `Json?`          | -         | Output metadata             |
| `previewUrl`            | `String?`        | -         | Preview image URL           |
| `errorMessage`          | `String?`        | -         | Error message               |
| `errorCode`             | `String?`        | -         | Error code                  |
| `retryCount`            | `Int`            | `0`       | Retry count                 |
| `maxRetries`            | `Int`            | `3`       | Max retries                 |
| `workerId`              | `String?`        | -         | Assigned GPU worker         |
| `queueName`             | `String?`        | -         | Queue name                  |
| `queuePosition`         | `Int?`           | -         | Queue position              |
| `callbackUrl`           | `String?`        | -         | Webhook callback URL        |
| `metadata`              | `Json`           | `{}`      | Arbitrary metadata          |
| `gpuType`               | `String?`        | -         | GPU type used               |
| `gpuTimeSeconds`        | `Int?`           | -         | GPU time consumed           |
| `tokensUsed`            | `Int?`           | -         | LLM tokens consumed         |
| `costEstimate`          | `Float?`         | -         | Estimated cost              |
| `userId`                | `String`         | -         | Owner user ID               |
| `organizationId`        | `String?`        | -         | Owner organization          |
| `projectId`             | `String?`        | -         | Project reference           |
| `createdAt`             | `DateTime`       | `now()`   | Created                     |
| `updatedAt`             | `DateTime`       | auto      | Updated                     |
| `startedAt`             | `DateTime?`      | -         | Processing start            |
| `completedAt`           | `DateTime?`      | -         | Processing end              |
| `estimatedCompletionAt` | `DateTime?`      | -         | ETA                         |
| `deletedAt`             | `DateTime?`      | -         | Soft delete                 |

**Relations:** `model` (ModelRegistry?), `workflow` (Workflow?), `outputs`
(GeneratedOutput[]), `provenance` (Provenance?) **Table:** `generation_jobs`

### Workflow

Reusable workflow definitions for ComfyUI, Blender, Unreal, Godot, or custom
pipelines. Includes versioning, visibility controls, and community statistics.

| Field              | Type                 | Default   | Description                       |
| ------------------ | -------------------- | --------- | --------------------------------- |
| `id`               | `String`             | auto      | Primary key                       |
| `name`             | `String`             | -         | Workflow name                     |
| `description`      | `String?`            | -         | Description                       |
| `engine`           | `WorkflowEngine`     | -         | Engine type                       |
| `category`         | `WorkflowCategory`   | -         | Category                          |
| `status`           | `WorkflowStatus`     | `DRAFT`   | Lifecycle status                  |
| `visibility`       | `WorkflowVisibility` | `PRIVATE` | Access level                      |
| `currentVersion`   | `String`             | `"1.0.0"` | Current version                   |
| `totalVersions`    | `Int`                | `1`       | Total version count               |
| `parameters`       | `Json`               | `[]`      | Workflow parameters schema        |
| `outputs`          | `Json`               | `[]`      | Expected outputs schema           |
| `requirements`     | `Json`               | `{}`      | Hardware/software requirements    |
| `definition`       | `Json`               | `{}`      | Workflow definition (nodes/edges) |
| `previewImage`     | `String?`            | -         | Preview image URL                 |
| `tags`             | `String[]`           | `[]`      | Tags                              |
| `runCount`         | `Int`                | `0`       | Total runs                        |
| `successfulRuns`   | `Int`                | `0`       | Successful runs                   |
| `avgExecutionTime` | `Float?`             | -         | Average execution time            |
| `starCount`        | `Int`                | `0`       | Stars/likes                       |
| `downloadCount`    | `Int`                | `0`       | Downloads                         |
| `author`           | `String`             | -         | Author name                       |
| `ownerId`          | `String`             | -         | Owner user ID                     |
| `organizationId`   | `String?`            | -         | Organization                      |
| `searchVector`     | `tsvector?`          | -         | Full-text search                  |

**Unique:** `[ownerId, name]` **Relations:** `versions` (WorkflowVersion[]),
`jobs` (GenerationJob[]), `outputs` (GeneratedOutput[]), `stars`
(WorkflowStar[]) **Table:** `workflows`

### WorkflowVersion

Immutable snapshot of a workflow definition at a specific version point.

| Field          | Type       | Default | Description                  |
| -------------- | ---------- | ------- | ---------------------------- |
| `id`           | `String`   | auto    | Primary key                  |
| `workflowId`   | `String`   | -       | FK to Workflow               |
| `version`      | `String`   | -       | Semver string                |
| `changelog`    | `String?`  | -       | Version changelog            |
| `definition`   | `Json`     | -       | Workflow definition snapshot |
| `parameters`   | `Json`     | `[]`    | Parameters snapshot          |
| `requirements` | `Json`     | `{}`    | Requirements snapshot        |
| `deprecated`   | `Boolean`  | `false` | Whether deprecated           |
| `author`       | `String`   | -       | Version author               |
| `metadata`     | `Json`     | `{}`    | Metadata                     |
| `createdAt`    | `DateTime` | `now()` | Created                      |

**Unique:** `[workflowId, version]` **Table:** `workflow_versions`

### WorkflowStar

User star/like on a workflow (many-to-many between users and workflows).

**Unique:** `[workflowId, userId]` **Table:** `workflow_stars`

### WorkflowTemplate

Curated workflow templates for quick-start creation.

| Field          | Type               | Default | Description         |
| -------------- | ------------------ | ------- | ------------------- |
| `id`           | `String`           | auto    | Primary key         |
| `name`         | `String`           | -       | Template name       |
| `description`  | `String?`          | -       | Description         |
| `engine`       | `WorkflowEngine`   | -       | Engine type         |
| `category`     | `WorkflowCategory` | -       | Category            |
| `definition`   | `Json`             | -       | Workflow definition |
| `parameters`   | `Json`             | `[]`    | Default parameters  |
| `previewImage` | `String?`          | -       | Preview URL         |
| `tags`         | `String[]`         | `[]`    | Tags                |
| `featured`     | `Boolean`          | `false` | Whether featured    |
| `usageCount`   | `Int`              | `0`     | Usage count         |

**Table:** `workflow_templates`

### GeneratedOutput

Every file produced by a generation job. Tracks full storage details, media
dimensions, access counts, and participates in the lineage graph.

| Field            | Type             | Default   | Description                |
| ---------------- | ---------------- | --------- | -------------------------- |
| `id`             | `String`         | auto      | Primary key                |
| `name`           | `String`         | -         | Output filename            |
| `description`    | `String?`        | -         | Description                |
| `type`           | `OutputFileType` | -         | File type category         |
| `mimeType`       | `String`         | -         | MIME type                  |
| `size`           | `BigInt`         | -         | Size in bytes              |
| `hashAlgorithm`  | `HashAlgorithm`  | `SHA256`  | Hash algorithm             |
| `hashValue`      | `String`         | -         | File hash                  |
| `storageBucket`  | `String`         | -         | Object storage bucket      |
| `storageKey`     | `String`         | -         | Object storage key         |
| `storageTier`    | `StorageTier`    | `HOT`     | Storage tier               |
| `storageRegion`  | `String?`        | -         | Storage region             |
| `status`         | `OutputStatus`   | `PENDING` | Availability status        |
| `width`          | `Int?`           | -         | Width (pixels)             |
| `height`         | `Int?`           | -         | Height (pixels)            |
| `depth`          | `Int?`           | -         | Depth (for 3D)             |
| `duration`       | `Float?`         | -         | Duration (seconds, for AV) |
| `frameCount`     | `Int?`           | -         | Frame count (for video)    |
| `jobId`          | `String?`        | -         | FK to GenerationJob        |
| `workflowId`     | `String?`        | -         | FK to Workflow             |
| `ownerId`        | `String`         | -         | Owner user ID              |
| `organizationId` | `String?`        | -         | Organization               |
| `projectId`      | `String?`        | -         | Project reference          |
| `tags`           | `String[]`       | `[]`      | Tags                       |
| `metadata`       | `Json`           | `{}`      | Metadata                   |
| `accessCount`    | `Int`            | `0`       | Access counter             |
| `lastAccessedAt` | `DateTime?`      | -         | Last accessed              |

**Unique:** `[storageBucket, storageKey]` **Relations:** `job`, `workflow`,
`provenance`, `parentEdges` (LineageEdge[]), `childEdges` (LineageEdge[])
**Table:** `generated_outputs`

### LineageEdge

Directed edge in the output lineage graph. Tracks how one output was derived
from another (upscaled, composed, refined, etc.).

| Field            | Type              | Default | Description                  |
| ---------------- | ----------------- | ------- | ---------------------------- |
| `id`             | `String`          | auto    | Primary key                  |
| `sourceId`       | `String`          | -       | FK to source GeneratedOutput |
| `targetId`       | `String`          | -       | FK to target GeneratedOutput |
| `type`           | `LineageEdgeType` | -       | Edge type                    |
| `transformation` | `String?`         | -       | Transformation description   |
| `weight`         | `Float?`          | `1.0`   | Edge weight                  |

**Unique:** `[sourceId, targetId]` **Table:** `lineage_edges`

### Provenance

Full generation provenance for an output, linking the output to the job,
workflow, model, LoRAs, seed, and hardware used.

| Field                | Type       | Default | Description                 |
| -------------------- | ---------- | ------- | --------------------------- |
| `id`                 | `String`   | auto    | Primary key                 |
| `outputId`           | `String`   | -       | FK to GeneratedOutput (1:1) |
| `jobId`              | `String?`  | -       | FK to GenerationJob (1:1)   |
| `workflowInfo`       | `Json?`    | -       | {id, name, version}         |
| `modelInfo`          | `Json?`    | -       | {name, version, hash}       |
| `lorasUsed`          | `Json`     | `[]`    | [{name, version, weight}]   |
| `parameters`         | `Json`     | `{}`    | Generation parameters       |
| `inputs`             | `Json`     | `[]`    | [{outputId, url, type}]     |
| `seed`               | `Int?`     | -       | Random seed                 |
| `generatedAt`        | `DateTime` | `now()` | Generation timestamp        |
| `generationDuration` | `Int?`     | -       | Duration in ms              |
| `gpu`                | `String?`  | -       | GPU model                   |
| `provider`           | `String?`  | -       | Provider (cloud/local)      |
| `cost`               | `Float?`   | -       | Generation cost             |

**Table:** `provenance`

### ModelRegistry

Registry of AI models, checkpoints, LoRAs, VAEs, and embeddings used for
generation.

| Field               | Type               | Default  | Description               |
| ------------------- | ------------------ | -------- | ------------------------- |
| `id`                | `String`           | auto     | Primary key               |
| `name`              | `String`           | -        | Model name                |
| `description`       | `String?`          | -        | Description               |
| `type`              | `ModelType`        | -        | Model type                |
| `format`            | `ModelFormat`      | -        | File format               |
| `version`           | `String`           | -        | Version string            |
| `storagePath`       | `String`           | -        | Storage path              |
| `sizeBytes`         | `BigInt`           | -        | File size                 |
| `hashAlgorithm`     | `HashAlgorithm`    | `SHA256` | Hash algorithm            |
| `hashValue`         | `String`           | -        | File hash                 |
| `baseModel`         | `String?`          | -        | Base model identifier     |
| `trainedOn`         | `String?`          | -        | Training data description |
| `triggerWords`      | `String[]`         | `[]`     | Trigger words (for LoRA)  |
| `tags`              | `String[]`         | `[]`     | Tags                      |
| `metadata`          | `Json`             | `{}`     | Metadata                  |
| `compatibleEngines` | `WorkflowEngine[]` | `[]`     | Compatible engines        |
| `usageCount`        | `Int`              | `0`      | Usage count               |
| `downloadCount`     | `Int`              | `0`      | Download count            |
| `starCount`         | `Int`              | `0`      | Star count                |
| `uploaderId`        | `String`           | -        | Uploader user ID          |
| `organizationId`    | `String?`          | -        | Organization              |
| `isPublic`          | `Boolean`          | `false`  | Public visibility         |
| `isActive`          | `Boolean`          | `true`   | Active status             |
| `isVerified`        | `Boolean`          | `false`  | Verification status       |

**Unique:** `[name, version]` **Table:** `model_registry`

### RetentionPolicy

Defines data lifecycle policies that control how long generated outputs stay in
each storage tier before archival or deletion.

| Field                        | Type               | Default | Description               |
| ---------------------------- | ------------------ | ------- | ------------------------- |
| `id`                         | `String`           | auto    | Primary key               |
| `name`                       | `String`           | -       | Policy name (unique)      |
| `description`                | `String?`          | -       | Description               |
| `hotRetentionDays`           | `Int`              | `30`    | Days in hot storage       |
| `warmRetentionDays`          | `Int`              | `90`    | Days in warm storage      |
| `coldRetentionDays`          | `Int`              | `365`   | Days in cold storage      |
| `totalRetentionDays`         | `Int`              | `730`   | Total retention days      |
| `archiveAfterInactivityDays` | `Int?`             | -       | Auto-archive after N days |
| `outputTypes`                | `OutputFileType[]` | `[]`    | Applies to these types    |
| `tags`                       | `String[]`         | `[]`    | Filter by tags            |
| `isActive`                   | `Boolean`          | `true`  | Active status             |
| `organizationId`             | `String?`          | -       | Organization scope        |

**Table:** `retention_policies`

### GpuWorker

Registration record for GPU worker nodes that process generation jobs.

| Field                 | Type               | Default     | Description                |
| --------------------- | ------------------ | ----------- | -------------------------- |
| `id`                  | `String`           | auto        | Primary key                |
| `name`                | `String`           | -           | Worker name                |
| `hostname`            | `String`           | -           | Hostname (unique)          |
| `gpuType`             | `String`           | -           | GPU model                  |
| `gpuMemoryMb`         | `Int`              | -           | GPU VRAM in MB             |
| `gpuCount`            | `Int`              | `1`         | Number of GPUs             |
| `supportedTypes`      | `GenerationType[]` | `[]`        | Supported generation types |
| `supportedEngines`    | `WorkflowEngine[]` | `[]`        | Supported engines          |
| `maxConcurrentJobs`   | `Int`              | `1`         | Max concurrent jobs        |
| `status`              | `String`           | `"offline"` | Worker status              |
| `lastHeartbeat`       | `DateTime?`        | -           | Last heartbeat             |
| `currentJobId`        | `String?`          | -           | Current job ID             |
| `totalJobsProcessed`  | `Int`              | `0`         | Total jobs processed       |
| `totalGpuTimeSeconds` | `BigInt`           | `0`         | Total GPU seconds used     |
| `averageJobDuration`  | `Float?`           | -           | Average job duration       |
| `metadata`            | `Json`             | `{}`        | Metadata                   |

**Table:** `gpu_workers`

## Key Enums

### GenerationType

AI generation types: `TEXT_TO_IMAGE`, `IMAGE_TO_IMAGE`, `TEXT_TO_VIDEO`,
`IMAGE_TO_VIDEO`, `TEXT_TO_3D`, `IMAGE_TO_3D`, `TEXT_TO_AUDIO`,
`VOICE_SYNTHESIS`, `MUSIC_GENERATION`, `UPSCALING`, `INPAINTING`,
`BLENDER_RENDER`, `GAUSSIAN_SPLATTING`, `MESH_PROCESSING`, `TEXTURE_UPSCALE`

### WorkflowEngine

Workflow execution engines: `COMFYUI`, `BLENDER`, `UNREAL`, `GODOT`, `CUSTOM`

### WorkflowCategory

Workflow categories: `IMAGE_GENERATION`, `VIDEO_GENERATION`, `AUDIO_GENERATION`,
`GENERATION_3D`, `UPSCALING`, `STYLE_TRANSFER`, `INPAINTING`, `COMPOSITING`,
`RENDERING`, `SIMULATION`, `UTILITY`, `OTHER`

### ModelType

AI model types: `CHECKPOINT`, `LORA`, `CONTROLNET`, `VAE`, `EMBEDDING`,
`UPSCALER`, `CUSTOM`

### ModelFormat

Model file formats: `SAFETENSORS`, `PYTORCH`, `ONNX`, `GGUF`, `BLEND`, `GLTF`,
`FBX`, `OTHER`

### OutputFileType

Output categories: `IMAGE`, `VIDEO`, `AUDIO`, `MODEL_3D`, `POINT_CLOUD`,
`ANIMATION`, `TEXTURE`, `MATERIAL`, `DOCUMENT`, `DATA`, `OTHER`

### StorageTier

Storage tiers for lifecycle management: `HOT` (SSD fast access), `WARM`
(standard), `COLD` (archive), `GLACIER` (deep archive)

### LineageEdgeType

Output derivation types: `DERIVED_FROM`, `COMPOSED_OF`, `REFINED_FROM`,
`UPSCALED_FROM`, `CONVERTED_FROM`

### HashAlgorithm

Checksum algorithms: `SHA256`, `SHA512`, `MD5`, `BLAKE3`
