# AI-Assisted % KPI — V5 Build-Out Playbook (T11)
> **File:** `docs/playbooks/v5-ai-assisted-percentage-playbook.md`
> **Base branch:** `dev` (integration branch — `main` stays protected/production; PRs target `dev`)
> **Branch naming:** `feature/<ado-task-id>-<slug>-v5-pr<n>`
> **Plan date:** 2026-08-03
> **Priority:** Independent of other in-flight playbooks — reads only from tables already
> populated by existing, already-merged connectors (`daily_commits` from `ado_connector.py`,
> `copilot_user_usage` from `copilot_connector.py`). No connector changes required.
> **Source:** `work-items/112671-ai-assisted-percentage.md`; conversation 2026-08-03 (formula +
> per-project allocation approach agreed with lead).
## How to Use This Document
**T11** replaces the hardcoded `"AI-Assisted %": "—"` KPI placeholder in `store/db.py`'s
`query_aggregate()` with a real, computed value, at both the contributor and project level. It
requires no new connectors or API calls — both data sources (`copilot_user_usage`,
`daily_commits`) are already ingested. This is a pure store-layer computation + API/UI
### Formula choice — DECIDED (2026-08-03)
AI-Assisted % = copilot_user_usage.loc_added_sum ÷ SUM(daily_commits.churn_added, same
contributor, all projects, same period) × 100
**Per project (allocation):** Copilot doesn't report which project usage occurred in, so a
contributor's Copilot LOC is allocated across their projects proportionally to commit-churn
Allocated LOC (contributor, project) = contributor's loc_added_sum
× (contributor's churn_added in that project ÷ contributor's total churn_added)
Project AI-Assisted % = SUM(allocated LOC across contributors in that project)
÷ SUM(churn_added across contributors in that project) × 100
This was chosen over waiting for a commit-trailer attribution scheme (the original
`ai_assisted_pct.commit_attributed_pct` design) because that scheme was never adopted and both
inputs needed for this simpler formula already exist with zero additional ingestion work.
**Locked limitations (must ship as visible caveats, not silent assumptions):**
- Contributor↔Copilot identity match stays heuristic (existing local-part/prefix match) — a
non-match is "no data," never a fabricated `0%`.
- Per-project number is an allocation/estimate, not a direct measurement.
- Labeled as a "Copilot-based estimate" wherever surfaced — never presented as exact.
| # | Item | Gap | Depends on |
| T11 | "AI-Assisted %" KPI is a hardcoded `—` placeholder | `ai_assisted_pct` table was designed for a commit-trailer scheme that was never built; no computation exists over the data we already have | None (reads only from already-merged `daily_commits` + `copilot_user_usage`) |
- **PR-11** carries T11 end-to-end: store computation, API surfacing, UI label/tooltip. Branches
Single PR — merges directly to `dev` once approved.
| PR | Task | Recommended Model | Effort | Reason |
| PR-11 | T11 (AI-Assisted % KPI) | Claude Sonnet 4.5 | medium | New aggregation query across two existing tables + allocation math; moderate care needed for divide-by-zero/no-match edge cases, but no new connector/schema-heavy work |
| PR | Branch | Items | Branches from | Est. |
| PR-11 | `feature/112671-ai-assisted-percentage-v5-pr11` | T11 | `dev` | 1 session |
ADO task IDs: T11=112671 (see `work-items/112671-ai-assisted-percentage.md`).
| `store/db.py` (`query_aggregate()`'s AI-Assisted % KPI, new `query_ai_assisted_pct_by_project()`) | PR-11 |
| `api/server.py` (surface project-level breakdown if/where needed) | PR-11 |
| `public/src/**` (KPI card label/tooltip noting "Copilot-based estimate", per-project display if applicable) | PR-11 |
| `tests/test_store_db.py` | PR-11 |
(Inherits Hard Rules from `docs/playbooks/v1-dev-productivity-dashboard-playbook.md` §4 —
never fabricate data, aggregate/normalize at ingest not query time where applicable, zero-data
is a valid state not an error, and **Hard Rule 12: no automatic git actions** (stage/commit/push
only on explicit user approval, no PR opened). The following are additive, specific to this
1. Never fabricate a `0%` for a contributor/project with no matching Copilot data — absence of
data must render as "no data" (e.g. `—` or omitted), exactly like the existing KPI placeholder
2. The result must always be capped at 100% — a data anomaly (e.g. more Copilot LOC than total
churn in a period, due to overlapping/rolling-window mismatches between the two data sources)
must never render as e.g. 140%.
3. Any UI surfacing of this number must carry a visible "Copilot-based estimate" label/tooltip —
never presented as an exact, tool-agnostic figure.
4. Do not modify `copilot_connector.py`, `ado_connector.py`, or either source table's ingest
logic — this is a read-only computation over already-ingested data.
5. Divide-by-zero guards required: a contributor with zero `churn_added` in the period must not
crash the aggregate query; skip them (no data), don't produce `NaN`/`Infinity`.
- Claude Code / Cursor contribution to this metric — noted as future extension once/if those
connectors report comparable LOC-attribution data; this pass is Copilot-only.
- The original `ai_assisted_pct.commit_attributed_pct` commit-trailer (`Assisted-By: <tool>`)
attribution scheme — still not adopted; this playbook does not revisit that pilot.
- Repo-level granularity — the allocation formula operates at the project level (matching
`daily_commits`' `project_id` dimension); a repo-level breakdown would need repo-level Copilot
usage data, which does not exist.
## 6. Per-Session Contract
1. Branch from `dev` (first and only PR in this playbook).
2. Scoped changes only — no opportunistic cleanup outside this PR's stated files.
3. Add a runnable check for any new non-trivial logic — extend `tests/test_store_db.py`; no
test-framework scaffolding beyond what the PR's own code needs.
4. All checks green before any git action.
5. **Once all checks are green, stop and confirm with the user before staging, committing, or
pushing anything** — do not take any git action without explicit approval (Hard Rule 12).
6. On approval: `git add -A`, `commit-msg` skill for the message (no Claude co-author trailer),
`git push -u origin <branch>` (or `git push origin dev` if committing directly to `dev`).
7. No PR is opened as part of this workflow — push is the last step.
## PR 11 — AI-Assisted % KPI (T11)
**Branch:** `feature/112671-ai-assisted-percentage-v5-pr11`
**Model:** Claude Sonnet 4.5
`store/db.py`'s `query_aggregate()` hardcodes:
with_delta("AI-Assisted %", "—", 0, "pp"), # null until PR-4
`store/schema.sql`'s `ai_assisted_pct` table (`repo_id, project_id, tool, period,
tool_reported_pct, commit_attributed_pct`) exists but is never populated — it was designed for a
commit-trailer attribution scheme that was never adopted. Meanwhile, `copilot_user_usage`
(`loc_added_sum` per `user_login`, 28-day rolling) and `daily_commits` (`churn_added` per
contributor per project per day) are both already ingested and sufficient to compute a real
estimate without that scheme.
- Add a helper to match `copilot_user_usage.user_login` → `contributors.contributor_email`
(reuse the existing heuristic from `query_copilot_user_detail()` — local-part vs. login
prefix — factor it out into a shared function rather than duplicating the logic).
- Add `query_ai_assisted_pct(period_since, period_until, project_id="all")` — computes the
contributor-level and, when `project_id` is specific, the per-project allocation, per the
locked formula above. Guard divide-by-zero (contributor with zero `churn_added` in the
window is skipped, not a crash).
- Wire the result into `query_aggregate()`'s KPI list, replacing the hardcoded placeholder —
falls back to `"—"` only when there is truly no computable data (no contributor in scope has
both a Copilot match and non-zero churn).
- Add `query_ai_assisted_pct_by_project(period_since, period_until)` for a per-project
breakdown (used by whatever view needs project-level granularity — e.g. a projects table
column or a project-filtered KPI).
- Cap all computed percentages at 100.
2. In `api/server.py`: expose the per-project breakdown if the frontend needs a dedicated
endpoint (or fold into an existing aggregate response) — confirm against
`public/src/api.js`'s current consumption pattern before adding a new route unnecessarily.
3. In the frontend (`public/src/**`): update the "AI-Assisted %" KPI card (and any per-project
surfacing) to include a small label/tooltip: "Copilot-based estimate" — must be visible, not
buried in a hover-only state with no visual affordance that it's there.
### Prompt — paste verbatim into Claude Code
You are implementing PR-11 of the AI-Assisted % KPI playbook
(docs/playbooks/v5-ai-assisted-percentage-playbook.md).
Read the full PR-11 section and the "Formula choice — DECIDED" subsection before writing any
code. Read store/db.py (especially query_aggregate() and query_copilot_user_detail()),
store/schema.sql's copilot_user_usage/daily_commits/ai_assisted_pct comments, api/server.py, and
the KPI-card-rendering component in public/src/** in full first.
Branch: feature/112671-ai-assisted-percentage-v5-pr11 from dev.
Scope — T11 ONLY: replace the hardcoded "AI-Assisted %" placeholder with a real computed value,
per the locked formula (contributor: Copilot loc_added_sum ÷ total churn_added; project:
allocated proportionally by commit-churn share). Do NOT modify copilot_connector.py,
ado_connector.py, or either source table's ingest logic — this is read-only computation over
already-ingested data. Do NOT implement the commit-trailer (Assisted-By:) attribution scheme —
that remains out of scope.
═══════════════════════════════════════
STEP 1 — Store-layer computation
═══════════════════════════════════════
1. Factor out the existing user_login↔contributor_email matching heuristic from
query_copilot_user_detail() into a shared helper function.
2. Add query_ai_assisted_pct(period_since, period_until, project_id="all") implementing the
locked formula — contributor-level numerator/denominator from copilot_user_usage.loc_added_sum
and SUM(daily_commits.churn_added). Skip (don't crash on) contributors with zero churn_added
in the window. Cap result at 100.
3. Add query_ai_assisted_pct_by_project(period_since, period_until) implementing the allocation
formula (contributor's Copilot LOC distributed across projects proportional to their
churn_added share in each), aggregated per project.
4. Wire query_ai_assisted_pct() into query_aggregate()'s KPI list, replacing the hardcoded
with_delta("AI-Assisted %", "—", 0, "pp") line — preserve the with_delta() shape/convention
used by the other KPI cards (value, deltaLabel, isUp/isDown/isFlat). Fall back to "—" only
when there is truly no computable data for the period.
═══════════════════════════════════════
═══════════════════════════════════════
1. Confirm whether the existing /api/aggregate (or equivalent) response already carries the KPI
list from query_aggregate() — if so, no new route is needed for the contributor-level number.
2. If per-project breakdown needs its own endpoint, add it following the existing handler
conventions in api/server.py; otherwise fold it into an existing response the frontend already
fetches for project-level data.
═══════════════════════════════════════
═══════════════════════════════════════
1. Update the "AI-Assisted %" KPI card component to render the real value/delta from the API
response (no code change needed if it already just renders whatever with_delta() shape it's
given — confirm before touching markup).
2. Add a visible "Copilot-based estimate" label or icon+tooltip next to this KPI card — must have
a visual affordance (not hover-only with zero visual indicator that more context exists).
3. If a per-project breakdown is surfaced anywhere (e.g. project table, project detail view),
apply the same "Copilot-based estimate" labeling there too.
═══════════════════════════════════════
═══════════════════════════════════════
1. Extend tests/test_store_db.py to cover: normal contributor case (Copilot match + non-zero
churn), contributor with no Copilot match (must show no-data, not 0%), contributor with zero
churn_added (must not crash/produce NaN), per-project allocation math with 2+ projects for one
contributor, and the 100%-cap guard.
2. Run the full test suite (pytest) and the Vue test suite (public/tests) — no failures.
3. Confirm daily_commits, copilot_user_usage, and their existing ingest functions are
byte-for-byte unchanged (git diff shows no modifications to those symbols/tables).
Once implementation is complete and all tests pass, STOP. Do not stage,
commit, or push anything — confirm with the user first.
1. git add -A (stage all files).
2. Commit via the commit-msg skill.
Suggested title: "feat: compute real AI-Assisted % from Copilot usage + commit churn (T11)"
3. git push -u origin feature/112671-ai-assisted-percentage-v5-pr11. No PR to open.
No Co-authored-by: Claude trailer.
## 7. Verification Samples
**Contributor-level, single project, simple case:**
- `copilot_user_usage.loc_added_sum` = 400 for `fehsan_abold` (28-day window)
- `daily_commits.churn_added` sums to 1000 for `fehsan@alphabold.com` in the same window, all in
- Expected: `AI-Assisted % = 400 / 1000 * 100 = 40%` for the contributor; project `p1`'s allocated
share = 100% of that 400 LOC (since all churn was in `p1`), so project `p1`'s AI-Assisted % from
this contributor alone = 40%.
**Contributor-level, split across two projects:**
- Same 400 LOC from Copilot; `churn_added` = 600 in `p1`, 400 in `p2` (total 1000).
- Allocated Copilot LOC: `p1` gets `400 × (600/1000) = 240`; `p2` gets `400 × (400/1000) = 160`.
- `p1`'s contribution to project AI-Assisted % (numerator/denominator) = 240 / 600 = 40%;
`p2`'s = 160 / 400 = 40% (proportional allocation preserves the same rate per project when only
one contributor is involved — this changes once multiple contributors with different rates are
**No-match / zero-churn cases:**
- Contributor with a `copilot_user_usage` row but no matching `contributor_email` in
`daily_commits` for the period → excluded from the aggregate, not counted as `0%`.
- Contributor with `churn_added` = 0 in the period (no commits) → excluded (divide-by-zero
guard), not `NaN`/`Infinity`/crash.
Revert the PR-11 commit on `dev` (single commit/branch, no stacked dependents). This restores
the hardcoded `"AI-Assisted %": "—"` placeholder; no schema changes are introduced by this
playbook (it reads existing tables only), so rollback has zero data-migration concerns.
