Eve library

Sophia Database Schema

Guide

Revision 1

On this page

Sophia is the Research and Knowledge domain. Its database stores research documents with full bibliographic metadata, document chunks for vector indexing, citations with verification tracking, a knowledge graph (entities and relations), curated knowledge packs, and ingestion job pipelines.

Database: PostgreSQL ORM: Prisma Schema Location: libs/sophia/database/prisma/schema.prisma Environment Variable: SOPHIA_DATABASE_URL Generated Client: libs/sophia/database/src/generated/client

ER Diagram#

text
+----------------------------+
|         Document           |
|----------------------------|
| id (PK)                    |
| title                      |
| originalTitle?             |
| type                       |
| genre?                     |
| status                     |
| author?                    |
| authors (JSON)             |
| publisher?, isbn?, doi?    |
| language                   |
| tradition?                 |
| rightsStatus               |
| sourceUri?                 |
| pageCount?, wordCount?     |
| chunkCount                 |
| qualityScore?              |
| ownerId                    |
| organizationId?            |
| projectId?                 |
| searchVector (tsvector)    |
+------+------+---------+---+
       |      |         |
       |1     |1        |1
       |      |         |
       |*     |*        |*
+------+--+ +-+-------+ +--------+--------+
|DocChunk | |Citation  | | EntityMention    |
|---------| |----------| |------------------|
| id (PK) | | id (PK)  | | id (PK)          |
| docId   | | docId    | | entityId (FK)    |
| index   | | chunkId? | | documentId (FK)  |
| type    | | citedText| | chunkId? (FK)    |
| text    | | style    | | startOffset      |
| start   | | confid.  | | endOffset        |
| end     | | verified | | text             |
| page?   | | ownerId  | | confidence       |
| section?| +----------+ +--------+---------+
| embed?  |                       |
| overlap |                       |
+---------+                       |
                                  |*
                           +------+------+        +-------------------+
                           |   Entity    |1------*| KnowledgePackEntity|
                           |-------------|        |-------------------|
                           | id (PK)     |        | id (PK)           |
                           | type        |        | packId (FK)       |
                           | name        |        | entityId (FK)     |
                           | aliases[]   |        | importance        |
                           | description?|        +-------------------+
                           | language    |
                           | tradition?  |        +-------------------+
                           | confidence  |        |  KnowledgePack    |
                           | verified    |        |-------------------|
                           | mentionCnt  |        | id (PK)           |
                           | relationCnt |        | name              |
                           | canonicalId?|        | slug              |
                           | ownerId     |        | category          |
                           | searchVector|        | status            |
                           +------+------+        | visibility        |
                                  |               | traditions[]      |
                                  |1              | docCount          |
                                  |               | entityCount       |
                                  |*              | ownerId           |
                           +------+------+        +-------+-----------+
                           |  Relation   |                |
                           |-------------|                |1
                           | id (PK)     |                |
                           | type        |                |*
                           | sourceId FK | ------>  +-----+---------------+
                           | targetId FK | ------>  | KnowledgePackDocument|
                           | weight      |          |---------------------|
                           | bidirection |          | id (PK)             |
                           | confidence  |          | packId (FK)         |
                           | evidenceDoc?|          | documentId (FK)     |
                           | ownerId     |          | order               |
                           +-------------+          +---------------------+

+-------------------+       +-------------------+       +-------------------+
|  IngestionJob     |       | KnowledgePackStar |       |    AuditLog       |
|-------------------|       |-------------------|       |-------------------|
| id (PK)           |       | id (PK)           |       | id (PK)           |
| status            |       | packId (FK)       |       | action            |
| sourceType        |       | userId (UQ w/pack)|       | entityType        |
| sourceUri         |       +-------------------+       | entityId          |
| documentId? (FK)  |                                   | previousState?    |
| config            |                                   | newState?         |
| chunksCreated?    |                                   | userId?           |
| entitiesExtracted?|                                   | createdAt         |
| ownerId           |                                   +-------------------+
+-------------------+

Models#

Document#

The central research document model with comprehensive bibliographic metadata, multi-language support, tradition classification, and full-text search.

