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