nf-core Pipeline Deployment

Run nf-core bioinformatics pipelines on local or public sequencing data.

Target users: Bench scientists and researchers without specialized bioinformatics training who need to run large-scale omics analyses—differential expression, variant calling, or chromatin accessibility analysis.

Workflow Checklist

- [ ] Step 0: Acquire data (if from GEO/SRA)
- [ ] Step 1: Environment check (MUST pass)
- [ ] Step 2: Select pipeline (confirm with user)
- [ ] Step 3: Run test profile (MUST pass)
- [ ] Step 4: Create samplesheet
- [ ] Step 5: Configure & run (confirm genome with user)
- [ ] Step 6: Verify outputs

Step 0: Acquire Data (GEO/SRA Only)

Skip this step if user has local FASTQ files.

For public datasets, fetch from GEO/SRA first. See references/geo-sra-acquisition.md for the full workflow.

Quick start:

# 1. Get study info
python scripts/sra_geo_fetch.py info GSE110004

# 2. Download (interactive mode)
python scripts/sra_geo_fetch.py download GSE110004 -o ./fastq -i

# 3. Generate samplesheet
python scripts/sra_geo_fetch.py samplesheet GSE110004 --fastq-dir ./fastq -o samplesheet.csv

DECISION POINT: After fetching study info, confirm with user:

• Which sample subset to download (if multiple data types)
• Suggested genome and pipeline

Then continue to Step 1.

Step 1: Environment Check

Run first. Pipeline will fail without passing environment.

python scripts/check_environment.py

All critical checks must pass. If any fail, provide fix instructions:

Docker issues

Nextflow issues

Java issues

Do not proceed until all checks pass. For HPC/Singularity, see references/troubleshooting.md.

Step 2: Select Pipeline

DECISION POINT: Confirm with user before proceeding.

Auto-detect from data:

python scripts/detect_data_type.py /path/to/data

For pipeline-specific details:

• references/pipelines/rnaseq.md
• references/pipelines/sarek.md
• references/pipelines/atacseq.md

Step 3: Run Test Profile

Validates environment with small data. MUST pass before real data.

nextflow run nf-core/<pipeline> -r <version> -profile test,docker --outdir test_output

Verify:

ls test_output/multiqc/multiqc_report.html
grep "Pipeline completed successfully" .nextflow.log

If test fails, see references/troubleshooting.md.

Step 4: Create Samplesheet

Generate automatically

python scripts/generate_samplesheet.py /path/to/data <pipeline> -o samplesheet.csv

The script:

• Discovers FASTQ/BAM/CRAM files
• Pairs R1/R2 reads
• Infers sample metadata
• Validates before writing

For sarek: Script prompts for tumor/normal status if not auto-detected.

Validate existing samplesheet

python scripts/generate_samplesheet.py --validate samplesheet.csv <pipeline>

Samplesheet formats

rnaseq:

sample,fastq_1,fastq_2,strandedness
SAMPLE1,/abs/path/R1.fq.gz,/abs/path/R2.fq.gz,auto

sarek:

patient,sample,lane,fastq_1,fastq_2,status
patient1,tumor,L001,/abs/path/tumor_R1.fq.gz,/abs/path/tumor_R2.fq.gz,1
patient1,normal,L001,/abs/path/normal_R1.fq.gz,/abs/path/normal_R2.fq.gz,0

atacseq:

sample,fastq_1,fastq_2,replicate
CONTROL,/abs/path/ctrl_R1.fq.gz,/abs/path/ctrl_R2.fq.gz,1

Step 5: Configure & Run

5a. Check genome availability

python scripts/manage_genomes.py check <genome>
# If not installed:
python scripts/manage_genomes.py download <genome>

Common genomes: GRCh38 (human), GRCh37 (legacy), GRCm39 (mouse), R64-1-1 (yeast), BDGP6 (fly)

5b. Decision points

DECISION POINT: Confirm with user:

1. Genome: Which reference to use
2. Pipeline-specific options:
   • rnaseq: aligner (star_salmon recommended, hisat2 for low memory)
   • sarek: tools (haplotypecaller for germline, mutect2 for somatic)
   • atacseq: read_length (50, 75, 100, or 150)

5c. Run pipeline

nextflow run nf-core/<pipeline> \
    -r <version> \
    -profile docker \
    --input samplesheet.csv \
    --outdir results \
    --genome <genome> \
    -resume

Key flags:

• -r: Pin version
• -profile docker: Use Docker (or singularity for HPC)
• --genome: iGenomes key
• -resume: Continue from checkpoint

Resource limits (if needed):

--max_cpus 8 --max_memory '32.GB' --max_time '24.h'

Step 6: Verify Outputs

Check completion

ls results/multiqc/multiqc_report.html
grep "Pipeline completed successfully" .nextflow.log

Key outputs by pipeline

rnaseq:

• results/star_salmon/salmon.merged.gene_counts.tsv - Gene counts
• results/star_salmon/salmon.merged.gene_tpm.tsv - TPM values

sarek:

• results/variant_calling/*/ - VCF files
• results/preprocessing/recalibrated/ - BAM files

atacseq:

• results/macs2/narrowPeak/ - Peak calls
• results/bwa/mergedLibrary/bigwig/ - Coverage tracks

Quick Reference

For common exit codes and fixes, see references/troubleshooting.md.

Resume failed run

nextflow run nf-core/<pipeline> -resume

References

• references/geo-sra-acquisition.md - Downloading public GEO/SRA data
• references/troubleshooting.md - Common issues and fixes
• references/installation.md - Environment setup
• references/pipelines/rnaseq.md - RNA-seq pipeline details
• references/pipelines/sarek.md - Variant calling details
• references/pipelines/atacseq.md - ATAC-seq details

Disclaimer

This skill is provided as a prototype example demonstrating how to integrate nf-core bioinformatics pipelines into Claude Code for automated analysis workflows. The current implementation supports three pipelines (rnaseq, sarek, and atacseq), serving as a foundation that enables the community to expand support to the full set of nf-core pipelines.

It is intended for educational and research purposes and should not be considered production-ready without appropriate validation for your specific use case. Users are responsible for ensuring their computing environment meets pipeline requirements and for verifying analysis results.

Anthropic does not guarantee the accuracy of bioinformatics outputs, and users should follow standard practices for validating computational analyses. This integration is not officially endorsed by or affiliated with the nf-core community.

Attribution

When publishing results, cite the appropriate pipeline. Citations are available in each nf-core repository's CITATIONS.md file (e.g., https://github.com/nf-core/rnaseq/blob/3.22.2/CITATIONS.md).

Licenses

• nf-core pipelines: MIT License (https://nf-co.re/about)
• Nextflow: Apache License, Version 2.0 (https://www.nextflow.io/about-us.html)
• NCBI SRA Toolkit: Public Domain (https://github.com/ncbi/sra-tools/blob/master/LICENSE)

Single-Cell RNA-seq Quality Control

Automated QC workflow for single-cell RNA-seq data following scverse best practices.

When to Use This Skill

Use when users:

• Request quality control or QC on single-cell RNA-seq data
• Want to filter low-quality cells or assess data quality
• Need QC visualizations or metrics
• Ask to follow scverse/scanpy best practices
• Request MAD-based filtering or outlier detection

Supported input formats:

• .h5ad files (AnnData format from scanpy/Python workflows)
• .h5 files (10X Genomics Cell Ranger output)

Default recommendation: Use Approach 1 (complete pipeline) unless the user has specific custom requirements or explicitly requests non-standard filtering logic.

Approach 1: Complete QC Pipeline (Recommended for Standard Workflows)

For standard QC following scverse best practices, use the convenience script scripts/qc_analysis.py:

python3 scripts/qc_analysis.py input.h5ad
# or for 10X Genomics .h5 files:
python3 scripts/qc_analysis.py raw_feature_bc_matrix.h5

The script automatically detects the file format and loads it appropriately.

When to use this approach:

• Standard QC workflow with adjustable thresholds (all cells filtered the same way)
• Batch processing multiple datasets
• Quick exploratory analysis
• User wants the "just works" solution

Requirements: anndata, scanpy, scipy, matplotlib, seaborn, numpy

Parameters:

Customize filtering thresholds and gene patterns using command-line parameters:

• --output-dir - Output directory
• --mad-counts, --mad-genes, --mad-mt - MAD thresholds for counts/genes/MT%
• --mt-threshold - Hard mitochondrial % cutoff
• --min-cells - Gene filtering threshold
• --mt-pattern, --ribo-pattern, --hb-pattern - Gene name patterns for different species

Use --help to see current default values.

Outputs:

All files are saved to <input_basename>_qc_results/ directory by default (or to the directory specified by --output-dir):

• qc_metrics_before_filtering.png - Pre-filtering visualizations
• qc_filtering_thresholds.png - MAD-based threshold overlays
• qc_metrics_after_filtering.png - Post-filtering quality metrics
• <input_basename>_filtered.h5ad - Clean, filtered dataset ready for downstream analysis
• <input_basename>_with_qc.h5ad - Original data with QC annotations preserved

If copying outputs for user access, copy individual files (not the entire directory) so users can preview them directly.

Workflow Steps

The script performs the following steps:

1. Calculate QC metrics - Count depth, gene detection, mitochondrial/ribosomal/hemoglobin content
2. Apply MAD-based filtering - Permissive outlier detection using MAD thresholds for counts/genes/MT%
3. Filter genes - Remove genes detected in few cells
4. Generate visualizations - Comprehensive before/after plots with threshold overlays

Approach 2: Modular Building Blocks (For Custom Workflows)

For custom analysis workflows or non-standard requirements, use the modular utility functions from scripts/qc_core.py and scripts/qc_plotting.py:

# Run from scripts/ directory, or add scripts/ to sys.path if needed
import anndata as ad
from qc_core import calculate_qc_metrics, detect_outliers_mad, filter_cells
from qc_plotting import plot_qc_distributions  # Only if visualization needed

adata = ad.read_h5ad('input.h5ad')
calculate_qc_metrics(adata, inplace=True)
# ... custom analysis logic here

When to use this approach:

• Different workflow needed (skip steps, change order, apply different thresholds to subsets)
• Conditional logic (e.g., filter neurons differently than other cells)
• Partial execution (only metrics/visualization, no filtering)
• Integration with other analysis steps in a larger pipeline
• Custom filtering criteria beyond what command-line params support

Available utility functions:

From qc_core.py (core QC operations):

• calculate_qc_metrics(adata, mt_pattern, ribo_pattern, hb_pattern, inplace=True) - Calculate QC metrics and annotate adata
• detect_outliers_mad(adata, metric, n_mads, verbose=True) - MAD-based outlier detection, returns boolean mask
• apply_hard_threshold(adata, metric, threshold, operator='>', verbose=True) - Apply hard cutoffs, returns boolean mask
• filter_cells(adata, mask, inplace=False) - Apply boolean mask to filter cells
• filter_genes(adata, min_cells=20, min_counts=None, inplace=True) - Filter genes by detection
• print_qc_summary(adata, label='') - Print summary statistics

From qc_plotting.py (visualization):

• plot_qc_distributions(adata, output_path, title) - Generate comprehensive QC plots
• plot_filtering_thresholds(adata, outlier_masks, thresholds, output_path) - Visualize filtering thresholds
• plot_qc_after_filtering(adata, output_path) - Generate post-filtering plots

Example custom workflows:

Example 1: Only calculate metrics and visualize, don't filter yet

adata = ad.read_h5ad('input.h5ad')
calculate_qc_metrics(adata, inplace=True)
plot_qc_distributions(adata, 'qc_before.png', title='Initial QC')
print_qc_summary(adata, label='Before filtering')

Example 2: Apply only MT% filtering, keep other metrics permissive

adata = ad.read_h5ad('input.h5ad')
calculate_qc_metrics(adata, inplace=True)

# Only filter high MT% cells
high_mt = apply_hard_threshold(adata, 'pct_counts_mt', 10, operator='>')
adata_filtered = filter_cells(adata, ~high_mt)
adata_filtered.write('filtered.h5ad')

Example 3: Different thresholds for different subsets

adata = ad.read_h5ad('input.h5ad')
calculate_qc_metrics(adata, inplace=True)

# Apply type-specific QC (assumes cell_type metadata exists)
neurons = adata.obs['cell_type'] == 'neuron'
other_cells = ~neurons

# Neurons tolerate higher MT%, other cells use stricter threshold
neuron_qc = apply_hard_threshold(adata[neurons], 'pct_counts_mt', 15, operator='>')
other_qc = apply_hard_threshold(adata[other_cells], 'pct_counts_mt', 8, operator='>')

Best Practices

1. Be permissive with filtering - Default thresholds intentionally retain most cells to avoid losing rare populations
2. Inspect visualizations - Always review before/after plots to ensure filtering makes biological sense
3. Consider dataset-specific factors - Some tissues naturally have higher mitochondrial content (e.g., neurons, cardiomyocytes)
4. Check gene annotations - Mitochondrial gene prefixes vary by species (mt- for mouse, MT- for human)
5. Iterate if needed - QC parameters may need adjustment based on the specific experiment or tissue type

Reference Materials

For detailed QC methodology, parameter rationale, and troubleshooting guidance, see references/scverse_qc_guidelines.md. This reference provides:

• Detailed explanations of each QC metric and why it matters
• Rationale for MAD-based thresholds and why they're better than fixed cutoffs
• Guidelines for interpreting QC visualizations (histograms, violin plots, scatter plots)
• Species-specific considerations for gene annotations
• When and how to adjust filtering parameters
• Advanced QC considerations (ambient RNA correction, doublet detection)

Load this reference when users need deeper understanding of the methodology or when troubleshooting QC issues.

Next Steps After QC

Typical downstream analysis steps:

• Ambient RNA correction (SoupX, CellBender)
• Doublet detection (scDblFinder)
• Normalization (log-normalize, scran)
• Feature selection and dimensionality reduction
• Clustering and cell type annotation

Bio-Research Start

If you see unfamiliar placeholders or need to check which tools are connected, see CONNECTORS.md.

You are helping a biological researcher get oriented with the bio-research plugin. Walk through the following steps in order.

Step 1: Welcome

Display this welcome message:

Bio-Research Plugin

Your AI-powered research assistant for the life sciences. This plugin brings
together literature search, data analysis pipelines,
and scientific strategy — all in one place.

Step 2: Check Available MCP Servers

Test which MCP servers are connected by listing available tools. Group the results:

Literature & Data Sources:

• ~~literature database — biomedical literature search
• ~~literature database — preprint access (biology and medicine)
• ~~journal access — academic publications
• ~~data repository — collaborative research data (Sage Bionetworks)

Drug Discovery & Clinical:

• ~~chemical database — bioactive compound database
• ~~drug target database — drug target discovery platform
• ClinicalTrials.gov — clinical trial registry
• ~~clinical data platform — clinical trial site ranking and platform help

Visualization & AI:

• ~~scientific illustration — create scientific figures and diagrams
• ~~AI research platform — AI for biology (histopathology, drug discovery)

Report which servers are connected and which are not yet set up.

Step 3: Survey Available Skills

List the analysis skills available in this plugin:

Step 4: Optional Setup — Binary MCP Servers

Mention that two additional MCP servers are available as separate installations:

• ~~genomics platform — Access cloud analysis data and workflows Install: Download txg-node.mcpb from https://github.com/10XGenomics/txg-mcp/releases
• ~~tool database (Harvard MIMS) — AI tools for scientific discovery Install: Download tooluniverse.mcpb from https://github.com/mims-harvard/ToolUniverse/releases

These require downloading binary files and are optional.

Step 5: Ask How to Help

Ask the researcher what they're working on today. Suggest starting points based on common workflows:

1. Literature review — "Search ~~literature database for recent papers on [topic]"
2. Analyze sequencing data — "Run QC on my single-cell data" or "Set up an RNA-seq pipeline"
3. Drug discovery — "Search ~~chemical database for compounds targeting [protein]" or "Find drug targets for [disease]"
4. Data standardization — "Convert my instrument data to Allotrope format"
5. Research strategy — "Help me evaluate a new project idea"

Wait for the user's response and guide them to the appropriate tools and skills.

Cowork Plugin Customization

Customize a plugin for a specific organization — either by setting up a generic plugin template for the first time, or by tweaking and refining an already-configured plugin.

Finding the plugin: To find the plugin's source files, run find mnt/.local-plugins mnt/.plugins -type d -name "*<plugin-name>*" to locate the plugin directory, then read its files to understand its structure before making changes. If you cannot find the plugin directory, the user is likely running this conversation in a remote container. Abort and let them know: "Customizing plugins is currently only available in the desktop app's Cowork mode."

Determining the Customization Mode

After locating the plugin, check for ~~-prefixed placeholders: grep -rn '~~\w' /path/to/plugin --include='*.md' --include='*.json'

Default rule: If ~~ placeholders exist, default to Generic plugin setup unless the user explicitly asks to customize a specific part of the plugin.

1. Generic plugin setup — The plugin contains ~~-prefixed placeholders. These are customization points in a template that need to be replaced with real values (e.g., ~~Jira → Asana, ~~your-team-channel → #engineering).

2. Scoped customization — No ~~ placeholders exist, and the user asked to customize a specific part of the plugin (e.g., "customize the connectors", "update the standup skill", "change the ticket tool"). Read the plugin files to find the relevant section(s) and focus only on those. Do not scan the entire plugin or present unrelated customization items.

Legacy commands/ directories: Some plugins include a commands/ directory. The Cowork UI now presents these alongside skills as a single "Skills" concept, so treat commands/*.md files the same way you would skills/*/SKILL.md files when customizing.

3. General customization — No ~~ placeholders exist, and the user wants to modify the plugin broadly. Read the plugin's files to understand its current configuration, then ask the user what they'd like to change.

Important: Never change the name of the plugin or skill being customized. Do not rename directories, files, or the plugin/skill name fields.

Nontechnical output: All user-facing output (todo list items, questions, summaries) must be written in plain, nontechnical language. Never mention ~~ prefixes, placeholders, or customization points to the user. Frame everything in terms of the plugin's capabilities and the organization's tools.

Customization Workflow

Phase 0: Gather User Intent (scoped and general customization only)

For scoped customization and general customization (not generic plugin setup), check whether the user provided free-form context alongside their request (e.g., "customize the standup skill — we do async standups in #eng-updates every morning").

• If the user provided context: Record it and use it to pre-fill answers in Phase 3 — skip asking questions that the user already answered here.

• If the user did not provide context: Ask a single open-ended question using AskUserQuestion before proceeding. Tailor the question to what they asked to customize — e.g., "What changes do you have in mind for the brief skill?" or "What would you like to change about how this plugin works?" Keep it short and specific to their request.

  Use their response (if any) as additional context throughout the remaining phases.

Phase 1: Gather Context from Knowledge MCPs

Use company-internal knowledge MCPs to collect information relevant to the customization scope. See references/search-strategies.md for detailed query patterns by category.

