app/db/schema.ts

Functions

Imports

It imports no other module of this repo. Imported by app/lib/ai.server.ts, app/lib/binder.server.ts, app/lib/book-history.server.ts, app/lib/books.server.ts, app/lib/content.server.ts, app/lib/google/drive.server.ts, app/lib/google/oauth.server.ts, app/lib/google/refresh.server.ts, app/lib/google/search-console.server.ts, app/lib/health.server.ts, app/lib/inbox-report.server.ts, app/lib/index.server.ts, app/lib/legal.server.ts, app/lib/mcp/call-log.ts, app/lib/media.server.ts, app/lib/mentions.server.ts, app/lib/people-admin.server.ts, app/lib/people.server.ts, app/lib/projects.server.ts, app/lib/refresh.server.ts, app/lib/social/queue.server.ts, app/lib/voice.server.ts, app/lib/waiting.server.ts, app/lib/writing.server.ts.

Source

1// Mirrors drizzle/0001_init.sql to 0018_voice.sql, which own the shape. Change both in the
2// same commit. The FTS5 table site_items_fts has no mirror: only index.server.ts touches it, in SQL.
3
4import { sql } from "drizzle-orm";
5import { integer, primaryKey, real, sqliteTable, text } from "drizzle-orm/sqlite-core";
6
7export const people = sqliteTable("people", {
8  id: integer("id").primaryKey(),
9  email: text("email").notNull().unique(),
10  name: text("name").notNull().default(""),
11  isOwner: integer("is_owner", { mode: "boolean" }).notNull().default(false),
12  isReviewer: integer("is_reviewer", { mode: "boolean" }).notNull().default(false),
13  disabledAt: text("disabled_at"),
14  createdAt: text("created_at").notNull().default(sql`(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))`),
15});
16
17export const projects = sqliteTable("projects", {
18  id: integer("id").primaryKey(),
19  slug: text("slug").notNull().unique(),
20  name: text("name").notNull(),
21  createdAt: text("created_at").notNull().default(sql`(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))`),
22  site: text("site"),
23  book: text("book"),
24});
25
26export const projectMembers = sqliteTable(
27  "project_members",
28  {
29    projectId: integer("project_id").notNull().references(() => projects.id, { onDelete: "cascade" }),
30    personId: integer("person_id").notNull().references(() => people.id, { onDelete: "cascade" }),
31    role: text("role", { enum: ["reader", "editor"] }).notNull(),
32  },
33  (t) => [primaryKey({ columns: [t.projectId, t.personId] })],
34);
35
36export const healthState = sqliteTable("health_state", {
37  checkName: text("check_name").primaryKey(),
38  ok: integer("ok", { mode: "boolean" }).notNull(),
39  detail: text("detail").notNull(),
40  changedAt: text("changed_at").notNull(),
41});
42
43export const siteItems = sqliteTable(
44  "site_items",
45  {
46    projectId: integer("project_id").notNull().references(() => projects.id, { onDelete: "cascade" }),
47    itemId: text("item_id").notNull(),
48    kind: text("kind").notNull(),
49    title: text("title").notNull(),
50    status: text("status", { enum: ["draft", "scheduled", "published"] }).notNull(),
51    path: text("path"),
52    publishAt: text("publish_at"),
53    publishedAt: text("published_at"),
54    updatedAt: text("updated_at"),
55    bodyUpdatedAt: text("body_updated_at"),
56    syncedAt: text("synced_at").notNull(),
57  },
58  (t) => [primaryKey({ columns: [t.projectId, t.itemId] })],
59);
60
61export const drafts = sqliteTable(
62  "drafts",
63  {
64    projectId: integer("project_id").notNull().references(() => projects.id, { onDelete: "cascade" }),
65    itemId: text("item_id").notNull(),
66    personId: integer("person_id").notNull().references(() => people.id, { onDelete: "cascade" }),
67    source: text("source").notNull(),
68    baseVersion: text("base_version"),
69    updatedAt: text("updated_at").notNull(),
70  },
71  (t) => [primaryKey({ columns: [t.projectId, t.itemId, t.personId] })],
72);
73
74export const changes = sqliteTable("changes", {
75  id: text("id").primaryKey(),
76  projectId: integer("project_id").notNull().references(() => projects.id, { onDelete: "cascade" }),
77  itemId: text("item_id").notNull(),
78  personId: integer("person_id").notNull().references(() => people.id),
79  // Media actions since migration 0007: item_id is then the site's media id, and a file has no version.
80  // content-delete since migration 0009: item_id is the post's id, version_before the version deleted, version_after null.
81  // media-alt, media-tags, media-trash and media-restore since migration 0010: version_before and version_after are the file's metadata versions.
82  action: text("action", { enum: ["save", "publish", "schedule", "unpublish", "media-upload", "media-delete", "content-delete", "media-alt", "media-tags", "media-trash", "media-restore"] }).notNull(),
83  versionBefore: text("version_before"),
84  versionAfter: text("version_after"),
85  createdAt: text("created_at").notNull().default(sql`(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))`),
86  client: text("client"),
87});
88
89// Webmention decisions since migration 0011 (a table of its own: the changes table holds posts and
90// files). mention_id is the site's id for the mention and is null for a sweep; status_after is null
91// for a delete and a sweep; change_id is the id Carrel sent the site, so the two histories join.
92const MENTION_STATUS = ["unverified", "pending", "approved", "rejected", "failed"] as const;
93export const mentionDecisions = sqliteTable("mention_decisions", {
94  id: integer("id").primaryKey(),
95  projectId: integer("project_id").notNull().references(() => projects.id, { onDelete: "cascade" }),
96  mentionId: text("mention_id"),
97  personId: integer("person_id").notNull().references(() => people.id),
98  action: text("action", { enum: ["approve", "reject", "delete", "sweep"] }).notNull(),
99  statusBefore: text("status_before", { enum: MENTION_STATUS }),
100  statusAfter: text("status_after", { enum: MENTION_STATUS }),
101  changeId: text("change_id").notNull().unique(),
102  client: text("client"),
103  createdAt: text("created_at").notNull().default(sql`(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))`),
104});
105
106// The MCP call log (0012_mcp_calls.sql): one row per tool call, append-only (triggers refuse UPDATE
107// and DELETE). No foreign key on the person, so the record outlives them.
108export const mcpCalls = sqliteTable("mcp_calls", {
109  id: integer("id").primaryKey(),
110  personId: integer("person_id").notNull(),
111  caller: text("caller").notNull(),
112  client: text("client").notNull(),
113  tool: text("tool").notNull(),
114  arguments: text("arguments").notNull(),
115  argumentsTruncated: integer("arguments_truncated", { mode: "boolean" }).notNull().default(false),
116  outcome: text("outcome", { enum: ["ok", "refused"] }).notNull(),
117  result: text("result").notNull(),
118  createdAt: text("created_at").notNull().default(sql`(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))`),
119});
120
121export const aiDrafts = sqliteTable("ai_drafts", {
122  id: integer("id").primaryKey(),
123  projectId: integer("project_id").notNull().references(() => projects.id, { onDelete: "cascade" }),
124  itemId: text("item_id").notNull(),
125  personId: integer("person_id").notNull().references(() => people.id, { onDelete: "cascade" }),
126  client: text("client").notNull(),
127  source: text("source").notNull(),
128  baseVersion: text("base_version"),
129  note: text("note").notNull().default(""),
130  createdAt: text("created_at").notNull().default(sql`(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))`),
131});
132
133export const aiPublications = sqliteTable("ai_publications", {
134  changeId: text("change_id").primaryKey(),
135  projectId: integer("project_id").notNull().references(() => projects.id, { onDelete: "cascade" }),
136  itemId: text("item_id").notNull(),
137  personId: integer("person_id").notNull().references(() => people.id),
138  client: text("client").notNull(),
139  version: text("version").notNull(),
140  publishedAt: text("published_at").notNull().default(sql`(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))`),
141  emailedAt: text("emailed_at"),
142  emailError: text("email_error"),
143});
144
145export const bookFiles = sqliteTable(
146  "book_files",
147  {
148    projectId: integer("project_id").notNull().references(() => projects.id, { onDelete: "cascade" }),
149    path: text("path").notNull(),
150    kind: text("kind", { enum: ["scene", "character", "place", "rule", "outline", "book", "note"] }).notNull(),
151    sha: text("sha").notNull(),
152    source: text("source").notNull(),
153    meta: text("meta").notNull().default("{}"),
154    words: integer("words").notNull().default(0),
155    syncedAt: text("synced_at").notNull(),
156  },
157  (t) => [primaryKey({ columns: [t.projectId, t.path] })],
158);
159
160export const novelsShared = sqliteTable("novels_shared", {
161  path: text("path").primaryKey(),
162  sha: text("sha").notNull(),
163  source: text("source").notNull(),
164  syncedAt: text("synced_at").notNull(),
165});
166
167export const findings = sqliteTable("findings", {
168  id: integer("id").primaryKey(),
169  projectId: integer("project_id").notNull().references(() => projects.id, { onDelete: "cascade" }),
170  path: text("path").notNull(),
171  checkName: text("check_name").notNull(),
172  message: text("message").notNull(),
173  line: integer("line"),
174  excerpt: text("excerpt"),
175  fingerprint: text("fingerprint").notNull(),
176  status: text("status", { enum: ["open", "dismissed"] }).notNull().default("open"),
177  dismissedBy: integer("dismissed_by").references(() => people.id),
178  dismissedAt: text("dismissed_at"),
179  createdAt: text("created_at").notNull().default(sql`(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))`),
180});
181
182export const authorship = sqliteTable("authorship", {
183  id: text("id").primaryKey(),
184  projectId: integer("project_id").notNull().references(() => projects.id, { onDelete: "cascade" }),
185  path: text("path").notNull(),
186  personId: integer("person_id").notNull().references(() => people.id),
187  client: text("client"),
188  wordsAdded: integer("words_added").notNull(),
189  wordsRemoved: integer("words_removed").notNull(),
190  versionBefore: text("version_before"),
191  versionAfter: text("version_after").notNull(),
192  commitSha: text("commit_sha").notNull(),
193  createdAt: text("created_at").notNull().default(sql`(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))`),
194  /** What the save counts toward the day: typed words only. Empty before 0012 and for an AI client's save. */
195  wordsTyped: integer("words_typed"),
196});
197
198export const statusLabels = sqliteTable(
199  "status_labels",
200  {
201    projectId: integer("project_id").notNull().references(() => projects.id, { onDelete: "cascade" }),
202    position: integer("position").notNull(),
203    label: text("label").notNull(),
204    color: integer("color").notNull(),
205  },
206  (t) => [primaryKey({ columns: [t.projectId, t.position] })],
207);
208
209export const writingGoals = sqliteTable(
210  "writing_goals",
211  {
212    projectId: integer("project_id").notNull().references(() => projects.id, { onDelete: "cascade" }),
213    personId: integer("person_id").notNull().references(() => people.id, { onDelete: "cascade" }),
214    mode: text("mode", { enum: ["daily", "deadline"] }).notNull().default("daily"),
215    dailyTarget: integer("daily_target"),
216    writingDays: text("writing_days").notNull().default("0123456"),
217    allowNegative: integer("allow_negative", { mode: "boolean" }).notNull().default(false),
218  },
219  (t) => [primaryKey({ columns: [t.projectId, t.personId] })],
220);
221
222export const untypedWords = sqliteTable(
223  "untyped_words",
224  {
225    projectId: integer("project_id").notNull().references(() => projects.id, { onDelete: "cascade" }),
226    personId: integer("person_id").notNull().references(() => people.id, { onDelete: "cascade" }),
227    path: text("path").notNull(),
228    words: integer("words").notNull().default(0),
229  },
230  (t) => [primaryKey({ columns: [t.projectId, t.personId, t.path] })],
231);
232
233export const googleKeysSeen = sqliteTable("google_keys_seen", {
234  privateKeyId: text("private_key_id").primaryKey(),
235  clientEmail: text("client_email").notNull(),
236  firstSeenAt: text("first_seen_at").notNull().default(sql`(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))`),
237});
238
239export const googleTokens = sqliteTable("google_tokens", {
240  personId: integer("person_id").primaryKey().references(() => people.id, { onDelete: "cascade" }),
241  refreshToken: text("refresh_token").notNull(),
242  iv: text("iv").notNull(),
243  scope: text("scope").notNull(),
244  connectedAt: text("connected_at").notNull().default(sql`(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))`),
245  lastError: text("last_error"),
246});
247
248export const googleOauthStates = sqliteTable("google_oauth_states", {
249  state: text("state").primaryKey(),
250  personId: integer("person_id").notNull().references(() => people.id, { onDelete: "cascade" }),
251  createdAt: text("created_at").notNull().default(sql`(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))`),
252});
253
254export const manuscripts = sqliteTable("manuscripts", {
255  fileId: text("file_id").primaryKey(),
256  name: text("name").notNull(),
257  mimeType: text("mime_type").notNull(),
258  webViewLink: text("web_view_link").notNull(),
259  folder: text("folder").notNull().default(""),
260  people: text("people").notNull().default("[]"),
261  createdTime: text("created_time"),
262  modifiedTime: text("modified_time"),
263  syncedAt: text("synced_at").notNull(),
264});
265
266export const sentDocs = sqliteTable("sent_docs", {
267  id: integer("id").primaryKey(),
268  projectId: integer("project_id").notNull().references(() => projects.id, { onDelete: "cascade" }),
269  itemId: text("item_id").notNull(),
270  personId: integer("person_id").notNull().references(() => people.id, { onDelete: "cascade" }),
271  docId: text("doc_id").notNull(),
272  docUrl: text("doc_url").notNull(),
273  frontmatter: text("frontmatter").notNull().default(""),
274  createdAt: text("created_at").notNull().default(sql`(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))`),
275});
276
277export const searchConsolePages = sqliteTable(
278  "search_console_pages",
279  {
280    projectId: integer("project_id").notNull().references(() => projects.id, { onDelete: "cascade" }),
281    page: text("page").notNull(),
282    clicks: integer("clicks").notNull(),
283    impressions: integer("impressions").notNull(),
284    ctr: real("ctr").notNull(),
285    position: real("position").notNull(),
286    startDate: text("start_date").notNull(),
287    endDate: text("end_date").notNull(),
288    fetchedAt: text("fetched_at").notNull(),
289  },
290  (t) => [primaryKey({ columns: [t.projectId, t.page] })],
291);
292
293export const socialAccounts = sqliteTable("social_accounts", {
294  id: integer("id").primaryKey(),
295  key: text("key").notNull().unique(),
296  name: text("name").notNull(),
297  platform: text("platform", { enum: ["bluesky", "x"] }).notNull(),
298  kind: text("kind", { enum: ["personal", "brand"] }).notNull(),
299  handle: text("handle").notNull(),
300  projectId: integer("project_id").references(() => projects.id, { onDelete: "set null" }),
301  mode: text("mode", { enum: ["approval", "auto"] }).notNull().default("approval"),
302  enabled: integer("enabled", { mode: "boolean" }).notNull().default(false),
303  dailyCap: integer("daily_cap").notNull().default(3),
304  minGapMinutes: integer("min_gap_minutes").notNull().default(60),
305  monthlyBudgetMills: integer("monthly_budget_mills").notNull().default(0),
306  template: text("template").notNull().default(""),
307  voiceGuide: text("voice_guide").notNull().default(""),
308  createdAt: text("created_at").notNull().default(sql`(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))`),
309});
310
311export const socialRoutineRuns = sqliteTable("social_routine_runs", {
312  id: integer("id").primaryKey(),
313  kind: text("kind", { enum: ["batch", "nightly"] }).notNull(),
314  requestedAt: text("requested_at").notNull(),
315  events: text("events").notNull(),
316  status: text("status", { enum: ["fired", "failed"] }).notNull(),
317  sessionUrl: text("session_url"),
318  error: text("error"),
319});
320
321export const socialEvents = sqliteTable("social_events", {
322  id: integer("id").primaryKey(),
323  accountId: integer("account_id").notNull().references(() => socialAccounts.id, { onDelete: "cascade" }),
324  projectId: integer("project_id").notNull().references(() => projects.id, { onDelete: "cascade" }),
325  itemId: text("item_id").notNull(),
326  title: text("title").notNull().default(""),
327  url: text("url").notNull().default(""),
328  summary: text("summary").notNull().default(""),
329  publishedAt: text("published_at"),
330  state: text("state", { enum: ["waiting", "routine", "covered", "templated", "closed"] }).notNull().default("waiting"),
331  routineRunId: integer("routine_run_id").references(() => socialRoutineRuns.id),
332  createdAt: text("created_at").notNull().default(sql`(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))`),
333});
334
335export const socialPosts = sqliteTable("social_posts", {
336  id: integer("id").primaryKey(),
337  accountId: integer("account_id").notNull().references(() => socialAccounts.id, { onDelete: "cascade" }),
338  eventId: integer("event_id").notNull().references(() => socialEvents.id, { onDelete: "cascade" }),
339  text: text("text").notNull(),
340  source: text("source", { enum: ["predrafted", "routine", "template"] }).notNull(),
341  status: text("status", { enum: ["drafted", "held", "awaiting", "queued", "sent", "by-hand", "failed", "rejected"] }).notNull(),
342  lint: text("lint").notNull().default("[]"),
343  createdBy: text("created_by").notNull(),
344  approvedBy: integer("approved_by").references(() => people.id),
345  costMills: integer("cost_mills").notNull().default(0),
346  platformPostId: text("platform_post_id"),
347  error: text("error"),
348  createdAt: text("created_at").notNull().default(sql`(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))`),
349  sentAt: text("sent_at"),
350  acknowledgedAt: text("acknowledged_at"),
351});
352
353export const legalSections = sqliteTable("legal_sections", {
354  key: text("key").primaryKey(),
355  title: text("title").notNull(),
356  body: text("body").notNull(),
357  updatedAt: text("updated_at").notNull().default(sql`(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))`),
358  updatedBy: integer("updated_by").notNull().references(() => people.id),
359});
360
361export const bookMoves = sqliteTable("book_moves", {
362  id: integer("id").primaryKey(),
363  projectId: integer("project_id").notNull().references(() => projects.id, { onDelete: "cascade" }),
364  changeId: text("change_id").notNull(),
365  fromPath: text("from_path").notNull(),
366  toPath: text("to_path").notNull(),
367  commitSha: text("commit_sha").notNull(),
368  personId: integer("person_id").notNull().references(() => people.id),
369  createdAt: text("created_at").notNull().default(sql`(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))`),
370});
371
372// The voice pack and the cuts learned from the author's edits (0018_voice.sql, voice.server.ts).
373export const voicePacks = sqliteTable("voice_packs", {
374  projectId: integer("project_id").primaryKey().references(() => projects.id, { onDelete: "cascade" }),
375  guide: text("guide").notNull().default(""),
376  phrases: text("phrases").notNull().default("[]"),
377  allow: text("allow").notNull().default("[]"),
378  profile: text("profile"),
379  excerpts: text("excerpts").notNull().default("[]"),
380  posts: integer("posts").notNull().default(0),
381  builtAt: text("built_at"),
382  updatedAt: text("updated_at").notNull().default(sql`(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))`),
383});
384
385export const voiceCuts = sqliteTable(
386  "voice_cuts",
387  {
388    projectId: integer("project_id").notNull().references(() => projects.id, { onDelete: "cascade" }),
389    phrase: text("phrase").notNull(),
390    items: text("items").notNull().default("[]"),
391    kept: integer("kept").notNull().default(0),
392    setAside: integer("set_aside", { mode: "boolean" }).notNull().default(false),
393    lastCutAt: text("last_cut_at").notNull().default(sql`(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))`),
394  },
395  (t) => [primaryKey({ columns: [t.projectId, t.phrase] })],
396);