Database Setup

DentControl uses Laravel’s migration system to manage database schema. This guide covers database configuration, running migrations, and understanding the database structure.

Database Options

DentControl supports multiple database systems out of the box.

Supported Databases

SQLite

Default choiceSingle file database, perfect for development and small deployments. No server required.

MySQL

Production readyPopular relational database with excellent performance and wide hosting support.

PostgreSQL

Advanced featuresRobust database with advanced features and strong data integrity.

SQLite Setup (Default)

SQLite is the default database and requires minimal configuration.

Configure .env

Set the database connection in .env:

DB_CONNECTION=sqlite

Create database file

Create the SQLite database file:

touch database/database.sqlite

Set permissions

Ensure the database file is writable:

chmod 664 database/database.sqlite
chmod 775 database/

SQLite stores the entire database in a single file at database/database.sqlite.

MySQL Setup

Create database

Create a MySQL database:

CREATE DATABASE dentcontrol CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'dentcontrol'@'localhost' IDENTIFIED BY 'secure_password';
GRANT ALL PRIVILEGES ON dentcontrol.* TO 'dentcontrol'@'localhost';
FLUSH PRIVILEGES;

Configure .env

Update your .env file:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=dentcontrol
DB_USERNAME=dentcontrol
DB_PASSWORD=secure_password

Test connection

Test the database connection:

php artisan db:show

PostgreSQL Setup

Create database

Create a PostgreSQL database:

CREATE DATABASE dentcontrol;
CREATE USER dentcontrol WITH PASSWORD 'secure_password';
GRANT ALL PRIVILEGES ON DATABASE dentcontrol TO dentcontrol;

Configure .env

Update your .env file:

DB_CONNECTION=pgsql
DB_HOST=127.0.0.1
DB_PORT=5432
DB_DATABASE=dentcontrol
DB_USERNAME=dentcontrol
DB_PASSWORD=secure_password

Test connection

Verify the connection:

php artisan db:show

Running Migrations

Migrations create and modify database tables. DentControl includes all necessary migrations.

Initial Migration

Run migrations for the first time:

php artisan migrate

On production servers, Laravel requires the --force flag to run migrations:

php artisan migrate --force

Migration Commands

Command	Description
`php artisan migrate`	Run pending migrations
`php artisan migrate:status`	Show migration status
`php artisan migrate:rollback`	Rollback last batch
`php artisan migrate:reset`	Rollback all migrations
`php artisan migrate:refresh`	Reset and re-run all migrations
`php artisan migrate:fresh`	Drop all tables and re-migrate

Use migrate:fresh with caution - it deletes all data!

Migration File Structure

DentControl includes the following migrations (in execution order):

Laravel System Tables

System Migrations

0001_01_01_000000_create_users_table.php       # Default Laravel users (not used)
0001_01_01_000001_create_cache_table.php       # Cache storage
0001_01_01_000002_create_jobs_table.php        # Queue jobs and batches

DentControl Application Tables

Clinica (Clinic)

File: 2026_02_23_084014_create_clinica_table.phpThe root table for multi-clinic support. Stores clinic information, address, and status.

Key Fields

- id_clinica (primary key)
- nombre (clinic name)
- rfc (tax ID, unique)
- Address fields (calle, ciudad, estado, etc.)
- telefono
- logo_ruta
- estatus (activo/baja)

Catalogo Servicios (Service Catalog)

File: 2026_02_23_085029_create_catalogo_servicios_table.phpServices offered by each clinic (e.g., “Limpieza”, “Ortodoncia”).

Key Fields

- id_cat_servicio (primary key)
- id_clinica (foreign key)
- nombre (service name, unique per clinic)
- descripcion
- duracion (duration in minutes)
- precio_sugerido
- estatus

Catalogo Tratamientos (Treatment Catalog)

File: 2026_02_23_085812_create_catalogo_tratamientos_table.phpTreatment types offered by clinics.

Paciente (Patient)

