Skip to content

Data Model

Target Architecture — Final-State Design

This page describes the final-state logical data model of the Governance, Security & Compliance Platform. Each bounded context owns its physical schema (database-per-service); the model below is the logical view across contexts. Persistence is NHibernate on Azure SQL / PostgreSQL. Every entity carries the cross-cutting Metadata Schema fields.

Logical Model

The platform's data divides into three logical clusters:

  1. Policy & decision — PolicyDefinition → PolicyRule; PolicyDecision references the definition/version it evaluated and may open an ApprovalRequest.
  2. Approval & audit — ApprovalRequest → ApprovalDecision; every decision and approval writes an AuditEntry (append-only, hash-chained).
  3. Security, classification & risk — SecurityFinding, SecretReference, DataClassification, TenantIsolationRule, and RiskScore supply attributes and signals into evaluation and compliance.
erDiagram
    PolicyDefinition ||--o{ PolicyRule : contains
    PolicyDefinition ||--o{ PolicyDecision : "evaluated by"
    PolicyDecision ||--o| ApprovalRequest : "may open"
    ApprovalRequest ||--|| ApprovalDecision : "resolved by"
    PolicyDecision ||--o{ AuditEntry : "writes"
    ApprovalRequest ||--o{ AuditEntry : "writes"
    SecurityFinding ||--o{ AuditEntry : "writes"

    PolicyDefinition {
        string policyDefinitionId PK
        string tenantId
        string name
        string domain
        string effect
        string version
        string status
        datetime publishedAt
    }
    PolicyRule {
        string policyRuleId PK
        string policyDefinitionId FK
        string action
        string condition
        string effect
        string gate
        int priority
    }
    PolicyDecision {
        string policyDecisionId PK
        string tenantId
        string policyDefinitionId FK
        string policyVersion
        string decision
        string action
        int riskScore
        string approvalRequestId FK
        string traceId
        datetime evaluatedAt
    }
    ApprovalRequest {
        string approvalRequestId PK
        string tenantId
        string policyDecisionId FK
        string status
        string action
        datetime expiresAt
        int escalationLevel
        datetime createdAt
    }
    ApprovalDecision {
        string approvalDecisionId PK
        string approvalRequestId FK
        string approverId
        string outcome
        string justification
        datetime decidedAt
    }
    AuditEntry {
        string auditEntryId PK
        string tenantId
        string action
        string subject
        string resource
        string outcome
        string hash
        string previousHash
        string traceId
        datetime occurredAt
    }
    SecurityFinding {
        string securityFindingId PK
        string tenantId
        string category
        string severity
        string status
        string dedupKey
        int riskScore
        datetime createdAt
    }
Hold "Alt" / "Option" to enable pan & zoom

Cross-service references

Foreign-key relationships shown above are logical. Because each service owns its own database, cross-service references (e.g. PolicyDecision.approvalRequestId → ApprovalRequest) are soft references resolved via APIs/events, not enforced DB constraints. Within a single service (e.g. PolicyDefinition → PolicyRule), they are real constraints.

Supporting Entities (not shown in ER diagram)

Entity Owning Service Key fields Notes
SecretReference SecretGovernanceService vaultUri, secretName, secretVersion, rotationPolicy Points to Key Vault; never holds secret values.
TenantIsolationRule TenantIsolationPolicyService scope, isolationMode, residencyRegion ABAC supplier to evaluation.
DataClassification DataClassificationService subjectRef, label, categories, contentHash One active label per (subjectRef, contentHash).
RiskScore RiskScoringService subjectType, subjectId, score, band, version Latest-per-subject + version history.
ComplianceReport ComplianceReportService framework, period, controlResults, evidenceLocation Metadata in SQL, evidence in Blob.

Retention

Data Retention Mechanism
AuditEntry Indefinite (regulatory). Hot in SQL ≥ 13 months; older exported to Blob and tiered to cool/archive. AuditExportWorker + Blob lifecycle policy.
PolicyDecision ≥ 7 years (evidence). Append-only table; archival export with audit.
PolicyDefinition / PolicyRule (versions) Indefinite (needed to replay historical decisions). Immutable versions; never hard-deleted.
ApprovalRequest / ApprovalDecision ≥ 7 years (evidence). Retained; closed requests archived.
ComplianceReport + evidence Per framework (commonly 7 years). Blob WORM/immutable container where required.
SecurityFinding ≥ 2 years after resolution. Status-based archival; not deleted while open.
SecretReference Lifetime of the secret + rotation history. Retired references kept for audit; values never stored.
DataClassification / RiskScore Latest live; history ≥ 1 year. Versioned; older versions tiered.

Retention is itself policy-driven (a DataClassification/framework determines the minimum), and every export/archival action is audited.

Multi-Tenancy

  • Tenant column everywhere — tenantId is a first-class, non-nullable column on every table and part of every primary/secondary index used for tenant-scoped queries, per the Metadata Schema.
  • Enforced in the pipeline — ConnectSoft.Extensions.Saas.NHibernate applies a tenant filter at the session level; ConnectSoft.Extensions.Saas.AspNetCore / ConnectSoft.Extensions.WebSecurity assert the tenantId claim before handlers run. Cross-tenant reads are impossible without an explicit, approved TenantIsolationRule grant.
  • Isolation as data — TenantIsolationRule makes the isolation boundary itself a queryable, governed entity (residency region, isolation mode, cross-tenant grants).
  • Hash chains are per-tenant — AuditEntry chains are scoped per tenant so one tenant's audit integrity is independent of another's.
  • Physical options — default is shared-schema with row-level tenant filtering; high-isolation editions can map to database-per-tenant for the audit and compliance stores (configured via ConnectSoft.Extensions.Saas.Options).