⚡ 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,
asc,
desc,
eq,
getTableColumns,
gte,
inArray,
lte,
type SQL,
type SQLWrapper,
sql,
} from "drizzle-orm";
import { alias } from "drizzle-orm/pg-core";
import { HTTPException } from "hono/http-exception";
import { publicExternalLink } from "../../project/controllers/public-external-link";
import {
columnTable,
externalLinkTable,
labelTable,
projectTable,
taskTable,
taskRelationTable,
userTable,
} from "../../database/schema";
import { boundedTaskRead, type TaskReadDatabase } from "../bounded-read";
import {
boardDescription,
boardProjectDescription,
descriptionDeferred,
projectDescriptionDeferred,
} from "../description-pages";
import { taskIsCompleted } from "../task-is-completed";
import { getSubtaskCounts } from "../get-subtask-counts";
export type GetTasksOptions = {
publicOnly?: boolean;
assigneeId?: string;
dueAfter?: string;
dueBefore?: string;
limit?: number;
page?: number;
relatedPage?: number;
priority?: string;
sortBy?:
| "createdAt"
| "priority"
| "dueDate"
| "position"
| "title"
| "number";
sortOrder?: "asc" | "desc";
status?: string;
};
const priorityCaseExpr = sql<number>`CASE
WHEN ${taskTable.priority} = 'urgent' THEN 4
WHEN ${taskTable.priority} = 'high' THEN 3
WHEN ${taskTable.priority} = 'medium' THEN 2
WHEN ${taskTable.priority} = 'low' THEN 1
ELSE 0
END`;
function sortValue(sortBy: GetTasksOptions["sortBy"]): SQLWrapper {
switch (sortBy) {
case "createdAt":
return taskTable.createdAt;
case "priority":
return priorityCaseExpr;
case "dueDate":
return taskTable.dueDate;
case "title":
return taskTable.title;
case "number":
return taskTable.number;
default:
return taskTable.position;
}
}
function buildOrderBy(
sortBy: GetTasksOptions["sortBy"],
sortOrder: GetTasksOptions["sortOrder"],
): SQL {
return (sortOrder === "desc" ? desc : asc)(sortValue(sortBy));
}
function boardRevision(
publicOnly: boolean | undefined,
fields: SQLWrapper[],
): SQL<string> {
return publicOnly
? sql<string>`coalesce(sum(hashtextextended(jsonb_build_array(${sql.join(fields, sql`, `)})::text, 0)::numeric), 0)::text`
: sql<string>`'0'`;
}
async function getTasksPage(
db: TaskReadDatabase,
projectId: string,
options: GetTasksOptions,
userId?: string,
) {
const [project] = await db
.select({
...getTableColumns(projectTable),
revision: options.publicOnly
? sql<string>`${projectTable}.xmin::text`
: sql<string>`'0'`,
description: boardProjectDescription,
descriptionDeferred: projectDescriptionDeferred,
})
.from(projectTable)
.where(eq(projectTable.id, projectId))
.limit(1);
if (!project) {
throw new HTTPException(404, {
message: "Project not found",
});
}
const conditions = [eq(taskTable.projectId, projectId)];
if (options.status) {
conditions.push(eq(taskTable.status, options.status));
}
if (options.priority) {
conditions.push(eq(taskTable.priority, options.priority));
}
if (options.assigneeId) {
conditions.push(eq(taskTable.userId, options.assigneeId));
}
if (options.dueBefore) {
conditions.push(lte(taskTable.dueDate, new Date(options.dueBefore)));
}
if (options.dueAfter) {
conditions.push(gte(taskTable.dueDate, new Date(options.dueAfter)));
}
const whereClause = and(...conditions);
const page = options.page && options.page > 0 ? options.page : 1;
const pageSize =
options.limit && options.limit > 0 ? Math.min(options.limit, 100) : 50;
const offset = (page - 1) * pageSize;
const relatedPage = options.relatedPage ?? 1;
const relatedPageSize = 100;
const relatedOffset = (relatedPage - 1) * relatedPageSize;
const orderByClause = buildOrderBy(
options.sortBy ?? "position",
options.sortOrder ?? "asc",
);
const taskCountQuery = db
.select({
count: sql<number>`count(*)`,
revision: boardRevision(options.publicOnly, [
taskTable.id,
sortValue(options.sortBy),
taskTable.status,
// Row versions detect content edits without hashing large descriptions.
sql`${taskTable}.xmin::text`,
userTable.name,
userTable.image,
]),
})
.from(taskTable)
.$dynamic();
const [taskCount] = await (
options.publicOnly
? taskCountQuery.leftJoin(userTable, eq(taskTable.userId, userTable.id))
: taskCountQuery
).where(whereClause);
const total = Number(taskCount?.count ?? 0);
const taskSelection = {
id: taskTable.id,
title: taskTable.title,
number: taskTable.number,
description: boardDescription,
descriptionDeferred,
status: taskTable.status,
priority: taskTable.priority,
startDate: taskTable.startDate,
dueDate: taskTable.dueDate,
recurrence: taskTable.recurrence,
position: taskTable.position,
createdAt: taskTable.createdAt,
userId: taskTable.userId,
assigneeName: userTable.name,
assigneeId: userTable.id,
assigneeImage: userTable.image,
projectId: taskTable.projectId,
};
const query = db
.select(taskSelection)
.from(taskTable)
.leftJoin(userTable, eq(taskTable.userId, userTable.id))
.leftJoin(projectTable, eq(taskTable.projectId, projectTable.id))
.where(whereClause)
.orderBy(orderByClause, asc(taskTable.id));
const paginatedTasks = await query.limit(pageSize).offset(offset);
const taskIds = paginatedTasks.map((task) => task.id);
const subtaskCounts = await getSubtaskCounts(
db,
taskIds,
project.workspaceId,
options.publicOnly ?? false,
userId,
);
const labelsData =
taskIds.length > 0
? await db
.select({
id: labelTable.id,
name: labelTable.name,
color: labelTable.color,
taskId: labelTable.taskId,
})
.from(labelTable)
.where(inArray(labelTable.taskId, taskIds))
.orderBy(asc(labelTable.id))
.limit(relatedPageSize)
.offset(relatedOffset)
: [];
const externalLinksData =
taskIds.length > 0
? await db
.select()
.from(externalLinkTable)
.where(inArray(externalLinkTable.taskId, taskIds))
.orderBy(asc(externalLinkTable.id))
.limit(relatedPageSize)
.offset(relatedOffset)
: [];
const taskLabelsMap = new Map<
string,
Array<{ id: string; name: string; color: string }>
>();
for (const label of labelsData) {
if (label.taskId) {
if (!taskLabelsMap.has(label.taskId)) {
taskLabelsMap.set(label.taskId, []);
}
taskLabelsMap.get(label.taskId)?.push({
id: label.id,
name: label.name,
color: label.color,
});
}
}
const taskExternalLinksMap = new Map<
string,
Array<{
id: string;
taskId: string;
integrationId: string | null;
resourceType: string;
externalId: string;
url: string;
title: string | null;
metadata: Record<string, unknown> | null;
createdAt: Date;
updatedAt: Date;
}>
>();
for (const externalLink of externalLinksData) {
if (!taskExternalLinksMap.has(externalLink.taskId)) {
taskExternalLinksMap.set(externalLink.taskId, []);
}
const link = {
...externalLink,
metadata: parseMetadata(externalLink.metadata),
};
taskExternalLinksMap
.get(externalLink.taskId)
?.push(options.publicOnly ? publicExternalLink(link) : link);
}
const projectColumns = await db
.select()
.from(columnTable)
.where(eq(columnTable.projectId, projectId))
.orderBy(asc(columnTable.position), asc(columnTable.id))
.limit(relatedPageSize)
.offset(relatedOffset);
// Keep every selected task representable even when its column falls on a
// later metadata page. At most 100 distinct task statuses can be present.
const missingStatuses = Array.from(
new Set(paginatedTasks.map((task) => task.status)),
).filter(
(status) =>
status !== "planned" &&
status !== "archived" &&
!projectColumns.some((column) => column.slug === status),
);
if (missingStatuses.length) {
const taskColumns = await db
.selectDistinctOn([columnTable.slug])
.from(columnTable)
.where(
and(
eq(columnTable.projectId, projectId),
inArray(columnTable.slug, missingStatuses),
),
)
.orderBy(
asc(columnTable.slug),
asc(columnTable.position),
asc(columnTable.id),
)
.limit(100);
projectColumns.push(...taskColumns);
}
const [columnCount] = await db
.select({
count: sql<number>`count(*)`,
revision: boardRevision(options.publicOnly, [
columnTable.id,
columnTable.slug,
columnTable.position,
columnTable.name,
columnTable.icon,
columnTable.isFinal,
]),
})
.from(columnTable)
.where(eq(columnTable.projectId, projectId));
let labelCount = 0;
let linkCount = 0;
let labelRevision = "0";
let linkRevision = "0";
if (taskIds.length) {
const [labels] = await db
.select({
count: sql<number>`count(*)`,
revision: boardRevision(options.publicOnly, [
labelTable.id,
labelTable.taskId,
labelTable.name,
labelTable.color,
]),
})
.from(labelTable)
.where(inArray(labelTable.taskId, taskIds));
const [links] = await db
.select({
count: sql<number>`count(*)`,
revision: boardRevision(options.publicOnly, [
externalLinkTable.id,
externalLinkTable.taskId,
sql`${externalLinkTable}.xmin::text`,
]),
})
.from(externalLinkTable)
.where(inArray(externalLinkTable.taskId, taskIds));
labelCount = Number(labels?.count ?? 0);
linkCount = Number(links?.count ?? 0);
labelRevision = labels?.revision ?? "0";
linkRevision = links?.revision ?? "0";
}
let publicRelatedRevision = "0";
if (options.publicOnly) {
// Every task page must detect edits to cards or metadata loaded earlier.
const [labels] = await db
.select({
revision: boardRevision(true, [
labelTable.id,
labelTable.taskId,
labelTable.name,
labelTable.color,
]),
})
.from(labelTable)
.innerJoin(taskTable, eq(labelTable.taskId, taskTable.id))
.where(whereClause);
const [links] = await db
.select({
revision: boardRevision(true, [
externalLinkTable.id,
sql`${externalLinkTable}.xmin::text`,
]),
})
.from(externalLinkTable)
.innerJoin(taskTable, eq(externalLinkTable.taskId, taskTable.id))
.where(whereClause);
const parent = alias(taskTable, "board_parent");
const [children] = await db
.select({
revision: boardRevision(true, [
taskRelationTable.id,
taskTable.id,
taskIsCompleted,
]),
})
.from(taskRelationTable)
.innerJoin(parent, eq(taskRelationTable.sourceTaskId, parent.id))
.innerJoin(taskTable, eq(taskRelationTable.targetTaskId, taskTable.id))
.innerJoin(projectTable, eq(taskTable.projectId, projectTable.id))
.where(
and(
eq(parent.projectId, projectId),
eq(taskRelationTable.relationType, "subtask"),
eq(projectTable.workspaceId, project.workspaceId),
eq(projectTable.isPublic, true),
),
);
publicRelatedRevision = `${labels?.revision}:${links?.revision}:${children?.revision}`;
}
const columns = projectColumns.map((column) => ({
id: column.slug,
slug: column.slug,
name: column.name,
position: column.position,
icon: column.icon,
isFinal: column.isFinal,
tasks: paginatedTasks
.filter((task) => task.status === column.slug)
.map((task) => ({
...task,
subtaskCounts: subtaskCounts.get(task.id) ?? { completed: 0, total: 0 },
labels: taskLabelsMap.get(task.id) || [],
externalLinks: taskExternalLinksMap.get(task.id) || [],
})),
}));
const archivedTasks = paginatedTasks
.filter((task) => task.status === "archived")
.map((task) => ({
...task,
subtaskCounts: subtaskCounts.get(task.id) ?? { completed: 0, total: 0 },
labels: taskLabelsMap.get(task.id) || [],
externalLinks: taskExternalLinksMap.get(task.id) || [],
}));
const plannedTasks = paginatedTasks
.filter((task) => task.status === "planned")
.map((task) => ({
...task,
subtaskCounts: subtaskCounts.get(task.id) ?? { completed: 0, total: 0 },
labels: taskLabelsMap.get(task.id) || [],
externalLinks: taskExternalLinksMap.get(task.id) || [],
}));
return {
data: {
id: project.id,
name: project.name,
slug: project.slug,
icon: project.icon,
description: project.description,
descriptionDeferred: project.descriptionDeferred,
isPublic: project.isPublic,
workspaceId: project.workspaceId,
backgroundVersion: project.backgroundVersion,
columns,
archivedTasks,
plannedTasks,
},
pagination: {
total,
...(options.publicOnly
? {
revision: `${project.revision}:${total}:${taskCount?.revision}:${columnCount?.count}:${columnCount?.revision}:${publicRelatedRevision}`,
relatedRevision: `${labelCount}:${labelRevision}:${linkCount}:${linkRevision}`,
}
: {}),
page,
pageSize,
totalPages: Math.max(1, Math.ceil(total / pageSize)),
relatedPage,
relatedPageSize,
relatedTotalPages: Math.max(
1,
Math.ceil(
Math.max(Number(columnCount?.count ?? 0), labelCount, linkCount) /
relatedPageSize,
),
),
},
};
}
function parseMetadata(raw: string | null): Record<string, unknown> | null {
if (!raw) return null;
try {
const value: unknown = JSON.parse(raw);
return value !== null && typeof value === "object" && !Array.isArray(value)
? (value as Record<string, unknown>)
: null;
} catch {
return null;
}
}
export default function getTasks(
projectId: string,
options: GetTasksOptions = {},
userId?: string,
) {
return boundedTaskRead(
(db) => getTasksPage(db, projectId, options, userId),
"Task list request took too long; retry later",
);
}