AI Tool Usage + AI Assistant + Team Velocity Algorithm — V2 Build-Out Playbook (T3 / T5 / T6)

AI Tool Usage + AI Assistant + Team Velocity Algorithm — V2 Build-Out Playbook (T3 / T5 / T6)

File: docs/playbooks/v2-team-velocity-playbook.md Base branch: dev (integration branch — main stays protected/production; PRs target dev) Branch naming: feature/<ado-task-id>-<slug>-v1-pr<n> (branch slugs keep their original v1 suffix — task IDs/branch names are unchanged by this playbook’s v1→v2 rename) Plan date: July 2026 (renamed to v2 2026-07-23) Owner: Fatima — all three tasks in this playbook (T3, T5, T6). Status: T3 and T6 are on hold (see blockers below); T5 is active/unblocked. All three are blocked on docs/playbooks/v3-platform-modernization-playbook.md merging to dev first — that playbook migrates public/ off the static-page shell (Next.js/Vue) and re-does its responsive layout, and this playbook’s UI-touching work (T5’s chat widget, T6’s KPI card) would otherwise be built against a shell that’s about to be replaced. T0/T1/T2 are already merged to dev (see the parent playbook, docs/playbooks/v1-dev-productivity-dashboard-playbook.md, now marked complete). T4 (commit-trailer attribution) was deleted outright — not relocated, not on hold. Source spec: uploads/AlphaBOLD_Dev_Productivity_Dashboard_Design.docx (extracted to scratch/extracted_text.txt), Sections 3–9. For T6 specifically, Section 3’s velocity row and store/schema.sql’s own comment on weekly_velocity (“Real story-point velocity via Azure Boards is a future connector; this PR uses commit count as the velocity proxy until Boards data is wired in”) are the direct source of the gap T6 closes.

---

How to Use This Document

T5 is relocated verbatim from the parent playbook’s PR-5 (T5) — no content changed, only moved here since it’s now owned by this session rather than the original playbook’s task-order stack. T6 is the team velocity algorithm: discovering real Azure Boards data (sprints, work items, story points) and computing team velocity from it, replacing the commit-count proxy currently shipping as “Team Velocity.” T3 is relocated verbatim from the parent playbook’s PR-3 — the Copilot/Copilot Studio/Claude Code usage connectors.

Task order in this playbook is T3, T5, T6 — all three are independent of each other (each needs only the already-merged Phase 1 metrics store; T6 additionally needs live Azure Boards API access, T3 needs the AI-tool provider secret keys below) and can be worked in either order or in parallel once unblocked. Branch-from targets below reflect that independence.

Current blockers

Task	Blocked on	Detail
T3	IT — secret key provisioning	GitHub Copilot Metrics API token, Copilot Studio Dataverse/Entra ID OAuth credential, and Claude Code Admin API key (sk-ant-admin01-...) are not yet provisioned. See PR 3’s “Data Source” subsection for exactly which credential each connector needs.
T6	Management — team velocity calculation sign-off	Current interim implementation (already built per §5.1 below) computes velocity as the total story points of work items in {"resolved", "done", "closed"} states — a flat sum, no sprint/iteration grouping, no rolling average. Management has this under review; do not change the computation until they confirm it’s the final formula.
T5	None	Active/unblocked on its own merits, but still gated behind v3 landing first (see Status note above) before its chat-widget work begins.

Reference inside Claude Code:

@docs/playbooks/v2-team-velocity-playbook.md read section "PR 5" and execute the prompt verbatim

---

1. Executive Summary

#	Item	Status / Gap	Depends on
T3	No AI-tool usage data	Copilot Metrics API, Copilot Studio, and Claude Code Admin API are not integrated; tool-adoption sparkline has no real backing data	On hold — blocked on IT secret-key provisioning (see blockers table); Phase 1 (dev), merged
T5	No natural-language query layer	No AI Assistant/chatbot exists over the metrics store; design doc’s optional Claude Code Skill (Appendix A) is superseded by an assistant integration per the parent playbook’s latest revision	Active; Phase 1 (dev), merged; gated behind v3 landing
T6	No real velocity algorithm	weekly_velocity is populated from commit_count/pr_count only (store/db.py lines 177–202), and the “Team Velocity” KPI (lines 391–428) sums a single ISO week’s commit_count, ignoring pr_count entirely — no Azure Boards data (sprints, work items, story points) is pulled or used at all. The schema’s own comment calls this a temporary proxy.	On hold — blocked on Management sign-off on the velocity formula (see blockers table); Phase 1 (dev), merged

PR Strategy — 3 PRs, independent of each other

• PR-3 carries T3, relocated verbatim from the parent playbook: Copilot Metrics API + Copilot Studio + Claude Code Admin API connectors, plus the tool-adoption sparkline wired to real data. Branches from dev. On hold pending IT secret-key provisioning.
• PR-5 carries T5, relocated verbatim from the parent playbook: an optional AI Assistant/chatbot — a small backend endpoint that gives an LLM (Sonnet 5 for MVP) tool-call access to the metrics store and answers natural-language questions. Branches from dev.
• PR-6 carries T6: the Azure Boards connector and the velocity computation built on top of it, as one combined task — discover each project’s Boards configuration (board columns, done-state mappings, story points), normalize it, and compute team velocity as the total story points closed (Resolved/Done/Closed states) for a project, falling back to the existing commit-count proxy when a project has no Boards data at all. Simplified scope (July 2026): no sprint/iteration grouping and no tiered rolling-average — see §5.1 below. Branches from dev. On hold pending Management sign-off on this formula.

Merge Sequencing

0. v3-platform-modernization-playbook.md must merge to dev first — see this file’s Status note above.
1. PR-3 (T3), PR-5 (T5), and PR-6 (T6) are independent of each other — any can branch from dev and merge first once unblocked; no stacking between them.

Model Guidance

