Surveys

This page documents the database schema used to manage surveys, their questions, and recorded responses. It mirrors the style used by the other dataschema pages: a visual diagram followed by a per-table breakdown with field definitions, indexes, and relationships.

Tables

surveys

Top-level survey metadata (title, description, context, and survey type).

View fields and relationships

• id: UUID, Primary Key
• created_at: Timestamp with time zone, The time when the survey was created.
• title: Text, Human-readable title for the survey.
• description: Text, Longer description shown to users.
• context: Text, Free-form context string describing where/why the survey is used.
• type: survey_types, Enum describing the kind of survey (e.g., intake, feedback, post-study).
• Indexes: Primary Key on id.
• Relationships: One-to-Many with survey_questions and survey_responses (each question and response references surveys.id).

survey_questions

Questions associated with a survey. Questions support different types and optional choice lists.

View fields and relationships

• id: UUID, Primary Key
• created_at: Timestamp with time zone, When the question was created.
• survey_id: UUID, Foreign Key referencing surveys(id) — the survey that owns this question.
• question_type: Text, The input type for the question (e.g., text, multiple_choice, checkbox, scale).
• question_text: Text, The question prompt shown to the user.
• question_options: Text[], Optional list of choices for choice-based questions.
• is_required: Boolean, Whether answering the question is required.
• question_number: Integer, Ordering index for rendering questions within a survey.
• Indexes: Primary Key on id, Foreign Key index on survey_id.
• Relationships: Many-to-One with surveys.

Notes:

• question_options is stored as an array of text values; consumer code should validate option formats and ordering.
• question_number should be unique per survey_id if strict ordering is required by the application.

survey_responses

Stores user-submitted responses. Answers are stored in a JSONB blob to allow flexible question shapes and typed answers.

View fields and relationships

• id: UUID, Primary Key
• created_at: Timestamp with time zone, When the response was submitted.
• survey_id: UUID, Foreign Key referencing surveys(id) — the survey answered.
• user_id: UUID, Foreign Key referencing users(id) — the user who submitted the response. May be null if anonymous responses are allowed.
• answers: JSONB, A JSON object mapping question_number or question_id to the user's answer.
• Indexes: Primary Key on id; recommended indexes on survey_id and user_id for query performance.
• Relationships: Many-to-One with surveys, Many-to-One with users.

Notes:

• The answers JSONB schema is intentionally flexible to support multiple question types and evolving forms; consumers should validate and normalize answers when reading.
• If anonymity is required, user_id may be left null and any identifying fields should be omitted.

user table

The users table is referenced in this schema to establish relationships with user-submitted responses. See the Users & Classroom dataschema for the full users table definition. See full definiton in the Users & Classroom.