Database Setup

Overview

ARCA uses PostgreSQL as its database and Prisma ORM for database management, migrations, and type-safe queries. This guide covers database setup, migrations, and maintenance.

Database Schema

The ARCA database includes the following main entities:

Usuários

Users with role-based access (Admin, Secretário, Supervisor, Estagiário)

Pacientes

Patient records with demographic and treatment information

Atendimentos

Appointment sessions with timestamps and observations

Lista de Espera

Waiting list for prospective patients

Documentos

User documents and discharge reports

Logs de Auditoria

Complete audit trail for compliance

Initial Database Setup

Create PostgreSQL Database

Create a new PostgreSQL database for ARCA:

# Connect to PostgreSQL
psql -U postgres

# Create database
CREATE DATABASE arca_db;

# Create user (optional)
CREATE USER arca_user WITH PASSWORD 'your_password';

# Grant privileges
GRANT ALL PRIVILEGES ON DATABASE arca_db TO arca_user;

Configure Database URL

Set the DATABASE_URL in your apps/backend/.env:

DATABASE_URL="postgresql://arca_user:arca_password@localhost:5432/arca_db"

For production, use connection pooling and SSL:

postgresql://user:pass@host:5432/db?schema=public&sslmode=require&connection_limit=10

Run Initial Migration

Navigate to the backend directory and run migrations:

cd apps/backend
npx prisma migrate dev

This creates all tables defined in the Prisma schema.

Seed the Database

Populate initial data (roles, lookup tables, default users):

npm run db:seed

The seed script creates:

Roles: ADMIN, SECRETARIO, SUPERVISOR, ESTAGIARIO
Gêneros: Masculino, Feminino, Não-binário, Prefiro não informar
Cores de Pele: Branca, Preta, Parda, Amarela, Indígena
Escolaridades: Fundamental to Pós-graduação
Status de Atendimento: Agendado, Em Andamento, Concluído, Cancelado, Faltou
Default Users: One user for each role

Prisma Schema

The database schema is defined in apps/backend/prisma/schema.prisma:

Key Models

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])
  // ... relations
}

model Paciente {
  id_Paciente          String   @id @default(uuid()) @db.Uuid
  nomeRegistro         String   @db.VarChar(150)
  nomeSocial           String?  @db.VarChar(100)
  dataNascimento       DateTime @db.Date
  dataInicioTratamento DateTime @db.Date
  // ... more fields
}

model Atendimento {
  id_Atendimento String   @id @default(uuid()) @db.Uuid
  dataHoraInicio DateTime @db.Timestamp()
  dataHoraFim    DateTime @db.Timestamp()
  observacoes    String   @db.Text
  // ... relations to Paciente, Usuario, StatusAtendimento
}

View the complete schema at apps/backend/prisma/schema.prisma

Database Migrations

Prisma uses migration files to track and version database schema changes.

Creating Migrations

Modify the Schema

Edit apps/backend/prisma/schema.prisma with your changes.

Generate Migration

Create a new migration:

cd apps/backend
npx prisma migrate dev --name describe_your_change

Example:

npx prisma migrate dev --name add_patient_status_field

This will:

Generate migration SQL in prisma/migrations/
Apply the migration to your database
Regenerate Prisma Client

Review Migration

Check the generated SQL in prisma/migrations/[timestamp]_[name]/migration.sql

Applying Migrations

# Apply all pending migrations
cd apps/backend
npx prisma migrate dev

Never use migrate dev in production. Always use migrate deploy.

Migration History

ARCA’s migration history:

20250814224132_init

Initial database schema with all core tables:

USUARIOS, ROLES
PACIENTE, LISTA_ESPERA
ATENDIMENTOS, STATUS_ATENDIMENTO
DOCUMENTOS_USUARIO, RELATORIOS_ALTA
LOGS_AUDITORIA
Lookup tables (GENERO, CORES_PELE, ESCOLARIDADES)

20250819212429_change_role_id

Changed Role ID from UUID to SmallInt for better performance

20250828012458_add_is_active_to_waitlist

Added isActive field to waiting list for soft deletes

20250902224108_is_active

Extended isActive functionality across entities

Database Management Tools

Prisma Studio

Prisma Studio provides a visual interface for your database:

cd apps/backend
npx prisma studio

Opens at http://localhost:5555 with features:

View and edit records
Filter and search data
Explore relationships
No SQL required

Prisma Studio is for development only. Do not expose it in production.

Prisma Client

Regenerate Prisma Client after schema changes:

cd apps/backend
npx prisma generate

This creates TypeScript types matching your database schema.

Database Introspection

Pull schema from existing database:

cd apps/backend
npx prisma db pull

This updates schema.prisma to match your database structure.

Seeding the Database

