Jonny Nguyen
b300293a72
Add a new field `consecutiveFailures` to the Inbox model to track consecutive errors. Update the inbox guard logic to mark mailboxes as error after exceeding a failure threshold. Implement retry logic in email and IMAP services to handle transient connection issues. Adjust job processing to reclaim stale send jobs and improve overall reliability of email dispatching. Update routes to initialize `consecutiveFailures` on inbox creation and reset it when the inbox status changes to active.
81 lines
4.4 KiB
SQL
81 lines
4.4 KiB
SQL
-- RedefineTables
|
|
PRAGMA defer_foreign_keys=ON;
|
|
PRAGMA foreign_keys=OFF;
|
|
CREATE TABLE "new_Inbox" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"workspaceId" TEXT NOT NULL,
|
|
"email" TEXT NOT NULL,
|
|
"smtpHost" TEXT NOT NULL,
|
|
"smtpPort" INTEGER NOT NULL,
|
|
"imapHost" TEXT NOT NULL,
|
|
"imapPort" INTEGER NOT NULL,
|
|
"username" TEXT NOT NULL,
|
|
"password" TEXT NOT NULL,
|
|
"authType" TEXT NOT NULL DEFAULT 'password',
|
|
"oauthRefreshToken" TEXT,
|
|
"role" TEXT NOT NULL DEFAULT 'unassigned',
|
|
"provider" TEXT NOT NULL DEFAULT 'custom',
|
|
"status" TEXT NOT NULL DEFAULT 'active',
|
|
"dailyLimit" INTEGER NOT NULL DEFAULT 40,
|
|
"currentRamp" INTEGER NOT NULL DEFAULT 5,
|
|
"industry" TEXT NOT NULL DEFAULT 'SaaS',
|
|
"senderName" TEXT,
|
|
"companyName" TEXT,
|
|
"lastError" TEXT,
|
|
"errorAt" DATETIME,
|
|
"consecutiveFailures" INTEGER NOT NULL DEFAULT 0,
|
|
"spamFolderPath" TEXT,
|
|
"lastSpamRescuedAt" DATETIME,
|
|
"createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" DATETIME NOT NULL,
|
|
CONSTRAINT "Inbox_workspaceId_fkey" FOREIGN KEY ("workspaceId") REFERENCES "Workspace" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
INSERT INTO "new_Inbox" ("authType", "companyName", "createdAt", "currentRamp", "dailyLimit", "email", "errorAt", "id", "imapHost", "imapPort", "industry", "lastError", "lastSpamRescuedAt", "oauthRefreshToken", "password", "provider", "role", "senderName", "smtpHost", "smtpPort", "spamFolderPath", "status", "updatedAt", "username", "workspaceId") SELECT "authType", "companyName", "createdAt", "currentRamp", "dailyLimit", "email", "errorAt", "id", "imapHost", "imapPort", "industry", "lastError", "lastSpamRescuedAt", "oauthRefreshToken", "password", "provider", "role", "senderName", "smtpHost", "smtpPort", "spamFolderPath", "status", "updatedAt", "username", "workspaceId" FROM "Inbox";
|
|
DROP TABLE "Inbox";
|
|
ALTER TABLE "new_Inbox" RENAME TO "Inbox";
|
|
CREATE INDEX "Inbox_status_idx" ON "Inbox"("status");
|
|
CREATE INDEX "Inbox_workspaceId_role_idx" ON "Inbox"("workspaceId", "role");
|
|
CREATE UNIQUE INDEX "Inbox_workspaceId_email_key" ON "Inbox"("workspaceId", "email");
|
|
-- RedefineTables
|
|
PRAGMA defer_foreign_keys=ON;
|
|
PRAGMA foreign_keys=OFF;
|
|
CREATE TABLE "new_Inbox" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"workspaceId" TEXT NOT NULL,
|
|
"email" TEXT NOT NULL,
|
|
"smtpHost" TEXT NOT NULL,
|
|
"smtpPort" INTEGER NOT NULL,
|
|
"imapHost" TEXT NOT NULL,
|
|
"imapPort" INTEGER NOT NULL,
|
|
"username" TEXT NOT NULL,
|
|
"password" TEXT NOT NULL,
|
|
"authType" TEXT NOT NULL DEFAULT 'password',
|
|
"oauthRefreshToken" TEXT,
|
|
"role" TEXT NOT NULL DEFAULT 'unassigned',
|
|
"provider" TEXT NOT NULL DEFAULT 'custom',
|
|
"status" TEXT NOT NULL DEFAULT 'active',
|
|
"dailyLimit" INTEGER NOT NULL DEFAULT 40,
|
|
"currentRamp" INTEGER NOT NULL DEFAULT 5,
|
|
"industry" TEXT NOT NULL DEFAULT 'SaaS',
|
|
"senderName" TEXT,
|
|
"companyName" TEXT,
|
|
"lastError" TEXT,
|
|
"errorAt" DATETIME,
|
|
"consecutiveFailures" INTEGER NOT NULL DEFAULT 0,
|
|
"spamFolderPath" TEXT,
|
|
"lastSpamRescuedAt" DATETIME,
|
|
"createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" DATETIME NOT NULL,
|
|
CONSTRAINT "Inbox_workspaceId_fkey" FOREIGN KEY ("workspaceId") REFERENCES "Workspace" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
INSERT INTO "new_Inbox" ("authType", "companyName", "createdAt", "currentRamp", "dailyLimit", "email", "errorAt", "id", "imapHost", "imapPort", "industry", "lastError", "lastSpamRescuedAt", "oauthRefreshToken", "password", "provider", "role", "senderName", "smtpHost", "smtpPort", "spamFolderPath", "status", "updatedAt", "username", "workspaceId") SELECT "authType", "companyName", "createdAt", "currentRamp", "dailyLimit", "email", "errorAt", "id", "imapHost", "imapPort", "industry", "lastError", "lastSpamRescuedAt", "oauthRefreshToken", "password", "provider", "role", "senderName", "smtpHost", "smtpPort", "spamFolderPath", "status", "updatedAt", "username", "workspaceId" FROM "Inbox";
|
|
DROP TABLE "Inbox";
|
|
ALTER TABLE "new_Inbox" RENAME TO "Inbox";
|
|
CREATE INDEX "Inbox_status_idx" ON "Inbox"("status");
|
|
CREATE INDEX "Inbox_workspaceId_role_idx" ON "Inbox"("workspaceId", "role");
|
|
CREATE UNIQUE INDEX "Inbox_workspaceId_email_key" ON "Inbox"("workspaceId", "email");
|
|
PRAGMA foreign_keys=ON;
|
|
PRAGMA defer_foreign_keys=OFF;
|
|
|
|
-- Reduce IMAP polling frequency for existing installs still on the old default.
|
|
UPDATE "Setting" SET "value" = '*/20 * * * *' WHERE "key" = 'rescuer_cron' AND "value" = '*/10 * * * *';
|