Surveys - Developer Guide

This guide covers the technical architecture, database schema, and implementation details of the Survey feature in Pawtograder.

Overview

The survey system allows instructors to create custom surveys, collect student responses, and analyze feedback. It uses SurveyJS for the question builder and renderer.

Architecture

Tech Stack

Layer	Technology
Frontend	Next.js 15, React 18, Chakra UI
Survey Engine	SurveyJS (survey-core, survey-react-ui, survey-creator-react)
Backend	Supabase (PostgreSQL + Row Level Security)
State Management	React Hook Form, Refine
Date Handling	date-fns, date-fns-tz

Key Dependencies

{
  "survey-core": "^2.3.11",
  "survey-creator-core": "^2.3.11",
  "survey-creator-react": "^2.3.11",
  "survey-react-ui": "^2.3.11"
}

Database Schema

Tables

`surveys`

Main table storing survey definitions.

CREATE TABLE surveys (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    survey_id UUID NOT NULL DEFAULT gen_random_uuid(),  -- Logical ID for versioning
    class_id BIGINT NOT NULL REFERENCES classes(id) ON DELETE CASCADE,
    created_by UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
    title TEXT NOT NULL,
    description TEXT,
    json JSONB NOT NULL DEFAULT '[]'::jsonb,  -- SurveyJS JSON configuration
    status survey_status NOT NULL DEFAULT 'draft',
    allow_response_editing BOOLEAN NOT NULL DEFAULT FALSE,
    due_date TIMESTAMPTZ DEFAULT NULL,
    available_at TIMESTAMPTZ DEFAULT NULL,  -- When survey becomes visible
    assignment_id BIGINT REFERENCES assignments(id) ON DELETE SET NULL,  -- Optional link to assignment
    assigned_to_all BOOLEAN NOT NULL DEFAULT TRUE,
    validation_errors TEXT DEFAULT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMPTZ DEFAULT NULL,  -- Soft delete
    version INTEGER NOT NULL DEFAULT 1,
    type survey_type NOT NULL DEFAULT 'assign_all',
    series_id UUID REFERENCES survey_series(id) ON DELETE SET NULL,  -- Optional series link
    series_ordinal INTEGER DEFAULT NULL,  -- Position in series (Week 1, 2, etc.)
    analytics_config JSONB DEFAULT NULL  -- Per-question analytics configuration
);

`survey_series`

Stores survey series for trend analysis.

