[Bug][gh-copilot] DORA dashboard uses seat-assigning organization as organization adoption denominator

Bắt đầu bằng cách lần theo 27 vị trí truy vấn MySQL và các truy vấn PostgreSQL tương ứng trong các dashboard github-copilot-dora-correlation đi kèm. Sau đó kiểm tra CollectCopilotSeatAssignments, copilotSeatResponse và ExtractSeats để hiểu dữ liệu enterprise seat và việc quy thuộc cho tổ chức. Phần hoàn thành cần xác định một tập hợp người dùng được cấp phép hợp lệ trong phạm vi tổ chức cho mức độ áp dụng hiện tại và lịch sử, mà không phân loại mức độ áp dụng NULL là >75%.

Search before asking

• I searched the issues and found no issue covering this organization attribution and denominator problem.

Related, but not duplicates:

• #8964 updates Copilot API endpoints and schemas.
• #8960 adds AI adoption phases/cohorts.
• #9002 implements parts of those changes.

What happened

The bundled github-copilot-dora-correlation dashboards calculate organization adoption as:

daily_active_users /
COUNT(_tool_copilot_seats WHERE organization = selected_scope.organization)

This assumes that Copilot seats assigned to members of an organization are also assigned by that organization.

GitHub explicitly supports assigning licenses through a central or "shell" organization. Its current attribution rules state that once a user has a Copilot seat anywhere in the enterprise, their usage is attributed to every organization in that enterprise where they are a member.

Therefore, an organization can have valid organization-scoped usage metrics while having zero seats assigned directly by that organization.

Observed result:

• _tool_copilot_enterprise_daily_metrics contained daily active-user data for the selected organization scope.
• _tool_copilot_seats contained no seats attributed to that organization.
• The dashboard denominator was zero, so adoption was NULL.
• Adoption trend, current adoption, correlation, and tier-based panels showed no or misleading data.
• NULL adoption was classified as >75% by tier queries because their CASE expressions use ELSE '>75%'.

The MySQL DORA dashboard currently contains this seat-denominator pattern in 27 query locations. The PostgreSQL version appears to use the same assumption.

There is also a plugin-level enterprise seat attribution concern:

• CollectCopilotSeatAssignments correctly calls the enterprise seats endpoint when an enterprise is configured.
• copilotSeatResponse does not parse the response's organization field.
• ExtractSeats sets every row's Organization to connection.Organization.
• The enterprise endpoint can return multiple seat rows for the same user when access is granted through multiple organizations or enterprise teams.

This means enterprise seat records cannot reliably represent organization membership or organization-level license eligibility.

GitHub documentation:

https://docs.github.com/en/copilot/concepts/copilot-usage-metrics/copilot-metrics#how-are-metrics-attributed-across-organizations

Relevant behavior from the documentation:

If licenses are assigned in a dedicated "shell" organization for administrative purposes within the enterprise, users still appear in the metrics for all other organizations in the enterprise they belong to.

What do you expect to happen

Organization-scoped dashboards should use an organization-scoped licensed-user population rather than filtering enterprise seats by their assigning organization.

How to reproduce

1. Create an enterprise containing:
   • a central organization used to assign Copilot seats
   • a separate product organization
2. Assign Copilot seats to users through the central organization.
3. Make those users active members of the product organization.
4. Enable GitHub Copilot usage metrics for the enterprise.
5. Configure the DevLake gh-copilot plugin with the enterprise and product organization.
6. Collect Copilot reports and seat assignments.
7. Select the product organization in the bundled GitHub Copilot + DORA Correlation dashboard.
8. Confirm that organization-scoped active-user metrics exist.
9. Observe that the dashboard denominator is zero or otherwise incorrect because it filters seats by assigning organization.
10. Observe that NULL adoption weeks can be labeled >75%.

Diagnostic query:

SELECT
  day,
  daily_active_users
FROM _tool_copilot_enterprise_daily_metrics
WHERE connection_id = ?
  AND scope_id = ?
ORDER BY day DESC;

Current denominator pattern:

SELECT COUNT(*)
FROM _tool_copilot_seats s
WHERE s.connection_id = ?
  AND s.organization = ?;

The first query returns organization usage while the second returns no organization-assigned seats.

Anything else

Counting rows from _tool_copilot_user_daily_metrics is not a valid replacement denominator. Those reports represent daily user activity and produce weekday/weekend fluctuations rather than the complete licensed population.

The current-state seat table also cannot reconstruct historical weekly eligibility. A historical adoption trend needs either:

• a daily licensed-population snapshot, or
• an effective-dated license/membership source.

The dashboard should avoid silently applying today's seat state to historical dates.

Version

Observed with the gh-copilot plugin and bundled dashboard from the DevLake v1.0.3-beta10 generation. The same denominator pattern is present on the current main branch.

Are you willing to submit PR?

• Yes, I am willing to submit a PR.

Code of Conduct

• I agree to follow this project's Code of Conduct.