Database Schema Documentation¶

This document describes the EIAS database schema, entity relationships, and data model design decisions.

Overview¶

EIAS uses PostgreSQL with Prisma ORM. The schema supports: - Multi-tenant project management - Expert interview sessions with conversation history - VOI-based knowledge state tracking - Expert calibration and competence scoring - Insight extraction and review workflow

Entity Relationship Diagram¶

┌─────────────────────────────────────────────────────────────────────────────────────┐
│                              EIAS Database Schema                                   │
├─────────────────────────────────────────────────────────────────────────────────────┤
│                                                                                     │
│  ┌──────────┐         ┌─────────────────┐        ┌──────────────────┐             │
│  │   User   │────────▶│     Project     │◀───────│  SubQuestion     │             │
│  └──────────┘  owns   └─────────────────┘ has    └──────────────────┘             │
│       │                       │                          │                         │
│       │                       │ has                      │ addresses               │
│       │                       ▼                          ▼                         │
│       │              ┌─────────────────┐        ┌──────────────────┐             │
│       │              │ UncertaintyArea │        │FindingSubQuestion│             │
│       │              └─────────────────┘        └──────────────────┘             │
│       │                       │                          │                         │
│       │                       │ focuses                  │                         │
│       │                       ▼                          ▼                         │
│       │              ┌─────────────────┐        ┌──────────────────┐             │
│       │              │ InterviewAgent  │        │     Finding      │◀────────┐   │
│       │              └─────────────────┘        └──────────────────┘         │   │
│       │                       │                          │                   │   │
│       │                       │ has                      │ extracted from    │   │
│       │                       ▼                          ▼                   │   │
│       │              ┌─────────────────┐        ┌──────────────────┐        │   │
│       │              │   Invitation    │───────▶│ InterviewSession │────────┘   │
│       │              └─────────────────┘ starts └──────────────────┘             │
│       │                                                  │                         │
│       │                                                  │ has                     │
│       │                                                  ▼                         │
│       │                                         ┌──────────────────┐             │
│       │                                         │     Message      │             │
│       │                                         └──────────────────┘             │
│       │                                                  │                         │
│       │                                                  │ yields                  │
│       │                                                  ▼                         │
│       │         approves                        ┌──────────────────┐             │
│       └────────────────────────────────────────▶│     Insight      │             │
│                                                 └──────────────────┘             │
│                                                          │                         │
│                                                          │ becomes                 │
│                                                          ▼                         │
│                                                 ┌──────────────────┐             │
│                                                 │IntegratedKnowledge│            │
│                                                 └──────────────────┘             │
│                                                                                     │
│  ┌──────────────────────────────────────────────────────────────────────────────┐  │
│  │                        VOI Knowledge State Entities                          │  │
│  │  ┌──────────────────┐    ┌──────────────────┐    ┌──────────────────┐       │  │
│  │  │   Contradiction  │    │ ExpertCompetence │    │  GoldenQuestion  │       │  │
│  │  └──────────────────┘    └──────────────────┘    └──────────────────┘       │  │
│  │          │                                               │                   │  │
│  │          │                                               ▼                   │  │
│  │          │                                       ┌──────────────────┐       │  │
│  │          │                                       │GoldenQResponse   │       │  │
│  │          │                                       └──────────────────┘       │  │
│  └──────────────────────────────────────────────────────────────────────────────┘  │
└─────────────────────────────────────────────────────────────────────────────────────┘

Core Entities¶

User¶

Authenticated researchers who manage projects.

Field	Type	Description
`id`	UUID	Primary key
`email`	String	Unique email address
`name`	String?	Display name
`emailVerified`	DateTime?	When email was verified (NextAuth.js)
`image`	String?	Profile image URL

Relations: - Has many Projects (as owner) - Has many IntegratedKnowledge (as approver)

Project¶

Research project containing uncertainty areas and interview agents.

Field	Type	Description
`id`	UUID	Primary key
`name`	String	Project name
`description`	String	Research question/description
`goals`	String[]	Array of project goals
`status`	Enum	active, paused, completed, archived
`ownerId`	UUID	Foreign key to User

Relations: - Belongs to User (owner) - Has many UncertaintyAreas - Has many InterviewAgents - Has many SubQuestions - Has many Contradictions

UncertaintyArea¶

Knowledge gaps that the project aims to address.

Field	Type	Description
`id`	UUID	Primary key
`title`	String	Brief title
`description`	String	Detailed description
`priority`	Enum	high, medium, low
`status`	Enum	open, partially_addressed, resolved
`questions`	String[]	Specific questions to answer
`projectId`	UUID	Foreign key to Project

