feat: add cron job to cleanup old queued form responses #22035

devin-ai-integration · 2025-06-25T14:26:48Z

Add cron job to cleanup old queued form responses

Fixes: PRI-289

Summary

This PR adds a cleanup cron job for the App_RoutingForms_QueuedFormResponse table to automatically delete old records that:

Have actualResponseId as null
Are older than 1 week

Changes Made

New cron job:
Created /apps/web/app/api/cron/queuedFormResponseCleanup/route.ts
Deletion logic:
Deletes records where:
- actualResponseId is null, and
- createdAt is older than 1 week
Updated vercel.json:
- Added cron schedule to run twice daily (every 12 hours)
- Schedule: 0 */12 * * * (runs at midnight and noon)
- Path: /api/cron/queuedFormResponseCleanup

Link to Devin Run

app.devin.ai/sessions/ae393cc887ed4532926a0bc1b0354dfa

Requested by: hariom@cal.com

- Add cleanup cron job for App_RoutingForms_QueuedFormResponse table - Delete records with null actualResponseId older than 1 hour - Schedule to run twice daily (every 12 hours) via vercel.json - Follow existing cron job patterns for authentication and structure Co-Authored-By: hariom@cal.com <hariom@cal.com>

devin-ai-integration · 2025-06-25T14:26:50Z

🤖 Devin AI Engineer

I'll be helping with this pull request! Here's what you should know:

✅ I will automatically:

Address comments on this PR that start with 'DevinAI'.
Look at CI failures and help fix them

Note: I can only respond to comments from users who have write access to this repository.

⚙️ Control Options:

Disable automatic comment and CI monitoring

vercel · 2025-06-25T14:27:10Z

The latest updates on your projects. Learn more about Vercel for Git ↗︎

2 Skipped Deployments

Name	Status	Preview	Comments	Updated (UTC)
cal	⬜️ Ignored (Inspect)			Jul 21, 2025 0:20am
cal-eu	⬜️ Ignored (Inspect)	Visit Preview		Jul 21, 2025 0:20am

delve-auditor · 2025-06-25T14:29:14Z

✅ No security or compliance issues detected. Reviewed everything up to 9927b29.

Security Overview

🔎 Scanned files: 8 changed file(s)

Detected Code Changes

The diff is too large to display a summary of code changes.

Reply to this PR with @delve-auditor followed by a description of what change you want and we'll auto-submit a change to this PR to implement it.

- Change from lte to gte in createdAt comparison - Now properly deletes records older than 1 hour Co-Authored-By: hariom@cal.com <hariom@cal.com>

devin-ai-integration · 2025-07-04T17:03:11Z

Closing due to inactivity for more than 7 days. Configure here.

…ndpoint - Create cleanupExpiredQueuedFormResponses function in routing forms lib - Update API endpoint to import and use the new cleanup function - Follow established patterns for code organization in Cal.com - Maintain same functionality and authentication logic Co-Authored-By: hariom@cal.com <hariom@cal.com>

- Create routing-forms/cron/queuedFormResponseCleanup.ts with complete handler - Move authentication, HTTP handling, and database operations to routing-forms - Update API endpoint to simply import and call the cron handler - Remove old lib cleanup function - Follow user's preferred organizational pattern Co-Authored-By: hariom@cal.com <hariom@cal.com>

github-actions · 2025-07-12T08:02:40Z

E2E results are ready!

Workflow #68843.1 latest results

- Change from gte (greater than or equal) to lt (less than) cutoffTime - Now properly deletes records where createdAt < cutoffTime (older than 1 hour) - Previous logic was deleting newer records instead of older ones Co-Authored-By: hariom@cal.com <hariom@cal.com>

linear · 2025-07-18T03:48:50Z

PRI-289

…nup-queued-form-responses

coderabbitai · 2025-07-18T08:03:31Z

Important

Review skipped

Bot user detected.

To trigger a single review, invoke the @coderabbitai review command.

You can disable this status message by setting the reviews.review_status to false in the CodeRabbit configuration file.

