This post is part of the My Health Log series. The idea is to build a privacy-first health data app in public. Code on github.

The challenge is different health testing providers might use different conventions for the same test parameter. For eg, provider A might set the name of haemoglobin as HbA1C while another provider might set it as Haemoglobin A1c. Now while this is understandable by a health professional or a single long chat with context of all reports together, we do face this problem when we break down the whole process into isolated steps. If we simply parse the PDF data and store it in a db, the trends would be impossible to make and passing the data to an LLM would be really inefficient due to missing structure.

Proposals

The solution seems to be adding a normalisation step post processing. LLMs seem to be good at classifications or semantic mapping. So we can prepare a source of a key name with its description and some example values to help the LLM normalise the report.

1. Hardcoded mapping: We can create a one time mapping that the LLM can always refer. The biggest issue here is what if something comes up that is not part of the LLMs dataset. Do we let that metric go untracked?
2. Let LLM come up with the key: We can always let the LLM return a key but this again is not a good approach. A query to model is always stateless and probabilistic. It might come up with different keys for the same input for different runs.
3. Canonical DB: We can setup a canonical DB and seed it with the list of possible metrics from the available sources. This should cover most of the cases we will come across. In case we get something not part of the DB, we can always update the canonical DB.

Design

The canonical DB approach seems to be best fit here.

• Setup a PG table with the aliases, description and common examples.
• Fetch the data at app start and bake it into the system prompt. (A good approach might be to parse the data into a zod schema that the AI SDK can use as a output structure).
• Setup the system prompt to conform to the output schema. If it finds something outside of this schema and it is a health related parameter, it can return the information in another segment of the output, maybe like new keys, their description and some examples.
  • The new keys can then be added to the table. Maybe we can even give a tool to the normalisation agent to add it to DB.
• From the response output, we will store the data with the normalised key, name in the report to be displayed and the source report.

Proposed flow

Proposed DB Tables

-- Canonical metrics reference table
  CREATE TABLE canonical_metrics (
    id SERIAL PRIMARY KEY,
    key VARCHAR(50) UNIQUE NOT NULL,       -- "hba1c"
    display_name VARCHAR(100) NOT NULL,    -- "HbA1c"
    description TEXT,                       -- "Glycated haemoglobin, avg blood sugar over 2-3 months"
    category VARCHAR(50),                   -- "diabetes", "lipid", "liver"
    default_unit VARCHAR(20),               -- "%"
    aliases JSONB DEFAULT '[]',             -- ["HB1AC", "Hemoglobin A1c", "Glycated Hemoglobin"]
    examples JSONB DEFAULT '[]',            -- [{"value": 5.7, "unit": "%", "status": "normal"}]
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
  );

  -- Normalised metrics extracted from reports
  CREATE TABLE metrics (
    id SERIAL PRIMARY KEY,
    report_id INTEGER REFERENCES reports(id),
    canonical_key VARCHAR(50) REFERENCES canonical_metrics(key),
    original_name VARCHAR(200),             -- "HB1AC" (as printed on report)
    value DECIMAL,
    unit VARCHAR(20),
    reference_range VARCHAR(50),            -- "4.0 - 5.6"
    status VARCHAR(20),                     -- "normal", "high", "low"
    report_date DATE,
    created_at TIMESTAMP DEFAULT NOW()
  );

Next steps

With this design, we get consistent keys for charting while staying flexible for new metrics. The next challenge is extracting data from PDFs without exposing PII to cloud services.