Research: Galaxy PR #19305 - Implement Sample Sheets

PR: https://github.com/galaxyproject/galaxy/pull/19305 Branch: sample_sheets -> dev Merged: 2025-07-30 Stats: 113 files changed, +6504 / -235 Implements: Issue #19085

Overview

Problem

Bioinformatics workflows (e.g. ChIP-seq) require complex “sample sheets” that encode per-sample metadata not captured by Galaxy’s existing simple list/paired collection types. Without this, users had to either:

• Upload tabular files alongside lists, losing the structural connection between datasets and their metadata
• Manually encode metadata in file naming conventions

Solution

This PR introduces a new sample_sheet collection type that attaches typed, validated, columnar metadata to each element of a dataset collection. Sample sheets extend the existing collection system with:

1. column_definitions on DatasetCollection — a JSON column defining the schema (column name, type, validators, restrictions, default values)
2. columns on DatasetCollectionElement — a JSON column storing each element’s row of metadata values
3. New collection types: sample_sheet, sample_sheet:paired, sample_sheet:paired_or_unpaired, sample_sheet:record
4. Workbook (XLSX/CSV/TSV) generation and parsing — users can download spreadsheets, fill them in externally, and upload them back
5. Workflow editor UI for defining column schemas on collection inputs
6. Wizard-based collection creator for interactively building sample sheets from URIs, pasted data, existing collections, or uploaded workbooks
7. AG Grid-based spreadsheet UI for in-browser editing of sample sheet metadata
8. Rule Builder integration — new add_column_from_sample_sheet_index rule for using sample sheet metadata in the Apply Rules tool
9. __SAMPLE_SHEET_TO_TABULAR__ tool — converts sample sheet metadata to a tabular dataset for downstream use

Database Migration

File: lib/galaxy/model/migrations/alembic/versions_gxy/3af58c192752_implement_sample_sheets.py Revision: 3af58c192752, depends on 338d0e5deb03

Adds two JSON columns:

• dataset_collection.column_definitions — stores SampleSheetColumnDefinitions (list of column definition dicts)
• dataset_collection_element.columns — stores SampleSheetRow (list of column values for that element)

Both are nullable with default None, so existing collections are unaffected.

Status in codebase: EXISTS, unchanged.

Architecture

Data Model Flow

Workflow Input Definition
  -> collection_type = "sample_sheet:paired"
  -> column_definitions = [{name, type, optional, description, validators, restrictions, suggestions, default_value}, ...]

Collection Creation (API or fetch)
  -> DatasetCollection.column_definitions = column_definitions
  -> DatasetCollectionElement.columns = [value1, value2, ...] (one per element)

Downstream tool usage
  -> DatasetCollectionWrapper.sample_sheet_row(element_identifier) returns the row
  -> Rule Builder: add_column_from_sample_sheet_index extracts metadata columns for rule-based operations

API Flow

1. Workflow author defines sample_sheet:paired input with column definitions in workflow editor
2. User running workflow sees a wizard UI that lets them:
   • Select a source (remote files, paste URIs, upload workbook, select existing collection)
   • Auto-pair files if needed (for paired sample sheets)
   • Fill in metadata in an AG Grid spreadsheet
   • Download a pre-seeded workbook to fill in externally
3. Submission either:
   • Creates via fetch API (for URI-based imports): POST /api/tools/fetch with column_definitions on the target and row on each element
   • Creates via collection API (for existing datasets): POST /api/dataset_collections with column_definitions and rows on the payload

Client Flow

FormDataWorkflowRunTabs.vue  (detects sample_sheet type)
  -> SampleSheetCollectionCreator.vue
    -> SampleSheetWizard.vue (multi-step wizard)
      Step 1: Select source (SourceFromRemoteFiles, SourceFromPastedData, SourceFromWorkbook, SourceFromCollection, etc.)
      Step 2: Source-specific input (folder selection, paste area, dataset selection, etc.)
      Step 3: Auto-pairing (for paired types)
      Step 4: Upload workbook (if workbook source)
      Step 5: SampleSheetGrid.vue (AG Grid spreadsheet for editing metadata)
        -> Submit: either fetch API or collection create API

File Inventory

Backend Model Layer

Backend Schema Layer

Backend API / Services

Backend Manager Layer

Backend Workflow Layer

Backend Tools

Backend Utility Layer

Backend Tool Util / CWL

Backend Config / Job Execution

Client API Layer

Client Components — Collection Creation

Client Components — Wizard Sources

Client Components — Pairing

Client Components — Workflow Editor

Client Components — Form Data / Workflow Run

Client Components — Rule Builder

Client Components — History / Other

Client Composables / Stores / Utils

Selenium Navigation / Test Infrastructure

Test Files

Test Fixtures

Key Implementation Details

Collection Type System

Sample sheets introduce a new top-level rank type sample_sheet that can be composed:

• sample_sheet — flat list of datasets, each with columnar metadata
• sample_sheet:paired — each element is a paired collection, with metadata per pair
• sample_sheet:paired_or_unpaired — each element is either paired or unpaired
• sample_sheet:record — each element is a record (heterogeneous collection)

