Migrations

The CPR backend uses Laravel migrations to manage the database schema. There are 114 migration files that define the complete database structure.

Running Migrations

# Run all pending migrations
php artisan migrate

# Run with seeding
php artisan migrate --seed

# Rollback last batch
php artisan migrate:rollback

# Fresh migration (drops all tables and re-runs)
php artisan migrate:fresh

# Fresh migration with seeding
php artisan migrate:fresh --seed

# Check migration status
php artisan migrate:status

Schema Overview

Core Tables

Table	Purpose
users	System users (staff, doctors, admins)
branches	Clinic branch locations
branch_users	User-branch assignments (pivot)
roles	Spatie permission roles
permissions	Spatie permission definitions
model_has_roles	User-role assignments
model_has_permissions	Direct user permissions
role_has_permissions	Role-permission assignments
activity_log	Audit trail (Spatie Activity Log)

Patient Management

Table	Purpose
patients	Patient demographics, contact info, referral data
patient_visits	Visit records with status, branch, date, impression
patient_visit_plans	Treatment plans per visit
patient_visit_prescribed_medicines	Prescriptions per visit
complaints	Patient complaints per visit
medical_certificates	Generated medical certificates

Clinical Examinations

Table	Purpose
visual_acuities	Visual acuity measurements
refractions	Refraction exam results
tonometries	Intraocular pressure measurements
slit_lamps	Slit lamp examination findings
funduscopies	Fundus examination results
gonioscopies	Anterior chamber angle assessments
corneal_topographies	Corneal surface mapping
amsler_grids	Macular function tests
eoms	Extraocular muscle assessments
exophthalmometries	Eye protrusion measurements
lacrimal_irrigations	Tear duct assessments
radiologies	Radiology/imaging results
gross_examinations	Gross examination findings
indirect_ophthalmoscopies	Indirect ophthalmoscopy records

Billing & Transactions

Table	Purpose
transactions	Financial transactions
transaction_items	Line items per transaction
bill_items	Master list of billable items
bill_item_categories	Bill item categorization
branch_bill_items	Branch-specific bill items
patient_billing_transactions	Patient billing via queue tickets
payment_methods	Available payment methods
insurances	Insurance plans

Pharmacy & Inventory

Table	Purpose
pharmacy_items	Pharmaceutical products
pharmacy_item_categories	Drug categories
pharmacy_item_forms	Dosage forms (tablet, liquid, etc.)
stock_availables	Current stock levels
stock_available_categories	Stock categories
stock_available_units	Measurement units
stock_movements	Stock in/out tracking
purchase_orders	Orders to suppliers
purchase_order_details	PO line items
purchase_order_statuses	PO status types
deliveries	Delivery records
delivery_details	Delivery line items
suppliers	Supplier information
medicines	Medicine reference data

Queue System

Table	Purpose
services	Clinical services offered
branch_services	Services available per branch
branch_service_counters	Counter numbers per service
branch_service_users	Staff assigned to services
queue_tickets	Queue tickets with status tracking

Other

Table	Purpose
doctors	Doctor profiles
doctor_roles	Doctor specialization roles
surgery_schedules	Planned surgeries
surgery_locations	Operating rooms/locations
regions, provinces, municipalities, barangays, zipcodes	Philippine address lookup

Creating New Migrations

# Create a migration
php artisan make:migration create_example_table

# Create migration for an existing table
php artisan make:migration add_column_to_patients_table

Convention

Migration filenames follow the pattern: YYYY_MM_DD_HHMMSS_description.php

Example migration for a new clinical exam:

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('example_exams', function (Blueprint $table) {
            $table->id();
            $table->foreignId('patient_visit_id')->constrained()->cascadeOnDelete();
            // exam-specific columns
            $table->text('findings')->nullable();
            $table->string('eye')->nullable(); // OD, OS, OU
            $table->timestamps();
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('example_exams');
    }
};

Key Patterns Used

• Foreign keys use constrained()->cascadeOnDelete() for referential integrity
• Soft deletes are used on branches, doctors, medicines, bill_items, procedures, pharmacy_items
• JSON columns store flexible data (e.g., branches.settings, ophthalmoscopy shots)
• Enum casting is done at the model level via PHP enums, not in migrations
• Branch isolation is enforced via branch_id foreign key on most domain tables