Database Setup - Med Agenda

Overview

Med Agenda uses PostgreSQL as its primary database. The application supports both self-hosted PostgreSQL instances and managed database services like Neon.tech.

Database Requirements

PostgreSQL Version: 12 or higher (15+ recommended)
Extensions: None required (uses standard PostgreSQL features)
Encoding: UTF-8
Timezone: UTC recommended

Database Schema

Med Agenda uses JPA/Hibernate for automatic schema management. The application manages the following main entities:

Core Tables

Patients

Patient records with medical history and personal information

Doctors

Medical professionals with specialties and consultation rates

Consultations

Appointment scheduling and management

Diagnosis

Medical diagnoses linked to consultations with CID codes

Additional Tables

Admin: Administrative users
Payment: Payment transactions
News: System announcements
CID: International Classification of Diseases codes
GuiaDefinicao: Medical guidelines
HabilidadeMedica: Medical skills and specialties

Installation Methods

Neon.tech (Recommended)
Local PostgreSQL
Cloud Providers

Using Neon.tech Managed PostgreSQL

Neon.tech provides serverless PostgreSQL with automatic scaling and branching.

Create a Neon account

Create a new project

Click “New Project”
Choose a region close to your deployment
Select PostgreSQL version (15+ recommended)

Get connection string

Copy the connection string from your project dashboard:

postgresql://user:[email protected]/dbname?sslmode=require

Configure Med Agenda

Set the connection string in your environment:

export SPRING_DATASOURCE_URL="postgresql://user:[email protected]/dbname?sslmode=require"

Neon.tech includes SSL by default. Always use sslmode=require in the connection string.

Installing PostgreSQL Locally

# Install PostgreSQL
brew install postgresql@15

# Start PostgreSQL service
brew services start postgresql@15

# Create database
createdb medagenda

Using Cloud PostgreSQL Services

AWS RDS

Create RDS PostgreSQL instance
Configure security group to allow connections
Use endpoint as host in connection string:

jdbc:postgresql://your-instance.region.rds.amazonaws.com:5432/medagenda

Google Cloud SQL

Create Cloud SQL PostgreSQL instance
Configure authorized networks or use Cloud SQL Proxy
Use connection string:

jdbc:postgresql://instance-ip:5432/medagenda

Azure Database for PostgreSQL

Create Azure Database for PostgreSQL
Configure firewall rules
Use connection string with SSL:

jdbc:postgresql://server.postgres.database.azure.com:5432/medagenda?sslmode=require

DigitalOcean Managed Database

Create Managed PostgreSQL cluster
Download CA certificate if required
Use provided connection string

Connection Configuration

Connection String Format

The JDBC connection string follows this format:

jdbc:postgresql://[host]:[port]/[database]?[parameters]

Common Connection Parameters

sslmode

string

default:"disable"

SSL connection mode. Use require for production.Options: disable, allow, prefer, require, verify-ca, verify-full

currentSchema

string

default:"public"

Default schema to use

connectTimeout

integer

default:"10"

Connection timeout in seconds

socketTimeout

integer

default:"0"

Socket timeout in seconds (0 = infinite)

Example Connection Strings

jdbc:postgresql://localhost:5432/medagenda?sslmode=disable

Schema Management

Med Agenda uses Hibernate for automatic schema management.

DDL Auto Modes

Configure schema behavior with JPA_DDL_AUTO environment variable:

update (Default)
validate
create
none

export JPA_DDL_AUTO=update

Automatically updates schema based on entities
Adds new tables and columns
Does not remove columns or tables
Recommended for development

Use with caution in production. May cause unintended schema changes.

export JPA_DDL_AUTO=validate

Validates schema matches entities
Does not modify database
Throws exception if schema doesn’t match
Recommended for production

export JPA_DDL_AUTO=create

Drops and recreates schema on startup
Deletes all data
Only use for testing

Never use in production! All data will be lost.

export JPA_DDL_AUTO=none

No automatic schema management
Requires manual schema creation
Use with migration tools

Connection Pool Configuration

Med Agenda uses HikariCP for connection pooling.

Pool Settings

.env

# Maximum number of connections in pool
DB_POOL_MAX=10

# Minimum idle connections
DB_POOL_MIN_IDLE=5

# Connection timeout (ms)
DB_POOL_CONNECTION_TIMEOUT=30000

# Max lifetime of connection (ms)
DB_POOL_MAX_LIFETIME=1800000

Recommended Pool Sizes

Environment	Max Pool Size	Notes
Development	5-10	Sufficient for local testing
Staging	10-20	Moderate concurrent users
Production	20-50	Based on expected load
High Traffic	50-100	Monitor and adjust

Start with smaller pool sizes and increase based on monitoring. Too many connections can overload the database.

