app/lib/google/drive.server.ts

Functions

Imports

It imports app/db/schema.ts, app/lib/content.server.ts, app/lib/google/oauth.server.ts, app/lib/google/service-account.server.ts, app/lib/people.server.ts, app/lib/projects.server.ts, app/lib/roles.ts, app/lib/timed-fetch.server.ts. Imported by app/lib/google/refresh.server.ts, app/routes/editor.tsx, app/routes/manuscripts.tsx.

Source

1// Drive, two ways (design section 5, "Manuscripts" and "Google Docs"):
2//
3// - The manuscripts index, through the service account: the shared folder's files and their
4//   metadata (name, type, dates, owners and last editor), refreshed so files added later appear.
5//   Manuscript text never enters Carrel; search asks Drive's own full-text search each time.
6// - Send to Docs and Import, through Dustin's drive.file grant: a post's body goes out as a new Doc,
7//   and comes back as a new draft, never over one.
8
9import { and, asc, desc, eq, notInArray } from "drizzle-orm";
10import { drizzle } from "drizzle-orm/d1";
11
12import { manuscripts, sentDocs } from "~/db/schema";
13import { autosave, readDoc, readDraft } from "~/lib/content.server";
14import type { Viewer } from "~/lib/people.server";
15import type { SiteProject } from "~/lib/projects.server";
16import { can } from "~/lib/roles";
17
18import { userAccessToken } from "./oauth.server";
19import { GoogleNotConnected, saClient } from "./service-account.server";
20import { timedFetch } from "~/lib/timed-fetch.server";
21
22type Fetch = typeof fetch;
23
24const DRIVE = "https://www.googleapis.com/drive/v3/files";
25const UPLOAD = "https://www.googleapis.com/upload/drive/v3/files";
26const FOLDER = "application/vnd.google-apps.folder";
27const FILE_FIELDS = "id,name,mimeType,webViewLink,createdTime,modifiedTime,owners(displayName,emailAddress),lastModifyingUser(displayName,emailAddress)";
28/** How many folders one refresh walks; a manuscripts folder is far smaller. */
29const MAX_FOLDERS = 200;
30
31/** The folder Dustin shares with the service account. A var, read loosely like CARREL_ORIGIN. */
32export function manuscriptsFolder(env: Env): string | null {
33  const id = (env as { GOOGLE_MANUSCRIPTS_FOLDER_ID?: string }).GOOGLE_MANUSCRIPTS_FOLDER_ID?.trim();
34  return id && /^[A-Za-z0-9_-]+$/.test(id) ? id : null;
35}
36
37/** Manuscripts are Dustin's alone (design section 3, "Roles"). */
38function requireOwner(viewer: Viewer) {
39  if (!viewer.isOwner) throw new Response("Not found", { status: 404 });
40}
41
42type DriveFile = {
43  id: string;
44  name: string;
45  mimeType: string;
46  webViewLink?: string;
47  createdTime?: string;
48  modifiedTime?: string;
49  owners?: { displayName?: string; emailAddress?: string }[];
50  lastModifyingUser?: { displayName?: string; emailAddress?: string };
51};
52
53/** Drive's query language quotes with single quotes and escapes with a backslash. */
54function quote(value: string): string {
55  return `'${value.replace(/\\/g, "\\\\").replace(/'/g, "\\'")}'`;
56}
57
58async function listAll(sa: ReturnType<typeof saClient>, q: string): Promise<DriveFile[]> {
59  const out: DriveFile[] = [];
60  let pageToken: string | undefined;
61  do {
62    const params = new URLSearchParams({ q, fields: `nextPageToken,files(${FILE_FIELDS})`, pageSize: "1000", supportsAllDrives: "true", includeItemsFromAllDrives: "true" });
63    if (pageToken) params.set("pageToken", pageToken);
64    const res = await sa.request("GET", `${DRIVE}?${params}`);
65    if (!res.ok) throw new Error(`Drive did not list the folder (${res.status}).`);
66    const body = (await res.json()) as { files: DriveFile[]; nextPageToken?: string };
67    out.push(...body.files);
68    pageToken = body.nextPageToken;
69  } while (pageToken);
70  return out;
71}
72
73function peopleOf(f: DriveFile): string[] {
74  const names = [...(f.owners ?? []), ...(f.lastModifyingUser ? [f.lastModifyingUser] : [])].map((p) => p.displayName || p.emailAddress || "").filter(Boolean);
75  return [...new Set(names)];
76}
77
78export type ManuscriptRefresh = { files: number; removed: number; folders: number; more: boolean };
79
80/** Walks the shared folder and its subfolders, upserts every file, and drops what Drive no longer has. */
81export async function refreshManuscripts(env: Env, fetcher?: Fetch, now = new Date()): Promise<ManuscriptRefresh> {
82  const root = manuscriptsFolder(env);
83  if (!root) throw new GoogleNotConnected("GOOGLE_MANUSCRIPTS_FOLDER_ID is not set to the shared folder's id.");
84  const sa = saClient(env, fetcher);
85  const at = now.toISOString();
86  const queue: { id: string; path: string }[] = [{ id: root, path: "" }];
87  const files: (DriveFile & { folder: string })[] = [];
88  let folders = 0;
89  while (queue.length > 0 && folders < MAX_FOLDERS) {
90    const folder = queue.shift()!;
91    folders++;
92    for (const f of await listAll(sa, `${quote(folder.id)} in parents and trashed = false`)) {
93      if (f.mimeType === FOLDER) queue.push({ id: f.id, path: folder.path ? `${folder.path}/${f.name}` : f.name });
94      else files.push({ ...f, folder: folder.path });
95    }
96  }
97  const d = drizzle(env.DB);
98  for (const f of files) {
99    const values = {
100      name: f.name,
101      mimeType: f.mimeType,
102      webViewLink: f.webViewLink ?? `https://drive.google.com/file/d/${f.id}/view`,
103      folder: f.folder,
104      people: JSON.stringify(peopleOf(f)),
105      createdTime: f.createdTime ?? null,
106      modifiedTime: f.modifiedTime ?? null,
107      syncedAt: at,
108    };
109    await d.insert(manuscripts).values({ fileId: f.id, ...values }).onConflictDoUpdate({ target: manuscripts.fileId, set: values });
110  }
111  // Only a complete walk may drop rows; a walk cut short would make unwalked files look deleted.
112  let removed = 0;
113  if (queue.length === 0) {
114    const seen = files.map((f) => f.id);
115    const gone = await d
116      .delete(manuscripts)
117      .where(seen.length ? notInArray(manuscripts.fileId, seen) : undefined)
118      .returning({ id: manuscripts.fileId });
119    removed = gone.length;
120  }
121  return { files: files.length, removed, folders, more: queue.length > 0 };
122}
123
124export type Manuscript = { fileId: string; name: string; mimeType: string; webViewLink: string; folder: string; people: string[]; modifiedTime: string | null };
125
126export async function listManuscripts(db: D1Database, viewer: Viewer): Promise<Manuscript[]> {
127  requireOwner(viewer);
128  const rows = await drizzle(db).select().from(manuscripts).orderBy(desc(manuscripts.modifiedTime), asc(manuscripts.name)).all();
129  return rows.map((r) => ({ fileId: r.fileId, name: r.name, mimeType: r.mimeType, webViewLink: r.webViewLink, folder: r.folder, people: JSON.parse(r.people) as string[], modifiedTime: r.modifiedTime }));
130}
131
132export type ManuscriptSearch = { files: Manuscript[]; searched: "text" | "titles" };
133
134/**
135 * Full-text search through Drive, limited to the files in the index (the shared folder): the words
136 * are matched by Google against the documents where they live, and only which files matched comes
137 * back to Carrel. Google's docs do not say whether a metadata-only scope may run a full-text query;
138 * if Drive refuses one, the search falls back to titles and says so.
139 */
140export async function searchManuscripts(env: Env, viewer: Viewer, words: string, fetcher?: Fetch): Promise<ManuscriptSearch> {
141  requireOwner(viewer);
142  const q = words.trim().slice(0, 200);
143  const all = await listManuscripts(env.DB, viewer);
144  if (!q) return { files: all, searched: "text" };
145  const sa = saClient(env, fetcher);
146  const notFolder = `trashed = false and mimeType != ${quote(FOLDER)}`;
147  let searched: ManuscriptSearch["searched"] = "text";
148  let hits: DriveFile[];
149  try {
150    hits = await listAll(sa, `fullText contains ${quote(q)} and ${notFolder}`);
151  } catch {
152    searched = "titles";
153    hits = await listAll(sa, `name contains ${quote(q)} and ${notFolder}`);
154  }
155  const ids = new Set(hits.map((h) => h.id));
156  // Anything else the service account can see is not a manuscript; only indexed files count.
157  return { files: all.filter((m) => ids.has(m.fileId)), searched };
158}
159
160// ---------- Send to Docs and Import, over drive.file
161
162function splitFrontmatter(source: string): { frontmatter: string; body: string } {
163  const m = /^---\n[\s\S]*?\n---\n?/.exec(source.replace(/\r\n/g, "\n"));
164  return m ? { frontmatter: m[0].endsWith("\n") ? m[0] : `${m[0]}\n`, body: source.replace(/\r\n/g, "\n").slice(m[0].length) } : { frontmatter: "", body: source };
165}
166
167/** Sending outside Carrel is the Owner's (design section 3, "Roles": send externally). */
168function requireSend(project: SiteProject) {
169  if (!can(project.role, "send_external")) throw new Response("Forbidden", { status: 403 });
170}
171
172/**
173 * Copies a post into a new Google Doc in Dustin's Drive: his working draft if he has one, else the
174 * site's text. Only the body goes; the frontmatter stays in Carrel for Import to put back.
175 */
176export async function sendToDocs(env: Env, project: SiteProject, viewer: Viewer, itemId: string, fetcher: Fetch = timedFetch) {
177  requireSend(project);
178  const draft = await readDraft(env.DB, project, viewer, itemId);
179  const doc = draft ? null : await readDoc(env, project, itemId, fetcher);
180  const source = draft?.source ?? doc?.source;
181  if (source === undefined) return { ok: false as const, message: "There is nothing to send yet." };
182  const { frontmatter, body } = splitFrontmatter(source);
183  const title = /^title:\s*"?(.*?)"?\s*$/m.exec(frontmatter)?.[1] || doc?.title || itemId;
184
185  const token = await userAccessToken(env, viewer.id, fetcher);
186  const boundary = `carrel-${crypto.randomUUID()}`;
187  const multipart = [
188    `--${boundary}`,
189    "Content-Type: application/json; charset=UTF-8",
190    "",
191    JSON.stringify({ name: `${title} (from Carrel)`, mimeType: "application/vnd.google-apps.document" }),
192    `--${boundary}`,
193    "Content-Type: text/markdown; charset=UTF-8",
194    "",
195    body,
196    `--${boundary}--`,
197    "",
198  ].join("\r\n");
199  const res = await fetcher(`${UPLOAD}?uploadType=multipart&fields=id,webViewLink`, {
200    method: "POST",
201    headers: { Authorization: `Bearer ${token}`, "Content-Type": `multipart/related; boundary=${boundary}` },
202    body: multipart,
203  });
204  if (!res.ok) return { ok: false as const, message: `Google Docs did not accept the post (${res.status}).` };
205  const created = (await res.json()) as { id: string; webViewLink?: string };
206  const url = created.webViewLink ?? `https://docs.google.com/document/d/${created.id}/edit`;
207  await drizzle(env.DB).insert(sentDocs).values({ projectId: project.id, itemId, personId: viewer.id, docId: created.id, docUrl: url, frontmatter });
208  return { ok: true as const, docId: created.id, url };
209}
210
211export async function lastSentDoc(db: D1Database, project: SiteProject, itemId: string) {
212  return (
213    (await drizzle(db)
214      .select({ docId: sentDocs.docId, url: sentDocs.docUrl, frontmatter: sentDocs.frontmatter, createdAt: sentDocs.createdAt })
215      .from(sentDocs)
216      .where(and(eq(sentDocs.projectId, project.id), eq(sentDocs.itemId, itemId)))
217      .orderBy(desc(sentDocs.createdAt), desc(sentDocs.id))
218      .get()) ?? null
219  );
220}
221
222/**
223 * Brings the Doc's text back as a new draft, never over one: with a working draft open, it is
224 * refused until that draft is saved or discarded. The frontmatter kept at Send goes back on top.
225 */
226export async function importFromDocs(env: Env, project: SiteProject, viewer: Viewer, itemId: string, fetcher: Fetch = timedFetch) {
227  requireSend(project);
228  const sent = await lastSentDoc(env.DB, project, itemId);
229  if (!sent) return { ok: false as const, message: "This post has not been sent to Google Docs from Carrel, so there is no Doc to import." };
230  if (await readDraft(env.DB, project, viewer, itemId)) {
231    return { ok: false as const, message: "You have a working draft of this post. Save it to the site or discard it first; an import never replaces a draft." };
232  }
233  const token = await userAccessToken(env, viewer.id, fetcher);
234  const res = await fetcher(`${DRIVE}/${encodeURIComponent(sent.docId)}/export?mimeType=text%2Fmarkdown`, { headers: { Authorization: `Bearer ${token}` } });
235  if (!res.ok) return { ok: false as const, message: `Google did not export the Doc (${res.status}). It may have been deleted or moved out of reach.` };
236  const body = (await res.text()).replace(/\r\n/g, "\n");
237  const current = await readDoc(env, project, itemId, fetcher);
238  await autosave(env.DB, project, viewer, itemId, { source: `${sent.frontmatter}${body}`, baseVersion: current?.version ?? null });
239  return { ok: true as const, words: body.split(/\s+/).filter(Boolean).length };
240}