PR	Task	Recommended Model	Effort	Reason
PR-3	T3 (Copilot + Copilot Studio + Claude Code connectors)	Sonnet 5	low-medium	Same connector shape as PR-1, three more read-only admin APIs, aggregate-only (no per-commit granularity)
PR-5	T5 (AI Assistant/chatbot)	Sonnet 5	low-medium	Tool-calling assistant against an already-built store; low complexity because MVP RAG strategy is schema-grounded function-calling, not a new vector/graph index
PR-6	T6 (Team velocity algorithm — Boards connector + total story points)	Sonnet 5	medium	API discovery (board columns → done-state mappings, story points — none of it standardized across the org’s projects, per live R&D below) plus a straightforward total-points computation with a labeled commit-count fallback

---

2. PR Plan

PR	Branch	Items	Branches from	Est.	Status
PR-3	feature/112272-ai-tool-usage-connectors-v1-pr3	T3	dev	1 week	On hold — IT secret keys
PR-5	feature/112274-ai-assistant-chatbot-v1-pr5	T5	dev	2–3 days	Active, gated behind v3
PR-6	feature/112335-team-velocity-algorithm-v1-pr6	T6	dev	3–5 days	On hold — Management sign-off

ADO task IDs: T3=112272 (unchanged from the parent playbook’s T3), T5=112274 (unchanged from the parent playbook’s T5), T6=112335 (see work-items/).

---

3. Files Touched

File / path	PR
connectors/copilot_connector.py, connectors/copilot_studio_connector.py, connectors/claude_code_connector.py (new)	PR-3 (T3)
store/schema.sql (additive — tool_adoption table)	PR-3 (T3)
store/db.py, api/server.py (extended to ingest/serve tool-adoption data)	PR-3 (T3)
public/dashboard.js (tool-adoption sparkline wired to real data)	PR-3 (T3)
assistant/server.py, assistant/tools.py (new — LLM tool-calling endpoint over the metrics store)	PR-5 (T5)
public/dashboard.js/public/index.html (chat widget, additive)	PR-5 (T5)
connectors/ado_boards_connector.py (new — Azure Boards discovery: boards, columns, done-states, work items, story points)	PR-6 (T6)
store/schema.sql (additive — boards_work_items table)	PR-6 (T6)
store/db.py (Boards ingest, total-story-points velocity query, replacing the commit-count-proxy KPI logic)	PR-6 (T6)
api/server.py (velocity route(s) pass through the new {value, unit} output as-is)	PR-6 (T6)
public/dashboard.js (Team Velocity KPI card + trend chart wired to real Boards data, proxy-labeled fallback)	PR-6 (T6)

---

4. Hard Rules

(Inherits all Hard Rules from docs/playbooks/v1-dev-productivity-dashboard-playbook.md §4, minus Hard Rule 4 there which is now moot — T4 was deleted, not built. The following are additive, specific to T6 — T3 and T5 have no additional rules beyond the parent playbook’s, since both are relocated unchanged.)

1. No hardcoded “done” state name. The Boards connector must discover each project’s board column → state mapping via the Boards API (GET .../_apis/work/boards/{boardId}) at ingest time, matching the recognized done-state vocabulary (Resolved, Done, Closed, case-insensitive) against each column’s stateMappings — never assume a single literal string like "Done" or "Closed" applies org-wide.
2. Multiple distinct done states must all count. Some projects treat Resolved and Closed as two separate, independently-reachable terminal states (e.g. dev-complete vs. verified/accepted). Done-state discovery returns a set per project, never a single string, and work items in any discovered done state count toward the total.
3. Only leaf-level work item types count toward velocity — User Story, Product Backlog Item, Bug. Never Epic, Feature, or Task — these either roll up into or sit underneath the leaf items and would double-count effort.
4. Story points are nullable, never coerced to 0. SUM() must ignore NULL story-point values rather than treating unset points as zero-effort-but-still-counted.
5. Projects with zero Boards activity are a valid, non-error state — the connector must emit an empty/null result for them, exactly like ado_connector.py already tolerates repos with zero commits/PRs in a window. Never crash or skip the project from the output entirely. The velocity query falls back to the existing commit-count proxy for these projects, explicitly labeled “(proxy)” wherever rendered.
6. weekly_velocity’s existing commit_count/pr_count columns are not deleted — PR-6 adds boards_work_items alongside it. Commit-based data stays available as a supporting/fallback signal for projects with no Boards data whatsoever, but it is no longer what the “Team Velocity” KPI card presents once real Boards data exists for a project.
7. No sprint/iteration grouping, no tiered rolling average (current scope). Team Velocity is a flat total of story points across all closed leaf work items for a project — not an average over N sprints, and not a fallback chain between story points / item counts / commits. Only two outcomes exist: a real point total, or the labeled commit-count proxy. Sprint-based rolling averages are explicitly out of scope for this pass (see §5.1).

5. R&D Findings Behind T6’s Design

Before writing T6’s design, the ADO Boards REST API was probed live against the org’s actual 16 projects (PAT scopes: Analytics (Read), Code (Read), Work Items (Read)). Findings that directly shape the connector and algorithm design:

1. Work item volume varies wildly across projects — from 0 work items (Sales Leads Analyzer, BOLDHackathon, AI-Builder-Receipt-Processor, BOLDInsight, ACS BOLDInsight, DCNE-AI-PoC) to 2780 (Buildfitters). A connector that assumes every project has Boards data will crash or silently misreport on more than a third of the org’s projects.
2. “Done” state names are not standardized across projects’ boards. Board column → state mappings observed:
   • Buildfitters Stories board: Done column → Closed state (User Story)
   • SOBRSure App Development 2024 Stories board: Closed column → Closed state
   • SOBRSafe Stories board: columns are To Do / In Progress / Code Review / QA / UAT / Production — no column literally named “Done” or “Closed” Hardcoding WHERE [System.State] = 'Done' in a WIQL query would silently miss or misclassify completed work on any project that doesn’t use that exact word.
