⚡ Project management that doesn't become the project. Boards, Gantt charts, and GitHub integration. Self-host it or use Kaneo Cloud.
TypeScript honoissue-managementissue-trackerjira-alternativekanbanlinear-alternativemcpproject-managementreactself-hostedtypescript
import { and, between, eq, isNotNull, isNull, ne, or, sql } from "drizzle-orm";
import db from "../database";
import {
columnTable,
projectTable,
taskReminderSentTable,
taskTable,
userNotificationPreferenceTable,
workspaceUserTable,
} from "../database/schema";
import {
persistNotification,
dispatchNotification,
} from "../notification/controllers/create-notification";
import { workspaceIsActive } from "../workspace/archive/active-workspace-sql";
import {
DUE_DATE_DURATION_MS,
REMINDER_WINDOW_MINUTES,
} from "./reminder-timing";
type ReminderType = "configured_before" | "overdue";
const MINUTE_MS = 60 * 1000;
function buildWindows(now: Date) {
// Shift the window to stored day-start timestamps, preserving indexed lookups.
const nowMs = now.getTime() - DUE_DATE_DURATION_MS;
const windowEnd = new Date(nowMs);
return {
upcoming: {
start: new Date(nowMs - REMINDER_WINDOW_MINUTES * MINUTE_MS),
end: windowEnd,
type: "configured_before" as ReminderType,
notificationType: "due_date_reminder" as const,
},
overdue: {
end: windowEnd,
start: new Date(nowMs - 10 * MINUTE_MS),
type: "overdue" as ReminderType,
notificationType: "task_overdue" as const,
},
};
}
async function getTasksNeedingReminder(
windowStart: Date,
windowEnd: Date,
reminderType: ReminderType,
) {
const results = await db
.select({
id: taskTable.id,
title: taskTable.title,
userId: taskTable.userId,
dueDate: taskTable.dueDate,
projectId: taskTable.projectId,
leadTimeMinutes:
userNotificationPreferenceTable.dueDateReminderLeadTimeMinutes,
})
.from(taskTable)
.innerJoin(projectTable, eq(projectTable.id, taskTable.projectId))
.innerJoin(
workspaceUserTable,
and(
eq(workspaceUserTable.workspaceId, projectTable.workspaceId),
eq(workspaceUserTable.userId, taskTable.userId),
),
)
.leftJoin(columnTable, eq(taskTable.columnId, columnTable.id))
.leftJoin(
userNotificationPreferenceTable,
eq(userNotificationPreferenceTable.userId, taskTable.userId),
)
.leftJoin(
taskReminderSentTable,
and(
eq(taskReminderSentTable.taskId, taskTable.id),
eq(taskReminderSentTable.reminderType, reminderType),
),
)
.where(
and(
isNotNull(taskTable.userId),
isNotNull(taskTable.dueDate),
reminderType === "configured_before"
? sql`${taskTable.dueDate} - (COALESCE(${userNotificationPreferenceTable.dueDateReminderLeadTimeMinutes}, 1440) * interval '1 minute') BETWEEN ${windowStart.toISOString()} AND ${windowEnd.toISOString()}`
: between(taskTable.dueDate, windowStart, windowEnd),
isNull(taskReminderSentTable.id),
or(
isNull(userNotificationPreferenceTable.id),
eq(userNotificationPreferenceTable.dueDateReminderEnabled, true),
),
// Exclude tasks in final columns (completed); include tasks with no column
or(isNull(columnTable.isFinal), eq(columnTable.isFinal, false)),
// Archived tasks keep their due date but should not notify anyone
ne(taskTable.status, "archived"),
workspaceIsActive(projectTable.workspaceId),
),
);
return results;
}
async function processReminder(
task: {
id: string;
title: string;
userId: string | null;
dueDate: Date | null;
projectId: string;
leadTimeMinutes: number | null;
},
reminderType: ReminderType,
notificationType: "due_date_reminder" | "task_overdue",
) {
if (!task.userId) return;
const notification = await db.transaction(async (tx) => {
const [claimed] = await tx
.insert(taskReminderSentTable)
.values({
taskId: task.id,
reminderType,
})
.onConflictDoNothing({
target: [
taskReminderSentTable.taskId,
taskReminderSentTable.reminderType,
],
})
.returning();
if (!claimed) return null;
const created = await persistNotification(
{
userId: task.userId!,
type: notificationType,
eventData: {
taskTitle: task.title,
reminderType,
leadTimeMinutes: task.leadTimeMinutes ?? 1440,
dueDate: task.dueDate?.toISOString() ?? null,
},
resourceId: task.id,
resourceType: "task",
},
tx,
);
if (!created)
await tx
.delete(taskReminderSentTable)
.where(eq(taskReminderSentTable.id, claimed.id));
return created;
});
if (notification) await dispatchNotification(notification);
}
export async function checkDueDateReminders(): Promise<{ degraded: boolean }> {
const now = new Date();
const windows = buildWindows(now);
let degraded = false;
for (const window of Object.values(windows)) {
try {
const tasks = await getTasksNeedingReminder(
window.start,
window.end,
window.type,
);
for (const task of tasks) {
try {
await processReminder(task, window.type, window.notificationType);
} catch (error) {
degraded = true;
console.error("Failed to process due date reminder", {
taskId: task.id,
reminderType: window.type,
error,
});
}
}
} catch (error) {
degraded = true;
console.error("Failed to query tasks for due date reminders", {
reminderType: window.type,
error,
});
}
}
return { degraded };
}