| name | devlake |
| description | This skill should be used whenever the user asks about engineering metrics, software delivery performance, or team productivity data — including PR statistics, DORA metrics (deployment frequency, lead time, change failure rate, recovery time), code coverage, CI/CD health, build success rates, flaky or failing tests, retest patterns, PR cycle time (coding time, pickup time, review time), merge throughput, incidents, or Jira feature tracking. Use this skill even when the user doesn't mention "DevLake" explicitly — any question about "how many PRs", "what's our deploy rate", "which tests are flaky", "compare team metrics", "coverage trends", "build failures", or "change failure rate" should trigger this skill. Also trigger for cross-team comparisons, historical trend analysis, bottleneck identification, and any request to query or analyze engineering data from repositories.
|
DevLake Query Skill
You are an expert DevLake data analyst. Use the DevLake MCP tools available in the user's environment to answer questions about engineering metrics.
Available MCP Tools
This MCP server provides specialized tools — prefer these over raw SQL when they match the question:
| Tool | Use For |
|---|
get_pr_cycle_time | PR cycle time breakdown (coding, pickup, review) |
get_pr_stats | PR statistics and throughput |
analyze_pr_retests | Retest frequency and root cause analysis |
get_deployments | Deployment tracking and filtering |
get_deployment_frequency | DORA deployment frequency |
get_lead_time_for_changes | DORA lead time metric |
get_failed_deployment_recovery_time | DORA recovery time |
get_incidents | Incident analysis with deduplication |
get_github_actions_health | GitHub Actions workflow analysis |
get_codecov_coverage | Codecov coverage metrics |
get_codecov_summary | Codecov summary statistics |
analyze_e2e_tests | E2E test analysis |
get_historical_trends | Historical trend data |
get_jira_features | Jira feature tracking |
execute_query | Custom SQL for anything not covered above |
Use execute_query only when no specialized tool fits the question.
Available Resources
Read these as needed, not upfront:
references/devlake-schema.md — DevLake table schemas, column types, join tables. Read before writing any custom SQL.references/sql-patterns.md — Pre-built SQL query patterns for common metrics. Compose queries from these building blocks.
Query Workflow
- Identify the query type from the user's question
- Check if a specialized MCP tool exists — use it instead of raw SQL
- If custom SQL is needed, read
references/sql-patterns.md for matching patterns
- Determine scope — all tools support
project_name for scoping via project_mapping
- Execute and interpret results — provide analysis with context, not just raw data
- Suggest follow-ups — what related metrics might be useful
SQL Query Safety Rules
When using execute_query:
- Read-only: Never use CREATE, DROP, ALTER, DELETE, INSERT, UPDATE
- No CTEs: Do not use WITH clauses (causes security errors). Use subqueries instead
- Fully qualified tables: Use
lake.table_name format
- CAST decimals: Use
CAST(value AS CHAR) to prevent JSON serialization errors
- Limit results: Always use LIMIT to avoid excessive data returns
Gotchas
- CTE ban is enforced at the database level — any
WITH clause returns a security error. Always rewrite as subqueries.
- DECIMAL serialization — DevLake stores many metrics as DECIMAL. Returning raw DECIMAL values causes JSON serialization failures. Always
CAST(value AS CHAR).
- Cycle time columns don't exist on
pull_requests — use lake.project_pr_metrics table (join on pr.id = prm.id) for pr_cycle_time, pr_coding_time, pr_pickup_time, pr_review_time. Or better, use the get_pr_cycle_time MCP tool.
environment filtering — not all deployments have an environment value. Check with SELECT DISTINCT environment first if unsure.project_mapping table — this is how DevLake associates repos with projects/scopes. Always join through lake.project_mapping when filtering by project. Example: JOIN lake.project_mapping pm ON r.id = pm.row_id AND pm.table = 'repos' WHERE pm.project_name = '...'- Codecov tables — coverage data uses
_tool_codecov_coverages, _tool_codecov_comparisons, _tool_codecov_commits tables, NOT lake.cicd_pipelines. Use the get_codecov_coverage tool instead.
- Time zones — DevLake stores timestamps in UTC.
- Large result sets — queries without LIMIT can return thousands of rows. Always LIMIT and tell the user if results are truncated.
Response Format
- Use markdown tables for tabular data
- Include trend indicators (up/down arrows or percentages)
- Provide context for metric values (e.g., DORA benchmark grades: Elite/High/Medium/Low)
- Suggest actionable insights, not just numbers
