DX Data Navigator

Install

CODEBLOCK0

Query the DX Data Cloud PostgreSQL database using the mcp__dx-mcp-server__queryData tool.

Tool Usage

CODEBLOCK1

Always query information_schema.columns first if uncertain about table/column names:
CODEBLOCK2

Critical: Team Tables

Three team table types exist - use the right one:

Table	Use Case
INLINECODE2	Current org structure, linking users to teams for PR/deployment metrics
INLINECODE3

Teams within DX survey snapshots (use for DX scores) |
| dx_versioned_teams | Historical team structure at specific dates |

For DX survey scores: Join through dx_snapshot_teams. Use GROUP BY to avoid duplicates (team names can appear multiple times across snapshot history):
CODEBLOCK3

For PR/deployment metrics by team: Join through dx_users to dx_teams:
CODEBLOCK4

Discovering Team Names

Query the database to find available teams:
CODEBLOCK5

Data Domains

Core DX Metrics

Survey snapshots with team scores, benchmarks, and sentiment data.

Key tables: dx_snapshots, dx_snapshot_teams, dx_snapshot_items, INLINECODE11

dxsnapshots columns: id, accountid, contributors, participationrate, startdate (date), end_date (date)

dxsnapshotteams columns: id, snapshotid, teamid, name, parent (boolean), flattenedparent, contributors, participationrate

dxsnapshotitems columns: id, snapshotid, name, itemtype, prompt, target_label

dxsnapshotteamscores columns: id, snapshotid, snapshotteamid (FK to dxsnapshotteams.id), teamid (FK to dxteams.id), itemid (FK to dxsnapshotitems.id), score, vsorg, vsprev, vsindustry50, vsindustry75, vsindustry90, unit

Item types in dxsnapshotitems:

- core4: Effectiveness, Impact, Quality, Speed
INLINECODE13: Ease of delivery, Engagement, Weekly time loss, Quality, Speed
INLINECODE14: Deep work, Change Confidence, Documentation, Cross-team collaboration, Customer focus, Decision-making, etc.
INLINECODE15: Review wait time, CI wait time, Deploy frequency, PR merge frequency, AI time savings, Red tape, etc.
INLINECODE16: Raw average values for workflow metrics (actual numbers, not percentiles)
INLINECODE17: Tool satisfaction scores (e.g., code editors, issue trackers, CI/CD tools)

CODEBLOCK6

Teams and Users

Organization structure, team hierarchies, user profiles.

Key tables: dx_teams, dx_users, dx_team_hierarchies, INLINECODE21

dxteams columns: id, name, contributors, deletedat

dxusers key columns: id, name, email, teamid, ailightadoptiondate, aimoderateadoptiondate, aiheavyadoption_date

CODEBLOCK7

Pull Requests

PR metrics including cycle times, review wait times, and throughput.

Key tables: pull_requests, pull_request_reviews, INLINECODE24

pullrequests key columns: id, dxuserid, repoid, title, baseref, headref, additions, deletions, created, merged, closed, draft, bot_authored

Key metrics (all in seconds, divide by 3600 for hours):

- open_to_merge: Total PR cycle time
INLINECODE26: Time to first review
INLINECODE27: Time to approval
Business hour variants: add _business_hours suffix

CODEBLOCK8

Deployments and Incidents

Deployment frequency, success rates, and incident tracking for DORA metrics.

Key tables: deployments, incidents, INLINECODE31

deployments columns: id, service, repository, environment, deployedat, success, commitsha

incidents columns: id, name, priority, source, sourceurl, startedat, resolvedat, startedto_resolved (seconds), deleted

Deployment environments: dev, stage, prod, production
Incident priorities: '1 - Critical', '2 - High', '3 - Moderate', '4 - Low', '5 - Planning'
Incident source: Check SELECT DISTINCT source FROM incidents for available sources

CODEBLOCK9

AI Tools

AI coding assistant adoption tracking (e.g., GitHub Copilot).

Key tables: ai_tools, ai_tool_daily_metrics, github_copilot_daily_usages, INLINECODE36

githubcopilotdailyusages columns: id, login, date, enterpriseslug, active (boolean)

githubusers columns: id, login, verifiedemails, bot, active

Linking Copilot to teams: GitHub logins don't match DX user emails directly. Use github_users.verified_emails to link:
CODEBLOCK10

CODEBLOCK11

Issue Tracking

Project management data including issues, sprints, and cycle times (e.g., Jira).