Field Type Default Description
id String (cuid) auto Primary key
title String - Document title
originalTitle String? - Title in original language
type DocumentType OTHER Document type
genre DocumentGenre? - Genre classification
status DocumentStatus DRAFT Processing pipeline status
author String? - Primary author
authors Json [] All authors [{name, role}]
editor String? - Editor
translator String? - Translator
publisher String? - Publisher
publicationPlace String? - Publication location
publicationYear Int? - Publication year
edition String? - Edition
volume String? - Volume
issue String? - Issue
pages String? - Page range
isbn String? - ISBN
issn String? - ISSN
doi String? - DOI
url String? - URL
externalIds Json {} External identifiers
language String "en" Primary language
languages String[] [] All languages
originRegion String? - Region of origin
abstract String? - Abstract
summary String? - AI-generated summary
keyPoints Json [] Key points
keywords String[] [] Keywords
subjects String[] [] Subject classifications
tags String[] [] Tags
tradition String? - Primary spiritual tradition
traditions String[] [] All traditions
category String? - Category
compositionYear Int? - Year of original composition
compositionEra String? - Era (BCE/CE)
dateRange Json? - Date range {from, to}
rightsStatus RightsStatus UNKNOWN Copyright status
rightsHolder String? - Rights holder
license String? - License
sourceUri String? - Source file URI
sourceType SourceType? - Source type
fileFormat String? - File format
fileSize BigInt? - File size
checksum String? - File checksum
mimeType String? - MIME type
pageCount Int? - Page count
wordCount Int? - Word count
tokenCount Int? - Token count
chunkCount Int 0 Number of chunks
processingTimeMs Int? - Processing time in ms
processingError String? - Processing error
qualityScore Float? - Quality score
completeness Float? - Completeness score
accuracy Float? - Accuracy score
metadata Json {} Arbitrary metadata
ownerId String - Owner user ID
organizationId String? - Organization
projectId String? - Project reference
searchVector tsvector? - Full-text search

Unique: [ownerId, title, author] Relations: chunks (DocumentChunk[]), citations (Citation[]), entityMentions (EntityMention[]), ingestionJobs (IngestionJob[]), packDocuments (KnowledgePackDocument[]) Table: documents

DocumentChunk#

Text chunks of a document, used for vector indexing and retrieval. Each chunk includes position information, embeddings, overlap context, and quality scores.

Field Type Default Description
id String auto Primary key
documentId String - FK to Document
index Int - Chunk index within document
type ChunkType PARAGRAPH Chunk type
text String - Chunk text content
startOffset Int - Start character offset
endOffset Int - End character offset
pageNumber Int? - Source page number
sectionTitle String? - Section title
sectionIndex Int? - Section index
paragraphIndex Int? - Paragraph index
tokenCount Int? - Token count
charCount Int? - Character count
embeddingModel String? - Embedding model used
embeddingDimension Int? - Embedding vector dimension
embeddingVector Bytes? - Binary embedding vector
embeddingGeneratedAt DateTime? - When embedding was generated
overlapBefore Int 0 Overlap chars from previous
overlapAfter Int 0 Overlap chars into next
previousChunkId String? - Previous chunk ID
nextChunkId String? - Next chunk ID
context Json? - Contextual metadata
language String? - Chunk language
metadata Json {} Metadata
qualityScore Float? - Quality score

Unique: [documentId, index] Relations: document, entityMentions, citationSources Table: document_chunks

Citation#

Citation records linking claims to source documents with confidence scoring and multi-style formatting.

Field Type Default Description
id String auto Primary key
documentId String - FK to Document
chunkId String? - FK to DocumentChunk
citedText String - The cited text
citationStyle CitationStyle CHICAGO Formatting style
formattedCitation String? - Pre-formatted citation
startOffset Int? - Start offset in source
endOffset Int? - End offset in source
pageNumber Int? - Page number
sourceTitle String? - Source document title
sourceAuthor String? - Source author
sourceYear Int? - Source year
sourceUrl String? - Source URL
sourceIdentifier String? - External identifier
confidence Float 0 Confidence score (0-1)
verificationStatus VerificationStatus UNVERIFIED Verification state
verifiedAt DateTime? - Verification timestamp
verifiedBy String? - Verifier user ID
claimText String? - Associated claim text
claimType String? - Claim type
isInline Boolean true Inline vs bibliography
bibliographyOrder Int? - Order in bibliography
noteType String? - Footnote/endnote type
noteMarker String? - Note marker
noteContent String? - Note content
ownerId String - Owner user ID
projectId String? - Project reference

