I am planning a Retool app for reviewing creative image drafts. A brief may have several reference images and multiple generated iterations. Designers can create drafts in an external browser workspace such as Muse Image, then upload or link each result to the review database. I am not assuming a native Retool integration; the app only manages metadata, files, comments, and decisions.
The proposed tables are briefs, drafts, references, and reviews. The Retool page would show a brief list on the left, a selected draft with its references in the center, and reviewer controls on the right. Reviewers need to mark a draft Approved, Needs Revision, or Rejected, while retaining comments and the complete version history.
I am unsure whether the selected draft and unsaved review should live in temporary state, a form component, or URL parameters. Reviewers may switch between drafts quickly, so I need to prevent unsaved comments from being applied to the wrong draft.
My current approach is to use URL parameters for brief_id and draft_id, query the selected records from those IDs, and keep the current comment and decision in a form that resets whenever draft_id changes. The save query would include an optimistic-lock version from the draft row to catch concurrent reviews.
Would you use a single transaction that inserts the review and updates the draft status, or keep status as a derived value from the latest review? I also need a reliable way to disable navigation while a save query is running without making the interface feel stuck.
Examples of Retool review queues with versioned assets or concurrent reviewers would be helpful.