Platform Kit

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