{"skill":{"slug":"dx-data-navigator","displayName":"Dx Data Navigator","summary":"Query Developer Experience (DX) data via the DX Data MCP server PostgreSQL database. Use this skill when analyzing developer productivity metrics, team perfo...","description":"---\nname: dx-data-navigator\ndescription: Query Developer Experience (DX) data via the DX Data MCP server PostgreSQL database. Use this skill when analyzing developer productivity metrics, team performance, PR/code review metrics, deployment frequency, incident data, AI tool adoption, survey responses, DORA metrics, or any engineering analytics. Triggers on questions about DX scores, team comparisons, cycle times, code quality, developer sentiment, AI coding assistant adoption, sprint velocity, or engineering KPIs.\n---\n\n# DX Data Navigator\n\n## Install\n\n```bash\nnpx skills add pskoett/pskoett-ai-skills/dx-data-navigator\n```\n\nQuery the DX Data Cloud PostgreSQL database using the `mcp__dx-mcp-server__queryData` tool.\n\n## Tool Usage\n\n```\nmcp__dx-mcp-server__queryData(sql: \"SELECT ...\")\n```\n\nAlways query `information_schema.columns` first if uncertain about table/column names:\n```sql\nSELECT column_name, data_type FROM information_schema.columns\nWHERE table_name = 'table_name' ORDER BY ordinal_position;\n```\n\n## Critical: Team Tables\n\nThree team table types exist - use the right one:\n\n| Table | Use Case |\n|-------|----------|\n| `dx_teams` | Current org structure, linking users to teams for PR/deployment metrics |\n| `dx_snapshot_teams` | Teams within DX survey snapshots (use for DX scores) |\n| `dx_versioned_teams` | Historical team structure at specific dates |\n\n**For DX survey scores:** Join through `dx_snapshot_teams`. Use GROUP BY to avoid duplicates (team names can appear multiple times across snapshot history):\n```sql\nSELECT st.name as team, i.name as metric, MAX(ts.score) as score, MAX(ts.vs_industry50) as vs_industry\nFROM dx_snapshot_team_scores ts\nJOIN dx_snapshot_teams st ON ts.snapshot_team_id = st.id\nJOIN dx_snapshot_items i ON ts.item_id = i.id AND i.snapshot_id = ts.snapshot_id\nWHERE ts.snapshot_id = (SELECT id FROM dx_snapshots ORDER BY end_date DESC LIMIT 1)\n  AND st.name = 'Your Team Name'\n  AND i.item_type = 'core4'\nGROUP BY st.name, i.name;\n```\n\n**For PR/deployment metrics by team:** Join through `dx_users` to `dx_teams`:\n```sql\nSELECT t.name, COUNT(*) as prs\nFROM pull_requests p\nJOIN dx_users u ON p.dx_user_id = u.id\nJOIN dx_teams t ON u.team_id = t.id\nWHERE p.merged IS NOT NULL GROUP BY t.name;\n```\n\n## Discovering Team Names\n\nQuery the database to find available teams:\n```sql\nSELECT name FROM dx_teams WHERE deleted_at IS NULL ORDER BY name;\n```\n\n## Data Domains\n\n### Core DX Metrics\nSurvey snapshots with team scores, benchmarks, and sentiment data.\n\n**Key tables:** `dx_snapshots`, `dx_snapshot_teams`, `dx_snapshot_items`, `dx_snapshot_team_scores`\n\n**dx_snapshots columns:** id, account_id, contributors, participation_rate, start_date (date), end_date (date)\n\n**dx_snapshot_teams columns:** id, snapshot_id, team_id, name, parent (boolean), flattened_parent, contributors, participation_rate\n\n**dx_snapshot_items columns:** id, snapshot_id, name, item_type, prompt, target_label\n\n**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\n\n**Item types in dx_snapshot_items:**\n- `core4`: Effectiveness, Impact, Quality, Speed\n- `kpi`: Ease of delivery, Engagement, Weekly time loss, Quality, Speed\n- `sentiment`: Deep work, Change Confidence, Documentation, Cross-team collaboration, Customer focus, Decision-making, etc.\n- `workflow`: Review wait time, CI wait time, Deploy frequency, PR merge frequency, AI time savings, Red tape, etc.\n- `workflow_averages`: Raw average values for workflow metrics (actual numbers, not percentiles)\n- `csat`: Tool satisfaction scores (e.g., code editors, issue trackers, CI/CD tools)\n\n```sql\n-- Latest snapshot info\nSELECT id, start_date, end_date, contributors, participation_rate\nFROM dx_snapshots ORDER BY end_date DESC LIMIT 1;\n\n-- Team scores for specific metric (use GROUP BY to dedupe)\nSELECT st.name as team, i.name as metric, MAX(ts.score) as score, MAX(ts.vs_industry50) as vs_industry\nFROM dx_snapshot_team_scores ts\nJOIN dx_snapshot_teams st ON ts.snapshot_team_id = st.id\nJOIN dx_snapshot_items i ON ts.item_id = i.id AND i.snapshot_id = ts.snapshot_id\nWHERE ts.snapshot_id = (SELECT id FROM dx_snapshots ORDER BY end_date DESC LIMIT 1)\n  AND st.name = 'Your Team Name'\n  AND i.item_type = 'core4'\nGROUP BY st.name, i.name;\n\n-- All teams comparison on one metric\nSELECT st.name as team, MAX(ts.score) as score, MAX(ts.vs_industry50) as vs_industry\nFROM dx_snapshot_team_scores ts\nJOIN dx_snapshot_teams st ON ts.snapshot_team_id = st.id\nJOIN dx_snapshot_items i ON ts.item_id = i.id AND i.snapshot_id = ts.snapshot_id\nWHERE ts.snapshot_id = (SELECT id FROM dx_snapshots ORDER BY end_date DESC LIMIT 1)\n  AND i.name = 'Effectiveness' AND i.item_type = 'core4'\n  AND st.parent = false\nGROUP BY st.name\nORDER BY score DESC NULLS LAST;\n```\n\n### Teams and Users\nOrganization structure, team hierarchies, user profiles.\n\n**Key tables:** `dx_teams`, `dx_users`, `dx_team_hierarchies`, `dx_groups`\n\n**dx_teams columns:** id, name, contributors, deleted_at\n\n**dx_users key columns:** id, name, email, team_id, ai_light_adoption_date, ai_moderate_adoption_date, ai_heavy_adoption_date\n\n```sql\n-- Teams with contributor counts\nSELECT name, contributors FROM dx_teams WHERE deleted_at IS NULL ORDER BY contributors DESC;\n\n-- Users with AI adoption status\nSELECT name, email, ai_heavy_adoption_date FROM dx_users\nWHERE ai_heavy_adoption_date IS NOT NULL ORDER BY ai_heavy_adoption_date DESC;\n\n-- Team members\nSELECT u.name, u.email FROM dx_users u\nJOIN dx_teams t ON u.team_id = t.id\nWHERE t.name = 'Your Team Name';\n```\n\n### Pull Requests\nPR metrics including cycle times, review wait times, and throughput.\n\n**Key tables:** `pull_requests`, `pull_request_reviews`, `repos`\n\n**pull_requests key columns:** id, dx_user_id, repo_id, title, base_ref, head_ref, additions, deletions, created, merged, closed, draft, bot_authored\n\n**Key metrics (all in seconds, divide by 3600 for hours):**\n- `open_to_merge`: Total PR cycle time\n- `open_to_first_review`: Time to first review\n- `open_to_first_approval`: Time to approval\n- Business hour variants: add `_business_hours` suffix\n\n```sql\n-- PR metrics by team last 30 days\nSELECT t.name, COUNT(*) as prs,\n       AVG(p.open_to_merge)/3600 as avg_hours_to_merge,\n       AVG(p.open_to_first_review)/3600 as avg_hours_to_first_review\nFROM pull_requests p\nJOIN dx_users u ON p.dx_user_id = u.id\nJOIN dx_teams t ON u.team_id = t.id\nWHERE p.merged IS NOT NULL AND p.created > NOW() - INTERVAL '30 days'\nGROUP BY t.name ORDER BY prs DESC;\n\n-- PR size distribution\nSELECT\n    CASE\n        WHEN additions + deletions < 50 THEN 'XS (<50)'\n        WHEN additions + deletions < 200 THEN 'S (50-199)'\n        WHEN additions + deletions < 500 THEN 'M (200-499)'\n        ELSE 'L (500+)'\n    END as size_bucket,\n    COUNT(*) as count,\n    AVG(open_to_merge)/3600 as avg_hours\nFROM pull_requests\nWHERE merged IS NOT NULL AND created > NOW() - INTERVAL '90 days'\nGROUP BY size_bucket ORDER BY avg_hours;\n```\n\n### Deployments and Incidents\nDeployment frequency, success rates, and incident tracking for DORA metrics.\n\n**Key tables:** `deployments`, `incidents`, `incident_services`\n\n**deployments columns:** id, service, repository, environment, deployed_at, success, commit_sha\n\n**incidents columns:** id, name, priority, source, source_url, started_at, resolved_at, started_to_resolved (seconds), deleted\n\n**Deployment environments:** dev, stage, prod, production\n**Incident priorities:** '1 - Critical', '2 - High', '3 - Moderate', '4 - Low', '5 - Planning'\n**Incident source:** Check `SELECT DISTINCT source FROM incidents` for available sources\n\n```sql\n-- Deploy frequency by environment\nSELECT environment, COUNT(*) FROM deployments\nWHERE deployed_at > NOW() - INTERVAL '30 days' GROUP BY environment;\n\n-- Deployment success rate\nSELECT\n    COUNT(*) as total,\n    COUNT(*) FILTER (WHERE success) as successful,\n    COUNT(*) FILTER (WHERE success)::float / COUNT(*) * 100 as success_rate\nFROM deployments WHERE deployed_at > NOW() - INTERVAL '30 days';\n\n-- Mean Time to Recovery (MTTR)\nSELECT AVG(started_to_resolved)/3600 as avg_hours_to_resolve\nFROM incidents\nWHERE resolved_at IS NOT NULL AND priority IN ('1 - Critical', '2 - High');\n\n-- Incidents by priority\nSELECT priority, COUNT(*) FROM incidents\nWHERE started_at > NOW() - INTERVAL '90 days' AND deleted = false\nGROUP BY priority ORDER BY priority;\n```\n\n### AI Tools\nAI coding assistant adoption tracking (e.g., GitHub Copilot).\n\n**Key tables:** `ai_tools`, `ai_tool_daily_metrics`, `github_copilot_daily_usages`, `github_users`\n\n**github_copilot_daily_usages columns:** id, login, date, enterprise_slug, active (boolean)\n\n**github_users columns:** id, login, verified_emails, bot, active\n\n**Linking Copilot to teams:** GitHub logins don't match DX user emails directly. Use `github_users.verified_emails` to link:\n```sql\n-- Copilot usage by team (via github_users email linking)\nSELECT t.name as team, COUNT(DISTINCT c.login) as active_copilot_users\nFROM github_copilot_daily_usages c\nJOIN github_users gu ON c.login = gu.login\nJOIN dx_users u ON gu.verified_emails = u.email\nJOIN dx_teams t ON u.team_id = t.id\nWHERE c.date > NOW() - INTERVAL '30 days' AND c.active = true\nGROUP BY t.name ORDER BY active_copilot_users DESC;\n```\n\n```sql\n-- Daily Copilot active users (overall)\nSELECT date, COUNT(*) FILTER (WHERE active) as active_users\nFROM github_copilot_daily_usages\nWHERE date > NOW() - INTERVAL '30 days'\nGROUP BY date ORDER BY date;\n\n-- Copilot adoption rate (latest day)\nSELECT\n    COUNT(DISTINCT login) FILTER (WHERE active) as active_users,\n    COUNT(DISTINCT login) as total_users,\n    COUNT(DISTINCT login) FILTER (WHERE active)::float / COUNT(DISTINCT login) * 100 as adoption_pct\nFROM github_copilot_daily_usages\nWHERE date = (SELECT MAX(date) FROM github_copilot_daily_usages);\n\n-- Weekly trend\nSELECT DATE_TRUNC('week', date) as week,\n       COUNT(DISTINCT login) FILTER (WHERE active) as active_users\nFROM github_copilot_daily_usages\nWHERE date > NOW() - INTERVAL '90 days'\nGROUP BY week ORDER BY week;\n```\n\n### Issue Tracking\nProject management data including issues, sprints, and cycle times (e.g., Jira).\n\n**Key tables:** `jira_issues`, `jira_projects`, `jira_sprints`, `jira_issue_sprints`, `jira_issue_types`, `jira_statuses`\n\n**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\n\n**jira_sprints columns:** id, name, state ('active', 'closed', 'future'), start_date, end_date, complete_date\n\n```sql\n-- Sprint velocity (last 5 closed sprints)\nSELECT s.name, SUM(i.story_points) as points, COUNT(*) as issues\nFROM jira_sprints s\nJOIN jira_issue_sprints jis ON s.id = jis.sprint_id\nJOIN jira_issues i ON jis.issue_id = i.id\nWHERE s.state = 'closed' AND i.completed_at IS NOT NULL\nGROUP BY s.id, s.name ORDER BY s.complete_date DESC LIMIT 5;\n\n-- Issue cycle time by type\nSELECT it.name as issue_type, COUNT(*) as issues, AVG(i.cycle_time)/3600 as avg_hours\nFROM jira_issues i\nJOIN jira_issue_types it ON i.issue_type_id = it.id\nWHERE i.completed_at IS NOT NULL AND i.completed_at > NOW() - INTERVAL '90 days'\nGROUP BY it.name ORDER BY issues DESC;\n```\n\n### Service Catalog\nSoftware catalog with services, teams, domains, and ownership.\n\n**Key tables:** `dx_catalog_entities`, `dx_catalog_entity_owners`, `dx_catalog_entity_types`\n\n**dx_catalog_entities columns:** id, name, identifier, entity_type_identifier, description\n\n**Entity types:** service, team, domain (check `entity_type_identifier` column)\n\n```sql\n-- Services count by owning team\nSELECT t.name as team, COUNT(*) as services\nFROM dx_catalog_entity_owners eo\nJOIN dx_catalog_entities e ON eo.entity_id = e.id\nJOIN dx_teams t ON eo.team_id = t.id\nWHERE e.entity_type_identifier = 'service'\nGROUP BY t.name ORDER BY services DESC;\n\n-- List services with owners\nSELECT e.name as service, e.identifier, t.name as owner_team\nFROM dx_catalog_entities e\nJOIN dx_catalog_entity_owners eo ON e.id = eo.entity_id\nJOIN dx_teams t ON eo.team_id = t.id\nWHERE e.entity_type_identifier = 'service'\nORDER BY t.name, e.name;\n```\n\n### Pipelines and Code Quality\nCI/CD pipeline runs and code quality metrics (e.g., SonarCloud).\n\n**Key tables:** `pipeline_runs`, `sonarcloud_issues`, `sonarcloud_projects`, `sonarcloud_project_metrics`\n\n**pipeline_runs columns:** id, status, started_at, completed_at, duration\n\n```sql\n-- Pipeline success rate\nSELECT COUNT(*) as runs,\n       COUNT(*) FILTER (WHERE status = 'success') as successful,\n       COUNT(*) FILTER (WHERE status = 'success') * 100.0 / COUNT(*) as success_pct\nFROM pipeline_runs WHERE started_at > NOW() - INTERVAL '30 days';\n\n-- Pipeline duration trend\nSELECT DATE_TRUNC('week', started_at) as week,\n       AVG(duration)/60 as avg_minutes\nFROM pipeline_runs WHERE started_at > NOW() - INTERVAL '90 days'\nGROUP BY week ORDER BY week;\n```\n\n### Issues\nNormalized issue data from source control platforms (e.g., GitHub Issues).\n\n**Key tables:** `issues`, `github_issues`, `github_issue_labels`, `github_labels`\n\n**issues columns:** id, source, dx_user_id, title, state, created, completed, cycle_time\n\n```sql\n-- Issue throughput\nSELECT DATE_TRUNC('week', completed) as week, COUNT(*) as completed\nFROM issues WHERE completed > NOW() - INTERVAL '90 days'\nGROUP BY week ORDER BY week;\n```\n\n### Documentation\nDocumentation and knowledge base activity (e.g., Confluence, wikis).\n\n**Key tables:** `confluence_spaces`, `confluence_pages`, `confluence_page_versions`, `confluence_users`, `confluence_page_labels`\n\n**confluence_spaces columns:** id, name, external_key, space_type, status, source_url, created_at\n\n**confluence_pages columns:** id, space_id, author_id, title, status, views_count, created_at, updated_at\n\n**confluence_page_versions columns:** id, page_id, version_number, author_id, created_at\n\n```sql\n-- Most active Confluence spaces\nSELECT s.name as space_name, s.external_key,\n       COUNT(DISTINCT p.id) as page_count,\n       COUNT(DISTINCT pv.id) as total_edits,\n       MAX(pv.created_at) as last_activity\nFROM confluence_spaces s\nLEFT JOIN confluence_pages p ON s.id = p.space_id\nLEFT JOIN confluence_page_versions pv ON p.id = pv.page_id\nGROUP BY s.id, s.name, s.external_key\nORDER BY total_edits DESC LIMIT 15;\n\n-- Recent documentation activity\nSELECT p.title, s.name as space, pv.created_at\nFROM confluence_page_versions pv\nJOIN confluence_pages p ON pv.page_id = p.id\nJOIN confluence_spaces s ON p.space_id = s.id\nWHERE pv.created_at > NOW() - INTERVAL '7 days'\nORDER BY pv.created_at DESC LIMIT 20;\n```\n\n## Data Quality Notes\n\n**Known issues:**\n- Some team names may have typos - verify names by querying `dx_teams`\n- `incident_services` table is empty - incidents cannot be linked to specific services\n- `dx_users` AI adoption date fields are mostly NULL - use `github_copilot_daily_usages` instead\n- DX survey scores may have duplicates - always use GROUP BY with MAX() aggregation\n\n## Common Query Patterns\n\n### DORA Metrics\n```sql\n-- Deployment Frequency (daily average, production only)\nSELECT COUNT(*)::float / 30 as deploys_per_day FROM deployments\nWHERE deployed_at > NOW() - INTERVAL '30 days' AND environment IN ('prod', 'production');\n\n-- Lead Time for Changes (PR cycle time)\nSELECT\n    AVG(open_to_merge)/3600 as avg_hours,\n    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY open_to_merge)/3600 as median_hours\nFROM pull_requests\nWHERE merged IS NOT NULL AND created > NOW() - INTERVAL '30 days';\n\n-- Mean Time to Recovery\nSELECT AVG(started_to_resolved)/3600 as mttr_hours FROM incidents\nWHERE resolved_at IS NOT NULL AND priority IN ('1 - Critical', '2 - High')\n  AND started_at > NOW() - INTERVAL '90 days';\n\n-- Change Failure Rate (requires correlating incidents with deployments)\n```\n\n### Time-based Trends\n```sql\n-- Weekly PR throughput trend\nSELECT DATE_TRUNC('week', merged) as week, COUNT(*) as prs\nFROM pull_requests WHERE merged > NOW() - INTERVAL '90 days'\nGROUP BY week ORDER BY week;\n\n-- Monthly deployment trend\nSELECT DATE_TRUNC('month', deployed_at) as month, COUNT(*) as deploys\nFROM deployments WHERE deployed_at > NOW() - INTERVAL '12 months'\nGROUP BY month ORDER BY month;\n```\n\n### Historical DX Survey Comparison\n```sql\n-- Compare team scores across all surveys\nSELECT s.end_date as survey_date, i.name as metric, ts.score\nFROM dx_snapshot_team_scores ts\nJOIN dx_snapshots s ON ts.snapshot_id = s.id\nJOIN dx_snapshot_teams st ON ts.snapshot_team_id = st.id AND st.snapshot_id = s.id\nJOIN dx_snapshot_items i ON ts.item_id = i.id AND i.snapshot_id = s.id\nWHERE st.name = 'Your Team Name'\n  AND i.item_type = 'core4'\n  AND ts.score IS NOT NULL\nORDER BY s.end_date, i.name;\n\n-- Teams that improved most since last survey (use vs_prev)\nSELECT st.name as team, i.name as metric, MAX(ts.score) as score, MAX(ts.vs_prev) as change\nFROM dx_snapshot_team_scores ts\nJOIN dx_snapshot_teams st ON ts.snapshot_team_id = st.id\nJOIN dx_snapshot_items i ON ts.item_id = i.id AND i.snapshot_id = ts.snapshot_id\nWHERE ts.snapshot_id = (SELECT id FROM dx_snapshots ORDER BY end_date DESC LIMIT 1)\n  AND i.name = 'Effectiveness' AND i.item_type = 'core4'\n  AND st.parent = false\nGROUP BY st.name, i.name\nORDER BY change DESC NULLS LAST;\n```\n\n### Tool Satisfaction Analysis\n```sql\n-- Tool satisfaction scores (csat)\nSELECT i.name as tool, AVG(ts.score) as avg_satisfaction, COUNT(DISTINCT st.name) as teams_using\nFROM dx_snapshot_team_scores ts\nJOIN dx_snapshot_teams st ON ts.snapshot_team_id = st.id\nJOIN dx_snapshot_items i ON ts.item_id = i.id AND i.snapshot_id = ts.snapshot_id\nWHERE ts.snapshot_id = (SELECT id FROM dx_snapshots ORDER BY end_date DESC LIMIT 1)\n  AND i.item_type = 'csat' AND st.parent = false AND ts.score IS NOT NULL\nGROUP BY i.name ORDER BY avg_satisfaction ASC;\n```\n\n## Reference Files\n\nFor detailed schema documentation, read these files:\n\n| Domain | File | When to read |\n|--------|------|--------------|\n| DX Surveys/Scores | references/developer-experience.md | Survey data, snapshots, team scores, sentiment |\n| Teams/Users | references/teams-users.md | Team structure, user profiles, AI adoption dates |\n| Pull Requests | references/pull-requests.md | PR metrics, reviews, cycle times |\n| Deployments | references/deployments-incidents.md | Deploy frequency, incidents, DORA metrics |\n| AI Tools | references/ai-tools.md | AI assistant usage, adoption tracking |\n| Issue Tracking | references/jira.md | Issues, sprints, story points |\n| Catalog | references/catalog.md | Services, ownership, domains |\n| Pipelines/Quality | references/pipelines-quality.md | CI/CD runs, code quality issues |\n| Issues | references/issues-github.md | Source control issues, labels |\n","tags":{"latest":"1.0.0"},"stats":{"comments":0,"downloads":1237,"installsAllTime":2,"installsCurrent":2,"stars":0,"versions":1},"createdAt":1772726704434,"updatedAt":1778491738336},"latestVersion":{"version":"1.0.0","createdAt":1772726704434,"changelog":"Initial release of dx-data-navigator.\n\n- Enables querying Developer Experience (DX) analytics from the DX Data MCP server's PostgreSQL database.\n- Supports analysis of productivity metrics, team performance, PR/code review data, deployment frequency, incident tracking, AI tool adoption, survey results, and DORA metrics.\n- Provides specific SQL usage instructions and table relationships for accurate data retrieval.\n- Includes detailed examples for retrieving DX survey scores, PR metrics, team/user info, deployments, and incidents.\n- Guides on discovering schema and team names to assist in custom analytics queries.","license":null},"metadata":null,"owner":{"handle":"xiao1804","userId":"s179hfary78qfxgd6cf35kzrs583q7cf","displayName":"Xiao1804","image":"https://avatars.githubusercontent.com/u/34759843?v=4"},"moderation":{"isSuspicious":false,"isMalwareBlocked":false,"verdict":"clean","reasonCodes":["review.llm_review"],"summary":"Review: review.llm_review","engineVersion":"v2.4.24","updatedAt":1780089765979}}