Engineering Hemoconnect: High-Reliability PostgreSQL Schemas & REST APIs
backendpostgresqlapinodejshealthcare

Engineering Hemoconnect: High-Reliability PostgreSQL Schemas & REST APIs

How we built the backend system logic and relational database structure for Hemoconnect, a critical blood donation connection platform.

Engineering Hemoconnect: High-Reliability PostgreSQL Schemas & REST APIs

When building consumer apps or e-commerce storefronts, high availability is desirable. When developing a healthcare connection platform like Hemoconnect, reliability is non-negotiable.

Hemoconnect was conceived to bridge the urgent gap between individuals requiring emergency blood transfusions, eligible volunteer donors, and nearby clinical centers. In 2024, I served as the Backend Developer on the project, responsible for structuring the database models, crafting RESTful API endpoints, and guaranteeing transactional integrity.


Technical Challenges & Architecture

Connecting donors with recipients under time pressure introduces complex requirements:

  1. Donor Compatibility Matching: Calculating blood type compatibility matrices (e.g., O-negative universal donors, AB-positive universal recipients) quickly and deterministically.
  2. Geographical & Urgency Filtering: Sorting requests by urgency tier (Critical, Urgent, Standard) and proximity.
  3. Data Privacy & Contact Integrity: Safeguarding sensitive medical donor details while allowing verified communication channels during emergencies.

Relational Schema Design with PostgreSQL

We chose PostgreSQL for its strict ACID compliance, robust indexing capabilities, and expressive constraint mechanisms.

Core Tables & Relationships:

  • users: Authentication credentials, verified identity status, role flags (Donor, Requester, Hospital Admin).
  • donor_profiles: Blood group, donation eligibility timestamps, location coordinates, availability toggle.
  • blood_requests: Required units, blood type, urgency level, patient clinical reference, hospital verification status.
  • matches: Relational join entity tracking connection requests, status progression (Pending, Accepted, Completed), and timestamped audit logs.
CREATE TABLE blood_requests (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    requester_id UUID REFERENCES users(id) ON DELETE CASCADE,
    blood_group VARCHAR(5) NOT NULL,
    urgency_level VARCHAR(20) CHECK (urgency_level IN ('CRITICAL', 'URGENT', 'STANDARD')),
    units_needed INT NOT NULL CHECK (units_needed > 0),
    hospital_name VARCHAR(255) NOT NULL,
    city VARCHAR(100) NOT NULL,
    status VARCHAR(20) DEFAULT 'ACTIVE' CHECK (status IN ('ACTIVE', 'FULFILLED', 'EXPIRED')),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_blood_requests_active ON blood_requests (blood_group, city) WHERE status = 'ACTIVE';

Using filtered partial indexes like idx_blood_requests_active allowed our matching queries to execute in sub-millisecond times even under simulated load testing.


API Layer & Postman Test Automation

Built with Node.js and Express.js, the backend exposes decoupled RESTful resources:

  • Dedicated middleware verified JWT tokens and rate-limited sensitive request endpoints to prevent spam.
  • Validation pipelines ensured that invalid blood types or malformed phone formats were rejected prior to touching the database.
  • A comprehensive Postman collection was authored to automate regression testing across dozens of scenario tests (e.g., duplicate request submissions, expiring donation holds).

Final Thoughts

Working on Hemoconnect reinforced how software engineering can have a direct, positive impact on human lives. Writing clean, documented, and resilient backend code is not just a best practice—it is an obligation to the people who rely on our software when every second counts.