EHR Phenotype Sketch

Problem

Health systems constantly need to answer a deceptively simple question: which of our patients have condition X? The answer drives quality reporting, research cohorts, and care programs, and you cannot trust a single billing code to give it. Diagnosis codes are coded for reimbursement, not research, and rule-out codes, copy-forward problem lists, and history-of coding all inflate a naive one-code definition. This is why informatics teams write computable phenotypes: rule-based algorithms that combine diagnoses, labs, and medications into a decision, with explicit logic for the ambiguous cases.

EHR Phenotype Sketch builds one end to end. It takes synthetic EHR data, loads it into a normalized database with standard medical codes, and runs Type 2 Diabetes and Hypertension phenotypes that decide, per patient, whether the evidence supports the condition.

Why it mattered

The point I wanted to make demonstrable is that I can work in the actual vocabulary of clinical informatics: PheKB-style algorithm structure, four coordinated code systems, ADA and JNC thresholds, and the exclusion logic that separates a defensible phenotype from a code count. The honest scope is a sketch on synthetic data, but the pattern is the real one. The algorithm requires supporting evidence rather than a lone code, keeps insulin in its own value set because on its own it does not distinguish Type 1 from Type 2, and rules out gestational diabetes and prediabetes on the no-diagnosis path where a single elevated glucose and a metformin prescription would otherwise mislead it.

Architecture

• A loader reads six Synthea EHR tables (patient, encounter, condition, observation, medication, procedure) into a third-normal-form SQLite schema. Every clinical fact is one row carrying an explicit code_system column, so ICD-10-CM, SNOMED, LOINC, and RxNorm codes coexist without collision.
• A hand-curated, versioned value-set table (60 rows) defines the concept sets each phenotype arm joins against, with an index on code_system plus code that turns each join into an index probe.
• Both phenotypes are written primarily in CTE-based SQL and orchestrated from Python, which writes one row per patient per phenotype with a human-readable trace of which arms fired.
• The Type 2 Diabetes algorithm has three evidence arms (diagnosis, abnormal glycemic lab, non-insulin antidiabetic medication) plus a Type 1 rule-out and gestational and prediabetes exclusions on the inference path.
• A characterization script reports cohort counts, prevalence, and a demographic breakdown, and a base-R script writes a prevalence-by-age table and a bar plot.
• A validation script re-implements the case definition independently in Python and compares it to the SQL output on a stratified 20-patient sample.

Technical decisions

Multi-arm evidence instead of a single diagnosis code. Alternative: define a case as anyone with a Type 2 diagnosis code. That definition is a textbook false-positive generator, because a rule-out or history-of code counts the same as a confirmed diagnosis. The phenotype instead requires a diagnosis plus supporting evidence, or repeated diagnosis dates, or the no-diagnosis inference path, so a lone code never establishes a case on its own.

Gestational and prediabetes exclusions on the inference path only. Alternative: apply the exclusions everywhere, or nowhere. Applied everywhere, they would wrongly remove patients who carry a real Type 2 diagnosis and happen to have a gestational history. Applied nowhere, the no-diagnosis path would classify a pregnant patient on metformin with one elevated glucose as Type 2. The exclusions therefore fire only on the inference path, where the evidence is otherwise ambiguous.

An explicit code_system column instead of separate per-vocabulary tables. Alternative: one table per code system. Keeping ICD-10-CM, SNOMED, LOINC, and RxNorm facts in shared tables with a code_system discriminator is what lets a value set match across vocabularies with one index, and it mirrors how a real EHR normalizes FHIR-style code-system URIs to short tokens at load time.

Procedures kept in SNOMED rather than invented CPT codes. Alternative: crosswalk Synthea's SNOMED procedures to CPT to look more like US billing. Real US procedure billing uses CPT and HCPCS, which I cannot verify against a licensed source here, so the project keeps procedures SNOMED-coded and documents the CPT-shaped crosswalk instead of inventing codes.

Interesting challenges

The most instructive moment came from an adversarial self-review of the exclusion logic. The gestational and prediabetes exclusion value sets existed but were not actually wired into the inference path, so they were dead code that changed nothing. Catching that, wiring them in, and then mutation-testing the arms to prove each one changed a classification when it should, corrected the Type 2 cohort from 14 down to 13. That single-case correction is the whole argument for validating a phenotype rather than trusting that it runs: the algorithm executed cleanly the entire time it was subtly wrong.

The second challenge was calibration without a gold standard. Type 2 Diabetes at 12.0% of a whole-population synthetic sample sits sensibly between the CDC anchors of roughly 11.6% total diabetes and roughly 9% diagnosed, of which the large majority is Type 2, and the age gradient (0% under 18, 2.7% at 18 to 44, 16.7% at 45 to 64, 36.4% at 65 and over) matches the real epidemiology. These are order-of-magnitude sanity checks against synthetic data, not accuracy claims, and the writeup says so plainly.

Results

Scope and honest limits

The data is synthetic. Synthea models disease with rule-based modules, so prevalence and code co-occurrence are cleaner and more internally consistent than a real EHR, and real-world messiness (missing data, miscoding, free text) is not represented. The value sets are hand-curated for this demonstration and have not had a second-reviewer terminology audit, so they are illustrative rather than a validated release value set. Thresholds are parameterized to one convention (ADA 6.5% / 126 / 200 for diabetes, JNC 140/90 for hypertension); the 2017 ACC/AHA 130/80 threshold is an alternative not implemented here.

What "validation" means here is narrow and stated as such. The N=20 check is agreement between two implementations of the same specification against the same structured evidence, closer to a code review of the algorithm than to a clinical chart review. It validates the implementation against the spec, not the spec against clinician-adjudicated ground truth. On real data, phenotype performance would be reported as sensitivity, specificity, and PPV against a reviewed gold standard. CPT and HCPCS procedure handling is not claimed, because Synthea codes procedures in SNOMED. This is a portfolio sketch demonstrating the phenotyping pattern and the informatics tooling, not a clinical-grade or production system.

Lessons

A phenotype that runs is not a phenotype that is correct. The exclusion value sets were present, imported, and inert, and only an adversarial pass that asked whether each arm actually changed an outcome exposed it. Mutation-testing the arms afterward turned "the exclusions are wired in" from a claim into something the tests enforce.

Sanity-checking against published prevalence is the cheapest honest signal available when there is no gold standard. It cannot prove the phenotype is right, but a cohort that landed at 40% or at 2% would have told me something was wrong before any reviewer did.

Future work

• Run the phenotypes against a second, larger Synthea generation to check that the prevalence and age gradient are stable rather than an artifact of one 108-patient sample.
• Add a sensitivity, specificity, and PPV harness driven by a small hand-adjudicated label set, so the validation moves from two-implementation concordance toward a real gold-standard comparison.
• Expand the value sets using descendant-subtree expansion for SNOMED and ingredient-to-product expansion for RxNorm, rather than the enumerated sample codes the demo uses.