[DM-05] Surveys table (configurable questionnaires)

{
  "cost": {
    "input_tokens": 23,
    "estimated_usd": 0.056994,
    "output_tokens": 3795
  },
  "model": "claude-sonnet-4-20250514",
  "status": "success",
  "transport": "anthropic_api",
  "latency_ms": 47143,
  "response_text": "I'll design a flexible surveys table that supports configurable questionnaires. Here's a comprehensive schema:\n\n```sql\n-- Main surveys table\nCREATE TABLE surveys (\n    survey_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n    title VARCHAR(255) NOT NULL,\n    description TEXT,\n    survey_type VARCHAR(50) DEFAULT 'questionnaire', -- questionnaire, poll, assessment, feedback\n    status VARCHAR(20) DEFAULT 'draft', -- draft, active, paused, completed, archived\n    \n    -- Configuration\n    settings JSONB DEFAULT '{}', -- Survey-level settings (randomize_questions, show_progress, etc.)\n    max_responses INTEGER,\n    allow_anonymous BOOLEAN DEFAULT true,\n    allow_multiple_responses BOOLEAN DEFAULT false,\n    \n    -- Scheduling\n    start_date TIMESTAMP WITH TIME ZONE,\n    end_date TIMESTAMP WITH TIME ZONE,\n    \n    -- Access control\n    is_public BOOLEAN DEFAULT false,\n    access_code VARCHAR(50), -- Optional access code for private surveys\n    target_audience JSONB, -- Criteria for who can take the survey\n    \n    -- Metadata\n    created_by UUID NOT NULL,\n    organization_id UUID,\n    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,\n    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,\n    \n    -- Constraints\n    CONSTRAINT valid_status CHECK (status IN ('draft', 'active', 'paused', 'completed', 'archived')),\n    CONSTRAINT valid_dates CHECK (start_date IS NULL OR end_date IS NULL OR start_date < end_date)\n);\n\n-- Survey sections for grouping questions\nCREATE TABLE survey_sections (\n    section_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n    survey_id UUID NOT NULL REFERENCES surveys(survey_id) ON DELETE CASCADE,\n    title VARCHAR(255),\n    description TEXT,\n    display_order INTEGER NOT NULL,\n    \n    -- Section behavior\n    is_required BOOLEAN DEFAULT true,\n    show_condition JSONB, -- Conditional logic for showing this section\n    \n    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP\n);\n\n-- Question types and templates\nCREATE TABLE question_types (\n    type_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n    name VARCHAR(100) NOT NULL UNIQUE,\n    display_name VARCHAR(100) NOT NULL,\n    description TEXT,\n    input_type VARCHAR(50) NOT NULL, -- text, number, email, select, multiselect, radio, checkbox, scale, date, file\n    default_settings JSONB DEFAULT '{}',\n    validation_rules JSONB DEFAULT '{}',\n    is_active BOOLEAN DEFAULT true,\n    \n    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP\n);\n\n-- Survey questions\nCREATE TABLE survey_questions (\n    question_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n    survey_id UUID NOT NULL REFERENCES surveys(survey_id) ON DELETE CASCADE,\n    section_id UUID REFERENCES survey_sections(section_id) ON DELETE SET NULL,\n    question_type_id UUID NOT NULL REFERENCES question_types(type_id),\n    \n    -- Question content\n    question_text TEXT NOT NULL,\n    help_text TEXT,\n    placeholder_text VARCHAR(255),\n    \n    -- Question settings\n    is_required BOOLEAN DEFAULT false,\n    display_order INTEGER NOT NULL,\n    settings JSONB DEFAULT '{}', -- Question-specific settings (min_length, max_length, scale_min, scale_max, etc.)\n    \n    -- Conditional logic\n    show_condition JSONB, -- When to show this question based on previous answers\n    validation_rules JSONB DEFAULT '{}',\n    \n    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP\n);\n\n-- Question options (for select, radio, checkbox questions)\nCREATE TABLE question_options (\n    option_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n    question_id UUID NOT NULL REFERENCES survey_questions(question_id) ON DELETE CASCADE,\n    option_text VARCHAR(500) NOT NULL,\n    option_value VARCHAR(255),\n    display_order INTEGER NOT NULL,\n    \n    -- Option behavior\n    is_other BOOLEAN DEFAULT false, -- \"Other\" option that allows text input\n    jump_to_question_id UUID REFERENCES survey_questions(question_id), -- Skip logic\n    \n    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP\n);\n\n-- Survey responses (one per participant)\nCREATE TABLE survey_responses (\n    response_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n    survey_id UUID NOT NULL REFERENCES surveys(survey_id) ON DELETE CASCADE,\n    \n    -- Respondent info\n    respondent_id UUID, -- NULL for anonymous responses\n    session_id VARCHAR(255), -- For tracking anonymous sessions\n    ip_address INET,\n    user_agent TEXT,\n    \n    -- Response metadata\n    status VARCHAR(20) DEFAULT 'in_progress', -- in_progress, completed, abandoned\n    start_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,\n    completion_time TIMESTAMP WITH TIME ZONE,\n    time_spent_seconds INTEGER,\n    \n    -- Response data\n    responses JSONB NOT NULL DEFAULT '{}', -- Structured response data\n    metadata JSONB DEFAULT '{}', -- Additional metadata (device info, referrer, etc.)\n    \n    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,\n    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,\n    \n    CONSTRAINT valid_response_status CHECK (status IN ('in_progress', 'completed', 'abandoned'))\n);\n\n-- Individual question answers\nCREATE TABLE question_answers (\n    answer_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n    response_id UUID NOT NULL REFERENCES survey_responses(response_id) ON DELETE CASCADE,\n    question_id UUID NOT NULL REFERENCES survey_questions(question_id) ON DELETE CASCADE,\n    \n    -- Answer data\n    answer_text TEXT,\n    answer_number DECIMAL,\n    answer_date DATE,\n    answer_options JSONB, -- For multi-select answers\n    file_attachments JSONB, -- For file upload questions\n    \n    -- Answer metadata\n    answered_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,\n    time_spent_seconds INTEGER,\n    \n    UNIQUE(response_id, question_id)\n);\n\n-- Survey analytics and reporting\nCREATE TABLE survey_analytics (\n    analytics_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n    survey_id UUID NOT NULL REFERENCES surveys(survey_id) ON DELETE CASCADE,\n    \n    -- Participation metrics\n    total_views INTEGER DEFAULT 0,\n    total_starts INTEGER DEFAULT 0,\n    total_completions INTEGER DEFAULT 0,\n    completion_rate DECIMAL(5,2),\n    average_time_minutes DECIMAL(8,2),\n    \n    -- Response metrics\n    last_response_at TIMESTAMP WITH TIME ZONE,\n    responses_today INTEGER DEFAULT 0,\n    responses_this_week INTEGER DEFAULT 0,\n    responses_this_month INTEGER DEFAULT 0,\n    \n    -- Question-level analytics\n    question_analytics JSONB DEFAULT '{}',\n    \n    calculated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP\n);\n\n-- Survey sharing and distribution\nCREATE TABLE survey_distributions (\n    distribution_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n    survey_id UUID NOT NULL REFERENCES surveys(survey_id) ON DELETE CASCADE,\n    \n    -- Distribution method\n    method VARCHAR(50) NOT NULL, -- email, link, embed, qr_code, social\n    channel VARCHAR(100), -- specific channel (email_campaign, website, facebook, etc.)\n    \n    -- Distribution settings\n    custom_url VARCHAR(255),\n    embed_code TEXT,\n    settings JSONB DEFAULT '{}',\n    \n    -- Tracking\n    views INTEGER DEFAULT 0,\n    responses INTEGER DEFAULT 0,\n    created_by UUID NOT NULL,\n    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP\n);\n\n-- Indexes for performance\nCREATE INDEX idx_surveys_status ON surveys(status);\nCREATE INDEX idx_surveys_created_by ON surveys(created_by);\nCREATE INDEX idx_surveys_organization ON surveys(organization_id);\nCREATE INDEX idx_surveys_dates ON surveys(start_date, end_date) WHERE start_date IS NOT NULL OR end_date IS NOT NULL;\n\nCREATE INDEX idx_survey_sections_survey ON survey_sections(survey_id, display_order);\nCREATE INDEX idx_survey_questions_survey ON survey_questions(survey_id, display_order);\nCREATE INDEX idx_survey_questions_section ON survey_questions(section_id, display_order);\nCREATE INDEX idx_question_options_question ON question_options(question_id, display_order);\n\nCREATE INDEX idx_survey_responses_survey ON survey_responses(survey_id);\nCREATE INDEX idx_survey_responses_status ON survey_responses(status);\nCREATE INDEX idx_survey_responses_completion ON survey_responses(completion_time) WHERE completion_time IS NOT NULL;\nCREATE INDEX idx_question_answers_response ON question_answers(response_id);\nCREATE INDEX idx_question_answers_question ON question_answers(question_id);\n\n-- Create GIN indexes for JSONB columns\nCREATE INDEX idx_surveys_settings_gin ON surveys USING GIN(settings);\nCREATE INDEX idx_survey_questions_settings_gin ON survey_questions USING GIN(settings);\nCREATE INDEX idx_survey_responses_responses_gin ON survey_responses USING GIN(responses);\nCREATE INDEX idx_survey_responses_metadata_gin ON survey_responses USING GIN(metadata);\n\n-- Insert default question types\nINSERT INTO question_types (name, display_name, description, input_type, default_settings) VALUES\n('short_text', 'Short Text', 'Single line text input', 'text', '{\"max_length\": 255}'),\n('long_text', 'Long Text', 'Multi-line text area', 'textarea', '{\"max_length\": 2000}'),\n('email', 'Email', 'Email address input', 'email', '{}'),\n('number', 'Number', 'Numeric input', 'number', '{\"allow_decimal\": true}'),\n('single_choice', 'Single Choice', 'Radio buttons or dropdown', 'radio', '{\"display_as\": \"radio\"}'),\n('multiple_choice', 'Multiple Choice', 'Checkboxes for multiple selections', 'checkbox', '{}'),\n('dropdown', 'Dropdown', 'Dropdown selection', 'select', '{}'),\n('rating_scale', 'Rating Scale', 'Numeric rating scale', 'scale', '{\"min\": 1, \"max\": 5, \"step\": 1}'),\n('likert_scale', 'Likert Scale', 'Agreement scale', 'likert', '{\"options\": [\"Strongly Disagree\", \"Disagree\", \"Neutral\", \"Agree\", \"Strongly Agree\"]}'),\n('yes_no', 'Yes/No', 'Simple yes/no question', 'radio', '{\"options\": [{\"text\": \"Yes\", \"value\": \"yes\"}, {\"text\": \"No\", \"value\": \"no\"}]}'),\n('date', 'Date', 'Date picker', 'date', '{}'),\n('file_upload', 'File Upload', 'File attachment', 'file', '{\"max_files\": 1, \"max_size_mb\": 10}');\n\n-- Views for common queries\nCREATE VIEW survey_summary AS\nSELECT \n    s.survey_id,\n    s.title,\n    s.status,\n    s.created_at,\n    COUNT(DISTINCT sr.response_id) as total_responses,\n    COUNT(DISTINCT CASE WHEN sr.status = 'completed' THEN sr.response_id END) as completed_responses,\n    ROUND(\n        COUNT(DISTINCT CASE WHEN sr.status = 'completed' THEN sr.response_id END)::numeric * 100.0 / \n        NULLIF(COUNT(DISTINCT sr.response_id), 0), 2\n    ) as completion_rate,\n    AVG(CASE WHEN sr.status = 'completed' THEN sr.time_spent_seconds END) as avg_completion_time_seconds\nFROM surveys s\nLEFT JOIN survey_responses sr ON s.survey_id = sr.survey_id\nGROUP BY s.survey_id, s.title, s.status, s.created_at;\n\n-- Function to update analytics\nCREATE OR REPLACE FUNCTION update_survey_analytics(p_survey_id UUID)\nRETURNS VOID AS $$\nBEGIN\n    INSERT INTO survey_analytics (\n        survey_id,\n        total_views,\n        total_starts,\n        total_completions,\n        completion_rate,\n        average_time_minutes,\n        last_response_at,\n        responses_today,\n        responses_this_week,\n        responses_this_month\n    )\n    SELECT \n        p_survey_id,\n        COUNT(DISTINCT sr.response_id) as total_views,\n        COUNT(DISTINCT sr.response_id) as total_starts,\n        COUNT(DISTINCT CASE WHEN sr.status = 'completed' THEN sr.response_id END) as total_completions,\n        ROUND(\n            COUNT(DISTINCT CASE WHEN sr.status = 'completed' THEN sr.response_id END)::numeric * 100.0 / \n            NULLIF(COUNT(DISTINCT sr.response_id), 0), 2\n        ) as completion_rate,\n        ROUND(AVG(CASE WHEN sr.status = 'completed' THEN sr.time_spent_seconds END) / 60.0, 2) as average_time_minutes,\n        MAX(sr.created_at) as last_response_at,\n        COUNT(DISTINCT CASE WHEN sr.created_at >= CURRENT_DATE THEN sr.response_id END) as responses_today,\n        COUNT(DISTINCT CASE WHEN sr.created_at >= CURRENT_DATE - INTERVAL '7 days' THEN sr.response_id END) as responses_this_week,\n        COUNT(DISTINCT CASE WHEN sr.created_at >= CURRENT_DATE - INTERVAL '30 days' THEN sr.response_id END) as responses_this_month\n    FROM survey_responses sr\n    WHERE sr.survey_id = p_survey_id\n    ON CONFLICT (survey_id) DO UPDATE SET\n        total_views = EXCLUDED.total_views,\n        total_starts = EXCLUDED.total_starts,\n        total_completions = EXCLUDED.total_completions,\n        completion_rate = EXCLUDED.completion_rate,\n        average_time_minutes = EXCLUDED.average_time_minutes,\n        last_response_at = EXCLUDED.last_response_at,\n        responses_today = EXCLUDED.responses_today,\n        responses_this_week = EXCLUDED.responses_this_week,\n        responses_this_month = EXCLUDED.responses_this_month,\n        calculated_at = CURRENT_TIMESTAMP;\nEND;\n$$ LANGUAGE plpgsql;\n```\n\nThis survey system supports:\n\n**Key Features:**\n- Flexible question types with configurable settings\n- Survey sections for organization\n- Conditional logic and skip patterns\n- Anonymous and authenticated responses\n- Real-time analytics tracking\n- Multiple distribution methods\n- File upload capabilities\n- Progress tracking and session management\n\n**Question Types:**\n- Text inputs (short/long)\n- Single/multiple choice\n- Rating scales and Likert scales\n- Date selection\n- File uploads\n- Email validation\n- Numeric inputs\n\n**Advanced Capabilities:**\n- JSONB storage for flexible configuration\n- Conditional question display\n- Response validation\n- Time tracking\n- Comprehensive analytics\n- Survey sharing and embedding\n\nThe schema is designed to be highly configurable while maintaining performance through proper indexing and efficient query patterns."
}