Relations: - Belongs to Project - Many-to-many with InterviewAgent via AgentFocusArea

InterviewAgent¶

AI interview agent configuration.

Field	Type	Description
`id`	UUID	Primary key
`name`	String	Agent name
`systemPrompt`	String	Generated system prompt
`style`	Enum	exploratory, focused, validating
`timeEstimateMinutes`	Int	Expected interview duration
`status`	Enum	draft, active, paused, retired
`topicPriorities`	JSON?	Interview Protocol Framework config
`guardrails`	JSON?	Topic constraints and time allocation
`interviewRules`	JSON?	Probing depth and follow-up style
`completionCriteria`	JSON?	When to consider interview complete
`projectId`	UUID	Foreign key to Project

Relations: - Belongs to Project - Has many Invitations - Many-to-many with UncertaintyArea via AgentFocusArea

Invitation¶

Secure link for experts to access interviews.

Field	Type	Description
`id`	UUID	Primary key
`expertName`	String	Invited expert's name
`expertRole`	String	Expert's role/title
`expertEmail`	String?	Expert's email (optional)
`token`	String	Unique access token
`expiresAt`	DateTime	Token expiration
`status`	Enum	pending, opened, in_progress, completed, expired
`agentId`	UUID	Foreign key to InterviewAgent

Relations: - Belongs to InterviewAgent - Has one InterviewSession

InterviewSession¶

Active or completed interview session.

Field	Type	Description
`id`	UUID	Primary key
`status`	Enum	active, paused, completed, abandoned
`startedAt`	DateTime	When interview started
`completedAt`	DateTime?	When interview completed
`turnCount`	Int	Number of conversation turns
`expertCorrections`	String?	Expert's corrections/clarifications
`expertRating`	Int?	Expert's rating (1-5)
`analytics`	JSON?	Interview analytics data
`progressPercent`	Int?	Completion progress (0-100)
`invitationId`	UUID	Foreign key to Invitation

Relations: - Belongs to Invitation - Has many Messages - Has many Insights - Has many Findings

Message¶

Individual conversation message.

Field	Type	Description
`id`	UUID	Primary key
`role`	Enum	assistant, user
`content`	String	Message text
`timestamp`	DateTime	When message was sent
`metadata`	JSON?	Additional metadata
`qualityFlags`	JSON?	Question quality analysis
`sessionId`	UUID	Foreign key to InterviewSession

Relations: - Belongs to InterviewSession - Many-to-many with Insight via InsightSourceMessage

VOI Knowledge State Entities¶

SubQuestion¶

Atomic component of the research question for VOI tracking.

Field	Type	Description
`id`	UUID	Primary key
`projectId`	UUID	Foreign key to Project
`title`	String	Brief title
`description`	String?	Detailed description
`status`	Enum	uncovered, partial, answered
`confidence`	Float	Confidence score (0.0-1.0)
`currentAnswer`	String?	Provisional answer
`dependencies`	String[]	IDs of prerequisite sub-questions
`priority`	Enum	high, medium, low

Relations: - Belongs to Project - Many-to-many with Finding via FindingSubQuestion

Finding¶

Information extracted from expert responses.

Field	Type	Description
`id`	UUID	Primary key
`content`	String	Full finding content
`summary`	String	Brief summary
`confidence`	Float	Confidence score (0.0-1.0)
`verbatimExcerpt`	String?	Direct quote from expert
`expertId`	String	Reference to expert
`sessionId`	UUID	Foreign key to InterviewSession
`turnNumber`	Int	Which conversation turn

Relations: - Belongs to InterviewSession - Many-to-many with SubQuestion via FindingSubQuestion - Has many Contradictions (as findingA or findingB)

Contradiction¶

Conflicting information between findings.

Field	Type	Description
`id`	UUID	Primary key
`projectId`	UUID	Foreign key to Project
`description`	String	Description of the conflict
`nature`	String?	factual, interpretive, scope-dependent
`status`	Enum	unresolved, resolved, acknowledged
`resolution`	String?	How it was resolved
`findingAId`	UUID	First conflicting finding
`findingBId`	UUID	Second conflicting finding

Relations: - Belongs to Project - References two Findings

ExpertCompetence¶

