Published on

Meditrack

Authors
  • avatar
    Name
    Austin Sternberg
    Twitter

Overview

MediTrack is a comprehensive patient & appointment management system I built as part of an Intro to Databases group project. The goal was to create a production-ready medical management platform with real-world workflows including patient scheduling, billing, insurance claims, and role-based access control.

What it does

Built a complete healthcare management system with five distinct user roles:

  • Patients: Schedule appointments, view bills, manage insurance, update health demographics
  • Doctors: View appointments, update patient health records, complete visits and generate bills
  • Billing Staff: Process payment queue, manage billing records, handle insurance claims
  • Insurance Providers: Review and approve/deny claims, track coverage
  • Admins: Manage all users, hospitals, generate system reports

Key features

Patient Portal

  • Schedule appointments by selecting hospital, doctor, date and time
  • Dynamic time slot availability based on doctor schedules
  • View and cancel upcoming appointments
  • Track billing and insurance coverage

Clinical Workflows

  • Doctors manage hospital assignments
  • Complete appointments with notes
  • Update patient health demographics (weight, height, status)
  • Auto-generate billing entries on appointment completion

Billing & Insurance

  • Three-stage bill processing: Queued → Insurance → Paid
  • Insurance claim submission and approval workflow
  • Payment tracking with coverage calculations
  • Filtering and search across billing records

Admin Dashboard

  • Comprehensive reporting (hospitals, doctors, patients, billing, insurance)
  • User management with role-based access
  • Hospital and staff creation

Tech stack

  • Backend: Flask (Python) with blueprint-based routing
  • Database: MySQL with 3NF normalized schema
  • Auth: bcrypt password hashing, session-based authentication
  • Frontend: Bootstrap 5, server-rendered templates
  • Features: Role-based access control, relational data integrity

Database design

Schema in 3NF with key tables:

  • users (auth + role FK)
  • patient, doctor, billing_cost, insurance_provider
  • appointment (ties patient + doctor + hospital)
  • patient_billing (links appointments to bills)
  • health_demographics, patient_insurance

Full ER diagram and example queries included in the sql/ folder.

Sample query

Dynamic time slot generation for appointment scheduling:

# Fetch hospital operating hours
query = """
    SELECT operating_hours FROM hospital
    WHERE hospital_id = %s
"""

# Generate 30-minute slots excluding booked times
query = """
    SELECT time FROM appointment
    WHERE doctor_id = %s AND date = %s
"""
# Filter available slots and return to frontend

Running locally

# Install dependencies
pip install -r requirements.txt

# Run Flask app
python ./app.py

By default connects to a Kent State network DB. For local XAMPP/MySQL, update app.py:

app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'root'
app.config['MYSQL_PASSWORD'] = ''
app.config['MYSQL_DB'] = 'meditrack'

Import sql/meditrack & data.sql for example data.

Challenges & learnings

Data modeling: Designing a schema that handles appointments, billing, and insurance relationships without redundancy required careful normalization and foreign key planning.

Role-based routing: Implemented session-based auth with role checking on every route. Learned to balance security with user experience.

Dynamic scheduling: Building a real-time appointment scheduler that accounts for doctor availability, hospital hours, and existing bookings was more complex than expected—required careful time zone handling and 12hr/24hr format conversions.

Multi-stage workflows: Billing lifecycle (Queued → Insurance → Paid) taught me about state management in database-driven apps.

Next steps

  • Add email notifications for appointments and billing
  • Implement prescription/medication tracking
  • Add calendar view for appointment scheduling
  • Build REST API for mobile app integration
  • Add unit tests for critical workflows

Check out the full project at github.com/EnderHubris/MediTrack.

View on GitHub