/* * Copyright 2024 The Backstage Authors * * Licensed 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. */ import { DatabaseService } from '@backstage/backend-plugin-api'; import { PeriodRange, V2DailyTotal, V2DashboardData, V2IngestionLogRow, V2MetricsByCliRow, V2MetricsByFeatureRow, V2MetricsByIdeRow, V2MetricsByLanguageFeatureRow, V2MetricsByModelFeatureRow, V2MetricsByLanguageModelRow, V2PrMetricsRow, V2UserMetricRow, V2UserTeamRow, MetricsScope, } from '@backstage-community/plugin-copilot-common'; import { DateTime } from 'luxon'; import { Knex } from 'knex'; import { migrationsDir } from './DatabaseHandler'; import { batchInsertInChunks } from '../utils/batchInsert'; type Options = { database: DatabaseService; }; export class DatabaseHandlerV2 { static async create(options: Options): Promise { const { database } = options; const client = await database.getClient(); if (!database.migrations?.skip) { await client.migrate.latest({ directory: migrationsDir }); } return new DatabaseHandlerV2(client); } private constructor(private readonly db: Knex) {} async insertDailyTotals(rows: V2DailyTotal[]): Promise { if (!rows.length) { return; } await batchInsertInChunks(rows, 100, async chunk => { await this.db('copilot_daily_totals') .insert(chunk) .onConflict(['day', 'metrics_type', 'entity_id', 'team_slug']) .ignore(); }); } async insertPrMetrics(rows: V2PrMetricsRow[]): Promise { if (!rows.length) { return; } await batchInsertInChunks(rows, 100, async chunk => { await this.db('copilot_pr_metrics') .insert(chunk) .onConflict(['day', 'metrics_type', 'entity_id', 'team_slug']) .ignore(); }); } async insertByFeature(rows: V2MetricsByFeatureRow[]): Promise { if (!rows.length) { return; } await batchInsertInChunks(rows, 100, async chunk => { await this.db('copilot_metrics_by_feature') .insert(chunk) .onConflict([ 'day', 'metrics_type', 'entity_id', 'team_slug', 'feature', ]) .ignore(); }); } async insertByIde(rows: V2MetricsByIdeRow[]): Promise { if (!rows.length) { return; } await batchInsertInChunks(rows, 100, async chunk => { await this.db('copilot_metrics_by_ide') .insert(chunk) .onConflict(['day', 'metrics_type', 'entity_id', 'team_slug', 'ide']) .ignore(); }); } async insertByLanguageFeature( rows: V2MetricsByLanguageFeatureRow[], ): Promise { if (!rows.length) { return; } await batchInsertInChunks(rows, 100, async chunk => { await this.db( 'copilot_metrics_by_language_feature', ) .insert(chunk) .onConflict([ 'day', 'metrics_type', 'entity_id', 'team_slug', 'language', 'feature', ]) .ignore(); }); } async insertByModelFeature( rows: V2MetricsByModelFeatureRow[], ): Promise { if (!rows.length) return; await batchInsertInChunks(rows, 100, async chunk => { await this.db( 'copilot_metrics_by_model_feature', ) .insert(chunk) .onConflict([ 'day', 'metrics_type', 'entity_id', 'team_slug', 'model_id', 'feature', ]) .ignore(); }); } async insertByLanguageModel( rows: V2MetricsByLanguageModelRow[], ): Promise { if (!rows.length) return; await batchInsertInChunks(rows, 100, async chunk => { await this.db( 'copilot_metrics_by_language_model', ) .insert(chunk) .onConflict([ 'day', 'metrics_type', 'entity_id', 'team_slug', 'language', 'model_id', ]) .ignore(); }); } async insertByCli(rows: V2MetricsByCliRow[]): Promise { if (!rows.length) return; await batchInsertInChunks(rows, 100, async chunk => { await this.db('copilot_metrics_by_cli') .insert(chunk) .onConflict(['day', 'metrics_type', 'entity_id', 'team_slug']) .ignore(); }); } async insertUserMetrics(rows: V2UserMetricRow[]): Promise { if (!rows.length) { return; } await batchInsertInChunks(rows, 100, async chunk => { await this.db('copilot_user_metrics') .insert(chunk) .onConflict(['day', 'metrics_type', 'entity_id', 'user_id']) .ignore(); }); } async insertUserTeams(rows: V2UserTeamRow[]): Promise { if (!rows.length) { return; } await batchInsertInChunks(rows, 100, async chunk => { await this.db('copilot_user_teams') .insert(chunk) .onConflict(['day', 'metrics_type', 'entity_id', 'user_id', 'team_id']) .ignore(); }); } async upsertIngestionLog(row: V2IngestionLogRow): Promise { await this.db('copilot_ingestion_log') .insert(row) .onConflict(['day', 'metrics_type', 'entity_id']) .merge(); } async getMissingDays( metricsType: MetricsScope, entityId: string, from: string, to: string, requiredComponents: string[] = ['totals'], ): Promise { const successfulRows = await this.db('copilot_ingestion_log') .where('metrics_type', metricsType) .where('entity_id', entityId) .where('status', 'success') .whereBetween('day', [from, to]) .select('day', 'components_loaded'); const successfulDays = new Set( successfulRows .filter(row => this.hasRequiredComponents(row.components_loaded, requiredComponents), ) .map(row => this.normalizeDay(row.day)) .filter((day): day is string => Boolean(day)), ); const start = DateTime.fromISO(from, { zone: 'utc' }).startOf('day'); const end = DateTime.fromISO(to, { zone: 'utc' }).startOf('day'); if (!start.isValid || !end.isValid || start > end) { return []; } const missingDays: string[] = []; for (let cursor = start; cursor <= end; cursor = cursor.plus({ days: 1 })) { const day = cursor.toISODate(); if (day && !successfulDays.has(day)) { missingDays.push(day); } } return missingDays; } async getLastIngestedDay( metricsType: MetricsScope, entityId: string, ): Promise { const row = await this.db('copilot_ingestion_log') .where('metrics_type', metricsType) .where('entity_id', entityId) .where('status', 'success') .orderBy('day', 'desc') .first('day'); return this.normalizeDay(row?.day); } async getIngestionLog( metricsType: MetricsScope, entityId: string, from?: string, to?: string, ): Promise { const query = this.db('copilot_ingestion_log') .where('metrics_type', metricsType) .where('entity_id', entityId); if (from && to) { query.whereBetween('day', [from, to]); } else if (from) { query.where('day', '>=', from); } else if (to) { query.where('day', '<=', to); } return query.orderBy('day', 'asc').select('*'); } async getDailyTotals( metricsType: MetricsScope, entityId: string, from: string, to: string, teamSlug?: string, ): Promise { const query = this.db('copilot_daily_totals') .where('metrics_type', metricsType) .where('entity_id', entityId) .whereBetween('day', [from, to]); query.where('team_slug', teamSlug ?? ''); return query.orderBy('day', 'asc').select('*'); } async getPrMetrics( metricsType: MetricsScope, entityId: string, from: string, to: string, teamSlug?: string, ): Promise { const query = this.db('copilot_pr_metrics') .where('metrics_type', metricsType) .where('entity_id', entityId) .whereBetween('day', [from, to]); query.where('team_slug', teamSlug ?? ''); return query.orderBy('day', 'asc').select('*'); } async getByFeature( metricsType: MetricsScope, entityId: string, from: string, to: string, teamSlug?: string, ): Promise { const query = this.db('copilot_metrics_by_feature') .where('metrics_type', metricsType) .where('entity_id', entityId) .whereBetween('day', [from, to]); query.where('team_slug', teamSlug ?? ''); return query.orderBy('day', 'asc').select('*'); } async getByIde( metricsType: MetricsScope, entityId: string, from: string, to: string, teamSlug?: string, ): Promise { const query = this.db('copilot_metrics_by_ide') .where('metrics_type', metricsType) .where('entity_id', entityId) .whereBetween('day', [from, to]); query.where('team_slug', teamSlug ?? ''); return query.orderBy('day', 'asc').select('*'); } async getByLanguageFeature( metricsType: MetricsScope, entityId: string, from: string, to: string, teamSlug?: string, feature?: string, ): Promise { const query = this.db( 'copilot_metrics_by_language_feature', ) .where('metrics_type', metricsType) .where('entity_id', entityId) .whereBetween('day', [from, to]); query.where('team_slug', teamSlug ?? ''); if (feature !== undefined) { query.where('feature', feature); } return query.orderBy('day', 'asc').select('*'); } async getByModelFeature( metricsType: MetricsScope, entityId: string, from: string, to: string, teamSlug?: string, ): Promise { const query = this.db( 'copilot_metrics_by_model_feature', ) .where('metrics_type', metricsType) .where('entity_id', entityId) .whereBetween('day', [from, to]); query.where('team_slug', teamSlug ?? ''); return query.orderBy('day', 'asc').select('*'); } async getByLanguageModel( metricsType: MetricsScope, entityId: string, from: string, to: string, teamSlug?: string, ): Promise { const query = this.db( 'copilot_metrics_by_language_model', ) .where('metrics_type', metricsType) .where('entity_id', entityId) .whereBetween('day', [from, to]); query.where('team_slug', teamSlug ?? ''); return query.orderBy('day', 'asc').select('*'); } async getDashboardData( metricsType: MetricsScope, entityId: string, from: string, to: string, teamSlug?: string, ): Promise { const [ daily, byFeature, byLanguage, byModelFeature, byLanguageModel, prMetrics, ] = await Promise.all([ this.getDailyTotals(metricsType, entityId, from, to, teamSlug), this.getByFeature(metricsType, entityId, from, to, teamSlug), this.getByLanguageFeature(metricsType, entityId, from, to, teamSlug), this.getByModelFeature(metricsType, entityId, from, to, teamSlug), this.getByLanguageModel(metricsType, entityId, from, to, teamSlug), this.getPrMetrics(metricsType, entityId, from, to, teamSlug), ]); return { daily, byFeature, byLanguage, byModelFeature, byLanguageModel, prMetrics, }; } async getTeams( metricsType: MetricsScope, entityId: string, from?: string, to?: string, minMembers: number = 5, ): Promise { const query = this.db('copilot_user_teams') .where('metrics_type', metricsType) .where('entity_id', entityId) .whereNot('team_slug', ''); if (from && to) { query.whereBetween('day', [from, to]); } else if (from) { query.where('day', '>=', from); } else if (to) { query.where('day', '<=', to); } const rows = await query .groupBy('team_slug') .havingRaw('COUNT(DISTINCT ??) >= ?', ['user_id', minMembers]) .orderBy('team_slug', 'asc') .select('team_slug'); return rows.map(row => row.team_slug); } async getPeriodRange( metricsType: MetricsScope, entityId: string, ): Promise { const row = await this.db('copilot_daily_totals') .where('metrics_type', metricsType) .where('entity_id', entityId) .select( this.db.raw('MIN(day) as min_day'), this.db.raw('MAX(day) as max_day'), ) .first(); const minDate = this.normalizeDay(row?.min_day); const maxDate = this.normalizeDay(row?.max_day); if (!minDate || !maxDate) { return null; } return { minDate, maxDate }; } private normalizeDay(value: unknown): string | null { if (!value) { return null; } if (value instanceof Date) { return DateTime.fromJSDate(value, { zone: 'utc' }).toISODate(); } if (typeof value === 'string') { const datePrefix = /^\d{4}-\d{2}-\d{2}/.exec(value)?.[0]; if (datePrefix) { return datePrefix; } const parsed = DateTime.fromISO(value, { zone: 'utc' }); if (parsed.isValid) { return parsed.toISODate(); } } return null; } private hasRequiredComponents( value: unknown, requiredComponents: string[], ): boolean { const loadedComponents = new Set(this.parseComponentsLoaded(value)); return requiredComponents.every(component => loadedComponents.has(component), ); } private parseComponentsLoaded(value: unknown): string[] { if (!value) { return []; } if (Array.isArray(value)) { return value.filter( (entry): entry is string => typeof entry === 'string', ); } if (typeof value !== 'string') { return []; } try { const parsed = JSON.parse(value); return Array.isArray(parsed) ? parsed.filter((entry): entry is string => typeof entry === 'string') : []; } catch { return []; } } }