> ## Documentation Index
> Fetch the complete documentation index at: https://docs.findmydata.io/llms.txt
> Use this file to discover all available pages before exploring further.

# ADR 0028 scale out architecture

# ADR-0028: Scale-out architecture — measured envelope, async seam, Postgres backend

**Status:** Accepted · 2026-07-16 — Phase A shipped (banding fix 26.7.16.6); **Phase B complete** (async `DbClient` seam + full \~840-site cutover, 2026-07-17); **Phase C complete** (2026-07-17, 26.7.17.15): dialect layer, `PostgresDbClient`, consolidated PG schema, `FOR UPDATE SKIP LOCKED` queue, tsvector FTS — the entire suite runs green on real Postgres 16 (selected by `FMD_DB_BACKEND`). **Phase D COMPLETE** (2026-07-18, released 26.7.18.16): pluggable `ObjectStore` (local / S3 / Azure-Blob, streaming); pgvector embedding index (real `vector` column + HNSW + `<=>`, auto-detected, with the LSH path as the SQLite/no-pgvector fallback); packed simhash bands (`band0..3` columns, `fingerprint_bands` dropped) + promoted hot-facet columns; and a dual-backend batched load harness. **Load-validated on real Postgres 16 to 2M assets** with complete metrics (11.8M rows, 7.1k assets/s, packed-band lookup p95 \~20 ms, pgvector-HNSW neighbour p95 \~2.5 ms) — a single-process 10M synthetic run exceeds the 4-CPU/5.77 GiB dev VM's RAM (a box limit; per-asset cost and ingest rate are flat from 100k→2.6M, so the envelope extrapolates linearly). The at-scale run flushed out a real SQLite planner regression in the packed-band retrieval (a 4-way `OR` scanned every simhash row — 2.1 s p95 at 1M; rewritten as a `UNION` of single-band index probes → 20 ms, 100×) and an adversarial review flushed out a pgvector-extension install race — both fixed. CI now certifies both backends (Postgres+pgvector service job). Next scale-out step (evidence-gated): tenant/asset partitioning + a true 10M+ run on production-class hardware

## Context

The operator directed that the product "needs to ship as a scalable / robust
solution." The design target ([capacity model](../capacity-model.md)) is a single
large organization at \~100k identities and up to 500M file-like assets; the
current implementation is one SQLite file behind one process (ADR-0003).

Two evidence-gathering exercises ground this ADR:

1. **A 1M-asset load measurement** (2026-07-16, single dev machine): 1M assets
   ingest in \~4.3 minutes at pure-storage speed, \~2.4 GB on disk, permission-set
   dedup **274:1** (better at scale than the 7–23:1 measured at 5k), bounded
   container-listing p95 **86 ms**. Storage and interactive queries are NOT the
   single-node bottleneck; the **single writer** and **no replication/HA** are
   (capacity model §5).
2. **A code inventory of the porting surface** (two independent inventory agents
   * an assessment pass): **569 synchronous SQL call sites across \~51 files**;
     25 synchronous transaction closures; the ADR-0003 "role-specific store
     interfaces" **do not exist as code** (the seam is table-naming discipline —
     the only injected, genuinely swappable store today is `ObjectStore`); the
     outbox table exists in schema but has **zero code**; FTS5 is contained to 2
     source files; `json_extract` to 2 files (28 sites); the SQL dialect is
     otherwise conservatively portable (`ON CONFLICT` upserts throughout, no
     AUTOINCREMENT/rowid/strftime, app-generated TEXT ids, epoch-ms integers).

The inventory also surfaced a **correctness bug independent of any port**: simhash
candidate banding was implemented as 16 × 4-bit bands (16 possible values per
band ⇒ each bucket matches \~1/16 of the whole corpus), so near-duplicate
candidate retrieval degenerated with corpus size and the `LIMIT 200` silently
destroyed recall. Fixed in 26.7.16.6 (see Decision 1).

## Decisions

### 1. Phase A (now): certify and harden the single-node envelope

* **Publish measured numbers, not extrapolations** — done for 1M assets
  (capacity model §7 stage 3). The supported single-node envelope is **1–5M
  assets**; beyond that, the Postgres path below is the growth story. SQLite
  **remains the default** for evaluations and small/mid deployments — a
  zero-dependency single container is a genuine product advantage, and "scales
  down" is part of shipping robust.
