Database Setup and Schema Migration for MELIKA

MELIKA’s persistence layer is a PostgreSQL database whose full schema — tables, indexes, foreign-key constraints, PL/pgSQL functions, and triggers — lives in a single SQL file at server/src/database/melika_db.sql. Applying this file to a fresh database is the only migration step required. This page walks through local setup, explains every table and trigger, and shows how to seed the first admin user so you can log in immediately after deployment.

Prerequisites

PostgreSQL ≥ 14

Install PostgreSQL locally via your OS package manager or use the Railway-managed Postgres service in production. The psql CLI must be available in your terminal.

Node.js ≥ 20

Required to run the admin-creation script in server/scripts/crearAdmin.js after the schema is applied.

Applying the schema

Create the database

Create a new, empty database named melika_db (or any name you prefer — just update DB_NAME accordingly):

createdb melika_db

If your PostgreSQL user is not the OS user, supply credentials explicitly:

createdb -U postgres melika_db

Apply the schema

Run the bundled SQL file against the new database. This creates all tables, sequences, constraints, functions, and triggers in the correct dependency order:

psql -d melika_db -f server/src/database/melika_db.sql

For remote databases (including Railway), supply the full connection string:

psql "postgresql://user:pass@host:5432/melika_db" \
  -f server/src/database/melika_db.sql

Verify the tables

Open a psql session and list all relations to confirm the schema was applied:

psql -d melika_db

\dt

You should see all tables listed. Exit with \q.

Schema overview

The schema models a complete digital health workflow — from user authentication and invitation management through to appointment booking, clinical history recording, and audit logging.

Table	Description
`usuarios`	Platform users (patients, doctors, admins). Stores hashed passwords, email-verification status, and role.
`codigos_verificacion`	Short-lived email verification codes sent to users on registration or password reset.
`tokens_invitacion`	Single-use invitation tokens that allow a new user to complete registration via a secure link.
`especialidades`	Medical specialities catalogue (e.g. Cardiología, Pediatría). Referenced by `medicos`.
`medicamentos`	Medication catalogue used when composing prescriptions inside clinical histories.
`medicos`	Doctor profiles linked to a `usuarios` record; stores specialty, biography, rating, and schedule metadata.
`franjas_horarias`	Individual bookable time slots belonging to a doctor, with a `disponible` flag managed by triggers.
`citas`	Appointment records linking a patient, a doctor, and a `franja_horaria`. Subject to audit triggers.
`historias_clinicas`	Clinical history entries attached to a `cita`; stores diagnosis, treatment, and prescribed medications.
`logs_citas`	Immutable audit log populated automatically by the `fn_auditar_citas` trigger on every change to `citas`.
`documentos_clinicos`	Clinical documents (medical histories, prescriptions, lab orders, external uploads) attached to a patient or clinical history.
`recetas_medicas`	Individual prescription lines (medication, dose, frequency, duration) linked to a clinical history.
`ordenes_examenes`	Lab and imaging orders issued within a clinical history.

Database triggers

MELIKA offloads three critical business rules to PostgreSQL triggers, guaranteeing correctness regardless of which application layer (API, admin script, direct SQL) performs the write.

fn_auditar_citas — audit trail for appointments

Fires AFTER INSERT, UPDATE, or DELETE on the citas table. Every mutation is recorded in logs_citas with the operation type, a timestamp, and a snapshot of the affected row. Because logs_citas is written inside the same transaction, the audit record is guaranteed to exist if and only if the original write succeeded.This table provides the immutable audit trail required for healthcare data compliance. Do not grant DELETE or UPDATE privileges on logs_citas to the application database user.

-- Trigger definition (excerpt from melika_db.sql)
CREATE TRIGGER trg_auditoria_citas
  AFTER INSERT OR UPDATE OR DELETE ON citas
  FOR EACH ROW EXECUTE FUNCTION fn_auditar_citas();

fn_verificar_disponibilidad_critica — concurrency-safe slot booking

Fires BEFORE INSERT on citas. It checks whether the target franja_horaria is still marked disponible = TRUE. If the slot has already been taken, the function raises a custom PostgreSQL exception:

45001 — the specified time slot does not exist in the records.
45002 — the slot is already booked (disponible = FALSE).

The application layer catches these specific SQLSTATE codes and returns a user-friendly conflict response instead of a generic 500 error. This prevents double-booking even under concurrent requests without requiring application-level locking.

