⭐️⭐️⭐️ 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.
TypeScript agenticagileai-agentsbug-reportingbug-trackerganttissue-trackerjirajira-alternativekanbanlinearlinear-alternativeopen-sourceproduct-managementproject-managementroadmapself-hostedsprint-planningteam-collaborationtrello-alternative
import {
agentSkill,
agentTool,
aiAgent,
db,
teamWorkspaceId,
integrationCredential,
issue,
issueActivity,
project,
projectColumn,
projectMember,
team,
teamInvite,
teamMember,
teamRole,
user,
workspace,
workspaceManager,
} from '@repo/db';
import { and, desc, eq, ilike, inArray, isNotNull, isNull, or, sql } from 'drizzle-orm';
import { HttpError, iso, pgErrorCode } from '#shared/lib';
import { isReservedSlug, projectRefSql, teamRef } from './ref';
import { getLimits } from '#shared/limits';
import { fullPermissions, type Permissions } from '#shared/permissions';
import {
getMemberContext,
getMembership,
getMembershipSource,
getTeamPermissions,
listMembersPage,
matchesFilters,
type MemberFilters,
type MemberRole,
type MemberSource,
} from '#modules/members/service';
import { getStats, type StatsDto } from '#modules/analytics/service';
import { teamSkillObjectKeys } from '#modules/agents/skills/service';
import { deleteObjects } from '@repo/storage';
import {
assertMayCreateTeam,
projectWorkspaceGrant,
workspaceGrant,
workspaceGrants,
workspaceHoldsWork,
} from '#modules/workspaces/service';
import {
mergePermissions,
projectAccess,
teamAccess,
type AccessVia,
} from '#shared/workspace-roles';
// The rank a person holds in a team.
export type TeamRole = 'owner' | 'manager' | 'member';
// What a team_member row can say. An agent's bot user belongs to the team and appears
// in its member list, but its standing grants nothing: what it may do is the team role
// on its ai_agent row.
export type TeamStanding = TeamRole | 'agent';
// Owners and managers run the team, so they hold everything it can grant. Everyone
// else — a member, and an agent — holds what their project roles in it grant.
export function runsTeam(standing: TeamStanding | null): boolean {
return standing === 'owner' || standing === 'manager';
}
export interface TeamRow {
id: number;
workspaceId: number;
name: string;
slug: string | null;
// How web URLs name the team: the slug, or the id while it has none.
ref: string;
// Whether the team is reachable over MCP at all. Off closes its own resources and
// every project it owns, whichever projects it covers.
mcpEnabled: boolean;
// The caller's standing in the team, not a property of the team itself. An agent
// reading its own team is 'agent', which runs nothing.
role: TeamStanding;
// 'workspace' when the caller is not in the team and reaches it through their role
// in its workspace. Their source and joinedAt are then the team's defaults.
via: AccessVia;
// How the caller's own membership came about. 'scim' is the identity provider's, and
// is left there rather than here.
source: MemberSource;
joinedAt: string;
projectCount: number;
memberCount: number;
// How many of those members are owners: the last one cannot leave.
ownerCount: number;
// The roles the team's projects assign from, the integration credentials they run
// on, the agents that work in them, and the skills and tools those agents use.
// Counted here so the page shows them beside the section without opening it.
roleCount: number;
integrationCount: number;
agentCount: number;
skillCount: number;
toolCount: number;
createdAt: string;
}
// One member of a team, as the team detail lists them: a person, or the bot user of
// one of the team's agents. An agent carries the id and handle it is addressed by; its
// standing in the team is 'agent', and what it may do is the role each of its project
// memberships carries.
export interface TeamMemberRow {
userId: string;
name: string;
email: string;
image: string | null;
role: TeamStanding;
source: MemberSource;
agentId: number | null;
username: string | null;
joinedAt: string;
}
// A project the team owns. `isMember` is the caller's own access to it: a team
// member sees every project of the team, but only opens the ones they belong to.
export interface TeamProjectRow {
id: number;
key: string;
ref: string;
name: string;
description: string;
// Whether the team's MCP reach covers this project. Only counts while the team's
// own switch is on.
mcpEnabled: boolean;
memberCount: number;
owners: { userId: string; name: string; image: string | null }[];
isMember: boolean;
archivedAt: string | null;
createdAt: string;
}
// One page of the team's projects, with how many the caller reads in all.
export interface TeamProjectPage {
items: TeamProjectRow[];
total: number;
}
// A project as a picker reads it, plus the MCP reach the team's switches set.
export interface TeamProjectOption {
id: number;
key: string;
ref: string;
name: string;
mcpEnabled: boolean;
}
// One member of a project the team owns. The access their membership resolves to is
// not carried here: it is the matrix of the role they hold, which the reader already
// has from the team's roles.
export interface TeamProjectMemberRow {
userId: string;
name: string;
email: string;
// The handle they are mentioned by, @username. An agent's bot user carries the
// agent's handle.
username: string | null;
image: string | null;
isAgent: boolean;
role: MemberRole;
roleId: number | null;
roleName: string | null;
// What the member does in the project, and which path their membership came from —
// both needed by the panel, which edits the membership from there.
description: string;
source: MemberSource;
timezone: string;
joinedAt: string;
}
// One project of the team, opened: how it is doing, and where the reader stands in
// it. Its members are a page of their own, so a project with many of them is not
// loaded whole.
export interface TeamProjectDetail {
// The most recent entry in the project's issue feed, or null when nothing has
// happened in it yet.
lastActivityAt: string | null;
stats: StatsDto;
// The reader's access to this project, or null when they only reach it through the
// team. What they may do with its members is decided from it.
viewer: {
role: MemberRole;
via: AccessVia;
source: MemberSource;
permissions: Permissions;
} | null;
}
// One page of the team's members, with how many match the search.
export interface TeamMemberPage {
items: TeamMemberRow[];
total: number;
}
// One page of a project's members, with how many match the search.
export interface TeamProjectMemberPage {
items: TeamProjectMemberRow[];
total: number;
}
export interface TeamDetail extends TeamRow {
// What the caller may do with the resources the team holds for all its projects.
// Owners and managers run the team, so they get the full matrix; a member gets the
// permissions of their project roles in it, merged.
permissions: Permissions;
// The people who run the team, owners first. Bounded by the standing they hold, so
// it comes with the team rather than out of the paged member list.
leads: TeamLeadRow[];
}
export interface TeamLeadRow {
userId: string;
name: string;
email: string;
image: string | null;
role: 'owner' | 'manager';
}
// The caller's teams as DTOs, optionally narrowed to one: the teams they are in and
// the ones their role in a workspace reaches (workspace-roles.ts).
async function loadTeamRows(userId: string, teamId?: number): Promise<TeamRow[]> {
const grants = await workspaceGrants(userId);
const grantedTeams = [...grants.keys()];
const rows = await db
.select({
id: team.id,
workspaceId: team.workspaceId,
name: team.name,
slug: team.slug,
mcpEnabled: team.mcpEnabled,
role: teamMember.role,
source: teamMember.source,
joinedAt: teamMember.createdAt,
createdAt: team.createdAt,
})
.from(team)
.leftJoin(teamMember, and(eq(teamMember.teamId, team.id), eq(teamMember.userId, userId)))
.where(
and(
or(
isNotNull(teamMember.userId),
grantedTeams.length > 0 ? inArray(team.id, grantedTeams) : undefined,
),
teamId === undefined ? undefined : eq(team.id, teamId),
),
)
.orderBy(team.id);
if (rows.length === 0) return [];
const ids = rows.map((r) => r.id);
const [
projectCounts,
memberCounts,
roleCounts,
integrationCounts,
agentCounts,
skillCounts,
toolCounts,
] = await Promise.all([
db
.select({
teamId: project.teamId,
count: sql<number>`count(*)::int`,
joined: sql<number>`count(${projectMember.userId})::int`,
})
.from(project)
.leftJoin(
projectMember,
and(eq(projectMember.projectId, project.id), eq(projectMember.userId, userId)),
)
.where(inArray(project.teamId, ids))
.groupBy(project.teamId),
db
.select({
teamId: teamMember.teamId,
count: sql<number>`count(*)::int`,
owners: sql<number>`count(*) filter (where ${teamMember.role} = 'owner')::int`,
})
.from(teamMember)
.where(inArray(teamMember.teamId, ids))
.groupBy(teamMember.teamId),
db
.select({ teamId: teamRole.teamId, count: sql<number>`count(*)::int` })
.from(teamRole)
.where(inArray(teamRole.teamId, ids))
.groupBy(teamRole.teamId),
db
.select({ teamId: integrationCredential.teamId, count: sql<number>`count(*)::int` })
.from(integrationCredential)
.where(inArray(integrationCredential.teamId, ids))
.groupBy(integrationCredential.teamId),
db
.select({ teamId: aiAgent.teamId, count: sql<number>`count(*)::int` })
.from(aiAgent)
.where(inArray(aiAgent.teamId, ids))
.groupBy(aiAgent.teamId),
db
.select({ teamId: agentSkill.teamId, count: sql<number>`count(*)::int` })
.from(agentSkill)
.where(inArray(agentSkill.teamId, ids))
.groupBy(agentSkill.teamId),
db
.select({ teamId: agentTool.teamId, count: sql<number>`count(*)::int` })
.from(agentTool)
.where(inArray(agentTool.teamId, ids))
.groupBy(agentTool.teamId),
]);
const projects = new Map(projectCounts.map((r) => [r.teamId, r]));
const members = new Map(memberCounts.map((r) => [r.teamId, r]));
const roles = new Map(roleCounts.map((r) => [r.teamId, r.count]));
const integrations = new Map(integrationCounts.map((r) => [r.teamId, r.count]));
const agents = new Map(agentCounts.map((r) => [r.teamId, r.count]));
const skills = new Map(skillCounts.map((r) => [r.teamId, r.count]));
const tools = new Map(toolCounts.map((r) => [r.teamId, r.count]));
return rows.map((row) => {
const grant = grants.get(row.id) ?? null;
const access = teamAccess(row.role as TeamStanding | null, grant)!;
const standing = access.role;
const projectCount = projects.get(row.id);
return {
id: row.id,
workspaceId: row.workspaceId,
name: row.name,
slug: row.slug,
ref: teamRef(row),
mcpEnabled: row.mcpEnabled,
role: standing,
via: access.via,
source: (row.source as MemberSource | null) ?? 'invite',
joinedAt: iso(row.joinedAt ?? row.createdAt),
projectCount: (runsTeam(standing) || grant ? projectCount?.count : projectCount?.joined) ?? 0,
memberCount: members.get(row.id)?.count ?? 0,
ownerCount: members.get(row.id)?.owners ?? 0,
roleCount: roles.get(row.id) ?? 0,
integrationCount: integrations.get(row.id) ?? 0,
agentCount: agents.get(row.id) ?? 0,
skillCount: skills.get(row.id) ?? 0,
toolCount: tools.get(row.id) ?? 0,
createdAt: iso(row.createdAt),
};
});
}
// The teams the user belongs to. mcpOnly drops the ones with MCP switched off, so an
// MCP caller only sees the teams it can act in.
export async function listTeams(
userId: string,
opts: { mcpOnly?: boolean } = {},
): Promise<TeamRow[]> {
const rows = await loadTeamRows(userId);
return opts.mcpOnly ? rows.filter((row) => row.mcpEnabled) : rows;
}
// The team's MCP switch alone, for the guard that runs on every team-scoped MCP call.
export async function teamMcpEnabled(teamId: number): Promise<boolean> {
const [row] = await db
.select({ mcpEnabled: team.mcpEnabled })
.from(team)
.where(eq(team.id, teamId));
return row?.mcpEnabled ?? false;
}
export async function getTeamProjectDefaults(
teamId: number,
): Promise<{ defaultAgentIds: number[] }> {
const [row] = await db
.select({ defaultAgentIds: team.defaultAgentIds })
.from(team)
.where(eq(team.id, teamId));
const ids = row?.defaultAgentIds ?? [];
if (!ids.length) return { defaultAgentIds: [] };
const agents = await db
.select({ id: aiAgent.id })
.from(aiAgent)
.where(and(eq(aiAgent.teamId, teamId), inArray(aiAgent.id, ids)));
const available = new Set(agents.map((agent) => agent.id));
return { defaultAgentIds: ids.filter((id) => available.has(id)) };
}
export async function setTeamProjectDefaults(
teamId: number,
defaultAgentIds: number[],
): Promise<{ defaultAgentIds: number[] }> {
const ids = [...new Set(defaultAgentIds)];
if (ids.length) {
const agents = await db
.select({ id: aiAgent.id })
.from(aiAgent)
.where(and(eq(aiAgent.teamId, teamId), inArray(aiAgent.id, ids)));
if (agents.length !== ids.length)
throw new HttpError(400, 'Every default agent must belong to the team');
}
await db.update(team).set({ defaultAgentIds: ids }).where(eq(team.id, teamId));
return { defaultAgentIds: ids };
}
// The team's MCP settings: the switch, and which of its projects it covers. Both are
// written from the team's MCP section — a project does not open itself.
export interface TeamMcpSettings {
enabled: boolean;
projects: { projectId: number; enabled: boolean }[];
}
async function getTeamMcp(teamId: number): Promise<TeamMcpSettings> {
const [rows, projects] = await Promise.all([
db.select({ mcpEnabled: team.mcpEnabled }).from(team).where(eq(team.id, teamId)),
db
.select({ projectId: project.id, enabled: project.mcpEnabled })
.from(project)
.where(eq(project.teamId, teamId))
.orderBy(project.key),
]);
return { enabled: rows[0]?.mcpEnabled ?? false, projects };
}
// Applies a patch to the team's MCP settings and returns the result. A project of
// another team is ignored rather than written, so an id from elsewhere changes
// nothing.
export async function setTeamMcp(
teamId: number,
patch: { enabled?: boolean; projects?: { projectId: number; enabled: boolean }[] },
): Promise<TeamMcpSettings> {
if (patch.enabled !== undefined) {
await db.update(team).set({ mcpEnabled: patch.enabled }).where(eq(team.id, teamId));
}
// Written as one statement per value rather than one per project, so a whole set
// of checkboxes costs two.
for (const enabled of [true, false]) {
const ids = (patch.projects ?? []).filter((p) => p.enabled === enabled).map((p) => p.projectId);
if (ids.length === 0) continue;
await db
.update(project)
.set({ mcpEnabled: enabled })
.where(and(eq(project.teamId, teamId), inArray(project.id, ids)));
}
return getTeamMcp(teamId);
}
export async function getTeamMembership(
teamId: number,
userId: string,
): Promise<TeamStanding | null> {
const rows = await db
.select({ role: teamMember.role })
.from(teamMember)
.where(and(eq(teamMember.teamId, teamId), eq(teamMember.userId, userId)));
return rows[0] ? (rows[0].role as TeamStanding) : null;
}
// The team itself: the row the list carries, with what the caller may do with the
// resources it holds. Its members and its projects are read separately, one request
// per section of the page. Returns null when the caller is not a member of it.
export async function getTeam(teamId: number, userId: string): Promise<TeamDetail | null> {
const [row] = await loadTeamRows(userId, teamId);
if (!row) return null;
const [permissions, leads] = await Promise.all([
runsTeam(row.role)
? fullPermissions()
: Promise.all([getTeamPermissions(teamId, userId), workspaceGrant(teamId, userId)]).then(
([own, grant]) => mergePermissions(own, grant === 'owner' ? null : grant),
),
listTeamLeads(teamId),
]);
return { ...row, permissions, leads };
}
// The owners and the managers of the team, owners first, then by name. Agents are left
// out: an agent's standing grants nothing, so it runs no team.
async function listTeamLeads(teamId: number): Promise<TeamLeadRow[]> {
const rows = await db
.select({
userId: user.id,
name: user.name,
email: user.email,
image: user.image,
role: teamMember.role,
})
.from(teamMember)
.innerJoin(user, eq(user.id, teamMember.userId))
.leftJoin(aiAgent, eq(aiAgent.userId, teamMember.userId))
.where(
and(
eq(teamMember.teamId, teamId),
inArray(teamMember.role, ['owner', 'manager']),
isNull(aiAgent.id),
),
)
.orderBy(desc(eq(teamMember.role, 'owner')), user.name);
return rows.map((r) => ({ ...r, role: r.role as 'owner' | 'manager' }));
}
// One page of the team's members, people and agents alike, by name. The search and the
// window run in the database, so a team with many members is never loaded whole.
export async function listTeamMembers(
teamId: number,
options: MemberFilters & { limit: number; offset: number },
): Promise<TeamMemberPage> {
const where = and(eq(teamMember.teamId, teamId), matchesFilters(options));
const [rows, counted] = await Promise.all([
db
.select({
userId: user.id,
name: user.name,
email: user.email,
image: user.image,
role: teamMember.role,
source: teamMember.source,
agentId: aiAgent.id,
username: aiAgent.username,
joinedAt: teamMember.createdAt,
})
.from(teamMember)
.innerJoin(user, eq(user.id, teamMember.userId))
.leftJoin(aiAgent, eq(aiAgent.userId, teamMember.userId))
.where(where)
.orderBy(user.name)
.limit(options.limit)
.offset(options.offset),
db
.select({ count: sql<number>`count(*)::int` })
.from(teamMember)
.innerJoin(user, eq(user.id, teamMember.userId))
.leftJoin(aiAgent, eq(aiAgent.userId, teamMember.userId))
.where(where),
]);
return {
items: rows.map((m) => ({
userId: m.userId,
name: m.name,
email: m.email,
image: m.image,
role: m.role as TeamStanding,
source: m.source as MemberSource,
agentId: m.agentId,
username: m.username,
joinedAt: iso(m.joinedAt),
})),
total: counted[0]?.count ?? 0,
};
}
// The projects of the team a caller reads: owners, managers and anyone whose workspace
// role reaches the team see every one of them, everyone else only the ones they joined.
// The search matches the key or the name.
async function visibleTeamProjects(
teamId: number,
userId: string,
standing: TeamStanding,
search?: string,
) {
const seesAll = runsTeam(standing) || (await workspaceGrant(teamId, userId)) !== null;
const term = search?.trim();
return and(
eq(project.teamId, teamId),
seesAll
? undefined
: sql`exists (select 1 from ${projectMember} where ${projectMember.projectId} = ${project.id}
and ${projectMember.userId} = ${userId})`,
term ? or(ilike(project.key, `%${term}%`), ilike(project.name, `%${term}%`)) : undefined,
);
}
// One page of the projects the team owns, with the caller's own access to each. The
// window runs in the database, so a team with many projects is never loaded whole;
// the owners are read for the page, not for every project.
export async function listTeamProjects(
teamId: number,
userId: string,
standing: TeamStanding,
options: { search?: string; limit: number; offset: number },
): Promise<TeamProjectPage> {
const where = await visibleTeamProjects(teamId, userId, standing, options.search);
const [projects, counted] = await Promise.all([
db
.select({
id: project.id,
key: project.key,
ref: projectRefSql,
name: project.name,
description: project.description,
mcpEnabled: project.mcpEnabled,
archivedAt: project.archivedAt,
createdAt: project.createdAt,
memberCount: sql<number>`count(${projectMember.userId})::int`,
isMember: sql<boolean>`bool_or(${projectMember.userId} = ${userId})`,
})
.from(project)
.innerJoin(team, eq(team.id, project.teamId))
.leftJoin(projectMember, eq(projectMember.projectId, project.id))
.where(where)
.groupBy(project.id, team.id)
.orderBy(project.key)
.limit(options.limit)
.offset(options.offset),
db
.select({ count: sql<number>`count(*)::int` })
.from(project)
.where(where),
]);
const projectOwners = projects.length
? await db
.select({
projectId: projectMember.projectId,
userId: user.id,
name: user.name,
image: user.image,
})
.from(projectMember)
.innerJoin(user, eq(user.id, projectMember.userId))
.where(
and(
inArray(
projectMember.projectId,
projects.map((p) => p.id),
),
eq(projectMember.role, 'owner'),
),
)
.orderBy(user.name)
: [];
const ownersByProject = new Map<number, TeamProjectRow['owners']>();
for (const o of projectOwners) {
const owners = ownersByProject.get(o.projectId) ?? [];
owners.push({ userId: o.userId, name: o.name, image: o.image });
ownersByProject.set(o.projectId, owners);
}
return {
items: projects.map((p) => ({
id: p.id,
key: p.key,
ref: p.ref,
name: p.name,
description: p.description,
mcpEnabled: p.mcpEnabled,
memberCount: p.memberCount,
owners: ownersByProject.get(p.id) ?? [],
isMember: p.isMember ?? false,
archivedAt: p.archivedAt ? iso(p.archivedAt) : null,
createdAt: iso(p.createdAt),
})),
total: counted[0]?.count ?? 0,
};
}
// Every project the caller reads, as id, key, name and MCP reach: the picker on an
// agent and the team's MCP switches, which need them all rather than a page.
export async function listTeamProjectOptions(
teamId: number,
userId: string,
standing: TeamStanding,
): Promise<TeamProjectOption[]> {
return db
.select({
id: project.id,
key: project.key,
ref: projectRefSql,
name: project.name,
mcpEnabled: project.mcpEnabled,
})
.from(project)
.innerJoin(team, eq(team.id, project.teamId))
.where(await visibleTeamProjects(teamId, userId, standing))
.orderBy(project.key);
}
// When the project's issue feed last moved. Read as the newest row rather than a
// max(), which the driver returns as a formatted string instead of a Date.
async function lastActivityAt(projectId: number): Promise<string | null> {
const rows = await db
.select({ createdAt: issueActivity.createdAt })
.from(issueActivity)
.innerJoin(issue, eq(issue.id, issueActivity.issueId))
.where(eq(issue.projectId, projectId))
.orderBy(desc(issueActivity.createdAt))
.limit(1);
return rows[0] ? iso(rows[0].createdAt) : null;
}
// Whether the project is one the team owns. The team-scoped project routes check it
// so a project of another team answers 404 rather than being acted on.
export async function teamOwnsProject(teamId: number, projectId: number): Promise<boolean> {
const rows = await db
.select({ id: project.id })
.from(project)
.where(and(eq(project.id, projectId), eq(project.teamId, teamId)));
return rows.length > 0;
}
// One project the team owns, with its issue stats and the reader's standing in it.
// Returns null when the project is not owned by the team, and when the caller
// neither runs the team nor belongs to the project, so the route answers 404 in both
// cases.
export async function getTeamProject(
teamId: number,
projectId: number,
userId: string,
standing: TeamStanding,
): Promise<TeamProjectDetail | null> {
if (!(await teamOwnsProject(teamId, projectId))) return null;
const viewer = projectAccess(
await getMemberContext(projectId, userId),
await projectWorkspaceGrant(projectId, userId),
);
if (!runsTeam(standing) && !viewer) return null;
const [activity, stats, source] = await Promise.all([
lastActivityAt(projectId),
getStats(projectId),
getMembershipSource(projectId, userId),
]);
return {
lastActivityAt: activity,
stats,
viewer: viewer
? {
role: viewer.role,
via: viewer.via,
source: source ?? 'invite',
permissions: viewer.permissions,
}
: null,
};
}
// One page of a project's members, owners first and the rest newest membership
// first: who runs the project is the first thing the list has to answer. Returns
// null under the same two conditions getTeamProject does.
export async function listTeamProjectMembers(
teamId: number,
projectId: number,
userId: string,
standing: TeamStanding,
options: MemberFilters & { limit: number; offset: number },
): Promise<TeamProjectMemberPage | null> {
if (!(await teamOwnsProject(teamId, projectId))) return null;
if (
!runsTeam(standing) &&
!(await getMembership(projectId, userId)) &&
!(await projectWorkspaceGrant(projectId, userId))
) {
return null;
}
const { items, total } = await listMembersPage(projectId, { ...options, order: 'owners' });
return {
items: items.map((m) => ({
userId: m.userId,
name: m.name,
email: m.email,
username: m.username,
image: m.image,
isAgent: m.isAgent,
role: m.role,
roleId: m.roleId,
roleName: m.roleName,
description: m.description,
source: m.source,
timezone: m.timezone,
joinedAt: m.createdAt,
})),
total,
};
}
type Transaction = Parameters<Parameters<typeof db.transaction>[0]>[0];
export async function insertOwnedTeam(
tx: Transaction,
workspaceId: number,
name: string,
ownerId: string,
slug: string | null = null,
) {
const [row] = await tx.insert(team).values({ workspaceId, name, slug }).returning();
const [membership] = await tx
.insert(teamMember)
.values({ teamId: row.id, userId: ownerId, role: 'owner' })
.returning();
return { team: row, membership };
}
// The people across the workspace's teams, which is what a seat ceiling counts. An
// agent's bot user sits in a member list but takes no seat.
export async function listSeatHolderIds(workspaceId: number): Promise<string[]> {
const rows = await db
.selectDistinct({ userId: teamMember.userId })
.from(teamMember)
.innerJoin(team, eq(team.id, teamMember.teamId))
.where(and(eq(team.workspaceId, workspaceId), sql`${teamMember.role} <> 'agent'`));
return rows.map((row) => row.userId);
}
// Refuses to add this person to the team when that takes a seat the workspace does not
// have. Somebody already in another team of the workspace holds a seat.
export async function assertSeatFree(teamId: number, userId: string): Promise<void> {
const workspaceId = await teamWorkspaceId(teamId, db);
const { maxSeats } = await getLimits(workspaceId);
if (maxSeats === 0) return;
const holders = await listSeatHolderIds(workspaceId);
if (!holders.includes(userId) && holders.length >= maxSeats) {
throw new HttpError(409, `The workspace has no free seats out of ${maxSeats}`);
}
}
export async function createTeam(
name: string,
slug: string,
ownerId: string,
workspaceId: number,
): Promise<TeamRow> {
await assertMayCreateTeam(workspaceId, ownerId);
const { maxTeams } = await getLimits(workspaceId);
if (maxTeams > 0 && (await db.$count(team, eq(team.workspaceId, workspaceId))) >= maxTeams) {
throw new HttpError(409, `The workspace already has ${maxTeams} teams`);
}
assertSlugAllowed(slug);
return withSlugConflict(() =>
db.transaction(async (tx) => {
const { team: row, membership } = await insertOwnedTeam(tx, workspaceId, name, ownerId, slug);
return {
id: row.id,
workspaceId: row.workspaceId,
name: row.name,
slug: row.slug,
ref: teamRef(row),
mcpEnabled: row.mcpEnabled,
role: 'owner',
via: 'member',
source: 'invite',
joinedAt: iso(membership.createdAt),
projectCount: 0,
memberCount: 1,
ownerCount: 1,
roleCount: 1,
integrationCount: 0,
agentCount: 0,
skillCount: 0,
toolCount: 0,
createdAt: iso(row.createdAt),
};
}),
);
}
function assertSlugAllowed(slug: string | undefined): void {
if (slug && isReservedSlug(slug)) {
throw new HttpError(400, `'${slug}' is reserved and cannot be a team slug`);
}
}
// slug is the only unique column of team, so a unique violation names it.
async function withSlugConflict<T>(write: () => Promise<T>): Promise<T> {
try {
return await write();
} catch (err) {
if (pgErrorCode(err) === '23505')
throw new HttpError(409, 'Another team already uses this slug');
throw err;
}
}
export async function updateTeam(
teamId: number,
changes: { name?: string; slug?: string },
userId: string,
): Promise<TeamRow> {
assertSlugAllowed(changes.slug);
if (changes.slug === undefined) {
const [current] = await db.select({ slug: team.slug }).from(team).where(eq(team.id, teamId));
if (current?.slug === null) {
throw new HttpError(400, "Set the team's slug before changing it");
}
}
// Drizzle skips undefined values, and throws when none are left to set.
if (changes.name !== undefined || changes.slug !== undefined) {
await withSlugConflict(() => db.update(team).set(changes).where(eq(team.id, teamId)));
}
const [row] = await loadTeamRows(userId, teamId);
return row;
}
// A team holding a project or an AI agent is kept: those are people's work, and the
// delete cascades to everything the team owns.
export async function deleteTeam(teamId: number): Promise<void> {
const skillKeys = await teamSkillObjectKeys(teamId);
const deleted = await db
.delete(team)
.where(
and(
eq(team.id, teamId),
sql`not exists (select 1 from ${project} where ${project.teamId} = ${team.id})`,
sql`not exists (select 1 from ${aiAgent} where ${aiAgent.teamId} = ${team.id})`,
),
)
.returning({ id: team.id });
if (deleted.length === 0) {
throw new HttpError(409, 'Delete the projects and AI agents of the team first');
}
await deleteObjects(skillKeys);
}
async function membershipInTransaction(
tx: Transaction,
teamId: number,
userId: string,
): Promise<TeamStanding | null> {
const [row] = await tx
.select({ role: teamMember.role })
.from(teamMember)
.where(and(eq(teamMember.teamId, teamId), eq(teamMember.userId, userId)));
return row ? (row.role as TeamStanding) : null;
}
// The actor's standing raised to their workspace grant (getTeamAccess), read inside the
// transaction that locks the team.
async function actorStandingInTransaction(
tx: Transaction,
teamId: number,
userId: string,
): Promise<TeamStanding | null> {
const standing = await membershipInTransaction(tx, teamId, userId);
return teamAccess(standing, await workspaceGrant(teamId, userId))?.role ?? null;
}
// The last owner row is kept even against the workspace owner, who acts as an owner
// without one.
async function assertKeepsAnOwner(tx: Transaction, teamId: number): Promise<void> {
const owners = await tx.$count(
teamMember,
and(eq(teamMember.teamId, teamId), eq(teamMember.role, 'owner')),
);
if (owners === 1) throw new HttpError(409, 'A team must keep at least one owner');
}
// Changes what a member ranks as in the team. An agent's standing comes from its
// agent settings, and nobody sets their own rank. Only an owner grants the owner rank
// or changes what another owner holds.
export async function setTeamMemberRole(
teamId: number,
actor: { userId: string; role: TeamStanding },
userId: string,
role: TeamRole,
): Promise<void> {
await db.transaction(async (tx) => {
await tx.select({ id: team.id }).from(team).where(eq(team.id, teamId)).for('update');
const actorRole = await actorStandingInTransaction(tx, teamId, actor.userId);
if (!runsTeam(actorRole))
throw new HttpError(403, 'Only a team owner or manager can change ranks');
if (userId === actor.userId) throw new HttpError(409, 'You cannot change your own rank');
const current = await membershipInTransaction(tx, teamId, userId);
if (!current) throw new HttpError(404, 'Member not found');
if (current === 'agent')
throw new HttpError(409, "An agent's rank comes from its agent settings");
if (actorRole !== 'owner' && (role === 'owner' || current === 'owner')) {
throw new HttpError(403, 'Only a team owner can grant or take the owner rank');
}
if (current === 'owner' && role !== 'owner') await assertKeepsAnOwner(tx, teamId);
await tx
.update(teamMember)
.set({ role })
.where(and(eq(teamMember.teamId, teamId), eq(teamMember.userId, userId)));
});
}
// Refuses to end a membership that would leave a project of the team without an owner:
// ending it takes the member's project memberships with it, and the project's own member
// routes refuse such a removal for the same reason. The other owner has to be named
// first, from the project's member list.
async function assertLeavesNoProjectOwnerless(
tx: Transaction,
teamId: number,
userId: string,
subject: 'You' | 'They',
): Promise<void> {
const soleOwned = await tx
.select({ key: project.key })
.from(projectMember)
.innerJoin(project, eq(project.id, projectMember.projectId))
.where(
and(
eq(project.teamId, teamId),
eq(projectMember.userId, userId),
eq(projectMember.role, 'owner'),
sql`(select count(*) from ${projectMember} o where o.project_id = ${project.id} and o.role = 'owner') = 1`,
),
)
.orderBy(project.key);
if (soleOwned.length === 0) return;
const keys = soleOwned.map((r) => r.key).join(', ');
throw new HttpError(
409,
`${subject} are the only owner of ${keys}. Give each one another owner first.`,
);
}
// Ends a team membership: the member leaves the team and every project it owns. What
// they already did in those projects stays — issues keep their assignee and their
// author.
async function dropTeamMembership(tx: Transaction, teamId: number, userId: string): Promise<void> {
const teamProjects = tx
.select({ id: project.id })
.from(project)
.where(eq(project.teamId, teamId));
await tx
.delete(projectMember)
.where(and(eq(projectMember.userId, userId), inArray(projectMember.projectId, teamProjects)));
// A pending invite this member created outlives them otherwise, keeping the
// registration gate open on their behalf after they were removed.
await tx
.delete(teamInvite)
.where(
and(
eq(teamInvite.teamId, teamId),
eq(teamInvite.invitedByUserId, userId),
eq(teamInvite.status, 'pending'),
),
);
// A column cannot keep assigning issues to someone who no longer belongs to the
// project, the same rule remove_member follows.
await tx
.update(projectColumn)
.set({ autoAssignUserId: null })
.where(
and(
eq(projectColumn.autoAssignUserId, userId),
inArray(projectColumn.projectId, teamProjects),
),
);
await tx
.delete(teamMember)
.where(and(eq(teamMember.teamId, teamId), eq(teamMember.userId, userId)));
}
// A membership the SCIM group reconciliation owns ends at the identity provider: the
// next sync would put it back, and dropping it here would take the project memberships
// that same sync granted with it. Only the plain member rows it writes — a rank raised
// by hand is the team's, and the reconciliation leaves those alone too.
async function assertNotProvisioned(
tx: Transaction,
teamId: number,
userId: string,
): Promise<void> {
const [row] = await tx
.select({ role: teamMember.role, source: teamMember.source })
.from(teamMember)
.where(and(eq(teamMember.teamId, teamId), eq(teamMember.userId, userId)));
if (row?.source === 'scim' && row.role === 'member') {
throw new HttpError(409, 'This membership is managed by SCIM');
}
}
// Removes a member from the team. Nobody removes themselves — that is leaving the
// team.
export async function removeTeamMember(
teamId: number,
actorId: string,
userId: string,
): Promise<void> {
await db.transaction(async (tx) => {
await tx.select({ id: team.id }).from(team).where(eq(team.id, teamId)).for('update');
if ((await actorStandingInTransaction(tx, teamId, actorId)) !== 'owner') {
throw new HttpError(403, 'Only a team owner can remove members');
}
if (userId === actorId) throw new HttpError(409, 'Leave the team instead of removing yourself');
const current = await membershipInTransaction(tx, teamId, userId);
if (!current) throw new HttpError(404, 'Member not found');
if (current === 'agent')
throw new HttpError(409, 'An agent is removed with its agent settings');
if (current === 'owner') await assertKeepsAnOwner(tx, teamId);
await assertNotProvisioned(tx, teamId, userId);
await assertLeavesNoProjectOwnerless(tx, teamId, userId, 'They');
await dropTeamMembership(tx, teamId, userId);
});
}
// Drops the caller's own membership, with their access to the team's projects. The
// team keeps the projects themselves. The last owner cannot leave: a team without an
// owner has nobody who can rename it. Neither can the only owner of one of its
// projects, for the same reason one project down. An agent cannot either: its standing
// is written with the agent and nothing puts it back, so leaving would strand it.
export async function leaveTeam(teamId: number, userId: string): Promise<void> {
await db.transaction(async (tx) => {
await tx.select({ id: team.id }).from(team).where(eq(team.id, teamId)).for('update');
const role = await membershipInTransaction(tx, teamId, userId);
if (!role) throw new HttpError(404, 'Member not found');
if (role === 'agent') throw new HttpError(409, 'An agent is removed with its agent settings');
if (role === 'owner') {
const [{ count }] = await tx
.select({ count: sql<number>`count(*)::int` })
.from(teamMember)
.where(and(eq(teamMember.teamId, teamId), eq(teamMember.role, 'owner')));
if (count === 1) throw new HttpError(409, 'The last owner cannot leave the team');
}
await assertNotProvisioned(tx, teamId, userId);
await assertLeavesNoProjectOwnerless(tx, teamId, userId, 'You');
await dropTeamMembership(tx, teamId, userId);
});
}
// The workspaces an account owns, as it is deleted. One holding no project and no AI
// agent goes with the account, teams and all. Any other passes to the instance owner,
// with the teams and projects the account owned alone in it, so its people keep a
// workspace somebody manages.
export async function releaseOwnedWorkspaces(userId: string): Promise<void> {
const owned = await db
.select({ workspaceId: workspaceManager.workspaceId })
.from(workspaceManager)
.where(and(eq(workspaceManager.userId, userId), eq(workspaceManager.role, 'owner')));
if (owned.length === 0) return;
const [instanceOwner] = await db
.select({ id: user.id })
.from(user)
.where(eq(user.role, 'god'))
.orderBy(user.createdAt)
.limit(1);
const successor = instanceOwner?.id;
for (const { workspaceId } of owned) {
if (!(await workspaceHoldsWork(workspaceId))) {
await db.delete(workspace).where(eq(workspace.id, workspaceId));
continue;
}
if (!successor || successor === userId) continue;
await db.transaction(async (tx) => {
await tx
.delete(workspaceManager)
.where(
and(eq(workspaceManager.workspaceId, workspaceId), eq(workspaceManager.userId, userId)),
);
await tx
.insert(workspaceManager)
.values({ workspaceId, userId: successor, role: 'owner' })
.onConflictDoUpdate({
target: [workspaceManager.workspaceId, workspaceManager.userId],
set: { role: 'owner' },
});
});
await dropWorkspaceMemberships(workspaceId, userId);
}
}
// Takes a person out of every team of the workspace, and with that out of all of its
// projects: what the workspace's identity provider does when it deprovisions them. A
// team or project they own alone passes to the workspace owner, so none is left with
// nobody who can manage it.
export async function dropWorkspaceMemberships(workspaceId: number, userId: string): Promise<void> {
await db.transaction(async (tx) => {
const teams = await tx
.select({ id: team.id })
.from(team)
.innerJoin(teamMember, eq(teamMember.teamId, team.id))
.where(and(eq(team.workspaceId, workspaceId), eq(teamMember.userId, userId)))
.orderBy(team.id)
.for('update');
const [owner] = await tx
.select({ userId: workspaceManager.userId })
.from(workspaceManager)
.where(
and(eq(workspaceManager.workspaceId, workspaceId), eq(workspaceManager.role, 'owner')),
);
for (const { id: teamId } of teams) {
if (owner && owner.userId !== userId) {
await handOverSoleOwnership(tx, teamId, userId, owner.userId);
}
await dropTeamMembership(tx, teamId, userId);
}
});
}
// Makes the successor the owner of what the person owns alone in the team: the team
// itself and any of its projects. A project membership stands on a team one, so the
// successor joins the team first.
async function handOverSoleOwnership(
tx: Transaction,
teamId: number,
userId: string,
successorId: string,
): Promise<void> {
const owners = await tx
.select({ userId: teamMember.userId })
.from(teamMember)
.where(and(eq(teamMember.teamId, teamId), eq(teamMember.role, 'owner')));
const ownsTeamAlone = owners.length === 1 && owners[0]!.userId === userId;
const soleProjects = await tx
.select({ id: project.id })
.from(projectMember)
.innerJoin(project, eq(project.id, projectMember.projectId))
.where(
and(
eq(project.teamId, teamId),
eq(projectMember.userId, userId),
eq(projectMember.role, 'owner'),
sql`(select count(*) from ${projectMember} o where o.project_id = ${project.id} and o.role = 'owner') = 1`,
),
);
if (!ownsTeamAlone && soleProjects.length === 0) return;
await tx
.insert(teamMember)
.values({ teamId, userId: successorId, role: ownsTeamAlone ? 'owner' : 'member' })
.onConflictDoNothing();
if (ownsTeamAlone) {
await tx
.update(teamMember)
.set({ role: 'owner' })
.where(and(eq(teamMember.teamId, teamId), eq(teamMember.userId, successorId)));
}
if (soleProjects.length > 0) {
// Not a SCIM row any more: the next sync must not take the ownership back.
await tx
.insert(projectMember)
.values(
soleProjects.map(({ id }) => ({
projectId: id,
userId: successorId,
role: 'owner',
roleId: null,
})),
)
.onConflictDoUpdate({
target: [projectMember.projectId, projectMember.userId],
set: { role: 'owner', roleId: null, source: 'invite' },
});
}
}