醫療預約恢復的 Postgres 檢索架構：涵蓋三個保留窗口

Short answer: the retrieval architecture for a healthcare appointment assistant should keep the booking database authoritative, retain a replayable change log beside a rebuildable index, and degrade to constrained database lookup rather than serve a slot whose freshness cannot be proved.

For a healthcare appointment assistant, the expensive term is rarely the few sentences returned to a model. It is the retained, duplicated state: embeddings, index replicas, document payloads, change history, snapshots, and the temporary overlap required during a rebuild. Before choosing a retrieval engine, write the bill as source records + change log + active index + rebuild index + backups. If the active index contains every historical slot and every copy of unchanged provider text, retention dominates both storage and recovery time.

The useful change is to keep only searchable facts with a defined business lifetime in the hot index while preserving enough ordered change data to reconstruct it. The catch is real: once old index generations and raw retrieval traces expire, an investigation can reproduce the source state but may not reproduce the exact ranking that a user saw.

Freshness wins.

What the retention bill is actually buying

An appointment result is not one kind of data. Provider specialty and accessibility notes change slowly; slot availability can change between two turns; patient-entered text may be sensitive; an embedding is derived state; and a ranking trace is operational evidence. Giving all five the same retention period is convenient, expensive, and difficult to defend.

Start with bytes, not product tiers. Let S be authoritative scheduling data, L the ordered change log retained for replay, I one searchable index generation, R the temporary second generation during rebuild, and T sampled retrieval traces. Steady-state retained data is approximately S + L + I + T; planned rebuild capacity is S + L + I + R + T. Replicas and backups multiply their respective terms. This equation doesn't predict a bill without measured byte counts, but it exposes the lever: shrinking duplicate payloads in I, bounding L, and sampling T moves the dominant storage term without weakening the booking system of record.

I would not retain free-form patient queries in the index merely because they might improve a future relevance test. Keep evaluation cases de-identified and access-controlled, with a documented purpose and expiry. HIPAA's Security Rule requires administrative, physical, and technical safeguards for electronic protected health information; a retrieval trace is therefore not harmless debug text just because it sits outside the clinical database.

Stop keeping superseded index generations after the rollback window, and stop keeping full traces after the investigation window. That saves retained state and shortens deletion work. It also means an incident older than those windows may be explainable from audit events but not bit-for-bit replayable. That's the honest trade.

How should healthcare appointment assistant retrieval recover from failure?

Use three state classes, each with a different recovery promise. The comparison is architectural; none is a universal winner.

The assistant can use dense retrieval to interpret “a cardiologist near my office after lunch,” because retrieval-augmented generation combines a generator with external non-parametric memory. It cannot treat similarity as a reservation lock. Candidate discovery and appointment confirmation belong on opposite sides of a hard boundary: retrieval proposes provider or slot identifiers; the transactional scheduling path rechecks constraints and commits the booking.

Keep it boring.

When semantic retrieval is unavailable or its applied change position is behind the freshness budget, fall back to an authoritative lookup with explicit filters such as specialty, location, date range, and accessibility needs. Quality drops because paraphrases may be missed, but correctness remains inspectable. If neither a fresh index nor the authoritative query can establish availability, the assistant should ask the user to narrow the request or defer confirmation. Fabricating a plausible slot is not recovery.

Failure semantics need versions, not optimism

A health check saying “up” cannot prove that an index includes the cancellation committed one second ago. Every source mutation needs a monotonically ordered position or an equivalent database-native ordering token. Each index generation publishes the highest continuously applied position, its schema version, embedding configuration identifier, and build timestamp. A gap blocks the watermark; later events cannot conceal it.

This creates four named failure modes. Lag means the index is valid but outside the freshness budget. Gap means at least one ordered change is missing. Generation mismatch means query and index disagree on schema or embedding configuration. Split confirmation means retrieval produced a candidate that the authoritative booking transaction no longer accepts. Only the first three affect discovery; the fourth is an expected concurrency outcome and should return the user to candidate selection without claiming a booking.

