Skip to content

TaskView is a self-hosted project and task management platform focused on clarity, ownership, and control. TaskView is built for teams that want a transparent, self-hosted alternative to SaaS task managers.

8.4 KB
Raw Blame
import { and, eq, exists, inArray, or } from 'drizzle-orm';
import {
    CollaborationPermissionsToRoleSchema,
    CollaborationRolesSchema,
    CollaborationUsersSchema,
    CollaborationUsersToGoalsSchema,
    CollaborationUsersToRolesSchema,
    GoalsSchema,
    PermissionsSchema,
} from 'taskview-db-schemas';
import { Database } from '../modules/db';
import type {
    FetchGoalIdsWithAnyPermissionParams,
    FetchPermissionsForGoalByUserParams,
    FetchPermissionsForGoalsParams,
    GoalPermissionItemsFromDb,
    GoalPermissionItemsWithGoalFromDb,
    GoalPermissionType,
} from '../types/auth.types';
import type { GoalItemInDb } from '../types/goal.type';
import type { ListItemInDb } from '../types/lists.types';
import type { TaskItemInDb } from '../types/tasks.types';

export class GoalPermissionsRepository {
    private db: Database;

    constructor() {
        this.db = Database.getInstance();
    }

    async getGoalIdForList(listId: number): Promise<ListItemInDb['goal_id'] | null> {
        const result = await this.db.query<ListItemInDb>('select goal_id from tasks.goal_lists where id = $1', [
            listId,
        ]);
        if (result.rows.length > 0) {
            return result.rows[0].goal_id;
        }
        return null;
    }

    /**
     * Permission set of an arbitrary user for a goal, without an AppUser/request
     * context — used by background workers (e.g. deadline notifications) that
     * must gate content per recipient.
     */
    async fetchPermissionsForGoalByUser(params: FetchPermissionsForGoalByUserParams): Promise<GoalPermissionItemsFromDb> {
        const goalInfo = await this.db.query<GoalItemInDb>('select * from tasks.goals where id = $1', [params.goalId]);

        if (goalInfo.rows.length === 0) {
            return [];
        }

        let query = '';
        let args: any = [];

        if (goalInfo.rows[0].owner === params.userId) {
            query = `select name as "permissionName", id as "permissionId" from tv_auth.permissions;`;
        } else {
            query = `select p.name as "permissionName", p.id as "permissionId"
                        from collaboration.users cu
                                left join collaboration.users_to_goals utg on cu.id = utg.user_id
                                left join tasks.goals tg on utg.goal_id = tg.id
                                left join collaboration.users_to_roles utr on utr.user_id = cu.id
                                left join collaboration.roles rol on utr.role_id = rol.id and rol.goal_id = tg.id
                                left join collaboration.permissions_to_role ptr on rol.id = ptr.role_id
                                left join tv_auth.permissions p on ptr.permission_id = p.id
                        where email = $1 and tg.id = $2 and p.name is not null and p.id is not null;`;
            args = [params.email, params.goalId];
        }

        const result = await this.db.query<GoalPermissionItemsFromDb[number]>(query, args);

        return result.rows;
    }

