Clinical Trial Analysis System

A relational database and analysis platform for immune cell population data from clinical trials. Built with SQLite, Python, and Plotly Dash.

Quick Start

Running in GitHub Codespaces

# 1. Install dependencies
pip install -r requirements.txt

# 2. Load data (creates schema and loads 10,500 samples)
python scripts/load_data_pandas.py

# 3. Launch interactive dashboard
python run_dashboard.py

Dashboard URL: http://localhost:8050

Dashboard Features

The interactive dashboard provides all analysis results:

• Part 2 - Frequency Overview: View relative frequencies for all cell populations
• Part 3 - Statistical Analysis: Compare responders vs non-responders with statistical tests
• Part 4 - Data Subset Analysis: Explore melanoma/miraclib cohort breakdowns
• Filters: Condition, treatment, timepoint, sample type, response status
• Export: Download filtered data as CSV

Database Schema

Design

The database uses a normalized relational schema with 4 tables:

projects (3 projects)
  └─→ subjects (3,500 subjects)
       └─→ samples (10,500 samples)
            └─→ cell_counts (10,500 records)

Table Definitions

projects

• project_id (TEXT, PK)

subjects

• subject_id (TEXT, PK)
• project_id (FK → projects)
• condition (melanoma, carcinoma, healthy)
• age (integer, 0-120)
• sex (M or F)
• treatment (miraclib, phauximab, none)
• response (yes, no, or NULL)

samples

• sample_id (TEXT, PK)
• subject_id (FK → subjects, CASCADE DELETE)
• sample_type (PBMC or WB)
• time_from_treatment_start (integer, days)
• UNIQUE constraint on (subject_id, sample_type, time_from_treatment_start)

cell_counts

• sample_id (TEXT, PK, FK → samples, CASCADE DELETE)
• b_cell, cd8_t_cell, cd4_t_cell, nk_cell, monocyte (integers, >= 0)

Design Rationale

Normalization: Separate tables for projects, subjects, samples, and measurements prevent data duplication and maintain consistency. Subject demographics are stored once, while multiple samples per subject are linked via foreign keys.

Referential Integrity: Foreign keys with CASCADE DELETE ensure that deleting a subject automatically removes their samples and cell counts. This prevents orphaned records and maintains data consistency.

Constraints: CHECK constraints enforce data validity at the database level (age ranges, valid sex values, non-negative cell counts). The UNIQUE constraint prevents duplicate samples for the same subject/timepoint/type combination.

Indexes: Seven indexes on commonly filtered columns (condition, treatment, timepoint, sex, sample_type) optimize analytical queries. These support the dashboard's filtering operations and statistical analyses.

Scalability

Current Capacity:

• Handles hundreds of projects
• Tens of thousands of subjects
• Hundreds of thousands of samples
• Sub-second query performance for typical analyses

Scaling to Production (millions of samples):

1. Composite Indexes: Add multi-column indexes for common filter combinations:

   CREATE INDEX idx_condition_treatment ON subjects(condition, treatment);
   CREATE INDEX idx_sample_type_time ON samples(sample_type, time_from_treatment_start);

2. Materialized Views: Pre-compute frequent aggregations:

   CREATE TABLE summary_statistics AS
   SELECT condition, treatment, timepoint, AVG(cd8_t_cell) as avg_cd8
   FROM subjects JOIN samples JOIN cell_counts
   GROUP BY condition, treatment, timepoint;

3. Partitioning: Split large tables by project or date range to improve query performance and maintenance operations.

4. Database Migration: Move to PostgreSQL for:

   • Better concurrency (multiple simultaneous analyses)
   • Advanced indexing (BRIN, partial indexes)
   • Table partitioning support
   • Connection pooling

5. Caching Layer: Add Redis for frequently accessed aggregations and filter combinations used by the dashboard.

6. Archival Strategy: Move old projects to separate archive tables to keep active tables smaller and faster.

Code Structure

Architecture

src/
├── database/          # Data layer
│   ├── schema.sql     # Table definitions
│   ├── db_manager.py  # Connection management, transactions
│   ├── data_loader.py # CSV import with validation
│   └── exceptions.py  # Custom error types
│
├── analysis/          # Business logic
│   ├── frequency_calculator.py  # Cell type percentages
│   ├── data_aggregator.py       # Group comparisons
│   ├── statistical_analyzer.py  # Responder analysis
│   └── subset_analyzer.py       # Cohort filtering
│
└── config.py          # Centralized settings

