Model electronic medical records with temporal versioning. Store patient histories, lab results, and medication logs with time-ordered clinical data.

Note
 This tutorial assumes you have a running 3-node Ferrosa cluster. If you have not set one up yet, follow the cluster-setup guide first — it only takes about 5 minutes.

Create the Keyspace

Every Ferrosa project starts with a keyspace. Think of it as a namespace that groups your tables together and tells the cluster how many copies of your data to keep. For healthcare data, we definitely want redundancy.

Connect to your cluster and run:

CREATE KEYSPACE IF NOT EXISTS healthcare
  WITH replication = {
    'class': 'SimpleStrategy',
    'replication_factor': 3
  };

With a replication factor of 3, every piece of patient data is stored on all three nodes. If one node goes down, your records are still fully available. That is exactly the kind of reliability you want for medical data.

Design the Schema

We will create four tables that work together to model a patient’s medical record. Each table is designed around the queries you will run most often.

Patients

The patients table stores demographic information. Each patient gets a unique UUID, and we use a set for allergies since a patient can have zero or many.

CREATE TABLE IF NOT EXISTS patients (
    patient_id      uuid PRIMARY KEY,
    name            text,
    dob             date,
    blood_type      text,
    allergies       set<text>,
    emergency_contact text
);

Clinical Events

Clinical events form the patient’s timeline: admissions, discharges, procedures, consultations. We cluster by event_time in descending order so the most recent events come back first — exactly what a nurse or doctor needs.

CREATE TABLE IF NOT EXISTS clinical_events (
    patient_id  uuid,
    event_time  timestamp,
    event_id    uuid,
    event_type  text,
    department  text,
    provider    text,
    notes       text,
    PRIMARY KEY (patient_id, event_time, event_id)
) WITH CLUSTERING ORDER BY (event_time DESC, event_id ASC);

Lab Results

Lab results are keyed by patient and collection time. The abnormal flag makes it easy to filter for results that need attention.

    patient_id      uuid,
    collected_at    timestamp,
    test_name       text,
    result_value    text,
    unit            text,
    reference_range text,
    abnormal        boolean,
    PRIMARY KEY (patient_id, collected_at)
) WITH CLUSTERING ORDER BY (collected_at DESC);

Medications

The medications table tracks current and past prescriptions. We partition by patient and cluster by medication name so you can look up any specific drug quickly.

    patient_id    uuid,
    prescribed_at timestamp,
    medication    text,
    dosage        text,
    frequency     text,
    prescriber    text,
    active        boolean,
    PRIMARY KEY (patient_id, medication)
);

That is four tables, each optimized for a different access pattern.

Register Patients

Let’s add three patients. Notice how we use uuid() to generate unique identifiers and curly braces for the set of allergies.

-- ── Patients ──

INSERT INTO patients (patient_id, name, dob, blood_type, allergies, emergency_contact)
VALUES (11111111-1111-1111-1111-111111111111,
        'Maria Chen', '1985-03-14', 'A+',
        {'penicillin', 'sulfa'}, 'James Chen (spouse) 555-0142');

INSERT INTO patients (patient_id, name, dob, blood_type, allergies, emergency_contact)
VALUES (22222222-2222-2222-2222-222222222222,
        'Robert Okafor', '1972-11-28', 'O-',
        {'latex'}, 'Amara Okafor (daughter) 555-0198');

