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

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. Project Overview 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. Bug Fix or Performance Improvement A deterministic failure, not an incident story 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 records that behavior against the unpatched path. 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. The obvious transaction was still wrong 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. Keep the model outside, then distrust the old snapshot 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: - the Survey still exists and is not archived; - it still belongs to the expected Workspace; - the Workspace still belongs to the expected Organization; - no Response arrived while the model was running. Only then does it load the stable persistence context and write the synthetic batch. The strongest lock was not the safest lock 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 : return await prisma.$transaction( async (tx) => { await tx.$queryRawSELECT 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. Strict failure where atomicity needs it 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. Code - Upstream issue: https://github.com/formbricks/formbricks/issues/8722 - Pull request in my fork: https://github.com/JuanTorchia/formbricks/pull/1 - Immutable final commit: https://github.com/JuanTorchia/formbricks/commit/7c183d42d6dc8f33aaaea9b297ab5c296794526a - PostgreSQL evidence package: https://gist.github.com/JuanTorchia/c3fc63122fba62a4b52e389c402aa2bb/c798152c61f1a1c20e046473518ad66f9160b3d9 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. My Improvements One invariant, tested from its failure edges 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