DB Schema: the four tables you care about

templates, assessments, responses, llm_keys

Other tables (user, session, account, promo_codes) exist but are scaffolding. These four are the product.

templates - the questionnaire definition

templates
  id: text (nanoid PK)
  userId: text (owner)
  title: text
  topic: text
  context: text (nullable - AI prompt context)
  targetAudience: text
  scaleLength: integer (default 5)
  scaleLevels: jsonb ScaleLevel[] (1..5 with label + description)
  domains: jsonb Domain[] (each has questions: Question[])
  visibility: text (private | public)
  clonedFromId: text (nullable - set when cloned)
  generatedByAi: boolean
  researchBrief: text (nullable - from research pipeline)

assessments - the instance sent to a team

assessments
  id: text (nanoid PK)
  userId: text (owner)
  templateId: text (FK to templates - no constraint, just reference)
  title: text
  description: text (nullable)
  status: text (draft | active | closed, default draft)
  inviteToken: text (unique - the shareable link key)
  teamName: text (nullable)
  dueDate: timestamp (nullable)

responses - one person's answers

responses
  id: text (nanoid PK)
  assessmentId: text (FK to assessments)
  respondentName: text (nullable)
  respondentRole: text (nullable)
  answers: jsonb Record<questionId, number|string> (default {})
  submittedAt: timestamp

llm_keys - encrypted BYOK credentials

llm_keys
  id: text (nanoid PK)
  userId: text (unique - one key per user)
  provider: text (openai | bedrock)
  encryptedKey: text (AES-256-GCM via lib/crypto.ts)
  keyHint: text (e.g. sk-...xxxx)
  model: text (nullable)
  apiFormat: text (openai | anthropic, default anthropic)
  awsRegion: text (nullable - Bedrock only)
  awsAccessKeyId: text (nullable - Bedrock only)
  encryptedAwsSecretKey: text (nullable - Bedrock only)

One row per user (unique constraint on userId). Encrypted with BETTER_AUTH_SECRET via PBKDF2 + AES-GCM in lib/crypto.ts.

The query pattern

Drizzle queries in this repo follow one pattern. Read it once, you can read them all:

// This is the shape of EVERY query in app/actions/*.ts

import { db } from '@/lib/db'
import { templates } from '@/lib/db/schema'
import { eq, and, desc } from 'drizzle-orm'

// Single row lookup
const template = await db
  .select()
  .from(templates)
  .where(eq(templates.id, templateId))
  .limit(1)
  .then((r) => r[0])

// List with filter + sort
const myTemplates = await db
  .select()
  .from(templates)
  .where(eq(templates.userId, userId))
  .orderBy(desc(templates.createdAt))

// Insert
await db.insert(templates).values({ id: nanoid(), userId, title, ... })

// Update
await db.update(templates)
  .set({ title: newTitle, updatedAt: new Date() })
  .where(eq(templates.id, id))

// Delete
await db.delete(templates).where(eq(templates.id, id))

Predict what this returns

Look at the query below. What shape does result have?

const result = await db
  .select()
  .from(assessments)
  .where(
    and(
      eq(assessments.userId, userId),
      eq(assessments.status, 'active')
    )
  )
  .orderBy(desc(assessments.createdAt))

The schema is the Rosetta Stone. When you read app/actions/templates.ts and see .where(eq(templates.userId, userId)), you now know it is filtering the templates table by owner. When you see .set({ status: 'active' }), you know it is updating the assessments table. The schema tells you what every query touches.

Primary source

Read lib/db/schema.ts line by line. It is 185 lines. Every type (ScaleLevel, Domain, Question, Visibility, AssessmentStatus, LlmProvider, ApiFormat) is defined here. This file is the single source of truth for what data the app stores.

Citations: schema from lib/db/schema.ts:1-185, query patterns from app/actions/templates.ts and app/actions/assessments.ts.

Lesson 4 - Auth Guards: why some routes need login

You will learn the two-layer auth system: layout-level guard in app/(app)/layout.tsx and action-level guard in getUserId(). Plus why /respond/[token] is public.