Skip to content

⭐️⭐️⭐️ Open-source, self-hosted alternative to Linear and Plane. Project management and issue tracking where teams and AI agents work side by side to plan and ship products.

30.9 KB
Raw Blame
import {
  db,
  aiAgent,
  chatAttachment,
  documentAsset,
  initiative,
  initiativeAttachment,
  issue,
  issueActivity,
  issueAttachment,
  issueType,
  project,
  projectColumn,
  projectMember,
  projectDocument,
  teamRole,
  projectSetting,
  team,
  teamMember,
  type DbExecutor,
} from '@repo/db';
import {
  and,
  desc,
  eq,
  getTableColumns,
  ilike,
  inArray,
  isNotNull,
  isNull,
  or,
  sql,
  type SQL,
} from 'drizzle-orm';
import { HttpError, iso } from '#shared/lib';
import { PROJECT_KEY_PATTERN } from './key';
import type { Permissions } from '#shared/permissions';
import { getProjectSetting, setProjectSetting } from '#shared/project-settings';
import { PROJECT_FEATURES, featureLabel, type ProjectFeature } from '#shared/features';
import { getLimits, getTeamLimits } from '#shared/limits';
import { projectAccess, type AccessVia } from '#shared/workspace-roles';
import { toMemberContext, type MemberRole } from '#modules/members/service';
import { workspaceGrants } from '#modules/workspaces/service';
import { assertSeatFree } from '#modules/teams/service';
import { deleteThreadsWhere } from '#modules/agents/core/runtime/memory';
import { getProjectDefaults } from '#modules/settings/service';
import { getDefaultRoleId } from '#modules/roles/service';
import { dropUnusedTeamMembership } from '#modules/scim/reconcile';
import { projectRef, teamRef } from '#modules/teams/ref';
import { deleteObjects } from '@repo/storage';
import { lockAttachmentStorage } from '#modules/attachments/storage';

// Data access for projects: the top-level container that groups its own columns,
// issue types, labels, assignees, custom fields, issues, saved views, and
// actions. Access is by membership (project_member): the creator becomes an
// "owner" and only members can reach a project's entities.

export interface ProjectRow {
  id: number;
  teamId: number;
  teamName: string;
  // The team's segment in web URLs: its slug, or its id while it has none.
  teamRef: string;
  key: string;
  // How a URL names the project: "<teamRef>.<key>". See getProjectByRef.
  ref: string;
  name: string;
  description: string;
  mcpEnabled: boolean;
  // The team's own MCP switch, carried here because every MCP gate is a project
  // gate: a project is reachable only while both flags are on.
  teamMcpEnabled: boolean;
  initiativesEnabled: boolean;
  dashboardsEnabled: boolean;
  documentsEnabled: boolean;
  notesEnabled: boolean;
  cyclesEnabled: boolean;
  subtasksEnabled: boolean;
  checklistsEnabled: boolean;
  issueStatsEnabled: boolean;
  pointsEstimateEnabled: boolean;
  timeEstimateEnabled: boolean;
  timeLoggingEnabled: boolean;
  // The sections this project may use at all. A section missing here is blocked for
  // the team that owns the project: its flag above reads as off and the settings page
  // does not offer it.
  availableFeatures: ProjectFeature[];
  archivedAt: string | null;
  createdAt: string;
}

// The optional sections an owner can turn off per project (Settings -> General).
// A disabled section is hidden in the web app; its rows are kept.
export interface ProjectFeatures {
  initiatives: boolean;
  dashboards: boolean;
  documents: boolean;
  notes: boolean;
  cycles: boolean;
  subtasks: boolean;
  checklists: boolean;
  issueStats: boolean;
}

// A project in the caller's list, carrying the caller's own role in it. The list
// UI (project switcher, manage-projects page) uses `role` to gate owner-only
// actions like deletion; the API still enforces the permission on every request.
export interface ProjectListItem extends ProjectRow {
  role: 'owner' | 'member';
  via: AccessVia;
  lastActivityAt: string | null;
  isFavorite: boolean;
  isHidden: boolean;
  // The caller's resolved permission matrix in this project. Present only when the
  // list is requested with permissions (opts.withPermissions); omitted otherwise.
  permissions?: Permissions;
}

