Claude-SharepointLists/.claude/skills/sharepoint-lists/scripts/lib/graph.mjs

// SharePoint Lists core client — Microsoft Graph, app-only (client credentials).
//
// Zero dependencies. Uses Node's global fetch (Node 18+). This module is the
// single source of truth for talking to SharePoint Lists; the CLI (sp.mjs) is a
// thin wrapper around it, and a future MCP server can import this same class.
//
// Auth model: app-only via OAuth2 client credentials. The app registration must
// hold an *application* permission such as Sites.Selected (preferred) or
// Sites.ReadWrite.All, with admin consent granted. See references/setup.md.

const LOGIN_HOST = "https://login.microsoftonline.com";
const GRAPH_ROOT = "https://graph.microsoft.com/v1.0";

const GUID_RE = /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i;

/** Error carrying the Graph status code + structured error body, when present. */
export class GraphError extends Error {
  constructor(message, { status, code, requestId, body } = {}) {
    super(message);
    this.name = "GraphError";
    this.status = status;
    this.code = code;
    this.requestId = requestId;
    this.body = body;
  }
}

export class SharePointListsClient {
  /**
   * @param {object} cfg
   * @param {string} cfg.tenantId    Directory (tenant) ID or domain.
   * @param {string} cfg.clientId    Application (client) ID.
   * @param {string} cfg.clientSecret Client secret value.
   * @param {number} [cfg.maxRetries=3] Retries for 429/503 (honors Retry-After).
   */
  constructor({ tenantId, clientId, clientSecret, maxRetries = 3 } = {}) {
    if (!tenantId || !clientId || !clientSecret) {
      throw new Error(
        "Missing credentials: tenantId, clientId, and clientSecret are all required.",
      );
    }
    this.tenantId = tenantId;
    this.clientId = clientId;
    this.clientSecret = clientSecret;
    this.maxRetries = maxRetries;
    this._token = null; // { accessToken, expiresAt }
    this._siteCache = new Map(); // siteUrl -> siteId
    this._listCache = new Map(); // `${siteId}::${nameOrId}` -> list object
  }

  // ---- Auth ---------------------------------------------------------------

  async getToken() {
    const now = Date.now();
    if (this._token && this._token.expiresAt - 60_000 > now) {
      return this._token.accessToken;
    }
    const url = `${LOGIN_HOST}/${encodeURIComponent(this.tenantId)}/oauth2/v2.0/token`;
    const body = new URLSearchParams({
      grant_type: "client_credentials",
      client_id: this.clientId,
      client_secret: this.clientSecret,
      scope: "https://graph.microsoft.com/.default",
    });
    const res = await fetch(url, {
      method: "POST",
      headers: { "Content-Type": "application/x-www-form-urlencoded" },
      body,
    });
    const json = await res.json().catch(() => ({}));
    if (!res.ok) {
      throw new GraphError(
        `Token request failed (${res.status}): ${json.error_description || json.error || res.statusText}`,
        { status: res.status, code: json.error, body: json },
      );
    }
    this._token = {
      accessToken: json.access_token,
      expiresAt: now + (json.expires_in ?? 3600) * 1000,
    };
    return this._token.accessToken;
  }

  // ---- Low-level request --------------------------------------------------

  /**
   * Make a Graph request. `path` is relative to the v1.0 root (e.g. "/sites/...").
   * @param {string} method
   * @param {string} path
   * @param {object} [opts]
   * @param {object} [opts.query]    Query params; keys starting with "$" are OData.
   * @param {object} [opts.body]     JSON body (will be stringified).
   * @param {object} [opts.headers]  Extra headers (e.g. Prefer).
   */
  async graph(method, path, { query, body, headers } = {}) {
    const url = new URL(path.startsWith("http") ? path : `${GRAPH_ROOT}${path}`);
    if (query) {
      for (const [k, v] of Object.entries(query)) {
        if (v !== undefined && v !== null && v !== "") url.searchParams.set(k, v);
      }
    }
    for (let attempt = 0; ; attempt++) {
      const token = await this.getToken();
      const res = await fetch(url, {
        method,
        headers: {
          Authorization: `Bearer ${token}`,
          Accept: "application/json",
          ...(body ? { "Content-Type": "application/json" } : {}),
          ...headers,
        },
        body: body ? JSON.stringify(body) : undefined,
      });

      if ((res.status === 429 || res.status === 503) && attempt < this.maxRetries) {
        const retryAfter = Number(res.headers.get("Retry-After")) || 2 ** attempt;
        await sleep(retryAfter * 1000);
        continue;
      }

      if (res.status === 204) return null; // No Content (e.g. DELETE)
      const json = await res.json().catch(() => null);
      if (!res.ok) {
        const err = json?.error || {};
        throw new GraphError(
          `Graph ${method} ${url.pathname} failed (${res.status}): ${err.message || res.statusText}`,
          {
            status: res.status,
            code: err.code,
            requestId: res.headers.get("request-id"),
            body: json,
          },
        );
      }
      return json;
    }
  }

