dx-data-navigator

DX Data Navigator

安装

gh skill install pskoett/pskoett-skills dx-data-navigator

也可以使用 Agent Skills CLI 作为备用方案：

npx skills add pskoett/pskoett-skills/skills/dx-data-navigator

使用 mcp__dx-mcp-server__queryData 工具查询 DX Data Cloud PostgreSQL 数据库。

工具用法

mcp__dx-mcp-server__queryData(sql: "SELECT ...")

如果不确定表名或列名，请务必先查询 information_schema.columns：

SELECT column_name, data_type FROM information_schema.columns
WHERE table_name = 'table_name' ORDER BY ordinal_position;

重要：团队表

存在三种团队表，请使用正确的表：

表	用途
dx_teams	当前组织结构，用于关联用户与团队，以获取 PR/部署指标
dx_snapshot_teams	DX 调查快照中的团队（用于 DX 分数）
dx_versioned_teams	特定日期的历史团队结构

**对于 DX 调查分数：**通过 dx_snapshot_teams 进行关联。使用 GROUP BY 避免重复（团队名称可能会在快照历史记录中多次出现）：

SELECT st.name as team, i.name as metric, MAX(ts.score) as score, MAX(ts.vs_industry50) as vs_industry
FROM dx_snapshot_team_scores ts
JOIN dx_snapshot_teams st ON ts.snapshot_team_id = st.id
JOIN dx_snapshot_items i ON ts.item_id = i.id AND i.snapshot_id = ts.snapshot_id
WHERE ts.snapshot_id = (SELECT id FROM dx_snapshots ORDER BY end_date DESC LIMIT 1)
  AND st.name = 'Your Team Name'
  AND i.item_type = 'core4'
GROUP BY st.name, i.name;

**对于按团队划分的 PR/部署指标：**通过 dx_users 关联到 dx_teams：

SELECT t.name, COUNT(*) as prs
FROM pull_requests p
JOIN dx_users u ON p.dx_user_id = u.id
JOIN dx_teams t ON u.team_id = t.id
WHERE p.merged IS NOT NULL GROUP BY t.name;

查找团队名称

查询数据库以查找可用团队：

SELECT name FROM dx_teams WHERE deleted_at IS NULL ORDER BY name;

数据领域

核心 DX 指标

包含团队分数、基准和情绪数据的调查快照。

主要表： dx_snapshots、dx_snapshot_teams、dx_snapshot_items、dx_snapshot_team_scores

dx_snapshots 列： id、account_id、contributors、participation_rate、start_date (date)、end_date (date)

dx_snapshot_teams 列： id、snapshot_id、team_id、name、parent (boolean)、flattened_parent、contributors、participation_rate

dx_snapshot_items 列： id、snapshot_id、name、item_type、prompt、target_label

dx_snapshot_team_scores 列： id、snapshot_id、snapshot_team_id（外键指向 dx_snapshot_teams.id）、team_id（外键指向 dx_teams.id）、item_id（外键指向 dx_snapshot_items.id）、score、vs_org、vs_prev、vs_industry50、vs_industry75、vs_industry90、unit

dx_snapshot_items 中的项目类型：

• core4：Effectiveness、Impact、Quality、Speed
• kpi：Ease of delivery、Engagement、Weekly time loss、Quality、Speed
• sentiment：Deep work、Change Confidence、Documentation、Cross-team collaboration、Customer focus、Decision-making 等
• workflow：Review wait time、CI wait time、Deploy frequency、PR merge frequency、AI time savings、Red tape 等
• workflow_averages：工作流指标的原始平均值（实际数值，而非百分位数）
• csat：工具满意度分数（例如代码编辑器、问题跟踪器、CI/CD 工具）

-- Latest snapshot info
SELECT id, start_date, end_date, contributors, participation_rate
FROM dx_snapshots ORDER BY end_date DESC LIMIT 1;

