Q (sonnet): Can you find a study that I may emulate that has a data_clinical_outcomes.txt file and associated meta file to interogate

▶ list_guides
{}

◀ result
{"result":[{"uri":"cbioportal://mutation-frequency-guide","description":"Comprehensive guide for calculating gene mutation frequencies with gene-specific profiling denominators"},{"uri":"cbioportal://clinical-data-guide","description":"Guide for querying clinical data including patient vs sample level considerations"},{"uri":"cbioportal://sample-filtering-guide","description":"Guide for filtering samples and studies in cBioPortal queries"},{"uri":"cbioportal://common-pitfalls","description":"Guide to avoid common mistakes when querying cBioPortal data. If you already know which numbered pitfall applies, fetch just that section via read_guide(\"cbioportal://common-pitfalls#<number>\") (e.g. #16) instead of the full guide"},{"uri":"cbioportal://treatment-guide","description":"Guide for querying treatment/clinical event data including drug agents, timelines, and linking to genomic data"},{"uri":"cbioportal://faq-guide","description":"General cBioPortal FAQ: history, how to cite, data types, reference genome, abbreviations, GISTIC thresholds, API access"},{"uri":"cbioportal://statistical-tests-guide","description":"Statistical test selection guide — decision matrix for choosing Fisher's exact, Wilcoxon, chi-squared, t-test, ANOVA, etc. based on data type and group count"},{"uri":"cbioportal://gene-expression-guide","description":"Gene expression / copy-number / methylation analysis. Covers genetic_alteration_derived, profile_type discovery, and the gene_pair_coexpression view for Spearman correlation between two genes"},{"uri":"cbioportal://external-resources-guide","description":"Guide for finding external linked resources such as imaging, pathology, Minerva, HTAN, or other resource_* table links before declaring data unavailable"},{"uri":"cbioportal://gene-resolution-guide","description":"Guide for resolving ambiguous gene symbols, aliases, gene families, and shorthand such as CD3 before querying expression or alteration data"},{"uri":"cbioportal://study-resolution-guide","description":"Guide for resolving requested studies, avoiding silent substitute cohorts, and redirecting to known external cBioPortal instances when data is not in this deployment"},{"uri":"cbioportal://germline-guide","description":"Guide for querying germline variant data — storage columns, study discovery, query patterns, and somatic vs germline considerations"},{"uri":"cbioportal://study-guide/{study_id}","description":"Dynamic study-specific guide - use get_study_guide(study_id) tool to generate"}]}

▶ read_guide
{
 "uri": "cbioportal://clinical-data-guide"
}

▶ clickhouse_list_tables
{}

◀ result
# Clinical Data Query Guide

## Overview
Clinical data in cBioPortal is stored at both patient and sample levels. Understanding the distinction is crucial for accurate analysis.

## Data Organization

### Patient-Level vs Sample-Level Data
- **Patient-level**: Demographics, overall survival, disease stage (stored once per patient)
- **Sample-level**: Sample type, sequencing platform, purity (can have multiple per patient)

### Key Tables
- `clinical_patient`: Patient-level clinical attributes
- `clinical_sample`: Sample-level clinical attributes
- `clinical_data_derived`: Pre-joined view combining both levels
- `clinical_attribute_meta`: Metadata about available clinical attributes

## Recommended Approach: Use clinical_data_derived

The `clinical_data_derived` table is pre-joined and optimized for most queries:

```sql
-- Get clinical data for specific attributes
SELECT
    sample_unique_id,
    patient_unique_id,
    attribute_name,
    attribute_value
FROM clinical_data_derived
WHERE
    cancer_study_identifier = 'your_study_id'
    AND attribute_name IN ('CANCER_TYPE', 'SAMPLE_TYPE', 'TMB_NONSYNONYMOUS');
```

## Clinical Attribute Discovery

### Use clinical_attribute_meta for Discovering Available Attributes
**Always start here** to see what clinical attributes are available for a specific study:

```sql
-- Discover available clinical attributes for a study
SELECT
    attr_id,
    description,
    patient_attribute,
    cancer_study_id
FROM clinical_attribute_meta
WHERE
    cancer_study_id = (
        SELECT cancer_study_id
        FROM cancer_study
        WHERE cancer_study_identifier = 'your_study_id'
    )
ORDER BY patient_attribute, attr_id;
```

**Key Fields:**
- **attr_id**: matches attr_id in clinical_sample/clinical_patient tables
- **description**: human-readable description of the attribute
- **patient_attribute**: true = patient attribute, false = sample attribute
- **cancer_study_id**: links to cancer_study table (filter by study)

## Attribute Semantics and Matching

### Case-Insensitive Matching for Attribute Values

Clinical values are free text across studies and may differ only by case. For example, a controlled-looking value such as germline mutation status may appear as `GERMLINE`, `Germline`, or another case variant.

When filtering `clinical_data_derived.attribute_value`, use case-insensitive matching unless you have already profiled the exact values in the target study:

```sql
-- Correct: case-insensitive clinical value filter
SELECT DISTINCT sample_unique_id, patient_unique_id
FROM clinical_data_derived
WHERE cancer_study_identifier = 'your_study_id'
  AND attribute_name = 'MUTATION_STATUS'
  AND upper(attribute_value) = 'GERMLINE';
```

Do not write `attribute_value = 'GERMLINE'` without first checking all distinct values for that attribute in the study.

### Query the Requested Attribute, Not a Proxy

Do not infer one clinical attribute from a related subtype or marker. Query the actual requested attribute when it … (17073 more chars)

◀ result
{"tables":[{"name":"allele_specific_copy_number"},{"name":"alteration_driver_annotation"},{"name":"cancer_study"},{"name":"cancer_study_query_preferences"},{"name":"cancer_study_tags"},{"name":"cbioportal_sequence_state"},{"name":"clinical_attribute_counts"},{"name":"clinical_attribute_meta"},{"name":"clinical_data_derived"},{"name":"clinical_event"},{"name":"clinical_event_data"},{"name":"clinical_event_data_derived"},{"name":"clinical_event_derived"},{"name":"clinical_patient"},{"name":"clinical_sample"},{"name":"cna_event"},{"name":"cna_panel_gene_coverage"},{"name":"cna_wes_coverage"},{"name":"co_altered_genes_in_study"},{"name":"copy_number_seg"},{"name":"copy_number_seg_file"},{"name":"gene"},{"name":"gene_alias"},{"name":"gene_alteration_frequency_by_cancer_type"},{"name":"gene_cna_distribution_in_study"},{"name":"gene_mutation_frequency_by_cancer_type"},{"name":"gene_mutation_frequency_in_studies"},{"name":"gene_mutation_frequency_in_study"},{"name":"gene_mutation_variants_in_study"},{"name":"gene_pair_coexpression"},{"name":"gene_panel"},{"name":"gene_panel_list"},{"name":"gene_panel_to_gene_derived"},{"name":"generic_assay_data_derived"},{"name":"generic_assay_meta_derived"},{"name":"generic_assay_profile_entity_derived"},{"name":"generic_entity_properties"},{"name":"geneset"},{"name":"geneset_gene"},{"name":"geneset_hierarchy_leaf"},{"name":"geneset_hierarchy_node"},{"name":"genetic_alteration"},{"name":"genetic_alteration_derived"},{"name":"genetic_entity"},{"name":"genetic_profile"},{"name":"genetic_profile_link"},{"name":"genetic_profile_samples"},{"name":"genomic_event_derived"},{"name":"gistic"},{"name":"gistic_to_gene"},{"name":"info"},{"name":"mut_sig"},{"name":"mutation"},{"name":"mutation_count_by_keyword"},{"name":"mutation_derived"},{"name":"mutation_event"},{"name":"mutation_panel_gene_coverage"},{"name":"mutation_wes_coverage"},{"name":"patient"},{"name":"reference_genome"},{"name":"reference_genome_gene"},{"name":"resource_definition"},{"name":"resource_patient"},{"name":"resource_sample"},{"name":"resource_study"},{"name":"sample"},{"name":"sample_cna_event"},{"name":"sample_derived"},{"name":"sample_list"},{"name":"sample_list_list"},{"name":"sample_profile"},{"name":"sample_to_gene_panel_derived"},{"name":"structural_variant"},{"name":"sv_panel_gene_coverage"},{"name":"sv_wes_coverage"},{"name":"top_cna_genes_in_study"},{"name":"top_mutated_genes_in_cohort"},{"name":"top_mutated_genes_in_study"},{"name":"top_sv_genes_in_study"},{"name":"treatment_counts_in_study"},{"name":"treatment_regimens_in_study"},{"name":"type_of_cancer"}]}

