Features

Patient Management

Admission & Discharge: Record a patient’s admission and discharge information.

Ward Transfer: Handle transfers between wards, including verifying the patient’s identity and the target ward’s type and availability.

Doctor & Team Management

Doctor Records: The system must store doctor / consultant details and track which doctors have treated which patients.

Team Management: Keep a record of the patients assigned to each team, including the team code and the responsible doctor’s name.

Lists & Reports

Patients in a Ward: Show each patient’s name and age for a ward selected by an administrator.

Patients by Team: For a selected team, list all patients under its care and the name of the ward each patient is in.

Data Recording & Removal

Treatment Records: Log every treatment a patient receives, including the treating doctor.

Discharge Handling: When a patient is discharged, remove all information related to that patient from this system—except for medical records, which are handled by another system.

After a ward transfer, the patient’s previous wards are not stored.

Database Design

Users (`users`)

Column	Type	Description
id	INTEGER PK	Unique user ID
username	TEXT	Username
password_hash	TEXT	Password hash
role	INTEGER	User role (patient / doctor)
patient_id	INTEGER FK → patients(id)	Patient ID (if the user is a patient)
doctor_id	INTEGER FK → doctors(id)	Doctor ID (if the user is a doctor)

Credentials (`credentials`)

Column	Type	Description
token	TEXT PK	Login token
expire_at	DATETIME	Expiration time
role	INTEGER	User role (patient / doctor)
patient_id	INTEGER FK → patients(id)	Patient ID (if the user is a patient)
doctor_id	INTEGER FK → doctors(id)	Doctor ID (if the user is a doctor)

Wards (`wards`)

Column	Type	Description
id	INTEGER PK	Ward ID
name	TEXT	Ward name
type	TEXT	Ward type (e.g., Internal Medicine, ICU)
total_capacity	INTEGER	Total number of beds
current_occupancy	INTEGER	Number of occupied beds

Doctors (`doctors`)

Column	Type	Description
id	INTEGER PK	Doctor ID
name	TEXT	Name
title	TEXT	Honorific / title
birth_date	DATE	Date of birth
gender	TEXT	Gender
phone	TEXT	Phone
email	TEXT	Email
grade	INTEGER	Grade / seniority

Teams (`teams`)

Column	Type	Description
id	INTEGER PK	Team ID
department	TEXT	Department
consultant_id	INTEGER FK → doctors(id)	Consultant (lead doctor) ID
is_admin_team	BOOLEAN	Whether this is an admin team

Doctor–Team Link (`doctor_team_roles`)

Column	Type	Description
doctor_id	INTEGER FK → doctors(id)	Doctor ID
team_id	INTEGER FK → teams(id)	Team ID

Patients (`patients`)

Column	Type	Description
id	INTEGER PK	Patient ID
name	TEXT	Name
title	TEXT	Honorific
birth_date	DATE	Date of birth
gender	TEXT	Gender
phone	TEXT	Phone
email	TEXT	Email
postcode	TEXT	Postcode
address	TEXT	Address

Admissions (`admissions`)

Column	Type	Description
id	INTEGER PK	Admission ID
appointment_id	INTEGER FK → appointments(id)	Related appointment ID
patient_id	INTEGER FK → patients(id)	Patient ID
ward_id	INTEGER FK → wards(id)	Current ward ID
team_id	INTEGER FK → teams(id)	Care team ID
consultant_id	INTEGER FK → doctors(id)	Lead consultant ID
admitted_at	DATETIME	Admission time
~~discharged_at~~	~~DATETIME~~	~~Discharge time (nullable)~~

Treatments (`treatments`)

Column	Type	Description
id	INTEGER PK	Record ID
appointment_id	INTEGER FK → appointments(id)	Related appointment
doctor_id	INTEGER FK → doctors(id)	Treating doctor ID
patient_id	INTEGER FK → patients(id)	Patient ID
treated_at	DATETIME	Treatment time
treat_info	TEXT	Treatment details

Appointments (`appointments`)

Column	Type	Description
id	INTEGER PK	Appointment ID
patient_id	INTEGER FK → patients(id)	Patient ID
category	TEXT	Category (exam / admission / follow-up, etc.)
appointment_time	DATETIME	Scheduled time
description	TEXT	Patient description
approved	BOOLEAN	Approved or not
feedback	TEXT (NULLABLE)	Feedback notes
assigned_team	INTEGER FK → teams(id) (NULLABLE)	Assigned team
discharged	BOOLEAN	Whether the patient has been discharged

Seed Data

Administrator and receptionist team

Administrator account

API Endpoints

General

Endpoint	Description
`/login`	Log in
`/register_patient`	Patient self-registration
`/get_my_info`	Retrieve own user information
`/change_password`	Change password
`/get_doctor_list`	Get list of doctors
`/get_ward_list`	Get list of wards
`/get_team_list`	Get list of medical teams
`/get_team_info`	Get members of a specific medical team

Patient

Endpoint	Description
`/patient/book`	Create an appointment
`/patient/appointments`	View own appointments
`/patient/edit_appointment`	Edit an appointment
`/patient/cancel_appointment`	Cancel an appointment
`/patient/get_my_ward`	Get the ward currently assigned

Receptionist (identified by `teams.department`)

Endpoint	Description
`/receptionist/appointments`	View all appointments
`/receptionist/process_appointment`	Assign an appointment
`/receptionist/check_in`	Handle patient check-in

Doctor

Endpoint	Description
`/doctor/appointments`	View all appointments for the doctor’s team
`/doctor/treat`	Submit a treatment record

Administrator

Endpoint	Description
`/admin/transfer_ward`	Transfer a patient to another ward
`/admin/patient_discharge`	Discharge a patient
`/admin/create_ward`	Create a ward
`/admin/edit_ward`	Edit ward information
`/admin/delete_ward`	Delete a ward
`/admin/create_doctor_team`	Create a doctor team
`/admin/delete_doctor_team`	Delete a doctor team
`/admin/register`	Manually register a doctor / receptionist
`/admin/edit_team_info`	Edit medical team members
`/admin/edit_doctor_info`	Edit doctor information
`/admin/edit_patient_info`	Edit patient information
`/admin/set_resign_doctor`	Mark doctor as resigned / reinstated
`/admin/reset_user_password`	Reset a user’s password
`/admin/get_patient_list`	Get list of patients

Data Output

Endpoint	Description
`/data/ward_patients`	List patients currently in a ward
`/data/team_patients`	List all patients under a team and the ward they are in
~~`/data/treatment_doctors`~~	~~All doctors who have treated a patient (via treatment records)~~ Unused

Features

Features#

Database Design#

Users (users)#

Credentials (credentials)#

Wards (wards)#

Doctors (doctors)#

Teams (teams)#

Doctor–Team Link (doctor_team_roles)#

Patients (patients)#

Admissions (admissions)#

Treatments (treatments)#

Appointments (appointments)#

Seed Data#

API Endpoints#

General#

Patient#

Receptionist (identified by teams.department)#

Doctor#

Administrator#

Data Output#

Features

Database Design

Users (`users`)

Credentials (`credentials`)

Wards (`wards`)

Doctors (`doctors`)

Teams (`teams`)

Doctor–Team Link (`doctor_team_roles`)

Patients (`patients`)

Admissions (`admissions`)

Treatments (`treatments`)

Appointments (`appointments`)

Seed Data

API Endpoints

General

Patient

Receptionist (identified by `teams.department`)

Doctor

Administrator

Data Output