Crash on startup after UI initializes: SqliteError: no such table: templates (.filter is not a function)

[erfurtjohn]

Describe the Bug

There is a discrepancy between running the application locally via npm start and via Docker when using the SQLite/Knex backend:

1. Via npm start (Local Windows): The server logs multiple [autopurge] errors (.filter is not a function) and then hard-crashes immediately with SqliteError: no such table: templates. The UI never becomes available.
2. Via Docker: The application successfully boots up and the UI is accessible. Demo users are present, but demo data is missing. The background autopurge sequence logs the same .filter is not a function errors, but it does not crash the container.

This indicates an issue with how the SQLite database file is initialized, located, or migrated depending on the environment.

Steps to Reproduce

1. Clone the repository: git clone https://github.com/coolstartnow/isms-builder
2. Install dependencies: npm install
3. Run locally: npm start -> Observe the hard crash (no such table: templates).
4. Run via Docker -> Observe that the UI works and demo users exist, but data is missing and autopurge logs errors.

Expected Behaviour

The application should initialize the SQLite database identically in both environments, apply all schema migrations/seeds automatically, and run the autopurge service without throwing .filter TypeErrors or crashing.

Actual Behaviour

Local execution crashes completely due to missing tables, while the Docker setup bypasses the crash but leaves the database in a partially uninitialized state (missing demo data / malfunctioning autopurge).

Environment

• OS: Windows 11 (PowerShell) / Docker (Linux Container)
• Node.js version: v24.14.0
• STORAGE_BACKEND (json/sqlite): sqlite (Knex / better-sqlite3)

Relevant Logs (from local npm start crash)

[storage] Backend: sqlite (Knex)
[autopurge] Templates: deletedTmpl.filter is not a function
[autopurge] Risks: items.filter is not a function
[autopurge] Goals: items.filter is not a function
[autopurge] Guidance: items.filter is not a function
[autopurge] Training: items.filter is not a function
[autopurge] Contracts: items.filter is not a function
[autopurge] NDAs: items.filter is not a function
[autopurge] Policies: items.filter is not a function
[autopurge] GDPR VVT: items.filter is not a function
[autopurge] GDPR AV: items.filter is not a function
[autopurge] GDPR DSFA: items.filter is not a function
[autopurge] GDPR Incidents: items.filter is not a function
[autopurge] GDPR DSAR: items.filter is not a function
[autopurge] GDPR TOMs: items.filter is not a function
[autopurge] Public Incidents: items.filter is not a function
[autopurge] Suppliers: items.filter is not a function
[autopurge] Findings: items.filter is not a function
[notifier] SMTP nicht konfiguriert — E-Mail-Benachrichtigungen deaktiviert
ISMS Builder listening on http://localhost:3000
[storage] Backend: sqlite — Knex template store ready

C:\Users\j.erfurt\Documents\Repositories\isms-builder\node_modules\better-sqlite3\lib\methods\wrappers.js:5
        return this[cppdb].prepare(sql, this, false);
                           ^
SqliteError: select * from `templates` where `deleted_at` is not null order by `deleted_at` desc - no such table: templates
    at Database.prepare (C:\Users\j.erfurt\Documents\Repositories\isms-builder\node_modules\better-sqlite3\lib\methods\wrappers.js:5:21)
    at Client_BetterSQLite3._query (C:\Users\j.erfurt\Documents\Repositories\isms-builder\node_modules\knex\lib\dialects\better-sqlite3\index.js:42:34)
    at executeQuery (C:\Users\j.erfurt\Documents\Repositories\isms-builder\node_modules\knex\lib\execution\internal\query-executioner.js:37:17)
    at Client_BetterSQLite3.query (C:\Users\j.erfurt\Documents\Repositories\isms-builder\node_modules\knex\lib\client.js:157:12)
    at Runner.query (C:\Users\j.erfurt\Documents\Repositories\isms-builder\node_modules\knex\lib\execution\runner.js:141:36)
    at ensureConnectionCallback (C:\Users\j.erfurt\Documents\Repositories\isms-builder\node_modules\knex\lib\execution\internal\ensure-connection-callback.js:13:17)
    at Runner.ensureConnection (C:\Users\j.erfurt\Documents\Repositories\isms-builder\node_modules\knex\lib\execution\runner.js:318:20)
    at process.processTicksAndRejections (node:internal/process/task_queues:104:5)
    at async Runner.run (C:\Users\j.erfurt\Documents\Repositories\isms-builder\node_modules\knex\lib\execution\runner.js:30:19)
    at async Object.getDeletedTemplates (C:\Users\j.erfurt\Documents\Repositories\isms-builder\server\db\stores\templateStore.js:220:18) {
  code: 'SQLITE_ERROR'
}

[borishinzer]

Had the same, only made it working with switching to json (in .env). Later then setup MariaDB and migrated with help of claude.

[coolstartnow]

I dug into this — the crash isn't specific to templates, it affects every Knex-backed store under STORAGE_BACKEND=sqlite (Risks, Goals, Guidance, Training, Legal, GDPR, Suppliers, Findings, …). Two root causes in runAutopurge() (server/index.js):

1. Async/sync mismatch
When STORAGE_BACKEND is sqlite/mariadb/pg, each store module (e.g. server/db/riskStore.js) swaps its export for a Knex-backed variant whose getDeleted() is async and returns a Promise. runAutopurge() calls these functions synchronously and immediately does .filter() on the result — .filter doesn't exist on a Promise, hence items.filter is not a function for every entity type in the log.

2. Race condition (the hard crash)
Each Knex store module kicks off its schema init as fire-and-forget (_knex.init().catch(...)), never awaited by the caller. runAutopurge() runs synchronously right at server startup, before that init is guaranteed to finish. If the autopurge query for templates hits the DB before CREATE TABLE has completed, you get SqliteError: no such table: templates. Since that call isn't awaited or .catch()-guarded, the rejection is unhandled and crashes the process — timing-dependent, which is why it's inconsistent between environments (Windows local vs. Docker).

Fix direction: make runAutopurge() async and await every getDeleted() call, and don't start the HTTP listener / autopurge until the storage backend's init() promise has resolved.

This falls under our SQL-backend rework (SQLite/MariaDB/PostgreSQL support is still work-in-progress), so it'll be fixed together with that effort rather than as a standalone patch. Tracked internally — thanks for the detailed repro, it made this easy to pin down.

[coolstartnow]

Update: since this crash is a known, unresolved risk with STORAGE_BACKEND=sqlite at startup, I've changed the project-wide default recommendation from sqlite to json until this is properly fixed:

• .env.docker, .env.example — default is now STORAGE_BACKEND=json
• README, scripts/setup-server.sh, docs/ISMS-build-documentation.md — updated to recommend json instead of sqlite for production, with an explicit note pointing back to this issue
• Added scripts/prepare-production.js — an interactive tool to clear demo/test data module-by-module (or all at once) when going live, as an alternative to the in-app "Demo Reset" admin action (which is demo-instance-specific and still switches to sqlite — intentionally left unchanged for now, see commit message on cba0f5c/0cda524/f613579)

sqlite remains available and is not going away — it's just no longer the recommended default until this race condition is actually fixed. Keeping this issue open as the tracker for the real fix (making runAutopurge() wait for storage init).