🪧 Tips

Chat

There are 3 ways to chat with CodeRabbit:

Review comments: Directly reply to a review comment made by CodeRabbit. Example:
- I pushed a fix in commit <commit_id>, please review it.
- Explain this complex logic.
- Open a follow-up GitHub issue for this discussion.
Files and specific lines of code (under the "Files changed" tab): Tag @coderabbitai in a new review comment at the desired location with your query. Examples:
- @coderabbitai explain this code block.
- @coderabbitai modularize this function.
PR comments: Tag @coderabbitai in a new PR comment to ask questions about the PR branch. For the best results, please provide a very specific query, as very limited context is provided in this mode. Examples:
- @coderabbitai gather interesting stats about this repository and render them as a table. Additionally, render a pie chart showing the language distribution in the codebase.
- @coderabbitai read src/utils.ts and explain its main purpose.
- @coderabbitai read the files in the src/scheduler package and generate a class diagram using mermaid and a README in the markdown format.
- @coderabbitai help me debug CodeRabbit configuration file.

Support

Need help? Create a ticket on our support page for assistance with any issues or questions.

Note: Be mindful of the bot's finite context window. It's strongly recommended to break down tasks such as reading entire modules into smaller chunks. For a focused discussion, use review comments to chat about specific files and their changes, instead of using the PR comments.

CodeRabbit Commands (Invoked using PR comments)

@coderabbitai pause to pause the reviews on a PR.
@coderabbitai resume to resume the paused reviews.
@coderabbitai review to trigger an incremental review. This is useful when automatic reviews are disabled for the repository.
@coderabbitai full review to do a full review from scratch and review all the files again.
@coderabbitai summary to regenerate the summary of the PR.
@coderabbitai generate docstrings to generate docstrings for this PR.
@coderabbitai generate sequence diagram to generate a sequence diagram of the changes in this PR.
@coderabbitai resolve resolve all the CodeRabbit review comments.
@coderabbitai configuration to show the current CodeRabbit configuration for the repository.
@coderabbitai help to get help.

Other keywords and placeholders

Add @coderabbitai ignore anywhere in the PR description to prevent this PR from being reviewed.
Add @coderabbitai summary to generate the high-level summary at a specific location in the PR description.
Add @coderabbitai anywhere in the PR title to generate the title automatically.

CodeRabbit Configuration File (`.coderabbit.yaml`)

You can programmatically configure CodeRabbit by adding a .coderabbit.yaml file to the root of your repository.
Please see the configuration documentation for more information.
If your editor has YAML language server enabled, you can add the path at the top of this file to enable auto-completion and validation: # yaml-language-server: $schema=https://coderabbit.ai/integrations/schema.v2.json

Documentation and Community

Visit our Documentation for detailed information on how to use CodeRabbit.
Join our Discord Community to get help, request features, and share feedback.
Follow us on X/Twitter for updates and announcements.

- Move repository and service files to routingForm/queuedFormResponse subdirectory - Clean up unused methods and tests for better maintainability - Fix import paths and type definitions - Ensure all tests pass and maintain backward compatibility

- Update actualResponseId type from string to number to match Prisma Int type - Make updatedAt nullable (Date | null) to match Prisma DateTime? type - Fix import path for QueuedFormResponseRepository interface Co-Authored-By: hariom@cal.com <hariombalhara@gmail.com>

hariombalhara · 2025-07-18T11:28:31Z

@coderabbitai Review the PR

coderabbitai · 2025-07-18T11:30:39Z

Tip

For best results, initiate chat on the files or code changes.

🧩 Analysis chain

I'll review this PR for you. Let me start by gathering information about the changes and examining the code.

🏁 Script executed:

#!/bin/bash
# Get PR information and changed files
echo "=== PR Information ==="
gh pr view 22035 --json title,body,state,author,createdAt,updatedAt,additions,deletions,changedFiles

echo -e "\n=== Changed Files ==="
gh pr view 22035 --json files --jq '.files[] | {path: .path, additions: .additions, deletions: .deletions}'

