question_library_items

Overview

Past question data (the question library). Entries come from two origins: surveys in this system, and questionnaires imported from CS.

Table Definition

Logical name	Physical name	Column	Type	PK	Relation	Unique	Nullable	Default	Notes
Past question data	question_library_items	id	uuid	◯				gen_random_uuid()
		source_survey_id	uuid		surveys:id (cascade)		◯		Source survey for internal origin
		source_question_id	uuid				◯		Source question for internal origin. No FK constraint
		source_external_survey_id	uuid		external_surveys:id (cascade)		◯		Source questionnaire for external origin
		source_external_question_id	uuid		external_questions:id (cascade)	◯	◯		Source question for external origin
		category	varchar(255)						Category. Used to filter searches
		section_title	varchar(255)						Title of the original section
		question_type	question_type						single / multi / free_text / matrix / intro / pulldown
		normalized_payload	jsonb						Normalized question content
		created_by	uuid		users:id (set null)		◯		Registrant
		created_at	timestamptz					now()

Relations

• source_survey_id → surveys.id (onDelete: cascade)
• source_external_survey_id → external_surveys.id (onDelete: cascade)
• source_external_question_id → external_questions.id (onDelete: cascade)
• created_by → users.id (onDelete: set null)

Indexes

• Primary key (id)
• UNIQUE INDEX: question_library_items_external_question_idx (source_external_question_id)

Constraints

• CHECK: question_library_items_source_required — either source_survey_id or source_external_survey_id is required

Structure of normalized_payload

{
  "promptText": "以下のうち、知っているブランドをすべてお選びください。",
  "options": [
    { "label": "ブランド A", "isExclusive": false, "allowOtherInput": false, "isNotApplicable": false }
  ],
  "subItems": [{ "label": "満足度" }],
  "subQuestions": [
    { "label": "平日", "options": [{ "label": "毎日", "isExclusive": false, "allowOtherInput": false, "isNotApplicable": false }] }
  ]
}

Field	Required	Contents
promptText	◯	Prompt text
options	◯	Choices
subItems		Sub items (such as matrix columns); labels only
subQuestions		Sub-questions (label + choices)

Notes

• When entries are registered: on survey JSON export (existing entries from the same survey are deleted and re-registered), and on import from CS
• Branching is not stored: the normalized payload contains no branch rules or visibility logic, so they must be configured again after reuse
• Why source_question_id has no foreign key: snapshot row IDs change when versions are copied, so a foreign key constraint would break on every save. Reuse follows the stable ID instead
• Reuse works even if the source survey was deleted: for internal-origin entries, the question is reconstructed from the snapshot row as long as it remains
• Only administrators may delete an entry. created_by records the registrant but does not affect who can delete it