Calibration scores for experts (Cooke's Classical Model).

Field	Type	Description
`id`	UUID	Primary key
`expertId`	String	Unique expert identifier
`expertName`	String?	Expert's name
`expertRole`	String?	Expert's role
`statisticalAccuracy`	Float	Calibration score (0.0-1.0)
`informativeness`	Float	Entropy measure (0.0-1.0)
`competenceScore`	Float	accuracy × informativeness
`calibrationCount`	Int	Number of calibration questions answered
`lastCalibratedAt`	DateTime?	Last calibration time
`domains`	String[]	Areas of expertise

GoldenQuestion¶

Calibration questions with known answers.

Field	Type	Description
`id`	UUID	Primary key
`projectId`	UUID?	Optional project scope
`question`	String	The calibration question
`knownAnswer`	String	Verified correct answer
`domain`	String?	Domain/area covered
`difficulty`	String?	easy, medium, hard
`source`	String?	Answer source
`isActive`	Boolean	Whether question is active

Relations: - Optionally belongs to Project - Has many GoldenQuestionResponses

Insight & Knowledge Integration¶

Insight¶

Extracted insight pending review.

Field	Type	Description
`id`	UUID	Primary key
`content`	String	Insight content
`category`	String	factual_claim, expert_opinion, etc.
`confidence`	Enum	high, medium, low
`status`	Enum	pending_review, integrated, discarded, review_later
`reviewerNotes`	String?	Reviewer's notes
`sessionId`	UUID	Foreign key to InterviewSession

Relations: - Belongs to InterviewSession - Many-to-many with Message via InsightSourceMessage - Has one IntegratedKnowledge

IntegratedKnowledge¶

Approved insights added to project knowledge base.

Field	Type	Description
`id`	UUID	Primary key
`content`	String	Approved content
`sourceExpert`	String	Expert who provided the insight
`approvedAt`	DateTime	When approved
`projectId`	UUID	Foreign key to Project
`insightId`	UUID	Foreign key to Insight
`approvedById`	UUID	Foreign key to User

Relations: - Belongs to Project - Belongs to Insight - Belongs to User (approver)

Enums¶

Project & Agent Status¶

enum ProjectStatus {
  active
  paused
  completed
  archived
}

enum AgentStatus {
  draft
  active
  paused
  retired
}

Interview Status¶

enum InvitationStatus {
  pending
  opened
  in_progress
  completed
  expired
}

enum SessionStatus {
  active
  paused
  completed
  abandoned
}

VOI Status¶

enum SubQuestionStatus {
  uncovered   // No evidence yet
  partial     // Some evidence but incomplete
  answered    // Sufficient evidence
}

enum ContradictionStatus {
  unresolved   // Active conflict
  resolved     // Resolved with explanation
  acknowledged // Acknowledged but not resolved
}

Insight Categories¶

enum InsightStatus {
  pending_review
  integrated
  discarded
  review_later
}

// Categories (stored as String)
// factual_claim, expert_opinion, process_description,
// recommendation, warning, opportunity, constraint

Database Operations¶

Common Queries¶

Get project with knowledge state:

href="#__codelineno-5-1">const project = await prisma.project.findUnique({ where: { id: projectId }, include: { subQuestions: true, contradictions: true, agents: { include: { invitations: { include: { session: { include: { findings: true, }, }, }, }, }, }, }, });

Get interview session with messages:

const session = await prisma.interviewSession.findUnique({
  where: { id: sessionId },
  include: {
    messages: {
      orderBy: { timestamp: 'asc' },
    },
    invitation: {
      include: {
        agent: true,
      },
    },
  },
});

Migrations¶

# Create a new migration
npm run db:migrate -- --name add_new_field

# Push schema changes (development)
npm run db:push

# Reset database (caution: deletes all data)
npm run db:reset

# Open Prisma Studio
npm run db:studio

Indexes¶

The schema includes indexes for common query patterns:

Table	Index	Purpose
Project	`ownerId`, `status`	Filter projects by owner/status
UncertaintyArea	`projectId`, `priority`	List areas by project
Invitation	`token`, `agentId`, `status`	Token lookup, list by agent
Message	`sessionId`, `timestamp`	Load conversation in order
SubQuestion	`projectId`, `status`, `priority`	VOI queries
Finding	`sessionId`, `expertId`, `confidence`	Finding queries

Data Integrity¶

Cascade Deletes¶

Most relations use onDelete: Cascade: - Deleting a Project deletes all related UncertaintyAreas, Agents, SubQuestions - Deleting an InterviewAgent deletes all Invitations - Deleting an InterviewSession deletes all Messages, Insights, Findings

Unique Constraints¶

User.email
Invitation.token
ExpertCompetence.expertId
[agentId, uncertaintyId] on AgentFocusArea
[findingId, subQuestionId] on FindingSubQuestion

Architecture - System overview
VOI System - Knowledge state and VOI calculation
API Reference - API endpoints for data access