Key tables: jira_issues, jira_projects, jira_sprints, jira_issue_sprints, jira_issue_types, INLINECODE43

jiraissues key columns: id, key, summary, storypoints, cycletime (seconds), createdat, completedat, projectid, statusid, issuetypeid, userid

jirasprints columns: id, name, state ('active', 'closed', 'future'), startdate, enddate, completedate

CODEBLOCK12

Service Catalog

Software catalog with services, teams, domains, and ownership.

Key tables: dx_catalog_entities, dx_catalog_entity_owners, INLINECODE46

dxcatalogentities columns: id, name, identifier, entitytypeidentifier, description

Entity types: service, team, domain (check entity_type_identifier column)

CODEBLOCK13

Pipelines and Code Quality

CI/CD pipeline runs and code quality metrics (e.g., SonarCloud).

Key tables: pipeline_runs, sonarcloud_issues, sonarcloud_projects, INLINECODE51

pipelineruns columns: id, status, startedat, completed_at, duration

CODEBLOCK14

Issues

Normalized issue data from source control platforms (e.g., GitHub Issues).

Key tables: issues, github_issues, github_issue_labels, INLINECODE55

issues columns: id, source, dxuserid, title, state, created, completed, cycle_time

CODEBLOCK15

Documentation

Documentation and knowledge base activity (e.g., Confluence, wikis).

Key tables: confluence_spaces, confluence_pages, confluence_page_versions, confluence_users, INLINECODE60

confluencespaces columns: id, name, externalkey, spacetype, status, sourceurl, created_at

confluencepages columns: id, spaceid, authorid, title, status, viewscount, createdat, updatedat

confluencepageversions columns: id, pageid, versionnumber, authorid, createdat

CODEBLOCK16

Data Quality Notes

Known issues:

- Some team names may have typos - verify names by querying INLINECODE61
INLINECODE62 table is empty - incidents cannot be linked to specific services
INLINECODE63 AI adoption date fields are mostly NULL - use github_copilot_daily_usages instead
DX survey scores may have duplicates - always use GROUP BY with MAX() aggregation

Common Query Patterns

DORA Metrics

CODEBLOCK17

Time-based Trends

CODEBLOCK18

Historical DX Survey Comparison

CODEBLOCK19

Tool Satisfaction Analysis

CODEBLOCK20

Reference Files

For detailed schema documentation, read these files:

Domain	File	When to read
DX Surveys/Scores	references/developer-experience.md	Survey data, snapshots, team scores, sentiment
Teams/Users

DX Data Navigator

安装

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

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

工具使用

mcpdx-mcp-serverqueryData(sql: SELECT ...)

如果不确定表名或列名，请先查询 information_schema.columns：
sql
SELECT columnname, datatype FROM information_schema.columns
WHERE tablename = tablename ORDER BY ordinal_position;

关键：团队表

存在三种团队表类型——请使用正确的表：

表	使用场景
dxteams	当前组织架构，将用户关联到团队以获取 PR/部署指标
dxsnapshot_teams

DX 调查快照中的团队（用于 DX 评分） |
| dxversionedteams | 特定日期的历史团队架构 |