3. Sprint/iteration dates are frequently unset. Only Buildfitters (23 sprints, actively used) has populated startDate/finishDate on its iterations. Every other sampled project’s iterations (Iteration 1/2/3, ADO’s auto-created defaults) have blank dates. A velocity algorithm that requires dated sprints to bucket work would produce no output for most of the org.
4. Story points are inconsistently populated even where Boards is used. Sampled items showed 0/10 with story points set on Buildfitters, 2/10 on SOBRSure. Story points cannot be the only signal — a fallback is required.
5. Work item type mix varies — Epic/Feature/User Story/Task/Bug/Product Backlog Item appear across projects with different subsets in play. Velocity must count only leaf-level deliverables (User Story / Product Backlog Item / Bug), never roll-up containers (Epic, Feature) or sub-tasks (Task), to avoid double-counting.

Conclusion: nothing about “which state means done” can be a hardcoded constant — it must be discovered per project, at ingest time, exactly the way ado_connector.py already discovers repos per project. (The R&D findings above around sprint dates and story-point sparsity originally motivated a tiered, sprint-aware algorithm; per §5.1, that algorithm has since been simplified — done-state discovery itself is unaffected.)

5.1 Implementation Update (July 2026) — Simplified Scope

The original design below (and in work-items/112335-team-velocity-algorithm.md’s first draft) called for a three-tier adaptive algorithm keyed off Azure Boards’ sprint/iteration data — rolling averages of story points or item counts over min(DEFAULT_ROLLING_SPRINTS, sprints_available) sprints, selected per project based on whether dated sprints and story points were both present.

That sprint-aware, tiered design was replaced with a simpler flat total, per team decision. Team Velocity is now: the sum of story_points across all closed leaf work items (User Story / Product Backlog Item / Bug in a Resolved/Done/Closed state) for a project — no sprint/iteration grouping, no rolling average, no tiered fallback between story points and item counts. Only two outcomes exist:

• Real data — {value: <total story points>, unit: "SP"} when the project has any closed leaf work items in Boards
• Proxy fallback — {value: <commit count>, unit: "commits"} when the project has zero Boards data at all (unchanged pre-existing weekly_velocity logic, explicitly labeled)

Everything below in “PR 6” — the connector design, discovery sequence, done-state-set handling (§4 Hard Rule 2), leaf-type scoping, and hard rules around nullable story points — is otherwise unchanged and still accurate. Only the “Required change — Part B” adaptive-algorithm specifics (tier selection, sprint_velocity table, rolling averages) are superseded by this section; wherever you see sprint_velocity, select_velocity_tier(), DEFAULT_ROLLING_SPRINTS, or “Tier A/B/C” below, read it as historical context for why the algorithm discovers done states the way it does, not as the current implementation. The current implementation lives in store/schema.sql’s boards_work_items table and store/db.py’s query_team_velocity().

Sprint-based rolling averages may be revisited as a future iteration if there’s a real need for trend-smoothing, but are explicitly out of scope for the current pass.

---

PR 3 — Copilot, Copilot Studio, and Claude Code usage connectors (T3)

Branch: feature/112272-ai-tool-usage-connectors-v1-pr3 Branches from: dev Model: Sonnet 5 Effort: low-medium Status: ON HOLD (2026-07-23) — blocked on IT provisioning the GitHub Copilot Metrics API token, Copilot Studio Dataverse/Entra ID OAuth credential, and Claude Code Admin API key (sk-ant-admin01-...). Do not start implementation until all three secrets exist and are readable from the env vars named below.

Relocated verbatim from docs/playbooks/v1-dev-productivity-dashboard-playbook.md’s PR-3 (T3) — content unchanged, only the owner (Fatima) and status (on hold) are new.

Problem

Section 8 Phase 2: “Copilot Metrics API + Claude Code Admin API integration; tool-adoption chart.” Initial AI-tools integration scope (Executive Summary) adds Copilot Studio as a third connector alongside Copilot and Claude Code — Cursor is explicitly not part of this PR or V1 at all. These are aggregate, org/workspace-level stats (Section 4) — not per-commit attribution.

Required change

The three tools do not report comparable data — do not force a shared metric shape across them. Common denominator across all three is active_users; everything else is tool-specific and stays nullable per tool (never fabricate — ship nulls, not zeros or averages, where a tool doesn’t report a metric). Full endpoint/field detail and doc links are in work-items/112272-ai-tool-usage-connectors.md.

1. connectors/copilot_connector.py: GitHub’s original inline-JSON Metrics API (/orgs/{org}/copilot/metrics) was retired 2026-04-02. Current API (REST endpoints for Copilot usage metrics) is report-based and async — GET /orgs/{org}/copilot/metrics/reports/organization-28-day/latest and .../reports/users-28-day/latest return download_links, not an inline body; the connector fetches the report link, then downloads/parses it. Active users and acceptance rate (accepted/shown) come from the report. Token counts exist only for Copilot CLI activity — IDE completions and chat carry no token count; report NULL tokens otherwise, don’t estimate. Auth token from an env var (e.g. GITHUB_COPILOT_TOKEN) — blocked on IT provisioning this token.
2. connectors/copilot_studio_connector.py: no comparable REST metrics endpoint — Copilot Studio analytics is Dataverse-table-backed (Copilot/ConversationTranscript tables via the Dataverse Web API), auth’d with a Power Platform/Entra ID OAuth token (env var COPILOT_STUDIO_DATAVERSE_TOKEN), not a GH-style PAT — blocked on IT provisioning this credential. Reports DAU/MAU and session/conversation volume only — no token concept at all. See Analytics overview - Copilot Studio.
3. connectors/claude_code_connector.py: Anthropic’s Claude Code Analytics API (GET /v1/organizations/usage_report/claude_code, Admin API key auth via env var CLAUDE_CODE_ADMIN_KEY) returns genuinely per-user, per-day records: model_breakdown[].tokens.{input,output,cache_read,cache_creation} for full token detail, tool_actions.*.{accepted,rejected} for tool-call acceptance rates, plus num_sessions, lines_of_code, commits_by_claude_code. Aggregate daily records into ISO weeks client-side — blocked on IT provisioning the Admin API key.
4. Extend store/schema.sql (additive migration, don’t rewrite PR-2’s tables) with a tool_adoption table: tool, team, week, active_users as the columns every connector populates, plus nullable per-tool columns (tokens_total, acceptance_rate, session_count) that stay NULL for tools that don’t report that metric.
5. Wire the tool-adoption sparkline (Section 7.1: “weekly active users per AI tool”) in public/dashboard.js/api/server.py to active_users; any tokens/acceptance-rate display must label which tool it came from — never imply a tool reported a metric it doesn’t have.
6. All three connectors stay isolated per file — a Copilot API change must not risk copilot_studio_connector.py or claude_code_connector.py, and vice versa.

