apps/diffinery/src/lib/directory.ts at claude/graphql-issues · codician/diffinery · Diffinery

1 import type { Env } from "../env.js";
2 import type { RepoMeta, Role } from "../repo/types.js";
3 import { ROLE_LEVEL } from "../repo/types.js";
4 import { HttpError, RESERVED_LOGINS, isValidLogin, isValidName, newId, now, randomAlnum, randomToken, sha256 } from "./util.js";
5 import { mailForAudit, tokenCreated } from "../mail/notify.js";
6 import { enqueueMail } from "../mail/send.js";
7 import { archivePrefix, deletePrefixes } from "./storage-keys.js";
8 import { inviteProof } from "../auth/invite-proof.js";
9 import { checkRepositoryQuota } from "./quotas.js";
10 import { blockSubjects, isBlocked } from "./moderation.js";
11 import { agentRepoId } from "./agent-setup.js";
12
13 export interface Account {
14   id: string;
15   login: string;
16   kind: "user" | "org" | "agent";
17   name: string | null;
18   email: string | null;
19   status: "active" | "pending" | "disabled";
20   site_admin: number;
21   owner_id: string | null;
22   agent_policy: string | null;
23   /** Profile picture version (R2 avatars/<id>/<version>/<size>.webp), null for initials. */
24   avatar_version: number | null;
25   /** "light", "dark" or null (follow the system). */
26   appearance: string | null;
27   /** Turn on auto-merge for the pull requests it opens: a merge method or "default"; null (or absent before migration 0041) when off. */
28   auto_merge?: string | null;
29   /** Organizations: the role every member gets on its repositories ("none", "read", "write" or "admin"). */
30   base_role: string | null;
31   /** Set while a site administrator suspends the person (lib/moderation.ts); status is then `disabled`. */
32   suspended_at?: string | null;
33   suspended_by?: string | null;
34   suspended_reason?: string | null;
35   created_at: string;
36   /** When the account was deleted (accounts/lifecycle.ts); the row stays under a placeholder login. */
37   deleted_at?: string | null;
38 }
39
40 export interface Repo {
41   id: string;
42   owner_id: string;
43   owner_login: string;
44   owner_kind: "user" | "org" | "agent";
45   name: string;
46   description: string;
47   visibility: "private" | "public";
48   default_branch: string;
49   archived: number;
50   created_at: string;
51   updated_at: string;
52   pushed_at: string | null;
53   /** JSON array of topic names (GitHub's repository topics). */
54   topics?: string;
55   /** Project website (GitHub's `homepage`). */
56   homepage?: string;
57   /** Set while the owner is suspended (lib/moderation.ts): the repository is hidden from everyone but site administrators. */
58   owner_suspended_at?: string | null;
59 }
60
61 export type TokenScope = "read" | "write" | "admin";
62
63 export type BaseRole = "none" | "read" | "write" | "admin";
64 export const BASE_ROLES: readonly BaseRole[] = ["none", "read", "write", "admin"];
65
66 /** An organization's base permission; unset means read, the default for new organizations. */
67 export function baseRole(org: Pick<Account, "base_role">): BaseRole {
68   return BASE_ROLES.find((r) => r === org.base_role) ?? "read";
69 }
70
71 /** Repositories are public or private; GitHub's "internal" is refused like any other value. */
72 export function repoVisibility(v: unknown): Repo["visibility"] | undefined {
73   if (v === undefined || v === null || v === "") return undefined;
74   if (v === "public" || v === "private") return v;
75   throw new HttpError(422, "visibility must be public or private", { errors: [{ resource: "Repository", code: "invalid", field: "visibility" }] });
76 }
77
78 /** Data access for the global directory in D1. */
79 /** Invitations one person (not a site admin) may send in 24 hours. */
80 export const MAX_INVITES_PER_DAY = 50;
81
82 /** What adding someone did: added them at once, invited them by username, or sent an invitation link to an email address. */
83 export type AddResult = { added: Account } | { invitedUser: Account; id: string; renewed: boolean } | { invited: { email: string; url: string } };
84
85 /** A person's own pending invitations (binds: now, account id, account email). */
86 const MINE = "i.accepted_at IS NULL AND i.expires_at > ? AND (i.org_id IS NOT NULL OR i.repo_id IS NOT NULL) AND (i.account_id = ? OR (i.account_id IS NULL AND i.email = ?))";
87
88 /** One account's grants on one repository, as Directory.grantsFor reads them. */
89 export type RepoGrants = { repoId: string; accountId: string; access: Role | null; memberRole: string | null; baseRole: string | null };
90
91 export class Directory {
92   constructor(private env: Env) {}
93
94   private get db() {
95     return this.env.DB;
96   }
97
98   // ---------------------------------------------------------------- accounts
99
100   async account(id: string): Promise<Account | null> {
101     return this.db.prepare("SELECT * FROM accounts WHERE id = ?").bind(id).first<Account>();
102   }
103
104   async accountByLogin(login: string): Promise<Account | null> {
105     return this.db.prepare("SELECT * FROM accounts WHERE login = ?").bind(login).first<Account>();
106   }
107
108   async accountByEmail(email: string): Promise<Account | null> {
109     return this.db.prepare("SELECT * FROM accounts WHERE email = ? AND kind = 'user'").bind(email).first<Account>();
110   }
111
112   /**
113    * Only a verified email address makes a bootstrap administrator. Logins on the list are
114    * ignored: whoever creates an account picks its login, so a login proves nothing.
115    */
116   private isBootstrapAdmin(email: string): boolean {
117     const list = (this.env.BOOTSTRAP_ADMINS ?? "").split(",").map((s) => s.trim().toLowerCase()).filter(Boolean);
118     return list.includes(email.toLowerCase());
119   }
120
121   private async uniqueLogin(base: string): Promise<string> {
122     let candidate = base.replace(/[^A-Za-z0-9-]/g, "-").replace(/^-+|-+$/g, "").slice(0, 39) || "user";
123     for (let i = 0; i < 50; i++) {
124       const tryLogin = i === 0 ? candidate : `${candidate}-${i + 1}`;
125       if (!RESERVED_LOGINS.has(tryLogin.toLowerCase()) && !(await this.loginTaken(tryLogin))) return tryLogin;
126     }
127     candidate = `${candidate}-${randomToken(3)}`;
128     return candidate;
129   }
130
131   /**
132    * When a deleted account's username becomes free again (accounts/lifecycle.ts LOGIN_COOLDOWN_DAYS), or null
133    * when it isn't cooling down.
134    */
135   async loginCooldown(login: string): Promise<string | null> {
136     const r = await this.db.prepare("SELECT available_at FROM released_logins WHERE login = ? AND available_at > ?").bind(login, now()).first<{ available_at: string }>();
137     return r?.available_at ?? null;
138   }
139
140   /**
141    * Whether old URLs at this login still lead somewhere else: a renamed account's profile, or
142    * repositories and images that moved away with a transfer, a rename or before their organization
143    * was deleted. Whoever took the login would take over those redirects and could serve their own
144    * code at the old clone URLs, so it stays retired. `forAccountId` may take back its own old login.
145    */
146   async loginRetired(login: string, forAccountId = ""): Promise<boolean> {
147     const r = await this.db
148       .prepare(
149         `SELECT 1 FROM login_redirects WHERE login = ?1 AND account_id != ?2
150          UNION ALL SELECT 1 FROM repo_redirects r JOIN repos p ON p.id = r.repo_id WHERE r.owner_login = ?1 AND p.owner_id != ?2
151          UNION ALL SELECT 1 FROM package_redirects r JOIN packages p ON p.id = r.package_id WHERE r.owner_login = ?1 AND p.owner_id != ?2
152          LIMIT 1`,
153       )
154       .bind(login, forAccountId)
155       .first();
156     return !!r;
157   }
158
159   /** Whether an account has this login, a deleted account had it too recently for anyone to take it, or old URLs at it lead elsewhere. */
160   async loginTaken(login: string): Promise<boolean> {
161     return !!(await this.accountByLogin(login)) || !!(await this.loginCooldown(login)) || (await this.loginRetired(login));
162   }
163
164   /** Refuse a login that a deleted account gave up less than 90 days ago, or whose old URLs lead to another account's work. */
165   private async checkCooldown(login: string, what = "This username", forAccountId = ""): Promise<void> {
166     const until = await this.loginCooldown(login);
167     if (until) throw new HttpError(422, `${what} belonged to an account that was deleted recently; it can be used again from ${until.slice(0, 10)}`);
168     if (await this.loginRetired(login, forAccountId)) throw new HttpError(422, `${what} is retired: links to it still lead to repositories or a profile that moved elsewhere`);
169   }
170
171   /**
172    * Find or create the user behind a verified email (Access recovery, invite links, open sign-up,
173    * dev sign-in). A new account waits for an invite unless it has one, is a bootstrap
174    * administrator or `hints.active` says so (open sign-up).
175    */
176   async userForEmail(email: string, hints: { name?: string; login?: string; id?: string; active?: boolean } = {}): Promise<Account> {
177     const existing = await this.accountByEmail(email);
178     if (existing) {
179       if (existing.status === "pending" && this.isBootstrapAdmin(email)) {
180         await this.db.prepare("UPDATE accounts SET status = 'active', site_admin = 1 WHERE id = ?").bind(existing.id).run();
181         await this.audit({ actorId: existing.id, actorLogin: existing.login, action: "site_admin.grant", target: existing.login, detail: { via: "bootstrap" } });
182         return { ...existing, status: "active", site_admin: 1 };
183       }
184       return existing;
185     }
186     const admin = this.isBootstrapAdmin(email);
187     const login = await this.uniqueLogin(hints.login ?? email.split("@")[0]!);
188     const id = hints.id ?? newId();
189     const hasInvite = await this.db.prepare("SELECT 1 FROM invites WHERE email = ? AND accepted_at IS NULL AND expires_at > ?").bind(email, now()).first();
190     await this.db
191       .prepare("INSERT INTO accounts (id, login, kind, name, email, status, site_admin, created_at) VALUES (?, ?, 'user', ?, ?, ?, ?, ?)")
192       .bind(id, login, hints.name ?? null, email, admin || hasInvite || hints.active ? "active" : "pending", admin ? 1 : 0, now())
193       .run();
194     if (admin) await this.audit({ actorId: id, actorLogin: login, action: "site_admin.grant", target: login, detail: { via: "bootstrap" } });
195     if (hasInvite) await this.acceptInvitesForEmail(id, email);
196     return (await this.account(id))!;
197   }
198
199   /** Rename an account and change its display name (and an organization's base permission) in one write, after checking every field. */
200   async updateAccount(id: string, patch: { login?: string; name?: unknown; baseRole?: unknown }): Promise<void> {
201     if (patch.name !== undefined && patch.name !== null && typeof patch.name !== "string") throw new HttpError(422, "name must be a string", { errors: [{ resource: "Account", code: "invalid", field: "name" }] });
202     if (patch.baseRole !== undefined && !BASE_ROLES.includes(patch.baseRole as BaseRole)) throw new HttpError(422, "base permission must be none, read, write or admin", { errors: [{ resource: "Organization", code: "invalid", field: "default_repository_permission" }] });
203     if (patch.login && !isValidLogin(patch.login)) throw new HttpError(422, "invalid or reserved login");
204     if (patch.login) {
205       const clash = await this.accountByLogin(patch.login);
206       if (clash && clash.id !== id) throw new HttpError(422, "login already taken");
207       await this.checkCooldown(patch.login, "This name", id);
208     }
209     // A blank display name clears it, so pages fall back to the login.
210     const name = patch.name === undefined ? undefined : (patch.name as string | null)?.trim() || null;
211     const before = patch.login ? await this.account(id) : null;
212     await this.db
213       .prepare("UPDATE accounts SET login = COALESCE(?, login), name = CASE WHEN ? THEN ? ELSE name END, base_role = CASE WHEN kind = 'org' THEN COALESCE(?, base_role) ELSE base_role END WHERE id = ?")
214       .bind(patch.login ?? null, name === undefined ? 0 : 1, name ?? null, (patch.baseRole as string | undefined) ?? null, id)
215       .run();
216     if (patch.login && before && before.login.toLowerCase() !== patch.login.toLowerCase()) {
217       // The old login keeps leading here (repository URLs, the profile) until another account takes it.
218       await this.db.batch([
219         this.db.prepare("DELETE FROM login_redirects WHERE login = ?").bind(patch.login),
220         this.db.prepare("INSERT OR REPLACE INTO login_redirects (login, account_id, created_at) VALUES (?, ?, ?)").bind(before.login, id, now()),
221       ]);
222     }
223     if (patch.login) {
224       // Repos keep their ids; their Durable Objects cache the owner login, so refresh them.
225       const repos = await this.db.prepare("SELECT id FROM repos WHERE owner_id = ?").bind(id).all<{ id: string }>();
226       for (const r of repos.results) {
227         await this.env.REPO.get(this.env.REPO.idFromName(r.id)).updateMeta({ owner: patch.login });
228         await this.forgetArchives(r.id);
229       }
230     }
231   }
232
233   async setAppearance(id: string, appearance: "light" | "dark" | null): Promise<void> {
234     await this.db.prepare("UPDATE accounts SET appearance = ? WHERE id = ?").bind(appearance, id).run();
235   }
236
237   /** The account's own auto-merge setting (repo/auto-merge.ts applyOwn): a merge method, "default", or null for off. People and agents only. */
238   async setAutoMerge(id: string, value: "default" | "merge" | "squash" | "rebase" | null): Promise<void> {
239     await this.db.prepare("UPDATE accounts SET auto_merge = ? WHERE id = ? AND kind IN ('user', 'agent')").bind(value, id).run();
240   }
241
242   /** Point an account at a new profile picture version (null: back to initials). */
243   async setAvatarVersion(id: string, version: number | null): Promise<void> {
244     await this.db.prepare("UPDATE accounts SET avatar_version = ? WHERE id = ?").bind(version, id).run();
245   }
246
247   async createOrg(login: string, name: string, creator: Account): Promise<Account> {
248     if (!isValidLogin(login)) throw new HttpError(422, "invalid or reserved organization name");
249     if (await this.accountByLogin(login)) throw new HttpError(422, "name already taken");
250     await this.checkCooldown(login, "This name");
251     const id = newId();
252     const t = now();
253     await this.db.batch([
254       this.db.prepare("INSERT INTO accounts (id, login, kind, name, status, base_role, created_at) VALUES (?, ?, 'org', ?, 'active', 'read', ?)").bind(id, login, name || login, t),
255       this.db.prepare("INSERT INTO org_members (org_id, user_id, role, created_at) VALUES (?, ?, 'owner', ?)").bind(id, creator.id, t),
256     ]);
257     return (await this.account(id))!;
258   }
259
260   /**
261    * Delete an organization. Refused while it still owns repositories (transfer or delete them first),
262    * container images or an import in progress. Its agents are disabled (their tokens revoked), its
263    * members, pending invitations, secrets and variables, model keys and agent policy removed, and its login freed. The account row
264    * stays, disabled under a placeholder login, because the audit log, import history and its agents
265    * still refer to it. Repositories transferred out of it keep redirecting from their old URLs.
266    */
267   async deleteOrg(org: Account): Promise<void> {
268     if (org.kind !== "org") throw new HttpError(404, "not an organization");
269     const count = async (q: string) => (await this.db.prepare(q).bind(org.id).first<{ n: number }>())?.n ?? 0;
270     const repos = await count("SELECT count(*) AS n FROM repos WHERE owner_id = ?");
271     if (repos) throw new HttpError(422, `${org.login} still owns ${repos} ${repos === 1 ? "repository" : "repositories"}; transfer or delete ${repos === 1 ? "it" : "them"} before deleting the organization`);
272     const images = await count("SELECT count(*) AS n FROM packages WHERE owner_id = ?");
273     if (images) throw new HttpError(422, `${org.login} still owns ${images} container ${images === 1 ? "image" : "images"}; delete ${images === 1 ? "it" : "them"} before deleting the organization`);
274     const imports = await count("SELECT count(*) AS n FROM imports WHERE target_owner_id = ? AND status IN ('draft', 'queued', 'running')");
275     if (imports) throw new HttpError(422, `an import into ${org.login} is still in progress; wait for it to finish or cancel it first`);
276     // Other accounts may have installed its apps (apps/): deleting them is a decision of its own.
277     const apps = await count("SELECT count(*) AS n FROM apps WHERE owner_id = ?");
278     if (apps) throw new HttpError(422, `${org.login} still owns ${apps} ${apps === 1 ? "app" : "apps"}; delete ${apps === 1 ? "it" : "them"} under Settings → Apps before deleting the organization`);
279     const agents = await this.db.prepare("SELECT id FROM accounts WHERE kind = 'agent' AND owner_id = ? AND status != 'disabled'").bind(org.id).all<{ id: string }>();
280     for (const a of agents.results) await this.setStatus(a.id, "disabled");
281     await this.db.batch([
282       this.db.prepare("DELETE FROM org_members WHERE org_id = ?").bind(org.id),
283       this.db.prepare("DELETE FROM invites WHERE org_id = ? AND accepted_at IS NULL").bind(org.id),
284       this.db.prepare("DELETE FROM org_secrets WHERE org_id = ?").bind(org.id),
285       this.db.prepare("DELETE FROM org_secret_repos WHERE org_id = ?").bind(org.id),
286       this.db.prepare("DELETE FROM org_variables WHERE org_id = ?").bind(org.id),
287       // Phase 5b: its model keys, agent policy, allowance counts and the usage its keys paid for.
288       this.db.prepare("DELETE FROM model_keys WHERE account_id = ?").bind(org.id),
289       this.db.prepare("DELETE FROM org_agent_policies WHERE org_id = ?").bind(org.id),
290       this.db.prepare("DELETE FROM org_key_spend WHERE org_id = ?").bind(org.id),
291       this.db.prepare("DELETE FROM agent_usage WHERE key_owner = ?").bind(org.id),
292       this.db.prepare("DELETE FROM login_redirects WHERE account_id = ?").bind(org.id),
293       // Apps installed on it lose their access (apps/store.ts).
294       this.db.prepare("DELETE FROM app_installation_tokens WHERE installation_id IN (SELECT id FROM app_installations WHERE account_id = ?)").bind(org.id),
295       this.db.prepare("DELETE FROM app_installation_repos WHERE installation_id IN (SELECT id FROM app_installations WHERE account_id = ?)").bind(org.id),
296       this.db.prepare("DELETE FROM app_installations WHERE account_id = ?").bind(org.id),
297       this.db.prepare("UPDATE accounts SET login = ?, status = 'disabled' WHERE id = ?").bind(`deleted-${org.id}`, org.id),
298     ]);
299   }
300
301   /** Whether an account sees an organization's settings such as its base permission: members, the
302    * organization's own agents, agents of members, and site admins. */
303   async inOrg(orgId: string, account: Account): Promise<boolean> {
304     if (account.site_admin) return true;
305     if (account.kind === "agent") return account.owner_id === orgId || (!!account.owner_id && !!(await this.orgRole(orgId, account.owner_id)));
306     return !!(await this.orgRole(orgId, account.id));
307   }
308
309   async orgRole(orgId: string, userId: string): Promise<"owner" | "admin" | "member" | null> {
310     const r = await this.db.prepare("SELECT role FROM org_members WHERE org_id = ? AND user_id = ?").bind(orgId, userId).first<{ role: "owner" | "admin" | "member" }>();
311     return r?.role ?? null;
312   }
313
314   async orgsFor(userId: string): Promise<Array<Account & { role: string }>> {
315     const r = await this.db.prepare("SELECT a.*, m.role FROM org_members m JOIN accounts a ON a.id = m.org_id WHERE m.user_id = ? ORDER BY a.login").bind(userId).all<Account & { role: string }>();
316     return r.results;
317   }
318
319   async orgMembers(orgId: string): Promise<Array<Account & { role: string }>> {
320     const r = await this.db.prepare("SELECT a.*, m.role FROM org_members m JOIN accounts a ON a.id = m.user_id WHERE m.org_id = ? ORDER BY a.login").bind(orgId).all<Account & { role: string }>();
321     return r.results;
322   }
323
324   async setOrgMember(orgId: string, userId: string, role: "owner" | "admin" | "member" | null, by?: Account): Promise<void> {
325     const previous = await this.orgRole(orgId, userId);
326     await this.writeOrgMember(orgId, userId, role);
327     if (previous !== role) {
328       const org = await this.account(orgId);
329       if (!org) return;
330       await enqueueMail(this.env, [{ kind: "org_membership", data: { org: org.login, role, previous, by: by && by.id !== userId ? by.login : undefined }, to: { accountId: userId }, reason: "account", at: now() }]);
331       // A heads-up on Diffinery too when someone else adds you.
332       if (role && !previous && by && by.id !== userId) await this.notify([userId], { kind: "org_membership", title: `${by.login} added you to ${org.login} as ${role === "admin" ? "an admin" : `a${role === "owner" ? "n" : ""} ${role}`}`, url: `/${org.login}` });
333     }
334   }
335
336   /**
337    * Add someone to an organization by username or email, like GitHub's "Add people". Either way they
338    * get an invitation and join when they accept it: a username's invitation waits in their
339    * Settings → Invitations (with an email and a notification), an email address's invitation is a
340    * link that also creates the account if there is none. Only an admin adding themselves joins at once.
341    */
342   async addOrgPerson(org: Account, by: Account, who: string, role: "owner" | "admin" | "member"): Promise<AddResult> {
343     const q = who.trim().replace(/^@/, "");
344     if (!q) throw new HttpError(422, "enter a username or an email address");
345     const email = q.includes("@");
346     if (email && !/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(q)) throw new HttpError(422, "enter a valid email address");
347     // An email address always gets an invitation, even when it belongs to an account: adding the
348     // account directly would tell the admin its username, and the person hasn't agreed to join.
349     if (email) {
350       const inv = await this.createInvite(by, { email: q, orgId: org.id, role });
351       return { invited: { email: q, url: inv.url } };
352     }
353     const user = await this.accountByLogin(q);
354     if (user) {
355       if (user.kind !== "user") throw new HttpError(422, `${user.login} is not a person; agents belong to an organization through Settings → Agents`);
356       if (user.status === "disabled") throw new HttpError(422, `${user.login}'s account is disabled`);
357       if (await this.orgRole(org.id, user.id)) throw new HttpError(422, `${user.login} is already a member of ${org.login}`);
358       await this.refuseBlocked(org, user, by);
359       if (user.id === by.id) {
360         await this.setOrgMember(org.id, user.id, role, by);
361         return { added: user };
362       }
363       return { invitedUser: user, ...(await this.inviteAccount(by, user, { orgId: org.id, role })) };
364     }
365     throw new HttpError(404, `no one has the username ${q}; to invite someone new, enter their email address`);
366   }
367
368   /** Add a collaborator by username (a person or an agent; see grantRepoAccess), or invite an email address. */
369   async addRepoPerson(repo: Repo, by: Account, who: string, role: Role): Promise<AddResult> {
370     const q = who.trim().replace(/^@/, "");
371     if (!q) throw new HttpError(422, "enter a username or an email address");
372     const email = q.includes("@");
373     if (email && !/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(q)) throw new HttpError(422, "enter a valid email address");
374     // As for organizations, an email address always gets an invitation.
375     if (email) {
376       const inv = await this.createInvite(by, { email: q, repoId: repo.id, role });
377       return { invited: { email: q, url: inv.url } };
378     }
379     const account = await this.accountByLogin(q);
380     if (account) return this.grantRepoAccess(repo, by, account, role);
381     throw new HttpError(404, `no one has the username ${q}; to invite someone new, enter their email address`);
382   }
383
384   /**
385    * Give an account a role on a repository, as GitHub does: agents, people who already have a direct
386    * grant (a role change), members of the organization that owns the repository and the admin
387    * themselves get it at once; anyone else gets an invitation and has access only once they accept it.
388    */
389   async grantRepoAccess(repo: Repo, by: Account, account: Account, role: Role): Promise<{ added: Account } | { invitedUser: Account; id: string; renewed: boolean }> {
390     if (account.kind === "org") throw new HttpError(422, `${account.login} is an organization; add its people instead`);
391     if (account.status === "disabled") throw new HttpError(422, `${account.login}'s account is disabled`);
392     await this.refuseBlocked({ id: repo.owner_id, login: repo.owner_login }, account, by);
393     const direct =
394       account.kind === "agent" ||
395       account.id === by.id ||
396       !!(await this.db.prepare("SELECT 1 FROM repo_access WHERE repo_id = ? AND account_id = ?").bind(repo.id, account.id).first()) ||
397       (repo.owner_kind === "org" && !!(await this.orgRole(repo.owner_id, account.id)));
398     if (!direct) return { invitedUser: account, ...(await this.inviteAccount(by, account, { repoId: repo.id, role })) };
399     await this.setCollaborator(repo.id, account.id, role);
400     if (account.id !== by.id) await this.notify([account.id], { repoId: repo.id, kind: "repo_access", title: `${by.login} gave you ${role} access to ${repo.owner_login}/${repo.name}`, url: `/${repo.owner_login}/${repo.name}` });
401     return { added: account };
402   }
403
404   /** Revoke a pending invitation to an organization, a repository, or (neither) the site. False if there was none. */
405   async revokeInvite(id: string, scope: { orgId?: string; repoId?: string }): Promise<boolean> {
406     const r = await this.db
407       .prepare("DELETE FROM invites WHERE id = ? AND accepted_at IS NULL AND org_id IS ? AND repo_id IS ?")
408       .bind(id, scope.orgId ?? null, scope.repoId ?? null)
409       .run();
410     return (r.meta?.changes ?? 0) > 0;
411   }
412
413   /**
414    * Blocks keep people apart, as on GitHub: an owner (a person or an organization) can't invite or add
415    * someone it blocked, and nobody can invite or add a person who blocked them.
416    */
417   private async refuseBlocked(owner: Pick<Account, "id" | "login">, user: Account, by: Account): Promise<void> {
418     // An agent is blocked along with its owner.
419     if (await isBlocked(this.env, owner.id, blockSubjects(user))) throw new HttpError(422, `${owner.login} blocked ${user.kind === "agent" ? `${user.login}'s owner` : user.login}; unblock them first`);
420     if (await isBlocked(this.env, user.id, blockSubjects(by))) throw new HttpError(422, `you can't invite ${user.login}`);
421   }
422
423   private async writeOrgMember(orgId: string, userId: string, role: "owner" | "admin" | "member" | null): Promise<void> {
424     // Removing or demoting the last owner would leave nobody able to manage the organization.
425     if (role !== "owner" && (await this.orgRole(orgId, userId)) === "owner") {
426       const owners = await this.db.prepare("SELECT count(*) AS n FROM org_members WHERE org_id = ? AND role = 'owner' AND user_id != ?").bind(orgId, userId).first<{ n: number }>();
427       if (!owners?.n) throw new HttpError(422, "an organization needs at least one owner; make someone else an owner first");
428     }
429     if (role === null) {
430       // Their grants on the organization's repositories (including the admin grant for repositories they
431       // created) go too, for them and their agents. Roles are computed per request, so tokens lose access at once.
432       await this.db.batch([
433         this.db.prepare("DELETE FROM org_members WHERE org_id = ? AND user_id = ?").bind(orgId, userId),
434         this.db
435           .prepare("DELETE FROM repo_access WHERE repo_id IN (SELECT id FROM repos WHERE owner_id = ?) AND account_id IN (SELECT ? UNION SELECT id FROM accounts WHERE kind = 'agent' AND owner_id = ?)")
436           .bind(orgId, userId, userId),
437       ]);
438       return;
439     }
440     await this.db.prepare("INSERT INTO org_members (org_id, user_id, role, created_at) VALUES (?, ?, ?, ?) ON CONFLICT(org_id, user_id) DO UPDATE SET role = excluded.role").bind(orgId, userId, role, now()).run();
441   }
442
443   async users(): Promise<Account[]> {
444     return (await this.db.prepare("SELECT * FROM accounts WHERE kind = 'user' AND deleted_at IS NULL ORDER BY login").all<Account>()).results;
445   }
446
447   async setStatus(id: string, status: Account["status"]): Promise<void> {
448     const before = await this.account(id);
449     // Any status but disabled also lifts a suspension (lib/moderation.ts).
450     await this.db.prepare(`UPDATE accounts SET status = ?${status === "disabled" ? "" : ", suspended_at = NULL, suspended_by = NULL, suspended_reason = NULL"} WHERE id = ?`).bind(status, id).run();
451     if (status === "disabled") await this.db.prepare("UPDATE tokens SET revoked_at = ? WHERE account_id = ? AND revoked_at IS NULL").bind(now(), id).run();
452     // A disabled account's browser sessions end too.
453     if (status === "disabled") await this.db.prepare("DELETE FROM sessions WHERE account_id = ?").bind(id).run();
454     // People hear when they are disabled or enabled again (not when an invite activates them).
455     if (before?.kind === "user" && before.status !== status && (status === "disabled" || before.status === "disabled")) {
456       await enqueueMail(this.env, [{ kind: "account_status", data: { status: status === "disabled" ? "disabled" : "active" }, to: { accountId: id }, reason: "account", at: now() }]);
457     }
458   }
459
460   // ---------------------------------------------------------------- agents
461
462   async createAgent(owner: Account, input: { login: string; name?: string; orgId?: string; policy?: Record<string, unknown> }): Promise<Account> {
463     if (!isValidLogin(input.login)) throw new HttpError(422, "invalid or reserved agent name");
464     const login = input.login.endsWith("[bot]") ? input.login : `${input.login}`;
465     if (await this.accountByLogin(login)) throw new HttpError(422, "name already taken");
466     await this.checkCooldown(login, "This name");
467     const id = newId();
468     const ownerId = input.orgId ?? owner.id;
469     if (input.orgId) {
470       const role = await this.orgRole(input.orgId, owner.id);
471       if (role !== "owner" && role !== "admin") throw new HttpError(403, "only organization admins can create organization agents");
472     }
473     const policy = { maxRole: "write", canMergeProtected: false, budgetUsdMonthly: null, ...input.policy };
474     await this.db
475       .prepare("INSERT INTO accounts (id, login, kind, name, status, owner_id, agent_policy, created_at) VALUES (?, ?, 'agent', ?, 'active', ?, ?, ?)")
476       .bind(id, login, input.name?.trim() || login, ownerId, JSON.stringify(policy), now())
477       .run();
478     return (await this.account(id))!;
479   }
480
481   async agentsOwnedBy(ownerIds: string[]): Promise<Account[]> {
482     if (!ownerIds.length) return [];
483     const q = `SELECT * FROM accounts WHERE kind = 'agent' AND owner_id IN (${ownerIds.map(() => "?").join(",")}) ORDER BY login`;
484     return (await this.db.prepare(q).bind(...ownerIds).all<Account>()).results;
485   }
486
487   // ---------------------------------------------------------------- tokens
488
489   /** `importId`: a GitHub import's short-lived push token (see imports/service.ts). `client`: "cli" for a CLI sign-in's token (auth/cli-login.ts), "oauth" for a connected app's (auth/oauth.ts, with `oauthGrantId`). */
490   async createToken(
491     account: Account,
492     input: { name: string; scopes?: TokenScope[]; repoIds?: string[] | null; expiresInDays?: number; expiresInSeconds?: number; importId?: string; client?: "cli" | "oauth"; oauthGrantId?: string; notify?: boolean },
493   ): Promise<{ token: string; id: string; expiresAt: string }> {
494     const days = Math.min(Math.max(input.expiresInDays ?? 90, 1), 366);
495     // Letters and digits only after the prefix: the GitHub CLI masks a token after its last `_`
496     // (`gh auth status`), so an underscore in the random part would show the rest of it.
497     const token = `dfy_${randomAlnum(40)}`;
498     const id = newId();
499     const expiresAt = new Date(Date.now() + (input.expiresInSeconds ? Math.min(input.expiresInSeconds, days * 86400) * 1000 : days * 86400_000)).toISOString();
500     await this.db
501       .prepare("INSERT INTO tokens (id, account_id, name, token_hash, prefix, scopes, repo_ids, expires_at, created_at, import_id, client, oauth_grant_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
502       .bind(id, account.id, input.name || "token", await sha256(token), token.slice(0, 10), JSON.stringify(input.scopes?.length ? input.scopes : ["write"]), input.repoIds ? JSON.stringify(input.repoIds) : null, expiresAt, now(), input.importId ?? null, input.client ?? null, input.oauthGrantId ?? null)
503       .run();
504     // An import's own push token is server-managed and short-lived: no "new token" email. A connected
505     // app's tokens (auth/oauth.ts) email once, when the app is connected, not on every refresh.
506     if (!input.importId && input.notify !== false) await tokenCreated(this.env, this, account, { name: input.name || "token", scopes: input.scopes?.length ? input.scopes : ["write"] }).catch((e) => console.error("token email failed", e));
507     return { token, id, expiresAt };
508   }
509
510   async tokens(accountId: string): Promise<Array<{ id: string; name: string; prefix: string; scopes: string[]; repoIds: string[] | null; expiresAt: string; lastUsedAt: string | null; createdAt: string; client: string | null }>> {
511     // Import push tokens (import_id set) are server-managed and hidden from the personal token list, and
512     // connected apps' tokens are listed by connection (Settings → Access tokens → Connected apps).
513     const r = await this.db.prepare("SELECT * FROM tokens WHERE account_id = ? AND revoked_at IS NULL AND import_id IS NULL AND oauth_grant_id IS NULL ORDER BY created_at DESC").bind(accountId).all<Record<string, string | null>>();
514     return r.results.map((t) => ({ id: t.id!, name: t.name!, prefix: t.prefix!, scopes: JSON.parse(t.scopes!), repoIds: t.repo_ids ? JSON.parse(t.repo_ids) : null, expiresAt: t.expires_at!, lastUsedAt: t.last_used_at ?? null, createdAt: t.created_at!, client: t.client ?? null }));
515   }
516
517   async revokeToken(accountId: string, id: string): Promise<void> {
518     await this.db.prepare("UPDATE tokens SET revoked_at = ? WHERE id = ? AND account_id = ?").bind(now(), id, accountId).run();
519   }
520
521   async verifyToken(token: string): Promise<{ id: string; account: Account; owner: Account | null; scopes: TokenScope[]; repoIds: string[] | null; importId: string | null } | null> {
522     const hash = await sha256(token);
523     // The token, its account and that account's owner (for an agent) in one round trip: every API, git and MCP call waits for this.
524     const [row, account, tokenOwner] = await Promise.all([
525       this.db.prepare("SELECT * FROM tokens WHERE token_hash = ?").bind(hash).first<Record<string, string | null>>(),
526       this.db.prepare("SELECT * FROM accounts WHERE id = (SELECT account_id FROM tokens WHERE token_hash = ?)").bind(hash).first<Account>(),
527       this.db.prepare("SELECT * FROM accounts WHERE id = (SELECT a.owner_id FROM tokens t JOIN accounts a ON a.id = t.account_id WHERE t.token_hash = ?)").bind(hash).first<Account>(),
528     ]);
529     if (!row || row.revoked_at || row.expires_at! < now()) return null;
530     if (!account || account.id !== row.account_id || account.status !== "active") return null;
531     // An agent's tokens stop working while its owner is suspended (lib/moderation.ts), disabled or otherwise not active.
532     let owner: Account | null = null;
533     if (account.kind === "agent" && account.owner_id) {
534       owner = tokenOwner?.id === account.owner_id ? tokenOwner : await this.account(account.owner_id);
535       if (!owner || owner.suspended_at || owner.status !== "active") return null;
536       // A connected app acting as an organization's agent (auth/oauth.ts) stops the moment the person who
537       // approved it is no longer an owner or admin of that organization, not only at its next refresh.
538       if (row.oauth_grant_id && owner.kind === "org") {
539         const g = await this.db.prepare("SELECT person_id FROM oauth_grants WHERE id = ?").bind(row.oauth_grant_id).first<{ person_id: string }>();
540         const role = g ? await this.orgRole(owner.id, g.person_id) : null;
541         if (role !== "owner" && role !== "admin") return null;
542       }
543     }
544     if (!row.last_used_at || Date.now() - Date.parse(row.last_used_at) > 3600_000) {
545       await this.db.prepare("UPDATE tokens SET last_used_at = ? WHERE id = ?").bind(now(), row.id!).run();
546     }
547     return { id: row.id!, account, owner, scopes: JSON.parse(row.scopes!), repoIds: row.repo_ids ? JSON.parse(row.repo_ids) : null, importId: row.import_id ?? null };
548   }
549
550   /** Revoke a token by id whoever owns it (server-managed tokens such as an import's push token). */
551   async revokeTokenById(id: string): Promise<void> {
552     await this.db.prepare("UPDATE tokens SET revoked_at = ? WHERE id = ? AND revoked_at IS NULL").bind(now(), id).run();
553   }
554
555   /** Revoke every live token an import ever minted (not only the current one), so a stale token can never push. */
556   async revokeTokensByImport(importId: string): Promise<void> {
557     await this.db.prepare("UPDATE tokens SET revoked_at = ? WHERE import_id = ? AND revoked_at IS NULL").bind(now(), importId).run();
558   }
559
560   // ---------------------------------------------------------------- invites
561
562   /** The role is one an organization invitation can have and the inviter may give, and (for a new invitation) the inviter is under the daily limit. */
563   private async checkInvite(inviter: Account, input: { orgId?: string; repoId?: string; role: string }, isNew = true): Promise<void> {
564     if (input.orgId) {
565       if (input.role !== "member" && input.role !== "admin" && input.role !== "owner") throw new HttpError(422, "role must be member, admin or owner");
566       if (input.role === "owner" && !inviter.site_admin && (await this.orgRole(input.orgId, inviter.id)) !== "owner") throw new HttpError(403, "only owners can invite owners");
567     }
568     if (input.repoId && !Object.hasOwn(ROLE_LEVEL, input.role)) throw new HttpError(422, "permission must be read, triage, write, maintain or admin");
569     // Invitations are email to any address: a daily limit per inviter keeps an account from being used to spam.
570     if (isNew && !inviter.site_admin) {
571       const sent = (await this.db.prepare("SELECT count(*) AS n FROM invites WHERE invited_by = ? AND created_at > ?").bind(inviter.id, new Date(Date.now() - 86400_000).toISOString()).first<{ n: number }>())?.n ?? 0;
572       if (sent >= MAX_INVITES_PER_DAY) throw new HttpError(429, `you can send at most ${MAX_INVITES_PER_DAY} invitations in 24 hours; try again later`);
573     }
574   }
575
576   async createInvite(inviter: Account, input: { email: string; orgId?: string; repoId?: string; role: string }): Promise<{ id: string; url: string; token: string }> {
577     await this.checkInvite(inviter, input);
578     // One open invitation per address and organization or repository. The email column is NOCASE,
579     // so Bob@x.com and bob@x.com count as the same person.
580     const pending = await this.db
581       .prepare("SELECT 1 FROM invites WHERE email = ? AND account_id IS NULL AND org_id IS ? AND repo_id IS ? AND accepted_at IS NULL AND expires_at > ?")
582       .bind(input.email, input.orgId ?? null, input.repoId ?? null, now())
583       .first();
584     if (pending) throw new HttpError(422, `${input.email} already has a pending invitation; revoke it first to send a new one`);
585     // An address that already belongs to a member still gets an invitation (accepting it changes
586     // nothing): refusing would tell the inviter whose address it is.
587     const existing = await this.accountByEmail(input.email);
588     const token = randomToken(24);
589     const id = newId();
590     await this.db
591       .prepare("INSERT INTO invites (id, org_id, repo_id, email, role, token_hash, invited_by, created_at, expires_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)")
592       .bind(id, input.orgId ?? null, input.repoId ?? null, input.email, input.role, await sha256(token), inviter.id, now(), new Date(Date.now() + 14 * 86400_000).toISOString())
593       .run();
594     // If the person already exists, activate them now so the invite also works from the dashboard.
595     if (existing?.status === "pending") await this.setStatus(existing.id, "active");
596     const url = `${this.env.PUBLIC_URL}/invite/${token}`;
597     await this.mailInvite({ id, email: input.email, org_id: input.orgId ?? null, repo_id: input.repoId ?? null, role: input.role, invited_by: inviter.id, expires_at: new Date(Date.now() + 14 * 86400_000).toISOString() }, token);
598     return { id, url, token };
599   }
600
601   /**
602    * Invite an existing person by username to an organization or a repository. They join when they
603    * accept it in Settings → Invitations or through the API; the email and notification only point
604    * there, so the invitation carries no link that works for anyone else. Inviting someone who already
605    * has a pending invitation changes its role instead of sending another.
606    */
607   async inviteAccount(inviter: Account, user: Account, input: { orgId?: string; repoId?: string; role: string }): Promise<{ id: string; renewed: boolean }> {
608     const pending = await this.db
609       .prepare("SELECT id FROM invites WHERE account_id = ? AND org_id IS ? AND repo_id IS ? AND accepted_at IS NULL AND expires_at > ?")
610       .bind(user.id, input.orgId ?? null, input.repoId ?? null, now())
611       .first<{ id: string }>();
612     if (pending) {
613       await this.checkInvite(inviter, input, false);
614       await this.db.prepare("UPDATE invites SET role = ? WHERE id = ?").bind(input.role, pending.id).run();
615       return { id: pending.id, renewed: true };
616     }
617     await this.checkInvite(inviter, input);
618     const id = newId();
619     const expiresAt = new Date(Date.now() + 14 * 86400_000).toISOString();
620     // The token column is required and unique; nobody is given this one; the invitation is accepted by id.
621     await this.db
622       .prepare("INSERT INTO invites (id, org_id, repo_id, email, account_id, role, token_hash, invited_by, created_at, expires_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
623       .bind(id, input.orgId ?? null, input.repoId ?? null, user.email ?? null, user.id, input.role, await sha256(randomToken(24)), inviter.id, now(), expiresAt)
624       .run();
625     const org = input.orgId ? await this.account(input.orgId) : null;
626     const repo = input.repoId ? await this.repoById(input.repoId) : null;
627     const where = org ? org.login : repo ? `${repo.owner_login}/${repo.name}` : "Diffinery";
628     await this.notify([user.id], { repoId: input.repoId, kind: "invitation", title: `${inviter.login} invited you to ${where} as ${input.role}`, url: "/settings/invitations" });
629     await enqueueMail(this.env, [
630       {
631         kind: "invite",
632         data: { inviter: inviter.login, url: `${this.env.PUBLIC_URL}/settings/invitations`, org: org?.login, repo: repo ? `${repo.owner_login}/${repo.name}` : undefined, role: input.role, expiresAt, existing: true },
633         to: { accountId: user.id },
634         reason: "invitation",
635         at: now(),
636       },
637     ]);
638     return { id, renewed: false };
639   }
640
641   /**
642    * Email an invitation to its address. Only this emailed link carries the proof that lets
643    * someone create an account for the address (auth/invite-proof.ts); the plain link shown to
644    * the inviter leads to a page that sends this email.
645    */
646   async mailInvite(inv: { id: string; email: string; org_id: string | null; repo_id: string | null; role: string; invited_by: string | null; expires_at: string }, token: string): Promise<void> {
647     const proof = await inviteProof(this.env, await sha256(token));
648     // The proof rides in the fragment, which browsers never send to the server, so request logs never hold
649     // it; the invite page posts it (web/auth.tsx). Links sent before this carried it as ?proof=, still accepted.
650     const url = `${this.env.PUBLIC_URL}/invite/${token}#proof=${proof}`;
651     const inviter = inv.invited_by ? await this.account(inv.invited_by) : null;
652     const org = inv.org_id ? await this.account(inv.org_id) : null;
653     const repo = inv.repo_id ? await this.repoById(inv.repo_id) : null;
654     const existing = await this.accountByEmail(inv.email);
655     await enqueueMail(this.env, [
656       {
657         kind: "invite",
658         data: { inviter: inviter?.login ?? "diffinery", url, org: org?.login, repo: repo ? `${repo.owner_login}/${repo.name}` : undefined, role: inv.role, expiresAt: inv.expires_at },
659         to: existing ? { accountId: existing.id } : { email: inv.email },
660         reason: "invitation",
661         at: now(),
662       },
663     ]);
664   }
665
666   /** Invitations sent from an organization, a repository or the site. One sent to a username shows the username, never the person's email address. */
667   async invites(filter: { orgId?: string; repoId?: string; site?: boolean; pending?: boolean }): Promise<Array<{ id: string; email: string | null; login: string | null; role: string; createdAt: string; expiresAt: string; acceptedAt: string | null; invitedBy: string | null }>> {
668     const r = await this.db
669       .prepare(
670         `SELECT i.*, a.login AS inviter, u.login AS invitee FROM invites i LEFT JOIN accounts a ON a.id = i.invited_by LEFT JOIN accounts u ON u.id = i.account_id WHERE (? IS NULL OR i.org_id = ?) AND (? IS NULL OR i.repo_id = ?)${filter.site ? " AND i.org_id IS NULL AND i.repo_id IS NULL" : ""}${filter.pending ? " AND i.accepted_at IS NULL AND i.expires_at > ?" : ""} ORDER BY i.created_at DESC LIMIT 200`,
671       )
672       .bind(filter.orgId ?? null, filter.orgId ?? null, filter.repoId ?? null, filter.repoId ?? null, ...(filter.pending ? [now()] : []))
673       .all<Record<string, string | null>>();
674     return r.results.map((i) => ({ id: i.id!, email: i.account_id ? null : i.email!, login: i.account_id ? i.invitee ?? null : null, role: i.role!, createdAt: i.created_at!, expiresAt: i.expires_at!, acceptedAt: i.accepted_at ?? null, invitedBy: i.inviter ?? null }));
675   }
676
677   /**
678    * Pending invitations to an organization or a repository for this person: those sent to their
679    * username, and those sent to their email address.
680    */
681   async invitesFor(user: Account): Promise<Array<{ id: string; orgId: string | null; repoId: string | null; org: string | null; repo: string | null; role: string; invitedBy: string | null; createdAt: string; expiresAt: string }>> {
682     const r = await this.db
683       .prepare(
684         `SELECT i.*, a.login AS inviter, o.login AS org_login, ro.login || '/' || r.name AS repo_full FROM invites i
685          LEFT JOIN accounts a ON a.id = i.invited_by LEFT JOIN accounts o ON o.id = i.org_id LEFT JOIN repos r ON r.id = i.repo_id LEFT JOIN accounts ro ON ro.id = r.owner_id
686          WHERE ${MINE} ORDER BY i.created_at DESC LIMIT 100`,
687       )
688       .bind(now(), user.id, user.email ?? null)
689       .all<Record<string, string | null>>();
690     return r.results.map((i) => ({ id: i.id!, orgId: i.org_id ?? null, repoId: i.repo_id ?? null, org: i.org_login ?? null, repo: i.repo_full ?? null, role: i.role!, invitedBy: i.inviter ?? null, createdAt: i.created_at!, expiresAt: i.expires_at! }));
691   }
692
693   /** Accept or decline one of this person's pending invitations (see invitesFor). */
694   async answerInvite(id: string, user: Account, accept: boolean): Promise<{ orgId: string | null; repoId: string | null }> {
695     const inv = await this.db.prepare(`SELECT i.* FROM invites i WHERE ${MINE} AND i.id = ?`).bind(now(), user.id, user.email ?? null, id).first<Record<string, string | null>>();
696     if (!inv) throw new HttpError(404, "no pending invitation with this id");
697     if (accept) await this.applyInvite(inv, user);
698     else await this.db.prepare("DELETE FROM invites WHERE id = ? AND accepted_at IS NULL").bind(id).run();
699     return { orgId: inv.org_id ?? null, repoId: inv.repo_id ?? null };
700   }
701
702   async acceptInvite(token: string, user: Account): Promise<{ orgId: string | null; repoId: string | null }> {
703     const inv = await this.db.prepare("SELECT * FROM invites WHERE token_hash = ?").bind(await sha256(token)).first<Record<string, string | null>>();
704     if (!inv || inv.accepted_at || inv.expires_at! < now()) throw new HttpError(404, "this invite is invalid or has expired");
705     if (inv.account_id) {
706       if (inv.account_id !== user.id) throw new HttpError(403, "this invitation is for someone else");
707     } else if (inv.email && inv.email.toLowerCase() !== (user.email ?? "").toLowerCase()) throw new HttpError(403, `this invite was sent to ${inv.email}; sign in with that address`);
708     await this.applyInvite(inv, user);
709     return { orgId: inv.org_id ?? null, repoId: inv.repo_id ?? null };
710   }
711
712   private async acceptInvitesForEmail(userId: string, email: string): Promise<void> {
713     const user = (await this.account(userId))!;
714     const r = await this.db.prepare("SELECT * FROM invites WHERE email = ? AND account_id IS NULL AND accepted_at IS NULL AND expires_at > ?").bind(email, now()).all<Record<string, string | null>>();
715     for (const inv of r.results) await this.applyInvite(inv, user);
716   }
717
718   private async applyInvite(inv: Record<string, string | null>, user: Account): Promise<void> {
719     const stmts = [this.db.prepare("UPDATE invites SET accepted_at = ?, accepted_by = ? WHERE id = ?").bind(now(), user.id, inv.id!), this.db.prepare("UPDATE accounts SET status = 'active' WHERE id = ? AND status = 'pending'").bind(user.id)];
720     if (inv.org_id) stmts.push(this.db.prepare("INSERT INTO org_members (org_id, user_id, role, created_at) VALUES (?, ?, ?, ?) ON CONFLICT(org_id, user_id) DO NOTHING").bind(inv.org_id, user.id, inv.role === "admin" || inv.role === "owner" ? inv.role : "member", now()));
721     if (inv.repo_id) stmts.push(this.db.prepare("INSERT OR REPLACE INTO repo_access (repo_id, account_id, role) VALUES (?, ?, ?)").bind(inv.repo_id, user.id, inv.role));
722     await this.db.batch(stmts);
723     const org = inv.org_id ? await this.account(inv.org_id) : null;
724     const repo = inv.repo_id ? await this.repoById(inv.repo_id) : null;
725     if (inv.invited_by && inv.invited_by !== user.id) {
726       await enqueueMail(this.env, [
727         { kind: "invite_accepted", data: { login: user.login, email: inv.account_id ? "" : (user.email ?? inv.email ?? ""), org: org?.login, repo: repo ? `${repo.owner_login}/${repo.name}` : undefined }, to: { accountId: inv.invited_by }, reason: "account", at: now() },
728       ]);
729     }
730   }
731
732   /** Record a pending account's request. Administrators hear about it once a day at most, however often it is sent. */
733   async requestAccess(accountId: string, note: string): Promise<void> {
734     const before = await this.db.prepare("SELECT created_at FROM access_requests WHERE account_id = ?").bind(accountId).first<{ created_at: string }>();
735     await this.db.prepare("INSERT OR REPLACE INTO access_requests (account_id, note, created_at) VALUES (?, ?, ?)").bind(accountId, note.slice(0, 500), now()).run();
736     if (before && Date.parse(before.created_at) > Date.now() - 86_400_000) return;
737     const who = await this.account(accountId);
738     const admins = await this.db.prepare("SELECT id FROM accounts WHERE kind = 'user' AND site_admin = 1 AND status = 'active' LIMIT 20").all<{ id: string }>();
739     if (who) await enqueueMail(this.env, admins.results.map((a) => ({ kind: "access_request" as const, data: { email: who.email ?? "", login: who.login, note: note.slice(0, 500) }, to: { accountId: a.id }, reason: "admin" as const, at: now() })));
740   }
741
742   async accessRequests(): Promise<Array<{ account: Account; note: string; createdAt: string }>> {
743     const r = await this.db.prepare("SELECT a.*, r.note AS req_note, r.created_at AS req_at FROM access_requests r JOIN accounts a ON a.id = r.account_id WHERE a.status = 'pending' ORDER BY r.created_at").all<Account & { req_note: string; req_at: string }>();
744     return r.results.map((x) => ({ account: x, note: x.req_note, createdAt: x.req_at }));
745   }
746
747   // ---------------------------------------------------------------- repositories
748
749   async repo(owner: string, name: string): Promise<Repo | null> {
750     return this.db
751       .prepare("SELECT r.*, a.login AS owner_login, a.kind AS owner_kind, a.suspended_at AS owner_suspended_at FROM repos r JOIN accounts a ON a.id = r.owner_id WHERE a.login = ? AND r.name = ?")
752       .bind(owner, name.replace(/\.git$/, ""))
753       .first<Repo>();
754   }
755
756   async repoById(id: string): Promise<Repo | null> {
757     return this.db.prepare("SELECT r.*, a.login AS owner_login, a.kind AS owner_kind, a.suspended_at AS owner_suspended_at FROM repos r JOIN accounts a ON a.id = r.owner_id WHERE r.id = ?").bind(id).first<Repo>();
758   }
759
760   /**
761    * Repositories an account works with, most recently pushed first (at most 500): its own, its
762    * organizations' (as its role there allows) and those shared with it, like GitHub's /user/repos.
763    * With `ownerId`, that owner's repositories the account can see, public ones included. Without it,
764    * other people's public repositories aren't listed: there may be any number of them (see
765    * publicRepos), and listing them pushed the account's own out of the 500.
766    */
767   async reposVisibleTo(account: Account, ownerId?: string): Promise<Repo[]> {
768     if (account.status !== "active") return [];
769     if (account.kind === "agent") {
770       const owner = account.owner_id ? await this.account(account.owner_id) : null;
771       if (!owner || owner.status !== "active") return [];
772     }
773     const params: unknown[] = [];
774     let q = "SELECT r.*, a.login AS owner_login, a.kind AS owner_kind, a.suspended_at AS owner_suspended_at FROM repos r JOIN accounts a ON a.id = r.owner_id";
775     const subjectId = account.kind === "agent" ? account.owner_id! : account.id;
776     if (ownerId) {
777       q += " WHERE r.owner_id = ?";
778       params.push(ownerId);
779       if (!account.site_admin) {
780         q += ` AND (r.visibility = 'public' OR r.owner_id = ? OR r.owner_id IN (SELECT m.org_id FROM org_members m JOIN accounts o ON o.id = m.org_id WHERE m.user_id = ? AND (m.role != 'member' OR COALESCE(o.base_role, 'read') != 'none')) OR r.id IN (SELECT repo_id FROM repo_access WHERE account_id IN (?, ?)))`;
781         params.push(subjectId, subjectId, account.id, subjectId);
782       }
783     } else {
784       // Each part is an indexed lookup (repos by owner, org_members by user, repo_access by account).
785       q += ` WHERE r.id IN (SELECT id FROM repos WHERE owner_id = ?
786         UNION SELECT o.id FROM org_members m JOIN accounts oa ON oa.id = m.org_id JOIN repos o ON o.owner_id = m.org_id WHERE m.user_id = ? AND (m.role != 'member' OR COALESCE(oa.base_role, 'read') != 'none' OR o.visibility = 'public')
787         UNION SELECT repo_id FROM repo_access WHERE account_id IN (?, ?))`;
788       params.push(subjectId, subjectId, account.id, subjectId);
789     }
790     // A suspended person's repositories are hidden from everyone but site administrators.
791     if (!account.site_admin) q += " AND a.suspended_at IS NULL";
792     q += " ORDER BY COALESCE(r.pushed_at, r.updated_at) DESC LIMIT 500";
793     return (await this.db.prepare(q).bind(...params).all<Repo>()).results;
794   }
795
796   /**
797    * Public repositories, most recently pushed first (for the sitemap and search). `filter.words` keeps
798    * those whose owner/name, description or topics contain every word; `filter.owners` those of these
799    * owners (logins).
800    */
801   async publicRepos(limit: number, filter: { words?: string[]; owners?: string[] } = {}): Promise<Repo[]> {
802     let q = "SELECT r.*, a.login AS owner_login, a.kind AS owner_kind, a.suspended_at AS owner_suspended_at FROM repos r JOIN accounts a ON a.id = r.owner_id WHERE r.visibility = 'public' AND a.suspended_at IS NULL";
803     const params: unknown[] = [];
804     const owners = (filter.owners ?? []).slice(0, 20);
805     if (owners.length) {
806       q += ` AND a.login IN (${owners.map(() => "?").join(", ")})`;
807       params.push(...owners);
808     }
809     for (const w of (filter.words ?? []).slice(0, 10)) {
810       q += " AND (a.login || '/' || r.name || ' ' || COALESCE(r.description, '') || ' ' || r.topics) LIKE ? ESCAPE '\\'";
811       params.push(`%${w.replace(/[\\%_]/g, (ch) => `\\${ch}`)}%`);
812     }
813     q += " ORDER BY COALESCE(r.pushed_at, r.updated_at) DESC LIMIT ?";
814     params.push(limit);
815     return (await this.db.prepare(q).bind(...params).all<Repo>()).results;
816   }
817
818   /** `o.authorized`: the caller already checked that `creator` may create repositories for `owner` (GitHub imports). */
819   async createRepo(owner: Account, creator: Account, input: { name: string; description?: string; visibility?: string; defaultBranch?: string }, o: { authorized?: boolean } = {}): Promise<Repo> {
820     if (!isValidName(input.name)) throw new HttpError(422, "invalid repository name: use letters, numbers, '.', '-' and '_'");
821     // Like GitHub, a member who creates an organization repository administers it, whatever the base permission.
822     let creatorGrant: string | null = null;
823     if (o.authorized) {
824       // checked by the caller
825     } else if (owner.kind === "org") {
826       const subject = creator.kind === "agent" ? creator.owner_id! : creator.id;
827       const role = await this.orgRole(owner.id, subject);
828       if (!role && !creator.site_admin) throw new HttpError(403, "you are not a member of this organization");
829       if (role === "member") creatorGrant = subject;
830     } else if (owner.id !== creator.id && !creator.site_admin) throw new HttpError(403, "you can only create repositories for yourself or your organizations");
831     if (await this.repo(owner.login, input.name)) throw new HttpError(422, "a repository with this name already exists");
832     await checkRepositoryQuota(this.env, owner.id, owner.login);
833     const id = newId();
834     const t = now();
835     const branch = input.defaultBranch || "main";
836     await this.db.batch([
837       this.db
838         .prepare("INSERT INTO repos (id, owner_id, name, description, visibility, default_branch, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)")
839         .bind(id, owner.id, input.name, input.description ?? "", repoVisibility(input.visibility) ?? "private", branch, t, t),
840       ...(creatorGrant ? [this.db.prepare("INSERT INTO repo_access (repo_id, account_id, role) VALUES (?, ?, 'admin')").bind(id, creatorGrant)] : []),
841       // A new repository at a moved one's old name takes it over for good, as on GitHub.
842       ...this.claimName(owner.login, input.name),
843     ]);
844     const meta: RepoMeta = { repoId: id, ownerId: owner.id, owner: owner.login, name: input.name, defaultBranch: branch, ownerKind: owner.kind === "org" ? "org" : "user", archived: false };
845     const stub = this.env.REPO.get(this.env.REPO.idFromName(id));
846     await stub.init(meta);
847     if (owner.kind === "org") {
848       const vars = await this.orgVariables(owner.id);
849       if (vars.length) await stub.setOrgVariables(Object.fromEntries(vars.map((v) => [v.name, v.value])));
850     }
851     return (await this.repoById(id))!;
852   }
853
854   async updateRepo(repo: Repo, patch: { name?: string; description?: string; visibility?: string; defaultBranch?: string; archived?: boolean; homepage?: string; topics?: string[] }): Promise<Repo> {
855     if (patch.name && !isValidName(patch.name)) throw new HttpError(422, "invalid repository name");
856     const renamed = !!patch.name && patch.name !== repo.name;
857     if (renamed && patch.name!.toLowerCase() !== repo.name.toLowerCase() && (await this.repo(repo.owner_login, patch.name!))) {
858       throw new HttpError(422, "a repository with this name already exists", { errors: [{ resource: "Repository", code: "custom", field: "name", message: "name already exists on this account" }] });
859     }
860     await this.db.batch([
861       this.db
862         .prepare("UPDATE repos SET name = COALESCE(?, name), description = COALESCE(?, description), visibility = COALESCE(?, visibility), default_branch = COALESCE(?, default_branch), archived = COALESCE(?, archived), updated_at = ? WHERE id = ?")
863         .bind(patch.name ?? null, patch.description ?? null, repoVisibility(patch.visibility) ?? null, patch.defaultBranch ?? null, patch.archived === undefined ? null : patch.archived ? 1 : 0, now(), repo.id),
864       // A rename leaves a redirect from the old name, like a transfer.
865       ...(renamed ? [this.leaveRedirect(repo), ...this.claimName(repo.owner_login, patch.name!)] : []),
866     ]);
867     if (patch.homepage !== undefined || patch.topics !== undefined) {
868       await this.db
869         .prepare("UPDATE repos SET homepage = COALESCE(?, homepage), topics = COALESCE(?, topics) WHERE id = ?")
870         .bind(patch.homepage ?? null, patch.topics ? JSON.stringify(patch.topics.slice(0, 20)) : null, repo.id)
871         .run();
872     }
873     if (patch.name || patch.defaultBranch) await this.env.REPO.get(this.env.REPO.idFromName(repo.id)).updateMeta({ name: patch.name ?? repo.name, defaultBranch: patch.defaultBranch ?? repo.default_branch });
874     // The repository's object lands pull requests by itself (auto-merge, the merge queue): it must know, and stop.
875     if (patch.archived !== undefined) await this.env.REPO.get(this.env.REPO.idFromName(repo.id)).setArchived(patch.archived);
876     if (renamed) await this.forgetArchives(repo.id);
877     return (await this.repoById(repo.id))!;
878   }
879
880   async deleteRepo(repo: Repo): Promise<void> {
881     await this.db.batch(this.repoRowDeletes({ repoId: repo.id }));
882     // Wipes the Durable Object's storage, then the repository's R2 objects.
883     await this.env.REPO.get(this.env.REPO.idFromName(repo.id)).destroy();
884   }
885
886   /**
887    * The directory's part of deleting one repository, or every repository an account owns (account deletion,
888    * which then runs each Durable Object's `destroy()` in the background).
889    */
890   repoRowDeletes(which: { repoId: string } | { ownerId: string }): D1PreparedStatement[] {
891     const [ids, key] = "repoId" in which ? ["?", which.repoId] : ["SELECT id FROM repos WHERE owner_id = ?", which.ownerId];
892     return [
893       this.db.prepare(`DELETE FROM repo_access WHERE repo_id IN (${ids})`).bind(key),
894       // Agent setup's agents (lib/agent-setup.ts) could only ever reach this repository: they stop for good.
895       this.db.prepare(`UPDATE tokens SET revoked_at = ? WHERE revoked_at IS NULL AND account_id IN (SELECT agent_id FROM agent_setups WHERE repo_id IN (${ids}))`).bind(now(), key),
896       this.db.prepare(`UPDATE accounts SET status = 'disabled' WHERE id IN (SELECT agent_id FROM agent_setups WHERE repo_id IN (${ids}))`).bind(key),
897       this.db.prepare(`DELETE FROM agent_setups WHERE repo_id IN (${ids})`).bind(key),
898       this.db.prepare(`DELETE FROM agent_setup_checks WHERE repo_id IN (${ids})`).bind(key),
899       // Invitations reference the repository without ON DELETE CASCADE, so they'd block the delete.
900       this.db.prepare(`DELETE FROM invites WHERE repo_id IN (${ids})`).bind(key),
901       this.db.prepare(`DELETE FROM schedules WHERE repo_id IN (${ids})`).bind(key),
902       // A deleted repository's old names stop redirecting.
903       this.db.prepare(`DELETE FROM repo_redirects WHERE repo_id IN (${ids})`).bind(key),
904       // Apps installed with this repository selected (apps/store.ts) no longer list it.
905       this.db.prepare(`DELETE FROM app_installation_repos WHERE repo_id IN (${ids})`).bind(key),
906       this.db.prepare(`DELETE FROM repos WHERE ${"repoId" in which ? "id" : "owner_id"} = ?`).bind(key),
907     ];
908   }
909
910   /**
911    * Move a repository to another owner, like GitHub's transfer, optionally renaming it. The repository
912    * keeps its id, so its Durable Object (git data, issues, pull requests, runs, secrets, variables,
913    * environments, webhooks, deploy keys, caches) and its R2 objects stay where they are; its packages
914    * move with it, and the old owner/name redirects to the new one. The caller has checked that `by`
915    * administers the repository; this checks the rest: only people transfer, a personal repository only
916    * by its owner, and only to `by`'s own account or an organization `by` owns or administers.
917    */
918   async transferRepo(repo: Repo, by: Account, target: Account, newName?: string): Promise<Repo> {
919     if (by.kind !== "user" || by.status !== "active") throw new HttpError(403, "only people can transfer repositories");
920     if (repo.owner_kind !== "org" && repo.owner_id !== by.id && !by.site_admin) throw new HttpError(403, "only the owner can transfer a personal repository");
921     if (target.kind === "agent" || target.status !== "active") throw new HttpError(422, "repositories can only be transferred to a person or an organization", { errors: [{ resource: "Repository", code: "invalid", field: "new_owner" }] });
922     const targetRole = target.kind === "org" ? await this.orgRole(target.id, by.id) : null;
923     if (target.kind === "user" ? target.id !== by.id : targetRole !== "owner" && targetRole !== "admin") {
924       throw new HttpError(403, "you can transfer repositories only to your own account or to an organization you own or administer");
925     }
926     if (target.id === repo.owner_id) throw new HttpError(422, `${repo.owner_login}/${repo.name} already belongs to ${target.login}`, { errors: [{ resource: "Repository", code: "invalid", field: "new_owner" }] });
927     const name = newName?.trim() || repo.name;
928     if (!isValidName(name)) throw new HttpError(422, "invalid repository name: use letters, numbers, '.', '-' and '_'", { errors: [{ resource: "Repository", code: "invalid", field: "new_name" }] });
929     if (await this.repo(target.login, name)) throw new HttpError(422, `${target.login} already has a repository named ${name}`, { errors: [{ resource: "Repository", code: "custom", field: "name", message: "name already exists on this account" }] });
930     await checkRepositoryQuota(this.env, target.id, target.login);
931     // Packages linked to the repository move with it; an image of the same name under the target would collide.
932     const clash = await this.db
933       .prepare("SELECT p.name FROM packages p WHERE p.owner_id = ? AND p.name IN (SELECT name FROM packages WHERE repo_id = ?) LIMIT 1")
934       .bind(target.id, repo.id)
935       .first<{ name: string }>();
936     if (clash) throw new HttpError(422, `${target.login} already has a container image named ${clash.name}; this repository's image of that name would replace it`);
937     // An import filling the repository works by its owner and name; let it finish first.
938     const stub = this.env.REPO.get(this.env.REPO.idFromName(repo.id));
939     if (await stub.importLockHolder()) throw new HttpError(423, "repository is locked by an import in progress; transfer it once the import finishes");
940     const t = now();
941     await this.db.batch([
942       this.db.prepare("UPDATE repos SET owner_id = ?, name = ?, updated_at = ? WHERE id = ?").bind(target.id, name, t, repo.id),
943       this.leaveRedirect(repo),
944       ...this.claimName(target.login, name),
945       this.db.prepare("INSERT OR REPLACE INTO package_redirects (owner_login, name, package_id, created_at) SELECT ?, name, id, ? FROM packages WHERE repo_id = ?").bind(repo.owner_login, t, repo.id),
946       this.db.prepare("DELETE FROM package_redirects WHERE owner_login = ? AND name IN (SELECT name FROM packages WHERE repo_id = ?)").bind(target.login, repo.id),
947       this.db.prepare("UPDATE packages SET owner_id = ?, updated_at = ? WHERE repo_id = ?").bind(target.id, t, repo.id),
948       // Apps installed on the old owner with this repository selected lose it (apps/store.ts).
949       this.db.prepare("DELETE FROM app_installation_repos WHERE repo_id = ?").bind(repo.id),
950       // Direct collaborators stay, and a new personal owner needs no grant of its own. Grants that came
951       // with the old organization go, as when someone leaves it: its members' (including the admin grant
952       // of whoever created the repository), their agents' and the organization's own agents'.
953       this.db.prepare("DELETE FROM repo_access WHERE repo_id = ? AND account_id = ?").bind(repo.id, target.id),
954       ...(repo.owner_kind === "org"
955         ? [
956             this.db
957               .prepare("DELETE FROM repo_access WHERE repo_id = ? AND account_id IN (SELECT user_id FROM org_members WHERE org_id = ? UNION SELECT id FROM accounts WHERE kind = 'agent' AND (owner_id = ? OR owner_id IN (SELECT user_id FROM org_members WHERE org_id = ?)))")
958               .bind(repo.id, repo.owner_id, repo.owner_id, repo.owner_id),
959           ]
960         : []),
961     ]);
962     // The Durable Object keeps its id; refresh what it knows about its owner, and swap in the new owner's organization variables.
963     await stub.updateMeta({ owner: target.login, ownerId: target.id, ownerKind: target.kind === "org" ? "org" : "user", name });
964     const vars = target.kind === "org" ? await this.orgVariables(target.id) : [];
965     await stub.setOrgVariables(Object.fromEntries(vars.map((v) => [v.name, v.value])));
966     await this.forgetArchives(repo.id);
967     return (await this.repoById(repo.id))!;
968   }
969
970   /** Cached zipballs and tarballs are named after the owner and repository; drop them when either changes (they are rebuilt on the next download). */
971   private async forgetArchives(repoId: string): Promise<void> {
972     await deletePrefixes(this.env.BUCKET, [archivePrefix(repoId)]).catch((e) => console.error("could not drop cached archives", repoId, e));
973   }
974
975   // ---------------------------------------------------------------- redirects (transfers and renames)
976
977   /** Remember a repository's current owner/name before it moves, so the old URLs lead to it. */
978   private leaveRedirect(repo: Repo): D1PreparedStatement {
979     return this.db.prepare("INSERT OR REPLACE INTO repo_redirects (owner_login, name, repo_id, created_at) VALUES (?, ?, ?, ?)").bind(repo.owner_login, repo.name, repo.id, now());
980   }
981
982   /**
983    * A repository now lives at owner/name: an older redirect from there stops applying, and so do
984    * redirects of images named after it (<owner>/<name> and <owner>/<name>/...), which its own
985    * workflows push to.
986    */
987   private claimName(ownerLogin: string, name: string): D1PreparedStatement[] {
988     return [
989       this.db.prepare("DELETE FROM repo_redirects WHERE owner_login = ? AND name = ?").bind(ownerLogin, name),
990       this.db.prepare("DELETE FROM package_redirects WHERE owner_login = ? AND (name = ? OR substr(name, 1, length(?) + 1) = lower(?) || '/')").bind(ownerLogin, name.toLowerCase(), name, name),
991     ];
992   }
993
994   /**
995    * Where an owner/name that holds no repository now leads: a transferred or renamed repository, or one
996    * under a renamed owner. Callers must apply the same visibility check as for a direct load, so a
997    * redirect never shows that a private repository exists.
998    */
999   async movedRepo(owner: string, name: string): Promise<Repo | null> {
1000     const bare = name.replace(/\.git$/, "");
1001     const direct = await this.db.prepare("SELECT repo_id FROM repo_redirects WHERE owner_login = ? AND name = ?").bind(owner, bare).first<{ repo_id: string }>();
1002     if (direct) return this.repoById(direct.repo_id);
1003     const account = await this.renamedAccount(owner);
1004     if (!account) return null;
1005     const moved = await this.repo(account.login, bare);
1006     if (moved) return moved;
1007     const again = await this.db.prepare("SELECT repo_id FROM repo_redirects WHERE owner_login = ? AND name = ?").bind(account.login, bare).first<{ repo_id: string }>();
1008     return again ? this.repoById(again.repo_id) : null;
1009   }
1010
1011   /** The account that used to have this login, while no account has it now. */
1012   async renamedAccount(login: string): Promise<Account | null> {
1013     if (await this.accountByLogin(login)) return null;
1014     return this.db.prepare("SELECT a.* FROM login_redirects l JOIN accounts a ON a.id = l.account_id WHERE l.login = ? AND a.status = 'active'").bind(login).first<Account>();
1015   }
1016
1017   async setTopics(repoId: string, topics: string[]): Promise<void> {
1018     await this.db.prepare("UPDATE repos SET topics = ?, updated_at = ? WHERE id = ?").bind(JSON.stringify(topics), now(), repoId).run();
1019   }
1020
1021   // ---------------------------------------------------------------- organization variables
1022
1023   async orgVariables(orgId: string): Promise<Array<{ name: string; value: string; updated_at: string }>> {
1024     return (await this.db.prepare("SELECT name, value, updated_at FROM org_variables WHERE org_id = ? ORDER BY name").bind(orgId).all<{ name: string; value: string; updated_at: string }>()).results;
1025   }
1026
1027   /** Set (or, with null, delete) organization variables, then copy the set into each of the organization's repositories. */
1028   async setOrgVariables(orgId: string, changes: Record<string, string | null>): Promise<void> {
1029     const stmts = Object.entries(changes).map(([name, value]) =>
1030       value === null
1031         ? this.db.prepare("DELETE FROM org_variables WHERE org_id = ? AND name = ?").bind(orgId, name.toUpperCase())
1032         : this.db.prepare("INSERT OR REPLACE INTO org_variables (org_id, name, value, updated_at) VALUES (?, ?, ?, ?)").bind(orgId, name.toUpperCase(), value, now()),
1033     );
1034     if (stmts.length) await this.db.batch(stmts);
1035     await this.syncOrgVariables(orgId);
1036   }
1037
1038   /**
1039    * Add organization variables only where none exists (ON CONFLICT DO NOTHING), used by imports so
1040    * an import never overwrites a value an org admin already set. Returns which names were added and
1041    * which were skipped because a variable of that name already existed.
1042    */
1043   async addOrgVariablesIfAbsent(orgId: string, vars: Record<string, string>): Promise<{ added: string[]; skipped: string[] }> {
1044     const before = new Set((await this.orgVariables(orgId)).map((v) => v.name));
1045     const added: string[] = [];
1046     const skipped: string[] = [];
1047     const stmts: D1PreparedStatement[] = [];
1048     for (const [rawName, value] of Object.entries(vars)) {
1049       const name = rawName.toUpperCase();
1050       if (before.has(name)) {
1051         skipped.push(name);
1052         continue;
1053       }
1054       added.push(name);
1055       stmts.push(this.db.prepare("INSERT INTO org_variables (org_id, name, value, updated_at) VALUES (?, ?, ?, ?) ON CONFLICT(org_id, name) DO NOTHING").bind(orgId, name, value, now()));
1056     }
1057     if (stmts.length) {
1058       await this.db.batch(stmts);
1059       await this.syncOrgVariables(orgId);
1060     }
1061     return { added, skipped };
1062   }
1063
1064   /**
1065    * Copy the organization's current variable set into each of its repositories' Durable Objects.
1066    * Every repository is tried even when one fails, and failures get a second try. Any still failing
1067    * make this throw, so the caller can repeat the (idempotent) change: the variables themselves are
1068    * already saved, and each copy is the whole set, so a later sync brings a stale repository current.
1069    */
1070   private async syncOrgVariables(orgId: string): Promise<void> {
1071     const vars = Object.fromEntries((await this.orgVariables(orgId)).map((v) => [v.name, v.value]));
1072     let ids = (await this.db.prepare("SELECT id FROM repos WHERE owner_id = ?").bind(orgId).all<{ id: string }>()).results.map((r) => r.id);
1073     for (let attempt = 0; attempt < 2 && ids.length; attempt++) {
1074       const results = await Promise.allSettled(ids.map((id) => this.env.REPO.get(this.env.REPO.idFromName(id)).setOrgVariables(vars)));
1075       ids = ids.filter((_, i) => results[i]!.status === "rejected");
1076     }
1077     if (ids.length) throw new HttpError(503, `the variables were saved, but ${ids.length} ${ids.length === 1 ? "repository wasn't" : "repositories weren't"} updated; repeat the request to finish`);
1078   }
1079
1080   /** The highest role an agent may ever hold (its policy's maxRole, default write). */
1081   agentMaxRole(account: Account): Role {
1082     const policy = account.agent_policy ? (JSON.parse(account.agent_policy) as { maxRole?: Role }) : {};
1083     return policy.maxRole ?? "write";
1084   }
1085
1086   async touchPushed(repoId: string): Promise<void> {
1087     await this.db.prepare("UPDATE repos SET pushed_at = ?, updated_at = ? WHERE id = ?").bind(now(), now(), repoId).run();
1088   }
1089
1090   async collaborators(repoId: string): Promise<Array<{ account: Account; role: Role }>> {
1091     const r = await this.db.prepare("SELECT a.*, x.role AS access_role FROM repo_access x JOIN accounts a ON a.id = x.account_id WHERE x.repo_id = ? ORDER BY a.login").bind(repoId).all<Account & { access_role: Role }>();
1092     return r.results.map((x) => ({ account: x, role: x.access_role }));
1093   }
1094
1095   async setCollaborator(repoId: string, accountId: string, role: Role | null): Promise<void> {
1096     const previous = (await this.db.prepare("SELECT role FROM repo_access WHERE repo_id = ? AND account_id = ?").bind(repoId, accountId).first<{ role: Role }>())?.role ?? null;
1097     if (role) await this.db.prepare("INSERT OR REPLACE INTO repo_access (repo_id, account_id, role) VALUES (?, ?, ?)").bind(repoId, accountId, role).run();
1098     else await this.db.prepare("DELETE FROM repo_access WHERE repo_id = ? AND account_id = ?").bind(repoId, accountId).run();
1099     const repo = previous !== role ? await this.repoById(repoId) : null;
1100     if (repo) await enqueueMail(this.env, [{ kind: "repo_access", data: { repo: `${repo.owner_login}/${repo.name}`, role, previous }, to: { accountId }, reason: "account", at: now() }]);
1101   }
1102
1103   /** Accounts by id, in chunks that stay under D1's 100 bound parameters (missing ids are left out). */
1104   async accountsByIds(ids: string[]): Promise<Account[]> {
1105     const out: Account[] = [];
1106     const unique = [...new Set(ids)];
1107     for (let i = 0; i < unique.length; i += 90) {
1108       const part = unique.slice(i, i + 90);
1109       out.push(...(await this.db.prepare(`SELECT * FROM accounts WHERE id IN (${part.map(() => "?").join(", ")})`).bind(...part).all<Account>()).results);
1110     }
1111     return out;
1112   }
1113
1114   /** Accounts by login, case-insensitively (unknown logins are left out). */
1115   async accountsByLogins(logins: string[]): Promise<Account[]> {
1116     const out: Account[] = [];
1117     const unique = [...new Set(logins.map((l) => l.toLowerCase()))];
1118     for (let i = 0; i < unique.length; i += 90) {
1119       const part = unique.slice(i, i + 90);
1120       out.push(...(await this.db.prepare(`SELECT * FROM accounts WHERE login IN (${part.map(() => "?").join(", ")})`).bind(...part).all<Account>()).results);
1121     }
1122     return out;
1123   }
1124
1125   /**
1126    * The ids of `accounts` that can read `repo` (roleFor isn't null), with a few queries for all of
1127    * them rather than two each: a notification about a busy thread checks up to 200 people.
1128    */
1129   async readersOf(repo: Repo, accounts: Account[]): Promise<Set<string>> {
1130     const out = new Set<string>();
1131     const users: Account[] = [];
1132     for (const a of accounts) {
1133       if (a.status !== "active") continue;
1134       if (a.kind === "agent") {
1135         if (await this.roleFor(a, repo)) out.add(a.id);
1136       } else if (a.kind === "user") users.push(a);
1137     }
1138     for (const a of users) if (a.site_admin || repo.visibility !== "private" || repo.owner_id === a.id) out.add(a.id);
1139     const rest = users.filter((a) => !out.has(a.id));
1140     for (let i = 0; i < rest.length; i += 90) {
1141       const ids = rest.slice(i, i + 90).map((a) => a.id);
1142       const marks = ids.map(() => "?").join(", ");
1143       const shared = await this.db.prepare(`SELECT account_id AS id FROM repo_access WHERE repo_id = ? AND account_id IN (${marks})`).bind(repo.id, ...ids).all<{ id: string }>();
1144       for (const r of shared.results) out.add(r.id);
1145       if (repo.owner_kind === "org") {
1146         const members = await this.db
1147           .prepare(`SELECT m.user_id AS id, m.role, o.base_role FROM org_members m JOIN accounts o ON o.id = m.org_id WHERE m.org_id = ? AND m.user_id IN (${marks})`)
1148           .bind(repo.owner_id, ...ids)
1149           .all<{ id: string; role: string; base_role: string | null }>();
1150         for (const m of members.results) if (m.role === "owner" || m.role === "admin" || (m.role === "member" && baseRole(m) !== "none")) out.add(m.id);
1151       }
1152     }
1153     return out;
1154   }
1155
1156   /** Effective role of an account on a repository (null = no access). */
1157   /**
1158    * The grants that decide `accountId`'s role in the repository `owner/name` (its own access, and its membership of
1159    * the owning organization), read by name so a request can read them at the same time as the repository itself.
1160    * Empty when there is no such repository. roleFor takes them in place of reading them again.
1161    */
1162   async grantsFor(owner: string, name: string, accountId: string): Promise<RepoGrants[]> {
1163     const r = await this.db
1164       .prepare(
1165         "SELECT r.id AS repo_id, ? AS account_id, ra.role AS access, m.role AS member_role, o.base_role FROM repos r JOIN accounts o ON o.id = r.owner_id LEFT JOIN repo_access ra ON ra.repo_id = r.id AND ra.account_id = ? LEFT JOIN org_members m ON m.org_id = r.owner_id AND m.user_id = ? WHERE o.login = ? AND r.name = ?",
1166       )
1167       .bind(accountId, accountId, accountId, owner, name.replace(/\.git$/, ""))
1168       .all<{ repo_id: string; account_id: string; access: Role | null; member_role: string | null; base_role: string | null }>();
1169     return r.results.map((g) => ({ repoId: g.repo_id, accountId: g.account_id, access: g.access, memberRole: g.member_role, baseRole: g.base_role }));
1170   }
1171
1172   /** `account`'s own access to `repo` and its membership of the owning organization: from `grants` when they cover them, else read. */
1173   private async grantsOf(account: Account, repo: Repo, grants: RepoGrants[]): Promise<{ explicit: { role: Role } | null; m: { role: string; base_role: string | null } | null }> {
1174     const g = grants.find((x) => x.repoId === repo.id && x.accountId === account.id);
1175     if (g) return { explicit: g.access ? { role: g.access } : null, m: repo.owner_kind === "org" && g.memberRole ? { role: g.memberRole, base_role: g.baseRole } : null };
1176     const [m, explicit] = await Promise.all([
1177       repo.owner_kind === "org"
1178         ? this.db
1179             .prepare("SELECT m.role, o.base_role FROM org_members m JOIN accounts o ON o.id = m.org_id WHERE m.org_id = ? AND m.user_id = ?")
1180             .bind(repo.owner_id, account.id)
1181             .first<{ role: string; base_role: string | null }>()
1182         : null,
1183       this.db.prepare("SELECT role FROM repo_access WHERE repo_id = ? AND account_id = ?").bind(repo.id, account.id).first<{ role: Role }>(),
1184     ]);
1185     return { explicit, m };
1186   }
1187
1188   /**
1189    * `knownOwner`: an agent's owner as the caller already read it this request (auth/principal.ts), else it is read here.
1190    * `grants`: grantsFor's answers for this repository, read alongside it; any not covered are read here.
1191    */
1192   async roleFor(account: Account, repo: Repo, knownOwner?: Account | null, grants: RepoGrants[] = []): Promise<Role | null> {
1193     if (account.status !== "active") return null;
1194     if (account.kind === "agent") {
1195       if (!account.owner_id) return null;
1196       // An agent from Agent setup reaches its one repository and nothing else, whatever token it uses.
1197       const only = agentRepoId(account);
1198       if (only !== null && only !== repo.id) return null;
1199       // The owner, the owner's role and the agent's own grant, each read once and side by side.
1200       const ownerOf = knownOwner?.id === account.owner_id ? Promise.resolve(knownOwner) : this.account(account.owner_id);
1201       const [owner, ownerRole, explicit] = await Promise.all([
1202         ownerOf,
1203         ownerOf.then((o) => (!o || o.status !== "active" ? null : o.kind === "org" ? (repo.owner_id === o.id ? ("admin" as const) : null) : this.roleFor(o, repo, undefined, grants))),
1204         this.grantsOf(account, repo, grants).then((g) => g.explicit),
1205       ]);
1206       if (!owner || owner.status !== "active") return null;
1207       if (!ownerRole) return null;
1208       const policy = account.agent_policy ? (JSON.parse(account.agent_policy) as { maxRole?: Role }) : {};
1209       // A grant on the repository can lower an agent's role, never lift it past the maximum its owner set:
1210       // any of the repository's admins can grant, and only the owner decides how far the agent goes.
1211       const max = policy.maxRole && Object.hasOwn(ROLE_LEVEL, policy.maxRole) ? policy.maxRole : null;
1212       const granted = explicit?.role ?? max ?? "write";
1213       const cap: Role = max && ROLE_LEVEL[granted] > ROLE_LEVEL[max] ? max : granted;
1214       return ROLE_LEVEL[ownerRole] < ROLE_LEVEL[cap] ? ownerRole : cap;
1215     }
1216     if (account.site_admin) return "admin";
1217     let best: Role | null = null;
1218     const bump = (r: Role | null) => {
1219       if (r && (!best || ROLE_LEVEL[r] > ROLE_LEVEL[best])) best = r;
1220     };
1221     if (repo.owner_id === account.id) bump("admin");
1222     const { m, explicit } = await this.grantsOf(account, repo, grants);
1223     if (repo.owner_kind === "org") {
1224       // Owners and admins administer every repository; members get the organization's base permission.
1225       if (m?.role === "owner" || m?.role === "admin") bump("admin");
1226       else if (m?.role === "member") {
1227         const base = baseRole(m);
1228         if (base !== "none") bump(base);
1229       }
1230     }
1231     bump(explicit?.role ?? null);
1232     if (repo.visibility !== "private") bump("read");
1233     return best;
1234   }
1235
1236   // ---------------------------------------------------------------- notifications and audit
1237
1238   async notify(accountIds: string[], n: { repoId?: string; kind: string; title: string; url: string }): Promise<void> {
1239     const unique = [...new Set(accountIds)];
1240     if (!unique.length) return;
1241     await this.db.batch(unique.map((id) => this.db.prepare("INSERT INTO notifications (id, account_id, repo_id, kind, title, url, created_at) VALUES (?, ?, ?, ?, ?, ?, ?)").bind(newId(), id, n.repoId ?? null, n.kind, n.title, n.url, now())));
1242   }
1243
1244   async notifications(accountId: string, unreadOnly = false): Promise<Array<{ id: string; kind: string; title: string; url: string; createdAt: string; readAt: string | null }>> {
1245     const r = await this.db.prepare(`SELECT * FROM notifications WHERE account_id = ? ${unreadOnly ? "AND read_at IS NULL" : ""} ORDER BY created_at DESC LIMIT 100`).bind(accountId).all<Record<string, string | null>>();
1246     return r.results.map((n) => ({ id: n.id!, kind: n.kind!, title: n.title!, url: n.url!, createdAt: n.created_at!, readAt: n.read_at ?? null }));
1247   }
1248
1249   async markRead(accountId: string, id?: string): Promise<void> {
1250     await this.db.prepare(`UPDATE notifications SET read_at = ? WHERE account_id = ? ${id ? "AND id = ?" : ""} AND read_at IS NULL`).bind(...(id ? [now(), accountId, id] : [now(), accountId])).run();
1251   }
1252
1253   async audit(entry: { actorId?: string; actorLogin?: string; onBehalfOf?: string; action: string; target?: string; detail?: unknown; at?: string }): Promise<void> {
1254     await this.db
1255       .prepare("INSERT INTO audit_log (id, at, actor_id, actor_login, on_behalf_of, action, target, detail) VALUES (?, ?, ?, ?, ?, ?, ?, ?)")
1256       .bind(newId(), entry.at ?? now(), entry.actorId ?? null, entry.actorLogin ?? null, entry.onBehalfOf ?? null, entry.action, entry.target ?? null, entry.detail ? JSON.stringify(entry.detail) : null)
1257       .run();
1258     // Security emails (sign-ins, passkeys, tokens, push protection…) hang off the audit log.
1259     await mailForAudit(this.env, this, entry).catch((e) => console.error("security email failed", entry.action, e));
1260   }
1261
1262   async auditLog(limit = 200): Promise<Array<Record<string, string | null>>> {
1263     return (await this.db.prepare("SELECT * FROM audit_log ORDER BY at DESC LIMIT ?").bind(limit).all<Record<string, string | null>>()).results;
1264   }
1265 }
1266
1267 /** How long audit log entries are kept (docs/security/audit-log.md, and the privacy policy). */
1268 export const AUDIT_RETENTION_DAYS = 730;
1269
1270 /** Delete audit log entries older than AUDIT_RETENTION_DAYS, a bounded batch at a time; returns how many went. */
1271 export async function pruneAuditLog(db: D1Database, at = new Date(), batch = 5000): Promise<number> {
1272   const before = new Date(at.getTime() - AUDIT_RETENTION_DAYS * 86400_000).toISOString();
1273   const r = await db.prepare("DELETE FROM audit_log WHERE id IN (SELECT id FROM audit_log WHERE at < ? LIMIT ?)").bind(before, batch).run();
1274   return r.meta.changes ?? 0;
1275 }