Research

Organising a research database

From raw file to versioned database: disease-specific core datasets, a pseudonymised index, quality checks, and a query page that shows only aggregates.

CoworkClaude CodeArtifactLong-term project

Screenshot of a real tool · fictitious data, names blurred

The problem

Years of cohort spreadsheets, exam exports and thesis data collections, each in its own format. The question “how many patients do we have with this phenotype and this data item?” takes a week.

Step-by-step method

1. A written pipeline: original → raw → harmonised → database → analysis → export, each stage in its own folder; nothing is modified in place. The database is copied locally, never opened on a network folder; long jobs can be resumed, every write is all-or-nothing with a recount, and a new version is published by a verified copy followed by a switch-over, the old one being archived.

2. Core datasets by disease, each with its version (candidate, frozen) and a freeze procedure. A freeze follows five steps: a pre-filled variable book (inferred type, actual fill rate) approved by the person in charge, the import, a quality report in five families (identity, duplicates, ranges, consistency, completeness), discrepancies settled in one batch, then a numbered, time-stamped freeze with its read-only copy. A variable with no decision is not kept; a substantive correction opens a new version and runs the five steps again.

3. A pseudonymised pivot index to de-duplicate patients present in several cohorts, kept on the local workstation. Linking records across sources relies on a score, never on a single item: see Linking patients without merging them by mistake.

4. Quality-control rules and a decision log (why a given variable was recoded). Data are kept in long format, each value traced back to its source cell (file, sheet, row, column). An invalid value, an uncertain unit or a conflict between sources is kept and labelled; at the freeze, a single reference value is chosen. NULL is not zero, and every exclusion is justified and reversible.

5. Distinguish “measurement in the database” from “file available”: you know what is actually usable. Three levels of figures are reported: people listed, people with a source file, people with measurements in the database, plus the maximum achievable completeness of a measurement across all files. A year-by-year coverage audit tells a gap in the source (exams with a date only, confirmed by an identical re-export) from a linkage failure, with an independent recount.

6. A query page that handles only aggregates: population, criteria, required data, result as counts. Each statistics panel carries a “limitations” box: phenotype defined in tiers, pairs of exams formed within a time window and on reliable links only, median [Q1–Q3] with the number of measurements, and any count below the agreed threshold shown as “fewer than N”.

7. Identifiable extraction is done locally, by a script, on the basis of a validated “extraction request”. The request produces a standardised workbook (README, PATIENTS, one tab per data family, DICTIONARY, SOURCES), pseudonymised by default; the “counts only” button is often enough. Every query is logged (date, specification) so that it can be replayed exactly.

8. A “locked drawer” for sensitive data: whatever must never enter AI tools is isolated and documented, and open questions about access to it are settled with the institution. In practice, two copies: the pseudonymised working database (random identifiers, year of birth only, cleaned free text, proven equivalence with the original, numbered version) and the vault (correspondence key and originals). The AI works only on the pseudonymised database; the vault is opened only on explicit request, to link identities or to produce a requested identifiable deliverable, which stays on the workstation.

9. Exports for collaborators: aggregated figures and tables, never individual-level data.

10. A checklist for every new source: how it identifies a patient (inclusion number whose prefix encodes the site, initials code, opaque code, bare number; a collective value is never turned into a patient), which sheets are dictionaries or transposed tables (a source of phantom patients), and a two-tier architecture: inclusion first, then enrichment, which never adds a patient (checked at every import). Start again from the script of the closest cohort.

Going further

• Quality control without false alarms: written physiological ranges; mixed units suspected only with at least two source files and a clear clustering by a factor of 10; never an alert on an identifier, nor a range check on a 0/1 variable; a sign convention that changes from year to year brought back to the absolute value.
• Data taken from the report: a value missing from the table is looked for in the report, which prevails; negation is limited to the line; an exam indication is not a diagnosis; the supporting excerpt is kept and the origin labelled (table, report, qualitative, absent).
• Every list of patients triggers a longitudinal query: matching on at least two identity items; per person, number of exams, first and last date, follow-up time; three output files (identifiable on the workstation, pseudonymised, aggregated counts for the conversation). People missing from the index wait in a holding module (linked, new, doubtful), merged at the next version.
• A weekly read-only sentinel: integrity, orphans, duplicates, ranges, completeness by year, new, modified or encrypted files; an OK, warning or error verdict, compared with the previous week’s.
• Related guides and case: Linking patients without merging them by mistake, Preparing registry data entry, Building a research database with Claude.

Deliverable

A versioned, documented database, and an answer in minutes to “how many patients for this study?”.

Safeguards

Permissions first: legal basis, approval from the data protection officer and the clinical research department, where AI processing is authorised, and who decides on freezes. Health data: the regulatory framework applicable to research (declaration, reference methodology, patient information), storage authorised by the institution, no identifying data in an unauthorised service. Consult your institution’s clinical research department and DPO (data protection officer).

Starter prompt

Here is the list (column names only, no data) of my 4 cohort files. Propose a harmonised data dictionary, quality-control rules, and the pipeline folder structure. Do not open any patient data.

← Back to the triptych