Database Design - personalProjectsDeltaG8109/saludvital GitHub Wiki

Identity and User Profile Entities

roles

Column	MySQL Type	Constraints
id	INT UNSIGNED	Primary key, auto-increment
name	VARCHAR(30)	Not null, unique
description	VARCHAR(255)	Nullable
created_at	DATETIME	Not null
updated_at	DATETIME	Not null

Initial roles are Patient, Doctor, and Administrator.

users

Column	MySQL Type	Constraints
id	INT UNSIGNED	Primary key, auto-increment
role_id	INT UNSIGNED	Foreign key to roles.id, not null
email	VARCHAR(80)	Not null, unique
password_hash	VARCHAR(255)	Not null
is_active	BOOLEAN	Not null, default TRUE
last_login_at	DATETIME	Nullable, UTC convention
created_at	DATETIME	Not null
updated_at	DATETIME	Not null

Each account must have exactly one role. The application must update last_login_at only after successful authentication and must prevent inactive accounts from authenticating. Passwords must never be stored in plaintext.

patients

Column	MySQL Type	Constraints
id	INT UNSIGNED	Primary key, auto-increment
user_id	INT UNSIGNED	Nullable, unique foreign key to users.id
first_name	VARCHAR(50)	Not null
last_name	VARCHAR(50)	Not null
phone	VARCHAR(15)	Not null
date_of_birth	DATE	Not null
document_type	ENUM('CC', 'CE')	Not null
document_number	VARCHAR(15)	Not null
department	VARCHAR(100)	Not null
municipality	VARCHAR(100)	Not null
blood_type	VARCHAR(3)	Nullable
allergies	TEXT	Nullable
existing_conditions	TEXT	Nullable
address	VARCHAR(255)	Nullable
created_at	DATETIME	Not null
updated_at	DATETIME	Not null

A unique composite constraint must be defined on (document_type, document_number).

A patient record may exist without a web account. When user_id is not null, it must reference an account with the Patient role. Identity verification is required before associating an existing patient record with a newly created account.

doctors

Column	MySQL Type	Constraints
id	INT UNSIGNED	Primary key, auto-increment
user_id	INT UNSIGNED	Not null, unique foreign key to users.id
specialty_id	INT UNSIGNED	Not null, foreign key to specialties.id
professional_information	TEXT	Nullable
created_at	DATETIME	Not null
updated_at	DATETIME	Not null

The user_id column must remain unique so that one user account cannot be associated with multiple doctor profiles.

Doctor-Specialty Referential Integrity

The doctors.specialty_id field identifies the doctor's current primary specialty. A doctor may change their primary specialty without losing or invalidating existing appointments or clinical records.

The appointments entity must reference doctors.id and specialties.id through separate foreign keys. Do not define a composite foreign key from appointments(doctor_id, specialty_id) to doctors(id, specialty_id).

The appointments.specialty_id field records the specialty selected when the appointment was created. This value must remain unchanged when the doctor's current primary specialty changes.

When creating a new appointment, the backend must validate that the selected specialty is active and compatible with the doctor's current professional configuration. This validation applies to new bookings and must not invalidate historical appointments.

Changing a doctor's primary specialty must not automatically modify, cancel, or delete existing appointments, consultations, diagnoses, prescriptions, or appointment history. Any changes to future appointments must follow an explicit business workflow and must preserve historical records.

If doctors are allowed to have multiple specialties in the future, introduce a dedicated doctor-specialty association entity with the appropriate rules for current and historical assignments.

Specialties and Doctor Availability

specialties

Column	MySQL Type	Constraints
id	INT UNSIGNED	Primary key, auto-increment
name	VARCHAR(100)	Not null, unique
description	VARCHAR(255)	Nullable
is_active	BOOLEAN	Not null, default TRUE
created_at	DATETIME	Not null
updated_at	DATETIME	Not null

Inactive specialties must not be offered for new appointments. Deactivating a specialty must preserve historical appointments and clinical records.

doctor_availability

Column	MySQL Type	Constraints
id	INT UNSIGNED	Primary key, auto-increment
doctor_id	INT UNSIGNED	Not null, foreign key to doctors.id
day_of_week	TINYINT UNSIGNED	Not null, values 1–7
start_time	TIME	Not null
end_time	TIME	Not null
valid_from	DATE	Not null
valid_until	DATE	Nullable
is_active	BOOLEAN	Not null, default TRUE
created_at	DATETIME	Not null
updated_at	DATETIME	Not null

The weekday numbering is Monday = 1 through Sunday = 7.

The general business-hours window is 07:00–19:00 in America/Bogota. Weekly availability must fall within this window. Multiple availability blocks may be defined for the same doctor and weekday to represent breaks or split shifts.

The effective availability must account for the applicable date range, active status, schedule exceptions, existing appointments, and booking rules.

doctor_schedule_exceptions

Column	MySQL Type	Constraints
id	INT UNSIGNED	Primary key, auto-increment
doctor_id	INT UNSIGNED	Not null, foreign key to doctors.id
start_at	DATETIME	Not null, UTC convention
end_at	DATETIME	Not null, UTC convention
exception_type	ENUM('Unavailable', 'AdditionalAvailability')	Not null
reason	VARCHAR(255)	Not null
created_by	INT UNSIGNED	Nullable, foreign key to users.id
actor_snapshot	VARCHAR(100)	Not null
created_at	DATETIME	Not null, UTC convention

actor_snapshot identifies the human actor or automated process responsible for creating the exception. Human-created exceptions must reference the responsible user. Automated exceptions may use a null created_by and must identify the responsible process in actor_snapshot.

end_at must be later than start_at. Schedule changes must identify affected future appointments and preserve appointments that remain compatible with the updated schedule. An Unavailable exception blocks appointments during its interval. An AdditionalAvailability exception represents additional availability subject to the general business-hours policy.