File: 2026_02_23_090302_create_paciente_table.phpPatient information and medical records.

Key Fields

- id_paciente (primary key)
- id_clinica (foreign key)
- Personal info (nombre, apellidos, fecha_nacimiento, sexo)
- curp (unique national ID)
- telefono, ocupacion, peso
- Address fields
- estatus

Usuario (User)

File: 2026_02_23_090927_create_usuario_table.phpSystem users (dentists, assistants, admins).

Key Fields

- id_usuario (primary key)
- id_clinica (foreign key)
- Personal info (nombre, apellidos)
- cedula_profesional (professional license, unique)
- nom_usuario (username, unique)
- password (hashed)
- rol (superadmin, dentista, asistente)
- estatus
- remember_token

Expediente Clinico (Clinical Record)

File: 2026_02_23_092325_create_expediente_clinico_table.phpPatient medical history and clinical information.

Tratamiento (Treatment)

File: 2026_02_23_092642_create_tratamiento_table.phpActive treatments for patients.

Key Fields

- id_tratamiento (primary key)
- id_paciente, id_usuario, id_clinica (foreign keys)
- id_cat_tratamientos (treatment type)
- diagnostico_inicial
- precio_final
- fecha_inicio, fecha_fin
- estatus (curso, finalizado, pausado)

Notas Evolucion (Clinical Notes)

File: 2026_02_23_093121_create_notas_evolucion_table.phpProgress notes for treatments.

Acceso Movil (Mobile Access)

File: 2026_02_23_093639_create_acceso_movil_table.phpMobile app access tokens.

Citas (Appointments)

File: 2026_03_01_231623_create_citas_table.phpPatient appointments with dentists.

Key Fields

- id_cita (primary key)
- id_paciente, id_usuario, id_clinica (foreign keys)
- id_cat_servicio (service type)
- id_tratamiento (optional, linked treatment)
- fecha (date)
- hora (time)
- motivo_consulta
- Unique constraint: (id_usuario, fecha, hora)

Database Schema Overview

Here’s how the main tables relate to each other:

Key Relationships

Clinic-centric: All major entities belong to a clinic (id_clinica)
User assignments: Users (dentists/assistants) are assigned to one clinic
Patient records: Patients belong to a clinic and have multiple appointments
Appointments: Link patients, users (dentists), services, and optionally treatments
Treatments: Ongoing treatment plans with clinical notes
Cascade deletes: Most tables use onDelete('cascade') for referential integrity

Seeding Data

Seeders populate the database with initial or test data.

Running Seeders

php artisan db:seed

Creating Custom Seeders

Create a seeder for initial data:

php artisan make:seeder ClinicaSeeder

Database Maintenance

Backup Database

cp database/database.sqlite database/backups/backup-$(date +%Y%m%d).sqlite

Restore Database

cp database/backups/backup-20260305.sqlite database/database.sqlite

Database Optimization

Optimize Database

# Clear old cache entries
php artisan cache:clear

# Clear old job records
php artisan queue:flush

# Optimize database (MySQL)
php artisan db:optimize

Database Inspection

Using Artisan Commands

php artisan db:show

Using Tinker

Interact with your database using Laravel Tinker:

php artisan tinker

Troubleshooting

Migration fails - table already exists

This means migrations were partially run. Options:

Check migration status:
```
php artisan migrate:status
```

Rollback and retry:

php artisan migrate:rollback
php artisan migrate

Fresh start (destroys data!):
```
php artisan migrate:fresh
```

SQLite database locked

SQLite locks the entire database during writes.Solutions:

Close any database browser tools
Stop any running queue workers
Check file permissions:
```
chmod 664 database/database.sqlite
```
For production, consider MySQL or PostgreSQL

Foreign key constraint fails

This occurs when trying to insert data without required parent records.Example: Creating a user without a clinic:

// This will fail:
Usuario::create(['id_clinica' => 999, ...]);

// First create the clinic:
$clinica = Clinica::create(['nombre' => 'My Clinic', ...]);

