A health-record index is most vulnerable while it is being rebuilt. A source database can disconnect halfway through a large table. An embedding process can fail after thousands of rows. The application can restart between writing the final page and publishing it. If refresh means “delete the old index, then rebuild,” every one of those ordinary failures becomes an outage – or worse, leaves a partially refreshed corpus that looks complete.
Part 4 of this series develops a safer pattern: bounded, resumable, generation-aware ingestion with transactional publication. PostgreSQL, MySQL, and SQL Server remain the external clinical systems of record. A separate Internal DocumentDB Hybrid Store holds copied documents, vectors, metadata, and application state. New data is invisible while it is staged; only a completed table generation replaces the last-known-good generation.
Ingestion is where record content is first copied, which makes it the stage a residency obligation touches most directly. Where a country requires processing connected to primary or secondary health care to be carried out through a server and data centre inside its borders – Kenya, the setting of the research behind this series, is the example it draws on – that duty covers every intermediate the pipeline creates, not just the finished index. Sending row text to a hosted embedding API would be a cross-border transfer of health data, so embedding runs locally; and a staged generation that is not yet searchable is still a copy of sensitive personal data, so it stays inside the same boundary as the published one. Part 1 sets out the constraint these rules come from.
This article describes a working prototype built with synthetic health data. It is an engineering pattern, not a compliance claim or clinical guidance. Production use still requires threat modeling, operational validation, data governance, and organization-specific review.
The implementation in this series is Rust, while the pattern can use the Foundry Local SDK Reference from C#, JavaScript, Python, or Rust; Part 2 collects the language links.
Problem statement
Conventional refresh code often reads an entire table into memory or deletes the existing index before rebuilding it. With large health-record tables, either choice turns ordinary interruptions – source disconnects, embedding failures, process restarts, or write errors – into unbounded resource use or a visibly incomplete corpus.
Paging alone does not solve publication or recovery. A job can process thousands of rows and still fail before cutover; an offset without durable intent and generation identity cannot safely explain what should resume. Meanwhile, users need the last complete searchable view to remain available, even though it may be less fresh than the operational source.
The ingestion pattern must bound memory and concurrency, persist enough checkpoint state to recover, stage all new chunks outside active retrieval, and publish only a complete table generation. Failure must preserve the last-known-good index rather than exposing a partial replacement. Every intermediate – page buffer, chunk batch, embedding call, staged document – must also remain inside the facility, since a resumable pipeline that reaches offsite for one stage has moved the data regardless of how carefully the rest is bounded.
Success criteria
- Source rows and embedding work are processed in bounded pages and batches.
- A durable job records request intent, progress, generation, and restart position.
- Staged chunks remain invisible to retrieval until table completion.
- Publication atomically switches from the prior generation to the completed generation.
- A failed or interrupted run leaves the last-known-good generation searchable and resumable.
Separate source authority from search state
The first architectural decision is to avoid treating the search store as another clinical source. The source databases own the live records. The application reads them through read-only connectors. DocumentDB owns the derived representation used for retrieval, browsing, ingestion state, and schema metadata.
This boundary clarifies freshness. Live, guarded SQL sees the external source at query time. Semantic retrieval sees the latest successfully published ingest generation. Those answers can legitimately differ after source data changes and before the next completed refresh.
It also clarifies failure handling: an incomplete derived copy is not allowed to masquerade as current.
Stage 1: validate before launching background work
An ingestion request identifies a saved source, an ordered list of tables, optional exclusions per table, and an optional row limit. Before detaching the job, the server:
- decrypts the saved source credential;
- introspects the live source schema;
- rejects requested tables that do not exist;
- acquires global and per-source admission capacity; and
- persists the complete request in a durable job document.
The returned job ID is a UUIDv7. That time-ordered identity is useful for operational browsing, but the more important property is that the job document contains enough information to resume without reconstructing intent from logs.
A simplified request-to-worker boundary looks like this:
let job_id = Uuid::now_v7().to_string(); let request = IngestRequest { source_id, tables, excluded_columns, row_limit, }; jobs.insert_one(saved_job(job_id.clone(), &request)).await?; tokio::spawn(run_ingestion(job_id.clone(), request, permit)); Ok(Json(json!({ “job_id”: job_id })))
The production code adds authentication, source credential loading, progress notifications, error conversion, and cleanup. The principle is simple: persist the recovery contract before acknowledging the work.
Stage 2: bound memory with pages, but name the limitation
Each connector reads an ordered page using an offset and page_size. A declared primary key is preferred as the ordering column. PostgreSQL uses whole-row JSON conversion; MySQL and SQL Server decode supported column types individually.
Page-bounded ingestion prevents a million-row table from becoming one in-memory allocation. Defaults in the prototype use 256 source rows per page and embedding batches of 32. The page is projected and chunked before its embedding batches are written, so this is bounded processing – not a fully overlapped source-to-embed-to-write pipeline.
OFFSET is also a conscious compromise. It makes checkpointing understandable, but deep offsets can become expensive, and inserts or deletes during a run can shift positions. Keyset pagination over an immutable primary key is the stronger future design. Until that exists, avoid claiming snapshot semantics that the source transaction does not provide.
Stage 3: exclude before projection
Column exclusion must happen before both storage and embedding. Removing a field only from visible text while retaining it in the stored fields object is not exclusion. Likewise, a PII detector that merely highlights a field does not enforce minimization.
For each row, the pipeline follows this order:
source row -> remove explicitly excluded columns -> retain allowed structured fields -> omit known retrieval-noise fields from text projection -> optionally enrich patient reference -> optionally annotate clinical concepts -> chunk projected text -> embed chunks
Audit timestamps and opaque internal IDs can remain in allowed structured fields while being omitted from the text projection as retrieval noise. Business identifiers that users search for should remain searchable unless deliberately excluded.
The optional local extractor is often misunderstood. It can annotate sufficiently long rows with mentioned conditions, medications, labs, and best-effort ICD-10, RxNorm, or LOINC codes. It is fail-open for ingestion: a timeout or malformed result leaves the row unannotated. It does not redact or rewrite the source text. Its output is metadata copied to every chunk from that row. Terminology codes are hints, not validated clinical codes.
Stage 4: chunk and embed locally
The current chunker uses approximately 384 whitespace-delimited words with 64-word overlap. Configuration uses token-oriented names, but the implementation is not tokenizer-accurate. Saying “approximately 384/64 words” is more honest than implying exact model-token boundaries.
Each chunk stores:
- source_id, table, and source row_pk;
- zero-based chunk_index;
- allowed structured fields;
- projected text;
- a 1,024-dimensional contentVector;
- ingest_generation and active state;
- optional annotation; and
- ingestion timestamp.
Embeddings come from local fastembed BGE-M3, not Foundry Local. Documents and queries use the same prefix-free encoder. This is an index invariant: changing the model or dimensions requires rebuilding vectors and validating or recreating the vector indexes. Mixed incompatible vectors are not an online migration strategy.
A generation-aware identity lets old and new copies coexist:
let id = format!( “{source_id}:{table}:{row_pk}:{chunk_index}:{generation}” ); let record = doc! { “_id”: id, “source_id”: source_id, “table”: table, “row_pk”: row_pk, “chunk_index”: chunk_index, “fields”: fields, “text”: text, “contentVector”: vector, “ingest_generation”: generation, “active”: false, };
Every new table refresh receives its own UUIDv7 generation. Every inserted chunk starts inactive.
Stage 5: checkpoint only completed pages
A durable checkpoint records the current table, the next OFFSET, and the generation being staged. The offset advances only after all chunks for a page have been embedded and inserted.
Crashes can still occur during a page write. Resume therefore replays the checkpoint page idempotently. Before reinserting that page, the worker deletes inactive chunks in the same generation whose row keys appear in the fetched page:
records.delete_many(doc! { “source_id”: source_id, “table”: table, “ingest_generation”: generation, “active”: false, “row_pk”: { “$in”: row_keys }, }).await?; write_page_chunks(…).await?; jobs.update_one( doc! { “_id”: job_id }, doc! { “$set”: { “checkpoint_table”: table, “checkpoint_offset”: offset + fetched_rows, }}, ).await?;
The deletion is deliberately narrow. It cannot touch the active generation, another table, or another source. The worker stops after the first failed table, preserving one unambiguous resume position. The terminal job status distinguishes completed, partial, and failed.
On server boot, abandoned running jobs become failed with a sanitized recovery reason. Transient table refresh flags are reset, but active and staged records remain. This makes interruption visible without destroying recovery material.
Stage 6: publish with one transaction
The generation becomes current only after the table finishes. The cutover transaction performs three related updates:
session.start_transaction().await?; records.update_many( doc! { “source_id”: source, “table”: table, “active”: true }, doc! { “$set”: { “active”: false } }, ).session(&mut session).await?; records.update_many( doc! { “source_id”: source, “table”: table, “ingest_generation”: generation }, doc! { “$set”: { “active”: true } }, ).session(&mut session).await?; indexed_tables.update_one( doc! { “_id”: format!(“{source}:{table}”) }, doc! { “$set”: { “active_generation”: generation, “status”: “indexed” } }, ).session(&mut session).await?; session.commit_transaction().await?;
Readers filter active: true. If fetch, embedding, insert, or cutover fails, the previous active generation remains the last-known-good index. Old inactive generations are removed only after commit, on a best-effort basis.
This is the key reliability property: publication is a state transition, not the side effect of starting a rebuild.
The schema catalog follows the same discipline on its own timeline: a complete new set of table cards is written before schema_catalog_state.active_version moves. Record generations and catalog versions are independent and should not be conflated.
Binding the schema to the hospital ontology (planned)
The next design iteration adds one more step after every successful catalog refresh. A binder reads the active table cards, the administrator’s metadata overrides, and any PII analysis, and produces a SchemaBinding for the source: for each table, the hospital concept it represents (Admission, Prescription, LabResult, …), the role each column plays (PatientRef, EventTime, Status, Amount, Contact, …), the service lines that own that concept, and the shortest foreign-key path to the patient table. The binding is versioned against the catalog version that produced it, persisted to schema_bindings with a bounded history, and rebuilt whenever the catalog changes or an administrator saves an override. Plan 01 – service-line ontology and schema binding specifies the scoring signals, the fixed concept and role vocabularies, and the acceptance fixtures.
This belongs to ingestion time rather than query time for three reasons. First, it is expensive in the wrong place: token normalisation, BGE-M3 similarity against concept descriptions, and breadth-first search over foreign keys are cheap once per refresh and unacceptable inside a chat request. Second, it must be stable: a router or SQL compiler that re-derives “which table is the admissions table” on every question can change its mind between turns; a versioned binding gives every consumer the same answer until the catalog changes. Third, it needs an administrator in the loop. Heuristics will misbind some tables in any real hospital schema, and the override that fixes a binding is a catalog-time decision that should be recorded, diffed, and rolled forward with the catalog – not a prompt tweak.
The binder is design direction, not shipped code. Two properties it must hold are worth stating now: it never copies PII-role column values into the binding’s enumerated values, and it must reproduce the hand-written table-to-agent map for the development seed without any override before it is trusted on a second, differently named schema.
Operational tradeoffs
Generation-aware ingestion costs temporary storage because two copies can coexist. It also requires every current-data query – vector, lexical, aggregation, and Data Explorer – to apply the active-generation contract consistently. Missing that filter is a correctness bug.
Other tradeoffs remain explicit:
- OFFSET checkpoints are simple but can drift under concurrent source changes.
- Approximate word chunks are predictable but not tokenizer-aware.
- Page-bounded processing is not full pipeline overlap.
- Cleanup is eager and best-effort; there is no grace-period rollback UI.
- Server-side resume exists, but the standard client recovery experience is incomplete.
- Transactional cutover is implemented, while comprehensive crash-point and production-scale recovery evidence is still outstanding.
Anti-patterns to avoid
- Delete before rebuild. It turns refresh failure into downtime and destroys the last-known-good copy.
- Make staged rows searchable. A query can then combine old and incomplete new content.
- Checkpoint before durable page completion. Resume skips data that never fully landed.
- Use generated annotation as redaction. Metadata extraction does not remove PHI from text, vectors, or fields.
- Hide OFFSET behind “streaming ingestion” language. Bounded memory and stable concurrent-source semantics are different guarantees.
- Mix embedding dimensions. A vector field is meaningful only under a declared model, dimension, and preprocessing contract.
Prototype status
The prototype implements PostgreSQL, MySQL, and SQL Server ingestion; bounded OFFSET pages; explicit exclusions before projection; approximate 384/64 word chunking; local BGE-M3 1,024-dimensional embeddings; inactive UUIDv7 generations; durable checkpoints; resumable page replay; transactional cutover; and last-known-good reads. The optional extractor annotates but does not redact.
It does not yet provide keyset pagination, tokenizer-accurate chunks, fully overlapped bounded channels, a complete resume UI, or production-scale recovery certification. All current evidence uses synthetic health data. Nothing here establishes regulatory compliance, medical accuracy, or fitness for clinical decision-making.
Key takeaways
- Keep External Clinical Databases authoritative and the Internal DocumentDB Hybrid Store explicitly derived.
- Stage each refresh under a new inactive generation.
- Advance checkpoints only after complete page writes, and make replay idempotent.
- Publish records and the active-generation pointer together transactionally.
- Preserve the last-known-good generation on every failure path.
- Describe annotation, chunking, paging, and validation limits precisely.
Continue the series
- Previous: Part 3 – DocumentDB as an internal hybrid store
- Next: Part 5 – A local hybrid retrieval pattern
The code behind this series is open source: github.com/kevin-gatimu/onprem-health-rag. It is a synthetic-data research prototype, not a clinical or compliance-ready system — issues and corrections are welcome.