Database Models - VidaPlus API

Overview

VidaPlus uses SQLAlchemy ORM with a well-structured relational database schema. The system is built around a polymorphic user model and connected healthcare entities.

Database Configuration

The database connection is configured in vidaplus/database.py:6:

from sqlalchemy import create_engine
from sqlalchemy.orm import Session
from vidaplus.settings import Settings

engine = create_engine(Settings().DATABASE_URL)

def get_session():
    with Session(engine) as session:
        yield session

Set the DATABASE_URL environment variable to configure your database connection (PostgreSQL, MySQL, SQLite, etc.)

Core Models

User Model Hierarchy

VidaPlus implements polymorphic inheritance for users with three specialized types:

@table_registry.mapped_as_dataclass
class BaseUser:
    __tablename__ = 'users'
    __mapper_args__ = {
        'polymorphic_on': 'tipo',
        'polymorphic_identity': 'BASE',
    }

    id: Mapped[int] = mapped_column(primary_key=True, init=False)
    nome: Mapped[str] = mapped_column(String(255))
    email: Mapped[str] = mapped_column(String(255), unique=True, nullable=False)
    senha: Mapped[str] = mapped_column(String(255), nullable=False)
    telefone: Mapped[str] = mapped_column(String(20), nullable=True)
    created_at: Mapped[datetime] = mapped_column(server_default=func.now(), init=False)
    tipo: Mapped[UserRole] = mapped_column(Enum(UserRole))
    is_active: Mapped[bool] = mapped_column()
    is_superuser: Mapped[bool] = mapped_column()

AdminUser

Administrative users with full system access:

@table_registry.mapped_as_dataclass
class AdminUser(BaseUser):
    __tablename__ = 'admin_users'
    __mapper_args__ = {
        'polymorphic_identity': UserRole.ADMIN.value,
    }

    id: Mapped[int] = mapped_column(ForeignKey('users.id'), primary_key=True, init=False)

PacienteUser

Patient users with personal and address information:

@table_registry.mapped_as_dataclass
class PacienteUser(BaseUser):
    __tablename__ = 'paciente_users'
    __mapper_args__ = {
        'polymorphic_identity': UserRole.PACIENTE.value,
    }

    id: Mapped[int] = mapped_column(ForeignKey('users.id'), primary_key=True, init=False)
    cpf: Mapped[str] = mapped_column(String(14), unique=True, nullable=False, index=True)
    data_nascimento: Mapped[date] = mapped_column(Date)
    endereco: Mapped[str] = mapped_column(String(255))
    complemento: Mapped[str] = mapped_column(String(100), nullable=True)
    numero: Mapped[int] = mapped_column(Integer)
    bairro: Mapped[str] = mapped_column(String(100))
    cidade: Mapped[str] = mapped_column(String(100))
    estado: Mapped[str] = mapped_column(String(2))
    cep: Mapped[str] = mapped_column(String(10))

ProfissionalUser

Healthcare professionals with credentials and specialties:

@table_registry.mapped_as_dataclass
class ProfissionalUser(BaseUser):
    __tablename__ = 'profissional_users'
    __mapper_args__ = {
        'polymorphic_identity': UserRole.PROFISSIONAL.value,
    }

    id: Mapped[int] = mapped_column(ForeignKey('users.id'), primary_key=True, init=False)
    crmCoren: Mapped[str] = mapped_column(String(10), unique=True, nullable=False, index=True)
    especialidade: Mapped[Especialidade] = mapped_column(Enum(Especialidade))
    biografia: Mapped[str] = mapped_column(String(255), nullable=True)
    horario_atendimento: Mapped[HorarioAtendimento] = mapped_column(Enum(HorarioAtendimento))

Available Specialties

class Especialidade(Enum):
    CLINICA_GERAL = 'Clinica Geral'
    DERMATOLOGIA = 'Dermatologia'
    GINECOLOGIA = 'Ginecologia'
    ORTOPEDIA = 'Ortopedia'
    PEDIATRIA = 'Pediatria'
    PSIQUIATRIA = 'Psiquiatria'
    PSICOLOGIA = 'Psicologia'
    NUTRICAO = 'Nutrição'
    FISIOTERAPIA = 'Fisioterapia'
    ODONTOLOGIA = 'Odontologia'

Available Schedules

class HorarioAtendimento(Enum):
    MANHA = 'Manhã (08:00 - 12:00)'
    TARDE = 'Tarde (13:00 - 18:00)'
    NOITE = 'Noite (18:00 - 22:00)'
    INTEGRAL = 'Integral (08:00 - 22:00)'
    PLANTAO_12H = 'Plantão (12h)'
    PLANTAO_24H = 'Plantão (24h)'

Healthcare Models

Consulta (Appointment)

Appointment scheduling between patients and professionals:

@table_registry.mapped_as_dataclass
class Consulta:
    __tablename__ = 'consultas'

    id: Mapped[int] = mapped_column(primary_key=True, init=False)
    data: Mapped[date] = mapped_column(Date)
    hora: Mapped[time] = mapped_column(Time)
    paciente_id: Mapped[int] = mapped_column(ForeignKey('paciente_users.id'), nullable=False)
    profissional_id: Mapped[int] = mapped_column(ForeignKey('profissional_users.id'), nullable=False)
    prontuario_id: Mapped[int] = mapped_column(ForeignKey('prontuarios.id'))
    status: Mapped[Status] = mapped_column(Enum(Status), nullable=False)
    tipoConsulta: Mapped[TipoConsulta] = mapped_column(Enum(TipoConsulta), nullable=False)
    link: Mapped[str] = mapped_column(String(255), nullable=True)
    observacao: Mapped[str] = mapped_column(String(255), nullable=True)

Consultation Status

class Status(Enum):
    AGENDADA = 'Agendada'
    REALIZADA = 'Realizada'
    CANCELADA = 'Cancelada'
    NAO_COMPARECEU = 'Não Compareceu'
    REAGENDADA = 'Reagendada'
    PENDENTE = 'Pendente'

Consultation Type

class TipoConsulta(Enum):
    TELECONSULTA = 'Teleconsulta'
    PRESENCIAL = 'Presencial'

Prescricao (Prescription)

Medical prescriptions issued by professionals:

@table_registry.mapped_as_dataclass
class Prescricao:
    __tablename__ = 'prescricoes'

    id: Mapped[int] = mapped_column(primary_key=True, init=False)
    data: Mapped[date] = mapped_column(Date)
    paciente_id: Mapped[PacienteUser] = mapped_column(ForeignKey('paciente_users.id'), nullable=False)
    profissional_id: Mapped[ProfissionalUser] = mapped_column(ForeignKey('profissional_users.id'), nullable=False)
    prontuario_id: Mapped[int] = mapped_column(ForeignKey('prontuarios.id'))
    tipo_prescricao: Mapped[TipoPrescricao] = mapped_column(Enum(TipoPrescricao), nullable=False)
    medicamentos: Mapped[str] = mapped_column(String(255), nullable=False)
    observacao: Mapped[str] = mapped_column(String(255), nullable=True)

Exame (Medical Exam)

Medical test results and records:

@table_registry.mapped_as_dataclass
class Exame:
    __tablename__ = 'exames'

    id: Mapped[int] = mapped_column(primary_key=True, init=False)
    paciente_id: Mapped[int] = mapped_column(ForeignKey('paciente_users.id'), nullable=False)
    prontuario_id: Mapped[int] = mapped_column(ForeignKey('prontuarios.id'))
    data: Mapped[datetime] = mapped_column(Date)
    tipo: Mapped[TipoExame] = mapped_column(Enum(TipoExame), nullable=False)
    resultado: Mapped[str] = mapped_column(String(255), nullable=True)
    observacao: Mapped[str] = mapped_column(String(255), nullable=True)
    status: Mapped[StatusExame] = mapped_column(Enum(StatusExame), nullable=False)

Prontuario (Medical Record)

Central medical record aggregating all patient data:

@table_registry.mapped_as_dataclass
class Prontuario:
    __tablename__ = 'prontuarios'

    id: Mapped[int] = mapped_column(primary_key=True, init=False)
    paciente_id: Mapped[int] = mapped_column(ForeignKey('paciente_users.id'), nullable=False)
    lista_consultas: Mapped[list['Consulta']] = relationship(
        init=False, 
        cascade='all, delete-orphan',
        lazy='selectin',
    )
    lista_prescricoes: Mapped[list['Prescricao']] = relationship(
        init=False,
        cascade='all, delete-orphan',
        lazy='selectin',
    ) 
    lista_exames: Mapped[list['Exame']] = relationship(
        init=False,
        cascade='all, delete-orphan',
        lazy='selectin',
    )

The Prontuario model uses cascade delete, meaning all related consultations, prescriptions, and exams are deleted when a medical record is removed.

Facility Models

Leito (Hospital Bed)

Hospital bed management:

@table_registry.mapped_as_dataclass
class Leito:
    __tablename__ = 'leitos'

    id: Mapped[int] = mapped_column(primary_key=True, init=False)
    numero_leito: Mapped[str] = mapped_column(String(10), nullable=False)
    paciente_id: Mapped[int] = mapped_column(ForeignKey('paciente_users.id'), nullable=True)
    tipo: Mapped[TipoLeito] = mapped_column(Enum(TipoLeito), nullable=False)
    status: Mapped[StatusLeito] = mapped_column(Enum(StatusLeito), nullable=False)

Estoque (Inventory)

Medical supplies and equipment inventory:

@table_registry.mapped_as_dataclass
class Estoque:
    __tablename__ = 'estoques'

    id: Mapped[int] = mapped_column(primary_key=True, init=False)
    tipo_item: Mapped[TipoItem] = mapped_column(Enum(TipoItem), nullable=False)
    nome: Mapped[str] = mapped_column(String(255), nullable=False)
    quantidade: Mapped[int] = mapped_column(Integer, nullable=False)
    unidade: Mapped[str] = mapped_column(String(50), nullable=False)
    data_validade: Mapped[date] = mapped_column(Date, nullable=True)