Table: citations

Entity (Knowledge Graph Node)#

Entities form the nodes of the knowledge graph. Each entity has a type, confidence level, tradition classification, and can be merged with duplicate entities via canonicalId.

Field Type Default Description
id String auto Primary key
type EntityType - Entity type
name String - Entity name
originalName String? - Name in original language
aliases String[] [] Alternative names
description String? - Description
summary String? - Summary
language String "en" Primary language
properties Json {} Type-specific properties
tradition String? - Primary tradition
traditions String[] [] All traditions
category String? - Category
tags String[] [] Tags
labels String[] [] Labels
confidence EntityConfidence PROBABLE Confidence level
verificationStatus VerificationStatus UNVERIFIED Verification state
wikipediaUrl String? - Wikipedia URL
externalIds Json {} External identifiers
mentionCount Int 0 Mention count
relationCount Int 0 Relation count
referenceCount Int 0 Reference count
canonicalId String? - Canonical entity (for merges)
mergedInto String? - Merged into entity ID
mergedAt DateTime? - Merge timestamp
ownerId String - Owner user ID
organizationId String? - Organization
projectId String? - Project reference
searchVector tsvector? - Full-text search

Unique: [ownerId, type, name] Relations: outgoingRelations, incomingRelations, mentions, packEntities Table: entities

EntityMention#

Records where an entity is mentioned in a document, with position tracking and confidence scoring.

Field Type Default Description
id String auto Primary key
entityId String - FK to Entity
documentId String - FK to Document
chunkId String? - FK to DocumentChunk
startOffset Int - Start offset
endOffset Int - End offset
text String - Mentioned text
contextBefore String? - Text before mention
contextAfter String? - Text after mention
confidence Float 0 Detection confidence
verified Boolean false Human-verified

Table: entity_mentions

Relation (Knowledge Graph Edge)#

Directed edges in the knowledge graph connecting entities, with evidence tracking and confidence scoring.

Field Type Default Description
id String auto Primary key
type RelationType - Relation type
customType String? - Custom type label
sourceId String - FK to source Entity
targetId String - FK to target Entity
weight Float 1.0 Relation weight
bidirectional Boolean false Bidirectional flag
properties Json {} Type-specific properties
confidence Float 0 Confidence score
verificationStatus VerificationStatus UNVERIFIED Verification state
evidenceDocumentId String? - Evidence source document
evidenceChunkId String? - Evidence source chunk
evidenceText String? - Evidence text
evidenceType String? - Evidence type
context String? - Contextual note
temporalContext String? - Temporal context
spatialContext String? - Spatial context
labels String[] [] Labels
ownerId String - Owner user ID
projectId String? - Project reference

Unique: [sourceId, targetId, type] Table: relations

KnowledgePack#

Curated collections of documents and entities, organized by tradition, period, or topic. Knowledge packs can be published, shared, and tracked with usage statistics.

Field Type Default Description
id String auto Primary key
name String - Pack name
slug String - URL slug
description String? - Description
summary String? - Summary
category KnowledgePackCategory - Category
status KnowledgePackStatus DRAFT Lifecycle status
visibility KnowledgePackVisibility PRIVATE Access level
traditions String[] [] Traditions covered
timePeriod String? - Time period scope
regions String[] [] Geographic regions
topics String[] [] Topics
tags String[] [] Tags
coverage Json {} Coverage metadata
version String "1.0.0" Version
previousVersionId String? - Previous version
author String - Author name
contributors Json [] Contributors [{name, role}]
documentCount Int 0 Document count
entityCount Int 0 Entity count
relationCount Int 0 Relation count
citationCount Int 0 Citation count
chunkCount Int 0 Chunk count
totalTokens Int 0 Total tokens
viewCount Int 0 Views
downloadCount Int 0 Downloads
queryCount Int 0 Queries
starCount Int 0 Stars
qualityTier String? - Quality tier
qualityScore Float? - Quality score
completeness Float? - Completeness score
indexId String? - Vector index ID
indexStatus String? - Index status
lastIndexedAt DateTime? - Last indexed timestamp
ownerId String - Owner user ID
organizationId String? - Organization
searchVector tsvector? - Full-text search