* **Simhash banding fixed to the designed 4 × 16-bit bands** (fp-v3): recall is
  guaranteed for close copies (Hamming ≤ 3) while buckets stay selective
  (65,536 values per band). Because realistic template variants of business
  documents measure Hamming \~8–16 (simhash bit margins grow as √shingles while
  edit churn is linear — no selective banding can guarantee that range;
  fp-v2 only *appeared* to, at toy scale), near-duplicate candidate retrieval
  now has a **second bounded source**: the deterministic-default embedding
  index (`queryNeighbors`, LSH + rerank, topK 50). Both sources feed the same
  verifier — simhash Hamming ≤ 16 — so the *meaning* of `near_duplicate` is
  unchanged. Migration 0015 rebuilds existing band rows in place; the
  fingerprint algo version bump (fp-v3) regenerates bands on re-scan via the
  stage cache key.
* Band storage drops from 16 to 4 rows per fingerprint (the 500M-asset
  projection falls from \~2.9B to \~0.7B band rows; Phase D packs them into
  columns and deletes the table).

### 2. Phase B: one async data-access sweep — the real porting seam

The prerequisite for ANY second backend is asynchrony: bun:sqlite is
synchronous; every Postgres client is async; async propagates transitively. Do
this as **one short, codemod-assisted sweep**, not a long module-by-module
program (a half-converted codebase is the worst state to linger in):

* Introduce a thin **async `DbClient`** interface (`query/get/all/run` + an
  async `withTx` shaped for a pooled connection) replacing the `Db = Database`
  alias; convert all \~613 call sites; restructure the transaction closures
  (centralized through the `withTx` helper, not raw `db.transaction`). SQLite
  remains the only backend (sync calls wrapped async). Behavior-preserving; the
  test suite is the harness.
* **The seam is built and proven** (`kernel/dbclient.ts`, 2026-07-17). The
  load-bearing correctness decision it resolves: `bun:sqlite`'s
  `db.transaction(fn)` requires a **synchronous** `fn`, so once transaction
  bodies become `async` they yield the event loop and, on a single shared SQLite
  connection, another request's writes could interleave INSIDE an open
  transaction — silently breaking the atomicity the sync code got for free. The
  SQLite adapter therefore **serializes every operation through a per-connection
  async queue**: a transaction holds the queue for its whole duration (BEGIN
  IMMEDIATE … COMMIT/ROLLBACK issued manually around the awaited body), and its
  inner statements run on a "direct" client that does not re-enter the queue.
  This matches SQLite's single-writer model (no write concurrency is being given
  up) and preserves per-transaction atomicity exactly; a future Postgres adapter
  drops the queue for a pool + a dedicated connection per transaction. Proven by
  `dbclient.test.ts` (50 concurrent read-modify-write transactions lose no
  updates; a bare statement never slips inside an open transaction's window;
  rollback + nested-flatten).
* **The cutover is DONE** (2026-07-17): all \~840 call sites (619 non-test + 221
  test) converted onto the seam, \~40 `withTx` closures restructured to use the
  `tx` handle, async propagated to the already-async HTTP/worker roots, and
  entrypoints wrap the raw connection with `asyncClient` after running migrations
  synchronously. Driven by `tsc` as the exhaustive checklist. The one class `tsc`
  cannot see — a synchronous Hono handler passing an async function into
  `c.json({...})`, which serializes an unresolved Promise — was caught by the
  test suite (teams/exchange topology routes) and a systematic audit of every
  remaining sync handler (`/api/actions`). Behavior-preserving: `tsc` clean, 520
  tests pass, no deadlocks, lint clean; a 6-group adversarial review
  (missing-await in value/boolean/loop contexts, transaction atomicity,
  callback-restructure semantics) returned **zero findings**. SQLite remains the
  only backend.
* **Do not build the ADR-0003 role interfaces** — they don't exist and aren't
  needed; read paths legitimately join "role" tables against the system of
  record in single statements. The DbClient + a dialect layer is the seam.
  (This corrects ADR-0003's code-level claim; its schema/semantics contracts
  remain valid.)