type ProjectWithTeam = typeof project.$inferSelect & {
  teamName: string;
  teamSlug: string | null;
  teamMcpEnabled: boolean;
  workspaceId: number;
};

const projectWithTeam = {
  ...getTableColumns(project),
  teamName: team.name,
  teamSlug: team.slug,
  teamMcpEnabled: team.mcpEnabled,
  workspaceId: team.workspaceId,
};

// A blocked section reads as off whatever the project has stored, so masking here is
// what turns a feature off everywhere: the web app reads the flags off this DTO, and
// the route guards read them off the project the guard resolved.
export async function mapProject(row: ProjectWithTeam): Promise<ProjectRow> {
  const { blockedFeatures } = await getLimits(row.workspaceId);
  const on = (feature: ProjectFeature, stored: boolean) =>
    stored && !blockedFeatures.includes(feature);
  return {
    id: row.id,
    teamId: row.teamId,
    teamName: row.teamName,
    teamRef: teamRef({ id: row.teamId, slug: row.teamSlug }),
    key: row.key,
    ref: projectRef({ id: row.teamId, slug: row.teamSlug }, row.key),
    name: row.name,
    description: row.description,
    mcpEnabled: row.mcpEnabled,
    teamMcpEnabled: row.teamMcpEnabled,
    initiativesEnabled: on('initiatives', row.initiativesEnabled),
    dashboardsEnabled: on('dashboards', row.dashboardsEnabled),
    documentsEnabled: on('documents', row.documentsEnabled),
    notesEnabled: on('notes', row.notesEnabled),
    cyclesEnabled: on('cycles', row.cyclesEnabled),
    subtasksEnabled: on('subtasks', row.subtasksEnabled),
    checklistsEnabled: on('checklists', row.checklistsEnabled),
    issueStatsEnabled: on('issueStats', row.issueStatsEnabled),
    pointsEstimateEnabled: row.pointsEstimateEnabled,
    timeEstimateEnabled: row.timeEstimateEnabled,
    timeLoggingEnabled: row.timeLoggingEnabled,
    availableFeatures: PROJECT_FEATURES.filter((feature) => !blockedFeatures.includes(feature)),
    archivedAt: row.archivedAt ? iso(row.archivedAt) : null,
    createdAt: iso(row.createdAt),
  };
}

interface ProjectListOptions {
  mcpOnly?: boolean;
  withPermissions?: boolean;
  q?: string;
  sort?: 'key' | 'name' | 'created' | 'activity';
  teamId?: number;
  workspaceId?: number;
  ids?: number[];
  favorite?: boolean;
  hidden?: boolean;
  favoritesFirst?: boolean;
}

export async function listProjects(
  userId: string,
  opts: ProjectListOptions = {},
): Promise<ProjectListItem[]> {
  return (await readProjects(userId, opts)).items;
}

export function searchProjects(
  userId: string,
  opts: Omit<ProjectListOptions, 'mcpOnly' | 'withPermissions' | 'favoritesFirst'>,
  window: { limit: number; offset: number },
): Promise<{ items: ProjectListItem[]; total: number }> {
  return readProjects(userId, { ...opts, favoritesFirst: true }, window);
}

// A project reached through the workspace has no membership row, so its preference
// columns are null and count as off.
function preference(
  column: typeof projectMember.isFavorite | typeof projectMember.isHidden,
  on?: boolean,
) {
  if (on === undefined) return undefined;
  return on ? eq(column, true) : sql`${column} is not true`;
}