Availability and Integrity Rules

The model must prevent duplicate doctor availability records with identical scheduling attributes. A suitable unique constraint or equivalent validation strategy must be defined. This rule does not replace the validation required to prevent partially overlapping availability intervals.

• end_time must be later than start_time.
• valid_until, when present, must not precede valid_from.
• Exception end timestamps must be later than their start timestamps.
• Availability must not extend outside the general business-hours window.
• Overlapping weekly availability blocks for the same doctor must be rejected or normalized.
• Overlapping unavailability exceptions must be handled consistently.
• All generated appointment slots must satisfy the 30-minute duration, five-hour advance booking requirement, doctor availability, and concurrency rules.
• Schedule changes must identify affected future appointments and cancel only those that become incompatible with the updated schedule.
• Administrative schedule changes must record the responsible actor, reason, timestamp, and affected appointments in the audit trail.
• Historical appointments and clinical records must not be deleted when availability or specialty records are deactivated.
• Availability validation and appointment changes must be performed transactionally where required to preserve consistency.

Appointment Management and Clinical Records

appointment_statuses

Column	MySQL Type	Constraints
id	INT UNSIGNED	Primary key, auto-increment
name	VARCHAR(30)	Not null, unique
description	VARCHAR(255)	Nullable
created_at	DATETIME	Not null
updated_at	DATETIME	Not null

Initial status values are Pending, In Progress, Completed, and Cancelled. These values must be initialized consistently across environments.

The application must validate allowed state transitions. A cancelled appointment must not be treated as an active booking, and a completed appointment must retain its clinical and historical records.

appointments

Column	MySQL Type	Constraints
id	INT UNSIGNED	Primary key, auto-increment
patient_id	INT UNSIGNED	Not null, foreign key to patients.id
doctor_id	INT UNSIGNED	Not null, foreign key to doctors.id
specialty_id	INT UNSIGNED	Not null, foreign key to specialties.id
status_id	INT UNSIGNED	Not null, foreign key to appointment_statuses.id
start_at	DATETIME	Not null, UTC convention
end_at	DATETIME	Not null, UTC convention
reason	VARCHAR(500)	Nullable
source	ENUM('Web', 'WhatsApp', 'Admin')	Not null
created_at	DATETIME	Not null, UTC convention
updated_at	DATETIME	Not null, UTC convention

Doctor and Specialty Integrity Rules

• Every appointment must reference an existing doctor and an existing specialty through separate foreign keys.
• appointments.specialty_id records the specialty selected when the appointment was created.
• Historical appointment specialties must not be required to match the doctor's current primary specialty.
• New appointments must validate that the selected specialty is active and compatible with the doctor's current professional configuration.
• Changing a doctor's primary specialty must not alter existing appointment or clinical records.
• Historical appointments and clinical records must remain accessible according to the applicable authorization and retention policies.

appointment_history

Column	MySQL Type	Constraints
id	INT UNSIGNED	Primary key, auto-increment
appointment_id	INT UNSIGNED	Not null, foreign key to appointments.id
actor_user_id	INT UNSIGNED	Nullable, foreign key to users.id
actor_snapshot	VARCHAR(100)	Not null
event_type	VARCHAR(50)	Not null
previous_values	JSON	Nullable
new_values	JSON	Nullable
reason	VARCHAR(500)	Nullable
occurred_at	DATETIME	Not null, UTC convention

appointment_history records the lifecycle of an appointment, including creation, cancellation, rescheduling, and status changes.

actor_user_id may be null for automated actions that do not originate from a user account. actor_snapshot must identify the responsible actor or process, such as SystemScheduler or WhatsAppBot, without falsely attributing an automated action to a person.

History entries must be created transactionally with the corresponding appointment change. Ordinary application operations must not update or delete existing history entries. The schema must not store passwords, access tokens, verification codes, or unnecessary clinical information in history snapshots.

consultations

Column	MySQL Type	Constraints
id	INT UNSIGNED	Primary key, auto-increment
appointment_id	INT UNSIGNED	Not null, unique foreign key to appointments.id
doctor_id	INT UNSIGNED	Not null, foreign key to doctors.id
started_at	DATETIME	Not null, UTC convention
ended_at	DATETIME	Nullable, UTC convention
duration_seconds	INT UNSIGNED	Nullable
symptoms	TEXT	Nullable
patient_feeling	TEXT	Nullable
vital_signs	JSON	Nullable
clinical_notes	TEXT	Nullable
created_at	DATETIME	Not null, UTC convention
updated_at	DATETIME	Not null, UTC convention
status	ENUM('InProgress', 'Completed', 'Interrupted')	Not null, default InProgress
interruption_reason	VARCHAR(500)	Nullable

Each appointment may have at most one consultation. A consultation must reference the doctor assigned to its appointment.

Clinical integrity rules

• A consultation may be created only for an eligible appointment and must follow the permitted appointment state transitions.
• The application must validate that consultations.doctor_id matches appointments.doctor_id.
• ended_at and duration_seconds remain null while the consultation is in progress. When completed, the end timestamp must be later than the start timestamp.
• The duration must be calculated from the recorded timestamps rather than independently entered as an unrelated value.
• Completing a consultation and updating the appointment status to Completed must preserve transactional consistency and create the corresponding appointment history entry.
• The system must prevent a doctor from having more than one active consultation at the same time, using transactional concurrency control.
• Access to symptoms, vital signs, clinical notes, diagnoses, and prescriptions must be restricted to authorized users.
• vital_signs must follow a documented JSON structure with defined measurement names, units, and validation rules. A separate measurement table may be introduced later if individual measurements require independent history, queries, or reporting.

The backend must validate, within the same transaction, that consultations.doctor_id matches appointments.doctor_id and that the appointment is eligible for consultation. The system must reject any consultation that references a different doctor, even when both doctor identifiers independently reference existing records.