Prompt — paste verbatim into Claude Code (only once IT has provisioned all three secrets)

You are implementing PR-3 of the Team Velocity playbook
(docs/playbooks/v2-team-velocity-playbook.md).

Read the full PR-3 section, and Section 4 and Section 8 Phase 2 of
scratch/extracted_text.txt, before writing any code. Phase 1 (PR-0 direct
commit + PR-1/PR-2) is already merged to dev — read store/schema.sql,
store/db.py, api/server.py, and connectors/ado_connector.py in full first,
to match existing conventions.

Before writing any code, confirm GITHUB_COPILOT_TOKEN,
COPILOT_STUDIO_DATAVERSE_TOKEN, and CLAUDE_CODE_ADMIN_KEY are all set and
readable — this task is on hold until IT provisions them. If any is
missing, stop and report which one rather than stubbing a fake value.

Branch: feature/112272-ai-tool-usage-connectors-v1-pr3 from dev.

Scope — T3 ONLY, three tools: GitHub Copilot, Copilot Studio, Claude Code.
Do NOT build a Cursor connector — Cursor is out of scope for all of V1.
Create connectors/copilot_connector.py,
connectors/copilot_studio_connector.py, connectors/claude_code_connector.py,
an additive migration to store/schema.sql, and their tests. Extend (don't
rewrite) store/db.py and api/server.py to ingest/serve the new
tool_adoption data.

═══════════════════════════════════════
STEP 1 — Copilot connector
═══════════════════════════════════════

1. WRITE connectors/copilot_connector.py, matching ado_connector.py's
   shape (script entrypoint, --since/--until, JSON output to
   connectors/output/, no LLM calls, token from env var): calls GitHub's
   report-based Copilot usage metrics API (the original inline-JSON
   /orgs/{org}/copilot/metrics endpoint was retired 2026-04-02) —
   GET /orgs/{org}/copilot/metrics/reports/organization-28-day/latest and
   .../reports/users-28-day/latest, each returning a download_links array
   the connector must fetch and parse (not an inline body). Extract active
   users and acceptance rate (accepted/shown). Token counts are CLI-only
   (prompt tokens sent / output tokens generated) — leave tokens NULL for
   non-CLI activity, do not estimate. Docs:
   https://docs.github.com/en/rest/copilot/copilot-usage-metrics and
   https://docs.github.com/en/copilot/reference/metrics-data
   Aggregated (not per-commit — this API does not map 1:1 to individual
   commits).

═══════════════════════════════════════
STEP 2 — Copilot Studio connector
═══════════════════════════════════════

2. WRITE connectors/copilot_studio_connector.py. No comparable REST metrics
   endpoint exists — Copilot Studio analytics is Dataverse-table-backed
   (Copilot/ConversationTranscript tables via the Dataverse Web API),
   authenticated with a Power Platform/Entra ID OAuth token (use a
   distinct env var, COPILOT_STUDIO_DATAVERSE_TOKEN, not a shared token var
   with the other connectors). Extract DAU/MAU and session/conversation
   volume only — there is no token metric for this tool; leave
   tokens_total NULL always, not zero. Docs:
   https://learn.microsoft.com/en-us/microsoft-copilot-studio/analytics-overview
   Aggregate only, same caveat as Copilot — not per-commit granularity.

═══════════════════════════════════════
STEP 3 — Claude Code connector
═══════════════════════════════════════

3. WRITE connectors/claude_code_connector.py, same shape: calls Anthropic's
   Claude Code Analytics API — GET
   https://api.anthropic.com/v1/organizations/usage_report/claude_code
   with starting_at=YYYY-MM-DD, headers anthropic-version: 2023-06-01 and
   x-api-key: $CLAUDE_CODE_ADMIN_KEY (an Admin API key, sk-ant-admin01-...,
   distinct from a normal API key). Cursor-paginated (has_more/next_page);
   one record per actor per day — aggregate into ISO weeks client-side.
   Extract active users (distinct actor.email_address with sessions that
   week), full token breakdown from model_breakdown[].tokens.{input,
   output,cache_read,cache_creation}, and tool_actions.*.{accepted,
   rejected} for acceptance rates. Docs:
   https://platform.claude.com/docs/en/manage-claude/claude-code-analytics-api
   Same caveat — usage volume, not line-level attribution.

═══════════════════════════════════════
STEP 4 — Store + API
═══════════════════════════════════════

4. Add a tool_adoption table to store/schema.sql: tool, team, week,
   active_users as the columns every connector populates, plus nullable
   per-tool columns (tokens_total, acceptance_rate, session_count) that
   stay NULL for tools that don't report that metric — as an additive
   change, do not modify or drop PR-2's existing tables.
5. Extend store/db.py's ingest function to write all three connectors'
   output into tool_adoption, leaving tool-specific columns NULL rather
   than 0 when a tool doesn't report that metric.
6. Extend api/server.py with a route (or extend an existing route) that
   returns tool-adoption data shaped for the dashboard's sparkline.

═══════════════════════════════════════
STEP 5 — Dashboard wiring
═══════════════════════════════════════

7. In public/dashboard.js, wire the tool-adoption sparkline (Section 7.1)
   to the new API data instead of whatever placeholder/mock value it
   currently shows.

═══════════════════════════════════════
STEP 6 — Tests
═══════════════════════════════════════

8. Unit tests for all three connectors' normalization (mocked API
   responses, no live calls) and for the tool_adoption ingest/read path.

═══════════════════════════════════════
Verification
═══════════════════════════════════════

9. RUN pytest tests/ -v --tb=short. No new failures, including existing
   Phase 1 tests.

Once implementation is complete and all tests pass, STOP. Do not stage,
commit, or push anything — confirm with the user first.

