schema.prisma

// datasource db defines the database connection settings.
// It is configured for PostgreSQL and uses an environment variable for the connection URL.
// The 'extensions' feature enables the use of PostgreSQL-specific data types.
datasource db {
  provider   = "postgresql"
  url        = env("DATABASE_URL")
  extensions = [vector]
}

// generator db configures Prisma Client settings.
// It is set up to use Prisma Client Python with asyncio interface and specific features.
generator db {
  provider             = "prisma-client-py"
  interface            = "asyncio"
  recursive_type_depth = 5
  previewFeatures      = ["postgresqlExtensions"]
}

// ---------------------------------------------------------------------- //
// ------------------------- Common Models ------------------------------ //
// ---------------------------------------------------------------------- //

model User {
  id              String    @id @default(dbgenerated("gen_random_uuid()"))
  // This should probably have a shared id with Cloud Services so that we can look up users across them
  cloudServicesId String    @unique
  discordId       String    @unique
  createdAt       DateTime  @default(now())
  updatedAt       DateTime  @updatedAt
  lastSeen        DateTime?
  role            Role      @default(USER)
  deleted         Boolean   @default(false)

  Applications    Application[]
  Specifications  Specification[]
  LLMCallAttempts LLMCallAttempt[]
  Deployments     Deployment[]
  CompletedApp    CompletedApp[]
  Interview       Interview[]
  Modules         Module[]
}

enum Role {
  USER
  ADMIN
}

model Application {
  id          String   @id @default(dbgenerated("gen_random_uuid()"))
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
  name        String
  description String?
  deleted     Boolean  @default(false)

  Specifications  Specification[]
  CompletedApps   CompletedApp[]
  Deployments     Deployment[]
  LLMCallAttempts LLMCallAttempt[]
  User            User?            @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId          String?
  Interviews      Interview[]
  InterviewStep   InterviewStep[]
  Modules         Module[]
}

// ------------------------- Interview Models ---------------------------- //
// Interview models for the interview process
// ------------------------------------------------------------------------ //

enum InterviewPhase {
  FEATURES
  ARCHITECT
  COMPLETED
}

model Interview {
  id             String           @id @default(dbgenerated("gen_random_uuid()"))
  createdAt      DateTime         @default(now())
  updatedAt      DateTime         @updatedAt
  deleted        Boolean          @default(false)
  finished       Boolean          @default(false)
  name           String
  task           String
  User           User             @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId         String
  Application    Application      @relation(fields: [applicationId], references: [id], onDelete: Cascade)
  applicationId  String
  Specification  Specification[]
  InterviewStep  InterviewStep[]
  LLMCallAttempt LLMCallAttempt[]
}

model InterviewStep {
  id             String         @id @default(dbgenerated("gen_random_uuid()"))
  createdAt      DateTime       @default(now())
  updatedAt      DateTime       @updatedAt
  deleted        Boolean        @default(false)
  phase          InterviewPhase @default(FEATURES)
  phase_complete Boolean        @default(false)

  say            String
  thoughts       String
  Features       Feature[]
  Modules        Module[]
  access_roles   String[]
  Interview      Interview        @relation(fields: [interviewId], references: [id])
  interviewId    String
  Application    Application      @relation(fields: [applicationId], references: [id], onDelete: Cascade)
  applicationId  String
  LLMCallAttempt LLMCallAttempt[]
}

model Feature {
  id              String         @id @default(dbgenerated("gen_random_uuid()"))
  createdAt       DateTime       @default(now())
  updatedAt       DateTime       @updatedAt
  deleted         Boolean        @default(false)
  name            String         @default("")
  reasoning       String         @default("")
  functionality   String         @default("")
  InterviewStep   InterviewStep? @relation(fields: [interviewStepId], references: [id])
  interviewStepId String?
  Specification   Specification? @relation(fields: [specificationId], references: [id])
  specificationId String?

  @@map("Question")
}

// ------------------------- Requirements Models ------------------------------ //
// ApplicationRequirements represents the requirements for a specific application.  
// ------------------------- -------------------------------------------------- //

