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');