Background

Test files hand-roll CREATE TABLE DDL per backend, duplicating src/db/schema/. That DDL drifts from the real schema: a single schema column-add breaks N tests, and the PG/MySQL copies (only run in CI) diverge silently — this is exactly how the #3495 PostgreSQL test-table gap slipped past local runs.

A shared helper was introduced to fix this by construction:

• src/server/test-helpers/testDb.ts — createTestDb() runs the real SQLite migration registry (baseline 001 + every ALTER) against a fresh :memory: database, returning { sqlite, db, close }. Every test gets the exact production schema; a schema change is a single edit with zero test-DDL to update.
• Landed in refactor(test): shared createTestDb helper from migration registry (section B2, part 1) #3498 (helper + 3 telemetry tests) and refactor(test): migrate messages repo tests to createTestDb (section B2, part 2) #3500 (5 messages tests).

This issue tracks migrating the remaining 32 files that still hand-roll DDL. It's deferred because it's an incremental, individually-verified effort (see "friction" below), not a safe bulk find-replace.

Migration recipe (per file)

1. Imports: drop drizzle from the drizzle-orm/better-sqlite3 import (keep the BetterSQLite3Database type); add import { createTestDb } from '../../server/test-helpers/testDb.js'; (adjust relative path).
2. Replace the beforeEach DB setup:

   // before: new Database(':memory:') + db.exec(CREATE TABLE ...) + drizzle(db, { schema })
   const t = createTestDb();
   db = t.sqlite;
   drizzleDb = t.db;
   // ... construct repo as before

3. Adjust reference-data seeding (the friction — see below).
4. Verify: npx vitest run <file> → green, then tsc --noEmit, then the full suite.

Per-file friction / gotchas

• Reference-data seeds must satisfy the full schema's NOT NULL / PK columns. The hand-rolled tables were simplified; the real schema is stricter. E.g. seeding nodes requires sourceId (composite PK (nodeNum, sourceId) from migration 029) and createdAt/updatedAt NOT NULL. The minimal INSERT INTO nodes (nodeNum, nodeId) the old tests used will fail — use:

  db.prepare("INSERT INTO nodes (nodeNum, nodeId, sourceId, createdAt, updatedAt) VALUES (?, ?, 'default', ?, ?)")

• FKs are not enforced (better-sqlite3 defaults PRAGMA foreign_keys=OFF, and migration 041 dropped the telemetry→nodes FK), so reference rows are only needed where a test reads joined/enriched data.
• Redundant DDL becomes dead code. Any in-test CREATE INDEX/table that the migrations now provide should be removed (e.g. the migration-032 dedupe index — see the refactor(test): shared createTestDb helper from migration registry (section B2, part 1) #3498 review). Leaving it is harmless (IF NOT EXISTS) but confusing.
• Multi-backend files (PG/MySQL DDL, run only in CI) are NOT covered by createTestDb() (SQLite-only). Either leave their per-backend DDL, or extend the helper with PG/MySQL variants in a separate effort.

Remaining files (32)

SQLite-only — drop-in candidates (25)

• db/repositories/analysis.test.ts (8 separate DB setups — heavier)
• db/repositories/auth.test.ts
• db/repositories/auth.migration.test.ts (verify it's not asserting historical migration state)
• db/repositories/autoFavoriteTargets.perSource.test.ts
• db/repositories/channelDatabase.test.ts
• db/repositories/channels.test.ts
• db/repositories/embedProfiles.test.ts
• db/repositories/ignoredNodes.test.ts
• db/repositories/meshcore.test.ts
• db/repositories/meshcorePacketLog.perSource.test.ts
• db/repositories/messages.bigint.test.ts (seeds nodes with longName/shortName)
• db/repositories/messages.migration.test.ts
• db/repositories/misc.packetlog.test.ts (3 DB setups)
• db/repositories/nodes.test.ts
• db/repositories/nodes.invalidNodeNum.test.ts
• db/repositories/nodes.pruneOutsideBbox.test.ts
• db/repositories/perSource.test.ts
• db/repositories/settings.test.ts
• db/repositories/sources.test.ts
• server/services/appriseNotificationService.test.ts
• server/services/pushNotificationService.test.ts
• server/services/solarMonitoringService.test.ts
• services/database.test.ts (facade — heavier; verify it doesn't already self-init a DB)
• services/database.audit.test.ts
• services/database.extended.test.ts, services/database.unread.test.ts

Multi-backend — needs PG/MySQL handling, NOT a plain createTestDb() swap (4)

• db/repositories/misc.test.ts
• db/repositories/neighbors.test.ts
• db/repositories/notifications.test.ts
• db/repositories/traceroutes.test.ts

Intentionally NOT migrated (2)

• db/repositories/telemetry.multidb.test.ts — multi-backend by design (exercises per-backend DDL on purpose).
• db/repositories/schemaIntegrity.test.ts — asserts the hand-rolled DDL aligns with the schema; migrating it would defeat its purpose.

Suggested approach

Batch by domain (nodes, auth, channels, meshcore, services, database facade), one small PR each, verifying the file's tests + full suite per batch. Tackle the simple single-DB repo tests first; leave the facade (database.*) and analysis.test.ts for last (multiple/heavier setups). For the multi-backend files, decide separately whether to extend createTestDb() with PG/MySQL variants.

Refs: #3498, #3500. Part of the section-B refactors from the systemic review.