Bug: Expired tokens not being deleted with .where('expires_at', '<=', new Date())

[SOG-web]

Description

In the JoseJwtSessionManager class, there's an issue with the cleanup of expired refresh tokens. The query to delete expired tokens is not working properly:

await this.knex(AuthRefreshTokensTable)
  .where('expires_at', '<=', new Date())
  .delete();

This query is not successfully deleting expired tokens, likely due to issues with how JavaScript's Date objects are being converted to the database's date format.

Location

File: libs/auth/src/session/jose-jwt.ts
Lines: 152-158, 248-253, 285-290

Expected Behavior

Expired tokens should be deleted from the database when they expire.

Current Behavior

Expired tokens are not being deleted from the database, causing token accumulation over time.

Possible Fixes

1. Use a raw SQL query with proper date formatting:

await this.knex.raw(`DELETE FROM ${AuthRefreshTokensTable} WHERE expires_at <= ?`, [new Date()]);

2. Use Knex's date formatting functions:

await this.knex(AuthRefreshTokensTable)
  .where('expires_at', '<=', this.knex.fn.now())
  .delete();

3. Format the date explicitly before using it in the query:

const now = new Date();
await this.knex(AuthRefreshTokensTable)
  .where('expires_at', '<=', now.toISOString())
  .delete();

Impact

This issue can lead to database bloat as expired tokens accumulate over time. It may also impact performance of token-related operations and potentially cause memory issues if the table grows too large.

[SOG-web]

I've implemented a fix for this issue by replacing all instances of new Date() with this.knex.fn.now() in the date comparison queries. This should resolve the issue because:

1. this.knex.fn.now() is database-agnostic and will generate the correct SQL function for the current database system (e.g., NOW() for MySQL/PostgreSQL, CURRENT_TIMESTAMP for SQLite)

2. It ensures that the date comparison happens at the database level rather than in JavaScript, avoiding any potential date format conversion issues

3. It's more efficient as the database can use its native date functions for the comparison

The changes have been made in all four locations where expired tokens are cleaned up:

1. In the refreshSession method (lines 152-158)
2. In the verifySession method when handling token reuse (lines 208-215)
3. In the verifySession method's catch block (lines 247-253)
4. In the destroySession method (lines 284-290)

This should ensure that expired tokens are properly deleted in all scenarios.