Annotation question template import through the current UI

Chris authorized implementation on 2026-10-02, accepting the existing pilot workbooks and the standard downloadable template. He approved a 5 MiB upload limit, 500 questions, 100 options per question, depth 20, and default-off feature gating. This authorization covers implementation and review; environment activation remains a separate release decision.

User workflow

The existing Question Management Design page offers Import question template. Its dialog includes Download blank template: choose a file, validate, review the resolved tree and errors, explicitly confirm, then see the result. The browser retains the chosen file and draft after a validation or concurrency failure. Closing before confirmation creates no questions.

The first release creates custom questions and supports an explicit unassigned scope. Users subsequently assign them using the existing Assign page. Stage assignment within import is a later increment, not an implicit side effect. The import preserves system questions and resolves their legal aliases without creating duplicates. Existing question definitions and answers are preserved.

Input compatibility

• Standard format 1 is the canonical CSV/workbook contract in the extensibility architecture.
• Legacy pilot workbooks use row_id, parent_row_id, control_type, data_type, show_when_parent_answer, and optional answer_mode. An explicit adapter translates their Questions sheet to the canonical plan before validation. Hierarchical dotted IDs remain file-local references; they are not uploaded as persistent question identifiers. Original text and supporting audit sheets remain review-only and never become question wording.
• Legacy multiple=Yes plus answer_mode=Separate instances means multiple=true, answer_array=false; blank/One list retains the original array behavior. Reference conversion must preserve per-factor child ownership.
• Formula cells in imported author fields, macros and external workbook links are refused. Static review guidance is ignored. The original uploaded workbook is never rewritten.

Approved limits and rollout

Limit	Value
Uploaded bytes	5 MiB (5,242,880 bytes)
Custom questions	500
Options per question	100
Custom hierarchy depth	20 (a custom root is depth 1)

Defensive processing limits are 50 MiB expanded ZIP content, 1,000 ZIP entries, 40 table columns and 32,767 characters per cell. XML disables DTDs and external resolution; actual decompression cannot exceed each part's declared length. Numeric option tokens accept at most 100 digits, precision 38 and scale 18; standard sibling order is a nonnegative signed-32-bit integer. An excess returns a stable validation error before project mutation. These bounds protect memory and exact decimal comparisons rather than changing the approved review scope.

The generated annotationQuestionImport flag gates the UI and server actions, defaults to false in every environment, and is checked by the server for direct HTTP calls. Read-only preview and atomic apply can be implemented and verified offline without activating the feature in an environment.

Delivery sequence and existing dependencies

1. Reconcile the shared production schema and legacy-workbook adapter against the reference foundation in PR #2781. Keep its Python network writes disabled. The new production API runs .NET; the Python application is not called or modified.
2. Implement bounded CSV/XLSX parsing, server domain validation, template download, and read-only preview. Preserve stable option values, booleans, hierarchy, answer mode, descriptions and authored labels. Refuse unsupported extensions.
3. Implement atomic project apply plus insert-only success receipt in one Mongo transaction, optimistic concurrency, exact retry identity, and separate short-retention attempt history. Reuse the established isolated-read/session primitives. Never sequence the individual question HTTP endpoint.
4. Add the accessible Angular dialog to the current Design surface behind the generated flag. Show source format, question counts, conditions, repeatable instance/array mode, resolved system parents and accumulated row-validation errors. Graph validation follows only after author rows are valid, and reports its first refusal.
5. Prove download/upload/preview/confirm for both formats and a selected real pilot workbook in the isolated test environment. Verify refusal/rollback, retry, concurrency, authorization, and flag-off behavior before release.

The feature follows PR #2779's atomic audit contract. The existing answer-label writer and option display-label capabilities must be verified against current source before using those fields. Missing capabilities are reported explicitly; the importer must not silently discard fields to make a workbook pass.

Acceptance evidence