echo -e "\n=== PR Diff Summary ==="
gh pr diff 22035 --name-only

Length of output: 3283

Now let me examine the specific code changes to provide a thorough review:

🏁 Script executed:

#!/bin/bash
# Get the actual diff content for each file
echo "=== API Route (Cron Endpoint) ==="
gh pr diff 22035 -- apps/web/app/api/cron/queuedFormResponseCleanup/route.ts

echo -e "\n=== Vercel Configuration ==="
gh pr diff 22035 -- apps/web/vercel.json

echo -e "\n=== Main Cron Logic ==="
gh pr diff 22035 -- packages/app-store/routing-forms/cron/queuedFormResponseCleanup.ts

echo -e "\n=== Repository Implementation ==="
gh pr diff 22035 -- packages/lib/server/repository/routingForm/queuedFormResponse/PrismaQueuedFormResponseRepository.ts

Length of output: 776

Let me fix the command syntax to get the file contents:

🏁 Script executed:

#!/bin/bash
# Get the actual diff content for each file
echo "=== API Route (Cron Endpoint) ==="
gh pr diff 22035 apps/web/app/api/cron/queuedFormResponseCleanup/route.ts

echo -e "\n=== Vercel Configuration ==="
gh pr diff 22035 apps/web/vercel.json

echo -e "\n=== Main Cron Logic ==="
gh pr diff 22035 packages/app-store/routing-forms/cron/queuedFormResponseCleanup.ts

echo -e "\n=== Repository Implementation ==="
gh pr diff 22035 packages/lib/server/repository/routingForm/queuedFormResponse/PrismaQueuedFormResponseRepository.ts

Length of output: 764

Let me try a different approach to get the file contents:

🏁 Script executed:

#!/bin/bash
# Get the full diff for the PR
echo "=== Full PR Diff ==="
gh pr diff 22035

echo -e "\n=== Alternative: Get individual file contents ==="
# Try reading the files directly from the repository
cat apps/web/app/api/cron/queuedFormResponseCleanup/route.ts 2>/dev/null || echo "File not found in current branch"

Length of output: 18151

@hariombalhara Thank you for asking me to review this PR! I've analyzed the code changes and here's my comprehensive review:

✅ Strengths

Architecture & Design:

Excellent use of clean architecture principles with proper separation of concerns
Repository pattern implementation with dependency injection
Service layer abstraction with well-defined interfaces
Comprehensive test coverage (96 lines for repository, 159 lines for service)

Code Quality:

Strong TypeScript typing with proper interfaces
Proper error handling and logging throughout
Batch processing implementation to avoid database overload
Authentication protection for the cron endpoint

⚠️ Issues & Concerns

1. Critical Discrepancy - Cleanup Duration

// PR Description says "1 week" but implementation uses "1 hour"
private getExpiryCutoffTime(): Date {
  const olderThanHours = 1; // ❌ This is 1 hour, not 1 week
  return new Date(Date.now() - olderThanHours * 60 * 60 * 1000);
}

Impact: This could delete records much sooner than intended, potentially affecting user experience.

2. Inconsistent Cron Schedule

// vercel.json - runs every 12 hours
"schedule": "0 */12 * * *"

If cleanup is for 1-hour-old records, running every 12 hours seems inefficient. Consider:

If keeping 1-hour cleanup: Run more frequently (e.g., hourly)
If keeping 12-hour schedule: Increase cleanup duration to match

3. Hardcoded Values

// Should be configurable
const olderThanHours = 1;
const batchSize = 1000; // default
await new Promise((resolve) => setTimeout(resolve, 100)); // delay

🔧 Recommendations

1. Fix Duration Consistency

// Option A: Match PR description (1 week)
private getExpiryCutoffTime(): Date {
  const olderThanHours = 7 * 24; // 1 week
  return new Date(Date.now() - olderThanHours * 60 * 60 * 1000);
}

// Option B: Make it configurable
interface CleanupConfig {
  batchSize?: number;
  olderThanHours?: number; // Add this
}

