The AI demo failed, but the database remembered half of it

A developer found a critical atomicity bug in Formbricks' AI example-response generator, where a failed operation left partial data in the database and blocked retries. The fix wraps all persistence in a single transaction, ensuring one request has one outcome.

This is a submission for DEV's Summer Bug Smash: Clear the Lineup. The error message said the operation had failed. PostgreSQL told a different story. I was reading Formbricks' AI example-response generator when I found a sequence of writes that looked individually reasonable: create a tag, create a display, create a response, evaluate quotas, link the tag, repeat. But each completed piece could commit before the next one began. So I made the third response fail. The action rejected, exactly as the UI would expect. Underneath it, the first two responses were still there. So were three displays, a newly created tag and two tag links. | Record | Expected | Observed | |---|---|---| | Response | 0 | 2 | | Display | 0 | 3 | | newly-created Tag | 0 | 1 | | TagsOnResponses | 0 | 2 | The third display had been written before response creation failed. The error was real, but so was half the dataset. Then came the part that made this more than a cleanup problem. Formbricks only allows example generation while a survey has zero responses. After the existing rate-limit window, retrying the operation hit that guard. The failed attempt had left two responses behind, so its own debris prevented recovery. The operation reported failure, changed the database and then used that change as the reason the user could not try again. That is the invariant I set out to restore: one request to generate example responses must have one persistence outcome. Either the complete synthetic dataset commits, or none of that attempt remains. Formbricks https://github.com/formbricks/formbricks is an open-source experience-management platform for building surveys and analyzing responses. Its Survey Summary can generate example responses so a team can explore the analytics experience before collecting real data. That sounds like a small demo feature. Its persistence path is not small. One generation creates a generated-response tag, one Display and one Response for each synthetic submission, response timestamps, quota-evaluation links, tag links and additional impression-only Displays. The production path generates 20 example responses. From a person's perspective, that is one button press. Before this patch, the database saw a collection of separately committed operations. The distinction matters because a user-level operation does not become atomic just because every helper has a transaction somewhere inside it. If helper A commits, helper B commits and helper C rolls back, the database has faithfully protected three different operations. The user only asked for one. I wanted a reproduction I could run on demand. I replaced the external model boundary with a deterministic four-response dataset and injected a failure while response 3 was being created. Four responses keep the baseline small enough to inspect; the final validation separately exercises the production-sized batch of 20. Before the fix, response 1 and response 2 committed. Their tag links committed. The Display belonging to response 3 also committed because it was created before the injected failure. The enclosing action rejected, but there was no enclosing database transaction capable of undoing the earlier work. The baseline characterization test https://github.com/JuanTorchia/formbricks/blob/d5c5f381c994bafa2efdb7662e5468e6c887843d/apps/web/integration/ai-example-response-atomicity.test.ts This proves a mechanism, not its production frequency. I do not know how many users have encountered a mid-batch failure, how often it happens or what it has cost. I did not find this through a production incident report. The defensible claim is narrower: under a deterministic failure, the operation left the measured partial state above and made a later retry fail the zero-response guard. My first fix was the fix most of us would sketch immediately: start one outer Prisma transaction before persistence and pass its client into the writes. It solved the first rollback test. Then I constrained Prisma to one database connection. The test expired after about five seconds. The outer transaction owned the only connection, but organization, workspace, survey and quota services still performed reads through the global Prisma client. Those reads asked the pool for another connection. There was no other connection. The transaction waited on code inside itself until it expired. Increasing the timeout would only make the deadlock-shaped wait longer. The problem was not that the transaction needed more patience. The problem was that I had drawn a boundary in one function while the call graph quietly crossed it. That one-connection test changed the implementation. Stable survey, quota and workspace-to-organization context is now loaded once through the transaction client. Mutable quota counts and every write also stay on that client. Existing callers keep their cached, global path; the generated-response path opts into a narrow persistence context tied to its caller-owned transaction. It left me with a rule I trust more than the green test I had before: A transaction boundary is only real if every database operation inside it uses the same transaction client. Putting the model call inside the transaction would hold a database connection and lock while waiting on an external service. That makes atomicity expensive in exactly the wrong place, so generation remains outside. But that creates a race. Two collaborators can both observe zero responses, start model generation and return with valid datasets. A real respondent can also submit while the model is running. The survey may be archived. Ownership may no longer match the snapshot used to start the action. The transaction therefore acquires the survey lock after generation and revalidates the state that authorizes persistence: Only then does it load the stable persistence context and write the synthetic batch. My first instinct was SELECT ... FOR UPDATE . It serializes competing generators, but it can also conflict with the FOR KEY SHARE lock PostgreSQL uses when a normal Response or Display insert validates its foreign key to the Survey. The example generator should protect its own batch. It should not make a real respondent wait just because a synthetic demo is being persisted. The final design uses FOR NO KEY UPDATE : js return await prisma.$transaction async tx = { await tx.$queryRaw SELECT id FROM "Survey" WHERE id = ${survey.id} FOR NO KEY UPDATE ; // Revalidate archive state, ownership and zero Responses. // Load stable Survey and quota context through tx. // Persist every synthetic entity through tx. }, { timeout: 10 000 } ; That lock still serializes competing generators and Survey updates, while remaining compatible with the foreign-key lock used by normal inserts. I tested the distinction directly: pause example persistence at response 3, insert a real Response from a second connection, and verify that the real insert completes before the example transaction is released. The winning lock was not the one with the most intimidating name. It was the weakest lock that protected the invariant without placing the demo ahead of a real person. Quota evaluation introduced another boundary. For normal response intake, Formbricks historically treats some quota database errors as best-effort: log the problem and continue accepting the response. Changing that globally would turn this bug fix into an unrelated compatibility decision. For an atomic generated batch, however, swallowing a quota-link failure would commit another kind of partial dataset. The dedicated example-response context therefore selects strict propagation. A real PostgreSQL P2003 foreign-key error during quota-link creation is re-thrown and aborts the outer transaction. Existing response callers preserve their original API and best-effort behavior. That asymmetry is intentional. Reliability work is not making every path stricter. It is deciding which failures each path is allowed to survive. The model request stays outside the transaction. Inside it, the patch locks and revalidates the Survey, loads stable evaluation context through tx , creates all 20 responses and their related records through that same client, bulk inserts tag links and adds the remaining impression-only Displays before commit. The candidate patch also adds runtime guards so the atomic context cannot be used without its transaction, across surveys, with quotas from another survey or for contact-linked responses. A type that looks correct at one call site is not enough protection for shared persistence code. The completed regression matrix is broader than “the happy path still worksâ€Â: | Scenario | Verified result | |---|---| | failure while creating response 3 | zero synthetic rows; later retry succeeds | quota-link foreign-key failure P2003 | complete rollback | transaction expiration P2028 | complete rollback; later retry succeeds | | two generators race | one complete dataset; one domain rejection | | real Response arrives during model generation | generated batch rejected | | real Response arrives during persistence | real insert is not blocked | | Survey archived during generation | generated batch rejected | | generated Tag already exists | preexisting Tag survives rollback | | Prisma pool has one connection | complete response and quota path succeeds | The real PostgreSQL harness covers the production schema, ownership checks, Display creation, v1 Response persistence, quota lookup and evaluation, quota-link writes and bulk tag assignment. Focused compatibility run: 5 files passed, 158 tests passed PostgreSQL harness: 2 files passed, 8 tests passed One-connection full path: 1 file passed, 2 tests passed The PostgreSQL results were recorded before a later type-only follow-up. That follow-up changed how the same quota-evaluation payload is constructed so TypeScript preserves its discriminated union; it did not change the runtime values or transaction behavior. I attempted a PostgreSQL rerun, but my local database service was offline, so I am stating the gap instead of pretending the rerun happened. On the final commit, the fork's hosted Formbricks web build, official unit tests, linters and Helm validation passed. The hosted E2E job stopped in one second without executing test steps in the fork environment, and SonarQube could not authenticate without its repository secret. I will not compress that mixed result into “CI is green.†For the tested failure modes, a generated dataset commits completely or rolls back completely. A failed transaction no longer leaves synthetic Responses that create the persistent zero-response retry block. Competing persistence is serialized per Survey, state is revalidated after generation, quota capacity ordering is preserved and normal response insertion is not blocked by the Survey lock. The external model call is outside the database transaction. Two callers may still pay for duplicate model work before persistence serialization lets one win and rejects the other. This is not exactly-once generation. The explicit 10-second transaction timeout was validated locally. It is not a universal promise for every remote or heavily loaded PostgreSQL deployment. This work also does not cover a process crash outside PostgreSQL transaction semantics, establish production frequency or prove that a lease, queue or workflow engine is needed. Those are different claims requiring different evidence. I would rather leave a limitation visible than make the patch sound larger than it is. Atomicity is already a strong promise. It does not need borrowed certainty. Formbricks currently says community code contributions are accepted only in rare exceptions. I reported the reproducible bug and asked maintainers whether they wanted the prepared fix and where they would prefer the PostgreSQL regression to live. As of August 21, 2026, the upstream issue remains open https://github.com/formbricks/formbricks/issues/8722 , labeled as a bug, A contest deadline does not create a right to someone else's review queue. The patch and its evidence can stand in public without turning persistence into pressure. The first rollback test went green quickly. If I had stopped there, I would have proposed a transaction that could starve its own connection pool. If I had chosen the strongest row lock without testing a real insert beside it, I might have protected synthetic data by delaying an actual respondent. The difficult part was never typing $transaction . It was discovering where the operation really begins and ends. It was tracing every quiet database read that crossed that boundary. It was accepting that an external model belongs outside the lock, then distrusting every piece of state that might have changed while it ran. It was choosing a lock for the work that needed protection without making unrelated work pay for it. There is a small debugging exercise here that travels well beyond Formbricks. Find a feature your product presents as one action, especially one that writes in a loop. Fail it in the middle. Then ignore the error message and ask the database what actually happened. If those two answers disagree, the bug is not merely a missing rollback. It is a boundary nobody finished drawing.