Skip to main content

​
Overview

The Medical Center API uses PostgreSQL with Prisma ORM. The database architecture implements a two-tier user system that separates internal staff (authenticated via Supabase) from public clients (unauthenticated).

​
Core Models

​
Personas (Internal Personnel)

Stores personal information for all internal staff members including admins, assistants, and doctors. 
model Personas {
  id         Int      @id @default(autoincrement())
  nombres    String
  apellidos  String
  telefono   String?
  dni        String?  @unique
  foto_url   String? 
  created_at DateTime @default(now())

  usuario Usuarios?
  medico  Medicos?

  @@map("personas")
}
The Personas model serves as the base table for all internal system users. A persona can be linked to either a Usuarios record (for authentication) or a Medicos record (for doctor-specific data).

​
Usuarios (Authenticated Internal Users)

Links Supabase authentication to internal personnel with role-based access control. 
model Usuarios {
  id         String   @id               // UUID from Supabase Auth
  persona_id Int      @unique
  role       rol_usuario
  created_at DateTime @default(now())

  persona Personas @relation(fields: [persona_id], references: [id], onDelete: Cascade)

  @@map("usuarios")
}
​
id
String
required
UUID string from Supabase Auth, not auto-generated
​
persona_id
Int
required
Foreign key to Personas table (unique - one user per persona)
​
role
rol_usuario
required
Enum: admin or asistente

​
Clientes_publicos (Public Clients)

UnAuthenticated users who request appointments through the public API. 
model Clientes_publicos {
  id         Int      @id @default(autoincrement())
  nombres    String
  apellidos  String?
  telefono   String?
  email      String?
  created_at DateTime @default(now())

  citas     Citas[]
  contactos Contacto[]

  @@map("clientes_publicos")
}
Public clients are NOT authenticated. They are created on-the-fly when submitting appointment requests. Each appointment creates a new client record.

​
Medicos (Doctors)

Doctor profiles linked to personas with specialty information. 
model Medicos {
  id              Int      @id @default(autoincrement())
  persona_id      Int      @unique
  especialidad_id Int
  email           String   @unique
  colegiatura     String?
  activo          Boolean  @default(true)
  created_at      DateTime @default(now())

  persona      Personas       @relation(fields: [persona_id], references: [id], onDelete: Cascade)
  especialidad Especialidades @relation(fields: [especialidad_id], references: [id])
  citas        Citas[]

  @@map("medicos")
}
Doctors do not authenticate directly. They are managed by admins and linked to personas, but they don’t have login credentials.

​
Especialidades (Medical Specialties)

model Especialidades {
  id          Int      @id @default(autoincrement())
  nombre      String   @unique
  descripcion String?
  created_at  DateTime @default(now())

  medicos Medicos[]

  @@map("especialidades")
}

​
Citas (Appointments)

Tracks the complete appointment lifecycle from public request to final status. 
model Citas {
  id                  Int         @id @default(autoincrement())
  cliente_id          Int

  // Public user request data
  fecha_solicitada    DateTime    @db.Date
  hora_solicitada     DateTime    @db.Time

  // Assistant confirmation data
  fecha_confirmada    DateTime?   @db.Date
  hora_confirmada     DateTime?   @db.Time

  medico_id           Int?
  sintomas            String?
  estado              estado_cita @default(pendiente)
  created_at          DateTime    @default(now())

  cliente Clientes_publicos @relation(fields: [cliente_id], references: [id], onDelete: Cascade)
  medico  Medicos?          @relation(fields: [medico_id], references: [id])
  historial Historial[]

  @@map("citas")
}
  • fecha_solicitada / hora_solicitada: The date/time requested by the public client
  • fecha_confirmada / hora_confirmada: The actual date/time assigned by the assistant when confirming
  • These can differ - the assistant may reschedule based on doctor availability

​
Contacto (Contact Requests)

model Contacto {
  id         Int      @id @default(autoincrement())
  cliente_id Int
  asunto     String
  mensaje    String
  created_at DateTime @default(now())

  cliente Clientes_publicos @relation(fields: [cliente_id], references: [id], onDelete: Cascade)

  @@map("contacto")
}

​
Historial (Medical History)

Internal notes for attended appointments. 
model Historial {
  id         Int      @id @default(autoincrement())
  cita_id    Int
  notas      String?
  created_at DateTime @default(now())

  cita Citas @relation(fields: [cita_id], references: [id], onDelete: Cascade)

  @@map("historial")
}

​
Enums

​
rol_usuario

Defines the two internal user roles: 
enum rol_usuario {
  admin
  asistente
}
​
admin
enum
Full system access: manage users, doctors, appointments, and all resources
​
asistente
enum
Limited access: manage appointments and view doctors (read-only for users)

​
estado_cita

Tracks the appointment status throughout its lifecycle: 
enum estado_cita {
  pendiente
  confirmada
  cancelada
  atendida
}
​
pendiente
enum
Initial state when a public client submits an appointment request
​
confirmada
enum
Assistant has assigned a doctor and confirmed the date/time
​
cancelada
enum
Appointment was cancelled (doctor assignment is removed)
​
atendida
enum
Appointment was completed (can only transition from confirmada)

​
Relationships

​
Internal Staff Hierarchy

Personas (1) ──→ (0..1) Usuarios    [Authentication]
         (1) ──→ (0..1) Medicos     [Doctor profile]
A persona can be either a Usuario (admin/assistant) OR a Medico (doctor), but not both. Doctors don’t authenticate.

​
Appointment Flow

Clientes_publicos (1) ──→ (N) Citas
Citas (N) ──→ (0..1) Medicos
Citas (1) ──→ (N) Historial

​
Specialties

Especialidades (1) ──→ (N) Medicos

​
Cascade Delete Behavior

The following deletions cascade automatically:
  • Deleting a Personas record deletes linked Usuarios and Medicos
  • Deleting a Clientes_publicos record deletes all their Citas and Contacto records
  • Deleting a Citas record deletes all linked Historial entries

​
Database Provider

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}
The schema uses PostgreSQL with environment-based connection configuration.

Build docs developers (and LLMs) love

Get started for freeTalk to us