Data persistence

// 1. Provide requirements to AI
Prompt: "Design a database for an e-commerce system, including users, products, orders, and order items tables..."

// 2. Prisma Schema generated by AI
model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String?
  orders    Order[]
  createdAt DateTime @default(now())
}

model Product {
  id          String      @id @default(cuid())
  name        String
  price       Decimal     @db.Decimal(10, 2)
  description String?
  orderItems  OrderItem[]
  createdAt   DateTime    @default(now())
}

model Order {
  id         String     @id @default(cuid())
  userId     String
  user       User       @relation(fields: [userId], references: [id])
  items      OrderItem[]
  total      Decimal    @db.Decimal(10, 2)
  status     String     @default("pending")
  createdAt  DateTime   @default(now())
  
  @@index([userId])
}

model OrderItem {
  id        String   @id @default(cuid())
  orderId   String
  productId String
  order     Order    @relation(fields: [orderId], references: [id])
  product   Product  @relation(fields: [productId], references: [id])
  quantity  Int
  price     Decimal  @db.Decimal(10, 2)
  
  @@index([orderId])
  @@index([productId])
}

// NL2SQL function implementation
async function nl2sql(question: string, schema: DatabaseSchema): Promise<string> {
  const prompt = `Convert the question to SQL based on the database schema:

Schema: ${JSON.stringify(schema)}
Question: ${question}

Return only SQL, using parameterized queries.`;

  const sql = await callLLM(prompt);
  return validateAndSanitizeSQL(sql);
}

// Usage example
const sql = await nl2sql(
  "Query all articles created in the last week and their author information",
  { tables: { users: [...], posts: [...] } }
);
// Returns: SELECT p.*, u.name as author_name 
//       FROM posts p 
//       JOIN users u ON p.author_id = u.id 
//       WHERE p.created_at > NOW() - INTERVAL '7 days'

// Complete RAG flow
async function ragQuery(question: string) {
  // 1. Vectorize the question
  const questionEmbedding = await getEmbedding(question);
  
  // 2. Vector search (Pinecone)
  const results = await pineconeIndex.query({
    vector: questionEmbedding,
    topK: 5,
    includeMetadata: true
  });
  
  // 3. Build context
  const context = results.matches
    .map(m => m.metadata?.text)
    .join('\n\n');
  
  // 4. LLM generates an answer
  const answer = await generateAnswer(question, context);
  
  return answer;
}

// Usage
const answer = await ragQuery("What is Prisma?");
// AI generates accurate answers based on the retrieved documents