3-Database Division of Labor

Overview

In enterprise Life Sciences AI architectures, selecting a single database engine forces unacceptable compromises:

• Relational databases struggle with high-dimensional vector similarity scans at scale.
• Vector databases lack ACID transactions, complex relational joins, and cryptographic audit ledgers.
• Graph databases excel at multi-hop relationship traversal but are inefficient for dense vector indexing or raw text storage.

The platform resolves this by enforcing a strict Three-Database Division of Labor:

Database Store	Primary Architectural Role	Query Engine / Syntax	Key Functional Strength	Anti-Pattern to Avoid
PostgreSQL 16 (pgdata)	Authoritative System of Record (SoR), 21 CFR Part 11 Audit Trail, Relational RTM	Relational SQL, GROUP BY, tsvector @@, Foreign Keys	ACID transactions, append-only triggers, cryptographic manifests	High-dimensional vector scans at scale
Qdrant (qdrant_data)	Semantic Retrieval Layer (Dense + Sparse)	Filtered ANN Search, Prefetch, Reciprocal Rank Fusion (RRF)	Sub-5ms retrieval latency, dense/sparse fusion, payload pre-filtering	Relational joins, aggregations, audit ledgers
Memgraph MAGE (memgraph_data)	Structural Memory & Knowledge Graph Reasoning	Cypher Graph Queries, BFS, WSP, Multi-Hop Paths	In-memory C++ speed, transitive ripple effect analysis	Unstructured text dumps, blob storage

PostgreSQL: Canonical Relational System of Record

PostgreSQL is the source of truth for all business and regulatory records:

• controlled_documents: Version-controlled SOPs, specifications, and protocols with document status (DRAFT, EFFECTIVE, SUPERSEDED, WITHDRAWN).
• document_chunks: Decomposed text chunks indexed with SHA-256 integrity hashes.
• rtm_items: Relational V-Model linkage between URS, FS, DS, and Test Protocols.
• deviations & change_controls: Operational quality event records.
• audit_trail: Append-only 21 CFR Part 11 ledger capturing every insert and state mutation.
• gxp_backup_executions: Ledger of cloud snapshots and manual Qualified Person executions.

Immutability Trigger Enforcement

PostgreSQL enforces 21 CFR § 11.10(e) by binding PL/pgSQL triggers to critical tables:

CREATE OR REPLACE FUNCTION prevent_audit_trail_tampering()
RETURNS TRIGGER AS $$
BEGIN
    RAISE EXCEPTION '21 CFR Part 11 Violation: Updates and deletes are prohibited on audit_trail records.';
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_audit_trail_immutable
BEFORE UPDATE OR DELETE ON audit_trail
FOR EACH ROW EXECUTE FUNCTION prevent_audit_trail_tampering();

Qdrant: Semantic Retrieval Layer

Qdrant (v1.13.4) indexes all document chunks using hybrid vector representations:

• Dense Vectors (4096 dimensions): Generated by qwen3-embedding:8b via Ollama or OpenAI text-embedding-3-large. Captures deep semantic meaning and conceptual proximity.
• Sparse Vectors (SPLADE / BM25): Captures exact keyword hits, regulatory codes (e.g. SOP-QA-045), and specific parameter values.
• Payload Indexing: Every vector point stores metadata attributes: document_id, system_code, gamp_category, is_superseded, and chunk_hash.
• Pre-Filtering: Queries apply tenant and system filters directly in the vector index prior to similarity scoring.

Memgraph MAGE: Structural Knowledge Graph

Memgraph provides in-memory graph reasoning over a Labeled Property Graph (LPG):

• Nodes: :Document, :Requirement, :Specification, :TestProtocol, :System, :Asset, :Deviation, :ChangeControl.
• Relationships:
  • (:Requirement)-[:SPECIFIED_IN]->(:Document)
  • (:TestProtocol)-[:VERIFIES]->(:Requirement)
  • (:Deviation)-[:IMPACTS_SYSTEM]->(:System)
  • (:Asset)-[:CONTROLLED_BY_SOFTWARE]->(:System)
  • (:ChangeControl)-[:MODIFIES]->(:Requirement)
• Multi-Hop Traversal: When an engineering change control is submitted, Memgraph traverses downstream edges to instantly identify all test protocols, SOPs, and physical assets that must be re-qualified.