surveys

Overview

Surveys, holding the theme, status, and latest version number. The questionnaire content itself lives in the version snapshots.

Table Definition

Logical name	Physical name	Column	Type	PK	Relation	Unique	Nullable	Default	Notes
Survey	surveys	id	uuid	◯				gen_random_uuid()
		title	varchar(255)						Survey title
		client_name	varchar(255)						Client name
		objective	text						Research objective; preset selection and free input serialized into one string
		delivery_area	varchar(255)						Delivery area, same as above
		category	varchar(255)						Category, same as above
		prompt_text	text					''	Free text
		status	survey_status					下書き	Draft / In Review / Exported / Archived
		latest_version_no	integer					1	Version number of the latest version
		created_by	uuid		users:id (restrict)				Creator. Only the creator and administrators may delete
		updated_by	uuid		users:id (restrict)				Last updater
		created_at	timestamptz					now()
		updated_at	timestamptz					now()	The list sorts by updated time descending by default
		deleted_at	timestamptz				◯		Soft delete. null = not deleted

Relations

• created_by → users.id (onDelete: restrict)
• updated_by → users.id (onDelete: restrict)
• Referenced by: survey_permissions.survey_id (cascade)
• Referenced by: survey_versions.survey_id (cascade)
• Referenced by: survey_version_pointers.survey_id (cascade)
• Referenced by: user_survey_favorites.survey_id (cascade)
• Referenced by: question_library_items.source_survey_id (cascade)

Indexes

• Primary key (id)

Notes

• This is the only table in the system using soft deletion. Read queries always include deleted_at IS NULL
• objective / delivery_area / category are single strings in the database, but the theme API returns them split into "preset selection" and "free input"
• Running the JSON export (POST /api/surveys/{surveyId}/export) moves status to Exported automatically
• latest_version_no increases on every save and rollback. Identifying the latest version also uses survey_version_pointers