• CSV and workbook equivalence use the same canonical .NET plan, with tests for identical bytes/hashes, Unicode, quoting, booleans and ordering. Unicode 17 full case-folding data is vendored from Unicode. Plan serialization follows the typed subset of RFC 8785: only strings, bounded integer orders, booleans, nulls, arrays and objects enter the plan.
• Legacy human/animal fixtures preserve question text, conditions, built-in parents, helper text and repeatable factor instance mode.
• Preview changes no project or immutable success audit. Invalid input produces file/sheet/row/column errors and leaves the entire project unchanged.
• Failed transaction, stale preview and conflicting operation reuse write nothing. Identical retries return the recorded receipt without another apply.
• Flag off and denied authorization refuse template/preview/apply before file processing where transport permits, and create no project import history.
• UI tests cover file replacement, row-specific errors, explicit confirmation, double-click prevention, retained failure state and route project changes.
• User documentation explains supported formats, repeatable groups, system anchors and the explicit subsequent stage-assignment step.

Implemented transaction and recovery contract

Every endpoint is guarded by the existing project Design authorization policy and the same runtime/deployed flag before manual upload parsing. Preview loads a private uncached Project, builds the normalized plan and exercises the real domain writer without saving. No shared cached aggregate is mutated.

Apply reuploads the file with the operation ID, raw file hash, canonical plan hash, definition snapshot hash, project version and explicit unassigned mode. A matching committed receipt returns before drift validation. New operations recheck the bulk-study lock guard and reread the Project in a snapshot transaction. The receipt insert and optimistic Project save share that transaction. Majority commit is required, with bounded retry of the same commit for an unknown result. There is no per-row HTTP loop or nontransactional fallback. Existing questions, Stages and Studies are not edited; new custom children append to their parent.

Receipts retain the generated question-ID sequence (its index is the row ordinal), actor, hashes, timestamp and before/after version. They do not retain raw row IDs, cell text, uploaded files or filenames. Source-free attempt history has a 30-day TTL and a bounded 50-attempt project list. A history outage is best-effort and does not turn a committed receipt into a reported failure; receipt persistence itself is mandatory and transactional. Post-commit event dispatch follows the existing best-effort mechanism, not a newly claimed durable event outbox.

The browser prevents concurrent applies. Before confirmation leaves the browser, session storage records only the retry identity and hashes, not the file or rows. After a reload the same project dialog queries a receipt scoped to the current actor, or asks for the same file to retry without creating a new operation. Project navigation and runtime disable prevent further saves from an old dialog.

Nonempty metadata fields/response modes and distinct option display labels are refused explicitly because the current deployed writer does not support them. Answer labels are supported. Current schema-v0 custom questions support the additive multi-option target representation, so those conditions are preserved.

Local verification and release boundary

The focused API suite includes pure parsing and real MongoDB replica-set tests: atomic success, rollback after receipt plus Project writes, concurrent duplicate retry, unchanged project after preview, stale preview and source-free history. The actual existing human and animal reviewed workbooks pass the production adapter read-only; no workbook content is copied into committed fixtures. Angular tests exercise visible tree indentation, confirmation, retry, recovery, context changes and upload identity. Development builds pass.

PR #3934's quality-gate follow-up adds endpoint envelope/size/identity/error tests, strict CSV/OpenXML edge cases, native number/boolean/shared-string/date fixtures, control and condition validation, and browser download/error/recovery tests. Regex validation has an explicit one-second execution limit; date-format classification uses a character scan rather than a regex. OpenXML's explicit t="n" numeric cells follow the same validation as implicitly numeric cells, so date-formatted author fields and native numbers in text fields are refused. Coverage thresholds and import-code inclusion remain unchanged.

This is PR implementation evidence, not deployed acceptance. No staging or production activation, template rewrite, project upload or Stage assignment was performed. A release still needs review/CI and the isolated browser acceptance lane before enabling the flag. The feature is deliberately default-off.

Interface presentation

The dialog uses the existing Material typography and surface/outline/primary tokens; there is no new palette or decorative card system. Its distinguishing element is a DFS-indented tree whose branch glyph moves with the question, addressing the pilot overview's ambiguity. Conditions, requiredness and answer instances appear directly under the corresponding question, with help text expandable in place. Error feedback is announced; native file selection and Material confirmation/actions remain keyboard accessible.