Embedded Files
Designed a Portal from end-to-end: backend relational database, and a frontend web interface
As part of my Databases coursework, my partner and I chose to construct a portal that supports both patients and clinicians at an urgent care.
The data is stored in a PostgreSQL relational database hosted on Google Cloud Platform. The web interface was implemented using Python, Flask, and HTML/CSS. This includes connecting to the server and setting up the app routing (backend), and customizing the rendered webpages (frontend).
The back-end process:
Step 1: Entity Relationship Diagram
First, we created an ER diagram to understand the workflow of the entities in our relational database. While this diagram presents the initial setup, our final portal includes several more entities—with tables such as after_visit_summary, appointment_history, and pcp_info. The underlined attributes in each entity represent their primary keys, as identifying feature(s).
Important relationships:
The patient and doctor entities are related via pcp. There is one pcp for every patient—however, each doctor can be responsible for multiple patients.
The medication and testing entities can only be viewed by patient. Doctors can add and alter medication and testing.
Both patient and doctor entities can schedule appointments for a particular timeslot and exam_room.
Real-world constraints:
If a patient is a minor, a guardian can make an account on his/her behalf and provide both their and the minor's information.
Patients can only view their own medication and testing results. They cannot modify the data.
Optional attributes for patients: insurance information, medical history, and diagnoses.
Step 2: Initial SQL schema
For the sake of simplicity, here's a quick preview of the main/most interesting tables!
create table patient
(patient_ID varchar(10),
name varchar(25) not null,
DOB datetime not null,
insurance varchar(30),
medical_history text,
is_guardian boolean,
diagnoses text,
primary key(patient_ID));
create table doctor
(doc_ID varchar(10),
license_num varchar(9),
job_title varchar(20) not null,
date_hired datetime not null,
degrees varchar(10) not null,
expertise text,
primary key(doc_ID, license_num));
create table exam_room
(patient_ID int,
location varchar(15) not null,
primary key(room_num));
check(room_num>0);
create table PCP
(patient_ID varchar(10),
doc_ID varchar(10),
license_num varchar(9),
primary key(patient_ID, doc_ID, license_num));
create table medication
(med_ID varchar(15),
med_name varchar(25),
license_num varchar(9),
pharmacy_addr varchar(100) not null,
refills_num int,
type varchar(20),
is_prescription boolean,
primary key(med_ID)
foreign key(license_num) references doctor
check(refills_num>=0));
create table testing
(test_ID varchar(15),
doc_id varchar(10),
lab_reports text,
shots_given varchar(200),
primary key(test_ID)
foreign key(doc_ID) references doctor);
Step 3: Adding data
After running our VM instance on the Google Cloud Platform, we manually added data into each table using SQL's INSERT INTO method. Below are a few examples of adding data into our database:
INSERT INTO patient(patient_ID, name, DOB, insurance, medical_history, is_guardian, diagnoses)
VALUES (123, 'Mona Jackson', '1989-12-02', NULL, 'Migraines', False, NULL);
This statement adds a row to the patient table, containing the following information:
Patient_ID: 123
Name: Mona Jackson
DOB: 1989-12-02
Insurance: None
Medical history: Migraines
Is a guardian: False
Diagnoses: None
INSERT INTO medication(med_ID, med_name, license_num, pharmacy_addr, refills_num, type, is_prescription)
VALUES (781, 'Ibuprofen', 78221, '79 Columbus Ave, New York NY 10027', 2, 'NSAID', False);
This statement adds a row to the medication table, containing the following information:
Med_ID: 781
Medicine mame: Ibuprofen
License number: 78221
Pharmacy Address: 79 Columbus Ave, New York NY 10027
Number of refills: 2
Type: NSAID
Is a prescription: False
Step 4: Testing Interesting Queries
We then created SQL queries to test the accuracy of our relational database: for example, needing to ensure a patient can only view his/her own tests and medications; allowing doctors to pull up all relevant information for a specific patient; and many more! See a few of our most interesting/challenging queries below:
SELECT d.doc_id, d.license_num, count(d.doc_id) AS prescription_meds, m.p_name AS medication, m.type
FROM doctor as d, medication as m
WHERE d.doc_id = m.doc_id AND d.license_num = m.license_num AND is_prescription = True
GROUP BY d.doc_id, d.license_num, m.p_name, m.type;
This query returns a table containing the doctor's information (ID, license number) and relevant medication information (number, name, and type) for all prescription medications. Having this table readily available to clinic staff is critical in real-world applications to track the distribution and use of prescription medication!
SELECT name, medical_history, summary
FROM patient AS p, after_visit_summary AS a
WHERE p.patient_id = a.patient_id;
This query returns a table with patient names, their medical histories, and after visit summaries. This information is useful to clinic doctors to determine when their patients need to follow up (listed in their after visit summaries) and if they have chronic conditions or new, relevant diagnoses in their medical history.
SELECT a.patient_id, appointments, summary
FROM after_visit_summary AS a, appointment_history AS h
WHERE a.patient_id = h.patient_id
ORDER BY a.patient_id;
This query returns a table with patient IDs, a list of all of their appointments, and the after visit summaries of those appointments. This is helpful for both the patients and clinic staff to check the diagnoses, prescribed medications, and follow-up information (all in the after visit summaries) for each appointment.
SELECT patient_id, patient_name, doc_name
FROM pcp_info
ORDER BY doc_id, patient_name;
This query returns a table containing the patient IDs, patient names, and their provider's name. This would be helpful for the clinic and patient to be able to see directly who their provider is—and for the clinic, in particular, to see how many patients each provider is treating. The resulting table is ordered by doctor name, and the patients listed for each doctor are sorted alphabetically.
Now we jump to server.py and setting up the app routing to create the HTML webpages and use the database we've built!
Step 5: App Routing
Using Flask to dynamically generate the HTML pages, we now specify the app routing and define the respective methods to set up the pages for the web application. The code below sets up the routing methods for the patient_portal and returning_patient pages.
For the patient_portal page, the function simply renders the patient_portal page, titled "Patient Portal." There is no POST method required here, as the patient is not required to input any values. Instead, the patient will be presented with two buttons: new_patient and returning_patient. Note: this function is essentially a GET method.
For the returning_patient page, the function requires both GET and POST methods. Here, the GET method renders the patient_returning page (setting the page title as "Returning Patient"). To initiate the POST method and successfully log in, the patient must input her name and date of birth. The POST method will then insert these values into the patient_login table, which keeps track of all patient logins (the clinic can track all patient logins for logistical purposes). The app then reroutes to the patient_portal_main page which is the main page of the patient portal.
Another critical task is scheduling. The function below sets up the app routing methods for the scheduling_add page, which is the scheduling page for returning patients.
Here, the GET method first accesses all columns from the doctor table and adds this data to the resulting rendered template. This allows patients viewing the scheduling page to see all of the doctor information and choose which doctor they would like to schedule a visit with—the data provided includes the doctor's name, license number, medical degrees, and expertise.
The POST method then allows the patient to input the required information in order to schedule a visit. These values are then inserted into the the schedule table and the patient is then redirected to the home page.
Now, we can take a look at the clinician side of the portal! The code below defines the app routing for the doctor_login and clinic_portal pages, which is set up similarly to that of the patient_portal and returning_patient pages (see the first code snippet in Step 5).
The first app route (for doctor_login) requires both a GET and POST method. The GET method (which is hit if the function enters the "else" clause) renders the staff_login page . The POST method is implemented if the staff member inputs her login_id, which is an ID unique to each member. These values are then insterted into the doc_login table, which keeps track of all staff logins. The staff member is then considered to be successfully logged in and is redirected to the clinic_portal page.
This clinic_portal page is rendered via the second app route below. This function (which is essentially a GET method) renders the clinic_portal page, titled "Clinic Portal." Here, the staff member will be presented with a series of buttons to access different pages, such as Patient Files, Doctor Files, PCP Files, Patient Medications, etc.
This page is a work in progress. Stay tuned for updates!
Coming next: customizing the HTML pages. See section below for a preview! The templates can be found here.
Step 6: Customizing Webpages
This HTML code customizes the clinic_portal page, setting the main header of the page to "Columbia Student Clinic."
The remainder of the code sets up the "Patient and Doctor Info" section and allows a staff member to click on four buttons which reroute to their respective pages. These buttons (see Step 5 above), reroute the staff memeber to: Patient Files, Doctor Files, PCP Files, Patient Medications.
Page updated
Google Sites
Report abuse