    async fetchPermissionsForGoals(params: FetchPermissionsForGoalsParams): Promise<GoalPermissionItemsWithGoalFromDb> {
        if (params.goalIds.length === 0) return [];

        const goals = await this.db.dbDrizzle
            .select({ id: GoalsSchema.id, owner: GoalsSchema.owner })
            .from(GoalsSchema)
            .where(inArray(GoalsSchema.id, params.goalIds));

        const ownedGoalIds = goals.filter((g) => g.owner === params.userId).map((g) => g.id);
        const sharedGoalIds = goals.filter((g) => g.owner !== params.userId).map((g) => g.id);
        const rows: GoalPermissionItemsWithGoalFromDb = [];

        if (ownedGoalIds.length > 0) {
            const allPermissions = await this.db.dbDrizzle
                .select({ permissionName: PermissionsSchema.name, permissionId: PermissionsSchema.id })
                .from(PermissionsSchema);
            ownedGoalIds.forEach((goalId) => {
                allPermissions.forEach((p) => {
                    rows.push({ goalId, permissionName: p.permissionName as GoalPermissionType, permissionId: p.permissionId });
                });
            });
        }

        if (sharedGoalIds.length > 0) {
            const shared = await this.db.dbDrizzle
                .select({
                    goalId: CollaborationUsersToGoalsSchema.goalId,
                    permissionName: PermissionsSchema.name,
                    permissionId: PermissionsSchema.id,
                })
                .from(CollaborationUsersSchema)
                .innerJoin(
                    CollaborationUsersToGoalsSchema,
                    eq(CollaborationUsersToGoalsSchema.userId, CollaborationUsersSchema.id),
                )
                .innerJoin(
                    CollaborationUsersToRolesSchema,
                    eq(CollaborationUsersToRolesSchema.userId, CollaborationUsersSchema.id),
                )
                .innerJoin(
                    CollaborationRolesSchema,
                    and(
                        eq(CollaborationRolesSchema.id, CollaborationUsersToRolesSchema.roleId),
                        eq(CollaborationRolesSchema.goalId, CollaborationUsersToGoalsSchema.goalId),
                    ),
                )
                .innerJoin(
                    CollaborationPermissionsToRoleSchema,
                    eq(CollaborationPermissionsToRoleSchema.roleId, CollaborationRolesSchema.id),
                )
                .innerJoin(
                    PermissionsSchema,
                    eq(PermissionsSchema.id, CollaborationPermissionsToRoleSchema.permissionId),
                )
                .where(
                    and(
                        eq(CollaborationUsersSchema.email, params.email),
                        inArray(CollaborationUsersToGoalsSchema.goalId, sharedGoalIds),
                    ),
                );
            shared.forEach((r) => {
                rows.push({ goalId: r.goalId, permissionName: r.permissionName as GoalPermissionType, permissionId: r.permissionId });
            });
        }

        return rows;
    }

    async getGoalIdForTask(taskId: number): Promise<TaskItemInDb['goal_id'] | null> {
        const result = await this.db.query<TaskItemInDb>('select goal_id from tasks.tasks where id = $1', [taskId]);
        if (result.rows.length > 0) {
            return result.rows[0].goal_id;
        }
        return null;
    }

    async fetchGoalIdsWithAnyPermission(params: FetchGoalIdsWithAnyPermissionParams): Promise<number[]> {
        if (params.permissionNames.length === 0) return [];

        const permissionSubquery = this.db.dbDrizzle
            .select({ one: CollaborationUsersSchema.id })
            .from(CollaborationUsersSchema)
            .innerJoin(
                CollaborationUsersToGoalsSchema,
                and(
                    eq(CollaborationUsersToGoalsSchema.userId, CollaborationUsersSchema.id),
                    eq(CollaborationUsersToGoalsSchema.goalId, GoalsSchema.id),
                ),
            )
            .innerJoin(
                CollaborationUsersToRolesSchema,
                eq(CollaborationUsersToRolesSchema.userId, CollaborationUsersSchema.id),
            )
            .innerJoin(
                CollaborationRolesSchema,
                and(
                    eq(CollaborationRolesSchema.id, CollaborationUsersToRolesSchema.roleId),
                    eq(CollaborationRolesSchema.goalId, GoalsSchema.id),
                ),
            )
            .innerJoin(
                CollaborationPermissionsToRoleSchema,
                eq(CollaborationPermissionsToRoleSchema.roleId, CollaborationRolesSchema.id),
            )
            .innerJoin(
                PermissionsSchema,
                eq(PermissionsSchema.id, CollaborationPermissionsToRoleSchema.permissionId),
            )
            .where(
                and(
                    eq(CollaborationUsersSchema.email, params.email),
                    inArray(PermissionsSchema.name, params.permissionNames),
                ),
            );

        const result = await this.db.dbDrizzle
            .select({ id: GoalsSchema.id })
            .from(GoalsSchema)
            .where(
                and(
                    eq(GoalsSchema.organizationId, params.organizationId),
                    eq(GoalsSchema.archive, 0),
                    or(eq(GoalsSchema.owner, params.userId), exists(permissionSubquery)),
                ),
            );

        return result.map((r) => r.id).filter((id): id is number => Number.isInteger(id));
    }
}