The seed script (apps/backend/prisma/seed.ts) populates initial data.

Default Users Created

Email: admin@arca.com
Password: Admin123!
Role: ADMIN

Change these default passwords immediately in production!

Running Seed Script

cd apps/backend
npm run db:seed

The script is idempotent - it checks for existing data and only inserts if missing.

Custom Seed Data

Edit apps/backend/prisma/seed.ts to add your own seed data:

import { PrismaClient } from '@prisma/client'
import * as bcrypt from 'bcrypt'

const prisma = new PrismaClient()

async function main() {
  // Add your custom seed logic here
  console.log('Seeding custom data...')
}

main()
  .catch((e) => {
    console.error(e)
    process.exit(1)
  })
  .finally(async () => {
    await prisma.$disconnect()
  })

Production Database Setup

Managed PostgreSQL Options

Supabase

Free tier available, automatic backups, connection pooling

Neon

Serverless PostgreSQL with branching

Railway

Easy setup with automatic SSL

AWS RDS

Enterprise-grade with full control

Production Configuration

Enable SSL

Most production databases require SSL:

DATABASE_URL="postgresql://user:pass@host:5432/db?sslmode=require"

Connection Pooling

Use connection pooling for better performance:

# PgBouncer URL example
DATABASE_URL="postgresql://user:pass@host:6543/db?pgbouncer=true&connection_limit=10"

Run Migrations

Apply migrations in production:

cd apps/backend
npx prisma migrate deploy

Verify Connection

Test the database connection:

cd apps/backend
npx prisma db execute --stdin <<< "SELECT 1;"

Backup Strategy

Automated Backups

Enable automated backups on your database provider:

Supabase: Automatic daily backups
Railway: Point-in-time recovery
AWS RDS: Configure automated snapshots

Manual Backup

Create manual database dumps:

# Backup
pg_dump -U username -h host -d arca_db -F c -f arca_backup.dump

# Restore
pg_restore -U username -h host -d arca_db -F c arca_backup.dump

Export Data

Export specific data using Prisma:

const patients = await prisma.paciente.findMany()
fs.writeFileSync('patients.json', JSON.stringify(patients, null, 2))

Troubleshooting

Migration Conflicts

If migrations are out of sync:

# Check migration status
npx prisma migrate status

# Mark migrations as applied (if already in database)
npx prisma migrate resolve --applied "migration_name"

# Mark migrations as rolled back
npx prisma migrate resolve --rolled-back "migration_name"

Connection Pool Exhausted

Increase connection limit or use connection pooling:

DATABASE_URL="postgresql://...?connection_limit=20"

Or use PgBouncer/Supabase pooler.

Schema Drift

When database doesn’t match schema:

# See the differences
npx prisma db pull

# Then either:
# 1. Create migration from schema changes
npx prisma migrate dev --name fix_drift

# 2. Or update schema from database
npx prisma db pull --force

Slow Queries

Add indexes for commonly queried fields:

model Paciente {
  id_Paciente String @id @default(uuid())
  email       String @unique // Already indexed
  nome        String
  
  @@index([nome]) // Add index
}

Then create migration:

npx prisma migrate dev --name add_indexes

Best Practices

Version Control

Commit all migration files to version control

Test Migrations

Test migrations on staging before production

Backup Before Migrating

Always backup production database before migrations

Use Transactions

Wrap multiple operations in Prisma transactions

Next Steps

Production Deployment

Deploy your application to production

API Reference

Explore the backend API endpoints

Get Started

Core Features

User Management

Deployment

Development

​Overview

​Database Schema

Usuários

Pacientes

Atendimentos

Lista de Espera

Documentos

Logs de Auditoria

​Initial Database Setup

​Prisma Schema

​Database Migrations

​Creating Migrations

​Applying Migrations

​Migration History

​Database Management Tools

​Prisma Studio

​Prisma Client

​Database Introspection

​Seeding the Database

​Default Users Created

​Running Seed Script

​Custom Seed Data

​Production Database Setup

​Managed PostgreSQL Options

Supabase

Neon

Railway

AWS RDS

​Production Configuration

​Backup Strategy

​Troubleshooting

​Best Practices

Version Control

Test Migrations

Backup Before Migrating

Use Transactions

​Next Steps

Production Deployment

API Reference

Build docs developers (and LLMs) love

Overview

Database Schema

Initial Database Setup

Prisma Schema

Database Migrations

Creating Migrations

Applying Migrations

Migration History

Database Management Tools

Prisma Studio

Prisma Client

Database Introspection

Seeding the Database

Default Users Created

Running Seed Script

Custom Seed Data

Production Database Setup

Managed PostgreSQL Options

Production Configuration

Backup Strategy

Troubleshooting

Best Practices

Next Steps