diagnoses

Column	MySQL Type	Constraints
id	INT UNSIGNED	Primary key, auto-increment
consultation_id	INT UNSIGNED	Not null, foreign key to consultations.id
diagnosis	TEXT	Not null
notes	TEXT	Nullable
created_at	DATETIME	Not null, UTC convention
updated_at	DATETIME	Not null, UTC convention

A consultation may have multiple diagnoses. Each diagnosis must belong to an existing consultation. Access and changes must follow the clinical-record authorization and audit policies.

prescriptions

Column	MySQL Type	Constraints
id	INT UNSIGNED	Primary key, auto-increment
consultation_id	INT UNSIGNED	Not null, foreign key to consultations.id
issued_at	DATETIME	Not null, UTC convention
notes	TEXT	Nullable
created_at	DATETIME	Not null, UTC convention
updated_at	DATETIME	Not null, UTC convention

A consultation may have multiple prescriptions. Each prescription must belong to one consultation and may contain multiple prescription items. Prescriptions must remain associated with the consultation that produced them.

prescription_items

Column	MySQL Type	Constraints
id	INT UNSIGNED	Primary key, auto-increment
prescription_id	INT UNSIGNED	Not null, foreign key to prescriptions.id
medication_name	VARCHAR(150)	Not null
dosage	VARCHAR(100)	Not null
frequency	VARCHAR(100)	Not null
duration	VARCHAR(100)	Not null
instructions	TEXT	Nullable
created_at	DATETIME	Not null, UTC convention
updated_at	DATETIME	Not null, UTC convention

Each prescription item describes one medication entry. Medication instructions must be validated and displayed only to authorized users. The model must preserve the recorded prescription details as part of the clinical record.

Relationships Summary

• One patient may have many appointments.
• One doctor may have many appointments.
• One specialty may be referenced by many appointments.
• One appointment status may be referenced by many appointments.
• One appointment may have many history entries.
• One appointment may have zero or one consultation.
• One consultation may have many diagnoses.
• One consultation may have many prescriptions.
• One prescription may have many prescription items.

All foreign-key deletion policies must be chosen explicitly. Clinical records and historical appointment records must not be removed through indiscriminate cascading deletes.

Notifications, Audit Logs, and WhatsApp Integration

notification_preferences

Column	MySQL Type	Constraints
id	INT UNSIGNED	Primary key, auto-increment
patient_id	INT UNSIGNED	Not null, foreign key to patients.id
channel	ENUM('Email', 'SMS', 'WhatsApp')	Not null
event_type	VARCHAR(50)	Not null
is_enabled	BOOLEAN	Not null, default TRUE
created_at	DATETIME	Not null, UTC convention
updated_at	DATETIME	Not null, UTC convention

Required unique constraint: UNIQUE (patient_id, channel, event_type).

A patient must have at most one preference record for each notification channel and event type.

notifications

Column	MySQL Type	Constraints
id	INT UNSIGNED	Primary key, auto-increment
patient_id	INT UNSIGNED	Not null, foreign key to patients.id
appointment_id	INT UNSIGNED	Nullable, foreign key to appointments.id
channel	ENUM('Email', 'SMS', 'WhatsApp')	Not null
recipient_address	VARCHAR(255)	Not null
event_type	VARCHAR(50)	Not null
status	ENUM('Pending', 'Processing', 'Sent', 'Failed', 'Cancelled')	Not null, default Pending
scheduled_at	DATETIME	Not null, UTC convention
sent_at	DATETIME	Nullable, UTC convention
attempt_count	INT UNSIGNED	Not null, default 0
provider_message_id	VARCHAR(191)	Nullable
last_error	TEXT	Nullable, sanitized technical details only
created_at	DATETIME	Not null, UTC convention
updated_at	DATETIME	Not null, UTC convention

Notifications are patient-directed in the initial version. Every notification must reference a patient profile, regardless of whether the patient has a web account. The patient_id column is mandatory, and no user_id column is required. Notifications intended for doctors or administrators are outside the scope of this model.

The backend must validate the destination, channel, applicable preferences, and authorization before scheduling a notification. Appointment reminders should be scheduled according to the configured reminder policy, initially 24 hours before the appointment when applicable.

Failed notifications may be retried with controlled backoff and a maximum attempt policy. Retries must not duplicate the underlying appointment operation. Sent means that the provider accepted or reported the message as sent; it does not necessarily mean that the recipient received or read it.

Sensitive clinical information must not be included in WhatsApp messages. Error fields must not store credentials, tokens, verification codes, or unnecessary personal or clinical data.

Provider message identifiers must be unique within the provider's documented uniqueness scope. If multiple providers or accounts are supported, the provider/account scope must be included in the uniqueness design.

audit_logs

Column	MySQL Type	Constraints
id	INT UNSIGNED	Primary key, auto-increment
actor_user_id	INT UNSIGNED	Nullable, foreign key to users.id
actor_snapshot	VARCHAR(100)	Not null
action	VARCHAR(60)	Not null
target_type	VARCHAR(60)	Not null
target_id	VARCHAR(100)	Nullable
target_description	VARCHAR(255)	Nullable
changes	JSON	Nullable
reason	VARCHAR(500)	Nullable
result	ENUM('Success', 'Failure')	Not null
occurred_at	DATETIME	Not null, UTC convention

audit_logs records relevant administrative, security, and system actions, including changes to users, roles, specialties, medical schedules, and other protected resources.

The record must identify the actor, action, affected target, timestamp, outcome, and reason when applicable. The changes field may contain before-and-after values, but passwords, authentication tokens, verification codes, and unnecessary clinical information must never be recorded.

actor_user_id may be null for automated processes. actor_snapshot must identify the actual process or service, such as SystemScheduler or WhatsAppBot, rather than attributing the action to a human who did not perform it.

