Full Database Schema

Engine: MySQL
Conventions:

    Snake case for table and field names

    All tables have id (INT, PK, AUTO_INCREMENT)

    All timestamps are created_at, updated_at where relevant

    Foreign keys not enforced (for flexibility in cPanel), but respected in code

🔐 users

Stores all system users: clients, branch admins, and head office admins.
Field	Type	Notes
id	INT	PK
name	VARCHAR(100)	Full name
phone	VARCHAR(20)	Unique, used for login
email	VARCHAR(100)	Optional
password	VARCHAR(255)	Hashed password
role	ENUM	client, branch_admin, admin
branch_id	INT	FK (nullable for admin)
status	ENUM	active, suspended, deleted
device_token	TEXT	For push notifications (Expo)
created_at	DATETIME	
🏢 branches

Funeral parlour branches. Used to scope users, clients, and policies.
Field	Type	Notes
id	INT	PK
name	VARCHAR(100)	
location	TEXT	Address or GPS
contact	TEXT	Phone/email/etc
created_at	DATETIME	
📄 policy_types

Admin-defined plans with pricing and member limits.
Field	Type	Notes
id	INT	PK
name	VARCHAR(100)	E.g., Family Plan
description	TEXT	Optional
premium	DECIMAL(10,2)	Monthly fixed amount
max_members	INT	Max dependents allowed
created_by	INT	FK to admin (user ID)
active	BOOLEAN	Default: TRUE
created_at	DATETIME	
📑 policies

Client-owned active policies.
Field	Type	Notes
id	INT	PK
policy_number	VARCHAR	Unique, auto-generated
user_id	INT	FK to users (client)
policy_type_id	INT	FK to policy_types
branch_id	INT	FK
start_date	DATE	Policy effective date
status	ENUM	active, suspended, archived
last_payment	DATE	Updated after payment
created_at	DATETIME	
👥 policy_members

Members attached to a policy (main, spouse, child, etc.)
Field	Type	Notes
id	INT	PK
policy_id	INT	FK
name	VARCHAR(100)	
type	ENUM	main, spouse, child, parent, extended
dob	DATE	
documents	TEXT	JSON list of uploaded file paths
created_at	DATETIME	
💳 payments

Payments made by clients, captured at branch level.
Field	Type	Notes
id	INT	PK
policy_id	INT	FK
branch_id	INT	FK
method	VARCHAR(50)	EFT, Cash, Debit Order
amount	DECIMAL(10,2)	Fixed per policy
paid_on	DATE	Date of payment
created_at	DATETIME	
⚰️ claims

Funeral claims submitted by client or branch.
Field	Type	Notes
id	INT	PK
policy_id	INT	FK
member_id	INT	FK to policy_members
submitted_by	ENUM	client, branch
status	ENUM	submitted, queued, approved, rejected, paid
notes	TEXT	Optional general notes
admin_notes	TEXT	Notes by admin only
created_at	DATETIME	
📎 claim_documents

Uploads for claims (death cert, ID, etc.)
Field	Type	Notes
id	INT	PK
claim_id	INT	FK
file_path	TEXT	Relative path in uploads
uploaded_by	INT	FK to user
created_at	DATETIME	
💵 claim_payouts

Approved claims + financial payout info.
Field	Type	Notes
id	INT	PK
claim_id	INT	FK
amount	DECIMAL(10,2)	Payout amount
paid_on	DATE	When payout was made
method	VARCHAR(50)	EFT, Cash, etc.
reference	VARCHAR(100)	Optional ref number
created_at	DATETIME	
📝 tickets

Internal query system for claims or policies.
Field	Type	Notes
id	INT	PK
subject	VARCHAR(255)	Short description
context_type	ENUM	policy, claim
context_id	INT	FK to policy or claim
opened_by	INT	FK to user
assigned_to	INT	FK to user (optional)
status	ENUM	open, resolved, closed
created_at	DATETIME	
💬 ticket_messages

Threaded messages inside a ticket.
Field	Type	Notes
id	INT	PK
ticket_id	INT	FK
sender_id	INT	FK to user
message	TEXT	Body of the reply
created_at	DATETIME	
✍️ signatures

Stores client signatures (as file reference).
Field	Type	Notes
id	INT	PK
user_id	INT	FK to users
context_type	ENUM	claim, policy
context_id	INT	ID of the related claim or policy
signature	TEXT	File path to base64 image or PNG
created_at	DATETIME	
🔔 notifications

Push or internal alerts sent to users.
Field	Type	Notes
id	INT	PK
user_id	INT	FK
title	VARCHAR(100)	
body	TEXT	Notification message
read_status	BOOLEAN	Default: FALSE
created_at	DATETIME	
🔑 user_tokens

Manages active login tokens per user.
Field	Type	Notes
id	INT	PK
user_id	INT	FK
token	TEXT	Secure hash/UUID
created_at	DATETIME	
✅ Summary of Relationships

users --< policies --< policy_members
policies --< payments
policies --< claims --< claim_documents
claims --< claim_payouts
claims|policies --< tickets --< ticket_messages
users --< signatures
users --< notifications