klinik-gunung-docs
Backend

Database Documentation

Comprehensive documentation of the Klinik Gunung Semeru healthcare management system database structure, including all tables, relationships, and migration details.

Database Documentation

This comprehensive guide covers the complete database structure of the Klinik Gunung Semeru healthcare management system. The system uses MySQL as the database engine with Laravel migrations for schema management.

Database Overview

The Klinik Gunung Semeru database is designed to manage a comprehensive healthcare facility with the following main modules:

Core Modules

  • User Management: Authentication and user profiles
  • Staff Management: Healthcare professionals and administrative staff
  • Patient Management: Patient registration and profiles
  • Health Screening: Pre-climb health assessments
  • Medical Services: Consultations and physical examinations
  • Pharmacy Management: Drug inventory and dispensing
  • Financial Management: Payments and billing
  • Medical Records: Patient history and documentation

Database Statistics

  • Total Tables: 32+ tables
  • Total Migrations: 32+ migration files
  • Primary Database Engine: MySQL 8.0+
  • Character Set: UTF8MB4 (Unicode support)
  • Relationships: Extensive foreign key relationships

Table Categories and Relationships

1. Authentication & User Management

2. Staff Management (Healthcare Professionals)

3. Patient Management

4. Health Screening System

5. Medical Services & Records

6. Pharmacy & Inventory Management

7. Financial & Payment System

8. System Administration

1. Authentication & User Management

Users Table

Purpose: Main user authentication and profile management table.

Migration: 0001_01_01_000000_create_users_table.php

Schema::create('users', function (Blueprint $table) {
    $table->id();
    $table->uuid('uuid')->unique();
    $table->string('username')->unique()->nullable();
    $table->string('name');
    $table->string('email');
    $table->enum('role', [
        'patient', 'admin', 'doctor', 'nurse',
        'cashier', 'front_office', 'miniapp'
    ])->default('patient');
    $table->timestamp('email_verified_at')->nullable();
    $table->string('avatar')->default('avatars/avatar.svg');
    $table->string('password');
    $table->string('provider')->nullable(); // OAuth provider
    $table->string('provider_id')->nullable();
    $table->string('provider_token')->nullable();
    $table->rememberToken();
    $table->timestamps();
});

Key Fields:

  • uuid: Unique identifier for API access
  • role: User role (patient, admin, doctor, nurse, cashier, front_office, miniapp)
  • provider*: OAuth authentication fields
  • avatar: Profile picture path

Relationships:

  • One-to-One: patients, admins, doctors, nurses, cashiers, front_offices
  • One-to-Many: sessions

Password Reset Tokens Table

Purpose: Manages password reset functionality.

Schema::create('password_reset_tokens', function (Blueprint $table) {
    $table->string('email')->primary();
    $table->string('token');
    $table->timestamp('created_at')->nullable();
});

Sessions Table

Purpose: Laravel session management for user authentication state.

Schema::create('sessions', function (Blueprint $table) {
    $table->string('id')->primary();
    $table->foreignId('user_id')->nullable()->index();
    $table->string('ip_address', 45)->nullable();
    $table->text('user_agent')->nullable();
    $table->longText('payload');
    $table->integer('last_activity')->index();
});

User Informations Table

Migration: 2025_10_02_062242_create_user_informations_table.php

Purpose: Extended user profile information.

Schema::create('user_informations', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->constrained()->onDelete('cascade');
    $table->string('phone')->nullable();
    $table->text('address')->nullable();
    $table->date('date_of_birth')->nullable();
    $table->string('emergency_contact')->nullable();
    $table->string('emergency_phone')->nullable();
    $table->timestamps();
});

Personal Access Tokens Table

Migration: 2025_09_29_155650_create_personal_access_tokens_table.php

Purpose: API token management for Laravel Sanctum.

Schema::create('personal_access_tokens', function (Blueprint $table) {
    $table->id();
    $table->morphs('tokenable');
    $table->string('name');
    $table->string('token', 64)->unique();
    $table->text('abilities')->nullable();
    $table->timestamp('last_used_at')->nullable();
    $table->timestamp('expires_at')->nullable();
    $table->timestamps();
});

2. Staff Management (Healthcare Professionals)

Admins Table

Migration: 2025_09_29_152115_create_admins_table.php

Purpose: Administrative staff management.

Schema::create('admins', function (Blueprint $table) {
    $table->id();
    $table->uuid('uuid')->unique();
    $table->foreignId('user_id')->constrained('users')->onDelete('cascade');
    $table->string('nik');
    $table->string('email');
    $table->string('name', 100);
    $table->string('address', 100);
    $table->date('date_of_birth');
    $table->string('phone', 15);
    $table->longText('signature')->nullable();
    $table->enum('role', ['admin'])->default('admin');
    $table->timestamps();
});