Audit records must be protected from ordinary application-level modification or deletion. Foreign-key deletion rules must preserve audit records even when an account is deactivated or otherwise removed under an approved retention policy.

appointment_history remains responsible for appointment lifecycle events. audit_logs is responsible for broader system, administrative, and security traceability. A single business operation may create both records when both forms of traceability are required.

whatsapp_conversations

Column	MySQL Type	Constraints
id	INT UNSIGNED	Primary key, auto-increment
patient_id	INT UNSIGNED	Nullable, foreign key to patients.id
provider_conversation_id	VARCHAR(191)	Nullable
status	ENUM('Active', 'Completed', 'Expired', 'Blocked')	Not null, default Active
started_at	DATETIME	Not null, UTC convention
last_activity_at	DATETIME	Not null, UTC convention
ended_at	DATETIME	Nullable, UTC convention
created_at	DATETIME	Not null, UTC convention
updated_at	DATETIME	Not null, UTC convention

A conversation may begin before the patient has been identified. patient_id may be populated only after the identity-verification process succeeds.

Provider conversation identifiers must follow the provider's documented uniqueness scope. An active conversation must expire or close according to the configured session policy.

Provider conversation identifiers must be unique within the provider's documented uniqueness scope. If multiple providers or provider accounts are supported, the model must include the required provider and account identifiers and enforce an appropriate composite unique constraint. If the provider does not supply a conversation identifier, the column may remain NULL.

whatsapp_messages

Column	MySQL Type	Constraints
id	INT UNSIGNED	Primary key, auto-increment
conversation_id	INT UNSIGNED	Not null, foreign key to whatsapp_conversations.id
provider_message_id	VARCHAR(191)	Nullable
direction	ENUM('Inbound', 'Outbound')	Not null
message_type	VARCHAR(40)	Not null
content	TEXT	Nullable, temporary content subject to retention policy
processing_status	ENUM('Received', 'Processing', 'Processed', 'Failed')	Not null
delivery_status	VARCHAR(30)	Nullable
received_at	DATETIME	Nullable, UTC convention
processed_at	DATETIME	Nullable, UTC convention
content_expires_at	DATETIME	Nullable, UTC convention
created_at	DATETIME	Not null, UTC convention
provider_name	VARCHAR(50)	Not null
provider_account_id	VARCHAR(100)	Not null

The database must enforce a composite unique constraint on (provider_name, provider_account_id, provider_message_id). Because provider_message_id is nullable, messages without a provider identifier may be stored without that identifier. When an identifier is present, the database must reject duplicate identifiers within the same provider and account scope.

The implementation must confirm that this scope matches the provider's documented identifier guarantees. Repeated delivery of the same provider event must not create duplicate messages or repeat the associated business operation.

whatsapp_verifications

Column	MySQL Type	Constraints
id	INT UNSIGNED	Primary key, auto-increment
patient_id	INT UNSIGNED	Not null, foreign key to patients.id
code_hash	VARCHAR(255)	Not null
expires_at	DATETIME	Not null, UTC convention
attempt_count	INT UNSIGNED	Not null, default 0
verified_at	DATETIME	Nullable, UTC convention
invalidated_at	DATETIME	Nullable, UTC convention
created_at	DATETIME	Not null, UTC convention

Verification must use the patient's previously registered or verified WhatsApp number and the agreed identity-verification procedure. A document number alone must not be treated as proof of possession of the WhatsApp account.

Codes must expire, have a limited number of attempts, and be invalidated after successful verification or replacement. Verification requests must be rate-limited. Because short numeric codes have low entropy, their protection must be appropriate to their threat model; a plain fast hash alone may be insufficient.

whatsapp_appointment_requests

Column	MySQL Type	Constraints
id	INT UNSIGNED	Primary key, auto-increment
patient_id	INT UNSIGNED	Not null, foreign key to patients.id
conversation_id	INT UNSIGNED	Not null, foreign key to whatsapp_conversations.id
requested_specialty_id	INT UNSIGNED	Nullable, foreign key to specialties.id
requested_doctor_id	INT UNSIGNED	Nullable, foreign key to doctors.id
requested_start_at	DATETIME	Nullable, UTC convention
appointment_id	INT UNSIGNED	Nullable, foreign key to appointments.id
status	ENUM('Pending', 'Confirmed', 'Rejected', 'Cancelled', 'Expired')	Not null, default Pending
created_at	DATETIME	Not null, UTC convention
updated_at	DATETIME	Not null, UTC convention
idempotency_key	VARCHAR(191)	Not null, unique

The idempotency_key must be generated and reused for retries of the same logical appointment-request operation. Its uniqueness scope is global within whatsapp_appointment_requests. The backend must reuse the original key when retrying an operation and must not create a second appointment if the operation has already succeeded.

Notification and Messaging Retention

• Verification secrets must be invalidated and removed when no longer required.
• WhatsApp message content follows the initial 30-day retention proposal, subject to legal and privacy review.
• Technical messaging metadata follows the initial 90-day proposal, subject to necessity and legal requirements.
• Appointment, clinical, and audit records follow their own retention policies; deleting message content must not delete those records.
• Retention jobs must be auditable and must not remove records subject to a legitimate preservation requirement.

Relationships Summary

• One patient may have many notification preferences.
• One patient may receive many notifications, including without a web account.
• One appointment may be associated with many notifications.
• One user may generate many audit records; automated actions may have no user account.
• One patient may have many WhatsApp conversations.
• One conversation may contain many WhatsApp messages.
• One patient may have many verification records over time.
• One conversation may produce many appointment requests.
• One confirmed appointment request may reference one appointment; an appointment must not be created more than once for the same successfully processed request operation.

Foreign-key deletion policies, unique constraints, indexes, and retention behavior must be specified before implementation.