  // ---- Site & list resolution --------------------------------------------

  /**
   * Resolve a site URL (or host:path, or raw site id) to a Graph site id.
   * Accepts:
   *   - "https://contoso.sharepoint.com/sites/Marketing"
   *   - "contoso.sharepoint.com:/sites/Marketing"
   *   - "contoso.sharepoint.com,<guid>,<guid>" (already a site id) -> returned as-is
   */
  async resolveSiteId(site) {
    if (!site) throw new Error("A site URL or id is required.");
    // Already a composite site id (host,guid,guid)?
    if (site.includes(",")) return site;
    if (this._siteCache.has(site)) return this._siteCache.get(site);

    let host, relPath;
    if (site.startsWith("http://") || site.startsWith("https://")) {
      const u = new URL(site);
      host = u.host;
      relPath = u.pathname.replace(/\/$/, "");
    } else if (site.includes(":")) {
      [host, relPath] = site.split(":");
      relPath = (relPath || "").replace(/\/$/, "");
    } else {
      host = site; // bare hostname -> root site
      relPath = "";
    }

    const path = relPath
      ? `/sites/${host}:${encodeURI(relPath)}`
      : `/sites/${host}`;
    const data = await this.graph("GET", path, { query: { $select: "id,displayName,webUrl" } });
    this._siteCache.set(site, data.id);
    return data.id;
  }

  /** List all lists in a site. */
  async listLists(siteId, { select = "id,name,displayName,webUrl,list" } = {}) {
    const data = await this.graph("GET", `/sites/${siteId}/lists`, {
      query: { $select: select },
    });
    return data.value;
  }

  /**
   * Resolve a list identifier (GUID, internal name, or display name) to its id.
   * Caches per (siteId, identifier).
   */
  async resolveListId(siteId, listIdOrName) {
    if (!listIdOrName) throw new Error("A list id or name is required.");
    if (GUID_RE.test(listIdOrName)) return listIdOrName;

    const key = `${siteId}::${listIdOrName}`;
    if (this._listCache.has(key)) return this._listCache.get(key).id;

    const lists = await this.listLists(siteId);
    const needle = listIdOrName.toLowerCase();
    const match = lists.find(
      (l) =>
        l.name?.toLowerCase() === needle ||
        l.displayName?.toLowerCase() === needle,
    );
    if (!match) {
      const names = lists.map((l) => l.displayName || l.name).join(", ");
      throw new Error(
        `No list named "${listIdOrName}" in this site. Available: ${names || "(none)"}`,
      );
    }
    this._listCache.set(key, match);
    return match.id;
  }

  /** Get a single list's metadata. */
  async getList(siteId, listIdOrName) {
    const listId = await this.resolveListId(siteId, listIdOrName);
    return this.graph("GET", `/sites/${siteId}/lists/${listId}`);
  }

  /** Get column definitions (internal names, types) for a list. */
  async getColumns(siteId, listIdOrName) {
    const listId = await this.resolveListId(siteId, listIdOrName);
    const data = await this.graph("GET", `/sites/${siteId}/lists/${listId}/columns`);
    return data.value;
  }

  // ---- Items: read --------------------------------------------------------

