This is an automated email from the ASF dual-hosted git repository. ewega pushed a commit to branch ewega-copilot-ai-credit-usage in repository https://gitbox.apache.org/repos/asf/devlake.git
commit b072652bf34f4ed3dd1c551180d64ae2b7b2ba83 Author: ewega <[email protected]> AuthorDate: Tue Jun 30 19:47:47 2026 +0300 feat(gh-copilot): ingest AI credit usage and add credit consumption dashboard panels Add first-class GitHub Copilot AI credit billing ingestion to the gh-copilot plugin and surface credit-consumption KPIs on the Copilot Adoption dashboard. - New tool model _tool_copilot_ai_credit_usage (+ migration, GetTablesInfo) - Enterprise + organization daily collectors and extractors for the Billing Usage AI credit endpoints (API version 2026-03-10), reusing the existing stateful daily-iterator pattern - Wire 4 new subtask metas (collect/extract x enterprise/org) - Add "AI Credit Consumption" row (stats, time series, by-model and by-product/SKU tables) to mysql + postgresql adoption dashboards - e2e fixtures + extractor unit tests - Docs: plugin README + dashboard README Resolves #8961 Co-authored-by: Copilot <[email protected]> --- backend/plugins/gh-copilot/README.md | 14 + .../_raw_copilot_ai_credit_usage_org.csv | 2 + .../_tool_copilot_ai_credit_usage.csv | 3 + backend/plugins/gh-copilot/e2e/metrics_test.go | 8 + .../plugins/gh-copilot/models/ai_credit_usage.go | 76 ++++ .../20260701_add_copilot_ai_credit_usage.go | 78 ++++ .../gh-copilot/models/migrationscripts/register.go | 1 + backend/plugins/gh-copilot/models/models.go | 2 + .../gh-copilot/tasks/ai_credit_usage_collector.go | 172 +++++++++ .../gh-copilot/tasks/ai_credit_usage_extractor.go | 162 ++++++++ .../tasks/ai_credit_usage_extractor_test.go | 128 +++++++ backend/plugins/gh-copilot/tasks/register.go | 4 + backend/plugins/gh-copilot/tasks/subtasks.go | 34 ++ grafana/dashboards/mysql/GithubCopilotREADME.md | 2 + .../dashboards/mysql/github-copilot-adoption.json | 2 +- .../postgresql/github-copilot-adoption.json | 423 +++++++++++++++++++++ 16 files changed, 1110 insertions(+), 1 deletion(-) diff --git a/backend/plugins/gh-copilot/README.md b/backend/plugins/gh-copilot/README.md index 1c4aeda24..5d36e6aae 100644 --- a/backend/plugins/gh-copilot/README.md +++ b/backend/plugins/gh-copilot/README.md @@ -28,11 +28,22 @@ It follows the same structure/patterns as other DevLake data-source plugins (not - `GET /orgs/{org}/copilot/billing/seats` - `GET /orgs/{org}/copilot/metrics` +**AI credit usage endpoints** (GitHub Billing Usage REST API, version `2026-03-10`): + +- `GET /enterprises/{enterprise}/settings/billing/ai_credit/usage` +- `GET /organizations/{org}/settings/billing/ai_credit/usage` (note: `organizations/`, not `orgs/`) + +These are collected **daily** (one request per day, iterating `year`/`month`/`day`) and report Copilot +**AI credit consumption** broken down by product, SKU and model. Quantities and amounts are floats and the +`unit_type` is `ai-credits`. + **Stored data (tool layer)**: - `_tool_copilot_org_metrics` (daily aggregates) - `_tool_copilot_language_metrics` (editor/language breakdown) - `_tool_copilot_seats` (seat assignments) +- `_tool_copilot_ai_credit_usage` (daily AI credit consumption: gross/net quantity and amount per + product/sku/model, at `enterprise` or `organization` level) ## Data flow (high level) @@ -61,6 +72,9 @@ flowchart LR - GitHub Copilot Business or Enterprise enabled for the target organization - A token that can access GitHub Copilot billing/metrics (classic PAT with `manage_billing:copilot` works) +- For **AI credit usage**, the token additionally needs billing read access: + `manage_billing:copilot` (or `admin:enterprise` for the enterprise endpoint / `admin:org` for the + organization endpoint) ### 1) Create a connection diff --git a/backend/plugins/gh-copilot/e2e/metrics/raw_tables/_raw_copilot_ai_credit_usage_org.csv b/backend/plugins/gh-copilot/e2e/metrics/raw_tables/_raw_copilot_ai_credit_usage_org.csv new file mode 100644 index 000000000..6d59ff43b --- /dev/null +++ b/backend/plugins/gh-copilot/e2e/metrics/raw_tables/_raw_copilot_ai_credit_usage_org.csv @@ -0,0 +1,2 @@ +id,params,data,url,input,created_at +1,"{""ConnectionId"": 1, ""ScopeId"": ""octodemo"", ""Organization"": ""octodemo"", ""Endpoint"": ""https://api.github.com""}","{""timePeriod"": {""year"": 2026, ""month"": 6, ""day"": 8}, ""organization"": ""octodemo"", ""usageItems"": [{""product"": ""Copilot"", ""sku"": ""Copilot AI Credits"", ""model"": ""Claude Opus 4.8"", ""unitType"": ""ai-credits"", ""pricePerUnit"": 0.01, ""grossQuantity"": 5445.77595, ""grossAmount"": 54.4577595, ""discountQuantity"": 5445.77595, ""discountAmou [...] diff --git a/backend/plugins/gh-copilot/e2e/metrics/snapshot_tables/_tool_copilot_ai_credit_usage.csv b/backend/plugins/gh-copilot/e2e/metrics/snapshot_tables/_tool_copilot_ai_credit_usage.csv new file mode 100644 index 000000000..3fbe908d9 --- /dev/null +++ b/backend/plugins/gh-copilot/e2e/metrics/snapshot_tables/_tool_copilot_ai_credit_usage.csv @@ -0,0 +1,3 @@ +connection_id,scope_id,level,year,month,day,product,sku,model,unit_type,cost_center_id,date,enterprise,organization,user_login,cost_center_name,price_per_unit,gross_quantity,gross_amount,discount_quantity,discount_amount,net_quantity,net_amount +1,octodemo,organization,2026,6,8,Copilot,Copilot AI Credits,Claude Opus 4.8,ai-credits,,2026-06-08T00:00:00.000+00:00,,octodemo,,,0.01,5445.77595,54.4577595,5445.77595,54.4577595,0,0 +1,octodemo,organization,2026,6,8,Copilot,Copilot AI Credits,Claude Sonnet 4.6,ai-credits,,2026-06-08T00:00:00.000+00:00,,octodemo,,,0.01,176.97417,1.7697417,175.3533,1.753533,1.62087,0.0162087 diff --git a/backend/plugins/gh-copilot/e2e/metrics_test.go b/backend/plugins/gh-copilot/e2e/metrics_test.go index 2f339f95d..03b9c265d 100644 --- a/backend/plugins/gh-copilot/e2e/metrics_test.go +++ b/backend/plugins/gh-copilot/e2e/metrics_test.go @@ -60,13 +60,16 @@ func TestCopilotMetricsDataFlow(t *testing.T) { dataflowTester.ImportCsvIntoRawTable("./metrics/raw_tables/_raw_copilot_metrics.csv", "_raw_copilot_org_metrics") dataflowTester.ImportCsvIntoRawTable("./metrics/raw_tables/_raw_copilot_seats.csv", "_raw_copilot_seats") + dataflowTester.ImportCsvIntoRawTable("./metrics/raw_tables/_raw_copilot_ai_credit_usage_org.csv", "_raw_copilot_ai_credit_usage_org") dataflowTester.FlushTabler(&models.GhCopilotSeat{}) dataflowTester.FlushTabler(&models.GhCopilotEnterpriseDailyMetrics{}) dataflowTester.FlushTabler(&models.GhCopilotMetricsByLanguageFeature{}) + dataflowTester.FlushTabler(&models.GhCopilotAiCreditUsage{}) dataflowTester.Subtask(tasks.ExtractSeatsMeta, taskData) dataflowTester.Subtask(tasks.ExtractOrgMetricsMeta, taskData) + dataflowTester.Subtask(tasks.ExtractOrgAiCreditUsageMeta, taskData) dataflowTester.VerifyTableWithOptions(&models.GhCopilotEnterpriseDailyMetrics{}, e2ehelper.TableOptions{ CSVRelPath: "./metrics/snapshot_tables/_tool_copilot_enterprise_daily_metrics.csv", @@ -84,4 +87,9 @@ func TestCopilotMetricsDataFlow(t *testing.T) { CSVRelPath: "./metrics/snapshot_tables/_tool_copilot_metrics_by_language_feature.csv", IgnoreTypes: []interface{}{common.NoPKModel{}}, }) + + dataflowTester.VerifyTableWithOptions(&models.GhCopilotAiCreditUsage{}, e2ehelper.TableOptions{ + CSVRelPath: "./metrics/snapshot_tables/_tool_copilot_ai_credit_usage.csv", + IgnoreTypes: []interface{}{common.NoPKModel{}}, + }) } diff --git a/backend/plugins/gh-copilot/models/ai_credit_usage.go b/backend/plugins/gh-copilot/models/ai_credit_usage.go new file mode 100644 index 000000000..6cb794080 --- /dev/null +++ b/backend/plugins/gh-copilot/models/ai_credit_usage.go @@ -0,0 +1,76 @@ +/* +Licensed to the Apache Software Foundation (ASF) under one or more +contributor license agreements. See the NOTICE file distributed with +this work for additional information regarding copyright ownership. +The ASF licenses this file to You under the Apache License, Version 2.0 +(the "License"); you may not use this file except in compliance with +the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + +Unless required by applicable law or agreed to in writing, software +distributed under the License is distributed on an "AS IS" BASIS, +WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +See the License for the specific language governing permissions and +limitations under the License. +*/ + +package models + +import ( + "time" + + "github.com/apache/incubator-devlake/core/models/common" +) + +// AI credit usage "level" identifies which billing endpoint a usage row came from. +const ( + AiCreditUsageLevelEnterprise = "enterprise" + AiCreditUsageLevelOrganization = "organization" +) + +// GhCopilotAiCreditUsage stores a single AI credit billing usage line item for a +// time period, sourced from the GitHub Billing Usage "AI credit usage" reports +// (enterprise and organization endpoints). Each row corresponds to one usageItem +// (a product/sku/model/unitType combination) within a single day. +type GhCopilotAiCreditUsage struct { + ConnectionId uint64 `gorm:"primaryKey" json:"connectionId"` + ScopeId string `gorm:"primaryKey;type:varchar(255)" json:"scopeId"` + // Level distinguishes enterprise vs organization sourced usage. + Level string `gorm:"primaryKey;type:varchar(20)" json:"level"` + // Time period of the usage line (daily granularity). + Year int `gorm:"primaryKey" json:"year"` + Month int `gorm:"primaryKey" json:"month"` + Day int `gorm:"primaryKey" json:"day"` + // Usage line dimensions. + Product string `gorm:"primaryKey;type:varchar(100)" json:"product"` + Sku string `gorm:"primaryKey;type:varchar(150)" json:"sku"` + Model string `gorm:"primaryKey;type:varchar(150)" json:"model"` + UnitType string `gorm:"primaryKey;type:varchar(50)" json:"unitType"` + CostCenterId string `gorm:"primaryKey;type:varchar(100)" json:"costCenterId"` + + // Date is the time period expressed as a date (year-month-day) for easier + // time-series querying in dashboards. + Date time.Time `gorm:"type:date;index" json:"date"` + + // Top-level context (may be empty for the unfiltered aggregate report). + Enterprise string `gorm:"type:varchar(100)" json:"enterprise"` + Organization string `gorm:"type:varchar(100)" json:"organization"` + UserLogin string `gorm:"type:varchar(255)" json:"userLogin"` + CostCenterName string `gorm:"type:varchar(255)" json:"costCenterName"` + + // Billing metrics. GitHub returns fractional values, so these are float64. + PricePerUnit float64 `json:"pricePerUnit" gorm:"comment:Price per unit for the line item"` + GrossQuantity float64 `json:"grossQuantity" gorm:"comment:Gross quantity consumed (e.g. credits)"` + GrossAmount float64 `json:"grossAmount" gorm:"comment:Gross billed amount"` + DiscountQuantity float64 `json:"discountQuantity" gorm:"comment:Discounted quantity"` + DiscountAmount float64 `json:"discountAmount" gorm:"comment:Discounted amount"` + NetQuantity float64 `json:"netQuantity" gorm:"comment:Net quantity after discounts"` + NetAmount float64 `json:"netAmount" gorm:"comment:Net billed amount after discounts"` + + common.NoPKModel +} + +func (GhCopilotAiCreditUsage) TableName() string { + return "_tool_copilot_ai_credit_usage" +} diff --git a/backend/plugins/gh-copilot/models/migrationscripts/20260701_add_copilot_ai_credit_usage.go b/backend/plugins/gh-copilot/models/migrationscripts/20260701_add_copilot_ai_credit_usage.go new file mode 100644 index 000000000..7c8f946da --- /dev/null +++ b/backend/plugins/gh-copilot/models/migrationscripts/20260701_add_copilot_ai_credit_usage.go @@ -0,0 +1,78 @@ +/* +Licensed to the Apache Software Foundation (ASF) under one or more +contributor license agreements. See the NOTICE file distributed with +this work for additional information regarding copyright ownership. +The ASF licenses this file to You under the Apache License, Version 2.0 +(the "License"); you may not use this file except in compliance with +the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + +Unless required by applicable law or agreed to in writing, software +distributed under the License is distributed on an "AS IS" BASIS, +WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +See the License for the specific language governing permissions and +limitations under the License. +*/ + +package migrationscripts + +import ( + "time" + + "github.com/apache/incubator-devlake/core/context" + "github.com/apache/incubator-devlake/core/errors" + "github.com/apache/incubator-devlake/core/models/migrationscripts/archived" + "github.com/apache/incubator-devlake/helpers/migrationhelper" +) + +type addCopilotAiCreditUsage struct{} + +type aiCreditUsage20260701 struct { + ConnectionId uint64 `gorm:"primaryKey"` + ScopeId string `gorm:"primaryKey;type:varchar(255)"` + Level string `gorm:"primaryKey;type:varchar(20)"` + Year int `gorm:"primaryKey"` + Month int `gorm:"primaryKey"` + Day int `gorm:"primaryKey"` + Product string `gorm:"primaryKey;type:varchar(100)"` + Sku string `gorm:"primaryKey;type:varchar(150)"` + Model string `gorm:"primaryKey;type:varchar(150)"` + UnitType string `gorm:"primaryKey;type:varchar(50)"` + CostCenterId string `gorm:"primaryKey;type:varchar(100)"` + + Date time.Time `gorm:"type:date;index"` + + Enterprise string `gorm:"type:varchar(100)"` + Organization string `gorm:"type:varchar(100)"` + UserLogin string `gorm:"type:varchar(255)"` + CostCenterName string `gorm:"type:varchar(255)"` + + PricePerUnit float64 + GrossQuantity float64 + GrossAmount float64 + DiscountQuantity float64 + DiscountAmount float64 + NetQuantity float64 + NetAmount float64 + + archived.NoPKModel +} + +func (aiCreditUsage20260701) TableName() string { + return "_tool_copilot_ai_credit_usage" +} + +func (script *addCopilotAiCreditUsage) Up(basicRes context.BasicRes) errors.Error { + return migrationhelper.AutoMigrateTables(basicRes, + &aiCreditUsage20260701{}, + ) +} + +func (*addCopilotAiCreditUsage) Version() uint64 { + return 20260701000000 +} + +func (*addCopilotAiCreditUsage) Name() string { + return "Add Copilot AI credit usage table" +} diff --git a/backend/plugins/gh-copilot/models/migrationscripts/register.go b/backend/plugins/gh-copilot/models/migrationscripts/register.go index 399735695..0529532e2 100644 --- a/backend/plugins/gh-copilot/models/migrationscripts/register.go +++ b/backend/plugins/gh-copilot/models/migrationscripts/register.go @@ -31,5 +31,6 @@ func All() []plugin.MigrationScript { new(addPRFieldsToEnterpriseMetrics), new(addOrganizationIdToUserMetrics), new(addCopilotMetricsGaps), + new(addCopilotAiCreditUsage), } } diff --git a/backend/plugins/gh-copilot/models/models.go b/backend/plugins/gh-copilot/models/models.go index 5143ce5f8..2e43a1af6 100644 --- a/backend/plugins/gh-copilot/models/models.go +++ b/backend/plugins/gh-copilot/models/models.go @@ -47,5 +47,7 @@ func GetTablesInfo() []dal.Tabler { &GhCopilotSeat{}, // User-team mappings &GhCopilotUserTeam{}, + // AI credit billing usage + &GhCopilotAiCreditUsage{}, } } diff --git a/backend/plugins/gh-copilot/tasks/ai_credit_usage_collector.go b/backend/plugins/gh-copilot/tasks/ai_credit_usage_collector.go new file mode 100644 index 000000000..dded8f4f2 --- /dev/null +++ b/backend/plugins/gh-copilot/tasks/ai_credit_usage_collector.go @@ -0,0 +1,172 @@ +/* +Licensed to the Apache Software Foundation (ASF) under one or more +contributor license agreements. See the NOTICE file distributed with +this work for additional information regarding copyright ownership. +The ASF licenses this file to You under the Apache License, Version 2.0 +(the "License"); you may not use this file except in compliance with +the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + +Unless required by applicable law or agreed to in writing, software +distributed under the License is distributed on an "AS IS" BASIS, +WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +See the License for the specific language governing permissions and +limitations under the License. +*/ + +package tasks + +import ( + "bytes" + "encoding/json" + "fmt" + "io" + "net/http" + "net/url" + "strconv" + "strings" + "time" + + "github.com/apache/incubator-devlake/core/errors" + "github.com/apache/incubator-devlake/core/log" + "github.com/apache/incubator-devlake/core/plugin" + helper "github.com/apache/incubator-devlake/helpers/pluginhelper/api" + "github.com/apache/incubator-devlake/plugins/gh-copilot/models" +) + +const ( + rawEnterpriseAiCreditUsageTable = "copilot_ai_credit_usage_enterprise" + rawOrgAiCreditUsageTable = "copilot_ai_credit_usage_org" +) + +// CollectEnterpriseAiCreditUsage collects enterprise-level daily AI credit billing usage. +func CollectEnterpriseAiCreditUsage(taskCtx plugin.SubTaskContext) errors.Error { + return collectAiCreditUsage(taskCtx, models.AiCreditUsageLevelEnterprise) +} + +// CollectOrgAiCreditUsage collects organization-level daily AI credit billing usage. +func CollectOrgAiCreditUsage(taskCtx plugin.SubTaskContext) errors.Error { + return collectAiCreditUsage(taskCtx, models.AiCreditUsageLevelOrganization) +} + +// collectAiCreditUsage iterates day-by-day over the GitHub Billing Usage +// "AI credit usage" endpoint, passing year/month/day query parameters, and stores +// each daily report as a raw record. Shared by the enterprise and organization subtasks. +func collectAiCreditUsage(taskCtx plugin.SubTaskContext, level string) errors.Error { + data, ok := taskCtx.TaskContext().GetData().(*GhCopilotTaskData) + if !ok { + return errors.Default.New("task data is not GhCopilotTaskData") + } + connection := data.Connection + connection.Normalize() + logger := taskCtx.GetLogger() + + var urlTemplate, rawTable string + switch level { + case models.AiCreditUsageLevelEnterprise: + if !connection.HasEnterprise() { + logger.Info("No enterprise configured, skipping enterprise AI credit usage collection") + return nil + } + urlTemplate = fmt.Sprintf("enterprises/%s/settings/billing/ai_credit/usage", connection.Enterprise) + rawTable = rawEnterpriseAiCreditUsageTable + case models.AiCreditUsageLevelOrganization: + if connection.Organization == "" { + logger.Info("No organization configured, skipping org AI credit usage collection") + return nil + } + // Note: the billing usage endpoint uses "organizations/{org}", not "orgs/{org}". + urlTemplate = fmt.Sprintf("organizations/%s/settings/billing/ai_credit/usage", connection.Organization) + rawTable = rawOrgAiCreditUsageTable + default: + return errors.Default.New(fmt.Sprintf("unknown AI credit usage level: %s", level)) + } + + apiClient, err := CreateApiClient(taskCtx.TaskContext(), connection) + if err != nil { + return err + } + + rawArgs := helper.RawDataSubTaskArgs{ + Ctx: taskCtx, + Table: rawTable, + Options: copilotRawParams{ + ConnectionId: data.Options.ConnectionId, + ScopeId: data.Options.ScopeId, + Organization: connection.Organization, + Endpoint: connection.Endpoint, + }, + } + + collector, err := helper.NewStatefulApiCollector(rawArgs) + if err != nil { + return err + } + + now := time.Now().UTC() + start, until := computeReportDateRange(now, collector.GetSince()) + start = clampDailyMetricsStartForBackfill(start, until) + + dayIter := newDayIterator(start, until) + + err = collector.InitCollector(helper.ApiCollectorArgs{ + ApiClient: apiClient, + Input: dayIter, + UrlTemplate: urlTemplate, + Query: func(reqData *helper.RequestData) (url.Values, errors.Error) { + input := reqData.Input.(*dayInput) + y, m, d, parseErr := splitDay(input.Day) + if parseErr != nil { + return nil, parseErr + } + q := url.Values{} + q.Set("year", strconv.Itoa(y)) + q.Set("month", strconv.Itoa(m)) + q.Set("day", strconv.Itoa(d)) + return q, nil + }, + Incremental: true, + Concurrency: 1, + AfterResponse: ignoreNoContent, + ResponseParser: func(res *http.Response) ([]json.RawMessage, errors.Error) { + return parseAiCreditUsageResponse(res, logger) + }, + }) + if err != nil { + return err + } + return collector.Execute() +} + +// splitDay parses a YYYY-MM-DD string into year, month and day integers. +func splitDay(day string) (int, int, int, errors.Error) { + t, parseErr := time.Parse("2006-01-02", day) + if parseErr != nil { + return 0, 0, 0, errors.Default.Wrap(parseErr, fmt.Sprintf("invalid day %q", day)) + } + return t.Year(), int(t.Month()), t.Day(), nil +} + +// parseAiCreditUsageResponse reads the single JSON report object returned for one day. +// Empty bodies (no usage) are skipped; the whole object is stored as one raw record +// and exploded into per-usageItem rows by the extractor. +func parseAiCreditUsageResponse(res *http.Response, logger log.Logger) ([]json.RawMessage, errors.Error) { + body, readErr := io.ReadAll(res.Body) + res.Body.Close() + if readErr != nil { + return nil, errors.Default.Wrap(readErr, "failed to read AI credit usage response") + } + if isEmptyReport(body) { + return nil, nil + } + trimmed := bytes.TrimSpace(body) + // Defensive: only forward objects that actually carry usage items. + if !strings.Contains(string(trimmed), "usageItems") { + if logger != nil { + logger.Info("AI credit usage response had no usageItems, skipping") + } + return nil, nil + } + return []json.RawMessage{json.RawMessage(trimmed)}, nil +} diff --git a/backend/plugins/gh-copilot/tasks/ai_credit_usage_extractor.go b/backend/plugins/gh-copilot/tasks/ai_credit_usage_extractor.go new file mode 100644 index 000000000..f94cdf600 --- /dev/null +++ b/backend/plugins/gh-copilot/tasks/ai_credit_usage_extractor.go @@ -0,0 +1,162 @@ +/* +Licensed to the Apache Software Foundation (ASF) under one or more +contributor license agreements. See the NOTICE file distributed with +this work for additional information regarding copyright ownership. +The ASF licenses this file to You under the Apache License, Version 2.0 +(the "License"); you may not use this file except in compliance with +the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + +Unless required by applicable law or agreed to in writing, software +distributed under the License is distributed on an "AS IS" BASIS, +WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +See the License for the specific language governing permissions and +limitations under the License. +*/ + +package tasks + +import ( + "encoding/json" + "time" + + "github.com/apache/incubator-devlake/core/errors" + "github.com/apache/incubator-devlake/core/plugin" + helper "github.com/apache/incubator-devlake/helpers/pluginhelper/api" + "github.com/apache/incubator-devlake/plugins/gh-copilot/models" +) + +// aiCreditUsageReport mirrors the GitHub Billing Usage "AI credit usage" response. +type aiCreditUsageReport struct { + TimePeriod struct { + Year int `json:"year"` + Month int `json:"month"` + Day int `json:"day"` + } `json:"timePeriod"` + Enterprise string `json:"enterprise"` + Organization string `json:"organization"` + User string `json:"user"` + CostCenter *struct { + Id string `json:"id"` + Name string `json:"name"` + } `json:"costCenter"` + UsageItems []aiCreditUsageItem `json:"usageItems"` +} + +type aiCreditUsageItem struct { + Product string `json:"product"` + Sku string `json:"sku"` + Model string `json:"model"` + UnitType string `json:"unitType"` + PricePerUnit float64 `json:"pricePerUnit"` + GrossQuantity float64 `json:"grossQuantity"` + GrossAmount float64 `json:"grossAmount"` + DiscountQuantity float64 `json:"discountQuantity"` + DiscountAmount float64 `json:"discountAmount"` + NetQuantity float64 `json:"netQuantity"` + NetAmount float64 `json:"netAmount"` +} + +// ExtractEnterpriseAiCreditUsage parses raw enterprise AI credit usage reports. +func ExtractEnterpriseAiCreditUsage(taskCtx plugin.SubTaskContext) errors.Error { + return extractAiCreditUsage(taskCtx, models.AiCreditUsageLevelEnterprise, rawEnterpriseAiCreditUsageTable) +} + +// ExtractOrgAiCreditUsage parses raw organization AI credit usage reports. +func ExtractOrgAiCreditUsage(taskCtx plugin.SubTaskContext) errors.Error { + return extractAiCreditUsage(taskCtx, models.AiCreditUsageLevelOrganization, rawOrgAiCreditUsageTable) +} + +// extractAiCreditUsage explodes each daily report's usageItems into one +// GhCopilotAiCreditUsage row per product/sku/model/unitType. Shared by the +// enterprise and organization extractor subtasks. +func extractAiCreditUsage(taskCtx plugin.SubTaskContext, level, rawTable string) errors.Error { + data, ok := taskCtx.TaskContext().GetData().(*GhCopilotTaskData) + if !ok { + return errors.Default.New("task data is not GhCopilotTaskData") + } + connection := data.Connection + connection.Normalize() + + params := copilotRawParams{ + ConnectionId: data.Options.ConnectionId, + ScopeId: data.Options.ScopeId, + Organization: connection.Organization, + Endpoint: connection.Endpoint, + } + + extractor, err := helper.NewApiExtractor(helper.ApiExtractorArgs{ + RawDataSubTaskArgs: helper.RawDataSubTaskArgs{ + Ctx: taskCtx, + Table: rawTable, + Options: params, + }, + Extract: func(row *helper.RawData) ([]interface{}, errors.Error) { + rows, err := mapAiCreditUsageReport(level, data.Options.ConnectionId, data.Options.ScopeId, row.Data) + if err != nil { + return nil, err + } + results := make([]interface{}, len(rows)) + for i, r := range rows { + results[i] = r + } + return results, nil + }, + }) + if err != nil { + return err + } + return extractor.Execute() +} + +// mapAiCreditUsageReport unmarshals a single AI credit usage report and explodes its +// usageItems into one GhCopilotAiCreditUsage row per product/sku/model/unitType. +func mapAiCreditUsageReport(level string, connectionId uint64, scopeId string, raw []byte) ([]*models.GhCopilotAiCreditUsage, errors.Error) { + var report aiCreditUsageReport + if err := errors.Convert(json.Unmarshal(raw, &report)); err != nil { + return nil, err + } + + tp := report.TimePeriod + var date time.Time + if tp.Year > 0 && tp.Month > 0 && tp.Day > 0 { + date = time.Date(tp.Year, time.Month(tp.Month), tp.Day, 0, 0, 0, 0, time.UTC) + } + + costCenterId, costCenterName := "", "" + if report.CostCenter != nil { + costCenterId = report.CostCenter.Id + costCenterName = report.CostCenter.Name + } + + results := make([]*models.GhCopilotAiCreditUsage, 0, len(report.UsageItems)) + for _, item := range report.UsageItems { + results = append(results, &models.GhCopilotAiCreditUsage{ + ConnectionId: connectionId, + ScopeId: scopeId, + Level: level, + Year: tp.Year, + Month: tp.Month, + Day: tp.Day, + Product: item.Product, + Sku: item.Sku, + Model: item.Model, + UnitType: item.UnitType, + CostCenterId: costCenterId, + Date: date, + Enterprise: report.Enterprise, + Organization: report.Organization, + UserLogin: report.User, + CostCenterName: costCenterName, + PricePerUnit: item.PricePerUnit, + GrossQuantity: item.GrossQuantity, + GrossAmount: item.GrossAmount, + DiscountQuantity: item.DiscountQuantity, + DiscountAmount: item.DiscountAmount, + NetQuantity: item.NetQuantity, + NetAmount: item.NetAmount, + }) + } + return results, nil +} diff --git a/backend/plugins/gh-copilot/tasks/ai_credit_usage_extractor_test.go b/backend/plugins/gh-copilot/tasks/ai_credit_usage_extractor_test.go new file mode 100644 index 000000000..8292f4fdd --- /dev/null +++ b/backend/plugins/gh-copilot/tasks/ai_credit_usage_extractor_test.go @@ -0,0 +1,128 @@ +/* +Licensed to the Apache Software Foundation (ASF) under one or more +contributor license agreements. See the NOTICE file distributed with +this work for additional information regarding copyright ownership. +The ASF licenses this file to You under the Apache License, Version 2.0 +(the "License"); you may not use this file except in compliance with +the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + +Unless required by applicable law or agreed to in writing, software +distributed under the License is distributed on an "AS IS" BASIS, +WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +See the License for the specific language governing permissions and +limitations under the License. +*/ + +package tasks + +import ( + "testing" + "time" + + "github.com/apache/incubator-devlake/plugins/gh-copilot/models" + "github.com/stretchr/testify/assert" + "github.com/stretchr/testify/require" +) + +func TestSplitDay(t *testing.T) { + y, m, d, err := splitDay("2026-06-08") + require.Nil(t, err) + assert.Equal(t, 2026, y) + assert.Equal(t, 6, m) + assert.Equal(t, 8, d) + + _, _, _, err = splitDay("not-a-date") + assert.NotNil(t, err) +} + +func TestMapAiCreditUsageReport(t *testing.T) { + raw := []byte(`{ + "timePeriod": {"year": 2026, "month": 6, "day": 8}, + "enterprise": "Avocado Corp", + "usageItems": [ + { + "product": "Copilot", + "sku": "Copilot AI Credits", + "model": "Claude Opus 4.8", + "unitType": "ai-credits", + "pricePerUnit": 0.01, + "grossQuantity": 5445.77595, + "grossAmount": 54.4577595, + "discountQuantity": 5445.77595, + "discountAmount": 54.4577595, + "netQuantity": 0.0, + "netAmount": 0.0 + }, + { + "product": "Copilot", + "sku": "Copilot AI Credits", + "model": "Claude Sonnet 4.6", + "unitType": "ai-credits", + "pricePerUnit": 0.01, + "grossQuantity": 176.97417, + "grossAmount": 1.7697417, + "discountQuantity": 175.3533, + "discountAmount": 1.753533, + "netQuantity": 1.62087, + "netAmount": 0.0162087 + } + ] + }`) + + rows, err := mapAiCreditUsageReport(models.AiCreditUsageLevelEnterprise, 1, "avocado", raw) + require.Nil(t, err) + require.Len(t, rows, 2) + + first := rows[0] + assert.Equal(t, uint64(1), first.ConnectionId) + assert.Equal(t, "avocado", first.ScopeId) + assert.Equal(t, models.AiCreditUsageLevelEnterprise, first.Level) + assert.Equal(t, 2026, first.Year) + assert.Equal(t, 6, first.Month) + assert.Equal(t, 8, first.Day) + assert.Equal(t, time.Date(2026, 6, 8, 0, 0, 0, 0, time.UTC), first.Date) + assert.Equal(t, "Avocado Corp", first.Enterprise) + assert.Equal(t, "Claude Opus 4.8", first.Model) + assert.Equal(t, "ai-credits", first.UnitType) + assert.InDelta(t, 5445.77595, first.GrossQuantity, 1e-9) + assert.InDelta(t, 0.0, first.NetQuantity, 1e-9) + + second := rows[1] + assert.Equal(t, "Claude Sonnet 4.6", second.Model) + assert.InDelta(t, 1.62087, second.NetQuantity, 1e-9) + assert.InDelta(t, 0.0162087, second.NetAmount, 1e-9) +} + +func TestMapAiCreditUsageReportWithCostCenter(t *testing.T) { + raw := []byte(`{ + "timePeriod": {"year": 2025, "month": 6, "day": 10}, + "enterprise": "acme", + "organization": "platform", + "user": "octocat", + "costCenter": {"id": "cc-1", "name": "Engineering"}, + "usageItems": [ + {"product": "Copilot", "sku": "AI-CREDITS", "model": "gpt-4.1", "unitType": "credits", + "pricePerUnit": 1, "grossQuantity": 10, "grossAmount": 10, "discountQuantity": 0, + "discountAmount": 0, "netQuantity": 10, "netAmount": 10} + ] + }`) + + rows, err := mapAiCreditUsageReport(models.AiCreditUsageLevelOrganization, 2, "platform", raw) + require.Nil(t, err) + require.Len(t, rows, 1) + + row := rows[0] + assert.Equal(t, "cc-1", row.CostCenterId) + assert.Equal(t, "Engineering", row.CostCenterName) + assert.Equal(t, "platform", row.Organization) + assert.Equal(t, "octocat", row.UserLogin) + assert.InDelta(t, 10.0, row.NetAmount, 1e-9) +} + +func TestMapAiCreditUsageReportEmpty(t *testing.T) { + rows, err := mapAiCreditUsageReport(models.AiCreditUsageLevelEnterprise, 1, "x", []byte(`{"timePeriod":{"year":2026,"month":6,"day":8},"usageItems":[]}`)) + require.Nil(t, err) + assert.Len(t, rows, 0) +} diff --git a/backend/plugins/gh-copilot/tasks/register.go b/backend/plugins/gh-copilot/tasks/register.go index 3c7e5b1ee..fe6724cf1 100644 --- a/backend/plugins/gh-copilot/tasks/register.go +++ b/backend/plugins/gh-copilot/tasks/register.go @@ -28,11 +28,15 @@ func GetSubTaskMetas() []plugin.SubTaskMeta { CollectEnterpriseMetricsMeta, CollectUserMetricsMeta, CollectUserTeamsMeta, + CollectEnterpriseAiCreditUsageMeta, + CollectOrgAiCreditUsageMeta, // Extractors ExtractSeatsMeta, ExtractOrgMetricsMeta, ExtractEnterpriseMetricsMeta, ExtractUserMetricsMeta, ExtractUserTeamsMeta, + ExtractEnterpriseAiCreditUsageMeta, + ExtractOrgAiCreditUsageMeta, } } diff --git a/backend/plugins/gh-copilot/tasks/subtasks.go b/backend/plugins/gh-copilot/tasks/subtasks.go index 61ed57995..fd58766d5 100644 --- a/backend/plugins/gh-copilot/tasks/subtasks.go +++ b/backend/plugins/gh-copilot/tasks/subtasks.go @@ -105,3 +105,37 @@ var ExtractUserTeamsMeta = plugin.SubTaskMeta{ Description: "Extract Copilot user-team mappings into tool-layer table", Dependencies: []*plugin.SubTaskMeta{&CollectUserTeamsMeta}, } + +var CollectEnterpriseAiCreditUsageMeta = plugin.SubTaskMeta{ + Name: "collectEnterpriseAiCreditUsage", + EntryPoint: CollectEnterpriseAiCreditUsage, + EnabledByDefault: true, + DomainTypes: []string{plugin.DOMAIN_TYPE_CROSS}, + Description: "Collect GitHub Copilot enterprise AI credit billing usage", +} + +var CollectOrgAiCreditUsageMeta = plugin.SubTaskMeta{ + Name: "collectOrgAiCreditUsage", + EntryPoint: CollectOrgAiCreditUsage, + EnabledByDefault: true, + DomainTypes: []string{plugin.DOMAIN_TYPE_CROSS}, + Description: "Collect GitHub Copilot organization AI credit billing usage", +} + +var ExtractEnterpriseAiCreditUsageMeta = plugin.SubTaskMeta{ + Name: "extractEnterpriseAiCreditUsage", + EntryPoint: ExtractEnterpriseAiCreditUsage, + EnabledByDefault: true, + DomainTypes: []string{plugin.DOMAIN_TYPE_CROSS}, + Description: "Extract Copilot enterprise AI credit usage into tool-layer table", + Dependencies: []*plugin.SubTaskMeta{&CollectEnterpriseAiCreditUsageMeta}, +} + +var ExtractOrgAiCreditUsageMeta = plugin.SubTaskMeta{ + Name: "extractOrgAiCreditUsage", + EntryPoint: ExtractOrgAiCreditUsage, + EnabledByDefault: true, + DomainTypes: []string{plugin.DOMAIN_TYPE_CROSS}, + Description: "Extract Copilot organization AI credit usage into tool-layer table", + Dependencies: []*plugin.SubTaskMeta{&CollectOrgAiCreditUsageMeta}, +} diff --git a/grafana/dashboards/mysql/GithubCopilotREADME.md b/grafana/dashboards/mysql/GithubCopilotREADME.md index 642ff4252..92f424824 100644 --- a/grafana/dashboards/mysql/GithubCopilotREADME.md +++ b/grafana/dashboards/mysql/GithubCopilotREADME.md @@ -30,6 +30,8 @@ Tracks GitHub Copilot usage metrics across your organization: - IDE distribution (VS Code, JetBrains, Neovim, etc.) - Acceptance rates for code suggestions - Chat and PR summary feature usage +- **AI Credit Consumption**: gross/net credits and billed amount over time, broken down by model and + product/SKU (sourced from `_tool_copilot_ai_credit_usage`) ### 2. GitHub Copilot Impact Dashboard (`GithubCopilotImpact.json`) diff --git a/grafana/dashboards/mysql/github-copilot-adoption.json b/grafana/dashboards/mysql/github-copilot-adoption.json index e018b6ede..8c8347120 100644 --- a/grafana/dashboards/mysql/github-copilot-adoption.json +++ b/grafana/dashboards/mysql/github-copilot-adoption.json @@ -1 +1 @@ -{"annotations":{"list":[{"builtIn":1,"datasource":{"type":"datasource","uid":"grafana"},"enable":true,"hide":true,"iconColor":"rgba(0, 211, 255, 1)","name":"Annotations & Alerts","type":"dashboard"}]},"editable":true,"fiscalYearStartMonth":0,"graphTooltip":0,"links":[],"liveNow":false,"panels":[{"datasource":"mysql","description":"Latest daily active users count","fieldConfig":{"defaults":{"mappings":[],"thresholds":{"mode":"absolute","steps":[{"color":"green","value":null},{"color":"yel [...] \ No newline at end of file +{"annotations":{"list":[{"builtIn":1,"datasource":{"type":"datasource","uid":"grafana"},"enable":true,"hide":true,"iconColor":"rgba(0, 211, 255, 1)","name":"Annotations & Alerts","type":"dashboard"}]},"editable":true,"fiscalYearStartMonth":0,"graphTooltip":0,"links":[],"liveNow":false,"panels":[{"datasource":"mysql","description":"Latest daily active users count","fieldConfig":{"defaults":{"mappings":[],"thresholds":{"mode":"absolute","steps":[{"color":"green","value":null},{"color":"yel [...] \ No newline at end of file diff --git a/grafana/dashboards/postgresql/github-copilot-adoption.json b/grafana/dashboards/postgresql/github-copilot-adoption.json index 978d263b8..a503ee4a4 100644 --- a/grafana/dashboards/postgresql/github-copilot-adoption.json +++ b/grafana/dashboards/postgresql/github-copilot-adoption.json @@ -2255,6 +2255,429 @@ ], "title": "Data Volume by Table", "type": "table" + }, + { + "collapsed": false, + "gridPos": { + "h": 1, + "w": 24, + "x": 0, + "y": 112 + }, + "id": 40, + "panels": [], + "title": "AI Credit Consumption", + "type": "row" + }, + { + "datasource": "postgresql", + "description": "Total gross AI credits consumed in the selected period", + "fieldConfig": { + "defaults": { + "mappings": [], + "thresholds": { + "mode": "absolute", + "steps": [ + { + "color": "green", + "value": null + } + ] + }, + "unit": "short", + "decimals": 2 + }, + "overrides": [] + }, + "gridPos": { + "h": 4, + "w": 6, + "x": 0, + "y": 113 + }, + "id": 41, + "options": { + "colorMode": "value", + "graphMode": "none", + "justifyMode": "auto", + "orientation": "auto", + "reduceOptions": { + "calcs": [ + "lastNotNull" + ], + "fields": "", + "values": false + }, + "showPercentChange": false, + "textMode": "auto", + "wideLayout": true + }, + "pluginVersion": "11.0.0", + "targets": [ + { + "datasource": "postgresql", + "format": "table", + "rawQuery": true, + "rawSql": "SELECT SUM(gross_quantity) AS \"Gross Credits\" FROM _tool_copilot_ai_credit_usage WHERE $__timeFilter(\"date\") AND ('${connection_id}' = '' OR connection_id::text = '${connection_id}') AND scope_id = '${scope_id}'", + "refId": "A" + } + ], + "title": "Total Gross Credits", + "type": "stat" + }, + { + "datasource": "postgresql", + "description": "Total net AI credits (after discounts) in the selected period", + "fieldConfig": { + "defaults": { + "mappings": [], + "thresholds": { + "mode": "absolute", + "steps": [ + { + "color": "green", + "value": null + } + ] + }, + "unit": "short", + "decimals": 2 + }, + "overrides": [] + }, + "gridPos": { + "h": 4, + "w": 6, + "x": 6, + "y": 113 + }, + "id": 42, + "options": { + "colorMode": "value", + "graphMode": "none", + "justifyMode": "auto", + "orientation": "auto", + "reduceOptions": { + "calcs": [ + "lastNotNull" + ], + "fields": "", + "values": false + }, + "showPercentChange": false, + "textMode": "auto", + "wideLayout": true + }, + "pluginVersion": "11.0.0", + "targets": [ + { + "datasource": "postgresql", + "format": "table", + "rawQuery": true, + "rawSql": "SELECT SUM(net_quantity) AS \"Net Credits\" FROM _tool_copilot_ai_credit_usage WHERE $__timeFilter(\"date\") AND ('${connection_id}' = '' OR connection_id::text = '${connection_id}') AND scope_id = '${scope_id}'", + "refId": "A" + } + ], + "title": "Total Net Credits", + "type": "stat" + }, + { + "datasource": "postgresql", + "description": "Total net billed amount (after discounts) in the selected period", + "fieldConfig": { + "defaults": { + "mappings": [], + "thresholds": { + "mode": "absolute", + "steps": [ + { + "color": "green", + "value": null + } + ] + }, + "unit": "currencyUSD", + "decimals": 2 + }, + "overrides": [] + }, + "gridPos": { + "h": 4, + "w": 6, + "x": 12, + "y": 113 + }, + "id": 43, + "options": { + "colorMode": "value", + "graphMode": "none", + "justifyMode": "auto", + "orientation": "auto", + "reduceOptions": { + "calcs": [ + "lastNotNull" + ], + "fields": "", + "values": false + }, + "showPercentChange": false, + "textMode": "auto", + "wideLayout": true + }, + "pluginVersion": "11.0.0", + "targets": [ + { + "datasource": "postgresql", + "format": "table", + "rawQuery": true, + "rawSql": "SELECT SUM(net_amount) AS \"Net Amount\" FROM _tool_copilot_ai_credit_usage WHERE $__timeFilter(\"date\") AND ('${connection_id}' = '' OR connection_id::text = '${connection_id}') AND scope_id = '${scope_id}'", + "refId": "A" + } + ], + "title": "Total Net Amount", + "type": "stat" + }, + { + "datasource": "postgresql", + "description": "Total gross billed amount in the selected period", + "fieldConfig": { + "defaults": { + "mappings": [], + "thresholds": { + "mode": "absolute", + "steps": [ + { + "color": "green", + "value": null + } + ] + }, + "unit": "currencyUSD", + "decimals": 2 + }, + "overrides": [] + }, + "gridPos": { + "h": 4, + "w": 6, + "x": 18, + "y": 113 + }, + "id": 44, + "options": { + "colorMode": "value", + "graphMode": "none", + "justifyMode": "auto", + "orientation": "auto", + "reduceOptions": { + "calcs": [ + "lastNotNull" + ], + "fields": "", + "values": false + }, + "showPercentChange": false, + "textMode": "auto", + "wideLayout": true + }, + "pluginVersion": "11.0.0", + "targets": [ + { + "datasource": "postgresql", + "format": "table", + "rawQuery": true, + "rawSql": "SELECT SUM(gross_amount) AS \"Gross Amount\" FROM _tool_copilot_ai_credit_usage WHERE $__timeFilter(\"date\") AND ('${connection_id}' = '' OR connection_id::text = '${connection_id}') AND scope_id = '${scope_id}'", + "refId": "A" + } + ], + "title": "Total Gross Amount", + "type": "stat" + }, + { + "datasource": "postgresql", + "description": "Gross vs net AI credits consumed per day", + "fieldConfig": { + "defaults": { + "color": { + "mode": "palette-classic" + }, + "custom": { + "axisBorderShow": false, + "axisCenteredZero": false, + "axisColorMode": "text", + "axisLabel": "Credits", + "axisPlacement": "auto", + "barAlignment": 0, + "drawStyle": "line", + "fillOpacity": 10, + "gradientMode": "none", + "hideFrom": { + "legend": false, + "tooltip": false, + "viz": false + }, + "insertNulls": false, + "lineInterpolation": "linear", + "lineWidth": 2, + "pointSize": 5, + "scaleDistribution": { + "type": "linear" + }, + "showPoints": "never", + "spanNulls": false, + "stacking": { + "group": "A", + "mode": "none" + }, + "thresholdsStyle": { + "mode": "off" + } + }, + "mappings": [], + "unit": "short", + "thresholds": { + "mode": "absolute", + "steps": [ + { + "color": "green", + "value": null + } + ] + } + }, + "overrides": [] + }, + "gridPos": { + "h": 8, + "w": 24, + "x": 0, + "y": 117 + }, + "id": 45, + "options": { + "legend": { + "calcs": [], + "displayMode": "list", + "placement": "bottom", + "showLegend": true + }, + "tooltip": { + "mode": "single", + "sort": "none" + } + }, + "pluginVersion": "11.0.0", + "targets": [ + { + "datasource": "postgresql", + "format": "table", + "rawQuery": true, + "rawSql": "SELECT \"date\" AS time, SUM(gross_quantity) AS \"Gross Credits\", SUM(net_quantity) AS \"Net Credits\" FROM _tool_copilot_ai_credit_usage WHERE $__timeFilter(\"date\") AND ('${connection_id}' = '' OR connection_id::text = '${connection_id}') AND scope_id = '${scope_id}' GROUP BY \"date\" ORDER BY 1 NULLS LAST", + "refId": "A" + } + ], + "title": "AI Credits Consumed Over Time", + "type": "timeseries" + }, + { + "datasource": "postgresql", + "description": "AI credit consumption broken down by model", + "fieldConfig": { + "defaults": { + "mappings": [], + "thresholds": { + "mode": "absolute", + "steps": [ + { + "color": "green", + "value": null + } + ] + } + }, + "overrides": [] + }, + "gridPos": { + "h": 8, + "w": 12, + "x": 0, + "y": 125 + }, + "id": 46, + "options": { + "cellHeight": "sm", + "footer": { + "countRows": false, + "fields": "", + "reducer": [ + "sum" + ], + "show": false + }, + "showHeader": true + }, + "pluginVersion": "11.0.0", + "targets": [ + { + "datasource": "postgresql", + "format": "table", + "rawQuery": true, + "rawSql": "SELECT model AS \"Model\", SUM(gross_quantity) AS \"Gross Credits\", SUM(net_quantity) AS \"Net Credits\", SUM(net_amount) AS \"Net Amount\" FROM _tool_copilot_ai_credit_usage WHERE $__timeFilter(\"date\") AND ('${connection_id}' = '' OR connection_id::text = '${connection_id}') AND scope_id = '${scope_id}' GROUP BY \"model\" ORDER BY SUM(gross_quantity) DESC NULLS LAST LIMIT 20", + "refId": "A" + } + ], + "title": "AI Credits by Model", + "type": "table" + }, + { + "datasource": "postgresql", + "description": "AI credit consumption broken down by product and SKU", + "fieldConfig": { + "defaults": { + "mappings": [], + "thresholds": { + "mode": "absolute", + "steps": [ + { + "color": "green", + "value": null + } + ] + } + }, + "overrides": [] + }, + "gridPos": { + "h": 8, + "w": 12, + "x": 12, + "y": 125 + }, + "id": 47, + "options": { + "cellHeight": "sm", + "footer": { + "countRows": false, + "fields": "", + "reducer": [ + "sum" + ], + "show": false + }, + "showHeader": true + }, + "pluginVersion": "11.0.0", + "targets": [ + { + "datasource": "postgresql", + "format": "table", + "rawQuery": true, + "rawSql": "SELECT product AS \"Product\", sku AS \"SKU\", SUM(gross_quantity) AS \"Gross Credits\", SUM(net_quantity) AS \"Net Credits\", SUM(net_amount) AS \"Net Amount\" FROM _tool_copilot_ai_credit_usage WHERE $__timeFilter(\"date\") AND ('${connection_id}' = '' OR connection_id::text = '${connection_id}') AND scope_id = '${scope_id}' GROUP BY \"product\", \"sku\" ORDER BY SUM(gross_quantity) DESC NULLS LAST LIMIT 20", + "refId": "A" + } + ], + "title": "AI Credits by Product / SKU", + "type": "table" } ], "refresh": "",