CREATE TABLE survey_series (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    class_id BIGINT NOT NULL REFERENCES classes(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    description TEXT,
    created_by UUID REFERENCES profiles(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

`assignment_groups`

Updated to include mentor assignments.

ALTER TABLE assignment_groups
ADD COLUMN mentor_profile_id UUID REFERENCES profiles(id) ON DELETE SET NULL;

`survey_responses`

Stores individual student responses.

CREATE TABLE survey_responses (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    survey_id UUID NOT NULL REFERENCES surveys(id) ON DELETE CASCADE,
    profile_id UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
    response JSONB NOT NULL DEFAULT '{}'::jsonb,  -- Keyed by question name
    submitted_at TIMESTAMPTZ DEFAULT NULL,
    is_submitted BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMPTZ DEFAULT NULL,
    CONSTRAINT survey_responses_unique_per_profile UNIQUE (survey_id, profile_id)
);

`survey_assignments`

Maps surveys to specific students (when not assigned to all).

CREATE TABLE survey_assignments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    survey_id UUID NOT NULL REFERENCES surveys(id) ON DELETE CASCADE,
    profile_id UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT survey_assignments_unique_per_profile UNIQUE (survey_id, profile_id)
);

`survey_templates`

Reusable survey templates.

CREATE TABLE survey_templates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title TEXT NOT NULL,
    description TEXT NOT NULL DEFAULT '',
    template JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
    version INTEGER NOT NULL DEFAULT 1,
    scope template_scope NOT NULL DEFAULT 'course',  -- 'global' | 'course'
    class_id BIGINT NOT NULL REFERENCES classes(id) ON DELETE CASCADE
);

Enum Types

CREATE TYPE survey_status AS ENUM ('draft', 'published', 'closed');
CREATE TYPE template_scope AS ENUM ('global', 'course');
CREATE TYPE survey_type AS ENUM ('assign_all', 'specific', 'peer');

Database Triggers

Trigger	Table	Purpose
`update_surveys_updated_at`	surveys	Auto-update `updated_at` on changes
`update_survey_responses_updated_at`	survey_responses	Auto-update `updated_at` on changes
`update_survey_templates_updated_at`	survey_templates	Auto-update `updated_at` on changes
`set_survey_submitted_at_trigger`	survey_responses	Auto-set `submitted_at` when `is_submitted` flips to true

RPC Functions

`soft_delete_survey(p_survey_id UUID, p_survey_logical_id UUID)`

Atomically soft-deletes a survey and all its responses.

-- Usage
SELECT soft_delete_survey('physical-uuid', 'logical-uuid');

`create_survey_assignments(p_survey_id UUID, p_profile_ids UUID[])`

Bulk creates survey assignments for specific students.

-- Usage
SELECT create_survey_assignments('survey-uuid', ARRAY['profile-1', 'profile-2']);

`get_survey_status_for_assignment(p_assignment_id BIGINT, p_profile_id UUID)`

Returns survey completion status for a student on a specific assignment.

-- Usage
SELECT * FROM get_survey_status_for_assignment(123, 'profile-uuid');

Returns surveys linked to the assignment with completion status for the specified student.

`get_survey_responses_with_group_context(p_class_id BIGINT, p_survey_id UUID)`

Aggregates survey responses with group and mentor information for analytics.

-- Usage
SELECT * FROM get_survey_responses_with_group_context(456, 'survey-uuid');

Returns response data joined with group membership, mentor assignments, and section information.

Row Level Security (RLS)

Survey Policies

Policy	Role	Access
`surveys_select_staff`	Instructors, Graders	Read all active surveys in their classes
`surveys_select_students`	Students	Read published/closed surveys they’re assigned to
`surveys_insert_instructors`	Instructors	Create surveys
`surveys_update_instructors`	Instructors	Update surveys

Response Policies

Policy	Role	Access
`survey_responses_select_owner`	Owner	Read own responses
`survey_responses_select_staff`	Staff	Read all responses for surveys in their classes
`survey_responses_insert_owner`	Owner	Create responses
`survey_responses_update_owner`	Owner	Update own responses

TypeScript Types

// types/survey.ts
import type { Tables } from "@/utils/supabase/SupabaseTypes";

export type Survey = Tables<"surveys">;
export type SurveyResponse = Tables<"survey_responses">;
export type SurveyAssignee = Tables<"survey_assignments">;

export type SurveyWithResponse = Survey & {
  response_status: "not_started" | "in_progress" | "completed";
  submitted_at?: string | null;
  is_submitted?: boolean;
};

export type SurveyWithCounts = Survey & {
  response_count: number;
  submitted_count: number;
  assigned_student_count: number;
};

export type SurveyResponseWithProfile = SurveyResponse & {
  profiles: {
    id: string;
    name: string | null;
    sis_user_id?: string | null;
  };
};

File Structure

app/course/[course_id]/
├── surveys/                          # Student-facing
│   ├── page.tsx                      # Survey list
│   └── [survey_id]/
│       ├── page.tsx                  # Take survey
│       └── submit.ts                 # Save/load response utilities
└── manage/surveys/                   # Instructor-facing
    ├── page.tsx                      # Dashboard
    ├── SurveysTable.tsx              # Survey list table
    ├── SurveysHeader.tsx             # Page header
    ├── EmptySurveysState.tsx         # Empty state
    ├── new/
    │   ├── page.tsx                  # Create survey page
    │   └── form.tsx                  # Survey form component
    ├── preview/
    │   └── page.tsx                  # Preview survey
    └── [survey_id]/
        ├── edit/
        │   └── page.tsx              # Edit survey
        └── responses/
            ├── page.tsx              # Response dashboard
            ├── SurveyResponsesView.tsx  # Response table
            └── [response_id]/
                └── page.tsx          # Individual response

components/survey/
├── SurveyBuilder.tsx                 # Visual survey builder
├── SurveyBuilderModal.tsx            # Builder modal wrapper
├── SurveyBuilderDataTypes.ts         # Builder type definitions
├── SurveyFilterButtons.tsx           # Filter UI component
├── SurveyTemplateLibraryModal.tsx    # Template management
├── factories.ts                      # Builder factory functions
├── helpers.ts                        # Builder helper functions
└── serde.ts                          # Serialization/deserialization

components/
├── Survey.tsx                        # SurveyJS renderer
└── survey-preview-modal.tsx          # Preview modal

SurveyJS Integration

Rendering Surveys

import { Model } from "survey-core";
import { Survey } from "survey-react-ui";

function SurveyComponent({ surveyJson, initialData, onComplete }) {
  const survey = new Model(surveyJson);

  if (initialData) {
    survey.data = initialData;
  }

  survey.onComplete.add((sender) => {
    onComplete(sender.data);
  });

  return <Survey model={survey} />;
}

Survey JSON Structure

{
  "pages": [
    {
      "name": "page1",
      "elements": [
        {
          "type": "text",
          "name": "question1",
          "title": "What is your name?",
          "isRequired": true
        },
        {
          "type": "radiogroup",
          "name": "satisfaction",
          "title": "How satisfied are you?",
          "choices": [
            { "value": "1", "text": "Very Unsatisfied" },
            { "value": "5", "text": "Very Satisfied" }
          ]
        }
      ]
    }
  ]
}

Supported Question Types

Type	SurveyJS Type	Description
Short Text	`text`	Single-line text input
Long Text	`comment`	Multi-line textarea
Single Choice	`radiogroup`	Radio button selection
Multiple Choice	`checkbox`	Checkbox selection
Yes/No	`boolean`	Binary toggle

Response Handling

Saving Responses

// app/course/[course_id]/surveys/[survey_id]/submit.ts
export async function saveResponse(
  surveyId: string,
  profileId: string,
  responseData: Record<string, unknown>,
  isSubmitted: boolean
) {
  const supabase = createClient();

  const { data, error } = await supabase
    .from("survey_responses")
    .upsert(
      {
        survey_id: surveyId,
        profile_id: profileId,
        response: responseData,
        is_submitted: isSubmitted
      },
      { onConflict: "survey_id,profile_id" }
    )
    .select()
    .single();

  return { data, error };
}

Auto-save on Value Change

When allow_response_editing is enabled, responses are auto-saved:

const handleValueChanged = async (surveyModel: Model) => {
  if (!survey.allow_response_editing) return;

  await saveResponse(
    survey.id,
    profileId,
    surveyModel.data,
    false  // Not submitted yet
  );
};

CSV Export

The export functionality includes security protections against CSV injection:

function escapeCSVValue(value: unknown): string {
  const stringValue = String(value);
  const trimmed = stringValue.trimStart();

  // Prevent CSV formula injection (OWASP CWE-1236)
  if (["=", "+", "-", "@"].includes(trimmed[0] ?? "")) {
    return `"'${stringValue.replace(/"/g, '""')}"`;
  }

  if (stringValue.includes(",") || stringValue.includes('"') || stringValue.includes("\n")) {
    return `"${stringValue.replace(/"/g, '""')}"`;
  }

  return stringValue;
}

Testing

E2E Tests

Survey E2E tests are located at tests/e2e/surveys.test.tsx.

# Run survey tests
npx playwright test surveys

Manual Testing

Create a test class with npm run seed
Log in as an instructor
Navigate to Surveys to test CRUD operations
Log in as a student to test survey taking

Common Development Tasks

Adding a New Question Type

Update SurveyBuilderDataTypes.ts:

export type ElementType = "text" | "comment" | "radiogroup" | "checkbox" | "boolean" | "newtype";

Update the builder UI in SurveyBuilder.tsx
Add rendering support in Survey.tsx (usually automatic via SurveyJS)

Modifying the Database Schema

Create a new migration:

npx supabase migration new your_migration_name

Write your SQL in supabase/migrations/[timestamp]_your_migration_name.sql
Apply locally:
npx supabase db reset
Regenerate types:
npm run client-local

Adding RLS Policies

-- Example: Allow graders to delete responses
CREATE POLICY survey_responses_delete_staff ON survey_responses
  FOR DELETE
  USING (
    EXISTS (
      SELECT 1 FROM surveys s
      JOIN user_privileges up ON up.class_id = s.class_id
      WHERE s.id = survey_responses.survey_id
        AND up.user_id = auth.uid()
        AND up.role IN ('instructor', 'grader')
    )
  );

Troubleshooting

Survey not appearing for students

Check survey status is published
Verify deleted_at is NULL
If using specific assignments, confirm student is in survey_assignments
Check RLS policies are not blocking access

Response not saving

Verify the student’s profile_id matches their class enrollment
Check for unique constraint violations (duplicate responses)
Verify is_submitted trigger is working

Visual Builder not updating JSON

Check browser console for JavaScript errors
Verify onChange callback is properly wired
Check for JSON serialization issues in serde.ts

​Surveys - Developer Guide

​Overview

​Architecture

​Tech Stack

​Key Dependencies

​Database Schema

​Tables

​surveys

​survey_series

​assignment_groups

​survey_responses

​survey_assignments

​survey_templates

​Enum Types

​Database Triggers

​RPC Functions

​soft_delete_survey(p_survey_id UUID, p_survey_logical_id UUID)

​create_survey_assignments(p_survey_id UUID, p_profile_ids UUID[])

​get_survey_status_for_assignment(p_assignment_id BIGINT, p_profile_id UUID)

​get_survey_responses_with_group_context(p_class_id BIGINT, p_survey_id UUID)

​Row Level Security (RLS)

​Survey Policies

​Response Policies

​TypeScript Types

​File Structure

​SurveyJS Integration

​Rendering Surveys

​Survey JSON Structure

​Supported Question Types

​Response Handling

​Saving Responses

​Auto-save on Value Change

​CSV Export

​Testing

​E2E Tests

​Manual Testing

​Common Development Tasks

​Adding a New Question Type

​Modifying the Database Schema

​Adding RLS Policies

​Troubleshooting

​Survey not appearing for students

​Response not saving

​Visual Builder not updating JSON