Technical Design Document: Cohort Builder (2.0)

This document describes the backend architecture for Cohort Builder 2.0. It builds on the Cohort Builder - Technical Details - (1.0) design that is already in production. The scope is backend only — UI/UX design is covered separately.

JIRA:

PLFM-9721 - Getting issue details... STATUS

Status: DRAFT

Target: Design complete by Jun 30, 2026 | Implementation target end of Q3 2026

1. Problem Statement and Goals

1.1 What Broke in 1.0

Cohort Builder 1.0 was designed for a one-to-many relationship (~100 files per individual). In practice, certain studies (e.g., LLFM with 5,000+ participants) exhibit a many-to-many relationship where every individual maps to every file. This creates a cartesian explosion in the MATERIAL (MaterializedView) table that breaks Synapse row limits and degrades query performance.

Additionally, 1.0 assumed all users had full access to the PARTICIPANTS table. In 2.0, we must support users who do not have access to individual-level participant data but are permitted to see aggregate counts under governance constraints.

1.2 New Requirements for 2.0

Requirement	Problem	Source

Requirement	Problem	Source
Many:many resolution	File-to-participant mapping tables explode for dense studies	ARQ-6
Aggregate-only access	Unapproved users must be able to query for aggregate counts without seeing individual data	ARQ-2, PRIV-4
Sub-query cohort handoff	In 1.0, IDs are passed between views (scalability issue + privacy issue for unapproved users)	FILS-4
Count threshold gate	Total count must either be zero or meet the minimum threshold — query rejected only when count is greater than zero but below threshold	PRIV-4, meeting decision
Facet post-processing	Facet statistics must be visible but protected against arithmetic attacks via post-processing algorithms	MVP requirement, product decision
Complex query logic	Current filter API only supports AND between filters; need nested groups with OR/NOT	FILS-7
Column governance	Unrestricted GROUP BY could enable re-identification	PRIV-3, NFR-2
Query audit	No existing audit trail for queries — must detect/deter misuse	NFR-2, meeting decision
Cross-portal extensibility	ELITE and ADKP have different facets and privacy rules	ARQ-3

1.3 Scope

In scope: Backend architecture for aggregate-only access, facet post-processing, sub-query cohort handoff, privacy enforcement, enhanced filtering, governance, and audit.

Out of scope: Cohort saving/sharing (v3.0 / Q4), cross-portal cohort querying (v3.0+), tiered access UI (deferred/rejected), UI/UX design.

1.4 Goals

Support many:many studies by mapping individuals to Datasets instead of individual files
Enable authenticated users without AR approval to see aggregate counts AND facet statistics, protected by post-processing algorithms approved by ACT
Provide a sub-query mechanism so cohort definitions transfer between views without exposing IDs
Reject queries where the cohort count is greater than zero but below the ACT-configured threshold; allow zero-count responses to indicate no data exists for the selected cohort
Support nested filter groups with AND, OR, and NOT
Mandatory query audit logging with user notification

2. Architecture Overview

2.1 Two User Tiers

The 2.0 architecture supports two distinct user experiences based on their access level to the PARTICIPANTS source table:

User Tier	Access to PARTICIPANTS	Experience

User Tier	Access to PARTICIPANTS	Experience
Approved (met AR)	Full access	1.0 experience: see participant rows, facets, full query capabilities
Unapproved (authenticated, not met AR)	Aggregate only	2.0 experience: see total cohort count (hard gate: must be 0 or >= threshold) + facet statistics (post-processed, when count is above threshold); use sub-query to browse matching files

2.2 User Journeys

Approved User (unchanged from 1.0)

1. User queries PARTICIPANTS VirtualTable → sees rows + facets
2. Applies filters to define cohort
3. Clicks "View Files" → UI captures cohort definition as sub-query
4. Queries FILES VirtualTable with sub-query filter applied
5. Browses files → adds to download cart

Unapproved User (new in 2.0)