async function readProjects(
  userId: string,
  opts: ProjectListOptions,
  window?: { limit: number; offset: number },
): Promise<{ items: ProjectListItem[]; total: number }> {
  const grants = await workspaceGrants(userId);
  const grantedTeams = [...grants.keys()];
  const term = opts.q?.trim().replace(/[\\%_]/g, '\\$&');
  const callerMember = and(
    eq(projectMember.projectId, project.id),
    eq(projectMember.userId, userId),
  );
  const where = and(
    or(
      isNotNull(projectMember.userId),
      grantedTeams.length > 0 ? inArray(project.teamId, grantedTeams) : undefined,
    ),
    isNull(project.archivedAt),
    opts.mcpOnly ? and(eq(project.mcpEnabled, true), eq(team.mcpEnabled, true)) : undefined,
    opts.teamId !== undefined ? eq(project.teamId, opts.teamId) : undefined,
    opts.workspaceId !== undefined ? eq(team.workspaceId, opts.workspaceId) : undefined,
    opts.ids ? inArray(project.id, opts.ids) : undefined,
    preference(projectMember.isFavorite, opts.favorite),
    preference(projectMember.isHidden, opts.hidden),
    term
      ? or(
          ilike(project.key, `%${term}%`),
          ilike(project.name, `%${term}%`),
          ilike(project.description, `%${term}%`),
        )
      : undefined,
  );
  const readingTeams = [...grants]
    .filter(([, grant]) => grant === 'owner' || grant.work_items.read)
    .map(([teamId]) => teamId);
  const latestActivity = db
    .selectDistinctOn([issue.projectId], {
      projectId: issue.projectId,
      createdAt: issueActivity.createdAt,
    })
    .from(issueActivity)
    .innerJoin(issue, eq(issue.id, issueActivity.issueId))
    .innerJoin(project, eq(project.id, issue.projectId))
    .innerJoin(team, eq(team.id, project.teamId))
    .leftJoin(projectMember, callerMember)
    .leftJoin(teamRole, eq(teamRole.id, projectMember.roleId))
    .where(
      and(
        where,
        or(
          and(
            isNotNull(projectMember.userId),
            or(
              eq(projectMember.role, 'owner'),
              isNull(teamRole.permissions),
              sql`${teamRole.permissions} -> 'work_items' -> 'read' = 'true'::jsonb`,
            ),
          ),
          readingTeams.length > 0 ? inArray(project.teamId, readingTeams) : undefined,
        ),
      ),
    )
    .orderBy(issue.projectId, desc(issueActivity.createdAt))
    .as('latest_activity');
  const order: SQL[] = [];
  if (opts.favoritesFirst) order.push(sql`${projectMember.isFavorite} is true desc`);
  if (opts.sort === 'activity') order.push(sql`${latestActivity.createdAt} desc nulls last`);
  else if (opts.sort === 'created') order.push(desc(project.createdAt));
  else if (opts.sort === 'name') order.push(sql`lower(${project.name})`);
  const query = db
    .select({
      ...projectWithTeam,
      memberRole: projectMember.role,
      rolePermissions: teamRole.permissions,
      lastActivityAt: latestActivity.createdAt,
      isFavorite: projectMember.isFavorite,
      isHidden: projectMember.isHidden,
    })
    .from(project)
    .innerJoin(team, eq(team.id, project.teamId))
    .leftJoin(projectMember, callerMember)
    .leftJoin(teamRole, eq(teamRole.id, projectMember.roleId))
    .leftJoin(latestActivity, eq(latestActivity.projectId, project.id))
    .where(where)
    .orderBy(...order, project.key, project.id)
    .$dynamic();
  const [rows, counted] = await Promise.all([
    window ? query.limit(window.limit).offset(window.offset) : query,
    window
      ? db
          .select({ count: sql<number>`count(*)::int` })
          .from(project)
          .innerJoin(team, eq(team.id, project.teamId))
          .leftJoin(projectMember, callerMember)
          .where(where)
      : undefined,
  ]);
  const items = await Promise.all(
    rows.map(
      async ({ memberRole, rolePermissions, lastActivityAt, isFavorite, isHidden, ...row }) => {
        const member = memberRole
          ? toMemberContext(memberRole as MemberRole, rolePermissions)
          : null;
        const access = projectAccess(member, grants.get(row.teamId) ?? null)!;
        const item: ProjectListItem = {
          ...(await mapProject(row)),
          role: access.role,
          via: access.via,
          lastActivityAt: lastActivityAt ? iso(lastActivityAt) : null,
          isFavorite: isFavorite ?? false,
          isHidden: isHidden ?? false,
        };
        if (opts.withPermissions) item.permissions = access.permissions;
        return item;
      },
    ),
  );
  return { items, total: counted?.[0]?.count ?? items.length };
}