model Specification {
  id        String   @id @default(dbgenerated("gen_random_uuid()"))
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  deleted   Boolean  @default(false)

  User             User?            @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId           String?
  Interview        Interview?       @relation(fields: [interviewId], references: [id], onDelete: Cascade)
  interviewId      String?
  Features         Feature[]
  Application      Application?     @relation(fields: [applicationId], references: [id], onDelete: Cascade)
  applicationId    String?
  CompletedApps    CompletedApp[]
  DatabaseSchema   DatabaseSchema?  @relation(fields: [databaseSchemaId], references: [id], onDelete: Cascade)
  databaseSchemaId String?
  LLMCallAttempt   LLMCallAttempt[]
  Modules          Module[]
}

model Module {
  id              String         @id @default(dbgenerated("gen_random_uuid()"))
  createdAt       DateTime       @default(now())
  updatedAt       DateTime       @updatedAt
  deleted         Boolean        @default(false)
  name            String
  description     String
  interactions    String
  ApiRouteSpecs   APIRouteSpec[]
  Application     Application?   @relation(fields: [applicationId], references: [id], onDelete: Cascade)
  applicationId   String?
  User            User?          @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId          String?
  Specification   Specification? @relation(fields: [specificationId], references: [id])
  specificationId String?
  InterviewStep   InterviewStep? @relation(fields: [interviewStepId], references: [id])
  interviewStepId String?
}

enum AccessLevel {
  PUBLIC
  PROTECTED
}

enum HTTPVerb {
  GET
  POST
  PUT
  PATCH
  DELETE
  OPTIONS
  HEAD
  CONNECT
  TRACE
}

model APIRouteSpec {
  id                 String          @id @default(dbgenerated("gen_random_uuid()"))
  createdAt          DateTime        @default(now())
  functionName       String
  method             HTTPVerb
  path               String
  description        String
  AccessLevel        AccessLevel?
  AllowedAccessRoles String[]
  CompiledRoutes     CompiledRoute[]
  Module             Module?         @relation(fields: [moduleId], references: [id])
  moduleId           String?

  ResponseObject   ObjectType? @relation("ResponseObjectForAPI", fields: [responseObjectId], references: [id], onDelete: Cascade)
  responseObjectId String?
  RequestObject    ObjectType? @relation("RequestObjectForAPI", fields: [requestObjectId], references: [id], onDelete: Cascade)
  requestObjectId  String?
}

model ObjectType {
  id          String   @id @default(dbgenerated("gen_random_uuid()"))
  createdAt   DateTime @default(now())
  name        String
  description String?
  code        String?
  isPydantic  Boolean  @default(true)
  isEnum      Boolean  @default(false)

  Fields ObjectField[] @relation("ObjectTypeFields")

  // One problem I have with generating the serve code is not knowing what imports are needed for the type annotations
  // Best Idea I can think of is to include the import statement required for the type
  importStatements String[]

  DatabaseTable   DatabaseTable? @relation(fields: [databaseTableId], references: [id], onDelete: Cascade)
  databaseTableId String?

  // Reverse relations
  ReferredRequestAPIRoutes  APIRouteSpec[] @relation("RequestObjectForAPI")
  ReferredResponseAPIRoutes APIRouteSpec[] @relation("ResponseObjectForAPI")
  ReferredObjectFields      ObjectField[]  @relation("ObjectFieldTypes")
}

model ObjectField {
  id          String   @id @default(dbgenerated("gen_random_uuid()"))
  createdAt   DateTime @default(now())
  name        String
  description String?
  value       String?

  RelatedTypes ObjectType[] @relation("ObjectFieldTypes")
  typeName     String // list,dict,etc for primitive types, Type.name for composite types.

  // Reverse relations
  ReferredFunctionReturns Function[]  @relation("FunctionReturns")
  ReferredFunctionArgs    Function[]  @relation("FunctionArgs")
  ReferredObjectType      ObjectType? @relation("ObjectTypeFields", fields: [referredObjectTypeId], references: [id])
  referredObjectTypeId    String?
}