// Then create the user:
Usuario::create(['id_clinica' => $clinica->id_clinica, ...]);

Order matters! Create parent records first:

Clinica
CatalogoServicios, CatalogoTratamientos
Usuario, Paciente
ExpedienteClinico, Tratamiento
Citas, NotasEvolucion

Migration timeout on large databases

For large migrations, increase timeout:

// In migration file
public function up(): void
{
    DB::statement('SET SESSION max_execution_time = 600;');
    Schema::create('large_table', function (Blueprint $table) {
        // ...
    });
}

Or run migrations with higher limits:

php -d max_execution_time=600 artisan migrate

Cannot connect to database

Check these common issues:

Database server running?

# MySQL
sudo systemctl status mysql

# PostgreSQL
sudo systemctl status postgresql

Credentials correct?
- Verify .env settings
- Test with database client

Firewall blocking?

# Check MySQL port
sudo netstat -tlnp | grep 3306

# Check PostgreSQL port  
sudo netstat -tlnp | grep 5432

Clear config cache:
```
php artisan config:clear
```

Character encoding issues

Ensure UTF-8 encoding for proper Spanish character support:MySQL:

ALTER DATABASE dentcontrol CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

PostgreSQL:

-- Create database with UTF-8
CREATE DATABASE dentcontrol ENCODING 'UTF8';

SQLite: UTF-8 by default

Best Practices

Always backup before migrations: Especially on production
Test migrations locally first: Run on dev environment before production
Use transactions: Migrations automatically wrap operations in transactions
Version control migrations: Never modify existing migrations, create new ones
Document custom migrations: Add comments explaining complex schema changes
Regular backups: Automate daily database backups
Monitor database size: Watch SQLite file size, consider MySQL/PostgreSQL for growth
Use seeders for initial data: Keep test data consistent across environments

Next Steps

Configuration

Configure database connection settings

User Management

Create users and manage access

Deployment

Deploy your database to production

Get Started

Core Features

User Roles

Guides

Database Setup

Database Setup

Database Options

Supported Databases

SQLite

MySQL

PostgreSQL

SQLite Setup (Default)

MySQL Setup

PostgreSQL Setup

Running Migrations

Initial Migration

Migration Commands

Migration File Structure

Laravel System Tables

DentControl Application Tables

Database Schema Overview

Key Relationships

Seeding Data

Running Seeders

Creating Custom Seeders

Database Maintenance

Backup Database

Restore Database

Database Optimization

Database Inspection

Using Artisan Commands

Using Tinker

Troubleshooting

Best Practices

Next Steps

Configuration

User Management

Deployment

Build docs developers (and LLMs) love

Get Started

Core Features

User Roles

Guides

​Database Setup

​Database Options

​Supported Databases

SQLite

MySQL

PostgreSQL

​SQLite Setup (Default)

​MySQL Setup

​PostgreSQL Setup

​Running Migrations

​Initial Migration

​Migration Commands

​Migration File Structure

​Laravel System Tables

​DentControl Application Tables

​Database Schema Overview

​Key Relationships

​Seeding Data

​Running Seeders

​Creating Custom Seeders

​Database Maintenance

​Backup Database

​Restore Database

​Database Optimization

​Database Inspection

​Using Artisan Commands

​Using Tinker

​Troubleshooting

​Best Practices

​Next Steps

Configuration

User Management

Deployment

Build docs developers (and LLMs) love

Database Setup

Database Options

Supported Databases

SQLite Setup (Default)

MySQL Setup

PostgreSQL Setup

Running Migrations

Initial Migration

Migration Commands

Migration File Structure

Laravel System Tables

DentControl Application Tables

Database Schema Overview

Key Relationships

Seeding Data

Running Seeders

Creating Custom Seeders

Database Maintenance

Backup Database

Restore Database

Database Optimization

Database Inspection

Using Artisan Commands

Using Tinker

Troubleshooting

Best Practices

Next Steps