Database Schema

Overview

OdontologyApp uses MySQL as its primary database with a well-structured relational schema. The database is accessed through a connection pool and primarily uses stored procedures for data operations.

Database Connection

Connection Pool (src/lib/server/db.js:1-13):

import mysql from "mysql2/promise";
import { DB_HOST, DB_USER, DB_PASS, DB_NAME } from "$env/static/private";

export const pool = mysql.createPool({
  host: DB_HOST,
  user: DB_USER,
  password: DB_PASS,
  database: DB_NAME,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
});

Configuration

Connection limit: 10 concurrent connections
Queue limit: Unlimited (0 = no limit)
Wait for connections: true (requests wait for available connection)
Promise-based: Uses mysql2/promise for async/await support

Environment Variables

Required in .env:

DB_HOST=localhost
DB_USER=your_username
DB_PASS=your_password
DB_NAME=ontology_db

Core Tables

Branches (Sucursales)

Stores clinic branch/location information:

Column	Type	Constraints	Description
id	INT	PRIMARY KEY, AUTO_INCREMENT	Unique identifier
name	VARCHAR(100)	NOT NULL	Branch name
address	VARCHAR(255)		Physical address
icon	VARCHAR(50)		Icon/emoji for UI
status	ENUM	’active’, ‘inactive’	Branch status
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Creation timestamp

Example data:

INSERT INTO branches (name, address, icon, status) VALUES 
('Sucursal Central', 'Av. Providencia 1234, Santiago', '📍', 'active'),
('Sucursal Norte', 'Av. Recoleta 456, Santiago', '🏢', 'active');

Users

System users (administrators, doctors, secretaries):

Column	Type	Constraints	Description
id	INT	PRIMARY KEY, AUTO_INCREMENT	Unique identifier
name	VARCHAR(100)	NOT NULL	Full name
username	VARCHAR(50)	UNIQUE, NOT NULL	Login username
email	VARCHAR(100)	UNIQUE	Email address
password	VARCHAR(255)	NOT NULL	Bcrypt hashed password
role	ENUM	’admin’, ‘doctor’, ‘secretary’	User role
initials	VARCHAR(5)		User initials for UI
branch_id	INT	FOREIGN KEY → branches(id)	Assigned branch
status	ENUM	’active’, ‘inactive’	Account status
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Creation timestamp

Default users:

INSERT INTO users (name, username, email, password, role, initials, branch_id) VALUES 
-- admin / admin123
('Andrea Rojas', 'admin', '[email protected]', '$2b$10$mo1jWoA/zRY1wN6x6hsfgeAWNAOE7cLTXseMBiO3M4JSvhjvhImtC', 'admin', 'AR', 1),
-- doctor / doctor123
('Dr. Carlos Soto', 'doctor', '[email protected]', '$2b$10$aCdT80m.xivVAK0rWf.rD..SyWRRc6Xkdbvxnt46UC4Fxf6JpLxFO', 'doctor', 'CS', 2),
-- secretaria / secretaria123
('Lucía Mendoza', 'secretaria', '[email protected]', '$2b$10$Zs8LX/f2nidTFspYT6QQEOvYa9VI/CuAAmBWg10rcR1BdoP5G/BtO', 'secretary', 'LM', 1);

Doctors

Extended information for medical staff:

Column	Type	Constraints	Description
id	INT	PRIMARY KEY, AUTO_INCREMENT	Unique identifier
user_id	INT	UNIQUE, NOT NULL, FOREIGN KEY → users(id)	Associated user account
specialty	VARCHAR(100)		Medical specialty
license_number	VARCHAR(50)		Professional license number

Doctor_Branches

Many-to-many relationship between doctors and branches:

Column	Type	Constraints	Description
doctor_id	INT	PRIMARY KEY, FOREIGN KEY → doctors(id)	Doctor reference
branch_id	INT	PRIMARY KEY, FOREIGN KEY → branches(id)	Branch reference

Example:

