Linear categorization rules

This article describes how to create categorization rules for Linear allocation reporting. A categorization rule is an ordered set SQL queries that define categories for grouping your Linear issues. DX executes categorization rules nightly. For testing purposes, you can execute categorization rules immediately while editing.

When defining a category, you can choose between two types:

• Fixed category – Your query must return a linear_issue_id column, which represents the specific set of issues which fall under your category.
• Dynamic category – Your query must return a linear_issue_id and value column. The value column will determine the category each issue is assigned to.

Below are additional best practices for creating and editing categories rules:

• Keep categories unpublished while you’re working on them to avoid confusion.
• Test changes by clicking Process issues and then Preview to see the output.
• Order rules from most specific to broadest.
• Prefer sub-queries over hardcoded IDs.

Core database tables

Below are the main database tables you will use when defining SQL categories:

Table	Description
linear_issues	Table containing all Linear issues
linear_projects	List of all projects
linear_states	Workflow states (statuses)
linear_labels	Table containing all Linear labels
linear_issue_labels	List of labels associated with issues

Examples

Categorize by project

To categorize issues based on project, you can define categories that group issues using the linear_projects table.

You can define a single Dynamic category like the one below:

SELECT DISTINCT
    linear_issues.id AS linear_issue_id,
    linear_projects.name AS value
FROM linear_issues
JOIN linear_projects
    ON linear_projects.id = linear_issues.project_id

Or, you can define multiple Fixed categories, for example:

-- This SQL is for our "Infrastructure" category

SELECT DISTINCT linear_issues.id AS linear_issue_id
FROM linear_issues
JOIN linear_projects
    ON linear_projects.id = linear_issues.project_id
WHERE linear_projects.name IN ('Platform', 'DevOps', 'Infrastructure')

-- This SQL is for our "Product" category

SELECT DISTINCT linear_issues.id AS linear_issue_id
FROM linear_issues
JOIN linear_projects
    ON linear_projects.id = linear_issues.project_id
WHERE linear_projects.name IN ('Feature Development', 'Product')

Categorize by labels

To categorize issues based on labels, you can define categories that group issues using the linear_issue_labels table.

You can define a single Dynamic category like the one below:

SELECT DISTINCT
    linear_issues.id AS linear_issue_id,
    linear_labels.name AS value
FROM linear_issues
JOIN linear_issue_labels
    ON linear_issue_labels.issue_id = linear_issues.id
JOIN linear_labels
    ON linear_labels.id = linear_issue_labels.label_id
WHERE linear_labels.name IN ('capex', 'tech-debt', 'feature', 'bug-fix')

Or, you can define multiple Fixed categories, for example:

-- This SQL is for our "Tech debt" category

SELECT DISTINCT linear_issues.id AS linear_issue_id
FROM linear_issues
JOIN linear_issue_labels
    ON linear_issue_labels.issue_id = linear_issues.id
JOIN linear_labels
    ON linear_labels.id = linear_issue_labels.label_id
WHERE linear_labels.name IN ('tech-debt', 'maintenance', 'refactor')

-- This SQL is for our "Innovation" category

SELECT DISTINCT linear_issues.id AS linear_issue_id
FROM linear_issues
JOIN linear_issue_labels
    ON linear_issue_labels.issue_id = linear_issues.id
JOIN linear_labels
    ON linear_labels.id = linear_issue_labels.label_id
WHERE linear_labels.name IN ('feature', 'innovation', 'new-product')