Radiology Report Integration

Objective

Create a system that integrates radiology reports with imaging studies, enabling structured finding extraction, report templates, and clinical decision support.

Step 1: Create Report Studies Table

Store studies with reports.

CREATE TABLE report_studies (
    id INTEGER PRIMARY KEY,
    study_uid VARCHAR(100) UNIQUE,
    patient_id VARCHAR(50),
    patient_name VARCHAR(200),
    study_date DATE,
    modality VARCHAR(20),
    body_region VARCHAR(100),
    study_description VARCHAR(255),
    referring_physician VARCHAR(200),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Radiology Reports Table

Store report content.

CREATE TABLE radiology_reports (
    id INTEGER PRIMARY KEY,
    study_id INTEGER NOT NULL UNIQUE,
    report_uid VARCHAR(100) UNIQUE,
    report_status VARCHAR(50) DEFAULT 'draft',
    technique TEXT,
    comparison TEXT,
    findings TEXT,
    impression TEXT,
    recommendations TEXT,
    critical_finding BOOLEAN DEFAULT FALSE,
    critical_communicated BOOLEAN DEFAULT FALSE,
    critical_communicated_to VARCHAR(200),
    critical_communicated_at TIMESTAMP,
    dictated_by VARCHAR(200),
    dictated_at TIMESTAMP,
    transcribed_by VARCHAR(100),
    verified_by VARCHAR(200),
    verified_at TIMESTAMP,
    addendum TEXT,
    addendum_by VARCHAR(200),
    addendum_at TIMESTAMP,
    FOREIGN KEY (study_id) REFERENCES report_studies(id)
);

Step 3: Create Structured Findings Table

Extract individual findings.

CREATE TABLE structured_findings (
    id INTEGER PRIMARY KEY,
    report_id INTEGER NOT NULL,
    finding_category VARCHAR(100),
    anatomic_location VARCHAR(100),
    laterality VARCHAR(20),
    finding_type VARCHAR(100),
    finding_description TEXT,
    severity VARCHAR(50),
    size_mm DECIMAL(8, 2),
    change_from_prior VARCHAR(50),
    follow_up_required BOOLEAN DEFAULT FALSE,
    follow_up_interval VARCHAR(50),
    acr_code VARCHAR(20),
    snomed_code VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (report_id) REFERENCES radiology_reports(id)
);

Step 4: Create Report Templates Table

Store standard report templates.

CREATE TABLE report_templates (
    id INTEGER PRIMARY KEY,
    template_name VARCHAR(200) NOT NULL,
    modality VARCHAR(20),
    body_region VARCHAR(100),
    exam_type VARCHAR(100),
    technique_template TEXT,
    findings_template TEXT,
    impression_template TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 5: Create Critical Results Table

Track critical finding communications.

CREATE TABLE critical_results (
    id INTEGER PRIMARY KEY,
    report_id INTEGER NOT NULL,
    finding_summary VARCHAR(500),
    urgency_level VARCHAR(50),
    communicated_to VARCHAR(200),
    communication_method VARCHAR(50),
    communicated_at TIMESTAMP,
    acknowledged BOOLEAN DEFAULT FALSE,
    acknowledged_at TIMESTAMP,
    radiologist VARCHAR(200),
    notes TEXT,
    FOREIGN KEY (report_id) REFERENCES radiology_reports(id)
);

Step 6: Insert Sample Studies

Add studies for reporting.

INSERT INTO report_studies (id, study_uid, patient_id, patient_name, study_date, modality, body_region, study_description, referring_physician) VALUES
    (1, '1.2.840.10001.1', 'PAT-001', 'John Smith', '2024-01-15', 'CT', 'CHEST', 'CT Chest with Contrast', 'Dr. Anderson'),
    (2, '1.2.840.10001.2', 'PAT-002', 'Mary Johnson', '2024-01-16', 'MR', 'BRAIN', 'MRI Brain without Contrast', 'Dr. Thompson'),
    (3, '1.2.840.10001.3', 'PAT-003', 'Robert Williams', '2024-01-17', 'CT', 'ABDOMEN', 'CT Abdomen Pelvis with Contrast', 'Dr. Garcia'),
    (4, '1.2.840.10001.4', 'PAT-004', 'Sarah Brown', '2024-01-18', 'XR', 'CHEST', 'Chest X-Ray PA Lateral', 'Dr. Lee'),
    (5, '1.2.840.10001.5', 'PAT-005', 'Michael Davis', '2024-01-19', 'CT', 'HEAD', 'CT Head without Contrast', 'Dr. White');

Step 7: Insert Radiology Reports

Add report content.

INSERT INTO radiology_reports (id, study_id, report_uid, report_status, technique, comparison, findings, impression, recommendations, critical_finding, dictated_by, dictated_at, verified_by, verified_at) VALUES
    (1, 1, 'RPT-2024-001', 'final',
     'CT of the chest was performed with IV contrast using a PE protocol. Axial images were obtained from the thoracic inlet to the adrenal glands.',
     'CT Chest 06/15/2023',
     'LUNGS: 8mm solid nodule in the right upper lobe (series 3, image 85), increased from 6mm on prior. No other pulmonary nodules. No consolidation or interstitial abnormality.\n\nPLEURA: No pleural effusion or pneumothorax.\n\nMEDIASTINUM: No lymphadenopathy. Heart size normal. No pericardial effusion.\n\nVESSELS: No pulmonary embolism. Aorta normal caliber.',
     '1. Growing 8mm right upper lobe pulmonary nodule, increased from 6mm. Given growth, recommend short-term follow-up or PET-CT.\n2. No pulmonary embolism.',
     'Recommend CT chest in 3 months or PET-CT for further evaluation of growing nodule.',
     FALSE, 'Dr. Miller', '2024-01-15 11:00:00', 'Dr. Senior', '2024-01-15 14:00:00'),

    (2, 2, 'RPT-2024-002', 'final',
     'MRI of the brain was performed without contrast. Standard sequences including T1, T2, FLAIR, and DWI were obtained.',
     'MRI Brain 01/05/2023',
     'BRAIN PARENCHYMA: Stable scattered white matter hyperintensities on FLAIR, compatible with chronic small vessel ischemic disease. No new lesions. No acute infarct on DWI.\n\nVENTRICLES: Normal size and configuration.\n\nEXTRA-AXIAL: No extra-axial collection.',
     '1. Stable chronic small vessel ischemic changes.\n2. No acute intracranial abnormality.',
     NULL,
     FALSE, 'Dr. Garcia', '2024-01-16 10:30:00', 'Dr. Senior', '2024-01-16 13:00:00'),

    (3, 3, 'RPT-2024-003', 'final',
     'CT of the abdomen and pelvis was performed with oral and IV contrast.',
     'CT Abdomen Pelvis 10/01/2023',
     'LIVER: Previously noted segment 6 lesion now measures 1.2cm, decreased from 2.5cm. No new hepatic lesions.\n\nKIDNEYS: Simple cortical cyst left kidney, unchanged.\n\nBOWEL: Normal appearance. No obstruction.',
     '1. Responding segment 6 liver metastasis, decreased from 2.5cm to 1.2cm.\n2. No new metastatic disease.',
     'Recommend continued oncologic follow-up with CT in 3 months.',
     FALSE, 'Dr. Miller', '2024-01-17 11:00:00', 'Dr. Senior', '2024-01-17 15:00:00'),

    (4, 4, 'RPT-2024-004', 'final',
     'PA and lateral chest radiographs were obtained.',
     'None available',
     'LUNGS: Clear. No focal consolidation.\n\nPLEURAL SPACE: No effusion.\n\nHEART: Normal size.\n\nBONES: No acute osseous abnormality.',
     'No acute cardiopulmonary abnormality.',
     NULL,
     FALSE, 'Dr. Chen', '2024-01-18 09:00:00', 'Dr. Senior', '2024-01-18 11:00:00'),

    (5, 5, 'RPT-2024-005', 'final',
     'CT of the head was performed without contrast.',
     'None',
     'BRAIN: Large acute right MCA territory infarct with associated mass effect and 5mm midline shift. Hemorrhagic transformation not identified.\n\nVENTRICLES: Compression of right lateral ventricle.\n\nBONE: No fracture.',
     'CRITICAL: Large acute right MCA territory infarct with mass effect and midline shift. Immediate clinical correlation required.',
     'Urgent neurology/neurosurgery consultation.',
     TRUE, 'Dr. Miller', '2024-01-19 08:30:00', 'Dr. Senior', '2024-01-19 08:45:00');

Step 8: Insert Structured Findings

Extract key findings.

INSERT INTO structured_findings (id, report_id, finding_category, anatomic_location, laterality, finding_type, finding_description, severity, size_mm, change_from_prior, follow_up_required, follow_up_interval, acr_code, snomed_code) VALUES
    -- CT Chest findings
    (1, 1, 'pulmonary', 'upper lobe', 'right', 'nodule', 'Solid pulmonary nodule', 'indeterminate', 8.0, 'increased', TRUE, '3 months', 'LNOD-4B', '64572001'),
    (2, 1, 'vascular', 'pulmonary arteries', 'bilateral', 'normal', 'No pulmonary embolism', 'normal', NULL, 'stable', FALSE, NULL, NULL, NULL),
    -- MRI Brain findings
    (3, 2, 'white_matter', 'periventricular', 'bilateral', 'ischemia', 'Chronic small vessel ischemic changes', 'mild', NULL, 'stable', FALSE, NULL, NULL, '79741001'),
    -- CT Abdomen findings
    (4, 3, 'hepatic', 'segment 6', 'right', 'metastasis', 'Responding liver metastasis', 'improved', 12.0, 'decreased', TRUE, '3 months', NULL, '94222008'),
    (5, 3, 'renal', 'cortex', 'left', 'cyst', 'Simple renal cyst', 'benign', 22.0, 'stable', FALSE, NULL, NULL, '64594002'),
    -- CT Head findings
    (6, 5, 'cerebral', 'MCA territory', 'right', 'infarct', 'Acute large vessel infarct', 'critical', NULL, 'new', FALSE, NULL, NULL, '230690007'),
    (7, 5, 'mass_effect', 'midline', 'right', 'shift', 'Midline shift with mass effect', 'critical', 5.0, 'new', FALSE, NULL, NULL, '276647007');

Step 9: Insert Report Templates

Add standard templates.

INSERT INTO report_templates (id, template_name, modality, body_region, exam_type, technique_template, findings_template, impression_template, created_by) VALUES
    (1, 'CT Chest Standard', 'CT', 'CHEST', 'CT Chest',
     'CT of the chest was performed [with/without] IV contrast.',
     'LUNGS: [description]\n\nPLEURA: [description]\n\nMEDIASTINUM: [description]\n\nBONES: [description]',
     '[numbered impressions]',
     'Dr. Miller'),

    (2, 'CT Chest PE Protocol', 'CT', 'CHEST', 'CT PE',
     'CT of the chest was performed with IV contrast using a PE protocol. Axial images were obtained from the thoracic inlet to the adrenal glands.',
     'PULMONARY ARTERIES: [description]\n\nLUNGS: [description]\n\nPLEURA: [description]\n\nHEART: [description]',
     '[numbered impressions including PE status]',
     'Dr. Miller'),

    (3, 'MRI Brain Routine', 'MR', 'BRAIN', 'MRI Brain',
     'MRI of the brain was performed [with/without] contrast. Standard sequences including T1, T2, FLAIR, and DWI were obtained.',
     'BRAIN PARENCHYMA: [description]\n\nVENTRICLES: [description]\n\nEXTRA-AXIAL: [description]\n\nBONES: [description]',
     '[numbered impressions]',
     'Dr. Garcia');

Step 10: Insert Critical Results

Document critical communications.

INSERT INTO critical_results (id, report_id, finding_summary, urgency_level, communicated_to, communication_method, communicated_at, acknowledged, acknowledged_at, radiologist, notes) VALUES
    (1, 5, 'Large acute right MCA territory infarct with mass effect and 5mm midline shift', 'emergent', 'Dr. White (ED Attending)', 'phone', '2024-01-19 08:35:00', TRUE, '2024-01-19 08:36:00', 'Dr. Miller', 'Patient in ED, neurology and neurosurgery consulted immediately');

Step 11: Get Complete Report

View report with all sections.

SELECT
    rs.patient_name,
    rs.study_date,
    rs.study_description,
    rr.technique,
    rr.comparison,
    rr.findings,
    rr.impression,
    rr.recommendations,
    rr.dictated_by,
    rr.verified_by,
    rr.verified_at
FROM radiology_reports rr
INNER JOIN report_studies rs ON rr.study_id = rs.id
WHERE rr.id = 1;

Step 12: Get Report with Structured Findings

Extract findings for analysis.

SELECT
    sf.finding_category,
    sf.anatomic_location,
    sf.laterality,
    sf.finding_type,
    sf.finding_description,
    sf.severity,
    sf.size_mm,
    sf.change_from_prior,
    sf.follow_up_required
FROM structured_findings sf
WHERE sf.report_id = 1
ORDER BY sf.severity DESC;

Step 13: Find Critical Reports

List critical findings.

SELECT
    rs.patient_name,
    rs.study_date,
    rs.modality,
    rr.impression,
    cr.finding_summary,
    cr.communicated_to,
    cr.communicated_at,
    cr.acknowledged
FROM radiology_reports rr
INNER JOIN report_studies rs ON rr.study_id = rs.id
LEFT JOIN critical_results cr ON rr.id = cr.report_id
WHERE rr.critical_finding = TRUE
ORDER BY rs.study_date DESC;

Step 14: Findings Requiring Follow-Up

Track follow-up items.

SELECT
    rs.patient_name,
    rs.patient_id,
    rs.study_date,
    sf.anatomic_location,
    sf.finding_description,
    sf.size_mm,
    sf.follow_up_interval
FROM structured_findings sf
INNER JOIN radiology_reports rr ON sf.report_id = rr.id
INNER JOIN report_studies rs ON rr.study_id = rs.id
WHERE sf.follow_up_required = TRUE
ORDER BY rs.study_date;

Step 15: Report Turnaround Analysis

Measure reporting efficiency.

SELECT
    rs.modality,
    COUNT(*) as report_count,
    AVG(EXTRACT(EPOCH FROM (rr.verified_at - rs.study_date)) / 3600) as avg_hours_to_final
FROM radiology_reports rr
INNER JOIN report_studies rs ON rr.study_id = rs.id
WHERE rr.report_status = 'final'
GROUP BY rs.modality
ORDER BY avg_hours_to_final;

Cleanup (Optional)

DROP TABLE IF EXISTS critical_results;
DROP TABLE IF EXISTS structured_findings;
DROP TABLE IF EXISTS report_templates;
DROP TABLE IF EXISTS radiology_reports;
DROP TABLE IF EXISTS report_studies;

Expected Outcomes

• Reports linked to studies
• Findings extracted
• Templates available
• Critical results tracked
• Follow-ups identified

Report Status Values

Key Concepts Learned

• Report-study integration
• Structured finding extraction
• Critical result tracking
• Template management
• Follow-up coordination