▶ list_studies
{
 "search": "prostate MSK"
}

◀ result
{"result":[{"cancer_study_identifier":"prad_msk_2019","name":"Prostate Cancer (MSK, Cell Metab 2020)","type_of_cancer_id":"prostate","sample_count":18,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=prad_msk_2019"},{"cancer_study_identifier":"msk_met_2021","name":"MSK MetTropism (MSK, Cell 2021)","type_of_cancer_id":"mixed","sample_count":25775,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=msk_met_2021"},{"cancer_study_identifier":"prostate_msk_2024","name":"Prostate Cancer (MSK, Clin Cancer Res 2024)","type_of_cancer_id":"prostate","sample_count":2260,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=prostate_msk_2024"},{"cancer_study_identifier":"prad_msk_stopsack_2021","name":"Race Differences in Prostate Cancer (MSK, 2021)","type_of_cancer_id":"prad","sample_count":2069,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=prad_msk_stopsack_2021"},{"cancer_study_identifier":"prad_cdk12_mskcc_2020","name":"Prostate Adenocarcinoma (MSK, Eur Urol 2020)","type_of_cancer_id":"prad","sample_count":1465,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=prad_cdk12_mskcc_2020"},{"cancer_study_identifier":"prad_pik3r1_msk_2021","name":"Prostate Adenocarcinoma (MSK, Clin Cancer Res. 2022)","type_of_cancer_id":"prad","sample_count":1417,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=prad_pik3r1_msk_2021"},{"cancer_study_identifier":"prad_p1000","name":"Prostate Adenocarcinoma (MSK/DFCI, Nature Genetics 2018)","type_of_cancer_id":"prad","sample_count":1013,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=prad_p1000"},{"cancer_study_identifier":"prad_mskcc_2017","name":"Prostate Cancer (MSK, JCO Precis Oncol 2017)","type_of_cancer_id":"prad","sample_count":504,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=prad_mskcc_2017"},{"cancer_study_identifier":"prad_mcspc_mskcc_2020","name":"Metastatic castration-sensitive prostate cancer (MSK, Clin Cancer Res 2020)","type_of_cancer_id":"prad","sample_count":424,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=prad_mcspc_mskcc_2020"},{"cancer_study_identifier":"prad_mskcc","name":"Prostate Adenocarcinoma (MSK, Cancer Cell 2010)","type_of_cancer_id":"prad","sample_count":240,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=prad_mskcc"},{"cancer_study_identifier":"prad_msk_2025","name":"Prostate Adenocarcinoma (MSK, 2025)","type_of_cancer_id":"prad","sample_count":120,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=prad_msk_2025"},{"cancer_study_identifier":"prad_mskcc_2014","name":"Prostate Adenocarcinoma (MSK, PNAS 2014)","type_of_cancer_id":"prad","sample_count":104,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=prad_mskcc_2014"},{"cancer_study_identifier":"prad_idhmut_msk_2025","name":"Prostate Cancer-IDH Driver Mutant Cohort (MSK, 2024)","type_of_cancer_id":"prad","sample_c … (883 more chars)

▶ clickhouse_run_select_query
{
 "query": "SELECT DISTINCT event_type, count() as n FROM clinical_event GROUP BY event_type ORDER BY n DESC LIMIT 30"
}

◀ result
{"rows":[{"event_type":"Diagnosis","n":1699350},{"event_type":"Lab_Test","n":480739},{"event_type":"Treatment","n":228452},{"event_type":"DIAGNOSIS","n":216026},{"event_type":"Measurements","n":149880},{"event_type":"LAB_TEST","n":93797},{"event_type":"Surgery","n":78813},{"event_type":"STATUS","n":64414},{"event_type":"TREATMENT","n":37051},{"event_type":"Sequencing","n":29096},{"event_type":"Sample acquisition","n":29050},{"event_type":"SURGERY","n":25619},{"event_type":"SEQUENCING","n":25517},{"event_type":"PROCEDURES","n":23955},{"event_type":"Pathology","n":16755},{"event_type":"Sample Acquisition","n":10463},{"event_type":"PATHOLOGY","n":1795},{"event_type":"PROCEDURE","n":1232},{"event_type":"Specimen","n":1125},{"event_type":"Status","n":1057},{"event_type":"SPECIMEN","n":720},{"event_type":"Biopsy","n":570},{"event_type":"Death","n":286},{"event_type":"Procedure","n":265},{"event_type":"Brain Mets Present","n":188},{"event_type":"OTHER CANCERS","n":150},{"event_type":"BIOBANK","n":133},{"event_type":"Deceased","n":128},{"event_type":"FU MRI Progress","n":127},{"event_type":"Molecular Test","n":124}]}

▶ clickhouse_run_select_query
{
 "query": "
SELECT ce.event_type, count() as n
FROM clinical_event ce
JOIN patient p ON ce.patient_id = p.internal_id
JOIN cancer_study cs ON p.cancer_study_id = cs.cancer_study_id
WHERE cs.cancer_study_identifier = 'prad_msk_2019'
GROUP BY ce.event_type
ORDER BY n DESC
"
}

◀ result
{"rows":[]}

▶ clickhouse_run_select_query
{
 "query": "
SELECT cs.cancer_study_identifier, ce.event_type, count() as n
FROM clinical_event ce
JOIN patient p ON ce.patient_id = p.internal_id
JOIN cancer_study cs ON p.cancer_study_id = cs.cancer_study_id
WHERE upper(ce.event_type) LIKE '%OUTCOME%' OR upper(ce.event_type) LIKE '%STATUS%' OR upper(ce.event_type) LIKE '%PROGRESS%' OR upper(ce.event_type) LIKE '%RESPONSE%'
GROUP BY cs.cancer_study_identifier, ce.event_type
ORDER BY n DESC
LIMIT 40
"
}

◀ result
{"rows":[{"cancer_study_identifier":"msk_met_2021","event_type":"STATUS","n":25669},{"cancer_study_identifier":"mycf_msk_2026","event_type":"STATUS","n":6629},{"cancer_study_identifier":"brca_tcga_pan_can_atlas_2018","event_type":"STATUS","n":2294},{"cancer_study_identifier":"pancan_pcawg_2020","event_type":"STATUS","n":2237},{"cancer_study_identifier":"skcm_tcga_pan_can_atlas_2018","event_type":"STATUS","n":1964},{"cancer_study_identifier":"nsclc_ctdx_msk_2022","event_type":"STATUS","n":1745},{"cancer_study_identifier":"prad_tcga_pan_can_atlas_2018","event_type":"STATUS","n":1569},{"cancer_study_identifier":"gbm_tcga_pan_can_atlas_2018","event_type":"STATUS","n":1539},{"cancer_study_identifier":"ov_tcga_pan_can_atlas_2018","event_type":"STATUS","n":1470},{"cancer_study_identifier":"lgg_tcga_pan_can_atlas_2018","event_type":"STATUS","n":1307},{"cancer_study_identifier":"hnsc_tcga_pan_can_atlas_2018","event_type":"STATUS","n":1233},{"cancer_study_identifier":"coadread_tcga_pan_can_atlas_2018","event_type":"STATUS","n":1225},{"cancer_study_identifier":"ucec_tcga_pan_can_atlas_2018","event_type":"STATUS","n":1145},{"cancer_study_identifier":"luad_tcga_pan_can_atlas_2018","event_type":"STATUS","n":1106},{"cancer_study_identifier":"blca_tcga_pan_can_atlas_2018","event_type":"STATUS","n":1067},{"cancer_study_identifier":"lusc_tcga_pan_can_atlas_2018","event_type":"STATUS","n":1057},{"cancer_study_identifier":"thca_tcga_pan_can_atlas_2018","event_type":"STATUS","n":1052},{"cancer_study_identifier":"kirc_tcga_pan_can_atlas_2018","event_type":"STATUS","n":1051},{"cancer_study_identifier":"lihc_tcga_pan_can_atlas_2018","event_type":"STATUS","n":985},{"cancer_study_identifier":"cesc_tcga_pan_can_atlas_2018","event_type":"STATUS","n":931},{"cancer_study_identifier":"stad_tcga_pan_can_atlas_2018","event_type":"STATUS","n":930},{"cancer_study_identifier":"sarc_tcga_pan_can_atlas_2018","event_type":"STATUS","n":779},{"cancer_study_identifier":"coad_silu_2022","event_type":"STATUS","n":696},{"cancer_study_identifier":"kirp_tcga_pan_can_atlas_2018","event_type":"STATUS","n":541},{"cancer_study_identifier":"paad_tcga_pan_can_atlas_2018","event_type":"STATUS","n":508},{"cancer_study_identifier":"esca_tcga_pan_can_atlas_2018","event_type":"STATUS","n":461},{"cancer_study_identifier":"rms_msk_2026","event_type":"STATUS","n":436},{"cancer_study_identifier":"pcpg_tcga_pan_can_atlas_2018","event_type":"STATUS","n":389},{"cancer_study_identifier":"laml_tcga_pan_can_atlas_2018","event_type":"STATUS","n":388},{"cancer_study_identifier":"tgct_tcga_pan_can_atlas_2018","event_type":"STATUS","n":311},{"cancer_study_identifier":"luad_cas_2020","event_type":"Status","n":290},{"cancer_study_identifier":"meso_tcga_pan_can_atlas_2018","event_type":"STATUS","n":283},{"cancer_study_identifier":"thym_tcga_pan_can_atlas_2018","event_type":"STATUS","n":281},{"cancer_study_identifier":"breast_ink4_msk_2021","event_type":"Status","n":256},{"cancer_study_identifier":"acc_tcga_pan_can_atlas … (482 more chars)

▶ list_studies
{
 "search": "brain metastases NSCLC MSK"
}

◀ result
{"result":[]}

▶ clickhouse_run_select_query
{
 "query": "
SELECT ce.event_type, count() as n, count(DISTINCT p.internal_id) as n_patients
FROM clinical_event ce
JOIN patient p ON ce.patient_id = p.internal_id
JOIN cancer_study cs ON p.cancer_study_id = cs.cancer_study_id
WHERE cs.cancer_study_identifier = 'bm_nsclc_mskcc_2023'
GROUP BY ce.event_type
ORDER BY n DESC
"
}

◀ result
{"rows":[{"event_type":"Surgery","n":224,"n_patients":224},{"event_type":"Brain Mets Present","n":188,"n_patients":188},{"event_type":"Deceased","n":128,"n_patients":128},{"event_type":"FU MRI Progress","n":127,"n_patients":127},{"event_type":"FU Salvage","n":118,"n_patients":118}]}

▶ resolve_and_route
{
 "studyIds": [
  "bm_nsclc_mskcc_2023"
 ]
}

◀ result
{"success":true,"message":"Found 1 study. Use the metadata to call the appropriate navigation tool(s).","data":{"totalCount":1,"studiesWithMetadata":[{"studyId":"bm_nsclc_mskcc_2023","name":"Non-Small Cell Lung Cancer Brain Metastasis (MSK, Nat Commun 2023)","sampleCount":322,"studyViewUrl":"https://www.cbioportal.org/study?id=bm_nsclc_mskcc_2023","metadata":{"clinicalAttributeIds":["BM_OCCURENCE","BM_TO_PRIMARY_DX","CANCER_TYPE","CANCER_TYPE_DETAILED","DIAMETER_RESECTED_BM_LESION_MM","FRACTION_GENOME_ALTERED","INTRACRANIAL_DISEASE_PROGRESSION","INTRACRANIAL_DISEASE_PROGRESSION_TYPE","KPS","LINES_OF_THERAPY_PRIOR_TO_BM_RESECTION","MSI_TYPE","MUTATION_COUNT","NSCLC_SUBTYPE","NUMBER_BMS_AT_DX","ONCOTREE_CODE","OS_MONTHS","OS_STATUS","PFS_MONTHS","PFS_STATUS","PRIOR_PCI","PRIOR_TKI","PRIOR_WBRT","SAMPLE_CLASS","SAMPLE_COUNT","SAMPLE_TYPE","SEX","SMOKING_STATUS","SOMATIC_STATUS","SYMPTOM_AT_BM_DIAGNOSIS","SYSTEMIC_THERAPY_PRIOR_TO_BM_RESECTION","TISSUE_SPECIMEN_TYPE","TMB_NONSYNONYMOUS","TREATMENT_PRIOR_TO_BM_RESECTION","TYPES_OF_SYMPTOMS"],"molecularProfileIds":["bm_nsclc_mskcc_2023_cna","bm_nsclc_mskcc_2023_mutations","bm_nsclc_mskcc_2023_structural_variants"],"availableComparisonTabs":["overlap","clinical","survival","alterations","mutations"]}}]}}

▶ navigate_to_study_view
{
 "studyIds": [
  "bm_nsclc_mskcc_2023"
 ],
 "tab": "clinicalData"
}

◀ result
{"success":true,"message":"Navigating to https://www.cbioportal.org/study/clinicalData?id=bm_nsclc_mskcc_2023","url":"https://www.cbioportal.org/study/clinicalData?id=bm_nsclc_mskcc_2023","data":{"studyIds":["bm_nsclc_mskcc_2023"],"studies":[{"studyId":"bm_nsclc_mskcc_2023","name":"Non-Small Cell Lung Cancer Brain Metastasis (MSK, Nat Commun 2023)","sampleCount":322}],"tab":"clinicalData","hasFilters":false,"hasPlotsConfig":false,"pageDescription":"Table of clinical attributes per sample."}}

═ answer (success)
## Recommended study: `bm_nsclc_mskcc_2023`

**Non-Small Cell Lung Cancer Brain Metastasis (MSK, Nat Commun 2023)** — 322 samples

**Important caveat on methodology:** This ClickHouse database stores *parsed* clinical attributes and events, not raw study-package filenames. I cannot directly confirm from SQL that this study's underlying datahub package literally contains a file named `data_clinical_outcomes.txt` + `meta_clinical_outcomes.txt`. What I *can* confirm is that this study has an unusually rich, longitudinal **outcomes timeline** in `clinical_event` — the kind of data that specialized `*_outcomes` files are built to hold:

| Event type | Patients | Description |
|---|---|---|
| Surgery | 224 | Resection events |
| Brain Mets Present | 188 | BM detection timepoints |
| Deceased | 128 | Death events |
| FU MRI Progress | 127 | Follow-up MRI progression assessments |
| FU Salvage | 118 | Salvage therapy events |

It also has dedicated outcome-flavored clinical attributes (`INTRACRANIAL_DISEASE_PROGRESSION`, `INTRACRANIAL_DISEASE_PROGRESSION_TYPE`, `PFS_MONTHS`/`PFS_STATUS`, `OS_MONTHS`/`OS_STATUS`, `BM_TO_PRIMARY_DX`) — a good template if you're designing your own outcomes schema (progression type, timing relative to diagnosis, salvage events).

If you want to verify the literal file names/structure, I'd recommend cross-checking the raw study package on the [cBioPortal datahub GitHub repo](https://github.com/cBioPortal/datahub) for `bm_nsclc_mskcc_2023`, since that's outside what this database exposes.

[View Clinical Data Table — bm_nsclc_mskcc_2023](https://www.cbioportal.org/study/clinicalData?id=bm_nsclc_mskcc_2023)

Table of clinical attributes per sample.