1. User queries PARTICIPANTS VirtualTable
2. Preflight: total count = 0 or >= threshold?
3. If count = 0: return count 0 with a “no data for selected cohort” response; no facets are needed
4. If 0 < count < threshold: reject immediately
5. If count >= threshold: return total count (N = 847) + post-processed facet statistics
6. User applies filters using facet values visible in the statistics
7. Each filter update re-runs preflight: count = 0 or >= threshold?
8. If count becomes 0: show “no data for selected cohort”
9. If count drops below threshold but remains > 0: “Cannot proceed — cohort too small”
10. If count >= threshold: user sees updated post-processed facets
11. User clicks “View Files” → UI submits sub-query filter to FILES VT:
     WHERE participantId IN (SELECT individualId FROM syn_participants_vt WHERE [filters])
12. Backend validates: sub-query count = 0 or >= threshold? Source columns not in outer SELECT?
13. Query executes → user sees file rows → adds to download cart

2.3 Query Pipeline with New Steps

User Query: "SELECT * FROM syn_files_vt WHERE participantId IN (SELECT ...)"
     │
     ▼
[1] Parse query (extended grammar supports constrained sub-query)     ← ENHANCED
     │
     ▼
[2] VirtualTableIndexDescription.preprocessQuery()                     ← EXISTING
     │  Wraps defining SQL as CTE
     ▼
[3] TableQueryManagerImpl.queryPreflight()                              ← ENHANCED
     │  a. Check outer table access (user has READ on FILES VT)
     │  b. Check sub-query source access:
     │     - Does user have full access? → allow (1.0 path)
     │     - No? → Is source DataType == AGGREGATE_DATA? 
     │       → YES: run count check (must be >= threshold)
     │       → NO: throw UnauthorizedException
     │  c. Validate: outer query does NOT select from restricted source
     ▼
[4] CombinedQuery: apply additionalFilters + selectedFacets            ← ENHANCED (nested groups)
     │
     ▼
[5] QueryTranslator: translate to index SQL                            ← EXISTING
     │
     ▼
[6] Execute query + facets                                              ← EXISTING
     │
     ▼
[7] Facet post-processing: apply bound algorithm to facet counts       ← NEW
     │
     ▼
[8] Audit logger: record query details to Kinesis                      ← NEW
     │
     ▼
[9] Return QueryResultBundle                                           ← EXISTING

3. Data Model — Individual-to-Dataset Mapping

3.1 Problem

The 1.0 MATERIAL table maps files to participants via a FILE_TO_PART table. For a study with N participants and M files where every file maps to every participant, the MATERIAL table grows to N × M rows. For LLFM (5,000 participants × thousands of files) this exceeds practical limits.

3.2 Solution: Map to Datasets

For many:many studies, curators map individuals to Datasets (existing Synapse entity type) rather than individual files. A Dataset is a curated collection of file references (max 30K items). This reduces the mapping cardinality dramatically — e.g., 5,000 participants × 10 datasets instead of 5,000 × 50,000 files.

Key Decision: The Dataset entity already exists in Synapse and already integrates with the download cart. No new entity types are needed.

3.3 Source Table Layout (Curator-Managed)

Table	Description	Key Columns

Table	Description	Key Columns
Participants	One row per participant, all metadata	individualId, sex, age, diagnosis, study, ...
Individual-to-File mapping	For one:many studies (unchanged from 1.0)	individualId, fileEntityId
Individual-to-Dataset mapping	For many:many studies (NEW)	individualId, datasetId

3.4 MATERIAL Table (MaterializedView)

The MATERIAL table's defining SQL is updated to support both mapping types:

SELECT
    p.individualId, p.sex, p.age, p.diagnosis, p.study,
    COALESCE(fm.fileEntityId, dm.datasetId) AS dataReference,
    CASE
        WHEN fm.fileEntityId IS NOT NULL THEN 'FILE'
        ELSE 'DATASET'
    END AS referenceType
FROM syn_participants p
LEFT JOIN syn_file_mapping fm ON p.individualId = fm.individualId
LEFT JOIN syn_dataset_mapping dm ON p.individualId = dm.individualId

3.5 Impact on Download Cart

The download list already supports adding Datasets via AddToDownloadListRequest. No backend changes needed — the frontend handles the hybrid file+dataset presentation.

4. Aggregate Access Exception — DataType Model

4.1 Problem

In 1.0, the query preflight calls validateTableReadAccess() which recursively checks access on the VirtualTable's dependencies. If a user hasn't met the AR on the source PARTICIPANTS table, an UnauthorizedException is thrown — no access at all.