// DatabaseSchema represents the schema of a database.
// It includes a description and relationships to database tables and code graphs.
model DatabaseSchema {
  id             String                       @id @default(dbgenerated("gen_random_uuid()"))
  name           String?
  createdAt      DateTime                     @default(now())
  embedding      Unsupported("vector(1536)")?
  description    String
  DatabaseTables DatabaseTable[]
  Functions      Function[]
  Specification  Specification[]
}

// DatabaseTable represents a table within a database schema.
// It includes details about the table and relationships to schemas and other tables.
model DatabaseTable {
  id          String                       @id @default(dbgenerated("gen_random_uuid()"))
  name        String?
  createdAt   DateTime                     @default(now())
  embedding   Unsupported("vector(1536)")?
  description String
  definition  String
  isEnum      Boolean                      @default(false)

  // This seems like its gonna be a nightmare to keep track of for 1-1 1-M and M-M relationships
  RelatedFromTables DatabaseTable[] @relation("TableRelations")
  RelatedToTables   DatabaseTable[] @relation("TableRelations")
  DatabaseSchema    DatabaseSchema? @relation(fields: [databaseSchemaId], references: [id])
  databaseSchemaId  String?
  ObjectType        ObjectType[]
}

// ------------------------- Developer Models ---------------------------- //
// Database models for the developer
// ------------------------------------------------------------------------ //
enum FunctionState {
  DEFINITION
  WRITTEN
  VERIFIED
  FAILED
}

// CodeGraph represents a graph-based representation of code or logic.
// It is associated with function definitions and can be linked to a database schema.
model Function {
  id        String   @id @default(dbgenerated("gen_random_uuid()"))
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  functionName String
  description  String?
  template     String // This is the template of the function along with the docstring
  state        FunctionState

  Packages         Package[]
  rawCode          String? // This is the unpocessed code returned form the llm
  importStatements String[] // These are the import statements for the function
  functionCode     String? // This is the code of the function being written

  FunctionArgs           ObjectField[] @relation("FunctionArgs") // Function Args need name-type pairs
  FunctionReturn         ObjectField?  @relation("FunctionReturns", fields: [functionReturnObjectId], references: [id], onDelete: Cascade)
  functionReturnObjectId String?

  ParentFunction   Function?  @relation("ParentFunction", fields: [parentFunctionId], references: [id], onDelete: Cascade)
  parentFunctionId String?
  ChildFunctions   Function[] @relation("ParentFunction")

  DatabaseSchema   DatabaseSchema? @relation(fields: [databaseSchemaId], references: [id], onDelete: Cascade)
  databaseSchemaId String?
  CompiledRoute    CompiledRoute?  @relation("CompiledRouteFunctions", fields: [compiledRouteId], references: [id], onDelete: Cascade)
  compiledRouteId  String?

  // Reverse relations
  ReferredFunctionRootCompiledRoute CompiledRoute?   @relation("RootFunction")
  LLMCallAttempt                    LLMCallAttempt[]
}

model Package {
  id             String          @id @default(dbgenerated("gen_random_uuid()"))
  createdAt      DateTime        @default(now())
  packageName    String
  version        String
  specifier      String
  Functions      Function[]
  CompiledRoutes CompiledRoute[]
}

// ------------------------- Delivery Models ----------------------------- //

// CompiledRoute represents a version of a route that has been processed or compiled.
// It links to its source code graph and associated functions and applications.
model CompiledRoute {
  id          String                       @id @default(dbgenerated("gen_random_uuid()"))
  createdAt   DateTime                     @default(now())
  embedding   Unsupported("vector(1536)")?
  description String
  Packages    Package[]

  fileName         String
  mainFunctionName String
  compiledCode     String

  CompletedApp   CompletedApp @relation(fields: [completedAppId], references: [id], onDelete: Cascade)
  completedAppId String

  Functions      Function[] @relation("CompiledRouteFunctions")
  RootFunction   Function   @relation("RootFunction", fields: [rootFunctionId], references: [id], onDelete: Cascade)
  rootFunctionId String     @unique

  ApiRouteSpec   APIRouteSpec?    @relation(fields: [apiRouteSpecId], references: [id], onDelete: Cascade)
  apiRouteSpecId String?
  LLMCallAttempt LLMCallAttempt[]
}

