Merge branch 'main' into feat/upload-db

jgoux · jgoux · commit 9012b677fb44 · 2024-08-12T09:02:30.000+02:00
diff --git a/README.md b/README.md
@@ -1,13 +1,33 @@
-# postgres-new
+# postgres.new
 
 In-browser Postgres sandbox with AI assistance.
 
+![github-repo-hero](https://github.com/user-attachments/assets/e55f7c0d-a817-4aeb-838e-728aabda3a5d)
+
+With [postgres.new](https://postgres.new), you can instantly spin up an unlimited number of Postgres databases that run directly in your browser (and soon, deploy them to S3). 
+
+Each database is paired with a large language model (LLM) which opens the door to some interesting use cases:
+
+- Drag-and-drop CSV import (generate table on the fly)
+- Generate and export reports
+- Generate charts
+- Build database diagrams
+
+## How it works
+All queries in postgres.new run directly in your browser. There’s no remote Postgres container or WebSocket proxy.
+
+How is this possible? [PGlite](https://pglite.dev/), a WASM version of Postgres that can run directly in your browser. Every database that you create spins up a new instance of PGlite that exposes a fully-functional Postgres database. Data is stored in IndexedDB so that changes persist after refresh.
+
 ## Monorepo
 
 This is a monorepo split into the following projects:
 
-- [Frontend (Next.js)](./apps/postgres-new/)
-- [Backend (pg-gateway)](./apps/db-service/)
+- [Frontend (Next.js)](./apps/postgres-new/): This contains the primary web app built with Next.js
+- [Backend (pg-gateway)](./apps/db-service/): This serves S3-backed PGlite databases over the PG wire protocol using [pg-gateway](https://github.com/supabase-community/pg-gateway)
+
+## Video
+
+[![image](https://github.com/user-attachments/assets/9da04785-d813-4e9c-a400-4e00c63381a1)](https://youtu.be/ooWaPVvljlU)
 
 ## License
 
diff --git a/apps/db-service/package.json b/apps/db-service/package.json
@@ -9,7 +9,7 @@
     "psql": "docker compose run --rm -i psql psql"
   },
   "dependencies": {
-    "@electric-sql/pglite": "0.2.0-alpha.6",
+    "@electric-sql/pglite": "0.2.0-alpha.9",
     "pg-gateway": "^0.2.5-alpha.2",
     "tar": "^7.4.3"
   },
diff --git a/apps/postgres-new/README.md b/apps/postgres-new/README.md
@@ -2,9 +2,32 @@
 
 In-browser Postgres sandbox with AI assistance. Built on Next.js.
 
+## Architecture
+
+We use PGlite for 2 purposes:
+
+1. A "meta" DB that keeps track of all of the user databases along with their message history
+2. A "user" DB for each database the user creates along with whatever tables/data they've created
+
+Both databases are stored locally in the browser via IndexedDB. This means that these databases are not persisted to the cloud and cannot be accessed from multiple devices (though this is on the roadmap).
+
+Every PGlite instance runs in a Web Worker so that the main thread is not blocked.
+
+## AI
+
+The AI component is powered by OpenAI's GPT-4o model. The project uses [Vercel's AI SDK ](https://sdk.vercel.ai/docs/introduction) to simplify message streams and tool calls.
+
+## Authentication
+
+Because LLMs cost money, a lightweight auth wall exists to prevent abuse. It is currently only used to validate that the user has a legitimate GitHub account, but in the future it could be used to save private/public databases to the cloud.
+
+Authentication and users are managed by a [Supabase](https://supabase.com/) database. You can find the migrations and other configuration for this in the root [`./supabase`](../../supabase/) directory.
+
 ## Development
 
-1. Install deps:
+From this directory (`./apps/postgres-new`):
+
+1. Install dependencies:
    ```shell
    npm i
    ```
diff --git a/apps/postgres-new/app/api/chat/route.ts b/apps/postgres-new/app/api/chat/route.ts
@@ -41,6 +41,9 @@ export async function POST(req: Request) {
       When querying data, limit to 5 by default.
 
       When performing FTS, always use 'simple' (languages aren't available).
+
+      When importing CSVs try to solve the problem yourself (eg. use a generic text column, then refine)
+      vs. asking the user to change the CSV.
       
       You also know math. All math equations and expressions must be written in KaTex and must be wrapped in double dollar \`$$\`:
         - Inline: $$\\sqrt{26}$$
diff --git a/apps/postgres-new/app/page.tsx b/apps/postgres-new/app/page.tsx
@@ -68,18 +68,51 @@ export default function Page() {
     <Workspace
       databaseId={nextDatabaseId}
       visibility="local"
-      onStart={async () => {
+      onMessage={async () => {
         // Make the DB no longer hidden
         await updateDatabase({ id: nextDatabaseId, name: null, isHidden: false })
 
-        // Navigate to this DB's path
-        router.push(`/db/${nextDatabaseId}`)
-
         // Pre-load the next DB
         const nextId = uniqueId()
         localStorage.setItem('next-db-id', JSON.stringify(nextId))
         preloadDb(nextId)
       }}
+      onReply={async (message, append) => {
+        if (!dbManager) {
+          throw new Error('dbManager is not available')
+        }
+
+        const messages = await dbManager.getMessages(nextDatabaseId)
+        const isFirstReplyComplete =
+          !messages.some((message) => message.role === 'assistant' && !message.toolInvocations) &&
+          message.role === 'assistant' &&
+          !message.toolInvocations
+
+        // The model might run multiple tool calls before ending with a message, so
+        // we only want to redirect after all of these back-to-back calls finish
+        if (isFirstReplyComplete) {
+          router.push(`/db/${nextDatabaseId}`)
+
+          append({
+            role: 'user',
+            content: 'Name this conversation. No need to reply.',
+            data: {
+              automated: true,
+            },
+          })
+        }
+      }}
+      onCancelReply={(append) => {
+        router.push(`/db/${nextDatabaseId}`)
+
+        append({
+          role: 'user',
+          content: 'Name this conversation. No need to reply.',
+          data: {
+            automated: true,
+          },
+        })
+      }}
     />
   )
 }
diff --git a/apps/postgres-new/components/chat.tsx b/apps/postgres-new/components/chat.tsx
@@ -73,6 +73,28 @@ export default function Chat() {
 
   const sendCsv = useCallback(
     async (file: File) => {
+      if (file.type !== 'text/csv') {
+        // Add an artificial tool call requesting the CSV
+        // with an error indicating the file wasn't a CSV
+        appendMessage({
+          role: 'assistant',
+          content: '',
+          toolInvocations: [
+            {
+              state: 'result',
+              toolCallId: generateId(),
+              toolName: 'requestCsv',
+              args: {},
+              result: {
+                success: false,
+                error: `The file has type '${file.type}'. Let the user know that only CSV imports are currently supported.`,
+              },
+            },
+          ],
+        })
+        return
+      }
+
       const fileId = generateId()
 
       await saveFile(fileId, file)
@@ -120,7 +142,7 @@ export default function Chat() {
 
       const [file] = files
 
-      if (file && file.type === 'text/csv') {
+      if (file) {
         await sendCsv(file)
       }
     },
@@ -280,7 +302,7 @@ export default function Chat() {
             {user ? (
               <m.h3
                 layout
-                className="text-2xl font-light"
+                className="text-2xl font-light text-center"
                 variants={{
                   hidden: { opacity: 0, y: 10 },
                   show: { opacity: 1, y: 0 },
@@ -402,7 +424,7 @@ export default function Chat() {
                 const changeEvent = event as unknown as ChangeEvent<HTMLInputElement>
                 const [file] = Array.from(changeEvent.target?.files ?? [])
 
-                if (file && file.type === 'text/csv') {
+                if (file) {
                   await sendCsv(file)
                 }
 
diff --git a/apps/postgres-new/components/providers.tsx b/apps/postgres-new/components/providers.tsx
@@ -10,13 +10,7 @@ const queryClient = new QueryClient()
 
 export default function Providers({ children }: PropsWithChildren) {
   return (
-    // Force theme until we implement dark mode
-    <ThemeProvider
-      attribute="class"
-      defaultTheme="system"
-      storageKey="jonny"
-      disableTransitionOnChange
-    >
+    <ThemeProvider attribute="class" defaultTheme="system" disableTransitionOnChange>
       <QueryClientProvider client={queryClient}>
         <AppProvider>{children}</AppProvider>
       </QueryClientProvider>
diff --git a/apps/postgres-new/components/schema/table-graph.tsx b/apps/postgres-new/components/schema/table-graph.tsx
@@ -73,7 +73,10 @@ export default function TablesGraph({
 
         // `fitView` needs to happen during next event tick
         setTimeout(() => fitView(isFirstLoad ? 0 : 500), 0)
-        setIsFirstLoad(false)
+
+        if (tables.length > 0) {
+          setIsFirstLoad(false)
+        }
       })
     }
   }, [reactFlowInstance, tables, resolvedTheme, fitView, isFirstLoad])
diff --git a/apps/postgres-new/components/sidebar.tsx b/apps/postgres-new/components/sidebar.tsx
@@ -273,7 +273,7 @@ function DatabaseMenuItem({ database, isActive }: DatabaseMenuItemProps) {
             <AnimatePresence initial={false}>
               {!isOnDeployWaitlist ? (
                 <button
-                  className="px-4 py-3 bg-black text-white rounded-md"
+                  className="px-4 py-3 bg-foreground text-background rounded-md"
                   onClick={async () => {
                     await joinDeployWaitlist()
                   }}
@@ -399,7 +399,11 @@ function DatabaseMenuItem({ database, isActive }: DatabaseMenuItemProps) {
                     downloadFile(file)
                   }}
                 >
-                  <Download size={16} strokeWidth={2} className="flex-shrink-0" />
+                  <Download
+                    size={16}
+                    strokeWidth={2}
+                    className="flex-shrink-0 text-muted-foreground"
+                  />
 
                   <span>Download</span>
                 </DropdownMenuItem>
@@ -418,7 +422,7 @@ function DatabaseMenuItem({ database, isActive }: DatabaseMenuItemProps) {
                     strokeWidth={2}
                     className="flex-shrink-0 text-muted-foreground"
                   />
-                  <span>Publish</span>
+                  <span>Deploy</span>
                 </DropdownMenuItem>
                 <DropdownMenuSeparator />
                 <DropdownMenuItem
diff --git a/apps/postgres-new/components/tools/csv-export.tsx b/apps/postgres-new/components/tools/csv-export.tsx
@@ -1,5 +1,7 @@
 import { m } from 'framer-motion'
 import { Download } from 'lucide-react'
+import { useMemo } from 'react'
+import { format } from 'sql-formatter'
 import { loadFile } from '~/lib/files'
 import { ToolInvocation } from '~/lib/tools'
 import { downloadFile } from '~/lib/util'
@@ -10,19 +12,40 @@ export type CsvExportProps = {
 }
 
 export default function CsvExport({ toolInvocation }: CsvExportProps) {
+  const { sql } = toolInvocation.args
+
+  const formattedSql = useMemo(
+    () =>
+      format(sql, {
+        language: 'postgresql',
+        keywordCase: 'lower',
+        identifierCase: 'lower',
+        dataTypeCase: 'lower',
+        functionCase: 'lower',
+      }),
+    [sql]
+  )
+
   if (!('result' in toolInvocation)) {
     return null
   }
 
   const { result } = toolInvocation
 
   if (!result.success) {
-    return <div className="bg-destructive-300 px-6 py-4 rounded-md">Error executing SQL</div>
+    return (
+      <CodeAccordion
+        title="Error executing SQL"
+        language="sql"
+        code={formattedSql}
+        error={result.error}
+      />
+    )
   }
 
   return (
     <>
-      <CodeAccordion title="Executed SQL" language="sql" code={toolInvocation.args.sql} />
+      <CodeAccordion title="Executed SQL" language="sql" code={formattedSql} />
       <m.div
         layoutId={toolInvocation.toolCallId}
         className="self-start px-5 py-2.5 text-base rounded-full bg-border flex gap-2 items-center text-lighter italic"
diff --git a/apps/postgres-new/components/tools/csv-import.tsx b/apps/postgres-new/components/tools/csv-import.tsx
@@ -1,20 +1,43 @@
 import { ToolInvocation } from '~/lib/tools'
 import CodeAccordion from '../code-accordion'
+import { useMemo } from 'react'
+import { format } from 'sql-formatter'
 
 export type CsvExportProps = {
   toolInvocation: ToolInvocation<'importCsv'>
 }
 
 export default function CsvImport({ toolInvocation }: CsvExportProps) {
+  const { sql } = toolInvocation.args
+
+  const formattedSql = useMemo(
+    () =>
+      format(sql, {
+        language: 'postgresql',
+        keywordCase: 'lower',
+        identifierCase: 'lower',
+        dataTypeCase: 'lower',
+        functionCase: 'lower',
+      }),
+    [sql]
+  )
+
   if (!('result' in toolInvocation)) {
     return null
   }
 
   const { result } = toolInvocation
 
   if (!result.success) {
-    return <div className="bg-destructive-300 px-6 py-4 rounded-md">Error executing SQL</div>
+    return (
+      <CodeAccordion
+        title="Error executing SQL"
+        language="sql"
+        code={formattedSql}
+        error={result.error}
+      />
+    )
   }
 
-  return <CodeAccordion title="Executed SQL" language="sql" code={toolInvocation.args.sql} />
+  return <CodeAccordion title="Executed SQL" language="sql" code={formattedSql} />
 }
diff --git a/apps/postgres-new/components/tools/csv-request.tsx b/apps/postgres-new/components/tools/csv-request.tsx
@@ -23,11 +23,20 @@ export default function CsvRequest({ toolInvocation }: CsvRequestProps) {
     const { result } = toolInvocation
 
     if (!result.success) {
-      return null
+      return (
+        <m.div
+          layout="position"
+          layoutId={toolInvocation.toolCallId}
+          className="self-end px-5 py-2.5 text-base rounded-full bg-destructive flex gap-2 items-center text-lighter italic"
+        >
+          No CSV file selected
+        </m.div>
+      )
     }
 
     return (
       <m.div
+        layout="position"
         layoutId={toolInvocation.toolCallId}
         className="self-end px-5 py-2.5 text-base rounded-full bg-border flex gap-2 items-center text-lighter italic"
         style={{
@@ -52,7 +61,7 @@ export default function CsvRequest({ toolInvocation }: CsvRequestProps) {
   }
 
   return (
-    <m.div layoutId={toolInvocation.toolCallId}>
+    <m.div layout="position" layoutId={toolInvocation.toolCallId}>
       <input
         type="file"
         onChange={async (e) => {
diff --git a/apps/postgres-new/components/tools/executed-sql.tsx b/apps/postgres-new/components/tools/executed-sql.tsx
diff --git a/apps/postgres-new/components/workspace.tsx b/apps/postgres-new/components/workspace.tsx
diff --git a/apps/postgres-new/lib/util.ts b/apps/postgres-new/lib/util.ts
diff --git a/apps/postgres-new/package.json b/apps/postgres-new/package.json
diff --git a/package-lock.json b/package-lock.json
diff --git a/supabase/migrations/20240810203851_remove_waitlist_delete_policy.sql b/supabase/migrations/20240810203851_remove_waitlist_delete_policy.sql