  /**
   * List items with their field values.
   * @param {object} [opts]
   * @param {string} [opts.filter]  OData $filter, e.g. "fields/Status eq 'Open'".
   * @param {string} [opts.select]  Fields to return, e.g. "Title,Status".
   * @param {string} [opts.orderby] e.g. "fields/Created desc".
   * @param {number} [opts.top]     Page size / max when not fetching all.
   * @param {boolean}[opts.all]     Follow @odata.nextLink to fetch every page.
   */
  async listItems(siteId, listIdOrName, opts = {}) {
    const listId = await this.resolveListId(siteId, listIdOrName);
    const { filter, select, orderby, top, all = false } = opts;

    // Filtering/sorting on non-indexed columns requires this Prefer header.
    const headers = filter || orderby
      ? { Prefer: "HonorNonIndexedQueriesWarningMayFailRandomly" }
      : undefined;

    const expand = select
      ? `fields($select=${select})`
      : "fields";
    const query = {
      expand,
      $filter: filter,
      $orderby: orderby,
      $top: top,
    };

    let data = await this.graph("GET", `/sites/${siteId}/lists/${listId}/items`, {
      query,
      headers,
    });
    const items = data.value.map(simplifyItem);
    if (!all) return items;

    while (data["@odata.nextLink"]) {
      data = await this.graph("GET", data["@odata.nextLink"], { headers });
      items.push(...data.value.map(simplifyItem));
    }
    return items;
  }

  /** Get a single item by id, including its fields. */
  async getItem(siteId, listIdOrName, itemId) {
    const listId = await this.resolveListId(siteId, listIdOrName);
    const data = await this.graph(
      "GET",
      `/sites/${siteId}/lists/${listId}/items/${itemId}`,
      { query: { expand: "fields" } },
    );
    return simplifyItem(data);
  }

  // ---- Items: write -------------------------------------------------------

  /** Create an item. `fields` is a map of internal column name -> value. */
  async createItem(siteId, listIdOrName, fields) {
    const listId = await this.resolveListId(siteId, listIdOrName);
    const data = await this.graph(
      "POST",
      `/sites/${siteId}/lists/${listId}/items`,
      { body: { fields } },
    );
    return simplifyItem(data);
  }

  /** Update an item's fields (partial; only provided fields change). */
  async updateItem(siteId, listIdOrName, itemId, fields) {
    const listId = await this.resolveListId(siteId, listIdOrName);
    const data = await this.graph(
      "PATCH",
      `/sites/${siteId}/lists/${listId}/items/${itemId}/fields`,
      { body: fields },
    );
    return { id: String(itemId), fields: data };
  }

  /** Delete an item by id. Returns true on success. */
  async deleteItem(siteId, listIdOrName, itemId) {
    const listId = await this.resolveListId(siteId, listIdOrName);
    await this.graph(
      "DELETE",
      `/sites/${siteId}/lists/${listId}/items/${itemId}`,
    );
    return true;
  }

  // ---- People -------------------------------------------------------------

