external_surveys

Overview

Questionnaires imported from external tools such as Creative Survey (CS). The raw imported JSON is kept as-is.

Table Definition

Logical name	Physical name	Column	Type	PK	Relation	Unique	Nullable	Default	Notes
External questionnaire	external_surveys	id	uuid	◯				gen_random_uuid()
		source	varchar(50)						Source identifier. Currently creative_survey
		external_id	bigint						Questionnaire ID on the external tool's side
		origin	varchar(255)						Origin the data came from (e.g. https://4dsd.svy.ooo)
		name	text						Questionnaire name
		raw_survey	jsonb						Raw JSON of the CS questionnaire object
		raw_questionnaire	jsonb				◯		Raw JSON of the CS question-structure object
		generate_order	jsonb				◯		Page layout: a two-dimensional array of question IDs
		fetched_at	timestamptz				◯		When the extension fetched it from CS
		imported_at	timestamptz					now()	Import timestamp
		imported_by	uuid		users:id (set null)		◯		Who performed the import

Relations

• imported_by → users.id (onDelete: set null)
• Referenced by: external_questions.external_survey_id (cascade)
• Referenced by: question_library_items.source_external_survey_id (cascade)

Indexes

• Primary key (id)
• UNIQUE INDEX: external_surveys_source_external_id_idx (source, external_id)

Notes

• Behavior on re-import: if (source, external_id) already exists, the row is deleted and re-inserted. Its questions, choices, and branches cascade, so repeated imports never produce duplicates
• Why raw JSON is kept: the CS API is undocumented, so normalized columns alone would lose information. Keeping the raw JSON allows re-analysis once more of the specification is understood
• generate_order represents the CS page layout (which questions are grouped onto the same page)
• At import time, each question is also registered into question_library_items as external-origin data