4. Data, versions, and permanent closure

The relationships

erDiagram
    Patients ||--o{ Encounters : has
    Encounters ||--o{ Vitals : contains
    Encounters ||--o{ Notes : contains
    Patients {
        Guid Id PK
        string MedicalRecordNumber UK
        string GivenName
        string FamilyName
        DateOnly BirthDate
        long Version
    }
    Encounters {
        Guid Id PK
        Guid PatientId FK
        DateTimeOffset StartedAt
        bool IsClosed
        DateTimeOffset ClosedAt
        long Version
    }
    Vitals {
        Guid Id PK
        Guid EncounterId FK
        DateTimeOffset MeasuredAt
        long Version
    }
    Notes {
        Guid Id PK
        Guid EncounterId FK
        string Text
        long Version
    }

Some timestamps and individual measurements are omitted for readability. ClosedAt is nullable in code: absent while open. The API derives the string status from the domain's IsClosed boolean.

A primary key identifies a row; a foreign key references another row; a unique index permits only one patient per normalized MRN. Relationships restrict deletion. There are no application delete or reopen endpoints.

EF and migrations

EhrDbContext.cs exposes the entity sets and configures mappings. It is EF's working context, not the database file. This project stores GUIDs/dates as SQLite TEXT, whole numbers as INTEGER, and decimal measurement properties as TEXT.

Three migrations construct/evolve the schema:

1. InitialSchema: patient storage.
2. PatientTimestamps: patient timestamps.
3. ClinicalSchema: encounters, vitals, notes.

Startup's MigrateAsync checks applied migrations and applies pending ones. It does not clear the database each time. Historical migration models are frozen in separate files; later changes must not rewrite their meaning. Tests check upgrades, restart persistence, and agreement between schema/model. For the general concept, see Microsoft's migration guide.

SQLite enforces uniqueness, parent references, paired/nonempty vitals, note length, and consistent closure state/timestamp. There is no blanket trigger preventing all possible direct SQL edits after closure. Permanent closure is enforced by the domain and transactional application store paths; out-of-band database edits are outside the API contract.

Versions prevent lost updates

Patient, encounter, note, and vital entities each have a counter. Two readers fetch patient version 1. The first saves with version: 1, producing version 2. The second sends old version 1: the API returns 409 instead of overwriting the first correction.

Clinical corrections carry both the record version and encounterVersion. A content write returns this abbreviated shape:

{"item": {"id": "...", "version": 1}, "encounterVersion": 2}

New content starts at its own version 1 while the parent encounter advances. Use the returned encounterVersion for the next write. Another successful clinical write makes an older encounter version stale.

Why closure needs a transaction

Hiding Edit does not protect storage: a write may already be in flight when a different request closes the encounter.

ClinicalStore.WriteContentAsync does this inside one SQLite transaction:

1. Load the encounter and verify content parent ownership for corrections.
2. Require open status and the expected version with EnsureWritable.
3. Create/correct content and save it.
4. Conditionally advance the encounter version, matching ID, open status, and original version.
5. Require exactly one changed row and commit.

The update condition is conceptually:

WHERE Id = @id AND IsClosed = 0 AND Version = @expectedVersion

If one row is not changed, the operation fails; transaction disposal rolls back the content change too. Closure uses a similar conditional update. Concurrent close/content operations cannot both persist against the same open version. SQLite busy/locked errors in these clinical writes become conflicts.

Closure behavior

Once closed, encounter-start edits and note/vital creates/corrections are rejected. Records remain readable; patient demographics can still change; a separate new encounter can be started. Repeat close preserves the original timestamp/version. The request still needs a positive version, but an already-closed encounter accepts an older positive version. This is idempotence: repetition does not repeatedly change the result.

Input bounds

These are storage/input limits, not clinical reference ranges:

At least one measurement is required. Blood pressure requires both values. Start/measurement timestamps cannot be future dates. Notes are trimmed, nonblank plain text of at most 20,000 characters.

Checkpoint

Why does adding a note advance the encounter version? So closure and content writes compete on the same parent version and persistence can decide atomically.

Next: API walkthrough.