-- Team scores for specific metric (use GROUP BY to dedupe)
SELECT st.name as team, i.name as metric, MAX(ts.score) as score, MAX(ts.vs_industry50) as vs_industry
FROM dx_snapshot_team_scores ts
JOIN dx_snapshot_teams st ON ts.snapshot_team_id = st.id
JOIN dx_snapshot_items i ON ts.item_id = i.id AND i.snapshot_id = ts.snapshot_id
WHERE ts.snapshot_id = (SELECT id FROM dx_snapshots ORDER BY end_date DESC LIMIT 1)
  AND st.name = 'Your Team Name'
  AND i.item_type = 'core4'
GROUP BY st.name, i.name;

-- All teams comparison on one metric
SELECT st.name as team, MAX(ts.score) as score, MAX(ts.vs_industry50) as vs_industry
FROM dx_snapshot_team_scores ts
JOIN dx_snapshot_teams st ON ts.snapshot_team_id = st.id
JOIN dx_snapshot_items i ON ts.item_id = i.id AND i.snapshot_id = ts.snapshot_id
WHERE ts.snapshot_id = (SELECT id FROM dx_snapshots ORDER BY end_date DESC LIMIT 1)
  AND i.name = 'Effectiveness' AND i.item_type = 'core4'
  AND st.parent = false
GROUP BY st.name
ORDER BY score DESC NULLS LAST;

团队和用户

组织结构、团队层级和用户资料。

主要表： dx_teams、dx_users、dx_team_hierarchies、dx_groups

dx_teams 列： id、name、contributors、deleted_at

dx_users 关键列： id、name、email、team_id、ai_light_adoption_date、ai_moderate_adoption_date、ai_heavy_adoption_date

-- Teams with contributor counts
SELECT name, contributors FROM dx_teams WHERE deleted_at IS NULL ORDER BY contributors DESC;

-- Users with AI adoption status
SELECT name, email, ai_heavy_adoption_date FROM dx_users
WHERE ai_heavy_adoption_date IS NOT NULL ORDER BY ai_heavy_adoption_date DESC;

-- Team members
SELECT u.name, u.email FROM dx_users u
JOIN dx_teams t ON u.team_id = t.id
WHERE t.name = 'Your Team Name';

拉取请求

PR 指标，包括周期时间、评审等待时间和吞吐量。

主要表： pull_requests、pull_request_reviews、repos

pull_requests 关键列： id、dx_user_id、repo_id、title、base_ref、head_ref、additions、deletions、created、merged、closed、draft、bot_authored

关键指标（单位均为秒，除以 3600 可换算为小时）：

• open_to_merge：PR 总周期时间
• open_to_first_review：首次评审所需时间
• open_to_first_approval：获得批准所需时间
• 工作时间变体：添加 _business_hours 后缀

-- PR metrics by team last 30 days
SELECT t.name, COUNT(*) as prs,
       AVG(p.open_to_merge)/3600 as avg_hours_to_merge,
       AVG(p.open_to_first_review)/3600 as avg_hours_to_first_review
FROM pull_requests p
JOIN dx_users u ON p.dx_user_id = u.id
JOIN dx_teams t ON u.team_id = t.id
WHERE p.merged IS NOT NULL AND p.created > NOW() - INTERVAL '30 days'
GROUP BY t.name ORDER BY prs DESC;

-- PR size distribution
SELECT
    CASE
        WHEN additions + deletions < 50 THEN 'XS (<50)'
        WHEN additions + deletions < 200 THEN 'S (50-199)'
        WHEN additions + deletions < 500 THEN 'M (200-499)'
        ELSE 'L (500+)'
    END as size_bucket,
    COUNT(*) as count,
    AVG(open_to_merge)/3600 as avg_hours
FROM pull_requests
WHERE merged IS NOT NULL AND created > NOW() - INTERVAL '90 days'
GROUP BY size_bucket ORDER BY avg_hours;

部署和事件

用于 DORA 指标的部署频率、成功率和事件跟踪。

主要表： deployments、incidents、incident_services

deployments 列： id、service、repository、environment、deployed_at、success、commit_sha

incidents 列： id、name、priority、source、source_url、started_at、resolved_at、started_to_resolved (seconds)、deleted

部署环境： dev、stage、prod、production 事件优先级： '1 - Critical'、'2 - High'、'3 - Moderate'、'4 - Low'、'5 - Planning' 事件来源： 执行 SELECT DISTINCT source FROM incidents，查看可用来源