In 2.0, ACT needs to say: "This table is restricted, but authenticated users may see aggregate counts AND facet statistics from it, provided the output is protected by a post-processing algorithm." We need a mechanism for ACT to create this exception and bind the protection parameters.

4.2 Solution: Extend DataType with Bound Algorithm

The existing changeEntityDataType() service is already used by ACT to classify data. Currently supports SENSITIVE_DATA (default) and OPEN_DATA. We extend this with a new value plus binding parameters:

DataType	Meaning	Who Can Set

DataType	Meaning	Who Can Set
`SENSITIVE_DATA`	Default. Full AR enforcement. No exceptions.	Any user with UPDATE
`OPEN_DATA`	Safe for public. READ = automatic DOWNLOAD.	ACT only
`AGGREGATE_DATA`	NEW. Authenticated users may access aggregate counts + post-processed facets, subject to threshold and bound algorithm. Full row access still requires AR approval.	ACT only

4.3 What AGGREGATE_DATA Means at Runtime

When a VirtualTable's source table has DataType = AGGREGATE_DATA:

Approved users (met the AR): Full access. Unchanged from 1.0.
Unapproved but authenticated users:
- May query the VirtualTable for total count + facet statistics
- The total count is a hard gate: if count = 0, return a zero-count response indicating that no data exists for the selected cohort; if 0 < count < threshold, reject the query entirely (no count value, no facets — just an error)
- The total count itself is returned unmodified (not post-processed) — it's the gate, not a statistic
- Facet statistics (value counts in each facet) ARE returned, but with the bound post-processing algorithm applied (see Section 4.7)
- May use a sub-query referencing this table in a filter on another table (the cohort handoff), subject to the count threshold check
- May NOT see any individual row data or column values from the restricted source in query results
Anonymous users: No access. Must be authenticated.

4.4 The Arithmetic Attack Problem

Without post-processing, exact facet counts enable arithmetic attacks. For example, if a user sees:

sex: Male=500, Female=347
diagnosis: AD=412, MCI=435

They can apply the filter sex = Male AND diagnosis = AD and then observe the new facet counts for remaining attributes. By iteratively narrowing filters, they can isolate individual participants and infer their attribute values — even without ever seeing a row of data.

The solution is to apply a post-processing algorithm that introduces uncertainty into the facet counts, making it impossible to make deterministic inferences about individuals.

4.5 Post-Processing Algorithm Framework

The system supports an extensible set of post-processing algorithms that can be applied to facet counts. Each algorithm takes a set of raw counts and produces protected counts.

Algorithm	Description	Parameters	Trade-off

Algorithm	Description	Parameters	Trade-off
`ROUNDING`	Round all facet counts to the nearest N	`roundTo` (integer, e.g., 5 or 10)	Simple, deterministic, but low entropy — repeated queries give same result
`NOISE`	Add Laplace-distributed noise to each count (differential privacy-inspired)	`epsilon` (double, privacy budget — lower = more noise)	Strong privacy guarantee, but counts change per query and can be negative (clamped to 0)

Extensibility: New algorithms can be added later (e.g., K-anonymity binning, top-coding) by adding a new enum value and implementing the FacetPostProcessor interface. The framework is designed so ACT can choose the algorithm that best fits each dataset's risk profile.

Algorithm Interface

public interface FacetPostProcessor {
    /**
     * Apply post-processing to raw facet value counts.
     * @param rawCounts the original facet value → count map
     * @param parameters algorithm-specific parameters (from the binding)
     * @return protected counts — same keys, modified values
     */
    Map<String, Long> process(Map<String, Long> rawCounts, JsonNode parameters);
}

ROUNDING Algorithm

// Example: roundTo = 5
// Input:  {Male: 503, Female: 347, Other: 12}
// Output: {Male: 505, Female: 345, Other: 10}
long rounded = Math.round((double) rawCount / roundTo) * roundTo;

NOISE Algorithm (Laplace)

// Example: epsilon = 1.0
// Laplace noise with scale = 1/epsilon
// Input:  {Male: 503, Female: 347, Other: 12}
// Output: {Male: 504, Female: 345, Other: 13}  (varies per execution)
double scale = 1.0 / epsilon;
long noised = Math.max(0, rawCount + (long) laplaceSample(scale));