  /**
   * Resolve a person to the numeric LookupId that person/group columns store,
   * by querying the site's hidden "User Information List" (its item id IS the
   * LookupId). Write the result to `<Column>LookupId`, e.g.
   * `{ ProjectManagerLookupId: 53 }`.
   *
   * Limitation: the hidden list only contains people who have touched the site
   * (visited it, been granted access, or been referenced before). SharePoint's
   * ensureUser endpoint — which force-adds a user — requires certificate-based
   * app-only auth that client secrets can't provide, so an unknown user must
   * first be given access to the site (or @-mentioned/assigned once in the UI).
   *
   * @param {string} siteId Graph site id (from resolveSiteId).
   * @param {string} email  The person's email (case-insensitive).
   * @returns {{ lookupId: number, displayName: string, email: string }}
   */
  async resolvePersonByEmail(siteId, email) {
    const needle = String(email).trim().toLowerCase();
    if (!needle) throw new Error("An email is required.");
    const base = `/sites/${siteId}/lists/User%20Information%20List/items`;
    const headers = { Prefer: "HonorNonIndexedQueriesWarningMayFailRandomly" };
    const expand = "fields($select=EMail,UserName,Title)";
    const esc = needle.replace(/'/g, "''");

    // Fast path: server-side filter on EMail, then UserName (some entries only
    // carry the address there).
    for (const field of ["EMail", "UserName"]) {
      try {
        const data = await this.graph("GET", base, {
          query: { expand, $filter: `fields/${field} eq '${esc}'` },
          headers,
        });
        if (data.value?.length) return personHit(data.value[0]);
      } catch {
        break; // non-indexed filter refused — fall through to the full scan
      }
    }

    // Slow path: scan the list (it's small on most sites) and match in code.
    let data = await this.graph("GET", base, { query: { expand, $top: 200 }, headers });
    for (;;) {
      for (const item of data.value) {
        const f = item.fields ?? {};
        if ([f.EMail, f.UserName].some((v) => v?.toLowerCase() === needle)) {
          return personHit(item);
        }
      }
      if (!data["@odata.nextLink"]) break;
      data = await this.graph("GET", data["@odata.nextLink"], { headers });
    }

    throw new Error(
      `No user with email "${email}" in this site's user information list. ` +
        `They may never have accessed the site — grant them access (or assign ` +
        `them to any item once in the SharePoint UI), then retry.`,
    );
  }

  // ---- Convenience --------------------------------------------------------

  /** Verify credentials + connectivity. Returns the resolved site (if given). */
  async test(site) {
    await this.getToken();
    if (!site) return { ok: true, token: "acquired" };
    const siteId = await this.resolveSiteId(site);
    const lists = await this.listLists(siteId);
    return {
      ok: true,
      siteId,
      listCount: lists.length,
      lists: lists.map((l) => l.displayName || l.name),
    };
  }
}

/**
 * Report how close the app's client secret is to expiring, so consumers can
 * warn BEFORE auth starts failing with an opaque 401 (AADSTS7000222).
 *
 * Azure can't be queried for its own secret's expiry with app-only creds, so
 * the date is self-reported: set SP_SECRET_EXPIRES=YYYY-MM-DD in .env when you
 * create/rotate the secret (the Entra portal shows it in Certificates & secrets).
 *
 * @param {string} [expiresStr] The configured date; falsy → null (feature unused).
 * @returns {null | { expiresAt: string, daysLeft: number, level: "ok"|"warning"|"expired", message: string|null }}
 */
export function secretExpiryStatus(expiresStr, { warnDays = 30 } = {}) {
  if (!expiresStr) return null;
  const exp = new Date(expiresStr);
  if (Number.isNaN(exp.getTime())) {
    return {
      expiresAt: expiresStr,
      daysLeft: NaN,
      level: "warning",
      message: `SP_SECRET_EXPIRES is set but unparseable ("${expiresStr}") — use YYYY-MM-DD.`,
    };
  }
  const daysLeft = Math.floor((exp.getTime() - Date.now()) / 86_400_000);
  const expiresAt = exp.toISOString().slice(0, 10);
  if (daysLeft < 0) {
    return {
      expiresAt,
      daysLeft,
      level: "expired",
      message: `Client secret EXPIRED ${-daysLeft} day(s) ago (${expiresAt}). Auth will fail with 401 — create a new secret in Entra (Certificates & secrets), update SP_CLIENT_SECRET and SP_SECRET_EXPIRES.`,
    };
  }
  if (daysLeft <= warnDays) {
    return {
      expiresAt,
      daysLeft,
      level: "warning",
      message: `Client secret expires in ${daysLeft} day(s) (${expiresAt}) — rotate it soon: new secret in Entra, then update SP_CLIENT_SECRET and SP_SECRET_EXPIRES.`,
    };
  }
  return { expiresAt, daysLeft, level: "ok", message: null };
}

/** Shape a User Information List item into a person-resolution result. */
function personHit(item) {
  const f = item.fields ?? {};
  return {
    lookupId: Number(item.id),
    displayName: f.Title ?? "",
    email: f.EMail || f.UserName || "",
  };
}

/** Flatten the Graph item shape into { id, fields, webUrl, ...meta }. */
function simplifyItem(item) {
  if (!item) return item;
  const { id, webUrl, createdDateTime, lastModifiedDateTime, fields } = item;
  return { id, webUrl, createdDateTime, lastModifiedDateTime, fields: fields ?? {} };
}

function sleep(ms) {
  return new Promise((r) => setTimeout(r, ms));
}

export default SharePointListsClient;