Prisma Database Schema
Relevant source files
The following files were used as context for generating this wiki page:
The Prisma database schema defines the data persistence layer for the Opificia AI factory. It manages the core entities required to track AI agent activities, including issue processing, implementation plans, and execution events. The system uses PostgreSQL as the primary database, integrated through the Prisma ORM to provide type-safe database access for the NestJS server.
This schema supports the "foundations" and "first working loop" phases of the project. It enables the storage of run lifecycles, audit logs, and sovereignty-related data, ensuring that all agent actions—from ticket pickup to pull request opening—are recorded and verifiable.
Sources: AGENTS.md:12-18, ROADMAP.md:12-28
Database Architecture and Stack
The Opificia backend utilizes a modern TypeScript stack centered around NestJS and Prisma. The database layer is designed for self-hosting and air-gap capability, adhering to the project's sovereignty commitments.
- Database Engine: PostgreSQL.
- ORM: Prisma with
@prisma/clientand@prisma/adapter-pg. - Integration: NestJS provides the application framework, while Valkey handles caching and Better Auth manages identity.
The diagram shows the server-side stack where Prisma facilitates communication between the application logic and the PostgreSQL database.
Sources: AGENTS.md:14-18, apps/server/package.json:20-24
Data Models and Entities
The schema manages the lifecycle of a "Run," which represents a single agentic loop triggered by a ticket or issue. While the raw .prisma file was not provided, the following entities and structures are evidenced through the application's data transfer objects (DTOs), fixtures, and repository scripts.
The Run Lifecycle
A Run is the central entity that tracks the progress of an AI agent turning a ticket into a pull request.
| Field | Type | Description |
|---|---|---|
id | UUID | Unique identifier for the run. |
repo | String | The full name of the GitHub repository (e.g., opificia/factory). |
issueNumber | Integer | The number of the issue being addressed. |
state | Enum | The current phase: planning, awaiting_approval, implementing, pr_opened, failed, or cancelled. |
costUsd | Float | The accumulated cost of LLM tokens used during the run. |
prUrl | String? | The URL of the resulting pull request, if opened. |
plan | String? | The Markdown implementation plan proposed by the agent. |
Sources: apps/web/fixtures/plugin.ts:98-115, apps/web/src/run/conversation.tsx:280-310
Event Streaming and Audit Logs
The system records every discrete action as a RunEventDto. These events form a granular audit log of the agent's behavior, fulfilling the sovereignty commitment for human control and transparency.
The ER diagram illustrates the one-to-many relationship between a Run and its constituent Events.
Sources: apps/web/fixtures/runs.ts:11-14, SOVEREIGNTY.md:60-64
Event Types
The database stores various event types that represent stage transitions and agent tool usage:
run.queued: Initial entry of the run into the system.stage.started: Transition into a specific stage (e.g.,plan,implement).agent.tool: Recording of specific tool calls (e.g.,Read,Bash,Edit,network).agent.message: Agent output intended for the human reviewer.plan.proposed/plan.approved: Lifecycle events for the implementation plan.checks.completed: Results of security and hygiene checks (findings, additions, deletions).
Sources: apps/web/fixtures/runs.ts:24-118, apps/web/src/run/conversation.tsx:64-74
Schema Operations
Database schema changes are managed strictly through Prisma migrations. The server package provides specific scripts to handle deployment and development.
# Generate Prisma client
pnpm db:generate
# Create and apply migrations in development
pnpm db:migrate
# Deploy migrations in production
pnpm db:deploy
Sources: AGENTS.md:64-65, apps/server/package.json:10-12
Relationship to Sovereignty Commitments
The database schema is structured to support the project's core commitments:
- Humans Decide: By storing
plan.proposedandplan.approvedevents, the schema ensures no code is written without a recorded human approval in the database. - Your Data Stays Yours: The schema is designed for local PostgreSQL instances, ensuring logs and traces are stored only where the operator configures.
- Transparent Supply Chain: The
checks.completedevents store findings related to protected paths and secrets, providing a record of the agent's compliance with safety rules.
Sources: SOVEREIGNTY.md:38-64, apps/web/src/run/conversation.tsx:238-255
The Prisma Database Schema serves as the reliable source of truth for the software factory, enabling the "loop" where AI agents and human reviewers collaborate within a sovereign, audited environment.