Database Schema
Complete database schema and entity relationships for the MediPact platform.
Entity Relationship Diagram
Core Tables
PATIENTS
Stores patient identity and Hedera account information
| Column | Type | Description |
|---|---|---|
| upi | STRING (PK) | Universal Patient Identifier |
| hedera_account_id | STRING | Hedera account (0.0.xxxxx) for revenue |
| evm_address | STRING | EVM-compatible address (0x...) |
| payment_method | STRING | 'bank' or 'mobile_money' |
| bank_account_number | STRING (encrypted) | Encrypted bank account number |
| withdrawal_threshold_usd | DECIMAL | Auto-withdrawal threshold (default: $10.00) |
| auto_withdraw_enabled | BOOLEAN | Enable automatic withdrawals |
HOSPITALS
Hospital registration and verification
| Column | Type | Description |
|---|---|---|
| hospital_id | STRING (PK) | Unique hospital identifier |
| hedera_account_id | STRING | Hedera account for revenue |
| evm_address | STRING | EVM-compatible address (0x...) |
| payment_method | STRING | 'bank' or 'mobile_money' |
| bank_account_number | STRING (encrypted) | Encrypted bank account number |
| withdrawal_threshold_usd | DECIMAL | Auto-withdrawal threshold (default: $100.00) |
| verification_status | STRING | pending, verified, rejected |
RESEARCHERS
Researcher registration and access levels
| Column | Type | Description |
|---|---|---|
| researcher_id | STRING (PK) | Unique researcher identifier |
| hedera_account_id | STRING | Hedera account for payments |
| access_level | STRING | basic, verified, premium |
CONSENTS
Patient consent records linked to HCS and smart contracts
| Column | Type | Description |
|---|---|---|
| consent_id | STRING (PK) | Unique consent identifier |
| upi | STRING (FK) | Patient UPI reference |
| anonymous_patient_id | STRING | Anonymous ID (PID-001) |
| hcs_topic_id | STRING | HCS topic for consent proof |
| data_hash | STRING | Hash of anonymized dataset |
DATASETS
Marketplace datasets with HCS topic references
| Column | Type | Description |
|---|---|---|
| dataset_id | STRING (PK) | Unique dataset identifier |
| hospital_id | STRING (FK) | Hospital that created the dataset |
| consent_topic_id | STRING | HCS topic for consent verification |
| data_topic_id | STRING | HCS topic for data proof |
| price | DECIMAL(20,8) | Price in HBAR |
| price_usd | DECIMAL(10,2) | Price in USD |
| pricing_category | STRING | Pricing category (basic, condition, lab, etc.) |
WITHDRAWAL_HISTORY
Tracks all withdrawal requests and their status
| Column | Type | Description |
|---|---|---|
| id | INTEGER (PK) | Unique withdrawal ID |
| upi | STRING (FK) | Patient UPI (if patient withdrawal) |
| hospital_id | STRING (FK) | Hospital ID (if hospital withdrawal) |
| user_type | STRING | 'patient' or 'hospital' |
| amount_hbar | DECIMAL(20,8) | Withdrawal amount in HBAR |
| amount_usd | DECIMAL(10,2) | Withdrawal amount in USD |
| payment_method | STRING | 'bank' or 'mobile_money' |
| destination_account | STRING | Bank account or mobile money number (masked) |
| status | STRING | pending, processing, completed, failed |
| transaction_id | STRING | Fiat transfer transaction ID (if available) |
Key Relationships
Patient Identity (UPI)
The Universal Patient Identifier (UPI) enables cross-hospital patient identity linking. A patient can be registered at multiple hospitals, and all records are linked through the UPI.
Consent Tracking
Each consent record links a patient (via UPI) to an anonymous patient ID, HCS topic, and data hash. This enables verification of consent both on-chain (via ConsentManager contract) and off-chain (via database queries).
Hedera Integration
All entities that receive revenue (patients, hospitals) have Hedera account IDs stored in the database. Researchers also have Hedera accounts for making payments. HCS topic IDs are stored for verification purposes.