Clinical and consumer research, participant study operations
Automation Data Integrity Fix: Root Cause, Identity Model, Tests
The rebuilt identity model is live. All ten known misroutes were resolved and the 29-case acceptance test is the agreed definition of done.
- Our role
- Automation reliability engineer, diagnosis, redesign and acceptance testing
- Timeline
- Diagnosis, rebuild and verification against a pre-agreed test suite
- Sector
- Clinical and consumer research, participant study operations
At a glance
- Confirmed misroutes
- 10: all traced to one root cause
- Root cause
- A search with a single-record limit and no sort order
- Compounding bug
- A downstream module overwriting record identifiers
- Fix
- Composite identity key + deterministic tiebreak + immutable rows
- Acceptance test
- 29 cases, agreed before pricing
- Stack
- Make.com, Typeform, Airtable
What was breaking
Participant submissions were landing on other participants' records. Ten cases were confirmed, which in a research study is not a data-quality inconvenience. It is a validity problem. Answers attributed to the wrong person corrupt the dataset in a way that is very hard to detect after the fact and impossible to correct without knowing which records were affected.
The visible symptom was that manual repairs did not hold. Someone would correct a misrouted record, and some time later it would be wrong again. That is the signature of a second bug layered on the first: a downstream module was copying identifiers onto records as part of its normal operation, so every repair was overwritten the next time that path ran. Until that was understood, the team was effectively fighting the automation.
The third problem was the most serious and the least visible. Repeat submissions overwrote earlier answers in place. A participant who submitted a milestone twice did not produce two records, the second silently replaced the first. In a study collecting safety-relevant information, that is destroyed evidence. Nobody had reported it, because an overwrite leaves nothing behind to notice.
The actual root cause, once traced, was a single design choice in the intake scenario. It located the target record by searching on the participant's profile rather than on the invitation identifier, with a limit of one result and no sort order specified. When more than one record matched, which happens routinely in a study where participants have multiple invitations and milestones, the database returned whichever row it felt like, and the automation wrote to that one. Non-deterministic, undetectable from the logs, and correct often enough to look like it worked.
Ten confirmed misroutes in a research dataset
Answers attributed to the wrong participant undermine the validity of the study, not just the tidiness of the data, and the true count is only ever the number that happened to be noticed.
Manual repairs were silently reverted
A downstream module overwrote identifiers during normal operation, so corrections lasted until the next run. The team's remediation effort was being erased by the system it was fixing.
Repeat submissions destroyed earlier answers
Second submissions overwrote the first in place. For safety-relevant questions this is lost evidence, and overwrites leave no trace to audit.
The failure was intermittent, so it resisted diagnosis
A search returning an arbitrary row among several matches is right much of the time. Intermittent correctness is far harder to chase than consistent failure, and it erodes trust in the whole pipeline.
No way to prove the fix worked
Without an agreed set of cases, 'is it fixed?' is a matter of opinion, and after ten known misroutes, opinion was not going to be enough.
What we built
I did not patch the search. A non-deterministic lookup is a symptom of an absent identity model, and adding a sort order would have made the wrong row consistently wrong rather than arbitrarily wrong. The real question was: what uniquely identifies the thing a submission belongs to? Until that has an answer, every lookup is a guess with better or worse odds.
The answer turned out to be a composite of three values the submission already carried: the invitation identifier, the milestone, and the submission token. Together these are unique by construction, one invitation, at one milestone, with one submission token, is exactly one thing. Searching on that composite key is deterministic because at most one record can match. The previous search used the participant profile, which is the one piece of information guaranteed not to be unique in a study where each participant has many invitations and milestones.
For the cases where legitimate duplicates can still arise, I added an explicit deterministic tiebreak rule rather than leaving resolution to the database. This is the part that matters for trust: when two rows could match, the system does not pick one arbitrarily, it applies a stated rule that produces the same answer every time and that a human can verify by reading it. Arbitrary behaviour is not acceptable even when it is usually right.
The overwrite problem needed a policy change, not a code change. I replaced in-place updates with an immutable row policy: a new submission always creates a new row, and nothing already written is ever modified. Previous answers are therefore preserved by construction. It is not possible to lose them, because no code path exists that would. A separate selection rule then determines which row is the current one for reporting purposes, which cleanly separates 'what was submitted' from 'what counts right now'. Those are two different questions, and conflating them is what caused the data loss.
On top of that sits a submission-token replay guard, so a resubmitted or duplicated webhook cannot create a spurious record. And because the downstream module that had been overwriting identifiers was the reason manual repairs kept failing; that path was corrected too, otherwise the new identity model would have been undone by the same mechanism as the old repairs.
The last piece is the one I would insist on again. Before any pricing was discussed, I negotiated a twenty-nine-case acceptance test covering the known misroutes, the duplicate scenarios, the replay cases and the reporting-selection rules. Delivery included a full diff check against the existing data. Payment was tied to those cases passing. That inverts the usual dynamic, instead of the client being asked to trust that a subtle data bug is fixed, there is an objective, pre-agreed artefact that settles it. The acceptance test is now the live definition of done for the pipeline.
A non-deterministic lookup means the identity model is missing
The fix for an ambiguous search is not a sort order; that just makes it reliably wrong. It is to find the values that are unique by construction and search on those.
Make rows immutable and select the current one separately
'What was submitted' and 'what counts now' are different questions. Keeping every submission and choosing the reporting record with an explicit rule makes data loss structurally impossible instead of merely unlikely.
State the tiebreak rule; never let the database choose
Where legitimate duplicates exist, an explicit deterministic rule gives the same answer every time and can be verified by reading it. Arbitrary resolution is unacceptable even when it is usually correct.
Fix the thing that undoes your fix
Manual repairs kept reverting because a downstream module rewrote identifiers. A correct identity model would have been overwritten by the same path, so that had to be addressed as part of the same change.
Agree the acceptance test before discussing price
For a subtle data-integrity bug, 'is it fixed?' must not be a matter of opinion. Twenty-nine pre-agreed cases turn delivery into something objectively verifiable, which protects the client and the engineer equally.
Non-negotiables
- Existing data had to be preserved. A fix that corrected routing going forward but discarded or rewrote historical submissions would have been worse than the bug.
- The participant-facing form could not change. Whatever the identity model became, it had to be derivable from what submissions already carried.
- The definition of done had to be objective and agreed in advance, because trust in the pipeline had already been damaged.
- Legitimate duplicates genuinely exist: participants do have multiple invitations and milestones, so the fix could not simply forbid multiple matching rows.
The pipeline
Drawn as text and shapes rather than shipped as an image, so the step labels stay readable and downloadable.
Root cause: why ten records went to the wrong place
The intake searched on a non-unique value with a limit of one and no sort order. When several rows matched, the database returned an arbitrary one and the automation wrote to it.
- Trigger
- Branch / filter
- Error handling
- Processing step
- Primary path
- Flow
The rebuilt identity model
A composite key that is unique by construction, immutable rows so nothing can be lost, and an explicit rule selecting the current record for reporting.
- Trigger
- Branch / filter
- Error handling
- Processing step
- Data store or CRM
- Delivered output
- Primary path
- Flow
- Conditional or exception path
How the work runs
Each stage, what it does, and the specific failure it is built to prevent.
Reproduce the failure before theorising about it
Ten misroutes were confirmed but the mechanism was unknown, and the failure was intermittent. The first job was establishing what the intake scenario actually searched on and what the database returned when several rows matched, which is where the limit-of-one with no sort order surfaced.
Intermittent bugs invite plausible wrong theories. Reproducing the mechanism is what separates the root cause from a coincidence.
Identify why repairs were not holding
Manual corrections reverting is diagnostic information, not just a nuisance; it means something is writing during normal operation. Tracing that to a downstream module copying identifiers onto records explained the pattern and, crucially, meant any new identity model would have been overwritten by the same path if it were not fixed too.
Define what uniquely identifies a submission
The participant profile is the one value guaranteed not to be unique here, because each participant has many invitations and milestones. Invitation plus milestone plus submission token is unique by construction. Finding that combination is the actual fix; everything after it is implementation.
A key that is unique by construction cannot return an ambiguous match, so the non-determinism is eliminated rather than masked.
Replace the lookup with a deterministic composite-key search
The intake now searches on the composite key. At most one row can match, so there is no arbitrary selection to make. This is why adding a sort order would not have been sufficient. It would have made the search pick the same wrong row consistently.
State a tiebreak rule for legitimate duplicates
Genuine duplicates do occur, and the system must not resolve them by accident. An explicit deterministic rule produces the same outcome every time and can be checked by reading it, which is what makes the behaviour auditable rather than merely usually-right.
Removes the last place where the database's arbitrary ordering could influence the outcome.
Make rows immutable
In-place updates are replaced with an insert-only policy: every submission creates a new row and nothing already written is modified. Earlier answers are preserved by construction, so the silent-overwrite failure is not merely unlikely. There is no code path that could cause it.
Structurally eliminates destruction of safety-relevant data, which is the highest-consequence failure in the system.
Separate the current record from the submission history
With every submission retained, reporting needs to know which row counts. An explicit selection rule answers that, cleanly dividing 'what was submitted' from 'what is current'. Conflating those two questions is precisely what caused the data loss in the first place.
Add a submission-token replay guard
Webhooks get delivered twice. With an insert-only policy, an unguarded replay would create a spurious duplicate record. Keying the guard on the submission token means a repeated delivery is recognised and ignored, while a genuine second submission by the participant still creates a new row.
Distinguishes a duplicated delivery from a real repeat submission, two things that look identical without a token.
Verify against a pre-agreed 29-case acceptance test
The test suite was negotiated before pricing and covers the known misroutes, duplicate scenarios, replay cases and reporting-selection rules. Delivery included a full diff check against existing data. Payment was tied to the cases passing, so 'is it fixed?' had an objective answer rather than requiring the client to take a subtle data fix on trust.
The acceptance suite is now the live definition of done, so future changes have a regression baseline rather than a memory of what used to work.
What changed
| Aspect | Before | After |
|---|---|---|
| Record lookup | Searched on a non-unique profile value with a limit of one and no sort order. | Searches a composite key that is unique by construction, at most one row can match. |
| Ambiguous matches | The database returned an arbitrary row. | An explicit, stated tiebreak rule that gives the same answer every time. |
| Repeat submissions | Overwrote earlier answers in place, destroying safety-relevant data. | Always insert a new row; nothing written is ever modified. |
| Manual repairs | Silently reverted by a downstream module rewriting identifiers. | That path corrected, so repairs and the identity model both hold. |
| Duplicate webhooks | Not considered. | Replay guard on the submission token, distinguishing redelivery from a real resubmission. |
| Reporting | Current answers and submission history were the same mutable row. | Full history retained, with an explicit rule selecting the reporting record. |
| Definition of done | A matter of opinion after ten known misroutes. | A 29-case acceptance test, agreed before pricing, now the live regression baseline. |
What it runs on
Every tool listed with the job it actually does in this build.
Orchestration
Make.com
The orchestration layer being repaired, intake routing, the composite-key lookup, tiebreak logic, replay guard and reporting selection all live here.
Data
Airtable
The participant record store. Its arbitrary row ordering under an ambiguous search was the mechanism of the bug, and the immutable insert-only policy is enforced against it.
Integration
Typeform
The participant-facing submission path, left unchanged: the identity model had to be derivable from what submissions already carried.
Delivery
Acceptance test suite (29 cases)
The agreed definition of done, covering misroutes, duplicates, replays and reporting selection. Now the regression baseline for the pipeline.
What stopped being manual
Snippets are illustrative and generic. No client data appears in any of them.
The bug, in one search
Replaces an arbitrary row selection with a deterministic one. The manual act this removes is the entire downstream remediation effort, finding misrouted records and repairing them, repeatedly, by hand.
javascript
// BEFORE: participant profile is NOT unique in this study.
// Limit 1 with no sort order: the database returns an arbitrary
// matching row, and the automation writes to whichever it got.
search({ table: "records", where: { profile: submission.profile },
limit: 1 /* no sort order */ });
// AFTER, unique by construction, so at most one row can match.
search({ table: "records", where: {
invitation: submission.invitation,
milestone: submission.milestone,
token: submission.token,
}});Immutable rows instead of in-place updates
Replaces the silent destruction of earlier answers. Previously a second submission overwrote the first; now every submission is retained and a separate rule decides which one reporting uses.
javascript
// BEFORE: a repeat submission overwrote the earlier answers.
// update(existingRow, submission.answers); // evidence destroyed
// AFTER: insert-only. Nothing already written is ever modified.
insert({ ...submission.answers, submittedAt: now, token: submission.token });
// "What was submitted" and "what counts now" are different questions:
const current = selectCurrentRecord(rowsFor(invitation, milestone));Replay guard on the submission token
Replaces nothing a human was doing. This guards a failure the insert-only policy would otherwise introduce. A duplicated webhook delivery must not become a duplicate record, while a genuine repeat submission must.
javascript
// Webhooks are delivered more than once. With insert-only writes,
// an unguarded replay creates a spurious row.
if (await seenToken(submission.token)) {
return skip("replay of an already-processed submission");
}
// A real second submission by the participant carries a NEW token,
// so it still correctly creates a new row.What it was worth
Ten misroutes resolved by one change to the identity model. That ratio is the point: the team had been treating each misroute as an incident to repair, and every repair was being quietly undone. Once the root cause was a single non-unique search, the fix was singular too, and the class of bug disappeared rather than being reduced.
The immutable row policy changed a risk into an impossibility. Before, not losing safety-relevant answers depended on participants not submitting twice. After, there is no code path that modifies a written row, so the data cannot be destroyed by that mechanism at all. Moving a failure mode from 'unlikely' to 'structurally impossible' is worth far more than reducing its frequency, especially for data a study's conclusions rest on.
The acceptance test is the part I would transplant to every engagement like this. After ten known misroutes, asking a client to believe a subtle data bug is fixed is asking a lot, and 'it works now' is not verifiable. Twenty-nine cases agreed before pricing turned delivery into something objectively settled, and left behind a regression baseline, so the next change to this pipeline can be checked rather than hoped about.
One honest note on the numbers: ten is the count of misroutes that were discovered. With a non-deterministic search that is right much of the time, the true historical figure is unknowable, which is part of why the diff check against existing data mattered more than the headline count.
Known misroutes resolved
All traced to a single root cause and fixed by one identity-model change.
Root cause behind every case
A search on a non-unique value with a limit of one and no sort order.
Silent overwrites possible
Insert-only writes mean no code path can modify an existing row.
Acceptance cases agreed before pricing
Covering misroutes, duplicates, replays and reporting selection.
Values in the composite key
Invitation, milestone and submission token, unique by construction.
Handed over
- Root-cause diagnosis tracing all ten confirmed misroutes to a single non-deterministic search.
- Identification and correction of the downstream module that was silently reverting manual repairs.
- A composite identity key (invitation, milestone and submission token) unique by construction.
- A deterministic tiebreak rule for legitimate duplicates, stated explicitly rather than left to database ordering.
- An immutable row policy: every submission inserts a new row and nothing already written is modified.
- A separate reporting-selection rule distinguishing the current record from the submission history.
- A submission-token replay guard so duplicated webhook deliveries cannot create spurious records.
- A full diff check against existing data as part of delivery.
- A 29-case acceptance test suite, negotiated before pricing, covering misroutes, duplicates, replays and reporting selection, now the live definition of done.
Questions about this build
What teams usually ask when they recognise their own process here.
Because that makes the wrong answer consistent instead of arbitrary. The search was matching on a value that is not unique in this dataset: participants legitimately have many invitations and milestones, so among several valid matches, any choice is a guess. A sort order makes it the same guess every time, which is easier to debug and still wrong. The actual fix is to search on something that cannot match more than one row.
Ask what makes the thing unique in the real world, then check whether the submission already carries those values. Here it was invitation plus milestone plus submission token: one invitation, at one milestone, with one token, is exactly one submission. The previous design used the participant profile, which is the single value guaranteed to be ambiguous in a longitudinal study. The useful instinct is suspicion of any lookup keyed on a person rather than on an event.
Because careful is not a property you can verify. The failure was that repeat submissions overwrote earlier answers, including safety-relevant ones, and an overwrite leaves nothing behind to audit. With insert-only writes there is no code path that modifies a written row, so the failure is structurally impossible rather than merely guarded against. For data that a study's conclusions depend on, that distinction is the whole point.
An explicit selection rule, kept separate from the write path. That separation is the real insight: 'what was submitted' and 'what counts now' are two different questions, and the original design answered both with one mutable row, which is exactly how the history got destroyed. Retain everything, then decide what is current, and both questions stay answerable.
Because for a subtle data-integrity bug, 'is it fixed?' is otherwise a matter of opinion, and after ten known misroutes the client had good reason not to take it on faith. Agreeing twenty-nine cases up front means delivery is objectively settled, the scope cannot drift, and neither side is arguing afterwards about what was promised. It protects the engineer as much as the client, and it leaves a regression baseline behind.
A downstream module copied identifiers onto records as part of its normal operation, so any manual correction survived only until the next time that path ran. That detail mattered twice over: it explained the maddening symptom, and it meant the new identity model would have been overwritten by the same mechanism if it had not been fixed in the same change. A fix that your own pipeline reverts is not a fix.
Constantly. Any automation that looks up a record before writing to it has an identity model, whether or not anyone designed one, and 'search by name, take the first result' is the single most common version of this bug. CRM syncs, order processing, support ticket matching and billing all hit it. The questions transfer directly: what makes this unique, can the lookup return more than one row, and what happens when it does.
Capabilities this used
Read next
All ai automation workHave a process that works like this one did?
Book a strategy call. We will walk the workflow with you and say plainly which parts are worth automating and which are not.