DX Data Navigator

Install

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

Fallback using the Agent Skills CLI:

npx skills add pskoett/pskoett-skills/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