Skip to main content

​
Overview

ARCA uses PostgreSQL as its database and Prisma ORM for type-safe database access. The schema is defined in apps/backend/prisma/schema.prisma and includes models for users, patients, appointments, documents, and audit logs.
All IDs use UUID format for better security and scalability. Foreign keys maintain referential integrity with CASCADE or RESTRICT policies.

​
Core Entities

​
Usuario (User)

Stores system users including interns (estagiários) and supervisors.
​
id_User
UUID
required
Primary key - Unique user identifier
​
nome
String(50)
required
User’s full name
​
email
String(100)
required
Unique email address for authentication
​
senhaHash
String(255)
required
Bcrypt hashed password
​
roleId
SmallInt
required
Foreign key to Role table
​
isActive
Boolean
default:"true"
User account status
Relationships:
  • role → Many-to-One with Role
  • documentosUsuario → One-to-Many with DocumentoUsuario
  • logsExecutados → One-to-Many with LogAuditoria
  • atendimentoComoEstagiario → One-to-Many with Atendimento
  • atendimentoComoSupervisor → One-to-Many with Atendimento
  • relatorioComoEstagiario → One-to-Many with RelatorioAlta
  • relatorioComoSupervisor → One-to-Many with RelatorioAlta
  • pacientesComoEstagiario → One-to-Many with Paciente
  • pacientesComoSupervisor → One-to-Many with Paciente
model Usuario {
  id_User   String  @id @default(uuid()) @db.Uuid
  nome      String  @db.VarChar(50)
  email     String  @unique @db.VarChar(100)
  senhaHash String  @db.VarChar(255)
  roleId    Int     @db.SmallInt
  isActive  Boolean @default(true)

  role                      Role               @relation(fields: [roleId], references: [id_Role])
  documentosUsuario         DocumentoUsuario[] @relation("UsuarioDocumentos")
  logsExecutados            LogAuditoria[]     @relation("UsuarioExecutorLogs")
  atendimentoComoEstagiario Atendimento[]      @relation("EstagiarioAtendimentos")
  atendimentoComoSupervisor Atendimento[]      @relation("SupervisorAtendimentos")
  relatorioComoEstagiario   RelatorioAlta[]    @relation("EstagiarioRelatorios")
  relatorioComoSupervisor   RelatorioAlta[]    @relation("SupervisorRelatorios")
  pacientesComoEstagiario   Paciente[]         @relation("EstagiarioResponsavel")
  pacientesComoSupervisor   Paciente[]         @relation("SupervisorResponsavel")

  @@map("USUARIOS")
}

​
Role

Defines user roles (e.g., Intern, Supervisor, Admin).
​
id_Role
SmallInt
required
Primary key - Auto-incremented role ID
​
role
String(50)
required
Role name (e.g., “ESTAGIARIO”, “SUPERVISOR”)
​
descricao
String(255)
required
Role description
Relationships:
  • usuarios → One-to-Many with Usuario
model Role {
  id_Role   Int       @id @default(autoincrement()) @db.SmallInt
  role      String    @db.VarChar(50)
  descricao String    @db.VarChar(255)
  usuarios  Usuario[]

  @@map("ROLES")
}

​
Paciente (Patient)

Complete patient records with demographic and contact information.
​
id_Paciente
UUID
required
Primary key - Unique patient identifier
​
nomeRegistro
String(150)
required
Legal registered name
​
nomeSocial
String(100)
Preferred social name (optional)
​
dataNascimento
Date
required
Date of birth
​
id_Genero
SmallInt
required
Foreign key to Genero table
​
id_CorPele
SmallInt
required
Foreign key to CorPele table
​
id_Escolaridade
SmallInt
required
Foreign key to Escolaridade table
​
telefonePessoal
String(20)
required
Personal phone number
​
contatoEmergencia
String(20)
required
Emergency contact number
​
enderecoRua
String(255)
required
Street address
​
enderecoNumero
String(10)
required
Address number
​
enderecoBairro
String(100)
required
Neighborhood
​
enderecoCidade
String(100)
required
City
​
enderecoEstado
Char(2)
required
State (2-letter abbreviation)
​
enderecoCEP
Char(8)
required
Postal code (CEP)
​
dataInicioTratamento
Date
required
Treatment start date
​
id_Estagiario_Responsavel
UUID
required
Assigned intern (foreign key to Usuario)
​
id_Supervisor_Responsavel
UUID
required
Assigned supervisor (foreign key to Usuario)
Relationships:
  • genero → Many-to-One with Genero
  • corPele → Many-to-One with CorPele
  • escolaridade → Many-to-One with Escolaridade
  • estagiarioResponsavel → Many-to-One with Usuario
  • supervisorResponsavel → Many-to-One with Usuario
  • relatoriosAlta → One-to-Many with RelatorioAlta
  • logsAuditoria → One-to-Many with LogAuditoria
  • atendimentos → One-to-Many with Atendimento