INSERT INTO doctor_branches (doctor_id, branch_id) VALUES 
(1, 1), -- Doctor 1 works at Branch 1
(1, 2); -- Doctor 1 also works at Branch 2

Patients

Patient records:

Column	Type	Constraints	Description
id	INT	PRIMARY KEY, AUTO_INCREMENT	Unique identifier
medrecno	INT	NOT NULL	Medical record number
first_name	VARCHAR(100)	NOT NULL	First name
last_name	VARCHAR(100)	NOT NULL	Last name
cedula	VARCHAR(20)	UNIQUE	National ID number
birth_date	DATE		Date of birth
phone	VARCHAR(20)		Contact phone
email	VARCHAR(100)		Email address
sex	ENUM	’masculino’, ‘femenino’, ‘otro’	Gender
blood_group	VARCHAR(5)		Blood type
allergies	TEXT		Known allergies
branch_id	INT	FOREIGN KEY → branches(id)	Primary branch
status	ENUM	’active’, ‘inactive’, ‘on_hold’	Patient status
uuid	CHAR(36)	UNIQUE	UUID for public URLs
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Creation timestamp

Appointments (Citas)

Appointment scheduling:

Column	Type	Constraints	Description
id	INT	PRIMARY KEY, AUTO_INCREMENT	Unique identifier
patient_id	INT	NOT NULL, FOREIGN KEY → patients(id)	Patient reference
doctor_id	INT	NOT NULL, FOREIGN KEY → doctors(id)	Doctor reference
branch_id	INT	NOT NULL, FOREIGN KEY → branches(id)	Branch location
appointment_date	DATE	NOT NULL	Appointment date
appointment_time	TIME	NOT NULL	Appointment time
duration_minutes	INT	DEFAULT 30	Duration in minutes
status	ENUM	’scheduled’, ‘confirmed’, ‘waiting’, ‘completed’, ‘cancelled’	Appointment status
notes	TEXT		Additional notes
uuid	CHAR(36)	UNIQUE	UUID for reminders
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Creation timestamp

Medical_Records

Clinical history and consultations:

Column	Type	Constraints	Description
id	INT	PRIMARY KEY, AUTO_INCREMENT	Unique identifier
patient_id	INT	NOT NULL, FOREIGN KEY → patients(id)	Patient reference
doctor_id	INT	NOT NULL, FOREIGN KEY → doctors(id)	Attending doctor
record_date	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Consultation date
motif	TEXT		Chief complaint
diagnosis	TEXT		Medical diagnosis
treatment	TEXT		Treatment provided
future_plan	TEXT		Future treatment plan
next_visit	DATE		Next appointment date
observations	TEXT		Additional observations

Indications

Medical prescriptions and instructions:

Column	Type	Constraints	Description
id	INT	PRIMARY KEY, AUTO_INCREMENT	Unique identifier
patient_id	INT	NOT NULL, FOREIGN KEY → patients(id)	Patient reference
doctor_id	INT	NOT NULL, FOREIGN KEY → doctors(id)	Prescribing doctor
indication_date	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Date prescribed
description	TEXT	NOT NULL	Prescription details

Odontograms

Dental chart/diagram data:

Column	Type	Constraints	Description
id	INT	PRIMARY KEY, AUTO_INCREMENT	Unique identifier
patient_id	INT	NOT NULL, FOREIGN KEY → patients(id)	Patient reference
tooth_number	INT	NOT NULL	Tooth number (FDI notation)
state	ENUM	’healthy’, ‘caries’, ‘restored’, ‘extracted’, ‘crown’, ‘treatment’	Tooth condition
notes	TEXT		Additional notes
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP	Last update

Tooth numbering: Uses FDI World Dental Federation notation (11-18, 21-28, 31-38, 41-48)

Patient_Files

File attachments (X-rays, documents, etc.):

