Mahalo Health Data Model

This page provides a comprehensive overview of the database schema used by the Mahalo Health Platform. Understanding this data model will help you better utilize the API endpoints.

ma_patients
id (PK)

Stores core patient identity and linkage to authentication and program assignment.

program_id (FK), project_id

Column	Type	Description	Key
id	UUID	Unique identifier for the patient	PK
auth_uid	UUID	Supabase Auth user ID
name	TEXT	Full name of the patient
email	TEXT	Email address of the patient
phone	TEXT	Contact number
program_id	UUID	Linked program/cohort this patient belongs to	FK → ma_programs.id
project_id	UUID	Mahalo project/tenant identifier
status	TEXT	Current status (e.g., active, archived)
created_at	TIMESTAMP	Record creation time

ma_programs
id (PK)

Defines program/cohort segmentation.

Column	Type	Description	Key
id	UUID	Unique program identifier	PK
name	TEXT	Program or cohort name
timezone	TEXT	Timezone for data consistency
project_id	UUID	Associated project/tenant
is_active	BOOLEAN	Whether the program is active
created_at	TIMESTAMP	Timestamp of program creation

ma_admins
id (PK)

Admin users of the platform.

role_id (FK)

Column	Type	Description	Key
id	UUID	Unique identifier for the admin	PK
auth_uid	UUID	Supabase Auth user ID
name	TEXT	Full name of the admin
email	TEXT	Email address
role_id	UUID	Linked RBAC role	FK → ma_roles.id
project_id	UUID	Tenant/project association
is_active	BOOLEAN	Admin status flag
created_at	TIMESTAMP	Account creation time

ma_roles
id (PK)

RBAC role definitions for Mahalo Admin users.

Column	Type	Description	Key
id	UUID	Unique identifier for the role	PK
name	TEXT	Name of the role
description	TEXT	Short description of the role's purpose
scope	TEXT	Scope of access (e.g., program, global)
project_id	UUID	Project to which the role belongs
created_at	TIMESTAMP	Timestamp of role creation

ma_role_permissions
id (PK)

Permissions assigned to RBAC roles for access control.

role_id (FK)

Column	Type	Description	Key
id	UUID	Unique identifier for the permission record	PK
role_id	UUID	Reference to the role	FK → ma_roles.id
resource	TEXT	Name of the resource (e.g., patients)
action	TEXT	Allowed action (read, write, update, delete)

ma_program_assignments
id (PK)

Maps admin users to specific programs for scoped access.

admin_id (FK), program_id (FK)

Column	Type	Description	Key
id	UUID	Unique ID	PK
admin_id	UUID	Linked admin user	FK → ma_admins.id
program_id	UUID	Program the admin is assigned to	FK → ma_programs.id

ma_daily_metrics
id (PK)

Stores wearable data (e.g., WHOOP) daily per patient.

patient_id (FK)

Column	Type	Description	Key
id	UUID	Record ID	PK
patient_id	UUID	Linked patient	FK → ma_patients.id
program_id	UUID	Linked program
project_id	UUID	Tenant/project
date	DATE	The date this data applies to
hrv_ms	FLOAT	Heart Rate Variability in ms
rhr_bpm	FLOAT	Resting Heart Rate in bpm
sleep_hrs	FLOAT	Sleep duration in hours
steps	INTEGER	Step count
recorded_at	TIMESTAMP	Timestamp of data capture

ma_blood_tests
id (PK)

Captures manual blood panel results per patient.

patient_id (FK)

Column	Type	Description	Key
id	UUID	Test ID	PK
patient_id	UUID	Patient reference	FK → ma_patients.id
test_name	TEXT	Name of the blood test
value	FLOAT	Test result value
unit	TEXT	Unit of measurement
reference_low	FLOAT	Lower bound of normal range
reference_high	FLOAT	Upper bound of normal range
color	TEXT	Color-coding (green/red)
recorded_at	TIMESTAMP	When the value was recorded

ma_recommendations
id (PK)

Stores clinician-patient recommendations by domain.

patient_id (FK), created_by (FK)