4.6 DM Preview Workflow

Data managers (DMs) need to explore which algorithm and parameters produce acceptable results for their data before requesting ACT approval. The preview mechanism lets a DM see post-processed facet results on their own data, even though they have full access.

How It Works

DM has full access to the PARTICIPANTS table (they manage it)
DM adds a facetPostProcessing object to their query request
Backend recognizes this as a preview request — applies the specified algorithm to the facet results before returning them
DM iterates on algorithm + parameters until satisfied
DM demos the results to ACT during the exception request process

Query Request Extension

{
    "query": {
        "sql": "SELECT * FROM syn456",
        "additionalFilters": [...],
        "selectedFacets": [...]
    },
    "partMask": 16,
    "facetPostProcessing": {
        "algorithm": "ROUNDING",
        "parameters": {
            "roundTo": 5
        }
    }
}

Authorization: Any user with full access (DOWNLOAD permission) to the source table may use facetPostProcessing in preview mode. This is purely a client-side visualization aid — the raw data is already accessible to this user. The query response includes a flag facetPostProcessingApplied: true so the UI can indicate the results are post-processed.

4.7 ACT Binding Workflow

When ACT grants the aggregate exception, they bind the approved algorithm + parameters + threshold to the entity. This binding is what activates the AGGREGATE_DATA behavior for unapproved users.

User Story

DM prepares their PARTICIPANTS VirtualTable with appropriate defining SQL, facet types, and binning
DM previews post-processing results using facetPostProcessing in query requests
DM demos satisfactory results to ACT (e.g., in a meeting or via shared screenshots)
ACT approves and calls the extended changeEntityDataType endpoint to bind AGGREGATE_DATA with the agreed algorithm + parameters + threshold
From this point, unapproved users querying this table receive post-processed facets using the bound algorithm

Extended changeEntityDataType Endpoint

The existing endpoint PUT /entity/{id}/dataType currently takes only a query parameter ?type=. For AGGREGATE_DATA, we need to pass additional configuration. Two options:

Option A (Recommended): Add request body

PUT /entity/{id}/dataType
Content-Type: application/json

{
    "dataType": "AGGREGATE_DATA",
    "aggregateDataConfiguration": {
        "suppressionThreshold": 20,
        "facetPostProcessing": {
            "algorithm": "ROUNDING",
            "parameters": {
                "roundTo": 5
            }
        }
    }
}

Backwards compatibility: The existing ?type= query parameter continues to work for SENSITIVE_DATA and OPEN_DATA. When a request body is present, it takes precedence. When setting AGGREGATE_DATA, the request body is required (the query-param-only form returns 400 for this type).

Option B: Separate configuration endpoint

PUT /entity/{id}/dataType?type=AGGREGATE_DATA        (sets the type)
PUT /entity/{id}/aggregateDataConfiguration          (sets threshold + algorithm)
GET /entity/{id}/aggregateDataConfiguration          (reads current config)

Recommendation: Option A is simpler and makes the binding atomic — there's no window where the type is set but the algorithm isn't configured. The endpoint already returns DataTypeResponse; we extend this response to include the bound configuration when type is AGGREGATE_DATA.

New Schema: ChangeDataTypeRequest

{
    "description": "Request to change an entity's DataType classification.",
    "properties": {
        "dataType": {
            "$ref": "org.sagebionetworks.repo.model.DataType",
            "description": "The new DataType to assign."
        },
        "aggregateDataConfiguration": {
            "$ref": "org.sagebionetworks.repo.model.table.AggregateDataConfiguration",
            "description": "Required when dataType is AGGREGATE_DATA. Configuration for aggregate access including threshold and post-processing algorithm."
        }
    }
}

New Schema: AggregateDataConfiguration

{
    "description": "Configuration for the AGGREGATE_DATA access model. Bound by ACT when granting the aggregate exception.",
    "properties": {
        "suppressionThreshold": {
            "type": "integer",
            "description": "Minimum total count required for query results to be returned. Default: 20."
        },
        "facetPostProcessing": {
            "$ref": "org.sagebionetworks.repo.model.table.FacetPostProcessingConfig",
            "description": "The post-processing algorithm and parameters to apply to facet counts."
        }
    }
}

New Schema: FacetPostProcessingConfig