对于 DX 调查评分： 通过 dxsnapshotteams 进行关联。使用 GROUP BY 避免重复（团队名称可能在快照历史中多次出现）：
sql
SELECT st.name as team, i.name as metric, MAX(ts.score) as score, MAX(ts.vsindustry50) as vsindustry
FROM dxsnapshotteam_scores ts
JOIN dxsnapshotteams st ON ts.snapshotteamid = st.id
JOIN dxsnapshotitems i ON ts.itemid = i.id AND i.snapshotid = ts.snapshot_id
WHERE ts.snapshotid = (SELECT id FROM dxsnapshots 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/部署指标： 通过 dxusers 关联到 dxteams：
sql
SELECT t.name, COUNT(*) as prs
FROM pull_requests p
JOIN dxusers u ON p.dxuser_id = u.id
JOIN dxteams t ON u.teamid = t.id
WHERE p.merged IS NOT NULL GROUP BY t.name;

发现团队名称

查询数据库以查找可用的团队：
sql
SELECT name FROM dxteams WHERE deletedat IS NULL ORDER BY name;

数据域

核心 DX 指标

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

关键表： dxsnapshots, dxsnapshotteams, dxsnapshotitems, dxsnapshotteamscores

dxsnapshots 列： id, accountid, contributors, participationrate, startdate (date), end_date (date)

dxsnapshotteams 列： id, snapshotid, teamid, name, parent (boolean), flattenedparent, contributors, participationrate

dxsnapshotitems 列： id, snapshotid, name, itemtype, prompt, target_label

dxsnapshotteamscores 列： id, snapshotid, snapshotteamid (FK to dxsnapshotteams.id), teamid (FK to dxteams.id), itemid (FK to dxsnapshotitems.id), score, vsorg, vsprev, vsindustry50, vsindustry75, vsindustry90, unit

dxsnapshotitems 中的项目类型：

- core4：有效性、影响力、质量、速度
kpi：交付便捷性、参与度、每周时间损失、质量、速度
sentiment：深度工作、变革信心、文档、跨团队协作、客户导向、决策等
workflow：审查等待时间、CI 等待时间、部署频率、PR 合并频率、AI 节省时间、繁文缛节等
workflow_averages：工作流指标的原始平均值（实际数值，而非百分位数）
csat：工具满意度评分（例如代码编辑器、问题跟踪器、CI/CD 工具）

sql
-- 最新快照信息
SELECT id, startdate, enddate, contributors, participation_rate
FROM dxsnapshots ORDER BY enddate DESC LIMIT 1;

-- 特定指标的团队评分（使用 GROUP BY 去重）
SELECT st.name as team, i.name as metric, MAX(ts.score) as score, MAX(ts.vsindustry50) as vsindustry
FROM dxsnapshotteam_scores ts
JOIN dxsnapshotteams st ON ts.snapshotteamid = st.id
JOIN dxsnapshotitems i ON ts.itemid = i.id AND i.snapshotid = ts.snapshot_id
WHERE ts.snapshotid = (SELECT id FROM dxsnapshots ORDER BY end_date DESC LIMIT 1)
AND st.name = Your Team Name
AND i.item_type = core4
GROUP BY st.name, i.name;

-- 所有团队在某一指标上的比较
SELECT st.name as team, MAX(ts.score) as score, MAX(ts.vsindustry50) as vsindustry
FROM dxsnapshotteam_scores ts
JOIN dxsnapshotteams st ON ts.snapshotteamid = st.id
JOIN dxsnapshotitems i ON ts.itemid = i.id AND i.snapshotid = ts.snapshot_id
WHERE ts.snapshotid = (SELECT id FROM dxsnapshots 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;

团队与用户

组织架构、团队层级、用户档案。

关键表： dxteams, dxusers, dxteamhierarchies, dx_groups

dxteams 列： id, name, contributors, deletedat

dxusers 关键列： id, name, email, teamid, ailightadoptiondate, aimoderateadoptiondate, aiheavyadoption_date

sql
-- 团队及其贡献者数量
SELECT name, contributors FROM dxteams WHERE deletedat IS NULL ORDER BY contributors DESC;

-- 用户及其 AI 采用状态
SELECT name, email, aiheavyadoptiondate FROM dxusers
WHERE aiheavyadoptiondate IS NOT NULL ORDER BY aiheavyadoptiondate DESC;

-- 团队成员
SELECT u.name, u.email FROM dx_users u
JOIN dxteams t ON u.teamid = t.id
WHERE t.name = Your Team Name;

拉取请求

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

关键表： pullrequests, pullrequest_reviews, repos

pullrequests 关键列： id, dxuserid, repoid, title, baseref, headref, additions, deletions, created, merged, closed, draft, bot_authored

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

- opentomerge：PR 总周期时间
opentofirstreview：首次审查时间
opentofirstapproval：批准时间
工作小时变体：添加 businesshours 后缀

sql
-- 过去 30 天按团队统计的 PR 指标
SELECT t.name, COUNT(*) as prs,
AVG(p.opentomerge)/3600 as avghoursto_merge,
AVG(p.opentofirstreview)/3600 as avghourstofirst_review
FROM pull_requests p
JOIN dxusers u ON p.dxuser_id = u.id
JOIN dxteams t ON u.teamid = t.id
WHERE p.merged IS NOT NULL AND p.created > NOW() - INTERVAL 30 days
GROUP BY t.name ORDER BY prs DESC;

-- PR 大小分布
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(opentomerge)/3600 as avg_hours
FROM pull_requests
WHERE merged IS NOT NULL AND created > NOW() - INTERVAL 90 days
GROUP BY sizebucket ORDER BY avghours;

部署与事件

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

关键表： deployments, incidents, incident_services

deployments 列： id, service, repository, environment, deployedat, success, commitsha

incidents 列： id

dx-data-navigatorDX数据导航