Column	Type	Description	Key
id	UUID	Recommendation ID	PK
patient_id	UUID	Patient linked to the recommendation	FK → ma_patients.id
rec_type	TEXT	Type (Physical Activity, Nutrition)
content	TEXT	The actual recommendation content
created_by	UUID	Admin or clinician who wrote it	FK → ma_admins.id
created_at	TIMESTAMP	Creation time

ma_appointments
id (PK)

Scheduling information for patient check-ins and calls.

patient_id (FK)

Column	Type	Description	Key
id	UUID	Appointment ID	PK
patient_id	UUID	Linked patient	FK → ma_patients.id
program_id	UUID	Program under which it was booked
project_id	UUID	Tenant/project ID
type	TEXT	Type (e.g., Consultation, Follow-up)
status	TEXT	Appointment status (confirmed, cancelled)
start_time	TIMESTAMP	Start timestamp
end_time	TIMESTAMP	End timestamp
timezone	TEXT	Timezone of the event
location	TEXT	Where it happens (e.g., Zoom, clinic)
notes	TEXT	Extra notes for context
patient_initiated	BOOLEAN	True if patient requested it

ma_message_threads
id (PK)

Container for threaded communication between admin and patient.

patient_id (FK)

Column	Type	Description	Key
id	UUID	Thread ID	PK
patient_id	UUID	Linked patient	FK → ma_patients.id
program_id	UUID	Patient's program
project_id	UUID	Tenant/project context
created_by	UUID	Admin who initiated the thread
created_at	TIMESTAMP	Thread creation time
last_updated	TIMESTAMP	Time of last message in thread

ma_messages
id (PK)

Individual messages in a thread.

thread_id (FK)

Column	Type	Description	Key
id	UUID	Message ID	PK
thread_id	UUID	Belongs to this thread	FK → ma_message_threads.id
sender_id	UUID	ID of sender (admin or patient)
sender_role	TEXT	'admin' or 'patient'
content	TEXT	Message content
status	TEXT	sent, delivered, read
created_at	TIMESTAMP	Timestamp of sending

ma_food_logs
id (PK)

Logs of meals or food intake per patient.

patient_id (FK)

Column	Type	Description	Key
id	UUID	Log ID	PK
patient_id	UUID	Linked patient	FK → ma_patients.id
program_id	UUID	Related program
project_id	UUID	Tenant/project ID
meal_type	TEXT	breakfast, lunch, dinner, snack
food_items	TEXT[]	List of food item names
logged_at	TIMESTAMP	When the patient ate
submitted_at	TIMESTAMP	When they logged it
source	TEXT	'manual', 'photo', etc.

ma_food_items
id (PK)

Database of nutritional items.

Column	Type	Description	Key
id	UUID	Food item ID	PK
name	TEXT	Food name
category	TEXT	e.g., Protein, Carb, Fat, Fruit
macros	JSONB	macronutrient breakdown (calories, fat)
micros	JSONB	micronutrient info (vitamins, minerals)
image_url	TEXT	Optional image of food

ma_food_menus
id (PK)

Predefined menus (e.g., diabetic diet) assigned to patients.

program_id (FK), diet_type_id (FK)

Column	Type	Description	Key
id	UUID	Menu ID	PK
program_id	UUID	Program it belongs to	FK → ma_programs.id
diet_type_id	UUID	Type of diet (e.g., keto, diabetic)	FK → ma_diet_types.id
title	TEXT	Title of the menu
description	TEXT	Description of what's included
items	UUID[]	List of `ma_food_items` IDs

ma_diet_types
id (PK)

Defines types of diets (e.g., diabetic, keto, low-carb).

Column	Type	Description	Key
id	UUID	Diet type ID	PK
name	TEXT	Human-readable name
description	TEXT	Description of the diet
project_id	UUID	Project/tenant context

ma_questionnaires
id (PK)

Templates for assessments (e.g., PHQ-9, custom symptoms).

created_by (FK)

Column	Type	Description	Key
id	UUID	Questionnaire template ID	PK
name	TEXT	Title of the questionnaire
description	TEXT	What this questionnaire assesses
type	TEXT	symptom, baseline, demographic
trigger_mode	TEXT	manual, scheduled
created_by	UUID	Admin who created this template	FK → ma_admins.id

