Data Model

Database Configuration

Engine: PostgreSQL 15+ with pgcrypto and pgvector extensions
ORM: Prisma 7.6.0
Schema: Split across 45 .prisma files in packages/db/prisma/schema/
Migrations: 196 migration files tracking schema evolution
ID Strategy: Prefixed CUIDs generated at DB level: generate_prefixed_cuid('prefix'::text)

Entity Relationship Diagram

erDiagram
    User ||--o{ Member : "has memberships"
    User ||--o{ Session : "has sessions"
    User ||--o{ Account : "has OAuth accounts"
    
    Organization ||--o{ Member : "has members"
    Organization ||--o{ Policy : "has policies"
    Organization ||--o{ Control : "has controls"
    Organization ||--o{ Risk : "has risks"
    Organization ||--o{ Vendor : "has vendors"
    Organization ||--o{ Task : "has tasks"
    Organization ||--o{ Framework : "has frameworks"
    Organization ||--o{ Integration : "has integrations"
    Organization ||--o{ AuditLog : "has audit logs"
    Organization ||--o{ ApiKey : "has API keys"
    Organization ||--o{ Trust : "has trust portal"
    Organization ||--o{ Questionnaire : "has questionnaires"
    Organization ||--o{ OrganizationRole : "has custom roles"
    Organization ||--o{ Finding : "has findings"
    Organization ||--o{ EvidenceSubmission : "has evidence"
    
    Member ||--o{ Policy : "assigned/approves"
    Member ||--o{ Task : "assigned/approves"
    Member ||--o{ Risk : "assigned"
    Member ||--o{ Vendor : "assigned"
    Member ||--o{ Comment : "authors"
    Member ||--o{ Finding : "creates"
    
    Control }o--o{ Task : "many-to-many"
    Control }o--o{ Policy : "many-to-many"
    Control ||--o{ ControlDocumentType : "requires evidence types"
    
    Task ||--o{ EvidenceAutomation : "has automations"
    Task ||--o{ BrowserAutomation : "has browser tasks"
    Task }o--o{ Vendor : "many-to-many"
    Task }o--o{ Risk : "many-to-many"
    
    Framework }|--|| FrameworkEditorFramework : "references template"
    FrameworkEditorFramework ||--o{ FrameworkEditorRequirement : "has requirements"
    FrameworkEditorRequirement }o--o{ FrameworkEditorControlTemplate : "maps to controls"
    FrameworkEditorControlTemplate ||--o{ FrameworkEditorPolicyTemplate : "has policy templates"
    FrameworkEditorControlTemplate ||--o{ FrameworkEditorTaskTemplate : "has task templates"
    
    Trust ||--o{ TrustResource : "has resources"
    Trust ||--o{ TrustAccessRequest : "has access requests"
    TrustAccessRequest ||--o| TrustAccessGrant : "may grant"
    TrustAccessGrant ||--o{ TrustNDAAgreement : "requires NDA"
    
    Questionnaire ||--o{ QuestionnaireQuestionAnswer : "has Q&A pairs"

Core Entities

Authentication & Organization

User

Prefix: (Better Auth managed)
Purpose: Account-level identity
Key fields: email, emailVerified, role (platform-level: admin or null)
Notable: emailPreferences (JSON) for notification settings, banned/banExpires for suspension

Session

Purpose: Active login sessions
Key fields: token, expiresAt, ipAddress, userAgent
Notable: activeOrganizationId tracks current org context, impersonatedBy for admin impersonation

Organization

Purpose: Top-level tenant boundary
Key fields: name, slug, logo
Feature flags: evidenceApprovalEnabled, deviceAgentEnabled, securityTrainingEnabled
Onboarding: onboardingCompleted, subscriptionType
Notable: employeeSyncProvider for HR integration

Member

Prefix: (Better Auth managed, within organization context)
Purpose: User's role and status within an organization
Key fields: role (comma-separated, e.g., "admin,auditor"), department, jobTitle, isActive
Notable: Links to almost every domain entity as assignee/approver

OrganizationRole

Purpose: Custom roles beyond the 5 built-in ones
Key fields: name, permissions (JSON), obligations (JSON)
Scope: Per-organization, allows tenant-specific RBAC

GRC Core

Policy (prefix: `pol`)

Purpose: Security/compliance policies (e.g., "Access Control Policy")
Status: draft → published → needs_review
Content: JSON arrays (TipTap editor format) for both published and draft versions
Versioning: currentVersion, pendingVersion, linked PolicyVersion records
Display: EDITOR (rich text) or PDF (uploaded document)
Visibility: ALL or department-specific
Relationships: assignee (Member), approver (Member), controls (many-to-many)

Control (prefix: `ctl`)

Purpose: Security controls mapped to framework requirements
Key fields: name, description, lastReviewDate, nextReviewDate
Relationships: tasks, policies, control document types (evidence requirements)
Template link: References FrameworkEditorControlTemplate for framework mapping

Risk (prefix: `rsk`)

Purpose: Organizational risk register
Assessment: likelihood x impact = risk score (both inherent and residual)
Status: open → pending → closed/archived
Treatment: accept, avoid, mitigate, transfer
Categories: customer, fraud, governance, operations, people, regulatory, reporting, resilience, technology, vendor_management

Task (prefix: `tsk`)

Purpose: Compliance tasks and evidence collection
Status flow: todo → in_progress → in_review → done (also not_relevant, failed)
Automation: AUTOMATED or MANUAL
Frequency: daily, weekly, monthly, quarterly, yearly
Evidence approval: Optional workflow via approver member and approvedAt
Relationships: controls, vendors, risks, evidence automations, browser automations

Vendor (prefix: `vnd`)

Purpose: Third-party vendor risk management
Assessment: Inherent/residual likelihood x impact scoring
Status: not_assessed → in_progress → assessed
Categories: cloud, infrastructure, SaaS, finance, marketing, sales, hr, other
Trust: Display settings for trust portal, compliance badges (JSON), logo
Research: VendorRiskAssessment with AI-generated analysis

Framework

Purpose: Links an organization to a compliance framework
Key fields: organizationId + frameworkId (unique pair)
Template: References FrameworkEditorFramework (SOC 2, ISO 27001, etc.)
Mapping: RequirementsMapped tracks which requirements are addressed

Audit & Evidence

AuditLog (prefix: `aud`)

Purpose: Immutable record of all mutations
Key fields: entityType, entityId, description, method, path
Data: JSON with action, changes (before/after diffs), impersonatedBy
Entity types: organization, framework, requirement, control, policy, task, people, risk, vendor, tests, integration, trust, finding
Auto-generated: By AuditLogInterceptor on POST/PATCH/PUT/DELETE when @RequirePermission is present

Finding (prefix: `fnd`)

Purpose: Audit findings (gaps, observations, exceptions)
Type: soc2 or iso27001
Status: open → ready_for_review → closed (or needs_revision loop)
Content: Custom message or from FindingTemplate
Notable: revisionNote for auditor feedback on revisions

EvidenceSubmission (prefix: `evs`)

Purpose: Structured evidence submissions
Form types: board_meeting, access_request, penetration_test, rbac_matrix, network_diagram, etc.
Status: pending (default)
Data: JSON form submission

AI & Knowledge

Questionnaire (prefix: `qst`)

Purpose: Security questionnaires (uploaded documents)
Processing: parsing → completed/failed
Tracking: totalQuestions, answeredQuestions
Source: internal or external (from trust portal)

QuestionnaireQuestionAnswer

Purpose: Individual Q&A pairs within a questionnaire
Status: untouched → generated (AI) or manual (human)
Sources: JSON array of RAG source references
Notable: generatedAt timestamp for AI answers, updatedBy for manual edits

SecurityQuestionnaireManualAnswer

Purpose: Manual answer overrides (used as RAG context for future questions)

Trust Portal

Trust

Purpose: Organization's public compliance portal
Configuration: friendlyUrl, domain, domainVerified
Certifications: Boolean flags for SOC2, ISO 27001, ISO 42001, NEN 7510, GDPR, HIPAA, PCI DSS, ISO 9001
Status: draft → published
Access control: NDA requirements, allowed domains for bypass

TrustAccessRequest (prefix: `tar`)

Flow: requester submits → under_review → approved/denied/canceled
Requester info: name, email, company, jobTitle, purpose
Reviewer: Member with decision reason

TrustAccessGrant (prefix: `tag`)

Status: active → expired/revoked
Authentication: accessToken with expiration for portal access

TrustNDAAgreement (prefix: `tna`)

Status: pending → signed/void
Digital signing: signToken, PDF templates and signed copies in S3

Integrations & Automation

Integration

Purpose: SaaS/cloud service connections
Key fields: integrationId (type identifier), settings (JSON), userSettings (JSON)
Results: IntegrationResult with title, description, remediation, severity, status

EvidenceAutomation

Purpose: AI-powered evidence collection automation
Key fields: chatHistory, evaluationCriteria
Lifecycle: Runs tracked with versions

BrowserAutomation

Purpose: Playwright/Stagehand browser-based evidence tasks

Supporting Entities

Entity	Prefix	Purpose
Comment	`cmt`	Polymorphic comments on tasks, vendors, risks, policies
TaskItem	`tski`	Sub-tasks within vendors or risks
Attachment	`att`	File attachments on any entity
ApiKey	`apk`	Scoped API keys with hash/prefix indexing
Device	—	Fleet-managed endpoint devices
Context	—	Organization context data (onboarding answers, etc.)
Secret	—	Encrypted org secrets (for automations)
SOADocument	—	Statement of Applicability documents
SOAAnswer	—	Individual SOA question answers

Enums Reference

// Roles
enum Role { owner, admin, auditor, employee, contractor }
 
// Departments
enum Departments { none, admin, gov, hr, it, itsm, qms }
 
// Risk Assessment
enum Likelihood { very_unlikely, unlikely, possible, likely, very_likely }
enum Impact { insignificant, minor, moderate, major, severe }
enum RiskStatus { open, pending, closed, archived }
enum RiskCategory { customer, fraud, governance, operations, other, 
                    people, regulatory, reporting, resilience, technology,
                    vendor_management }
enum RiskTreatmentType { accept, avoid, mitigate, transfer }
 
// Tasks
enum TaskStatus { todo, in_progress, in_review, done, not_relevant, failed }
enum TaskFrequency { daily, weekly, monthly, quarterly, yearly }
enum Frequency { monthly, quarterly, yearly }
 
// Policies
enum PolicyStatus { draft, published, needs_review }
 
// Vendors
enum VendorStatus { not_assessed, in_progress, assessed }
enum VendorCategory { cloud, infrastructure, SaaS, finance, marketing,
                      sales, hr, other }
 
// Findings
enum FindingType { soc2, iso27001 }
enum FindingStatus { open, ready_for_review, needs_revision, closed }
 
// Questionnaires
enum QuestionnaireStatus { parsing, completed, failed }
enum AnswerStatus { untouched, generated, manual }
 
// Evidence Forms
enum EvidenceFormType { board_meeting, it_leadership_meeting, 
     risk_committee_meeting, meeting, access_request, whistleblower_report,
     penetration_test, rbac_matrix, infrastructure_inventory, 
     employee_performance_evaluation, network_diagram, tabletop_exercise }
 
// Polymorphic Types
enum CommentEntityType { task, vendor, risk, policy }
enum AttachmentEntityType { task, vendor, risk, comment, trust_nda, task_item }
enum AuditLogEntityType { organization, framework, requirement, control,
     policy, task, people, risk, vendor, tests, integration, trust, finding }

Multi-Tenancy Implementation

Every query is scoped by organizationId:

// Service pattern (every service follows this)
async findAll(organizationId: string) {
  return this.db.policy.findMany({
    where: { organizationId },  // ALWAYS present
    // ...
  });
}

The organizationId comes from the authenticated request, resolved by HybridAuthGuard, and injected via @OrganizationId() decorator.

Schema Organization

packages/db/prisma/schema/
├── schema.prisma          # Datasource + generator config
├── auth.prisma            # User, Session, Account, Member, Organization, Role
├── policy.prisma          # Policy, PolicyVersion
├── control.prisma         # Control, ControlDocumentType
├── risk.prisma            # Risk
├── vendor.prisma          # Vendor, VendorContact, VendorRiskAssessment
├── task.prisma            # Task
├── task-item.prisma       # TaskItem (sub-tasks)
├── framework.prisma       # Framework (org instances)
├── framework-editor.prisma # Framework templates, requirements, control/policy/task templates
├── finding.prisma         # Finding, FindingTemplate
├── evidence-submission.prisma # EvidenceSubmission
├── automation.prisma      # EvidenceAutomation, BrowserAutomation
├── integration.prisma     # Integration, IntegrationResult
├── questionnaire.prisma   # Questionnaire, Q&A, ManualAnswer
├── trust.prisma           # Trust, Resources, Access, NDA
├── soa.prisma             # SOA config, documents, answers
├── comment.prisma         # Comment
├── attachments.prisma     # Attachment
├── device.prisma          # Device
├── shared.prisma          # AuditLog, ApiKey, enums
└── ... (45 files total)