2. Environment Configuration

// Make values configurable via environment variables
const CLEANUP_OLDER_THAN_HOURS = process.env.QUEUED_FORM_CLEANUP_HOURS || "168"; // 1 week default
const DEFAULT_BATCH_SIZE = process.env.QUEUED_FORM_CLEANUP_BATCH_SIZE || "1000";

3. Improve Error Handling

// In the cron handler
} catch (error) {
  logger.error("Error during queued form response cleanup:", error);
  // Add more specific error handling
  return NextResponse.json(
    { status: "error", message: "Failed to cleanup queued form responses", error: error.message },
    { status: 500 }
  );
}

📋 Minor Issues

Test Import Path: The test imports could be more explicit
Missing JSDoc: Consider adding JSDoc comments for public methods
Prisma Transaction: For large deletions, consider using transactions

🎯 Overall Assessment

This is a well-structured PR that follows good engineering practices. The main concern is the duration discrepancy that needs to be resolved. Once that's fixed, this would be a solid addition to the codebase.

Rating: 7.5/10 - Good architecture and implementation, but needs the duration issue resolved.

Recommended Actions:

Fix the cleanup duration to match PR description or update the description
Adjust cron schedule to match cleanup duration
Consider making configuration values environment-driven

Would you like me to help create a follow-up issue for any of these improvements, or would you prefer to address them in this PR?

- Move repository and service files to routingForm/queuedFormResponse subdirectory - Clean up unused methods and tests for better maintainability - Fix import paths and type definitions - Ensure all tests pass and maintain backward compatibility

…t coverage

hariombalhara · 2025-07-18T12:12:47Z

@coderabbitai recheck the duration issue has been fixed

coderabbitai · 2025-07-18T12:13:48Z

Tip

For best results, initiate chat on the files or code changes.

🧩 Analysis chain

🏁 Script executed:

#!/bin/bash
# Check the current implementation of the cleanup duration in the service file
echo "=== Checking current cleanup duration implementation ==="
cat packages/app-store/routing-forms/cron/queuedFormResponseCleanup.ts | grep -A 10 -B 5 "getExpiryCutoffTime\|olderThanHours"

echo -e "\n=== Also checking the service file for any duration-related code ==="
cat packages/app-store/routing-forms/lib/QueuedFormResponseService.ts | grep -A 10 -B 5 "getExpiryCutoffTime\|olderThanHours\|cleanup\|expiry"

Length of output: 647

🏁 Script executed:

#!/bin/bash
# Let me first find the correct file paths and then examine the duration implementation
echo "=== Finding files related to queued form response cleanup ==="
fd -t f -e ts "queued.*form.*response" | head -20

echo -e "\n=== Finding files that contain QueuedFormResponseService ==="
rg -l "QueuedFormResponseService" --type ts

echo -e "\n=== Let me check the main cron file directly ==="
cat packages/app-store/routing-forms/cron/queuedFormResponseCleanup.ts

echo -e "\n=== Search for duration-related code in all relevant files ==="
rg -A 5 -B 5 "olderThanHours|getExpiryCutoffTime|cleanup.*duration" --type ts

Length of output: 5769

@hariombalhara Perfect! ✅ I can confirm that the duration issue has been completely resolved.

🎯 Issue Resolution Confirmed

Looking at the current implementation in QueuedFormResponseService.ts:

private getExpiryCutoffTime(): Date {
  const olderThanDays = 7; // ✅ Now correctly uses 7 days (1 week)
  return new Date(Date.now() - olderThanDays * 24 * 60 * 60 * 1000);
}

What's been fixed:

✅ Duration: Changed from 1 hour to 7 days (1 week) - now matches PR description
✅ Variable naming: Changed from olderThanHours to olderThanDays - more semantically correct
✅ Calculation: Properly calculates 7 days worth of milliseconds

📈 Updated Assessment

With this critical issue resolved, my rating for this PR increases significantly:

New Rating: 9/10 - Excellent implementation with proper architecture, comprehensive testing, and now consistent duration handling.