Unique: [ownerId, slug] Relations: documents (KnowledgePackDocument[]), entities (KnowledgePackEntity[]), stars (KnowledgePackStar[]) Table: knowledge_packs

IngestionJob#

Document ingestion pipeline job with multi-stage processing.

Field Type Default Description
id String auto Primary key
status IngestionJobStatus PENDING Job status
progress Int 0 Progress percentage
currentStage String? - Current processing stage
sourceType SourceType - Source type
sourceUri String - Source URI
sourceCredentials Json? - Encrypted credentials
documentId String? - FK to Document
config Json {} Job configuration
extractionConfig Json? - Extraction config
enrichmentConfig Json? - Enrichment config
chunkingConfig Json? - Chunking config
priority Int 0 Priority
workerId String? - Worker assignment
retryCount Int 0 Retry count
maxRetries Int 3 Max retries
result Json? - Processing result
chunksCreated Int? - Chunks created
tokensProcessed Int? - Tokens processed
entitiesExtracted Int? - Entities extracted
relationsExtracted Int? - Relations extracted
errorCode String? - Error code
errorMessage String? - Error message
errorStage String? - Stage where error occurred
ownerId String - Owner user ID
organizationId String? - Organization
projectId String? - Project reference

Table: ingestion_jobs

Key Enums#

EntityType#

Knowledge graph node types: TRADITION, TEXT, CONCEPT, PRACTICE, FIGURE, PLACE, SCHOOL, DOCTRINE, EVENT, TERM, SYMBOL, RITUAL, ARTIFACT, ORGANIZATION, CUSTOM

RelationType#

Knowledge graph edge types organized into categories:

Teaching/Learning: TAUGHT, STUDIED_WITH, INFLUENCED Authorship: WROTE, EDITED, TRANSLATED Membership: FOUNDED, MEMBER_OF, BELONGS_TO Location: LIVED_IN, BORN_IN, DIED_IN, LOCATED_IN Temporal: PRECEDED, SUCCEEDED, CONTEMPORARY_WITH Reference: REFERENCES, QUOTES, CITES Conceptual: RELATED_TO, SIMILAR_TO, OPPOSITE_OF, DERIVED_FROM, PART_OF Practice: PRACTICES, PRESCRIBES Other: CUSTOM

DocumentType#

Document classifications: BOOK, ARTICLE, THESIS, MANUSCRIPT, SCRIPTURE, COMMENTARY, ESSAY, LETTER, SPEECH, TRANSLATION, ANTHOLOGY, ENCYCLOPEDIA, DICTIONARY, OTHER

DocumentStatus#

Processing pipeline: DRAFT -> INGESTING -> EXTRACTING -> ENRICHING -> CHUNKING -> INDEXING -> PUBLISHED | FAILED | ARCHIVED | DELETED

EntityConfidence#

Confidence levels: CERTAIN, PROBABLE, POSSIBLE, UNCERTAIN, INFERRED

VerificationStatus#

Human verification: VERIFIED, REVIEWED, UNVERIFIED, DISPUTED, PENDING

KnowledgePackCategory#

Pack categories: TRADITION, PERIOD, TOPIC, FIGURE, TEXT, REGION, PRACTICE, CUSTOM

IngestionJobStatus#

Ingestion pipeline: PENDING -> QUEUED -> FETCHING -> EXTRACTING -> ENRICHING -> CHUNKING -> INDEXING -> COMPLETED | FAILED | CANCELLED

CitationStyle#

Citation formatting: CHICAGO, MLA, APA, HARVARD, TURABIAN, CUSTOM

RightsStatus#

Copyright: PUBLIC_DOMAIN, CREATIVE_COMMONS, RESTRICTED, UNKNOWN

SourceType#

Ingestion source types: FILE, URL, API, DATABASE, STREAM, ARCHIVE