Column	Type	Constraints	Description
id	INT	PRIMARY KEY, AUTO_INCREMENT	Unique identifier
patient_id	INT	NOT NULL, FOREIGN KEY → patients(id)	Patient reference
name	VARCHAR(255)	NOT NULL	File name
file_type	VARCHAR(50)		MIME type
file_size	INT		Size in bytes
file_url	VARCHAR(255)	NOT NULL	Storage URL/path
upload_date	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Upload timestamp

Logs

System audit trail:

Column	Type	Constraints	Description
id	INT	PRIMARY KEY, AUTO_INCREMENT	Unique identifier
user_id	INT	FOREIGN KEY → users(id)	User who performed action
action	VARCHAR(50)	NOT NULL	Action type
module	VARCHAR(50)	NOT NULL	Module/feature
description	TEXT		Detailed description
ip_address	VARCHAR(45)		User IP address
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Action timestamp

Permission System Tables

Permissions

Catalog of all system permissions:

Column	Type	Constraints	Description
id	INT	PRIMARY KEY, AUTO_INCREMENT	Unique identifier
code	VARCHAR(60)	UNIQUE, NOT NULL	Permission code (e.g., VIEW_PATIENTS)
label	VARCHAR(100)	NOT NULL	Human-readable name
module	VARCHAR(60)	NOT NULL	Module grouping
description	TEXT		Detailed description
sort_order	INT	DEFAULT 0	Display order
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Creation timestamp

Example permissions:

INSERT INTO permissions (code, label, module, description, sort_order) VALUES
('VIEW_PATIENTS', 'Ver Pacientes', 'Pacientes', 'Acceder al listado de pacientes', 10),
('CREATE_PATIENTS', 'Crear Pacientes', 'Pacientes', 'Registrar un nuevo paciente', 11),
('EDIT_PATIENTS', 'Editar Pacientes', 'Pacientes', 'Modificar datos de un paciente', 12);

User_Permissions

Individual permission assignments:

Column	Type	Constraints	Description
id	INT	PRIMARY KEY, AUTO_INCREMENT	Unique identifier
user_id	INT	NOT NULL, FOREIGN KEY → users(id)	User reference
permission_id	INT	NOT NULL, FOREIGN KEY → permissions(id)	Permission reference
granted	TINYINT(1)	DEFAULT 1	1 = granted, 0 = revoked
granted_by	INT	FOREIGN KEY → users(id)	Admin who granted permission
granted_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Grant timestamp

Unique constraint: (user_id, permission_id)

Stored Procedures

Patient Procedures

Defined in database.sql and referenced in API endpoints: sp_list_patients(search_query)

Lists patients with optional search filter
Usage: CALL sp_list_patients('') or CALL sp_list_patients('John')

sp_create_patient(…)

Creates new patient record
Returns: patient_id
Usage in API: (src/routes/api/patients/+server.js:69)

const [rows] = await pool.query(
  "CALL sp_create_patient(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
  [first_name, last_name, cedula, birth_date, phone, email, sex, 
   blood_group, allergies, branch_id, patientUuid, locals.user.id]
);

sp_delete_patient(patient_id, user_id)

Soft delete or archive patient
Logs action in audit trail

Finance Procedures

Defined in sp_finance.sql: sp_get_patient_finance(patient_id)

Returns multiple result sets:
1. Budgets with doctor names
2. Budget items with service names
3. Payment history

sp_create_budget(patient_id, doctor_id, total_amount, discount, notes, items_json)

Creates budget with line items
Uses JSON parameter for items
Transactional (rollback on error)
Returns: budget_id

sp_register_payment(patient_id, budget_id, amount, payment_method, notes)

Records payment transaction
Returns: payment_id

sp_update_budget_status(budget_id, status)

Updates budget status (draft, approved, rejected)

Permission Procedures

Defined in migration_phase6_permissions.sql: sp_check_single_permission(user_id, permission_code)

Checks if user has specific permission
Returns: granted (0 or 1)
Used in: (src/lib/server/checkPermission.js:25)

sp_get_user_permissions_list(user_id)

Returns array of permission codes for user
Used for frontend permission checks

