Example

Offline-First Inspection Questionnaire Architecture (Clean Reference)

This guide normalizes a practical architecture for large inspection forms (300-400 questions), such as SIRE-style vetting workflows.

It focuses on:

questionnaire versioning
reproducible reports
partial completion (for example, 20-30 answers out of 385)
strong offline behavior with later synchronization

1. Core model

Instead of linking answers directly to a mutable Question, link each answer to a versioned QuestionnaireItem.


Questionnaire (version)
  -> QuestionnaireItem[]
  -> Inspection
  -> Response[]

This keeps historical inspections valid even when future questionnaire versions change.

2. Recommended Prisma schema (normalized)


model Questionnaire {
  id        String   @id @default(uuid())
  name      String
  version   String
  createdAt DateTime @default(now())
 
  items QuestionnaireItem[]
}
 
model QuestionnaireItem {
  id              String   @id @default(uuid())
  questionnaireId String
 
  chapter         String
  section         String
  questionNumber  String
  text            String
  type            String
 
  questionnaire Questionnaire @relation(fields: [questionnaireId], references: [id])
  responses     Response[]
}
 
model Inspection {
  id              String   @id @default(uuid())
  vesselId        String
  questionnaireId String
  inspectorId     String
  date            DateTime @default(now())
 
  questionnaire Questionnaire @relation(fields: [questionnaireId], references: [id])
  responses     Response[]
}
 
model Response {
  id           String   @id @default(uuid())
  inspectionId String
  itemId       String
  answer       AnswerType
  remarks      String?
 
  inspection Inspection        @relation(fields: [inspectionId], references: [id])
  item       QuestionnaireItem @relation(fields: [itemId], references: [id])
  attachments Attachment[]
}
 
model Attachment {
  id         String   @id @default(uuid())
  responseId String
  url        String
  type       String
 
  response Response @relation(fields: [responseId], references: [id])
}
 
enum AnswerType {
  YES
  NO
  NA
  NOT_SEEN
}

3. CSV import mapping

Most source files map directly into QuestionnaireItem:

chapter
section
question (to questionNumber)
text
type


for (const row of csvRows) {
  await prisma.questionnaireItem.create({
    data: {
      questionnaireId,
      chapter: row.chapter,
      section: row.section,
      questionNumber: row.question,
      text: row.text,
      type: row.type,
    },
  })
}

4. Handling partial questionnaires

Recommended:

create Response rows only for answered items

Alternative:

pre-create empty rows for all 385 questions

The first option is usually better for storage and performance.

5. Snapshot pattern for large inspections

For large forms, many teams use an inspection snapshot so each inspection has its own frozen working set.


model InspectionItem {
  id           String @id @default(uuid())
  inspectionId String
 
  chapter      String
  section      String
  question     String
  text         String
  answer       AnswerType?
  remarks      String?
  status       String @default("pending")
 
  inspection Inspection @relation(fields: [inspectionId], references: [id])
}

Snapshot creation:


const questions = await prisma.questionnaireItem.findMany({
  where: { questionnaireId },
})
 
await prisma.inspectionItem.createMany({
  data: questions.map((q) => ({
    inspectionId,
    chapter: q.chapter,
    section: q.section,
    question: q.questionNumber,
    text: q.text,
  })),
})

This pattern improves:

load speed
offline reliability
report reproducibility

6. UI strategy for 300-400 questions

Use progressive navigation:


Chapter -> Section -> Questions

Do not render everything at once. Query only the active section:


const items = await prisma.inspectionItem.findMany({
  where: {
    inspectionId,
    chapter: currentChapter,
    section: currentSection,
  },
  orderBy: [{ question: "asc" }],
})

7. Instant save and progress

Save each answer immediately:


await prisma.inspectionItem.update({
  where: { id },
  data: {
    answer: "YES",
    remarks: "Pump maintenance log verified",
    status: "answered",
  },
})

Track progress from counts:


const total = await prisma.inspectionItem.count({ where: { inspectionId } })
 
const answered = await prisma.inspectionItem.count({
  where: {
    inspectionId,
    answer: { not: null },
  },
})

8. Local-first sync model

A stable offline flow:


UI -> local SQLite -> sync_queue -> API -> server DB

Minimal queue shape:


type SyncItem = {
  entityType: "response" | "inspection" | "attachment"
  entityId: string
  operation: "create" | "update" | "delete"
  payload: unknown
}

Sync loop example:


async function sync() {
  const pending = await db.getPendingQueueItems()
 
  for (const item of pending) {
    await fetch("/api/sync", {
      method: "POST",
      body: JSON.stringify(item),
    })
 
    await db.markQueueItemSynced(item.id)
  }
}

9. End-to-end workflow


Select vessel
-> Select questionnaire version
-> Create inspection
-> Generate snapshot items
-> Answer questions + add photos
-> Save locally
-> Sync when online
-> Generate reproducible report

10. Why this architecture works

scales to 1000+ questions
supports strict questionnaire versioning
handles partial completion cleanly
remains reliable offline
keeps reporting simple and auditable