Doctors Table

Migration: 2025_09_29_150627_create_doctors_table.php

Purpose: Medical doctor profiles and credentials.

Schema::create('doctors', function (Blueprint $table) {
    $table->id();
    $table->uuid('uuid')->unique();
    $table->foreignId('user_id')->constrained('users')->onDelete('cascade');
    $table->string('nik');
    $table->string('email');
    $table->string('name', 100);
    $table->string('address', 100);
    $table->date('date_of_birth');
    $table->string('phone', 15);
    $table->string('specialization')->nullable();
    $table->string('license_number')->nullable();
    $table->longText('signature')->nullable();
    $table->enum('role', ['doctor'])->default('doctor');
    $table->timestamps();
});

Relationships:

  • One-to-Many: physical_examinations

Nurses Table

Migration: 2025_09_29_150614_create_nurses_table.php

Purpose: Nursing staff management.

Schema::create('nurses', function (Blueprint $table) {
    $table->id();
    $table->uuid('uuid')->unique();
    $table->foreignId('user_id')->constrained('users')->onDelete('cascade');
    $table->string('nik');
    $table->string('email');
    $table->string('name', 100);
    $table->string('address', 100);
    $table->date('date_of_birth');
    $table->string('phone', 15);
    $table->longText('signature')->nullable();
    $table->enum('role', ['nurse'])->default('nurse');
    $table->timestamps();
});

Relationships:

  • One-to-Many: physical_examinations

Cashiers Table

Migration: 2025_09_29_150621_create_cashiers_table.php

Purpose: Billing and payment staff management.

Schema::create('cashiers', function (Blueprint $table) {
    $table->id();
    $table->uuid('uuid')->unique();
    $table->foreignId('user_id')->constrained('users')->onDelete('cascade');
    $table->string('nik');
    $table->string('email');
    $table->string('name', 100);
    $table->string('address', 100);
    $table->date('date_of_birth');
    $table->string('phone', 15);
    $table->longText('signature')->nullable();
    $table->enum('role', ['cashier'])->default('cashier');
    $table->timestamps();
});

Relationships:

  • One-to-Many: payments

Front Offices Table

Migration: 2025_09_29_162646_create_front_offices_table.php

Purpose: Reception and administrative staff.

Schema::create('front_offices', function (Blueprint $table) {
    $table->id();
    $table->uuid('uuid')->unique();
    $table->foreignId('user_id')->constrained('users')->onDelete('cascade');
    $table->string('nik');
    $table->string('email');
    $table->string('name', 100);
    $table->string('address', 100);
    $table->date('date_of_birth');
    $table->string('phone', 15);
    $table->longText('signature')->nullable();
    $table->enum('role', ['front_office'])->default('front_office');
    $table->timestamps();
});

3. Patient Management

Patients Table

Migration: 2025_09_29_150559_create_patients_table.php

Purpose: Patient registration and demographic information for climbers undergoing health screening.

Schema::create('patients', function (Blueprint $table) {
    $table->id();
    $table->uuid('uuid')->unique();
    $table->unsignedBigInteger('user_id')->nullable();
    $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
    $table->string('nik', 16); // National ID number
    $table->string('name');
    $table->string('place_of_birth');
    $table->date('date_of_birth');
    $table->string('rt_rw'); // Address detail
    $table->string('address');
    $table->string('village');
    $table->string('district');
    $table->string('religion');
    $table->string('marital_status');
    $table->string('occupation');
    $table->string('nationality')->default('WNI');
    $table->enum('gender', ['laki-laki', 'perempuan']);
    $table->string('email'); 
    $table->string('contact'); // Phone number
    $table->string('blood_type');
    $table->integer('age');
    $table->decimal('tinggi_badan', 5, 2); // Height in cm
    $table->decimal('berat_badan', 5, 2); // Weight in kg
    $table->string('ktp_images')->nullable(); // ID card photo
    $table->timestamp('archived_at')->nullable();
    $table->timestamps();
        });

Key Fields:

  • nik: Indonesian National ID (16 digits)
  • tinggi_badan/berat_badan: Height/Weight for BMI calculation
  • ktp_images: Path to uploaded ID card photo
  • archived_at: Soft delete functionality

Relationships:

  • One-to-One: users
  • One-to-Many: screenings, physical_examinations, payments, consultations, medical_records

4. Health Screening System

Screenings Table

Migration: 2025_09_29_152529_create_screenings_table.php

