Perturbation and Phenotype Experiments

This document catalogs experimental data involving perturbations, treatments, and phenotype measurements across the JGI and KBase lakehouses.

Overview

Perturbation experiments (TnSeq, knockouts, stress treatments, expression profiling) are distributed across multiple databases with different levels of metadata richness:

IMG RNA-seq Experiments

The IMG RNAseq database contains transcriptomics experiments with condition/treatment metadata.

Schema

rnaseq_experiment (110 experiments)
    │
    │ exp_oid
    ▼
rnaseq_sample (312 samples)
    │
    │ experiment
    ▼
rnaseq_dataset (expression data)

Location: "img-db-2 postgresql".img_rnaseq

Key Tables

Example Perturbation Experiments

SELECT exp_name, description
FROM "img-db-2 postgresql".img_rnaseq.rnaseq_experiment
LIMIT 5

Sample-Level Conditions

Samples include detailed treatment notes:

SELECT sample_oid, description, notes, organism_name
FROM "img-db-2 postgresql".img_rnaseq.rnaseq_sample
WHERE experiment = 4

Organisms with RNA-seq Data

SELECT COUNT(*) as samples, organism_name
FROM "img-db-2 postgresql".img_rnaseq.rnaseq_sample
GROUP BY organism_name ORDER BY samples DESC LIMIT 10

IMG Proteomics Experiments

Mass spectrometry-based proteomics with condition comparisons.

Schema

ms_experiment (5 experiments)
    │
    │ exp_oid
    ▼
ms_sample (sample metadata)
    │
    │ sample_oid
    ▼
ms_protein / ms_peptide (identifications)

Location: "img-db-2 postgresql".img_proteome

Example Experiments

SELECT exp_name, description, protein_count
FROM "img-db-2 postgresql".img_proteome.ms_experiment

GOLD Organism Phenotypes

Curated phenotype annotations for organisms registered in GOLD.

Schema

cvphenotype (159 controlled terms)
    │
    │ phenotype_id
    ▼
organism_phenotype (7,113 annotations)
    │
    │ organism_id
    ▼
organism_v2 (organism metadata)

Location: "gold-db-2 postgresql".gold

Phenotype Categories

Query Example

SELECT cv.term as phenotype, COUNT(*) as organisms
FROM "gold-db-2 postgresql".gold.organism_phenotype op
JOIN "gold-db-2 postgresql".gold.cvphenotype cv ON op.phenotype_id = cv.id
GROUP BY cv.term ORDER BY organisms DESC LIMIT 10

GOLD Studies with Perturbation Experiments

Many GOLD studies describe TnSeq, mutagenesis, and stress experiments in their descriptions.

TnSeq / Transposon Mutagenesis Studies

SELECT study_name, description
FROM "gold-db-2 postgresql".gold.study
WHERE LOWER(description) LIKE '%tnseq%'
   OR LOWER(description) LIKE '%transposon%'
   OR LOWER(description) LIKE '%mutagenesis%'

Stress and Expression Studies

SELECT study_name
FROM "gold-db-2 postgresql".gold.study
WHERE LOWER(description) LIKE '%stress%'
   OR LOWER(description) LIKE '%expression%'

MycoCosm Fungal Data

Fungal genome portals contain organism-specific expression, proteomics, and metabolomics tables.

Expression Data

Example organisms with expression data: - Emiliania huxleyi (conditional, diploid/haploid) - Chlamydomonas (microarray, mutants) - Trichoderma virens (Before/During/After Contact microarray)

Proteomics Data

Tables matching pattern proteomics_PXD* or Proteomics_*:

Metabolomics Data

Poronia crucua (myco-db-1 mysql.Porcrue1):

Transposon/Insertion Data

Many fungal genomes have TransposonRelatedModels tables for tracking mobile elements and insertions, useful for genetic studies.

KBase Phenotype Database

Structured experimental phenotype measurements.

Schema

Location: kbase_phenotype database

Schema Diagram

ENIGMA CoRAL Database

Groundwater microbiome data with environmental conditions.

Schema

Location: enigma_coral database

Sample Metadata

Samples include ENVO ontology terms for material type and MIxS environmental packages:

SELECT sdt_sample_name, depth_meter, material_sys_oterm_name
FROM enigma_coral.sdt_sample LIMIT 5

Cross-Database Query Patterns

Find Organisms with Both Phenotypes and Expression Data

-- Get organisms with phenotypes that also have RNA-seq
SELECT DISTINCT op.organism_id, cv.term
FROM "gold-db-2 postgresql".gold.organism_phenotype op
JOIN "gold-db-2 postgresql".gold.cvphenotype cv ON op.phenotype_id = cv.id
JOIN "gold-db-2 postgresql".gold.organism_v2 o ON op.organism_id = o.organism_id
WHERE o.ncbi_taxon_id IN (
    SELECT ncbi_taxon_id FROM "img-db-2 postgresql".img_rnaseq.rnaseq_sample
    WHERE ncbi_taxon_id IS NOT NULL
)

Find Studies with Specific Perturbation Types

-- Find all TnSeq/fitness studies
SELECT study_id, study_name, description
FROM "gold-db-2 postgresql".gold.study
WHERE LOWER(description) LIKE '%fitness%'
   OR LOWER(description) LIKE '%tnseq%'
   OR LOWER(description) LIKE '%knockout%'
   OR LOWER(study_name) LIKE '%fitness%'

Find Stress-Related RNA-seq Experiments

SELECT exp_oid, exp_name, description
FROM "img-db-2 postgresql".img_rnaseq.rnaseq_experiment
WHERE LOWER(description) LIKE '%stress%'
   OR LOWER(description) LIKE '%mutant%'
   OR LOWER(description) LIKE '%condition%'

Data Gaps and Limitations

Recommendations

1. For RNA-seq experiments: Start with img_rnaseq.rnaseq_experiment and filter by description
2. For organism phenotypes: Use GOLD organism_phenotype joined with cvphenotype
3. For TnSeq/mutagenesis: Search GOLD study.description for keywords
4. For fungal data: Query MycoCosm organism-specific tables
5. For condition metadata: Check rnaseq_sample.notes and description fields