* Fold in two cheap de-riskers: consolidate the vendor/account inline DDL
  (including the ad-hoc `ALTER TABLE` loop) into `migrations/` so Phase C faces
  one schema surface instead of three, and rewrite the 5 `INSERT OR IGNORE`
  sites to `ON CONFLICT DO NOTHING` (the syntax 36 other sites already use).

### 3. Phase C: Postgres backend behind the DbClient (config-selected)

* **Dialect layer** (thin, by measurement): `?`→`$n` placeholders,
  `json_extract`→`jsonb ->>` (28 sites, 2 files — including the analyst
  executor's dynamically composed fragments, which the dialect layer must
  cover), `GROUP_CONCAT`→`string_agg` (1 site), epoch-ms as BIGINT, boolean
  mapping.
* **Work queue first**: `leaseNext` is correct today only under SQLite's
  single-writer serialization; on Postgres it becomes the single-statement
  `UPDATE … WHERE id = (SELECT … FOR UPDATE SKIP LOCKED) RETURNING`. The other
  queue guards (heartbeat/complete/fail keyed on `lease_owner` + status with
  `changes === 0` handling) are already race-safe under MVCC. **Exit
  criterion:** all 10 `.changes`-based concurrency sites re-validated under
  Postgres concurrency.
* **FTS moves here, not Phase D**: a backend that cannot serve `/search` is not
  shippable. FTS5 → tenant-scoped `tsvector` + GIN + `websearch_to_tsquery`
  (2 files), which also fixes FTS5's cross-tenant posting-list coupling.
* **Dual-backend contract test suite** runs the full suite against both engines.
* Optional hardening: Postgres row-level security on `tenant_id` — the
  enforcement ADR-0003 promised but convention currently provides.

