dx-data-navigator

仓库: pskoett/pskoett-ai-skills
安装量: 229
排名: #3830

安装

npx skills add https://github.com/pskoett/pskoett-ai-skills --skill dx-data-navigator
DX Data Navigator
Install
npx skills
add
pskoett/pskoett-ai-skills/dx-data-navigator
Query the DX Data Cloud PostgreSQL database using the
mcp__dx-mcp-server__queryData
tool.
Tool Usage
mcp__dx-mcp-server__queryData(sql: "SELECT ...")
Always query
information_schema.columns
first if uncertain about table/column names:
SELECT
column_name
,
data_type
FROM
information_schema
.
columns
WHERE
table_name
=
'table_name'
ORDER
BY
ordinal_position
;
Critical: Team Tables
Three team table types exist - use the right one:
Table
Use Case
dx_teams
Current org structure, linking users to teams for PR/deployment metrics
dx_snapshot_teams
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):
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
;
For PR/deployment metrics by team:
Join through
dx_users
to
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
;
Discovering Team Names
Query the database to find available teams:
SELECT
name
FROM
dx_teams
WHERE
deleted_at
IS
NULL
ORDER
BY
name
;
Data Domains
Core DX Metrics
Survey snapshots with team scores, benchmarks, and sentiment data.
Key tables:
dx_snapshots
,
dx_snapshot_teams
,
dx_snapshot_items
,
dx_snapshot_team_scores
dx_snapshots columns:
id, account_id, contributors, participation_rate, start_date (date), end_date (date)
dx_snapshot_teams columns:
id, snapshot_id, team_id, name, parent (boolean), flattened_parent, contributors, participation_rate
dx_snapshot_items columns:
id, snapshot_id, name, item_type, prompt, target_label
dx_snapshot_team_scores columns:
id, snapshot_id, snapshot_team_id (FK to dx_snapshot_teams.id), team_id (FK to dx_teams.id), item_id (FK to dx_snapshot_items.id), score, vs_org, vs_prev, vs_industry50, vs_industry75, vs_industry90, unit
Item types in 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, etc.
workflow
Review wait time, CI wait time, Deploy frequency, PR merge frequency, AI time savings, Red tape, etc.
workflow_averages
Raw average values for workflow metrics (actual numbers, not percentiles)
csat
Tool satisfaction scores (e.g., code editors, issue trackers, CI/CD tools)
-- 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
;
Teams and Users
Organization structure, team hierarchies, user profiles.
Key tables:
dx_teams
,
dx_users
,
dx_team_hierarchies
,
dx_groups
dx_teams columns:
id, name, contributors, deleted_at
dx_users key columns:
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'
;
Pull Requests
PR metrics including cycle times, review wait times, and throughput.
Key tables:
pull_requests
,
pull_request_reviews
,
repos
pull_requests key columns:
id, dx_user_id, repo_id, title, base_ref, head_ref, 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
open_to_first_review
Time to first review
open_to_first_approval
Time to approval Business hour variants: add _business_hours suffix -- 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 ; Deployments and Incidents Deployment frequency, success rates, and incident tracking for DORA metrics. Key tables: deployments , incidents , incident_services deployments columns: id, service, repository, environment, deployed_at, success, commit_sha incidents columns: id, name, priority, source, source_url, started_at, resolved_at, started_to_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 -- 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 Tools AI coding assistant adoption tracking (e.g., GitHub Copilot). Key tables: ai_tools , ai_tool_daily_metrics , github_copilot_daily_usages , github_users github_copilot_daily_usages columns: id, login, date, enterprise_slug, active (boolean) github_users columns: id, login, verified_emails, bot, active Linking Copilot to teams: GitHub logins don't match DX user emails directly. Use github_users.verified_emails to link: -- 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 ; 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 , jira_statuses jira_issues key columns: id, key, summary, story_points, cycle_time (seconds), created_at, completed_at, project_id, status_id, issue_type_id, user_id jira_sprints columns: 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 ; Service Catalog Software catalog with services, teams, domains, and ownership. Key tables: dx_catalog_entities , dx_catalog_entity_owners , dx_catalog_entity_types dx_catalog_entities columns: id, name, identifier, entity_type_identifier, description Entity types: service, team, domain (check entity_type_identifier column) -- 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 ; Pipelines and Code Quality CI/CD pipeline runs and code quality metrics (e.g., SonarCloud). Key tables: pipeline_runs , sonarcloud_issues , sonarcloud_projects , sonarcloud_project_metrics pipeline_runs columns: 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 ; Issues Normalized issue data from source control platforms (e.g., GitHub Issues). Key tables: issues , github_issues , github_issue_labels , github_labels issues columns: 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 ; Documentation Documentation and knowledge base activity (e.g., Confluence, wikis). Key tables: confluence_spaces , confluence_pages , confluence_page_versions , confluence_users , confluence_page_labels confluence_spaces columns: id, name, external_key, space_type, status, source_url, created_at confluence_pages columns: id, space_id, author_id, title, status, views_count, created_at, updated_at confluence_page_versions columns: 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 ; Data Quality Notes Known issues: Some team names may have typos - verify names by querying dx_teams incident_services table is empty - incidents cannot be linked to specific services dx_users 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 -- 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) Time-based Trends -- 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 ; Historical DX Survey Comparison -- 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 Analysis -- 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 ; 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 references/teams-users.md Team structure, user profiles, AI adoption dates Pull Requests references/pull-requests.md PR metrics, reviews, cycle times Deployments references/deployments-incidents.md Deploy frequency, incidents, DORA metrics AI Tools references/ai-tools.md AI assistant usage, adoption tracking Issue Tracking references/jira.md Issues, sprints, story points Catalog references/catalog.md Services, ownership, domains Pipelines/Quality references/pipelines-quality.md CI/CD runs, code quality issues Issues references/issues-github.md Source control issues, labels

← 返回排行榜