Skip to Content
chalvien 1.0 is released
DocumentationTutorialsQuestionnaireQuestionnaire Part 1

Offline-first inspection architecture

Overview

This document describes a pragmatic architecture for applications that manage questionnaires and inspections (roughly 300–400 questions), support photos and local storage, and synchronize with a remote API. The approach is designed for Next.js with a local SQLite database.

Design goals

  • Clear separation of concerns: UI, domain logic, persistence, sync, and server API.
  • Reliable local-first behavior with durable local saves and a straightforward sync model.
  • Maintainable, testable modules with minimal coupling between layers.

Architecture layers

  1. UI — pages and components (Next.js App Router)
  2. Domain features — feature modules for inspections, questionnaires, vessels, etc.
  3. Local database — SQLite schema and access layer
  4. Sync engine — queue, background worker, and retry logic
  5. Server API — endpoints that accept and apply client changes
src/
├── app/                    # Next.js App Router
│   ├── (dashboard)/
│   │   ├── inspections/
│   │   │   ├── page.tsx
│   │   │   └── [inspectionId]/page.tsx
│   │   ├── vessels/page.tsx
│   │   └── questionnaires/page.tsx
│   ├── api/sync/route.ts    # sync endpoint(s)
│   ├── layout.tsx
│   └── page.tsx
├── features/                # domain modules (inspections, questionnaires, vessels)
├── db/                      # local database (schema, migrations)
├── sync/                    # sync engine and queue
├── store/                   # UI state
├── components/              # shared UI components
├── hooks/                   # custom hooks (useInspection, useSync)
├── lib/                     # utilities
└── types/

Local database

Keep the schema compact and versioned. Use a snapshot pattern for inspection items so each inspection stores a stable copy of questionnaire text when created.

export const inspections = sqliteTable("inspections", {
  id: text("id").primaryKey(),
  vesselId: text("vessel_id"),
  questionnaireId: text("questionnaire_id"),
  date: text("date"),
})
 
export const inspectionItems = sqliteTable("inspection_items", {
  id: text("id").primaryKey(),
  inspectionId: text("inspection_id"),
  chapter: text("chapter"),
  section: text("section"),
  question: text("question"),
  text: text("text"),
  answer: text("answer"),
  remarks: text("remarks"),
  syncStatus: text("sync_status")
})

Possible answer values: YES, NO, NA, NOT_SEEN.

Domain service examples

Keep domain logic inside feature modules. Services should operate on the local database and maintain sync metadata.

export async function getInspectionItems(inspectionId: string) {
  return db
    .select()
    .from(inspectionItems)
    .where(eq(inspectionItems.inspectionId, inspectionId))
}
export async function answerQuestion(id: string, answer: string) {
  await db
    .update(inspectionItems)
    .set({ answer, syncStatus: "pending" })
    .where(eq(inspectionItems.id, id))
}

Sync queue and engine

Local changes should append a compact entry to a sync_queue table. A background worker (triggered at app start, on network reconnect, or manually) attempts to send pending items to the server and marks items as synced or failed with retry metadata.

Example queue columns: id, entity_type, entity_id, operation, payload, status, created_at.

export async function runSync() {
  const items = await getPendingQueue()
  for (const item of items) {
    // POST to /api/sync and handle success/failure with retries
  }
}
Sync considerations (common pitfalls)
  • Idempotency: ensure server handlers are idempotent (use operation IDs).
  • Conflict handling: choose a merge strategy (last-write-wins, server-authoritative, or manual reconciliation).
  • Large payloads: upload large attachments (photos) separately and reference them in sync payloads.

Example server API

POST /api/sync should validate, apply, and respond with a clear status. Server-side storage can be PostgreSQL, SQLite, or MySQL depending on scale and operational needs.

UI and components

Design the inspection UI around the snapshot model. Typical composition:

InspectionPage
 ├─ ChapterSidebar
 ├─ ProgressBar
 └─ QuestionList (QuestionCard components)

Example QuestionCard (conceptual):

export function QuestionCard({ item }) {
  return (
    <div>
      <p>{item.question} {item.text}</p>
      <div className="actions">
        <button>YES</button>
        <button>NO</button>
        <button>N/A</button>
        <button>NOT SEEN</button>
      </div>
      <textarea placeholder="Remarks" />
    </div>
  )
}

Progress tracking

Compute progress locally from the inspection items: answered / total.

Attachments

Store photos locally and reference file paths in an attachments table with sync_status. Upload attachments during sync; prefer resumable or chunked uploads for large files.

Phase 1 — Data
  1. Define SQLite schema and migrations
  2. Implement questionnaire import from CSV (preserve versions)
Phase 2 — Core
  3. Create inspection entity and snapshot generation
  4. Implement local read/write services
Phase 3 — UI
  5. Chapter navigation and question list
  6. Question card interactions and local save
  7. Progress indicator and reporting views
Phase 4 — Offline & Sync
  8. Persist local saves reliably
  9. Implement sync queue and retry logic
 10. Implement background sync engine and attachment upload

Why this architecture works

  • Snapshot table keeps each inspection consistent for reporting and audits.
  • Local SQLite provides robust offline behavior.
  • Background sync with a queue handles intermittent connectivity.
  • Versioned questionnaires allow safe updates without corrupting past inspections.

Next steps

  • Implement idempotent server endpoints and a conflict resolution strategy.
  • Add tests for the sync flow (unit + integration with a test DB).
  • Optionally, add a lightweight script to generate a questionnaire DB from CSV.
Monorepo Part 2