app/db/members.ts

Functions

Imports

It imports app/db/client.ts, app/db/schema.ts. Imported by app/lab/access-sync.server.ts, app/lab/actor.server.ts, app/lab/members.server.ts.

Source

1// The /lab workspace's people (drizzle/0030_lab_members.sql). D1 is the source. Read uncached on every request, so a
2// removal or an expiry takes effect on the next click; written only by app/lab/members.server.ts, each change in
3// one batch with its member_audit row.
4import { and, asc, desc, eq, gt, inArray, lte, sql } from "drizzle-orm";
5import type { MemberRow } from "@dustinedwards/site-runtime/members";
6
7import { getDb } from "./client";
8import { memberAudit, memberTerms, members } from "./schema";
9
10export type SyncState = "pending" | "accepted" | "failed" | "not_needed";
11
12export type MemberAuditRow = {
13  at: string;
14  actor: string;
15  actor_title: string;
16  action: string;
17  member_email: string | null;
18  before: string | null;
19  after: string | null;
20  reason: string | null;
21  cloudflare: SyncState;
22  cloudflare_error?: string | null;
23};
24
25const ROW = {
26  email: members.email,
27  name: members.name,
28  title: members.title,
29  end_date: members.endDate,
30  status: members.status,
31};
32
33function asMemberRow(row: { email: string; name: string; title: string; end_date: string | null; status: string }): MemberRow {
34  return { ...row, status: row.status as MemberRow["status"] };
35}
36
37/** One person's row by their normalized email, or null. */
38export async function getMember(env: Env, email: string): Promise<MemberRow | null> {
39  const row = await getDb(env).select(ROW).from(members).where(eq(members.email, email)).get();
40  return row ? asMemberRow(row) : null;
41}
42
43/** Everyone ever added, removed and expired people included, by name. */
44export async function listMembers(env: Env): Promise<MemberRow[]> {
45  const rows = await getDb(env).select(ROW).from(members).orderBy(asc(members.name), asc(members.email));
46  return rows.map(asMemberRow);
47}
48
49/** The semester end dates, earliest first. */
50export async function listTerms(env: Env): Promise<string[]> {
51  const rows = await getDb(env).select({ endDate: memberTerms.endDate }).from(memberTerms).orderBy(asc(memberTerms.endDate));
52  return rows.map((r) => r.endDate);
53}
54
55/** Each person's latest change and whether the allow list has caught up with it. */
56export async function latestSyncByEmail(env: Env): Promise<Map<string, { cloudflare: SyncState; error: string | null }>> {
57  const latest = getDb(env)
58    .select({ id: sql<number>`max(${memberAudit.id})`.as("id") })
59    .from(memberAudit)
60    .where(sql`${memberAudit.memberEmail} IS NOT NULL`)
61    .groupBy(memberAudit.memberEmail);
62  const rows = await getDb(env)
63    .select({ email: memberAudit.memberEmail, cloudflare: memberAudit.cloudflare, error: memberAudit.cloudflareError })
64    .from(memberAudit)
65    .where(inArray(memberAudit.id, latest));
66  return new Map(rows.map((r) => [r.email as string, { cloudflare: r.cloudflare as SyncState, error: r.error }]));
67}
68
69/** One person's changes, newest first. */
70export async function memberHistory(env: Env, email: string) {
71  return getDb(env).select().from(memberAudit).where(eq(memberAudit.memberEmail, email)).orderBy(desc(memberAudit.id));
72}
73
74/**
75 * The emails whose removal or expiry the allow list has not confirmed yet, and who are not active again: their seats
76 * are still to free. A person removed and then added back is never named, or Capsid would free a member's seat.
77 */
78export async function unsyncedRemovals(env: Env): Promise<string[]> {
79  const rows = await getDb(env)
80    .selectDistinct({ email: memberAudit.memberEmail })
81    .from(memberAudit)
82    .innerJoin(members, eq(members.email, memberAudit.memberEmail))
83    .where(
84      sql`${memberAudit.cloudflare} IN ('pending', 'failed') AND ${memberAudit.action} IN ('remove', 'expire') AND ${members.status} <> 'active'`,
85    );
86  return rows.map((r) => r.email).filter((e): e is string => !!e);
87}
88
89/** The newest audit row's id, or 0: a sync settles only the changes that existed when it read the list. */
90export async function latestAuditId(env: Env): Promise<number> {
91  const row = await getDb(env).select({ id: sql<number>`coalesce(max(${memberAudit.id}), 0)` }).from(memberAudit).get();
92  return row?.id ?? 0;
93}
94
95/** Whether a change newer than `id` still waits on Access: another sync must carry it. */
96export async function pendingAfter(env: Env, id: number): Promise<boolean> {
97  const row = await getDb(env)
98    .select({ id: memberAudit.id })
99    .from(memberAudit)
100    .where(and(gt(memberAudit.id, id), eq(memberAudit.cloudflare, "pending")))
101    .get();
102  return !!row;
103}
104
105function auditValues(audit: MemberAuditRow) {
106  return {
107    at: audit.at,
108    actor: audit.actor,
109    actorTitle: audit.actor_title,
110    action: audit.action,
111    memberEmail: audit.member_email,
112    beforeRow: audit.before,
113    afterRow: audit.after,
114    reason: audit.reason,
115    cloudflare: audit.cloudflare,
116    cloudflareError: audit.cloudflare_error ?? null,
117  };
118}
119
120/**
121 * A person's new row and its audit row, together or not at all. `addedBy` is kept from the first add: a re-add of a
122 * removed person replaces their title and dates, not who first added them.
123 */
124export async function writeMemberChange(env: Env, after: MemberRow, actorEmail: string, audit: MemberAuditRow): Promise<void> {
125  const db = getDb(env);
126  await db.batch([
127    db
128      .insert(members)
129      .values({ email: after.email, name: after.name ?? "", title: after.title, endDate: after.end_date, status: after.status, addedBy: actorEmail })
130      .onConflictDoUpdate({
131        target: members.email,
132        // A blank name never overwrites the one kept on the row, so past runs and edits keep it.
133        set: { name: sql`coalesce(nullif(${after.name ?? ""}, ''), ${members.name})`, title: after.title, endDate: after.end_date, status: after.status, updatedAt: sql`(unixepoch())` },
134      }),
135    db.insert(memberAudit).values(auditValues(audit)),
136  ]);
137}
138
139/** Several people's changes in one batch, as a term review saves them. */
140export async function writeMemberChanges(env: Env, changes: Array<{ after: MemberRow; audit: MemberAuditRow }>): Promise<void> {
141  if (changes.length === 0) return;
142  const db = getDb(env);
143  const statements = changes.flatMap(({ after, audit }) => [
144    db
145      .update(members)
146      .set({ title: after.title, endDate: after.end_date, status: after.status, updatedAt: sql`(unixepoch())` })
147      .where(eq(members.email, after.email)),
148    db.insert(memberAudit).values(auditValues(audit)),
149  ]);
150  await db.batch(statements as [(typeof statements)[number], ...(typeof statements)[number][]]);
151}
152
153/** Replaces the semester end dates with `dates`, with the change's audit row. */
154export async function writeTerms(env: Env, dates: string[], actorEmail: string, audit: MemberAuditRow): Promise<void> {
155  const db = getDb(env);
156  await db.batch([
157    db.delete(memberTerms),
158    ...dates.map((endDate) => db.insert(memberTerms).values({ endDate, addedBy: actorEmail })),
159    db.insert(memberAudit).values(auditValues(audit)),
160  ]);
161}
162
163/**
164 * Records the Access sync's answer on every change up to `uptoId` still waiting on it: one sync carries the whole list
165 * as it stood when it was read, so it settles every change before that and none after.
166 */
167export async function recordSync(env: Env, state: "accepted" | "failed", error: string | null, uptoId: number): Promise<void> {
168  await getDb(env)
169    .update(memberAudit)
170    .set({ cloudflare: state, cloudflareError: error })
171    .where(and(inArray(memberAudit.cloudflare, ["pending", "failed"]), lte(memberAudit.id, uptoId)));
172}