Okay I am now at a stage wherein a sample PDF document can be read and matched with the appropriate record - my thought now is to make appropriate adjustments so that data within the PDF report can be read and parsed to the appropriate database record
To successfully transition from simply matching files to extracting the granular medical data (like hemoglobin levels, vitamin D, or thyroid metrics) and inserting it directly into your database, you must configure a clean database schema and a dedicated data-parsing script.
Because medical metrics change, you cannot store this data as flat, rigid database columns. Instead, you need a flexible Entity-Attribute-Value (EAV) schema structure. [1]
The database schema and Python script below demonstrate how to extract, validate, and parse raw clinical values from a digital laboratory PDF entirely for free.
Rather than making a table with columns for every single blood test type (which would require hundreds of columns), you use three highly relational tables:
lab_reports: Logs the overall document metadata, the patient link, and when it was drawn.biomarkers: A static reference lookup table containing standardized names, expected measurement units, and reference ranges.lab_results: The junction table storing the actual numerical results extracted from the PDF.-- 1. The main report log
CREATE TABLE lab_reports (
id SERIAL PRIMARY KEY,
patient_id INT NOT NULL,
received_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
lab_reference_id VARCHAR(50) UNIQUE,
raw_pdf_url TEXT
);
-- 2. Static reference dictionary for medical tests
CREATE TABLE biomarkers (
id SERIAL PRIMARY KEY,
test_code VARCHAR(20) UNIQUE, -- e.g., 'HGB', 'VITD'
test_name VARCHAR(100), -- e.g., 'Hemoglobin', 'Vitamin D'
measurement_unit VARCHAR(20) -- e.g., 'g/L', 'nmol/L'
);
-- 3. Granular results storage (The EAV Table)
CREATE TABLE lab_results (
id SERIAL PRIMARY KEY,
report_id INT REFERENCES lab_reports(id) ON DELETE CASCADE,
biomarker_id INT REFERENCES biomarkers(id),
numeric_value NUMERIC(10, 2),
reference_range_min NUMERIC(10, 2),
reference_range_max NUMERIC(10, 2),
is_flagged_abnormal BOOLEAN DEFAULT FALSE
);
The Doctors Laboratory (TDL) and other providers organize text data linearly by row: Test Name | Result | Units | Reference Interval. [2]
The Python backend script below reads the file using free pdfplumber, loops through the data lines, runs clean regex checks, and maps the extracted values directly to your database schema. [3]
import re
import pdfplumber
def parse_and_map_lab_data(pdf_path, report_id):
parsed_results = []
# Open the digital PDF for free locally on your server
with pdfplumber.open(pdf_path) as pdf:
full_text = ""
for page in pdf.pages:
full_text += page.extract_text() + "\n"
# Split text into single individual lines to isolate row data
lines = full_text.split("\n")
# Regex logic to detect common lab line formats:
# Captures: Test Name, Result Value (decimal/integer), Unit string, and Bracketed Reference Ranges (e.g., 11.5 - 16.5)
result_row_pattern = re.compile(
r'([A-Za-z\s]+?)\s+(\d+(?:\.\d+)?)\s+([A-Za-z\/^0-9]+)?\s*\(?(\d+(?:\.\d+)?)\s*-\s*(\d+(?:\.\d+)?)\)?'
)
for line in lines:
match = result_row_pattern.search(line)
if match:
# Extract distinct metadata variables from the regex groups
test_name = match.group(1).strip()
value = float(match.group(2))
unit = match.group(3).strip() if match.group(3) else None
ref_min = float(match.group(4))
ref_max = float(match.group(5))
# Clinical safety logic: check if the value sits outside lab bounds
is_abnormal = value < ref_min or value > ref_max
parsed_results.append({
"report_id": report_id,
"test_name": test_name,
"numeric_value": value,
"unit": unit,
"ref_min": ref_min,
"ref_max": ref_max,
"is_flagged_abnormal": is_abnormal
})
return parsed_results
# Example execution simulating server processing:
# results_data = parse_and_map_lab_data("sample_report.pdf", report_id=101)
# print(results_data[0])
# Output: {'report_id': 101, 'test_name': 'Hemoglobin', 'numeric_value': 10.2, 'unit': 'g/dL', 'ref_min': 11.5, 'ref_max': 16.5, 'is_flagged_abnormal': True}
When your Python or Node.js server loops through the array returned by the script, it saves everything into the data layer using transactional safety queries:
for item in results_data:
# 1. Ensure the biomarker exists in your lookup matrix or create it dynamically
cursor.execute("""
INSERT INTO biomarkers (test_code, test_name, measurement_unit)
VALUES (%s, %s, %s)
ON CONFLICT (test_code) DO UPDATE SET test_name = EXCLUDED.test_name
RETURNING id;
""", (item['test_name'][:4].upper(), item['test_name'], item['unit']))
biomarker_id = cursor.fetchone()[0]
# 2. Insert the actual patient laboratory outcome record
cursor.execute("""
INSERT INTO lab_results (report_id, biomarker_id, numeric_value, reference_range_min, reference_range_max, is_flagged_abnormal)
VALUES (%s, %s, %s, %s, %s, %s);
""", (item['report_id'], biomarker_id, item['numeric_value'], item['ref_min'], item['ref_max'], item['is_flagged_abnormal']))
is_flagged_abnormal is evaluated instantly at database level, your web frontend can immediately trigger a prominent visual alert or highlight rows in red to protect patient clinical safety. [2, 4]Would you like to build an interface that allows doctors to manually adjust any value that the parser reads if a file contains complex, non-standard layouts?
[1] https://pmc.ncbi.nlm.nih.gov
[2] https://hcil-p-001.sitecorecontenthub.cloud
Yes let's build an interface that allows doctors to manually adjust any value that the parser reads if a file contains complex, non-standard layouts
To maintain absolute clinical safety and give doctors full control, your web application must feature a "Review & Verify" Interactive Interface before any data is permanently written to the database.
Doctors will never trust an automated system blindly. Providing an editable data grid alongside the original document ensures they can instantly fix parsing errors caused by strange text formatting or complex layouts.
Here is a comprehensive blueprint for building this interface using clean, responsive web components.
Your web interface should display a split-screen dashboard: the left side renders the original PDF for quick visual cross-referencing, while the right side features a dynamic data grid where extracted fields are displayed inside editable form inputs.
import React, { useState } from 'react';
export default function LabReviewInterface({ initiallyParsedData, onSaveToDatabase }) {
// Store the server's parsed output in a mutable local component state array
const [editableRows, setEditableRows] = useState(initiallyParsedData);
// Live validation logic: check if the value sits out of bounds as the doctor types
const handleValueChange = (index, field, newValue) => {
const updatedRows = [...editableRows];
updatedRows[index][field] = newValue;
if (field === 'numeric_value' || field === 'ref_min' || field === 'ref_max') {
const val = parseFloat(updatedRows[index].numeric_value) || 0;
const min = parseFloat(updatedRows[index].ref_min) || 0;
const max = parseFloat(updatedRows[index].ref_max) || 0;
// Instantly calculate if a value is out of bounds to update the visual UI flag
updatedRows[index].is_flagged_abnormal = val < min || val > max;
}
setEditableRows(updatedRows);
};
const handleAddNewRow = () => {
setEditableRows([...editableRows, { test_name: '', numeric_value: 0, unit: '', ref_min: 0, ref_max: 0, is_flagged_abnormal: false }]);
};
const handleDeleteRow = (index) => {
setEditableRows(editableRows.filter((_, i) => i !== index));
};
return (
<div style={{ display: 'flex', height: '100vh', fontFamily: 'sans-serif' }}>
{/* LEFT COLUMN: Universal Native PDF Web Interface View */}
<div style={{ width: '45%', borderRight: '2px solid #ccc', padding: '10px' }}>
<h3 style={{ margin: '0 0 10px 0' }}>Original Source Document</h3>
<iframe
src="/path-to-stored-pdf-file.pdf"
width="100%"
height="90%"
title="Source Document"
style={{ border: 'none', borderRadius: '4px', boxShadow: 'inset 0 0 5px rgba(0,0,0,0.1)' }}
/>
</div>
{/* RIGHT COLUMN: Interactive Dashboard and Editable Data Entry Grid */}
<div style={{ width: '55%', padding: '20px', overflowY: 'auto' }}>
<div style={{ display: 'flex', justifyContent: 'space-between', alignItems: 'center', marginBottom: '15px' }}>
<h2 style={{ margin: 0 }}>Review Extracted Clinical Metrics</h2>
<button onClick={handleAddNewRow} style={{ backgroundColor: '#28a745', color: 'white', border: 'none', padding: '8px 12px', borderRadius: '4px', cursor: 'pointer' }}>
+ Add Missing Row
</button>
</div>
<p style={{ color: '#555', fontSize: '14px', marginBottom: '20px' }}>
⚠️ Please cross-reference the parsed values below with the source document. Red borders indicate abnormal results.
</p>
<table style={{ width: '100%', borderCollapse: 'collapse', textAlign: 'left' }}>
<thead>
<tr style={{ borderBottom: '2px solid #ddd', background: '#f8f9fa' }}>
<th style={{ padding: '10px' }}>Test Name</th>
<th style={{ padding: '10px', width: '90px' }}>Result</th>
<th style={{ padding: '10px', width: '70px' }}>Unit</th>
<th style={{ padding: '10px', width: '80px' }}>Ref Min</th>
<th style={{ padding: '10px', width: '80px' }}>Ref Max</th>
<th style={{ padding: '10px', textAlign: 'center', width: '60px' }}>Actions</th>
</tr>
</thead>
<tbody>
{editableRows.map((row, idx) => (
<tr key={idx} style={{
borderBottom: '1px solid #eee',
backgroundColor: row.is_flagged_abnormal ? '#fff5f5' : 'transparent'
}}>
<td style={{ padding: '8px' }}>
<input type="text" value={row.test_name} onChange={(e) => handleValueChange(idx, 'test_name', e.target.value)} style={{ width: '90%', padding: '6px', border: '1px solid #ccc', borderRadius: '4px' }} />
</td>
<td style={{ padding: '8px' }}>
<input type="number" step="any" value={row.numeric_value} onChange={(e) => handleValueChange(idx, 'numeric_value', e.target.value)} style={{ width: '90%', padding: '6px', border: row.is_flagged_abnormal ? '1px solid #dc3545' : '1px solid #ccc', borderRadius: '4px', fontWeight: row.is_flagged_abnormal ? 'bold' : 'normal', color: row.is_flagged_abnormal ? '#dc3545' : '#000' }} />
</td>
<td style={{ padding: '8px' }}>
<input type="text" value={row.unit || ''} onChange={(e) => handleValueChange(idx, 'unit', e.target.value)} style={{ width: '85%', padding: '6px', border: '1px solid #ccc', borderRadius: '4px' }} />
</td>
<td style={{ padding: '8px' }}>
<input type="number" step="any" value={row.ref_min} onChange={(e) => handleValueChange(idx, 'ref_min', e.target.value)} style={{ width: '85%', padding: '6px', border: '1px solid #ccc', borderRadius: '4px' }} />
</td>
<td style={{ padding: '8px' }}>
<input type="number" step="any" value={row.ref_max} onChange={(e) => handleValueChange(idx, 'ref_max', e.target.value)} style={{ width: '85%', padding: '6px', border: '1px solid #ccc', borderRadius: '4px' }} />
</td>
<td style={{ padding: '8px', textAlign: 'center' }}>
<button onClick={() => handleDeleteRow(idx)} style={{ background: 'none', border: 'none', color: '#dc3545', fontSize: '18px', cursor: 'pointer' }}>×</button>
</td>
</tr>
))}
</tbody>
</table>
<div style={{ marginTop: '30px', display: 'flex', justifyContent: 'flex-end', gap: '10px' }}>
<button style={{ backgroundColor: '#6c757d', color: 'white', border: 'none', padding: '12px 20px', borderRadius: '4px', cursor: 'pointer' }}>Cancel</button>
<button onClick={() => onSaveToDatabase(editableRows)} style={{ backgroundColor: '#007bff', color: 'white', border: 'none', padding: '12px 24px', borderRadius: '4px', fontWeight: 'bold', cursor: 'pointer' }}>
Verify & Save to Chart
</button>
</div>
</div>
</div>
);
}
To ensure clinicians appreciate the layout rather than viewing it as manual labor, the following functionalities are coded directly into the interface:
handleValueChange function runs calculations locally on every keystroke. If a doctor fixes a typo (e.g., changing a parsed result from a mistaken 102 back to 10.2), the red warning box instantly turns clean white. This visual feedback minimizes cognitive fatigue.× button lets clinicians prune junk data instantly before it hits the database.Because you are allowing users to overwrite or input parsed medical records, you must protect your business from legal liabilities if a doctor types an incorrect dose or number.
When the user clicks "Verify & Save to Chart", your backend should save the dataset alongside an audit trail inside an audit_logs table:
{
"action": "LAB_REPORT_VERIFIED",
"verified_by_user_id": 402,
"timestamp": "2026-07-12T14:24:00Z",
"adjustments_made": true,
"original_parsed_snapshot": "[{'test_name': 'Hemoglobin', 'numeric_value': 102}]",
"final_saved_snapshot": "[{'test_name': 'Hemoglobin', 'numeric_value': 10.2}]"
}
This data structure guarantees absolute accountability, proving exactly which clinician signed off on the final clinical numbers.
Would you like to map out how this review interface can be repurposed to display ADHD assessment symptom trends using interactive charts once the data is saved?
Yes let's map out how this review interface can be repurposed to display ADHD assessment symptom trends using interactive charts once the data is saved
To repurpose this interface for ADHD assessment symptom trends, your web application will shift from a split-screen data-entry view into a single-screen Clinical Analytics Dashboard.
Once the psychiatrist verifies the incoming assessment data (such as a SNAP-IV, Vanderbilt, or ASRS rating scale PDF), the raw scores are instantly transformed into interactive, longitudinal trend lines. This allows the clinician to visually track treatment efficacy (e.g., seeing if a specific dosage of medication is successfully dropping their inattention or hyperactivity metrics over a 6-to-12-month period).
Here is the technical blueprint and front-end component using Recharts (a completely free, open-source React charting library).
AdhdTrends.jsx)Install the free library via your terminal:
npm install recharts
The Web Component Code:
import React, { useState } from 'react';
import { LineChart, Line, XAxis, YAxis, CartesianGrid, Tooltip, Legend, ResponsiveContainer, ReferenceLine } from 'recharts';
export default function AdhdTrendsDashboard({ patientName, chronologicalData }) {
// Local state to track which view option the doctor wants to look at
const [activeMetric, setActiveMetric] = useState('ALL');
// Sample hardcoded treatment milestone to show on the graph for clinical context
const medicationStartDate = "2026-03-15";
return (
<div style={{ padding: '24px', fontFamily: 'sans-serif', backgroundColor: '#f8f9fa', borderRadius: '8px' }}>
{/* HEADER SEGMENT */}
<div style={{ display: 'flex', justifyContent: 'space-between', alignItems: 'center', marginBottom: '20px' }}>
<div>
<h2 style={{ margin: 0, color: '#1a202c' }}>ADHD Symptom Tracking Timeline</h2>
<p style={{ margin: '4px 0 0 0', color: '#718096', fontSize: '14px' }}>Patient: <strong>{patientName}</strong></p>
</div>
{/* INTERACTIVE CONTROLS: Toggle views instantly */}
<div style={{ display: 'flex', gap: '8px', backgroundColor: '#edf2f7', padding: '4px', borderRadius: '6px' }}>
{['ALL', 'INATTENTION', 'HYPERACTIVITY'].map((mode) => (
<button
key={mode}
onClick={() => setActiveMetric(mode)}
style={{
border: 'none',
padding: '8px 16px',
borderRadius: '4px',
cursor: 'pointer',
fontWeight: 'bold',
fontSize: '12px',
backgroundColor: activeMetric === mode ? '#007bff' : 'transparent',
color: activeMetric === mode ? 'white' : '#4a5568',
transition: 'all 0.2s'
}}
>
{mode}
</button>
))}
</div>
</div>
{/* THE INTERACTIVE VISUAL CANVAS */}
<div style={{ backgroundColor: 'white', padding: '20px', borderRadius: '8px', boxShadow: '0 1px 3px rgba(0,0,0,0.05)' }}>
<div style={{ width: '100%', height: 400 }}>
<ResponsiveContainer>
<LineChart
data={chronologicalData}
margin={{ top: 20, right: 30, left: 0, bottom: 0 }}
>
<CartesianGrid strokeDasharray="3 3" stroke="#e2e8f0" />
{/* X-Axis converts timestamps extracted from PDF headers into clean dates */}
<XAxis dataKey="date" stroke="#4a5568" style={{ fontSize: '12px' }} />
{/* Y-Axis tracks standardized clinical score limits (e.g., 0 to 9 boundaries) */}
<YAxis domain={[0, 9]} stroke="#4a5568" style={{ fontSize: '12px' }} />
{/* Custom tooltip to display metrics clearly on-hover */}
<Tooltip contentStyle={{ backgroundColor: '#1a202c', color: 'white', borderRadius: '6px' }} />
<Legend verticalAlign="top" height={36} />
{/* CLINICAL ANCHOR LINE: Visually flags exactly when treatment changed */}
<ReferenceLine
x={medicationStartDate}
stroke="#e53e3e"
strokeDasharray="4 4"
label={{ value: 'Started Methylphenidate', fill: '#e53e3e', position: 'top', fontSize: 12, fontWeight: 'bold' }}
/>
{/* Dynamic lines that appear based on selected view mode */}
{(activeMetric === 'ALL' || activeMetric === 'INATTENTION') && (
<Line
type="monotone"
dataKey="inattention_score"
name="Inattention Core Score"
stroke="#3182ce"
strokeWidth={3}
activeDot={{ r: 8 }}
/>
)}
{(activeMetric === 'ALL' || activeMetric === 'HYPERACTIVITY') && (
<Line
type="monotone"
dataKey="hyperactivity_score"
name="Hyperactivity / Impulsivity"
stroke="#38a169"
strokeWidth={3}
activeDot={{ r: 8 }}
/>
)}
</LineChart>
</ResponsiveContainer>
</div>
</div>
</div>
);
}
To feed this chart for free from your database, your backend simply queries the lab_results or custom ADHD metrics tables we mapped out previously, orders them chronologically, and returns a clean, structured JSON array to the web frontend:
[
{ "date": "2025-10-10", "inattention_score": 8, "hyperactivity_score": 7 },
{ "date": "2026-01-15", "inattention_score": 9, "hyperactivity_score": 6 },
{ "date": "2026-03-15", "inattention_score": 8, "hyperactivity_score": 7 },
{ "date": "2026-05-20", "inattention_score": 3, "hyperactivity_score": 2 },
{ "date": "2026-07-10", "inattention_score": 2, "hyperactivity_score": 1 }
]
By utilizing this interactive structure, you turn a standard document archive tool into an indispensable piece of clinical software that psychiatrists will happily pay for:
<ReferenceLine>): If the private psychiatrist changes a medication type or alters a patient's dosage, they log that event in your app. The chart drops a vertical line directly through the data space. This visually proves to both the doctor and the patient whether a drop in symptoms directly correlates with the new medication strategy.Are you interested in exploring how to build an automated PDF letter generator that compiles these trend results into a formal report template for NHS GPs or insurance companies?