Open-source AI coworkers that each get a computer of their own: a browser, files and tools, with every action decided before it happens and recorded after. Bring any AG-UI agent.
import { and, eq, inArray, sql } from "drizzle-orm";
import { isConfiguredAdmin, type OpenBotRole, setRole } from "../auth/roles";
import type { Database } from "../db/client";
import {
accounts,
revokedAccess,
sessions,
userRoles,
users,
} from "../db/schema";
/**
* Everybody who has signed in, and what an administrator may do about them.
*
* People appear here by having signed in, not by being invited: a deployment's identity provider
* decides who exists, and this decides what they may do once they are here.
*/
export type Person = {
id: string;
email: string;
name: string | null;
image: string | null;
role: OpenBotRole;
/**
* Which identity providers this person has arrived through. More than one is normal for a company
* mid-migration, where the same address exists in both Entra and Okta.
*/
providers: string[];
lastSignedInAt: string | null;
/** Whether an administrator has removed them. A revoked person keeps their row and their history. */
revoked: boolean;
/**
* Whether this person's role is fixed by `INITIAL_ADMIN_EMAILS`.
*
* The screen renders this rather than recomputing it: the deployment's configuration is the floor
* that guarantees a way back in, so somebody it names cannot be demoted or removed here.
*/
configuredAdmin: boolean;
};
/** One page of people, and whether there is another. */
export type PeoplePage = {
people: Person[];
/**
* The cursor for the next page, or null at the end.
*
* Keyset rather than an offset. An offset re-reads and discards everything before it, so page 50
* is fifty times the work of page 1, and a person who signs in while somebody is paging shifts
* every later row by one and hides another person entirely.
*/
nextCursor: string | null;
};
/** What a page request may ask for. */
export type PeopleQuery = {
/** Substring of the address or the name, case-insensitively. */
search?: string;
/** From a previous page's `nextCursor`. */
cursor?: string;
limit?: number;
/** One person, by id. Used by `find`, which needs the same aggregate for one row. */
id?: string;
};
export type PeopleStore = {
/**
* One page of people, newest sign-in first.
*
* Bounded because this grows with the company. It used to take no arguments and return everybody,
* joined to their roles, accounts and sessions, on every render of the admin screen.
*/
list: (query?: PeopleQuery) => Promise<PeoplePage>;
setRole: (userId: string, role: OpenBotRole) => Promise<void>;
revoke: (userId: string, revokedBy: string) => Promise<void>;
retireOwned: (userId: string, revokedBy: string) => Promise<void>;
restore: (userId: string) => Promise<void>;
find: (userId: string) => Promise<Person | undefined>;
isRevoked: (email: string) => Promise<boolean>;
};
/** How many people a page holds when the caller does not say. */
const DEFAULT_PAGE = 50;
/**
* The most a caller may ask for in one page.
*
* A ceiling rather than a suggestion, because the limit arrives over HTTP and the whole point of
* paging is that no single request can be made to read the entire deployment.
*
* Exported so the route parses against the same ceiling the store enforces, rather than the two
* drifting apart unnoticed.
*/
export const MAX_PAGE = 200;
/** Where a page stopped. Both halves of the sort, because either alone is ambiguous. */
type Cursor = { lastSignedInAt: string | null; email: string };
function encodeCursor(cursor: Cursor): string {
return Buffer.from(JSON.stringify(cursor), "utf8").toString("base64url");
}
/**
* Read a cursor a client sent back.
*
* A malformed one is treated as no cursor rather than as an error: it means the first page, which is
* a sensible answer to a stale or hand-edited link, and there is nothing here worth refusing over.
*
* Exported for regression tests: a well-formed cursor carrying a non-date `lastSignedInAt` must
* also fall back instead of reaching `::timestamptz` in SQL and answering 500.
*/
export function decodeCursor(value: string | undefined): Cursor | undefined {
if (!value) return undefined;
try {
const parsed = JSON.parse(
Buffer.from(value, "base64url").toString("utf8"),
) as Cursor;
if (typeof parsed?.email !== "string") return undefined;
// A well-formed cursor with a non-date `lastSignedInAt` would reach
// `${cursor.lastSignedInAt}::timestamptz` in SQL and answer 500. Treat it as no cursor
// (first page), consistent with how a stale or hand-edited cursor is handled above.
if (
parsed.lastSignedInAt !== null &&
parsed.lastSignedInAt !== undefined &&
(typeof parsed.lastSignedInAt !== "string" ||
Number.isNaN(Date.parse(parsed.lastSignedInAt)))
) {
return undefined;
}
return {
email: parsed.email,
lastSignedInAt:
typeof parsed.lastSignedInAt === "string"
? parsed.lastSignedInAt
: null,
};
} catch {
return undefined;
}
}
/** So a search for `100%` finds that and not everything. */
function escapeLike(value: string): string {
return value.replace(/[\\%_]/g, (character) => `\\${character}`);
}
/** One spelling of an address, so a provider's choice of case cannot create a second person. */
function normalize(email: string): string {
return email.trim().toLowerCase();
}
/**
* What else has to be retired when somebody is removed.
*
* A seam rather than an import, because this module has no business knowing what a connector is —
* and because the list of things a person owns will grow. It exists at all because removing somebody
* used to end their sessions and leave every credential they had granted this deployment sitting in
* the vault, usable: true of their access, false of the secret, and only the first of those is what
* an administrator was told they did.
*
* Optional, so a deployment without connectors is unchanged and a test can leave it out. Absent
* means nothing extra is retired, which is the behaviour this replaced rather than a new risk.
*/
export type OwnedCredentialRetirer = (
userId: string,
by: string,
) => Promise<{ retired: number }>;
export function createPeopleStore(
database: Database,
initialAdminEmails: readonly string[],
retireOwnedCredentials?: OwnedCredentialRetirer,
): PeopleStore {
async function list(query: PeopleQuery = {}): Promise<PeoplePage> {
const limit = Math.min(Math.max(query.limit ?? DEFAULT_PAGE, 1), MAX_PAGE);
const cursor = decodeCursor(query.cursor);
const search = query.search?.trim();
const filters = [];
if (query.id) filters.push(eq(users.id, query.id));
if (search) {
// Both fields, because an administrator looking for somebody has one or the other in mind and
// should not have to know which the deployment stored.
const pattern = `%${escapeLike(search)}%`;
filters.push(
sql`(${users.email} ilike ${pattern} escape '\\' or coalesce(${users.name}, '') ilike ${pattern} escape '\\')`,
);
}
if (cursor) {
filters.push(
sql`(
(${users.lastSignedInAt} is null and (${cursor.lastSignedInAt}::timestamptz is not null or ${users.email} > ${cursor.email}))
or (${users.lastSignedInAt} is not null and ${cursor.lastSignedInAt}::timestamptz is not null and (
${users.lastSignedInAt} < ${cursor.lastSignedInAt}::timestamptz
or (${users.lastSignedInAt} = ${cursor.lastSignedInAt}::timestamptz and ${users.email} > ${cursor.email})
))
)`,
);
}
const rows = await database
.select({
id: users.id,
email: users.email,
name: users.name,
image: users.image,
/*
* Aggregated rather than joined into duplicate rows. `user_roles` is a set and `accounts`
* has one row per provider, so a plain join would return the same person once per
* combination and the screen would list them several times.
*/
roles: sql<
string[]
>`coalesce(array_agg(distinct ${userRoles.role}) filter (where ${userRoles.role} is not null), '{}')`,
providers: sql<
string[]
>`coalesce(array_agg(distinct ${accounts.providerId}) filter (where ${accounts.providerId} is not null), '{}')`,
lastSignedInAt: users.lastSignedInAt,
revoked: sql<boolean>`bool_or(${revokedAccess.email} is not null)`,
})
.from(users)
.leftJoin(userRoles, eq(userRoles.userId, users.id))
.leftJoin(accounts, eq(accounts.userId, users.id))
.leftJoin(
revokedAccess,
eq(revokedAccess.email, sql`lower(${users.email})`),
)
.where(filters.length > 0 ? and(...filters) : undefined)
.groupBy(users.id)
/*
* Most recently here first, and `NULLS LAST` on purpose.
*
* Postgres sorts nulls first on a descending order, so without it everybody who has never
* signed in floats above everybody who just did. On a deployment of any size that is the
* whole first screen given to people who have never used it.
*/
.orderBy(sql`${users.lastSignedInAt} desc nulls last`, users.email)
// One more than asked for, so "is there another page" is answered without a second count
// query over the same aggregate.
.limit(limit + 1);
const page = rows.slice(0, limit);
const last = page.at(-1);
return {
people: page.map((row) => ({
id: row.id,
email: row.email,
name: row.name,
image: row.image,
// `admin` wins, the same way the request guard reads it. Anything else is a plain user.
role: row.roles.includes("admin") ? "admin" : "user",
providers: row.providers,
lastSignedInAt: row.lastSignedInAt
? new Date(row.lastSignedInAt).toISOString()
: null,
revoked: row.revoked === true,
configuredAdmin: isConfiguredAdmin(row.email, initialAdminEmails),
})),
nextCursor:
rows.length > limit && last
? encodeCursor({
lastSignedInAt: last.lastSignedInAt
? new Date(last.lastSignedInAt).toISOString()
: null,
email: last.email,
})
: null,
};
}
/**
* One person, by id.
*
* Its own query. This used to be `(await list()).find(...)`, which ran the whole aggregate over
* every user in the deployment and filtered the result in JavaScript, and it is called twice by
* every role change and every access change.
*/
async function find(userId: string): Promise<Person | undefined> {
const { people } = await list({ id: userId, limit: 1 });
return people[0];
}
return {
list,
find,
async setRole(userId, role) {
await setRole(database, userId, role);
},
/**
* Remove somebody, and end the session they are using.
*
* Both halves matter. The deny list stops the next sign-in, and deleting the sessions stops the
* current one: without that, somebody removed keeps working until their cookie happens to
* expire, which can be days.
*/
async revoke(userId, revokedBy) {
const [user] = await database
.select({ email: users.email })
.from(users)
.where(eq(users.id, userId))
.limit(1);
if (!user) return;
await database.transaction(async (tx) => {
await tx
.insert(revokedAccess)
.values({ email: normalize(user.email), revokedBy })
.onConflictDoNothing();
await tx.delete(sessions).where(eq(sessions.userId, userId));
});
},
/*
* After `revoke`, and deliberately not inside it.
*
* Retiring a credential is a write to the vault plus an audit row, and the vault is reached
* through its own interface rather than that transaction's handle. Holding the person's removal
* open until that finishes would make an unrelated failure able to undo the deny-list row and
* the session deletion, which are the two things that must not fail to stick.
*
* So the order is: stop them getting in, then stop us holding their secret. If the second half
* throws, the first is already done and the audit trail shows a removal with no retirement
* beside it — which is the honest record of what happened, and is recoverable by removing them
* again.
*/
async retireOwned(userId, revokedBy) {
await retireOwnedCredentials?.(userId, revokedBy);
},
async restore(userId) {
const [user] = await database
.select({ email: users.email })
.from(users)
.where(eq(users.id, userId))
.limit(1);
if (!user) return;
await database
.delete(revokedAccess)
.where(eq(revokedAccess.email, normalize(user.email)));
},
async isRevoked(email) {
const rows = await database
.select({ email: revokedAccess.email })
.from(revokedAccess)
.where(inArray(revokedAccess.email, [normalize(email)]))
.limit(1);
return rows.length > 0;
},
};
}