-- Deploy frequency by environment
SELECT environment, COUNT(*) FROM deployments
WHERE deployed_at > NOW() - INTERVAL '30 days' GROUP BY environment;

-- Deployment success rate
SELECT
    COUNT(*) as total,
    COUNT(*) FILTER (WHERE success) as successful,
    COUNT(*) FILTER (WHERE success)::float / COUNT(*) * 100 as success_rate
FROM deployments WHERE deployed_at > NOW() - INTERVAL '30 days';

-- Mean Time to Recovery (MTTR)
SELECT AVG(started_to_resolved)/3600 as avg_hours_to_resolve
FROM incidents
WHERE resolved_at IS NOT NULL AND priority IN ('1 - Critical', '2 - High');

-- Incidents by priority
SELECT priority, COUNT(*) FROM incidents
WHERE started_at > NOW() - INTERVAL '90 days' AND deleted = false
GROUP BY priority ORDER BY priority;

AI 工具

AI 编码助手采用情况跟踪（例如 GitHub Copilot）。

主要表： ai_tools、ai_tool_daily_metrics、github_copilot_daily_usages、github_users

github_copilot_daily_usages 列： id、login、date、enterprise_slug、active（布尔值）

github_users 列： id、login、verified_emails、bot、active

将 Copilot 与团队关联： GitHub 登录名与 DX 用户邮箱并不直接匹配。使用 github_users.verified_emails 进行关联：

-- Copilot usage by team (via github_users email linking)
SELECT t.name as team, COUNT(DISTINCT c.login) as active_copilot_users
FROM github_copilot_daily_usages c
JOIN github_users gu ON c.login = gu.login
JOIN dx_users u ON gu.verified_emails = u.email
JOIN dx_teams t ON u.team_id = t.id
WHERE c.date > NOW() - INTERVAL '30 days' AND c.active = true
GROUP BY t.name ORDER BY active_copilot_users DESC;

-- Daily Copilot active users (overall)
SELECT date, COUNT(*) FILTER (WHERE active) as active_users
FROM github_copilot_daily_usages
WHERE date > NOW() - INTERVAL '30 days'
GROUP BY date ORDER BY date;

-- Copilot adoption rate (latest day)
SELECT
    COUNT(DISTINCT login) FILTER (WHERE active) as active_users,
    COUNT(DISTINCT login) as total_users,
    COUNT(DISTINCT login) FILTER (WHERE active)::float / COUNT(DISTINCT login) * 100 as adoption_pct
FROM github_copilot_daily_usages
WHERE date = (SELECT MAX(date) FROM github_copilot_daily_usages);

-- Weekly trend
SELECT DATE_TRUNC('week', date) as week,
       COUNT(DISTINCT login) FILTER (WHERE active) as active_users
FROM github_copilot_daily_usages
WHERE date > NOW() - INTERVAL '90 days'
GROUP BY week ORDER BY week;

问题跟踪

项目管理数据，包括问题、冲刺和周期时间（例如 Jira）。

主要表： jira_issues、jira_projects、jira_sprints、jira_issue_sprints、jira_issue_types、jira_statuses

jira_issues 关键列： id、key、summary、story_points、cycle_time（秒）、created_at、completed_at、project_id、status_id、issue_type_id、user_id

jira_sprints 列： id、name、state（'active'、'closed'、'future'）、start_date、end_date、complete_date

-- Sprint velocity (last 5 closed sprints)
SELECT s.name, SUM(i.story_points) as points, COUNT(*) as issues
FROM jira_sprints s
JOIN jira_issue_sprints jis ON s.id = jis.sprint_id
JOIN jira_issues i ON jis.issue_id = i.id
WHERE s.state = 'closed' AND i.completed_at IS NOT NULL
GROUP BY s.id, s.name ORDER BY s.complete_date DESC LIMIT 5;

-- Issue cycle time by type
SELECT it.name as issue_type, COUNT(*) as issues, AVG(i.cycle_time)/3600 as avg_hours
FROM jira_issues i
JOIN jira_issue_types it ON i.issue_type_id = it.id
WHERE i.completed_at IS NOT NULL AND i.completed_at > NOW() - INTERVAL '90 days'
GROUP BY it.name ORDER BY issues DESC;

