A form builder's four identities: draft version, published revision, question ID and row ID

[zemaj]

If someone edits a question after responses exist, "the current form" is no longer enough to interpret an old answer.

I make Pencil at Just Every, a free and open source (MIT) form builder. Its Drizzle SQLite schema keeps four identities separate:

1. The flow has a draftVersion, used when editing.
2. A flow revision has its own ID, number, draft/published state and current flag.
3. A question has a stable logical ID within the revision, separate from its row ID.
4. Respondent sessions and responses point to the revision they used.

When a published form is copied into a new draft, a question keeps its logical identity but gets a new row in the new revision. An answer still resolves against the revision the respondent saw, not whichever prompt is current today. Revision numbers are unique within a flow, and question position and stable ID are unique within a revision.

Reads such as "current published revision" go through Drizzle. publishDraft prepares a native D1 batch instead: create the next draft, copy its steps, choices and rules, retire the old current flag, publish the selected draft, and advance draftVersion, all guarded by the expected draft version. We did not run that whole transition through Drizzle's transaction API.

A fixture for any revisioned form system:

• Publish a two-question form and save one response.
• Change a prompt, reorder the questions and publish again.
• Check the earlier response against its original revision.
• Compare stable question identity with revision-specific row identity.
• Try a publish with a stale editor version.

Where have you drawn the line between typed queries and a native batch for a multi-record state transition on D1? A small example with revision history would help.