Skip to main content

ADR-007 — Per-client data / memory / assistant architecture (4 layers; schema-per-client + pgvector)

canonical · tech-lead · updated 2026-06-22 · source

Status​

Accepted (2026-06-22). Builds on the closed-SaaS delivery model (tedos-business-model.md), the as-built product System of Record (comprender-mvp-e1.md), the engine + connector pattern, and the dev-workflow/product split in ADR-006. First-effort scope is tracked by issue #633 (Layers 1–2).

Context​

  • The product promise is a per-client operations brain — collect each client's data, accrete their memory from day one, and later offer an in-business chat over that data — not a generic CRUD admin. "Service outside, product inside": the reusable core is what makes each delivery cheaper than the last.
  • tedos has a per-objective AI task orchestrator with connectors, a human gate, a council, evals, and per-tenant credentials (packages/engine), fronted by a Fastify API. Effort E1 (comprender-mvp-e1.md) shipped the first product System of Record — @tedos/domain (Drizzle/Postgres) for project / person / person_identity with match-key dedupe — but it is structured data only: no memory, no ingestion-from-black-boxes, no assistant yet, and the SoR lives today in a single product schema, not yet isolated per client.
  • The first real client (Comprender) exposes exactly the missing pieces: per-client isolation, event-driven ingestion from external black boxes, per-client memory, and the substrate for the future assistant.
  • Two product constraints shape the data layer:
    1. Clients own and can export their data — "SaaS yes, hostage no" (tedos-business-model.md §1). Isolation must make a clean per-client export trivial.
    2. Tenant/client data must NEVER mix with the dev-workflow corpus (ADR-006 hard rule). The product System of Record is a separate database from the Plan Engine graph.
  • This needs an ADR now because Layers 1–2 are being built (#633) and the isolation + memory decisions are foundational — they are expensive to reverse once client data lands.

Decision​

One headline: a per-client operations brain in four layers — structured SoR, event-driven ingestion, per-tenant memory (activity log now + pgvector next), and an assistant that is retrieval over SoR + memory through the existing engine.

The four layers​

  1. System of Record (structured "what is"). Per-client structured domain data (person/identity now; offering, enrollment, payment, certificate next), modeled in @tedos/domain (the product SoR package). Nouns and state.
  2. Ingestion. Connectors + tenant inbound webhooks → upsert the SoR and append the activity log, event-driven: external black box → webhook → SoR upsert → enqueue objective (generalizes the existing github-webhook HMAC ingress pattern). No new state-machine engine — state lives in the SoR, transitions are short engine objectives.
  3. Memory. Two stores per tenant:
    • a per-tenant activity log — append-only event table, written on every SoR write (typed logActivity()). Built from day one.
    • pgvector semantic memory — embeddings of the client's content/events for retrieval. Built in the next effort (after Layers 1–2), in the same product Postgres (no separate vector DB).
  4. Assistant. A per-client chat over SoR + memory, implemented as retrieval + the existing orchestrator/evals/human-gate (no new engine). Staff-facing first (the client's back-office), end-user chat later. Built in a later effort.

Locked decisions​

#DecisionWhat it sets
D1Schema-per-client isolation. One Postgres cluster; a schema per project. A control/registry schema holds the project list; each client's data lives in its own schema.@tedos/domain provisions a schema per project; a tenant-scoped DB accessor selects the schema; the existing person/identity tables become the per-client template.
D2pgvector in the product Postgres for semantic memory. No separate vector DB.Memory embeddings live beside the SoR in the same cluster; one operational surface, one backup, one export.
D3Activity log from day one. Append-only per-tenant event table, written on SoR writes.Memory accretes from the first write, so the assistant is later just retrieval — not a backfill project.
D4Chat = retrieval + the existing engine. No new engine.The assistant reuses executeObjective + evals + the human gate (packages/engine); it adds a retrieval step over SoR + memory, nothing more.
D5Staff chat before end-user chat.First consumer is the client's back-office (Client OS staff); end-user (student/customer) chat is a later, separate decision.

Scope boundaries​

  • Product data only. This decision governs the product System of Record + memory + assistant. The dev-workflow Plan Engine graph (ADR-004/005/006) is a separate database and is out of scope (see Hard rules).
  • Tenant connectors are product, not workflow — they operate on the client's systems, never on our dev-workflow git-mechanics (.claude/rules/kit-engine-boundary.md, .claude/rules/workflow-vs-product.md).
  • The engine itself is untouched — growth is additive (SoR schema, webhook ingress, merge service, the black-box connectors).

Consequences​

Positive

  • Clean per-client export. Schema-per-client (D1) makes "give the client their data" a schema-level dump — directly serving the "clients own + export" promise (tedos-business-model.md §1).
  • Strong isolation without per-client operational overhead — one cluster, one set of migrations applied per schema, one backup/restore story.
  • Memory by construction (D3) — the assistant (Layer 4) becomes retrieval + engine, not a data backfill.
  • One operational surface for vectors (D2) — no second datastore to run, secure, back up, or export.
  • No new engine (D4) — the assistant inherits the existing reliability layer (evals, gate, trace).

Negative / trade-offs

  • Schema-per-client has a scale ceiling. Connection-pooling and migration fan-out grow with the number of schemas; very large schema counts (low thousands+) strain a single cluster and search_path routing.
  • Migrations must run per schema — provisioning and schema-evolution tooling is net-new in @tedos/domain (vs a single shared schema).
  • pgvector in the product Postgres couples vector and transactional load on one cluster; if vector query volume grows, it competes with SoR queries (revisit only if it becomes a measured bottleneck).

Isolation-model escalation path (D1 chosen deliberately as the middle option):

ModelIsolationExportCost / scaleVerdict
Shared DB, tenant_id columnweakest (one bad query crosses tenants)hard (filtered dumps)cheapest, scales furthestrejected — fails the isolation + clean-export promise
Schema-per-client (one cluster)strong (schema boundary)clean (per-schema dump)low overhead until schema count is highchosen
Database-per-clientstrongestcleanesthighest ops costescalate later — for a client demanding hard data residency/compliance, or when schema count strains the cluster

Escalate to DB-per-client when: a client contractually requires a dedicated database / data-residency guarantee, or the cluster's schema count / migration fan-out / pooling becomes a measured operational ceiling. The tenant-scoped accessor (D1) is the seam that makes that move mechanical rather than a rewrite.

Sequencing

  • Layers 1–2 first (this effort, #633): schema-per-client SoR + ingestion contract + activity log.
  • pgvector semantic memory next (the effort after).
  • Assistant later (staff chat first, D5).
  • Connectors per the program — the ingestion contract ships a connector seam now; real connectors (Moodle, store, events, payments/bank, CFDI, WhatsApp) land in E2+.

Hard rules​

  • Tenant/client data NEVER enters the dev-workflow graph (ADR-006). The product SoR + memory are a separate database from the Plan Engine graph — two datasets, never mixed.
  • Per-client isolation is enforced by the schema boundary, not by query discipline — all SoR/memory access goes through the tenant-scoped accessor (D1).
  • Secrets/PII never leak across tenants and never enter the dev corpus; fiscal PII stays masked (precedent: connectors/invoices.ts).

Alternatives considered​

OptionWhy not
Shared DB with a tenant_id columnWeakest isolation; filtered dumps make the "clean export" promise fragile (see escalation table).
Database-per-client nowStrongest isolation but highest ops cost for a solo operator; deferred as an escalation path, not the default.
A separate vector DB (e.g. a dedicated vector store)Second datastore to run/secure/back up/export; pgvector in the product Postgres keeps one operational + export surface (D2).
A new long-running workflow/state-machine engine for the assistantThe existing orchestrator + SoR-held state covers it; the assistant is retrieval + engine, not a new engine (D4).
End-user chat firstHigher blast radius / lower control; staff-facing chat is the safer first consumer (D5).

References​