Purpose: Main health screening sessions for mountain climbers.

Schema::create('screenings', function (Blueprint $table) {
    $table->id();
    $table->uuid('uuid')->unique();
    $table->unsignedBigInteger('patient_id');
    $table->foreign('patient_id')->references('id')->on('patients')->onDelete('cascade');
    $table->enum('screening_status', ['completed', 'pending', 'cancelled'])->default('pending');
    $table->enum('health_status', ['pending', 'sehat', 'tidak_sehat'])->default('pending');
    $table->enum('health_check_status', ['pending', 'completed'])->default('pending');
    $table->enum('payment_status', ['completed', 'pending', 'cancelled'])->default('pending');
    $table->boolean('konsultasi_dokter')->nullable();
    $table->boolean('konsultasi_dokter_status')->nullable();
    $table->enum('pendampingan', [
        'pendampingan_paramedis',
        'pendampingan_perawat',
        'pendampingan_dokter'
    ])->nullable();
    $table->integer('queue')->nullable();
    $table->date('screening_date')->nullable();
    $table->timestamps();
});

Key Fields:

  • health_status: Overall health assessment result
  • konsultasi_dokter: Doctor consultation required flag
  • pendampingan: Medical escort requirement level
  • queue: Queue number for processing

Screening Questions Table

Migration: 2025_09_29_153215_create_screening_questions_table.php

Purpose: Dynamic questionnaire system for health screenings.

Schema::create('screening_questions', function (Blueprint $table) {
    $table->id();
    $table->string('question_text');
    $table->enum('answer_type', [
        'text', 'number', 'date', 'textarea',
        'select', 'checkbox', 'checkbox_textarea'
    ]);
    $table->string('condition_value')->nullable();
    $table->boolean('requires_doctor')->default(false);
    $table->json('options')->nullable(); // For select/checkbox options
    $table->timestamps();
});

Key Features:

  • Flexible question types
  • Conditional logic support
  • Doctor consultation triggers

Screening Answers Table

Migration: 2025_09_29_153226_create_screening_answers_table.php

Purpose: Stores patient responses to screening questions.

Schema::create('screening_answers', function (Blueprint $table) {
    $table->id();
    $table->foreignId('screening_id')->constrained('screenings')->onDelete('cascade');
    $table->foreignId('question_id')->constrained('screening_questions')->onDelete('cascade');
    $table->text('answer')->nullable();
    $table->timestamps();
});

5. Medical Services & Records

Physical Examinations Table

Migration: 2025_09_29_153327_create_physical_examinations_table.php

Purpose: Comprehensive physical examination records.

Schema::create('physical_examinations', function (Blueprint $table) {
    $table->id();
    $table->uuid('uuid')->unique();
    $table->foreignId('patient_id')->constrained('patients')->cascadeOnDelete();
    $table->foreignId('nurse_id')->nullable()->constrained('nurses')->nullOnDelete();
    $table->foreignId('doctor_id')->nullable()->constrained('doctors')->nullOnDelete();
    $table->string('blood_pressure')->nullable();
    $table->integer('heart_rate')->nullable();
    $table->integer('oxygen_saturation')->nullable();
    $table->integer('respiratory_rate')->nullable();
    $table->decimal('body_temperature', 5, 2)->nullable();
    $table->text('physical_assessment')->nullable();
    $table->text('reason')->nullable();
    $table->text('medical_advice')->nullable();
    $table->enum('health_status', [
        'sehat',
        'tidak_sehat_dengan_pendamping',
        'tidak_sehat'
    ])->nullable();
    $table->text('doctor_advice')->nullable();
    $table->timestamps();
});

Vital Signs Tracked:

  • Blood pressure (systolic/diastolic)
  • Heart rate (BPM)
  • Oxygen saturation (SpO2)
  • Respiratory rate
  • Body temperature

Consultations Table

Migration: 2025_10_02_032107_create_consultations_table.php

Purpose: Medical consultation records.

Schema::create('consultations', function (Blueprint $table) {
    $table->id();
    $table->foreignId('patient_id')->nullable()->constrained()->onDelete('set null');
    $table->string('patient_name')->nullable();
    $table->date('visit_date');
    $table->enum('visit_type', [
        'Physical Examination',
        'Medical Consultation',
        'Medical Screening',
        'Follow-up'
    ]);
    $table->enum('patient_status', ['Registered', 'Not Registered']);
    $table->text('examination_results')->nullable();
    $table->text('notes')->nullable();
    $table->timestamps();
});

Medical Records Table

Migration: 2025_09_30_053241_create_medical_records_table.php

