Data Stores (the polyglot decision)

⚠ Decision update (D1, see 00 + 02): the build now starts single-store — Postgres 16 + pgvector + jsonb for M0–M1. The polyglot design below (adding MongoDB Atlas for documents/RAG) is the documented migration target for when document ingestion grows heavy, not the M0 starting point. Read this file as "the target architecture + the consistency machinery you'll need if/when you add Mongo." For M0, the RAG corpus is pgvector tables in the canonical DB (one store, one backup, transactional consistency, no outbox).

What persists where, and why. This is the most consequential decision in the build — the database model is the hardest thing to change after you have customers.

The split

Why both Postgres and Mongo

• Postgres is the truth. Entities, events, cases, and the index of observations need joins, constraints, transactions, and row-level security. The analyst's grounding ([OBS-####]) must resolve to a guaranteed-consistent row. This is non-negotiable and stays relational.
• Mongo is the shape-shifter. Raw payloads from 40+ heterogeneous sources don't fit one relational schema, RAG chunks are document-shaped, and Atlas Vector Search gives first-class semantic retrieval without bolting a vector extension onto the OLTP database. Putting these in Mongo keeps the canonical DB lean and fast.

Decided (D1): polyglot. Postgres is the system of record; MongoDB holds documents + the RAG corpus (Atlas Vector Search). The single-store fallback (Postgres + pgvector + jsonb, drop Mongo) is documented in case document ingestion stays light — but the build target is polyglot. The outbox worker below is decided: Cloud Tasks (not a polling loop).

Consistency model (how the two stay in sync)

Postgres is authoritative. Mongo is a projection written by the ingest pipeline in the same logical unit of work:

ingest(observation):
  tx = pg.begin()
  obs_id = pg.insert_observation(...)        # canonical, signed
  pg.attach_or_create_entity(obs_id)         # resolution
  pg.derive_events(obs_id)
  tx.commit()                                # PG is now source of truth
  # after commit (outbox pattern, at-least-once):
  mongo.upsert_raw(obs_id, raw_payload)
  if textual(raw_payload):
     chunks = chunk(raw_payload)
     vectors = embed(chunks)
     mongo.upsert_rag(obs_id, chunks, vectors)
  pg.notify("session_"+session_id, {...})

Use the transactional outbox pattern: the post-commit Mongo writes are enqueued in a Postgres outbox table inside the same transaction, then drained by a worker — so a crash never loses the projection and Mongo is eventually consistent with Postgres. Mongo docs always carry the canonical obs_id/entity_id so anything in Mongo can be re-grounded against Postgres.

Rule: nothing is true until it's in Postgres. Mongo never holds a fact Postgres doesn't. If Mongo and Postgres disagree, Postgres wins and Mongo is rebuilt from it (the projection is reproducible).

Isolation per tenant

• Postgres: one Cloud SQL instance per tenant (silo). RLS is defense-in-depth, not the primary boundary. (See security/43.)
• Mongo: one database per tenant inside a per-tenant Atlas project (preferred) — Atlas project = billing/network/access boundary. Cheaper alternative: shared project, DB-per-tenant, distinct DB users scoped to their DB. Decided: one Atlas project per tenant (project = isolation/billing/network boundary).
• GCS: one bucket per tenant, CMEK-encrypted with that tenant's KMS key.
• Redis: prefer per-tenant logical separation (key prefix t:<tid>:) on a shared instance for cost; per-tenant instance for strict isolation.

Backups & DR

• Postgres: automated daily backups + point-in-time recovery (PITR); cross-region backup for prod. Tested restore runbook.
• Mongo: Atlas continuous backups; because Mongo is a projection, a full rebuild from Postgres is also a valid recovery path.
• GCS: versioned buckets; lifecycle rules for cost.
• Decommission: 30-day retention snapshot of PG + Mongo + bucket before deletion (matches the control-plane danger-zone flow in §17).

Data classification

Gaps to close

• Confirm the embedding dimensions/model (affects the Vector Search index config — D5).
• Define the outbox worker (Cloud Tasks vs. a polling loop) and its retry/poison-message handling.
• Set RPO/RTO targets per plan tier and write the restore runbook.