The cron schedule of every 12 hours (in vercel.json) now makes perfect sense for cleaning up week-old records. This is a well-designed cleanup mechanism that will effectively maintain database hygiene without being overly aggressive.

Great job on addressing the feedback! 🐰

graphite-app · 2025-07-18T12:15:11Z

Graphite Automations

"Add consumer team as reviewer" took an action on this PR • (07/18/25)

1 reviewer was added to this PR based on Keith Williams's automation.

joeauyeung

LGTM just have one non-blocking comment

joeauyeung · 2025-07-18T13:59:47Z

packages/lib/server/service/routingForm/queuedFormResponse/QueuedFormResponseService.ts

+      where: {
+        actualResponseId: null,
+        createdAt: {
+          lt: cutoffTime,
+        },
+      },
+      params: {
+        take: batchSize,
+      },


It feels like this Prisma specific where statement should live inside the PrismaQueuedFormResponseRepository. If it's outside of that repository then we're inherently creating a dependency on Prisma.

@emrysal what are your thoughts on this?

@joeauyeung I didn't choose to do it because I am not sure if we have an example of an interface that is capable of defining complex AND or Logic.

So I though maybe we could use this prisma like interface itself as the generic interface. If you would notice we aren't importing Prisma in here https://github.com/calcom/cal.com/blob/devin/1750861124-cleanup-queued-form-responses/packages/lib/server/repository/routingForm/queuedFormResponse/QueuedFormResponseRepository.interface.ts#L0-L1

I will start a thread to discuss what approach should we take

joeauyeung

@hariombalhara actually talking with @emrysal these type of comments should be blocking as we want to progress our code

#22035 (comment)

packages/app-store/routing-forms/cron/queuedFormResponseCleanup.ts

.../server/repository/routingForm/queuedFormResponse/PrismaQueuedFormResponseRepository.test.ts

…ration tests

We are yet to finalize how to decouple prisma type from repository. I think for now having an interface that uses types similar to prisma but don't import prisma could work

alishaz-polymath

LGTM 👏

fix: correct date comparison logic to delete older records

3480088

- Change from lte to gte in createdAt comparison - Now properly deletes records older than 1 hour Co-Authored-By: hariom@cal.com <hariom@cal.com>

vercel bot deployed to Preview – dev June 25, 2025 15:48 View deployment

devin-ai-integration bot closed this Jul 4, 2025

hariombalhara reopened this Jul 12, 2025

vercel bot temporarily deployed to Preview – api July 12, 2025 06:31 Inactive

vercel bot deployed to Preview – dev July 12, 2025 06:56 View deployment

devin-ai-integration bot added the ready-for-e2e label Jul 12, 2025

devin-ai-integration bot and others added 2 commits July 12, 2025 07:17

vercel bot deployed to Preview – dev July 12, 2025 07:59 View deployment

vercel bot temporarily deployed to Preview – api July 12, 2025 08:28 Inactive

vercel bot temporarily deployed to Preview – cal July 12, 2025 08:28 Inactive

vercel bot deployed to Preview – dev July 12, 2025 08:48 View deployment

hariombalhara added 2 commits July 18, 2025 09:37

Merge remote-tracking branch 'origin/main' into devin/1750861124-clea…

3eaedc6

…nup-queued-form-responses

fixes

6197332

vercel bot deployed to Preview – dev July 18, 2025 08:23 View deployment

hariombalhara and others added 2 commits July 18, 2025 16:05

vercel bot deployed to Preview – dev July 18, 2025 11:53 View deployment

hariombalhara added 2 commits July 18, 2025 17:40

test: add comprehensive where clause validation tests and missing tes…

fce772d

…t coverage

hariombalhara marked this pull request as ready for review July 18, 2025 12:11

graphite-app bot requested a review from a team July 18, 2025 12:12

dosubot bot added routing-forms area: routing forms, routing, forms ✨ feature New feature or request labels Jul 18, 2025

hariombalhara added 2 commits July 18, 2025 17:45

chore: update cron schedule to weekly for 7-day retention period