model Paciente {
  id_Paciente               String   @id @default(uuid()) @db.Uuid
  nomeRegistro              String   @db.VarChar(150)
  nomeSocial                String?  @db.VarChar(100)
  dataNascimento            DateTime @db.Date
  id_Genero                 Int      @db.SmallInt
  id_CorPele                Int      @db.SmallInt
  id_Escolaridade           Int      @db.SmallInt
  telefonePessoal           String   @db.VarChar(20)
  contatoEmergencia         String   @db.VarChar(20)
  enderecoRua               String   @db.VarChar(255)
  enderecoNumero            String   @db.VarChar(10)
  enderecoBairro            String   @db.VarChar(100)
  enderecoCidade            String   @db.VarChar(100)
  enderecoEstado            String   @db.Char(2)
  enderecoCEP               String   @db.Char(8)
  dataInicioTratamento      DateTime @db.Date
  id_Estagiario_Responsavel String   @db.Uuid
  id_Supervisor_Responsavel String   @db.Uuid

  genero                Genero       @relation(fields: [id_Genero], references: [id_Genero])
  corPele               CorPele      @relation(fields: [id_CorPele], references: [id_CorPele])
  escolaridade          Escolaridade @relation(fields: [id_Escolaridade], references: [id_Escolaridade])
  estagiarioResponsavel Usuario      @relation("EstagiarioResponsavel", fields: [id_Estagiario_Responsavel], references: [id_User])
  supervisorResponsavel Usuario      @relation("SupervisorResponsavel", fields: [id_Supervisor_Responsavel], references: [id_User])

  relatoriosAlta RelatorioAlta[]
  logsAuditoria  LogAuditoria[]
  atendimentos   Atendimento[]

  @@map("PACIENTE")
}

​
Documents & Reports

​
DocumentoUsuario (User Document)

Stores user-uploaded documents and files.
​
id_Documento
UUID
required
Primary key - Document identifier
​
id_User
UUID
required
Foreign key to Usuario
​
nomeArquivo
String(100)
required
Original file name
​
caminhoArquivo
String(255)
required
File storage path
​
dataUpload
DateTime
default:"now()"
Upload timestamp
Relationships:
  • usuario → Many-to-One with Usuario (CASCADE delete)
model DocumentoUsuario {
  id_Documento   String   @id @default(uuid()) @db.Uuid
  id_User        String   @db.Uuid
  nomeArquivo    String   @db.VarChar(100)
  caminhoArquivo String   @db.VarChar(255)
  dataUpload     DateTime @default(now())

  usuario Usuario @relation("UsuarioDocumentos", fields: [id_User], references: [id_User], onDelete: Cascade)

  @@map("DOCUMENTOS_USUARIO")
}

​
RelatorioAlta (Discharge Report)

Patient discharge reports created by interns and approved by supervisors.
​
id_Documento
UUID
required
Primary key - Report identifier
​
id_Paciente
UUID
required
Foreign key to Paciente
​
id_Estagiario
UUID
required
Foreign key to Usuario (intern who created report)
​
id_Supervisor
UUID
required
Foreign key to Usuario (supervisor who reviewed)
​
conteudo
Text
required
Report content/body
​
dataEmissao
DateTime
default:"now()"
Report creation timestamp
​
status
StatusRelatorioEnum
default:"PENDENTE"
Report status (PENDENTE, EMITIDO, CANCELADO)
Relationships:
  • paciente → Many-to-One with Paciente
  • estagiario → Many-to-One with Usuario
  • supervisor → Many-to-One with Usuario
model RelatorioAlta {
  id_Documento  String              @id @default(uuid()) @db.Uuid
  id_Paciente   String              @db.Uuid
  id_Estagiario String              @db.Uuid
  id_Supervisor String              @db.Uuid
  conteudo      String              @db.Text
  dataEmissao   DateTime            @default(now())
  status        StatusRelatorioEnum @default(PENDENTE)

  paciente   Paciente @relation(fields: [id_Paciente], references: [id_Paciente])
  estagiario Usuario  @relation("EstagiarioRelatorios", fields: [id_Estagiario], references: [id_User])
  supervisor Usuario  @relation("SupervisorRelatorios", fields: [id_Supervisor], references: [id_User])

  @@map("RELATORIOS_ALTA")
}

