# 12 — Suggested Database Schema

← [Back to index](README.md)

Indicative Laravel migrations. Adapt to your tenancy strategy; all tables scoped per tenant.
**API responses are camelCase even though columns are snake_case** — map via Eloquent API
Resources (e.g. `fee_piastres` → `feePiastres`).

```php
// tenants
$t->uuid('id')->primary();
$t->string('name');             // clinic name
$t->string('slug')->unique();   // X-Tenant
$t->string('plan_code')->default('trial');
$t->timestamp('trial_ends_at')->nullable();
$t->json('working_hours')->nullable();   // {days:[{day,isOpen,openMinute,closeMinute}]}
$t->boolean('seed_sample_data')->default(true);
$t->timestamps();

// users  (owner + receptionist)
$t->uuid('id')->primary();
$t->foreignUuid('tenant_id');
$t->string('name');
$t->string('email');
$t->string('password');         // hashed
$t->string('role');             // doctor | receptionist
$t->timestamps();
$t->unique(['tenant_id','email']);

// visit_types
$t->uuid('id')->primary();
$t->foreignUuid('tenant_id');
$t->string('name');
$t->string('kind');             // firstVisit|followUp|consultation|procedure
$t->unsignedInteger('fee_piastres');
$t->unsignedInteger('duration_minutes');
$t->timestamps();

// patients
$t->uuid('id')->primary();
$t->foreignUuid('tenant_id');
$t->string('full_name');
$t->date('date_of_birth');
$t->string('gender');           // male|female
$t->string('phone');
$t->string('phone_alt')->nullable();
$t->string('email')->nullable();
$t->text('national_id')->nullable();       // encrypted
$t->string('address')->nullable();
$t->string('blood_type')->default('unknown');  // a_pos ...
$t->json('emergency_contact')->nullable();     // {name,phone,relation}
$t->json('insurance')->nullable();             // {provider,policyNumber(encrypted)}
$t->timestamp('last_visit_at')->nullable();
$t->softDeletes();
$t->timestamps();

// ── Medical record sections (children of patient) ──

// patient_allergies   (feeds drug-allergy check)
$t->uuid('id')->primary();
$t->foreignUuid('patient_id');
$t->string('substance');        // store lowercase for matching
$t->string('severity');         // mild|moderate|severe
$t->string('reaction')->nullable();
$t->text('notes')->nullable();
$t->timestamp('noted_at')->nullable();

// patient_chronic_conditions
$t->uuid('id')->primary();
$t->foreignUuid('patient_id');
$t->string('name');
$t->unsignedSmallInteger('since_year')->nullable();
$t->text('notes')->nullable();

// patient_current_medications
$t->uuid('id')->primary();
$t->foreignUuid('patient_id');
$t->string('drug_name');
$t->string('dose')->nullable();
$t->string('frequency')->nullable();
$t->timestamp('started_at')->nullable();
$t->text('notes')->nullable();

// patient_surgical_history
$t->uuid('id')->primary();
$t->foreignUuid('patient_id');
$t->string('procedure');
$t->date('date')->nullable();
$t->string('hospital')->nullable();
$t->string('surgeon')->nullable();
$t->text('notes')->nullable();

// patient_family_history
$t->uuid('id')->primary();
$t->foreignUuid('patient_id');
$t->string('condition');
$t->string('relation');
$t->text('notes')->nullable();

// patient_vaccinations
$t->uuid('id')->primary();
$t->foreignUuid('patient_id');
$t->string('name');
$t->date('date_given')->nullable();
$t->text('notes')->nullable();

// appointments
$t->uuid('id')->primary();
$t->foreignUuid('tenant_id');
$t->foreignUuid('patient_id');
$t->string('patient_name');     // denormalized
$t->foreignUuid('doctor_id');
$t->string('doctor_name');      // denormalized
$t->timestamp('scheduled_at');
$t->unsignedInteger('duration_minutes');
$t->string('visit_type');       // firstVisit ...
$t->string('status');           // scheduled ...
$t->text('notes')->nullable();
$t->timestamp('arrived_at')->nullable();
$t->timestamps();
// add an overlap guard per (doctor_id, time range) for blocking statuses

// visits
$t->uuid('id')->primary();
$t->foreignUuid('tenant_id');
$t->foreignUuid('appointment_id')->nullable();
$t->foreignUuid('patient_id');
$t->string('patient_name');
$t->foreignUuid('doctor_id');
$t->string('doctor_name');
$t->string('visit_type');
$t->string('status');           // inProgress|completed|signed
$t->timestamp('started_at');
$t->timestamp('completed_at')->nullable();
$t->json('vitals')->nullable();
$t->text('chief_complaint')->nullable();
$t->text('clinical_notes')->nullable();
$t->text('treatment_plan')->nullable();
$t->date('follow_up_date')->nullable();
$t->timestamps();

// diagnoses  (child of visit)
$t->uuid('id')->primary();
$t->foreignUuid('visit_id');
$t->string('name');
$t->string('icd_code')->nullable();
$t->text('notes')->nullable();
$t->boolean('is_primary')->default(false);

// visit_medications  (child of visit — PrescriptionItem)
$t->uuid('id')->primary();
$t->foreignUuid('visit_id');
$t->string('medication_name');
$t->string('dosage');
$t->string('frequency');
$t->unsignedInteger('duration_days');
$t->string('route')->nullable();
$t->text('instructions')->nullable();

// prescriptions
$t->uuid('id')->primary();
$t->foreignUuid('tenant_id');
$t->foreignUuid('patient_id');
$t->string('patient_name');
$t->foreignUuid('doctor_id');
$t->string('doctor_name');
$t->string('status');           // draft|active|expired|cancelled
$t->foreignUuid('visit_id')->nullable();
$t->string('diagnosis')->nullable();
$t->text('notes')->nullable();
$t->timestamp('valid_until')->nullable();
$t->timestamps();

// rx_items  (child of prescription — RxItem)
$t->uuid('id')->primary();
$t->foreignUuid('prescription_id');
$t->string('medication_name');
$t->string('active_ingredient');
$t->string('dosage');
$t->string('frequency');
$t->unsignedInteger('duration_days');
$t->string('route')->nullable();
$t->text('instructions')->nullable();
$t->unsignedInteger('quantity')->nullable();

// invoices
$t->uuid('id')->primary();
$t->foreignUuid('tenant_id');
$t->string('patient_name');
$t->unsignedInteger('amount_piastres');
$t->string('status');           // pending|paid
$t->string('description')->nullable();
$t->timestamps();
```