27d10b9

revert: restore 12-hour cron schedule for better batch management

4ed448d

vercel bot deployed to Preview – dev July 18, 2025 12:46 View deployment

joeauyeung approved these changes Jul 18, 2025

View reviewed changes

joeauyeung previously requested changes Jul 18, 2025

View reviewed changes

github-actions bot marked this pull request as draft July 18, 2025 14:10

keithwillcode reviewed Jul 18, 2025

View reviewed changes

packages/app-store/routing-forms/cron/queuedFormResponseCleanup.ts Show resolved Hide resolved

keithwillcode reviewed Jul 18, 2025

View reviewed changes

.../server/repository/routingForm/queuedFormResponse/PrismaQueuedFormResponseRepository.test.ts Outdated Show resolved Hide resolved

Add integration tests

7ea0882

vercel bot deployed to Preview – dev July 21, 2025 12:08 View deployment

refactor: apply test data builder pattern to queuedFormResponse integ…

9927b29

…ration tests

vercel bot deployed to Preview – dev July 21, 2025 12:36 View deployment

hariombalhara requested a review from joeauyeung July 21, 2025 12:36

hariombalhara marked this pull request as ready for review July 23, 2025 10:14

alishaz-polymath approved these changes Aug 5, 2025

View reviewed changes

hariombalhara merged commit a71d949 into main Aug 5, 2025
45 checks passed

hariombalhara deleted the devin/1750861124-cleanup-queued-form-responses branch August 5, 2025 16:47

feat: add cron job to cleanup old queued form responses #22035

feat: add cron job to cleanup old queued form responses #22035

Uh oh!

Conversation

devin-ai-integration bot commented Jun 25, 2025 • edited by hariombalhara Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Add cron job to cleanup old queued form responses

Summary

Changes Made

Link to Devin Run

Uh oh!

devin-ai-integration bot commented Jun 25, 2025

🤖 Devin AI Engineer

Uh oh!

vercel bot commented Jun 25, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

delve-auditor bot commented Jun 25, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

devin-ai-integration bot commented Jul 4, 2025

Uh oh!

github-actions bot commented Jul 12, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

E2E results are ready!

Uh oh!

linear bot commented Jul 18, 2025

Uh oh!

coderabbitai bot commented Jul 18, 2025

Review skipped

Chat

Support

CodeRabbit Commands (Invoked using PR comments)

Other keywords and placeholders

CodeRabbit Configuration File (.coderabbit.yaml)

Documentation and Community

Uh oh!

hariombalhara commented Jul 18, 2025

Uh oh!

coderabbitai bot commented Jul 18, 2025

✅ Strengths

⚠️ Issues & Concerns

1. Critical Discrepancy - Cleanup Duration

2. Inconsistent Cron Schedule

3. Hardcoded Values

🔧 Recommendations

1. Fix Duration Consistency

2. Environment Configuration

3. Improve Error Handling

📋 Minor Issues

🎯 Overall Assessment

Uh oh!

hariombalhara commented Jul 18, 2025

Uh oh!

coderabbitai bot commented Jul 18, 2025

🎯 Issue Resolution Confirmed

📈 Updated Assessment

Uh oh!

graphite-app bot commented Jul 18, 2025

Graphite Automations

Uh oh!

joeauyeung left a comment

Choose a reason for hiding this comment

Uh oh!

joeauyeung Jul 18, 2025

Choose a reason for hiding this comment

Uh oh!

hariombalhara Jul 21, 2025

Choose a reason for hiding this comment

Uh oh!

joeauyeung left a comment • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

alishaz-polymath left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

devin-ai-integration bot commented Jun 25, 2025 •

edited by hariombalhara

Loading

vercel bot commented Jun 25, 2025 •

edited

Loading

delve-auditor bot commented Jun 25, 2025 •

edited

Loading

github-actions bot commented Jul 12, 2025 •

edited

Loading

CodeRabbit Configuration File (`.coderabbit.yaml`)

joeauyeung left a comment •

edited

Loading