-- Custom exceptions raised by this function
RAISE EXCEPTION 'ERR_FRANJA_INEXISTENTE: La franja horaria especificada no existe en los registros.'
  USING ERRCODE = '45001';

RAISE EXCEPTION 'ERR_FRANJA_OCUPADA: Conflicto de concurrencia. La franja horaria ya ha sido reservada.'
  USING ERRCODE = '45002';

fn_sincronizar_franja_horaria — automatic slot state management

Fires AFTER INSERT, UPDATE, or DELETE on citas and keeps the franjas_horarias.disponible flag in sync with actual appointment state:

INSERT — marks the booked slot as disponible = FALSE immediately.
UPDATE (state transitions to 'cancelada') — sets disponible = TRUE, making the time window bookable again without any additional API call.
DELETE — releases the slot by setting disponible = TRUE.

This keeps the disponible flag — the source of truth for scheduling — always consistent with actual appointment state, even if a change is performed directly in the database.

Creating the first admin user

The database starts empty — there are no users until you run the admin-creation script. This script inserts a fully verified admin account directly into the usuarios table, bypassing the email-verification flow. The admin email and an initial password are hardcoded in the script; you should change the password immediately after the first login.

Set database environment variables

Make sure all five DB_* variables (DB_HOST, DB_PORT, DB_NAME, DB_USER, DB_PASSWORD) are set in server/.env (local) or in the Railway service environment (production) before running the script.

Run the script

From the repository root:

node server/scripts/crearAdmin.js

The script does not prompt for input — it uses the hardcoded credentials defined inside crearAdmin.js. If an admin with the same email already exists, the script exits without creating a duplicate. On success it prints the new account’s email, role, and the initial plaintext password to the console.

Verify login and change the password

Start the server and log in with the admin credentials through the MELIKA frontend or via the API to confirm the account works correctly. Change the default password immediately after the first login.

On Railway, run this script using the Railway shell for the server service after the schema has been applied. Open the service in the Railway dashboard, click Shell, and execute node scripts/crearAdmin.js in the remote environment (the shell opens at the service root directory, /server).

Railway PostgreSQL

When you add a PostgreSQL service to your Railway project and link it to the server service, Railway exposes the following individual connection variables, which map directly to what server/src/config/db.js expects:

PGHOST      → set as DB_HOST
PGPORT      → set as DB_PORT
PGDATABASE  → set as DB_NAME
PGUSER      → set as DB_USER
PGPASSWORD  → set as DB_PASSWORD

Railway does not automatically set the DB_* variable names that MELIKA uses — you must either copy the values from the Railway Postgres service’s Connect tab and add them as DB_HOST, DB_PORT, DB_NAME, DB_USER, and DB_PASSWORD in the server service variables, or use Railway’s variable references to wire them through.

In the Railway dashboard, use variable references to avoid duplicating secrets. For example, set DB_HOST to ${{Postgres.PGHOST}} and Railway will substitute the real value at runtime.

Backup recommendations

Railway’s PostgreSQL service does not enable automated backups on the free tier. Set up regular backups before going to production with real patient data.

Use pg_dump to create a portable backup of the entire database:

# Full schema + data backup (custom format, compressed)
pg_dump --format=custom \
  "postgresql://user:pass@host:5432/melika_db" \
  -f melika_backup_$(date +%Y%m%d).dump

To restore from a backup:

pg_restore --format=custom \
  -d "postgresql://user:pass@host:5432/melika_db" \
  melika_backup_20240101.dump

For production deployments handling real patient data, schedule daily automated backups and store them in a separate cloud bucket (e.g. AWS S3, Google Cloud Storage). Verify restore procedures periodically — a backup you have never tested is not a backup.

Setup & Deploy

Database Setup and Schema Migration for MELIKA

Prerequisites

PostgreSQL ≥ 14

Node.js ≥ 20

Applying the schema

Schema overview

Database triggers

Creating the first admin user

Railway PostgreSQL

Backup recommendations

Build docs developers (and LLMs) love

Setup & Deploy

Documentation Index

​Prerequisites

PostgreSQL ≥ 14

Node.js ≥ 20

​Applying the schema

​Schema overview

​Database triggers

​Creating the first admin user

​Railway PostgreSQL

​Backup recommendations

Build docs developers (and LLMs) love

Prerequisites

Applying the schema

Schema overview

Database triggers

Creating the first admin user

Railway PostgreSQL

Backup recommendations