Model Consistency and Integrity Rules

Role and Profile Consistency

• Patient profiles associated with web accounts must reference accounts with the Patient role.
• Doctor profiles must reference accounts with the Doctor role.
• Administrator accounts do not require patient or doctor profiles.
• Role changes must not leave incompatible profile associations.
• Account deactivation must not automatically delete profiles, appointments, consultations, clinical records, or audit history.

The backend must enforce role and profile compatibility. Database constraints should reinforce these rules where technically feasible.

Auxiliary Entities

• emergency_contacts: stores multiple emergency contacts for a patient.
• password_reset_tokens: stores protected password-reset token representations, expiration timestamps, and usage status.
• whatsapp_verifications: stores protected, temporary identity-verification records.
• audit_logs: records relevant administrative, automated, and security actions.

All auxiliary entities must follow the system's integrity, access-control, and retention policies.

Functional Requirements Traceability Matrix

Functional Requirements	Functional Area	Supporting Entities and Rules
FR-001–008	Authentication, roles, authorization, and password recovery	users, roles, password_reset_tokens; backend authentication and authorization rules
FR-009–018	Patient registration and patient profiles with or without web accounts	patients, users, roles; identity and document validation
FR-019–022	Patient profile, medical information, emergency contacts, and notification preferences	patients, emergency_contacts, notification_preferences
FR-023–024	Patient dashboard and clinical history	appointments, consultations, diagnoses, prescriptions, prescription_items
FR-025–037	Appointment discovery, booking, cancellation, rescheduling, and availability	appointments, appointment_statuses, appointment_history, specialties, doctor_availability, doctor_schedule_exceptions; transactional concurrency controls
FR-038–050	Doctor workflow, consultation management, metrics, and clinical records	doctors, consultations, diagnoses, prescriptions, prescription_items, appointments
FR-051–062	Administration, doctor and specialty management, auditability, and reporting	users, roles, patients, doctors, specialties, appointments, audit_logs; administrative business rules
FR-063–067	Notifications, reminders, preferences, delivery tracking, and retries	notifications, notification_preferences, appointments; idempotent delivery processing
FR-068–073	Automated WhatsApp bot, identity verification, and appointment requests	whatsapp_conversations, whatsapp_messages, whatsapp_verifications, whatsapp_appointment_requests, patients, appointments
FR-074–076	Data integrity, audit trail, and historical record preservation	audit_logs, appointment_history; retention and integrity policies
FR-077–081	Backend validation, security, privacy, and recovery	users, password_reset_tokens, whatsapp_verifications, audit_logs; backend security controls
FR-082–085	Spanish interface, responsive design, validation feedback, and accessibility	Frontend design and validation rules; no additional database entity is inherently required

This matrix provides a preliminary mapping between functional requirements and the proposed data model. Each individual requirement must be verified against the complete functional requirements specification before the database-design issue is considered complete.

Additional Model Review Decisions

1. Vital signs: retain the consultations.vital_signs JSON field initially, with a documented schema, supported measurements, units, and validation rules. Reconsider a dedicated measurement entity if detailed historical queries or independent measurement records become necessary.
2. External event idempotency: record a provider event or operation identifier within its documented uniqueness scope to prevent duplicate processing. Idempotency does not replace authentication or authorization.
3. Doctor metrics: calculate active patients as unique patients with at least one completed consultation during the preceding rolling 12-month period.
4. Schedule changes: identify only future appointments made incompatible by the change, preserve unaffected appointments, and record the actor, reason, timestamp, and affected appointments.
5. Remaining policy decisions: finalize the report catalogue and obtain approval for data-retention and anonymization policies before production. Patient identity-document changes and interrupted consultation handling are defined in the final integrity and business rule decisions.

emergency_contacts

Column	MySQL Type	Constraints
id	INT UNSIGNED	Primary key, auto-increment
patient_id	INT UNSIGNED	Not null, foreign key to patients.id
first_name	VARCHAR(50)	Not null
last_name	VARCHAR(50)	Not null
phone	VARCHAR(15)	Not null
email	VARCHAR(254)	Nullable
relationship	VARCHAR(50)	Nullable
created_at	DATETIME	Not null, UTC convention
updated_at	DATETIME	Not null, UTC convention

A patient may have multiple emergency contacts. Emergency contacts must not receive access to the patient's account or clinical records merely because they are registered as contacts.

password_reset_tokens

Column	MySQL Type	Constraints
id	INT UNSIGNED	Primary key, auto-increment
user_id	INT UNSIGNED	Not null, foreign key to users.id
token_hash	VARCHAR(255)	Not null
expires_at	DATETIME	Not null, UTC convention
used_at	DATETIME	Nullable, UTC convention
created_at	DATETIME	Not null, UTC convention

Password-reset tokens must never be stored in plaintext. Tokens must expire, be single-use, and be invalidated after successful password reset or replacement. The backend must apply rate limiting and must not disclose whether an email address belongs to an existing account through the recovery response.

Expired and used token records must be cleaned up according to the security and retention policy. Password reset must not automatically reactivate a deactivated account.

Final Integrity and Business Rule Decisions

1. Appointment Concurrency and Overlap Prevention

Every appointment must satisfy end_at > start_at. The backend must validate this rule before creating or rescheduling an appointment. Where supported by the selected MySQL version and storage engine, a database CHECK constraint should also enforce it.

The backend and database must prevent overlapping active appointments for the same doctor.

Appointment intervals follow the half-open interval convention [start_at, end_at). An appointment ending at 10:30 and another starting at 10:30 do not overlap.

Only appointments with the Pending or In Progress status occupy a bookable time interval and must be considered when preventing overlapping appointments.

Appointments with the Completed or Cancelled status must not block future bookings. Status transitions must be validated so that a completed or cancelled appointment cannot become an active booking without an explicit, authorized business workflow.