// Resolves the project a URL names. The full form is "<teamRef>.<key>", where the
// team is its slug or its id; a slug starts with a letter, so the two never collide.
// A bare key is what every URL carried while keys were unique on the instance: it
// still names a project as long as only one project with that key is in the
// caller's teams, and is refused with 409 once there are several.
export async function getProjectByRef(ref: string, userId: string): Promise<ProjectRow | null> {
  const dot = ref.indexOf('.');
  if (dot >= 0) {
    const teamPart = ref.slice(0, dot);
    const byTeam = /^\d{1,9}$/.test(teamPart)
      ? eq(team.id, Number(teamPart))
      : eq(team.slug, teamPart);
    const [row] = await db
      .select(projectWithTeam)
      .from(project)
      .innerJoin(team, eq(team.id, project.teamId))
      .where(and(byTeam, eq(project.key, ref.slice(dot + 1))));
    return row ? mapProject(row) : null;
  }

  const rows = await db
    .select(projectWithTeam)
    .from(project)
    .innerJoin(team, eq(team.id, project.teamId))
    .where(eq(project.key, ref));
  if (rows.length <= 1) return rows[0] ? mapProject(rows[0]) : null;

  const [teamIds, grants] = await Promise.all([
    db
      .select({ teamId: teamMember.teamId })
      .from(teamMember)
      .where(
        and(
          eq(teamMember.userId, userId),
          inArray(
            teamMember.teamId,
            rows.map((r) => r.teamId),
          ),
        ),
      ),
    workspaceGrants(userId),
  ]);
  const mine = rows.filter(
    (r) => teamIds.some((m) => m.teamId === r.teamId) || grants.has(r.teamId),
  );
  if (mine.length === 0) return null;
  if (mine.length > 1) {
    throw new HttpError(
      409,
      `Several of your teams have a project '${ref}'. Name it with its team, as '<team>.${ref}'.`,
    );
  }
  return mapProject(mine[0]);
}

export async function getProjectById(id: number): Promise<ProjectRow | null> {
  const rows = await db
    .select(projectWithTeam)
    .from(project)
    .innerJoin(team, eq(team.id, project.teamId))
    .where(eq(project.id, id));
  return rows[0] ? mapProject(rows[0]) : null;
}

// The team that owns a project, for the resources the team holds on behalf of all of
// them (integration credentials, notification providers). Throws 404 for an unknown
// project.
export async function getProjectTeamId(projectId: number): Promise<number> {
  const rows = await db
    .select({ teamId: project.teamId })
    .from(project)
    .where(eq(project.id, projectId));
  if (!rows[0]) throw new HttpError(404, 'Project not found');
  return rows[0].teamId;
}

// The team a new project belongs to. The caller's rank in it is checked by the route.
export async function targetTeam(userId: string, teamId: number): Promise<TargetTeam> {
  const [row] = await db
    .select({
      id: team.id,
      name: team.name,
      slug: team.slug,
      mcpEnabled: team.mcpEnabled,
      defaultAgentIds: team.defaultAgentIds,
      workspaceId: team.workspaceId,
    })
    .from(team)
    .where(eq(team.id, teamId));
  if (!row) throw new HttpError(404, 'Team not found');
  // The creator joins the team with the project (joinTeamAsCreator).
  await assertSeatFree(row.id, userId);
  return row;
}

// A project membership stands on a team one. The workspace owner runs every team of the
// workspace without being in it, so a project they create there makes them a member.
export async function joinTeamAsCreator(
  tx: DbExecutor,
  teamId: number,
  userId: string,
): Promise<void> {
  await tx.insert(teamMember).values({ teamId, userId, role: 'member' }).onConflictDoNothing();
}

// The team a project is created in, with what mapProject needs from it.
export interface TargetTeam {
  id: number;
  name: string;
  slug: string | null;
  mcpEnabled: boolean;
  defaultAgentIds: number[];
  workspaceId: number;
}

