kaneo (minimalist kanban) fork to experiment adding a tangled integration
github.com/usekaneo/kaneo
1import { and, between, eq, isNotNull, isNull, or } from "drizzle-orm";
2import db from "../database";
3import {
4 columnTable,
5 taskReminderSentTable,
6 taskTable,
7} from "../database/schema";
8import createNotification from "../notification/controllers/create-notification";
9
10type ReminderType = "one_day_before" | "one_hour_before" | "overdue";
11
12const HOUR_MS = 60 * 60 * 1000;
13const MINUTE_MS = 60 * 1000;
14
15function buildWindows(now: Date) {
16 const nowMs = now.getTime();
17
18 return {
19 oneDay: {
20 start: new Date(nowMs + 23 * HOUR_MS + 50 * MINUTE_MS),
21 end: new Date(nowMs + 24 * HOUR_MS + 10 * MINUTE_MS),
22 type: "one_day_before" as ReminderType,
23 notificationType: "due_date_reminder" as const,
24 },
25 oneHour: {
26 start: new Date(nowMs + 50 * MINUTE_MS),
27 end: new Date(nowMs + 70 * MINUTE_MS),
28 type: "one_hour_before" as ReminderType,
29 notificationType: "due_date_reminder" as const,
30 },
31 overdue: {
32 end: now,
33 start: new Date(nowMs - 10 * MINUTE_MS),
34 type: "overdue" as ReminderType,
35 notificationType: "task_overdue" as const,
36 },
37 };
38}
39
40async function getTasksNeedingReminder(
41 windowStart: Date,
42 windowEnd: Date,
43 reminderType: ReminderType,
44) {
45 const results = await db
46 .select({
47 id: taskTable.id,
48 title: taskTable.title,
49 userId: taskTable.userId,
50 dueDate: taskTable.dueDate,
51 projectId: taskTable.projectId,
52 })
53 .from(taskTable)
54 .leftJoin(columnTable, eq(taskTable.columnId, columnTable.id))
55 .leftJoin(
56 taskReminderSentTable,
57 and(
58 eq(taskReminderSentTable.taskId, taskTable.id),
59 eq(taskReminderSentTable.reminderType, reminderType),
60 ),
61 )
62 .where(
63 and(
64 isNotNull(taskTable.userId),
65 isNotNull(taskTable.dueDate),
66 between(taskTable.dueDate, windowStart, windowEnd),
67 isNull(taskReminderSentTable.id),
68 // Exclude tasks in final columns (completed); include tasks with no column
69 or(isNull(columnTable.isFinal), eq(columnTable.isFinal, false)),
70 ),
71 );
72
73 return results;
74}
75
76async function processReminder(
77 task: {
78 id: string;
79 title: string;
80 userId: string | null;
81 dueDate: Date | null;
82 projectId: string;
83 },
84 reminderType: ReminderType,
85 notificationType: "due_date_reminder" | "task_overdue",
86) {
87 if (!task.userId) return;
88
89 // Insert sent record first — if it already exists, skip notification
90 try {
91 const [inserted] = await db
92 .insert(taskReminderSentTable)
93 .values({
94 taskId: task.id,
95 reminderType,
96 })
97 .onConflictDoNothing({
98 target: [
99 taskReminderSentTable.taskId,
100 taskReminderSentTable.reminderType,
101 ],
102 })
103 .returning();
104
105 if (!inserted) return;
106 } catch {
107 return;
108 }
109
110 await createNotification({
111 userId: task.userId,
112 type: notificationType,
113 eventData: {
114 taskTitle: task.title,
115 reminderType,
116 dueDate: task.dueDate?.toISOString() ?? null,
117 },
118 resourceId: task.id,
119 resourceType: "task",
120 });
121}
122
123export async function checkDueDateReminders(): Promise<void> {
124 const now = new Date();
125 const windows = buildWindows(now);
126
127 for (const window of Object.values(windows)) {
128 try {
129 const tasks = await getTasksNeedingReminder(
130 window.start,
131 window.end,
132 window.type,
133 );
134
135 for (const task of tasks) {
136 try {
137 await processReminder(task, window.type, window.notificationType);
138 } catch (error) {
139 console.error("Failed to process due date reminder", {
140 taskId: task.id,
141 reminderType: window.type,
142 error,
143 });
144 }
145 }
146 } catch (error) {
147 console.error("Failed to query tasks for due date reminders", {
148 reminderType: window.type,
149 error,
150 });
151 }
152 }
153}