# Check List — Proof of Concept Hybrid offline-first quality-inspection reporting application. The backend is Node.js + Express + MariaDB; the frontend is a Bootstrap 5 single-page application split into two role-specific shells. Operators fill in inspection records and attach images locally in IndexedDB; completed reports (with image binaries) are uploaded to the server. Administrators manage all configuration entities and review submitted reports through a separate console. --- ## Contents - [What is included](#what-is-included) - [Scope of this PoC](#scope-of-this-poc) - [Project structure](#project-structure) - [Data flow — detailed](#data-flow--detailed) - [Application boot](#1-application-boot) - [Authentication flow](#2-authentication-flow) - [Admin console — configuration management](#3-admin-console--configuration-management) - [User workspace — task processing](#4-user-workspace--task-processing) - [Report submission flow](#5-report-submission-flow) - [Admin report review](#6-admin-report-review) - [Cache and offline strategy](#7-cache-and-offline-strategy) - [Session lifecycle and browser restart behaviour](#8-session-lifecycle-and-browser-restart-behaviour) - [Database schema overview](#database-schema-overview) - [API endpoints](#api-endpoints) - [Run with Docker and Dev Containers](#run-with-docker-and-dev-containers) - [Database bootstrap](#database-bootstrap) - [Validate the environment](#validate-the-environment) - [Notes on security posture](#notes-on-security-posture) --- ## What is included - Node.js REST API (`/api/v1/…`) for admin entities, reports, image policy, and authentication - Two Bootstrap 5 frontend shells served directly by Express: - `user.html` — operator workspace: open an assigned task, fill in inspection records per category, attach and optimize images, save as draft or submit as final - `admin.html` — administrator console: manage users, sites, check-list records and templates, task assignments, image policy, and review submitted reports with image viewer and map - Cookie-based session authentication for both admin and user roles; sessions survive page reload but are cleared on server restart (in-memory store) - Cache-Control headers set to `no-cache` on all HTML responses so browsers always revalidate page content after a Docker restart - MariaDB schema covering admin entities, image rules, reports, report image blobs, and an audit log - Seed data with one sample inspection checklist template, lookup values, image policy, and export profile - Docker Compose and VS Code Dev Container setup for local development - Smoke test that verifies API, database, and phpMyAdmin connectivity ## Scope of this PoC **Included:** - Versioned REST API under `/api/v1/` - Cookie-based session authentication (admin and user), login pages at `/login-admin` and `/login-user` - Full CRUD endpoints for admin-managed entities (categories, sub-categories, severities, statuses, handled-by options, projects, processes, users, sites, check-list records, check-list templates, tasks) - Bulk data endpoint `GET /api/v1/admin/all` — one request loads the entire entity graph used by both admin and user consoles - Image rules endpoint with server-side LRU cache; clients read and enforce the active rule locally - Report submission and retrieval; image upload/download endpoints (images stored as `LONGBLOB` rows in MariaDB) - Audit logging for report submissions and deletions, image-rule updates - Offline-capable local drafts via IndexedDB with automatic server hydration on re-open - Web Worker image optimisation (OffscreenCanvas with main-thread fallback) - EXIF extraction preserved through the optimisation pipeline - PWA manifest with SVG icon **Not included:** - Password hashing (credentials are compared in plain text — PoC only) - Rate limiting, CSRF tokens, or JWT-based authentication - XLSX/ZIP export generation - Automated test suite (only a connectivity smoke test is provided) - Production-grade bundling or minification --- ## Project structure ```text . ├── docker-compose.yml ├── package.json ├── PROJECT_FILES_GUIDE.md ← per-file guided walk-through ├── README.md ← this file ├── public/ │ ├── admin.html ← admin workspace shell │ ├── admin-app.js ← admin entry point │ ├── user.html ← operator workspace shell │ ├── user-app.js ← user entry point │ ├── portal.html ← neutral landing page at / │ ├── login-admin.html ← admin login form │ ├── login-user.html ← user login form │ ├── styles.css ← shared styles (Bootstrap 5 overrides) │ ├── manifest.webmanifest │ └── js/ │ ├── admin.js ← admin console controller and renderers │ ├── user.js ← user task workflow controller │ ├── api.js ← fetchJson helper (versioned base path) │ ├── constants.js ← shared constants (store names, API base) │ ├── state.js ← shared state container (IndexedDB handle) │ ├── db.js ← shared IndexedDB open + CRUD helpers │ ├── user-db.js ← user-side IndexedDB task-data cache │ ├── validation.js ← image-rule and report validators │ ├── images.js ← image optimisation (Worker + fallback) │ ├── image-worker.js ← OffscreenCanvas worker implementation │ └── exif.js ← lightweight JPEG EXIF parser ├── scripts/ │ └── test-environment.js ← connectivity smoke test ├── sql/ │ ├── schema.sql ← full MariaDB DDL │ └── seed.sql ← sample data + admin/user credentials └── src/ ├── app.js ← Express wiring (routes, middleware, static) ├── server.js ← entry point (boot, DB ping, shutdown) ├── config/ │ └── env.js ← dotenv loader + required-var validation ├── db/ │ └── pool.js ← shared MariaDB connection pool ├── middleware/ │ ├── authMiddleware.js ← requireAdminAuth / requireUserAuth / requireAnyAuth │ ├── errorHandler.js ← 404 and global error JSON responders │ └── validateParams.js ← URL-parameter regex safety guards ├── routes/ │ ├── adminRoutes.js ← /api/v1/admin/* │ ├── authRoutes.js ← /api/v1/auth/* │ ├── configRoutes.js ← /api/v1/config/* │ ├── healthRoutes.js ← /api/v1/health │ ├── lookupRoutes.js ← /api/v1/lookups/* │ ├── reportRoutes.js ← /api/v1/reports/* │ └── templateRoutes.js ← /api/v1/templates/* ├── services/ │ ├── adminService.js ← admin entity CRUD + bulk load │ ├── auditService.js ← audit_log writes │ ├── authService.js ← in-memory session store │ ├── cacheService.js ← generic LRU/TTL cache factory │ ├── configService.js ← image rules + export profile queries │ ├── lookupService.js ← lookup_sets / lookup_values queries │ ├── reportService.js ← reports + report_images CRUD │ └── templateService.js ← template catalogue queries └── utils/ ├── asyncHandler.js ← wraps async route handlers for error propagation └── json.js ← safe JSON column parser ``` For a per-file guided walk-through see [PROJECT_FILES_GUIDE.md](PROJECT_FILES_GUIDE.md). --- ## Data flow — detailed This section traces how data moves through every layer of the application for each major scenario. ### 1. Application boot ``` docker compose up └─ app container ├─ npm install └─ node src/server.js ├─ src/config/env.js — loads .env, validates required keys, │ exports typed `env` object ├─ src/db/pool.js — creates shared MariaDB connection pool │ (connectionLimit from DB_CONNECTION_LIMIT, │ default 5) ├─ SELECT 1 AS ok — fails fast if DB is not reachable └─ app.listen(port) — Express starts accepting connections └─ db container (bitnami/mariadb) └─ on a FRESH volume: runs sql/schema.sql then sql/seed.sql (mounted at /docker-entrypoint-initdb.d) └─ phpmyadmin container — web UI at :8080 ``` **Express middleware stack** (`src/app.js`, top to bottom): 1. `cors()` — wide-open CORS for PoC use 2. `cookieParser()` — parses `auth_token` cookie used by auth middleware 3. `express.json({ limit: '50mb' })` — accepts large base64 image payloads 4. `noCacheHtml` middleware applied to every HTML route — sets `Cache-Control: no-cache` so browsers always revalidate page content with the server; this prevents stale HTML being served from cache after a Docker restart 5. API routes under `/api/v1/` 6. HTML page routes (`/`, `/login-admin`, `/login-user`, `/admin`, `/user`) 7. `express.static(publicDir)` with `setHeaders` applying `no-cache` for `.html` files; all other static assets (JS, CSS) use default ETag revalidation 8. `notFoundHandler` → 404 JSON 9. Global `errorHandler` → structured JSON error with stack in development --- ### 2. Authentication flow ``` Browser Express MariaDB │ │ │ ├─ GET / ─────────────────────► │ │ │◄─ portal.html ─────────────── │ │ │ │ │ ├─ GET /login-user ────────────► │ │ │◄─ login-user.html ──────────── │ │ │ │ │ ├─ POST /api/v1/auth/user/login │ │ │ { email, password } ────────► │ │ │ ├─ verifyUserCredentials() ────►│ │ │ SELECT FROM admin_users │ │ │ WHERE email=? AND │ │ │ password_hash=? ◄────────────│ │ │ │ │ ├─ generateSessionToken() │ │ │ (random string + timestamp) │ │ │ │ │ ├─ createSession(token, data) │ │ │ stored in in-memory Map │ │ │ { type, id, email, role, … } │ │ │ TTL: 24 hours │ │ │ │ │◄─ 200 { success, token, user } │ │ │ Set-Cookie: auth_token=… │ │ │ sessionStorage.setItem( │ │ │ 'auth_token', token) │ │ │ │ │ ├─ GET /user?userId= ──────► │ │ │ Cookie: auth_token=… │ │ │ ├─ requireUserAuth middleware │ │ │ validateSession(token) │ │ │ checks in-memory Map │ │◄─ user.html ────────────────── │ │ ``` **Key points:** - Sessions live in an **in-memory `Map`** in `authService.js`. A Docker restart clears all sessions. - The `auth_token` cookie has a 24-hour `Max-Age`. If the browser sends a stale cookie after a server restart, auth middleware returns `401`. The user app detects `401` from `loadFromServer()` and redirects to `/login-user` automatically. - The `userId` query parameter appended to the redirect URL (`/user?userId=N`) is used by `filterTasksByUser()` on the client side to show only tasks assigned to that user. - Admin login follows the same pattern against the `admin_credentials` table with `requireAdminAuth` middleware guarding `/admin`. --- ### 3. Admin console — configuration management ``` Browser (admin.html) Express / MariaDB │ │ ├─ DOMContentLoaded ───────────► admin-app.js │ openDatabase() opens IndexedDB 'check-list-poc-db' │ dbGet(STORE_CONFIG, reads cached image rules (may be null │ 'imageRules') on first visit) │ initAdmin() ─────────────► admin.js │ │ │ loadFromServer() ─────►├─ GET /api/v1/admin/all │ │ adminService.loadAllAdminData() │ │ parallel queries to: │ │ admin_categories │ │ admin_sub_categories │ │ admin_severities │ │ admin_statuses │ │ admin_handled_by │ │ admin_projects │ │ admin_processes │ │ admin_users │ │ admin_sites │ │ admin_cl_records │ │ admin_cl_templates │ │ (+ admin_cl_template_records join) │ │ admin_tasks │ ◄─ full entity graph ──│ │ caches entire response │ │ in IndexedDB STORE_CONFIG │ key 'admin_all' │ │ │ │ initNavigation() wires sidebar expand/collapse, │ panel switching, opens Reports tab │ renderTaskList() default panel │ renderUserList() (task count badge per user) │ renderSiteList() │ │ renderClRecordList() │ │ renderClTemplateList() │ │ renderImagePolicy() │ │ renderTemplateSettings() │ │ renderTaskSettings() │ ``` **CRUD operations** (same pattern for every entity): ``` User clicks Save Express MariaDB │ │ │ ├─ POST /api/v1/admin/users ───► │ │ │ { email, name, … } ├─ adminService.createUser() ──►│ │ │ INSERT INTO admin_users │ │ │◄─ { id, … } ─────────────────│ │◄─ 201 { id, … } ──────────── │ │ │ admin.users.push(created) │ │ │ cacheState() ────────────── updates IndexedDB 'admin_all' │ │ renderUserList() re-renders the table │ ``` PUT and DELETE follow the same pattern. All mutations also call `cacheState()` to keep the IndexedDB snapshot current so the app works offline immediately after any change. **Task assignment** creates a row in `admin_tasks` linking a user, site, and checklist template. That row is what the user console reads to know which tasks exist for a given `userId`. --- ### 4. User workspace — task processing ``` Browser (user.html) Express / MariaDB / IndexedDB │ │ ├─ DOMContentLoaded ───────────► user-app.js │ openDatabase() opens 'check-list-poc-db' (shared IndexedDB) │ initUser() ───────────────► user.js │ │ │ loadFromServer() │ │ ├─ GET /api/v1/admin/all ►│ same bulk endpoint as admin console │ │ │ returns tasks, templates, records, │ │ │ sites, users, settings │ │◄─ full entity graph ───│ │ │ saves to IndexedDB │ │ │ STORE_CONFIG 'admin_all' │ │ │ │ └─ on 401: redirect ────►│ session gone (server restart) → │ to /login-user user re-authenticates │ on other error: falls back to IndexedDB cache │ │ │ loadTaskData() ──────────── opens 'user-portal-db' (second IndexedDB) │ reads all task-specific data: │ visitDate, per-record answers, image blobs │ keyed by taskId (numeric) │ │ │ filterTasksByUser() keeps only tasks where task.userId === │ Number(URL ?userId=N) │ │ │ renderTaskListView() table of assigned tasks │ renderSidebarTasks() task count badge in sidebar ``` **Opening a task:** ``` User clicks Open user.js IndexedDB / Server │ │ │ ├─ showDetailView(taskId) ─────► │ │ │ │ id = Number(taskId) │ │ │ (normalizes string from DOM) │ │ │ │ │ maybeHydrateFromServer(id) ►│ │ │ │ checks 'user-portal-db' for │ │ │ existing record data │ │ │ if EMPTY (fresh browser/ │ │ │ cleared storage): │ │ ├─ GET /api/v1/reports/:id ────►│ │ │ fetches last submitted │ │ │ answers (visitDate, records) │ │ │ seeds local state from │ │ │ server response │ │ │ images marked │ │ │ uploadedToServer:true │ │ │ persists to IndexedDB │ │ │ │ │ maybeDownloadImages(id) ───►│ │ │ │ if any image has │ │ │ uploadedToServer:true AND │ │ │ no dataUrl: │ │ ├─ GET /api/v1/reports/:id/images►│ │ │ fetches LONGBLOB rows │ │ │ converts to base64 dataUrls │ │ │ restores into local state │ │ │ saves to IndexedDB │ │ │ │ │ renderTaskDetail() ────────►│ renders form with visit date, │ │ │ category tabs, per-record │ │ │ fields pre-populated from │ │ │ hydrated state │ ``` **Record editing and auto-save:** ``` User changes a field user.js IndexedDB │ │ │ ├─ onRecordFieldChange() ──────► │ │ │ │ updates userState.taskData[id]│ │ │ .records[recId].status │ │ │ .records[recId].handledBy │ │ │ .records[recId].comment │ │ │ safeSave() │ │ ├─ saveOneTaskData(id, data) ──►│ │ │ stores { taskId, visitDate, │ │ │ records } in 'user-portal-db'│ │ │ keyPath: taskId │ ``` **Image attachment flow:** ``` User drops / selects image user.js Worker / IndexedDB │ │ │ ├─ onImageAdd() / drop ────────► │ │ │ │ readFileAsDataUrl(file) │ │ │ parseExifFromDataUrl() │ │ optimizeImagesIfNeeded() ──│ │ │ │ if oversizeBehavior = │ │ │ 'auto_optimize': │ │ │ images.js → image-worker.js│ │ │ OffscreenCanvas resize │ │ │ (main-thread canvas fallback│ │ │ when Worker unavailable) │ │ │ EXIF block preserved │ │ │ result stored in: │ │ │ taskData[id].records[recId] │ │ │ .images[].dataUrl │ │ │ safeSave() → IndexedDB │ ``` --- ### 5. Report submission flow **Save as Draft:** ``` User clicks Save as Draft user.js Express / MariaDB │ │ │ ├─ saveDraft() ────────────────► │ │ │ │ persistCurrentFormData() │ │ │ optimizeImagesIfNeeded() │ │ │ updateTaskStatus('draft') │ │ ├─ PUT /api/v1/admin/tasks/:id │ │ │ { …, status:'draft' } ──────►│ │ │ UPDATE admin_tasks SET │ │ │ status='draft' │ │ │ uploadReport('draft') ────────►│ │ ├─ POST /api/v1/reports │ │ │ { id, answers: { records, │ │ │ visitDate, … } } │ │ │ reportService.submitReport() │ │ │ stripImagesFromAnswers() │ │ │ → answers_json (no blobs) │ │ │ storeReportImages() │ │ │ → LONGBLOB rows per image │ │ │ ON DUPLICATE KEY UPDATE │ │ │ (idempotent re-submit) │ │ │◄─ 201 / 200 ─────────────────│ │ │ Images NOT stripped from │ │ │ IndexedDB (kept for editing) │ ``` **Save as Final:** Same as draft with additional steps before upload: 1. `validateVisitDate()` — checks date is within template `validFrom`/`validTill` 2. `validateForFinal()` — every record must have a Status; records with status requiring Handled By, Comment, or image attachment are checked 3. `processImagesForFinal()` — renames images to a standardised convention and applies resize/quality optimisation per image policy 4. After successful upload: `stripImageDataFromStorage()` removes `dataUrl` bytes from IndexedDB (only `name`, `size`, `uploadedToServer:true` metadata kept) to free local storage 5. If upload fails: images are kept in IndexedDB; toast warns the user --- ### 6. Admin report review ``` Admin clicks View (task row) admin.js Express / MariaDB │ │ │ ├─ viewTaskReport(taskId) ─────► │ │ │ ├─ GET /api/v1/reports/:id ────►│ │ │ reads reports.answers_json │ │ │◄─ { answers } ───────────────│ │ │ │ │ ├─ GET /api/v1/reports/:id/ │ │ │ images ───────────────────►│ │ │ reads LONGBLOB rows, │ │ │ converts to base64 dataUrls │ │ │ grouped by record_id │ │ │◄─ { [recordId]: [{dataUrl}] } │ │ │ │ │ buildAndShowReportModal() renders Bootstrap modal with │ │ all fields, images in lightbox, │ │ EXIF data, and map pins for │ │ images that have GPS coordinates│ ``` **Reopen task:** ``` Admin clicks Reopen admin.js / Express MariaDB │ │ │ ├─ reopenTask(id) ─────────────► │ │ │ ├─ PUT /api/v1/admin/tasks/:id │ │ │ { …, status:'pending' } ────►│ │ │ UPDATE admin_tasks │ │ │ SET status='pending' │ │ task.status = 'pending' enables editing on user side │ ``` --- ### 7. Cache and offline strategy The application uses **two IndexedDB databases** running concurrently in the browser: | Database | Name | Usage | |---|---|---| | Shared admin cache | `check-list-poc-db` | Entity graph from `/api/v1/admin/all` stored under key `admin_all` in `STORE_CONFIG`. Also caches image rules. Used by both admin and user consoles. | | User task data | `user-portal-db` | Per-task record answers, visit date, and image `dataUrl` blobs. Stored by task ID. Used only by the user console. | **Load priority on startup:** ``` navigator.onLine? YES → loadFromServer() fetch /api/v1/admin/all → success: save to IndexedDB, populate state → 401: redirect to login (session expired) → other: loadFromCache() (fall back to last snapshot) NO → loadFromCache() read 'admin_all' from IndexedDB ``` **HTML caching:** All HTML responses include `Cache-Control: no-cache`. The browser stores the HTML but validates it with the server on every load using ETag conditional GETs. A `304 Not Modified` (no body) confirms the page is current; a `200` delivers the new version. JS and CSS files use Express's default ETag validation without the `no-cache` directive. **Sync button:** The user task list view includes a manual **Sync** button (`#syncBtn`). It calls `forceSyncWithServer()`, which re-fetches `/api/v1/admin/all`, re-applies the user filter, and re-renders both the task list and sidebar without a full page reload. --- ### 8. Session lifecycle and browser restart behaviour Because sessions are kept in an **in-memory Map** on the server, a Docker container restart clears all sessions while the browser's `auth_token` cookie remains (24-hour `Max-Age`). The sequence that follows: ``` 1. Container restarts → sessions Map cleared 2. Browser sends request with old cookie 3. Server: validateSession(token) → null → 401 4. user.js loadFromServer() detects 401 → window.location = '/login-user' 5. User logs in → new token → new session created in Map 6. /api/v1/admin/all succeeds → tasks visible ``` If IndexedDB has a previous snapshot, the user will see their old tasks immediately while the login redirect is pending — the fallback `loadFromCache()` path handles that. Once re-logged in, the fresh server data overwrites the cache. --- ## Database schema overview ``` Templates (legacy catalogue, used by smoke test and lookups) templates ──< template_versions lookup_sets ──< lookup_values Configuration image_rules (1 active row; LRU-cached by configService) export_profiles (1 active row) Admin entities (managed through admin console) admin_categories ──< admin_sub_categories admin_severities admin_statuses (requireHandledBy, requireComment flags) admin_handled_by admin_projects ──< admin_processes admin_sites admin_users (also used for login via password_hash column) admin_cl_records (sort_order, category, sub_category, descriptions EN/FR/NL, severity, imageRequired) admin_cl_templates ──< admin_cl_template_records (join) admin_tasks (links user + site + template; status: pending/draft/final) Reports reports (answers stored as JSON; report_uuid is the client-side task ID) report_images (LONGBLOB per image, indexed by report_uuid + record_id) Audit audit_log (entity_type, entity_code, action, old/new JSON) Credentials admin_credentials (single admin account; plain-text password — PoC only) ``` --- ## API endpoints All endpoints live under `/api/v1/`. Routes that read or modify sensitive data require an authenticated session cookie (`auth_token`). ### Public (no authentication) | Method | Path | Description | |---|---|---| | GET | `/api/v1/health` | Liveness + DB ping | | GET | `/api/v1/templates` | List templates | | GET | `/api/v1/templates/:code` | Get one template | | GET | `/api/v1/templates/:code/versions` | List versions | | PUT | `/api/v1/templates/:code/versions/:v/publish` | Publish version | | GET | `/api/v1/lookups` | List lookup sets | | GET | `/api/v1/lookups/:code` | Get lookup values | | GET | `/api/v1/config/image-rules` | Active image rule | | PUT | `/api/v1/config/image-rules` | Update image rule (audit-logged) | | GET | `/api/v1/config/export` | Active export profile | | POST | `/api/v1/auth/admin/login` | Admin login → sets cookie | | POST | `/api/v1/auth/user/login` | User login → sets cookie | | POST | `/api/v1/auth/logout` | Clears cookie and session | | GET | `/api/v1/auth/check` | Validate current session | ### Requires authentication (`requireAnyAuth`) | Method | Path | Description | |---|---|---| | GET | `/api/v1/admin/all` | Bulk load every admin entity | | CRUD | `/api/v1/admin/categories` | Categories | | CRUD | `/api/v1/admin/sub-categories` | Sub-categories (parent: category) | | CRUD | `/api/v1/admin/severities` | Severity levels | | CRUD | `/api/v1/admin/statuses` | Statuses (with requiredHandledBy/Comment flags) | | CRUD | `/api/v1/admin/handled-by` | Handled-by options | | CRUD | `/api/v1/admin/projects` | Projects | | CRUD | `/api/v1/admin/processes` | Processes (parent: project) | | CRUD | `/api/v1/admin/users` | Users (operators) | | CRUD | `/api/v1/admin/sites` | Sites | | CRUD | `/api/v1/admin/cl-records` | Check-list records | | CRUD | `/api/v1/admin/cl-templates` | Check-list templates (with record ID list) | | CRUD | `/api/v1/admin/tasks` | Task assignments | | GET/POST | `/api/v1/reports` | List / submit reports | | GET/DELETE | `/api/v1/reports/:id` | Get / delete one report | | GET | `/api/v1/reports/:id/images` | All images for a report (base64 dataUrls) | | DELETE | `/api/v1/reports/:id/images/:recId/:fileName` | Delete one image | --- ## Run with Docker and Dev Containers 1. Copy `.env.example` to `.env` (or leave defaults — the Compose file supplies fallback values for all variables). 2. **VS Code Dev Containers:** run `Dev Containers: Reopen in Container`. **Or** start directly: `docker compose up -d --build`. Services after startup: | Service | URL | |---|---| | API + web app | `http://localhost:3000` | | phpMyAdmin | `http://localhost:8080` | | MariaDB | `localhost:3306` | The workspace is bind-mounted into the `app` container. `node --watch` picks up source edits without a restart. HTML changes are delivered immediately because `Cache-Control: no-cache` forces browser revalidation on every load. --- ## Database bootstrap On a fresh MariaDB volume, Docker runs `sql/schema.sql` then `sql/seed.sql` automatically (files are mounted at `/docker-entrypoint-initdb.d`). If you already have an older local volume, either recreate it: ```bash docker compose down -v docker compose up -d --build ``` or re-import manually: ```bash docker compose exec -T db sh -lc \ 'mariadb -uroot -p"$MARIADB_ROOT_PASSWORD" < /docker-entrypoint-initdb.d/schema.sql && \ mariadb -uroot -p"$MARIADB_ROOT_PASSWORD" < /docker-entrypoint-initdb.d/seed.sql' ``` Seeded admin and user credentials are in `sql/seed.sql` — search for `admin_credentials` and `admin_users`. **Change them before any non-local use.** --- ## Validate the environment Once containers are running: ```bash npm run test:environment ``` The smoke test checks: - API health endpoint (`/api/v1/health`) - Seeded templates queryable via `/api/v1/templates` - Direct MariaDB connectivity - phpMyAdmin availability --- ## Notes on security posture This is a proof of concept. The following are known gaps that must be addressed before any shared or production deployment: | Gap | Risk | Mitigation | |---|---|---| | Plain-text passwords | Credential theft via DB dump | Replace with bcrypt or argon2 | | In-memory session store | All sessions lost on restart | Move to Redis or DB-backed sessions | | No CSRF protection | Cross-site request forgery | Add CSRF tokens or SameSite=Strict cookies | | No rate limiting | Brute-force login attacks | Add express-rate-limit | | Wide-open CORS | Any origin can call the API | Restrict `cors()` to known origins | | No account lockout | Unlimited login attempts | Add failed-attempt counter | | No HTTPS enforcement | Token interception on plain HTTP | Terminate TLS at reverse proxy |