服务目录

包含服务、团队、领域和所有权信息的软件目录。

主要表： dx_catalog_entities、dx_catalog_entity_owners、dx_catalog_entity_types

dx_catalog_entities 列： id、name、identifier、entity_type_identifier、description

实体类型： service、team、domain（检查 entity_type_identifier 列）

-- Services count by owning team
SELECT t.name as team, COUNT(*) as services
FROM dx_catalog_entity_owners eo
JOIN dx_catalog_entities e ON eo.entity_id = e.id
JOIN dx_teams t ON eo.team_id = t.id
WHERE e.entity_type_identifier = 'service'
GROUP BY t.name ORDER BY services DESC;

-- List services with owners
SELECT e.name as service, e.identifier, t.name as owner_team
FROM dx_catalog_entities e
JOIN dx_catalog_entity_owners eo ON e.id = eo.entity_id
JOIN dx_teams t ON eo.team_id = t.id
WHERE e.entity_type_identifier = 'service'
ORDER BY t.name, e.name;

流水线和代码质量

CI/CD 流水线运行和代码质量指标（例如 SonarCloud）。

主要表： pipeline_runs、sonarcloud_issues、sonarcloud_projects、sonarcloud_project_metrics

pipeline_runs 列： id、status、started_at、completed_at、duration

-- Pipeline success rate
SELECT COUNT(*) as runs,
       COUNT(*) FILTER (WHERE status = 'success') as successful,
       COUNT(*) FILTER (WHERE status = 'success') * 100.0 / COUNT(*) as success_pct
FROM pipeline_runs WHERE started_at > NOW() - INTERVAL '30 days';

-- Pipeline duration trend
SELECT DATE_TRUNC('week', started_at) as week,
       AVG(duration)/60 as avg_minutes
FROM pipeline_runs WHERE started_at > NOW() - INTERVAL '90 days'
GROUP BY week ORDER BY week;

问题

来自源代码管理平台的规范化问题数据（例如 GitHub Issues）。

主要表： issues、github_issues、github_issue_labels、github_labels

issues 列： id、source、dx_user_id、title、state、created、completed、cycle_time

-- Issue throughput
SELECT DATE_TRUNC('week', completed) as week, COUNT(*) as completed
FROM issues WHERE completed > NOW() - INTERVAL '90 days'
GROUP BY week ORDER BY week;

文档

文档和知识库活动（例如 Confluence、维基）。

主要表： confluence_spaces、confluence_pages、confluence_page_versions、confluence_users、confluence_page_labels

confluence_spaces 列： id、name、external_key、space_type、status、source_url、created_at

confluence_pages 列： id、space_id、author_id、title、status、views_count、created_at、updated_at

confluence_page_versions 列： id、page_id、version_number、author_id、created_at

-- Most active Confluence spaces
SELECT s.name as space_name, s.external_key,
       COUNT(DISTINCT p.id) as page_count,
       COUNT(DISTINCT pv.id) as total_edits,
       MAX(pv.created_at) as last_activity
FROM confluence_spaces s
LEFT JOIN confluence_pages p ON s.id = p.space_id
LEFT JOIN confluence_page_versions pv ON p.id = pv.page_id
GROUP BY s.id, s.name, s.external_key
ORDER BY total_edits DESC LIMIT 15;

-- Recent documentation activity
SELECT p.title, s.name as space, pv.created_at
FROM confluence_page_versions pv
JOIN confluence_pages p ON pv.page_id = p.id
JOIN confluence_spaces s ON p.space_id = s.id
WHERE pv.created_at > NOW() - INTERVAL '7 days'
ORDER BY pv.created_at DESC LIMIT 20;

数据质量说明

已知问题：

• 部分团队名称可能存在拼写错误——请通过查询 dx_teams 验证名称
• incident_services 表为空——无法将事件关联到特定服务
• dx_users 中的 AI 采用日期字段大多为 NULL——请改用 github_copilot_daily_usages
• DX 调查得分可能存在重复项——始终使用带有 MAX() 聚合的 GROUP BY

常见查询模式

DORA 指标