Item Types

class TipoItem(Enum):
    MEDICAMENTO = 'Medicamento'
    INSUMO = 'Insumo'
    EQUIPAMENTO = 'Equipamento'

Entity Relationships

Database Sessions

VidaPlus uses dependency injection for database sessions:

from typing import Annotated
from fastapi import Depends
from sqlalchemy.orm import Session
from vidaplus.database import get_session

Session = Annotated[Session, Depends(get_session)]

@router.post('/')
def create_resource(session: Session):
    # session is automatically provided and managed
    pass

Never create sessions manually with Session(engine). Always use the get_session dependency to ensure proper connection management.

Querying Patterns

Simple Query

from sqlalchemy import select

user = session.scalar(
    select(BaseUser).where(BaseUser.email == email)
)

Query with Filters and Pagination

pacientes = session.scalars(
    select(PacienteUser)
    .where(PacienteUser.tipo == UserRole.PACIENTE)
    .offset(filter_users.offset)
    .limit(filter_users.limit)
).all()

Checking Existence

db_user = session.scalar(
    select(PacienteUser).where(
        (PacienteUser.email == user.email) | (PacienteUser.cpf == user.cpf)
    )
)

if db_user:
    raise HTTPException(status_code=HTTPStatus.CONFLICT, detail='User already exists')

Get by Primary Key

user = session.get(PacienteUser, user_id)
if not user:
    raise HTTPException(status_code=HTTPStatus.NOT_FOUND, detail='User not found')

Creating Records

db_user = PacienteUser(
    nome=user.nome,
    email=user.email,
    senha=hashed_password,
    telefone=user.telefone,
    cpf=user.cpf,
    data_nascimento=user.data_nascimento,
    endereco=user.endereco,
    complemento=user.complemento,
    numero=user.numero,
    bairro=user.bairro,
    cidade=user.cidade,
    estado=user.estado,
    cep=user.cep,
    tipo=user.tipo,
    is_active=user.is_active,
    is_superuser=user.is_superuser,
)

session.add(db_user)
session.commit()
session.refresh(db_user)  # Reload to get generated fields

Updating Records

user_to_update = session.get(PacienteUser, user_id)
if not user_to_update:
    raise HTTPException(status_code=HTTPStatus.NOT_FOUND, detail='User not found')

user_to_update.nome = user.nome
user_to_update.email = user.email
user_to_update.telefone = user.telefone

session.commit()
session.refresh(user_to_update)

Deleting Records

user_to_delete = session.get(PacienteUser, user_id)
if not user_to_delete:
    raise HTTPException(status_code=HTTPStatus.NOT_FOUND, detail='User not found')

session.delete(user_to_delete)
session.commit()

Best Practices

Use Indexes

Critical fields like email, cpf, and crmCoren are indexed for fast lookups.

Cascade Carefully

The Prontuario model uses cascade deletes. Understand implications before deleting.

Validate Constraints

Use unique constraints on email, CPF, and CRM/COREN to prevent duplicates.

Handle Integrity Errors

Catch IntegrityError exceptions for constraint violations.

Next Steps

Error Handling

Learn how to handle database errors

API Endpoints

Explore API endpoints for each model

Get Started

Core Concepts

Guides

​Overview

​Database Configuration

​Core Models

​User Model Hierarchy

​AdminUser

​PacienteUser

​ProfissionalUser

​Available Specialties

​Available Schedules

​Healthcare Models

​Consulta (Appointment)

​Consultation Status

​Consultation Type

​Prescricao (Prescription)

​Exame (Medical Exam)

​Prontuario (Medical Record)

​Facility Models

​Leito (Hospital Bed)

​Estoque (Inventory)

​Item Types

​Entity Relationships

​Database Sessions

​Querying Patterns

​Simple Query

​Query with Filters and Pagination

​Checking Existence

​Get by Primary Key

​Creating Records

​Updating Records

​Deleting Records

​Best Practices

Use Indexes

Cascade Carefully

Validate Constraints

Handle Integrity Errors

​Next Steps

Error Handling

API Endpoints

Build docs developers (and LLMs) love

Overview

Database Configuration

Core Models

User Model Hierarchy

AdminUser

PacienteUser

ProfissionalUser

Available Specialties

Available Schedules

Healthcare Models

Consulta (Appointment)

Consultation Status

Consultation Type

Prescricao (Prescription)

Exame (Medical Exam)

Prontuario (Medical Record)

Facility Models

Leito (Hospital Bed)

Estoque (Inventory)

Item Types

Entity Relationships

Database Sessions

Querying Patterns

Simple Query

Query with Filters and Pagination

Checking Existence

Get by Primary Key

Creating Records

Updating Records

Deleting Records

Best Practices

Next Steps