Data Model

This document describes the database schema, tenant isolation rules, and key entity relationships.

Schema Location

All table definitions are in shared/schema.ts. This file uses Drizzle ORM with PostgreSQL.

Tenant Isolation Rules

Hard Rule

Every table with user data must include tenant_id and scope all queries by it.

Pattern (verified from shared/schema.ts):

export const yourTable = pgTable("your_table", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  tenantId: varchar("tenant_id").notNull().references(() => tenants.id),
  // ... other columns
}, (table) => ({
  tenantIdx: index("your_table_tenant_idx").on(table.tenantId)
}));

Query pattern (verified from server/lib/vectorStore.ts:75):

WHERE tenant_id = ${tenantId}

API enforcement (verified from server/routes.ts:161-172):

• Use requireTenant(req, res) helper in every route
• Returns null and sends error response if tenant missing/invalid
• No silent fallbacks allowed

Core Entities

Tenants (tenants)

Organization-level isolation unit.

Schema (shared/schema.ts:39-43):

export const tenants = pgTable("tenants", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  name: text("name").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull()
});

Packs (packs)

Requirement packs (e.g., SAMA Pre-Submission).

Schema (shared/schema.ts:67-75):

export const packs = pgTable("packs", {
  id: varchar("id").primaryKey(),
  name: text("name").notNull(),
  description: text("description"),
  regulatorCode: varchar("regulator_code", { length: 20 }),
  version: varchar("version", { length: 20 }).default("1.0"),
  isActive: integer("is_active").default(1),
  createdAt: timestamp("created_at").defaultNow().notNull()
});

Documents (documents)

Evidence files uploaded by users.

Schema (shared/schema.ts:192-218):

Column	Type	Purpose
id	varchar	Primary key (UUID)
tenantId	varchar	Foreign key to tenants
originalFilename	text	User's filename
fileType	varchar	Extension (pdf, docx, etc.)
sha256Hash	varchar(64)	Deduplication hash
parentDocumentId	varchar	For ZIP children
status	varchar	Lifecycle state
filePath	text	Storage path

Indexes: tenantIdx, sha256Idx, statusIdx, parentIdx

Chunks (chunks)

Text segments from documents for vector search.

Schema (shared/schema.ts:236-250):

Column	Type	Purpose
id	varchar	Primary key
tenantId	varchar	Foreign key to tenants
documentId	varchar	Foreign key to documents
chunkIndex	integer	Order within document
content	text	Text content
pageNumber	integer	Source page (nullable)
startOffset	integer	Character offset start
endOffset	integer	Character offset end
tokenCount	integer	Token count

Chunk Embeddings (chunk_embeddings)

Vector storage for semantic search.

Schema (shared/schema.ts:263-271):

Column	Type	Purpose
id	varchar	Primary key
tenantId	varchar	Foreign key to tenants
chunkId	varchar	Foreign key to chunks
embedding	vector(1536)	OpenAI embedding

Vector type: Custom type defined at lines 7-17.

Runs (runs)

Assessment batch runs.

Schema (shared/schema.ts:330-366):

Column	Type	Purpose
id	varchar	Primary key
tenantId	varchar	Foreign key to tenants
packId	varchar	Pack being assessed
environment	varchar	DEV/STAGING/PROD
status	varchar	Run lifecycle state
snapshotStatus	varchar	Replay snapshot state
traceId	varchar(64)	OpenTelemetry trace ID
rootSpanId	varchar(64)	Root span ID
snapshot*	varchar	Pinned version IDs (6 fields)

Snapshot fields for deterministic replay:

• snapshotCriteriaVersionId
• snapshotCorpusActivationId
• snapshotRetrievalVersionId
• snapshotPromptVersionId
• snapshotChunkingVersionId
• snapshotAgentVersionId

Run Replay Snapshots (run_replay_snapshots)

Immutable snapshot store for deterministic replay.

Schema (shared/schema.ts:382-397):

Column	Type	Purpose
id	varchar	Primary key
runId	varchar	Foreign key to runs (unique)
tenantId	varchar	Foreign key to tenants
snapshotHash	varchar(64)	Content hash
assessmentsData	jsonb	Frozen assessments
tasksData	jsonb	Frozen tasks
exportsData	jsonb	Frozen exports
configSnapshot	jsonb	Frozen config
capturedAt	timestamp	When captured

Assessments (assessments)

Individual requirement evaluations.