-- Deployment Frequency (daily average, production only)
SELECT COUNT(*)::float / 30 as deploys_per_day FROM deployments
WHERE deployed_at > NOW() - INTERVAL '30 days' AND environment IN ('prod', 'production');

-- Lead Time for Changes (PR cycle time)
SELECT
    AVG(open_to_merge)/3600 as avg_hours,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY open_to_merge)/3600 as median_hours
FROM pull_requests
WHERE merged IS NOT NULL AND created > NOW() - INTERVAL '30 days';

-- Mean Time to Recovery
SELECT AVG(started_to_resolved)/3600 as mttr_hours FROM incidents
WHERE resolved_at IS NOT NULL AND priority IN ('1 - Critical', '2 - High')
  AND started_at > NOW() - INTERVAL '90 days';

-- Change Failure Rate (requires correlating incidents with deployments)

基于时间的趋势

-- Weekly PR throughput trend
SELECT DATE_TRUNC('week', merged) as week, COUNT(*) as prs
FROM pull_requests WHERE merged > NOW() - INTERVAL '90 days'
GROUP BY week ORDER BY week;

-- Monthly deployment trend
SELECT DATE_TRUNC('month', deployed_at) as month, COUNT(*) as deploys
FROM deployments WHERE deployed_at > NOW() - INTERVAL '12 months'
GROUP BY month ORDER BY month;

DX 调查历史对比

-- Compare team scores across all surveys
SELECT s.end_date as survey_date, i.name as metric, ts.score
FROM dx_snapshot_team_scores ts
JOIN dx_snapshots s ON ts.snapshot_id = s.id
JOIN dx_snapshot_teams st ON ts.snapshot_team_id = st.id AND st.snapshot_id = s.id
JOIN dx_snapshot_items i ON ts.item_id = i.id AND i.snapshot_id = s.id
WHERE st.name = 'Your Team Name'
  AND i.item_type = 'core4'
  AND ts.score IS NOT NULL
ORDER BY s.end_date, i.name;

-- Teams that improved most since last survey (use vs_prev)
SELECT st.name as team, i.name as metric, MAX(ts.score) as score, MAX(ts.vs_prev) as change
FROM dx_snapshot_team_scores ts
JOIN dx_snapshot_teams st ON ts.snapshot_team_id = st.id
JOIN dx_snapshot_items i ON ts.item_id = i.id AND i.snapshot_id = ts.snapshot_id
WHERE ts.snapshot_id = (SELECT id FROM dx_snapshots ORDER BY end_date DESC LIMIT 1)
  AND i.name = 'Effectiveness' AND i.item_type = 'core4'
  AND st.parent = false
GROUP BY st.name, i.name
ORDER BY change DESC NULLS LAST;

工具满意度分析

-- Tool satisfaction scores (csat)
SELECT i.name as tool, AVG(ts.score) as avg_satisfaction, COUNT(DISTINCT st.name) as teams_using
FROM dx_snapshot_team_scores ts
JOIN dx_snapshot_teams st ON ts.snapshot_team_id = st.id
JOIN dx_snapshot_items i ON ts.item_id = i.id AND i.snapshot_id = ts.snapshot_id
WHERE ts.snapshot_id = (SELECT id FROM dx_snapshots ORDER BY end_date DESC LIMIT 1)
  AND i.item_type = 'csat' AND st.parent = false AND ts.score IS NOT NULL
GROUP BY i.name ORDER BY avg_satisfaction ASC;

参考文件

如需了解详细的架构文档，请阅读以下文件：

领域	文件	阅读时机
DX 调查/得分	references/developer-experience.md	调查数据、快照、团队得分、情绪
团队/用户	references/teams-users.md	团队结构、用户资料、AI 采用日期
Pull Request	references/pull-requests.md	PR 指标、评审、周期时间
部署	references/deployments-incidents.md	部署频率、事件、DORA 指标
AI 工具	references/ai-tools.md	AI 助手使用情况、采用情况跟踪
问题跟踪	references/jira.md	问题、冲刺、故事点
目录	references/catalog.md	服务、所有权、领域
流水线/质量	references/pipelines-quality.md	CI/CD 运行、代码质量问题
问题	references/issues-github.md	源代码管理问题、标签