addeed chathistory functionality

15 files changed, 522 insertions, 118 deletions

diff --git a/apps/web/app/(dash)/chat/[chatid]/page.tsx b/apps/web/app/(dash)/chat/[chatid]/page.tsx
new file mode 100644
index 00000000..531facba
--- /dev/null
+++ b/apps/web/app/(dash)/chat/[chatid]/page.tsx
@@ -0,0 +1,35 @@
+import { getFullChatThread } from "@/app/actions/fetchers";
+import { chatSearchParamsCache } from "@/lib/searchParams";
+import ChatWindow from "../chatWindow";
+
+async function Page({
+  params,
+  searchParams,
+}: {
+  params: { chatid: string };
+  searchParams: Record<string, string | string[] | undefined>;
+}) {
+  const { firstTime, q, spaces } = chatSearchParamsCache.parse(searchParams);
+
+  const chat = await getFullChatThread(params.chatid);
+
+  console.log(chat);
+
+  if (!chat.success || !chat.data) {
+    // TODO: handle this error
+    return <div>Chat not found</div>;
+  }
+
+  console.log(chat.data);
+
+  return (
+    <ChatWindow
+      q={q}
+      spaces={spaces}
+      initialChat={chat.data.length > 0 ? chat.data : undefined}
+      threadId={params.chatid}
+    />
+  );
+}
+
+export default Page;
diff --git a/apps/web/app/(dash)/chat/actions.ts b/apps/web/app/(dash)/chat/actions.ts
deleted file mode 100644
index e69de29b..00000000
--- a/apps/web/app/(dash)/chat/actions.ts
+++ /dev/null
diff --git a/apps/web/app/(dash)/chat/chatWindow.tsx b/apps/web/app/(dash)/chat/chatWindow.tsx
index 23f49554..97530f60 100644
--- a/apps/web/app/(dash)/chat/chatWindow.tsx
+++ b/apps/web/app/(dash)/chat/chatWindow.tsx
@@ -23,16 +23,12 @@ import { codeLanguageSubset } from "@/lib/constants";
 import { z } from "zod";
 import { toast } from "sonner";
 import Link from "next/link";