Retries must be idempotent. Applying the same change twice should converge on one projection state, and a delete must carry enough version information that an older upsert cannot resurrect it. A dead-letter queue can isolate malformed events, but it must also stop watermark advancement; otherwise the system advertises freshness it doesn't possess. There is an uncomfortable latency trade-off here — strict gap detection may degrade search sooner, while skipping the event produces faster answers with unknown correctness.

HTTP status codes can preserve that distinction at the assistant boundary. A 409 Conflict fits a slot that was valid during discovery but conflicts with current resource state during confirmation, as defined by RFC 9110. A timeout is uncertain, not a rejection: query the booking operation by its idempotency key before asking the user to submit again. I'm not sure one universal freshness threshold is defensible across appointment types; emergency routing, routine checkups, and wait-list suggestions have different harm models, so clinical and scheduling owners must set the budget and validate it with observed change-to-index lag.

A small Python recovery controller

The controller below encodes the decision boundary without depending on a retrieval vendor. The numbers are example policy inputs, not measured recommendations. Production values must come from the service-level objective and risk review.

from dataclasses import dataclass
from enum import Enum


class Route(Enum):
    SEMANTIC = "semantic"
    AUTHORITATIVE = "authoritative"
    DEFER = "defer"


@dataclass(frozen=True)
class IndexState:
    generation: str
    schema_version: int
    applied_position: int
    source_position: int
    lag_seconds: float
    has_gap: bool


def choose_route(
    state: IndexState | None,
    expected_generation: str,
    expected_schema: int,
    max_lag_seconds: float,
    authoritative_lookup_available: bool,
) -> Route:
    index_is_safe = (
        state is not None
        and state.generation == expected_generation
        and state.schema_version == expected_schema
        and not state.has_gap
        and state.applied_position <= state.source_position
        and state.lag_seconds <= max_lag_seconds
    )
    if index_is_safe:
        return Route.SEMANTIC
    if authoritative_lookup_available:
        return Route.AUTHORITATIVE
    return Route.DEFER

The check applied_position <= source_position is necessary but insufficient; continuity is why has_gap exists. In a real implementation, the consumer derives that condition from its durable checkpoint and the ordered log, not from a caller-provided boolean. The response should also carry the chosen route and index generation into structured telemetry, never into prose that invites the model to reinterpret system state.

Do not automatically switch to an older index just because it answers quickly. An older generation is suitable only when its schema, embedding configuration, source checkpoint, and approved rollback window all match a declared recovery policy. Otherwise “last known good” is only a comforting label.

Retention and release gates

Test recovery as a sequence, not as a screenshot of top-k relevance. Pause change consumption, create a cancellation and a new slot, resume with one duplicated event, and verify that the watermark advances only across a continuous sequence. Then rebuild into a separate generation, replay changes that arrived during the build, compare retrieval quality on de-identified cases, and atomically promote the generation. Delete the old generation only after the rollback window closes.

The release gate should compare retrieval quality and latency separately for the normal semantic route and the authoritative fallback. Track change-to-index lag, gap age, fallback rate, candidate-set recall on reviewed cases, confirmation conflicts, and the age of the generation serving each answer. Averages hide the tail; use percentiles and counts, but don't invent a target before measuring traffic and agreeing on harm.

Not suitable: this design is too elaborate for a read-only directory whose source is replaced atomically and can be rebuilt well inside its tolerated outage. In that case, a signed snapshot plus blue-green replacement may be easier to operate. Conversely, stick with database-only filtered lookup when the request vocabulary is controlled and exact fields meet the quality requirement; semantic retrieval adds derived state, evaluation work, and another privacy surface. Choose the three-state design when free-text matching materially improves candidate discovery and stale availability must still be contained.

The final acceptance test is blunt: remove the retrieval index during a staged request, and confirm that the system either returns constrained, freshly checked candidates or clearly declines to confirm. No invented availability. No ambiguous success.

References

• Retrieval-Augmented Generation for Knowledge-Intensive NLP Tasks: https://arxiv.org/abs/2005.11401
• HTTP Semantics, RFC 9110: https://www.rfc-editor.org/rfc/rfc9110
• HIPAA Security Rule: https://www.hhs.gov/hipaa/for-professionals/security/index.html

Further reading

• NIST Privacy Framework: https://www.nist.gov/privacy-framework