[BUG] Error when Saving Notifications

[Yeraze]

Console Logs show a 500 error:

cors-detection.js:35 
 POST https://meshmonitor.home./???.com/api/push/preferences 500 (Internal Server Error)
(anonymous)    @    cors-detection.js:35
request    @    main-BdhhTVew.js:2
await in request
post    @    main-BdhhTVew.js:2
oe    @    main-BdhhTVew.js:141
bd    @    mapIcons-Bj5C1gN-.js:8
(anonymous)    @    mapIcons-Bj5C1gN-.js:8
an    @    mapIcons-Bj5C1gN-.js:8
Td    @    mapIcons-Bj5C1gN-.js:8
dp    @    mapIcons-Bj5C1gN-.js:9
lp    @    mapIcons-Bj5C1gN-.js:9

Container Logs show an SQLite error:

meshmonitor-1
STDOUT
[ERROR] Failed to save preferences for user 2: SqliteError: ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint
meshmonitor-1
STDOUT
    at Database.prepare (/app/node_modules/better-sqlite3/lib/methods/wrappers.js:5:21)
meshmonitor-1
STDOUT
    at BetterSQLiteSession.prepareQuery (file:///app/node_modules/drizzle-orm/better-sqlite3/session.js:23:30)
meshmonitor-1
STDOUT
    at BetterSQLiteSession.prepareOneTimeQuery (file:///app/node_modules/drizzle-orm/sqlite-core/session.js:141:17)
meshmonitor-1
STDOUT
    at QueryPromise._prepare (file:///app/node_modules/drizzle-orm/sqlite-core/query-builders/insert.js:146:81)
meshmonitor-1
STDOUT
    at QueryPromise.run (file:///app/node_modules/drizzle-orm/sqlite-core/query-builders/insert.js:162:17)
meshmonitor-1
STDOUT
    at QueryPromise.execute (file:///app/node_modules/drizzle-orm/sqlite-core/query-builders/insert.js:174:54)
meshmonitor-1
STDOUT
    at QueryPromise.then (file:///app/node_modules/drizzle-orm/query-promise.js:21:17)
meshmonitor-1
STDOUT
    at process.processTicksAndRejections (node:internal/process/task_queues:104:5) {
meshmonitor-1
STDOUT
  code: 'SQLITE_ERROR'
meshmonitor-1
STDOUT
}

[Yeraze]

Claude finished @Yeraze's task —— View job

---

🤖 Automated Triage

Todo List:

• Check for duplicate issues
• Analyze push preferences codebase
• Identify the database constraint issue
• Provide diagnostic steps

Thank you for reporting this issue! I've analyzed the code and this appears to be a database constraint conflict when saving notification preferences.

Root Cause Analysis

The error ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint in SQLite indicates there's a mismatch in the database schema for the user_notification_preferences table. Looking at the code:

1. SQLite schema (src/db/schema/notifications.ts:39-59): Uses user_id as a foreign key but doesn't define it as unique
2. Repository code (src/db/repositories/notifications.ts:220-232): Tries to use onConflictDoUpdate with userId as the conflict target
3. The problem: SQLite requires an explicit UNIQUE constraint or PRIMARY KEY to use ON CONFLICT, but userId column lacks this constraint

Diagnostic Steps

To help confirm and resolve this issue, please run the following:

1. Check your database type and schema

# Check if you're using SQLite
docker exec meshmonitor-1 ls -la /data/meshmonitor.db

# Check the actual table schema
docker exec meshmonitor-1 sqlite3 /data/meshmonitor.db ".schema user_notification_preferences"

Expected output: Should show the table structure and reveal whether user_id has a UNIQUE constraint.

2. Check for existing notification preferences

docker exec meshmonitor-1 sqlite3 /data/meshmonitor.db "SELECT user_id, COUNT(*) FROM user_notification_preferences GROUP BY user_id HAVING COUNT(*) > 1;"

Expected output: Should be empty (no duplicate user IDs) or show duplicate entries if they exist.

3. Check recent database migrations

docker exec meshmonitor-1 sqlite3 /data/meshmonitor.db "SELECT * FROM migrations ORDER BY id DESC LIMIT 5;"

Expected output: Should show recent migration history - look for any notification-related migrations.

4. Try the failing operation with debug info

Check your container logs immediately after the error occurs:

docker logs meshmonitor-1 --tail 50

What to look for: Any additional SQL error details or migration warnings.

Suggested Labels

• bug
• database
• notifications
• sqlite

Documentation References

• Database Architecture: The multi-database system is documented in CLAUDE.md:71-107
• Migration System: Database migrations are handled via the centralized registry in src/db/migrations.ts

Please share the outputs from these diagnostic steps, and I'll provide specific guidance on resolving the constraint issue.

---