enum StatusRelatorioEnum {
  PENDENTE
  EMITIDO
  CANCELADO
}

​
Audit & Sessions

​
LogAuditoria (Audit Log)

Complete audit trail for LGPD compliance and security.
​
id_Log
UUID
required
Primary key - Log entry identifier
​
id_Usuario_Executor
UUID
required
User who performed the action
​
id_Paciente
UUID
required
Patient affected by the action
​
tipoAcao
TipoAcaoEnum
required
Action type (CREATION, VIEW, UPDATE, DELETION)
​
acessoEm
DateTime
default:"now()"
Action timestamp
​
detalhes
Text
required
Detailed description of the action
Relationships:
  • usuarioExecutor → Many-to-One with Usuario
  • paciente → Many-to-One with Paciente
model LogAuditoria {
  id_Log              String       @id @default(uuid()) @db.Uuid
  id_Usuario_Executor String       @db.Uuid
  id_Paciente         String       @db.Uuid
  tipoAcao            TipoAcaoEnum
  acessoEm            DateTime     @default(now())
  detalhes            String       @db.Text

  usuarioExecutor Usuario  @relation("UsuarioExecutorLogs", fields: [id_Usuario_Executor], references: [id_User])
  paciente        Paciente @relation(fields: [id_Paciente], references: [id_Paciente])

  @@map("LOGS_AUDITORIA")
}

enum TipoAcaoEnum {
  CREATION
  VIEW
  UPDATE
  DELETION
}

​
Atendimento (Appointment)

Patient appointment sessions with start/end times and notes.
​
id_Atendimento
UUID
required
Primary key - Appointment identifier
​
dataHoraInicio
Timestamp
required
Appointment start time
​
dataHoraFim
Timestamp
required
Appointment end time
​
id_Paciente
UUID
required
Foreign key to Paciente
​
id_Estagiario_Executor
UUID
required
Intern conducting the appointment
​
id_Supervisor_Executor
UUID
required
Supervisor overseeing the appointment
​
id_Status
SmallInt
required
Foreign key to StatusAtendimento
​
observacoes
Text
required
Session notes and observations
Relationships:
  • paciente → Many-to-One with Paciente (CASCADE delete)
  • estagiarioExecutor → Many-to-One with Usuario
  • supervisorExecutor → Many-to-One with Usuario
  • status → Many-to-One with StatusAtendimento
model Atendimento {
  id_Atendimento         String   @id @default(uuid()) @db.Uuid
  dataHoraInicio         DateTime @db.Timestamp()
  dataHoraFim            DateTime @db.Timestamp()
  id_Paciente            String   @db.Uuid
  id_Estagiario_Executor String   @db.Uuid
  id_Supervisor_Executor String   @db.Uuid
  id_Status              Int      @db.SmallInt
  observacoes            String   @db.Text

  paciente           Paciente          @relation(fields: [id_Paciente], references: [id_Paciente], onDelete: Cascade)
  estagiarioExecutor Usuario           @relation("EstagiarioAtendimentos", fields: [id_Estagiario_Executor], references: [id_User])
  supervisorExecutor Usuario           @relation("SupervisorAtendimentos", fields: [id_Supervisor_Executor], references: [id_User])
  status             StatusAtendimento @relation(fields: [id_Status], references: [id_Status])

  @@map("ATENDIMENTOS")
}

​
ListaEspera (Waitlist)

Pending patients awaiting assignment to treatment.
​
id_Lista
UUID
required
Primary key - Waitlist entry identifier
​
nomeRegistro
String(150)
required
Legal registered name
​
nomeSocial
String(100)
Preferred social name (optional)
​
dataNascimento
Date
required
Date of birth
​
telefonePessoal
String(20)
required
Personal phone number
​
contatoEmergencia
String(20)
required
Emergency contact
​
enderecoRua
String(255)
required
Street address
​
enderecoNumero
String(10)
required
Address number
​
enderecoBairro
String(100)
required
Neighborhood
​
enderecoCidade
String(100)
required
City
​
enderecoEstado
Char(2)
required
State abbreviation
​
enderecoCEP
Char(8)
required
Postal code
​
createdAt
DateTime
default:"now()"
Entry creation timestamp
​
isActive
Boolean
default:"true"
Active status
​
id_Genero
SmallInt
required
Foreign key to Genero
​
id_CorPele
SmallInt
required
Foreign key to CorPele
​
id_Escolaridade
SmallInt
required
Foreign key to Escolaridade
Relationships:
  • genero → Many-to-One with Genero
  • corPele → Many-to-One with CorPele
  • escolaridade → Many-to-One with Escolaridade
