🏥 Medical System Database Overview

Comprehensive view of all data storage systems in the medical project

Summary
SQLite Databases
CSV Files
JSON Files
Medication Data
Integration
7
SQLite Databases
86
CSV Data Files
12
JSON Config Files
557K
Glucose Readings

🎯 Data Organization Strategy

Current Status: The medication data exists in static HTML files (Emergency Medical French.html, Emergency Medical Information.html) but should be migrated to database storage for dynamic updates.

🔄 Recommended Migration Path

Extract medication tables from HTML
→
Store in christina_medical_knowledge.db
→
Update HTML to read from database

medical_words.db

SQLite

Purpose: Real-time voice logging (insulin, medications, symptoms, todos)

5
Tables
51
Medical Words
medical_words 51 rows
glucose_words 4 rows
sync_history 22 rows
bot_settings 1 rows
pending_confirmations 0 rows

clarity_clean.db

SQLite

Purpose: Processed Dexcom glucose data from Clarity exports

3
Tables
557K
Glucose Readings
glucose_readings 557,048 rows
calibrations 709 rows
device_info 112 rows

christina_medical_knowledge.db

SQLite

Purpose: Comprehensive medical knowledge base (EMPTY - ready for medication migration)

7
Tables
0
Medications
medications 0 rows - READY FOR DATA
allergies 0 rows
conditions 0 rows
doctors 0 rows
emergency_contacts 0 rows
lab_results 0 rows
documents 4 rows

medical_history.db

SQLite

Purpose: Medical events and appointments from PKB sync

2
Tables
63
Medical Events
medical_events 63 rows
cardiac_tests 0 rows

symptom_diary.db

SQLite

Purpose: Symptom tracking and pattern analysis (EMPTY - ready for data)

3
Tables
0
Symptoms
symptoms 0 rows
symptom_patterns 0 rows
symptom_correlations 0 rows

document_index.db

SQLite

Purpose: Medical document indexing and search

1
Tables
2
Documents
documents 2 rows

simple_docs.db

SQLite

Purpose: Simple document storage (EMPTY)

1
Tables
0
Documents
documents 0 rows

🩸 Blood Test Data (80+ Individual Marker Files)

Each blood marker has its own CSV file for time-series tracking:

dexcom_glucose_readings.csv

CSV

Purpose: Real-time glucose monitoring from Dexcom API

Structure: Timestamp, Timezone_Abbr, Value_mmol, Trend, Seconds_Between_Readings

bp_complete_dataset_fixed.csv

CSV

Purpose: Complete blood pressure history

Structure: Source, Date, Heart rate, Systolic, Diastolic

blood_c-reactive_protein.csv

CSV

Purpose: CRP inflammation marker tracking

Note: One of 80+ individual blood marker CSV files

blood_creatinine.csv

CSV

Purpose: Kidney function marker tracking

Note: Critical for medical correlation analysis

📋 Blood Marker Categories

  • Kidney Function: creatinine, eGFR, urea, albumin
  • Cardiac Markers: BNP, NT-pro BNP, troponin
  • Inflammation: CRP, lymphocytes, neutrophils
  • Metabolic: glucose, HbA1c, cholesterol, triglycerides
  • Immunology: IgG subclasses, ANCA, complement
  • Vitamins/Minerals: Vitamin D, calcium, magnesium

pkb_sync_database.json

JSON

Purpose: PatientsKnowBest synchronization state

Usage: Tracks last sync dates, processed appointments

pkb_appointments.json

JSON

Purpose: Raw appointment data from PKB portal

Usage: Intermediate storage before processing

google-credentials.json

JSON

Purpose: Google Cloud service account credentials

Usage: Speech-to-text, Calendar integration

latest_glucose_word.json

JSON

Purpose: Cache for current glucose status

Usage: Quick access for web interface

g7_overlap_periods.json

JSON

Purpose: G6/G7 sensor transition analysis

Usage: Gap detection and calibration tracking

process_status.json

JSON

Purpose: System health monitoring

Usage: Process Manager status tracking

💊 Current Medication Data Status

Problem: Medication data is hardcoded in static HTML files, making updates manual and error-prone.

Files with medication tables:

  • Emergency Medical French.html - French emergency card
  • Emergency Medical Information.html - English emergency card

🎯 Proposed Migration Strategy

Step Action Target Database Priority
1 Extract medication tables from HTML files christina_medical_knowledge.db High
2 Create medication management API endpoints Flask application High
3 Update HTML files to read from database Template system Medium
4 Add web interface for medication management New HTML template Low

🗄️ christina_medical_knowledge.db Schema

The medications table is already designed and ready:

medications table:
  - id (INTEGER, PRIMARY KEY)
  - document_id (INTEGER, Foreign Key)
  - medication_name (TEXT) - e.g., "Novorapid insulin"
  - dose (TEXT) - e.g., "Variable units"
  - frequency (TEXT) - e.g., "With meals"
  - timing (TEXT) - e.g., "Before eating"
  - route (TEXT) - e.g., "Subcutaneous injection"
  - essential (BOOLEAN) - Critical medications
  - start_date (DATE)
  - stop_date (DATE)

🔄 Data Integration Architecture

Telegram Voice
→
medical_words.db
→
Web Interface
Dexcom API
→
CSV Files
→
clarity_clean.db
PKB Portal
→
JSON Cache
→
medical_history.db

🎯 Future Integration Opportunities

  • VictoriaMetrics: Time series database for high-performance analytics
  • Google Sheets: External sharing and collaboration
  • Home Assistant: Location tracking for timezone automation
  • Document Processing: AI extraction from medical PDFs

📊 Data Quality Priorities

High Priority

  • medical_words.db - Real-time voice logging
  • clarity_clean.db - Glucose continuity
  • Medication database migration

Medium Priority

  • Blood marker CSV consolidation
  • PKB sync reliability
  • Document indexing expansion

Future Development

  • Symptom pattern analysis
  • Predictive health analytics
  • Cross-correlation studies