Case study · Project
Building a research database with Claude
Dozens of cohort files, exports and registries, several thousand patients: how a single, versioned, pseudonymised database was built with an orchestrator and sub-skills, and what each incident taught.
Idea
What problem, in one sentence?
State the problem in one sentence
“How many patients do we have with this phenotype and this data item?” takes a week: dozens of files, each with its own way of identifying a patient, its own units and its own gaps. The need is not one more spreadsheet, it is a single database that answers in minutes, with counts only.
DifficultyEach project had rebuilt its own extraction, with counts that never matched.
SolutionOne database, never a parallel one; every new need becomes a query on that database.
Concept
What will the tool be, and what will it do?
Write the framework before the first line of code
Legal basis, approval from the data protection officer and the clinical research department, where AI processing is authorised, who decides on freezes. Then the working rule: the AI works only on the pseudonymised database; the vault (correspondence key and originals) is opened only on explicit request, on the workstation.
!No identifying data in the conversation: show column names, structures and counts.
Split into building blocks rather than one giant skill
A “database” orchestrator that carries the shared rules (pipeline, index, freeze, publication) and routes; separate sub-skills for each disease; a single query block; one block per registry. The orchestrator never does the detail.
What to tell Claude“Here is the skill for my research database. Identify what belongs to the shared rules and what belongs to a disease or a registry, and propose a split into an orchestrator and sub-skills, with what each one covers and what it excludes.”
DifficultyThe orchestrator had grown until it handled the detail of every disease: it became unreadable and contradicted itself.
SolutionSplit into sub-skills; the orchestrator keeps only the shared rules and the routing.
Build
What to ask Claude, in what order?
A start-of-session ritual
At every session, Claude reads the plan and the log, then sums up the state in three lines: where things stand, what is blocking, the next step. When a rule is born, the skill, the plan and the log are updated in the same turn, not “later”.
DifficultyA rule decided in one session, forgotten by the next.
SolutionNo rule exists until it is written, in the same turn, into the skill, the plan and the log.
Integrate sources one at a time
Each new source goes through a checklist: how it identifies a patient, which sheets are dictionaries or transposed tables, then a two-tier architecture (inclusion, then enrichment, which never adds a patient). Start again from the script of the closest cohort; every value stays traced back to its cell.
DifficultyA few dozen phantom patients were born of a transposed sheet: column headers had been read as identities.
SolutionTransposed sheets and dictionaries are spotted before any import, and an “enrichment adds no patient” check runs at every pass.
Link patients with a score
Error-tolerant identity, date of birth with a tolerance, a body of evidence; three thresholds (certain, probable, doubtful); doubtful links stay in the database with their reason. A placebo test measures the share of links due to chance.
DifficultyTwo different people merged because they shared the same date of birth and a similar name.
SolutionA “unique date of birth” rule counted over the whole scope, a cap on links per record, and a monthly human check of a sample of certain links.
!Never a merge on a single item.
Test
How do you prove it works?
Check quality without drowning the signal
A quality report in five families, discrepancies settled in one batch, then a numbered, time-stamped freeze with a read-only copy. A variable with no decision is not kept; a substantive correction opens a new version.
DifficultySeveral thousand alerts on the first pass, the vast majority false: nobody read them any more.
SolutionNo more alerts on identifiers or range checks on 0/1 variables, mixed units suspected only across at least two files with a clear clustering by a factor of 10, sign conventions brought back to the absolute value.
Audit coverage year by year
Three levels of figures (people listed, with a source file, with measurements in the database) and, for each source, a yearly audit: an exam is “available” if it carries several measurements or a report; exams with a date only reveal the gaps.
DifficultyAn almost empty year was first read as an absence of exams.
SolutionAn identical re-export proved that the gap came from the source; a source gap is now told apart from a linkage failure, with an independent recount.
Go live
How to deliver, publish and sustain it?
Treat a stock of exams without clinical data as a methods object
A stock of several thousand exams with no associated clinical data is neither discarded nor mixed into the database: first profile its columns (types, fill rates, ranges), then separate what can be published without outcomes (description, feasibility, reproducibility) from what requires linkage to vital status, which falls under its own framework.
!No clinical question on this stock before profiling.
Answer in minutes and monitor every week
Every list of patients triggers a longitudinal query; an extraction request produces a standardised, pseudonymised, logged workbook; statistics panels show their limitations and mask small counts. A weekly read-only sentinel returns an OK, warning or error verdict, compared with the previous one.
DifficultyA database opened on a network folder ended up in an inconsistent state after a connection drop.
SolutionDatabase copied locally, resumable jobs, all-or-nothing writes with a recount, publication by verified copy then switch-over.