// Every new project starts with one column per state type, so it's usable (has
// somewhere to put an issue) without a trip to Settings first.
export const DEFAULT_COLUMNS: { name: string; stateType: string; color: string }[] = [
  { name: 'Backlog', stateType: 'backlog', color: '#71717a' },
  { name: 'Todo', stateType: 'unstarted', color: '#6b7280' },
  { name: 'In Progress', stateType: 'started', color: '#eab308' },
  { name: 'Done', stateType: 'completed', color: '#22c55e' },
  { name: 'Canceled', stateType: 'canceled', color: '#ef4444' },
];

// Issue types a new project starts with, picked by sphere of work in the create
// dialog. The first entry of a set becomes the project's default type. "general"
// is the fallback when no preset is chosen: a single Task, so the project is
// usable without committing to a classification.
export const ISSUE_TYPE_PRESETS: Record<string, { name: string; color: string }[]> = {
  general: [{ name: 'Task', color: '#0ea5e9' }],
  software: [
    { name: 'Feature', color: '#8b5cf6' },
    { name: 'Bug', color: '#e11d48' },
    { name: 'Task', color: '#0ea5e9' },
    { name: 'Tech debt', color: '#f97316' },
    { name: 'Research', color: '#14b8a6' },
  ],
  product: [
    { name: 'Epic', color: '#8b5cf6' },
    { name: 'Feature', color: '#0ea5e9' },
    { name: 'Feedback', color: '#eab308' },
    { name: 'Research', color: '#14b8a6' },
  ],
  content: [
    { name: 'Article', color: '#0ea5e9' },
    { name: 'Video', color: '#e11d48' },
    { name: 'Social post', color: '#8b5cf6' },
    { name: 'Idea', color: '#eab308' },
    { name: 'Review', color: '#22c55e' },
  ],
  marketing: [
    { name: 'Campaign', color: '#8b5cf6' },
    { name: 'Landing', color: '#0ea5e9' },
    { name: 'Asset', color: '#14b8a6' },
    { name: 'Email', color: '#f97316' },
    { name: 'Research', color: '#22c55e' },
  ],
  design: [
    { name: 'Screen', color: '#0ea5e9' },
    { name: 'Component', color: '#8b5cf6' },
    { name: 'Asset', color: '#14b8a6' },
    { name: 'Research', color: '#22c55e' },
  ],
  sales: [
    { name: 'Lead', color: '#0ea5e9' },
    { name: 'Deal', color: '#22c55e' },
    { name: 'Follow-up', color: '#eab308' },
    { name: 'Account', color: '#8b5cf6' },
  ],
  operations: [
    { name: 'Request', color: '#0ea5e9' },
    { name: 'Process', color: '#8b5cf6' },
    { name: 'Purchase', color: '#22c55e' },
    { name: 'Maintenance', color: '#f97316' },
  ],
  support: [
    { name: 'Incident', color: '#e11d48' },
    { name: 'Request', color: '#0ea5e9' },
    { name: 'Question', color: '#eab308' },
    { name: 'Change', color: '#8b5cf6' },
  ],
  recruiting: [
    { name: 'Candidate', color: '#0ea5e9' },
    { name: 'Onboarding', color: '#22c55e' },
    { name: 'Request', color: '#8b5cf6' },
    { name: 'Policy', color: '#6b7280' },
  ],
};

export const ISSUE_TYPE_PRESET_KEYS = Object.keys(ISSUE_TYPE_PRESETS);

export type IssueTypePreset = keyof typeof ISSUE_TYPE_PRESETS;

// The team's default agents as the member rows a new project starts with, on the
// default role.
export async function defaultAgentMembers(ownerTeam: {
  id: number;
  defaultAgentIds: number[];
}): Promise<{ userId: string; role: 'member'; roleId: number | null }[]> {
  if (ownerTeam.defaultAgentIds.length === 0) return [];
  const agents = await db
    .select({ userId: aiAgent.userId })
    .from(aiAgent)
    .where(and(eq(aiAgent.teamId, ownerTeam.id), inArray(aiAgent.id, ownerTeam.defaultAgentIds)));
  if (agents.length === 0) return [];
  const roleId = await getDefaultRoleId();
  return agents.map((agent) => ({ userId: agent.userId, role: 'member', roleId }));
}

