Data Model

This section documents the domain data model — the entities, their relationships, and the conventions every table follows. It reconciles two things:

1. The proven MVP schema — 28 tables validated in production on the Base44 rebuild (set-test-claude), the source of truth for what the product actually stores today.
2. The target-state additions — entities the documented user flows require but the MVP schema does not yet contain (supply-chain/vendors, training, BCP processes, a normalized risk register, notifications, and a first-class audit trail).

Everything here lives inside a tenant schema — see Multi-Tenancy. The model is expressed for PostgreSQL 16 + Drizzle ORM; the MVP's SQLite types are mapped to their Postgres equivalents below.

---

Tenancy: what "tenant" and "client" mean here

This is the single most important modeling decision, and the existing spec leaves it implicit. Made explicit:

• A tenant = one WorkOS Organization = one Postgres schema (tenant_<id>) = the organization that logs into the Admin portal (an MSSP, or a standalone enterprise using the product for itself). This is the schema boundary; see Multi-Tenancy.
• A client = a row in the clients table inside that tenant schema — one of the MSSP's customer organizations. A tenant has many clients.
• A customer-portal user authenticates into the same tenant schema but is scoped to a single client_id.

Consequence — two isolation layers, not one:

Boundary	Between	Enforced by
Tenant isolation	One MSSP vs another MSSP	Postgres schema (search_path) — the hard boundary
Client isolation	Two clients of the same MSSP	Application RBAC — every customer-portal query filtered by the JWT's client_id