On user approval only:
  1. git add -A (stage all files).
  2. Commit via the commit-msg skill.
     Suggested title: "feat: dpd PR-3 — Copilot, Copilot Studio, and Claude Code usage connectors, tool-adoption chart (T3)"
     Resolves: #112272
  3. git push -u origin feature/112272-ai-tool-usage-connectors-v1-pr3. No PR to open.

No Co-authored-by: Claude trailer.

---

PR 5 — AI Assistant/chatbot for ad-hoc querying (T5)

Branch: feature/112274-ai-assistant-chatbot-v1-pr5 Branches from: dev Model: Sonnet 5 Effort: low-medium

Relocated verbatim from docs/playbooks/v1-dev-productivity-dashboard-playbook.md’s PR-5 (T5) — content unchanged, only the branch-from target is dev instead of the parent playbook’s (now-deleted) PR-4 branch.

Problem

Section 5.4 and Appendix A: an optional conversational layer on top of the (now real) dashboard — natural-language questions answered by querying the metrics store, not by an LLM reading raw data in-context (Section 6.1: “one query call … vs. thousands of tokens of re-derivation per question”). This revision supersedes the design doc’s Claude Code Skill (Appendix A) with a standalone AI Assistant/chatbot: an LLM brain (backend endpoint, not a Claude Code-only Skill) that the dashboard UI can talk to via a chat widget, with the SQLite metrics store (store/schema.sql) as its knowledge source.

RAG Strategy

The knowledge source is a small, structured SQLite store with a handful of pre-aggregated tables (Section 5.2) — not a corpus of unstructured documents. That shapes which RAG pattern fits:

Strategy	Fit for this knowledge source	Verdict
Vector embeddings	Built for semantic search over free text (docs, commit messages, PR descriptions). The store’s tables are numeric/categorical aggregates with no free-text field to embed yet.	Not needed for MVP
GraphRAG	Built for multi-hop relationship reasoning over a knowledge graph (e.g. “who reviews whose PRs most often” across teams/repos). Current schema is flat aggregate tables, not a modeled entity graph.	Not needed for MVP; premature complexity
Schema-grounded tool-calling (“prompt injection”)	The LLM is given the store’s schema + a small set of parameterized query functions (tool definitions) in its system prompt; it converts the NL question into one or more tool calls, reads back the returned aggregate JSON, and summarizes. No new index to build or keep in sync — reuses api/server.py’s/store/db.py’s existing query logic verbatim.	MVP choice

MVP recommendation: schema-grounded tool-calling, no vector DB, no graph DB. The store is small and already has a thin read API (PR-2) that returns exactly the aggregate shapes a question needs — building a retrieval index on top of data that’s already directly queryable is solving a problem the architecture doesn’t have yet (Ponytail rung 1: does this need to exist at all).

LLM choice for MVP: Sonnet 5 — every other PR in this playbook is built and reviewed with Sonnet 5 (Model Guidance, §1), so using it here keeps tool-call/JSON-schema behavior consistent with what’s already validated elsewhere in the repo, and its tool-calling reliability on structured schemas is a known quantity. GLM 5.2 is a viable lower-cost alternative if query volume grows large enough that inference cost matters — swap-in candidate, not a Phase-4 blocker; revisit at P2 if usage data shows it’s warranted.

P2 (future, only if a real need shows up):

• Vector embeddings over free-text fields once they exist in the store or connectors — commit messages, PR titles/descriptions, README/doc text — to answer semantic questions the current schema can’t (“find PRs about the auth refactor”). Add a lightweight embedding store (e.g. sqlite-vec alongside the existing SQLite file) rather than standing up a separate vector DB, to keep the single-file-store simplicity from Section 5.2.
• GraphRAG only if cross-entity relationship questions become a recurring, real ask (review graphs, team/dependency structure across repos) — not before the schema itself models those relationships. Treat as a P3/optional escalation from the P2 vector layer, not a parallel track.

Required change

1. assistant/tools.py: tool/function definitions wrapping store/db.py’s existing query functions (or api/server.py’s routes called in-process) — one tool per filter shape already supported (project, tool, date range, contributor), returning the same aggregate JSON shape mockData.js’s aggregate() produces. No new query logic duplicated a third time.
2. assistant/server.py: a small endpoint that accepts a natural-language question, sends it to Sonnet 5 with the tool definitions and a schema-grounded system prompt (store’s table/column names, valid filter values), executes whatever tool call(s) the model returns, feeds the result back, and returns the model’s final natural-language answer (2–3 sentences plus a number, chart only if asked — same summarization contract as the original Appendix A skeleton).
3. A minimal chat widget wired into public/dashboard.js/public/index.html (additive — does not touch existing KPI/chart/table rendering) that posts questions to assistant/server.py and renders the answer.
4. No LLM calls anywhere outside assistant/ — Hard Rule 2 is unchanged: connectors/store/API stay pure ETL.

Prompt — paste verbatim into Claude Code

You are implementing PR-5 of the Team Velocity playbook
(docs/playbooks/v2-team-velocity-playbook.md).

Read the full PR-5 section (including its "RAG Strategy" subsection), and
Section 5.4 and Section 6.2 of scratch/extracted_text.txt, before writing
any code. Read store/schema.sql and api/server.py in full first —
assistant/tools.py must reuse the same query logic as the API, not
duplicate it ad hoc.

Branch: feature/112274-ai-assistant-chatbot-v1-pr5 from dev.

Scope — T5 ONLY. Create assistant/tools.py, assistant/server.py, and their
tests. Add a minimal chat widget to public/dashboard.js/public/index.html
(additive only — do not touch existing KPI/chart/table rendering). Do not
modify store/, api/, or connectors/.

MVP RAG strategy is schema-grounded tool-calling — NOT vector embeddings,
NOT GraphRAG. Do not build a vector index or graph store; the metrics
store is small, structured, and already queryable through api/server.py.

═══════════════════════════════════════
STEP 1 — Tool definitions
═══════════════════════════════════════

1. WRITE assistant/tools.py: one tool/function definition per filter
   shape already supported by api/server.py (project, tool, date range,
   contributor), each wrapping the existing store/db.py query function or
   calling api/server.py's route handler in-process. Each tool returns
   the same aggregate JSON shape mockData.js's aggregate() produces — do
   not reimplement the aggregation logic a third time.

