/** * Membership, invites, password-reset links and agent ownership over SQL (RFC-0005 §3, §7, §11). * Rows live in Better Auth's organization tables (`organization`, `member`, `invitation`; one * organization per deployment) plus `agent_owner` and `password_reset` (migration 0012). Every * multi-row change is one guarded statement or one atomic `batch()` (AGENTS.md rule 4); the * database itself refuses a second Owner or a second membership (unique indexes). * * Worker-safe: no Node imports. */ import { isRole, type Role } from "@cloudhal/agent-runtime"; import type { SqlDriver, SqlStatement } from "@cloudhal/storage"; import { providerRevocation } from "./auth/grants.ts"; import { countHit, pruneCounters, randomPrune } from "./rate-limits.ts"; import { allSiteRoles, inviteSiteRoles, parseSiteRoles, replaceSiteRoles, type SiteRoles, toRoles, } from "./site-members.ts"; /** The one organization of a deployment (Cloud: one per tenant). */ export const DEPLOYMENT_ORG = "deployment"; export interface MemberRow { /** Better Auth user id. */ userId: string; name: string; email: string; role: Role; joinedAt: string; /** RFC-0034 A2: the sites an Editor, Author or Contributor is limited to (absent: every site). */ sites?: SiteRoles; } export type InviteStatus = "pending" | "accepted" | "canceled" | "expired"; export interface InviteRow { id: string; email: string; role: Role; status: InviteStatus; inviterId: string; inviterName: string | null; delivery: "link" | "email"; createdAt: string; expiresAt: string; acceptedUserId: string | null; acceptedAt: string | null; canceledBy: string | null; canceledAt: string | null; /** RFC-0034 A2: the sites the new member will be limited to. */ sites?: SiteRoles; } export interface PasswordResetRow { id: string; userId: string; createdBy: string; createdAt: string; expiresAt: string; usedAt: string | null; } type Row = Record; const ALPHANUM = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789"; /** A Better Auth–style id (32 alphanumerics). */ export function newRowId(): string { const bytes = crypto.getRandomValues(new Uint8Array(32)); return Array.from(bytes, (b) => ALPHANUM[b % ALPHANUM.length]).join(""); } function iso(value: unknown): string { if (value instanceof Date) return value.toISOString(); if (typeof value === "number") return new Date(value).toISOString(); return String(value ?? ""); } function toMember(r: Row): MemberRow | undefined { if (!isRole(r.role)) return undefined; return { userId: r.userId as string, name: (r.name as string | null) ?? "", email: (r.email as string | null) ?? "", role: r.role, joinedAt: iso(r.createdAt), }; } export class StaffStore { readonly #sql: SqlDriver; readonly #now: () => Date; readonly #prune: () => boolean; /** `prune` decides per hit whether to delete expired rate-limit counters (tests pin it). */ constructor(sql: SqlDriver, now: () => Date = () => new Date(), prune = randomPrune) { this.#sql = sql; this.#now = now; this.#prune = prune; } // ───────────────────────────── members ───────────────────────────── /** * One query with the member's site assignments (it runs on every signed-in API request, and a * second round trip to D1 there slowed every screen). */ async member(userId: string): Promise { const rows = await this.#sql.all( `SELECT m."userId", m."role", m."createdAt", u."name", u."email", s.site_id AS "siteId", s.role AS "siteRole" FROM "member" m JOIN "user" u ON u."id" = m."userId" LEFT JOIN site_members s ON s.user_id = m."userId" WHERE m."organizationId" = ? AND m."userId" = ? ORDER BY s.site_id`, [DEPLOYMENT_ORG, userId], ); const member = rows[0] ? toMember(rows[0]) : undefined; if (!member) return undefined; const sites = toRoles( rows .filter((r) => typeof r.siteId === "string") .map((r) => ({ site_id: r.siteId as string, role: String(r.siteRole) })), ); return sites ? { ...member, sites } : member; } /** Members by rank, then join date. */ async list(): Promise { const rows = await this.#sql.all( `SELECT m."userId", m."role", m."createdAt", u."name", u."email" FROM "member" m JOIN "user" u ON u."id" = m."userId" WHERE m."organizationId" = ? ORDER BY m."createdAt", m."userId"`, [DEPLOYMENT_ORG], ); const order: Role[] = ["owner", "administrator", "editor", "author", "contributor"]; const sites = await allSiteRoles(this.#sql); return rows .map(toMember) .filter((m): m is MemberRow => m !== undefined) .map((m) => (sites.has(m.userId) ? { ...m, sites: sites.get(m.userId) as SiteRoles } : m)) .sort((a, b) => order.indexOf(a.role) - order.indexOf(b.role)); } /** * The deployment's name (shown on invites): the organization's, unless it still has the default * from a migration that ran before any site existed; then the first site's. */ async deploymentName(): Promise { const row = await this.#sql.get<{ name: string }>( `SELECT "name" FROM "organization" WHERE "id" = ?`, [DEPLOYMENT_ORG], ); if (row?.name && row.name !== "Cloudhal") return row.name; const site = await this.#sql.get<{ name: string | null }>( "SELECT json_extract(body, '$.name') AS name FROM nodes WHERE kind = 'site' ORDER BY created_at, id LIMIT 1", ); return site?.name ?? row?.name ?? "Cloudhal"; } async owner(): Promise { return (await this.list()).find((m) => m.role === "owner"); } /** Adds a membership; false when the user already has one (or `owner` is taken). */ async join(userId: string, role: Role): Promise { try { const r = await this.#sql.run( `INSERT INTO "member" ("id", "organizationId", "userId", "role", "createdAt") SELECT ?, ?, ?, ?, ? WHERE NOT EXISTS (SELECT 1 FROM "member" WHERE "organizationId" = ? AND "userId" = ?)`, [newRowId(), DEPLOYMENT_ORG, userId, role, this.#stamp(), DEPLOYMENT_ORG, userId], ); return r.changes === 1; } catch { return false; // unique index: a concurrent join, or a second owner } } /** * The first account of a deployment becomes the Owner (one statement: only while nobody is); * otherwise it joins as `fallback` (open sign-up: Contributor), or not at all. */ async joinAsOwnerOr(userId: string, fallback: Role | undefined): Promise { try { const r = await this.#sql.run( `INSERT INTO "member" ("id", "organizationId", "userId", "role", "createdAt") SELECT ?, ?, ?, 'owner', ? WHERE NOT EXISTS (SELECT 1 FROM "member" WHERE "organizationId" = ? AND ("role" = 'owner' OR "userId" = ?))`, [newRowId(), DEPLOYMENT_ORG, userId, this.#stamp(), DEPLOYMENT_ORG, userId], ); if (r.changes === 1) return "owner"; } catch { // a concurrent first sign-up took the Owner } if (!fallback) return undefined; return (await this.join(userId, fallback)) ? fallback : (await this.member(userId))?.role; } /** Changes a role, guarded on the role the caller saw (a concurrent change voids it). */ async setRole(userId: string, from: Role, to: Role): Promise { if (from === "owner" || to === "owner") return false; const [r] = await this.#sql.batch([ { sql: `UPDATE "member" SET "role" = ? WHERE "organizationId" = ? AND "userId" = ? AND "role" = ?`, params: [to, DEPLOYMENT_ORG, userId, from], }, // An Administrator works on every site: their site rows go with the promotion. ...(to === "administrator" ? [{ sql: "DELETE FROM site_members WHERE user_id = ?", params: [userId] }] : []), ]); return r?.changes === 1; } /** RFC-0034 A2: replaces the member's site assignments (empty: every site). */ async setSites(userId: string, sites: SiteRoles): Promise { await this.#sql.batch(replaceSiteRoles(userId, sites, this.#stamp())); } /** Deletes a membership (never the Owner's). */ async remove(userId: string): Promise { const gone = ` AND NOT EXISTS (SELECT 1 FROM "member" WHERE "organizationId" = ? AND "userId" = ?)`; const unlessMember = (s: SqlStatement): SqlStatement => ({ sql: `${s.sql}${gone}`, params: [...(s.params ?? []), DEPLOYMENT_ORG, userId], }); const at = this.#stamp(); const [r] = await this.#sql.batch([ { sql: `DELETE FROM "member" WHERE "organizationId" = ? AND "userId" = ? AND "role" <> 'owner'`, params: [DEPLOYMENT_ORG, userId], }, unlessMember({ sql: "DELETE FROM site_members WHERE user_id = ?", params: [userId] }), // RFC-0044: their OAuth connections end in the same batch (grants, the provider's consent, // refresh tokens and pending codes), so a failure later in the removal leaves none alive. unlessMember({ sql: "UPDATE agent_grants SET revoked_at = ? WHERE user_id = ? AND revoked_at IS NULL", params: [at, userId], }), ...providerRevocation(userId, undefined, at).map(unlessMember), ]); return r?.changes === 1; } /** * Ownership transfer (RFC-0005 §3): one batch; each statement is guarded on both rows' current * roles, and the last one asserts the outcome, so a concurrent change voids the whole operation. * The caller is demoted first so the one-Owner index never sees two. */ async transfer(fromUserId: string, toUserId: string): Promise { if (fromUserId === toUserId) return false; const org = DEPLOYMENT_ORG; try { await this.#sql.batch([ { sql: `UPDATE "member" SET "role" = 'administrator' WHERE "organizationId" = ? AND "userId" = ? AND "role" = 'owner' AND EXISTS (SELECT 1 FROM "member" WHERE "organizationId" = ? AND "userId" = ? AND "role" = 'administrator')`, params: [org, fromUserId, org, toUserId], }, { sql: `UPDATE "member" SET "role" = 'owner' WHERE "organizationId" = ? AND "userId" = ? AND "role" = 'administrator' AND NOT EXISTS (SELECT 1 FROM "member" WHERE "organizationId" = ? AND "role" = 'owner')`, params: [org, toUserId, org], }, // Assertion: exactly the target is the Owner now, else NOT NULL fails and nothing applies. this.#assert( `(SELECT COUNT(*) FROM "member" WHERE "organizationId" = ? AND "role" = 'owner' AND "userId" = ?) = 1`, [org, toUserId], ), ]); return true; } catch { return false; } } /** * Operator recovery (`cloudhal owner transfer`, deployment token): the member `toUserId` becomes * the Owner and the current Owner (if any) an Administrator, in one batch. */ async forceOwner(toUserId: string): Promise<{ from: string | null } | undefined> { const current = await this.owner(); if (current?.userId === toUserId) return { from: toUserId }; const org = DEPLOYMENT_ORG; try { await this.#sql.batch([ { sql: `UPDATE "member" SET "role" = 'administrator' WHERE "organizationId" = ? AND "role" = 'owner' AND EXISTS (SELECT 1 FROM "member" WHERE "organizationId" = ? AND "userId" = ?)`, params: [org, org, toUserId], }, { sql: `UPDATE "member" SET "role" = 'owner' WHERE "organizationId" = ? AND "userId" = ?`, params: [org, toUserId], }, this.#assert( `(SELECT COUNT(*) FROM "member" WHERE "organizationId" = ? AND "role" = 'owner' AND "userId" = ?) = 1`, [org, toUserId], ), ]); return { from: current?.userId ?? null }; } catch { return undefined; } } // ───────────────────────────── agents ───────────────────────────── /** Records whose key an agent actor uses (kept after the key is deleted). */ async recordAgent(agentId: string, userId: string, keyId: string): Promise { await this.#sql.run( "INSERT INTO agent_owner (agent_id, user_id, key_id, created_at) VALUES (?, ?, ?, ?) ON CONFLICT (agent_id) DO NOTHING", [agentId, userId, keyId, this.#stamp()], ); } /** The Better Auth user id behind an agent actor. */ async userOfAgent(agentId: string): Promise { const row = await this.#sql.get<{ user_id: string }>( "SELECT user_id FROM agent_owner WHERE agent_id = ?", [agentId], ); return row?.user_id; } /** Agent actor ids of every key a person ever created. */ async agentsOf(userId: string): Promise { const rows = await this.#sql.all<{ agent_id: string }>( "SELECT agent_id FROM agent_owner WHERE user_id = ? ORDER BY created_at LIMIT 500", [userId], ); return rows.map((r) => r.agent_id); } /** Disables every key of a person (removal, RFC-0005 §3); kept for audit, deletable. */ async disableKeysOf(userId: string): Promise { const r = await this.#sql.run( `UPDATE "apikey" SET "enabled" = 0, "updatedAt" = ? WHERE "referenceId" = ? AND ("enabled" IS NULL OR "enabled" <> 0)`, [this.#stamp(), userId], ); return r.changes; } // ───────────────────────────── invites ───────────────────────────── /** * Stores an invite (only the token's hash) and cancels any pending invite for the same email * (re-inviting replaces it), in one batch. */ async createInvite(input: { email: string; role: Role; inviterId: string; expiresAt: string; delivery: "link" | "email"; tokenHash: string; sites?: SiteRoles | undefined; }): Promise { const id = newRowId(); const now = this.#stamp(); await this.#sql.batch([ { sql: `UPDATE "invitation" SET "status" = 'canceled', "canceledBy" = ?, "canceledAt" = ? WHERE "organizationId" = ? AND "email" = ? AND "status" = 'pending'`, params: [input.inviterId, now, DEPLOYMENT_ORG, input.email], }, { sql: `INSERT INTO "invitation" ("id", "organizationId", "email", "role", "status", "expiresAt", "createdAt", "inviterId", "tokenHash", "delivery", "sites") VALUES (?, ?, ?, ?, 'pending', ?, ?, ?, ?, ?, ?)`, params: [ id, DEPLOYMENT_ORG, input.email, input.role, input.expiresAt, now, input.inviterId, input.tokenHash, input.delivery, input.sites && Object.keys(input.sites).length > 0 ? JSON.stringify(input.sites) : null, ], }, ]); return (await this.invite(id)) as InviteRow; } async invite(id: string): Promise { const row = await this.#sql.get(`${INVITE_SELECT} WHERE i."id" = ?`, [id]); return row ? this.#toInvite(row) : undefined; } async inviteByHash(tokenHash: string): Promise { const row = await this.#sql.get(`${INVITE_SELECT} WHERE i."tokenHash" = ?`, [tokenHash]); return row ? this.#toInvite(row) : undefined; } /** Pending invites (and recent accepted ones without an account), newest first. */ async listInvites(): Promise { const rows = await this.#sql.all( `${INVITE_SELECT} WHERE i."organizationId" = ? AND (i."status" = 'pending' OR (i."status" = 'accepted' AND i."acceptedUserId" IS NULL)) ORDER BY i."createdAt" DESC LIMIT 200`, [DEPLOYMENT_ORG], ); return rows.map((r) => this.#toInvite(r)); } /** Invites accepted since `since` that `inviterId` sent (`member-joined` notifications). */ async acceptedInvitesBy(inviterId: string, since: string): Promise { const rows = await this.#sql.all( `${INVITE_SELECT} WHERE i."organizationId" = ? AND i."inviterId" = ? AND i."status" = 'accepted' AND i."acceptedUserId" IS NOT NULL AND i."acceptedAt" >= ? ORDER BY i."acceptedAt" DESC LIMIT 50`, [DEPLOYMENT_ORG, inviterId, since], ); return rows.map((r) => this.#toInvite(r)); } async countPending(): Promise { const row = await this.#sql.get<{ n: number }>( `SELECT COUNT(*) AS n FROM "invitation" WHERE "organizationId" = ? AND "status" = 'pending' AND "expiresAt" > ?`, [DEPLOYMENT_ORG, this.#stamp()], ); return Number(row?.n ?? 0); } async countCreatedSince(inviterId: string, since: string): Promise { const row = await this.#sql.get<{ n: number }>( `SELECT COUNT(*) AS n FROM "invitation" WHERE "organizationId" = ? AND "inviterId" = ? AND "createdAt" >= ?`, [DEPLOYMENT_ORG, inviterId, since], ); return Number(row?.n ?? 0); } /** * Claims an invite for acceptance with one guarded statement (RFC-0005 §7.1): `changes = 1` * wins; anything else is gone (used, revoked, expired, unknown). */ async claimInvite(tokenHash: string): Promise { const now = this.#stamp(); const r = await this.#sql.run( `UPDATE "invitation" SET "status" = 'accepted', "acceptedAt" = ? WHERE "tokenHash" = ? AND "status" = 'pending' AND "expiresAt" > ?`, [now, tokenHash, now], ); return r.changes === 1; } /** Account creation failed after the claim: the invite becomes usable again. */ async releaseInvite(id: string): Promise { await this.#sql.run( `UPDATE "invitation" SET "status" = 'pending', "acceptedAt" = NULL WHERE "id" = ? AND "status" = 'accepted' AND "acceptedUserId" IS NULL`, [id], ); } /** * Binds an accepted invite to its account and inserts the membership, guarded on the invite * being accepted by exactly this user (one batch). */ async completeInvite(invite: InviteRow, userId: string): Promise { try { const [bind, insert] = await this.#sql.batch([ { sql: `UPDATE "invitation" SET "acceptedUserId" = ? WHERE "id" = ? AND "status" = 'accepted' AND "acceptedUserId" IS NULL`, params: [userId, invite.id], }, { sql: `INSERT INTO "member" ("id", "organizationId", "userId", "role", "createdAt") SELECT ?, ?, ?, ?, ? WHERE EXISTS (SELECT 1 FROM "invitation" WHERE "id" = ? AND "status" = 'accepted' AND "acceptedUserId" = ? AND "role" = ?)`, params: [ newRowId(), DEPLOYMENT_ORG, userId, invite.role, this.#stamp(), invite.id, userId, invite.role, ], }, ...inviteSiteRoles(invite.id, userId, invite.sites ?? {}, this.#stamp()), ]); return bind?.changes === 1 && insert?.changes === 1; } catch { return false; } } /** Revokes a pending invite. */ async cancelInvite(id: string, by: string): Promise { const r = await this.#sql.run( `UPDATE "invitation" SET "status" = 'canceled', "canceledBy" = ?, "canceledAt" = ? WHERE "id" = ? AND "organizationId" = ? AND "status" = 'pending'`, [by, this.#stamp(), id, DEPLOYMENT_ORG], ); return r.changes === 1; } /** Cancels every pending invite a person sent (their removal). */ async cancelInvitesBy(inviterId: string, by: string): Promise { const r = await this.#sql.run( `UPDATE "invitation" SET "status" = 'canceled', "canceledBy" = ?, "canceledAt" = ? WHERE "organizationId" = ? AND "inviterId" = ? AND "status" = 'pending'`, [by, this.#stamp(), DEPLOYMENT_ORG, inviterId], ); return r.changes; } // ───────────────────────────── password resets ───────────────────────────── /** Stores a reset link (hash only); an older unused link for the same user stops working. */ async createReset(input: { userId: string; createdBy: string; expiresAt: string; tokenHash: string; }): Promise { const id = newRowId(); const now = this.#stamp(); await this.#sql.batch([ { sql: "UPDATE password_reset SET used_at = ? WHERE user_id = ? AND used_at IS NULL", params: [now, input.userId], }, { sql: "INSERT INTO password_reset (id, token_hash, user_id, created_by, created_at, expires_at) VALUES (?, ?, ?, ?, ?, ?)", params: [id, input.tokenHash, input.userId, input.createdBy, now, input.expiresAt], }, ]); return { id, userId: input.userId, createdBy: input.createdBy, createdAt: now, expiresAt: input.expiresAt, usedAt: null, }; } /** A usable (unused, unexpired) reset link. */ async resetByHash(tokenHash: string): Promise { const row = await this.#sql.get( "SELECT * FROM password_reset WHERE token_hash = ? AND used_at IS NULL AND expires_at > ?", [tokenHash, this.#stamp()], ); return row ? { id: row.id as string, userId: row.user_id as string, createdBy: row.created_by as string, createdAt: row.created_at as string, expiresAt: row.expires_at as string, usedAt: null, } : undefined; } /** Uses a reset link once (guarded). */ async claimReset(tokenHash: string): Promise { const now = this.#stamp(); const r = await this.#sql.run( "UPDATE password_reset SET used_at = ? WHERE token_hash = ? AND used_at IS NULL AND expires_at > ?", [now, tokenHash, now], ); return r.changes === 1; } // ───────────────────────────── rate limits ───────────────────────────── /** * Fixed-window counter under keys of our own (`cloudhal:…`) in `rate_limits` (`./rate-limits.ts`, * #168): one atomic upsert per hit, and now and then a prune of expired counters. Returns false * once `max` requests were seen within `windowMs`. */ async hit(key: string, windowMs: number, max: number): Promise { const now = this.#now().getTime(); const count = await countHit(this.#sql, { key, windowMs, now }); if (this.#prune()) await pruneCounters(this.#sql, now); return count <= max; } // ───────────────────────────── helpers ───────────────────────────── #stamp(): string { return this.#now().toISOString(); } /** A batch statement that fails (rolling the batch back) unless `condition` holds. */ #assert(condition: string, params: (string | number)[]) { return { sql: `INSERT INTO op_guards (op_id, seq, ok, created_at) SELECT ?, 0, CASE WHEN ${condition} THEN 1 END, ?`, params: [`members_${newRowId()}`, ...params, this.#stamp()], }; } #toInvite(r: Row): InviteRow { const status = r.status as string; const expired = status === "pending" && iso(r.expiresAt) <= this.#stamp(); return { id: r.id as string, email: r.email as string, role: (isRole(r.role) ? r.role : "contributor") as Role, status: expired ? "expired" : (status as InviteStatus), inviterId: r.inviterId as string, inviterName: (r.inviterName as string | null) ?? null, delivery: r.delivery === "email" ? "email" : "link", createdAt: iso(r.createdAt), expiresAt: iso(r.expiresAt), acceptedUserId: (r.acceptedUserId as string | null) ?? null, acceptedAt: r.acceptedAt ? iso(r.acceptedAt) : null, canceledBy: (r.canceledBy as string | null) ?? null, canceledAt: r.canceledAt ? iso(r.canceledAt) : null, ...(parseSiteRoles(r.sites) ? { sites: parseSiteRoles(r.sites) as SiteRoles } : {}), }; } } const INVITE_SELECT = `SELECT i.*, u."name" AS "inviterName" FROM "invitation" i LEFT JOIN "user" u ON u."id" = i."inviterId"`;