export async function createProject(
  input: {
    key: string;
    name: string;
    description?: string;
    preset?: string;
  },
  ownerId: string,
  teamId: number,
): Promise<ProjectRow> {
  const ownerTeam = await targetTeam(ownerId, teamId);
  // What a new project starts with, set instance-wide in god mode. Read before the
  // transaction opens so the settings lookup is not part of it.
  const defaults = await getProjectDefaults();
  const agentMembers = await defaultAgentMembers(ownerTeam);
  return db.transaction(async (tx) => {
    const [row] = await tx
      .insert(project)
      .values({
        teamId: ownerTeam.id,
        key: input.key,
        name: input.name,
        description: input.description ?? '',
        mcpEnabled: defaults.mcpEnabled,
      })
      .returning();
    await joinTeamAsCreator(tx, ownerTeam.id, ownerId);
    await tx.insert(projectMember).values({ projectId: row.id, userId: ownerId, role: 'owner' });
    if (agentMembers.length > 0) {
      await tx
        .insert(projectMember)
        .values(agentMembers.map((member) => ({ projectId: row.id, ...member })));
    }
    for (const [position, column] of DEFAULT_COLUMNS.entries()) {
      await tx.insert(projectColumn).values({
        projectId: row.id,
        name: column.name,
        stateType: column.stateType,
        color: column.color,
        position,
      });
    }
    const types = ISSUE_TYPE_PRESETS[input.preset ?? 'general'] ?? ISSUE_TYPE_PRESETS.general;
    for (const [position, type] of types.entries()) {
      await tx.insert(issueType).values({
        projectId: row.id,
        name: type.name,
        color: type.color,
        isDefault: position === 0,
        position,
      });
    }
    await tx
      .insert(projectSetting)
      .values({ projectId: row.id, key: AUTO_ARCHIVE_KEY, value: DEFAULT_AUTO_ARCHIVE });
    return mapProject({
      ...row,
      teamName: ownerTeam.name,
      teamSlug: ownerTeam.slug,
      teamMcpEnabled: ownerTeam.mcpEnabled,
      workspaceId: ownerTeam.workspaceId,
    });
  });
}

// A key stored before PROJECT_KEY_PATTERN existed (e.g. "7XTR") cannot form an issue
// identifier, so it may be replaced once. A valid key does not change.
export async function updateProject(
  projectId: number,
  patch: { key?: string; name?: string; description?: string },
): Promise<ProjectRow | null> {
  const values: Partial<typeof project.$inferInsert> = {};
  if (patch.key !== undefined) {
    const current = await getProjectById(projectId);
    if (!current) return null;
    if (patch.key !== current.key) {
      if (new RegExp(PROJECT_KEY_PATTERN).test(current.key)) {
        throw new HttpError(400, 'The project key cannot change');
      }
      values.key = patch.key;
    }
  }
  if (patch.name !== undefined) values.name = patch.name;
  if (patch.description !== undefined) values.description = patch.description;
  if (Object.keys(values).length === 0) return getProjectById(projectId);
  await db.update(project).set(values).where(eq(project.id, projectId));
  return getProjectById(projectId);
}

export async function setProjectArchived(
  projectId: number,
  archived: boolean,
): Promise<ProjectRow | null> {
  await db
    .update(project)
    .set({ archivedAt: archived ? new Date() : null })
    .where(eq(project.id, projectId));
  return getProjectById(projectId);
}

// The project's feature toggles, read from the project row.
export function projectFeatures(
  row: Pick<ProjectRow, `${keyof ProjectFeatures}Enabled`>,
): ProjectFeatures {
  return {
    initiatives: row.initiativesEnabled,
    dashboards: row.dashboardsEnabled,
    documents: row.documentsEnabled,
    notes: row.notesEnabled,
    cycles: row.cyclesEnabled,
    subtasks: row.subtasksEnabled,
    checklists: row.checklistsEnabled,
    issueStats: row.issueStatsEnabled,
  };
}