ma_questions
id (PK)

Questions that belong to a questionnaire template.

questionnaire_id (FK)

Column	Type	Description	Key
id	UUID	Question ID	PK
questionnaire_id	UUID	Parent questionnaire ID	FK → ma_questionnaires.id
order_index	INT	Order of the question
label	TEXT	Text of the question
type	TEXT	Type (text, multiple_choice, scale)
options	TEXT[]	Answer options (if applicable)

ma_questionnaire_instances
id (PK)

Assigned versions of a questionnaire to patients.

questionnaire_id (FK), patient_id (FK)

Column	Type	Description	Key
id	UUID	Instance ID	PK
questionnaire_id	UUID	Template this is based on	FK → ma_questionnaires.id
patient_id	UUID	Who it's assigned to	FK → ma_patients.id
assigned_at	TIMESTAMP	When it was issued
due_date	TIMESTAMP	Optional due date
status	TEXT	new, completed, skipped

ma_responses
id (PK)

Answers to individual questions in a questionnaire.

instance_id (FK), question_id (FK)

Column	Type	Description	Key
id	UUID	Response ID	PK
instance_id	UUID	Linked questionnaire instance	FK → ma_questionnaire_instances.id
question_id	UUID	Which question this answers	FK → ma_questions.id
response	TEXT	Patient's answer
score	INT	Numeric scoring value (if applicable)

ma_notification_templates
id (PK)

Defines the type of messages to be sent (reminders, tips, alerts).

Column	Type	Description	Key
id	UUID	Template ID	PK
project_id	UUID	Project/tenant context
program_id	UUID	(Optional) Program-scoped template
event_type	TEXT	Trigger event (e.g., food_log_reminder)
channel	TEXT	in-app, push, email
message	TEXT	Content to be delivered
is_active	BOOLEAN	Whether the template is currently enabled

ma_notification_logs
id (PK)

Tracks delivery of notifications to patients.

template_id (FK), patient_id (FK)

Column	Type	Description	Key
id	UUID	Notification event ID	PK
template_id	UUID	Which template was used	FK → ma_notification_templates.id
patient_id	UUID	Recipient	FK → ma_patients.id
channel	TEXT	Where it was delivered (e.g., in-app)
message	TEXT	Rendered message content
delivery_status	TEXT	delivered, failed
delivery_time	TIMESTAMP	When it was sent
was_seen	BOOLEAN	If the user has seen the message

ma_patient_notification_settings
id (PK)

Allows patient-level opt-in/out for each notification type.

patient_id (FK)

Column	Type	Description	Key
id	UUID	Settings row ID	PK
patient_id	UUID	Linked patient	FK → ma_patients.id
event_type	TEXT	Notification type (e.g., food_log_reminder)
channel	TEXT	in-app, push, email
opted_out	BOOLEAN	True if user disabled this notification

ma_report_templates
id (PK)

Defines reusable formats for generating health reports.

Column	Type	Description	Key
id	UUID	Report template ID	PK
name	TEXT	Human-readable name
project_id	UUID	Project that owns the template
layout	JSONB	Structure for output (sections, order)
created_at	TIMESTAMP	Creation date

ma_generated_reports
id (PK)

Instance of a report generated for a patient.

report_template_id (FK), patient_id (FK)

Column	Type	Description	Key
id	UUID	Unique report ID	PK
report_template_id	UUID	Which template was used	FK → ma_report_templates.id
patient_id	UUID	Who the report was about	FK → ma_patients.id
generated_at	TIMESTAMP	When it was generated

ma_report_exports
id (PK)

Tracks how and when reports were shared/exported.

report_id (FK)

Column	Type	Description	Key
id	UUID	Export ID	PK
report_id	UUID	Linked report	FK → ma_generated_reports.id
format	TEXT	pdf, email, web
exported_at	TIMESTAMP	Time of export
recipient	TEXT	Email or user who downloaded

ma_project_settings
id (PK)

Stores global configuration per Mahalo tenant (e.g., feature toggles, billing tier).

