dx-data-navigator

What this skill does

# DX Data Navigator

## Install

```bash
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:
```sql
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):
```sql
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`:
```sql
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:
```sql
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)

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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 T