What to gather (scope to what's relevant):

• Tool names and services the organization uses
• Organizational processes and workflows
• Team conventions (naming, statuses, estimation scales)
• Configuration values (workspace IDs, project names, team identifiers)

Sources to search:

1. Chat/Slack MCPs — tool mentions, integrations, workflow discussions
2. Document MCPs — onboarding docs, tool guides, setup instructions
3. Email MCPs — license notifications, admin emails, setup invitations

Record all findings for use in Phase 3.

Phase 2: Create Todo List

Build a todo list of changes to make, scoped appropriately:

• For scoped customization: Only include items related to the specific section the user asked about.
• For generic plugin setup: Run grep -rn '~~\w' /path/to/plugin --include='*.md' --include='*.json' to find all placeholder customization points. Group them by theme.
• For general customization: Read the plugin files, understand the current config, and based on the user's request, identify what needs to change.

Use user-friendly descriptions that focus on the plugin's purpose:

• Good: "Learn how standup prep works at Company"
• Bad: "Replace placeholders in skills/standup-prep/SKILL.md"

Phase 3: Complete Todo Items

Work through each item using context from Phase 0 and Phase 1.

If the user's free-form input (Phase 0) or knowledge MCPs (Phase 1) provided a clear answer: Apply directly without confirmation.

Otherwise: Use AskUserQuestion. Don't assume "industry standard" defaults are correct — if neither the user's input nor knowledge MCPs provided a specific answer, ask. Note: AskUserQuestion always includes a Skip button and a free-text input box for custom answers, so do not include None or Other as options.

Types of changes:

1. Placeholder replacements (generic setup): ~~Jira → Asana, ~~your-org-channel → #engineering
2. Content updates: Modifying instructions, skills, workflows, or references to match the organization
3. URL pattern updates: tickets.example.com/your-team/123 → app.asana.com/0/PROJECT_ID/TASK_ID
4. Configuration values: Workspace IDs, project names, team identifiers

If user doesn't know or skips, leave the value unchanged (or the ~~-prefixed placeholder, for generic setup).

Phase 4: Search for Useful MCPs

After customization items have been resolved, connect MCPs for any tools that were identified or changed. See references/mcp-servers.md for the full workflow, category-to-keywords mapping, and config file format.

For each tool identified during customization:

1. Search the registry: search_mcp_registry(keywords=[...]) using category keywords from references/mcp-servers.md, or search for the specific tool name if already known
2. If unconnected: suggest_connectors(directoryUuids=["chosen-uuid"]) — user completes auth
3. Update the plugin's MCP config file (check plugin.json for custom location, otherwise .mcp.json at root)

Collect all MCP results and present them together in the summary output (see below) — don't present MCPs one at a time during this phase.

Packaging the Plugin

After all customizations are applied, package the plugin as a .plugin file for the user:

1. Zip the plugin directory (excluding setup/ since it's no longer needed):

   cd /path/to/plugin && zip -r /tmp/plugin-name.plugin . -x "setup/*" && cp /tmp/plugin-name.plugin /path/to/outputs/plugin-name.plugin
   

2. Present the file to the user with the .plugin extension so they can install it directly. (Presenting the .plugin file will show to the user as a rich preview where they can look through the plugin files, and they can accept the customization by pressing a button.)

Important: Always create the zip in /tmp/ first, then copy to the outputs folder. Writing directly to the outputs folder may fail due to permissions and leave behind temporary files.

Naming: Use the original plugin directory name for the .plugin file (e.g., if the plugin directory is coder, the output file should be coder.plugin). Do not rename the plugin or its files during customization — only replace placeholder values and update content.

Summary Output

After customization, present the user with a summary of what was learned grouped by source. Always include the MCPs sections showing which MCPs were connected during setup and which ones the user should still connect:

## From searching Slack

- You use Asana for project management
- Sprint cycles are 2 weeks

## From searching documents

- Story points use T-shirt sizes

## From your answers

- Ticket statuses are: Backlog, In Progress, In Review, Done

Then present the MCPs that were connected during setup and any that the user should still connect, with instructions on how to connect them.

If no knowledge MCPs were available in Phase 1, and the user had to answer at least one question manually, include a note at the end:

By the way, connecting sources like Slack or Microsoft Teams would let me find answers automatically next time you customize a plugin.

Additional Resources

• references/mcp-servers.md — MCP discovery workflow, category-to-keywords mapping, config file locations
• references/search-strategies.md — Knowledge MCP query patterns for finding tool names and org values
• examples/customized-mcp.json — Example fully configured .mcp.json

Create Cowork Plugin

Build a new plugin from scratch through guided conversation. Walk the user through discovery, planning, design, implementation, and packaging — delivering a ready-to-install .plugin file at the end.

Overview

A plugin is a self-contained directory that extends Claude's capabilities with skills, agents, hooks, and MCP server integrations. This skill encodes the full plugin architecture and a five-phase workflow for creating one conversationally.

The process:

1. Discovery — understand what the user wants to build
2. Component Planning — determine which component types are needed
3. Design & Clarifying Questions — specify each component in detail
4. Implementation — create all plugin files
5. Review & Package — deliver the .plugin file

Nontechnical output: Keep all user-facing conversation in plain language. Do not expose implementation details like file paths, directory structures, or schema fields unless the user asks. Frame everything in terms of what the plugin will do.

Plugin Architecture

Directory Structure

Every plugin follows this layout:

plugin-name/
├── .claude-plugin/
│   └── plugin.json           # Required: plugin manifest
├── skills/                   # Skills (subdirectories with SKILL.md)
│   └── skill-name/
│       ├── SKILL.md
│       └── references/
├── agents/                   # Subagent definitions (.md files)
├── .mcp.json                 # MCP server definitions
└── README.md                 # Plugin documentation

Legacy commands/ format: Older plugins may include a commands/ directory with single-file .md slash commands. This format still works, but new plugins should use skills/*/SKILL.md instead — the Cowork UI presents both as a single "Skills" concept, and the skills format supports progressive disclosure via references/.

Rules:

• .claude-plugin/plugin.json is always required
• Component directories (skills/, agents/) go at the plugin root, not inside .claude-plugin/
• Only create directories for components the plugin actually uses
• Use kebab-case for all directory and file names

plugin.json Manifest

Located at .claude-plugin/plugin.json. Minimal required field is name.

{
  "name": "plugin-name",
  "version": "0.1.0",
  "description": "Brief explanation of plugin purpose",
  "author": {
    "name": "Author Name"
  }
}

Name rules: kebab-case, lowercase with hyphens, no spaces or special characters. Version: semver format (MAJOR.MINOR.PATCH). Start at 0.1.0.

Optional fields: homepage, repository, license, keywords.

Custom component paths can be specified (supplements, does not replace, auto-discovery):

{
  "commands": "./custom-commands",
  "agents": ["./agents", "./specialized-agents"],
  "hooks": "./config/hooks.json",
  "mcpServers": "./.mcp.json"
}

Component Schemas

Detailed schemas for each component type are in references/component-schemas.md. Summary:

This schema is shared with Claude Code's plugin system, but you're creating a plugin for Claude Cowork, a desktop app for doing knowledge work. Cowork users will usually find skills the most useful. Scaffold new plugins with skills/*/SKILL.md — do not create commands/ unless the user explicitly needs the legacy single-file format.

Customizable plugins with ~~ placeholders

Do not use or ask about this pattern by default. Only introduce ~~ placeholders if the user explicitly says they want people outside their organization to use the plugin. You can mention this is an option if it seems like the user wants to distribute the plugin externally, but do not proactively ask about this with AskUserQuestion.

When a plugin is intended to be shared with others outside their company, it might have parts that need to be adapted to individual users. You might need to reference external tools by category rather than specific product (e.g., "project tracker" instead of "Jira"). When sharing is needed, use generic language and mark these as requiring customization with two tilde characters such as create an issue in ~~project tracker. If used any tool categories, write a CONNECTORS.md file at the plugin root to explain:

# Connectors

## How tool references work

Plugin files use `~~category` as a placeholder for whatever tool the user
connects in that category. Plugins are tool-agnostic — they describe
workflows in terms of categories rather than specific products.

## Connectors for this plugin

| Category        | Placeholder         | Options                         |
| --------------- | ------------------- | ------------------------------- |
| Chat            | `~~chat`            | Slack, Microsoft Teams, Discord |
| Project tracker | `~~project tracker` | Linear, Asana, Jira             |

${CLAUDE_PLUGIN_ROOT} Variable

Use ${CLAUDE_PLUGIN_ROOT} for all intra-plugin path references in hooks and MCP configs. Never hardcode absolute paths.

Guided Workflow

When you ask the user something, use AskUserQuestion. Don't assume "industry standard" defaults are correct. Note: AskUserQuestion always includes a Skip button and a free-text input box for custom answers, so do not include None or Other as options.

Phase 1: Discovery

Goal: Understand what the user wants to build and why.

Ask (only what is unclear — skip questions if the user's initial request already answers them):

• What should this plugin do? What problem does it solve?
• Who will use it and in what context?
• Does it integrate with any external tools or services?
• Is there a similar plugin or workflow to reference?

Summarize understanding and confirm before proceeding.

Output: Clear statement of plugin purpose and scope.

Phase 2: Component Planning

Goal: Determine which component types the plugin needs.

Based on the discovery answers, determine:

• Skills — Does it need specialized knowledge that Claude should load on-demand, or user-initiated actions? (domain expertise, reference schemas, workflow guides, deploy/configure/analyze/review actions)
• MCP Servers — Does it need external service integration? (databases, APIs, SaaS tools)
• Agents (uncommon) — Are there autonomous multi-step tasks? (validation, generation, analysis)
• Hooks (rare) — Should something happen automatically on certain events? (enforce policies, load context, validate operations)

Present a component plan table, including component types you decided not to create:

| Component | Count | Purpose |
|-----------|-------|---------|
| Skills    | 3     | Domain knowledge for X, /do-thing, /check-thing |
| Agents    | 0     | Not needed |
| Hooks     | 1     | Validate writes |
| MCP       | 1     | Connect to service Y |

Get user confirmation or adjustments before proceeding.

Output: Confirmed list of components to create.

Phase 3: Design & Clarifying Questions

Goal: Specify each component in detail. Resolve all ambiguities before implementation.

For each component type in the plan, ask targeted design questions. Present questions grouped by component type. Wait for answers before proceeding.

Skills:

• What user queries should trigger this skill?
• What knowledge domains does it cover?
• Should it include reference files for detailed content?
• If the skill represents a user-initiated action: what arguments does it accept, and what tools does it need? (Read, Write, Bash, Grep, etc.)

Agents:

• Should each agent trigger proactively or only when requested?
• What tools does it need?
• What should the output format be?

Hooks:

• Which events? (PreToolUse, PostToolUse, Stop, SessionStart, etc.)
• What behavior — validate, block, modify, add context?
• Prompt-based (LLM-driven) or command-based (deterministic script)?

MCP Servers:

• What server type? (stdio for local, SSE for hosted with OAuth, HTTP for REST APIs)
• What authentication method?
• What tools should be exposed?

If the user says "whatever you think is best," provide specific recommendations and get explicit confirmation.

Output: Detailed specification for every component.

Phase 4: Implementation

Goal: Create all plugin files following best practices.

Order of operations:

1. Create the plugin directory structure
2. Create plugin.json manifest
3. Create each component (see references/component-schemas.md for exact formats)
4. Create README.md documenting the plugin

Implementation guidelines:

• Skills use progressive disclosure: lean SKILL.md body (under 3,000 words), detailed content in references/. Frontmatter description must be third-person with specific trigger phrases. Skill bodies are instructions FOR Claude, not messages to the user — write them as directives about what to do.
• Agents need a description with <example> blocks showing triggering conditions, plus a system prompt in the markdown body.
• Hooks config goes in hooks/hooks.json. Use ${CLAUDE_PLUGIN_ROOT} for script paths. Prefer prompt-based hooks for complex logic.
• MCP configs go in .mcp.json at plugin root. Use ${CLAUDE_PLUGIN_ROOT} for local server paths. Document required env vars in README.

Phase 5: Review & Package

Goal: Deliver the finished plugin.

1. Summarize what was created — list each component and its purpose

2. Ask if the user wants any adjustments

3. Run claude plugin validate <path-to-plugin-json> to check the plugin structure. If this command is unavailable (e.g., when running inside Cowork), verify the structure manually:

   • .claude-plugin/plugin.json exists and contains valid JSON with at least a name field
   • The name field is kebab-case (lowercase letters, numbers, and hyphens only)
   • Any component directories referenced by the plugin (commands/, skills/, agents/, hooks/) actually exist and contain files in the expected formats — .md for commands/skills/agents, .json for hooks
   • Each skill subdirectory contains a SKILL.md
   • Report what passed and what didn't, the same way the CLI validator would

   Fix any errors before proceeding.

4. Package as a .plugin file:

cd /path/to/plugin-dir && zip -r /tmp/plugin-name.plugin . -x "*.DS_Store" && cp /tmp/plugin-name.plugin /path/to/outputs/plugin-name.plugin

Important: Always create the zip in /tmp/ first, then copy to the outputs folder. Writing directly to the outputs folder may fail due to permissions.

Naming: Use the plugin name from plugin.json for the .plugin file (e.g., if name is code-reviewer, output code-reviewer.plugin).

The .plugin file will appear in the chat as a rich preview where the user can browse the files and accept the plugin by pressing a button.

Best Practices

• Start small: Begin with the minimum viable set of components. A plugin with one well-crafted skill is more useful than one with five half-baked components.
• Progressive disclosure for skills: Core knowledge in SKILL.md, detailed reference material in references/, working examples in examples/.
• Clear trigger phrases: Skill descriptions should include specific phrases users would say. Agent descriptions should include <example> blocks.
• Skills are for Claude: Write skill body content as instructions for Claude to follow, not documentation for the user to read.
• Imperative writing style: Use verb-first instructions in skills ("Parse the config file," not "You should parse the config file").
• Portability: Always use ${CLAUDE_PLUGIN_ROOT} for intra-plugin paths, never hardcoded paths.
• Security: Use environment variables for credentials, HTTPS for remote servers, least-privilege tool access.

Additional Resources

• references/component-schemas.md — Detailed format specifications for every component type (skills, agents, hooks, MCP, legacy commands, CONNECTORS.md)
• references/example-plugins.md — Three complete example plugin structures at different complexity levels

/customer-escalation

If you see unfamiliar placeholders or need to check which tools are connected, see CONNECTORS.md.

Package a support issue into a structured escalation brief for engineering, product, or leadership. Gathers context, structures reproduction steps, assesses business impact, and identifies the right escalation target.

Usage

/customer-escalation <issue description> [customer name or account]

Examples:

• /customer-escalation API returning 500 errors intermittently for Acme Corp
• /customer-escalation Data export is missing rows — 3 customers reported this week
• /customer-escalation SSO login loop affecting all Enterprise customers
• /customer-escalation Customer threatening to churn over missing audit log feature

Workflow

1. Understand the Issue

Parse the input and determine:

• What's broken or needed: The core technical or product issue
• Who's affected: Specific customer(s), segment, or all users
• How long: When did this start? How long has the customer been waiting?
• What's been tried: Any troubleshooting or workarounds attempted
• Why escalate now: What makes this need attention beyond normal support

Use the "When to Escalate vs. Handle in Support" criteria below to confirm this warrants escalation.

2. Gather Context

Pull together relevant information from available sources:

• ~~support platform: Related tickets, timeline of communications, previous troubleshooting
• ~~CRM (if connected): Account details, key contacts, previous escalations
• ~~chat: Internal discussions about this issue, similar reports from other customers
• ~~project tracker (if connected): Related bug reports or feature requests, engineering status
• ~~knowledge base: Known issues or workarounds, relevant documentation

3. Assess Business Impact

Using the impact dimensions below, quantify:

• Breadth: How many customers/users affected? Growing?
• Depth: Blocked vs. inconvenienced?
• Duration: How long has this been going on?
• Revenue: ARR at risk? Pending deals affected?
• Time pressure: Hard deadline?

4. Determine Escalation Target

Using the escalation tiers below, identify the right target: L2 Support, Engineering, Product, Security, or Leadership.

5. Structure Reproduction Steps (for bugs)

If the issue is a bug, follow the reproduction step best practices below to document clear repro steps with environment details and evidence.

6. Generate Escalation Brief

## ESCALATION: [One-line summary]

**Severity:** [Critical / High / Medium]
**Target team:** [Engineering / Product / Security / Leadership]
**Reported by:** [Your name/team]
**Date:** [Today's date]

### Impact
- **Customers affected:** [Who and how many]
- **Workflow impact:** [What they can't do]
- **Revenue at risk:** [If applicable]
- **Time in queue:** [How long this has been an issue]

### Issue Description
[Clear, concise description of the problem — 3-5 sentences]

### What's Been Tried
1. [Troubleshooting step and result]
2. [Troubleshooting step and result]
3. [Troubleshooting step and result]

### Reproduction Steps
[If applicable — follow the format below]
1. [Step]
2. [Step]
3. [Step]
Expected: [X]
Actual: [Y]
Environment: [Details]

### Customer Communication
- **Last update to customer:** [Date and what was communicated]
- **Customer expectation:** [What they're expecting and by when]
- **Escalation risk:** [Will they escalate further if not resolved by X?]

### What's Needed
- [Specific ask — "investigate root cause", "prioritize fix",
  "make product decision on X", "approve exception for Y"]
- **Deadline:** [When this needs resolution or an update]

### Supporting Context
- [Related tickets or links]
- [Internal discussion threads]
- [Documentation or logs]

7. Offer Next Steps

After generating the escalation:

• "Want me to post this in a ~~chat channel for the target team?"
• "Should I update the customer with an interim response?"
• "Want me to set a follow-up reminder to check on this?"
• "Should I draft a customer-facing update with the current status?"

When to Escalate vs. Handle in Support

Handle in Support When:

• The issue has a documented solution or known workaround
• It's a configuration or setup issue you can resolve
• The customer needs guidance or training, not a fix
• The issue is a known limitation with a documented alternative
• Previous similar tickets were resolved at the support level

Escalate When:

• Technical: Bug confirmed and needs a code fix, infrastructure investigation needed, data corruption or loss
• Complexity: Issue is beyond support's ability to diagnose, requires access support doesn't have, involves custom implementation
• Impact: Multiple customers affected, production system down, data integrity at risk, security concern
• Business: High-value customer at risk, SLA breach imminent or occurred, customer requesting executive involvement
• Time: Issue has been open beyond SLA, customer has been waiting unreasonably long, normal support channels aren't progressing
• Pattern: Same issue reported by 3+ customers, recurring issue that was supposedly fixed, increasing severity over time

Escalation Tiers

L1 → L2 (Support Escalation)

From: Frontline support To: Senior support / technical support specialists When: Issue requires deeper investigation, specialized product knowledge, or advanced troubleshooting What to include: Ticket summary, steps already tried, customer context

L2 → Engineering

From: Senior support To: Engineering team (relevant product area) When: Confirmed bug, infrastructure issue, needs code change, requires system-level investigation What to include: Full reproduction steps, environment details, logs or error messages, business impact, customer timeline

L2 → Product

From: Senior support To: Product management When: Feature gap causing customer pain, design decision needed, workflow doesn't match customer expectations, competing customer needs require prioritization What to include: Customer use case, business impact, frequency of request, competitive pressure (if known)

Any → Security

From: Any support tier To: Security team When: Potential data exposure, unauthorized access, vulnerability report, compliance concern What to include: What was observed, who/what is potentially affected, immediate containment steps taken, urgency assessment Note: Security escalations bypass normal tier progression — escalate immediately regardless of your level

Any → Leadership

From: Any tier (usually L2 or manager) To: Support leadership, executive team When: High-revenue customer threatening churn, SLA breach on critical account, cross-functional decision needed, exception to policy required, PR or legal risk What to include: Full business context, revenue at risk, what's been tried, specific decision or action needed, deadline

Business Impact Assessment

When escalating, quantify impact where possible:

Impact Dimensions

Severity Shorthand

• Critical: Production down, data at risk, security breach, or multiple high-value customers affected. Needs immediate attention.
• High: Major functionality broken, key customer blocked, SLA at risk. Needs same-day attention.
• Medium: Significant issue with workaround, important but not urgent business impact. Needs attention this week.

Writing Reproduction Steps

Good reproduction steps are the single most valuable thing in a bug escalation. Follow these practices:

1. Start from a clean state: Describe the starting point (account type, configuration, permissions)
2. Be specific: "Click the Export button in the top-right of the Dashboard page" not "try to export"
3. Include exact values: Use specific inputs, dates, IDs — not "enter some data"
4. Note the environment: Browser, OS, account type, feature flags, plan level
5. Capture the frequency: Always reproducible? Intermittent? Only under certain conditions?
6. Include evidence: Screenshots, error messages (exact text), network logs, console output
7. Note what you've ruled out: "Tested in Chrome and Firefox — same behavior" "Not account-specific — reproduced on test account"

Follow-up Cadence After Escalation

Don't escalate and forget. Maintain ownership of the customer relationship.

Follow-up Actions

• Check with the receiving team for progress
• Update the customer even if there's no new information ("We're still investigating — here's what we know so far")
• Adjust severity if the situation changes (better or worse)
• Document all updates in the ticket for audit trail
• Close the loop when resolved: confirm with customer, update internal tracking, capture learnings

De-escalation

Not every escalation stays escalated. De-escalate when:

• Root cause is found and it's a support-resolvable issue
• A workaround is found that unblocks the customer
• The issue resolves itself (but still document root cause)
• New information changes the severity assessment

When de-escalating:

• Notify the team you escalated to
• Update the ticket with the resolution
• Inform the customer of the resolution
• Document what was learned for future reference

Escalation Best Practices

1. Always quantify impact — vague escalations get deprioritized
2. Include reproduction steps for bugs — this is the #1 thing engineering needs
3. Be clear about what you need — "investigate" vs. "fix" vs. "decide" are different asks
4. Set and communicate a deadline — urgency without a deadline is ambiguous
5. Maintain ownership of the customer relationship even after escalating the technical issue
6. Follow up proactively — don't wait for the receiving team to come to you
7. Document everything — the escalation trail is valuable for pattern detection and process improvement

/customer-research

If you see unfamiliar placeholders or need to check which tools are connected, see CONNECTORS.md.

Multi-source research on a customer question, product topic, or account-related inquiry. Synthesizes findings from all available sources with clear attribution and confidence scoring.

Usage

/customer-research <question or topic>

Workflow

1. Parse the Research Request

Identify what type of research is needed:

• Customer question: Something a customer has asked that needs an answer (e.g., "Does our product support SSO with Okta?")
• Issue investigation: Background on a reported problem (e.g., "Has this bug been reported before? What's the known workaround?")
• Account context: History with a specific customer (e.g., "What did we tell Acme Corp last time they asked about this?")
• Topic research: General topic relevant to support work (e.g., "Best practices for webhook retry logic")

Before searching, clarify what you're actually trying to find:

• Is this a factual question with a definitive answer?
• Is this a contextual question requiring multiple perspectives?
• Is this an exploratory question where the scope is still being defined?
• Who is the audience for the answer (internal team, customer, leadership)?

2. Search Available Sources

Search systematically through the source tiers below, adapting to what is connected. Don't stop at the first result — cross-reference across sources.

Tier 1 — Official Internal Sources (highest confidence):

• ~~knowledge base (if connected): product docs, runbooks, FAQs, policy documents
• ~~cloud storage: internal documents, specs, guides, past research
• Product roadmap (internal-facing): feature timelines, priorities

Tier 2 — Organizational Context:

• ~~CRM notes: account notes, activity history, previous answers, opportunity details
• ~~support platform (if connected): previous resolutions, known issues, workarounds
• Meeting notes: previous discussions, decisions, commitments

Tier 3 — Team Communications:

• ~~chat: search for the topic in relevant channels; check if teammates have discussed or answered this before
• ~~email: search for previous correspondence on this topic
• Calendar notes: meeting agendas and post-meeting notes

Tier 4 — External Sources:

• Web search: official documentation, blog posts, community forums
• Public knowledge bases, help centers, release notes
• Third-party documentation: integration partners, complementary tools

Tier 5 — Inferred or Analogical (use when direct sources don't yield answers):

• Similar situations: how similar questions were handled before
• Analogous customers: what worked for comparable accounts
• General best practices: industry standards and norms

3. Synthesize Findings

Compile results into a structured research brief:

## Research: [Question/Topic]

### Answer
[Clear, direct answer to the question — lead with the bottom line]

**Confidence:** [High / Medium / Low]
[Explain what drives the confidence level]

### Key Findings

**From [Source 1]:**
- [Finding with specific detail]
- [Finding with specific detail]

**From [Source 2]:**
- [Finding with specific detail]

### Context & Nuance
[Any caveats, edge cases, or additional context that matters]

### Sources
1. [Source name/link] — [what it contributed]
2. [Source name/link] — [what it contributed]
3. [Source name/link] — [what it contributed]

### Gaps & Unknowns
- [What couldn't be confirmed]
- [What might need verification from a subject matter expert]

### Recommended Next Steps
- [Action if the answer needs to go to a customer]
- [Action if further research is needed]
- [Who to consult for verification if needed]

4. Handle Insufficient Sources

If no connected sources yield results:

• Perform web research on the topic
• Ask the user for internal context:
  • "I couldn't find this in connected sources. Do you have internal docs or knowledge base articles about this?"
  • "Has your team discussed this topic before? Any ~~chat channels I should check?"
  • "Is there a subject matter expert who would know the answer?"
• Be transparent about limitations:
  • "This answer is based on web research only — please verify against your internal documentation before sharing with the customer."
  • "I found a possible answer but couldn't confirm it from an authoritative internal source."

5. Customer-Facing Considerations

If the research is to answer a customer question:

• Flag if the answer involves product roadmap, pricing, legal, or security topics that may need review
• Note if the answer differs from what may have been communicated previously
• Suggest appropriate caveats for the customer-facing response
• Offer to draft the customer response: "Want me to draft a response to the customer based on these findings?"

6. Knowledge Capture

After research is complete, suggest capturing the knowledge:

• "Should I save these findings to your knowledge base for future reference?"
• "Want me to create a FAQ entry based on this research?"
• "This might be worth documenting — should I draft a runbook entry?"

This helps build institutional knowledge and reduces duplicate research effort across the team.

Source Prioritization and Confidence

Confidence by Source Tier

Confidence Levels

Always assign and communicate a confidence level:

High Confidence:

• Answer confirmed by official documentation or authoritative source
• Multiple sources corroborate the same answer
• Information is current (verified within a reasonable timeframe)
• "I'm confident this is accurate based on [source]."

Medium Confidence:

• Answer found in informal sources (chat, email) but not official docs
• Single source without corroboration
• Information may be slightly outdated but likely still valid
• "Based on [source], this appears to be the case, but I'd recommend confirming with [team/person]."

Low Confidence:

• Answer is inferred from related information
• Sources are outdated or potentially unreliable
• Contradictory information found across sources
• "I wasn't able to find a definitive answer. Based on [context], my best assessment is [answer], but this should be verified before sharing with the customer."

Unable to Determine:

• No relevant information found in any source
• Question requires specialized knowledge not available in sources
• "I couldn't find information about this. I recommend reaching out to [suggested expert/team] for a definitive answer."

Handling Contradictions

When sources disagree:

1. Note the contradiction explicitly
2. Identify which source is more authoritative or more recent
3. Present both perspectives with context
4. Recommend how to resolve the discrepancy
5. If going to a customer: use the most conservative/cautious answer until resolved

When to Escalate vs. Answer Directly

Answer Directly When:

• Official documentation clearly addresses the question
• Multiple reliable sources corroborate the answer
• The question is factual and non-sensitive
• The answer doesn't involve commitments, timelines, or pricing
• You've answered similar questions before with confirmed accuracy

Escalate or Verify When:

• The answer involves product roadmap commitments or timelines
• Pricing, legal terms, or contract-specific questions
• Security, compliance, or data handling questions
• The answer could set a precedent or create expectations
• You found contradictory information in sources
• The question involves a specific customer's custom configuration
• The answer requires specialized expertise you don't have
• The customer is at risk and the wrong answer could exacerbate the situation

Escalation Path:

1. Subject matter expert: For technical or domain-specific questions
2. Product team: For roadmap, feature, or capability questions
3. Legal/compliance: For terms, privacy, security, or regulatory questions
4. Billing/finance: For pricing, invoice, or payment-related questions
5. Engineering: For custom configurations, bugs, or technical root causes
6. Leadership: For strategic decisions, exceptions, or high-stakes situations

Research Documentation for Team Knowledge Base

After completing research, capture the knowledge for future use.

When to Document:

• Question has come up before or likely will again
• Research took significant effort to compile
• Answer required synthesizing multiple sources
• Answer corrects a common misunderstanding
• Answer involves nuance that's easy to get wrong

Documentation Format:

## [Question/Topic]

**Last Verified:** [date]
**Confidence:** [level]

### Answer
[Clear, direct answer]

### Details
[Supporting detail, context, and nuance]

### Sources
[Where this information came from]

### Related Questions
[Other questions this might help answer]

### Review Notes
[When to re-verify, what might change this answer]

Knowledge Base Hygiene:

• Date-stamp all entries
• Flag entries that reference specific product versions or features
• Review and update entries quarterly
• Archive entries that are no longer relevant
• Tag entries for searchability (by topic, product area, customer segment)

/draft-response

If you see unfamiliar placeholders or need to check which tools are connected, see CONNECTORS.md.

Draft a professional, customer-facing response tailored to the situation, customer relationship, and communication context.

Usage

/draft-response <context about the customer question, issue, or request>

Examples:

• /draft-response Acme Corp is asking when the new dashboard feature will ship
• /draft-response Customer escalation — their integration has been down for 2 days
• /draft-response Responding to a feature request we won't be building
• /draft-response Customer hit a billing error and wants a resolution ASAP

Workflow

1. Understand the Context

Parse the user's input to determine:

• Customer: Who is the communication for? Look up account context if available.
• Situation type: Question, issue, escalation, announcement, negotiation, bad news, good news, follow-up
• Urgency: Is this time-sensitive? How long has the customer been waiting?
• Channel: Email, support ticket, chat, or other (adjust formality accordingly)
• Relationship stage: New customer, established, frustrated/escalated
• Stakeholder level: End user, manager, executive, technical, business

2. Research Context

Gather relevant background from available sources:

~~email:

• Previous correspondence with this customer on this topic
• Any commitments or timelines previously shared
• Tone and style of the existing thread

~~chat:

• Internal discussions about this customer or topic
• Any guidance from product, engineering, or leadership
• Similar situations and how they were handled

~~CRM (if connected):

• Account details and plan level
• Contact information and key stakeholders
• Previous escalations or sensitive issues

~~support platform (if connected):

• Related tickets and their resolution
• Known issues or workarounds
• SLA status and response time commitments

~~knowledge base:

• Official documentation or help articles to reference
• Product roadmap information (if shareable)
• Policy or process documentation

3. Generate the Draft

Produce a response tailored to the situation:

## Draft Response

**To:** [Customer contact name]
**Re:** [Subject/topic]
**Channel:** [Email / Ticket / Chat]
**Tone:** [Empathetic / Professional / Technical / Celebratory / Candid]

---

[Draft response text]

---

### Notes for You (internal — do not send)
- **Why this approach:** [Rationale for tone and content choices]
- **Things to verify:** [Any facts or commitments to confirm before sending]
- **Risk factors:** [Anything sensitive about this response]
- **Follow-up needed:** [Actions to take after sending]
- **Escalation note:** [If this should be reviewed by someone else first]

4. Run Quality Checks

Before presenting the draft, verify:

• Tone matches the situation and relationship
• No commitments beyond what's authorized
• No product roadmap details that shouldn't be shared externally
• Accurate references to previous conversations
• Clear next steps and ownership
• Appropriate for the stakeholder level (not too technical for executives, not too vague for engineers)
• Length is appropriate for the channel (shorter for chat, fuller for email)

5. Offer Iterations

After presenting the draft:

• "Want me to adjust the tone? (more formal, more casual, more empathetic, more direct)"
• "Should I add or remove any specific points?"
• "Want me to make this shorter/longer?"
• "Should I draft a version for a different stakeholder?"
• "Want me to draft the internal escalation note as well?"
• "Should I prepare a follow-up message to send after [X days] if no response?"

Customer Communication Best Practices

Core Principles

1. Lead with empathy: Acknowledge the customer's situation before jumping to solutions
2. Be direct: Get to the point — customers are busy. Bottom-line-up-front.
3. Be honest: Never overpromise, never mislead, never hide bad news in jargon
4. Be specific: Use concrete details, timelines, and names — avoid vague language
5. Own it: Take responsibility when appropriate. "We" not "the system" or "the process"
6. Close the loop: Every response should have a clear next step or call to action
7. Match their energy: If they're frustrated, be empathetic first. If they're excited, be enthusiastic.

Response Structure

For most customer communications, follow this structure:

1. Acknowledgment / Context (1-2 sentences)
   - Acknowledge what they said, asked, or are experiencing
   - Show you understand their situation

2. Core Message (1-3 paragraphs)
   - Deliver the main information, answer, or update
   - Be specific and concrete
   - Include relevant details they need

3. Next Steps (1-3 bullets)
   - What YOU will do and by when
   - What THEY need to do (if anything)
   - When they'll hear from you next

4. Closing (1 sentence)
   - Warm but professional sign-off
   - Reinforce you're available if needed

Length Guidelines

• Chat/IM: 1-4 sentences. Get to the point immediately.
• Support ticket response: 1-3 short paragraphs. Structured and scannable.
• Email: 3-5 paragraphs max. Respect their inbox.
• Escalation response: As long as needed to be thorough, but well-structured with headers.
• Executive communication: Shorter is better. 2-3 paragraphs max. Data-driven.

Tone and Style Guidelines

Tone Spectrum

Tone Adjustments by Relationship Stage

New Customer (0-3 months):

• More formal and professional
• Extra context and explanation (don't assume knowledge)
• Proactively offer help and resources
• Build trust through reliability and responsiveness

Established Customer (3+ months):

• Warm and collaborative
• Can reference shared history and previous conversations
• More direct and efficient communication
• Show awareness of their goals and priorities

Frustrated or Escalated Customer:

• Extra empathy and acknowledgment
• Urgency in response times
• Concrete action plans with specific commitments
• Shorter feedback loops

Writing Style Rules

DO:

• Use active voice ("We'll investigate" not "This will be investigated")
• Use "I" for personal commitments and "we" for team commitments
• Name specific people when assigning actions ("Sarah from our engineering team will...")
• Use the customer's terminology, not your internal jargon
• Include specific dates and times, not relative terms ("by Friday January 24" not "in a few days")
• Break up long responses with headers or bullet points

DON'T:

• Use corporate jargon or buzzwords ("synergy", "leverage", "paradigm shift")
• Deflect blame to other teams, systems, or processes
• Use passive voice to avoid ownership ("Mistakes were made")
• Include unnecessary caveats or hedging that undermines confidence
• CC people unnecessarily — only include those who need to be in the conversation
• Use exclamation marks excessively (one per email max, if any)

Situation-Specific Approaches

Answering a product question:

• Lead with the direct answer
• Provide relevant documentation links
• Offer to connect them with the right resource if needed
• If you don't know the answer: say so honestly, commit to finding out, give a timeline

Responding to an issue or bug:

• Acknowledge the impact on their work
• State what you know about the issue and its status
• Provide workaround if available
• Set expectations for resolution timeline
• Commit to updates at regular intervals

Handling an escalation:

• Acknowledge the severity and their frustration
• Take ownership (no deflecting or excuse-making)
• Provide a clear action plan with timeline
• Identify the person accountable for resolution
• Offer a meeting or call if appropriate for the severity

Delivering bad news (feature sunset, delay, can't-fix):

• Be direct — don't bury the news
• Explain the reasoning honestly
• Acknowledge the impact on them specifically
• Offer alternatives or mitigation
• Provide a clear path forward

Sharing good news (feature launch, milestone, recognition):

• Lead with the positive outcome
• Connect it to their specific goals or use case
• Suggest next steps to capitalize on the good news
• Express genuine enthusiasm

Declining a request (feature request, discount, exception):

• Acknowledge the request and its reasoning
• Be honest about the decision
• Explain the why without being dismissive
• Offer alternatives when possible
• Leave the door open for future conversation

Response Templates for Common Scenarios

Acknowledging a Bug Report

Hi [Name],

Thank you for reporting this — I can see how [specific impact] would be
frustrating for your team.

I've confirmed the issue and escalated it to our engineering team as a
[priority level]. Here's what we know so far:
- [What's happening]
- [What's causing it, if known]
- [Workaround, if available]

I'll update you by [specific date/time] with a resolution timeline.
In the meantime, [workaround details if applicable].

Let me know if you have any questions or if this is impacting you in
other ways I should know about.

Best,
[Your name]

Acknowledging a Billing or Account Issue

Hi [Name],

Thank you for reaching out about this — I understand billing issues
need prompt attention, and I want to make sure this gets resolved
quickly.

I've looked into your account and here's what I'm seeing:
- [What happened — clear factual explanation]
- [Impact on their account — charges, access, etc.]

Here's what I'm doing to fix this:
- [Action 1 — with timeline]
- [Action 2 — if applicable]

[If resolution is immediate: "This has been corrected and you should
see the change reflected within [timeframe]."]
[If needs investigation: "I'm escalating this to our billing team
and will have an update for you by [specific date]."]

I'm sorry for the inconvenience. Let me know if you have any
questions about your account.

Best,
[Your name]

Responding to a Feature Request You Won't Build

Hi [Name],

Thank you for sharing this request — I can see why [capability] would
be valuable for [their use case].

I discussed this with our product team, and this isn't something we're
planning to build in the near term. The primary reason is [honest,
respectful explanation — e.g., it serves a narrow use case, it conflicts
with our architecture direction, etc.].

That said, I want to make sure you can accomplish your goal. Here are
some alternatives:
- [Alternative approach 1]
- [Alternative approach 2]
- [Integration or workaround if applicable]

I've also documented your request in our feedback system, and if our
direction changes, I'll let you know.

Would any of these alternatives work for your team? Happy to dig
deeper into any of them.

Best,
[Your name]

Outage or Incident Communication

Hi [Name],

I wanted to reach out directly to let you know about an issue affecting
[service/feature] that I know your team relies on.

**What happened:** [Clear, non-technical explanation]
**Impact:** [How it affects them specifically]
**Status:** [Current status — investigating / identified / fixing / resolved]
**ETA for resolution:** [Specific time if known, or "we'll update every X hours"]

[If applicable: "In the meantime, you can [workaround]."]

I'm personally tracking this and will update you as soon as we have a
resolution. You can also check [status page URL] for real-time updates.

I'm sorry for the disruption to your team's work. We take this seriously
and [what you're doing to prevent recurrence if known].

[Your name]

Following Up After Silence

Hi [Name],

I wanted to check in — I sent over [what you sent] on [date] and
wanted to make sure it didn't get lost in the shuffle.

[Brief reminder of what you need from them or what you're offering]

If now isn't a good time, no worries — just let me know when would be
better, and I'm happy to reconnect then.

Best,
[Your name]

Follow-up and Escalation Guidance

Follow-up Cadence

When to Escalate

Escalate to your manager when:

• Customer threatens to cancel or significantly downsell
• Customer requests exception to policy you can't authorize
• An issue has been unresolved for longer than SLA allows
• Customer requests direct contact with leadership
• You've made an error that needs senior involvement to resolve

Escalate to product/engineering when:

• Bug is critical and blocking the customer's business
• Feature gap is causing a competitive loss
• Customer has unique technical requirements beyond standard support
• Integration issues require engineering investigation

Escalation format:

ESCALATION: [Customer Name] — [One-line summary]

Urgency: [Critical / High / Medium]
Customer impact: [What's broken for them]
History: [Brief background — 2-3 sentences]
What I've tried: [Actions taken so far]
What I need: [Specific help or decision needed]
Deadline: [When this needs to be resolved by]

/kb-article

If you see unfamiliar placeholders or need to check which tools are connected, see CONNECTORS.md.

Draft a publish-ready knowledge base article from a resolved support issue, common question, or documented workaround. Structures the content for searchability and self-service.

Usage

/kb-article <resolved issue, ticket reference, or topic description>

Examples:

• /kb-article How to configure SSO with Okta — resolved this for 3 customers last month
• /kb-article Ticket #4521 — customer couldn't export data over 10k rows
• /kb-article Common question: how to set up webhook notifications
• /kb-article Known issue: dashboard charts not loading on Safari 16

Workflow

1. Understand the Source Material

Parse the input to identify:

• What was the problem? The original issue, question, or error
• What was the solution? The resolution, workaround, or answer
• Who does this affect? User type, plan level, or configuration
• How common is this? One-off or recurring issue
• What article type fits best? How-to, troubleshooting, FAQ, known issue, or reference (see article types below)

If a ticket reference is provided, look up the full context:

• ~~support platform: Pull the ticket thread, resolution, and any internal notes
• ~~knowledge base: Check if a similar article already exists (update vs. create new)
• ~~project tracker: Check if there's a related bug or feature request

2. Draft the Article

Using the article structure, formatting standards, and searchability best practices below:

• Follow the template for the chosen article type (how-to, troubleshooting, FAQ, known issue, or reference)
• Apply the searchability best practices: customer-language title, plain-language opening sentence, exact error messages, common synonyms
• Keep it scannable: headers, numbered steps, short paragraphs

3. Generate the Article

Present the draft with metadata:

## KB Article Draft

**Title:** [Article title]
**Type:** [How-to / Troubleshooting / FAQ / Known Issue / Reference]
**Category:** [Product area or topic]
**Tags:** [Searchable tags]
**Audience:** [All users / Admins / Developers / Specific plan]

---

[Full article content — using the appropriate template below]

---

### Publishing Notes
- **Source:** [Ticket #, customer conversation, or internal discussion]
- **Existing articles to update:** [If this overlaps with existing content]
- **Review needed from:** [SME or team if technical accuracy needs verification]
- **Suggested review date:** [When to revisit for accuracy]

4. Offer Next Steps

After generating the article:

• "Want me to check if a similar article already exists in your ~~knowledge base?"
• "Should I adjust the technical depth for a different audience?"
• "Want me to draft a companion article (e.g., a how-to to go with this troubleshooting guide)?"
• "Should I create an internal-only version with additional technical detail?"

Article Structure and Formatting Standards

Universal Article Elements

Every KB article should include:

1. Title: Clear, searchable, describes the outcome or problem (not internal jargon)
2. Overview: 1-2 sentences explaining what this article covers and who it's for
3. Body: Structured content appropriate to the article type
4. Related articles: Links to relevant companion content
5. Metadata: Category, tags, audience, last updated date

Formatting Rules

• Use headers (H2, H3) to break content into scannable sections
• Use numbered lists for sequential steps
• Use bullet lists for non-sequential items
• Use bold for UI element names, key terms, and emphasis
• Use code blocks for commands, API calls, error messages, and configuration values
• Use tables for comparisons, options, or reference data
• Use callouts/notes for warnings, tips, and important caveats
• Keep paragraphs short — 2-4 sentences max
• One idea per section — if a section covers two topics, split it

Writing for Searchability

Articles are useless if customers can't find them. Optimize every article for search:

Title Best Practices

Keyword Optimization

• Include exact error messages — customers copy-paste error text into search
• Use customer language, not internal terminology — "can't log in" not "authentication failure"
• Include common synonyms — "delete/remove", "dashboard/home page", "export/download"
• Add alternate phrasings — address the same issue from different angles in the overview
• Tag with product areas — make sure category and tags match how customers think about the product

Opening Sentence Formula

Start every article with a sentence that restates the problem or task in plain language:

• How-to: "This guide shows you how to [accomplish X]."
• Troubleshooting: "If you're seeing [symptom], this article explains how to fix it."
• FAQ: "[Question in the customer's words]? Here's the answer."
• Known issue: "Some users are experiencing [symptom]. Here's what we know and how to work around it."

Article Type Templates

How-to Articles

Purpose: Step-by-step instructions for accomplishing a task.

Structure:

# How to [accomplish task]

[Overview — what this guide covers and when you'd use it]

## Prerequisites
- [What's needed before starting]

## Steps
### 1. [Action]
[Instruction with specific details]

### 2. [Action]
[Instruction]

## Verify It Worked
[How to confirm success]

## Common Issues
- [Issue]: [Fix]

## Related Articles
- [Links]

Best practices:

• Start each step with a verb
• Include the specific path: "Go to Settings > Integrations > API Keys"
• Mention what the user should see after each step ("You should see a green confirmation banner")
• Test the steps yourself or verify with a recent ticket resolution

Troubleshooting Articles

Purpose: Diagnose and resolve a specific problem.

Structure:

# [Problem description — what the user sees]

## Symptoms
- [What the user observes]

## Cause
[Why this happens — brief, non-jargon explanation]

## Solution
### Option 1: [Primary fix]
[Steps]

### Option 2: [Alternative if Option 1 doesn't work]
[Steps]

## Prevention
[How to avoid this in the future]

## Still Having Issues?
[How to get help]

Best practices:

• Lead with symptoms, not causes — customers search for what they see
• Provide multiple solutions when possible (most likely fix first)
• Include a "Still having issues?" section that points to support
• If the root cause is complex, keep the customer-facing explanation simple

FAQ Articles

Purpose: Quick answer to a common question.

Structure:

# [Question — in the customer's words]

[Direct answer — 1-3 sentences]

## Details
[Additional context, nuance, or explanation if needed]

## Related Questions
- [Link to related FAQ]
- [Link to related FAQ]

Best practices:

• Answer the question in the first sentence
• Keep it concise — if the answer needs a walkthrough, it's a how-to, not an FAQ
• Group related FAQs and link between them

Known Issue Articles

Purpose: Document a known bug or limitation with a workaround.

Structure:

# [Known Issue]: [Brief description]

**Status:** [Investigating / Workaround Available / Fix In Progress / Resolved]
**Affected:** [Who/what is affected]
**Last updated:** [Date]

## Symptoms
[What users experience]

## Workaround
[Steps to work around the issue, or "No workaround available"]

## Fix Timeline
[Expected fix date or current status]

## Updates
- [Date]: [Update]

Best practices:

• Keep the status current — nothing erodes trust faster than a stale known issue article
• Update the article when the fix ships and mark as resolved
• If resolved, keep the article live for 30 days for customers still searching the old symptoms

Review and Maintenance Cadence

Knowledge bases decay without maintenance. Follow this schedule:

Article Lifecycle

1. Draft: Written, needs review
2. Published: Live and available to customers
3. Needs update: Flagged for revision (product change, feedback, or age)
4. Archived: No longer relevant but preserved for reference
5. Retired: Removed from the knowledge base

When to Update vs. Create New

Update existing when:

• The product changed and steps need refreshing
• The article is mostly right but missing a detail
• Feedback indicates customers are confused by a specific section
• A better workaround or solution was found

Create new when:

• A new feature or product area needs documentation
• A resolved ticket reveals a gap — no article exists for this topic
• The existing article covers too many topics and should be split
• A different audience needs the same information explained differently

Linking and Categorization Taxonomy

Category Structure

Organize articles into a hierarchy that matches how customers think:

Getting Started
├── Account setup
├── First-time configuration
└── Quick start guides

Features & How-tos
├── [Feature area 1]
├── [Feature area 2]
└── [Feature area 3]

Integrations
├── [Integration 1]
├── [Integration 2]
└── API reference

Troubleshooting
├── Common errors
├── Performance issues
└── Known issues

Billing & Account
├── Plans and pricing
├── Billing questions
└── Account management

Linking Best Practices

• Link from troubleshooting to how-to: "For setup instructions, see [How to configure X]"
• Link from how-to to troubleshooting: "If you encounter errors, see [Troubleshooting X]"
• Link from FAQ to detailed articles: "For a full walkthrough, see [Guide to X]"
• Link from known issues to workarounds: Keep the chain from problem to solution short
• Use relative links within the KB — they survive restructuring better than absolute URLs
• Avoid circular links — if A links to B, B shouldn't link back to A unless both are genuinely useful entry points

KB Writing Best Practices

1. Write for the customer who is frustrated and searching for an answer — be clear, direct, and helpful
2. Every article should be findable through search using the words a customer would type
3. Test your articles — follow the steps yourself or have someone unfamiliar with the topic follow them
4. Keep articles focused — one problem, one solution. Split if an article is growing too long
5. Maintain aggressively — a wrong article is worse than no article
6. Track what's missing — every ticket that could have been a KB article is a content gap
7. Measure impact — articles that don't get traffic or don't reduce tickets need to be improved or retired

/ticket-triage

If you see unfamiliar placeholders or need to check which tools are connected, see CONNECTORS.md.

Categorize, prioritize, and route an incoming support ticket or customer issue. Produces a structured triage assessment with a suggested initial response.

Usage

/ticket-triage <ticket text, customer message, or issue description>

Examples:

• /ticket-triage Customer says their dashboard has been showing a blank page since this morning
• /ticket-triage "I was charged twice for my subscription this month"
• /ticket-triage User can't connect their SSO — getting a 403 error on the callback URL
• /ticket-triage Feature request: they want to export reports as PDF

Workflow

1. Parse the Issue

Read the input and extract:

• Core problem: What is the customer actually experiencing?
• Symptoms: What specific behavior or error are they seeing?
• Customer context: Who is this? Any account details, plan level, or history available?
• Urgency signals: Are they blocked? Is this production? How many users affected?
• Emotional state: Frustrated, confused, matter-of-fact, escalating?

2. Categorize and Prioritize

Using the category taxonomy and priority framework below:

• Assign a primary category (bug, how-to, feature request, billing, account, integration, security, data, performance) and an optional secondary category
• Assign a priority (P1–P4) based on impact and urgency
• Identify the product area the issue maps to

3. Check for Duplicates and Known Issues

Before routing, check available sources:

• ~~support platform: Search for similar open or recently resolved tickets
• ~~knowledge base: Check for known issues or existing documentation
• ~~project tracker: Check if there's an existing bug report or feature request

Apply the duplicate detection process below.

4. Determine Routing

Using the routing rules below, recommend which team or queue should handle this based on category and complexity.

5. Generate Triage Output

## Triage: [One-line issue summary]

**Category:** [Primary] / [Secondary if applicable]
**Priority:** [P1-P4] — [Brief justification]
**Product area:** [Area/team]

### Issue Summary
[2-3 sentence summary of what the customer is experiencing]

### Key Details
- **Customer:** [Name/account if known]
- **Impact:** [Who and what is affected]
- **Workaround:** [Available / Not available / Unknown]
- **Related tickets:** [Links to similar issues if found]
- **Known issue:** [Yes — link / No / Checking]

### Routing Recommendation
**Route to:** [Team or queue]
**Why:** [Brief reasoning]

### Suggested Initial Response
[Draft first response to the customer — acknowledge the issue,
set expectations, provide workaround if available.
Use the auto-response templates below as a starting point.]

### Internal Notes
- [Any additional context for the agent picking this up]
- [Reproduction hints if it's a bug]
- [Escalation triggers to watch for]

6. Offer Next Steps

After presenting the triage:

• "Want me to draft a full response to the customer?"
• "Should I search for more context on this issue?"
• "Want me to check if this is a known bug in the tracker?"
• "Should I escalate this? I can package it with /customer-escalation."

Category Taxonomy

Assign every ticket a primary category and optionally a secondary category:

Category Determination Tips

• If the customer reports both a bug and a feature request, the bug is primary
• If they can't log in due to a bug, category is Bug (not Account) — root cause drives the category
• "It used to work and now it doesn't" = Bug
• "I want it to work differently" = Feature request
• "How do I make it work?" = How-to
• When in doubt, lean toward Bug — it's better to investigate than dismiss

Priority Framework

P1 — Critical

Criteria: Production system down, data loss or corruption, security breach, all or most users affected.

• The customer cannot use the product at all
• Data is being lost, corrupted, or exposed
• A security incident is in progress
• The issue is worsening or expanding in scope

SLA expectation: Respond within 1 hour. Continuous work until resolved or mitigated. Updates every 1-2 hours.

P2 — High

Criteria: Major feature broken, significant workflow blocked, many users affected, no workaround.

• A core workflow is broken but the product is partially usable
• Multiple users are affected or a key account is impacted
• The issue is blocking time-sensitive work
• No reasonable workaround exists

SLA expectation: Respond within 4 hours. Active investigation same day. Updates every 4 hours.

P3 — Medium

Criteria: Feature partially broken, workaround available, single user or small team affected.

• A feature isn't working correctly but a workaround exists
• The issue is inconvenient but not blocking critical work
• A single user or small team is affected
• The customer is not escalating urgently

SLA expectation: Respond within 1 business day. Resolution or update within 3 business days.

P4 — Low

Criteria: Minor inconvenience, cosmetic issue, general question, feature request.

• Cosmetic or UI issues that don't affect functionality
• Feature requests and enhancement ideas
• General questions or how-to inquiries
• Issues with simple, documented solutions

SLA expectation: Respond within 2 business days. Resolution at normal pace.

Priority Escalation Triggers

Automatically bump priority up when:

• Customer has been waiting longer than the SLA allows
• Multiple customers report the same issue (pattern detected)
• The customer explicitly escalates or mentions executive involvement
• A workaround that was in place stops working
• The issue expands in scope (more users, more data, new symptoms)

Routing Rules

Route tickets based on category and complexity:

Duplicate Detection

Before creating a new ticket or routing, check for duplicates:

1. Search by symptom: Look for tickets with similar error messages or descriptions
2. Search by customer: Check if this customer has an open ticket for the same issue
3. Search by product area: Look for recent tickets in the same feature area
4. Check known issues: Compare against documented known issues

If a duplicate is found:

• Link the new ticket to the existing one
• Notify the customer that this is a known issue being tracked
• Add any new information from the new report to the existing ticket
• Bump priority if the new report adds urgency (more customers affected, etc.)

Auto-Response Templates by Category

Bug — Initial Response

Thank you for reporting this. I can see how [specific impact]
would be disruptive for your work.

I've logged this as a [priority] issue and our team is
investigating. [If workaround exists: "In the meantime, you
can [workaround]."]

I'll update you within [SLA timeframe] with what we find.

How-to — Initial Response

Great question! [Direct answer or link to documentation]

[If more complex: "Let me walk you through the steps:"]
[Steps or guidance]

Let me know if that helps, or if you have any follow-up
questions.

Feature Request — Initial Response

Thank you for this suggestion — I can see why [capability]
would be valuable for your workflow.

I've documented this and shared it with our product team.
While I can't commit to a specific timeline, your feedback
directly informs our roadmap priorities.

[If alternative exists: "In the meantime, you might find
[alternative] helpful for achieving something similar."]

Billing — Initial Response

I understand billing issues need prompt attention. Let me
look into this for you.

[If straightforward: resolution details]
[If complex: "I'm reviewing your account now and will have
an answer for you within [timeframe]."]

Security — Initial Response

Thank you for flagging this — we take security concerns
seriously and are reviewing this immediately.

I've escalated this to our security team for investigation.
We'll follow up with you within [timeframe] with our findings.

[If action is needed: "In the meantime, we recommend
[protective action]."]

Triage Best Practices

1. Read the full ticket before categorizing — context in later messages often changes the assessment
2. Categorize by root cause, not just the symptom described
3. When in doubt on priority, err on the side of higher — it's easier to de-escalate than to recover from a missed SLA
4. Always check for duplicates and known issues before routing
5. Write internal notes that help the next person pick up context quickly
6. Include what you've already checked or ruled out to avoid duplicate investigation
7. Flag patterns — if you're seeing the same issue repeatedly, escalate the pattern even if individual tickets are low priority

/analyze - Answer Data Questions

If you see unfamiliar placeholders or need to check which tools are connected, see CONNECTORS.md.

Answer a data question, from a quick lookup to a full analysis to a formal report.

Usage

/analyze <natural language question>

Workflow

1. Understand the Question

Parse the user's question and determine:

• Complexity level:
  • Quick answer: Single metric, simple filter, factual lookup (e.g., "How many users signed up last week?")
  • Full analysis: Multi-dimensional exploration, trend analysis, comparison (e.g., "What's driving the drop in conversion rate?")
  • Formal report: Comprehensive investigation with methodology, caveats, and recommendations (e.g., "Prepare a quarterly business review of our subscription metrics")
• Data requirements: Which tables, metrics, dimensions, and time ranges are needed
• Output format: Number, table, chart, narrative, or combination

2. Gather Data

If a data warehouse MCP server is connected:

1. Explore the schema to find relevant tables and columns
2. Write SQL query(ies) to extract the needed data
3. Execute the query and retrieve results
4. If the query fails, debug and retry (check column names, table references, syntax for the specific dialect)
5. If results look unexpected, run sanity checks before proceeding

If no data warehouse is connected:

1. Ask the user to provide data in one of these ways:
   • Paste query results directly
   • Upload a CSV or Excel file
   • Describe the schema so you can write queries for them to run
2. If writing queries for manual execution, use the sql-queries skill for dialect-specific best practices
3. Once data is provided, proceed with analysis

3. Analyze

• Calculate relevant metrics, aggregations, and comparisons
• Identify patterns, trends, outliers, and anomalies
• Compare across dimensions (time periods, segments, categories)
• For complex analyses, break the problem into sub-questions and address each

4. Validate Before Presenting

Before sharing results, run through validation checks:

• Row count sanity: Does the number of records make sense?
• Null check: Are there unexpected nulls that could skew results?
• Magnitude check: Are the numbers in a reasonable range?
• Trend continuity: Do time series have unexpected gaps?
• Aggregation logic: Do subtotals sum to totals correctly?

If any check raises concerns, investigate and note caveats.

5. Present Findings

For quick answers:

• State the answer directly with relevant context
• Include the query used (collapsed or in a code block) for reproducibility

For full analyses:

• Lead with the key finding or insight
• Support with data tables and/or visualizations
• Note methodology and any caveats
• Suggest follow-up questions

For formal reports:

• Executive summary with key takeaways
• Methodology section explaining approach and data sources
• Detailed findings with supporting evidence
• Caveats, limitations, and data quality notes
• Recommendations and suggested next steps

6. Visualize Where Helpful

When a chart would communicate results more effectively than a table:

• Use the data-visualization skill to select the right chart type
• Generate a Python visualization or build it into an HTML dashboard
• Follow visualization best practices for clarity and accuracy

Examples

Quick answer:

/analyze How many new users signed up in December?

Full analysis:

/analyze What's causing the increase in support ticket volume over the past 3 months? Break down by category and priority.

Formal report:

/analyze Prepare a data quality assessment of our customer table -- completeness, consistency, and any issues we should address.

Tips

• Be specific about time ranges, segments, or metrics when possible
• If you know the table names, mention them to speed up the process
• For complex questions, Claude may break them into multiple queries
• Results are always validated before presentation -- if something looks off, Claude will flag it

/build-dashboard - Build Interactive Dashboards

If you see unfamiliar placeholders or need to check which tools are connected, see CONNECTORS.md.

Build a self-contained interactive HTML dashboard with charts, filters, tables, and professional styling. Opens directly in a browser -- no server or dependencies required.

Usage

/build-dashboard <description of dashboard> [data source]

Workflow

1. Understand the Dashboard Requirements

Determine:

• Purpose: Executive overview, operational monitoring, deep-dive analysis, team reporting
• Audience: Who will use this dashboard?
• Key metrics: What numbers matter most?
• Dimensions: What should users be able to filter or slice by?
• Data source: Live query, pasted data, CSV file, or sample data

2. Gather the Data

If data warehouse is connected:

1. Query the necessary data
2. Embed the results as JSON within the HTML file

If data is pasted or uploaded:

1. Parse and clean the data
2. Embed as JSON in the dashboard

If working from a description without data:

1. Create a realistic sample dataset matching the described schema
2. Note in the dashboard that it uses sample data
3. Provide instructions for swapping in real data

3. Design the Dashboard Layout

Follow a standard dashboard layout pattern:

┌──────────────────────────────────────────────────┐
│  Dashboard Title                    [Filters ▼]  │
├────────────┬────────────┬────────────┬───────────┤
│  KPI Card  │  KPI Card  │  KPI Card  │ KPI Card  │
├────────────┴────────────┼────────────┴───────────┤
│                         │                        │
│    Primary Chart        │   Secondary Chart      │
│    (largest area)       │                        │
│                         │                        │
├─────────────────────────┴────────────────────────┤
│                                                  │
│    Detail Table (sortable, scrollable)           │
│                                                  │
└──────────────────────────────────────────────────┘

Adapt the layout to the content:

• 2-4 KPI cards at the top for headline numbers
• 1-3 charts in the middle section for trends and breakdowns
• Optional detail table at the bottom for drill-down data
• Filters in the header or sidebar depending on complexity

4. Build the HTML Dashboard

Generate a single self-contained HTML file using the base template below. The file includes:

Structure (HTML):

• Semantic HTML5 layout
• Responsive grid using CSS Grid or Flexbox
• Filter controls (dropdowns, date pickers, toggles)
• KPI cards with values and labels
• Chart containers
• Data table with sortable headers

Styling (CSS):

• Professional color scheme (clean whites, grays, with accent colors for data)
• Card-based layout with subtle shadows
• Consistent typography (system fonts for fast loading)
• Responsive design that works on different screen sizes
• Print-friendly styles

Interactivity (JavaScript):

• Chart.js for interactive charts (included via CDN)
• Filter dropdowns that update all charts and tables simultaneously
• Sortable table columns
• Hover tooltips on charts
• Number formatting (commas, currency, percentages)

Data (embedded JSON):

• All data embedded directly in the HTML as JavaScript variables
• No external data fetches required
• Dashboard works completely offline

5. Implement Chart Types

Use Chart.js for all charts. Common dashboard chart patterns:

• Line chart: Time series trends
• Bar chart: Category comparisons
• Doughnut chart: Composition (when <6 categories)
• Stacked bar: Composition over time
• Mixed (bar + line): Volume with rate overlay

Use the Chart.js integration patterns below for each chart type.

6. Add Interactivity

Use the filter and interactivity implementation patterns below for dropdown filters, date range filters, combined filter logic, sortable tables, and chart updates.

7. Save and Open

1. Save the dashboard as an HTML file with a descriptive name (e.g., sales_dashboard.html)
2. Open it in the user's default browser
3. Confirm it renders correctly
4. Provide instructions for updating data or customizing

Base Template

Every dashboard follows this structure:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Dashboard Title</title>
    <script src="https://cdn.jsdelivr.net/npm/chart.js@4.5.1" integrity="sha384-jb8JQMbMoBUzgWatfe6COACi2ljcDdZQ2OxczGA3bGNeWe+6DChMTBJemed7ZnvJ" crossorigin="anonymous"></script>
    <script src="https://cdn.jsdelivr.net/npm/chartjs-adapter-date-fns@3.0.0" integrity="sha384-cVMg8E3QFwTvGCDuK+ET4PD341jF3W8nO1auiXfuZNQkzbUUiBGLsIQUE+b1mxws" crossorigin="anonymous"></script>
    <style>
        /* Dashboard styles go here */
    </style>
</head>
<body>
    <div class="dashboard-container">
        <header class="dashboard-header">
            <h1>Dashboard Title</h1>
            <div class="filters">
                <!-- Filter controls -->
            </div>
        </header>

        <section class="kpi-row">
            <!-- KPI cards -->
        </section>

        <section class="chart-row">
            <!-- Chart containers -->
        </section>

        <section class="table-section">
            <!-- Data table -->
        </section>

        <footer class="dashboard-footer">
            <span>Data as of: <span id="data-date"></span></span>
        </footer>
    </div>

    <script>
        // Embedded data
        const DATA = [];

        // Dashboard logic
        class Dashboard {
            constructor(data) {
                this.rawData = data;
                this.filteredData = data;
                this.charts = {};
                this.init();
            }

            init() {
                this.setupFilters();
                this.renderKPIs();
                this.renderCharts();
                this.renderTable();
            }

            applyFilters() {
                // Filter logic
                this.filteredData = this.rawData.filter(row => {
                    // Apply each active filter
                    return true; // placeholder
                });
                this.renderKPIs();
                this.updateCharts();
                this.renderTable();
            }

            // ... methods for each section
        }

        const dashboard = new Dashboard(DATA);
    </script>
</body>
</html>

KPI Card Pattern

<div class="kpi-card">
    <div class="kpi-label">Total Revenue</div>
    <div class="kpi-value" id="kpi-revenue">$0</div>
    <div class="kpi-change positive" id="kpi-revenue-change">+0%</div>
</div>

function renderKPI(elementId, value, previousValue, format = 'number') {
    const el = document.getElementById(elementId);
    const changeEl = document.getElementById(elementId + '-change');

    // Format the value
    el.textContent = formatValue(value, format);

    // Calculate and display change
    if (previousValue && previousValue !== 0) {
        const pctChange = ((value - previousValue) / previousValue) * 100;
        const sign = pctChange >= 0 ? '+' : '';
        changeEl.textContent = `${sign}${pctChange.toFixed(1)}% vs prior period`;
        changeEl.className = `kpi-change ${pctChange >= 0 ? 'positive' : 'negative'}`;
    }
}

function formatValue(value, format) {
    switch (format) {
        case 'currency':
            if (value >= 1e6) return `$${(value / 1e6).toFixed(1)}M`;
            if (value >= 1e3) return `$${(value / 1e3).toFixed(1)}K`;
            return `$${value.toFixed(0)}`;
        case 'percent':
            return `${value.toFixed(1)}%`;
        case 'number':
            if (value >= 1e6) return `${(value / 1e6).toFixed(1)}M`;
            if (value >= 1e3) return `${(value / 1e3).toFixed(1)}K`;
            return value.toLocaleString();
        default:
            return value.toString();
    }
}

Chart.js Integration

Chart Container Pattern

<div class="chart-container">
    <h3 class="chart-title">Monthly Revenue Trend</h3>
    <canvas id="revenue-chart"></canvas>
</div>

Line Chart

function createLineChart(canvasId, labels, datasets) {
    const ctx = document.getElementById(canvasId).getContext('2d');
    return new Chart(ctx, {
        type: 'line',
        data: {
            labels: labels,
            datasets: datasets.map((ds, i) => ({
                label: ds.label,
                data: ds.data,
                borderColor: COLORS[i % COLORS.length],
                backgroundColor: COLORS[i % COLORS.length] + '20',
                borderWidth: 2,
                fill: ds.fill || false,
                tension: 0.3,
                pointRadius: 3,
                pointHoverRadius: 6,
            }))
        },
        options: {
            responsive: true,
            maintainAspectRatio: false,
            interaction: {
                mode: 'index',
                intersect: false,
            },
            plugins: {
                legend: {
                    position: 'top',
                    labels: { usePointStyle: true, padding: 20 }
                },
                tooltip: {
                    callbacks: {
                        label: function(context) {
                            return `${context.dataset.label}: ${formatValue(context.parsed.y, 'currency')}`;
                        }
                    }
                }
            },
            scales: {
                x: {
                    grid: { display: false }
                },
                y: {
                    beginAtZero: true,
                    ticks: {
                        callback: function(value) {
                            return formatValue(value, 'currency');
                        }
                    }
                }
            }
        }
    });
}

Bar Chart

function createBarChart(canvasId, labels, data, options = {}) {
    const ctx = document.getElementById(canvasId).getContext('2d');
    const isHorizontal = options.horizontal || labels.length > 8;

    return new Chart(ctx, {
        type: 'bar',
        data: {
            labels: labels,
            datasets: [{
                label: options.label || 'Value',
                data: data,
                backgroundColor: options.colors || COLORS.map(c => c + 'CC'),
                borderColor: options.colors || COLORS,
                borderWidth: 1,
                borderRadius: 4,
            }]
        },
        options: {
            responsive: true,
            maintainAspectRatio: false,
            indexAxis: isHorizontal ? 'y' : 'x',
            plugins: {
                legend: { display: false },
                tooltip: {
                    callbacks: {
                        label: function(context) {
                            return formatValue(context.parsed[isHorizontal ? 'x' : 'y'], options.format || 'number');
                        }
                    }
                }
            },
            scales: {
                x: {
                    beginAtZero: true,
                    grid: { display: isHorizontal },
                    ticks: isHorizontal ? {
                        callback: function(value) {
                            return formatValue(value, options.format || 'number');
                        }
                    } : {}
                },
                y: {
                    beginAtZero: !isHorizontal,
                    grid: { display: !isHorizontal },
                    ticks: !isHorizontal ? {
                        callback: function(value) {
                            return formatValue(value, options.format || 'number');
                        }
                    } : {}
                }
            }
        }
    });
}

Doughnut Chart

function createDoughnutChart(canvasId, labels, data) {
    const ctx = document.getElementById(canvasId).getContext('2d');
    return new Chart(ctx, {
        type: 'doughnut',
        data: {
            labels: labels,
            datasets: [{
                data: data,
                backgroundColor: COLORS.map(c => c + 'CC'),
                borderColor: '#ffffff',
                borderWidth: 2,
            }]
        },
        options: {
            responsive: true,
            maintainAspectRatio: false,
            cutout: '60%',
            plugins: {
                legend: {
                    position: 'right',
                    labels: { usePointStyle: true, padding: 15 }
                },
                tooltip: {
                    callbacks: {
                        label: function(context) {
                            const total = context.dataset.data.reduce((a, b) => a + b, 0);
                            const pct = ((context.parsed / total) * 100).toFixed(1);
                            return `${context.label}: ${formatValue(context.parsed, 'number')} (${pct}%)`;
                        }
                    }
                }
            }
        }
    });
}

Updating Charts on Filter Change

function updateChart(chart, newLabels, newData) {
    chart.data.labels = newLabels;

    if (Array.isArray(newData[0])) {
        // Multiple datasets
        newData.forEach((data, i) => {
            chart.data.datasets[i].data = data;
        });
    } else {
        chart.data.datasets[0].data = newData;
    }

    chart.update('none'); // 'none' disables animation for instant update
}

Filter and Interactivity Implementation

Dropdown Filter

<div class="filter-group">
    <label for="filter-region">Region</label>
    <select id="filter-region" onchange="dashboard.applyFilters()">
        <option value="all">All Regions</option>
    </select>
</div>

function populateFilter(selectId, data, field) {
    const select = document.getElementById(selectId);
    const values = [...new Set(data.map(d => d[field]))].sort();

    // Keep the "All" option, add unique values
    values.forEach(val => {
        const option = document.createElement('option');
        option.value = val;
        option.textContent = val;
        select.appendChild(option);
    });
}

function getFilterValue(selectId) {
    const val = document.getElementById(selectId).value;
    return val === 'all' ? null : val;
}

Date Range Filter

<div class="filter-group">
    <label>Date Range</label>
    <input type="date" id="filter-date-start" onchange="dashboard.applyFilters()">
    <span>to</span>
    <input type="date" id="filter-date-end" onchange="dashboard.applyFilters()">
</div>

function filterByDateRange(data, dateField, startDate, endDate) {
    return data.filter(row => {
        const rowDate = new Date(row[dateField]);
        if (startDate && rowDate < new Date(startDate)) return false;
        if (endDate && rowDate > new Date(endDate)) return false;
        return true;
    });
}

Combined Filter Logic

applyFilters() {
    const region = getFilterValue('filter-region');
    const category = getFilterValue('filter-category');
    const startDate = document.getElementById('filter-date-start').value;
    const endDate = document.getElementById('filter-date-end').value;

    this.filteredData = this.rawData.filter(row => {
        if (region && row.region !== region) return false;
        if (category && row.category !== category) return false;
        if (startDate && row.date < startDate) return false;
        if (endDate && row.date > endDate) return false;
        return true;
    });

    this.renderKPIs();
    this.updateCharts();
    this.renderTable();
}

Sortable Table

function renderTable(containerId, data, columns) {
    const container = document.getElementById(containerId);
    let sortCol = null;
    let sortDir = 'desc';

    function render(sortedData) {
        let html = '<table class="data-table">';

        // Header
        html += '<thead><tr>';
        columns.forEach(col => {
            const arrow = sortCol === col.field
                ? (sortDir === 'asc' ? ' ▲' : ' ▼')
                : '';
            html += `<th onclick="sortTable('${col.field}')" style="cursor:pointer">${col.label}${arrow}</th>`;
        });
        html += '</tr></thead>';

        // Body
        html += '<tbody>';
        sortedData.forEach(row => {
            html += '<tr>';
            columns.forEach(col => {
                const value = col.format ? formatValue(row[col.field], col.format) : row[col.field];
                html += `<td>${value}</td>`;
            });
            html += '</tr>';
        });
        html += '</tbody></table>';

        container.innerHTML = html;
    }

    window.sortTable = function(field) {
        if (sortCol === field) {
            sortDir = sortDir === 'asc' ? 'desc' : 'asc';
        } else {
            sortCol = field;
            sortDir = 'desc';
        }
        const sorted = [...data].sort((a, b) => {
            const aVal = a[field], bVal = b[field];
            const cmp = aVal < bVal ? -1 : aVal > bVal ? 1 : 0;
            return sortDir === 'asc' ? cmp : -cmp;
        });
        render(sorted);
    };

    render(data);
}

CSS Styling for Dashboards

Color System

:root {
    /* Background layers */
    --bg-primary: #f8f9fa;
    --bg-card: #ffffff;
    --bg-header: #1a1a2e;

    /* Text */
    --text-primary: #212529;
    --text-secondary: #6c757d;
    --text-on-dark: #ffffff;

    /* Accent colors for data */
    --color-1: #4C72B0;
    --color-2: #DD8452;
    --color-3: #55A868;
    --color-4: #C44E52;
    --color-5: #8172B3;
    --color-6: #937860;

    /* Status colors */
    --positive: #28a745;
    --negative: #dc3545;
    --neutral: #6c757d;

    /* Spacing */
    --gap: 16px;
    --radius: 8px;
}

Layout

* {
    margin: 0;
    padding: 0;
    box-sizing: border-box;
}

body {
    font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, sans-serif;
    background: var(--bg-primary);
    color: var(--text-primary);
    line-height: 1.5;
}

.dashboard-container {
    max-width: 1400px;
    margin: 0 auto;
    padding: var(--gap);
}

.dashboard-header {
    background: var(--bg-header);
    color: var(--text-on-dark);
    padding: 20px 24px;
    border-radius: var(--radius);
    margin-bottom: var(--gap);
    display: flex;
    justify-content: space-between;
    align-items: center;
    flex-wrap: wrap;
    gap: 12px;
}

.dashboard-header h1 {
    font-size: 20px;
    font-weight: 600;
}

KPI Cards

.kpi-row {
    display: grid;
    grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));
    gap: var(--gap);
    margin-bottom: var(--gap);
}

.kpi-card {
    background: var(--bg-card);
    border-radius: var(--radius);
    padding: 20px 24px;
    box-shadow: 0 1px 3px rgba(0, 0, 0, 0.08);
}

.kpi-label {
    font-size: 13px;
    color: var(--text-secondary);
    text-transform: uppercase;
    letter-spacing: 0.5px;
    margin-bottom: 4px;
}

.kpi-value {
    font-size: 28px;
    font-weight: 700;
    color: var(--text-primary);
    margin-bottom: 4px;
}

.kpi-change {
    font-size: 13px;
    font-weight: 500;
}

.kpi-change.positive { color: var(--positive); }
.kpi-change.negative { color: var(--negative); }

Chart Containers

.chart-row {
    display: grid;
    grid-template-columns: repeat(auto-fit, minmax(400px, 1fr));
    gap: var(--gap);
    margin-bottom: var(--gap);
}

.chart-container {
    background: var(--bg-card);
    border-radius: var(--radius);
    padding: 20px 24px;
    box-shadow: 0 1px 3px rgba(0, 0, 0, 0.08);
}

.chart-container h3 {
    font-size: 14px;
    font-weight: 600;
    color: var(--text-primary);
    margin-bottom: 16px;
}

.chart-container canvas {
    max-height: 300px;
}

Filters

.filters {
    display: flex;
    gap: 12px;
    align-items: center;
    flex-wrap: wrap;
}

.filter-group {
    display: flex;
    align-items: center;
    gap: 6px;
}

.filter-group label {
    font-size: 12px;
    color: rgba(255, 255, 255, 0.7);
}

.filter-group select,
.filter-group input[type="date"] {
    padding: 6px 10px;
    border: 1px solid rgba(255, 255, 255, 0.2);
    border-radius: 4px;
    background: rgba(255, 255, 255, 0.1);
    color: var(--text-on-dark);
    font-size: 13px;
}

.filter-group select option {
    background: var(--bg-header);
    color: var(--text-on-dark);
}

Data Table

.table-section {
    background: var(--bg-card);
    border-radius: var(--radius);
    padding: 20px 24px;
    box-shadow: 0 1px 3px rgba(0, 0, 0, 0.08);
    overflow-x: auto;
}

.data-table {
    width: 100%;
    border-collapse: collapse;
    font-size: 13px;
}

.data-table thead th {
    text-align: left;
    padding: 10px 12px;
    border-bottom: 2px solid #dee2e6;
    color: var(--text-secondary);
    font-weight: 600;
    font-size: 12px;
    text-transform: uppercase;
    letter-spacing: 0.5px;
    white-space: nowrap;
    user-select: none;
}

.data-table thead th:hover {
    color: var(--text-primary);
    background: #f8f9fa;
}

.data-table tbody td {
    padding: 10px 12px;
    border-bottom: 1px solid #f0f0f0;
}

.data-table tbody tr:hover {
    background: #f8f9fa;
}

.data-table tbody tr:last-child td {
    border-bottom: none;
}

Responsive Design

@media (max-width: 768px) {
    .dashboard-header {
        flex-direction: column;
        align-items: flex-start;
    }

    .kpi-row {
        grid-template-columns: repeat(2, 1fr);
    }

    .chart-row {
        grid-template-columns: 1fr;
    }

    .filters {
        flex-direction: column;
        align-items: flex-start;
    }
}

@media print {
    body { background: white; }
    .dashboard-container { max-width: none; }
    .filters { display: none; }
    .chart-container { break-inside: avoid; }
    .kpi-card { border: 1px solid #dee2e6; box-shadow: none; }
}

Performance Considerations for Large Datasets

Data Size Guidelines

Pre-Aggregation Pattern

Instead of embedding raw data and aggregating in the browser:

// DON'T: embed 50,000 raw rows
const RAW_DATA = [/* 50,000 rows */];

// DO: pre-aggregate before embedding
const CHART_DATA = {
    monthly_revenue: [
        { month: '2024-01', revenue: 150000, orders: 1200 },
        { month: '2024-02', revenue: 165000, orders: 1350 },
        // ... 12 rows instead of 50,000
    ],
    top_products: [
        { product: 'Widget A', revenue: 45000 },
        // ... 10 rows
    ],
    kpis: {
        total_revenue: 1980000,
        total_orders: 15600,
        avg_order_value: 127,
    }
};

Chart Performance

• Limit line charts to <500 data points per series (downsample if needed)
• Limit bar charts to <50 categories
• For scatter plots, cap at 1,000 points (use sampling for larger datasets)
• Disable animations for dashboards with many charts: animation: false in Chart.js options
• Use Chart.update('none') instead of Chart.update() for filter-triggered updates

DOM Performance

• Limit data tables to 100-200 visible rows. Add pagination for more.
• Use requestAnimationFrame for coordinated chart updates
• Avoid rebuilding the entire DOM on filter change -- update only changed elements

// Efficient table pagination
function renderTablePage(data, page, pageSize = 50) {
    const start = page * pageSize;
    const end = Math.min(start + pageSize, data.length);
    const pageData = data.slice(start, end);
    // Render only pageData
    // Show pagination controls: "Showing 1-50 of 2,340"
}

Examples

/build-dashboard Monthly sales dashboard with revenue trend, top products, and regional breakdown. Data is in the orders table.

/build-dashboard Here's our support ticket data [pastes CSV]. Build a dashboard showing volume by priority, response time trends, and resolution rates.

/build-dashboard Create a template executive dashboard for a SaaS company showing MRR, churn, new customers, and NPS. Use sample data.

Tips

• Dashboards are fully self-contained HTML files -- share them with anyone by sending the file
• For real-time dashboards, consider connecting to a BI tool instead. These dashboards are point-in-time snapshots
• Request "dark mode" or "presentation mode" for different styling
• You can request a specific color scheme to match your brand

/create-viz - Create Visualizations

If you see unfamiliar placeholders or need to check which tools are connected, see CONNECTORS.md.

Create publication-quality data visualizations using Python. Generates charts from data with best practices for clarity, accuracy, and design.

Usage

/create-viz <data source> [chart type] [additional instructions]

Workflow

1. Understand the Request

Determine:

• Data source: Query results, pasted data, CSV/Excel file, or data to be queried
• Chart type: Explicitly requested or needs to be recommended
• Purpose: Exploration, presentation, report, dashboard component
• Audience: Technical team, executives, external stakeholders

2. Get the Data

If data warehouse is connected and data needs querying:

1. Write and execute the query
2. Load results into a pandas DataFrame

If data is pasted or uploaded:

1. Parse the data into a pandas DataFrame
2. Clean and prepare as needed (type conversions, null handling)

If data is from a previous analysis in the conversation:

1. Reference the existing data

3. Select Chart Type

If the user didn't specify a chart type, recommend one based on the data and question:

Explain the recommendation briefly if the user didn't specify.

4. Generate the Visualization

Write Python code using one of these libraries based on the need:

• matplotlib + seaborn: Best for static, publication-quality charts. Default choice.
• plotly: Best for interactive charts or when the user requests interactivity.

Code requirements:

import matplotlib.pyplot as plt
import seaborn as sns
import pandas as pd

# Set professional style
plt.style.use('seaborn-v0_8-whitegrid')
sns.set_palette("husl")

# Create figure with appropriate size
fig, ax = plt.subplots(figsize=(10, 6))

# [chart-specific code]

# Always include:
ax.set_title('Clear, Descriptive Title', fontsize=14, fontweight='bold')
ax.set_xlabel('X-Axis Label', fontsize=11)
ax.set_ylabel('Y-Axis Label', fontsize=11)

# Format numbers appropriately
# - Percentages: '45.2%' not '0.452'
# - Currency: '$1.2M' not '1200000'
# - Large numbers: '2.3K' or '1.5M' not '2300' or '1500000'

# Remove chart junk
ax.spines['top'].set_visible(False)
ax.spines['right'].set_visible(False)

plt.tight_layout()
plt.savefig('chart_name.png', dpi=150, bbox_inches='tight')
plt.show()

5. Apply Design Best Practices

Color:

• Use a consistent, colorblind-friendly palette
• Use color meaningfully (not decoratively)
• Highlight the key data point or trend with a contrasting color
• Grey out less important reference data

Typography:

• Descriptive title that states the insight, not just the metric (e.g., "Revenue grew 23% YoY" not "Revenue by Month")
• Readable axis labels (not rotated 90 degrees if avoidable)
• Data labels on key points when they add clarity

Layout:

• Appropriate whitespace and margins
• Legend placement that doesn't obscure data
• Sorted categories by value (not alphabetically) unless there's a natural order

Accuracy:

• Y-axis starts at zero for bar charts
• No misleading axis breaks without clear notation
• Consistent scales when comparing panels
• Appropriate precision (don't show 10 decimal places)

6. Save and Present

1. Save the chart as a PNG file with descriptive name
2. Display the chart to the user
3. Provide the code used so they can modify it
4. Suggest variations (different chart type, different grouping, zoomed time range)

Examples

/create-viz Show monthly revenue for the last 12 months as a line chart with the trend highlighted

/create-viz Here's our NPS data by product: [pastes data]. Create a horizontal bar chart ranking products by score.

/create-viz Query the orders table and create a heatmap of order volume by day-of-week and hour

Tips

• If you want interactive charts (hover, zoom, filter), mention "interactive" and Claude will use plotly
• Specify "presentation" if you need larger fonts and higher contrast
• You can request multiple charts at once (e.g., "create a 2x2 grid of charts showing...")
• Charts are saved to your current directory as PNG files

Data Visualization Skill

Chart selection guidance, Python visualization code patterns, design principles, and accessibility considerations for creating effective data visualizations.

Chart Selection Guide

Choose by Data Relationship

When NOT to Use Certain Charts

• Pie charts: Avoid unless <6 categories and exact proportions matter less than rough comparison. Humans are bad at comparing angles. Use bar charts instead.
• 3D charts: Never. They distort perception and add no information.
• Dual-axis charts: Use cautiously. They can mislead by implying correlation. Clearly label both axes if used.
• Stacked bar (many categories): Hard to compare middle segments. Use small multiples or grouped bars instead.
• Donut charts: Slightly better than pie charts but same fundamental issues. Use for single KPI display at most.

Python Visualization Code Patterns

Setup and Style

import matplotlib.pyplot as plt
import matplotlib.ticker as mticker
import seaborn as sns
import pandas as pd
import numpy as np

# Professional style setup
plt.style.use('seaborn-v0_8-whitegrid')
plt.rcParams.update({
    'figure.figsize': (10, 6),
    'figure.dpi': 150,
    'font.size': 11,
    'axes.titlesize': 14,
    'axes.titleweight': 'bold',
    'axes.labelsize': 11,
    'xtick.labelsize': 10,
    'ytick.labelsize': 10,
    'legend.fontsize': 10,
    'figure.titlesize': 16,
})

# Colorblind-friendly palettes
PALETTE_CATEGORICAL = ['#4C72B0', '#DD8452', '#55A868', '#C44E52', '#8172B3', '#937860']
PALETTE_SEQUENTIAL = 'YlOrRd'
PALETTE_DIVERGING = 'RdBu_r'

Line Chart (Time Series)

fig, ax = plt.subplots(figsize=(10, 6))

for label, group in df.groupby('category'):
    ax.plot(group['date'], group['value'], label=label, linewidth=2)

ax.set_title('Metric Trend by Category', fontweight='bold')
ax.set_xlabel('Date')
ax.set_ylabel('Value')
ax.legend(loc='upper left', frameon=True)
ax.spines['top'].set_visible(False)
ax.spines['right'].set_visible(False)

# Format dates on x-axis
fig.autofmt_xdate()

plt.tight_layout()
plt.savefig('trend_chart.png', dpi=150, bbox_inches='tight')

Bar Chart (Comparison)

fig, ax = plt.subplots(figsize=(10, 6))

# Sort by value for easy reading
df_sorted = df.sort_values('metric', ascending=True)

bars = ax.barh(df_sorted['category'], df_sorted['metric'], color=PALETTE_CATEGORICAL[0])

# Add value labels
for bar in bars:
    width = bar.get_width()
    ax.text(width + 0.5, bar.get_y() + bar.get_height()/2,
            f'{width:,.0f}', ha='left', va='center', fontsize=10)

ax.set_title('Metric by Category (Ranked)', fontweight='bold')
ax.set_xlabel('Metric Value')
ax.spines['top'].set_visible(False)
ax.spines['right'].set_visible(False)

plt.tight_layout()
plt.savefig('bar_chart.png', dpi=150, bbox_inches='tight')

Histogram (Distribution)

fig, ax = plt.subplots(figsize=(10, 6))

ax.hist(df['value'], bins=30, color=PALETTE_CATEGORICAL[0], edgecolor='white', alpha=0.8)

# Add mean and median lines
mean_val = df['value'].mean()
median_val = df['value'].median()
ax.axvline(mean_val, color='red', linestyle='--', linewidth=1.5, label=f'Mean: {mean_val:,.1f}')
ax.axvline(median_val, color='green', linestyle='--', linewidth=1.5, label=f'Median: {median_val:,.1f}')

ax.set_title('Distribution of Values', fontweight='bold')
ax.set_xlabel('Value')
ax.set_ylabel('Frequency')
ax.legend()
ax.spines['top'].set_visible(False)
ax.spines['right'].set_visible(False)

plt.tight_layout()
plt.savefig('histogram.png', dpi=150, bbox_inches='tight')

Heatmap

fig, ax = plt.subplots(figsize=(10, 8))

# Pivot data for heatmap format
pivot = df.pivot_table(index='row_dim', columns='col_dim', values='metric', aggfunc='sum')

sns.heatmap(pivot, annot=True, fmt=',.0f', cmap='YlOrRd',
            linewidths=0.5, ax=ax, cbar_kws={'label': 'Metric Value'})

ax.set_title('Metric by Row Dimension and Column Dimension', fontweight='bold')
ax.set_xlabel('Column Dimension')
ax.set_ylabel('Row Dimension')

plt.tight_layout()
plt.savefig('heatmap.png', dpi=150, bbox_inches='tight')

Small Multiples

categories = df['category'].unique()
n_cats = len(categories)
n_cols = min(3, n_cats)
n_rows = (n_cats + n_cols - 1) // n_cols

fig, axes = plt.subplots(n_rows, n_cols, figsize=(5*n_cols, 4*n_rows), sharex=True, sharey=True)
axes = axes.flatten() if n_cats > 1 else [axes]

for i, cat in enumerate(categories):
    ax = axes[i]
    subset = df[df['category'] == cat]
    ax.plot(subset['date'], subset['value'], color=PALETTE_CATEGORICAL[i % len(PALETTE_CATEGORICAL)])
    ax.set_title(cat, fontsize=12)
    ax.spines['top'].set_visible(False)
    ax.spines['right'].set_visible(False)

# Hide empty subplots
for j in range(i+1, len(axes)):
    axes[j].set_visible(False)

fig.suptitle('Trends by Category', fontsize=14, fontweight='bold', y=1.02)
plt.tight_layout()
plt.savefig('small_multiples.png', dpi=150, bbox_inches='tight')

Number Formatting Helpers

def format_number(val, format_type='number'):
    """Format numbers for chart labels."""
    if format_type == 'currency':
        if abs(val) >= 1e9:
            return f'${val/1e9:.1f}B'
        elif abs(val) >= 1e6:
            return f'${val/1e6:.1f}M'
        elif abs(val) >= 1e3:
            return f'${val/1e3:.1f}K'
        else:
            return f'${val:,.0f}'
    elif format_type == 'percent':
        return f'{val:.1f}%'
    elif format_type == 'number':
        if abs(val) >= 1e9:
            return f'{val/1e9:.1f}B'
        elif abs(val) >= 1e6:
            return f'{val/1e6:.1f}M'
        elif abs(val) >= 1e3:
            return f'{val/1e3:.1f}K'
        else:
            return f'{val:,.0f}'
    return str(val)

# Usage with axis formatter
ax.yaxis.set_major_formatter(mticker.FuncFormatter(lambda x, p: format_number(x, 'currency')))

Interactive Charts with Plotly

import plotly.express as px
import plotly.graph_objects as go

# Simple interactive line chart
fig = px.line(df, x='date', y='value', color='category',
              title='Interactive Metric Trend',
              labels={'value': 'Metric Value', 'date': 'Date'})
fig.update_layout(hovermode='x unified')
fig.write_html('interactive_chart.html')
fig.show()

# Interactive scatter with hover data
fig = px.scatter(df, x='metric_a', y='metric_b', color='category',
                 size='size_metric', hover_data=['name', 'detail_field'],
                 title='Correlation Analysis')
fig.show()

Design Principles

Color

• Use color purposefully: Color should encode data, not decorate
• Highlight the story: Use a bright accent color for the key insight; grey everything else
• Sequential data: Use a single-hue gradient (light to dark) for ordered values
• Diverging data: Use a two-hue gradient with neutral midpoint for data with a meaningful center
• Categorical data: Use distinct hues, maximum 6-8 before it gets confusing
• Avoid red/green only: 8% of men are red-green colorblind. Use blue/orange as primary pair

Typography

• Title states the insight: "Revenue grew 23% YoY" beats "Revenue by Month"
• Subtitle adds context: Date range, filters applied, data source
• Axis labels are readable: Never rotated 90 degrees if avoidable. Shorten or wrap instead
• Data labels add precision: Use on key points, not every single bar
• Annotation highlights: Call out specific points with text annotations

Layout

• Reduce chart junk: Remove gridlines, borders, backgrounds that don't carry information
• Sort meaningfully: Categories sorted by value (not alphabetically) unless there's a natural order (months, stages)
• Appropriate aspect ratio: Time series wider than tall (3:1 to 2:1); comparisons can be squarer
• White space is good: Don't cram charts together. Give each visualization room to breathe

Accuracy

• Bar charts start at zero: Always. A bar from 95 to 100 exaggerates a 5% difference
• Line charts can have non-zero baselines: When the range of variation is meaningful
• Consistent scales across panels: When comparing multiple charts, use the same axis range
• Show uncertainty: Error bars, confidence intervals, or ranges when data is uncertain
• Label your axes: Never make the reader guess what the numbers mean

Accessibility Considerations

Color Blindness

• Never rely on color alone to distinguish data series
• Add pattern fills, different line styles (solid, dashed, dotted), or direct labels
• Test with a colorblind simulator (e.g., Coblis, Sim Daltonism)
• Use the colorblind-friendly palette: sns.color_palette("colorblind")

Screen Readers

• Include alt text describing the chart's key finding
• Provide a data table alternative alongside the visualization
• Use semantic titles and labels

General Accessibility

• Sufficient contrast between data elements and background
• Text size minimum 10pt for labels, 12pt for titles
• Avoid conveying information only through spatial position (add labels)
• Consider printing: does the chart work in black and white?

Accessibility Checklist

Before sharing a visualization:

• Chart works without color (patterns, labels, or line styles differentiate series)
• Text is readable at standard zoom level
• Title describes the insight, not just the data
• Axes are labeled with units
• Legend is clear and positioned without obscuring data
• Data source and date range are noted

/explore-data - Profile and Explore a Dataset

If you see unfamiliar placeholders or need to check which tools are connected, see CONNECTORS.md.

Generate a comprehensive data profile for a table or uploaded file. Understand its shape, quality, and patterns before diving into analysis.

Usage

/explore-data <table_name or file>

Workflow

1. Access the Data

If a data warehouse MCP server is connected:

1. Resolve the table name (handle schema prefixes, suggest matches if ambiguous)
2. Query table metadata: column names, types, descriptions if available
3. Run profiling queries against the live data

If a file is provided (CSV, Excel, Parquet, JSON):

1. Read the file and load into a working dataset
2. Infer column types from the data

If neither:

1. Ask the user to provide a table name (with their warehouse connected) or upload a file
2. If they describe a table schema, provide guidance on what profiling queries to run

2. Understand Structure

Before analyzing any data, understand its structure:

Table-level questions:

• How many rows and columns?
• What is the grain (one row per what)?
• What is the primary key? Is it unique?
• When was the data last updated?
• How far back does the data go?

Column classification — categorize each column as one of:

• Identifier: Unique keys, foreign keys, entity IDs
• Dimension: Categorical attributes for grouping/filtering (status, type, region, category)
• Metric: Quantitative values for measurement (revenue, count, duration, score)
• Temporal: Dates and timestamps (created_at, updated_at, event_date)
• Text: Free-form text fields (description, notes, name)
• Boolean: True/false flags
• Structural: JSON, arrays, nested structures

3. Generate Data Profile

Run the following profiling checks:

Table-level metrics:

• Total row count
• Column count and types breakdown
• Approximate table size (if available from metadata)
• Date range coverage (min/max of date columns)

All columns:

• Null count and null rate
• Distinct count and cardinality ratio (distinct / total)
• Most common values (top 5-10 with frequencies)
• Least common values (bottom 5 to spot anomalies)

Numeric columns (metrics):

min, max, mean, median (p50)
standard deviation
percentiles: p1, p5, p25, p75, p95, p99
zero count
negative count (if unexpected)

String columns (dimensions, text):

min length, max length, avg length
empty string count
pattern analysis (do values follow a format?)
case consistency (all upper, all lower, mixed?)
leading/trailing whitespace count

Date/timestamp columns:

min date, max date
null dates
future dates (if unexpected)
distribution by month/week
gaps in time series

Boolean columns:

true count, false count, null count
true rate

Present the profile as a clean summary table, grouped by column type (dimensions, metrics, dates, IDs).

4. Identify Data Quality Issues

Apply the quality assessment framework below. Flag potential problems:

• High null rates: Columns with >5% nulls (warn), >20% nulls (alert)
• Low cardinality surprises: Columns that should be high-cardinality but aren't (e.g., a "user_id" with only 50 distinct values)
• High cardinality surprises: Columns that should be categorical but have too many distinct values
• Suspicious values: Negative amounts where only positive expected, future dates in historical data, obviously placeholder values (e.g., "N/A", "TBD", "test", "999999")
• Duplicate detection: Check if there's a natural key and whether it has duplicates
• Distribution skew: Extremely skewed numeric distributions that could affect averages
• Encoding issues: Mixed case in categorical fields, trailing whitespace, inconsistent formats

5. Discover Relationships and Patterns

After profiling individual columns:

• Foreign key candidates: ID columns that might link to other tables
• Hierarchies: Columns that form natural drill-down paths (country > state > city)
• Correlations: Numeric columns that move together
• Derived columns: Columns that appear to be computed from others
• Redundant columns: Columns with identical or near-identical information

6. Suggest Interesting Dimensions and Metrics

Based on the column profile, recommend:

• Best dimension columns for slicing data (categorical columns with reasonable cardinality, 3-50 values)
• Key metric columns for measurement (numeric columns with meaningful distributions)
• Time columns suitable for trend analysis
• Natural groupings or hierarchies apparent in the data
• Potential join keys linking to other tables (ID columns, foreign keys)

7. Recommend Follow-Up Analyses

Suggest 3-5 specific analyses the user could run next:

• "Trend analysis on [metric] by [time_column] grouped by [dimension]"
• "Distribution deep-dive on [skewed_column] to understand outliers"
• "Data quality investigation on [problematic_column]"
• "Correlation analysis between [metric_a] and [metric_b]"
• "Cohort analysis using [date_column] and [status_column]"

Output Format

## Data Profile: [table_name]

### Overview
- Rows: 2,340,891
- Columns: 23 (8 dimensions, 6 metrics, 4 dates, 5 IDs)
- Date range: 2021-03-15 to 2024-01-22

### Column Details
[summary table]

### Data Quality Issues
[flagged issues with severity]

### Recommended Explorations
[numbered list of suggested follow-up analyses]

Quality Assessment Framework

Completeness Score

Rate each column:

• Complete (>99% non-null): Green
• Mostly complete (95-99%): Yellow -- investigate the nulls
• Incomplete (80-95%): Orange -- understand why and whether it matters
• Sparse (<80%): Red -- may not be usable without imputation

Consistency Checks

Look for:

• Value format inconsistency: Same concept represented differently ("USA", "US", "United States", "us")
• Type inconsistency: Numbers stored as strings, dates in various formats
• Referential integrity: Foreign keys that don't match any parent record
• Business rule violations: Negative quantities, end dates before start dates, percentages > 100
• Cross-column consistency: Status = "completed" but completed_at is null

Accuracy Indicators

Red flags that suggest accuracy issues:

• Placeholder values: 0, -1, 999999, "N/A", "TBD", "test", "xxx"
• Default values: Suspiciously high frequency of a single value
• Stale data: Updated_at shows no recent changes in an active system
• Impossible values: Ages > 150, dates in the far future, negative durations
• Round number bias: All values ending in 0 or 5 (suggests estimation, not measurement)

Timeliness Assessment

• When was the table last updated?
• What is the expected update frequency?
• Is there a lag between event time and load time?
• Are there gaps in the time series?

Pattern Discovery Techniques

Distribution Analysis

For numeric columns, characterize the distribution:

• Normal: Mean and median are close, bell-shaped
• Skewed right: Long tail of high values (common for revenue, session duration)
• Skewed left: Long tail of low values (less common)
• Bimodal: Two peaks (suggests two distinct populations)
• Power law: Few very large values, many small ones (common for user activity)
• Uniform: Roughly equal frequency across range (often synthetic or random)

Temporal Patterns

For time series data, look for:

• Trend: Sustained upward or downward movement
• Seasonality: Repeating patterns (weekly, monthly, quarterly, annual)
• Day-of-week effects: Weekday vs. weekend differences
• Holiday effects: Drops or spikes around known holidays
• Change points: Sudden shifts in level or trend
• Anomalies: Individual data points that break the pattern

Segmentation Discovery

Identify natural segments by:

• Finding categorical columns with 3-20 distinct values
• Comparing metric distributions across segment values
• Looking for segments with significantly different behavior
• Testing whether segments are homogeneous or contain sub-segments

Correlation Exploration

Between numeric columns:

• Compute correlation matrix for all metric pairs
• Flag strong correlations (|r| > 0.7) for investigation
• Note: Correlation does not imply causation -- flag this explicitly
• Check for non-linear relationships (e.g., quadratic, logarithmic)

Schema Understanding and Documentation

Schema Documentation Template

When documenting a dataset for team use:

## Table: [schema.table_name]

**Description**: [What this table represents]
**Grain**: [One row per...]
**Primary Key**: [column(s)]
**Row Count**: [approximate, with date]
**Update Frequency**: [real-time / hourly / daily / weekly]
**Owner**: [team or person responsible]

### Key Columns

| Column | Type | Description | Example Values | Notes |
|--------|------|-------------|----------------|-------|
| user_id | STRING | Unique user identifier | "usr_abc123" | FK to users.id |
| event_type | STRING | Type of event | "click", "view", "purchase" | 15 distinct values |
| revenue | DECIMAL | Transaction revenue in USD | 29.99, 149.00 | Null for non-purchase events |
| created_at | TIMESTAMP | When the event occurred | 2024-01-15 14:23:01 | Partitioned on this column |

### Relationships
- Joins to `users` on `user_id`
- Joins to `products` on `product_id`
- Parent of `event_details` (1:many on event_id)

### Known Issues
- [List any known data quality issues]
- [Note any gotchas for analysts]

### Common Query Patterns
- [Typical use cases for this table]

Schema Exploration Queries

When connected to a data warehouse, use these patterns to discover schema:

-- List all tables in a schema (PostgreSQL)
SELECT table_name, table_type
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name;

-- Column details (PostgreSQL)
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'my_table'
ORDER BY ordinal_position;

-- Table sizes (PostgreSQL)
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

-- Row counts for all tables (general pattern)
-- Run per-table: SELECT COUNT(*) FROM table_name

Lineage and Dependencies

When exploring an unfamiliar data environment:

1. Start with the "output" tables (what reports or dashboards consume)
2. Trace upstream: What tables feed into them?
3. Identify raw/staging/mart layers
4. Map the transformation chain from raw data to analytical tables
5. Note where data is enriched, filtered, or aggregated

Tips

• For very large tables (100M+ rows), profiling queries use sampling by default -- mention if you need exact counts
• If exploring a new dataset for the first time, this command gives you the lay of the land before writing specific queries
• The quality flags are heuristic -- not every flag is a real problem, but each is worth a quick look

SQL Queries Skill

Write correct, performant, readable SQL across all major data warehouse dialects.

Dialect-Specific Reference

PostgreSQL (including Aurora, RDS, Supabase, Neon)

Date/time:

-- Current date/time
CURRENT_DATE, CURRENT_TIMESTAMP, NOW()

-- Date arithmetic
date_column + INTERVAL '7 days'
date_column - INTERVAL '1 month'

-- Truncate to period
DATE_TRUNC('month', created_at)

-- Extract parts
EXTRACT(YEAR FROM created_at)
EXTRACT(DOW FROM created_at)  -- 0=Sunday

-- Format
TO_CHAR(created_at, 'YYYY-MM-DD')

String functions:

-- Concatenation
first_name || ' ' || last_name
CONCAT(first_name, ' ', last_name)

-- Pattern matching
column ILIKE '%pattern%'  -- case-insensitive
column ~ '^regex_pattern$'  -- regex

-- String manipulation
LEFT(str, n), RIGHT(str, n)
SPLIT_PART(str, delimiter, position)
REGEXP_REPLACE(str, pattern, replacement)

Arrays and JSON:

-- JSON access
data->>'key'  -- text
data->'nested'->'key'  -- json
data#>>'{path,to,key}'  -- nested text

-- Array operations
ARRAY_AGG(column)
ANY(array_column)
array_column @> ARRAY['value']

Performance tips:

• Use EXPLAIN ANALYZE to profile queries
• Create indexes on frequently filtered/joined columns
• Use EXISTS over IN for correlated subqueries
• Partial indexes for common filter conditions
• Use connection pooling for concurrent access

Snowflake

Date/time:

-- Current date/time
CURRENT_DATE(), CURRENT_TIMESTAMP(), SYSDATE()

-- Date arithmetic
DATEADD(day, 7, date_column)
DATEDIFF(day, start_date, end_date)

-- Truncate to period
DATE_TRUNC('month', created_at)

-- Extract parts
YEAR(created_at), MONTH(created_at), DAY(created_at)
DAYOFWEEK(created_at)

-- Format
TO_CHAR(created_at, 'YYYY-MM-DD')

String functions:

-- Case-insensitive by default (depends on collation)
column ILIKE '%pattern%'
REGEXP_LIKE(column, 'pattern')

-- Parse JSON
column:key::string  -- dot notation for VARIANT
PARSE_JSON('{"key": "value"}')
GET_PATH(variant_col, 'path.to.key')

-- Flatten arrays/objects
SELECT f.value FROM table, LATERAL FLATTEN(input => array_col) f

Semi-structured data:

-- VARIANT type access
data:customer:name::STRING
data:items[0]:price::NUMBER

-- Flatten nested structures
SELECT
    t.id,
    item.value:name::STRING as item_name,
    item.value:qty::NUMBER as quantity
FROM my_table t,
LATERAL FLATTEN(input => t.data:items) item

Performance tips:

• Use clustering keys on large tables (not traditional indexes)
• Filter on clustering key columns for partition pruning
• Set appropriate warehouse size for query complexity
• Use RESULT_SCAN(LAST_QUERY_ID()) to avoid re-running expensive queries
• Use transient tables for staging/temp data

BigQuery (Google Cloud)

Date/time:

-- Current date/time
CURRENT_DATE(), CURRENT_TIMESTAMP()

-- Date arithmetic
DATE_ADD(date_column, INTERVAL 7 DAY)
DATE_SUB(date_column, INTERVAL 1 MONTH)
DATE_DIFF(end_date, start_date, DAY)
TIMESTAMP_DIFF(end_ts, start_ts, HOUR)

-- Truncate to period
DATE_TRUNC(created_at, MONTH)
TIMESTAMP_TRUNC(created_at, HOUR)

-- Extract parts
EXTRACT(YEAR FROM created_at)
EXTRACT(DAYOFWEEK FROM created_at)  -- 1=Sunday

-- Format
FORMAT_DATE('%Y-%m-%d', date_column)
FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', ts_column)

String functions:

-- No ILIKE, use LOWER()
LOWER(column) LIKE '%pattern%'
REGEXP_CONTAINS(column, r'pattern')
REGEXP_EXTRACT(column, r'pattern')

-- String manipulation
SPLIT(str, delimiter)  -- returns ARRAY
ARRAY_TO_STRING(array, delimiter)

Arrays and structs:

-- Array operations
ARRAY_AGG(column)
UNNEST(array_column)
ARRAY_LENGTH(array_column)
value IN UNNEST(array_column)

-- Struct access
struct_column.field_name

Performance tips:

• Always filter on partition columns (usually date) to reduce bytes scanned
• Use clustering for frequently filtered columns within partitions
• Use APPROX_COUNT_DISTINCT() for large-scale cardinality estimates
• Avoid SELECT * -- billing is per-byte scanned
• Use DECLARE and SET for parameterized scripts
• Preview query cost with dry run before executing large queries

Redshift (Amazon)

Date/time:

-- Current date/time
CURRENT_DATE, GETDATE(), SYSDATE

-- Date arithmetic
DATEADD(day, 7, date_column)
DATEDIFF(day, start_date, end_date)

-- Truncate to period
DATE_TRUNC('month', created_at)

-- Extract parts
EXTRACT(YEAR FROM created_at)
DATE_PART('dow', created_at)

String functions:

-- Case-insensitive
column ILIKE '%pattern%'
REGEXP_INSTR(column, 'pattern') > 0

-- String manipulation
SPLIT_PART(str, delimiter, position)
LISTAGG(column, ', ') WITHIN GROUP (ORDER BY column)

Performance tips:

• Design distribution keys for collocated joins (DISTKEY)
• Use sort keys for frequently filtered columns (SORTKEY)
• Use EXPLAIN to check query plan
• Avoid cross-node data movement (watch for DS_BCAST and DS_DIST)
• ANALYZE and VACUUM regularly
• Use late-binding views for schema flexibility

Databricks SQL

Date/time:

-- Current date/time
CURRENT_DATE(), CURRENT_TIMESTAMP()

-- Date arithmetic
DATE_ADD(date_column, 7)
DATEDIFF(end_date, start_date)
ADD_MONTHS(date_column, 1)

-- Truncate to period
DATE_TRUNC('MONTH', created_at)
TRUNC(date_column, 'MM')

-- Extract parts
YEAR(created_at), MONTH(created_at)
DAYOFWEEK(created_at)

Delta Lake features:

-- Time travel
SELECT * FROM my_table TIMESTAMP AS OF '2024-01-15'
SELECT * FROM my_table VERSION AS OF 42

-- Describe history
DESCRIBE HISTORY my_table

-- Merge (upsert)
MERGE INTO target USING source
ON target.id = source.id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *

Performance tips:

• Use Delta Lake's OPTIMIZE and ZORDER for query performance
• Leverage Photon engine for compute-intensive queries
• Use CACHE TABLE for frequently accessed datasets
• Partition by low-cardinality date columns

Common SQL Patterns

Window Functions

-- Ranking
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC)
RANK() OVER (PARTITION BY category ORDER BY revenue DESC)
DENSE_RANK() OVER (ORDER BY score DESC)

-- Running totals / moving averages
SUM(revenue) OVER (ORDER BY date_col ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as running_total
AVG(revenue) OVER (ORDER BY date_col ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg_7d

-- Lag / Lead
LAG(value, 1) OVER (PARTITION BY entity ORDER BY date_col) as prev_value
LEAD(value, 1) OVER (PARTITION BY entity ORDER BY date_col) as next_value

-- First / Last value
FIRST_VALUE(status) OVER (PARTITION BY user_id ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
LAST_VALUE(status) OVER (PARTITION BY user_id ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

-- Percent of total
revenue / SUM(revenue) OVER () as pct_of_total
revenue / SUM(revenue) OVER (PARTITION BY category) as pct_of_category

CTEs for Readability

WITH
-- Step 1: Define the base population
base_users AS (
    SELECT user_id, created_at, plan_type
    FROM users
    WHERE created_at >= DATE '2024-01-01'
      AND status = 'active'
),

-- Step 2: Calculate user-level metrics
user_metrics AS (
    SELECT
        u.user_id,
        u.plan_type,
        COUNT(DISTINCT e.session_id) as session_count,
        SUM(e.revenue) as total_revenue
    FROM base_users u
    LEFT JOIN events e ON u.user_id = e.user_id
    GROUP BY u.user_id, u.plan_type
),

-- Step 3: Aggregate to summary level
summary AS (
    SELECT
        plan_type,
        COUNT(*) as user_count,
        AVG(session_count) as avg_sessions,
        SUM(total_revenue) as total_revenue
    FROM user_metrics
    GROUP BY plan_type
)

SELECT * FROM summary ORDER BY total_revenue DESC;

Cohort Retention

WITH cohorts AS (
    SELECT
        user_id,
        DATE_TRUNC('month', first_activity_date) as cohort_month
    FROM users
),
activity AS (
    SELECT
        user_id,
        DATE_TRUNC('month', activity_date) as activity_month
    FROM user_activity
)
SELECT
    c.cohort_month,
    COUNT(DISTINCT c.user_id) as cohort_size,
    COUNT(DISTINCT CASE
        WHEN a.activity_month = c.cohort_month THEN a.user_id
    END) as month_0,
    COUNT(DISTINCT CASE
        WHEN a.activity_month = c.cohort_month + INTERVAL '1 month' THEN a.user_id
    END) as month_1,
    COUNT(DISTINCT CASE
        WHEN a.activity_month = c.cohort_month + INTERVAL '3 months' THEN a.user_id
    END) as month_3
FROM cohorts c
LEFT JOIN activity a ON c.user_id = a.user_id
GROUP BY c.cohort_month
ORDER BY c.cohort_month;

Funnel Analysis

WITH funnel AS (
    SELECT
        user_id,
        MAX(CASE WHEN event = 'page_view' THEN 1 ELSE 0 END) as step_1_view,
        MAX(CASE WHEN event = 'signup_start' THEN 1 ELSE 0 END) as step_2_start,
        MAX(CASE WHEN event = 'signup_complete' THEN 1 ELSE 0 END) as step_3_complete,
        MAX(CASE WHEN event = 'first_purchase' THEN 1 ELSE 0 END) as step_4_purchase
    FROM events
    WHERE event_date >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY user_id
)
SELECT
    COUNT(*) as total_users,
    SUM(step_1_view) as viewed,
    SUM(step_2_start) as started_signup,
    SUM(step_3_complete) as completed_signup,
    SUM(step_4_purchase) as purchased,
    ROUND(100.0 * SUM(step_2_start) / NULLIF(SUM(step_1_view), 0), 1) as view_to_start_pct,
    ROUND(100.0 * SUM(step_3_complete) / NULLIF(SUM(step_2_start), 0), 1) as start_to_complete_pct,
    ROUND(100.0 * SUM(step_4_purchase) / NULLIF(SUM(step_3_complete), 0), 1) as complete_to_purchase_pct
FROM funnel;

Deduplication

-- Keep the most recent record per key
WITH ranked AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY entity_id
            ORDER BY updated_at DESC
        ) as rn
    FROM source_table
)
SELECT * FROM ranked WHERE rn = 1;

Error Handling and Debugging

When a query fails:

1. Syntax errors: Check for dialect-specific syntax (e.g., ILIKE not available in BigQuery, SAFE_DIVIDE only in BigQuery)
2. Column not found: Verify column names against schema -- check for typos, case sensitivity (PostgreSQL is case-sensitive for quoted identifiers)
3. Type mismatches: Cast explicitly when comparing different types (CAST(col AS DATE), col::DATE)
4. Division by zero: Use NULLIF(denominator, 0) or dialect-specific safe division
5. Ambiguous columns: Always qualify column names with table alias in JOINs
6. Group by errors: All non-aggregated columns must be in GROUP BY (except in BigQuery which allows grouping by alias)

Statistical Analysis Skill

Descriptive statistics, trend analysis, outlier detection, hypothesis testing, and guidance on when to be cautious about statistical claims.

Descriptive Statistics Methodology

Central Tendency

Choose the right measure of center based on the data:

Always report mean and median together for business metrics. If they diverge significantly, the data is skewed and the mean alone is misleading.

Spread and Variability

• Standard deviation: How far values typically fall from the mean. Use with normally distributed data.
• Interquartile range (IQR): Distance from p25 to p75. Robust to outliers. Use with skewed data.
• Coefficient of variation (CV): StdDev / Mean. Use to compare variability across metrics with different scales.
• Range: Max minus min. Sensitive to outliers but gives a quick sense of data extent.

Percentiles for Business Context

Report key percentiles to tell a richer story than mean alone:

p1:   Bottom 1% (floor / minimum typical value)
p5:   Low end of normal range
p25:  First quartile
p50:  Median (typical user)
p75:  Third quartile
p90:  Top 10% / power users
p95:  High end of normal range
p99:  Top 1% / extreme users

Example narrative: "The median session duration is 4.2 minutes, but the top 10% of users spend over 22 minutes per session, pulling the mean up to 7.8 minutes."

Describing Distributions

Characterize every numeric distribution you analyze:

• Shape: Normal, right-skewed, left-skewed, bimodal, uniform, heavy-tailed
• Center: Mean and median (and the gap between them)
• Spread: Standard deviation or IQR
• Outliers: How many and how extreme
• Bounds: Is there a natural floor (zero) or ceiling (100%)?

Trend Analysis and Forecasting

Identifying Trends

Moving averages to smooth noise:

# 7-day moving average (good for daily data with weekly seasonality)
df['ma_7d'] = df['metric'].rolling(window=7, min_periods=1).mean()

# 28-day moving average (smooths weekly AND monthly patterns)
df['ma_28d'] = df['metric'].rolling(window=28, min_periods=1).mean()

Period-over-period comparison:

• Week-over-week (WoW): Compare to same day last week
• Month-over-month (MoM): Compare to same month prior
• Year-over-year (YoY): Gold standard for seasonal businesses
• Same-day-last-year: Compare specific calendar day

Growth rates:

Simple growth: (current - previous) / previous
CAGR: (ending / beginning) ^ (1 / years) - 1
Log growth: ln(current / previous)  -- better for volatile series

Seasonality Detection

Check for periodic patterns:

1. Plot the raw time series -- visual inspection first
2. Compute day-of-week averages: is there a clear weekly pattern?
3. Compute month-of-year averages: is there an annual cycle?
4. When comparing periods, always use YoY or same-period comparisons to avoid conflating trend with seasonality

Forecasting (Simple Methods)

For business analysts (not data scientists), use straightforward methods:

• Naive forecast: Tomorrow = today. Use as a baseline.
• Seasonal naive: Tomorrow = same day last week/year.
• Linear trend: Fit a line to historical data. Only for clearly linear trends.
• Moving average forecast: Use trailing average as the forecast.

Always communicate uncertainty. Provide a range, not a point estimate:

• "We expect 10K-12K signups next month based on the 3-month trend"
• NOT "We will get exactly 11,234 signups next month"

When to escalate to a data scientist: Non-linear trends, multiple seasonalities, external factors (marketing spend, holidays), or when forecast accuracy matters for resource allocation.

Outlier and Anomaly Detection

Statistical Methods

Z-score method (for normally distributed data):

z_scores = (df['value'] - df['value'].mean()) / df['value'].std()
outliers = df[abs(z_scores) > 3]  # More than 3 standard deviations

IQR method (robust to non-normal distributions):

Q1 = df['value'].quantile(0.25)
Q3 = df['value'].quantile(0.75)
IQR = Q3 - Q1
lower_bound = Q1 - 1.5 * IQR
upper_bound = Q3 + 1.5 * IQR
outliers = df[(df['value'] < lower_bound) | (df['value'] > upper_bound)]

Percentile method (simplest):

outliers = df[(df['value'] < df['value'].quantile(0.01)) |
              (df['value'] > df['value'].quantile(0.99))]

Handling Outliers

Do NOT automatically remove outliers. Instead:

1. Investigate: Is this a data error, a genuine extreme value, or a different population?
2. Data errors: Fix or remove (e.g., negative ages, timestamps in year 1970)
3. Genuine extremes: Keep them but consider using robust statistics (median instead of mean)
4. Different population: Segment them out for separate analysis (e.g., enterprise vs. SMB customers)

Report what you did: "We excluded 47 records (0.3%) with transaction amounts >$50K, which represent bulk enterprise orders analyzed separately."

Time Series Anomaly Detection

For detecting unusual values in a time series:

1. Compute expected value (moving average or same-period-last-year)
2. Compute deviation from expected
3. Flag deviations beyond a threshold (typically 2-3 standard deviations of the residuals)
4. Distinguish between point anomalies (single unusual value) and change points (sustained shift)

Hypothesis Testing Basics

When to Use

Use hypothesis testing when you need to determine whether an observed difference is likely real or could be due to random chance. Common scenarios:

• A/B test results: Is variant B actually better than A?
• Before/after comparison: Did the product change actually move the metric?
• Segment comparison: Do enterprise customers really have higher retention?

The Framework

1. Null hypothesis (H0): There is no difference (the default assumption)
2. Alternative hypothesis (H1): There is a difference
3. Choose significance level (alpha): Typically 0.05 (5% chance of false positive)
4. Compute test statistic and p-value
5. Interpret: If p < alpha, reject H0 (evidence of a real difference)

Common Tests

Practical Significance vs. Statistical Significance

Statistical significance means the difference is unlikely due to chance.

Practical significance means the difference is large enough to matter for business decisions.

A difference can be statistically significant but practically meaningless (common with large samples). Always report:

• Effect size: How big is the difference? (e.g., "Variant B improved conversion by 0.3 percentage points")
• Confidence interval: What's the range of plausible true effects?
• Business impact: What does this translate to in revenue, users, or other business terms?

Sample Size Considerations

• Small samples produce unreliable results, even with significant p-values
• Rule of thumb for proportions: Need at least 30 events per group for basic reliability
• For detecting small effects (e.g., 1% conversion rate change), you may need thousands of observations per group
• If your sample is small, say so: "With only 200 observations per group, we have limited power to detect effects smaller than X%"

When to Be Cautious About Statistical Claims

Correlation Is Not Causation

When you find a correlation, explicitly consider:

• Reverse causation: Maybe B causes A, not A causes B
• Confounding variables: Maybe C causes both A and B
• Coincidence: With enough variables, spurious correlations are inevitable

What you can say: "Users who use feature X have 30% higher retention" What you cannot say without more evidence: "Feature X causes 30% higher retention"

Multiple Comparisons Problem

When you test many hypotheses, some will be "significant" by chance:

• Testing 20 metrics at p=0.05 means ~1 will be falsely significant
• If you looked at many segments before finding one that's different, note that
• Adjust for multiple comparisons with Bonferroni correction (divide alpha by number of tests) or report how many tests were run

Simpson's Paradox

A trend in aggregated data can reverse when data is segmented:

• Always check whether the conclusion holds across key segments
• Example: Overall conversion goes up, but conversion goes down in every segment -- because the mix shifted toward a higher-converting segment

Survivorship Bias

You can only analyze entities that "survived" to be in your dataset:

• Analyzing active users ignores those who churned
• Analyzing successful companies ignores those that failed
• Always ask: "Who is missing from this dataset, and would their inclusion change the conclusion?"

Ecological Fallacy

Aggregate trends may not apply to individuals:

• "Countries with higher X have higher Y" does NOT mean "individuals with higher X have higher Y"
• Be careful about applying group-level findings to individual cases

Anchoring on Specific Numbers

Be wary of false precision:

• "Churn will be 4.73% next quarter" implies more certainty than is warranted
• Prefer ranges: "We expect churn between 4-6% based on historical patterns"
• Round appropriately: "About 5%" is often more honest than "4.73%"

/validate-data - Validate Analysis Before Sharing

If you see unfamiliar placeholders or need to check which tools are connected, see CONNECTORS.md.

Review an analysis for accuracy, methodology, and potential biases before sharing with stakeholders. Generates a confidence assessment and improvement suggestions.

Usage

/validate-data <analysis to review>

The analysis can be:

• A document or report in the conversation
• A file (markdown, notebook, spreadsheet)
• SQL queries and their results
• Charts and their underlying data
• A description of methodology and findings

Workflow

1. Review Methodology and Assumptions

Examine:

• Question framing: Is the analysis answering the right question? Could the question be interpreted differently?
• Data selection: Are the right tables/datasets being used? Is the time range appropriate?
• Population definition: Is the analysis population correctly defined? Are there unintended exclusions?
• Metric definitions: Are metrics defined clearly and consistently? Do they match how stakeholders understand them?
• Baseline and comparison: Is the comparison fair? Are time periods, cohort sizes, and contexts comparable?

2. Run the Pre-Delivery QA Checklist

Work through the checklist below — data quality, calculation, reasonableness, and presentation checks.

3. Check for Common Analytical Pitfalls

Systematically review against the detailed pitfall catalog below (join explosion, survivorship bias, incomplete period comparison, denominator shifting, average of averages, timezone mismatches, selection bias).

4. Verify Calculations and Aggregations

Where possible, spot-check:

• Recalculate a few key numbers independently
• Verify that subtotals sum to totals
• Check that percentages sum to 100% (or close to it) where expected
• Confirm that YoY/MoM comparisons use the correct base periods
• Validate that filters are applied consistently across all metrics

Apply the result sanity-checking techniques below (magnitude checks, cross-validation, red-flag detection).

5. Assess Visualizations

If the analysis includes charts:

• Do axes start at appropriate values (zero for bar charts)?
• Are scales consistent across comparison charts?
• Do chart titles accurately describe what's shown?
• Could the visualization mislead a quick reader?
• Are there truncated axes, inconsistent intervals, or 3D effects that distort perception?

6. Evaluate Narrative and Conclusions

Review whether:

• Conclusions are supported by the data shown
• Alternative explanations are acknowledged
• Uncertainty is communicated appropriately
• Recommendations follow logically from findings
• The level of confidence matches the strength of evidence

7. Suggest Improvements

Provide specific, actionable suggestions:

• Additional analyses that would strengthen the conclusions
• Caveats or limitations that should be noted
• Better visualizations or framings for key points
• Missing context that stakeholders would want

8. Generate Confidence Assessment

Rate the analysis on a 3-level scale:

Ready to share -- Analysis is methodologically sound, calculations verified, caveats noted. Minor suggestions for improvement but nothing blocking.

Share with noted caveats -- Analysis is largely correct but has specific limitations or assumptions that must be communicated to stakeholders. List the required caveats.

Needs revision -- Found specific errors, methodological issues, or missing analyses that should be addressed before sharing. List the required changes with priority order.

Output Format

## Validation Report

### Overall Assessment: [Ready to share | Share with caveats | Needs revision]

### Methodology Review
[Findings about approach, data selection, definitions]

### Issues Found
1. [Severity: High/Medium/Low] [Issue description and impact]
2. ...

### Calculation Spot-Checks
- [Metric]: [Verified / Discrepancy found]
- ...

### Visualization Review
[Any issues with charts or visual presentation]

### Suggested Improvements
1. [Improvement and why it matters]
2. ...

### Required Caveats for Stakeholders
- [Caveat that must be communicated]
- ...

Pre-Delivery QA Checklist

Run through this checklist before sharing any analysis with stakeholders.

Data Quality Checks

• Source verification: Confirmed which tables/data sources were used. Are they the right ones for this question?
• Freshness: Data is current enough for the analysis. Noted the "as of" date.
• Completeness: No unexpected gaps in time series or missing segments.
• Null handling: Checked null rates in key columns. Nulls are handled appropriately (excluded, imputed, or flagged).
• Deduplication: Confirmed no double-counting from bad joins or duplicate source records.
• Filter verification: All WHERE clauses and filters are correct. No unintended exclusions.

Calculation Checks

• Aggregation logic: GROUP BY includes all non-aggregated columns. Aggregation level matches the analysis grain.
• Denominator correctness: Rate and percentage calculations use the right denominator. Denominators are non-zero.
• Date alignment: Comparisons use the same time period length. Partial periods are excluded or noted.
• Join correctness: JOIN types are appropriate (INNER vs LEFT). Many-to-many joins haven't inflated counts.
• Metric definitions: Metrics match how stakeholders define them. Any deviations are noted.
• Subtotals sum: Parts add up to the whole where expected. If they don't, explain why (e.g., overlap).

Reasonableness Checks

• Magnitude: Numbers are in a plausible range. Revenue isn't negative. Percentages are between 0-100%.
• Trend continuity: No unexplained jumps or drops in time series.
• Cross-reference: Key numbers match other known sources (dashboards, previous reports, finance data).
• Order of magnitude: Total revenue is in the right ballpark. User counts match known figures.
• Edge cases: What happens at the boundaries? Empty segments, zero-activity periods, new entities.

Presentation Checks

• Chart accuracy: Bar charts start at zero. Axes are labeled. Scales are consistent across panels.
• Number formatting: Appropriate precision. Consistent currency/percentage formatting. Thousands separators where needed.
• Title clarity: Titles state the insight, not just the metric. Date ranges are specified.
• Caveat transparency: Known limitations and assumptions are stated explicitly.
• Reproducibility: Someone else could recreate this analysis from the documentation provided.

Common Data Analysis Pitfalls

Join Explosion

The problem: A many-to-many join silently multiplies rows, inflating counts and sums.

How to detect:

-- Check row count before and after join
SELECT COUNT(*) FROM table_a;  -- 1,000
SELECT COUNT(*) FROM table_a a JOIN table_b b ON a.id = b.a_id;  -- 3,500 (uh oh)

How to prevent:

• Always check row counts after joins
• If counts increase, investigate the join relationship (is it really 1:1 or 1:many?)
• Use COUNT(DISTINCT a.id) instead of COUNT(*) when counting entities through joins

Survivorship Bias

The problem: Analyzing only entities that exist today, ignoring those that were deleted, churned, or failed.

Examples:

• Analyzing user behavior of "current users" misses churned users
• Looking at "companies using our product" ignores those who evaluated and left
• Studying properties of "successful" outcomes without "unsuccessful" ones

How to prevent: Ask "who is NOT in this dataset?" before drawing conclusions.

Incomplete Period Comparison

The problem: Comparing a partial period to a full period.

Examples:

• "January revenue is $500K vs. December's $800K" -- but January isn't over yet
• "This week's signups are down" -- checked on Wednesday, comparing to a full prior week

How to prevent: Always filter to complete periods, or compare same-day-of-month / same-number-of-days.

Denominator Shifting

The problem: The denominator changes between periods, making rates incomparable.

Examples:

• Conversion rate improves because you changed how you count "eligible" users
• Churn rate changes because the definition of "active" was updated

How to prevent: Use consistent definitions across all compared periods. Note any definition changes.

Average of Averages

The problem: Averaging pre-computed averages gives wrong results when group sizes differ.

Example:

• Group A: 100 users, average revenue $50
• Group B: 10 users, average revenue $200
• Wrong: Average of averages = ($50 + $200) / 2 = $125
• Right: Weighted average = (100*$50 + 10*$200) / 110 = $63.64

How to prevent: Always aggregate from raw data. Never average pre-aggregated averages.

Timezone Mismatches

The problem: Different data sources use different timezones, causing misalignment.

Examples:

• Event timestamps in UTC vs. user-facing dates in local time
• Daily rollups that use different cutoff times

How to prevent: Standardize all timestamps to a single timezone (UTC recommended) before analysis. Document the timezone used.

Selection Bias in Segmentation

The problem: Segments are defined by the outcome you're measuring, creating circular logic.

Examples:

• "Users who completed onboarding have higher retention" -- obviously, they self-selected
• "Power users generate more revenue" -- they became power users BY generating revenue

How to prevent: Define segments based on pre-treatment characteristics, not outcomes.

Other Statistical Traps

• Simpson's paradox: Trend reverses when data is aggregated vs. segmented
• Correlation presented as causation without supporting evidence
• Small sample sizes leading to unreliable conclusions
• Outliers disproportionately affecting averages (should medians be used instead?)
• Multiple testing / cherry-picking significant results
• Look-ahead bias: Using future information to explain past events
• Cherry-picked time ranges that favor a particular narrative

Result Sanity Checking

Magnitude Checks

For any key number in your analysis, verify it passes the "smell test":

Cross-Validation Techniques

1. Calculate the same metric two different ways and verify they match
2. Spot-check individual records -- pick a few specific entities and trace their data manually
3. Compare to known benchmarks -- match against published dashboards, finance reports, or prior analyses
4. Reverse engineer -- if total revenue is X, does per-user revenue times user count approximately equal X?
5. Boundary checks -- what happens when you filter to a single day, a single user, or a single category? Are those micro-results sensible?

Red Flags That Warrant Investigation

• Any metric that changed by more than 50% period-over-period without an obvious cause
• Counts or sums that are exact round numbers (suggests a filter or default value issue)
• Rates exactly at 0% or 100% (may indicate incomplete data)
• Results that perfectly confirm the hypothesis (reality is usually messier)
• Identical values across time periods or segments (suggests the query is ignoring a dimension)

Documentation Standards for Reproducibility

Analysis Documentation Template

Every non-trivial analysis should include:

## Analysis: [Title]

### Question
[The specific question being answered]

### Data Sources
- Table: [schema.table_name] (as of [date])
- Table: [schema.other_table] (as of [date])
- File: [filename] (source: [where it came from])

### Definitions
- [Metric A]: [Exactly how it's calculated]
- [Segment X]: [Exactly how membership is determined]
- [Time period]: [Start date] to [end date], [timezone]

### Methodology
1. [Step 1 of the analysis approach]
2. [Step 2]
3. [Step 3]

### Assumptions and Limitations
- [Assumption 1 and why it's reasonable]
- [Limitation 1 and its potential impact on conclusions]

### Key Findings
1. [Finding 1 with supporting evidence]
2. [Finding 2 with supporting evidence]

### SQL Queries
[All queries used, with comments]

### Caveats
- [Things the reader should know before acting on this]

Code Documentation

For any code (SQL, Python) that may be reused:

"""
Analysis: Monthly Cohort Retention
Author: [Name]
Date: [Date]
Data Source: events table, users table
Last Validated: [Date] -- results matched dashboard within 2%

Purpose:
    Calculate monthly user retention cohorts based on first activity date.

Assumptions:
    - "Active" means at least one event in the month
    - Excludes test/internal accounts (user_type != 'internal')
    - Uses UTC dates throughout

Output:
    Cohort retention matrix with cohort_month rows and months_since_signup columns.
    Values are retention rates (0-100%).
"""

Version Control for Analyses

• Save queries and code in version control (git) or a shared docs system
• Note the date of the data snapshot used
• If an analysis is re-run with updated data, document what changed and why
• Link to prior versions of recurring analyses for trend comparison

Examples

/validate-data Review this quarterly revenue analysis before I send it to the exec team: [analysis]

/validate-data Check my churn analysis -- I'm comparing Q4 churn rates to Q3 but Q4 has a shorter measurement window

/validate-data Here's a SQL query and its results for our conversion funnel. Does the logic look right? [query + results]

Tips

• Run /validate-data before any high-stakes presentation or decision
• Even quick analyses benefit from a sanity check -- it takes a minute and can save your credibility
• If the validation finds issues, fix them and re-validate
• Share the validation output alongside your analysis to build stakeholder confidence

/write-query - Write Optimized SQL

If you see unfamiliar placeholders or need to check which tools are connected, see CONNECTORS.md.

Write a SQL query from a natural language description, optimized for your specific SQL dialect and following best practices.

Usage

/write-query <description of what data you need>

Workflow

1. Understand the Request

Parse the user's description to identify:

• Output columns: What fields should the result include?
• Filters: What conditions limit the data (time ranges, segments, statuses)?
• Aggregations: Are there GROUP BY operations, counts, sums, averages?
• Joins: Does this require combining multiple tables?
• Ordering: How should results be sorted?
• Limits: Is there a top-N or sample requirement?

2. Determine SQL Dialect

If the user's SQL dialect is not already known, ask which they use:

• PostgreSQL (including Aurora, RDS, Supabase, Neon)
• Snowflake
• BigQuery (Google Cloud)
• Redshift (Amazon)
• Databricks SQL
• MySQL (including Aurora MySQL, PlanetScale)
• SQL Server (Microsoft)
• DuckDB
• SQLite
• Other (ask for specifics)

Remember the dialect for future queries in the same session.

3. Discover Schema (If Warehouse Connected)

If a data warehouse MCP server is connected:

1. Search for relevant tables based on the user's description
2. Inspect column names, types, and relationships
3. Check for partitioning or clustering keys that affect performance
4. Look for pre-built views or materialized views that might simplify the query

4. Write the Query

Follow these best practices:

Structure:

• Use CTEs (WITH clauses) for readability when queries have multiple logical steps
• One CTE per logical transformation or data source
• Name CTEs descriptively (e.g., daily_signups, active_users, revenue_by_product)

Performance:

• Never use SELECT * in production queries -- specify only needed columns
• Filter early (push WHERE clauses as close to the base tables as possible)
• Use partition filters when available (especially date partitions)
• Prefer EXISTS over IN for subqueries with large result sets
• Use appropriate JOIN types (don't use LEFT JOIN when INNER JOIN is correct)
• Avoid correlated subqueries when a JOIN or window function works
• Be mindful of exploding joins (many-to-many)

Readability:

• Add comments explaining the "why" for non-obvious logic
• Use consistent indentation and formatting
• Alias tables with meaningful short names (not just a, b, c)
• Put each major clause on its own line

Dialect-specific optimizations:

• Apply dialect-specific syntax and functions (see sql-queries skill for details)
• Use dialect-appropriate date functions, string functions, and window syntax
• Note any dialect-specific performance features (e.g., Snowflake clustering, BigQuery partitioning)

5. Present the Query

Provide:

1. The complete query in a SQL code block with syntax highlighting
2. Brief explanation of what each CTE or section does
3. Performance notes if relevant (expected cost, partition usage, potential bottlenecks)
4. Modification suggestions -- how to adjust for common variations (different time range, different granularity, additional filters)

6. Offer to Execute

If a data warehouse is connected, offer to run the query and analyze the results. If the user wants to run it themselves, the query is ready to copy-paste.

Examples

Simple aggregation:

/write-query Count of orders by status for the last 30 days

Complex analysis:

/write-query Cohort retention analysis -- group users by their signup month, then show what percentage are still active (had at least one event) at 1, 3, 6, and 12 months after signup

Performance-critical:

/write-query We have a 500M row events table partitioned by date. Find the top 100 users by event count in the last 7 days with their most recent event type.

Tips

• Mention your SQL dialect upfront to get the right syntax immediately
• If you know the table names, include them -- otherwise Claude will help you find them
• Specify if you need the query to be idempotent (safe to re-run) or one-time
• For recurring queries, mention if it should be parameterized for date ranges

/accessibility-review

If you see unfamiliar placeholders or need to check which tools are connected, see CONNECTORS.md.

Audit a design or page for WCAG 2.1 AA accessibility compliance.

Usage

/accessibility-review $ARGUMENTS

Audit for accessibility: @$1

WCAG 2.1 AA Quick Reference

Perceivable

• 1.1.1 Non-text content has alt text
• 1.3.1 Info and structure conveyed semantically
• 1.4.3 Contrast ratio >= 4.5:1 (normal text), >= 3:1 (large text)
• 1.4.11 Non-text contrast >= 3:1 (UI components, graphics)

Operable

• 2.1.1 All functionality available via keyboard
• 2.4.3 Logical focus order
• 2.4.7 Visible focus indicator
• 2.5.5 Touch target >= 44x44 CSS pixels

Understandable

• 3.2.1 Predictable on focus (no unexpected changes)
• 3.3.1 Error identification (describe the error)
• 3.3.2 Labels or instructions for inputs

Robust

• 4.1.2 Name, role, value for all UI components

Common Issues

1. Insufficient color contrast
2. Missing form labels
3. No keyboard access to interactive elements
4. Missing alt text on meaningful images
5. Focus traps in modals
6. Missing ARIA landmarks
7. Auto-playing media without controls
8. Time limits without extension options

Testing Approach

1. Automated scan (catches ~30% of issues)
2. Keyboard-only navigation
3. Screen reader testing (VoiceOver, NVDA)
4. Color contrast verification
5. Zoom to 200% — does layout break?

Output

## Accessibility Audit: [Design/Page Name]
**Standard:** WCAG 2.1 AA | **Date:** [Date]

### Summary
**Issues found:** [X] | **Critical:** [X] | **Major:** [X] | **Minor:** [X]

### Findings

#### Perceivable
| # | Issue | WCAG Criterion | Severity | Recommendation |
|---|-------|---------------|----------|----------------|
| 1 | [Issue] | [1.4.3 Contrast] | 🔴 Critical | [Fix] |

#### Operable
| # | Issue | WCAG Criterion | Severity | Recommendation |
|---|-------|---------------|----------|----------------|
| 1 | [Issue] | [2.1.1 Keyboard] | 🟡 Major | [Fix] |

#### Understandable
| # | Issue | WCAG Criterion | Severity | Recommendation |
|---|-------|---------------|----------|----------------|
| 1 | [Issue] | [3.3.2 Labels] | 🟢 Minor | [Fix] |

#### Robust
| # | Issue | WCAG Criterion | Severity | Recommendation |
|---|-------|---------------|----------|----------------|
| 1 | [Issue] | [4.1.2 Name, Role, Value] | 🟡 Major | [Fix] |

### Color Contrast Check
| Element | Foreground | Background | Ratio | Required | Pass? |
|---------|-----------|------------|-------|----------|-------|
| [Body text] | [color] | [color] | [X]:1 | 4.5:1 | ✅/❌ |

### Keyboard Navigation
| Element | Tab Order | Enter/Space | Escape | Arrow Keys |
|---------|-----------|-------------|--------|------------|
| [Element] | [Order] | [Behavior] | [Behavior] | [Behavior] |

### Screen Reader
| Element | Announced As | Issue |
|---------|-------------|-------|
| [Element] | [What SR says] | [Problem if any] |

### Priority Fixes
1. **[Critical fix]** — Affects [who] and blocks [what]
2. **[Major fix]** — Improves [what] for [who]
3. **[Minor fix]** — Nice to have

If Connectors Available

If ~~design tool is connected:

• Inspect color values, font sizes, and touch targets directly from Figma
• Check component ARIA roles and keyboard behavior in the design spec

If ~~project tracker is connected:

• Create tickets for each accessibility finding with severity and WCAG criterion
• Link findings to existing accessibility remediation epics

Tips

1. Start with contrast and keyboard — These catch the most common and impactful issues.
2. Test with real assistive technology — My audit is a great start, but manual testing with VoiceOver/NVDA catches things I can't.
3. Prioritize by impact — Fix issues that block users first, polish later.

/design-critique

If you see unfamiliar placeholders or need to check which tools are connected, see CONNECTORS.md.

Get structured design feedback across multiple dimensions.

Usage

/design-critique $ARGUMENTS

Review the design: @$1

If a Figma URL is provided, pull the design from Figma. If a file is referenced, read it. Otherwise, ask the user to describe or share their design.

What I Need From You

• The design: Figma URL, screenshot, or detailed description
• Context: What is this? Who is it for? What stage (exploration, refinement, final)?
• Focus (optional): "Focus on mobile" or "Focus on the onboarding flow"

Critique Framework

1. First Impression (2 seconds)

• What draws the eye first? Is that correct?
• What's the emotional reaction?
• Is the purpose immediately clear?

2. Usability

• Can the user accomplish their goal?
• Is the navigation intuitive?
• Are interactive elements obvious?
• Are there unnecessary steps?

3. Visual Hierarchy

• Is there a clear reading order?
• Are the right elements emphasized?
• Is whitespace used effectively?
• Is typography creating the right hierarchy?

4. Consistency

• Does it follow the design system?
• Are spacing, colors, and typography consistent?
• Do similar elements behave similarly?

5. Accessibility

• Color contrast ratios
• Touch target sizes
• Text readability
• Alternative text for images

How to Give Feedback

• Be specific: "The CTA competes with the navigation" not "the layout is confusing"
• Explain why: Connect feedback to design principles or user needs
• Suggest alternatives: Don't just identify problems, propose solutions
• Acknowledge what works: Good feedback includes positive observations
• Match the stage: Early exploration gets different feedback than final polish

Output

## Design Critique: [Design Name]

### Overall Impression
[1-2 sentence first reaction — what works, what's the biggest opportunity]

### Usability
| Finding | Severity | Recommendation |
|---------|----------|----------------|
| [Issue] | 🔴 Critical / 🟡 Moderate / 🟢 Minor | [Fix] |

### Visual Hierarchy
- **What draws the eye first**: [Element] — [Is this correct?]
- **Reading flow**: [How does the eye move through the layout?]
- **Emphasis**: [Are the right things emphasized?]

### Consistency
| Element | Issue | Recommendation |
|---------|-------|----------------|
| [Typography/spacing/color] | [Inconsistency] | [Fix] |

### Accessibility
- **Color contrast**: [Pass/fail for key text]
- **Touch targets**: [Adequate size?]
- **Text readability**: [Font size, line height]

### What Works Well
- [Positive observation 1]
- [Positive observation 2]

### Priority Recommendations
1. **[Most impactful change]** — [Why and how]
2. **[Second priority]** — [Why and how]
3. **[Third priority]** — [Why and how]

If Connectors Available

If ~~design tool is connected:

• Pull the design directly from Figma and inspect components, tokens, and layers
• Compare against the existing design system for consistency

If ~~user feedback is connected:

• Cross-reference design decisions with recent user feedback and support tickets

Tips

1. Share the context — "This is a checkout flow for a B2B SaaS" helps me give relevant feedback.
2. Specify your stage — Early exploration gets different feedback than final polish.
3. Ask me to focus — "Just look at the navigation" gives you more depth on one area.

/design-handoff

If you see unfamiliar placeholders or need to check which tools are connected, see CONNECTORS.md.

Generate comprehensive developer handoff documentation from a design.

Usage

/design-handoff $ARGUMENTS

Generate handoff specs for: @$1

If a Figma URL is provided, pull the design from Figma. Otherwise, work from the provided description or screenshot.

What to Include

Visual Specifications

• Exact measurements (padding, margins, widths)
• Design token references (colors, typography, spacing)
• Responsive breakpoints and behavior
• Component variants and states

Interaction Specifications

• Click/tap behavior
• Hover states
• Transitions and animations (duration, easing)
• Gesture support (swipe, pinch, long-press)

Content Specifications

• Character limits
• Truncation behavior
• Empty states
• Loading states
• Error states

Edge Cases

• Minimum/maximum content
• International text (longer strings)
• Slow connections
• Missing data

Accessibility

• Focus order
• ARIA labels and roles
• Keyboard interactions
• Screen reader announcements

Principles

1. Don't assume — If it's not specified, the developer will guess. Specify everything.
2. Use tokens, not values — Reference spacing-md not 16px.
3. Show all states — Default, hover, active, disabled, loading, error, empty.
4. Describe the why — "This collapses on mobile because users primarily use one-handed" helps developers make good judgment calls.

Output

## Handoff Spec: [Feature/Screen Name]

### Overview
[What this screen/feature does, user context]

### Layout
[Grid system, breakpoints, responsive behavior]

### Design Tokens Used
| Token | Value | Usage |
|-------|-------|-------|
| `color-primary` | #[hex] | CTA buttons, links |
| `spacing-md` | [X]px | Between sections |
| `font-heading-lg` | [size/weight/family] | Page title |

### Components
| Component | Variant | Props | Notes |
|-----------|---------|-------|-------|
| [Component] | [Variant] | [Props] | [Special behavior] |

### States and Interactions
| Element | State | Behavior |
|---------|-------|----------|
| [CTA Button] | Hover | [Background darken 10%] |
| [CTA Button] | Loading | [Spinner, disabled] |
| [Form] | Error | [Red border, error message below] |

### Responsive Behavior
| Breakpoint | Changes |
|------------|---------|
| Desktop (>1024px) | [Default layout] |
| Tablet (768-1024px) | [What changes] |
| Mobile (<768px) | [What changes] |

### Edge Cases
- **Empty state**: [What to show when no data]
- **Long text**: [Truncation rules]
- **Loading**: [Skeleton or spinner]
- **Error**: [Error state appearance]

### Animation / Motion
| Element | Trigger | Animation | Duration | Easing |
|---------|---------|-----------|----------|--------|
| [Element] | [Trigger] | [Description] | [ms] | [easing] |

### Accessibility Notes
- [Focus order]
- [ARIA labels needed]
- [Keyboard interactions]

If Connectors Available

If ~~design tool is connected:

• Pull exact measurements, tokens, and component specs from Figma
• Export assets and generate a complete spec sheet

If ~~project tracker is connected:

• Link the handoff to the implementation ticket
• Create sub-tasks for each section of the spec

Tips

1. Share the Figma link — I can pull exact measurements, tokens, and component info.
2. Mention edge cases — "What happens with 100 items?" helps me spec boundary conditions.
3. Specify the tech stack — "We use React + Tailwind" helps me give relevant implementation notes.

/design-system

If you see unfamiliar placeholders or need to check which tools are connected, see CONNECTORS.md.

Manage your design system — audit for consistency, document components, or design new patterns.

Usage

/design-system audit                    # Full system audit
/design-system document [component]     # Document a component
/design-system extend [pattern]         # Design a new component or pattern

Components of a Design System

Design Tokens

Atomic values that define the visual language:

• Colors (brand, semantic, neutral)
• Typography (scale, weights, line heights)
• Spacing (scale, component padding)
• Borders (radius, width)
• Shadows (elevation levels)
• Motion (durations, easings)

Components

Reusable UI elements with defined:

• Variants (primary, secondary, ghost)
• States (default, hover, active, disabled, loading, error)
• Sizes (sm, md, lg)
• Behavior (interactions, animations)
• Accessibility (ARIA, keyboard)

Patterns

Common UI solutions combining components:

• Forms (input groups, validation, submission)
• Navigation (sidebar, tabs, breadcrumbs)
• Data display (tables, cards, lists)
• Feedback (toasts, modals, inline messages)

Principles

1. Consistency over creativity — The system exists so teams don't reinvent the wheel
2. Flexibility within constraints — Components should be composable, not rigid
3. Document everything — If it's not documented, it doesn't exist
4. Version and migrate — Breaking changes need migration paths

Output — Audit

## Design System Audit

### Summary
**Components reviewed:** [X] | **Issues found:** [X] | **Score:** [X/100]

### Naming Consistency
| Issue | Components | Recommendation |
|-------|------------|----------------|
| [Inconsistent naming] | [List] | [Standard to adopt] |

### Token Coverage
| Category | Defined | Hardcoded Values Found |
|----------|---------|----------------------|
| Colors | [X] | [X] instances of hardcoded hex |
| Spacing | [X] | [X] instances of arbitrary values |
| Typography | [X] | [X] instances of custom fonts/sizes |

### Component Completeness
| Component | States | Variants | Docs | Score |
|-----------|--------|----------|------|-------|
| Button | ✅ | ✅ | ⚠️ | 8/10 |
| Input | ✅ | ⚠️ | ❌ | 5/10 |

### Priority Actions
1. [Most impactful improvement]
2. [Second priority]
3. [Third priority]

Output — Document

## Component: [Name]

### Description
[What this component is and when to use it]

### Variants
| Variant | Use When |
|---------|----------|
| [Primary] | [Main actions] |
| [Secondary] | [Supporting actions] |

### Props / Properties
| Property | Type | Default | Description |
|----------|------|---------|-------------|
| [prop] | [type] | [default] | [description] |

### States
| State | Visual | Behavior |
|-------|--------|----------|
| Default | [description] | — |
| Hover | [description] | [interaction] |
| Active | [description] | [interaction] |
| Disabled | [description] | Non-interactive |
| Loading | [description] | [animation] |

### Accessibility
- **Role**: [ARIA role]
- **Keyboard**: [Tab, Enter, Escape behavior]
- **Screen reader**: [Announced as...]

### Do's and Don'ts
| ✅ Do | ❌ Don't |
|------|---------|
| [Best practice] | [Anti-pattern] |

### Code Example
[Framework-appropriate code snippet]

Output — Extend

## New Component: [Name]

### Problem
[What user need or gap this component addresses]

### Existing Patterns
| Related Component | Similarity | Why It's Not Enough |
|-------------------|-----------|---------------------|
| [Component] | [What's shared] | [What's missing] |

### Proposed Design

#### API / Props
| Property | Type | Default | Description |
|----------|------|---------|-------------|
| [prop] | [type] | [default] | [description] |

#### Variants
| Variant | Use When | Visual |
|---------|----------|--------|
| [Variant] | [Scenario] | [Description] |

#### States
| State | Behavior | Notes |
|-------|----------|-------|
| Default | [Description] | — |
| Hover | [Description] | [Interaction] |
| Disabled | [Description] | Non-interactive |
| Loading | [Description] | [Animation] |

#### Tokens Used
- Colors: [Which tokens]
- Spacing: [Which tokens]
- Typography: [Which tokens]

### Accessibility
- **Role**: [ARIA role]
- **Keyboard**: [Expected interactions]
- **Screen reader**: [Announced as...]

### Open Questions
- [Decision that needs design review]
- [Edge case to resolve]

If Connectors Available

If ~~design tool is connected:

• Audit components directly in Figma — check naming, variants, and token usage
• Pull component properties and layer structure for documentation

If ~~knowledge base is connected:

• Search for existing component documentation and usage guidelines
• Publish updated documentation to your wiki

Tips

1. Start with an audit — Know where you are before deciding where to go.
2. Document as you build — It's easier to document a component while designing it.
3. Prioritize coverage over perfection — 80% of components documented beats 100% of 10 components.

/research-synthesis

If you see unfamiliar placeholders or need to check which tools are connected, see CONNECTORS.md.

Synthesize user research data into actionable insights. See the user-research skill for research methods, interview guides, and analysis frameworks.

Usage

/research-synthesis $ARGUMENTS

What I Accept

• Interview transcripts or notes
• Survey results (CSV, pasted data)
• Usability test recordings or notes
• Support tickets or feedback
• NPS/CSAT responses
• App store reviews

Output

## Research Synthesis: [Study Name]
**Method:** [Interviews / Survey / Usability Test] | **Participants:** [X]
**Date:** [Date range] | **Researcher:** [Name]

### Executive Summary
[3-4 sentence overview of key findings]

### Key Themes

#### Theme 1: [Name]
**Prevalence:** [X of Y participants]
**Summary:** [What this theme is about]
**Supporting Evidence:**
- "[Quote]" — P[X]
- "[Quote]" — P[X]
**Implication:** [What this means for the product]

#### Theme 2: [Name]
[Same format]

### Insights → Opportunities

| Insight | Opportunity | Impact | Effort |
|---------|-------------|--------|--------|
| [What we learned] | [What we could do] | High/Med/Low | High/Med/Low |

### User Segments Identified
| Segment | Characteristics | Needs | Size |
|---------|----------------|-------|------|
| [Name] | [Description] | [Key needs] | [Rough %] |

### Recommendations
1. **[High priority]** — [Why, based on which findings]
2. **[Medium priority]** — [Why]
3. **[Lower priority]** — [Why]

### Questions for Further Research
- [What we still don't know]

### Methodology Notes
[How the research was conducted, any limitations or biases to note]

If Connectors Available

If ~~user feedback is connected:

• Pull support tickets, feature requests, and NPS responses to supplement research data
• Cross-reference themes with real user complaints and requests

If ~~product analytics is connected:

• Validate qualitative findings with usage data and behavioral metrics
• Quantify the impact of identified pain points

If ~~knowledge base is connected:

• Search for prior research studies and findings to compare against
• Publish the synthesis to your research repository

Tips

1. Include raw quotes — Direct participant quotes make insights credible and memorable.
2. Separate observations from interpretations — "5 of 8 users clicked the wrong button" is an observation. "The button placement is confusing" is an interpretation.
3. Quantify where possible — "Most users" is vague. "7 of 10 users" is specific.

User Research

Help plan, execute, and synthesize user research studies.

Research Methods

Interview Guide Structure

1. Warm-up (5 min): Build rapport, explain the session
2. Context (10 min): Understand their current workflow
3. Deep dive (20 min): Explore the specific topic
4. Reaction (10 min): Show concepts or prototypes
5. Wrap-up (5 min): Anything we missed? Thank them.

Analysis Framework

• Affinity mapping: Group observations into themes
• Impact/effort matrix: Prioritize findings
• Journey mapping: Visualize the user experience over time
• Jobs to be done: Understand what users are hiring your product to do

Deliverables

• Research plan (objectives, methods, timeline, participants)
• Interview guide (questions, probes, activities)
• Synthesis report (themes, insights, recommendations)
• Highlight reel (key quotes and observations)

/ux-copy

If you see unfamiliar placeholders or need to check which tools are connected, see CONNECTORS.md.

Write or review UX copy for any interface context.

Usage

/ux-copy $ARGUMENTS

What I Need From You

• Context: What screen, flow, or feature?
• User state: What is the user trying to do? How are they feeling?
• Tone: Formal, friendly, playful, reassuring?
• Constraints: Character limits, platform guidelines?

Principles

1. Clear: Say exactly what you mean. No jargon, no ambiguity.
2. Concise: Use the fewest words that convey the full meaning.
3. Consistent: Same terms for the same things everywhere.
4. Useful: Every word should help the user accomplish their goal.
5. Human: Write like a helpful person, not a robot.

Copy Patterns

CTAs

• Start with a verb: "Start free trial", "Save changes", "Download report"
• Be specific: "Create account" not "Submit"
• Match the outcome to the label

Error Messages

Structure: What happened + Why + How to fix

• "Payment declined. Your card was declined by your bank. Try a different card or contact your bank."

Empty States

Structure: What this is + Why it's empty + How to start

• "No projects yet. Create your first project to start collaborating with your team."

Confirmation Dialogs

• Make the action clear: "Delete 3 files?" not "Are you sure?"
• Describe consequences: "This can't be undone"
• Label buttons with the action: "Delete files" / "Keep files" not "OK" / "Cancel"

Tooltips

• Concise, helpful, never obvious

Loading States

• Set expectations, reduce anxiety

Onboarding

• Progressive disclosure, one concept at a time

Voice and Tone

Adapt tone to context:

• Success: Celebratory but not over the top
• Error: Empathetic and helpful
• Warning: Clear and actionable
• Neutral: Informative and concise

Output

## UX Copy: [Context]

### Recommended Copy
**[Element]**: [Copy]

### Alternatives
| Option | Copy | Tone | Best For |
|--------|------|------|----------|
| A | [Copy] | [Tone] | [When to use] |
| B | [Copy] | [Tone] | [When to use] |
| C | [Copy] | [Tone] | [When to use] |

### Rationale
[Why this copy works — user context, clarity, action-orientation]

### Localization Notes
[Anything translators should know — idioms to avoid, character expansion, cultural context]

If Connectors Available

If ~~knowledge base is connected:

• Pull your brand voice guidelines and content style guide
• Check for existing copy patterns and terminology standards

If ~~design tool is connected:

• View the screen context in Figma to understand the full user flow
• Check character limits and layout constraints from the design

Tips

1. Be specific about context — "Error message when payment fails" is better than "error message."
2. Share your brand voice — "We're professional but warm" helps me match your tone.
3. Consider the user's emotional state — Error messages need empathy. Success messages can celebrate.

/architecture

If you see unfamiliar placeholders or need to check which tools are connected, see CONNECTORS.md.

Create an Architecture Decision Record (ADR) or evaluate a system design.

Usage

/architecture $ARGUMENTS

Modes

Create an ADR: "Should we use Kafka or SQS for our event bus?" Evaluate a design: "Review this microservices proposal" System design: "Design the notification system for our app"

See the system-design skill for detailed frameworks on requirements gathering, scalability analysis, and trade-off evaluation.

Output — ADR Format

# ADR-[number]: [Title]

**Status:** Proposed | Accepted | Deprecated | Superseded
**Date:** [Date]
**Deciders:** [Who needs to sign off]

## Context
[What is the situation? What forces are at play?]

## Decision
[What is the change we're proposing?]

## Options Considered

### Option A: [Name]
| Dimension | Assessment |
|-----------|------------|
| Complexity | [Low/Med/High] |
| Cost | [Assessment] |
| Scalability | [Assessment] |
| Team familiarity | [Assessment] |

**Pros:** [List]
**Cons:** [List]

### Option B: [Name]
[Same format]

## Trade-off Analysis
[Key trade-offs between options with clear reasoning]

## Consequences
- [What becomes easier]
- [What becomes harder]
- [What we'll need to revisit]

## Action Items
1. [ ] [Implementation step]
2. [ ] [Follow-up]

If Connectors Available

If ~~knowledge base is connected:

• Search for prior ADRs and design docs
• Find relevant technical context

If ~~project tracker is connected:

• Link to related epics and tickets
• Create implementation tasks

Tips

1. State constraints upfront — "We need to ship in 2 weeks" or "Must handle 10K rps" shapes the answer.
2. Name your options — Even if you're leaning one way, I'll give a more balanced analysis with explicit alternatives.
3. Include non-functional requirements — Latency, cost, team expertise, and maintenance burden matter as much as features.

/code-review

If you see unfamiliar placeholders or need to check which tools are connected, see CONNECTORS.md.

Review code changes with a structured lens on security, performance, correctness, and maintainability.

Usage

/code-review <PR URL or file path>

Review the provided code changes: @$1

If no specific file or URL is provided, ask what to review.

How It Works

┌─────────────────────────────────────────────────────────────────┐
│                      CODE REVIEW                                   │
├─────────────────────────────────────────────────────────────────┤
│  STANDALONE (always works)                                       │
│  ✓ Paste a diff, PR URL, or point to files                      │
│  ✓ Security audit (OWASP top 10, injection, auth)               │
│  ✓ Performance review (N+1, memory leaks, complexity)           │
│  ✓ Correctness (edge cases, error handling, race conditions)    │
│  ✓ Style (naming, structure, readability)                        │
│  ✓ Actionable suggestions with code examples                    │
├─────────────────────────────────────────────────────────────────┤
│  SUPERCHARGED (when you connect your tools)                      │
│  + Source control: Pull PR diff automatically                    │
│  + Project tracker: Link findings to tickets                     │
│  + Knowledge base: Check against team coding standards           │
└─────────────────────────────────────────────────────────────────┘

Review Dimensions

Security

• SQL injection, XSS, CSRF
• Authentication and authorization flaws
• Secrets or credentials in code
• Insecure deserialization
• Path traversal
• SSRF

Performance

• N+1 queries
• Unnecessary memory allocations
• Algorithmic complexity (O(n²) in hot paths)
• Missing database indexes
• Unbounded queries or loops
• Resource leaks

Correctness

• Edge cases (empty input, null, overflow)
• Race conditions and concurrency issues
• Error handling and propagation
• Off-by-one errors
• Type safety

Maintainability

• Naming clarity
• Single responsibility
• Duplication
• Test coverage
• Documentation for non-obvious logic

Output

## Code Review: [PR title or file]

### Summary
[1-2 sentence overview of the changes and overall quality]

### Critical Issues
| # | File | Line | Issue | Severity |
|---|------|------|-------|----------|
| 1 | [file] | [line] | [description] | 🔴 Critical |

### Suggestions
| # | File | Line | Suggestion | Category |
|---|------|------|------------|----------|
| 1 | [file] | [line] | [description] | Performance |

### What Looks Good
- [Positive observations]

### Verdict
[Approve / Request Changes / Needs Discussion]

If Connectors Available

If ~~source control is connected:

• Pull the PR diff automatically from the URL
• Check CI status and test results

If ~~project tracker is connected:

• Link findings to related tickets
• Verify the PR addresses the stated requirements

If ~~knowledge base is connected:

• Check changes against team coding standards and style guides

Tips

1. Provide context — "This is a hot path" or "This handles PII" helps me focus.
2. Specify concerns — "Focus on security" narrows the review.
3. Include tests — I'll check test coverage and quality too.

/debug

If you see unfamiliar placeholders or need to check which tools are connected, see CONNECTORS.md.

Run a structured debugging session to find and fix issues systematically.

Usage

/debug $ARGUMENTS

How It Works

┌─────────────────────────────────────────────────────────────────┐
│                       DEBUG                                        │
├─────────────────────────────────────────────────────────────────┤
│  Step 1: REPRODUCE                                                │
│  ✓ Understand the expected vs. actual behavior                   │
│  ✓ Identify exact reproduction steps                             │
│  ✓ Determine scope (when did it start? who is affected?)        │
│                                                                    │
│  Step 2: ISOLATE                                                   │
│  ✓ Narrow down the component, service, or code path             │
│  ✓ Check recent changes (deploys, config changes, dependencies) │
│  ✓ Review logs and error messages                                │
│                                                                    │
│  Step 3: DIAGNOSE                                                  │
│  ✓ Form hypotheses and test them                                 │
│  ✓ Trace the code path                                           │
│  ✓ Identify root cause (not just symptoms)                      │
│                                                                    │
│  Step 4: FIX                                                       │
│  ✓ Propose a fix with explanation                                │
│  ✓ Consider side effects and edge cases                          │
│  ✓ Suggest tests to prevent regression                           │
└─────────────────────────────────────────────────────────────────┘

What I Need From You

Tell me about the problem. Any of these help:

• Error message or stack trace
• Steps to reproduce
• What changed recently
• Logs or screenshots
• Expected vs. actual behavior

Output

## Debug Report: [Issue Summary]

### Reproduction
- **Expected**: [What should happen]
- **Actual**: [What happens instead]
- **Steps**: [How to reproduce]

### Root Cause
[Explanation of why the bug occurs]

### Fix
[Code changes or configuration fixes needed]

### Prevention
- [Test to add]
- [Guard to put in place]

If Connectors Available

If ~~monitoring is connected:

• Pull logs, error rates, and metrics around the time of the issue
• Show recent deploys and config changes that may correlate

If ~~source control is connected:

• Identify recent commits and PRs that touched affected code paths
• Check if the issue correlates with a specific change

If ~~project tracker is connected:

• Search for related bug reports or known issues
• Create a ticket for the fix once identified

Tips

1. Share error messages exactly — Don't paraphrase. The exact text matters.
2. Mention what changed — Recent deploys, dependency updates, and config changes are top suspects.
3. Include context — "This works in staging but not prod" or "Only affects large payloads" narrows things fast.

/deploy-checklist

If you see unfamiliar placeholders or need to check which tools are connected, see CONNECTORS.md.

Generate a pre-deployment checklist to verify readiness before shipping.

Usage

/deploy-checklist $ARGUMENTS

Output

## Deploy Checklist: [Service/Release]
**Date:** [Date] | **Deployer:** [Name]

### Pre-Deploy
- [ ] All tests passing in CI
- [ ] Code reviewed and approved
- [ ] No known critical bugs in release
- [ ] Database migrations tested (if applicable)
- [ ] Feature flags configured (if applicable)
- [ ] Rollback plan documented
- [ ] On-call team notified

### Deploy
- [ ] Deploy to staging and verify
- [ ] Run smoke tests
- [ ] Deploy to production (canary if available)
- [ ] Monitor error rates and latency for 15 min
- [ ] Verify key user flows

### Post-Deploy
- [ ] Confirm metrics are nominal
- [ ] Update release notes / changelog
- [ ] Notify stakeholders
- [ ] Close related tickets

### Rollback Triggers
- Error rate exceeds [X]%
- P50 latency exceeds [X]ms
- [Critical user flow] fails

Customization

Tell me about your deploy and I'll customize the checklist:

• "We use feature flags" → adds flag verification steps
• "This includes a database migration" → adds migration-specific checks
• "This is a breaking API change" → adds consumer notification steps

If Connectors Available

If ~~source control is connected:

• Pull the release diff and list of changes
• Verify all PRs are approved and merged

If ~~CI/CD is connected:

• Check build and test status automatically
• Verify pipeline is green before deploy

If ~~monitoring is connected:

• Pre-fill rollback trigger thresholds from current baselines
• Set up post-deploy metric watch

Tips

1. Run before every deploy — Even routine ones. Checklists prevent "I forgot to..."
2. Customize once, reuse — Tell me your stack and I'll remember your deploy process.
3. Include rollback criteria — Decide when to roll back before you deploy, not during.

Technical Documentation

Write clear, maintainable technical documentation for different audiences and purposes.

Document Types

README

• What this is and why it exists
• Quick start (< 5 minutes to first success)
• Configuration and usage
• Contributing guide

API Documentation

• Endpoint reference with request/response examples
• Authentication and error codes
• Rate limits and pagination
• SDK examples

Runbook

• When to use this runbook
• Prerequisites and access needed
• Step-by-step procedure
• Rollback steps
• Escalation path

Architecture Doc

• Context and goals
• High-level design with diagrams
• Key decisions and trade-offs
• Data flow and integration points

Onboarding Guide

• Environment setup
• Key systems and how they connect
• Common tasks with walkthroughs
• Who to ask for what

Principles

1. Write for the reader — Who is reading this and what do they need?
2. Start with the most useful information — Don't bury the lede
3. Show, don't tell — Code examples, commands, screenshots
4. Keep it current — Outdated docs are worse than no docs
5. Link, don't duplicate — Reference other docs instead of copying

/incident-response

If you see unfamiliar placeholders or need to check which tools are connected, see CONNECTORS.md.

Manage an incident from detection through postmortem.

Usage

/incident-response $ARGUMENTS

Modes

/incident-response new [description]     # Start a new incident
/incident-response update [status]       # Post a status update
/incident-response postmortem            # Generate postmortem from incident data

If no mode is specified, ask what phase the incident is in.

How It Works

┌─────────────────────────────────────────────────────────────────┐
│                    INCIDENT RESPONSE                               │
├─────────────────────────────────────────────────────────────────┤
│  Phase 1: TRIAGE                                                  │
│  ✓ Assess severity (SEV1-4)                                     │
│  ✓ Identify affected systems and users                          │
│  ✓ Assign roles (IC, comms, responders)                         │
│                                                                    │
│  Phase 2: COMMUNICATE                                              │
│  ✓ Draft internal status update                                  │
│  ✓ Draft customer communication (if needed)                     │
│  ✓ Set up war room and cadence                                   │
│                                                                    │
│  Phase 3: MITIGATE                                                 │
│  ✓ Document mitigation steps taken                               │
│  ✓ Track timeline of events                                      │
│  ✓ Confirm resolution                                            │
│                                                                    │
│  Phase 4: POSTMORTEM                                               │
│  ✓ Blameless postmortem document                                 │
│  ✓ Timeline reconstruction                                       │
│  ✓ Root cause analysis (5 whys)                                  │
│  ✓ Action items with owners                                      │
└─────────────────────────────────────────────────────────────────┘

Severity Classification

Communication Guidance

Provide clear, factual updates at regular cadence. Include: what's happening, who's affected, what we're doing, when the next update is.

Output — Status Update

## Incident Update: [Title]
**Severity:** SEV[1-4] | **Status:** Investigating | Identified | Monitoring | Resolved
**Impact:** [Who/what is affected]
**Last Updated:** [Timestamp]

### Current Status
[What we know now]

### Actions Taken
- [Action 1]
- [Action 2]

### Next Steps
- [What's happening next and ETA]

### Timeline
| Time | Event |
|------|-------|
| [HH:MM] | [Event] |

Output — Postmortem

## Postmortem: [Incident Title]
**Date:** [Date] | **Duration:** [X hours] | **Severity:** SEV[X]
**Authors:** [Names] | **Status:** Draft

### Summary
[2-3 sentence plain-language summary]

### Impact
- [Users affected]
- [Duration of impact]
- [Business impact if quantifiable]

### Timeline
| Time (UTC) | Event |
|------------|-------|
| [HH:MM] | [Event] |

### Root Cause
[Detailed explanation of what caused the incident]

### 5 Whys
1. Why did [symptom]? → [Because...]
2. Why did [cause 1]? → [Because...]
3. Why did [cause 2]? → [Because...]
4. Why did [cause 3]? → [Because...]
5. Why did [cause 4]? → [Root cause]

### What Went Well
- [Things that worked]

### What Went Poorly
- [Things that didn't work]

### Action Items
| Action | Owner | Priority | Due Date |
|--------|-------|----------|----------|
| [Action] | [Person] | P0/P1/P2 | [Date] |

### Lessons Learned
[Key takeaways for the team]

If Connectors Available

If ~~monitoring is connected:

• Pull alert details and metrics
• Show graphs of affected metrics

If ~~incident management is connected:

• Create or update incident in PagerDuty/Opsgenie
• Page on-call responders

If ~~chat is connected:

• Post status updates to incident channel
• Create war room channel

Tips

1. Start writing immediately — Don't wait for complete information. Update as you learn more.
2. Keep updates factual — What we know, what we've done, what's next. No speculation.
3. Postmortems are blameless — Focus on systems and processes, not individuals.

/standup

If you see unfamiliar placeholders or need to check which tools are connected, see CONNECTORS.md.

Generate a standup update by pulling together recent activity across your tools.

How It Works

┌─────────────────────────────────────────────────────────────────┐
│                        STANDUP                                    │
├─────────────────────────────────────────────────────────────────┤
│  STANDALONE (always works)                                       │
│  ✓ Tell me what you worked on and I'll structure it             │
│  ✓ Format for daily standup (yesterday / today / blockers)      │
│  ✓ Keep it concise and action-oriented                          │
├─────────────────────────────────────────────────────────────────┤
│  SUPERCHARGED (when you connect your tools)                      │
│  + Source control: Recent commits and PRs                        │
│  + Project tracker: Ticket status changes                        │
│  + Chat: Relevant discussions and decisions                      │
│  + CI/CD: Build and deploy status                                │
└─────────────────────────────────────────────────────────────────┘

What I Need From You

Option A: Let me pull it If your tools are connected, just say /standup and I'll gather everything automatically.

Option B: Tell me what you did "Worked on the auth migration, reviewed 3 PRs, got blocked on the API rate limiting issue."

Output

## Standup — [Date]

### Yesterday
- [Completed item with ticket reference if available]
- [Completed item]

### Today
- [Planned item with ticket reference]
- [Planned item]

### Blockers
- [Blocker with context and who can help]

If Connectors Available

If ~~source control is connected:

• Pull recent commits and PRs (opened, reviewed, merged)
• Summarize code changes at a high level

If ~~project tracker is connected:

• Pull tickets moved to "in progress" or "done"
• Show upcoming sprint items

If ~~chat is connected:

• Scan for relevant discussions and decisions
• Flag threads needing your response

Tips

1. Run it every morning — Build a habit and never scramble for standup notes.
2. Add context — After I generate, add any nuance about blockers or priorities.
3. Share format — Ask me to format for Slack, email, or your team's standup tool.

System Design

Help design systems and evaluate architectural decisions.

Framework

1. Requirements Gathering

• Functional requirements (what it does)
• Non-functional requirements (scale, latency, availability, cost)
• Constraints (team size, timeline, existing tech stack)

2. High-Level Design

• Component diagram
• Data flow
• API contracts
• Storage choices

3. Deep Dive

• Data model design
• API endpoint design (REST, GraphQL, gRPC)
• Caching strategy
• Queue/event design
• Error handling and retry logic

4. Scale and Reliability

• Load estimation
• Horizontal vs. vertical scaling
• Failover and redundancy
• Monitoring and alerting

5. Trade-off Analysis

• Every decision has trade-offs. Make them explicit.
• Consider: complexity, cost, team familiarity, time to market, maintainability

Output

Produce clear, structured design documents with diagrams (ASCII or described), explicit assumptions, and trade-off analysis. Always identify what you'd revisit as the system grows.

Tech Debt Management

Systematically identify, categorize, and prioritize technical debt.

Categories

Prioritization Framework

Score each item on:

• Impact: How much does it slow the team down? (1-5)
• Risk: What happens if we don't fix it? (1-5)
• Effort: How hard is the fix? (1-5, inverted — lower effort = higher priority)

Priority = (Impact + Risk) x (6 - Effort)

Output

Produce a prioritized list with estimated effort, business justification for each item, and a phased remediation plan that can be done alongside feature work.

Testing Strategy

Design effective testing strategies balancing coverage, speed, and maintenance.

Testing Pyramid

        /  E2E  \         Few, slow, high confidence
       / Integration \     Some, medium speed
      /    Unit Tests  \   Many, fast, focused

Strategy by Component Type

• API endpoints: Unit tests for business logic, integration tests for HTTP layer, contract tests for consumers
• Data pipelines: Input validation, transformation correctness, idempotency tests
• Frontend: Component tests, interaction tests, visual regression, accessibility
• Infrastructure: Smoke tests, chaos engineering, load tests

What to Cover

Focus on: business-critical paths, error handling, edge cases, security boundaries, data integrity.

Skip: trivial getters/setters, framework code, one-off scripts.

Output

Produce a test plan with: what to test, test type for each area, coverage targets, and example test cases. Identify gaps in existing coverage.

Digest Command

If you see unfamiliar placeholders or need to check which tools are connected, see CONNECTORS.md.

Scan recent activity across all connected sources and generate a structured digest highlighting what matters.

Instructions

1. Parse Flags

Determine the time window from the user's input:

• --daily — Last 24 hours (default if no flag specified)
• --weekly — Last 7 days

The user may also specify a custom range:

• --since yesterday
• --since Monday
• --since 2025-01-20

2. Check Available Sources

Identify which MCP sources are connected (same approach as the search command):

• ~~chat — channels, DMs, mentions
• ~~email — inbox, sent, threads
• ~~cloud storage — recently modified docs shared with user
• ~~project tracker — tasks assigned, completed, commented on
• ~~CRM — opportunity updates, account activity
• ~~knowledge base — recently updated wiki pages

If no sources are connected, guide the user:

To generate a digest, you'll need at least one source connected.
Check your MCP settings to add ~~chat, ~~email, ~~cloud storage, or other tools.

3. Gather Activity from Each Source

~~chat:

• Search for messages mentioning the user (to:me)
• Check channels the user is in for recent activity
• Look for threads the user participated in
• Identify new messages in key channels

~~email:

• Search recent inbox messages
• Identify threads with new replies
• Flag emails with action items or questions directed at the user

~~cloud storage:

• Find documents recently modified or shared with the user
• Note new comments on docs the user owns or collaborates on

~~project tracker:

• Tasks assigned to the user (new or updated)
• Tasks completed by others that the user follows
• Comments on tasks the user is involved with

~~CRM:

• Opportunity stage changes
• New activities logged on accounts the user owns
• Updated contacts or accounts

~~knowledge base:

• Recently updated documents in relevant collections
• New documents created in watched areas

4. Identify Key Items

From all gathered activity, extract and categorize:

Action Items:

• Direct requests made to the user ("Can you...", "Please...", "@user")
• Tasks assigned or due soon
• Questions awaiting the user's response
• Review requests

Decisions:

• Conclusions reached in threads or emails
• Approvals or rejections
• Policy or direction changes

Mentions:

• Times the user was mentioned or referenced
• Discussions about the user's projects or areas

Updates:

• Status changes on projects the user follows
• Document updates in the user's domain
• Completed items the user was waiting on

5. Group by Topic

Organize the digest by topic, project, or theme rather than by source. Merge related activity across sources:

## Project Aurora
- ~~chat: Design review thread concluded — team chose Option B (#design, Tuesday)
- ~~email: Sarah sent updated spec incorporating feedback (Wednesday)
- ~~cloud storage: "Aurora API Spec v3" updated by Sarah (Wednesday)
- ~~project tracker: 3 tasks moved to In Progress, 2 completed

## Budget Planning
- ~~email: Finance team requesting Q2 projections by Friday
- ~~chat: Todd shared template in #finance (Monday)
- ~~cloud storage: "Q2 Budget Template" shared with you (Monday)

6. Format the Digest

Structure the output clearly:

# [Daily/Weekly] Digest — [Date or Date Range]

Sources scanned: ~~chat, ~~email, ~~cloud storage, [others]

## Action Items (X items)
- [ ] [Action item 1] — from [person], [source] ([date])
- [ ] [Action item 2] — from [person], [source] ([date])

## Decisions Made
- [Decision 1] — [context] ([source], [date])
- [Decision 2] — [context] ([source], [date])

## [Topic/Project Group 1]
[Activity summary with source attribution]

## [Topic/Project Group 2]
[Activity summary with source attribution]

## Mentions
- [Mention context] — [source] ([date])

## Documents Updated
- [Doc name] — [who modified, what changed] ([date])

7. Handle Unavailable Sources

If any source fails or is unreachable:

Note: Could not reach [source name] for this digest.
The following sources were included: [list of successful sources].

Do not let one failed source prevent the digest from being generated. Produce the best digest possible from available sources.

8. Summary Stats

End with a quick summary:

---
[X] action items · [Y] decisions · [Z] mentions · [W] doc updates
Across [N] sources · Covering [time range]

Notes

• Default to --daily if no flag is specified
• Group by topic/project, not by source — users care about what happened, not where it happened
• Action items should always be listed first — they are the most actionable part of a digest
• Deduplicate cross-source activity (same decision in ~~chat and email = one entry)
• For weekly digests, prioritize significance over completeness — highlight what matters, skip noise
• If the user has a memory system (CLAUDE.md), use it to decode people names and project references
• Include enough context in each item that the user can decide whether to dig deeper without clicking through

Knowledge Synthesis

The last mile of enterprise search. Takes raw results from multiple sources and produces a coherent, trustworthy answer.

The Goal

Transform this:

~~chat result: "Sarah said in #eng: 'let's go with REST, GraphQL is overkill for our use case'"
~~email result: "Subject: API Decision — Sarah's email confirming REST approach with rationale"
~~cloud storage result: "API Design Doc v3 — updated section 2 to reflect REST decision"
~~project tracker result: "Task: Finalize API approach — marked complete by Sarah"

Into this:

The team decided to go with REST over GraphQL for the API redesign. Sarah made the
call, noting that GraphQL was overkill for the current use case. This was discussed
in #engineering on Tuesday, confirmed via email Wednesday, and the design doc has
been updated to reflect the decision. The related ~~project tracker task is marked complete.

Sources:
- ~~chat: #engineering thread (Jan 14)
- ~~email: "API Decision" from Sarah (Jan 15)
- ~~cloud storage: "API Design Doc v3" (updated Jan 15)
- ~~project tracker: "Finalize API approach" (completed Jan 15)

Deduplication

Cross-Source Deduplication

The same information often appears in multiple places. Identify and merge duplicates:

Signals that results are about the same thing:

• Same or very similar text content
• Same author/sender
• Timestamps within a short window (same day or adjacent days)
• References to the same entity (project name, document, decision)
• One source references another ("as discussed in ~~chat", "per the email", "see the doc")

How to merge:

• Combine into a single narrative item
• Cite all sources where it appeared
• Use the most complete version as the primary text
• Add unique details from each source

Deduplication Priority

When the same information exists in multiple sources, prefer:

1. The most complete version (fullest context)
2. The most authoritative source (official doc > chat)
3. The most recent version (latest update wins for evolving info)

What NOT to Deduplicate

Keep as separate items when:

• The same topic is discussed but with different conclusions
• Different people express different viewpoints
• The information evolved meaningfully between sources (v1 vs v2 of a decision)
• Different time periods are represented

Citation and Source Attribution

Every claim in the synthesized answer must be attributable to a source.

Attribution Format

Inline for direct references:

Sarah confirmed the REST approach in her email on Wednesday.
The design doc was updated to reflect this (~~cloud storage: "API Design Doc v3").

Source list at the end for completeness:

Sources:
- ~~chat: #engineering discussion (Jan 14) — initial decision thread
- ~~email: "API Decision" from Sarah Chen (Jan 15) — formal confirmation
- ~~cloud storage: "API Design Doc v3" last modified Jan 15 — updated specification

Attribution Rules

• Always name the source type (~~chat, ~~email, ~~cloud storage, etc.)
• Include the specific location (channel, folder, thread)
• Include the date or relative time
• Include the author when relevant
• Include document/thread titles when available
• For ~~chat, note the channel name
• For ~~email, note the subject line and sender
• For ~~cloud storage, note the document title

Confidence Levels

Not all results are equally trustworthy. Assess confidence based on:

Freshness

For status queries, heavily weight freshness. For policy/factual queries, freshness matters less.

Authority

Expressing Confidence

When confidence is high (multiple fresh, authoritative sources agree):

The team decided to use REST for the API redesign. [direct statement]

When confidence is moderate (single source or somewhat dated):

Based on the discussion in #engineering last month, the team was leaning
toward REST for the API redesign. This may have evolved since then.

When confidence is low (old data, informal source, or conflicting signals):

I found a reference to an API migration discussion from three months ago
in ~~chat, but I couldn't find a formal decision document. The information
may be outdated. You might want to check with the team for current status.

Conflicting Information

When sources disagree:

I found conflicting information about the API approach:
- The ~~chat discussion on Jan 10 suggested GraphQL
- But Sarah's email on Jan 15 confirmed REST
- The design doc (updated Jan 15) reflects REST

The most recent sources indicate REST was the final decision,
but the earlier ~~chat discussion explored GraphQL first.

Always surface conflicts rather than silently picking one version.

Summarization Strategies

For Small Result Sets (1-5 results)

Present each result with context. No summarization needed — give the user everything:

[Direct answer synthesized from results]

[Detail from source 1]
[Detail from source 2]

Sources: [full attribution]

For Medium Result Sets (5-15 results)

Group by theme and summarize each group:

[Overall answer]

Theme 1: [summary of related results]
Theme 2: [summary of related results]

Key sources: [top 3-5 most relevant sources]
Full results: [count] items found across [sources]

For Large Result Sets (15+ results)

Provide a high-level synthesis with the option to drill down:

[Overall answer based on most relevant results]

Summary:
- [Key finding 1] (supported by N sources)
- [Key finding 2] (supported by N sources)
- [Key finding 3] (supported by N sources)

Top sources:
- [Most authoritative/relevant source]
- [Second most relevant]
- [Third most relevant]

Found [total count] results across [source list].
Want me to dig deeper into any specific aspect?

Summarization Rules

• Lead with the answer, not the search process
• Do not list raw results — synthesize them into narrative
• Group related items from different sources together
• Preserve important nuance and caveats
• Include enough detail that the user can decide whether to dig deeper
• Always offer to provide more detail if the result set was large

Synthesis Workflow

[Raw results from all sources]
          ↓
[1. Deduplicate — merge same info from different sources]
          ↓
[2. Cluster — group related results by theme/topic]
          ↓
[3. Rank — order clusters and items by relevance to query]
          ↓
[4. Assess confidence — freshness × authority × agreement]
          ↓
[5. Synthesize — produce narrative answer with attribution]
          ↓
[6. Format — choose appropriate detail level for result count]
          ↓
[Coherent answer with sources]

Anti-Patterns

Do not:

• List results source by source ("From ~~chat: ... From ~~email: ... From ~~cloud storage: ...")
• Include irrelevant results just because they matched a keyword
• Bury the answer under methodology explanation
• Present conflicting info without flagging the conflict
• Omit source attribution
• Present uncertain information with the same confidence as well-supported facts
• Summarize so aggressively that useful detail is lost

Do:

• Lead with the answer
• Group by topic, not by source
• Flag confidence levels when appropriate
• Surface conflicts explicitly
• Attribute all claims to sources
• Offer to go deeper when result sets are large

Search Strategy

If you see unfamiliar placeholders or need to check which tools are connected, see CONNECTORS.md.

The core intelligence behind enterprise search. Transforms a single natural language question into parallel, source-specific searches and produces ranked, deduplicated results.

The Goal

Turn this:

"What did we decide about the API migration timeline?"

Into targeted searches across every connected source:

~~chat:  "API migration timeline decision" (semantic) + "API migration" in:#engineering after:2025-01-01
~~knowledge base: semantic search "API migration timeline decision"
~~project tracker:  text search "API migration" in relevant workspace

Then synthesize the results into a single coherent answer.

Query Decomposition

Step 1: Identify Query Type

Classify the user's question to determine search strategy:

Step 2: Extract Search Components

From the query, extract:

• Keywords: Core terms that must appear in results
• Entities: People, projects, teams, tools (use memory system if available)
• Intent signals: Decision words, status words, temporal markers
• Constraints: Time ranges, source hints, author filters
• Negations: Things to exclude

Step 3: Generate Sub-Queries Per Source

For each available source, create one or more targeted queries:

Prefer semantic search for:

• Conceptual questions ("What do we think about...")
• Questions where exact keywords are unknown
• Exploratory queries

Prefer keyword search for:

• Known terms, project names, acronyms
• Exact phrases the user quoted
• Filter-heavy queries (from:, in:, after:)

Generate multiple query variants when the topic might be referred to differently:

User: "Kubernetes setup"
Queries: "Kubernetes", "k8s", "cluster", "container orchestration"

Source-Specific Query Translation

~~chat

Semantic search (natural language questions):

query: "What is the status of project aurora?"

Keyword search:

query: "project aurora status update"
query: "aurora in:#engineering after:2025-01-15"
query: "from:<@UserID> aurora"

Filter mapping:

~~knowledge base (Wiki)

Semantic search — Use for conceptual queries:

descriptive_query: "API migration timeline and decision rationale"

Keyword search — Use for exact terms:

query: "API migration"
query: "\"API migration timeline\""  (exact phrase)

~~project tracker

Task search:

text: "API migration"
workspace: [workspace_id]
completed: false  (for status queries)
assignee_any: "me"  (for "my tasks" queries)

Filter mapping:

Result Ranking

Relevance Scoring

Score each result on these factors (weighted by query type):

Authority Hierarchy

Depends on query type:

For factual/policy questions:

Wiki/Official docs > Shared documents > Email announcements > Chat messages

For "what happened" / decision questions:

Meeting notes > Thread conclusions > Email confirmations > Chat messages

For status questions:

Task tracker > Recent chat > Status docs > Email updates

Handling Ambiguity

When a query is ambiguous, prefer asking one focused clarifying question over guessing:

Ambiguous: "search for the migration"
→ "I found references to a few migrations. Are you looking for:
   1. The database migration (Project Phoenix)
   2. The cloud migration (AWS → GCP)
   3. The email migration (Exchange → O365)"

Only ask for clarification when:

• There are genuinely distinct interpretations that would produce very different results
• The ambiguity would significantly affect which sources to search

Do NOT ask for clarification when:

• The query is clear enough to produce useful results
• Minor ambiguity can be resolved by returning results from multiple interpretations

Fallback Strategies

When a source is unavailable or returns no results:

1. Source unavailable: Skip it, search remaining sources, note the gap
2. No results from a source: Try broader query terms, remove date filters, try alternate keywords
3. All sources return nothing: Suggest query modifications to the user
4. Rate limited: Note the limitation, return results from other sources, suggest retrying later

Query Broadening

If initial queries return too few results:

Original: "PostgreSQL migration Q2 timeline decision"
Broader:  "PostgreSQL migration"
Broader:  "database migration"
Broadest: "migration"

Remove constraints in this order:

1. Date filters (search all time)
2. Source/location filters
3. Less important keywords
4. Keep only core entity/topic terms

Parallel Execution

Always execute searches across sources in parallel, never sequentially. The total search time should be roughly equal to the slowest single source, not the sum of all sources.

[User query]
     ↓ decompose
[~~chat query] [~~email query] [~~cloud storage query] [Wiki query] [~~project tracker query]
     ↓            ↓            ↓              ↓            ↓
  (parallel execution)
     ↓
[Merge + Rank + Deduplicate]
     ↓
[Synthesized answer]

Search Command

If you see unfamiliar placeholders or need to check which tools are connected, see CONNECTORS.md.

Search across all connected MCP sources in a single query. Decompose the user's question, run parallel searches, and synthesize results.

Instructions

1. Check Available Sources

Before searching, determine which MCP sources are available. Attempt to identify connected tools from the available tool list. Common sources:

• ~~chat — chat platform tools
• ~~email — email tools
• ~~cloud storage — cloud storage tools
• ~~project tracker — project tracking tools
• ~~CRM — CRM tools
• ~~knowledge base — knowledge base tools

If no MCP sources are connected:

To search across your tools, you'll need to connect at least one source.
Check your MCP settings to add ~~chat, ~~email, ~~cloud storage, or other tools.

Supported sources: ~~chat, ~~email, ~~cloud storage, ~~project tracker, ~~CRM, ~~knowledge base,
and any other MCP-connected service.

2. Parse the User's Query

Analyze the search query to understand:

• Intent: What is the user looking for? (a decision, a document, a person, a status update, a conversation)
• Entities: People, projects, teams, tools mentioned
• Time constraints: Recency signals ("this week", "last month", specific dates)
• Source hints: References to specific tools ("in ~~chat", "that email", "the doc")
• Filters: Extract explicit filters from the query:
  • from: — Filter by sender/author
  • in: — Filter by channel, folder, or location
  • after: — Only results after this date
  • before: — Only results before this date
  • type: — Filter by content type (message, email, doc, thread, file)

3. Decompose into Sub-Queries

For each available source, create a targeted sub-query using that source's native search syntax:

~~chat:

• Use available search and read tools for your chat platform
• Translate filters: from: maps to sender, in: maps to channel/room, dates map to time range filters
• Use natural language queries for semantic search when appropriate
• Use keyword queries for exact matches

~~email:

• Use available email search tools
• Translate filters: from: maps to sender, dates map to time range filters
• Map type: to attachment filters or subject-line searches as appropriate

~~cloud storage:

• Use available file search tools
• Translate to file query syntax: name contains, full text contains, modified date, file type
• Consider both file names and content

~~project tracker:

• Use available task search or typeahead tools
• Map to task text search, assignee filters, date filters, project filters

~~CRM:

• Use available CRM query tools
• Search across Account, Contact, Opportunity, and other relevant objects

~~knowledge base:

• Use semantic search for conceptual questions
• Use keyword search for exact matches

4. Execute Searches in Parallel

Run all sub-queries simultaneously across available sources. Do not wait for one source before searching another.

For each source:

• Execute the translated query
• Capture results with metadata (timestamps, authors, links, source type)
• Note any sources that fail or return errors — do not let one failure block others

5. Rank and Deduplicate Results

Deduplication:

• Identify the same information appearing across sources (e.g., a decision discussed in ~~chat AND confirmed via email)
• Group related results together rather than showing duplicates
• Prefer the most authoritative or complete version

Ranking factors:

• Relevance: How well does the result match the query intent?
• Freshness: More recent results rank higher for status/decision queries
• Authority: Official docs > wiki > chat messages for factual questions; conversations > docs for "what did we discuss" queries
• Completeness: Results with more context rank higher

6. Present Unified Results

Format the response as a synthesized answer, not a raw list of results:

For factual/decision queries:

[Direct answer to the question]

Sources:
- [Source 1: brief description] (~~chat, #channel, date)
- [Source 2: brief description] (~~email, from person, date)
- [Source 3: brief description] (~~cloud storage, doc name, last modified)

For exploratory queries ("what do we know about X"):

[Synthesized summary combining information from all sources]

Found across:
- ~~chat: X relevant messages in Y channels
- ~~email: X relevant threads
- ~~cloud storage: X related documents
- [Other sources as applicable]

Key sources:
- [Most important source with link/reference]
- [Second most important source]

For "find" queries (looking for a specific thing):

[The thing they're looking for, with direct reference]

Also found:
- [Related items from other sources]

7. Handle Edge Cases

Ambiguous queries: If the query could mean multiple things, ask one clarifying question before searching:

"API redesign" could refer to a few things. Are you looking for:
1. The REST API v2 redesign (Project Aurora)
2. The internal SDK API changes
3. Something else?

No results:

I couldn't find anything matching "[query]" across [list of sources searched].

Try:
- Broader terms (e.g., "database" instead of "PostgreSQL migration")
- Different time range (currently searching [time range])
- Checking if the relevant source is connected (currently searching: [sources])

Partial results (some sources failed):

[Results from successful sources]

Note: I couldn't reach [failed source(s)] during this search.
Results above are from [successful sources] only.

Notes

• Always search multiple sources in parallel — never sequentially
• Synthesize results into answers, do not just list raw search results
• Include source attribution so users can dig deeper
• Respect the user's filter syntax and apply it appropriately per source
• When a query mentions a specific person, search for their messages/docs/mentions across all sources
• For time-sensitive queries, prioritize recency in ranking
• If only one source is connected, still provide useful results from that source

Source Management

If you see unfamiliar placeholders or need to check which tools are connected, see CONNECTORS.md.

Knows what sources are available, helps connect new ones, and manages how sources are queried.

Checking Available Sources

Determine which MCP sources are connected by checking available tools. Each source corresponds to a set of MCP tools:

If a tool prefix is available, the source is connected and searchable.

Guiding Users to Connect Sources

When a user searches but has few or no sources connected:

You currently have [N] source(s) connected: [list].

To expand your search, you can connect additional sources in your MCP settings:
- ~~chat — messages, threads, channels
- ~~email — emails, conversations, attachments
- ~~cloud storage — docs, sheets, slides
- ~~project tracker — tasks, projects, milestones
- ~~CRM — accounts, contacts, opportunities
- ~~knowledge base — wiki pages, knowledge base articles

The more sources you connect, the more complete your search results.

When a user asks about a specific tool that is not connected:

[Tool name] isn't currently connected. To add it:
1. Open your MCP settings
2. Add the [tool] MCP server configuration
3. Authenticate when prompted

Once connected, it will be automatically included in future searches.

Source Priority Ordering

Different query types benefit from searching certain sources first. Use these priorities to weight results, not to skip sources:

By Query Type

Decision queries ("What did we decide..."):

1. ~~chat (conversations where decisions happen)
2. ~~email (decision confirmations, announcements)
3. ~~cloud storage (meeting notes, decision logs)
4. Wiki (if decisions are documented)
5. Task tracker (if decisions are captured in tasks)

Status queries ("What's the status of..."):

1. Task tracker (~~project tracker — authoritative status)
2. ~~chat (real-time discussion)
3. ~~cloud storage (status docs, reports)
4. ~~email (status update emails)
5. Wiki (project pages)

Document queries ("Where's the doc for..."):

1. ~~cloud storage (primary doc storage)
2. Wiki / ~~knowledge base (knowledge base)
3. ~~email (docs shared via email)
4. ~~chat (docs shared in channels)
5. Task tracker (docs linked to tasks)

People queries ("Who works on..." / "Who knows about..."):

1. ~~chat (message authors, channel members)
2. Task tracker (task assignees)
3. ~~cloud storage (doc authors, collaborators)
4. ~~CRM (account owners, contacts)
5. ~~email (email participants)

Factual/Policy queries ("What's our policy on..."):

1. Wiki / ~~knowledge base (official documentation)
2. ~~cloud storage (policy docs, handbooks)
3. ~~email (policy announcements)
4. ~~chat (policy discussions)

Default Priority (General Queries)

When query type is unclear:

1. ~~chat (highest volume, most real-time)
2. ~~email (formal communications)
3. ~~cloud storage (documents and files)
4. Wiki / ~~knowledge base (structured knowledge)
5. Task tracker (work items)
6. CRM (customer data)

Rate Limiting Awareness

MCP sources may have rate limits. Handle them gracefully:

Detection

Rate limit responses typically appear as:

• HTTP 429 responses
• Error messages mentioning "rate limit", "too many requests", or "quota exceeded"
• Throttled or delayed responses

Handling

When a source is rate limited:

1. Do not retry immediately — respect the limit
2. Continue with other sources — do not block the entire search
3. Inform the user:

Note: [Source] is temporarily rate limited. Results below are from
[other sources]. You can retry in a few minutes to include [source].

4. For digests — if rate limited mid-scan, note which time range was covered before the limit hit

Prevention

• Avoid unnecessary API calls — check if the source is likely to have relevant results before querying
• Use targeted queries over broad scans when possible
• For digests, batch requests where the API supports it
• Cache awareness: if a search was just run, avoid re-running the same query immediately

Source Health

Track source availability during a session:

Source Status:
  ~~chat:        ✓ Available
  ~~email:        ✓ Available
  ~~cloud storage:  ✓ Available
  ~~project tracker:        ✗ Not connected
  ~~CRM:   ✗ Not connected
  ~~knowledge base:      ⚠ Rate limited (retry in 2 min)

When reporting search results, include which sources were searched so the user knows the scope of the answer.

Adding Custom Sources

The enterprise search plugin works with any MCP-connected source. As new MCP servers become available, they can be added to the .mcp.json configuration. The search and digest commands will automatically detect and include new sources based on available tools.

To add a new source:

1. Add the MCP server configuration to .mcp.json
2. Authenticate if required
3. The source will be included in subsequent searches automatically

Audit Support

Important: This skill assists with SOX compliance workflows but does not provide audit or legal advice. All testing workpapers and assessments should be reviewed by qualified financial professionals. While "significance" and "materiality" are context-specific concepts that are ultimately assessed by auditors, this skill is intended to assist professionals in the creation and evaluation of effective internal controls and documentation for audits.

SOX 404 control testing methodology, sample selection approaches, testing documentation standards, control deficiency classification, and common control types.

SOX 404 Control Testing Methodology

Overview

SOX Section 404 requires management to assess the effectiveness of internal controls over financial reporting (ICFR). This involves:

1. Scoping: Identify significant accounts and relevant assertions
2. Risk assessment: Evaluate the risk of material misstatement for each significant account
3. Control identification: Document the controls that address each risk
4. Testing: Test the design and operating effectiveness of key controls
5. Evaluation: Assess whether any deficiencies exist and their severity
6. Reporting: Document the assessment and any material weaknesses

Scoping Significant Accounts

An account is significant if there is more than a remote likelihood that it could contain a misstatement that is material (individually or in aggregate).

Quantitative factors:

• Account balance exceeds materiality threshold (typically 3-5% of a key benchmark)
• Transaction volume is high, increasing the risk of error
• Account is subject to significant estimates or judgment

Qualitative factors:

• Account involves complex accounting (revenue recognition, derivatives, pensions)
• Account is susceptible to fraud (cash, revenue, related-party transactions)
• Account has had prior misstatements or audit adjustments
• Account involves significant management judgment or estimates
• New account or significantly changed process

Relevant Assertions by Account Type

Design Effectiveness vs Operating Effectiveness

Design effectiveness: Is the control properly designed to prevent or detect a material misstatement in the relevant assertion?

• Evaluated through walkthroughs (trace a transaction end-to-end through the process)
• Confirm the control is placed at the right point in the process
• Confirm the control addresses the identified risk
• Performed at least annually, or when processes change

Operating effectiveness: Did the control actually operate as designed throughout the testing period?

• Evaluated through testing (inspection, observation, re-performance, inquiry)
• Requires sufficient sample sizes to support a conclusion
• Must cover the full period of reliance

Sample Selection Approaches

Random Selection

When to use: Default method for transaction-level controls with large populations.

Method:

1. Define the population (all transactions subject to the control during the period)
2. Number each item in the population sequentially
3. Use a random number generator to select sample items
4. Ensure no bias in selection (all items have equal probability)

Advantages: Statistically valid, defensible, no selection bias Disadvantages: May miss high-risk items, requires complete population listing

Targeted (Judgmental) Selection

When to use: Supplement to random selection for risk-based testing; primary method when population is small or highly varied.

Method:

1. Identify items with specific risk characteristics:
   • High dollar amount (above a defined threshold)
   • Unusual or non-standard transactions
   • Period-end transactions (cut-off risk)
   • Related-party transactions
   • Manual or override transactions
   • New vendor/customer transactions
2. Select items matching risk criteria
3. Document rationale for each targeted selection

Advantages: Focuses on highest-risk items, efficient use of testing effort Disadvantages: Not statistically representative, may over-represent certain risks

Haphazard Selection

When to use: When random selection is impractical (no sequential population listing) and population is relatively homogeneous.

Method:

1. Select items without any specific pattern or bias
2. Ensure selections are spread across the full population period
3. Avoid unconscious bias (don't always pick items at the top, round numbers, etc.)

Advantages: Simple, no technology required Disadvantages: Not statistically valid, susceptible to unconscious bias

Systematic Selection

When to use: When population is sequential and you want even coverage across the period.

Method:

1. Calculate the sampling interval: Population size / Sample size
2. Select a random starting point within the first interval
3. Select every Nth item from the starting point

Example: Population of 1,000, sample of 25 → interval of 40. Random start: item 17. Select items 17, 57, 97, 137, ...

Advantages: Even coverage across population, simple to execute Disadvantages: Periodic patterns in the population could bias results

Sample Size Guidance

Factors increasing sample size:

• Higher inherent risk in the account/process
• Control is the sole control addressing a significant risk (no redundancy)
• Prior period control deficiency identified
• New control (not tested in prior periods)
• External auditor reliance on management testing

Testing Documentation Standards

Workpaper Requirements

Every control test should be documented with:

1. Control identification:

   • Control number/ID
   • Control description (what is done, by whom, how often)
   • Control type (manual, automated, IT-dependent manual)
   • Control frequency
   • Risk and assertion addressed

2. Test design:

   • Test objective (what you are trying to determine)
   • Test procedures (step-by-step instructions)
   • Expected evidence (what you expect to see if the control is effective)
   • Sample selection methodology and rationale

3. Test execution:

   • Population description and size
   • Sample selection details (method, items selected)
   • Results for each sample item (pass/fail with specific evidence examined)
   • Exceptions noted with full description

4. Conclusion:

   • Overall assessment (effective / deficiency / significant deficiency / material weakness)
   • Basis for conclusion
   • Impact assessment for any exceptions
   • Compensating controls considered (if applicable)

5. Sign-off:

   • Tester name and date
   • Reviewer name and date

Evidence Standards

Sufficient evidence includes:

• Screenshots showing system-enforced controls
• Signed/initialed approval documents
• Email approvals with identifiable approver and date
• System audit logs showing who performed the action and when
• Re-performed calculations with matching results
• Observation notes (with date, location, observer)

Insufficient evidence:

• Verbal confirmations alone (must be corroborated)
• Undated documents
• Evidence without identifiable performer/approver
• Generic system reports without date/time stamps
• "Per discussion with [name]" without corroborating documentation

Working Paper Organization

Organize testing files by control area:

SOX Testing/
├── [Year]/
│   ├── Scoping and Risk Assessment/
│   ├── Revenue Cycle/
│   │   ├── Control Matrix
│   │   ├── Walkthrough Documentation
│   │   ├── Test Workpapers (one per control)
│   │   └── Supporting Evidence
│   ├── Procure to Pay/
│   ├── Payroll/
│   ├── Financial Close/
│   ├── Treasury/
│   ├── Fixed Assets/
│   ├── IT General Controls/
│   ├── Entity Level Controls/
│   └── Summary and Conclusions/
│       ├── Deficiency Evaluation
│       └── Management Assessment

Control Deficiency Classification

Deficiency

A deficiency in internal control exists when the design or operation of a control does not allow management or employees, in the normal course of performing their assigned functions, to prevent or detect misstatements on a timely basis.

Evaluation factors:

• What is the likelihood that the control failure could result in a misstatement?
• What is the magnitude of the potential misstatement?
• Is there a compensating control that mitigates the deficiency?

Significant Deficiency

A deficiency, or combination of deficiencies, that is less severe than a material weakness yet important enough to merit attention by those charged with governance.

Indicators:

• The deficiency could result in a misstatement that is more than inconsequential but less than material
• There is more than a remote (but less than reasonably possible) likelihood of a material misstatement
• The control is a key control and the deficiency is not fully mitigated by compensating controls
• Combination of individually minor deficiencies that together represent a significant concern

Material Weakness

A deficiency, or combination of deficiencies, such that there is a reasonable possibility that a material misstatement of the financial statements will not be prevented or detected on a timely basis.

Indicators:

• Identification of fraud by senior management (any magnitude)
• Restatement of previously issued financial statements to correct a material error
• Identification by the auditor of a material misstatement that would not have been detected by the company's controls
• Ineffective oversight of financial reporting by the audit committee
• Deficiency in a pervasive control (entity-level, IT general control) affecting multiple processes

Deficiency Aggregation

Individual deficiencies that are not significant individually may be significant in combination:

1. Identify all deficiencies in the same process or affecting the same assertion
2. Evaluate whether the combined effect could result in a material misstatement
3. Consider whether deficiencies in compensating controls exacerbate other deficiencies
4. Document the aggregation analysis and conclusion

Remediation

For each identified deficiency:

1. Root cause analysis: Why did the control fail? (design gap, execution failure, staffing, training, system issue)
2. Remediation plan: Specific actions to fix the control (redesign, additional training, system enhancement, added review)
3. Timeline: Target date for remediation completion
4. Owner: Person responsible for implementing the remediation
5. Validation: How and when the remediated control will be re-tested to confirm effectiveness

Common Control Types

IT General Controls (ITGCs)

Controls over the IT environment that support the reliable functioning of application controls and automated processes.

Access Controls:

• User access provisioning (new access requests require approval)
• User access de-provisioning (terminated users removed timely)
• Privileged access management (admin/superuser access restricted and monitored)
• Periodic access reviews (user access recertified on a defined schedule)
• Password policies (complexity, rotation, lockout)
• Segregation of duties enforcement (conflicting access prevented)

Change Management:

• Change requests documented and approved before implementation
• Changes tested in a non-production environment before promotion
• Separation of development and production environments
• Emergency change procedures (documented, approved post-implementation)
• Change review and post-implementation validation

IT Operations:

• Batch job monitoring and exception handling
• Backup and recovery procedures (regular backups, tested restores)
• System availability and performance monitoring
• Incident management and escalation procedures
• Disaster recovery planning and testing

Manual Controls

Controls performed by people using judgment, typically involving review and approval.

Examples:

• Management review of financial statements and key metrics
• Supervisory approval of journal entries above a threshold
• Three-way match verification (PO, receipt, invoice)
• Account reconciliation preparation and review
• Physical inventory observation and count
• Vendor master data change approval
• Customer credit approval

Key attributes to test:

• Was the control performed by the right person (proper authority)?
• Was it performed timely (within the required timeframe)?
• Is there evidence of the review (signature, initials, email, system log)?
• Did the reviewer have sufficient information to perform an effective review?
• Were exceptions identified and appropriately addressed?

Automated Controls

Controls enforced by IT systems without human intervention.

Examples:

• System-enforced approval workflows (cannot proceed without required approvals)
• Three-way match automation (system blocks payment if PO/receipt/invoice don't match)
• Duplicate payment detection (system flags or blocks duplicate invoices)
• Credit limit enforcement (system prevents orders exceeding credit limit)
• Automated calculations (depreciation, amortization, interest, tax)
• System-enforced segregation of duties (conflicting roles prevented)
• Input validation controls (required fields, format checks, range checks)
• Automated reconciliation matching

Testing approach:

• Test design: Confirm the system configuration enforces the control as intended
• Test operating effectiveness: For automated controls, if the system configuration has not changed, one test of the control is typically sufficient for the period (supplemented by ITGC testing of change management)
• Verify change management ITGCs are effective (if system changed, re-test the control)

IT-Dependent Manual Controls

Manual controls that rely on the completeness and accuracy of system-generated information.

Examples:

• Management review of a system-generated exception report
• Supervisor review of a system-generated aging report to assess reserves
• Reconciliation using system-generated trial balance data
• Approval of transactions identified by a system-generated workflow

Testing approach:

• Test the manual control (review, approval, follow-up on exceptions)
• AND test the completeness and accuracy of the underlying report/data (IPE — Information Produced by the Entity)
• IPE testing confirms the data the reviewer relied on was complete and accurate

Entity-Level Controls

Broad controls that operate at the organizational level and affect multiple processes.

Examples:

• Tone at the top / code of conduct
• Risk assessment process
• Audit committee oversight of financial reporting
• Internal audit function and activities
• Fraud risk assessment and anti-fraud programs
• Whistleblower/ethics hotline
• Management monitoring of control effectiveness
• Financial reporting competence (staffing, training, qualifications)
• Period-end financial reporting process (close procedures, GAAP compliance reviews)

Significance:

• Entity-level controls can mitigate but typically cannot replace process-level controls
• Ineffective entity-level controls (especially audit committee oversight and tone at the top) are strong indicators of a material weakness
• Effective entity-level controls may reduce the extent of testing needed for process-level controls

Close Management

Important: This skill assists with close management workflows but does not provide financial advice. All close activities should be reviewed by qualified financial professionals.

Month-end close checklist, task sequencing and dependencies, status tracking, and common close activities organized by day.

Month-End Close Checklist

Pre-Close (Last 2-3 Business Days of the Month)

• Send close calendar and deadline reminders to all contributors
• Confirm cut-off procedures with AP, AR, payroll, and treasury
• Verify all sub-systems are processing normally (ERP, payroll, banking)
• Complete preliminary bank reconciliation (all but last-day activity)
• Review open purchase orders for potential accrual needs
• Confirm payroll processing schedule aligns with close timeline
• Collect information for any known unusual transactions

Close Day 1 (T+1: First Business Day After Month-End)

• Confirm all sub-ledger modules have completed period-end processing
• Run AP accruals for goods/services received but not invoiced
• Post payroll entries and payroll accrual (if pay period straddles month-end)
• Record cash receipts and disbursements through month-end
• Post intercompany transactions and confirm with counterparties
• Complete bank reconciliation with final bank statement
• Run fixed asset depreciation
• Post prepaid expense amortization

Close Day 2 (T+2)

• Complete revenue recognition entries and deferred revenue adjustments
• Post all remaining accrual journal entries
• Complete AR subledger reconciliation
• Complete AP subledger reconciliation
• Record inventory adjustments (if applicable)
• Post FX revaluation entries for foreign currency balances
• Begin balance sheet account reconciliations

Close Day 3 (T+3)

• Complete all balance sheet reconciliations
• Post any adjusting journal entries identified during reconciliation
• Complete intercompany reconciliation and elimination entries
• Run preliminary trial balance and income statement
• Perform preliminary flux analysis on income statement
• Investigate and resolve material variances

Close Day 4 (T+4)

• Post tax provision entries (income tax, sales tax, property tax)
• Complete equity roll-forward (stock compensation, treasury stock)
• Finalize all journal entries — soft close
• Generate draft financial statements (P&L, BS, CF)
• Perform detailed flux analysis and prepare variance explanations
• Management review of financial statements and key metrics

Close Day 5 (T+5)

• Post any final adjustments from management review
• Finalize financial statements — hard close
• Lock the period in the ERP/GL system
• Distribute financial reporting package to stakeholders
• Update forecasts/projections based on actual results
• Conduct close retrospective — identify process improvements

Task Sequencing and Dependencies

Dependency Map

Tasks are organized by what must complete before the next task can begin:

LEVEL 1 (No dependencies — can start immediately at T+1):
├── Cash receipts/disbursements recording
├── Bank statement retrieval
├── Payroll processing/accrual
├── Fixed asset depreciation run
├── Prepaid amortization
├── AP accrual preparation
└── Intercompany transaction posting

LEVEL 2 (Depends on Level 1 completion):
├── Bank reconciliation (needs: cash entries + bank statement)
├── Revenue recognition (needs: billing/delivery data finalized)
├── AR subledger reconciliation (needs: all revenue/cash entries)
├── AP subledger reconciliation (needs: all AP entries/accruals)
├── FX revaluation (needs: all foreign currency entries posted)
└── Remaining accrual JEs (needs: review of all source data)

LEVEL 3 (Depends on Level 2 completion):
├── All balance sheet reconciliations (needs: all JEs posted)
├── Intercompany reconciliation (needs: both sides posted)
├── Adjusting entries from reconciliations
└── Preliminary trial balance

LEVEL 4 (Depends on Level 3 completion):
├── Tax provision (needs: pre-tax income finalized)
├── Equity roll-forward
├── Consolidation and eliminations
├── Draft financial statements
└── Preliminary flux analysis

LEVEL 5 (Depends on Level 4 completion):
├── Management review
├── Final adjustments
├── Hard close / period lock
├── Financial reporting package
└── Forecast updates

Critical Path

The critical path determines the minimum close duration. Typical critical path:

Cash/AP/AR entries → Subledger reconciliations → Balance sheet recs →
  Tax provision → Draft financials → Management review → Hard close

To shorten the close:

• Automate Level 1 entries (depreciation, prepaid amortization, standard accruals)
• Pre-reconcile accounts during the month (continuous reconciliation)
• Parallel-process independent reconciliations
• Set clear deadlines with consequences for late submissions
• Use standardized templates to reduce reconciliation prep time

Status Tracking and Reporting

Close Status Dashboard

Track each close task with the following attributes:

Status Definitions

• Not Started: Task has not yet begun (may be waiting on dependencies)
• In Progress: Task is actively being worked on
• Complete: Task is finished and has been reviewed/approved
• Blocked: Task cannot proceed due to a dependency, missing data, or issue
• At Risk: Task is in progress but may not meet its deadline

Daily Close Status Meeting (Recommended)

During the close period, hold a brief (15-minute) daily standup:

1. Review status board: Walk through open tasks, flag any that are behind
2. Identify blockers: Surface any issues preventing task completion
3. Reassign or escalate: Adjust ownership or escalate blockers to resolve quickly
4. Update timeline: If any tasks are at risk, assess impact on overall close timeline

Close Metrics to Track Over Time

Common Close Activities by Day

Typical 5-Day Close Calendar

Accelerated Close (3-Day Target)

For organizations targeting a faster close:

Prerequisites for a 3-day close:

• Automated recurring journal entries (depreciation, amortization, standard accruals)
• Continuous reconciliation during the month (not all at month-end)
• Automated intercompany elimination
• Pre-close activities completed before month-end (cut-off, accrual estimates)
• Empowered team with clear ownership and minimal handoffs
• Real-time or near-real-time sub-system integration

Close Process Improvement

Common Bottlenecks and Solutions

Close Retrospective Questions

After each close, ask:

1. What went well this close that we should continue?
2. What took longer than expected and why?
3. What blockers did we encounter and how can we prevent them?
4. Were there any surprises in the financial results we should have caught earlier?
5. What can we automate or streamline for next month?

/financial-statements

If you see unfamiliar placeholders or need to check which tools are connected, see CONNECTORS.md.

Important: This command assists with financial statement workflows but does not provide financial advice. All statements should be reviewed by qualified financial professionals before use in reporting or filings.

Generate financial statements with period-over-period comparison and variance analysis. The workflow below walks through income statement generation; balance sheet and cash flow statement reference formats, GAAP presentation requirements (ASC 220/210/230), and common period-end adjustments are included as supporting reference material.

Usage

/financial-statements <period-type> <period>

Arguments

• period-type — The reporting period type:
  • monthly — Single month P&L with prior month and prior year month comparison
  • quarterly — Quarter P&L with prior quarter and prior year quarter comparison
  • annual — Full year P&L with prior year comparison
  • ytd — Year-to-date P&L with prior year YTD comparison
• period — The period to report (e.g., 2024-12, 2024-Q4, 2024)

Workflow

1. Gather Financial Data

If ~~erp or ~~data warehouse is connected:

• Pull trial balance or income statement data for the specified period
• Pull comparison period data (prior period, prior year, budget/forecast)
• Pull account hierarchy and groupings for presentation

If no data source is connected:

Connect ~~erp or ~~data warehouse to pull financial data automatically. You can also paste trial balance data, upload a spreadsheet, or provide income statement data for analysis.

Prompt the user to provide:

• Current period revenue and expense data (by account or category)
• Comparison period data (prior period, prior year, and/or budget)
• Any known adjustments or reclassifications

2. Generate Income Statement

Present in standard multi-column format:

INCOME STATEMENT
Period: [Period description]
(in thousands, unless otherwise noted)

                              Current    Prior      Variance   Variance   Budget    Budget
                              Period     Period     ($)        (%)        Amount    Var ($)
                              --------   --------   --------   --------   --------  --------
REVENUE
  Product revenue             $XX,XXX    $XX,XXX    $X,XXX     X.X%       $XX,XXX   $X,XXX
  Service revenue             $XX,XXX    $XX,XXX    $X,XXX     X.X%       $XX,XXX   $X,XXX
  Other revenue               $XX,XXX    $XX,XXX    $X,XXX     X.X%       $XX,XXX   $X,XXX
                              --------   --------   --------              --------  --------
TOTAL REVENUE                 $XX,XXX    $XX,XXX    $X,XXX     X.X%       $XX,XXX   $X,XXX

COST OF REVENUE
  [Cost items]                $XX,XXX    $XX,XXX    $X,XXX     X.X%       $XX,XXX   $X,XXX
                              --------   --------   --------              --------  --------
GROSS PROFIT                  $XX,XXX    $XX,XXX    $X,XXX     X.X%       $XX,XXX   $X,XXX
  Gross Margin                XX.X%      XX.X%

OPERATING EXPENSES
  Research & development      $XX,XXX    $XX,XXX    $X,XXX     X.X%       $XX,XXX   $X,XXX
  Sales & marketing           $XX,XXX    $XX,XXX    $X,XXX     X.X%       $XX,XXX   $X,XXX
  General & administrative    $XX,XXX    $XX,XXX    $X,XXX     X.X%       $XX,XXX   $X,XXX
                              --------   --------   --------              --------  --------
TOTAL OPERATING EXPENSES      $XX,XXX    $XX,XXX    $X,XXX     X.X%       $XX,XXX   $X,XXX

OPERATING INCOME (LOSS)       $XX,XXX    $XX,XXX    $X,XXX     X.X%       $XX,XXX   $X,XXX
  Operating Margin            XX.X%      XX.X%

OTHER INCOME (EXPENSE)
  Interest income             $XX,XXX    $XX,XXX    $X,XXX     X.X%
  Interest expense           ($XX,XXX)  ($XX,XXX)   $X,XXX     X.X%
  Other, net                  $XX,XXX    $XX,XXX    $X,XXX     X.X%
                              --------   --------   --------
TOTAL OTHER INCOME (EXPENSE)  $XX,XXX    $XX,XXX    $X,XXX     X.X%

INCOME BEFORE TAXES           $XX,XXX    $XX,XXX    $X,XXX     X.X%
  Income tax expense          $XX,XXX    $XX,XXX    $X,XXX     X.X%
                              --------   --------   --------

NET INCOME (LOSS)             $XX,XXX    $XX,XXX    $X,XXX     X.X%       $XX,XXX   $X,XXX
  Net Margin                  XX.X%      XX.X%

3. Variance Analysis

For each line item, calculate and flag material variances.

Variance Calculation

For each line item, calculate:

• Dollar variance: Current period - Prior period (or current period - budget)
• Percentage variance: (Current - Prior) / |Prior| x 100
• Basis point change: For margins and ratios, express change in basis points (1 bp = 0.01%)

Materiality Thresholds

Define what constitutes a "material" variance requiring investigation. Common approaches:

• Fixed dollar threshold: Variances exceeding a set dollar amount (e.g., $50K, $100K)
• Percentage threshold: Variances exceeding a set percentage (e.g., 10%, 15%)
• Combined: Either the dollar OR percentage threshold is exceeded
• Scaled: Different thresholds for different line items based on their size and volatility

Example thresholds (adjust for your organization):

Variance Decomposition

Break down total variance into component drivers:

• Volume/quantity effect: Change in volume at prior period rates
• Rate/price effect: Change in rate/price at current period volume
• Mix effect: Shift in composition between items with different rates/margins
• New/discontinued items: Items present in one period but not the other
• One-time/non-recurring items: Items that are not expected to repeat
• Timing effect: Items shifting between periods (not a true change in run rate)
• Currency effect: Impact of FX rate changes on translated results