Schema (shared/schema.ts:419-437):

Column	Type	Purpose
id	varchar	Primary key
runId	varchar	Foreign key to runs
tenantId	varchar	Foreign key to tenants
requirementId	varchar	Requirement being assessed
status	varchar	COMPLETE/PARTIAL/MISSING/FAILED
confidence	real	0.0-1.0 confidence score
citations	jsonb	Evidence citations
retrievedChunks	jsonb	Chunks used for assessment
reasoning	text	LLM reasoning
retrievalMode	varchar	primary/fallback/skipped

Tasks (tasks)

Generated remediation tasks.

Schema (shared/schema.ts:476-502):

Column	Type	Purpose
id	varchar	Primary key
tenantId	varchar	Foreign key to tenants
createdByRunId	varchar	Run that created this task
assessmentId	varchar	Related assessment
title	text	Task title
description	text	Task description
gapType	varchar	Gap classification
suggestedArtifact	text	What to produce
acceptanceCriteria	jsonb	Success criteria
status	varchar	OPEN/IN_PROGRESS/BLOCKED/DONE
closedByRunId	varchar	Run that closed this task

Status Enums

EvidenceStatus (Portal)

Document processing lifecycle.

Definition (shared/schema.ts:160-167):

export const EvidenceStatus = {
  UPLOADED: "UPLOADED",
  EXTRACTED: "EXTRACTED", 
  PARSED: "PARSED",
  INDEXED: "INDEXED",
  READY: "READY",
  FAILED: "FAILED"
} as const;

RunStatus

Assessment run lifecycle.

Definition (shared/schema.ts:294-299):

export const RunStatus = {
  PENDING: "PENDING",
  IN_PROGRESS: "IN_PROGRESS",
  COMPLETED: "COMPLETED",
  FAILED: "FAILED"
} as const;

SnapshotStatus

Deterministic replay snapshot state.

Definition (shared/schema.ts:306-311):

export const SnapshotStatus = {
  PENDING: "PENDING",     // Run not yet completed
  CAPTURED: "CAPTURED",   // Snapshot captured - deterministic replay available
  FAILED: "FAILED",       // Snapshot capture failed
  LEGACY: "LEGACY"        // Pre-Step 9 run - no snapshot exists
} as const;

AssessmentStatus

Requirement evaluation outcome.

Definition (shared/schema.ts:318-323):

export const AssessmentStatus = {
  COMPLETE: "COMPLETE",
  PARTIAL: "PARTIAL",
  MISSING: "MISSING",
  FAILED: "FAILED"
} as const;

TaskStatus

Remediation task lifecycle.

Definition (shared/schema.ts:450-455):

export const TaskStatus = {
  OPEN: "OPEN",
  IN_PROGRESS: "IN_PROGRESS",
  BLOCKED: "BLOCKED",
  DONE: "DONE"
} as const;

Environment

Deployment environment.

Definition (shared/schema.ts:283-287):

export const Environment = {
  DEV: "DEV",
  STAGING: "STAGING",
  PROD: "PROD"
} as const;

Entity Relationships

tenants
   │
   ├─► packs (via pack_bindings)
   │
   ├─► documents
   │      │
   │      └─► chunks
   │            │
   │            └─► chunk_embeddings
   │
   ├─► runs
   │      │
   │      ├─► assessments
   │      │      │
   │      │      └─► tasks
   │      │
   │      ├─► run_steps
   │      │
   │      ├─► run_replay_snapshots
   │      │
   │      └─► exports
   │
   └─► audit_logs

Constants and Limits

File limits (shared/schema.ts:172-179):

export const ALLOWED_FILE_TYPES = ["pdf", "docx", "xlsx", "csv", "png", "jpg", "jpeg", "zip", "txt"] as const;

export const ZIP_LIMITS = {
  MAX_DEPTH: 5,
  MAX_FILES: 1000,
  MAX_TOTAL_SIZE_BYTES: 500 * 1024 * 1024 // 500MB
} as const;

Upload limit (server/routes.ts:88):

limits: { fileSize: 100 * 1024 * 1024 } // 100MB

Schema Migrations

Use Drizzle's schema push:

npm run db:push

For destructive changes, use:

npm run db:push --force

warning

Never manually alter primary key types. See [important_database_safety_rules] in development guidelines.

Related Pages

• Architecture Overview - System design
• Repository Map - Where to edit
• Determinism and Replay - Snapshot pinning