Pathology Slide Images

Objective

Create a system for managing digital pathology whole slide images (WSI) with support for specimen tracking, staining protocols, and diagnostic annotations.

Step 1: Create Pathology Cases Table

Store case-level information.

CREATE TABLE pathology_cases (
    id INTEGER PRIMARY KEY,
    case_number VARCHAR(50) NOT NULL UNIQUE,
    patient_id VARCHAR(50),
    patient_name VARCHAR(200),
    date_of_birth DATE,
    accession_date DATE NOT NULL,
    specimen_site VARCHAR(200),
    specimen_type VARCHAR(100),
    clinical_history TEXT,
    requesting_physician VARCHAR(200),
    assigned_pathologist VARCHAR(200),
    case_status VARCHAR(50) DEFAULT 'accessioned',
    priority VARCHAR(20) DEFAULT 'routine',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Specimens Table

Track individual specimens.

CREATE TABLE pathology_specimens (
    id INTEGER PRIMARY KEY,
    case_id INTEGER NOT NULL,
    specimen_id VARCHAR(50) NOT NULL,
    specimen_label VARCHAR(100),
    tissue_type VARCHAR(100),
    collection_method VARCHAR(100),
    fixative VARCHAR(50) DEFAULT 'formalin',
    fixation_time_hours INTEGER,
    gross_description TEXT,
    block_count INTEGER DEFAULT 0,
    received_date DATE,
    received_by VARCHAR(100),
    FOREIGN KEY (case_id) REFERENCES pathology_cases(id)
);

Step 3: Create Tissue Blocks Table

Track tissue blocks.

CREATE TABLE tissue_blocks (
    id INTEGER PRIMARY KEY,
    specimen_id INTEGER NOT NULL,
    block_id VARCHAR(50) NOT NULL,
    block_designation VARCHAR(20),
    tissue_description VARCHAR(255),
    embedding_medium VARCHAR(50) DEFAULT 'paraffin',
    section_count INTEGER DEFAULT 0,
    created_date DATE,
    created_by VARCHAR(100),
    FOREIGN KEY (specimen_id) REFERENCES pathology_specimens(id)
);

Step 4: Create Slides Table

Store slide information.

CREATE TABLE pathology_slides (
    id INTEGER PRIMARY KEY,
    block_id INTEGER NOT NULL,
    slide_id VARCHAR(50) NOT NULL,
    stain_type VARCHAR(100) NOT NULL,
    stain_protocol VARCHAR(100),
    section_thickness_um INTEGER DEFAULT 4,
    slide_quality VARCHAR(50),
    scan_status VARCHAR(50) DEFAULT 'pending',
    scanned_date DATE,
    scanned_by VARCHAR(100),
    FOREIGN KEY (block_id) REFERENCES tissue_blocks(id)
);

Step 5: Create Whole Slide Images Table

Store digital slide images.

CREATE TABLE whole_slide_images (
    id INTEGER PRIMARY KEY,
    slide_id INTEGER NOT NULL,
    image_file IMAGE(JPEG),
    thumbnail IMAGE(JPEG),
    file_format VARCHAR(20),
    file_size_gb DECIMAL(6, 2),
    width_pixels BIGINT,
    height_pixels BIGINT,
    resolution_mpp DECIMAL(6, 4),
    magnification VARCHAR(20),
    compression_type VARCHAR(50),
    scanner_model VARCHAR(100),
    scan_date TIMESTAMP,
    pyramid_levels INTEGER,
    tile_size INTEGER,
    FOREIGN KEY (slide_id) REFERENCES pathology_slides(id)
);

Step 6: Create Annotations Table

Store pathologist markings.

CREATE TABLE slide_annotations (
    id INTEGER PRIMARY KEY,
    wsi_id INTEGER NOT NULL,
    annotation_type VARCHAR(50),
    region_type VARCHAR(50),
    coordinates TEXT,
    area_mm2 DECIMAL(10, 4),
    label VARCHAR(255),
    description TEXT,
    classification VARCHAR(100),
    confidence_score DECIMAL(3, 2),
    annotated_by VARCHAR(100),
    annotation_source VARCHAR(50) DEFAULT 'manual',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (wsi_id) REFERENCES whole_slide_images(id)
);

Step 7: Insert Sample Cases

Add pathology cases.

INSERT INTO pathology_cases (id, case_number, patient_id, patient_name, date_of_birth, accession_date, specimen_site, specimen_type, clinical_history, requesting_physician, assigned_pathologist, case_status, priority) VALUES
    (1, 'PATH-2024-001', 'PAT-001', 'John Smith', '1960-05-15', '2024-01-15', 'Left Breast', 'Excisional Biopsy', 'Palpable mass left breast, mammogram shows irregular density', 'Dr. Anderson', 'Dr. Williams', 'diagnosed', 'routine'),
    (2, 'PATH-2024-002', 'PAT-002', 'Mary Johnson', '1975-08-22', '2024-01-16', 'Colon', 'Colonoscopy Biopsies', 'Screening colonoscopy, polyps found', 'Dr. Thompson', 'Dr. Williams', 'diagnosed', 'routine'),
    (3, 'PATH-2024-003', 'PAT-003', 'Robert Davis', '1955-11-08', '2024-01-17', 'Prostate', 'Needle Core Biopsies', 'Elevated PSA 8.5, DRE abnormal', 'Dr. Miller', 'Dr. Chen', 'in_progress', 'routine'),
    (4, 'PATH-2024-004', 'PAT-004', 'Sarah Wilson', '1968-03-30', '2024-01-17', 'Skin', 'Punch Biopsy', 'Pigmented lesion right arm, asymmetric', 'Dr. Lee', 'Dr. Chen', 'diagnosed', 'stat'),
    (5, 'PATH-2024-005', 'PAT-005', 'Michael Brown', '1982-07-12', '2024-01-18', 'Lymph Node', 'Excisional Biopsy', 'Enlarged cervical lymph node, persistent', 'Dr. Garcia', 'Dr. Williams', 'accessioned', 'routine');

Step 8: Insert Specimens

Add specimen records.

INSERT INTO pathology_specimens (id, case_id, specimen_id, specimen_label, tissue_type, collection_method, fixative, fixation_time_hours, gross_description, block_count, received_date, received_by) VALUES
    (1, 1, 'PATH-2024-001-A', 'Left Breast Mass', 'Breast', 'Excision', 'formalin', 24, 'Irregular firm mass 2.5x2.0x1.8 cm with surrounding adipose tissue', 5, '2024-01-15', 'Tech. Wilson'),
    (2, 2, 'PATH-2024-002-A', 'Ascending Colon Polyp 1', 'Colon', 'Biopsy', 'formalin', 12, 'Polypoid tissue fragment 0.8 cm', 1, '2024-01-16', 'Tech. Brown'),
    (3, 2, 'PATH-2024-002-B', 'Ascending Colon Polyp 2', 'Colon', 'Biopsy', 'formalin', 12, 'Polypoid tissue fragment 0.6 cm', 1, '2024-01-16', 'Tech. Brown'),
    (4, 3, 'PATH-2024-003-A', 'Prostate Cores Right', 'Prostate', 'Needle Biopsy', 'formalin', 8, '6 needle core biopsies from right prostate, each 1.0-1.5 cm', 3, '2024-01-17', 'Tech. Davis'),
    (5, 3, 'PATH-2024-003-B', 'Prostate Cores Left', 'Prostate', 'Needle Biopsy', 'formalin', 8, '6 needle core biopsies from left prostate, each 1.0-1.5 cm', 3, '2024-01-17', 'Tech. Davis'),
    (6, 4, 'PATH-2024-004-A', 'Right Arm Skin Lesion', 'Skin', 'Punch Biopsy', 'formalin', 6, 'Punch biopsy 0.4 cm with central pigmentation', 1, '2024-01-17', 'Tech. Wilson');

Step 9: Insert Tissue Blocks

Add block records.

INSERT INTO tissue_blocks (id, specimen_id, block_id, block_designation, tissue_description, section_count, created_date, created_by) VALUES
    -- Breast blocks
    (1, 1, 'PATH-2024-001-A1', 'A1', 'Tumor center', 8, '2024-01-16', 'Tech. Wilson'),
    (2, 1, 'PATH-2024-001-A2', 'A2', 'Tumor periphery', 6, '2024-01-16', 'Tech. Wilson'),
    (3, 1, 'PATH-2024-001-A3', 'A3', 'Margin superior', 4, '2024-01-16', 'Tech. Wilson'),
    (4, 1, 'PATH-2024-001-A4', 'A4', 'Margin inferior', 4, '2024-01-16', 'Tech. Wilson'),
    (5, 1, 'PATH-2024-001-A5', 'A5', 'Margin deep', 4, '2024-01-16', 'Tech. Wilson'),
    -- Colon blocks
    (6, 2, 'PATH-2024-002-A1', 'A1', 'Polyp 1 entirely submitted', 3, '2024-01-17', 'Tech. Brown'),
    (7, 3, 'PATH-2024-002-B1', 'B1', 'Polyp 2 entirely submitted', 3, '2024-01-17', 'Tech. Brown'),
    -- Prostate blocks
    (8, 4, 'PATH-2024-003-A1', 'A1', 'Right apex cores 1-2', 4, '2024-01-18', 'Tech. Davis'),
    (9, 4, 'PATH-2024-003-A2', 'A2', 'Right mid cores 3-4', 4, '2024-01-18', 'Tech. Davis'),
    (10, 4, 'PATH-2024-003-A3', 'A3', 'Right base cores 5-6', 4, '2024-01-18', 'Tech. Davis'),
    -- Skin block
    (11, 6, 'PATH-2024-004-A1', 'A1', 'Lesion bisected', 6, '2024-01-17', 'Tech. Wilson');

Step 10: Insert Slides

Add slide records with stains.

INSERT INTO pathology_slides (id, block_id, slide_id, stain_type, stain_protocol, section_thickness_um, slide_quality, scan_status, scanned_date, scanned_by) VALUES
    -- Breast slides
    (1, 1, 'PATH-2024-001-A1-HE', 'H&E', 'Standard H&E', 4, 'excellent', 'scanned', '2024-01-16', 'Tech. Scanner'),
    (2, 1, 'PATH-2024-001-A1-ER', 'ER', 'ER IHC Protocol', 4, 'good', 'scanned', '2024-01-17', 'Tech. Scanner'),
    (3, 1, 'PATH-2024-001-A1-PR', 'PR', 'PR IHC Protocol', 4, 'good', 'scanned', '2024-01-17', 'Tech. Scanner'),
    (4, 1, 'PATH-2024-001-A1-HER2', 'HER2', 'HER2 IHC Protocol', 4, 'excellent', 'scanned', '2024-01-17', 'Tech. Scanner'),
    (5, 1, 'PATH-2024-001-A1-Ki67', 'Ki-67', 'Ki67 IHC Protocol', 4, 'good', 'scanned', '2024-01-17', 'Tech. Scanner'),
    (6, 2, 'PATH-2024-001-A2-HE', 'H&E', 'Standard H&E', 4, 'excellent', 'scanned', '2024-01-16', 'Tech. Scanner'),
    -- Colon slides
    (7, 6, 'PATH-2024-002-A1-HE', 'H&E', 'Standard H&E', 4, 'excellent', 'scanned', '2024-01-17', 'Tech. Scanner'),
    (8, 7, 'PATH-2024-002-B1-HE', 'H&E', 'Standard H&E', 4, 'good', 'scanned', '2024-01-17', 'Tech. Scanner'),
    -- Prostate slides
    (9, 8, 'PATH-2024-003-A1-HE', 'H&E', 'Standard H&E', 4, 'excellent', 'scanned', '2024-01-18', 'Tech. Scanner'),
    (10, 8, 'PATH-2024-003-A1-P504S', 'P504S/P63', 'PIN-4 Protocol', 4, 'good', 'pending', NULL, NULL),
    -- Skin slides
    (11, 11, 'PATH-2024-004-A1-HE', 'H&E', 'Standard H&E', 4, 'excellent', 'scanned', '2024-01-17', 'Tech. Scanner'),
    (12, 11, 'PATH-2024-004-A1-SOX10', 'SOX10', 'SOX10 IHC Protocol', 4, 'good', 'scanned', '2024-01-17', 'Tech. Scanner');

Step 11: Insert Whole Slide Images

Add WSI metadata.

INSERT INTO whole_slide_images (id, slide_id, file_format, file_size_gb, width_pixels, height_pixels, resolution_mpp, magnification, compression_type, scanner_model, scan_date, pyramid_levels, tile_size) VALUES
    (1, 1, 'SVS', 2.5, 98304, 65536, 0.2500, '40x', 'JPEG', 'Aperio AT2', '2024-01-16 14:30:00', 6, 256),
    (2, 2, 'SVS', 1.8, 81920, 54272, 0.2500, '40x', 'JPEG', 'Aperio AT2', '2024-01-17 09:15:00', 6, 256),
    (3, 3, 'SVS', 1.9, 86016, 57344, 0.2500, '40x', 'JPEG', 'Aperio AT2', '2024-01-17 09:30:00', 6, 256),
    (4, 4, 'SVS', 2.1, 90112, 59392, 0.2500, '40x', 'JPEG', 'Aperio AT2', '2024-01-17 10:00:00', 6, 256),
    (5, 5, 'SVS', 1.7, 77824, 51200, 0.2500, '40x', 'JPEG', 'Aperio AT2', '2024-01-17 10:30:00', 6, 256),
    (6, 6, 'SVS', 2.3, 94208, 62464, 0.2500, '40x', 'JPEG', 'Aperio AT2', '2024-01-16 15:00:00', 6, 256),
    (7, 7, 'SVS', 0.8, 40960, 30720, 0.2500, '40x', 'JPEG', 'Aperio AT2', '2024-01-17 11:00:00', 5, 256),
    (8, 11, 'SVS', 0.5, 32768, 24576, 0.2500, '40x', 'JPEG', 'Aperio AT2', '2024-01-17 11:30:00', 5, 256),
    (9, 12, 'SVS', 0.5, 32768, 24576, 0.2500, '40x', 'JPEG', 'Aperio AT2', '2024-01-17 12:00:00', 5, 256);

Step 12: Insert Annotations

Add pathologist findings.

INSERT INTO slide_annotations (id, wsi_id, annotation_type, region_type, area_mm2, label, description, classification, confidence_score, annotated_by, annotation_source) VALUES
    -- Breast annotations
    (1, 1, 'diagnosis', 'polygon', 12.5, 'Invasive Carcinoma', 'Invasive ductal carcinoma, grade 2', 'malignant', 1.00, 'Dr. Williams', 'manual'),
    (2, 1, 'measurement', 'line', NULL, 'Tumor Size', 'Maximum dimension 18mm', NULL, NULL, 'Dr. Williams', 'manual'),
    (3, 1, 'feature', 'polygon', 2.3, 'DCIS Component', 'Associated DCIS, cribriform pattern', 'pre-malignant', 1.00, 'Dr. Williams', 'manual'),
    (4, 2, 'biomarker', 'region', 8.5, 'ER Positive', 'Strong nuclear staining 95%', 'positive', 0.95, 'Dr. Williams', 'manual'),
    (5, 3, 'biomarker', 'region', 8.5, 'PR Positive', 'Strong nuclear staining 80%', 'positive', 0.80, 'Dr. Williams', 'manual'),
    (6, 4, 'biomarker', 'region', 8.5, 'HER2 Negative', 'Score 1+', 'negative', 1.00, 'Dr. Williams', 'manual'),
    -- Colon annotations
    (7, 7, 'diagnosis', 'polygon', 3.2, 'Tubular Adenoma', 'Tubular adenoma with low-grade dysplasia', 'pre-malignant', 1.00, 'Dr. Williams', 'manual'),
    -- Skin annotations
    (8, 8, 'diagnosis', 'polygon', 0.8, 'Melanoma', 'Invasive melanoma, Breslow 1.2mm', 'malignant', 1.00, 'Dr. Chen', 'manual'),
    (9, 8, 'measurement', 'point', NULL, 'Clark Level', 'Level IV invasion', NULL, NULL, 'Dr. Chen', 'manual');

Step 13: Get Case with All Slides

View complete case slides.

SELECT
    pc.case_number,
    ps.specimen_label,
    tb.block_designation,
    psl.stain_type,
    psl.slide_quality,
    wsi.file_size_gb || ' GB' as file_size,
    wsi.magnification
FROM pathology_cases pc
INNER JOIN pathology_specimens ps ON pc.id = ps.case_id
INNER JOIN tissue_blocks tb ON ps.id = tb.specimen_id
INNER JOIN pathology_slides psl ON tb.id = psl.block_id
LEFT JOIN whole_slide_images wsi ON psl.id = wsi.slide_id
WHERE pc.id = 1
ORDER BY ps.specimen_id, tb.block_designation, psl.stain_type;

Step 14: Get Slide with Annotations

View slide findings.

SELECT
    psl.slide_id,
    psl.stain_type,
    sa.annotation_type,
    sa.label,
    sa.description,
    sa.classification,
    sa.area_mm2,
    sa.annotated_by
FROM pathology_slides psl
INNER JOIN whole_slide_images wsi ON psl.id = wsi.slide_id
INNER JOIN slide_annotations sa ON wsi.id = sa.wsi_id
WHERE psl.id = 1
ORDER BY sa.annotation_type;

Step 15: Pending Scans Report

Find slides awaiting scanning.

SELECT
    pc.case_number,
    pc.priority,
    ps.specimen_label,
    psl.slide_id,
    psl.stain_type,
    psl.scan_status
FROM pathology_slides psl
INNER JOIN tissue_blocks tb ON psl.block_id = tb.id
INNER JOIN pathology_specimens ps ON tb.specimen_id = ps.id
INNER JOIN pathology_cases pc ON ps.case_id = pc.id
WHERE psl.scan_status = 'pending'
ORDER BY pc.priority DESC, pc.accession_date;

Cleanup (Optional)

DROP TABLE IF EXISTS slide_annotations;
DROP TABLE IF EXISTS whole_slide_images;
DROP TABLE IF EXISTS pathology_slides;
DROP TABLE IF EXISTS tissue_blocks;
DROP TABLE IF EXISTS pathology_specimens;
DROP TABLE IF EXISTS pathology_cases;

Expected Outcomes

• Cases organized with specimens
• Blocks and slides tracked
• WSI metadata stored
• Annotations documented
• Scan workflow managed

Common Stain Types

Key Concepts Learned

• Pathology workflow hierarchy
• Specimen to slide tracking
• Whole slide image management
• Annotation storage
• Biomarker documentation