Column	Type	Description	Key
id	UUID	Setting row ID	PK
project_id	UUID	Which tenant/project it belongs to
key	TEXT	Setting name (e.g., max_patients)
value	TEXT	Stringified setting value
created_at	TIMESTAMP	When it was created

ma_program_settings
id (PK)

Allows per-program overrides for default behavior (e.g., HRV threshold).

program_id (FK)

Column	Type	Description	Key
id	UUID	Row ID	PK
program_id	UUID	Linked program	FK → ma_programs.id
key	TEXT	Name of the setting
value	TEXT	Value of the setting
updated_at	TIMESTAMP	Last updated timestamp

ma_feature_flags
id (PK)

Toggles for features (used in rollout, pricing tiers, testing).

Column	Type	Description	Key
id	UUID	Feature flag ID	PK
project_id	UUID	Project this feature applies to
name	TEXT	Feature name (e.g., food_tracking)
enabled	BOOLEAN	True if active
created_at	TIMESTAMP	Timestamp of creation

ma_device_integrations
id (PK)

Tracks linked wearables (e.g., WHOOP, Oura) for patients.

patient_id (FK)

Column	Type	Description	Key
id	UUID	Row ID	PK
patient_id	UUID	Linked patient	FK → ma_patients.id
project_id	UUID	Linked project
provider	TEXT	whoop, oura, fitbit, etc.
access_token	TEXT	OAuth token for syncing
status	TEXT	linked, unlinked, error
last_synced	TIMESTAMP	Last sync timestamp

ma_users_audit
id (PK)

Logs actions taken by admin or patient (auditing, compliance).

Column	Type	Description	Key
id	UUID	Unique audit ID	PK
affected_user_id	UUID	User whose data was touched
actor_id	UUID	Who performed the action
actor_role	TEXT	admin or patient
action	TEXT	Action type (e.g., edit_recommendation)
resource	TEXT	Affected table/entity
timestamp	TIMESTAMP	When the action occurred
metadata	JSONB	Optional additional info

Mahalo Health Data Model

ma_patientsid (PK)

ma_programsid (PK)

ma_adminsid (PK)

ma_rolesid (PK)

ma_role_permissionsid (PK)

ma_program_assignmentsid (PK)

ma_daily_metricsid (PK)

ma_blood_testsid (PK)

ma_recommendationsid (PK)

ma_appointmentsid (PK)

ma_message_threadsid (PK)

ma_messagesid (PK)

ma_food_logsid (PK)

ma_food_itemsid (PK)

ma_food_menusid (PK)

ma_diet_typesid (PK)

ma_questionnairesid (PK)

ma_questionsid (PK)

ma_questionnaire_instancesid (PK)

ma_responsesid (PK)

ma_notification_templatesid (PK)

ma_notification_logsid (PK)

ma_patient_notification_settingsid (PK)

ma_report_templatesid (PK)

ma_generated_reportsid (PK)

ma_report_exportsid (PK)

ma_project_settingsid (PK)

ma_program_settingsid (PK)

ma_feature_flagsid (PK)

ma_device_integrationsid (PK)

ma_users_auditid (PK)

ma_patients
id (PK)

ma_programs
id (PK)

ma_admins
id (PK)

ma_roles
id (PK)

ma_role_permissions
id (PK)

ma_program_assignments
id (PK)

ma_daily_metrics
id (PK)

ma_blood_tests
id (PK)

ma_recommendations
id (PK)

ma_appointments
id (PK)

ma_message_threads
id (PK)

ma_messages
id (PK)

ma_food_logs
id (PK)

ma_food_items
id (PK)

ma_food_menus
id (PK)

ma_diet_types
id (PK)

ma_questionnaires
id (PK)

ma_questions
id (PK)

ma_questionnaire_instances
id (PK)

ma_responses
id (PK)

ma_notification_templates
id (PK)

ma_notification_logs
id (PK)

ma_patient_notification_settings
id (PK)

ma_report_templates
id (PK)

ma_generated_reports
id (PK)

ma_report_exports
id (PK)

ma_project_settings
id (PK)

ma_program_settings
id (PK)

ma_feature_flags
id (PK)

ma_device_integrations
id (PK)

ma_users_audit
id (PK)