CT and MRI Scan Organization

Objective

Create a comprehensive system for managing CT and MRI cross-sectional imaging studies with support for multiple series, sequences, and post-processing reconstructions.

Step 1: Create Scan Patients Table

Store patient information for cross-sectional imaging.

CREATE TABLE scan_patients (
    id INTEGER PRIMARY KEY,
    patient_id VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(200),
    date_of_birth DATE,
    gender VARCHAR(20),
    weight_kg DECIMAL(5, 2),
    allergies TEXT,
    creatinine DECIMAL(4, 2),
    gfr INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create CT/MRI Studies Table

Store study-level information.

CREATE TABLE cross_sectional_studies (
    id INTEGER PRIMARY KEY,
    patient_id INTEGER NOT NULL,
    study_date DATE NOT NULL,
    study_time TIME,
    modality VARCHAR(10) NOT NULL,
    body_region VARCHAR(100),
    study_description VARCHAR(255),
    protocol_name VARCHAR(100),
    contrast_used BOOLEAN DEFAULT FALSE,
    contrast_agent VARCHAR(100),
    contrast_volume_ml DECIMAL(5, 2),
    scanner_model VARCHAR(100),
    referring_physician VARCHAR(200),
    radiologist VARCHAR(200),
    series_count INTEGER DEFAULT 0,
    total_images INTEGER DEFAULT 0,
    status VARCHAR(50) DEFAULT 'acquired',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (patient_id) REFERENCES scan_patients(id)
);

Step 3: Create Series Table

Store series/sequence information.

CREATE TABLE scan_series (
    id INTEGER PRIMARY KEY,
    study_id INTEGER NOT NULL,
    series_number INTEGER,
    series_description VARCHAR(255),
    series_type VARCHAR(50),
    plane VARCHAR(50),
    slice_thickness_mm DECIMAL(5, 2),
    slice_spacing_mm DECIMAL(5, 2),
    field_of_view_mm INTEGER,
    matrix_size VARCHAR(20),
    image_count INTEGER DEFAULT 0,
    acquisition_time TIME,
    -- CT specific
    kvp INTEGER,
    ma INTEGER,
    ctdi_vol DECIMAL(6, 2),
    dlp DECIMAL(8, 2),
    -- MRI specific
    tr_ms DECIMAL(8, 2),
    te_ms DECIMAL(6, 2),
    flip_angle INTEGER,
    sequence_type VARCHAR(50),
    FOREIGN KEY (study_id) REFERENCES cross_sectional_studies(id)
);

Step 4: Create Scan Images Table

Store individual slice images.

CREATE TABLE scan_images (
    id INTEGER PRIMARY KEY,
    series_id INTEGER NOT NULL,
    instance_number INTEGER,
    image_data IMAGE(PNG),
    thumbnail IMAGE(JPEG),
    slice_location_mm DECIMAL(8, 2),
    window_center INTEGER,
    window_width INTEGER,
    pixel_spacing VARCHAR(20),
    rows INTEGER,
    columns INTEGER,
    file_size BIGINT,
    FOREIGN KEY (series_id) REFERENCES scan_series(id)
);

Step 5: Create Measurements Table

Store radiologist measurements.

CREATE TABLE scan_measurements (
    id INTEGER PRIMARY KEY,
    study_id INTEGER NOT NULL,
    series_id INTEGER,
    measurement_type VARCHAR(50),
    structure_name VARCHAR(100),
    value DECIMAL(10, 2),
    unit VARCHAR(20),
    comparison_value DECIMAL(10, 2),
    comparison_date DATE,
    change_percent DECIMAL(5, 2),
    measured_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (study_id) REFERENCES cross_sectional_studies(id),
    FOREIGN KEY (series_id) REFERENCES scan_series(id)
);

Step 6: Create Findings Table

Document imaging findings.

CREATE TABLE scan_findings (
    id INTEGER PRIMARY KEY,
    study_id INTEGER NOT NULL,
    organ_system VARCHAR(100),
    finding_category VARCHAR(50),
    finding_description TEXT,
    location VARCHAR(100),
    size_mm VARCHAR(50),
    severity VARCHAR(50),
    follow_up_needed BOOLEAN DEFAULT FALSE,
    follow_up_interval VARCHAR(50),
    created_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (study_id) REFERENCES cross_sectional_studies(id)
);

Step 7: Insert Sample Patients

Add patient records with relevant clinical data.

INSERT INTO scan_patients (id, patient_id, name, date_of_birth, gender, weight_kg, allergies, creatinine, gfr) VALUES
    (1, 'CS-PAT-001', 'Thomas Anderson', '1960-03-15', 'Male', 85.0, 'None', 1.1, 75),
    (2, 'CS-PAT-002', 'Jennifer Martinez', '1975-08-22', 'Female', 65.0, 'Iodine contrast', 0.9, 92),
    (3, 'CS-PAT-003', 'William Johnson', '1952-11-08', 'Male', 92.0, 'None', 1.4, 55),
    (4, 'CS-PAT-004', 'Amanda Lee', '1988-04-30', 'Female', 58.0, 'None', 0.8, 105),
    (5, 'CS-PAT-005', 'Richard Davis', '1968-07-12', 'Male', 78.0, 'Gadolinium', 1.0, 82);

Step 8: Insert CT/MRI Studies

Add imaging studies.

INSERT INTO cross_sectional_studies (id, patient_id, study_date, study_time, modality, body_region, study_description, protocol_name, contrast_used, contrast_agent, contrast_volume_ml, scanner_model, referring_physician, radiologist, series_count, total_images, status) VALUES
    (1, 1, '2024-01-15', '09:00:00', 'CT', 'CHEST', 'CT Chest with Contrast', 'CT Chest PE Protocol', TRUE, 'Omnipaque 350', 100.0, 'GE Revolution CT', 'Dr. Anderson', 'Dr. Miller', 5, 750, 'completed'),
    (2, 1, '2024-01-15', '09:30:00', 'CT', 'ABDOMEN_PELVIS', 'CT Abdomen Pelvis with Contrast', 'CT AP Routine', TRUE, 'Omnipaque 350', 125.0, 'GE Revolution CT', 'Dr. Anderson', 'Dr. Miller', 4, 600, 'completed'),
    (3, 2, '2024-01-16', '10:00:00', 'MR', 'BRAIN', 'MRI Brain without Contrast', 'MR Brain Routine', FALSE, NULL, NULL, 'Siemens Prisma 3T', 'Dr. Thompson', 'Dr. Garcia', 8, 400, 'completed'),
    (4, 3, '2024-01-17', '14:00:00', 'CT', 'HEAD', 'CT Head without Contrast', 'CT Head Stroke', FALSE, NULL, NULL, 'GE Revolution CT', 'Dr. White', 'Dr. Miller', 2, 80, 'completed'),
    (5, 4, '2024-01-18', '11:00:00', 'MR', 'SPINE', 'MRI Lumbar Spine without Contrast', 'MR Lumbar Routine', FALSE, NULL, NULL, 'Siemens Prisma 3T', 'Dr. Lee', 'Dr. Garcia', 6, 240, 'completed'),
    (6, 5, '2024-01-19', '08:00:00', 'MR', 'KNEE', 'MRI Left Knee without Contrast', 'MR Knee Routine', FALSE, NULL, NULL, 'Siemens Prisma 3T', 'Dr. Anderson', 'Dr. Garcia', 7, 280, 'in_progress');

Step 9: Insert Series Information

Add series with technical parameters.

INSERT INTO scan_series (id, study_id, series_number, series_description, series_type, plane, slice_thickness_mm, slice_spacing_mm, field_of_view_mm, matrix_size, image_count, kvp, ma, ctdi_vol, dlp, tr_ms, te_ms, flip_angle, sequence_type) VALUES
    -- CT Chest series
    (1, 1, 1, 'Topogram', 'scout', 'coronal', NULL, NULL, 500, '512x512', 2, 120, 50, NULL, NULL, NULL, NULL, NULL, NULL),
    (2, 1, 2, 'Axial Pre-Contrast', 'primary', 'axial', 1.25, 1.0, 350, '512x512', 200, 120, 200, 12.5, 450, NULL, NULL, NULL, NULL),
    (3, 1, 3, 'Axial Arterial Phase', 'primary', 'axial', 1.25, 1.0, 350, '512x512', 200, 120, 220, 14.0, 500, NULL, NULL, NULL, NULL),
    (4, 1, 4, 'Coronal MPR', 'reconstruction', 'coronal', 3.0, 2.0, 350, '512x512', 150, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
    (5, 1, 5, 'Sagittal MPR', 'reconstruction', 'sagittal', 3.0, 2.0, 350, '512x512', 150, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
    -- MRI Brain series
    (6, 3, 1, 'Localizer', 'scout', 'multi', 5.0, 5.0, 250, '256x256', 9, NULL, NULL, NULL, NULL, 20, 5, 20, 'GRE'),
    (7, 3, 2, 'Axial T1', 'primary', 'axial', 5.0, 1.0, 220, '256x256', 30, NULL, NULL, NULL, NULL, 500, 10, 90, 'SE'),
    (8, 3, 3, 'Axial T2', 'primary', 'axial', 5.0, 1.0, 220, '256x256', 30, NULL, NULL, NULL, NULL, 4000, 100, 90, 'FSE'),
    (9, 3, 4, 'Axial FLAIR', 'primary', 'axial', 5.0, 1.0, 220, '256x256', 30, NULL, NULL, NULL, NULL, 9000, 120, 90, 'FLAIR'),
    (10, 3, 5, 'Axial DWI', 'primary', 'axial', 5.0, 1.0, 220, '128x128', 30, NULL, NULL, NULL, NULL, 5000, 80, 90, 'EPI'),
    (11, 3, 6, 'Sagittal T1', 'primary', 'sagittal', 5.0, 1.0, 220, '256x256', 25, NULL, NULL, NULL, NULL, 500, 10, 90, 'SE'),
    -- MRI Lumbar Spine series
    (12, 5, 1, 'Sagittal T1', 'primary', 'sagittal', 4.0, 0.5, 280, '320x320', 20, NULL, NULL, NULL, NULL, 600, 12, 90, 'SE'),
    (13, 5, 2, 'Sagittal T2', 'primary', 'sagittal', 4.0, 0.5, 280, '320x320', 20, NULL, NULL, NULL, NULL, 3500, 90, 90, 'FSE'),
    (14, 5, 3, 'Sagittal STIR', 'primary', 'sagittal', 4.0, 0.5, 280, '256x256', 20, NULL, NULL, NULL, NULL, 4000, 60, 90, 'STIR'),
    (15, 5, 4, 'Axial T2', 'primary', 'axial', 4.0, 4.0, 180, '256x256', 60, NULL, NULL, NULL, NULL, 3500, 90, 90, 'FSE');

Step 10: Insert Measurements

Add quantitative findings.

INSERT INTO scan_measurements (id, study_id, series_id, measurement_type, structure_name, value, unit, comparison_value, comparison_date, change_percent, measured_by) VALUES
    -- CT Chest measurements
    (1, 1, 3, 'diameter', 'Main Pulmonary Artery', 28.5, 'mm', 27.0, '2023-06-15', 5.6, 'Dr. Miller'),
    (2, 1, 3, 'diameter', 'Ascending Aorta', 35.2, 'mm', 34.8, '2023-06-15', 1.1, 'Dr. Miller'),
    (3, 1, 3, 'diameter', 'Pulmonary Nodule RLL', 8.0, 'mm', 6.5, '2023-06-15', 23.1, 'Dr. Miller'),
    -- CT Abdomen measurements
    (4, 2, NULL, 'diameter', 'Liver Lesion Segment 6', 15.0, 'mm', 12.0, '2023-06-15', 25.0, 'Dr. Miller'),
    (5, 2, NULL, 'diameter', 'Abdominal Aorta', 22.0, 'mm', NULL, NULL, NULL, 'Dr. Miller'),
    -- MRI Brain measurements
    (6, 3, 7, 'volume', 'Hippocampus Left', 3.2, 'cm3', NULL, NULL, NULL, 'Dr. Garcia'),
    (7, 3, 7, 'volume', 'Hippocampus Right', 3.4, 'cm3', NULL, NULL, NULL, 'Dr. Garcia'),
    -- MRI Spine measurements
    (8, 5, 12, 'diameter', 'Disc Bulge L4-L5', 4.0, 'mm', NULL, NULL, NULL, 'Dr. Garcia'),
    (9, 5, 12, 'diameter', 'Central Canal L4-L5', 8.0, 'mm', NULL, NULL, NULL, 'Dr. Garcia');

Step 11: Insert Findings

Document imaging findings.

INSERT INTO scan_findings (id, study_id, organ_system, finding_category, finding_description, location, size_mm, severity, follow_up_needed, follow_up_interval, created_by) VALUES
    -- CT Chest findings
    (1, 1, 'Pulmonary', 'nodule', 'Solid pulmonary nodule', 'Right lower lobe', '8mm', 'indeterminate', TRUE, '3 months', 'Dr. Miller'),
    (2, 1, 'Cardiovascular', 'normal', 'No pulmonary embolism', 'Pulmonary arteries', NULL, 'normal', FALSE, NULL, 'Dr. Miller'),
    (3, 1, 'Cardiovascular', 'finding', 'Coronary artery calcifications', 'LAD, RCA', NULL, 'mild', FALSE, NULL, 'Dr. Miller'),
    -- CT Abdomen findings
    (4, 2, 'Hepatobiliary', 'lesion', 'Hypodense liver lesion, likely hemangioma', 'Segment 6', '15mm', 'benign', TRUE, '12 months', 'Dr. Miller'),
    (5, 2, 'Renal', 'cyst', 'Simple renal cyst', 'Left kidney lower pole', '22mm', 'benign', FALSE, NULL, 'Dr. Miller'),
    -- MRI Brain findings
    (6, 3, 'Brain', 'normal', 'No acute intracranial abnormality', NULL, NULL, 'normal', FALSE, NULL, 'Dr. Garcia'),
    (7, 3, 'Brain', 'finding', 'Scattered white matter hyperintensities', 'Periventricular', NULL, 'mild', FALSE, NULL, 'Dr. Garcia'),
    -- MRI Spine findings
    (8, 5, 'Spine', 'degeneration', 'Disc desiccation and bulge', 'L4-L5', '4mm bulge', 'moderate', FALSE, NULL, 'Dr. Garcia'),
    (9, 5, 'Spine', 'stenosis', 'Mild central canal stenosis', 'L4-L5', '8mm canal', 'mild', FALSE, NULL, 'Dr. Garcia');

Step 12: Get Study with All Series

View complete study structure.

SELECT
    css.study_description,
    css.modality,
    ss.series_number,
    ss.series_description,
    ss.plane,
    ss.slice_thickness_mm || 'mm' as thickness,
    ss.image_count,
    CASE
        WHEN css.modality = 'CT' THEN ss.kvp || 'kVp'
        ELSE ss.tr_ms || '/' || ss.te_ms || 'ms'
    END as technique
FROM cross_sectional_studies css
INNER JOIN scan_series ss ON css.id = ss.study_id
WHERE css.id = 1
ORDER BY ss.series_number;

Step 13: Compare Measurements Over Time

Track lesion changes.

SELECT
    sm.structure_name,
    sm.value || ' ' || sm.unit as current,
    sm.comparison_value || ' ' || sm.unit as previous,
    sm.comparison_date,
    sm.change_percent || '%' as change,
    CASE
        WHEN sm.change_percent > 20 THEN 'Significant increase'
        WHEN sm.change_percent < -20 THEN 'Significant decrease'
        ELSE 'Stable'
    END as status
FROM scan_measurements sm
WHERE sm.study_id = 1
  AND sm.comparison_value IS NOT NULL
ORDER BY ABS(sm.change_percent) DESC;

Step 14: Findings Requiring Follow-Up

List actionable findings.

SELECT
    sp.name as patient,
    css.study_date,
    css.modality,
    sf.organ_system,
    sf.finding_description,
    sf.size_mm,
    sf.severity,
    sf.follow_up_interval
FROM scan_findings sf
INNER JOIN cross_sectional_studies css ON sf.study_id = css.id
INNER JOIN scan_patients sp ON css.patient_id = sp.id
WHERE sf.follow_up_needed = TRUE
ORDER BY css.study_date;

Step 15: Radiation Dose Summary

Analyze CT radiation exposure.

SELECT
    sp.name as patient,
    css.study_date,
    css.body_region,
    SUM(ss.ctdi_vol) as total_ctdi,
    SUM(ss.dlp) as total_dlp
FROM cross_sectional_studies css
INNER JOIN scan_patients sp ON css.patient_id = sp.id
INNER JOIN scan_series ss ON css.id = ss.study_id
WHERE css.modality = 'CT'
GROUP BY sp.id, sp.name, css.id, css.study_date, css.body_region
ORDER BY total_dlp DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS scan_findings;
DROP TABLE IF EXISTS scan_measurements;
DROP TABLE IF EXISTS scan_images;
DROP TABLE IF EXISTS scan_series;
DROP TABLE IF EXISTS cross_sectional_studies;
DROP TABLE IF EXISTS scan_patients;

Expected Outcomes

• Studies organized by modality
• Series with technical parameters
• Measurements tracked over time
• Findings documented
• Radiation dose recorded

MRI Sequence Types

Key Concepts Learned

• Multi-series study organization
• Modality-specific parameters
• Measurement comparison
• Finding categorization
• Dose tracking for CT