+import { createChatObject } from "@/app/actions/doers";
 
 function ChatWindow({
   q,
   spaces,
-}: {
-  q: string;
-  spaces: { id: string; name: string }[];
-}) {
-  const [layout, setLayout] = useState<"chat" | "initial">("initial");
-  const [chatHistory, setChatHistory] = useState<ChatHistory[]>([
+  initialChat = [
     {
       question: q,
       answer: {
@@ -40,7 +36,18 @@ function ChatWindow({
         sources: [],
       },
     },
-  ]);
+  ],
+  threadId,
+}: {
+  q: string;
+  spaces: { id: string; name: string }[];
+  initialChat?: ChatHistory[];
+  threadId: string;
+}) {
+  const [layout, setLayout] = useState<"chat" | "initial">(
+    initialChat.length > 1 ? "chat" : "initial",
+  );
+  const [chatHistory, setChatHistory] = useState<ChatHistory[]>(initialChat);
   const [isAutoScroll, setIsAutoScroll] = useState(true);
 
   const removeJustificationFromText = (text: string) => {
@@ -61,7 +68,7 @@ function ChatWindow({
 
   const getAnswer = async (query: string, spaces: string[]) => {
     const sourcesFetch = await fetch(
-      `/api/chat?q=${query}&spaces=${spaces}&sourcesOnly=true`,
+      `/api/chat?q=${query}&spaces=${spaces}&sourcesOnly=true&threadId=${threadId}`,
       {
         method: "POST",
         body: JSON.stringify({ chatHistory }),
@@ -84,74 +91,108 @@ function ChatWindow({
       toast.error("Something went wrong while getting the sources");
       return;
     }
-
-    setChatHistory((prevChatHistory) => {
-      window.scrollTo({
-        top: document.documentElement.scrollHeight,
-        behavior: "smooth",
-      });
-      const newChatHistory = [...prevChatHistory];
-      const lastAnswer = newChatHistory[newChatHistory.length - 1];
-      if (!lastAnswer) return prevChatHistory;
-      const filteredSourceUrls = new Set(
-        sourcesParsed.data.metadata.map((source) => source.url),
-      );
-      const uniqueSources = sourcesParsed.data.metadata.filter((source) => {
-        if (filteredSourceUrls.has(source.url)) {
-          filteredSourceUrls.delete(source.url);
-          return true;
-        }
-        return false;
-      });
-      lastAnswer.answer.sources = uniqueSources.map((source) => ({
-        title: source.title ?? "Untitled",
-        type: source.type ?? "page",
-        source: source.url ?? "https://supermemory.ai",
-        content: source.description ?? "No content available",
-        numChunks: sourcesParsed.data.metadata.filter(
-          (f) => f.url === source.url,
-        ).length,
-      }));
-      return newChatHistory;
+    window.scrollTo({
+      top: document.documentElement.scrollHeight,
+      behavior: "smooth",
     });
 
-    const resp = await fetch(`/api/chat?q=${query}&spaces=${spaces}`, {
-      method: "POST",
-      body: JSON.stringify({ chatHistory }),
-    });
+    // Assuming this is part of a larger function within a React component
+    const updateChatHistoryAndFetch = async () => {
+      // Step 1: Update chat history with the assistant's response
+      await new Promise((resolve) => {
+        setChatHistory((prevChatHistory) => {
+          const newChatHistory = [...prevChatHistory];
+          const lastAnswer = newChatHistory[newChatHistory.length - 1];
+          if (!lastAnswer) {
+            resolve(undefined);
+            return prevChatHistory;
+          }
 
-    const reader = resp.body?.getReader();
-    let done = false;
-    while (!done && reader) {
-      const { value, done: d } = await reader.read();
-      done = d;
+          const filteredSourceUrls = new Set(
+            sourcesParsed.data.metadata.map((source) => source.url),
+          );
+          const uniqueSources = sourcesParsed.data.metadata.filter((source) => {
+            if (filteredSourceUrls.has(source.url)) {
+              filteredSourceUrls.delete(source.url);
+              return true;
+            }
+            return false;
+          });
 
-      setChatHistory((prevChatHistory) => {
-        const newChatHistory = [...prevChatHistory];
-        const lastAnswer = newChatHistory[newChatHistory.length - 1];
-        if (!lastAnswer) return prevChatHistory;
-        const txt = new TextDecoder().decode(value);
+          lastAnswer.answer.sources = uniqueSources.map((source) => ({
+            title: source.title ?? "Untitled",
+            type: source.type ?? "page",
+            source: source.url ?? "https://supermemory.ai",
+            content: source.description ?? "No content available",
+            numChunks: sourcesParsed.data.metadata.filter(
+              (f) => f.url === source.url,
+            ).length,
+          }));
+
+          resolve(newChatHistory);
+          return newChatHistory;
+        });
+      });
 
-        if (isAutoScroll) {
-          window.scrollTo({
-            top: document.documentElement.scrollHeight,
-            behavior: "smooth",
+      // Step 2: Fetch data from the API
+      const resp = await fetch(
+        `/api/chat?q=${query}&spaces=${spaces}&threadId=${threadId}`,
+        {
+          method: "POST",
+          body: JSON.stringify({ chatHistory }),
+        },
+      );
+
+      // Step 3: Read the response stream and update the chat history
+      const reader = resp.body?.getReader();
+      let done = false;
+      while (!done && reader) {
+        const { value, done: d } = await reader.read();
+        if (d) {
+          console.log(chatHistory);
+          setChatHistory((prevChatHistory) => {
+            console.log(prevChatHistory);
+            createChatObject(threadId, prevChatHistory);
+            return prevChatHistory;
           });
         }
+        done = d;
 
-        lastAnswer.answer.parts.push({ text: txt });
-        return newChatHistory;
-      });
-    }
+        const txt = new TextDecoder().decode(value);
+        setChatHistory((prevChatHistory) => {
+          const newChatHistory = [...prevChatHistory];
+          const lastAnswer = newChatHistory[newChatHistory.length - 1];
+          if (!lastAnswer) return prevChatHistory;
+
+          if (isAutoScroll) {
+            window.scrollTo({
+              top: document.documentElement.scrollHeight,
+              behavior: "smooth",
+            });
+          }
+
+          lastAnswer.answer.parts.push({ text: txt });
+          return newChatHistory;
+        });
+      }
+    };
+
+    updateChatHistoryAndFetch();
   };
 
   useEffect(() => {
-    if (q.trim().length > 0) {
+    if (q.trim().length > 0 || chatHistory.length > 0) {
       setLayout("chat");
-      getAnswer(
-        q,
-        spaces.map((s) => s.id),
-      );
+      const lastChat = chatHistory.length > 0 ? chatHistory.length - 1 : 0;
+      const startGenerating = chatHistory[lastChat]?.answer.parts[0]?.text
+        ? false
+        : true;
+      if (startGenerating) {
+        getAnswer(
+          q,
+          spaces.map((s) => `${s}`),
+        );
+      }
     } else {
       router.push("/home");
     }
diff --git a/apps/web/app/(dash)/chat/page.tsx b/apps/web/app/(dash)/chat/page.tsx
index 73519851..28ade147 100644
--- a/apps/web/app/(dash)/chat/page.tsx
+++ b/apps/web/app/(dash)/chat/page.tsx
@@ -12,7 +12,7 @@ function Page({
 
   console.log(spaces);
 
-  return <ChatWindow q={q} spaces={[]} />;
+  return <ChatWindow q={q} spaces={spaces} threadId={""} />;
 }
 
 export default Page;
diff --git a/apps/web/app/(dash)/home/page.tsx b/apps/web/app/(dash)/home/page.tsx
index 6fe26513..7226de24 100644
--- a/apps/web/app/(dash)/home/page.tsx
+++ b/apps/web/app/(dash)/home/page.tsx
@@ -5,6 +5,7 @@ import QueryInput from "./queryinput";
 import { homeSearchParamsCache } from "@/lib/searchParams";
 import { getSpaces } from "@/app/actions/fetchers";
 import { useRouter } from "next/navigation";
+import { createChatThread } from "@/app/actions/doers";
 
 function Page({
   searchParams,
@@ -12,7 +13,8 @@ function Page({
   searchParams: Record<string, string | string[] | undefined>;
 }) {
   // TODO: use this to show a welcome page/modal
-  const { firstTime } = homeSearchParamsCache.parse(searchParams);
+  // const { firstTime } = homeSearchParamsCache.parse(searchParams);
+  const { push } = useRouter();
 
   const [spaces, setSpaces] = useState<{ id: number; name: string }[]>([]);
 
@@ -20,13 +22,12 @@ function Page({
     getSpaces().then((res) => {
       if (res.success && res.data) {
         setSpaces(res.data);
+        return;
       }
       // TODO: HANDLE ERROR
     });
   }, []);
 
-  const { push } = useRouter();
-
   return (
     <div className="max-w-3xl h-full justify-center flex mx-auto w-full flex-col">
       {/* all content goes here */}
@@ -34,13 +35,12 @@ function Page({
 
       <div className="w-full h-96">
         <QueryInput
-          handleSubmit={(q, spaces) => {
-            const newQ =
-              "/chat?q=" +
-              encodeURI(q) +
-              (spaces ? "&spaces=" + JSON.stringify(spaces) : "");
+          handleSubmit={async (q, spaces) => {
+            const threadid = await createChatThread(q);
 
-            push(newQ);
+            push(
+              `/chat/${threadid.data}?spaces=${JSON.stringify(spaces)}&q=${q}`,
+            );
           }}
           initialSpaces={spaces}
         />
diff --git a/apps/web/app/actions/doers.ts b/apps/web/app/actions/doers.ts
index 6c7180d9..8833d5d2 100644
--- a/apps/web/app/actions/doers.ts
+++ b/apps/web/app/actions/doers.ts
@@ -2,7 +2,13 @@
 
 import { revalidatePath } from "next/cache";
 import { db } from "../../server/db";
-import { contentToSpace, space, storedContent } from "../../server/db/schema";
+import {
+  chatHistory,
+  chatThreads,
+  contentToSpace,
+  space,
+  storedContent,
+} from "../../server/db/schema";
 import { ServerActionReturnType } from "./types";
 import { auth } from "../../server/auth";
 import { Tweet } from "react-tweet/api";
@@ -10,6 +16,7 @@ import { getMetaData } from "@/lib/get-metadata";
 import { and, eq, inArray, sql } from "drizzle-orm";
 import { LIMITS } from "@/lib/constants";
 import { z } from "zod";
+import { ChatHistory } from "@repo/shared-types";
 
 export const createSpace = async (
   input: string | FormData,
@@ -266,3 +273,74 @@ export const createMemory = async (input: {
     };
   }
 };
+
+export const createChatThread = async (
+  firstMessage: string,
+): ServerActionReturnType<string> => {
+  const data = await auth();
+
+  if (!data || !data.user || !data.user.id) {
+    return { error: "Not authenticated", success: false };
+  }
+
+  const thread = await db
+    .insert(chatThreads)
+    .values({
+      firstMessage,
+      userId: data.user.id,
+    })
+    .returning({ id: chatThreads.id })
+    .execute();
+
+  console.log(thread);
+
+  if (!thread[0]) {
+    return {
+      success: false,
+      error: "Failed to create chat thread",
+    };
+  }
+
+  return { success: true, data: thread[0].id };
+};
+
+export const createChatObject = async (
+  threadId: string,
+  chatHistorySoFar: ChatHistory[],
+): ServerActionReturnType<boolean> => {
+  const data = await auth();
+
+  if (!data || !data.user || !data.user.id) {
+    return { error: "Not authenticated", success: false };
+  }
+
+  const lastChat = chatHistorySoFar[chatHistorySoFar.length - 1];
+  if (!lastChat) {
+    return {
+      success: false,
+      data: false,
+      error: "No chat object found",
+    };
+  }
+  console.log("sources: ", lastChat.answer.sources);
+
+  const saved = await db.insert(chatHistory).values({
+    question: lastChat.question,
+    answer: lastChat.answer.parts.map((part) => part.text).join(""),
+    answerSources: JSON.stringify(lastChat.answer.sources),
+    threadId,
+  });
+
+  if (!saved) {
+    return {
+      success: false,
+      data: false,
+      error: "Failed to save chat object",
+    };
+  }
+
+  return {
+    success: true,
+    data: true,
+  };
+};
diff --git a/apps/web/app/actions/fetchers.ts b/apps/web/app/actions/fetchers.ts
index dc71252e..708b82b2 100644
--- a/apps/web/app/actions/fetchers.ts
+++ b/apps/web/app/actions/fetchers.ts
@@ -1,8 +1,10 @@
 "use server";
 
-import { eq, inArray, not, sql } from "drizzle-orm";
+import { and, asc, eq, inArray, not, sql } from "drizzle-orm";
 import { db } from "../../server/db";
 import {
+  chatHistory,
+  chatThreads,
   Content,
   contentToSpace,
   storedContent,
@@ -10,6 +12,8 @@ import {
 } from "../../server/db/schema";
 import { ServerActionReturnType, Space } from "./types";
 import { auth } from "../../server/auth";
+import { ChatHistory, SourceZod } from "@repo/shared-types";
+import { z } from "zod";
 
 export const getSpaces = async (): ServerActionReturnType<Space[]> => {
   const data = await auth();
@@ -103,22 +107,27 @@ export const getAllUserMemoriesAndSpaces = async (): ServerActionReturnType<{
   // console.log(contentCountBySpace);
 
   // get a count with space mappings like spaceID: count (number of memories in that space)
-  const contentCountBySpace = await db
-    .select({
-      spaceId: contentToSpace.spaceId,
-      count: sql<number>`count(*)`.mapWith(Number),
-    })
-    .from(contentToSpace)
-    .where(
-      inArray(
-        contentToSpace.spaceId,
-        spacesWithoutUser.map((space) => space.id),
-      ),
-    )
-    .groupBy(contentToSpace.spaceId)
-    .execute();
 
-  console.log(contentCountBySpace);
+  const len = spacesWithoutUser.map((space) => space.id).length;
+
+  if (len > 0) {
+    const contentCountBySpace = await db
+      .select({
+        spaceId: contentToSpace.spaceId,
+        count: sql<number>`count(*)`.mapWith(Number),
+      })
+      .from(contentToSpace)
+      .where(
+        inArray(
+          contentToSpace.spaceId,
+          spacesWithoutUser.map((space) => space.id),
+        ),
+      )
+      .groupBy(contentToSpace.spaceId)
+      .execute();
+
+    console.log(contentCountBySpace);
+  }
 
   const contentNotInAnySpace = await db
     .select()
@@ -140,3 +149,64 @@ export const getAllUserMemoriesAndSpaces = async (): ServerActionReturnType<{
     data: { spaces: spacesWithoutUser, memories: contentNotInAnySpace },
   };
 };
+
+export const getFullChatThread = async (
+  threadId: string,
+): ServerActionReturnType<ChatHistory[]> => {
+  const data = await auth();
+
+  if (!data || !data.user || !data.user.id) {
+    return { error: "Not authenticated", success: false };
+  }
+
+  const thread = await db.query.chatThreads.findFirst({
+    where: and(
+      eq(chatThreads.id, threadId),
+      eq(chatThreads.userId, data.user.id),
+    ),
+  });
+
+  if (!thread) {
+    return { error: "Thread not found", success: false };
+  }
+
+  const allChatsInThisThread = await db.query.chatHistory
+    .findMany({
+      where: and(eq(chatHistory.threadId, threadId)),
+      orderBy: asc(chatHistory.id),
+    })
+    .execute();
+
+  const accumulatedChatHistory: ChatHistory[] = allChatsInThisThread.map(
+    (chat) => {
+      console.log("answer sources", chat.answerSources);
+      const sourceCheck = z
+        .array(SourceZod)
+        .safeParse(JSON.parse(chat.answerSources ?? "[]"));
+
+      if (!sourceCheck.success || !sourceCheck.data) {
+        console.error("sourceCheck.error", sourceCheck.error);
+        throw new Error("Invalid source data");
+      }
+
+      const sources = sourceCheck.data;
+
+      return {
+        question: chat.question,
+        answer: {
+          parts: [
+            {
+              text: chat.answer ?? undefined,
+            },
+          ],
+          sources: sources ?? [],
+        },
+      };
+    },
+  );
+
+  return {
+    success: true,
+    data: accumulatedChatHistory,
+  };
+};
diff --git a/apps/web/app/api/chat/route.ts b/apps/web/app/api/chat/route.ts
index c19ce92b..25b1845b 100644
--- a/apps/web/app/api/chat/route.ts
+++ b/apps/web/app/api/chat/route.ts
@@ -1,9 +1,5 @@
 import { type NextRequest } from "next/server";
-import {
-  ChatHistory,
-  ChatHistoryZod,
-  convertChatHistoryList,
-} from "@repo/shared-types";
+import { ChatHistoryZod, convertChatHistoryList } from "@repo/shared-types";
 import { ensureAuth } from "../ensureAuth";
 import { z } from "zod";
 
@@ -49,6 +45,8 @@ export async function POST(req: NextRequest) {
     );
   }
 
+  console.log("validated.data.chatHistory", validated.data.chatHistory);
+
   const modelCompatible = await convertChatHistoryList(
     validated.data.chatHistory,
   );
diff --git a/apps/web/drizzle.config.ts b/apps/web/drizzle.config.ts
index ab071121..521e1fcb 100644
--- a/apps/web/drizzle.config.ts
+++ b/apps/web/drizzle.config.ts
@@ -1,7 +1,7 @@
 import { type Config } from "drizzle-kit";
 
 export default {
-  schema: "./app/helpers/server/db/schema.ts",
+  schema: "./server/db/schema.ts",
   dialect: "sqlite",
   driver: "d1",
   dbCredentials: {
diff --git a/apps/web/lib/searchParams.ts b/apps/web/lib/searchParams.ts
index 9899eaf7..6db718c2 100644
--- a/apps/web/lib/searchParams.ts
+++ b/apps/web/lib/searchParams.ts
@@ -16,11 +16,19 @@ export const chatSearchParamsCache = createSearchParamsCache({
   firstTime: parseAsBoolean.withDefault(false),
   q: parseAsString.withDefault(""),
   spaces: parseAsArrayOf(
-    parseAsJson(() =>
-      z.object({
-        id: z.string(),
-        name: z.string(),
-      }),
-    ),
+    parseAsJson((c) => {
+      const valid = z
+        .object({
+          id: z.string(),
+          name: z.string(),
+        })
+        .safeParse(c);
+
+      if (!valid.success) {
+        return null;
+      }
+
+      return valid.data;
+    }),
   ).withDefault([]),
 });
diff --git a/apps/web/migrations/000_setup.sql b/apps/web/migrations/000_setup.sql
index 0c151b98..7e4275b8 100644
--- a/apps/web/migrations/000_setup.sql
+++ b/apps/web/migrations/000_setup.sql
@@ -27,6 +27,23 @@ CREATE TABLE `authenticator` (
 	FOREIGN KEY (`userId`) REFERENCES `user`(`id`) ON UPDATE no action ON DELETE cascade
 );
 --> statement-breakpoint
+CREATE TABLE `chatHistory` (
+	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
+	`threadId` text NOT NULL,
+	`question` text NOT NULL,
+	`answerParts` text,
+	`answerSources` text,
+	`answerJustification` text,
+	FOREIGN KEY (`threadId`) REFERENCES `chatThread`(`id`) ON UPDATE no action ON DELETE cascade
+);
+--> statement-breakpoint
+CREATE TABLE `chatThread` (
+	`id` text PRIMARY KEY NOT NULL,
+	`firstMessage` text NOT NULL,
+	`userId` text NOT NULL,
+	FOREIGN KEY (`userId`) REFERENCES `user`(`id`) ON UPDATE no action ON DELETE cascade
+);
+--> statement-breakpoint
 CREATE TABLE `contentToSpace` (
 	`contentId` integer NOT NULL,
 	`spaceId` integer NOT NULL,
@@ -60,7 +77,7 @@ CREATE TABLE `storedContent` (
 	`ogImage` text(255),
 	`type` text DEFAULT 'page',
 	`image` text(255),
-	`user` integer,
+	`user` text,
 	FOREIGN KEY (`user`) REFERENCES `user`(`id`) ON UPDATE no action ON DELETE cascade
 );
 --> statement-breakpoint
@@ -80,6 +97,8 @@ CREATE TABLE `verificationToken` (
 );
 --> statement-breakpoint
 CREATE UNIQUE INDEX `authenticator_credentialID_unique` ON `authenticator` (`credentialID`);--> statement-breakpoint
+CREATE INDEX `chatHistory_thread_idx` ON `chatHistory` (`threadId`);--> statement-breakpoint
+CREATE INDEX `chatThread_user_idx` ON `chatThread` (`userId`);--> statement-breakpoint
 CREATE UNIQUE INDEX `space_name_unique` ON `space` (`name`);--> statement-breakpoint
 CREATE INDEX `spaces_name_idx` ON `space` (`name`);--> statement-breakpoint
 CREATE INDEX `spaces_user_idx` ON `space` (`user`);--> statement-breakpoint
diff --git a/apps/web/migrations/meta/0000_snapshot.json b/apps/web/migrations/meta/0000_snapshot.json
index 20327dda..3e197cbd 100644
--- a/apps/web/migrations/meta/0000_snapshot.json
+++ b/apps/web/migrations/meta/0000_snapshot.json
@@ -1,7 +1,7 @@
 {
   "version": "6",
   "dialect": "sqlite",
-  "id": "4a568d9b-a0e6-44ed-946b-694e34b063f3",
+  "id": "349eea0d-f26e-4579-9c65-3982816b0c6c",
   "prevId": "00000000-0000-0000-0000-000000000000",
   "tables": {
     "account": {
@@ -191,6 +191,119 @@
       },
       "uniqueConstraints": {}
     },
+    "chatHistory": {
+      "name": "chatHistory",
+      "columns": {
+        "id": {
+          "name": "id",
+          "type": "integer",
+          "primaryKey": true,
+          "notNull": true,
+          "autoincrement": true
+        },
+        "threadId": {
+          "name": "threadId",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false
+        },
+        "question": {
+          "name": "question",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false
+        },
+        "answerParts": {
+          "name": "answerParts",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": false,
+          "autoincrement": false
+        },
+        "answerSources": {
+          "name": "answerSources",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": false,
+          "autoincrement": false
+        },
+        "answerJustification": {
+          "name": "answerJustification",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": false,
+          "autoincrement": false
+        }
+      },
+      "indexes": {
+        "chatHistory_thread_idx": {
+          "name": "chatHistory_thread_idx",
+          "columns": ["threadId"],
+          "isUnique": false
+        }
+      },
+      "foreignKeys": {
+        "chatHistory_threadId_chatThread_id_fk": {
+          "name": "chatHistory_threadId_chatThread_id_fk",
+          "tableFrom": "chatHistory",
+          "tableTo": "chatThread",
+          "columnsFrom": ["threadId"],
+          "columnsTo": ["id"],
+          "onDelete": "cascade",
+          "onUpdate": "no action"
+        }
+      },
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {}
+    },
+    "chatThread": {
+      "name": "chatThread",
+      "columns": {
+        "id": {
+          "name": "id",
+          "type": "text",
+          "primaryKey": true,
+          "notNull": true,
+          "autoincrement": false
+        },
+        "firstMessage": {
+          "name": "firstMessage",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false
+        },
+        "userId": {
+          "name": "userId",
+          "type": "text",
+          "primaryKey": false,
+          "notNull": true,
+          "autoincrement": false
+        }
+      },
+      "indexes": {
+        "chatThread_user_idx": {
+          "name": "chatThread_user_idx",
+          "columns": ["userId"],
+          "isUnique": false
+        }
+      },
+      "foreignKeys": {
+        "chatThread_userId_user_id_fk": {
+          "name": "chatThread_userId_user_id_fk",
+          "tableFrom": "chatThread",
+          "tableTo": "user",
+          "columnsFrom": ["userId"],
+          "columnsTo": ["id"],
+          "onDelete": "cascade",
+          "onUpdate": "no action"
+        }
+      },
+      "compositePrimaryKeys": {},
+      "uniqueConstraints": {}
+    },
     "contentToSpace": {
       "name": "contentToSpace",
       "columns": {
@@ -411,7 +524,7 @@
         },
         "user": {
           "name": "user",
-          "type": "integer",
+          "type": "text",
           "primaryKey": false,
           "notNull": false,
           "autoincrement": false
diff --git a/apps/web/migrations/meta/_journal.json b/apps/web/migrations/meta/_journal.json
index 90bb9df7..0babac49 100644
--- a/apps/web/migrations/meta/_journal.json
+++ b/apps/web/migrations/meta/_journal.json
@@ -5,8 +5,8 @@
     {
       "idx": 0,
       "version": "6",
-      "when": 1718412145023,
-      "tag": "0000_absurd_pandemic",
+      "when": 1719075265633,
+      "tag": "0000_conscious_arachne",
       "breakpoints": true
     }
   ]
diff --git a/apps/web/server/db/schema.ts b/apps/web/server/db/schema.ts
index 1ff23c82..f54d2094 100644
--- a/apps/web/server/db/schema.ts
+++ b/apps/web/server/db/schema.ts
@@ -154,3 +154,40 @@ export type StoredSpace = typeof space.$inferSelect;
 export type ChachedSpaceContent = StoredContent & {
   space: number;
 };
+
+export const chatThreads = createTable(
+  "chatThread",
+  {
+    id: text("id")
+      .notNull()
+      .primaryKey()
+      .$defaultFn(() => crypto.randomUUID()),
+    firstMessage: text("firstMessage").notNull(),
+    userId: text("userId")
+      .notNull()
+      .references(() => users.id, { onDelete: "cascade" }),
+  },
+  (thread) => ({
+    userIdx: index("chatThread_user_idx").on(thread.userId),
+  }),
+);
+
+export const chatHistory = createTable(
+  "chatHistory",
+  {
+    id: integer("id").notNull().primaryKey({ autoIncrement: true }),
+    threadId: text("threadId")
+      .notNull()
+      .references(() => chatThreads.id, { onDelete: "cascade" }),
+    question: text("question").notNull(),
+    answer: text("answerParts"), // Single answer part as string
+    answerSources: text("answerSources"), // JSON stringified array of objects
+    answerJustification: text("answerJustification"),
+  },
+  (history) => ({
+    threadIdx: index("chatHistory_thread_idx").on(history.threadId),
+  }),
+);
+
+export type ChatThread = typeof chatThreads.$inferSelect;
+export type ChatHistory = typeof chatHistory.$inferSelect;
diff --git a/packages/shared-types/index.ts b/packages/shared-types/index.ts
index d3f466e1..318684b7 100644
--- a/packages/shared-types/index.ts
+++ b/packages/shared-types/index.ts
@@ -1,18 +1,20 @@
 import { z } from "zod";
 
+export const SourceZod = z.object({
+  type: z.string(),
+  source: z.string(),
+  title: z.string(),
+  content: z.string(),
+  numChunks: z.number().optional().default(1),
+});
+
+export type Source = z.infer<typeof SourceZod>;
+
 export const ChatHistoryZod = z.object({
   question: z.string(),
   answer: z.object({
-    parts: z.array(z.object({ text: z.string() })),
-    sources: z.array(
-      z.object({
-        type: z.enum(["note", "page", "tweet"]),
-        source: z.string(),
-        title: z.string(),
-        content: z.string(),
-        numChunks: z.number().optional().default(1),
-      }),
-    ),
+    parts: z.array(z.object({ text: z.string().optional() })),
+    sources: z.array(SourceZod),
     justification: z.string().optional(),
   }),
 });
@@ -52,5 +54,8 @@ export function convertChatHistoryList(
     );
   });
 
+  // THE LAST ASSISTANT CONTENT WILL ALWAYS BE EMPTY, so we remove it
+  convertedChats.pop();
+
   return convertedChats;
 }