dashboard/             # Presentation layer
├── app.py             # Dash application
├── layout.py          # UI components
├── callbacks.py       # Interactivity
├── visualizations.py  # Charts
├── data_loader.py     # Dashboard data access
└── display_utils.py   # Display formatting

scripts/
└── load_data_pandas.py  # Fast data loading utility

tests/                 # Unit and integration tests

Design Rationale

Separation of Concerns: The codebase is organized into distinct layers:

• Data layer (src/database/) handles all database operations
• Business logic (src/analysis/) contains analytical calculations
• Presentation layer (dashboard/) manages UI and visualization
• Configuration (src/config.py) centralizes all settings

This separation makes the code easier to test, maintain, and extend. Each module has a single responsibility and clear interfaces.

Database Abstraction: DatabaseManager provides a clean API for database operations with context managers for automatic transaction handling:

with db_manager.transaction() as conn:
    # Operations here are atomic
    conn.execute("INSERT INTO ...")
    # Auto-commit on success, rollback on error

Data Validation: The DataLoader validates data before insertion, catching errors early with clear messages. It uses a three-phase loading strategy (projects → subjects → samples+counts) to maintain referential integrity.

Caching: FrequencyCalculator caches computed results since cell type percentages don't change. This makes the dashboard responsive even with 10,500 samples.

Error Handling: Custom exceptions (DatabaseError, ValidationError, DataLoadError) provide specific error types that can be caught and handled appropriately by calling code.

Testing: Unit tests verify individual components, while integration tests ensure the full pipeline works correctly. Test fixtures provide consistent test data.

Module Interactions

CSV File
  ↓
DataLoader (validates, loads in batches)
  ↓
DatabaseManager (manages transactions)
  ↓
SQLite Database
  ↓
FrequencyCalculator (computes percentages, caches)
  ↓
DataAggregator (groups, filters)
  ↓
Dashboard (visualizes, exports)

Dashboard

Launch the interactive dashboard with:

python run_dashboard.py

Access at: http://localhost:8050

Features

• Frequency Overview: Pie charts, heatmaps, composition analysis
• Treatment Comparison: Side-by-side comparisons across treatments
• Time Series: Track changes over Days 0, 7, and 14
• Subject Trajectory: Individual patient tracking
• Statistical Analysis: Responder vs non-responder comparison with Mann-Whitney U tests
• Subset Analysis: Melanoma/miraclib cohort breakdown

Filters: condition, treatment, timepoint, sample type, response

Export: Download filtered data as CSV

Data Summary

Metric	Count
Projects	3
Subjects	3,500
Samples	10,500
Cell Types Tracked	5 (B cells, CD8 T, CD4 T, NK, Monocytes)
Timepoints	3 (Days 0, 7, 14)

Sample Types: PBMC (7,500), Whole Blood (3,000)
Conditions: Melanoma (1,726), Carcinoma (1,301), Healthy (474)
Treatments: Miraclib (1,566), Phauximab (1,461), None (474)

Documentation

• Database Schema & Queries - Complete schema documentation, sample queries, scalability considerations
• Dashboard Guide - Features, usage examples, troubleshooting
• Statistical Analysis - Methodology, interpretation guide
• Subset Analysis - Cohort filtering, demographic breakdowns

Dependencies

Core:

• pandas >= 2.0.0
• click >= 8.1.0

Dashboard:

• dash >= 2.14.0
• plotly >= 5.18.0
• dash-bootstrap-components >= 1.5.0

Analysis:

• scipy >= 1.11.0
• numpy >= 1.24.0
• statsmodels >= 0.14.0

Testing:

• pytest >= 7.4.0

All dependencies are in requirements.txt.

Testing

# Run all tests
pytest tests/

# Run with coverage
pytest tests/ --cov=src --cov=dashboard

Project Status

All four parts of the technical assessment are complete:

1. Relational database with schema, loading, and queries
2. Cell frequency analysis and interactive dashboard
3. Statistical comparison (responder vs non-responder)
4. Subset analysis (melanoma/miraclib cohort)

Author

Aryan Chopra