ADR-CORTEX-DB-ARCHITECTURE: the cortex memory storage substrate

ADR-CORTEX-DB-ARCHITECTURE: the cortex memory storage substrate

Status: Proposed — nothing in this ADR is built, deployed, or enforced.
Date: 2026-08-03
Closes: gap G-W1 (delivery/CORTEX-MASTER-GAP-REGISTER-2026-08-02.md:260)
Detail: [[delivery/CORTEX-DB-ARCHITECTURE-2026-08-03.md]] (the full design; this ADR is the record)
Supersedes on the substrate question: the stale services/cortex-memory/README.md "Operating
constraints" text (Postgres + pgvector + 1536-dim), which describes a scaffold that was never built.

Context

The direction was decided on 2026-06-21 and is not re-litigated here.
delivery/CORTEX-MULTI-TENANCY-ARCH-REVIEW-CHARTER-2026-06-21.md Part 5 records the operator locking
*Option C — "a single, elastic, multi-tenant memory PLATFORM with logical isolation (RLS on the
principal tuple), NOT per-agent/per-tenant databases & files." Option B (per-tenant DB) was rejected;
Option A (namespace-in-place on SQLite) survives only as phase-1 of C. The charter then deferred the
implementation: "Tech detail deferred … the DIRECTION is locked; the implementation is designed at
build time."*

It was never designed. G-W1 records the consequence verbatim: "The corpus's most-repeated storage
recommendation. Code is SQLite + a separate FAISS index. … No decision record." *This ADR is that
missing record.*

What is actually deployed (measured 2026-08-03, not asserted)

Estate A = /var/lib/cortex-memory, 950 MB / 2,565 files, 100% SQLite + FAISS + JSONL. There is
no Postgres driver in the service (pyproject.toml:18-62); its own cert.manifest.yaml:201-215 marks
planned_postgres "ASPIRATIONAL … no code references it yet."

The defects that decide this ADR are isolation and deletability, not load:

• 196,754 rows carry no tenant column at all — entity_graph.relationships (141,552) and

.mentions (55,202) in one 62 MB file shared by 8+ tenants, plus optimizer.db.retrieval_feedback
(11,794 rows of raw user query text, no tenant column, no retention).

• There is no purge(tenant_id) operation anywhere. Teardown composes per-user primitives in a loop.
• tenant_id is an unvalidated free-text string. Any string that reaches the service permanently

provisions storage. *909 namespaces exist; 900 would be rejected by a registry gate; 9 are
candidate-real. iam.tenants holds 2 rows, and its overlap with those 909 is zero* — the
control plane and the data plane have never met.

• Supporting: 91.5% freelist with grep VACUUM = 0; no schema-version primitive

(PRAGMA user_version/alembic = 0); ~10× content duplication (9.97× for kulbir, 11.06× fleet-wide);
no connection pool across 24 sqlite3.connect sites.

Load is not the reason. The service has averaged 2.7% of one core over 14 days (32,155 s CPU /
1,191,480 s elapsed). Any argument for this change from performance is unsound; the argument is that a
filename prefix is not a tenant, and that is unfixable in a directory of SQLite files at any speed.

Decision

*One Postgres substrate, cortex schema family, shared tables partitioned by tenant, isolation
enforced by FORCE ROW LEVEL SECURITY on the app.tenant_id GUC — the idiom already shipped by
cortex's own control plane* (db/migrations/20260722000000_cortex_maas_control/migration.sql,
services/cortex-maas-control/.../repository.py:142-144,205-211).

Specific rulings, each contested and resolved by adversarial review:

1. Size-routed vector isolation. Long-tail tenants live in pool partitions carrying *no ANN index
at all (exact scan); only promoted "whale" partitions get HNSW. This satisfies P-8 — the tenant
predicate constrains candidate generation — which a shared HASH-bucketed HNSW does not, because
pgvector filters after the graph is traversed.
2. tenant_id text with wire/RLS identifier ten_<ULID> (Crockford base32). Operator ruling
2026-08-03. Retyping iam.tenants.id (uuid) plus the mission/apibff planes and their FKs is
priced as real cross-plane work, not as free — the "0 rows, therefore free" premise was false.
3. RLS policies are PERMISSIVE + RESTRICTIVE twins, never RESTRICTIVE-only. Verified empirically on
PG 16.12: a restrictive-only policy returns 0 rows for the correct tenant with the GUC correctly
set. Two candidate designs would have shipped a total outage.
4. Every partition carries its own policy. Verified: partitions do not inherit RLS (parent
t/t, child f/f).
5. halfvec(384), schema-qualified. 776 B/row vs 1,544 B for vector(384). The vector extension
is installed in the apibff schema against search_path = mission, iam, public, so unqualified
vector/halfvec do not resolve (ERROR: type "halfvec" does not exist).
6. L4 is a log-index split, not a table. Bytes stay append-only and hash-chained in immutable
objects; Postgres holds only (tenant_id, seq, ts, prev_hash, this_hash, payload_ref) with monthly
RANGE partitions and BRIN. This is better on erasure than moving the bytes in — deleting a payload
leaves the hash rows, so link-to-link continuity still verifies, whereas a DELETE on in-table bytes
breaks the chain. It also answers the operator's "why not Splunk" architecturally: an index over
immutable segments, which is Splunk's architecture without Splunk.
7. Registry-as-gate, born armed in B. A tenant must exist in the registry before storage is
provisioned for it. Estate A is measured externally, never gated — zero code change to a service
under concurrent modification (operator ruling 2026-08-03).
8. optimizer.db raw query text is deleted, not migrated. Unattributable PII must not be imported
into the new isolation boundary.
9. No new engine. Elasticsearch/OpenSearch/Splunk/ClickHouse are rejected on arithmetic, not
prejudice: 1,000 indices means ~25 MB shards against a 10–50 GB target, and the viable
shared-index + _routing pattern is weaker* isolation than engine-enforced RLS. Redis for L1 is
rejected on evidence — the service is a single process, so the multi-worker premise is false.
L2.5's "lost on every restart" is an unset env var (SYMPOSIUM_GRAFOSA_PERSIST_DIR), not an
architecture defect.

Consequences

Accepted, with the cost stated rather than absorbed:

• The 10,000-tenant target is not met on this host — ≈1,700–3,700 tenants, ≈500 at 10× users, with

disk and HNSW residency converging. The ruled 1,000-tenant target fits; headroom beyond it does not
exist on current hardware. This overrides all three candidate designs and all three judges.

• The covering index duplicates the vector — ~2× vector storage for ~10× fewer pages.
• The fast path needs ~28 lock slots against a 16-slot budget — a throughput gradient, not a cliff.
• Relation count roughly doubles the instance's (~2,270 against ~2,823 existing).

Residual risk the operator carries (full list in the design §7):

• conduir is rolsuper=t rolbypassrls=t. Every policy here is bypassable by that credential — the

database twin of enforce_admins=false.

• archive_mode = off — no PITR, unbounded RPO. Promoted to a Phase-1 blocker, because the

corrected R-1 finding shows estate A has no usable backup either: 0 of 193 hourly snapshots
contain production data (the _cid() substring-resolver defect), and the schedule was disabled at
12:27 UTC 2026-08-03. The migration does not destroy per-tenant recovery; *it inherits an estate
that never had it.*

• Every latency figure is derived, not measured; the promotion threshold moves ~7× on one index

decision. §8 Q-1 gates committing the threshold on measurement.

• Tenant isolation is database-enforced; branch and project_id isolation are application-enforced.
• RLS timing side channels are mitigated only by the assumption that tenants never receive raw SQL — a

property of the API surface, not of RLS.

Alternatives rejected

Per-tenant database (charter Option B) — rejected by the operator 2026-06-21 on the ruled tenant
shape (shared-SaaS, many small tenants). Namespace-in-place on SQLite (Option A) — cannot make
isolation a DB-enforced invariant; survives only as the data model C's policies are defined over.
tenant_id uuid — rejected: live tenant values are human slugs, and the team plane already chose
text so RLS compares text to a text GUC without a cast. Apache AGE for L2 — rejected on published
measurement ([r*..N] sequential-scans; recursive CTEs win). HASH-64 buckets with HNSW everywhere —
rejected under P-8, and it fails its own isolation probe at any tenant count above 64.

Verification

Nothing here is built. The design's claims were verified read-only against the live estate and
conduir-postgres (PG 16.12); the five load-bearing security/durability claims above were
re-verified independently of the agents that produced them. *This ADR has not yet had cross-family
swarm review* — that gates acceptance, per CLAUDE.md §0.