⭐️⭐️⭐️ 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 {
db,
user,
session,
oauthAccessToken,
oauthRefreshToken,
teamInvite,
account,
agentSkill,
agentTool,
aiAgent,
initiative,
issue,
issueActivity,
project,
projectDashboard,
projectMember,
team,
teamMember,
teamRole,
projectView,
workspaceManager,
} from '@repo/db';
import {
and,
desc,
eq,
exists,
ilike,
inArray,
isNotNull,
isNull,
notExists,
or,
sql,
type SQL,
} from 'drizzle-orm';
import type { AnyPgColumn, PgTable } from 'drizzle-orm/pg-core';
import { accountsManagedByScim } from '@repo/auth';
import { iso } from '#shared/lib';
import { deleteAccount } from '#shared/account-deletion';
import {
defaultMemberPermissions,
fullPermissions,
normalizePermissions,
type Permissions,
} from '#shared/permissions';
import { listAllMembers, listMemberContexts } from '#modules/members/service';
import type { TeamStanding } from '#modules/teams/service';
// Data access for the instance directories (god mode): every account and every
// project on this instance. It reads across the better-auth tables (user, session,
// account) and across projects the caller is not a member of, which no
// project-scoped store may do — every function here is behind the god guard.
// The instance owner keeps god mode; an agent's account follows its agent; with SCIM the
// identity provider decides.
export type ActiveLock = 'owner' | 'agent' | 'scim';
export interface InstanceUserRow {
id: string;
name: string;
email: string;
image: string | null;
emailVerified: boolean;
// The global better-auth role: "god" for the instance owner, "user" otherwise.
role: string;
// True when this user is an AI agent's bot user. Agents are created on a
// project's AI Agents screen, not by signing up.
isAgent: boolean;
// The sign-in methods linked to the account ("credential", "google", …).
providers: string[];
projectCount: number;
// False when the account is deactivated (user.active), by god or the instance's SCIM
// provider: it cannot open a session until it is reactivated.
active: boolean;
// Why god cannot switch `active` here, or null when it can.
activeLock: ActiveLock | null;
// When the person last used the app, to the hour; null when they have not used it since
// the column was added.
lastSeenAt: string | null;
createdAt: string;
}
// A project the user can reach, with the access their membership resolves to.
export interface InstanceUserProject {
projectId: number;
projectKey: string;
projectName: string;
role: 'owner' | 'member';
roleId: number | null;
roleName: string | null;
// The effective matrix: full for an owner, the assigned role's matrix for a
// member, the default member matrix when no role is assigned.
permissions: Permissions;
// How many owners the project has. 1 on a project this user owns means deleting
// the account would leave the project with nobody who can manage it, unless it is in a
// workspace of theirs.
ownerCount: number;
// Whether the project's team is in a workspace this user owns. That workspace passes to
// the instance owner with the account, and with it what the user owned alone there.
inOwnWorkspace: boolean;
joinedAt: string;
}
export interface InstanceUserDetail extends InstanceUserRow {
projects: InstanceUserProject[];
}
type UserRow = typeof user.$inferSelect;
// The effective matrix of a membership: full for an owner, the assigned role's
// matrix for a member, the default member matrix when no role is assigned.
function resolvePermissions(role: 'owner' | 'member', rolePermissions: unknown): Permissions {
if (role === 'owner') return fullPermissions();
if (!rolePermissions) return defaultMemberPermissions();
return normalizePermissions(rolePermissions);
}
// The per-user facts that live in other tables. Collected in one grouped query
// each and joined in memory, so the user query stays a plain select.
interface UserFacts {
agents: Set<string>;
providers: Map<string, string[]>;
projectCounts: Map<string, number>;
}
async function loadUserFacts(userIds: string[]): Promise<UserFacts> {
if (userIds.length === 0) {
return {
agents: new Set(),
providers: new Map(),
projectCounts: new Map(),
};
}
const [agentRows, accountRows, memberRows] = await Promise.all([
db.select({ userId: aiAgent.userId }).from(aiAgent).where(inArray(aiAgent.userId, userIds)),
db
.select({ userId: account.userId, providerId: account.providerId })
.from(account)
.where(inArray(account.userId, userIds)),
db
.select({ userId: projectMember.userId, count: sql<number>`count(*)::int` })
.from(projectMember)
.where(inArray(projectMember.userId, userIds))
.groupBy(projectMember.userId),
]);
const providers = new Map<string, string[]>();
for (const r of accountRows) {
const list = providers.get(r.userId) ?? [];
if (!list.includes(r.providerId)) list.push(r.providerId);
providers.set(r.userId, list);
}
return {
agents: new Set(agentRows.map((r) => r.userId)),
providers,
projectCounts: new Map(memberRows.map((r) => [r.userId, r.count])),
};
}
function activeLockOf(role: string, isAgent: boolean, scim: boolean): ActiveLock | null {
if (role === 'god') return 'owner';
if (isAgent) return 'agent';
return scim ? 'scim' : null;
}
function toRow(r: UserRow, facts: UserFacts, scim: boolean): InstanceUserRow {
const lastSeen = r.lastSeenAt ?? null;
const role = r.role ?? 'user';
const isAgent = facts.agents.has(r.id);
return {
id: r.id,
name: r.name,
email: r.email,
image: r.image,
emailVerified: r.emailVerified,
role,
isAgent,
providers: facts.providers.get(r.id) ?? [],
projectCount: facts.projectCounts.get(r.id) ?? 0,
active: r.active !== false,
activeLock: activeLockOf(role, isAgent, scim),
lastSeenAt: lastSeen ? iso(lastSeen) : null,
createdAt: iso(r.createdAt),
};
}
export interface InstanceUserPage {
items: InstanceUserRow[];
// How many accounts match the filters, ignoring the page window.
total: number;
}
// Which accounts the directory lists: real people, the bot users behind AI agents,
// or both.
export const USER_KINDS = ['human', 'agent', 'all'] as const;
export type UserKind = (typeof USER_KINDS)[number];
// One page of accounts, newest first. `search` matches the name or the email;
// `kind` narrows to people or agent bot users. Both filters run in SQL, so the page
// window and the total count agree.
export async function listInstanceUsers(options: {
search?: string;
kind: UserKind;
limit: number;
offset: number;
}): Promise<InstanceUserPage> {
const term = options.search?.trim();
const isAgent = db
.select({ n: sql`1` })
.from(aiAgent)
.where(eq(aiAgent.userId, user.id));
const where = and(
term ? or(ilike(user.name, `%${term}%`), ilike(user.email, `%${term}%`)) : undefined,
options.kind === 'human'
? notExists(isAgent)
: options.kind === 'agent'
? exists(isAgent)
: undefined,
);
const [rows, totals] = await Promise.all([
db
.select()
.from(user)
.where(where)
.orderBy(desc(user.createdAt))
.limit(options.limit)
.offset(options.offset),
db
.select({ count: sql<number>`count(*)::int` })
.from(user)
.where(where),
]);
const facts = await loadUserFacts(rows.map((r) => r.id));
const scim = await accountsManagedByScim();
return { items: rows.map((r) => toRow(r, facts, scim)), total: totals[0]?.count ?? 0 };
}
// One account with the projects it can reach. Returns null for an unknown id.
export async function getInstanceUser(userId: string): Promise<InstanceUserDetail | null> {
const rows = await db.select().from(user).where(eq(user.id, userId));
const row = rows[0];
if (!row) return null;
const [facts, memberships, ownedWorkspaces] = await Promise.all([
loadUserFacts([row.id]),
db
.select({
projectId: project.id,
projectKey: project.key,
projectName: project.name,
workspaceId: team.workspaceId,
role: projectMember.role,
roleId: projectMember.roleId,
roleName: teamRole.name,
permissions: teamRole.permissions,
joinedAt: projectMember.createdAt,
})
.from(projectMember)
.innerJoin(project, eq(project.id, projectMember.projectId))
.innerJoin(team, eq(team.id, project.teamId))
.leftJoin(teamRole, eq(teamRole.id, projectMember.roleId))
.where(eq(projectMember.userId, userId))
.orderBy(project.name),
db
.select({ id: workspaceManager.workspaceId })
.from(workspaceManager)
.where(and(eq(workspaceManager.userId, userId), eq(workspaceManager.role, 'owner'))),
]);
const owned = new Set(ownedWorkspaces.map((w) => w.id));
const ownerCounts = await countOwnersByProject(memberships.map((m) => m.projectId));
const projects: InstanceUserProject[] = memberships.map((m) => {
const role = m.role === 'owner' ? 'owner' : 'member';
return {
projectId: m.projectId,
projectKey: m.projectKey,
projectName: m.projectName,
role,
roleId: m.roleId,
roleName: m.roleName,
ownerCount: ownerCounts.get(m.projectId) ?? 0,
inOwnWorkspace: owned.has(m.workspaceId),
permissions: resolvePermissions(role, m.permissions),
joinedAt: iso(m.joinedAt),
};
});
return { ...toRow(row, facts, await accountsManagedByScim()), projects };
}
// How many owners each of the given projects has, keyed by project id.
async function countOwnersByProject(projectIds: number[]): Promise<Map<number, number>> {
if (projectIds.length === 0) return new Map();
const rows = await db
.select({ projectId: projectMember.projectId, count: sql<number>`count(*)::int` })
.from(projectMember)
.where(and(eq(projectMember.role, 'owner'), inArray(projectMember.projectId, projectIds)))
.groupBy(projectMember.projectId);
return new Map(rows.map((r) => [r.projectId, r.count]));
}
// Removes the account. Every table that points at a user either cascades (its
// sessions, accounts, memberships, notifications, preferences) or sets the
// reference to null (assignee, activity actor, invites), so this is a single
// delete.
export async function deleteInstanceUser(userId: string): Promise<void> {
await deleteAccount(userId);
}
// Deactivates or reactivates an account without deleting it, through the same user.active
// a SCIM provider writes: the auth session hook and the request guard refuse it, API keys
// included, which therefore stay and work again once it is reactivated. Its sessions, MCP
// OAuth tokens and pending invites are dropped. Returns the updated user, or null if gone.
export async function setInstanceUserActive(
userId: string,
active: boolean,
): Promise<InstanceUserDetail | null> {
if (!(await getInstanceUser(userId))) return null;
// Marked first, so a sign-in that lands while the revocation runs is refused a session.
await db.update(user).set({ active }).where(eq(user.id, userId));
if (!active) {
await db.delete(session).where(eq(session.userId, userId));
await db.delete(oauthRefreshToken).where(eq(oauthRefreshToken.userId, userId));
await db.delete(oauthAccessToken).where(eq(oauthAccessToken.userId, userId));
// Their pending invites would otherwise keep the registration gate open.
await db
.delete(teamInvite)
.where(and(eq(teamInvite.invitedByUserId, userId), eq(teamInvite.status, 'pending')));
}
return getInstanceUser(userId);
}
// ── Project directory ────────────────────────────────────────────────────────
// What a project holds, counted across its dependent tables. Read for the list and
// the detail alike, so a row in the directory already carries everything.
export interface InstanceProjectCounts {
memberCount: number;
issueCount: number;
archivedIssueCount: number;
initiativeCount: number;
dashboardCount: number;
viewCount: number;
agentCount: number;
skillCount: number;
toolCount: number;
}
export interface InstanceProjectRow extends InstanceProjectCounts {
id: number;
key: string;
name: string;
description: string;
mcpEnabled: boolean;
// The most recent entry in the project's issue feed, or null when nothing has
// happened in it yet.
lastActivityAt: string | null;
createdAt: string;
}
// One member of the project, with the access their membership resolves to. The
// mirror of InstanceUserProject: same facts, read from the project's side.
export interface InstanceProjectMember {
userId: string;
name: string;
email: string;
// The handle they are mentioned by, @username. An agent's bot user carries the
// agent's handle, not one of its own.
username: string | null;
image: string | null;
isAgent: boolean;
role: 'owner' | 'member';
roleId: number | null;
roleName: string | null;
permissions: Permissions;
description: string;
timezone: string;
joinedAt: string;
}
export interface InstanceProjectDetail extends InstanceProjectRow {
members: InstanceProjectMember[];
}
export interface InstanceProjectPage {
items: InstanceProjectRow[];
// How many projects match the search, ignoring the page window.
total: number;
}
// How many rows each of the given projects can draw on in a team-scoped table (the
// skill library, the configured tools): they belong to the team that owns the project,
// so two projects of one team report the same number.
async function countByTeamOfProject(
table: PgTable,
teamIdColumn: AnyPgColumn,
projectIds: number[],
): Promise<Map<number, number>> {
const rows = await db
.select({ projectId: project.id, count: sql<number>`count(${teamIdColumn})::int` })
.from(project)
.leftJoin(table, eq(teamIdColumn, project.teamId))
.where(inArray(project.id, projectIds))
.groupBy(project.id);
return new Map(rows.map((r) => [r.projectId, r.count]));
}
const countAt = (counts: Map<number, number>, id: number) => counts.get(id) ?? 0;
// How many rows each of the given owners has in a table that points at them, keyed by
// the owner id: a project-scoped table by project, a team-scoped one by team.
async function countByOwner(
table: PgTable,
ownerIdColumn: AnyPgColumn,
ownerIds: number[],
extra?: SQL,
): Promise<Map<number, number>> {
const rows = await db
.select({ ownerId: ownerIdColumn, count: sql<number>`count(*)::int` })
.from(table)
.where(and(inArray(ownerIdColumn, ownerIds), extra))
.groupBy(ownerIdColumn);
return new Map(rows.map((r) => [r.ownerId as number, r.count]));
}
type ProjectFacts = InstanceProjectCounts & { lastActivityAt: string | null };
const EMPTY_FACTS: ProjectFacts = {
memberCount: 0,
issueCount: 0,
archivedIssueCount: 0,
initiativeCount: 0,
dashboardCount: 0,
viewCount: 0,
agentCount: 0,
skillCount: 0,
toolCount: 0,
lastActivityAt: null,
};
// The counts and the last feed entry for the given projects, each in one grouped
// query and joined in memory, so the project query stays a plain select.
async function loadProjectFacts(projectIds: number[]): Promise<(id: number) => ProjectFacts> {
if (projectIds.length === 0) return () => EMPTY_FACTS;
const [
members,
issues,
archivedIssues,
initiatives,
dashboards,
views,
agents,
skills,
tools,
activityRows,
] = await Promise.all([
countByOwner(projectMember, projectMember.projectId, projectIds),
countByOwner(issue, issue.projectId, projectIds, isNull(issue.archivedAt)),
countByOwner(issue, issue.projectId, projectIds, isNotNull(issue.archivedAt)),
countByOwner(initiative, initiative.projectId, projectIds),
countByOwner(projectDashboard, projectDashboard.projectId, projectIds),
countByOwner(projectView, projectView.projectId, projectIds),
// An agent belongs to a team and works in the projects it is a member of, so the
// count per project is its memberships, not its own rows.
countByOwner(
projectMember,
projectMember.projectId,
projectIds,
sql`exists (select 1 from ${aiAgent} where ${aiAgent.userId} = ${projectMember.userId})`,
),
countByTeamOfProject(agentSkill, agentSkill.teamId, projectIds),
countByTeamOfProject(agentTool, agentTool.teamId, projectIds),
// The feed has no project column of its own; it reaches one through its issue.
// Reduced to the latest per project below, because aggregating in SQL would
// return the max as a driver-formatted string, not a Date.
db
.select({ projectId: issue.projectId, createdAt: issueActivity.createdAt })
.from(issueActivity)
.innerJoin(issue, eq(issue.id, issueActivity.issueId))
.where(inArray(issue.projectId, projectIds)),
]);
const lastActivity = new Map<number, Date>();
for (const r of activityRows) {
const current = lastActivity.get(r.projectId);
if (!current || r.createdAt > current) lastActivity.set(r.projectId, r.createdAt);
}
return (id: number): ProjectFacts => {
const activeAt = lastActivity.get(id);
return {
memberCount: countAt(members, id),
issueCount: countAt(issues, id),
archivedIssueCount: countAt(archivedIssues, id),
initiativeCount: countAt(initiatives, id),
dashboardCount: countAt(dashboards, id),
viewCount: countAt(views, id),
agentCount: countAt(agents, id),
skillCount: countAt(skills, id),
toolCount: countAt(tools, id),
lastActivityAt: activeAt ? iso(activeAt) : null,
};
};
}
type ProjectRow = typeof project.$inferSelect;
function toProjectRow(r: ProjectRow, facts: ProjectFacts): InstanceProjectRow {
return {
id: r.id,
key: r.key,
name: r.name,
description: r.description,
mcpEnabled: r.mcpEnabled,
createdAt: iso(r.createdAt),
...facts,
};
}
// One page of projects, newest first. `search` matches the key or the name and runs
// in SQL, so the page window and the total count agree.
export async function listInstanceProjects(options: {
search?: string;
limit: number;
offset: number;
}): Promise<InstanceProjectPage> {
const term = options.search?.trim();
const where = term
? or(ilike(project.key, `%${term}%`), ilike(project.name, `%${term}%`))
: undefined;
const [rows, totals] = await Promise.all([
db
.select()
.from(project)
.where(where)
.orderBy(desc(project.createdAt))
.limit(options.limit)
.offset(options.offset),
db
.select({ count: sql<number>`count(*)::int` })
.from(project)
.where(where),
]);
const facts = await loadProjectFacts(rows.map((r) => r.id));
const items = rows.map((r) => toProjectRow(r, facts(r.id)));
return { items, total: totals[0]?.count ?? 0 };
}
// One project with its members and the access each membership resolves to. Returns
// null for an unknown id.
export async function getInstanceProject(projectId: number): Promise<InstanceProjectDetail | null> {
const rows = await db.select().from(project).where(eq(project.id, projectId));
const row = rows[0];
if (!row) return null;
const [facts, memberships, contexts] = await Promise.all([
loadProjectFacts([row.id]),
listAllMembers(projectId),
listMemberContexts(projectId),
]);
const members: InstanceProjectMember[] = memberships.flatMap((m) => {
const context = contexts.get(m.userId);
if (!context) return [];
return [
{
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,
permissions: context.permissions,
description: m.description,
timezone: m.timezone,
joinedAt: m.createdAt,
},
];
});
members.sort((a, b) => a.name.localeCompare(b.name));
return { ...toProjectRow(row, facts(row.id)), members };
}
// ── Team directory ───────────────────────────────────────────────────────────
// What a team holds, counted across the tables it owns and the projects it owns.
// Read for the list and the detail alike, so a row in the directory already carries
// everything.
export interface InstanceTeamCounts {
memberCount: number;
projectCount: number;
issueCount: number;
agentCount: number;
skillCount: number;
toolCount: number;
roleCount: number;
}
export interface InstanceTeamRow extends InstanceTeamCounts {
id: number;
name: string;
mcpEnabled: boolean;
createdAt: string;
}
export interface InstanceTeamProject {
id: number;
key: string;
name: string;
mcpEnabled: boolean;
memberCount: number;
issueCount: number;
createdAt: string;
}
// One member of the team. The role is the fixed team rank, not a project role, and
// 'agent' is the bot user of an ai_agent.
export interface InstanceTeamMember {
userId: string;
name: string;
email: string;
image: string | null;
isAgent: boolean;
role: TeamStanding;
joinedAt: string;
}
export interface InstanceTeamPage {
items: InstanceTeamRow[];
// How many teams match the search, ignoring the page window.
total: number;
}
export interface InstanceTeamProjectPage {
items: InstanceTeamProject[];
total: number;
}
export interface InstanceTeamMemberPage {
items: InstanceTeamMember[];
total: number;
}
const EMPTY_TEAM_COUNTS: InstanceTeamCounts = {
memberCount: 0,
projectCount: 0,
issueCount: 0,
agentCount: 0,
skillCount: 0,
toolCount: 0,
roleCount: 0,
};
// The counts for the given teams, each in one grouped query and joined in memory,
// so the team query stays a plain select.
async function loadTeamCounts(teamIds: number[]): Promise<(id: number) => InstanceTeamCounts> {
if (teamIds.length === 0) return () => EMPTY_TEAM_COUNTS;
const [members, projects, issues, agents, skills, tools, roles] = await Promise.all([
countByOwner(teamMember, teamMember.teamId, teamIds),
countByOwner(project, project.teamId, teamIds),
// Issues have no team column; they reach one through their project.
db
.select({ teamId: project.teamId, count: sql<number>`count(*)::int` })
.from(issue)
.innerJoin(project, eq(project.id, issue.projectId))
.where(and(inArray(project.teamId, teamIds), isNull(issue.archivedAt)))
.groupBy(project.teamId)
.then((rows) => new Map(rows.map((r) => [r.teamId, r.count]))),
countByOwner(aiAgent, aiAgent.teamId, teamIds),
countByOwner(agentSkill, agentSkill.teamId, teamIds),
countByOwner(agentTool, agentTool.teamId, teamIds),
countByOwner(teamRole, teamRole.teamId, teamIds),
]);
return (id) => ({
memberCount: countAt(members, id),
projectCount: countAt(projects, id),
issueCount: countAt(issues, id),
agentCount: countAt(agents, id),
skillCount: countAt(skills, id),
toolCount: countAt(tools, id),
roleCount: countAt(roles, id),
});
}
type TeamRow = typeof team.$inferSelect;
function toTeamRow(r: TeamRow, counts: InstanceTeamCounts): InstanceTeamRow {
return {
id: r.id,
name: r.name,
mcpEnabled: r.mcpEnabled,
createdAt: iso(r.createdAt),
...counts,
};
}
// One page of teams, newest first. `search` matches the name and runs in SQL, so the
// page window and the total count agree.
export async function listInstanceTeams(options: {
search?: string;
limit: number;
offset: number;
}): Promise<InstanceTeamPage> {
const term = options.search?.trim();
const where = term ? ilike(team.name, `%${term}%`) : undefined;
const [rows, totals] = await Promise.all([
db
.select()
.from(team)
.where(where)
.orderBy(desc(team.createdAt))
.limit(options.limit)
.offset(options.offset),
db
.select({ count: sql<number>`count(*)::int` })
.from(team)
.where(where),
]);
const counts = await loadTeamCounts(rows.map((r) => r.id));
return { items: rows.map((r) => toTeamRow(r, counts(r.id))), total: totals[0]?.count ?? 0 };
}
// One team with what it holds. Its projects and its members are their own paged
// routes, so a large team is never loaded whole. Returns null for an unknown id.
export async function getInstanceTeam(teamId: number): Promise<InstanceTeamRow | null> {
const rows = await db.select().from(team).where(eq(team.id, teamId));
const row = rows[0];
if (!row) return null;
const counts = await loadTeamCounts([row.id]);
return toTeamRow(row, counts(row.id));
}
// One page of the projects a team owns, by key. `search` matches the key or the name.
export async function listInstanceTeamProjects(
teamId: number,
options: { search?: string; limit: number; offset: number },
): Promise<InstanceTeamProjectPage> {
const term = options.search?.trim();
const where = and(
eq(project.teamId, teamId),
term ? or(ilike(project.key, `%${term}%`), ilike(project.name, `%${term}%`)) : undefined,
);
const [rows, totals] = await Promise.all([
db
.select({
id: project.id,
key: project.key,
name: project.name,
mcpEnabled: project.mcpEnabled,
createdAt: project.createdAt,
memberCount: sql<number>`count(distinct ${projectMember.userId})::int`,
issueCount: sql<number>`count(distinct ${issue.id})::int`,
})
.from(project)
.leftJoin(projectMember, eq(projectMember.projectId, project.id))
.leftJoin(issue, and(eq(issue.projectId, project.id), isNull(issue.archivedAt)))
.where(where)
.groupBy(project.id)
.orderBy(project.key)
.limit(options.limit)
.offset(options.offset),
db
.select({ count: sql<number>`count(*)::int` })
.from(project)
.where(where),
]);
return {
items: rows.map((p) => ({ ...p, createdAt: iso(p.createdAt) })),
total: totals[0]?.count ?? 0,
};
}
// One page of the team's members, people and agents alike, by name. `search` matches
// the name or the address.
export async function listInstanceTeamMembers(
teamId: number,
options: { search?: string; limit: number; offset: number },
): Promise<InstanceTeamMemberPage> {
const term = options.search?.trim();
const where = and(
eq(teamMember.teamId, teamId),
term ? or(ilike(user.name, `%${term}%`), ilike(user.email, `%${term}%`)) : undefined,
);
const [rows, totals] = await Promise.all([
db
.select({
userId: user.id,
name: user.name,
email: user.email,
image: user.image,
agentId: aiAgent.id,
role: teamMember.role,
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))
.where(where),
]);
return {
items: rows.map((m) => ({
userId: m.userId,
name: m.name,
email: m.email,
image: m.image,
isAgent: m.agentId !== null,
role: m.role as TeamStanding,
joinedAt: iso(m.joinedAt),
})),
total: totals[0]?.count ?? 0,
};
}
// Marks the account's email address as confirmed and returns the updated user.
// Returns null for an unknown id.
export async function verifyInstanceUserEmail(userId: string): Promise<InstanceUserDetail | null> {
const updated = await db
.update(user)
.set({ emailVerified: true })
.where(eq(user.id, userId))
.returning({ id: user.id });
if (updated.length === 0) return null;
return getInstanceUser(userId);
}