Starter Sqlite

Next.js + Prisma + SQLite starter architecture Examples

Starter architecture example specifically optimized for offline-first questionnaire apps like the one you are building.

It solves three very tricky problems:

1. large questionnaires (300-400 questions)
1. offline capture
1. later synchronization.

For your inspection / questionnaire apps (300–400 questions, offline capture, later sync), a slightly specialized architecture works best with:

Next.js
Prisma
SQLite
PWA capabilities for offline work.

The key idea is to separate four layers clearly:

UI / Routes
Domain features (questionnaires, inspections)
Data persistence (SQLite / Prisma)
Offline sync layer

1. Suggested Architecture for Questionnaire Apps


src
├── app/                         # Next.js routing layer
│   ├── (dashboard)
│   │   ├── inspections
│   │   │   ├── page.tsx
│   │   │   └── [inspectionId]
│   │   │       └── page.tsx
│   │   │
│   │   ├── questionnaires
│   │   │   ├── page.tsx
│   │   │   └── [id]
│   │   │       └── page.tsx
│   │   │
│   │   └── vessels
│   │       └── page.tsx
│   │
│   ├── api/                     # optional sync endpoints
│   │   └── sync
│   │       └── route.ts
│   │
│   ├── layout.tsx
│   └── page.tsx
│
├── features/
│   ├── inspections
│   │   ├── actions
│   │   ├── components
│   │   ├── services
│   │   └── types.ts
│   │
│   ├── questionnaires
│   │   ├── components
│   │   ├── services
│   │   └── types.ts
│   │
│   ├── responses
│   │   ├── actions
│   │   ├── services
│   │   └── types.ts
│   │
│   └── vessels
│       ├── components
│       ├── services
│       └── types.ts
│
├── offline/                     # offline-first logic
│   ├── sync.ts
│   ├── queue.ts
│   └── storage.ts
│
├── db/                          # database layer
│   ├── prisma.ts
│   ├── schema.prisma
│   └── migrations
│
├── components/                  # design system
│   ├── ui
│   └── common
│
├── hooks
│   ├── useOffline.ts
│   └── useSync.ts
│
├── lib                          # utilities
│
├── types
│
└── config

2. Data Model (Very Important)

For questionnaires you typically need four main entities.

Example Prisma schema.


model Questionnaire {
  id          String   @id @default(uuid())
  title       String
  version     String
  questions   Question[]
}

model Question {
  id              String   @id @default(uuid())
  questionnaireId String
  text            String
  chapter         String
  section         String

  questionnaire   Questionnaire @relation(fields: [questionnaireId], references: [id])
}

model Inspection {
  id        String   @id @default(uuid())
  vesselId  String
  date      DateTime
  responses Response[]
}

model Response {
  id           String   @id @default(uuid())
  inspectionId String
  questionId   String
  answer       String
  remarks      String?

  inspection Inspection @relation(fields: [inspectionId], references: [id])
}

This structure works very well for: • 385 questions • multiple inspections • dynamic questionnaires.

3. Offline Strategy (Critical)

Your offline layer should handle:


user answers question
       ↓
save locally
       ↓
mark as "pending sync"
       ↓
sync when connection returns

Example structure.


offline/
   queue.ts
   sync.ts
   storage.ts

Example queue:


export type SyncItem = {
  type: "response"
  payload: any
}

#### 4. Sync Workflow

Typical process:
```text
Offline User
     ↓
Local SQLite
     ↓
Sync Queue
     ↓
API /sync
     ↓
Server Database

Example endpoint:


POST /api/sync

Handles:

responses
inspections
attachments.

5. UI Pattern for Large Questionnaires

For 300–400 questions, never load everything.

Instead use chapter navigation.


Chapter 1
   Question 1
   Question 2
   Question 3

Chapter 2
   Question 4
   Question 5

Components:


QuestionnairePage
   ChapterNavigation
   QuestionCard
   ResponseInput

6. Response Component Example


type Props = {
  question: Question
  value?: string
  onChange: (value: string) => void
}

export function QuestionCard({ question, value, onChange }: Props) {
  return (
    <div>
      <p>{question.text}</p>

      <select
        value={value}
        onChange={(e) => onChange(e.target.value)}
      >
        <option value="YES">YES</option>
        <option value="NO">NO</option>
        <option value="NA">N/A</option>
        <option value="NOT_SEEN">NOT SEEN</option>
      </select>
    </div>
  )
}

7. Very Important Performance Tip

For large questionnaires:

Never do:


385 React state variables

Instead use:


Zustand store
or
local database persistence

Libraries:

Zustand
TanStack Query

8. PWA Layer

For offline support use:

service worker
local database
background sync.

Libraries often used with Next.js:

next-pwa

9. Ideal Workflow for Inspection Tool

Load questionnaire definition
Start inspection
Save answers locally
Allow photos / remarks
Sync later
Generate report.

10. Final Concept

Your application becomes 3 mini systems:


UI System
   Next.js App Router

Domain System
   inspections
   vessels
   questionnaires

Data System
   Prisma
   SQLite
   Sync

This architecture scales well to:

400+ questions
offline inspections
report generation
multi-vessel inspections