Surveys Schema Deep Dive

Overview

The surveys module provides a flexible survey system with two table types: surveys for survey definitions and survey_responses for collected answers. Surveys can be either global (site-wide) or item-specific. Survey structure is stored as a JSON blob (surveyJson) using the JSONB column type, allowing dynamic question schemas without rigid database modeling.

Source file: template/lib/db/schema.ts

---

Table: surveys

Stores survey definitions with their question structure in a JSON column.

Columns

Column	DB Name	Type	Nullable	Default	Constraints
id	id	text	No	crypto.randomUUID()	Primary Key
slug	slug	text	No	-	Unique
title	title	text	No	-	-
description	description	text	Yes	-	-
type	type	text (enum)	No	-	global, item
itemId	item_id	text	Yes	-	Item slug (for item surveys)
status	status	text (enum)	No	'draft'	draft, published, closed
surveyJson	survey_json	jsonb	No	-	Full survey structure
createdAt	created_at	timestamp (tz)	No	now()	-
updatedAt	updated_at	timestamp (tz)	No	now()	-
publishedAt	published_at	timestamp (tz)	Yes	-	-
closedAt	closed_at	timestamp (tz)	Yes	-	-
deletedAt	deleted_at	timestamp (tz)	Yes	-	Soft delete

Indexes

Name	Columns	Type
surveys_slug_idx	slug	B-tree
surveys_type_idx	type	B-tree
surveys_item_id_idx	itemId	B-tree
surveys_status_idx	status	B-tree
surveys_created_at_idx	createdAt	B-tree

Survey Type Enum

Value	Description
global	Site-wide survey visible to all users
item	Survey attached to a specific item (referenced by itemId)

Survey Status Enum

Value	Description
draft	Not yet published, only visible to admins
published	Live and accepting responses
closed	No longer accepting responses

---

Table: survey_responses

Stores individual user responses to surveys. Response data is stored as a JSONB blob.

Columns

Column	DB Name	Type	Nullable	Default	Constraints
id	id	text	No	crypto.randomUUID()	Primary Key
surveyId	survey_id	text	No	-	FK -> surveys.id (RESTRICT)
userId	user_id	text	Yes	-	FK -> users.id (SET NULL)
itemId	item_id	text	Yes	-	Item context slug
data	data	jsonb	No	-	Response answers
completedAt	completed_at	timestamp (tz)	No	-	When user finished
ipAddress	ip_address	text	Yes	-	Submitter IP
userAgent	user_agent	text	Yes	-	Browser user agent
createdAt	created_at	timestamp (tz)	No	now()	-
updatedAt	updated_at	timestamp (tz)	No	now()	-

Foreign Keys

Column	References	On Delete
surveyId	surveys.id	RESTRICT
userId	users.id	SET NULL

DELETE Behavior

The surveyId foreign key uses RESTRICT (not CASCADE), meaning a survey cannot be deleted while it has responses. This protects response data from accidental loss. Use soft-delete (deletedAt) on the survey instead.

The userId foreign key uses SET NULL, preserving anonymous response data even when a user account is deleted.

Indexes

Name	Columns	Type
survey_responses_survey_id_idx	surveyId	B-tree
survey_responses_user_id_idx	userId	B-tree
survey_responses_item_id_idx	itemId	B-tree
survey_responses_completed_at_idx	completedAt	B-tree

---

TypeScript Types

export type Survey = typeof surveys.$inferSelect;

export type SurveyItem = Survey & {
    responseCount?: number;
    isCompletedByUser?: boolean;
};

export type NewSurvey = typeof surveys.$inferInsert;
export type SurveyResponse = typeof surveyResponses.$inferSelect;
export type NewSurveyResponse = typeof surveyResponses.$inferInsert;

---

Relations Diagram

---

Survey Lifecycle

---

The surveyJson Column

The surveyJson JSONB column stores the complete survey definition. This is a flexible schema that can represent various question types:

// Example surveyJson structure
{
  "pages": [
    {
      "name": "page1",
      "elements": [
        {
          "type": "rating",
          "name": "satisfaction",
          "title": "How satisfied are you?",
          "rateMin": 1,
          "rateMax": 5
        },
        {
          "type": "text",
          "name": "feedback",
          "title": "Any additional feedback?"
        },
        {
          "type": "radiogroup",
          "name": "recommend",
          "title": "Would you recommend this?",
          "choices": ["Yes", "No", "Maybe"]
        }
      ]
    }
  ]
}

---

Query Examples

Create a survey

import { db } from '@/lib/db/drizzle';
import { surveys } from '@/lib/db/schema';

await db.insert(surveys).values({
    slug: 'user-satisfaction-2025',
    title: 'User Satisfaction Survey 2025',
    description: 'Help us improve our platform',
    type: 'global',
    status: 'draft',
    surveyJson: {
        pages: [{
            name: 'page1',
            elements: [
                { type: 'rating', name: 'overall', title: 'Overall satisfaction' }
            ]
        }]
    },
});

Publish a survey

await db
    .update(surveys)
    .set({
        status: 'published',
        publishedAt: new Date(),
        updatedAt: new Date(),
    })
    .where(eq(surveys.id, surveyId));

Submit a response

import { surveyResponses } from '@/lib/db/schema';

await db.insert(surveyResponses).values({
    surveyId,
    userId,
    itemId: 'specific-item-slug', // Optional, for item-type surveys
    data: {
        overall: 4,
        feedback: 'Great platform, minor UI issues',
        recommend: 'Yes',
    },
    completedAt: new Date(),
    ipAddress: request.headers.get('x-forwarded-for'),
    userAgent: request.headers.get('user-agent'),
});

Get surveys with response counts

import { sql } from 'drizzle-orm';

const surveysWithCounts = await db
    .select({
        id: surveys.id,
        title: surveys.title,
        status: surveys.status,
        responseCount: sql<number>`(
            SELECT count(*) FROM survey_responses
            WHERE survey_responses.survey_id = surveys.id
        )`,
    })
    .from(surveys)
    .where(isNull(surveys.deletedAt))
    .orderBy(desc(surveys.createdAt));

Check if user completed a survey

const completed = await db
    .select({ id: surveyResponses.id })
    .from(surveyResponses)
    .where(
        and(
            eq(surveyResponses.surveyId, surveyId),
            eq(surveyResponses.userId, userId)
        )
    )
    .limit(1);

const hasCompleted = completed.length > 0;

Get published surveys for an item

const itemSurveys = await db
    .select()
    .from(surveys)
    .where(
        and(
            eq(surveys.type, 'item'),
            eq(surveys.itemId, 'my-item-slug'),
            eq(surveys.status, 'published'),
            isNull(surveys.deletedAt)
        )
    );

---

Design Notes

• JSONB for flexibility. Using surveyJson and data as JSONB columns allows the survey system to support any question type without schema migrations. The trade-off is less strict type safety at the database level.
• RESTRICT on delete. Surveys with responses cannot be hard-deleted. Use the deletedAt soft-delete column instead.
• Anonymous responses supported. The userId on survey_responses is nullable and uses SET NULL on delete, allowing both authenticated and anonymous survey submissions.
• Item context. The itemId field on both tables enables item-specific surveys (e.g., "Rate this tool") while keeping the schema generic enough for global surveys.