// Turns the optional sections on or off. Only the supplied ones change; a section
// that is turned off keeps its rows and shows again when it is turned back on.
export async function setProjectFeatures(
  projectId: number,
  patch: Partial<ProjectFeatures>,
): Promise<ProjectRow | null> {
  const { blockedFeatures } = await getTeamLimits(await getProjectTeamId(projectId));
  const blocked = blockedFeatures.find((feature) => patch[feature]);
  if (blocked) {
    throw new HttpError(400, `${featureLabel(blocked)} are not available for this team`);
  }
  const values: Partial<typeof project.$inferInsert> = {};
  if (patch.initiatives !== undefined) values.initiativesEnabled = patch.initiatives;
  if (patch.dashboards !== undefined) values.dashboardsEnabled = patch.dashboards;
  if (patch.documents !== undefined) values.documentsEnabled = patch.documents;
  if (patch.notes !== undefined) values.notesEnabled = patch.notes;
  if (patch.cycles !== undefined) values.cyclesEnabled = patch.cycles;
  if (patch.subtasks !== undefined) values.subtasksEnabled = patch.subtasks;
  if (patch.checklists !== undefined) values.checklistsEnabled = patch.checklists;
  if (patch.issueStats !== undefined) values.issueStatsEnabled = patch.issueStats;
  if (Object.keys(values).length === 0) return getProjectById(projectId);
  await db.update(project).set(values).where(eq(project.id, projectId));
  return getProjectById(projectId);
}

// Which estimate kinds the project's issues carry, and whether its members log the
// time they spend. Held on the project row rather than in project_setting: every
// member's project payload already carries it, so a board knows whether estimates
// are on without a request of its own. Logging is independent of the time estimate:
// a team can log time without estimating first.
export interface EstimateSettings {
  points: boolean;
  time: boolean;
  logging: boolean;
}

// Turns them on or off. One turned off keeps what the issues already carry — the
// estimates, the logged entries — which show again when it is turned back on.
export async function setEstimateSettings(
  projectId: number,
  input: EstimateSettings,
): Promise<EstimateSettings | null> {
  const [row] = await db
    .update(project)
    .set({
      pointsEstimateEnabled: input.points,
      timeEstimateEnabled: input.time,
      timeLoggingEnabled: input.logging,
    })
    .where(eq(project.id, projectId))
    .returning();
  return row
    ? {
        points: row.pointsEstimateEnabled,
        time: row.timeEstimateEnabled,
        logging: row.timeLoggingEnabled,
      }
    : null;
}

// Auto-archive thresholds for a project. Stored in project_setting under
// AUTO_ARCHIVE_KEY as { completedDays, canceledDays }. Each value is the number of
// days an issue may sit inactive in a completed/canceled column before the sweep
// archives it; null disables archiving for that state group. A new project is
// created with DEFAULT_AUTO_ARCHIVE; a project with no stored row (created before
// this) keeps both null, so nothing is archived until an owner turns it on. The
// sweep reads the same key and jsonb fields directly (modules/issues/auto-archive.ts)
// — keep them in sync.
const AUTO_ARCHIVE_KEY = 'auto_archive';

const DEFAULT_AUTO_ARCHIVE = { completedDays: 28, canceledDays: 7 };

export interface AutoArchiveSettings {
  completedDays: number | null;
  canceledDays: number | null;
}

// Coerces a stored/input value to a positive integer day count, or null (disabled)
// for anything else. Guards against non-integer or non-positive thresholds.
function normalizeDays(value: unknown): number | null {
  if (value == null) return null;
  const n = Number(value);
  return Number.isInteger(n) && n > 0 ? n : null;
}

export async function getAutoArchiveSettings(projectId: number): Promise<AutoArchiveSettings> {
  const stored = await getProjectSetting<Partial<AutoArchiveSettings>>(projectId, AUTO_ARCHIVE_KEY);
  return {
    completedDays: normalizeDays(stored?.completedDays),
    canceledDays: normalizeDays(stored?.canceledDays),
  };
}

export async function setAutoArchiveSettings(
  projectId: number,
  input: { completedDays?: number | null; canceledDays?: number | null },
): Promise<AutoArchiveSettings> {
  const next: AutoArchiveSettings = {
    completedDays: normalizeDays(input.completedDays),
    canceledDays: normalizeDays(input.canceledDays),
  };
  await setProjectSetting(projectId, AUTO_ARCHIVE_KEY, next);
  return next;
}