{
    "description": "Specifies a post-processing algorithm and its parameters for protecting facet counts.",
    "properties": {
        "algorithm": {
            "$ref": "org.sagebionetworks.repo.model.table.FacetPostProcessingAlgorithm",
            "description": "The algorithm to apply."
        },
        "parameters": {
            "type": "object",
            "description": "Algorithm-specific parameters. For ROUNDING: {roundTo: integer}. For NOISE: {epsilon: double}."
        }
    }
}

New Schema: FacetPostProcessingAlgorithm (Enum)

{
    "description": "Available algorithms for post-processing facet value counts.",
    "name": "FacetPostProcessingAlgorithm",
    "type": "string",
    "enum": [
        {
            "name": "ROUNDING",
            "description": "Round all facet counts to the nearest N (deterministic)."
        },
        {
            "name": "NOISE",
            "description": "Add Laplace-distributed noise to each count (non-deterministic, differential privacy-inspired)."
        }
    ]
}

4.8 Storage: Extended DATA_TYPE Table

Extend the existing DATA_TYPE table to store the bound configuration:

ALTER TABLE DATA_TYPE
ADD COLUMN SUPPRESSION_THRESHOLD BIGINT DEFAULT NULL,
ADD COLUMN POST_PROCESSING_ALGORITHM VARCHAR(50) DEFAULT NULL,
ADD COLUMN POST_PROCESSING_PARAMETERS JSON DEFAULT NULL;

Column	Type	Description

Column	Type	Description
`SUPPRESSION_THRESHOLD`	BIGINT	Minimum count for the hard gate. Only applies when DATA_TYPE = AGGREGATE_DATA.
`POST_PROCESSING_ALGORITHM`	VARCHAR(50)	Enum value: ROUNDING, NOISE, etc.
`POST_PROCESSING_PARAMETERS`	JSON	Algorithm-specific params serialized as JSON (e.g., `{"roundTo": 5}`)

These columns are NULL for SENSITIVE_DATA and OPEN_DATA. They are required (validated at the manager layer) when DATA_TYPE = AGGREGATE_DATA.

4.9 Modified Preflight Logic

// Pseudocode for enhanced validateTableReadAccess()
void validateTableReadAccess(UserInfo user, IndexDescription index) {
    String entityId = index.getIdAndVersion().getId().toString();
    
    // Check READ permission (ACL-based)
    authorizationManager.canAccess(user, entityId, ENTITY, READ).checkAuthorizationOrElseThrow();
    
    // Check DOWNLOAD permission (required for table content access)
    AuthorizationStatus downloadStatus = authorizationManager.canAccess(user, entityId, ENTITY, DOWNLOAD);
    
    if (!downloadStatus.isAuthorized()) {
        // User does NOT have full access. Check for aggregate exception.
        DataType dataType = dataTypeDao.getDataType(entityId);
        if (DataType.AGGREGATE_DATA.equals(dataType) && !user.isAnonymous()) {
            // Load the bound configuration
            AggregateDataConfiguration config = dataTypeDao.getAggregateConfig(entityId);
            // Mark this dependency as "aggregate-only" in the query context.
            queryContext.markAggregateOnly(entityId, config);
            return; // Allow query to proceed with constraints
        }
        // No exception applies — block access
        downloadStatus.checkAuthorizationOrElseThrow();
    }
    
    // Recurse into dependencies
    for (IndexDescription dependency : index.getDependencies()) {
        validateTableReadAccess(user, dependency);
    }
}

4.10 Enforcement During Query Execution

When a table is marked as "aggregate-only" in the query context, the following constraints are enforced:

Count gate: Execute SELECT COUNT(*) first. If count = 0, return a zero-count response indicating that no data exists for the selected cohort. If 0 < count < threshold, reject the entire query with an error. Do NOT return the count. Do NOT return facets.

Total count returned unmodified: If count = 0 or count >= threshold, include the raw total count in the response. A zero count is allowed because it only indicates that no data exists for the selected cohort; a non-zero count is returned only when it is above threshold.

Facets post-processed: Facet value counts are computed normally, then the bound algorithm is applied before inclusion in the response.
No row data: No individual rows or column values from the restricted source are included.
Sub-query usage: The table may appear as the source of an IN sub-query in a filter on another table (see Section 5). The sub-query count is validated against the threshold before the outer query executes.
Column restriction: No columns from the restricted source may appear in the outer query's SELECT clause.

