Platform Kit

1import type { paths } from '@/platform/platform-kit-nextjs/lib/management-api-schema'
2import { listTablesSql } from '@/platform/platform-kit-nextjs/lib/pg-meta'
3import { NextResponse } from 'next/server'
4import OpenAI from 'openai'
5import createClient from 'openapi-fetch'
6
7const openai = new OpenAI({
8  apiKey: process.env.OPENAI_API_KEY,
9})
10
11const client = createClient<paths>({
12  baseUrl: 'https://api.supabase.com',
13  headers: {
14    Authorization: `Bearer ${process.env.SUPABASE_MANAGEMENT_API_TOKEN}`,
15  },
16})
17
18// Function to get database schema
19async function getDbSchema(projectRef: string) {
20  const token = process.env.SUPABASE_MANAGEMENT_API_TOKEN
21  if (!token) {
22    throw new Error('Supabase Management API token is not configured.')
23  }
24
25  const sql = listTablesSql()
26
27  const { data, error } = await client.POST('/v1/projects/{ref}/database/query', {
28    params: {
29      path: {
30        ref: projectRef,
31      },
32    },
33    body: {
34      query: sql,
35      read_only: true,
36    },
37  })
38
39  if (error) {
40    throw error
41  }
42
43  return data as any
44}
45
46function formatSchemaForPrompt(schema: any) {
47  let schemaString = ''
48  if (schema && Array.isArray(schema)) {
49    schema.forEach((table: any) => {
50      const columnInfo = table.columns.map((c: any) => `${c.name} (${c.data_type})`)
51      schemaString += `Table "${table.name}" has columns: ${columnInfo.join(', ')}.\n`
52    })
53  }
54  return schemaString
55}
56
57export async function POST(request: Request) {
58  try {
59    const { prompt, projectRef } = await request.json()
60
61    if (!prompt) {
62      return NextResponse.json({ message: 'Prompt is required.' }, { status: 400 })
63    }
64    if (!projectRef) {
65      return NextResponse.json({ message: 'projectRef is required.' }, { status: 400 })
66    }
67
68    // Implement your permission check here (e.g. check if the user is a member of the project)
69    // In this example, everyone can access all projects
70    const userHasPermissionForProject = Boolean(projectRef)
71
72    if (!userHasPermissionForProject) {
73      return NextResponse.json(
74        { message: 'You do not have permission to access this project.' },
75        { status: 403 }
76      )
77    }
78
79    // 1. Get database schema
80    const schema = await getDbSchema(projectRef)
81    const formattedSchema = formatSchemaForPrompt(schema)
82
83    // 2. Create a prompt for OpenAI
84    const systemPrompt = `You are an expert SQL assistant. Given the following database schema, write a SQL query that answers the user's question. Return only the SQL query, do not include any explanations or markdown.\n\nSchema:\n${formattedSchema}`
85
86    // 3. Call OpenAI to generate SQL using responses.create (plain text output)
87    const response = await openai.responses.create({
88      model: 'gpt-4.1',
89      instructions: systemPrompt, // Use systemPrompt as instructions
90      input: prompt, // User's question
91    })
92
93    const sql = response.output_text
94
95    if (!sql) {
96      return NextResponse.json(
97        { message: 'Could not generate SQL from the prompt.' },
98        { status: 500 }
99      )
100    }
101
102    // 4. Return the generated SQL
103    return NextResponse.json({ sql })
104  } catch (error: any) {
105    console.error('AI SQL generation error:', error)
106    const errorMessage = error.message || 'An unexpected error occurred.'
107    const status = error.response?.status || 500
108    return NextResponse.json({ message: errorMessage }, { status })
109  }
110}