// The subtask automations, stored in project_setting under SUBTASK_AUTOMATION_KEY.
// completeParent moves a parent into the column of its last closed subtask once
// every subtask is closed; closeSubtasks moves the still-open subtasks of an issue
// into the column the issue was closed in. Both off unless a project turns them on:
// they rewrite states nobody asked to change. Applied in modules/issues/automation.ts.
const SUBTASK_AUTOMATION_KEY = 'subtask_automation';

export interface SubtaskAutomationSettings {
  completeParent: boolean;
  closeSubtasks: boolean;
}

export async function getSubtaskAutomationSettings(
  projectId: number,
): Promise<SubtaskAutomationSettings> {
  const stored = await getProjectSetting<Partial<SubtaskAutomationSettings>>(
    projectId,
    SUBTASK_AUTOMATION_KEY,
  );
  return {
    completeParent: stored?.completeParent === true,
    closeSubtasks: stored?.closeSubtasks === true,
  };
}

export async function setSubtaskAutomationSettings(
  projectId: number,
  input: SubtaskAutomationSettings,
): Promise<SubtaskAutomationSettings> {
  const next: SubtaskAutomationSettings = {
    completeParent: input.completeParent,
    closeSubtasks: input.closeSubtasks,
  };
  await setProjectSetting(projectId, SUBTASK_AUTOMATION_KEY, next);
  return next;
}

// Deletes a project and everything scoped to it. Every project-scoped foreign key
// has ON DELETE CASCADE on project_id, so deleting the project row removes its
// columns, issue types, labels, initiatives, issues, views, dashboards, and
// actions, which in turn cascade to their own dependents (an issue's labels, field
// values/options, attachments, and activity; a custom field's values). The
// issue.column_id foreign key is NO ACTION, checked at end of statement — both the
// issues and their columns are deleted by the same cascade, so it is satisfied. The
// conversation threads of the project's agents are deleted first, since they live
// outside those cascades.
export async function deleteProject(projectId: number): Promise<void> {
  await deleteThreadsWhere({ projectId });
  // A team membership the SCIM reconciliation granted stands on the project
  // memberships it granted with it, and no group change follows the delete to re-check
  // it, so the members are read while they still exist and re-checked afterwards.
  const provisioned = await db
    .select({ teamId: project.teamId, userId: projectMember.userId })
    .from(projectMember)
    .innerJoin(project, eq(project.id, projectMember.projectId))
    .where(and(eq(projectMember.projectId, projectId), eq(projectMember.source, 'scim')));
  const assetKeys = await db.transaction(async (tx) => {
    // Serialize with the final upload quota check. A concurrent upload either
    // commits before these reads or loses its FK race and cleans its S3 object.
    await lockAttachmentStorage(tx, projectId);
    const issueAssets = await tx
      .select({ s3Key: issueAttachment.s3Key })
      .from(issueAttachment)
      .innerJoin(issue, eq(issue.id, issueAttachment.issueId))
      .where(eq(issue.projectId, projectId));
    const chatAssets = await tx
      .select({ s3Key: chatAttachment.s3Key })
      .from(chatAttachment)
      .where(eq(chatAttachment.projectId, projectId));
    const documentAssets = await tx
      .select({ s3Key: documentAsset.s3Key })
      .from(documentAsset)
      .innerJoin(projectDocument, eq(projectDocument.id, documentAsset.documentId))
      .where(eq(projectDocument.projectId, projectId));
    const initiativeAssets = await tx
      .select({ s3Key: initiativeAttachment.s3Key })
      .from(initiativeAttachment)
      .innerJoin(initiative, eq(initiative.id, initiativeAttachment.initiativeId))
      .where(eq(initiative.projectId, projectId));
    await tx.delete(project).where(eq(project.id, projectId));
    return [...issueAssets, ...chatAssets, ...documentAssets, ...initiativeAssets].map(
      (asset) => asset.s3Key,
    );
  });
  await deleteObjects(assetKeys);
  for (const { teamId, userId } of provisioned) {
    await dropUnusedTeamMembership(teamId, userId);
  }
}