Purpose: Centralized patient medical history.

Schema::create('medical_records', function (Blueprint $table) {
    $table->id();
    $table->uuid('uuid')->unique();
    $table->foreignId('patient_id')->constrained('patients')->onDelete('cascade');
    $table->foreignId('physical_examination_id')->constrained('physical_examinations')->onDelete('cascade');
    $table->string('medical_record_number')->unique();
    $table->timestamps();
});

6. Pharmacy & Inventory Management

Drug Categories Table

Migration: 2025_10_02_032722_create_drug_categories_table.php

Purpose: Classification system for medications.

Schema::create('drug_categories', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->text('description')->nullable();
    $table->timestamps();
});

Storage Racks Table

Migration: 2025_10_02_032744_create_storage_racks_table.php

Purpose: Pharmacy storage location management.

Schema::create('storage_racks', function (Blueprint $table) {
    $table->id();
    $table->string('rack_number');
    $table->string('location_description')->nullable();
    $table->integer('capacity')->nullable();
    $table->timestamps();
});

Drugs Table

Migration: 2025_10_02_032752_create_drugs_table.php

Purpose: Comprehensive drug inventory management.

Schema::create('drugs', function (Blueprint $table) {
    $table->id();
    $table->uuid('uuid')->unique();
    $table->string('name'); // Generic name
    $table->string('brand')->nullable(); // Brand name
    $table->text('description')->nullable();
    $table->string('form'); // tablet, sirup, kapsul, etc.
    $table->string('strength'); // 500 mg, 120 mg/5 mL, etc.
    $table->foreignId('category_id')->constrained('drug_categories');
    $table->enum('status', [
        'bebas', 'bebas_terbatas',
        'keras', 'narkotika', 'psikotropika'
    ])->default('bebas');
    $table->string('barcode')->unique()->nullable();
    $table->string('supplier_code')->nullable();
    $table->integer('min_stock')->default(0);
    $table->integer('max_stock')->default(0);
    $table->integer('current_stock')->default(0);
    $table->foreignId('rack_id')->constrained('storage_racks');
    $table->string('rack_position', 10); // 001, 002, etc.
    $table->date('expiration_date')->nullable();
    $table->timestamps();
    $table->softDeletes();
});

Drug Classification (Indonesian System):

  • bebas: Over-the-counter
  • bebas_terbatas: Limited over-the-counter
  • keras: Prescription only
  • narkotika: Narcotics
  • psikotropika: Psychotropics

Stock Transactions Table

Migration: 2025_10_02_032803_create_stock_transaction_table.php

Purpose: Track all pharmacy inventory movements.

Schema::create('stock_transactions', function (Blueprint $table) {
    $table->id();
    $table->foreignId('drug_id')->constrained('drugs')->onDelete('cascade');
    $table->enum('transaction_type', [
        'purchase', 'sale', 'adjustment',
        'transfer', 'return', 'expiry'
    ]);
    $table->integer('quantity');
    $table->decimal('unit_price', 10, 2)->nullable();
    $table->decimal('total_price', 10, 2)->nullable();
    $table->text('notes')->nullable();
    $table->string('reference_number')->nullable(); // Invoice/Prescription number
    $table->timestamp('transaction_date');
    $table->timestamps();
});

7. Financial & Payment System

Amount Services Table

Migration: 2025_09_30_163525_create_amount_services_table.php

Purpose: Service pricing configuration.

Schema::create('amount_services', function (Blueprint $table) {
    $table->id();
    $table->string('service_name');
    $table->decimal('amount', 10, 2);
    $table->text('description')->nullable();
    $table->boolean('is_active')->default(true);
    $table->timestamps();
});

Payments Table

Migration: 2025_09_30_164211_create_payments_table.php

Purpose: Payment processing and tracking.

Schema::create('payments', function (Blueprint $table) {
    $table->id();
    $table->uuid('uuid')->unique();
    $table->string('no_transaction')->unique();
    $table->foreignId('patient_id')->constrained('patients')->cascadeOnDelete();
    $table->foreignId('cashier_id')->nullable()->constrained('cashiers')->nullOnDelete();
    $table->boolean('payment_status')->default(false);
    $table->decimal('amount_paid', 10, 2)->nullable();
    $table->string('payment_method')->nullable();
    $table->string('payment_proof')->nullable();
    $table->text('service_types')->nullable();
    $table->foreignId('amount_service_id')->constrained('amount_services')->cascadeOnDelete();
    $table->timestamps();
});

Transactions Table

Migration: 2025_09_30_200944_create_transactions_table.php

Purpose: General transaction logging system.

