Sibyla Database — Entity Relationship Diagram

erDiagram
    %% ============================================================
    %% Sibyla database ER diagram
    %% Generated from src/Sibyla.Infrastructure/Persistence/Migrations/SibylaDbContextModelSnapshot.cs
    %% (EF Core model snapshot, latest migration: 20260723190000_MoloniLedgerConcurrency)
    %% ============================================================

    %% ---------- Identity & Users ----------
    BusinessEntity {
        uuid Id PK
        string Code UK
        string CountryCode
        timestamptz CreatedAt
        string CreatedBy
        string LegalName
        uuid MergedIntoEntityId FK
        string NormalizedLegalName
        string Origin
        string Status
        timestamptz UpdatedAt
        string UpdatedBy
    }

    BusinessEntityExtractionProfile {
        uuid Id PK
        uuid BusinessEntityId FK,UK
        string Hint
        timestamptz CreatedAt
        string CreatedBy
        string CreationReason
        timestamptz UpdatedAt
        string UpdatedBy
        string UpdateReason
    }

    Department {
        uuid Id PK
        uuid InternalCompanyEntityId FK
        string Code
        timestamptz CreatedAt
        bool IsActive
        string Name
    }

    EntityRoleAssignment {
        uuid Id PK
        timestamptz AssignedAt
        string AssignedBy
        uuid BusinessEntityId FK
        string Role
        string Status
        string VerificationStatus
        timestamptz VerifiedAt
        string VerifiedBy
    }

    EntityTaxIdentifier {
        uuid Id PK
        uuid BusinessEntityId FK
        string CountryCode
        timestamptz CreatedAt
        string CreatedBy
        string IdentifierType
        bool IsPrimary
        string NormalizedValue
        string OriginalValue
    }

    UserProfile {
        uuid Id PK
        timestamptz CreatedAt
        string DisplayName
        string Email
        string ExternalSubjectId UK
        bool IsActive
    }

    Membership {
        uuid Id PK
        uuid InternalCompanyEntityId FK
        timestamptz CreatedAt
        string CreatedBy
        uuid DepartmentId FK
        bool IsActive
        uuid UserProfileId FK
    }

    UserChannel {
        uuid Id PK
        string ChannelType
        timestamptz CreatedAt
        string ExternalIdentifier
        bool IsEnabled
        jsonb MetadataJson
        string NormalizedIdentifier
        uuid UserProfileId FK
    }

    %% ---------- Intake ----------
    IntakeItem {
        uuid Id PK
        uuid InternalCompanyEntityId FK
        string ChannelType
        timestamptz CompletedAt
        string CorrelationId
        uuid DepartmentId FK
        string ExternalMessageId
        string ExternalSenderIdentifier
        string FailureReason
        string IdempotencyKey
        timestamptz ReceivedAt
        string RegisteredBy
        string RequestFingerprintSha256
        string Status
        jsonb TrustedMetadataJson
        uuid UserProfileId FK
    }

    ChannelIntakeCandidate {
        uuid Id PK
        string CandidateKey
        timestamptz CompletedAt
        string ContentType
        long DeclaredSizeBytes
        uuid DocumentId FK
        uuid DuplicateDocumentId FK
        string FailureCode
        uuid IntakeItemId FK
        string OriginalFileName
        timestamptz RegisteredAt
        string Status
    }

    %% ---------- Documents ----------
    Document {
        uuid Id PK
        string AttentionReason
        decimal InternalCompanyConfidence
        uuid InternalCompanyEntityId FK
        string InternalCompanyOrigin
        jsonb CatalogingEvidenceJson
        string CatalogingState
        long ConcurrencyToken
        string CorrelationId
        uuid CurrentRevisionId FK
        uuid DepartmentId FK
        string DocumentType
        string Direction
        decimal DocumentTypeConfidence
        string DocumentTypeOrigin
        uuid DuplicateOfDocumentId FK
        uuid IntakeItemId FK
        string OriginalContentHashSha256 UK
        timestamptz RegisteredAt
        string RegisteredBy
        string ResumeStatus
        string SemanticFingerprint
        jsonb SemanticFingerprintComponentsJson
        string Status
        decimal CounterpartyConfidence
        uuid CounterpartyEntityId FK
        string CounterpartyOrigin
        timestamptz UpdatedAt
    }

    DocumentComment {
        uuid Id PK
        string AuthorDisplayName
        string AuthorKind
        string AuthorSubject
        timestamptz CreatedAt
        uuid DocumentId FK
        timestamptz EditedAt
        string Text
        long Version
    }

    ExtractionRevision {
        uuid Id PK
        string AccountIdentifier
        jsonb AdditionalDataJson
        timestamptz ApprovedAt
        string ApprovedBy
        string BankName
        string BuyerCountryCode
        string BuyerIdentifierType
        string BuyerName
        decimal BuyerNameConfidence
        string BuyerTaxNumber
        decimal BuyerTaxNumberConfidence
        string ContractVersion
        string CorrectionReason
        timestamptz CreatedAt
        string CreatedBy
        string Currency
        bool DeterministicGatesPassed
        uuid DocumentId FK
        string DocumentType
        decimal DocumentTypeConfidence
        date DueDate
        long DurationMilliseconds
        decimal EffectiveConfidence
        decimal EstimatedCost
        jsonb FieldConfidenceJson
        string FiscalDocumentNumber
        string InstructionVersion
        date IssueDate
        string ModelId
        string ModelVersion
        decimal NetAmount
        jsonb OperatorHintJson
        date PeriodEnd
        date PeriodStart
        jsonb RawResponseJson
        string ReadingMode
        string ReadingPipelineVersion
        int RevisionNumber
        string SkillVersion
        string Source
        uuid SourceJobId "soft ref, no FK"
        uuid SupersedesRevisionId FK
        string SupplierCountryCode
        string SupplierIdentifierType
        string SupplierName
        decimal SupplierNameConfidence
        string SupplierTaxNumber
        decimal SupplierTaxNumberConfidence
        decimal TotalAmount
        jsonb UsageJson
        jsonb ValidationGatesJson
        decimal VatAmount
        jsonb WarningsJson
        decimal WithholdingAmount
    }

    DocumentLine {
        uuid Id PK
        jsonb AdditionalDataJson
        string Category
        string Description
        uuid ExtractionRevisionId FK
        jsonb FieldConfidenceJson
        int LineNumber
        decimal LineTotal
        decimal Quantity
        string Reference
        date TransactionDate
        decimal UnitPrice
        decimal VatAmount
        decimal VatRate
    }

    FileAsset {
        uuid Id PK
        timestamptz ConfirmedAt
        string ContentHashSha256
        string ContentType
        timestamptz CreatedAt
        string CreatedBy
        uuid DocumentId FK
        bool IsImmutable
        string Kind
        string OriginalFileName
        long SizeBytes
        string StorageObjectKey UK
    }

    ExcelCommit {
        uuid Id PK
        int AttemptCount
        timestamptz ConfirmedAt
        uuid DocumentId FK
        string ExpectedSchemaFingerprint
        uuid ExtractionRevisionId FK
        timestamptz LastAttemptAt
        string LastError
        string MstId UK
        string ObservedSchemaFingerprint
        timestamptz PreparedAt
        string State
        string TableName
        string WorkbookDriveId
        string WorkbookItemId
        string WorkbookSessionId
        string WorksheetName
    }

    %% ---------- Financial ----------
    BankAccount {
        uuid Id PK
        long CegidBankAccountId
        uuid CompanyEntityId FK
        timestamptz CreatedAt
        string CreatedBy
        string Currency
        string Iban
        bool IsActive
        string Name
        timestamptz UpdatedAt
        string UpdatedBy
    }

    CashAccount {
        uuid Id PK
        long CegidCashAccountId
        uuid CompanyEntityId FK
        timestamptz CreatedAt
        string CreatedBy
        string Currency
        bool IsActive
        string Name
        timestamptz UpdatedAt
        string UpdatedBy
    }

    FinancialAssessment {
        uuid Id PK
        string ContractVersion
        timestamptz CreatedAt
        string CreatedBy
        uuid DocumentId FK
        long DurationMilliseconds
        decimal EstimatedCost
        uuid ExtractionRevisionId FK
        string InputSha256
        string InstructionVersion
        string ModelId
        jsonb ProposalsJson
        jsonb RawResponseJson
        uuid SourceJobId FK
        string Status
        string Summary
        jsonb UsageJson
        jsonb WarningsJson
    }

    %% ---------- Workflows ----------
    Job {
        uuid Id PK
        int AttemptCount
        string ActiveDeduplicationKey
        timestamptz AvailableAt
        uuid ClaimToken
        timestamptz ClaimedAt
        string ClaimedBy
        timestamptz CompletedAt
        uuid ConcurrencyToken
        string CorrelationId
        timestamptz CreatedAt
        uuid DocumentId FK
        timestamptz HeartbeatAt
        string Lane
        string LastError
        timestamptz LeaseUntil
        int MaxAttempts
        jsonb PayloadJson
        string Postcondition
        int Priority
        string State
        string Type
    }

    JobAttempt {
        uuid Id PK
        int AttemptNumber
        uuid ClaimToken UK
        timestamptz CompletedAt
        string Error
        uuid JobId FK
        timestamptz LastHeartbeatAt
        string Outcome
        timestamptz StartedAt
        string WorkerId
    }

    %% ---------- Provider connections and neutral integration ----------
    CompanyIntegrationSettings {
        uuid Id PK
        uuid CompanyEntityId FK,UK
        string Provider
        timestamptz UpdatedAt
        string UpdatedBy
        long Version
    }
    CegidCompanyConnection {
        uuid Id PK
        string ApiBaseUrl
        string ClientId
        uuid CompanyEntityId FK,UK
        long ConfigurationVersion
        string ConnectionKey UK
        timestamptz CreatedAt
        string CreatedBy
        string DefaultPaymentMechanism
        bool Enabled
        string LastTestFailureCode
        bool LastTestSucceeded
        timestamptz LastTestedAt
        string OAuthBaseUrl
        string ProtectedClientSecret
        uuid PurchasePaymentBankAccountId FK
        uuid PurchasePaymentCashAccountId FK
        long PurchasePaymentSeriesId
        int SalesBackfillDays
        timestamptz SecretUpdatedAt
        timestamptz UpdatedAt
        string UpdatedBy
        string UploadBaseUrl
        long ValidatedConfigurationVersion
        long Version
    }

    CegidFiscalYearMapping {
        uuid Id PK
        uuid CegidCompanyConnectionId FK
        int FiscalYear
        string SubentityId
    }

    MoloniCompanyConnection {
        uuid Id PK
        uuid CompanyEntityId FK,UK
        timestamptz AccessTokenExpiresAt
        long ConfigurationVersion
        timestamptz CreatedAt
        string CreatedBy
        string DeveloperId
        long DocumentSetId
        bool Enabled
        string LastErrorCode
        bool LastTestSucceeded
        timestamptz LastTestedAt
        long MaturityDateId
        long MoloniCompanyId
        timestamptz OAuthStateExpiresAt
        long OAuthStateConfigurationVersion
        string OAuthStateHash
        long PaymentMethodId
        string ProtectedClientSecret
        string ProtectedRefreshToken
        bool ReauthorizationRequired
        string RedirectUri
        timestamptz RefreshTokenExpiresAt
        int SalesBackfillDays
        timestamptz TokenIssuedAt
        timestamptz UpdatedAt
        string UpdatedBy
        long ValidatedConfigurationVersion
        long Version
        long WarehouseId
    }

    DocumentTypeSetting {
        string DocumentType PK
        string LabelPt
        string LabelEn
        int SortOrder
        bool IsActive
        long Version
        timestamptz UpdatedAt
        string UpdatedBy
    }

    IntegrationCommit {
        uuid Id PK
        int AttemptCount
        uuid CompanyEntityId FK
        timestamptz ConfirmedAt
        string ConnectionKey
        uuid DocumentId FK
        string ExternalReference
        uuid ExtractionRevisionId FK
        int FiscalYear
        timestamptz LastAttemptAt
        string LastError
        timestamptz PreparedAt
        string Provider
        string PurchaseDocumentId
        string PurchasePaymentId
        string State
        long Version
    }

    IntegrationOperation {
        uuid Id PK
        int AttemptCount
        uuid IntegrationCommitId FK
        timestamptz ConfirmedAt
        string ExternalId
        string Kind
        timestamptz LastAttemptAt
        string LastError
        string OperationKey UK
        string Provider
        timestamptz PreparedAt
        string RequestHashSha256
        jsonb RequestJson
        string State
        long Version
    }

    IntegrationPartyBinding {
        uuid Id PK
        uuid BusinessEntityId FK
        string ExternalId
        uuid CompanyEntityId FK
        string ConnectionKey
        string Kind
        string Provider
        string TaxIdentifier
        timestamptz UpdatedAt
    }

    IntegrationProductBinding {
        uuid Id PK
        string ExternalId
        uuid CompanyEntityId FK
        string ConnectionKey
        timestamptz CreatedAt
        string Description
        string NormalizedDescription
        string ProductCode
        string Provider
        timestamptz UpdatedAt
    }

    IntegrationProductPlKeyMapping {
        uuid Id PK
        uuid CompanyEntityId FK
        string ConnectionKey
        bool IsActive
        string PlKey
        string ProductCode
        string Provider
        timestamptz UpdatedAt
        string UpdatedBy
    }

    IntegrationSalesDocument {
        uuid Id PK
        string ExternalId
        uuid CompanyEntityId FK
        string ConnectionKey
        string Currency
        string CustomerCountryCode
        uuid CustomerEntityId FK
        string CustomerName
        string CustomerTaxIdentifier
        string DocumentNumber
        date DueDate
        int FiscalYear
        timestamptz ImportedAt
        date IssueDate
        string Kind
        decimal NetAmount
        string PayloadHashSha256
        jsonb PayloadJson
        string Provider
        decimal ReceivedAmount
        string State
        decimal TotalAmount
        timestamptz UpdatedAt
        decimal VatAmount
    }

    IntegrationSalesLine {
        uuid Id PK
        string ExternalId
        uuid IntegrationSalesDocumentId FK
        string Description
        int LineNumber
        string MstId UK
        decimal NetAmount
        string ProductCode
        string ProjectionError
        string ProjectionState
        string Provider
        decimal Quantity
        decimal ReceivedAmount
        decimal TotalAmount
        decimal UnitPrice
        decimal VatAmount
        decimal VatRate
    }

    IntegrationSalesPdfAsset {
        uuid Id PK
        string ExternalId
        uuid CompanyEntityId FK
        string ConnectionKey
        string ContentHashSha256
        timestamptz FetchedAt
        int FiscalYear
        string Provider
        string ResourceType
        long SizeBytes
        string StorageObjectKey UK
        int Version
    }

    IntegrationSalesReceipt {
        uuid Id PK
        string ExternalId
        uuid CompanyEntityId FK
        string ConnectionKey
        string Currency
        int FiscalYear
        timestamptz ImportedAt
        string PayloadHashSha256
        jsonb PayloadJson
        string Provider
        string PaymentMechanism
        date ReceiptDate
        string ReceiptNumber
        string State
        decimal TotalAmount
        timestamptz UpdatedAt
    }

    IntegrationSalesReceiptAllocation {
        uuid Id PK
        string ExternalReceiptLineId
        uuid IntegrationSalesDocumentId FK
        uuid IntegrationSalesLineId FK
        uuid IntegrationSalesReceiptId FK
        string Provider
        decimal ReceivedValue
        decimal SettlementPercentage
    }

    IntegrationSyncRun {
        uuid Id PK
        uuid CompanyEntityId FK
        timestamptz CompletedAt
        string ConnectionKey
        int DocumentsChanged
        int DocumentsSeen
        int FiscalYear
        string LastError
        int PdfsStored
        string Provider
        int ReceiptsChanged
        int ReceiptsSeen
        timestamptz StartedAt
        string State
    }

    PurchasePaymentInstruction {
        uuid Id PK
        decimal Amount
        timestamptz ApprovedAt
        string ApprovedBy
        uuid BankAccountId FK
        uuid CashAccountId FK
        timestamptz CreatedAt
        string CreatedBy
        string Currency
        uuid DocumentId FK
        long DocumentSeriesId
        date PaymentDate
        string PaymentMechanism
        string RejectionReason
        string State
    }

    PurchasePaymentAllocation {
        uuid Id PK
        string ExternalPayableId
        decimal PaidValue
        string PayableType
        uuid PurchasePaymentInstructionId FK
        decimal SettlementPercentage
        uuid TargetDocumentId FK
    }

    %% ---------- Standalone / no FK relationships ----------
    AuditEvent {
        uuid Id PK
        string Action
        string Actor
        string CorrelationId
        string EntityId
        string EntityType
        jsonb PayloadJson
        string Severity
        timestamptz Timestamp
    }

    SystemSetting {
        string Key PK
        timestamptz UpdatedAt
        string UpdatedBy
        jsonb ValueJson
    }

    %% ================= Relationships =================

    %% Identity
    BusinessEntity |o--o{ BusinessEntity : "merged into"
    BusinessEntity ||--o| BusinessEntityExtractionProfile : "extraction profile"
    BusinessEntity ||--o{ Department : "departments"
    BusinessEntity ||--o{ EntityRoleAssignment : "role assignments"
    BusinessEntity ||--o{ EntityTaxIdentifier : "tax identifiers"
    BusinessEntity ||--o{ Membership : "memberships (company)"
    Department |o--o{ Membership : "memberships"
    UserProfile ||--o{ Membership : "memberships"
    UserProfile ||--o{ UserChannel : "channels"

    %% Intake
    BusinessEntity |o--o{ IntakeItem : "internal company"
    Department |o--o{ IntakeItem : "department"
    UserProfile |o--o{ IntakeItem : "submitted by"
    IntakeItem ||--o{ ChannelIntakeCandidate : "candidates"
    Document |o--o| ChannelIntakeCandidate : "resulting document"
    Document |o--o{ ChannelIntakeCandidate : "duplicate of"

    %% Documents
    BusinessEntity |o--o{ Document : "internal company"
    BusinessEntity |o--o{ Document : "counterparty"
    Department |o--o{ Document : "department"
    IntakeItem |o--o{ Document : "intake item"
    Document |o--o{ Document : "duplicate of"
    Document ||--o{ ExtractionRevision : "revisions"
    ExtractionRevision |o--o{ Document : "current revision"
    ExtractionRevision |o--o{ ExtractionRevision : "supersedes"
    ExtractionRevision ||--o{ DocumentLine : "lines"
    Document ||--o{ DocumentComment : "comments"
    Document ||--o{ FileAsset : "file assets"
    Document ||--o{ ExcelCommit : "excel commits"
    ExtractionRevision ||--o{ ExcelCommit : "excel commits"

    %% Financial
    BusinessEntity ||--o{ BankAccount : "bank accounts"
    BusinessEntity ||--o{ CashAccount : "cash accounts"
    Document ||--o{ FinancialAssessment : "assessments"
    ExtractionRevision ||--o{ FinancialAssessment : "assessments"
    Job ||--|| FinancialAssessment : "source job"

    %% Workflows
    Document |o--o{ Job : "jobs"
    Job ||--o{ JobAttempt : "attempts"

    %% Provider connections and neutral integration
    BusinessEntity ||--o| CompanyIntegrationSettings : "selected provider"
    BusinessEntity ||--o| CegidCompanyConnection : "cegid connection"
    BankAccount |o--o{ CegidCompanyConnection : "purchase payment bank acct"
    CashAccount |o--o{ CegidCompanyConnection : "purchase payment cash acct"
    CegidCompanyConnection ||--o{ CegidFiscalYearMapping : "fiscal year mappings"
    BusinessEntity ||--o| MoloniCompanyConnection : "moloni connection"
    BusinessEntity ||--o{ IntegrationCommit : "company"
    Document ||--o{ IntegrationCommit : "prepared for"
    ExtractionRevision ||--o{ IntegrationCommit : "commits"
    IntegrationCommit |o--o{ IntegrationOperation : "operations"
    BusinessEntity ||--o{ IntegrationPartyBinding : "party bindings"
    BusinessEntity ||--o{ IntegrationPartyBinding : "company"
    BusinessEntity ||--o{ IntegrationProductBinding : "company"
    BusinessEntity ||--o{ IntegrationProductPlKeyMapping : "company"
    BusinessEntity ||--o{ IntegrationSalesDocument : "company"
    BusinessEntity |o--o{ IntegrationSalesDocument : "customer"
    IntegrationSalesDocument ||--o{ IntegrationSalesLine : "lines"
    BusinessEntity ||--o{ IntegrationSalesPdfAsset : "company"
    BusinessEntity ||--o{ IntegrationSalesReceipt : "company"
    IntegrationSalesDocument ||--o{ IntegrationSalesReceiptAllocation : "receipt allocations"
    IntegrationSalesLine |o--o{ IntegrationSalesReceiptAllocation : "receipt allocations"
    IntegrationSalesReceipt ||--o{ IntegrationSalesReceiptAllocation : "allocations"
    BusinessEntity ||--o{ IntegrationSyncRun : "company"
    PurchasePaymentInstruction ||--o{ PurchasePaymentAllocation : "allocations"
    Document ||--o{ PurchasePaymentAllocation : "target document"
    BankAccount |o--o{ PurchasePaymentInstruction : "bank account"
    CashAccount |o--o{ PurchasePaymentInstruction : "cash account"
    Document ||--|| PurchasePaymentInstruction : "payment instruction"