4.11 Response Representation

For an unapproved user querying an AGGREGATE_DATA source:

{
    "queryCount": 847,
    "facets": [
        {
            "columnName": "sex",
            "facetType": "enumeration",
            "facetValues": [
                {"value": "Male", "count": 505, "isSelected": false},
                {"value": "Female", "count": 345, "isSelected": false}
            ]
        },
        {
            "columnName": "diagnosis",
            "facetType": "enumeration",
            "facetValues": [
                {"value": "AD", "count": 410, "isSelected": false},
                {"value": "MCI", "count": 435, "isSelected": false}
            ]
        }
    ],
    "queryResult": null,
    "facetPostProcessingApplied": true,
    "queryAudited": true
}

Note: queryResult (row data) is null because this is aggregate-only access. The facetPostProcessingApplied flag tells the UI that counts are approximate.

5. Sub-Query Cohort Handoff

5.1 Problem

In 1.0, the UI captures participant IDs from the PARTICIPANTS view and passes them as an IN clause to the FILES view. This has two problems:

Scalability: Thousands of IDs in a URL/request body
Privacy: Unapproved users must never see individual participant IDs

5.2 Solution: Sub-Query Filter

Instead of passing IDs, the UI passes the cohort definition as a sub-query. The backend resolves the IDs internally without exposing them to the client.

New Schema: ColumnSubQueryFilter

{
    "description": "A filter that restricts rows to those where a column's value appears in the results of a sub-query against another table. The backend executes the sub-query internally — the caller never sees the resulting IDs.",
    "implements": [
        {"$ref": "org.sagebionetworks.repo.model.table.QueryFilter"}
    ],
    "properties": {
        "concreteType": {
            "type": "string"
        },
        "columnName": {
            "type": "string",
            "description": "Column on the queried table to match against sub-query results (LHS of IN)."
        },
        "subQuery": {
            "$ref": "org.sagebionetworks.repo.model.table.SubQueryFilter",
            "description": "Defines the sub-query whose results form the RHS of the IN clause."
        }
    }
}

New Schema: SubQueryFilter

{
    "description": "Defines a constrained sub-query: SELECT single_column FROM table [WHERE conditions].",
    "properties": {
        "tableId": {
            "type": "string",
            "description": "The syn ID of the table/VirtualTable to sub-query."
        },
        "selectColumn": {
            "type": "string",
            "description": "The single column whose distinct values form the IN list."
        },
        "additionalFilters": {
            "type": "array",
            "items": {"$ref": "org.sagebionetworks.repo.model.table.QueryFilter"},
            "description": "Filters defining the cohort (same filter tree the user built)."
        }
    }
}

5.3 Example: Cohort to Files Handoff

User defined cohort "age_bin = 65+ AND sex = female" on PARTICIPANTS VT (syn456). Now querying FILES VT (syn789):

Object model (what the UI submits):

{
    "query": {
        "sql": "SELECT * FROM syn789",
        "additionalFilters": [
            {
                "concreteType": "...ColumnSubQueryFilter",
                "columnName": "participantId",
                "isDefiningCondition": true,
                "subQuery": {
                    "tableId": "syn456",
                    "selectColumn": "individualId",
                    "additionalFilters": [
                        {"concreteType": "...ColumnSingleValueQueryFilter",
                         "columnName": "age_bin", "operator": "EQUAL", "values": ["65+"]},
                        {"concreteType": "...ColumnSingleValueQueryFilter",
                         "columnName": "sex", "operator": "EQUAL", "values": ["female"]}
                    ]
                }
            }
        ]
    }
}

Equivalent SQL (for clients that write SQL directly):

SELECT * FROM syn789
WHERE participantId IN (SELECT individualId FROM syn456 WHERE age_bin = '65+' AND sex = 'female')

5.4 Backend Validation (Pre-flight)

When a ColumnSubQueryFilter (or an IN sub-query in SQL) is encountered during preflight:

Resolve sub-query source — load the VirtualTable referenced by tableId
Check user access to the sub-query source:
- User has full access (met AR)? → No threshold check needed. Execute normally.
- User lacks full access but source is AGGREGATE_DATA? → Proceed to count check.
- Neither? → UnauthorizedException
Count check: Execute SELECT COUNT(DISTINCT selectColumn) FROM tableId WHERE [filters]
- Count = 0? → Proceed with an empty result set; the UI may indicate that no data exists for the selected cohort.
- Count < threshold? → Reject with error: "Cohort size is below the minimum threshold. Adjust your filters to include more participants."
Column restriction: Verify the outer query's SELECT does not include any columns that originate from the restricted sub-query source.
Execute the full query with the sub-query embedded in the translated SQL.

5.5 Constrained Sub-Query Grammar (JavaCC Extension)

The SQL parser (table-query-parser.jj) will be extended to support sub-queries, but only in the constrained form:

subQuery := SELECT single_column FROM table_reference [WHERE search_condition]

Allowed:

Single column in SELECT (no expressions, no *)
Single FROM table reference (no JOINs, no UNION)
Optional WHERE with full boolean expression grammar

Disallowed (parser rejects at parse time):

Multiple SELECT columns
GROUP BY
ORDER BY
LIMIT / OFFSET
Nested sub-queries (sub-query within a sub-query)
Aggregate functions (COUNT, SUM, etc.) in SELECT
JOIN, UNION

The sub-query may appear only on the right-hand side of an IN predicate:

column_name IN ( subQuery )

5.6 Translation

In SQLTranslatorUtils, the sub-query is translated by:

Resolving tableId to its VirtualTable defining SQL
Wrapping the VirtualTable as a CTE (same mechanism as the outer query)
Applying row-level filters (benefactor) to the sub-query source
Applying the user's WHERE filters
Producing: column IN (WITH syn456 AS (...) SELECT individualId FROM syn456 WHERE ...)

6. Enhanced Query Filtering — Nested Filter Groups

6.1 Current State

The existing additionalFilters mechanism supports leaf predicates joined with AND only. The SQL parser supports full SQL-92 boolean expressions (nested AND/OR/NOT with parentheses), but the programmatic API cannot express this.

6.2 Proposed: Recursive Filter Tree (Mirrors SQL-92 Grammar)

A FilterGroup represents a parenthesized group of conditions. It maps directly to the parser's AST:

API Model	SQL-92 AST Node	SQL Output

API Model	SQL-92 AST Node	SQL Output
`FilterGroup(operator=OR, children=[...])`	`SearchCondition`	`(child1 OR child2 OR ...)`
`FilterGroup(operator=AND, children=[...])`	`BooleanTerm`	`(child1 AND child2 AND ...)`
`FilterGroup(not=true, ...)`	`BooleanFactor(not=true)`	`NOT (...)`
Leaf filter (e.g. `ColumnSingleValueQueryFilter`)	`Predicate`	`column op value`
Nested `FilterGroup` inside a parent	`BooleanPrimary(SearchCondition)`	`( nested_expression )`

Schema: FilterGroup

{
    "description": "A group of filter conditions combined with a boolean operator. Groups can be nested to form arbitrary boolean expression trees.",
    "implements": [{"$ref": "org.sagebionetworks.repo.model.table.QueryFilter"}],
    "properties": {
        "operator": {
            "name": "BooleanOperator",
            "type": "string",
            "enum": [
                {"name": "AND", "description": "All children must match."},
                {"name": "OR", "description": "At least one child must match."}
            ]
        },
        "not": {
            "type": "boolean",
            "description": "When true, negates this entire group. Default: false."
        },
        "children": {
            "type": "array",
            "items": {"$ref": "org.sagebionetworks.repo.model.table.QueryFilter"},
            "description": "Child filters — leaf predicates or nested FilterGroups."
        }
    }
}

6.3 Example: Complex Query

SQL: WHERE ((diagnosis LIKE '%Alzheimer%' AND age > 65) OR (diagnosis LIKE '%dementia%')) AND NOT (study = 'excluded') AND sex = 'female'

Maps to a FilterGroup tree: root AND group with three children — an OR group (containing an AND group and a leaf), a negated AND group, and a leaf.

6.4 Additional Operators on Leaf Filters

Extend ColumnSingleValueFilterOperator:

Operator	SQL	Status

Operator	SQL	Status
LIKE, EQUAL, IN	Existing