The booking workflow must:

1. Begin a database transaction.
2. Acquire an appropriate lock to serialize conflicting booking operations for the selected doctor.
3. Revalidate the doctor's effective availability, schedule exceptions, booking advance rules, and existing active appointments.
4. Reject the booking if an overlapping appointment exists.
5. Create the appointment and its initial appointment_history entry in the same transaction.
6. Commit only if all operations succeed; otherwise, roll back.

For the initial MySQL implementation, locking the selected doctor row with an appropriate transactional locking read is an acceptable strategy, provided every booking and rescheduling operation follows the same locking protocol. The implementation must recheck overlaps after acquiring the lock.

A unique constraint on (doctor_id, start_at) is insufficient because it does not prevent appointments with different start times from overlapping.

Concurrent booking and rescheduling scenarios must be covered by automated integration tests.

2. Doctor and Specialty Consistency

The initial model supports one current primary specialty per doctor.

The doctors.specialty_id field identifies the doctor's current primary specialty. The appointments.doctor_id and appointments.specialty_id fields must reference doctors.id and specialties.id through separate foreign keys.

A composite foreign key linking appointments(doctor_id, specialty_id) to doctors(id, specialty_id) must not be created because it would couple historical appointments to the doctor's current specialty.

The appointment's specialty_id is a historical record of the specialty selected at booking time. It must remain unchanged if the doctor changes their primary specialty.

A specialty change must not delete, rewrite, or invalidate historical appointments, consultations, diagnoses, prescriptions, or appointment history. The backend must validate specialty compatibility for new bookings. Any required changes to future appointments must be handled through an explicit, audited business workflow.

Deactivating a specialty must prevent new bookings under that specialty while preserving existing historical records. Specialty records referenced by appointments must not be physically deleted without an approved preservation or anonymization process.

3. Notifications for Patients With and Without Web Accounts

Patient notification preferences must belong to patients, regardless of whether the patient has a web account.

The notification_preferences entity must use:

• patient_id INT UNSIGNED NOT NULL, foreign key to patients.id.
• channel ENUM('Email', 'SMS', 'WhatsApp') NOT NULL.
• event_type VARCHAR(50) NOT NULL.
• is_enabled BOOLEAN NOT NULL DEFAULT TRUE.

A unique constraint must enforce uniqueness on (patient_id, channel, event_type).

The user_id column must be removed from notification_preferences. Account creation or association must not require transferring preferences because they remain linked to the patient profile.

Patients without web accounts may receive notifications through WhatsApp or SMS. The backend must verify that the selected destination is valid and, where required, verified. It must honor the applicable preferences and consent requirements.

4. Notification Recipient Model

The notifications entity represents patient-directed notifications in the initial version.

The patient_id column must be NOT NULL. The user_id column must be removed from this entity to avoid ambiguity between an account and its patient profile.

The entity must retain appointment_id as nullable because some notifications may concern account verification or other patient events unrelated to an appointment.

recipient_address must capture the intended destination when the notification is scheduled. Changes to a patient's contact details must not silently redirect an already queued notification.

The backend must validate the patient, destination, channel, applicable preferences, and authorization rules before sending.

Direct notifications to doctors or administrators are outside this initial patient-notification model. If later required, they must be designed explicitly rather than introducing ambiguous nullable recipient references.

5. WhatsApp Message Deduplication and Operation Idempotency

WhatsApp message deduplication and appointment-operation idempotency are separate requirements.

whatsapp_messages

The entity must store:

• provider_name VARCHAR(50) NOT NULL.
• provider_account_id VARCHAR(100) NOT NULL, or an equivalent documented provider/account scope.
• provider_message_id VARCHAR(191), nullable only when the provider does not supply an identifier.

When a provider message identifier exists, the database must enforce uniqueness within the provider's documented uniqueness scope, using a unique constraint that includes the relevant provider/account identifiers and message ID.

The system must handle duplicate deliveries without repeating the associated business operation.

whatsapp_appointment_requests

The idempotency_key must be globally unique within whatsapp_appointment_requests.

The same logical appointment-request operation must reuse its original idempotency key on retries. A new key must not be generated simply because an operation is retried.

The appointment creation, request confirmation, and appointment history entry must be transactionally consistent. If the operation has already succeeded, a retry must return or reuse the existing result rather than creating a second appointment.

The implementation must define the key-generation strategy and retention period. The key must remain available for as long as necessary to prevent duplicate processing of the same logical operation.

6. Automated Schedule Exceptions

In doctor_schedule_exceptions:

• Change created_by to INT UNSIGNED NULL, retaining its foreign key to users.id.
• Add actor_snapshot VARCHAR(100) NOT NULL.

Human-created exceptions must reference the responsible user. Automated exceptions may have a null created_by and must identify the responsible process in actor_snapshot.

The system must record the reason and timestamp and identify only future appointments made incompatible by a schedule change. Unaffected appointments must remain unchanged.

Changes to schedules, affected appointments, and corresponding audit records must be transactionally consistent where applicable. If notification delivery subsequently fails, the schedule change must not be reverted; failed notifications must be retried separately.

7. Consultation Lifecycle

Add the following column to consultations:

status ENUM('InProgress', 'Completed', 'Interrupted') NOT NULL DEFAULT 'InProgress'.

The consultation lifecycle is separate from the appointment lifecycle.

• A consultation may start only when the appointment is in an eligible state and the assigned doctor is authorized to attend it.
• The consultation and appointment status must be updated transactionally.
• Completing the consultation must set the appointment status to Completed and create the corresponding appointment history entry in the same transaction.
• An interrupted consultation must not automatically mark the appointment as completed.
• When a consultation is interrupted, the system must record its end timestamp and the reason for interruption.
• The initial model permits at most one consultation per appointment. A resumption must preserve the existing consultation record rather than creating a duplicate.