// Application represents the main software application entity.
// It includes basic metadata and has relationships with compiled routes.
model CompletedApp {
  id          String   @id @default(dbgenerated("gen_random_uuid()"))
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
  deleted     Boolean  @default(false)
  name        String
  description String

  User            User?           @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId          String?
  CompiledRoutes  CompiledRoute[]
  Deployments     Deployment[]
  Specification   Specification?  @relation(fields: [specificationId], references: [id], onDelete: Cascade)
  specificationId String?
  Application     Application?    @relation(fields: [applicationId], references: [id])
  applicationId   String?

  companionCompletedAppId String?

  LLMCallAttempt LLMCallAttempt[]
}

// ---------------------------------------------------------------------- //
// ------------------------- Deployment Model --------------------------- //
// ---------------------------------------------------------------------- //

model Deployment {
  id         String   @id @default(dbgenerated("gen_random_uuid()"))
  createdAt  DateTime @default(now())
  updatedAt  DateTime @updatedAt
  deleted    Boolean  @default(false)
  fileName   String?
  fileSize   Int?
  path       String?
  fileBytes  Bytes?
  repo       String?  @unique
  dbName     String?  @unique
  dbUser     String?  @unique
  zipFile    Boolean  @default(false)
  githubRepo Boolean  @default(true)
  hosted     Boolean  @default(true)

  User           User?            @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId         String?
  Application    Application?     @relation(fields: [applicationId], references: [id])
  applicationId  String?
  CompletedApp   CompletedApp?    @relation(fields: [completedAppId], references: [id], onDelete: Cascade)
  completedAppId String?
  LLMCallAttempt LLMCallAttempt[]
}

// ------------------------- LLM Models ----------------------------- //

model LLMCallTemplate {
  id               String           @id @default(dbgenerated("gen_random_uuid()"))
  createdAt        DateTime         @default(now())
  developmentPhase DevelopmentPhase
  templateName     String
  model            String
  fileHash         String
  systemPrompt     String
  userPrompt       String
  retryPrompt      String

  Uses LLMCallAttempt[]
}

enum DevelopmentPhase {
  REQUIREMENTS
  DEVELOPMENT
  DEPLOYMENT
}

model LLMCallAttempt {
  id               String   @id @default(dbgenerated("gen_random_uuid()"))
  createdAt        DateTime @default(now())
  model            String
  completionTokens Int
  promptTokens     Int
  totalTokens      Int

  FirstCall   LLMCallAttempt?  @relation("FirstCall", fields: [firstCallId], references: [id], onDelete: Cascade)
  firstCallId String?
  RetryCalls  LLMCallAttempt[] @relation("FirstCall")

  attempt  Int
  prompt   Json
  response String

  User            User?          @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId          String?
  Application     Application?   @relation(fields: [applicationId], references: [id], onDelete: Cascade)
  applicationId   String?
  Interview       Interview?     @relation(fields: [interviewId], references: [id])
  interviewId     String?
  Specification   Specification? @relation(fields: [specificationId], references: [id])
  specificationId String?
  CompiledRoute   CompiledRoute? @relation(fields: [compiledRouteId], references: [id])
  compiledRouteId String?
  Function        Function?      @relation(fields: [functionId], references: [id])
  functionId      String?
  CompletedApp    CompletedApp?  @relation(fields: [completedAppId], references: [id])
  completedAppId  String?
  Deployment      Deployment?    @relation(fields: [deploymentId], references: [id])
  deploymentId    String?

  LLMCallTemplate   LLMCallTemplate? @relation(fields: [llmCallTemplateId], references: [id], onDelete: Cascade)
  llmCallTemplateId String?
  InterviewStep     InterviewStep?   @relation(fields: [interviewStepId], references: [id])
  interviewStepId   String?
}

enum Status {
  STARTED
  SUCCESS
  FAILED
}

model EventLog {
  id            String           @id @default(dbgenerated("gen_random_uuid()"))
  createdAt     DateTime         @default(now())
  userId        String?
  applicationId String?
  keyType       DevelopmentPhase
  keyValue      String
  event         String
  status        Status
  message       String?

  @@index([userId, applicationId])
  @@index([keyValue])
}