flagon-io/g1t

public

1	/**
2	* The chat service: a workspace's channels, direct messages, threads and
3	* messages. Conversations are between people (docs.g1t.sh/guides/chat/);
4	* messages from agents of the past still show, under the name they had.
5	*
6	* Reached through service bindings: `POST /rpc/<method>` with snake_case
7	* bodies (`chatClient` in @g1t/contracts), and `GET /live` for a channel's
8	* socket, which the site forwards after checking the session. Each channel
9	* has a room (src/room.ts) that delivers what happens in it live.
10	*
11	* Workspaces are kept by id, so renaming one changes nothing here; who is
12	* in a workspace comes from the viewer's memberships, as in every service.
13	*/
14
15	import {
16	CHAT_VIEWER_HEADER,
17	G1T_KEY,
18	G1T_PRINCIPAL,
19	fail,
20	identityClient,
21	idFloor,
22	newId,
23	ok,
24	openD1,
25	parsePrincipalKey,
26	principalKey,
27	type Channel,
28	type ChannelChange,
29	type ChannelDetail,
30	type ChannelMember,
31	type ChatLiveEvent,
32	type ChatMessage,
33	type ChatReaction,
34	type CustomEmoji,
35	type EmojiFile,
36	type EmojiList,
37	type EmojiUpload,
38	type ChatSettings,
39	type ChatSettingsView,
40	type ChatActivity,
41	type ChatSidebar,
42	type ChatSidebarEntry,
43	type Member,
44	type MemberProfile,
45	type MessageCard,
46	type MessagePage,
47	type NewChannel,
48	type PostMessage,
49	type Principal,
50	type Result,
51	type ServiceBinding,
52	type User,
53	type Viewer,
54	type Workspace,
55	} from "@g1t/contracts";
56
57	import { type ActivityRow, activitySpan, chatActivity } from "./activity.ts";
58	import {
59	MAX_REACTIONS_PER_MESSAGE,
60	emojiImage,
61	emojiName,
62	fromBase64,
63	mayRemove,
64	mayUpload,
65	reactionEmoji,
66	roomForReaction,
67	tallyReactions,
68	type ReactionRow,
69	type ReactionTally,
70	} from "./emoji.ts";
71	import { shownCard } from "./cards.ts";
72	// @g1t: the direct message every member has with g1t (src/g1t.ts, src/answer.ts).
73	import {
74	G1T_PROFILE,
75	type MessageMeta,
76	type SaidRow,
77	conversation,
78	g1tDmPerson,
79	latestPage,
80	metaColumn,
81	pageFrom,
82	parseMeta,
83	shownAnswer,
84	startsAnswer,
85	} from "./g1t.ts";
86	import { internalAnswer } from "./internal.ts";
87	import { mentionedHandles, mentionsColumn } from "./mentions.ts";
88	import { messageBody, meterDay, pageOf, pageSize } from "./messages.ts";
89	import { GENERAL, MAX_DM_MEMBERS, channelName, dmKey, dmMembers } from "./names.ts";
90	import { ROOM_MEMBER_HEADER, type ChannelRoom, type RoomMember } from "./room.ts";
91	import { mayCreateChannel, mayManageChannel, permissionsFor, rowFor, settingsChange, settingsOf, type SettingsRow } from "./settings.ts";
92	import { dmTitle, sidebarOrder, tally, type UnreadRow } from "./unread.ts";
93	// Live notifications and counts (services/notify).
94	import { notifyMessage, notifyMuted, notifyRead } from "./notify.ts";
95
96	export { ChannelRoom } from "./room.ts";
97
98	type Env = {
99	DB: D1Database;
100	IDENTITY: ServiceBinding;
101	ROOMS: DurableObjectNamespace<ChannelRoom>;
102	/** The avatars namespace: custom emoji images, under `emoji/<sha256>`, which the usercontent origin serves. */
103	AVATARS: KVNamespace;
104	/** Live notifications and unread counts (services/notify); absent, nobody is told. */
105	NOTIFY?: ServiceBinding;
106	/** Ask (services/integrations): @g1t's answers, which the channel's room asks for. */
107	INTEGRATIONS?: ServiceBinding;
108	};
109
110	type EmojiRow = {
111	workspace_id: string;
112	name: string;
113	alias_of: string \| null;
114	file: string;
115	content_type: CustomEmoji["content_type"];
116	bytes: number;
117	created_by: string;
118	created_at: string;
119	deleted_at: string \| null;
120	};
121
122	/** The viewer's role in a workspace, or null when they are not in it. */
123	function roleOf(viewer: Viewer, workspace: string): "owner" \| "member" \| null {
124	return viewer?.workspaces?.find((m) => m.slug === workspace.toLowerCase())?.role ?? null;
125	}
126
127	async function sha256(bytes: Uint8Array): Promise<string> {
128	const digest = await crypto.subtle.digest("SHA-256", bytes);
129	return [...new Uint8Array(digest)].map((b) => b.toString(16).padStart(2, "0")).join("");
130	}
131
132	type ChannelRow = {
133	id: string;
134	workspace_id: string;
135	kind: "channel" \| "dm";
136	name: string \| null;
137	topic: string \| null;
138	private: number;
139	dm_key: string \| null;
140	created_by: string;
141	created_at: string;
142	archived_at: string \| null;
143	last_message_at: string \| null;
144	};
145
146	type MemberRow = {
147	channel_id: string;
148	principal: string;
149	role: "owner" \| "member";
150	starred: number;
151	muted: number;
152	last_read_id: string \| null;
153	joined_at: string;
154	};
155
156	type MessageRow = {
157	id: string;
158	channel_id: string;
159	author: string;
160	kind: "text" \| "card";
161	body: string;
162	card: string \| null;
163	mentions: string;
164	thread_root: string \| null;
165	reply_count: number;
166	last_reply_at: string \| null;
167	created_at: string;
168	edited_at: string \| null;
169	deleted_at: string \| null;
170	/** On @g1t's answers, questions asked from a page and new-conversation marks (src/g1t.ts `MessageMeta`). */
171	meta: string \| null;
172	};
173
174	/**
175	* An agent deleted for good, as `former_agents` keeps it (migration 0004).
176	* Workspaces no longer have agents; their old messages show under the name
177	* kept here, else as a deleted agent.
178	*/
179	type FormerAgentRow = { agent_id: string; workspace_id: string; handle: string; display_name: string; avatar_seed: string \| null; look: string \| null; deleted_at: string };
180
181	/** What a method found out about the channel it was asked about. */
182	type Place = { slug: string; workspace: Workspace; channel: ChannelRow; member: MemberRow \| null };
183
184	/** The most unread messages one sidebar reads to count; past it, counts are "at least". */
185	const MAX_UNREAD_ROWS = 5_000;
186	/** The longest channel topic. */
187	const MAX_TOPIC = 250;
188	/** How many others a direct message's sidebar entry shows. */
189	const DM_FACES = 4;
190
191	const now = () => new Date().toISOString();
192
193	function isMember(viewer: Viewer, workspace: string): boolean {
194	return !!viewer?.workspaces?.some((m) => m.slug === workspace.toLowerCase());
195	}
196
197	function isOwner(viewer: Viewer, workspace: string): boolean {
198	return !!viewer?.workspaces?.some((m) => m.slug === workspace.toLowerCase() && m.role === "owner");
199	}
200
201	function userKey(viewer: User): string {
202	return principalKey({ kind: "user", id: viewer.id });
203	}
204
205	function toChannel(row: ChannelRow): Channel {
206	return {
207	id: row.id,
208	workspace_id: row.workspace_id,
209	kind: row.kind,
210	name: row.kind === "dm" ? null : row.name,
211	topic: row.topic,
212	private: row.kind === "dm" \|\| !!row.private,
213	created_by: parsePrincipalKey(row.created_by) ?? { kind: "user", id: row.created_by },
214	created_at: row.created_at,
215	archived_at: row.archived_at,
216	last_message_at: row.last_message_at,
217	};
218	}
219
220	function bytesOf(text: string): number {
221	return new TextEncoder().encode(text).length;
222	}
223
224	class Chat {
225	private readonly workspaces = new Map<string, Promise<Workspace \| null>>();
226	private readonly people = new Map<string, Promise<Map<string, Member>>>();
227	private readonly usernames = new Map<string, string>();
228	/** Agents deleted for good, by id, from `former_agents`; null for one chat never heard of. */
229	private readonly former = new Map<string, FormerAgentRow \| null>();
230	private readonly settingsRows = new Map<string, Promise<SettingsRow \| null>>();
231
232	/** `defer` runs work after the answer is sent: the request's waitUntil. */
233	constructor(
234	private readonly env: Env,
235	private readonly defer: (work: Promise<unknown>) => void = () => {},
236	) {}
237
238	private get db() {
239	return this.env.DB;
240	}
241
242	// ── Who and where ───────────────────────────────────────────────────────
243
244	private workspace(slug: string): Promise<Workspace \| null> {
245	const key = slug.toLowerCase();
246	let found = this.workspaces.get(key);
247	if (!found) {
248	found = identityClient(this.env.IDENTITY).getWorkspace(key);
249	this.workspaces.set(key, found);
250	}
251	return found;
252	}
253
254	/** The workspace's people by username, with their names and avatars; asked once per request. */
255	private members(slug: string, workspace: Workspace): Promise<Map<string, Member>> {
256	let found = this.people.get(workspace.id);
257	if (!found) {
258	// Asked as the workspace itself.
259	const actor: User = {
260	id: workspace.id,
261	username: workspace.slug,
262	kind: "workspace",
263	verified: true,
264	workspaces: [{ slug: workspace.slug, role: "member" }],
265	};
266	found = identityClient(this.env.IDENTITY)
267	.listMembers(slug, actor)
268	.then((result) => new Map(result.ok ? result.value.map((m) => [m.username, m]) : []))
269	.catch((error) => {
270	console.error("chat could not list members of", slug, error);
271	return new Map<string, Member>();
272	});
273	this.people.set(workspace.id, found);
274	}
275	return found;
276	}
277
278	/**
279	* Agents of the past, by id, as `former_agents` remembers them: the
280	* name and face their messages still show under. Once per request.
281	*/
282	private async formerAgents(ids: string[]): Promise<Map<string, FormerAgentRow>> {
283	const wanted = [...new Set(ids)].filter((id) => !this.former.has(id));
284	if (wanted.length) {
285	const rows = await this.db
286	.prepare(`SELECT * FROM former_agents WHERE agent_id IN (${wanted.map(() => "?").join(", ")})`)
287	.bind(...wanted)
288	.all<FormerAgentRow>()
289	.catch((error: unknown) => {
290	console.error("chat could not read former agents", error);
291	return { results: [] as FormerAgentRow[] };
292	});
293	for (const id of wanted) this.former.set(id, rows.results.find((row) => row.agent_id === id) ?? null);
294	}
295	const out = new Map<string, FormerAgentRow>();
296	for (const id of ids) {
297	const row = this.former.get(id);
298	if (row) out.set(id, row);
299	}
300	return out;
301	}
302
303	/**
304	* How each member key shows, for one workspace. An agent of the past
305	* shows under the name it had, else as a deleted agent, marked `deleted`.
306	*/
307	private async profiles(slug: string, workspace: Workspace, keys: string[]): Promise<Map<string, MemberProfile>> {
308	const principals = [...new Set(keys)].map((key) => parsePrincipalKey(key)).filter((p): p is Principal => !!p);
309	const userIds = principals.filter((p) => p.kind === "user").map((p) => p.id);
310	const agentIds = principals.filter((p) => p.kind === "agent").map((p) => p.id);
311	const unnamed = userIds.filter((id) => !this.usernames.has(id));
312	const [named, people, former] = await Promise.all([
313	unnamed.length ? identityClient(this.env.IDENTITY).usernames(unnamed).catch(() => ({}) as Record<string, string>) : ({} as Record<string, string>),
314	userIds.length ? this.members(slug, workspace) : new Map<string, Member>(),
315	agentIds.length ? this.formerAgents(agentIds) : new Map<string, FormerAgentRow>(),
316	]);
317	for (const [id, username] of Object.entries(named)) this.usernames.set(id, username);
318	const out = new Map<string, MemberProfile>();
319	for (const p of principals) {
320	if (p.kind === "system") {
321	// @g1t: the one system member there is.
322	out.set(principalKey(p), G1T_PROFILE);
323	} else if (p.kind === "user") {
324	const username = this.usernames.get(p.id) ?? null;
325	const person = username ? people.get(username) : undefined;
326	out.set(principalKey(p), {
327	...p,
328	name: username ?? "ghost",
329	display_username: person?.display_username ?? null,
330	// Their display name, else their username as they wrote it
331	// (`memberName` reads this the same way).
332	display_name: person?.name?.trim() \|\| person?.display_username \|\| username \|\| "Former member",
333	avatar: person?.avatar ?? null,
334	role: null,
335	title: null,
336	avatar_seed: null,
337	});
338	} else {
339	const was = former.get(p.id) ?? null;
340	out.set(principalKey(p), {
341	...p,
342	name: was?.handle ?? p.id,
343	// Never blank: an old agent's messages show under the name it had, else as a deleted agent.
344	display_name: was?.display_name ?? "A deleted agent",
345	avatar: null,
346	role: null,
347	title: null,
348	avatar_seed: was?.avatar_seed ?? null,
349	archived_at: null,
350	archived_by: null,
351	deleted: true,
352	});
353	}
354	}
355	return out;
356	}
357
358	private async profile(slug: string, workspace: Workspace, key: string): Promise<MemberProfile> {
359	return (await this.profiles(slug, workspace, [key])).get(key)!;
360	}
361
362	/** Whether `principal` may be added to a conversation in this workspace: people of it only. */
363	private async belongs(slug: string, workspace: Workspace, principal: Principal): Promise<boolean> {
364	if (principal.kind !== "user") return false;
365	if (!this.usernames.has(principal.id)) {
366	const named = await identityClient(this.env.IDENTITY).usernames([principal.id]);
367	for (const [id, username] of Object.entries(named)) this.usernames.set(id, username);
368	}
369	const username = this.usernames.get(principal.id);
370	return !!username && (await this.members(slug, workspace)).has(username);
371	}
372
373	/**
374	* The viewer's workspace, checked: they must belong to it, as in every
375	* other service.
376	*/
377	private async viewerWorkspace(slug: string, viewer: Viewer): Promise<Result<Workspace>> {
378	if (!viewer) return fail("unauthenticated", "Sign in to use chat.");
379	if (!slug \|\| !isMember(viewer, slug)) return fail("forbidden", "Only members of a workspace can use its chat.");
380	const workspace = await this.workspace(slug);
381	return workspace ? ok(workspace) : fail("not_found", "No such workspace.");
382	}
383
384	/**
385	* A channel the viewer may read (`read`: any public one in their
386	* workspace, or one they are in) or write in (`member`: one they are in).
387	* A private channel or direct message they are not in is not found, so
388	* its existence does not leak.
389	*/
390	private async place(
391	slug: string,
392	channelId: string,
393	viewer: Viewer,
394	need: "read" \| "member",
395	): Promise<Result<Place>> {
396	const found = await this.viewerWorkspace(slug, viewer);
397	if (!found.ok) return found;
398	const workspace = found.value;
399	const [channel, member] = await Promise.all([
400	this.db
401	.prepare("SELECT * FROM channels WHERE id = ? AND workspace_id = ?")
402	.bind(String(channelId ?? ""), workspace.id)
403	.first<ChannelRow>(),
404	this.db
405	.prepare("SELECT * FROM channel_members WHERE channel_id = ? AND principal = ?")
406	.bind(String(channelId ?? ""), userKey(viewer!))
407	.first<MemberRow>(),
408	]);
409	if (!channel) return fail("not_found", "No such channel.");
410	const open = channel.kind === "channel" && !channel.private;
411	if (!member && !open) return fail("not_found", "No such channel.");
412	if (!member && need === "member") return fail("forbidden", `Join #${channel.name} first.`);
413	return ok({ slug: slug.toLowerCase(), workspace, channel, member });
414	}
415
416	private room(channelId: string) {
417	return this.env.ROOMS.get(this.env.ROOMS.idFromName(channelId));
418	}
419
420	/** Tells everyone looking at a channel, after the answer is sent. */
421	private broadcast(channelId: string, event: ChatLiveEvent, except: string \| null = null): void {
422	this.defer(
423	this.room(channelId)
424	.broadcast(event, except)
425	.catch((error: unknown) => console.error("chat could not broadcast to", channelId, error)),
426	);
427	}
428
429	/**
430	* Messages as they go out, with their reactions. `me` (a member key) is
431	* the viewer an answer is for; null for what everyone in a room gets,
432	* where no reaction is anyone's own.
433	*/
434	private async toMessages(slug: string, workspace: Workspace, rows: MessageRow[], me: string \| null = null): Promise<ChatMessage[]> {
435	const reactions = await this.reactionsOf(rows.filter((r) => !r.deleted_at).map((r) => r.id), me);
436	const reactors = [...reactions.values()].flatMap((list) => list.flatMap((r) => r.by));
437	const profiles = await this.profiles(slug, workspace, [...rows.map((r) => r.author), ...reactors]);
438	const at = Date.now();
439	return rows.map((row) => {
440	const gone = !!row.deleted_at;
441	// @g1t's answers, questions asked from a page, new-conversation marks (src/g1t.ts).
442	const meta = gone ? {} : parseMeta(row.meta);
443	const extra: Pick<ChatMessage, "answer" \| "page" \| "reset"> = {};
444	if (meta.answer) extra.answer = shownAnswer(meta.answer, row.created_at, at);
445	if (meta.page) extra.page = meta.page;
446	if (meta.reset) extra.reset = true;
447	return {
448	...extra,
449	reactions: gone
450	? []
451	: (reactions.get(row.id) ?? []).map((r) => ({ ...r, by: r.by.map((key) => profiles.get(key)!).filter(Boolean) })),
452	id: row.id,
453	channel_id: row.channel_id,
454	author: profiles.get(row.author)!,
455	kind: row.kind,
456	body: gone ? "" : row.body,
457	card: gone \|\| !row.card ? null : shownCard(JSON.parse(row.card) as MessageCard),
458	thread_root: row.thread_root,
459	reply_count: row.reply_count,
460	last_reply_at: row.last_reply_at,
461	created_at: row.created_at,
462	edited_at: row.edited_at,
463	deleted_at: row.deleted_at,
464	};
465	});
466	}
467
468	/** Reactions on these messages, counted (src/emoji.ts). One read, by the reactions table's key. */
469	private async reactionsOf(ids: string[], me: string \| null): Promise<Map<string, ReactionTally[]>> {
470	if (!ids.length) return new Map();
471	const rows = await this.db
472	.prepare(
473	"SELECT message_id, emoji, principal, created_at FROM reactions WHERE message_id IN (SELECT value FROM json_each(?))",
474	)
475	.bind(JSON.stringify(ids))
476	.all<ReactionRow>();
477	return tallyReactions(rows.results, me);
478	}
479
480	private async messageRow(channelId: string, id: string): Promise<MessageRow \| null> {
481	return this.db.prepare("SELECT * FROM messages WHERE id = ? AND channel_id = ?").bind(String(id ?? ""), channelId).first<MessageRow>();
482	}
483
484	/** Sends a message as it now is to everyone looking at its channel. */
485	private rebroadcast(place: Place, id: string): void {
486	this.defer(
487	(async () => {
488	const row = await this.messageRow(place.channel.id, id);
489	if (!row) return;
490	const [message] = await this.toMessages(place.slug, place.workspace, [row]);
491	await this.room(place.channel.id).broadcast({ type: "message.updated", message });
492	})().catch((error) => console.error("chat could not rebroadcast", id, error)),
493	);
494	}
495
496	// ── The sidebar ─────────────────────────────────────────────────────────
497
498	/**
499	* Puts a person in the workspace's #general, once. A new workspace has
500	* no channels, so the first sidebar anyone in it asks for creates
501	* #general; and everyone who asks for the sidebar is put in it the first
502	* time, so a new workspace has somewhere to talk and a new member lands
503	* where everyone is. Someone who leaves it is not put back
504	* (`general_joined`). A private channel someone named `general` is never
505	* joined this way.
506	*/
507	private async ensureGeneral(workspace: Workspace, me: string): Promise<void> {
508	const seen = await this.db
509	.prepare("SELECT 1 FROM general_joined WHERE workspace_id = ? AND principal = ?")
510	.bind(workspace.id, me)
511	.first();
512	if (seen) return;
513	const at = now();
514	await this.db
515	.prepare(
516	"INSERT OR IGNORE INTO channels (id, workspace_id, kind, name, topic, private, created_by, created_at) VALUES (?, ?, 'channel', ?, ?, 0, ?, ?)",
517	)
518	.bind(newId("chn"), workspace.id, GENERAL, "Anything and everything for the whole workspace.", me, at)
519	.run();
520	const general = await this.db
521	.prepare("SELECT * FROM channels WHERE workspace_id = ? AND name = ?")
522	.bind(workspace.id, GENERAL)
523	.first<ChannelRow>();
524	const statements = [
525	this.db
526	.prepare("INSERT OR IGNORE INTO general_joined (workspace_id, principal, joined_at) VALUES (?, ?, ?)")
527	.bind(workspace.id, me, at),
528	];
529	// The workspace's default channels (#general unless its owners chose
530	// others): public and not archived only, whatever was kept.
531	const settings = await this.settings(workspace, general?.id ?? null);
532	const defaults = settings.default_channels.length
533	? await this.db
534	.prepare(
535	"SELECT * FROM channels WHERE workspace_id = ? AND kind = 'channel' AND private = 0 AND archived_at IS NULL AND id IN (SELECT value FROM json_each(?))",
536	)
537	.bind(workspace.id, JSON.stringify(settings.default_channels))
538	.all<ChannelRow>()
539	: { results: [] as ChannelRow[] };
540	for (const channel of defaults.results) {
541	statements.push(this.joinStatement(channel.id, me, channel.created_by === me ? "owner" : "member", at));
542	}
543	await this.db.batch(statements);
544	}
545
546	/**
547	* Adds a member. Someone joining starts with everything already said
548	* read, so a long channel does not greet them with its whole history as
549	* unread.
550	*/
551	private joinStatement(channelId: string, principal: string, role: "owner" \| "member", at: string): D1PreparedStatement {
552	return this.db
553	.prepare(
554	"INSERT OR IGNORE INTO channel_members (channel_id, principal, role, last_read_id, joined_at) VALUES (?1, ?2, ?3, (SELECT MAX(id) FROM messages WHERE channel_id = ?1), ?4)",
555	)
556	.bind(channelId, principal, role, at);
557	}
558
559	async sidebar(a: { workspace: string; viewer: Viewer }): Promise<Result<ChatSidebar>> {
560	const found = await this.viewerWorkspace(a.workspace, a.viewer);
561	if (!found.ok) return found;
562	const workspace = found.value;
563	const slug = a.workspace.toLowerCase();
564	const me = userKey(a.viewer!);
565	await this.ensureGeneral(workspace, me);
566
567	const [joined, unread, dmOthers, browsable] = await Promise.all([
568	this.db
569	.prepare(
570	`SELECT c.*, m.starred, m.muted, m.last_read_id
571	FROM channel_members m JOIN channels c ON c.id = m.channel_id
572	WHERE m.principal = ? AND c.workspace_id = ? AND c.archived_at IS NULL`,
573	)
574	.bind(me, workspace.id)
575	.all<ChannelRow & { starred: number; muted: number; last_read_id: string \| null }>(),
576	this.db
577	.prepare(
578	`SELECT msg.channel_id, msg.id, msg.author, msg.mentions
579	FROM channel_members m
580	JOIN channels c ON c.id = m.channel_id
581	JOIN messages msg ON msg.channel_id = m.channel_id AND msg.id > COALESCE(m.last_read_id, '')
582	WHERE m.principal = ?1 AND c.workspace_id = ?2 AND c.archived_at IS NULL
583	AND msg.deleted_at IS NULL AND msg.author != ?1
584	LIMIT ${MAX_UNREAD_ROWS}`,
585	)
586	.bind(me, workspace.id)
587	.all<UnreadRow>(),
588	this.db
589	.prepare(
590	`SELECT o.channel_id, o.principal
591	FROM channel_members m
592	JOIN channels c ON c.id = m.channel_id AND c.kind = 'dm'
593	JOIN channel_members o ON o.channel_id = m.channel_id AND o.principal != m.principal
594	WHERE m.principal = ? AND c.workspace_id = ? AND c.archived_at IS NULL
595	ORDER BY o.joined_at, o.principal`,
596	)
597	.bind(me, workspace.id)
598	.all<{ channel_id: string; principal: string }>(),
599	this.db
600	.prepare(
601	`SELECT COUNT(*) AS n FROM channels c
602	WHERE c.workspace_id = ? AND c.kind = 'channel' AND c.private = 0 AND c.archived_at IS NULL
603	AND NOT EXISTS (SELECT 1 FROM channel_members m WHERE m.channel_id = c.id AND m.principal = ?)`,
604	)
605	.bind(workspace.id, me)
606	.first<{ n: number }>(),
607	]);
608
609	const others = new Map<string, string[]>();
610	for (const row of dmOthers.results) others.set(row.channel_id, [...(others.get(row.channel_id) ?? []), row.principal]);
611	const profiles = await this.profiles(slug, workspace, [me, ...dmOthers.results.map((r) => r.principal)]);
612	const self = profiles.get(me) ?? null;
613	const counts = tally(
614	unread.results,
615	me,
616	self?.name ?? a.viewer!.username,
617	new Map(joined.results.map((row) => [row.id, row.last_read_id])),
618	);
619
620	const entries: ChatSidebarEntry[] = joined.results.map((row) => {
621	const faces = (others.get(row.id) ?? []).map((key) => profiles.get(key)!).filter(Boolean);
622	const count = counts.get(row.id) ?? { unread: 0, mentions: 0 };
623	return {
624	channel: toChannel(row),
625	title: row.kind === "dm" ? dmTitle(faces, self) : (row.name ?? ""),
626	others: row.kind === "dm" ? faces.slice(0, DM_FACES) : [],
627	starred: !!row.starred,
628	muted: !!row.muted,
629	unread: count.unread,
630	mentions: count.mentions,
631	};
632	});
633	const settings = await this.settings(workspace);
634	return ok({ entries: sidebarOrder(entries), browsable: browsable?.n ?? 0, can: permissionsFor(settings, roleOf(a.viewer, slug)) });
635	}
636
637	/**
638	* What was said in `[from, until)` where the viewer can read: every
639	* public channel of the workspace, and the private channels and direct
640	* messages they are in (as `place` reads). Text messages and thread
641	* replies not deleted; never a card. Message ids sort by time, so each
642	* conversation's span is one range of `messages_by_channel`, read in one
643	* round trip for them all. Counts only, nothing of what was said.
644	*/
645	async activity(a: { workspace: string; viewer: Viewer; from: string; until: string }): Promise<Result<ChatActivity>> {
646	const found = await this.viewerWorkspace(a.workspace, a.viewer);
647	if (!found.ok) return found;
648	const workspace = found.value;
649	const span = activitySpan(a.from, a.until);
650	if (!span) return fail("invalid", "from and until must be RFC 3339 times, from before until.");
651	const me = userKey(a.viewer!);
652	const rows = await this.db
653	.prepare(
654	`SELECT msg.channel_id, msg.author, COUNT(*) AS n
655	FROM channels c
656	JOIN messages msg ON msg.channel_id = c.id AND msg.id >= ?3 AND msg.id < ?4
657	WHERE c.workspace_id = ?1 AND c.archived_at IS NULL
658	AND (
659	(c.kind = 'channel' AND c.private = 0)
660	OR EXISTS (SELECT 1 FROM channel_members m WHERE m.channel_id = c.id AND m.principal = ?2)
661	)
662	AND msg.deleted_at IS NULL AND msg.kind = 'text'
663	GROUP BY msg.channel_id, msg.author`,
664	)
665	.bind(workspace.id, me, idFloor("msg", span.fromMs), idFloor("msg", span.untilMs))
666	.all<ActivityRow>();
667	return ok(chatActivity(rows.results, { from: span.from, until: span.until }));
668	}
669
670	// ── Channels ────────────────────────────────────────────────────────────
671
672	async channel(a: { workspace: string; channel_id: string; viewer: Viewer }): Promise<Result<ChannelDetail>> {
673	const found = await this.place(a.workspace, a.channel_id, a.viewer, "read");
674	if (!found.ok) return found;
675	const { slug, workspace, channel, member } = found.value;
676	const [rows, settings] = await Promise.all([
677	this.db
678	.prepare("SELECT * FROM channel_members WHERE channel_id = ? ORDER BY joined_at, principal")
679	.bind(channel.id)
680	.all<MemberRow>(),
681	this.settings(workspace),
682	]);
683	const profiles = await this.profiles(slug, workspace, rows.results.map((r) => r.principal));
684	return ok({
685	channel: toChannel(channel),
686	can_manage: channel.kind === "channel" && mayManageChannel(settings, roleOf(a.viewer, slug), member?.role ?? null),
687	members: rows.results.map((row) => ({
688	channel_id: row.channel_id,
689	member: profiles.get(row.principal)!,
690	role: row.role,
691	starred: !!row.starred,
692	muted: !!row.muted,
693	last_read_id: row.last_read_id,
694	joined_at: row.joined_at,
695	})),
696	});
697	}
698
699	/** A channel by its name, as the site's URLs name them; read like `channel`. */
700	async channelByName(a: { workspace: string; name: string; viewer: Viewer }): Promise<Result<ChannelDetail>> {
701	const found = await this.viewerWorkspace(a.workspace, a.viewer);
702	if (!found.ok) return found;
703	const named = channelName(a.name ?? "");
704	if (!named.ok) return fail("not_found", "No such channel.");
705	const row = await this.db
706	.prepare("SELECT id FROM channels WHERE workspace_id = ? AND name = ?")
707	.bind(found.value.id, named.name)
708	.first<{ id: string }>();
709	if (!row) return fail("not_found", "No such channel.");
710	// A private channel the viewer is not in stays not found there.
711	return this.channel({ workspace: a.workspace, channel_id: row.id, viewer: a.viewer });
712	}
713
714	/**
715	* Every public channel, and the private ones the viewer is in; a private
716	* one they are not in stays unseen. With `archived`, the archived ones.
717	*/
718	async browse(a: { workspace: string; viewer: Viewer; archived?: boolean }): Promise<Result<Channel[]>> {
719	const found = await this.viewerWorkspace(a.workspace, a.viewer);
720	if (!found.ok) return found;
721	const rows = await this.db
722	.prepare(
723	`SELECT * FROM channels c
724	WHERE c.workspace_id = ?1 AND c.kind = 'channel'
725	AND (c.archived_at IS NULL) = (?3 = 0)
726	AND (c.private = 0 OR EXISTS (SELECT 1 FROM channel_members m WHERE m.channel_id = c.id AND m.principal = ?2))
727	ORDER BY c.name`,
728	)
729	.bind(found.value.id, userKey(a.viewer!), a.archived === true ? 1 : 0)
730	.all<ChannelRow>();
731	return ok(rows.results.map(toChannel));
732	}
733
734	async createChannel(a: { workspace: string; viewer: Viewer; input: NewChannel }): Promise<Result<Channel>> {
735	const found = await this.viewerWorkspace(a.workspace, a.viewer);
736	if (!found.ok) return found;
737	const workspace = found.value;
738	const named = channelName(a.input?.name ?? "");
739	if (!named.ok) return fail("invalid", named.message);
740	const topic = typeof a.input?.topic === "string" ? a.input.topic.trim() : "";
741	if (topic.length > MAX_TOPIC) return fail("invalid", `A topic is at most ${MAX_TOPIC} characters.`);
742	const isPrivate = !!a.input?.private;
743	if (!mayCreateChannel(await this.settings(workspace), roleOf(a.viewer, a.workspace), isPrivate)) {
744	return fail("forbidden", `Only owners can create ${isPrivate ? "private" : "public"} channels in this workspace.`);
745	}
746	const taken = await this.db
747	.prepare("SELECT 1 FROM channels WHERE workspace_id = ? AND name = ?")
748	.bind(workspace.id, named.name)
749	.first();
750	if (taken) return fail("conflict", `#${named.name} already exists.`);
751	const me = userKey(a.viewer!);
752	const row: ChannelRow = {
753	id: newId("chn"),
754	workspace_id: workspace.id,
755	kind: "channel",
756	name: named.name,
757	topic: topic \|\| null,
758	private: isPrivate ? 1 : 0,
759	dm_key: null,
760	created_by: me,
761	created_at: now(),
762	archived_at: null,
763	last_message_at: null,
764	};
765	try {
766	await this.db.batch([
767	this.db
768	.prepare(
769	"INSERT INTO channels (id, workspace_id, kind, name, topic, private, created_by, created_at) VALUES (?, ?, 'channel', ?, ?, ?, ?, ?)",
770	)
771	.bind(row.id, row.workspace_id, row.name, row.topic, row.private, me, row.created_at),
772	this.joinStatement(row.id, me, "owner", row.created_at),
773	]);
774	} catch (error) {
775	if (String(error).includes("UNIQUE")) return fail("conflict", `#${named.name} already exists.`);
776	throw error;
777	}
778	return ok(toChannel(row));
779	}
780
781	async openDm(a: { workspace: string; viewer: Viewer; members: Principal[] }): Promise<Result<Channel>> {
782	const found = await this.viewerWorkspace(a.workspace, a.viewer);
783	if (!found.ok) return found;
784	const workspace = found.value;
785	const slug = a.workspace.toLowerCase();
786	const me = userKey(a.viewer!);
787	const asked = Array.isArray(a.members) ? a.members : [];
788	const principals: Principal[] = [];
789	for (const member of asked) {
790	const p = member && parsePrincipalKey(`${member.kind}:${member.id}`);
791	// A person of the workspace, or @g1t.
792	if (!p \|\| (p.kind !== "user" && principalKey(p) !== G1T_KEY)) return fail("invalid", "Each member is a person of the workspace, by id.");
793	principals.push(p);
794	}
795	const members = dmMembers(me, principals.map(principalKey));
796	if (members.includes(G1T_KEY) && !g1tDmPerson(members)) {
797	return fail("invalid", "A direct message with g1t is just you and g1t.");
798	}
799	if (members.length > MAX_DM_MEMBERS) {
800	return fail("invalid", `A direct message has at most ${MAX_DM_MEMBERS} people. Make a private channel instead.`);
801	}
802	const key = dmKey(members);
803	const existing = await this.db
804	.prepare("SELECT * FROM channels WHERE workspace_id = ? AND dm_key = ?")
805	.bind(workspace.id, key)
806	.first<ChannelRow>();
807	if (existing) return ok(toChannel(existing));
808
809	for (const member of members) {
810	// g1t is in every workspace.
811	if (member === me \|\| member === G1T_KEY) continue;
812	const p = parsePrincipalKey(member)!;
813	if (!(await this.belongs(slug, workspace, p))) {
814	return fail("not_found", "That person is not in this workspace.");
815	}
816	}
817	const at = now();
818	await this.db
819	.prepare(
820	"INSERT OR IGNORE INTO channels (id, workspace_id, kind, private, dm_key, created_by, created_at) VALUES (?, ?, 'dm', 1, ?, ?, ?)",
821	)
822	.bind(newId("chn"), workspace.id, key, me, at)
823	.run();
824	// Read back by key: if two people opened it at once, both get the one that won.
825	const channel = await this.db
826	.prepare("SELECT * FROM channels WHERE workspace_id = ? AND dm_key = ?")
827	.bind(workspace.id, key)
828	.first<ChannelRow>();
829	if (!channel) return fail("conflict", "The direct message could not be opened. Try again.");
830	await this.db.batch(members.map((member) => this.joinStatement(channel.id, member, "member", at)));
831	return ok(toChannel(channel));
832	}
833
834	async join(a: { workspace: string; channel_id: string; viewer: Viewer }): Promise<Result<null>> {
835	const found = await this.place(a.workspace, a.channel_id, a.viewer, "read");
836	if (!found.ok) return found;
837	const { channel, member } = found.value;
838	if (member) return ok(null);
839	if (channel.archived_at) return fail("invalid", "This channel is archived.");
840	await this.joinStatement(channel.id, userKey(a.viewer!), "member", now()).run();
841	return ok(null);
842	}
843
844	async leave(a: { workspace: string; channel_id: string; viewer: Viewer }): Promise<Result<null>> {
845	const found = await this.place(a.workspace, a.channel_id, a.viewer, "read");
846	if (!found.ok) return found;
847	const { channel, member } = found.value;
848	if (!member) return ok(null);
849	if (channel.kind === "dm") return fail("invalid", "A direct message can't be left. Mute it instead.");
850	const me = userKey(a.viewer!);
851	await this.db.prepare("DELETE FROM channel_members WHERE channel_id = ? AND principal = ?").bind(channel.id, me).run();
852	// Out of a private channel, they may no longer read it, live either.
853	if (channel.private) {
854	this.defer(this.room(channel.id).drop(me).catch((error: unknown) => console.error("chat could not drop", me, error)));
855	}
856	return ok(null);
857	}
858
859	async invite(a: { workspace: string; channel_id: string; viewer: Viewer; member: Principal }): Promise<Result<null>> {
860	const found = await this.place(a.workspace, a.channel_id, a.viewer, "member");
861	if (!found.ok) return found;
862	const { slug, workspace, channel } = found.value;
863	if (channel.kind === "dm") return fail("invalid", "People can't be added to a direct message. Start a new one with everyone in it.");
864	if (channel.archived_at) return fail("invalid", "This channel is archived.");
865	const p = a.member && parsePrincipalKey(`${a.member.kind}:${a.member.id}`);
866	if (!p \|\| p.kind !== "user") return fail("invalid", "Invite a person of the workspace, by id.");
867	if (!(await this.belongs(slug, workspace, p))) return fail("not_found", "That person is not in this workspace.");
868	await this.joinStatement(channel.id, principalKey(p), "member", now()).run();
869	return ok(null);
870	}
871
872	async setPreferences(a: {
873	workspace: string;
874	channel_id: string;
875	viewer: Viewer;
876	prefs: { starred?: boolean; muted?: boolean };
877	}): Promise<Result<null>> {
878	const found = await this.place(a.workspace, a.channel_id, a.viewer, "member");
879	if (!found.ok) return found;
880	const starred = typeof a.prefs?.starred === "boolean" ? (a.prefs.starred ? 1 : 0) : null;
881	const muted = typeof a.prefs?.muted === "boolean" ? (a.prefs.muted ? 1 : 0) : null;
882	await this.db
883	.prepare(
884	"UPDATE channel_members SET starred = COALESCE(?, starred), muted = COALESCE(?, muted) WHERE channel_id = ? AND principal = ?",
885	)
886	.bind(starred, muted, found.value.channel.id, userKey(a.viewer!))
887	.run();
888	// Notify: the badge counts the conversation again, or leaves it out, in every tab.
889	if (muted !== null) {
890	this.defer(
891	notifyMuted(this.env.NOTIFY, { slug: found.value.slug, channel_id: found.value.channel.id, user_id: a.viewer!.id, muted: !!muted }).catch((error) =>
892	console.error("chat could not notify a mute", error),
893	),
894	);
895	}
896	return ok(null);
897	}
898
899	// ── What owners decide (src/settings.ts) ────────────────────────────────
900
901	/** The row of a workspace's chat settings, read once per request. */
902	private settingsRow(workspace: Workspace): Promise<SettingsRow \| null> {
903	let found = this.settingsRows.get(workspace.id);
904	if (!found) {
905	found = this.db
906	.prepare("SELECT emoji_upload, public_channels, private_channels, manage_channels, default_channels FROM chat_settings WHERE workspace_id = ?")
907	.bind(workspace.id)
908	.first<SettingsRow>();
909	this.settingsRows.set(workspace.id, found);
910	}
911	return found;
912	}
913
914	/**
915	* A workspace's chat settings. Default channels never chosen are
916	* #general, looked up unless `generalId` is given.
917	*/
918	private async settings(workspace: Workspace, generalId?: string \| null): Promise<ChatSettings> {
919	const row = await this.settingsRow(workspace);
920	if (row?.default_channels != null \|\| generalId !== undefined) return settingsOf(row, generalId ?? null);
921	const general = await this.db
922	.prepare("SELECT id FROM channels WHERE workspace_id = ? AND name = ? AND kind = 'channel'")
923	.bind(workspace.id, GENERAL)
924	.first<{ id: string }>();
925	return settingsOf(row, general?.id ?? null);
926	}
927
928	async chatSettings(a: { workspace: string; viewer: Viewer }): Promise<Result<ChatSettingsView>> {
929	const found = await this.viewerWorkspace(a.workspace, a.viewer);
930	if (!found.ok) return found;
931	const workspace = found.value;
932	const [settings, channels] = await Promise.all([
933	this.settings(workspace),
934	this.db
935	.prepare("SELECT * FROM channels WHERE workspace_id = ? AND kind = 'channel' AND private = 0 AND archived_at IS NULL ORDER BY name")
936	.bind(workspace.id)
937	.all<ChannelRow>(),
938	]);
939	return ok({ settings, can: permissionsFor(settings, roleOf(a.viewer, a.workspace)), channels: channels.results.map(toChannel) });
940	}
941
942	async setChatSettings(a: { workspace: string; viewer: Viewer; change: Partial<ChatSettings> }): Promise<Result<ChatSettings>> {
943	const found = await this.viewerWorkspace(a.workspace, a.viewer);
944	if (!found.ok) return found;
945	const workspace = found.value;
946	if (roleOf(a.viewer, a.workspace) !== "owner") return fail("forbidden", "Only owners can change the workspace's chat settings.");
947	const checked = settingsChange(a.change);
948	if (!checked.ok) return fail("invalid", checked.message);
949	const change = checked.change;
950	if (change.default_channels) {
951	// Only the workspace's public channels that are not archived: a private
952	// one would put people somewhere they were never invited.
953	const rows = await this.db
954	.prepare(
955	"SELECT id FROM channels WHERE workspace_id = ? AND kind = 'channel' AND private = 0 AND archived_at IS NULL AND id IN (SELECT value FROM json_each(?))",
956	)
957	.bind(workspace.id, JSON.stringify(change.default_channels))
958	.all<{ id: string }>();
959	const open = new Set(rows.results.map((r) => r.id));
960	if (change.default_channels.some((id) => !open.has(id))) return fail("invalid", "Default channels must be public channels that are not archived.");
961	}
962	const next: ChatSettings = { ...(await this.settings(workspace)), ...change };
963	const row = rowFor(next);
964	await this.db
965	.prepare(
966	`INSERT INTO chat_settings (workspace_id, emoji_upload, public_channels, private_channels, manage_channels, default_channels)
967	VALUES (?1, ?2, ?3, ?4, ?5, ?6)
968	ON CONFLICT (workspace_id) DO UPDATE SET emoji_upload = ?2, public_channels = ?3, private_channels = ?4, manage_channels = ?5, default_channels = ?6`,
969	)
970	.bind(workspace.id, row.emoji_upload, row.public_channels, row.private_channels, row.manage_channels, row.default_channels)
971	.run();
972	this.settingsRows.delete(workspace.id);
973	return ok(next);
974	}
975
976	/**
977	* Renames, archives or unarchives a channel, or changes its topic. The
978	* workspace's `manage_channels` setting says who may do the first three;
979	* any member may change the topic. #general stays #general, and stays.
980	*/
981	async updateChannel(a: { workspace: string; channel_id: string; viewer: Viewer; change: ChannelChange }): Promise<Result<Channel>> {
982	// Archived or not, a channel is found the same way; a private one only by its members.
983	const found = await this.place(a.workspace, a.channel_id, a.viewer, "read");
984	if (!found.ok) return found;
985	const { slug, workspace, channel, member } = found.value;
986	if (channel.kind === "dm") return fail("invalid", "A direct message has no name or topic to change.");
987	const change = a.change ?? {};
988	const next: ChannelRow = { ...channel };
989	if (change.name !== undefined \|\| change.archived !== undefined) {
990	const settings = await this.settings(workspace);
991	if (!mayManageChannel(settings, roleOf(a.viewer, slug), member?.role ?? null)) {
992	return fail(
993	"forbidden",
994	settings.manage_channels === "owners"
995	? "Only workspace owners can rename or archive channels here."
996	: "Only this channel's owners and workspace owners can rename or archive it.",
997	);
998	}
999	if (channel.name === GENERAL) return fail("invalid", "#general is where everyone is: it can't be renamed or archived.");
1000	}
1001	if (change.name !== undefined) {
1002	const named = channelName(String(change.name ?? ""));
1003	if (!named.ok) return fail("invalid", named.message);
1004	if (named.name !== channel.name) {
1005	const taken = await this.db
1006	.prepare("SELECT 1 FROM channels WHERE workspace_id = ? AND name = ? AND id != ?")
1007	.bind(workspace.id, named.name, channel.id)
1008	.first();
1009	if (taken \|\| named.name === GENERAL) return fail("conflict", `#${named.name} already exists.`);
1010	}
1011	next.name = named.name;
1012	}
1013	if (change.topic !== undefined) {
1014	if (!member) return fail("forbidden", `Join #${channel.name} first.`);
1015	const topic = typeof change.topic === "string" ? change.topic.trim() : "";
1016	if (topic.length > MAX_TOPIC) return fail("invalid", `A topic is at most ${MAX_TOPIC} characters.`);
1017	next.topic = topic \|\| null;
1018	}
1019	if (change.archived !== undefined) {
1020	next.archived_at = change.archived ? (channel.archived_at ?? now()) : null;
1021	} else if (channel.archived_at && (change.name !== undefined \|\| change.topic !== undefined)) {
1022	return fail("invalid", "This channel is archived. Unarchive it first.");
1023	}
1024	try {
1025	await this.db
1026	.prepare("UPDATE channels SET name = ?, topic = ?, archived_at = ? WHERE id = ?")
1027	.bind(next.name, next.topic, next.archived_at, channel.id)
1028	.run();
1029	} catch (error) {
1030	if (String(error).includes("UNIQUE")) return fail("conflict", `#${next.name} already exists.`);
1031	throw error;
1032	}
1033	const updated = toChannel(next);
1034	this.broadcast(channel.id, { type: "channel.updated", channel: updated });
1035	return ok(updated);
1036	}
1037
1038	// ── Messages ────────────────────────────────────────────────────────────
1039
1040	/**
1041	* Newest first, a page at a time. With `thread_root`, that thread's
1042	* replies, and the message they reply to as the oldest once the page
1043	* reaches the start of the thread; without, the channel's top-level
1044	* messages. A deleted message stays only while replies hang off it.
1045	*/
1046	async messages(a: {
1047	workspace: string;
1048	channel_id: string;
1049	viewer: Viewer;
1050	before?: string \| null;
1051	after?: string \| null;
1052	limit?: number \| null;
1053	thread_root?: string \| null;
1054	}): Promise<Result<MessagePage>> {
1055	const found = await this.place(a.workspace, a.channel_id, a.viewer, "read");
1056	if (!found.ok) return found;
1057	const { slug, workspace, channel } = found.value;
1058	const size = pageSize(a.limit);
1059	// Ids are lowercase letters, digits and `_`, so `~` sorts after every
1060	// one: the first page reads from the newest as a range of the index.
1061	const before = typeof a.before === "string" && a.before ? a.before : "~";
1062	const root = typeof a.thread_root === "string" && a.thread_root ? a.thread_root : null;
1063	if (typeof a.after === "string" && a.after) {
1064	// Catching up after a reconnect: what came after, oldest first. Deleted
1065	// ones too, so the client drops them; read one past the page to know
1066	// whether there is more.
1067	const newer = root
1068	? await this.db
1069	.prepare("SELECT * FROM messages WHERE thread_root = ? AND channel_id = ? AND id > ? ORDER BY id LIMIT ?")
1070	.bind(root, channel.id, a.after, size + 1)
1071	.all<MessageRow>()
1072	: await this.db
1073	.prepare("SELECT * FROM messages WHERE channel_id = ? AND thread_root IS NULL AND id > ? ORDER BY id LIMIT ?")
1074	.bind(channel.id, a.after, size + 1)
1075	.all<MessageRow>();
1076	const page = pageOf(newer.results, size);
1077	return ok({ messages: await this.toMessages(slug, workspace, page.rows, userKey(a.viewer!)), older: null, newer: page.older });
1078	}
1079	const rows = root
1080	? await this.db
1081	.prepare(
1082	`SELECT * FROM messages WHERE thread_root = ?1 AND channel_id = ?2 AND id < ?3
1083	ORDER BY id DESC LIMIT ?4`,
1084	)
1085	.bind(root, channel.id, before, size + 1)
1086	.all<MessageRow>()
1087	: await this.db
1088	.prepare(
1089	`SELECT * FROM messages
1090	WHERE channel_id = ?1 AND thread_root IS NULL AND id < ?2
1091	AND (deleted_at IS NULL OR reply_count > 0)
1092	ORDER BY id DESC LIMIT ?3`,
1093	)
1094	.bind(channel.id, before, size + 1)
1095	.all<MessageRow>();
1096	const page = pageOf(rows.results, size);
1097	let list = page.rows;
1098	// The message a thread is under: the oldest once the page reaches the
1099	// start, and, on the first page, as `root` however long the thread is,
1100	// so what the thread replies to stays at the top of it.
1101	const firstPage = before === "~";
1102	const rootRow = root && (page.older === null \|\| firstPage) ? await this.messageRow(channel.id, root) : null;
1103	if (rootRow && page.older === null) list = [...list, rootRow];
1104	const messages = await this.toMessages(slug, workspace, list, userKey(a.viewer!));
1105	if (!rootRow \|\| !firstPage) return ok({ messages, older: page.older });
1106	const shown = messages.find((m) => m.id === rootRow.id) ?? (await this.toMessages(slug, workspace, [rootRow], userKey(a.viewer!)))[0] ?? null;
1107	return ok({ messages, older: page.older, root: shown });
1108	}
1109
1110	/**
1111	* Writes a message and everything that follows from it: the thread's
1112	* reply count, the channel's last activity, the author's own read mark,
1113	* the meter; then, after answering, tells the room and notifies the
1114	* people it is for.
1115	*/
1116	private async write(
1117	place: Place,
1118	author: string,
1119	input: { body: string; card: MessageCard \| null; thread_root: string \| null },
1120	options: { meta?: MessageMeta; notify?: boolean } = {},
1121	): Promise<Result<ChatMessage>> {
1122	const { channel, workspace } = place;
1123	if (channel.archived_at) return fail("invalid", "This channel is archived.");
1124	let threadRoot: string \| null = null;
1125	if (input.thread_root) {
1126	const root = await this.messageRow(channel.id, input.thread_root);
1127	if (!root \|\| (root.deleted_at && !root.reply_count)) return fail("not_found", "No such message to reply to.");
1128	// A reply to a reply goes in the same thread.
1129	threadRoot = root.thread_root ?? root.id;
1130	}
1131	const at = now();
1132	const body = input.body;
1133	const handles = mentionedHandles(body);
1134	const row: MessageRow = {
1135	id: newId("msg"),
1136	channel_id: channel.id,
1137	author,
1138	kind: input.card ? "card" : "text",
1139	body,
1140	card: input.card ? JSON.stringify(input.card) : null,
1141	mentions: mentionsColumn(handles),
1142	thread_root: threadRoot,
1143	reply_count: 0,
1144	last_reply_at: null,
1145	created_at: at,
1146	edited_at: null,
1147	deleted_at: null,
1148	meta: metaColumn(options.meta ?? {}),
1149	};
1150	const statements = [
1151	this.db
1152	.prepare(
1153	"INSERT INTO messages (id, channel_id, author, kind, body, card, mentions, thread_root, created_at, meta) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
1154	)
1155	.bind(row.id, row.channel_id, author, row.kind, row.body, row.card, row.mentions, threadRoot, at, row.meta),
1156	this.db.prepare("UPDATE channels SET last_message_at = ? WHERE id = ?").bind(at, channel.id),
1157	// What you wrote, you have read.
1158	this.db
1159	.prepare("UPDATE channel_members SET last_read_id = ? WHERE channel_id = ? AND principal = ?")
1160	.bind(row.id, channel.id, author),
1161	this.db
1162	.prepare(
1163	`INSERT INTO chat_meter (workspace_id, day, messages, bytes) VALUES (?, ?, 1, ?)
1164	ON CONFLICT (workspace_id, day) DO UPDATE SET messages = messages + 1, bytes = bytes + excluded.bytes`,
1165	)
1166	.bind(workspace.id, meterDay(at), bytesOf(row.body) + bytesOf(row.card ?? "")),
1167	];
1168	if (threadRoot) {
1169	statements.push(
1170	this.db
1171	.prepare("UPDATE messages SET reply_count = reply_count + 1, last_reply_at = ? WHERE id = ?")
1172	.bind(at, threadRoot),
1173	);
1174	}
1175	await this.db.batch(statements);
1176
1177	const [message] = await this.toMessages(place.slug, workspace, [row]);
1178	this.broadcast(channel.id, { type: "message.created", message });
1179	if (threadRoot) this.rebroadcast(place, threadRoot);
1180	// An answer from @g1t notifies when it ends (src/room.ts), not while it is empty.
1181	if (options.notify === false) return ok(message);
1182	// Notify: counts for everyone in the conversation, a notification for those it is for.
1183	this.defer(
1184	notifyMessage(this.db, this.env.NOTIFY, (keys) => this.profiles(place.slug, workspace, keys), { slug: place.slug, channel, row, handles }).catch(
1185	(error) => console.error("chat could not notify about", row.id, error),
1186	),
1187	);
1188	return ok(message);
1189	}
1190
1191	async post(a: { workspace: string; channel_id: string; viewer: Viewer; message: PostMessage }): Promise<Result<ChatMessage>> {
1192	const found = await this.place(a.workspace, a.channel_id, a.viewer, "read");
1193	if (!found.ok) return found;
1194	const body = messageBody(a.message?.body);
1195	if (!body.ok) return fail("invalid", body.message);
1196	const me = userKey(a.viewer!);
1197	let place = found.value;
1198	if (!place.member) {
1199	// Saying something in a public channel joins it, as reading does not.
1200	if (place.channel.archived_at) return fail("invalid", "This channel is archived.");
1201	await this.joinStatement(place.channel.id, me, "member", now()).run();
1202	place = { ...place, member: { channel_id: place.channel.id, principal: me } as MemberRow };
1203	}
1204	const input = { body: body.body, card: null, thread_root: a.message?.thread_root ?? null };
1205	const members = place.channel.kind === "dm" ? await this.memberKeys(place.channel.id) : [];
1206	if (!startsAnswer({ channelKind: place.channel.kind, members, author: me, threadRoot: input.thread_root, card: false, body: input.body })) {
1207	return this.write(place, me, input);
1208	}
1209	// A question for @g1t: kept with the page it was asked from, then answered.
1210	const page = pageFrom(a.message?.page);
1211	const written = await this.write(place, me, input, { meta: page ? { page } : {} });
1212	if (written.ok) this.defer(this.startAnswer(place, a.viewer!).catch((error) => console.error("g1t could not start an answer in", place.channel.id, error)));
1213	return written;
1214	}
1215
1216	private async memberKeys(channelId: string): Promise<string[]> {
1217	const rows = await this.db.prepare("SELECT principal FROM channel_members WHERE channel_id = ?").bind(channelId).all<{ principal: string }>();
1218	return rows.results.map((row) => row.principal);
1219	}
1220
1221	// ── @g1t (src/g1t.ts, src/answer.ts) ────────────────────────────────────
1222
1223	/**
1224	* Starts @g1t's answer to what was last said in its direct message: an
1225	* empty message from g1t, `thinking`, which the channel's room writes the
1226	* answer into as it streams (src/room.ts `startAnswer`).
1227	*/
1228	private async startAnswer(place: Place, viewer: User): Promise<void> {
1229	const recent = await this.db
1230	.prepare("SELECT id, author, kind, body, meta, deleted_at FROM messages WHERE channel_id = ? AND thread_root IS NULL ORDER BY id DESC LIMIT 120")
1231	.bind(place.channel.id)
1232	.all<SaidRow>();
1233	const rows = recent.results.reverse();
1234	const messages = conversation(rows);
1235	if (!messages.length) return;
1236	const written = await this.write(place, G1T_KEY, { body: "", card: null, thread_root: null }, { meta: { answer: { state: "thinking" } }, notify: false });
1237	if (!written.ok) return;
1238	const { channel } = place;
1239	await this.room(channel.id).startAnswer({
1240	slug: place.slug,
1241	channel: { id: channel.id, kind: channel.kind, name: channel.name },
1242	actor: viewer,
1243	messages,
1244	// The page of the newest question; one asked in Chat is on the direct message itself.
1245	page: { ...latestPage(rows), path: latestPage(rows)?.path ?? `/${place.slug}/-/chat/dm/${channel.id}` },
1246	message: written.value,
1247	});
1248	}
1249
1250	/** The viewer's direct message with @g1t, checked: theirs, and with g1t. */
1251	private async g1tPlace(slug: string, channelId: string, viewer: Viewer): Promise<Result<Place>> {
1252	const found = await this.place(slug, channelId, viewer, "member");
1253	if (!found.ok) return found;
1254	if (found.value.channel.kind !== "dm" \|\| g1tDmPerson(await this.memberKeys(found.value.channel.id)) !== userKey(viewer!)) {
1255	return fail("invalid", "That is not a direct message with g1t.");
1256	}
1257	return found;
1258	}
1259
1260	async g1tDm(a: { workspace: string; viewer: Viewer }): Promise<Result<Channel>> {
1261	return this.openDm({ workspace: a.workspace, viewer: a.viewer, members: [G1T_PRINCIPAL] });
1262	}
1263
1264	/**
1265	* "New conversation": stops an answer still coming, and leaves a mark
1266	* after which g1t reads only what is said next. Everything before stays
1267	* to read.
1268	*/
1269	async g1tReset(a: { workspace: string; channel_id: string; viewer: Viewer }): Promise<Result<ChatMessage>> {
1270	const found = await this.g1tPlace(a.workspace, a.channel_id, a.viewer);
1271	if (!found.ok) return found;
1272	await this.room(found.value.channel.id).stopAnswer();
1273	const written = await this.write(found.value, G1T_KEY, { body: "New conversation", card: null, thread_root: null }, { meta: { reset: true }, notify: false });
1274	if (written.ok) {
1275	// Their own mark is read.
1276	await this.db
1277	.prepare("UPDATE channel_members SET last_read_id = ? WHERE channel_id = ? AND principal = ?")
1278	.bind(written.value.id, found.value.channel.id, userKey(a.viewer!))
1279	.run();
1280	}
1281	return written;
1282	}
1283
1284	/** Answers the latest question again: g1t's last answer goes, and a new one is written. */
1285	async g1tRetry(a: { workspace: string; channel_id: string; viewer: Viewer }): Promise<Result<null>> {
1286	const found = await this.g1tPlace(a.workspace, a.channel_id, a.viewer);
1287	if (!found.ok) return found;
1288	const place = found.value;
1289	const last = await this.db
1290	.prepare("SELECT * FROM messages WHERE channel_id = ? AND thread_root IS NULL AND deleted_at IS NULL ORDER BY id DESC LIMIT 1")
1291	.bind(place.channel.id)
1292	.first<MessageRow>();
1293	if (!last \|\| (last.author === G1T_KEY && parseMeta(last.meta).reset)) return fail("invalid", "There is no question to answer again.");
1294	if (last.author === G1T_KEY) {
1295	await this.room(place.channel.id).stopAnswer();
1296	await this.db.prepare("UPDATE messages SET deleted_at = ?, body = '', meta = NULL WHERE id = ?").bind(now(), last.id).run();
1297	this.broadcast(place.channel.id, { type: "message.deleted", channel_id: place.channel.id, id: last.id });
1298	}
1299	await this.startAnswer(place, a.viewer!);
1300	return ok(null);
1301	}
1302
1303	async g1tStop(a: { workspace: string; channel_id: string; viewer: Viewer }): Promise<Result<null>> {
1304	const found = await this.g1tPlace(a.workspace, a.channel_id, a.viewer);
1305	if (!found.ok) return found;
1306	await this.room(found.value.channel.id).stopAnswer();
1307	return ok(null);
1308	}
1309
1310	/**
1311	* For g1t's own services (`POST /internal/g1t_post`): says something as
1312	* @g1t in a direct message with it. Never reached from the site.
1313	*/
1314	async internalG1tPost(a: { workspace: string; channel_id: string; body: string }): Promise<Result<ChatMessage>> {
1315	const found = await this.internalPlace(a.workspace, a.channel_id);
1316	if (!found.ok) return found;
1317	const body = messageBody(a.body);
1318	if (!body.ok) return fail("invalid", body.message);
1319	return this.write(found.value, G1T_KEY, { body: body.body, card: null, thread_root: null });
1320	}
1321
1322	/** `POST /internal/g1t_edit`: changes one of @g1t's messages in a direct message with it. */
1323	async internalG1tEdit(a: { workspace: string; channel_id: string; id: string; body: string }): Promise<Result<ChatMessage>> {
1324	const found = await this.internalPlace(a.workspace, a.channel_id);
1325	if (!found.ok) return found;
1326	const place = found.value;
1327	const row = await this.messageRow(place.channel.id, a.id);
1328	if (!row \|\| row.deleted_at \|\| row.author !== G1T_KEY) return fail("not_found", "No such message from g1t.");
1329	const body = messageBody(a.body);
1330	if (!body.ok) return fail("invalid", body.message);
1331	const at = now();
1332	await this.db.prepare("UPDATE messages SET body = ?, edited_at = ? WHERE id = ?").bind(body.body, at, row.id).run();
1333	const [message] = await this.toMessages(place.slug, place.workspace, [{ ...row, body: body.body, edited_at: at }]);
1334	this.broadcast(place.channel.id, { type: "message.updated", message });
1335	return ok(message);
1336	}
1337
1338	/** A direct message with @g1t, by the workspace's slug and the channel's id, for g1t's own services. */
1339	private async internalPlace(slug: string, channelId: string): Promise<Result<Place>> {
1340	const workspace = await this.workspace(String(slug ?? ""));
1341	if (!workspace) return fail("not_found", "No such workspace.");
1342	const channel = await this.db
1343	.prepare("SELECT * FROM channels WHERE id = ? AND workspace_id = ?")
1344	.bind(String(channelId ?? ""), workspace.id)
1345	.first<ChannelRow>();
1346	if (!channel \|\| channel.kind !== "dm" \|\| !g1tDmPerson(await this.memberKeys(channel.id))) return fail("not_found", "No such direct message with g1t.");
1347	return ok({ slug: workspace.slug.toLowerCase(), workspace, channel, member: null });
1348	}
1349
1350	async edit(a: { workspace: string; channel_id: string; viewer: Viewer; id: string; body: string }): Promise<Result<ChatMessage>> {
1351	const found = await this.place(a.workspace, a.channel_id, a.viewer, "read");
1352	if (!found.ok) return found;
1353	const place = found.value;
1354	const row = await this.messageRow(place.channel.id, a.id);
1355	if (!row \|\| row.deleted_at) return fail("not_found", "No such message.");
1356	if (row.author !== userKey(a.viewer!)) return fail("forbidden", "Only its author can edit a message.");
1357	const body = messageBody(a.body, !!row.card);
1358	if (!body.ok) return fail("invalid", body.message);
1359	const at = now();
1360	const mentions = mentionsColumn(mentionedHandles(body.body));
1361	await this.db
1362	.prepare("UPDATE messages SET body = ?, mentions = ?, edited_at = ? WHERE id = ?")
1363	.bind(body.body, mentions, at, row.id)
1364	.run();
1365	const [message] = await this.toMessages(place.slug, place.workspace, [{ ...row, body: body.body, mentions, edited_at: at }]);
1366	this.broadcast(place.channel.id, { type: "message.updated", message });
1367	return ok(message);
1368	}
1369
1370	/** Deletes a message, keeping its place so its thread still hangs together. Its author or a workspace owner may. */
1371	async remove(a: { workspace: string; channel_id: string; viewer: Viewer; id: string }): Promise<Result<null>> {
1372	const found = await this.place(a.workspace, a.channel_id, a.viewer, "read");
1373	if (!found.ok) return found;
1374	const place = found.value;
1375	const row = await this.messageRow(place.channel.id, a.id);
1376	if (!row \|\| row.deleted_at) return fail("not_found", "No such message.");
1377	const me = userKey(a.viewer!);
1378	// In your direct message with @g1t, g1t's messages are yours to delete too.
1379	const g1tsInMine = row.author === G1T_KEY && place.channel.kind === "dm" && g1tDmPerson(await this.memberKeys(place.channel.id)) === me;
1380	if (row.author !== me && !isOwner(a.viewer, a.workspace) && !g1tsInMine) {
1381	return fail("forbidden", "Only its author or a workspace owner can delete a message.");
1382	}
1383	const statements = [
1384	this.db
1385	.prepare("UPDATE messages SET deleted_at = ?, body = '', card = NULL, mentions = '' WHERE id = ?")
1386	.bind(now(), row.id),
1387	];
1388	if (row.thread_root) {
1389	statements.push(
1390	this.db.prepare("UPDATE messages SET reply_count = MAX(reply_count - 1, 0) WHERE id = ?").bind(row.thread_root),
1391	);
1392	}
1393	await this.db.batch(statements);
1394	this.broadcast(place.channel.id, { type: "message.deleted", channel_id: place.channel.id, id: row.id });
1395	if (row.thread_root) this.rebroadcast(place, row.thread_root);
1396	return ok(null);
1397	}
1398
1399	async markRead(a: { workspace: string; channel_id: string; viewer: Viewer; id: string }): Promise<Result<null>> {
1400	const found = await this.place(a.workspace, a.channel_id, a.viewer, "read");
1401	if (!found.ok) return found;
1402	const { channel, member } = found.value;
1403	// Someone reading a public channel they have not joined keeps no read state.
1404	if (!member) return ok(null);
1405	const id = typeof a.id === "string" ? a.id : "";
1406	if (!id) return fail("invalid", "Say which message was read.");
1407	// Only forward: reading an old thread does not mark newer messages unread.
1408	const changed = await this.db
1409	.prepare(
1410	"UPDATE channel_members SET last_read_id = ?1 WHERE channel_id = ?2 AND principal = ?3 AND (last_read_id IS NULL OR last_read_id < ?1)",
1411	)
1412	.bind(id, channel.id, member.principal)
1413	.run();
1414	if (changed.meta.changes) {
1415	this.broadcast(channel.id, {
1416	type: "read",
1417	channel_id: channel.id,
1418	principal: { kind: "user", id: a.viewer!.id },
1419	last_read_id: id,
1420	});
1421	// Notify: the read drops the counts in every tab of theirs.
1422	this.defer(
1423	notifyRead(this.db, this.env.NOTIFY, { slug: found.value.slug, channel_id: channel.id, user_id: a.viewer!.id, username: a.viewer!.username, last_read_id: id }).catch(
1424	(error) => console.error("chat could not notify a read", error),
1425	),
1426	);
1427	}
1428	return ok(null);
1429	}
1430
1431	// ── Reactions (src/emoji.ts) ────────────────────────────────────────────
1432
1433	/** One message's reactions now, as `me` sees them. */
1434	private async reactionsFor(place: Place, messageId: string, me: string): Promise<ChatReaction[]> {
1435	const list = (await this.reactionsOf([messageId], me)).get(messageId) ?? [];
1436	const profiles = await this.profiles(place.slug, place.workspace, list.flatMap((r) => r.by));
1437	return list.map((r) => ({ ...r, by: r.by.map((key) => profiles.get(key)!).filter(Boolean) }));
1438	}
1439
1440	/**
1441	* Adds or takes back `who`'s reaction. Each member reacts once with each
1442	* emoji, a message holds at most 50 different ones, and a workspace's own
1443	* emoji must exist to be used (taking one back never needs it to). The
1444	* room hears of each change.
1445	*/
1446	private async reactTo(place: Place, who: string, messageId: unknown, input: unknown, remove: boolean): Promise<Result<ChatReaction[]>> {
1447	const parsed = reactionEmoji(input);
1448	if (!parsed.ok) return fail("invalid", parsed.message);
1449	const row = await this.messageRow(place.channel.id, String(messageId ?? ""));
1450	if (!row \|\| row.deleted_at) return fail("not_found", "No such message.");
1451	let changed = 0;
1452	if (remove) {
1453	const done = await this.db
1454	.prepare("DELETE FROM reactions WHERE message_id = ? AND principal = ? AND emoji = ?")
1455	.bind(row.id, who, parsed.emoji)
1456	.run();
1457	changed = done.meta.changes;
1458	} else {
1459	if (parsed.custom) {
1460	const known = await this.db
1461	.prepare("SELECT 1 FROM custom_emoji WHERE workspace_id = ? AND name = ? AND deleted_at IS NULL")
1462	.bind(place.workspace.id, parsed.custom)
1463	.first();
1464	if (!known) return fail("not_found", `This workspace has no :${parsed.custom}: emoji.`);
1465	}
1466	const kinds = await this.db.prepare("SELECT DISTINCT emoji FROM reactions WHERE message_id = ?").bind(row.id).all<{ emoji: string }>();
1467	if (!roomForReaction(new Set(kinds.results.map((k) => k.emoji)), parsed.emoji)) {
1468	return fail("invalid", `A message can have at most ${MAX_REACTIONS_PER_MESSAGE} different reactions.`);
1469	}
1470	const done = await this.db
1471	.prepare("INSERT OR IGNORE INTO reactions (message_id, principal, emoji, created_at) VALUES (?, ?, ?, ?)")
1472	.bind(row.id, who, parsed.emoji, now())
1473	.run();
1474	changed = done.meta.changes;
1475	}
1476	if (changed) {
1477	const member = await this.profile(place.slug, place.workspace, who);
1478	this.broadcast(place.channel.id, {
1479	type: remove ? "reaction.removed" : "reaction.added",
1480	channel_id: place.channel.id,
1481	message_id: row.id,
1482	emoji: parsed.emoji,
1483	member,
1484	});
1485	}
1486	return ok(await this.reactionsFor(place, row.id, who));
1487	}
1488
1489	async react(a: { workspace: string; channel_id: string; viewer: Viewer; message_id: string; emoji: string }): Promise<Result<ChatReaction[]>> {
1490	const found = await this.place(a.workspace, a.channel_id, a.viewer, "read");
1491	if (!found.ok) return found;
1492	return this.reactTo(found.value, userKey(a.viewer!), a.message_id, a.emoji, false);
1493	}
1494
1495	async unreact(a: { workspace: string; channel_id: string; viewer: Viewer; message_id: string; emoji: string }): Promise<Result<ChatReaction[]>> {
1496	const found = await this.place(a.workspace, a.channel_id, a.viewer, "read");
1497	if (!found.ok) return found;
1498	return this.reactTo(found.value, userKey(a.viewer!), a.message_id, a.emoji, true);
1499	}
1500
1501	// ── A workspace's own emoji (src/emoji.ts) ──────────────────────────────
1502
1503	private async emojiUpload(workspace: Workspace): Promise<EmojiUpload> {
1504	return settingsOf(await this.settingsRow(workspace)).emoji_upload;
1505	}
1506
1507	private async toEmoji(slug: string, workspace: Workspace, rows: EmojiRow[]): Promise<CustomEmoji[]> {
1508	const profiles = await this.profiles(slug, workspace, rows.map((r) => r.created_by));
1509	return rows.map((row) => ({
1510	name: row.name,
1511	alias_of: row.alias_of,
1512	file: row.file,
1513	content_type: row.content_type,
1514	bytes: row.bytes,
1515	created_by: profiles.get(row.created_by)!,
1516	created_at: row.created_at,
1517	}));
1518	}
1519
1520	private liveEmoji(workspace: Workspace, name: string): Promise<EmojiRow \| null> {
1521	return this.db
1522	.prepare("SELECT * FROM custom_emoji WHERE workspace_id = ? AND name = ? AND deleted_at IS NULL")
1523	.bind(workspace.id, name)
1524	.first<EmojiRow>();
1525	}
1526
1527	async listEmoji(a: { workspace: string; viewer: Viewer }): Promise<Result<EmojiList>> {
1528	const found = await this.viewerWorkspace(a.workspace, a.viewer);
1529	if (!found.ok) return found;
1530	const workspace = found.value;
1531	const [rows, setting] = await Promise.all([
1532	this.db
1533	.prepare("SELECT * FROM custom_emoji WHERE workspace_id = ? AND deleted_at IS NULL ORDER BY name")
1534	.bind(workspace.id)
1535	.all<EmojiRow>(),
1536	this.emojiUpload(workspace),
1537	]);
1538	const role = roleOf(a.viewer, a.workspace);
1539	return ok({
1540	emoji: await this.toEmoji(a.workspace.toLowerCase(), workspace, rows.results),
1541	emoji_upload: setting,
1542	can_upload: mayUpload(setting, role),
1543	can_manage: role === "owner",
1544	});
1545	}
1546
1547	/** Who may add one: checked against the workspace's setting. */
1548	private async mayAdd(workspace: Workspace, viewer: Viewer, slug: string): Promise<Result<null>> {
1549	const setting = await this.emojiUpload(workspace);
1550	if (!mayUpload(setting, roleOf(viewer, slug))) return fail("forbidden", "Only owners can add emoji in this workspace.");
1551	return ok(null);
1552	}
1553
1554	async addEmoji(a: { workspace: string; viewer: Viewer; name: string; file: EmojiFile }): Promise<Result<CustomEmoji>> {
1555	const found = await this.viewerWorkspace(a.workspace, a.viewer);
1556	if (!found.ok) return found;
1557	const workspace = found.value;
1558	const allowed = await this.mayAdd(workspace, a.viewer, a.workspace);
1559	if (!allowed.ok) return allowed;
1560	const named = emojiName(a.name);
1561	if (!named.ok) return fail("invalid", named.message);
1562	if (await this.liveEmoji(workspace, named.name)) return fail("conflict", `:${named.name}: is already taken.`);
1563	const bytes = fromBase64(a.file?.data);
1564	if (!bytes) return fail("invalid", "Choose a PNG, GIF or WebP image of at most 256 KB.");
1565	const checked = emojiImage(bytes);
1566	if (!checked.ok) return fail("invalid", checked.message);
1567	const file = await sha256(bytes);
1568	// Kept by its hash, so the same image stored twice is one file; its
1569	// type is the one read from its bytes (usercontent serves only that).
1570	await this.env.AVATARS.put(`emoji/${file}`, bytes, { metadata: { contentType: checked.image.content_type } });
1571	const row: EmojiRow = {
1572	workspace_id: workspace.id,
1573	name: named.name,
1574	alias_of: null,
1575	file,
1576	content_type: checked.image.content_type,
1577	bytes: bytes.length,
1578	created_by: userKey(a.viewer!),
1579	created_at: now(),
1580	deleted_at: null,
1581	};
1582	const added = await this.insertEmoji(row);
1583	if (!added.ok) return added;
1584	const [emoji] = await this.toEmoji(a.workspace.toLowerCase(), workspace, [row]);
1585	return ok(emoji!);
1586	}
1587
1588	private async insertEmoji(row: EmojiRow): Promise<Result<null>> {
1589	try {
1590	await this.db
1591	.prepare(
1592	"INSERT INTO custom_emoji (workspace_id, name, alias_of, file, content_type, bytes, created_by, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
1593	)
1594	.bind(row.workspace_id, row.name, row.alias_of, row.file, row.content_type, row.bytes, row.created_by, row.created_at)
1595	.run();
1596	return ok(null);
1597	} catch (error) {
1598	if (String(error).includes("UNIQUE")) return fail("conflict", `:${row.name}: is already taken.`);
1599	throw error;
1600	}
1601	}
1602
1603	async aliasEmoji(a: { workspace: string; viewer: Viewer; name: string; target: string }): Promise<Result<CustomEmoji>> {
1604	const found = await this.viewerWorkspace(a.workspace, a.viewer);
1605	if (!found.ok) return found;
1606	const workspace = found.value;
1607	const allowed = await this.mayAdd(workspace, a.viewer, a.workspace);
1608	if (!allowed.ok) return allowed;
1609	const named = emojiName(a.name);
1610	if (!named.ok) return fail("invalid", named.message);
1611	const targetName = String(a.target ?? "").trim().replace(/^:+\|:+$/g, "").toLowerCase();
1612	let target = await this.liveEmoji(workspace, targetName);
1613	// An alias of an alias names the emoji itself, so removing one never strands another.
1614	if (target?.alias_of) target = await this.liveEmoji(workspace, target.alias_of);
1615	if (!target) return fail("not_found", `This workspace has no :${targetName}: emoji.`);
1616	if (await this.liveEmoji(workspace, named.name)) return fail("conflict", `:${named.name}: is already taken.`);
1617	const row: EmojiRow = { ...target, name: named.name, alias_of: target.name, created_by: userKey(a.viewer!), created_at: now(), deleted_at: null };
1618	const added = await this.insertEmoji(row);
1619	if (!added.ok) return added;
1620	const [emoji] = await this.toEmoji(a.workspace.toLowerCase(), workspace, [row]);
1621	return ok(emoji!);
1622	}
1623
1624	async removeEmoji(a: { workspace: string; viewer: Viewer; name: string }): Promise<Result<null>> {
1625	const found = await this.viewerWorkspace(a.workspace, a.viewer);
1626	if (!found.ok) return found;
1627	const workspace = found.value;
1628	const name = String(a.name ?? "").trim().replace(/^:+\|:+$/g, "").toLowerCase();
1629	const row = await this.liveEmoji(workspace, name);
1630	if (!row) return fail("not_found", `This workspace has no :${name}: emoji.`);
1631	if (!mayRemove(row.created_by, userKey(a.viewer!), roleOf(a.viewer, a.workspace))) {
1632	return fail("forbidden", "Only whoever added an emoji, or an owner, can remove it.");
1633	}
1634	// An emoji goes with its aliases; an alias goes alone.
1635	await this.db
1636	.prepare(
1637	"UPDATE custom_emoji SET deleted_at = ?1 WHERE workspace_id = ?2 AND deleted_at IS NULL AND (name = ?3 OR (?4 = 0 AND alias_of = ?3))",
1638	)
1639	.bind(now(), workspace.id, row.name, row.alias_of ? 1 : 0)
1640	.run();
1641	// Its image goes once nothing live shows it, in any workspace.
1642	this.defer(
1643	(async () => {
1644	const used = await this.db.prepare("SELECT 1 FROM custom_emoji WHERE file = ? AND deleted_at IS NULL LIMIT 1").bind(row.file).first();
1645	if (!used) await this.env.AVATARS.delete(`emoji/${row.file}`);
1646	})().catch((error) => console.error("chat could not forget emoji", row.file, error)),
1647	);
1648	return ok(null);
1649	}
1650
1651	async setEmojiUpload(a: { workspace: string; viewer: Viewer; value: EmojiUpload }): Promise<Result<EmojiUpload>> {
1652	const found = await this.viewerWorkspace(a.workspace, a.viewer);
1653	if (!found.ok) return found;
1654	if (roleOf(a.viewer, a.workspace) !== "owner") return fail("forbidden", "Only owners can change who adds emoji.");
1655	if (a.value !== "members" && a.value !== "admins") return fail("invalid", "Choose members or owners.");
1656	// The same setting as Settings → Chat (`setChatSettings`).
1657	const set = await this.setChatSettings({ workspace: a.workspace, viewer: a.viewer, change: { emoji_upload: a.value } });
1658	return set.ok ? ok(set.value.emoji_upload) : set;
1659	}
1660
1661	// ── The live socket ─────────────────────────────────────────────────────
1662
1663	/**
1664	* `GET /live?workspace=<slug>&channel=<id>`, upgraded to a WebSocket. The
1665	* viewer comes in CHAT_VIEWER_HEADER, set by the site after checking the
1666	* session; trusted only because this Worker is reachable through service
1667	* bindings alone (`workers_dev` is off and it has no routes). Checked
1668	* like any read, then handed to the channel's room.
1669	*/
1670	async live(request: Request): Promise<Response> {
1671	if (request.headers.get("upgrade")?.toLowerCase() !== "websocket") {
1672	return new Response("Expected a WebSocket upgrade\n", { status: 426 });
1673	}
1674	let viewer: Viewer = null;
1675	try {
1676	viewer = JSON.parse(request.headers.get(CHAT_VIEWER_HEADER) ?? "null") as Viewer;
1677	} catch {
1678	viewer = null;
1679	}
1680	if (!viewer?.id) return new Response("Sign in to use chat\n", { status: 401 });
1681	const url = new URL(request.url);
1682	const channelId = url.searchParams.get("channel") ?? "";
1683	let slug = (url.searchParams.get("workspace") ?? "").toLowerCase();
1684	if (!slug) {
1685	// Not named: whichever of the viewer's workspaces holds the channel.
1686	const row = await this.db.prepare("SELECT workspace_id FROM channels WHERE id = ?").bind(channelId).first<{ workspace_id: string }>();
1687	if (row) {
1688	const theirs = await Promise.all((viewer.workspaces ?? []).map((m) => this.workspace(m.slug)));
1689	slug = theirs.find((w) => w?.id === row.workspace_id)?.slug.toLowerCase() ?? "";
1690	}
1691	}
1692	const found = await this.place(slug, channelId, viewer, "read");
1693	if (!found.ok) return new Response(`${found.error.message}\n`, { status: found.error.code === "forbidden" ? 403 : 404 });
1694	const { workspace, channel } = found.value;
1695	const who: RoomMember = { channel_id: channel.id, member: await this.profile(slug, workspace, userKey(viewer)) };
1696	const headers = new Headers(request.headers);
1697	headers.delete(CHAT_VIEWER_HEADER);
1698	headers.set(ROOM_MEMBER_HEADER, JSON.stringify(who));
1699	return this.room(channel.id).fetch(new Request(request.url, { method: "GET", headers }));
1700	}
1701	}
1702
1703	/** One RPC method's answer. */
1704	async function answer(service: Chat, method: string, args: any): Promise<Response> {
1705	switch (method) {
1706	case "sidebar":
1707	return Response.json(await service.sidebar(args));
1708	case "activity":
1709	return Response.json(await service.activity(args));
1710	case "channel":
1711	return Response.json(await service.channel(args));
1712	case "channel_by_name":
1713	return Response.json(await service.channelByName(args));
1714	case "browse":
1715	return Response.json(await service.browse(args));
1716	case "create_channel":
1717	return Response.json(await service.createChannel(args));
1718	case "update_channel":
1719	return Response.json(await service.updateChannel(args));
1720	case "chat_settings":
1721	return Response.json(await service.chatSettings(args));
1722	case "set_chat_settings":
1723	return Response.json(await service.setChatSettings(args));
1724	case "open_dm":
1725	return Response.json(await service.openDm(args));
1726	case "join":
1727	return Response.json(await service.join(args));
1728	case "leave":
1729	return Response.json(await service.leave(args));
1730	case "invite":
1731	return Response.json(await service.invite(args));
1732	case "messages":
1733	return Response.json(await service.messages(args));
1734	case "post":
1735	return Response.json(await service.post(args));
1736	case "edit":
1737	return Response.json(await service.edit(args));
1738	case "remove":
1739	return Response.json(await service.remove(args));
1740	case "mark_read":
1741	return Response.json(await service.markRead(args));
1742	case "set_preferences":
1743	return Response.json(await service.setPreferences(args));
1744	case "react":
1745	return Response.json(await service.react(args));
1746	case "unreact":
1747	return Response.json(await service.unreact(args));
1748	case "list_emoji":
1749	return Response.json(await service.listEmoji(args));
1750	case "add_emoji":
1751	return Response.json(await service.addEmoji(args));
1752	case "alias_emoji":
1753	return Response.json(await service.aliasEmoji(args));
1754	case "remove_emoji":
1755	return Response.json(await service.removeEmoji(args));
1756	case "set_emoji_upload":
1757	return Response.json(await service.setEmojiUpload(args));
1758	case "g1t_dm":
1759	return Response.json(await service.g1tDm(args));
1760	case "g1t_reset":
1761	return Response.json(await service.g1tReset(args));
1762	case "g1t_retry":
1763	return Response.json(await service.g1tRetry(args));
1764	case "g1t_stop":
1765	return Response.json(await service.g1tStop(args));
1766	default:
1767	return new Response("Unknown method\n", { status: 404 });
1768	}
1769	}
1770
1771	export default {
1772	async fetch(request: Request, env: Env, ctx: ExecutionContext): Promise<Response> {
1773	const url = new URL(request.url);
1774	if (request.method === "GET" && url.pathname === "/live") {
1775	return new Chat(env, (work) => ctx.waitUntil(work)).live(request);
1776	}
1777	const internal = url.pathname.match(/^\/internal\/([a-z0-9_]+)$/);
1778	if (request.method === "POST" && internal) {
1779	// Only g1t's own services (src/internal.ts).
1780	return internalAnswer(new Chat(env, (work) => ctx.waitUntil(work)), request, internal[1]!);
1781	}
1782	const match = url.pathname.match(/^\/rpc\/([a-z0-9_]+)$/);
1783	if (request.method !== "POST" \|\| !match) return new Response("Not found\n", { status: 404 });
1784	// A replica near the caller when it asks for one (@g1t/contracts d1.ts).
1785	const opened = openD1(env.DB, request);
1786	const service = new Chat(Object.create(env, { DB: { value: opened.db } }) as Env, (work) => ctx.waitUntil(work));
1787	const args = (await request.json().catch(() => ({}))) as any;
1788	return opened.finish(await answer(service, match[1], args));
1789	},
1790	} satisfies ExportedHandler<Env>;