Schema::create('transactions', function (Blueprint $table) {
    $table->id();
    $table->uuid('uuid')->unique();
    $table->string('transaction_number')->unique();
    $table->string('transaction_type');
    $table->decimal('amount', 10, 2);
    $table->string('description')->nullable();
    $table->json('metadata')->nullable();
    $table->timestamp('transaction_date');
    $table->timestamps();
});

Amount Service Payment Table

Migration: 2025_09_30_164212_create_amount_service_payment_table.php

Purpose: Junction table for service-payment relationships.

Schema::create('amount_service_payment', function (Blueprint $table) {
    $table->id();
    $table->foreignId('amount_service_id')->constrained()->onDelete('cascade');
    $table->foreignId('payment_id')->constrained()->onDelete('cascade');
    $table->timestamps();
});

8. System Administration

Miniapp Access Codes Table

Migration: 2025_10_31_180000_create_miniapp_access_codes_table.php & 2025_11_12_200000_create_miniapp_access_codes_table.php

Purpose: Access control for miniapp features.

Schema::create('miniapp_access_codes', function (Blueprint $table) {
    $table->id();
    $table->string('code')->unique();
    $table->string('description')->nullable();
    $table->boolean('is_active')->default(true);
    $table->timestamp('expires_at')->nullable();
    $table->timestamps();
});

Releases Table

Migration: 2025_10_06_094436_create_releases_table.php

Purpose: Application release management.

Schema::create('releases', function (Blueprint $table) {
    $table->id();
    $table->string('version');
    $table->string('title');
    $table->text('description')->nullable();
    $table->enum('status', ['draft', 'published', 'archived'])->default('draft');
    $table->timestamp('released_at')->nullable();
    $table->timestamps();
});

Release Items Table

Migration: 2025_10_06_094437_create_release_items_table.php

Purpose: Individual release notes and changes.

Schema::create('release_items', function (Blueprint $table) {
    $table->id();
    $table->foreignId('release_id')->constrained()->onDelete('cascade');
    $table->enum('type', ['feature', 'bugfix', 'improvement', 'breaking']);
    $table->string('title');
    $table->text('description')->nullable();
    $table->timestamps();
});

Database Relationships Summary

Major Relationships:

  1. Users → Staff Tables: One-to-One (users.id → staff.user_id)
  2. Users → Patients: One-to-One (users.id → patients.user_id)
  3. Patients → Screenings: One-to-Many
  4. Patients → Physical_Examinations: One-to-Many
  5. Patients → Payments: One-to-Many
  6. Screenings → Screening_Answers: One-to-Many
  7. Drugs → Stock_Transactions: One-to-Many
  8. Amount_Services → Payments: One-to-Many

Foreign Key Constraints:

  • Cascade Delete: Most relationships use cascade for data integrity
  • Null on Delete: Some optional relationships allow nullification
  • Restrict Delete: Critical relationships prevent deletion

Migration Order

The migrations should be run in chronological order (by filename) to maintain database integrity and relationships.

Performance Considerations

Indexing Strategy:

  • Primary keys automatically indexed
  • Foreign keys automatically indexed
  • Additional indexes on frequently queried fields:
    • patients.nik
    • users.email
    • screenings.screening_date
    • payments.no_transaction

Optimization Recommendations:

  1. Composite Indexes: For complex queries
  2. Full-text Search: For patient name searches
  3. Partitioning: For large tables (logs, transactions)
  4. Archiving: Regular cleanup of old records

Data Integrity

Constraints Implemented:

  • Unique Constraints: UUIDs, transaction numbers, barcodes
  • Foreign Key Constraints: Maintain referential integrity
  • Check Constraints: Enum values, data ranges
  • Not Null Constraints: Required fields

Soft Deletes:

  • drugs table uses soft deletes for inventory tracking
  • patients table has archived_at for record keeping

Backup Strategy

Recommended Backup Types:

  1. Full Database Backup: Weekly
  2. Incremental Backups: Daily
  3. Transaction Log Backups: Hourly
  4. File System Backups: Daily (for uploaded files)

Recovery Time Objectives (RTO/RPO):

  • RTO: 4 hours (time to restore service)
  • RPO: 1 hour (maximum data loss tolerance)

This comprehensive database structure supports all aspects of the Klinik Gunung Semeru healthcare management system, from patient registration through medical services, pharmacy management, and financial operations.

Gemini AI Integration for Health Screening

Comprehensive documentation of Google Gemini AI integration for automated KTP, Passport analysis, and document processing in the Klinik Gunung Semeru healthcare system

Controllers

Documentation of all controllers in the Klinik Gunung Health Screening System backend