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.$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.

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
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.”

What this fix guarantees

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.

What it deliberately does not guarantee

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.

Where the open-source contribution stands

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, labeled as a bug,
without an assignee or a maintainer response. Another contributor asked to
work on it; I clarified respectfully that a tested candidate already exists.
The pull request linked above lives in my fork. It is not merged or accepted
upstream.

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.

What stayed with me

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.