INSERT INTO patients (patient_id, name, dob, blood_type, allergies, emergency_contact)
VALUES (33333333-3333-3333-3333-333333333333,
        'Priya Sharma', '1998-07-02', 'B+',

Priya has no known allergies, so we pass an empty set {}. Ferrosa stores that efficiently — no wasted space.

Record Clinical Events

Now let’s build out a realistic timeline. We will add events across multiple departments for each patient. Each event captures who, what, where, and when.

-- ── Clinical Events ──

-- Maria Chen: ER visit and follow-up
INSERT INTO clinical_events (patient_id, event_time, event_id, event_type, department, provider, notes)
VALUES (11111111-1111-1111-1111-111111111111, '2026-03-10 08:15:00', uuid(), 'admission', 'Emergency', 'Dr. Williams', 'Patient presents with acute abdominal pain, onset 6 hours ago');

INSERT INTO clinical_events (patient_id, event_time, event_id, event_type, department, provider, notes)
VALUES (11111111-1111-1111-1111-111111111111, '2026-03-10 09:30:00', uuid(), 'imaging', 'Radiology', 'Dr. Park', 'CT abdomen with contrast ordered, pending penicillin allergy review');

INSERT INTO clinical_events (patient_id, event_time, event_id, event_type, department, provider, notes)
VALUES (11111111-1111-1111-1111-111111111111, '2026-03-10 11:00:00', uuid(), 'procedure', 'Surgery', 'Dr. Reyes', 'Laparoscopic appendectomy performed without complications');

INSERT INTO clinical_events (patient_id, event_time, event_id, event_type, department, provider, notes)
VALUES (11111111-1111-1111-1111-111111111111, '2026-03-11 07:00:00', uuid(), 'discharge', 'Surgery', 'Dr. Reyes', 'Discharged in stable condition, follow-up in 2 weeks');

INSERT INTO clinical_events (patient_id, event_time, event_id, event_type, department, provider, notes)
VALUES (11111111-1111-1111-1111-111111111111, '2026-03-15 14:00:00', uuid(), 'consultation', 'Primary Care', 'Dr. Nguyen', 'Post-op follow-up, incision healing well, no signs of infection');

-- Robert Okafor: cardiology workup
INSERT INTO clinical_events (patient_id, event_time, event_id, event_type, department, provider, notes)
VALUES (22222222-2222-2222-2222-222222222222, '2026-03-08 10:00:00', uuid(), 'consultation', 'Cardiology', 'Dr. Adams', 'Annual cardiac evaluation, patient reports occasional palpitations');

INSERT INTO clinical_events (patient_id, event_time, event_id, event_type, department, provider, notes)
VALUES (22222222-2222-2222-2222-222222222222, '2026-03-08 11:30:00', uuid(), 'diagnostic', 'Cardiology', 'Tech. Rivera', '12-lead ECG performed, sinus rhythm, no acute changes');

INSERT INTO clinical_events (patient_id, event_time, event_id, event_type, department, provider, notes)
VALUES (22222222-2222-2222-2222-222222222222, '2026-03-09 08:00:00', uuid(), 'diagnostic', 'Cardiology', 'Tech. Rivera', '24-hour Holter monitor placed');

INSERT INTO clinical_events (patient_id, event_time, event_id, event_type, department, provider, notes)
VALUES (22222222-2222-2222-2222-222222222222, '2026-03-12 15:00:00', uuid(), 'consultation', 'Cardiology', 'Dr. Adams', 'Holter results normal, palpitations likely benign, follow-up in 6 months');

-- Priya Sharma: sports medicine
INSERT INTO clinical_events (patient_id, event_time, event_id, event_type, department, provider, notes)
VALUES (33333333-3333-3333-3333-333333333333, '2026-03-05 16:45:00', uuid(), 'admission', 'Emergency', 'Dr. Kim', 'Right ankle injury during soccer match, significant swelling');

INSERT INTO clinical_events (patient_id, event_time, event_id, event_type, department, provider, notes)
VALUES (33333333-3333-3333-3333-333333333333, '2026-03-05 17:30:00', uuid(), 'imaging', 'Radiology', 'Dr. Park', 'X-ray right ankle: no fracture, grade 2 lateral sprain');

INSERT INTO clinical_events (patient_id, event_time, event_id, event_type, department, provider, notes)
VALUES (33333333-3333-3333-3333-333333333333, '2026-03-05 18:15:00', uuid(), 'discharge', 'Emergency', 'Dr. Kim', 'RICE protocol, air cast fitted, follow-up with sports medicine');

INSERT INTO clinical_events (patient_id, event_time, event_id, event_type, department, provider, notes)
VALUES (33333333-3333-3333-3333-333333333333, '2026-03-12 09:00:00', uuid(), 'consultation', 'Sports Medicine', 'Dr. Torres', 'Swelling reduced, starting physical therapy, 4-6 week recovery');

That is 15 clinical events across three patients. Notice how each patient’s events tell a coherent medical story — this is exactly how real EMR data looks.

Store Lab Results

Lab results are the backbone of clinical decision-making. Let’s add a realistic panel for each patient.

INSERT INTO clinical_events (patient_id, event_time, event_id, event_type, department, provider, notes)
VALUES (33333333-3333-3333-3333-333333333333, '2026-03-17 09:00:00', uuid(), 'therapy', 'Physical Therapy', 'PT. Marshall', 'First PT session, range of motion exercises, patient tolerating well');

-- ── Lab Results ──

-- Maria Chen: pre-surgical and post-op labs
INSERT INTO lab_results (patient_id, collected_at, test_name, result_value, unit, reference_range, abnormal)
VALUES (11111111-1111-1111-1111-111111111111, '2026-03-10 08:30:00', 'WBC', '14.2', 'x10^9/L', '4.5-11.0', true);

INSERT INTO lab_results (patient_id, collected_at, test_name, result_value, unit, reference_range, abnormal)
VALUES (11111111-1111-1111-1111-111111111111, '2026-03-10 08:30:01', 'Hemoglobin', '13.8', 'g/dL', '12.0-16.0', false);

INSERT INTO lab_results (patient_id, collected_at, test_name, result_value, unit, reference_range, abnormal)
VALUES (11111111-1111-1111-1111-111111111111, '2026-03-10 08:30:02', 'CRP', '48.5', 'mg/L', '0-10', true);

INSERT INTO lab_results (patient_id, collected_at, test_name, result_value, unit, reference_range, abnormal)
VALUES (11111111-1111-1111-1111-111111111111, '2026-03-11 06:00:00', 'WBC', '9.1', 'x10^9/L', '4.5-11.0', false);

-- Robert Okafor: cardiac panel
INSERT INTO lab_results (patient_id, collected_at, test_name, result_value, unit, reference_range, abnormal)
VALUES (22222222-2222-2222-2222-222222222222, '2026-03-08 10:15:00', 'Total Cholesterol', '242', 'mg/dL', '<200', true);

INSERT INTO lab_results (patient_id, collected_at, test_name, result_value, unit, reference_range, abnormal)
VALUES (22222222-2222-2222-2222-222222222222, '2026-03-08 10:15:01', 'LDL', '158', 'mg/dL', '<100', true);

INSERT INTO lab_results (patient_id, collected_at, test_name, result_value, unit, reference_range, abnormal)
VALUES (22222222-2222-2222-2222-222222222222, '2026-03-08 10:15:02', 'Troponin I', '0.02', 'ng/mL', '<0.04', false);

-- Priya Sharma: routine bloods
INSERT INTO lab_results (patient_id, collected_at, test_name, result_value, unit, reference_range, abnormal)

Notice Maria’s elevated WBC and CRP on admission — classic markers for appendicitis. Her post-op WBC came back to normal. That is the kind of trend these time-ordered tables make easy to spot.

Track Medications

Let’s record current and past prescriptions. The active column lets you quickly filter for what a patient is taking right now.

INSERT INTO lab_results (patient_id, collected_at, test_name, result_value, unit, reference_range, abnormal)
VALUES (33333333-3333-3333-3333-333333333333, '2026-03-05 17:00:01', 'Platelets', '285', 'x10^9/L', '150-400', false);

INSERT INTO lab_results (patient_id, collected_at, test_name, result_value, unit, reference_range, abnormal)
VALUES (33333333-3333-3333-3333-333333333333, '2026-03-05 17:00:02', 'D-Dimer', '0.3', 'mg/L', '<0.5', false);

-- ── Medications ──

-- Maria Chen: post-surgical meds
INSERT INTO medications (patient_id, prescribed_at, medication, dosage, frequency, prescriber, active)
VALUES (11111111-1111-1111-1111-111111111111, '2026-03-11 07:00:00', 'Acetaminophen', '500mg', 'every 6 hours', 'Dr. Reyes', true);

INSERT INTO medications (patient_id, prescribed_at, medication, dosage, frequency, prescriber, active)
VALUES (11111111-1111-1111-1111-111111111111, '2026-03-11 07:00:00', 'Cephalexin', '500mg', 'twice daily', 'Dr. Reyes', true);

INSERT INTO medications (patient_id, prescribed_at, medication, dosage, frequency, prescriber, active)
VALUES (11111111-1111-1111-1111-111111111111, '2026-03-10 09:00:00', 'Morphine IV', '4mg', 'as needed', 'Dr. Williams', false);

-- Robert Okafor: cardiac meds
INSERT INTO medications (patient_id, prescribed_at, medication, dosage, frequency, prescriber, active)
VALUES (22222222-2222-2222-2222-222222222222, '2026-03-08 12:00:00', 'Atorvastatin', '20mg', 'once daily', 'Dr. Adams', true);

INSERT INTO medications (patient_id, prescribed_at, medication, dosage, frequency, prescriber, active)

Notice that Maria is on Cephalexin (a cephalosporin) instead of penicillin-based antibiotics — her allergy record in the patients table flagged that. In a real system, you would enforce this with application-level checks.

Query Clinical Data

Now for the rewarding part. Let’s run the kinds of queries that doctors, nurses, and analysts use every day.

Patient Timeline

Pull up everything that happened to a patient, most recent first. Because we used CLUSTERING ORDER BY (event_time DESC), this is extremely fast.

-- Full clinical timeline for Maria Chen
SELECT event_time, event_type, department, provider, notes
FROM clinical_events
WHERE patient_id = 11111111-1111-1111-1111-111111111111;

Recent Lab Results

Get the most recent labs for a patient. The LIMIT clause keeps it manageable.

-- Last 5 lab results for Robert Okafor
SELECT collected_at, test_name, result_value, unit, reference_range, abnormal
FROM lab_results
WHERE patient_id = 22222222-2222-2222-2222-222222222222
LIMIT 5;

Active Medications

What is a patient currently taking? Filter by active = true to get only current prescriptions.

-- Current medications for Maria Chen
SELECT medication, dosage, frequency, prescriber
FROM medications
WHERE patient_id = 11111111-1111-1111-1111-111111111111
  AND active = true
Important
 About ALLOW FILTERING: We need this because active is not part of the primary key. In production, you might create a separate active_medications table to avoid filtering. For now, with small datasets, it works just fine.

Abnormal Lab Results

Quickly find results that are outside the normal range — the ones that need a doctor’s attention.

-- All abnormal results for Maria Chen
SELECT collected_at, test_name, result_value, unit, reference_range
FROM lab_results
WHERE patient_id = 11111111-1111-1111-1111-111111111111
  AND abnormal = true

Clinical Events by Type

Filter the timeline to just procedures, just imaging, or any other event type you need.

-- All imaging events for Priya Sharma
SELECT event_time, provider, notes
FROM clinical_events
WHERE patient_id = 33333333-3333-3333-3333-333333333333

Patient Lookup

Sometimes the simplest query is the most important one. When a patient arrives, the first thing staff need is their demographic information and allergy list.

ALLOW FILTERING;

-- Look up Maria Chen's full profile
SELECT name, dob, blood_type, allergies, emergency_contact

This returns instantly because patient_id is the partition key. No matter how many millions of patients your system holds, this lookup hits exactly one partition on one node.

Time-Bounded Event Search

Pull events from a specific date range. This is useful for generating discharge summaries or insurance claim documentation.

WHERE patient_id = 11111111-1111-1111-1111-111111111111;

-- Maria's clinical events from March 10-11 (hospital stay)
SELECT event_time, event_type, department, provider, notes
FROM clinical_events
WHERE patient_id = 11111111-1111-1111-1111-111111111111

Because event_time is a clustering column, Ferrosa can efficiently seek to the start of the range and scan forward. No full-partition scan needed.

All Medications for a Patient

For a complete medication reconciliation — both active and discontinued — simply omit the filter on active.

  AND event_time <= '2026-03-11 23:59:59';

-- Full medication history for Priya Sharma
SELECT medication, dosage, frequency, prescriber, active, prescribed_at
Tip
 Schema design insight: Notice how each table serves a different audience. The patients table is for registration desks. The clinical_events table is for doctors reviewing a timeline. The lab_results table is for lab technicians and nurses. The medications table is for pharmacists. Designing tables around who reads the data — not just who writes it — is a core Ferrosa pattern.

What You Learned

You just built the foundation of an electronic medical records system. Here is what you accomplished:

  • Created a replicated keyspace with RF=3 for data durability critical to healthcare

  • Designed a temporal schema with descending clustering order for instant access to the most recent events

  • Used CQL collection types (sets) to model multi-valued fields like allergies

  • Built a patient timeline that returns events in reverse chronological order without sorting

  • Wrote analytical queries to filter abnormal lab results and active medications

The data model you built here scales to millions of patients. Each patient’s data lives on the same partition, so lookups are always fast regardless of how large the cluster grows. That is the power of partition-based modeling.