The regex for valid collection types:

^((list|paired|paired_or_unpaired|record)(:(list|paired|paired_or_unpaired|record))*|sample_sheet|sample_sheet:paired|sample_sheet:record|sample_sheet:paired_or_unpaired)$

Key difference from list: sample sheets cannot be nested further. A sample_sheet is always the outermost rank. This is enforced by the regex.

Column Definition Schema

class SampleSheetColumnDefinition(TypedDict):
    name: str                    # column name (no special characters)
    type: SampleSheetColumnType  # "string" | "int" | "float" | "boolean" | "element_identifier"
    optional: bool
    description: NotRequired[Optional[str]]
    default_value: NotRequired[Optional[SampleSheetColumnValueT]]
    validators: NotRequired[Optional[List[Dict[str, Any]]]]  # only safe validators: regex, in_range, length
    restrictions: NotRequired[Optional[List[SampleSheetColumnValueT]]]
    suggestions: NotRequired[Optional[List[SampleSheetColumnValueT]]]

Workbook Generation

Uses openpyxl to generate XLSX files with:

• Column headers derived from prefix columns (URI columns for sample_sheet, URI 1/URI 2 for paired) + user-defined columns
• Data validation (dropdown lists for restrictions, type validation)
• Cell protection on non-editable columns
• Instructions sheet
• Extra column help sheet (for Galaxy-recognized columns like dbkey, file_type, etc.)

Workbook Parsing

Supports three formats:

1. XLSX — detected by ZIP magic bytes (PK\x03\x04), parsed with openpyxl
2. CSV — detected by csv.Sniffer, parsed with csv module
3. TSV — detected by csv.Sniffer (delimiter=\t)

The ReadOnlyWorkbook protocol abstracts over both formats.

Rule Builder Integration

New rule add_column_from_sample_sheet_index:

• Extracts a value from source["columns"] at a given index
• Appends it as a new column to the row data
• Enables deriving collections (e.g., nested lists grouped by treatment) from sample sheet metadata

The __init_rule_data() in the collections manager propagates element.columns into the sources dict so rules can access them.

Tool Wrapper Integration

DatasetCollectionWrapper exposes sample_sheet_row(element_identifier) which returns the columns list for an element. This is used by the __SAMPLE_SHEET_TO_TABULAR__ tool’s Cheetah template to iterate over rows and produce tabular output.

Fetch API Path

When creating sample sheets from URIs, the fetch API path is used:

1. Each element in the fetch payload has a row field containing its column values
2. The target has column_definitions describing the schema
3. data_fetch.py passes row through to discovered file metadata
4. discover.py propagates rows through CollectionBuilder.add_dataset(row=row) and get_level(row=row)
5. Builder stores rows and passes them to build_collection() which sets DatasetCollectionElement.columns

API Changes Summary

New Endpoints

Modified Endpoints

New Schema Types

• SampleSheetColumnDefinitionModel
• CreateWorkbookRequest
• ParseWorkbook / ParsedWorkbook
• CreateWorkbookForCollectionApi
• ParseWorkbookForCollectionApi / ParsedWorkbookForCollection

Cross-Reference Notes

All 113 files from the PR exist at their original paths in the current codebase. The collection_specification branch HEAD (2a5538b103) matches the dev branch merge base — no additional commits have been made on top of the sample_sheets merge. The codebase is in the exact state left by the PR merge.

Test Coverage Summary

Unit Tests

• Validation: 205 lines in test_sample_sheet_util.py covering all column types, restrictions, optional fields, validators (regex, in_range, length), element_identifier validation, special character rejection
• Workbook generation/parsing: test_sample_sheet_workbook.py covers XLSX and TSV roundtrips for simple, paired, paired_or_unpaired, from-collection scenarios
• Type descriptions: test_type_descriptions.py validates the collection type regex
• Workbook utilities: test_workbook_util.py tests CSV/TSV parsing
• Fetch workbooks: test_fetch_workbooks.py adds CSV/TSV parsing, updates existing tests for new return types
• Model discovery: test_model_discovery.py tests row propagation during collection population

API Integration Tests

• Collection creation with sample_sheet type (simple, paired, element_identifier columns)
• Column definition validation (bad types, missing fields, unsafe validators)
• Row validation against column definitions (type mismatches, out-of-range)
• Workbook download/parse roundtrip via API
• Workbook for collection via API
• Fetch-based sample sheet creation
• Paired fetch-based sample sheet creation

Selenium Tests

• Workflow editor: defining sample_sheet:paired input with ChIP-seq-like column definitions
• Workflow run from URIs: paste URLs -> auto-pair -> fill grid -> submit -> verify tabular output
• Workflow run from existing collection: select list:paired -> fill metadata grid -> submit -> verify

Rules DSL Tests

• rules_dsl_spec.yml test cases for add_column_from_sample_sheet_index
• rules_test_data.py integration test data: sample_sheet -> nested list, sample_sheet:paired -> nested list:paired