Database Initialization

First Time Setup

When running Med Agenda for the first time:

Create empty database

CREATE DATABASE medagenda;

Configure connection

Set environment variables with database credentials

Start application

The application will automatically create tables on first run (if JPA_DDL_AUTO=update)

Verify schema

\dt -- List all tables

You should see tables for: admin, patient, doctor, consultation, diagnosis, etc.

Seed Data (Optional)

To add initial data, connect to your database and run:

-- Create admin user (password should be hashed by application)
INSERT INTO admin (user_id, name, email, password, creation_timestamp, updated_timestamp)
VALUES (
  gen_random_uuid(),
  'System Admin',
  '[email protected]',
  '$2a$10$...', -- Use BCrypt hashed password
  NOW(),
  NOW()
);

Never store plain-text passwords. Use the application’s authentication endpoints to create users.

Backup and Recovery

Backup Database

# Full database backup
pg_dump -U postgres medagenda > backup.sql

# Compressed backup
pg_dump -U postgres medagenda | gzip > backup.sql.gz

# Backup to custom format (recommended)
pg_dump -U postgres -Fc medagenda > backup.dump

Restore Database

# Drop and recreate database
dropdb medagenda
createdb medagenda

# Restore from backup
psql -U postgres medagenda < backup.sql

Automated Backups

Set up automated backups with cron:

# Add to crontab (daily backup at 2 AM)
0 2 * * * pg_dump -U postgres medagenda | gzip > /backups/medagenda-$(date +\%Y\%m\%d).sql.gz

# Keep only last 7 days
0 3 * * * find /backups -name "medagenda-*.sql.gz" -mtime +7 -delete

Monitoring and Maintenance

Check Database Size

SELECT 
  pg_size_pretty(pg_database_size('medagenda')) as database_size;

Table Sizes

SELECT 
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Active Connections

SELECT 
  count(*) as connection_count,
  state
FROM pg_stat_activity
WHERE datname = 'medagenda'
GROUP BY state;

Vacuum and Analyze

Regularly maintain database performance:

-- Analyze tables for query optimization
ANALYZE;

-- Vacuum to reclaim storage
VACUUM ANALYZE;

Troubleshooting

Connection refused

Check if PostgreSQL is running:

# Local
pg_isready -h localhost -p 5432

# Docker
docker ps | grep postgres

Verify firewall allows connections on port 5432.

Authentication failed

Verify credentials are correct:

psql -h localhost -U your_user -d medagenda

Check pg_hba.conf authentication settings.

Schema validation errors

If using JPA_DDL_AUTO=validate and getting errors:

# Temporarily switch to update to fix schema
export JPA_DDL_AUTO=update

# Or manually update schema to match entities

Too many connections

Reduce connection pool size:

export DB_POOL_MAX=10

Or increase PostgreSQL max_connections in postgresql.conf.

Slow queries

Enable query logging:

export JPA_SHOW_SQL=true

Check and optimize slow queries. Add indexes where needed.

Next Steps

Environment Variables

Configure database connection strings

API Reference

Explore the Med Agenda API

Overview

User Guides

Core Features

Deployment

Development

​Overview

​Database Requirements

​Database Schema

​Core Tables

Patients

Doctors

Consultations

Diagnosis

​Additional Tables

​Installation Methods

​Using Neon.tech Managed PostgreSQL

​Installing PostgreSQL Locally

​Using Cloud PostgreSQL Services

​Connection Configuration

​Connection String Format

​Common Connection Parameters

​Example Connection Strings

​Schema Management

​DDL Auto Modes

​Connection Pool Configuration

​Pool Settings

​Recommended Pool Sizes

​Database Initialization

​First Time Setup

​Seed Data (Optional)

​Backup and Recovery

​Backup Database

​Restore Database

​Automated Backups

​Monitoring and Maintenance

​Check Database Size

​Table Sizes

​Active Connections

​Vacuum and Analyze

​Troubleshooting

​Next Steps

Environment Variables

API Reference

Build docs developers (and LLMs) love

Overview

Database Requirements

Database Schema

Core Tables

Additional Tables

Installation Methods

Using Neon.tech Managed PostgreSQL

Installing PostgreSQL Locally

Using Cloud PostgreSQL Services

Connection Configuration

Connection String Format

Common Connection Parameters

Example Connection Strings

Schema Management

DDL Auto Modes

Connection Pool Configuration

Pool Settings

Recommended Pool Sizes

Database Initialization

First Time Setup

Seed Data (Optional)

Backup and Recovery

Backup Database

Restore Database

Automated Backups

Monitoring and Maintenance

Check Database Size

Table Sizes

Active Connections

Vacuum and Analyze

Troubleshooting

Next Steps