model ListaEspera {
  id_Lista          String   @id @default(uuid()) @db.Uuid
  nomeRegistro      String   @db.VarChar(150)
  nomeSocial        String?  @db.VarChar(100)
  dataNascimento    DateTime @db.Date
  telefonePessoal   String   @db.VarChar(20)
  contatoEmergencia String   @db.VarChar(20)
  enderecoRua       String   @db.VarChar(255)
  enderecoNumero    String   @db.VarChar(10)
  enderecoBairro    String   @db.VarChar(100)
  enderecoCidade    String   @db.VarChar(100)
  enderecoEstado    String   @db.Char(2)
  enderecoCEP       String   @db.Char(8)
  createdAt         DateTime @default(now())
  isActive          Boolean  @default(true)
  id_Genero         Int      @db.SmallInt
  id_CorPele        Int      @db.SmallInt
  id_Escolaridade   Int      @db.SmallInt

  genero       Genero       @relation(fields: [id_Genero], references: [id_Genero])
  corPele      CorPele      @relation(fields: [id_CorPele], references: [id_CorPele])
  escolaridade Escolaridade @relation(fields: [id_Escolaridade], references: [id_Escolaridade])

  @@map("LISTA_ESPERA")
}

​
Lookup Tables

These tables store predefined reference data.

​
Genero (Gender)

model Genero {
  id_Genero   Int           @id @default(autoincrement()) @db.SmallInt
  nome        String        @db.VarChar
  pacientes   Paciente[]
  listaEspera ListaEspera[]

  @@map("GENERO")
}
Fields:
  • id_Genero - Primary key
  • nome - Gender name (e.g., “Masculino”, “Feminino”, “Não-binário”)

​
CorPele (Skin Color)

model CorPele {
  id_CorPele  Int           @id @default(autoincrement()) @db.SmallInt
  nome        String        @db.VarChar
  pacientes   Paciente[]
  listaEspera ListaEspera[]

  @@map("CORES_PELE")
}
Fields:
  • id_CorPele - Primary key
  • nome - Skin color classification (IBGE categories)

​
Escolaridade (Education Level)

model Escolaridade {
  id_Escolaridade Int           @id @default(autoincrement()) @db.SmallInt
  nome            String        @db.VarChar
  pacientes       Paciente[]
  listaEspera     ListaEspera[]

  @@map("ESCOLARIDADES")
}
Fields:
  • id_Escolaridade - Primary key
  • nome - Education level (e.g., “Fundamental”, “Médio”, “Superior”)

​
StatusAtendimento (Appointment Status)

model StatusAtendimento {
  id_Status    Int           @id @default(autoincrement()) @db.SmallInt
  nome         String        @db.VarChar
  atendimentos Atendimento[]

  @@map("STATUS_ATENDIMENTO")
}
Fields:
  • id_Status - Primary key
  • nome - Status name (e.g., “Agendado”, “Concluído”, “Cancelado”)

​
Entity Relationship Diagram

Here’s a visual representation of the database relationships:

​
Database Operations

​
Migrations

Create and apply schema changes: 
# Create a new migration
cd apps/backend
npx prisma migrate dev --name add_new_field

​
Seeding

Populate initial data: 
cd apps/backend
npm run db:seed

​
Prisma Studio

Visual database browser: 
cd apps/backend
npx prisma studio
# Opens at http://localhost:5555

​
Indexes and Performance

Consider adding indexes for frequently queried fields:
  • Usuario.email - Already unique indexed
  • Paciente.id_Estagiario_Responsavel - Foreign key index
  • Atendimento.dataHoraInicio - For date range queries
  • LogAuditoria.acessoEm - For audit reports
Example index creation: 
model Atendimento {
  // ... existing fields ...
  
  @@index([dataHoraInicio])
  @@index([id_Paciente, dataHoraInicio])
}

​
Data Integrity

​
Cascade Deletes

  • DocumentoUsuario → Deletes when Usuario is deleted
  • Atendimento → Deletes when Paciente is deleted

​
Referential Integrity

All foreign keys enforce referential integrity:
  • Cannot delete a Role if users exist with that role
  • Cannot delete a Usuario if they have associated patients
  • Cannot delete lookup table entries if referenced

​
LGPD Compliance

The schema supports LGPD (Brazilian data protection law) compliance:

Audit Trail

LogAuditoria tracks all data access and modifications

Data Minimization

Only necessary fields collected and stored

Soft Deletes

isActive flags allow data retention with privacy

Access Control

Role-based permissions restrict data access

Build docs developers (and LLMs) love

Get started for freeTalk to us