The system must prevent a doctor from having more than one active consultation at the same time through appropriate transactional concurrency control.

8. Patient Identity Changes

The patient profile must not allow patients to edit their identity-document type or document number directly.

The following rules apply:

• document_type remains ENUM('CC', 'CE') NOT NULL.
• document_number remains VARCHAR(15) NOT NULL.
• A unique composite constraint must remain in place on (document_type, document_number).
• The patient interface must display these fields as read-only.
• If a correction or legitimate identity change is required, the patient must contact an administrator through the approved support process.
• The administrator must verify the patient's identity, validate the requested correction, and record the actor, reason, timestamp, and relevant before-and-after values in audit_logs.
• The change must preserve the integrity of existing appointments, consultations, and clinical history.
• The change must be performed transactionally and must reject any document identity that conflicts with another patient record.

Changing an identity document must not automatically transfer a patient profile or its history to another person.

9. Clinical Data and Vital Signs

Retain consultations.vital_signs JSON in the initial model.

Before implementation, document a schema that defines the supported measurements, numeric validation, units, and measurement timestamps where applicable. For example, blood pressure may use mmHg and body temperature may use degrees Celsius.

Clinical information must be accessible only to authorized roles. Relevant clinical-record changes must be traceable without recording passwords, verification codes, access tokens, or unnecessary sensitive information in audit logs.

A separate vital-sign measurement entity may be introduced if individual measurement history, detailed reporting, or longitudinal clinical analysis becomes necessary.

10. Foreign-Key Deletion and Retention Policies

Foreign-key deletion behavior must be explicitly specified for all relationships.

• Appointments and appointment history must not be removed through indiscriminate cascading deletes.
• Consultations, diagnoses, prescriptions, and prescription items must preserve clinical-record integrity.
• Audit records must remain traceable if a user account is deactivated or legitimately removed under an approved retention policy.
• Where permitted, deleting an actor account may set actor_user_id to NULL while retaining actor_snapshot.
• User accounts with records that must be retained must be deactivated instead of physically deleted.
• Temporary message content and eligible technical records may be cleaned up according to their retention policies without deleting appointment or clinical records.

Retention and deletion must follow the approved privacy, security, and applicable legal requirements. Account deactivation alone does not authorize indefinite retention of every personal-data field.

11. Retention Policy Review

The initial proposal retains WhatsApp message content for 30 days and technical messaging metadata for up to 90 days. These periods remain provisional until reviewed against applicable data-protection and record-retention requirements.

Appointment records, clinical records, audit logs, message content, and technical metadata must have separate documented retention rules.

The cleanup process must be auditable and must preserve records subject to an applicable preservation requirement.

12. Final Implementation Requirements

Before database implementation, the design must specify:

• Primary keys, foreign keys, unique constraints, and required indexes.
• Referential actions for deletion and updates.
• Transaction boundaries for appointment creation, rescheduling, consultation completion, and schedule changes.
• Concurrency-control strategies for bookings and active consultations.
• WhatsApp provider-event deduplication and operation idempotency.
• Authorization and audit requirements for sensitive patient and clinical data.
• Automated tests for all integrity constraints and critical business rules.

Every functional requirement must be verified individually against the final database model before the database-design issue is considered complete.

Foreign Key Relationships and Deletion Policies

1. General Policy

SaludVital must preserve appointment history, clinical records, and auditability. Deactivating an account must not automatically delete its related records.

All foreign keys must define an explicit deletion policy. The application must not rely on database defaults for critical relationships.

2. Foreign Key Deletion Rules

Relationship	Deletion Policy	Reason
users.role_id → roles.id	RESTRICT	A role cannot be deleted while users reference it.
patients.user_id → users.id	RESTRICT	Prevent deleting a user while a patient profile references it.
doctors.user_id → users.id	RESTRICT	Preserve the doctor profile and its historical relationships.
doctors.specialty_id → specialties.id	RESTRICT	A specialty cannot be deleted while doctors reference it.
doctor_availability.doctor_id → doctors.id	RESTRICT	Preserve schedule records; deactivate them or close their validity period instead.
doctor_schedule_exceptions.doctor_id → doctors.id	RESTRICT	Preserve schedule-exception history.
doctor_schedule_exceptions.created_by → users.id	SET NULL	Preserve the exception if its originating user reference is removed; retain an actor snapshot where required.
appointments.patient_id → patients.id	RESTRICT	Preserve appointment history.
appointments.doctor_id → doctors.id	RESTRICT	Preserve the historical doctor relationship and prevent deletion of a doctor referenced by appointments.
appointments.specialty_id → specialties.id	RESTRICT	Preserve the specialty recorded for the appointment and prevent deletion of a specialty referenced by historical appointments.
appointments.status_id → appointment_statuses.id	RESTRICT	Prevent deleting a status referenced by existing appointments.
appointment_history.appointment_id → appointments.id	RESTRICT	Appointment history must remain available while its appointment is retained.
appointment_history.actor_user_id → users.id	SET NULL	Preserve the event and actor snapshot without requiring the user record to remain.
consultations.appointment_id → appointments.id	RESTRICT	Preserve the clinical encounter associated with the appointment.
consultations.doctor_id → doctors.id	RESTRICT	Preserve clinical authorship and accountability.
diagnoses.consultation_id → consultations.id	RESTRICT	Prevent accidental deletion of clinical records.
prescriptions.consultation_id → consultations.id	RESTRICT	Preserve prescriptions and their clinical context.
prescription_items.prescription_id → prescriptions.id	RESTRICT	Prevent accidental loss of prescribed medication details.
notification_preferences.patient_id → patients.id	RESTRICT	Preferences belong to the patient profile; update or deactivate them according to the privacy policy.
notifications.patient_id → patients.id	RESTRICT	Preserve notification delivery records.
notifications.appointment_id → appointments.id	SET NULL	Retain the notification record if its appointment reference is legitimately removed, subject to other dependencies.
audit_logs.actor_user_id → users.id	SET NULL	Preserve the audit record and actor snapshot.
emergency_contacts.patient_id → patients.id	RESTRICT	Require an explicit, legally permitted removal or anonymization workflow before removing a patient.
password_reset_tokens.user_id → users.id	CASCADE	Reset tokens are temporary security records and must not outlive their account.
whatsapp_conversations.patient_id → patients.id	SET NULL	Preserve necessary conversation metadata if the patient association is legitimately removed, subject to retention rules.
whatsapp_messages.conversation_id → whatsapp_conversations.id	RESTRICT	Apply explicit message-retention and cleanup rules before removing a conversation.
whatsapp_verifications.patient_id → patients.id	CASCADE	Verification records are temporary and must not outlive the patient record.
whatsapp_appointment_requests.patient_id → patients.id	RESTRICT	Preserve request history and prevent accidental loss of booking records.
whatsapp_appointment_requests.conversation_id → whatsapp_conversations.id	RESTRICT	Preserve the conversation context for a retained request.
whatsapp_appointment_requests.appointment_id → appointments.id	SET NULL	Preserve the request if its linked appointment reference is legitimately removed.
whatsapp_appointment_requests.requested_specialty_id → specialties.id	SET NULL	Preserve the WhatsApp appointment request if the requested specialty reference is removed. This requires the column to allow NULL.
whatsapp_appointment_requests.requested_doctor_id → doctors.id	SET NULL	Preserve the WhatsApp appointment request if the requested doctor reference is removed. This requires the column to allow NULL.