**Built (2026-07-17), verified against a real Postgres 16 container:**
`kernel/pg-dialect.ts` (the measured translations — `?`→`$n` string-literal-safe,
`json_extract`→`jsonb ->>` with `::numeric` for the numeric facet fields,
`GROUP_CONCAT`→`string_agg`, DDL `INTEGER`→`BIGINT`); `kernel/dbclient-pg.ts`
(`PostgresDbClient` over Bun's native `SQL` behind the seam — per-tx pooled
connection via `sql.begin` so no serialization queue; int8→BigInt→number so call
sites stay backend-agnostic); `kernel/db-pg-schema.ts` (materializes the FINAL
consolidated schema from a fresh migrated SQLite DB — Postgres is a new backend
so the in-place rebuilds vanish — topologically ordered, idempotent, FTS5 →
`tsvector` + GIN + trigger). `FMD_DB_BACKEND`/`FMD_DB_URL` select it via one
async `initDb`; SQLite stays default. The dialect-specific paths are
backend-aware via `DbClient.dialect`: `/api/search` (`MATCH`→`@@
websearch_to_tsquery`) and the queue lease (`FOR UPDATE OF w SKIP LOCKED`, so two
workers on separate connections can't double-claim a unit). `pg-contract.test.ts`
(gated on `FMD_TEST_PG_URL`) runs the onboarding→scan→project→search flow +
queue-race on real PG (6/6 green; schema applies as 150 DDL / 85 tables). The
finishing step is wiring the **whole** 520-test suite against Postgres (the async
`openTestDb` re-plumb, or a CI matrix), not just the contract subset.

### 4. Phase D: storage roles + 10M+ validation — SHIPPED (26.7.18.16)

* **`ObjectStore` → pluggable `BlobBackend`** (local FS default / S3 via Bun's
  native client / Azure Blob REST) with a streaming `getStream()`. The purpose
  gate + artifacts metadata stay in the orchestrator (run before any backend byte
  read on every backend); a per-row `backend` marker makes a `FMD_OBJECT_STORE`
  switch non-destructive. Remote backends must be the operator's OWN bucket
  (content stays in the deployment's trust boundary); creds by `*_REF`.
* **Embeddings → pgvector**, contained to `vectorindex.ts` + the schema-apply
  path. Auto-detected: when the extension is present, a real `vector(256)` column
  * HNSW + the `<=>` operator replace the LSH round-trip; SQLite and
    Postgres-without-pgvector keep the LSH path (zero-dependency default preserved).
* **Fingerprint bands → packed `band0..3` columns** (the `fingerprint_bands`
  table is dropped); near-dup retrieval is a `UNION` of four single-band index
  probes (fast on both engines). **Hot facets** (provenance, confidence,
  labelState, riskSeverity) → real indexed columns on `search_documents`.
* **Partitioning: DEFERRED.** The target is one large org = one tenant, so
  Postgres tenant-partitioning yields a single partition and \~zero benefit;
  asset-hash partitioning is premature before real 10M numbers exist. Documented
  as an evidence-gated follow-up, not shipped.
* **Load-validated on real Postgres 16 to 2M assets** with complete metrics (see
  Verification). A single-process 10M synthetic run OOMs the 4-CPU/5.77 GiB dev
  VM (\~2.6M ceiling) — a box-RAM limit, not a schema or throughput limit; the
  per-asset cost (\~1.95 KB) and ingest rate (\~7.1k/s) are flat from 100k→2.6M, so
  the envelope extrapolates linearly. A true 10M+ run is gated on
  production-class hardware, not on the code.

### 5. Explicitly out (until evidence demands them)

* **External search engines** (Azure AI Search/OpenSearch) and **message-bus
  queues**: both require the outbox (zero code today) and denormalized search
  documents, and the queue doubles as an operational read-model that 7 modules
  query — replacing it re-architects campaign control. Revisit only if tsvector
  faceting or Postgres queue throughput measurably fails at target scale.
* **Per-tenant SQLite sharding / Litestream**: rejected — the target is a
  single large org; sharding doesn't split one tenant's corpus, and replicas
  don't relieve a write-heavy scan pipeline. Litestream buys durability, not
  scale.

## Consequences

* Enterprise conversations get an honest, measured story now (1M certified on
  one node; 274:1 permission dedup measured) with a concrete, code-grounded
  growth path — instead of an unvalidated 500M claim.
* The async sweep (B) is the big disruptive step; scheduling it as one sweep
  concentrates the pain and keeps the dual-backend phase mechanical.
* SQLite stays first-class: every phase preserves the zero-dependency default.

## Verification

Phase A (this release): 1M loadgen measured and published; banding property
tests (4×16 shape, pigeonhole ≤ 3 guarantee, selectivity, migration rebuild);
acceptance suite proves template variants still cluster via the two-source
candidate design; full suite 363 green. Later phases carry their own exit
criteria as listed above.

Phases B–D: the **entire test suite runs green on both backends** — SQLite (538
pass) and real Postgres 16 with pgvector (543 pass) — and CI runs both legs.
Phase D load measurements (dev VM: 4-CPU / 5.77 GiB / Bun 1.3.14; Postgres tuned
lean — `shared_buffers=128MB` — to survive the write-heavy load on the small VM,
which makes the cache-sensitive query p95s pessimistic vs a production-sized
buffer pool):

| Run                                | Ingest                        | Size          | Dedup | Notable query p95                                              |
| ---------------------------------- | ----------------------------- | ------------- | ----- | -------------------------------------------------------------- |
| 1M SQLite (new batched harness)    | 3.5k assets/s                 | 1.41 KB/asset | 274:1 | container-listing 97 ms                                        |
| 2M Postgres (core: bands + facets) | **7.1k assets/s**, 11.8M rows | 1.95 KB/asset | 327:1 | packed-band lookup **20 ms**, facet 771 ms\*                   |
| 500k Postgres + pgvector           | 3.6k assets/s                 | 3.49 KB/asset | 208:1 | pgvector-HNSW neighbour **2.5 ms**, band 3 ms, HNSW build 18 s |

The at-scale run earned its keep: it surfaced a real SQLite planner regression —
D3's 4-way band `OR` fell back to scanning every simhash row (**2.1 s p95 at
1M**), rewritten as a `UNION` of single-band index probes (**20 ms**, 100×,
recall-identical). A single-process 10M synthetic run OOMs this dev VM (\~2.6M
ceiling, box-RAM-bound), so 10M+ is validated by linear extrapolation from the
flat per-asset cost, pending production-class hardware.

\* the facet-filter benchmark omits the domain-id predicate the real `/api/assets`
query carries (which the `(tenant_id, domain_id, …)` indexes serve), so it is a
worst-case single-tenant scan; the packed-band and pgvector numbers use the
shipped query shapes.