ebuzima schema briefing

How the tables actually connect

ebuzima is a ClickHouse mirror of a Frappe/ERPNext health information system — 1,422 tables, 604 of them clinical. Before you go looking for data, it helps to know that ClickHouse enforces none of the relationships between them. This deck covers the three mechanisms that hold the schema together instead, each verified against real rows.

01 The shape of the system

One table per form

Every table name starting with tab corresponds to one DocType — Frappe's word for an entity or form: tabPatient, tabPatient Encounter, tabDrug Prescription. That's not a coincidence you need to reverse-engineer — the app's own metadata tables (tabDocType, tabDocField) tell you exactly what fields each one has, and which of those fields point elsewhere.

1,422tables total

604Healthcare-module tables (clinical core)

0foreign keys enforced by ClickHouse

That last number is the whole reason this deck exists.

02 The core problem

Nothing here is a real foreign key

In a normal relational database, a foreign key guarantees a row on one side exists on the other. ClickHouse has no such constraint — every relationship you'll find is a convention the Frappe application maintains in code, not something the database checks. That means three different things can look identical (a column holding a string) but behave very differently:

Physical child tables — rows genuinely live together, linked by columns baked into the child.
Link fields — a soft pointer: a string that's supposed to match another table's key.
Dynamic links — a soft pointer where even which table it points to varies row by row.

The rest of this deck is one slide per type — what it is, how to query it, and how much to trust it.

Type 1 · strongest

Physical child tables

When a form has a repeatable section — e.g. a list of prescribed drugs — Frappe stores those rows in their own table. Each child row carries three columns that tie it back: parent (the parent row's key), parenttype (which doctype that parent is), and parentfield (which section it belongs to). This is the closest thing here to a real foreign key — the data physically can't exist without it.

One shared shape, many owners: Drug Prescription is embedded by 15+ different clinical forms. Outpatient Consultation alone accounts for 5.76M of its rows — distinguishable only by parenttype.

-- always filter by parenttype on a shared child table, or you'll mix unrelated forms
SELECT * FROM ebuzima.`tabDrug Prescription`
WHERE parenttype = 'Outpatient Consultation'
  AND parent = '<consultation-name>';

Type 2 · soft, usually reliable

Link fields

A Link field just stores a string that's meant to match the name column of another table — no different, physically, from any other text column. Whether it actually matches is entirely up to the application that wrote it.

I checked this specific one against live data rather than assuming:

598,414 / 598,803Patient Encounter rows resolve to a real Patient

99.93%match rate — solid, not guaranteed

SELECT pe.name, p.patient_name
FROM ebuzima.`tabPatient Encounter` pe
LEFT JOIN ebuzima.`tabPatient` p ON pe.patient = p.name;
-- p.name IS NULL on the ~0.07% that don't resolve

Rule of thumb: Link fields are usually trustworthy, but "usually" is doing real work in that sentence — spot-check before you rely on a join being complete.

Type 3 · softest, resolve before joining

Dynamic links

Here two columns work as a pair: one names which table to look in, the other holds the value to look up there — and the target table can be different on every row. Metadata alone can't tell you the target; you have to look at the data.

Resolved by querying the real column — this field points to one of five doctypes depending on the row:

Patient Medical Record.reference_doctype → Lab Test, Vital Signs, Clinical Procedure, Patient Encounter, or Therapy Session.

-- step 1: find out what targets actually occur
SELECT reference_doctype, count() FROM ebuzima.`tabPatient Medical Record`
GROUP BY reference_doctype;

-- step 2: join per target, one doctype at a time
SELECT pmr.*, lt.*
FROM ebuzima.`tabPatient Medical Record` pmr
JOIN ebuzima.`tabLab Test` lt ON pmr.reference_name = lt.name
WHERE pmr.reference_doctype = 'Lab Test';

Watch for this

Metadata can lie by omission

One more thing worth knowing before you go searching: some tables that current forms still reference — and that still hold live rows — have no current entry in tabDocType. Their metadata was removed at some point (likely a rename or deprecation that didn't fully clean up), but the physical table and its data were left behind.

14 tables in this state, including Antenatal Care, Lab Prescription, and PrEP Management — all still populated, none discoverable by browsing the app's current form definitions.

If you're hunting for a table and it's not in tabDocType or the app's UI, check system.tables directly before assuming it doesn't exist.

Putting it together

Before you write a query

1Find the doctype name for your concept — check tabDocType, or search the schema map (next slide).
2Look it up in tabDocField to see its Link and Table fields — that's the real edge list, not guesswork from column names.
3On a shared child table, always filter by parenttype — the same table can hold rows from a dozen unrelated forms.
4On a Link field, don't assume 100% integrity — spot-check the match rate the way we did for Patient Encounter → Patient.
5On a Dynamic Link, resolve the target column first (GROUP BY reference_doctype) before you try to join anything.

Go explore

The interactive schema map

Everything in this deck comes from that map — a searchable, click-through view of all 604 clinical tables where every relationship is drawn from live metadata and the trickier ones are verified against real rows.

Open the full schema map

Click any table there to see exactly what it's a child of, what children it has, and what it links to or from — the same three colors as this deck.