sp_get_all_permissions()

Returns complete permission catalog
Used for admin permission management

sp_upsert_user_permission(user_id, permission_code, granted, granted_by)

Assigns or revokes permission
Idempotent (insert or update)

Database Relationships

Branches
  ├── Users (branch_id)
  ├── Patients (branch_id)
  ├── Appointments (branch_id)
  └── Doctor_Branches (branch_id)

Users
  ├── Doctors (user_id) [1:1]
  ├── Medical_Records (doctor_id)
  ├── Indications (doctor_id)
  ├── Logs (user_id)
  └── User_Permissions (user_id)

Patients
  ├── Appointments (patient_id)
  ├── Medical_Records (patient_id)
  ├── Indications (patient_id)
  ├── Odontograms (patient_id)
  └── Patient_Files (patient_id)

Doctors
  ├── Appointments (doctor_id)
  ├── Medical_Records (doctor_id)
  └── Doctor_Branches (doctor_id) [Many-to-Many]

Permissions
  └── User_Permissions (permission_id)

Query Patterns

Using the Connection Pool

import { pool } from "$lib/server/db";

// Simple query
const [rows] = await pool.query("SELECT * FROM patients WHERE id = ?", [patientId]);

// Stored procedure
const [results] = await pool.query("CALL sp_list_patients(?)", [searchQuery]);
const patients = results[0]; // First result set

// Transaction
const connection = await pool.getConnection();
try {
  await connection.beginTransaction();
  await connection.query("INSERT INTO ...", [...]);
  await connection.query("UPDATE ...", [...]);
  await connection.commit();
} catch (error) {
  await connection.rollback();
  throw error;
} finally {
  connection.release();
}

Prepared Statements

All queries use prepared statements to prevent SQL injection:

// ✅ SAFE - Parameterized query
await pool.query("SELECT * FROM users WHERE username = ?", [username]);

// ❌ UNSAFE - String concatenation
await pool.query(`SELECT * FROM users WHERE username = '${username}'`);

Database Initialization

Create database: CREATE DATABASE IF NOT EXISTS ontology_db;
Run schema: mysql -u user -p ontology_db < database.sql
Run migrations: mysql -u user -p ontology_db < migration_phase6_permissions.sql
Run stored procedures: mysql -u user -p ontology_db < sp_finance.sql

See Installation Guide for complete setup instructions.

Architecture

Guides

Overview

Database Connection

Configuration

Environment Variables

Core Tables

Branches (Sucursales)

Users

Doctors

Doctor_Branches

Patients

Appointments (Citas)

Medical_Records

Indications

Odontograms

Patient_Files

Logs

Permission System Tables

Permissions

User_Permissions

Stored Procedures

Patient Procedures

Finance Procedures

Permission Procedures

Database Relationships

Query Patterns

Using the Connection Pool

Prepared Statements

Database Initialization

Build docs developers (and LLMs) love

Architecture

Guides

​Overview

​Database Connection

​Configuration

​Environment Variables

​Core Tables

​Branches (Sucursales)

​Users

​Doctors

​Doctor_Branches

​Patients

​Appointments (Citas)

​Medical_Records

​Indications

​Odontograms

​Patient_Files

​Logs

​Permission System Tables

​Permissions

​User_Permissions

​Stored Procedures

​Patient Procedures

​Finance Procedures

​Permission Procedures

​Database Relationships

​Query Patterns

​Using the Connection Pool

​Prepared Statements

​Database Initialization

Build docs developers (and LLMs) love

Overview

Database Connection

Configuration

Environment Variables

Core Tables

Branches (Sucursales)

Users

Doctors

Doctor_Branches

Patients

Appointments (Citas)

Medical_Records

Indications

Odontograms

Patient_Files

Logs

Permission System Tables

Permissions

User_Permissions

Stored Procedures

Patient Procedures

Finance Procedures

Permission Procedures

Database Relationships

Query Patterns

Using the Connection Pool

Prepared Statements

Database Initialization