Patient Imaging History

Objective

Create a system for tracking patient imaging history over time, enabling longitudinal analysis, comparison studies, and comprehensive imaging timelines.

Step 1: Create Patients Table

Store patient demographics.

CREATE TABLE imaging_patients (
    id INTEGER PRIMARY KEY,
    patient_id VARCHAR(50) NOT NULL UNIQUE,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    date_of_birth DATE,
    gender VARCHAR(20),
    primary_physician VARCHAR(200),
    total_studies INTEGER DEFAULT 0,
    last_study_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Patient Studies Table

Store all imaging studies.

CREATE TABLE patient_studies (
    id INTEGER PRIMARY KEY,
    patient_id INTEGER NOT NULL,
    study_uid VARCHAR(100) UNIQUE,
    study_date DATE NOT NULL,
    study_time TIME,
    modality VARCHAR(20),
    body_region VARCHAR(100),
    study_description VARCHAR(255),
    institution VARCHAR(200),
    referring_physician VARCHAR(200),
    radiologist VARCHAR(200),
    image_count INTEGER DEFAULT 0,
    has_report BOOLEAN DEFAULT FALSE,
    report_date DATE,
    study_status VARCHAR(50) DEFAULT 'completed',
    FOREIGN KEY (patient_id) REFERENCES imaging_patients(id)
);

Step 3: Create Study Comparisons Table

Link related studies for comparison.

CREATE TABLE study_comparisons (
    id INTEGER PRIMARY KEY,
    current_study_id INTEGER NOT NULL,
    prior_study_id INTEGER NOT NULL,
    comparison_type VARCHAR(50),
    time_interval_days INTEGER,
    compared_by VARCHAR(100),
    comparison_notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (current_study_id) REFERENCES patient_studies(id),
    FOREIGN KEY (prior_study_id) REFERENCES patient_studies(id)
);

Step 4: Create Key Images Table

Store significant images from studies.

CREATE TABLE key_images (
    id INTEGER PRIMARY KEY,
    study_id INTEGER NOT NULL,
    image_data IMAGE(JPEG),
    thumbnail IMAGE(JPEG),
    image_description VARCHAR(255),
    finding_summary TEXT,
    marked_by VARCHAR(100),
    marked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (study_id) REFERENCES patient_studies(id)
);

Step 5: Create Imaging Alerts Table

Track follow-up recommendations.

CREATE TABLE imaging_alerts (
    id INTEGER PRIMARY KEY,
    patient_id INTEGER NOT NULL,
    study_id INTEGER NOT NULL,
    alert_type VARCHAR(50),
    finding VARCHAR(255),
    recommended_followup VARCHAR(255),
    followup_interval VARCHAR(50),
    due_date DATE,
    alert_status VARCHAR(50) DEFAULT 'active',
    resolved_date DATE,
    resolved_by VARCHAR(100),
    FOREIGN KEY (patient_id) REFERENCES imaging_patients(id),
    FOREIGN KEY (study_id) REFERENCES patient_studies(id)
);

Step 6: Insert Sample Patients

Add patient records.

INSERT INTO imaging_patients (id, patient_id, first_name, last_name, date_of_birth, gender, primary_physician, total_studies, last_study_date) VALUES
    (1, 'PAT-10001', 'John', 'Smith', '1955-03-15', 'Male', 'Dr. Anderson', 8, '2024-01-15'),
    (2, 'PAT-10002', 'Mary', 'Johnson', '1968-07-22', 'Female', 'Dr. Thompson', 5, '2024-01-10'),
    (3, 'PAT-10003', 'Robert', 'Williams', '1972-11-08', 'Male', 'Dr. Garcia', 12, '2024-01-18'),
    (4, 'PAT-10004', 'Sarah', 'Brown', '1980-04-30', 'Female', 'Dr. Miller', 3, '2024-01-05'),
    (5, 'PAT-10005', 'Michael', 'Davis', '1960-09-12', 'Male', 'Dr. Anderson', 15, '2024-01-20');

Step 7: Insert Patient Studies

Add imaging history.

INSERT INTO patient_studies (id, patient_id, study_uid, study_date, study_time, modality, body_region, study_description, institution, referring_physician, radiologist, image_count, has_report, report_date, study_status) VALUES
    -- Patient 1 history
    (1, 1, '1.2.840.10001.1.1', '2022-06-15', '09:00:00', 'CT', 'CHEST', 'CT Chest Low Dose Lung Screening', 'City General', 'Dr. Anderson', 'Dr. Miller', 200, TRUE, '2022-06-15', 'completed'),
    (2, 1, '1.2.840.10001.1.2', '2023-06-20', '10:30:00', 'CT', 'CHEST', 'CT Chest Low Dose Lung Screening', 'City General', 'Dr. Anderson', 'Dr. Miller', 210, TRUE, '2023-06-20', 'completed'),
    (3, 1, '1.2.840.10001.1.3', '2024-01-15', '09:15:00', 'CT', 'CHEST', 'CT Chest Low Dose Lung Screening', 'City General', 'Dr. Anderson', 'Dr. Miller', 205, TRUE, '2024-01-15', 'completed'),
    (4, 1, '1.2.840.10001.1.4', '2023-03-10', '14:00:00', 'XR', 'CHEST', 'Chest X-Ray PA Lateral', 'City General', 'Dr. Anderson', 'Dr. Chen', 2, TRUE, '2023-03-10', 'completed'),
    -- Patient 2 history
    (5, 2, '1.2.840.10002.1.1', '2023-01-05', '11:00:00', 'MR', 'BRAIN', 'MRI Brain with Contrast', 'City General', 'Dr. Thompson', 'Dr. Garcia', 300, TRUE, '2023-01-05', 'completed'),
    (6, 2, '1.2.840.10002.1.2', '2024-01-10', '10:00:00', 'MR', 'BRAIN', 'MRI Brain with Contrast', 'City General', 'Dr. Thompson', 'Dr. Garcia', 320, TRUE, '2024-01-10', 'completed'),
    -- Patient 3 history
    (7, 3, '1.2.840.10003.1.1', '2022-05-01', '08:30:00', 'CT', 'ABDOMEN', 'CT Abdomen Pelvis with Contrast', 'City General', 'Dr. Garcia', 'Dr. Miller', 400, TRUE, '2022-05-01', 'completed'),
    (8, 3, '1.2.840.10003.1.2', '2022-11-15', '09:00:00', 'CT', 'ABDOMEN', 'CT Abdomen Pelvis with Contrast', 'City General', 'Dr. Garcia', 'Dr. Miller', 420, TRUE, '2022-11-15', 'completed'),
    (9, 3, '1.2.840.10003.1.3', '2023-05-20', '10:00:00', 'CT', 'ABDOMEN', 'CT Abdomen Pelvis with Contrast', 'City General', 'Dr. Garcia', 'Dr. Miller', 415, TRUE, '2023-05-20', 'completed'),
    (10, 3, '1.2.840.10003.1.4', '2024-01-18', '09:30:00', 'CT', 'ABDOMEN', 'CT Abdomen Pelvis with Contrast', 'City General', 'Dr. Garcia', 'Dr. Miller', 430, TRUE, '2024-01-18', 'completed'),
    -- Patient 5 history
    (11, 5, '1.2.840.10005.1.1', '2023-06-01', '11:00:00', 'CT', 'CHEST', 'CT Chest with Contrast', 'City General', 'Dr. Anderson', 'Dr. Miller', 350, TRUE, '2023-06-01', 'completed'),
    (12, 5, '1.2.840.10005.1.2', '2023-09-15', '10:00:00', 'PET', 'WHOLEBODY', 'PET-CT Whole Body', 'Cancer Center', 'Dr. Lee', 'Dr. Wilson', 800, TRUE, '2023-09-15', 'completed'),
    (13, 5, '1.2.840.10005.1.3', '2024-01-20', '09:00:00', 'CT', 'CHEST', 'CT Chest with Contrast', 'City General', 'Dr. Anderson', 'Dr. Miller', 360, TRUE, '2024-01-20', 'completed');

Step 8: Insert Study Comparisons

Link related studies.

INSERT INTO study_comparisons (id, current_study_id, prior_study_id, comparison_type, time_interval_days, compared_by, comparison_notes) VALUES
    -- Patient 1 lung screening follow-ups
    (1, 2, 1, 'follow-up', 370, 'Dr. Miller', 'Annual lung screening comparison'),
    (2, 3, 2, 'follow-up', 209, 'Dr. Miller', 'Follow-up lung screening, nodule tracking'),
    -- Patient 2 brain MRI
    (3, 6, 5, 'follow-up', 370, 'Dr. Garcia', 'Annual MS surveillance'),
    -- Patient 3 oncology follow-ups
    (4, 8, 7, 'follow-up', 198, 'Dr. Miller', '6-month oncology follow-up'),
    (5, 9, 8, 'follow-up', 186, 'Dr. Miller', '6-month oncology follow-up'),
    (6, 10, 9, 'follow-up', 243, 'Dr. Miller', 'Treatment response assessment'),
    -- Patient 5 oncology
    (7, 13, 11, 'follow-up', 233, 'Dr. Miller', 'Post-treatment surveillance');

Step 9: Insert Key Images

Mark important images.

INSERT INTO key_images (id, study_id, image_description, finding_summary, marked_by) VALUES
    (1, 1, 'Axial CT slice 85', 'Initial 6mm RUL nodule identified', 'Dr. Miller'),
    (2, 2, 'Axial CT slice 82', 'RUL nodule stable at 6mm', 'Dr. Miller'),
    (3, 3, 'Axial CT slice 80', 'RUL nodule increased to 8mm', 'Dr. Miller'),
    (4, 5, 'Axial FLAIR slice 15', 'Multiple periventricular white matter lesions', 'Dr. Garcia'),
    (5, 6, 'Axial FLAIR slice 15', 'Stable white matter lesion burden', 'Dr. Garcia'),
    (6, 7, 'Axial CT slice 150', 'Liver lesion segment 6 - 2.5cm', 'Dr. Miller'),
    (7, 10, 'Axial CT slice 148', 'Liver lesion decreased to 1.2cm', 'Dr. Miller');

Step 10: Insert Imaging Alerts

Add follow-up recommendations.

INSERT INTO imaging_alerts (id, patient_id, study_id, alert_type, finding, recommended_followup, followup_interval, due_date, alert_status) VALUES
    (1, 1, 3, 'nodule_followup', 'Growing RUL nodule 8mm', 'CT Chest Low Dose', '3 months', '2024-04-15', 'active'),
    (2, 2, 6, 'surveillance', 'MS white matter lesions', 'MRI Brain', '12 months', '2025-01-10', 'active'),
    (3, 3, 10, 'oncology_followup', 'Responding liver metastasis', 'CT Abdomen Pelvis', '3 months', '2024-04-18', 'active'),
    (4, 5, 13, 'oncology_followup', 'Post-treatment surveillance', 'CT Chest', '3 months', '2024-04-20', 'active'),
    (5, 1, 1, 'nodule_followup', 'Incidental 6mm RUL nodule', 'CT Chest Low Dose', '12 months', '2023-06-15', 'resolved');

Step 11: Get Patient Imaging Timeline

View complete patient history.

SELECT
    ps.study_date,
    ps.modality,
    ps.body_region,
    ps.study_description,
    ps.image_count,
    ps.radiologist,
    ps.study_status
FROM patient_studies ps
WHERE ps.patient_id = 1
ORDER BY ps.study_date DESC;

Step 12: Get Study with Comparisons

View study with prior comparisons.

SELECT
    current_study.study_date as current_date,
    current_study.study_description as current_study,
    prior_study.study_date as prior_date,
    prior_study.study_description as prior_study,
    sc.time_interval_days || ' days' as interval,
    sc.comparison_notes
FROM study_comparisons sc
INNER JOIN patient_studies current_study ON sc.current_study_id = current_study.id
INNER JOIN patient_studies prior_study ON sc.prior_study_id = prior_study.id
WHERE current_study.id = 3;

Step 13: Get Active Imaging Alerts

Find pending follow-ups.

SELECT
    ip.first_name || ' ' || ip.last_name as patient,
    ia.finding,
    ia.recommended_followup,
    ia.followup_interval,
    ia.due_date,
    CASE
        WHEN ia.due_date < CURRENT_DATE THEN 'Overdue'
        WHEN ia.due_date <= CURRENT_DATE + 30 THEN 'Due Soon'
        ELSE 'Scheduled'
    END as urgency
FROM imaging_alerts ia
INNER JOIN imaging_patients ip ON ia.patient_id = ip.id
WHERE ia.alert_status = 'active'
ORDER BY ia.due_date;

Step 14: Patient Study Volume Analysis

Analyze imaging utilization.

SELECT
    ip.first_name || ' ' || ip.last_name as patient,
    ip.total_studies,
    COUNT(ps.id) as verified_count,
    MIN(ps.study_date) as first_study,
    MAX(ps.study_date) as last_study,
    COUNT(DISTINCT ps.modality) as modalities_used
FROM imaging_patients ip
LEFT JOIN patient_studies ps ON ip.id = ps.patient_id
GROUP BY ip.id, ip.first_name, ip.last_name, ip.total_studies
ORDER BY ip.total_studies DESC;

Step 15: Modality History by Patient

View studies grouped by modality.

SELECT
    ps.modality,
    COUNT(*) as study_count,
    MIN(ps.study_date) as first_date,
    MAX(ps.study_date) as last_date,
    SUM(ps.image_count) as total_images
FROM patient_studies ps
WHERE ps.patient_id = 3
GROUP BY ps.modality
ORDER BY study_count DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS imaging_alerts;
DROP TABLE IF EXISTS key_images;
DROP TABLE IF EXISTS study_comparisons;
DROP TABLE IF EXISTS patient_studies;
DROP TABLE IF EXISTS imaging_patients;

Expected Outcomes

• Patient histories tracked
• Studies organized chronologically
• Comparisons linked
• Alerts managed
• Key images preserved

Alert Types

Key Concepts Learned

• Longitudinal patient tracking
• Study comparison linking
• Follow-up alert management
• Key image documentation
• Imaging timeline queries