═══════════════════════════════════════
STEP 2 — Assistant endpoint
═══════════════════════════════════════

2. WRITE assistant/server.py: a small HTTP endpoint that:
   - accepts a natural-language question (POST body)
   - calls Sonnet 5 (the model used throughout this playbook — see PR-5's
     "RAG Strategy" subsection for why) with assistant/tools.py's tool
     definitions and a schema-grounded system prompt (table/column names,
     valid filter values, the store's grain — day/week/sprint)
   - executes whatever tool call(s) the model returns, feeds the tool
     result back to the model
   - returns the model's final answer: 2-3 sentences plus a number, chart
     only if the question asks for one
   - No LLM calls anywhere else in the repo (Hard Rule 2 unchanged);
     credentials for the LLM API read from an environment variable, never
     hardcoded.

═══════════════════════════════════════
STEP 3 — Chat widget
═══════════════════════════════════════

3. Add a minimal chat widget to public/index.html/public/dashboard.js: a
   text input + response area that POSTs to assistant/server.py and
   renders the answer. Additive only — existing dashboard rendering paths
   must be untouched.

═══════════════════════════════════════
STEP 4 — Tests
═══════════════════════════════════════

4. WRITE tests for assistant/tools.py (each tool's output shape against a
   fixture-seeded store) and assistant/server.py (mock the LLM call —
   never hit the real Sonnet 5 API in tests; assert the endpoint executes
   the tool call the mocked model response requests and returns its
   result).

═══════════════════════════════════════
Verification
═══════════════════════════════════════

5. RUN pytest tests/ -v --tb=short. No new failures.
6. Manually: run assistant/server.py against a fixture-seeded store, ask
   it a sample question ("who had the highest velocity last sprint?"),
   confirm it calls a tool (not raw-data reading) and returns a
   2-3-sentence answer with a number.

Once implementation is complete and all tests pass, STOP. Do not stage,
commit, or push anything — confirm with the user first.

On user approval only:
  1. git add -A (stage all files).
  2. Commit via the commit-msg skill.
     Suggested title: "feat: dpd PR-5 — AI Assistant for natural-language metrics queries (T5)"
     Resolves: #112274
  3. git push -u origin feature/112274-ai-assistant-chatbot-v1-pr5. No PR to open.

No Co-authored-by: Claude trailer.

---

PR 6 — Team velocity algorithm: Azure Boards connector + total story points (T6)

Branch: feature/112335-team-velocity-algorithm-v1-pr6 Branches from: dev Model: Sonnet 5 Effort: medium

Implementation note (July 2026): this section reflects the simplified, current scope — see §5.1 above. The original tiered/sprint-aware design has been superseded; the connector’s discovery logic (done-state sets, leaf-type filtering) is unchanged from that original design.

Problem

store/schema.sql’s own comment on weekly_velocity (lines 73–74) already documents this gap: “Real story-point velocity via Azure Boards is a future connector; this PR uses commit count as the velocity proxy until Boards data is wired in.” The current “Team Velocity” KPI (store/db.py lines 391–428) sums a single ISO week’s commit_count and labels it "{n} commits" — it never reads pr_count and has no relationship to actual work delivered. No code today calls any Boards API endpoint at all.

Live R&D against the org (§5 above) confirms a naive fix would break silently on most of the org’s projects: done-state names are inconsistent, and some projects treat Resolved and Closed as two distinct states rather than one. This task is one combined PR because the connector and the velocity query are inseparable in practice — the query’s total is a direct function of what the connector discovers per project; building them as separate PRs would mean PR-6a ships data nothing consumes, or PR-6b’s query has nothing real to compute over.

Data Source — Azure DevOps Boards (Work Item Tracking + Work APIs)

Purpose	Endpoint
List boards	GET https://dev.azure.com/{org}/{project}/_apis/work/boards?api-version=7.1
Get board (columns + stateMappings)	GET https://dev.azure.com/{org}/{project}/_apis/work/boards/{boardId}?api-version=7.1
WIQL query	POST https://dev.azure.com/{org}/{project}/_apis/wit/wiql?api-version=7.1
Batch-fetch work items	GET https://dev.azure.com/{org}/{project}/_apis/wit/workitems?ids=...&fields=...&api-version=7.1

Auth: same PAT as ado_connector.py (ADO_PAT env var), Basic auth (empty username + PAT). Confirmed during R&D that the current PAT’s scopes (Analytics (Read), Code (Read), Work Items (Read)) are sufficient for every endpoint above.

Required change — Part A: Boards discovery/connector

1. connectors/ado_boards_connector.py: for each accessible project (reusing ado_connector.py’s list_projects() — import it, don’t duplicate it):
   • List boards → find the board backing User Story or Product Backlog Item
   • Get that board’s columns → match stateMappings against the recognized done-state vocabulary (Resolved, Done, Closed, case-insensitive) → collect as a set of done states per work item type, per project (never hardcoded, and never assumed to be a single string — some projects have both Resolved and Closed as distinct states)
   • WIQL-query closed leaf work items (User Story, Product Backlog Item, Bug only, never Epic/Feature/Task) in the discovered done state(s)
   • Batch-fetch System.IterationPath, Microsoft.VSTS.Scheduling.StoryPoints, System.State, System.ChangedDate for the matched IDs
2. Normalize to one row per closed work item (project_id, project_name, work_item_id, work_item_type, iteration_path, story_points — nullable, never coerced to 0 — state, changed_date). (iterations may still be captured in connector output for potential future use, but are not consumed by the current velocity query.)
3. Projects with zero matching work items still appear in the output with an empty work_items list — never omitted.
4. Output to connectors/output/ado_boards_<date>.json, same idempotent/re-runnable contract as ado_connector.py. No LLM calls anywhere in this file.
5. Script entrypoint with --org, --since/--until (filtering on changed_date), --projects, --output-dir, --log-level — matching ado_connector.py’s CLI shape.

Required change — Part B: Total story points velocity

6. store/schema.sql (additive migration): a boards_work_items table — project_id, work_item_id, work_item_type, state, story_points (nullable, never coerced to 0), changed_date (nullable), one row per closed leaf work item. weekly_velocity is untouched (Hard Rule 6).
7. store/db.py:
   • ingest_boards() — ingest function for connectors/output/ado_boards_*.json → upserts one row per closed leaf work item into boards_work_items (no sprint/iteration grouping).
   • query_team_velocity(conn, project_id, fallback_value, since=None, until=None) → returns {value, unit}:
     • If the project has any boards_work_items rows: value = SUM(story_points) (NULLs ignored automatically by SUM(), optionally scoped to a changed_date window via since/until), unit = "SP"
     • If the project has zero boards_work_items rows: value = fallback_value (the caller-supplied, pre-existing weekly_velocity commit-count), unit = "commits"
   • Replaces the current KPI’s SQL, reusing the existing with_delta()/pct_change() helpers rather than duplicating delta-formatting logic.
   • Velocity trend chart data source switches from weekly_velocity.commit_count to boards_work_items (grouped by ISO week via changed_date) for projects with real Boards data, falling back to the existing weekly_velocity logic unchanged otherwise — still producing the same velocityPoints/velocityPath/velocityAreaPath shape the dashboard already renders — no downstream rendering-code rewrite needed.
8. api/server.py: the route(s) serving KPI/velocity-trend data pass through the new {value, unit} shape as-is (no reformatting into a bare string server-side).
9. public/dashboard.js: Team Velocity KPI card renders unit-aware ("X SP" vs. "X commits"), and shows a small basis badge/tooltip only when the value is the commit-count proxy — mirrors how AI-assisted % must show its basis in the parent playbook’s Hard Rule 3, applied here to velocity’s basis instead.

Prompt — paste verbatim into Claude Code

You are implementing PR-6 of the Team Velocity playbook
(docs/playbooks/v2-team-velocity-playbook.md).

Read the full PR-6 section, Section 5 (R&D Findings), Section 5.1
(Implementation Update — Simplified Scope), and Section 4 (Hard Rules)
before writing any code. Read connectors/ado_connector.py,
store/schema.sql, store/db.py (especially the "Team Velocity" KPI block
around lines 391-428), and api/server.py in full first — this PR reuses
ado_connector.py's list_projects() and extends (never rewrites)
store/schema.sql, store/db.py, api/server.py.

Branch: feature/112335-team-velocity-algorithm-v1-pr6 from dev.

Scope — T6 ONLY: the Azure Boards connector AND the flat total-story-
points velocity query built on it, as one combined task. Create
connectors/ado_boards_connector.py and its test file
(tests/test_ado_boards_connector.py). Add an additive boards_work_items
table to store/schema.sql (do not modify or drop weekly_velocity or any
other existing table). Extend store/db.py and api/server.py. Modify ONLY
the Team Velocity KPI card and velocity trend chart rendering in
public/dashboard.js — leave every other KPI/chart/table untouched.

Hard Rules for this PR:
- No hardcoded "done" state string — discover the set of done states per
  project/work-item-type from the board's columnType=="done" column's
  stateMappings. Some projects use Resolved and Closed as two distinct
  done states — both must be discovered and both must count.
- Only User Story / Product Backlog Item / Bug count as leaf work items —
  never Epic, Feature, or Task.
- Projects with zero matching work items still appear in connector output
  with an empty work_items list — never omitted or skipped.
- story_points stays nullable end-to-end (connector output, schema
  column, and SQL) — never coerced to 0. SUM() must ignore NULLs
  naturally; do not COALESCE individual rows to 0 before summing.
- No sprint/iteration grouping and no tiered/rolling-average logic
  (out of scope for this pass, per §5.1) — velocity is a flat SUM of
  story_points across all closed leaf work items per project.
- Zero-Boards-data projects must still render something (the existing
  commit-count proxy, explicitly labeled "commits") — never a
  blank chart or an unlabeled number.

═══════════════════════════════════════
PART A — Boards connector
═══════════════════════════════════════

STEP 1 — Discovery sequence

1. WRITE connectors/ado_boards_connector.py with these functions, each
   with its own docstring documenting the exact endpoint + fields used
   (mirror ado_connector.py's docstring style):
   - list_boards(org, project, headers) -> list[dict]
   - get_board_done_states(org, project, board_id, headers) -> dict
     mapping work_item_type -> set[str] of done state names, derived from
     column(s) matching the recognized done-state vocabulary (Resolved,
     Done, Closed — case-insensitive) in stateMappings
   - list_iterations(org, project, headers) -> list[dict]  (id, name,
     startDate, finishDate — nullable dates; captured for potential
     future use, not consumed by velocity in this pass)
   - query_closed_work_items(org, project, done_states, since, until,
     headers) -> list[dict]  (WIQL query scoped to WorkItemType IN
     ('User Story','Product Backlog Item','Bug') AND State IN the
     discovered done states, changed within [since, until])
   - fetch_work_item_fields(org, project, ids, headers) -> list[dict]
     (batch GET with fields=System.Id,System.WorkItemType,System.State,
     System.IterationPath,Microsoft.VSTS.Scheduling.StoryPoints,
     System.ChangedDate)

STEP 2 — Normalization

2. WRITE normalise_work_items() and normalise_iterations(), producing the
   row shapes in PR-6's "Required change — Part A" step 2.
3. WRITE the per-project orchestration (find the board whose backing
   types include User Story or Product Backlog Item — match by type
   membership from the board's own config, not by board name) that calls
   the above in sequence per project, tolerating any endpoint returning
   zero results without raising, and unions per-type done-state sets into
   one done_states set per project.

STEP 3 — CLI + output

4. Add a run() orchestration function and a script entrypoint matching
   ado_connector.py's argparse shape (--org, --since, --until, --projects,
   --output-dir, --log-level). Write output to
   connectors/output/ado_boards_<date>.json.

STEP 4 — Connector tests

5. WRITE tests/test_ado_boards_connector.py: mocked/fixture API responses
   (no live ADO calls). Cover: done-state discovery from a board with a
   column literally named something other than "Done"/"Closed" (prove the
   vocabulary-based discovery works, not exact-name-matching); a project
   with both Resolved and Closed as distinct done states, where work
   items in either state are both matched; a project with zero closed
   work items still produces an empty-but-present entry; only User
   Story/PBI/Bug are included even when Epic/Feature/Task items are
   present in the fixture; story_points is preserved as None (not 0) when
   the API returns it unset.

═══════════════════════════════════════
PART B — Total story points velocity
═══════════════════════════════════════

STEP 5 — Schema

6. ADD to store/schema.sql: a boards_work_items table (project_id,
   work_item_id, work_item_type, state, story_points NULLABLE,
   changed_date NULLABLE), PRIMARY KEY (project_id, work_item_id), with
   an index on project_id. Follow the file's existing comment-header
   convention (see the weekly_velocity/ai_assisted_pct sections for the
   style) — document that story_points is intentionally nullable and
   summed with NULLs ignored, referencing this playbook.

STEP 6 — Ingest

7. WRITE ingest_boards(conn, payload) in store/db.py for
   connectors/output/ado_boards_*.json's work-item rows, upserting one
   row per closed leaf work item directly into boards_work_items (no
   grouping by iteration/sprint). Must be idempotent (safe to re-run on
   the same file).

STEP 7 — Velocity query

8. WRITE query_team_velocity(conn, project_id, fallback_value,
   since=None, until=None) -> dict:
   - If the project has any boards_work_items rows: value =
     SUM(story_points) (NULLs ignored by SUM(), optionally scoped to a
     changed_date window via since/until), unit = "SP".
   - If the project has zero boards_work_items rows: value =
     fallback_value (the caller-supplied existing weekly_velocity
     commit-count), unit = "commits".
   Reuse the existing pct_change()/with_delta() helpers rather than
   duplicating delta-formatting logic.
9. Replace the current hardcoded "Team Velocity" KPI construction (the
   cur_velocity/prev_velocity block and its with_delta(...) call) with a
   call to query_team_velocity(), keeping with_delta()'s existing
   signature/shape so no other KPI card's code path changes.
10. Update the velocity trend chart's data source to read from
    boards_work_items (grouped by ISO week via changed_date) for projects
    with real Boards data, falling back to the existing
    weekly_velocity-based logic unchanged when a project has zero
    boards_work_items rows — keep producing the same
    velocityPoints/velocityPath/velocityAreaPath shape.

STEP 8 — API + dashboard

11. In api/server.py, ensure the route(s) returning this KPI/chart data
    pass through query_team_velocity()'s {value, unit} shape as-is.
12. In public/dashboard.js, update ONLY the Team Velocity KPI card
    rendering to show `${value} ${unit}` and, only when unit is
    "commits", a small label/tooltip explaining the basis.

STEP 9 — Store tests

13. Extend tests/test_store_db.py: fixture-seeded boards_work_items rows
    covering totals-with-points, NULL-story-points rows being ignored by
    the sum, a project with zero boards_work_items rows correctly falling
    back to the commit-count proxy, and since/until date-window scoping;
    assert query_team_velocity() returns the correct {value, unit} in
    each case and that the KPI/trend-chart integration in
    query_aggregate() reflects it.

═══════════════════════════════════════
Verification
═══════════════════════════════════════

14. RUN pytest tests/ -v --tb=short. No new failures, including existing
    Phase 1 tests.
15. Manually: run connectors/ado_boards_connector.py against a fixture or
    live org, ingest its output, run api/server.py, serve public/,
    confirm the Team Velocity KPI card shows the correct unit/label per
    project (SP vs. commits) and the trend chart renders
    without error.

Once implementation is complete and all tests pass, STOP. Do not stage,
commit, or push anything — confirm with the user first.

On user approval only:
  1. git add -A (stage all files).
  2. Commit via the commit-msg skill.
     Suggested title: "feat: velocity PR-6 — Azure Boards connector and total story points team velocity (T6)"
     Resolves: #112335
  3. git push -u origin feature/112335-team-velocity-algorithm-v1-pr6. No PR to open.

No Co-authored-by: Claude trailer.

---

6. Verification Samples

Checkpoint	Verify
PR-3 merged	Tool-adoption sparkline shows real Copilot/Copilot Studio/Claude Code active-user data; no per-commit attribution claims anywhere
PR-5 merged	Asking the AI Assistant a natural-language question (“who had the highest velocity last sprint?”) triggers a tool call against assistant/tools.py, not raw-data reading; no vector/graph index was introduced for MVP
PR-6 merged	connectors/ado_boards_connector.py run against the real org produces ado_boards_<date>.json with entries for all 16 projects, including the 8+ with zero work items (empty-but-present, not omitted); done-state discovery correctly resolves SOBRSafe’s non-standard column names without hardcoding, and correctly unions distinct Resolved/Closed states for projects that use both
PR-6 merged	Team Velocity KPI shows points (a flat sum of story_points across all closed leaf work items) for projects with real Boards data, and an explicitly-labeled commits value for projects with zero Boards data — never an unqualified number claiming to be one thing while showing another
PR-6 merged	SUM(story_points) correctly ignores NULL-valued rows rather than treating them as zero-value work
Any PR	Existing Phase 1 tests (tests/test_store_db.py, tests/test_api_server.py) still pass unmodified in their pre-existing cases

---

7. Rollback

• PR-3 (T3, Copilot/Copilot Studio/Claude Code connectors): Revert the three connector files and the additive tool_adoption migration; tool-adoption sparkline reverts to its pre-PR-3 placeholder state. No impact to Phase 1’s commit/velocity data.
• PR-5 (T5, AI Assistant): Remove assistant/ and the chat widget from public/; no impact to the dashboard’s core rendering, which never depended on the assistant.
• PR-6 (T6, Boards connector + team velocity): Revert connectors/ado_boards_connector.py, the boards_work_items migration, and the KPI/chart changes in store/db.py, api/server.py, and public/dashboard.js; Team Velocity reverts to the pre-existing commit-count-proxy behavior. No impact to any other KPI.