3. Deactivation and Historical Records

• User, patient, doctor, and specialty records should normally be deactivated instead of physically deleted when historical records depend on them.
• Deactivating a doctor must prevent new bookings and trigger the defined process for affected future appointments.
• Deactivating a user must not automatically erase appointments, consultations, diagnoses, prescriptions, or audit history.
• Physical deletion and anonymization must follow approved legal, privacy, and retention requirements.
• Temporary security and messaging data must have explicit cleanup rules.
• Database constraints do not replace authorization checks, application validation, or transactional business rules.

4. Implementation Requirements

Before creating migrations, verify that:

1. All referenced columns have compatible data types and signedness.
2. Every foreign key must reference an existing primary or unique key with compatible column types and signedness.
3. Indexes support foreign-key lookups and common queries.
4. Retained clinical and audit records cannot be deleted through ordinary application workflows.
5. Automated cleanup jobs respect retention rules and do not remove records required for traceability.
6. Deletion, deactivation, and anonymization operations are covered by automated tests.

These policies must be reviewed against applicable healthcare, privacy, and data-retention requirements before production deployment.

Business Hours and Time Zone Policy

General Business Hours

SaludVital's general medical appointment window is 07:00 to 19:00 Colombia local time (America/Bogota). Individual doctors may have narrower availability periods within this window.

Appointments have a standard duration of 30 minutes. Therefore, the latest standard appointment start time is 18:30, allowing the appointment to end by 19:00.

The system must not offer appointment slots outside the doctor's effective availability. Effective availability is determined by the doctor's configured schedule, applicable schedule exceptions, existing appointments, and the general business-hours window.

Date and Time Handling

• Store appointment and consultation timestamps consistently in UTC.
• Convert timestamps to America/Bogota when displaying local appointment times and applying local business-hour rules.
• Use a consistent time source for all booking, cancellation, and rescheduling calculations.
• Do not rely on the application server's default time zone.
• Store recurring weekly schedules using local time-of-day values and an explicit applicable time zone.

Booking Advance Time

A patient may book an appointment only when its start time is at least five elapsed hours after the booking request time.

The backend must validate this rule when creating an appointment, including requests received through WhatsApp.

Patient Cancellation and Rescheduling

Patients may cancel or reschedule an appointment when at least 24 elapsed hours remain before its scheduled start time.

Rescheduling must preserve the same doctor and revalidate the requested slot's availability and all booking rules atomically.

Administrative schedule changes follow separate rules: only future appointments made incompatible by the change are cancelled, and the affected actions must be recorded in the audit trail.

Automated WhatsApp Appointment Service

The WhatsApp integration is an automated bot, not a manual appointment-processing workflow.

The bot must guide patients through supported operations, verify identity when required, collect the necessary appointment information, and invoke backend operations. The backend remains responsible for authorization, identity checks, business rules, availability validation, and database consistency.

A patient may have a patient record without a web account. However, a patient record alone does not authorize access to appointment information or protected operations.

The identity-verification process uses the patient's document number and a temporary verification code delivered to the previously registered and verified WhatsApp number. Verification codes must expire, have attempt limits, and never be stored in plaintext.

WhatsApp Data Model

The following entities support the automated workflow:

• whatsapp_verifications: temporary identity-verification records, expiration, verification status, and attempt limits.
• whatsapp_conversations: conversation state and activity timestamps.
• whatsapp_messages: message direction, provider message identifier, processing or delivery status, and timestamps.
• whatsapp_appointment_requests: requested appointment details and the request lifecycle.

Incoming provider events must be processed idempotently to prevent duplicate operations. Outgoing notifications and messages must support delivery-status tracking and controlled retries.

The bot must not transmit clinical history, diagnoses, prescriptions, or other unnecessary sensitive medical information through WhatsApp.

The production integration will use the WhatsApp Business Cloud API. A mock or simulator may be used during development and testing.

Security and Auditability

All appointment operations initiated by WhatsApp must pass through the same backend business rules used by the web application.

The system must record relevant appointment lifecycle changes, administrative actions, and security events without logging passwords, verification codes, access tokens, or unnecessary clinical information.