🐛 Copilot: Chart X-axis and tooltips show "Invalid Date" when using PostgreSQL

[rumenv]

Workspace

copilot

📜 Description

All V2 dashboard charts (IDE Daily Active Users, IDE Weekly Active Users, Avg Chat Requests, etc.) display "Invalid Date" on the X-axis tick labels and in chart tooltips. The chart data itself renders correctly.

Root Cause
The backend route handler for /api/copilot/v2/metrics/daily (and other date-indexed endpoints) returns raw Knex query results directly:

// service/router.cjs.js
const result = await dbV2.getDailyTotals(type, entityId, from, to, team);
return res.json(result);

The day column is declared as table.date('day') in migrations. When using PostgreSQL, the pg driver deserializes date columns as JavaScript Date objects. When serialized to JSON, these become full ISO 8601 strings like "2026-05-26T00:00:00.000Z".

The frontend formatDay utility in chartUtils.esm.js then does:

function formatDay(day) {
  const date = new Date(`${day}T00:00:00`);  // → new Date("2026-05-26T00:00:00.000ZT00:00:00")
  return date.toLocaleDateString("en-US", { month: "short", day: "numeric" });
}

This produces Invalid Date because it appends T00:00:00 to a string that already contains a full timestamp.

Note: DatabaseHandlerV2 already has a normalizeDay() method that correctly handles this case (Date → "YYYY-MM-DD") — it's just not being called before the HTTP response is serialized.

Suggested Fix
In the route handler(s) that return rows with a day field, normalize the day before sending:

const result = await dbV2.getDailyTotals(type, entityId, from, to, team);
return res.json(result.map(row => ({ ...row, day: dbV2.normalizeDay(row.day) })));

Or alternatively, fix formatDay in the frontend to handle both "YYYY-MM-DD" and full ISO strings:

function formatDay(day) {
  const dateStr = typeof day === 'string' ? day.slice(0, 10) : day;
  const date = new Date(`${dateStr}T00:00:00`);
  return date.toLocaleDateString("en-US", { month: "short", day: "numeric" });
}

Steps to Reproduce

👍 Expected behavior

Chart X-axis and tooltips show formatted dates like "May 26".

👎 Actual Behavior with Screenshots

Chart X-axis and tooltips show "Invalid Date".

👟 Reproduction steps

Steps to Reproduce

1. Run Backstage with PostgreSQL (not SQLite)
2. Install @backstage-community/plugin-copilot-backend@1.0.0 and @backstage-community/plugin-copilot@1.1.0
3. Allow some data to be ingested
4. Open the Copilot Insights page → V2 Dashboard

📃 Provide the context for the Bug.

No response

👀 Have you spent some time to check if this bug has been raised before?

• I checked and didn't find similar issue

🏢 Have you read the Code of Conduct?

• I have read the Code of Conduct

Are you willing to submit PR?

Yes I am willing to submit a PR!