Two clients of the same MSSP are not separated by schema — they share the tenant schema and are kept apart by the client_id filter in the auth middleware (the MVP's requireClientAccess guard). This means:

• The Admin cross-client dashboard is an ordinary intra-schema query grouped by client_id — no cross-schema UNION (which Multi-Tenancy lists as a tradeoff to avoid).
• A client-level right-to-be-forgotten is a DELETE … WHERE client_id = ? within the schema; a tenant-level erasure is DROP SCHEMA tenant_xxx CASCADE.
• client_id is therefore a required, indexed foreign key on nearly every business table — it is the intra-tenant partition key, and the primary defense for customer-portal data.

Confirm before build: this "tenant = Admin account, client = intra-tenant org" reading is the only one consistent with the cross-client Admin dashboard and the customer-portal isolation model. It should be ratified explicitly, because an alternative reading (client = tenant = schema) would force cross-schema aggregation on every Admin screen and is rejected here.

---

Conventions (target state)

Applied to every table unless noted. These upgrade the MVP's SQLite-era choices to the locked Postgres 16 + Drizzle stack.

Concern	MVP (SQLite)	Target (Postgres 16)
Primary key	TEXT (UUID string)	uuid PK, default gen_random_uuid()
Timestamps	TEXT datetime('now'), columns created_date / updated_date	timestamptz, columns created_at / updated_at; updated_at maintained by trigger
Flexible/nested data	TEXT holding JSON (questions, answers, findings, metadata, …)	jsonb (see "JSONB vs normalized" below)
Status / type enums	free TEXT	Postgres enum or text + CHECK constraint — never free text for lifecycle states
Client scope	client_id TEXT REFERENCES clients(id)	client_id uuid NOT NULL REFERENCES clients(id), always indexed
Authorship	created_by, created_by_id	created_by_id uuid REFERENCES admin_users(id); keep denormalized created_by_name only for display
Sample/seed data	is_sample INTEGER on every table	is_seed boolean — nonprod only; production tenants never carry seed rows
Referential integrity	FKs mostly declared, some (standard_id) missing	all FKs declared with explicit ON DELETE behavior
Soft delete	none	deleted_at timestamptz NULL on user-deletable business entities; hard-delete only for erasure/DR

JSONB vs normalized — the rule: keep jsonb where the shape is genuinely open or snapshot-like (a questionnaire's questions definition, a response's answers, AI metadata, a DFD's dfd_data). Normalize anything you filter, join, aggregate, or report on across rows. The MVP over-uses JSON blobs (e.g. workplans.tasks, system_dfd_reports.threats); the target state promotes those to real child tables (workplan_tasks, findings) so the dashboards, search, and reports can query them. Each such promotion is called out in the domain sections.

---

Entity catalog

Entities grouped by domain. Status column: MVP = exists in the 28-table production schema; New = target-state addition required by the user flows but absent from the MVP.

A. Tenancy & Identity

Entity	Status	Notes
clients	MVP	The MSSP's customer orgs. Carries portal-access toggle + access_expires_at (see Notifications & Scheduled Jobs for the annual-license job).
admin_users	MVP	MSSP analysts. Identity is federated via WorkOS; local rows hold profile, permission_level, permissions (RBAC).
customer_users	MVP	Customer-portal users, each client_id-scoped.
vendors	New	A client's third parties. Root of the entire Supply-Chain domain — the MVP has no vendor tables at all.

B. Frameworks & Standards

Entity	Status	Notes
standards	MVP	Compliance frameworks. requirements / controls are JSON blobs today.
standard_files	MVP	Uploaded source documents for a standard.
standard_clauses	New	Normalized output of the AI clause-extraction flow (clause text + plain-language explanation + the three requirement "lenses": general / IT-systems / supply-chain). Today these live inside the standards JSON blob; promoting them to rows is what lets questionnaires, risk mapping, and reports reference a clause by ID.

C. Questionnaires & Responses

Entity	Status	Notes
questionnaires	MVP	Master templates and per-client assigned copies (is_master_template, questionnaire_type = questions | table).
responses	MVP	A client's answers; table-questionnaire answers seed the system_details / process inventory downstream.
question_analyses	MVP	Per-question AI analysis (score, compliance status, recommendations).
category_reviews	MVP	Per-category review rollup of a response.
org_structure_questionnaires	MVP	Departments / roles / reporting lines — feeds training scope, BCP, supply-chain.

D. Policies

Entity	Status	Notes
policy_documents	MVP	Policy templates / AI-generated drafts.
client_policy_submissions	MVP	Client-uploaded documents for review (status lifecycle: pending → under_review → approved/needs_revision/rejected).
approved_policies	MVP	Finalized, versioned, published policy with expiry_date.
policy_annexes	MVP	Annex documents attached to a policy.
documentation_sets	MVP	Groups of required documents assigned to a client.

E. Evidence & Files

Entity	Status	Notes
client_files	MVP	General client uploads. file_url → S3 (see Architecture).
file_reviews	MVP	AI/human review of a client file.
system_documentation_files	MVP	Files attached to a system.
security_assessment_files	MVP	Uploaded technical security reports (pen-test / external-surface / code-review / assessment).

All file entities store only S3 object references, never bytes. The upload pipeline (type validation, malware scanning, text extraction for Search) is an architecture concern; the tables hold file_url, file_type, file_size, and extracted-text linkage.

F. Systems & Data

Entity	Status	Notes
system_details	MVP	A client IT system; list is derived automatically from table-questionnaire answers.
system_analyses	MVP	AI end-to-end system analysis (findings, risks, score, mitigated score).
system_dfd_reports	MVP	Data-flow diagram + trust_boundaries + threats. threats JSON should be promoted to findings (below).
data_type_analysis_records	MVP	Per-system data classification (types, sensitivity, retention, legal basis) — the privacy/data-mapping picture.

G. Security Findings

Entity	Status	Notes
findings	New	The MVP stores findings as JSON arrays inside security_assessment_files, system_dfd_reports, file_reviews, and system_analyses. Target state promotes them to one normalized findings table (severity, title, evidence, source entity, remediation, lifecycle open→in_progress→resolved). This is what makes the Security dashboards, per-finding remediation workplans, and cross-source risk aggregation possible — and is the anchor row for Search.
security_tools	New	The "Cyber Security" screen's tool inventory (endpoint / network / identity / cloud) and per-tool scan results. Absent from the MVP.

H. Risk Management

Entity	Status	Notes
risk_items	New	The Risk hub aggregates gaps from every domain (training, cyber, supply-chain, systems, processes) and maps each to NIST 800-53 controls. The MVP has no risk register.
risk_decisions	New	Per-gap decision: accept / mitigate / transfer / avoid. A mitigate decision emits a workplan_task.

I. Supply Chain / Vendors — entirely New

Entity	Status	Notes
vendors	New	(also in domain A) The vendor register.
vendor_risk_assessments	New	Per-vendor, per-standard gap analysis + score.
vendor_system_links	New	Which client systems each vendor supports; service criticality + SLA.
scrm_policies	New	Generated Supply-Chain Risk-Management policy per client.

Vendor questionnaires and vendor files reuse questionnaires / client_files with a vendor scope rather than new tables.

J. Training — entirely New

Entity	Status	Notes
training_programs	New	Per-department programs generated from a standard.
training_kits	New	Instructor/content kits, by department + standard.
training_sessions	New	Scheduled sessions + the one-time 24h public employee link.
annual_training_plan	New	Month-by-month, two-year schedule matrix.
training_submissions	New	Employee post-training questionnaire answers + competency scores; gaps flow to Risk Management.

K. Business Continuity (BCP) — entirely New

Entity	Status	Notes
processes	New	Operational processes, classified to departments; derived from table questionnaires.
process_dependencies	New	Which systems/vendors each process depends on.
process_costs	New	Cost inputs + manual-vs-AI cost comparison.
bia_records	New	Business-impact analysis per process (RTO/RPO/MTD, impacts, criticality, recovery actions). Uses the TimescaleDB extension for continuity time-series.

L. Remediation & Outputs

Entity	Status	Notes
workplans	MVP	Per-client remediation container.
workplan_tasks	New	The MVP stores tasks as a JSON blob in workplans.tasks; promote to rows so tasks are assignable, trackable, and reportable (owner, status draft→approved→in_progress→completed, AI effort/cost estimate, source gap).
reports	New	Generated formal report artifacts (language, sections, S3 PDF URL, status). See Report Generation. The MVP generated PDFs ad hoc with no persisted report entity.

M. Platform / Cross-cutting

Entity	Status	Notes
audit_events	New	Append-only, per-tenant-schema audit trail (DELETE/UPDATE revoked at the DB role level) — supersedes the MVP's partial activity_logs. See Observability Tier 1.
activity_logs	MVP	The MVP's broad activity feed; folded into audit_events in the target state (kept as a view for compatibility).
notifications	New	In-app notification inbox + delivery records. See Notifications & Scheduled Jobs. The MVP had only email_logs.
email_logs	MVP	Transactional-email delivery log (AWS SES).

---

Core relationships (ER diagram)

The spine of the model — the proven MVP core plus the key normalized additions. Domain-specific entities (training, BCP, vendor sub-tables) hang off clients following the same pattern and are omitted here for legibility.

---

How data converges (the aggregation spine)

The product's defining pattern: table-questionnaire answers fan out into inventories, every analysis emits gaps, and all gaps converge on Risk + Workplans + the Report. The data model must make each hop a real foreign-key relationship, not a JSON blob, or the convergence screens can't be built:

table questionnaire responses
        │  (seed)
        ├─► system_details ──► system_analyses ──► findings ─┐
        ├─► processes ──► bia_records / process_costs ───────┤
        └─► (org structure) ──► training_* ──► gaps ─────────┤
                                                             ▼
   vendors ──► vendor_risk_assessments ──► gaps ──►   risk_items ──► risk_decisions
   security uploads ──► findings ──────────────────►      │ (mitigate)
                                                          ▼
                                                   workplan_tasks ──► workplans
                                                          │
                                                          ▼
                                                       reports (final PDF)

This is why findings, risk_items, and workplan_tasks are promoted out of JSON in the target state: they are the join points of the whole platform.

---

Migration note

The move from the MVP SQLite schema to this target Postgres model is not a fresh start — it carries real customer data. The type mapping (TEXT→uuid/timestamptz/jsonb), the JSON-blob-to-child-table promotions, and the single-MSSP→schema-per-tenant reshaping are the substance of that migration and are owned by the separate migration plan, not this document. The purpose of this page is to define the target shape that migration must land on.