Implement Automated Refresh Token Cleanup and Schema Optimization

[Harxhit]

Summary

Implement automated cleanup for revoked and expired refresh tokens, add database indexing to support efficient cleanup operations, and provide verification that the cleanup process works correctly.

Contexts

The authentication system now supports refresh token rotation, revocation, session tracking, and token families.

As users continue to authenticate and refresh sessions, revoked and expired refresh tokens will accumulate in the database. Without a cleanup mechanism, the refresh_tokens table will continue to grow unnecessarily, increasing storage usage and potentially impacting query performance.

Since cleanup operations will frequently query revoked and expired tokens, the schema should also be reviewed and optimized with appropriate indexes to ensure efficient lookups and deletion operations.

This work requires updates across the database schema, cleanup infrastructure, testing, and operational verification.

Tasks

• Implement a scheduled cleanup job for refresh tokens
• Remove revoked refresh tokens automatically
• Remove expired refresh tokens automatically
• Add logging for cleanup execution and deletion statistics
• Review the RefreshToken schema for cleanup-related query patterns
• Add or optimize indexes required for efficient cleanup operations
• Add automated tests covering cleanup behavior
• Verify that active refresh tokens are never removed
• Record a demo video showing successful cleanup of revoked and expired refresh tokens

Acceptance Criteria

• Revoked refresh tokens are automatically deleted
• Expired refresh tokens are automatically deleted
• Active refresh tokens remain untouched
• Cleanup executes on a scheduled interval
• Cleanup execution is logged
• Appropriate indexes exist for cleanup-related queries
• Tests validate cleanup behavior
• Demo video shows successful cleanup of revoked and expired refresh tokens
• No regression in authentication or refresh-token flows

Area

backend

Difficulty

Hard

[github-actions]

Hi @Harxhit,

Thanks for opening this issue.

Please reply with one of the following areas so the issue can be routed to the appropriate team member:

- /backend
- /web
- /mobile
- /devops

Once an area is selected, the corresponding label will be added automatically.

[Harxhit]

/backend

[github-actions]

The issue has been classified as backend.

@Harxhit, please review and triage this issue when available.

The backend label has been applied and the issue has been routed accordingly.

[udaycodespace]

Hi @Harxhit,

I came across this issue from the Discord post regarding the new authentication-related backend tasks, and I'd like to work on it.

I've handled a similar token-cleanup problem in a previous college project where we implemented scheduled cleanup jobs for expired sessions/tokens to prevent database growth and maintain query performance.

My initial approach would be:

1. Review the current RefreshToken schema and cleanup query patterns.
2. Add the required indexes for efficient revoked/expired token lookups.
3. Implement a scheduled cleanup job with logging and deletion metrics.
4. Add tests to ensure active tokens are never removed and validate cleanup behavior end-to-end.

If there are any project-specific considerations or if you'd like me to refine the approach before starting, I'm happy to discuss and iterate on it.

If the approach looks good, could you please assign this issue to me?

[ramnnn2006]

heyy @Harxhit / pa
i'd like to take this one up

soo , i went through the auth flow and here's my plan:

• schema: RefreshToken currently only has indexes on userId, family and the unique tokenHash. the cleanup query filters on expiresAt and revokedAt, so i guess i would add indexes on both and ship the migration with the PR.

• scheduler: a small fastify plugin following the existing prisma.ts plugin pattern. runs once on boot, then on an interval (default 24h, env configurable), cleaned up properly on shutdown and disabled in tests. no new dependencies needed. logs deleted count and duration per run.

• cleanup service: a deleteMany that removes tokens that are expired or revoked. one thing i noticed though, the refresh flow relies on revoked tokens for token-family reuse detection, so deleting them immediately would weaken that. i'd keep revoked tokens around for a short grace period (say 7 days, configurable) before purging. happy to adjust if you'd rather purge instantly.

• tests: vitest cases seeding active, revoked and expired tokens, asserting cleanup removes only the right ones and that active tokens are never touched, plus the logged stats.

could u assign this to me , thankyou!

[udaycodespace]

Hi @Harxhit,

I came across this issue from the Discord post regarding the new authentication-related backend tasks, and I'd like to work on it.

I've handled a similar token-cleanup problem in a previous college project where we implemented scheduled cleanup jobs for expired sessions/tokens to prevent database growth and maintain query performance.

My initial approach would be:

1. Review the current RefreshToken schema and cleanup query patterns.
2. Add the required indexes for efficient revoked/expired token lookups.
3. Implement a scheduled cleanup job with logging and deletion metrics.
4. Add tests to ensure active tokens are never removed and validate cleanup behavior end-to-end.

If there are any project-specific considerations or if you'd like me to refine the approach before starting, I'm happy to discuss and iterate on it.

If the approach looks good, could you please assign this issue to me?

Hi @Harxhit,

Following up on this issue. I'm still interested in working on it if it's available.

I recently worked on and merged PR #506, where I implemented GitHub platform auto-discovery, addressed review feedback, resolved CI issues, added tests, and completed the contribution through to merge.

For this issue, my plan would be to first review the existing refresh-token lifecycle, cleanup requirements, and schema/query patterns before proposing the final implementation details. Since this touches authentication, token rotation, and session management, I'd like to ensure the cleanup strategy aligns with the current security model and doesn't impact existing flows.

If the issue is still open, I'd appreciate being considered for assignment.

Thanks.

[Harxhit]

@udaycodespace I have assigned this issue to you.