Feat/mysql support

[lifeihu1982]

Summary

Add MySQL/MariaDB as an alternative database backend alongside SQLite.

Changes

Backend

• DatabaseFactory — abstracts SQLite/MySQL via CLAUDE_MEM_DB_TYPE setting
• MySQL SessionStore — full implementation with prepared statements
• PaginationHelper — resolveResults() handles sync (SQLite) and async (MySQL) .all() calls
• ChromaSync — backfill supports MySQL async queries
• WORKER_MODE — multi-worker MySQL deployments (server/client modes)
• ViewerRoutes & DataRoutes — added await for async getProjectCatalog()

Frontend

• Date Range Selector — visible date picker with default 3-day range
• Quick Presets — buttons for 3天, 7天, 30天, 全部
• CSS Styles — time-range-quick-select and time-range-btn

Security & Migration Fixes

• P0 Bootstrap — runMigrations() now checks if schema_versions table exists before querying
• Migration 8 — use loop instead of multi-statement exec() for ALTER TABLE
• Password Hashing — use bcrypt for password storage (with SHA-256 fallback)
• SQL Injection — validate index names before using in DROP INDEX
• DatabaseManager.close() — guard against missing sessionSearch.close() method
• Content Hash — use SHA-256 instead of 32-bit djb2 to prevent birthday-paradox collisions

Configuration

{
  "CLAUDE_MEM_DB_TYPE": "mysql",
  "CLAUDE_MEM_MYSQL_HOST": "localhost",
  "CLAUDE_MEM_MYSQL_PORT": "3306",
  "CLAUDE_MEM_MYSQL_USER": "claude_mem",
  "CLAUDE_MEM_MYSQL_PASSWORD": "***",
  "CLAUDE_MEM_MYSQL_DATABASE": "claude_mem"
}

Default is sqlite — fully backward compatible.

Testing

• 753 sessions, 16000+ observations verified
• All API endpoints working
• TypeScript: 0 errors

Stats

56 files, +11,670 / -1,011 lines

[greptile-apps]

Greptile Summary

This PR adds MySQL/MariaDB as an alternative database backend alongside SQLite, including a full async SessionStore, connection pooling via mysql2, schema migrations, bcrypt password hashing, and frontend date-range controls. Many previously-flagged issues (bootstrap crash, migration008 exec(), content hash collision, DatabaseManager.close() throw) are now fixed, but several route handlers in DataRoutes.ts were not updated to be async, causing MySQL's async store methods to be silently mishandled.

• Six sync route handlers break for MySQL — handleGetObservationById, handleGetObservationsByIds, handleGetSessionById, handleGetSdkSessionsByIds, handleGetPromptById, and handleImport call async MySQLSessionStore methods without await, sending Promise objects as JSON responses and silently skipping all data in the import endpoint.
• /api/observations/by-file hardcodes the SQLite module — it imports getObservationsByFilePath from sqlite/observations/get.js and passes a MySQLDatabase instance to it, which will throw at runtime for MySQL.
• runner.ts ships as dead code with a conflicting schema — the file is @deprecated but not deleted; its version-26/27 assignments conflict with the live migrations.ts, making it a latent corruption risk.

Confidence Score: 2/5

Not safe to merge — six route handlers will silently return empty objects instead of real data when the MySQL backend is active, and the import endpoint will discard all submitted data without error.

The async/await gaps in DataRoutes.ts affect the majority of read endpoints and the entire import flow in MySQL mode. Every one of these handlers completes without error (no exception is thrown), so the breakage is invisible in logs — callers just receive empty responses. The SQLite-specific import hardcoded in the by-file handler adds a hard crash path on top of that.

src/services/worker/http/routes/DataRoutes.ts needs the most attention — six handlers need to be converted to async and all MySQL store calls need await. src/services/mysql/migrations/runner.ts should be deleted.

Important Files Changed

Filename	Overview
src/services/worker/http/routes/DataRoutes.ts	Six synchronous route handlers call async MySQLSessionStore methods without await, returning Promise objects as JSON; /api/observations/by-file hardcodes the SQLite module; getAllProjects is not awaited.
src/services/mysql/migrations.ts	Bootstrap check for schema_versions now correctly precedes the initial SELECT; migration004 records itself; migration008 uses a loop instead of exec(). Migration 028 adds the previously-missing merged_into_project columns.
src/services/mysql/migrations/runner.ts	Marked @deprecated but still present; contains conflicting version-number assignments for migrations 26 and 27 that diverge from the live migrations.ts schema — a latent corruption risk if ever imported.
src/services/mysql/AuthStore.ts	bcrypt is now preferred with SHA-256 as fallback; verifyPassword correctly handles both hash formats including the legacy salt:hash format.
src/services/mysql/SessionStore.ts	Comprehensive async MySQL session store; SHA-256 content hash replaces the prior 32-bit djb2; all methods are async, which is correct but means callers must await them.
src/services/worker/DatabaseManager.ts	close() now guards sessionSearch.close() with a typeof check; db.close() comment explains the shared-pool situation. Both previously-flagged issues addressed.
src/services/worker/PaginationHelper.ts	resolveResults() correctly dispatches sync/async .all() calls; migration 028 now supplies the merged_into_project columns referenced in queries.
src/services/mysql/Database.ts	Clean mysql2/promise pool wrapper with statement caching, bounded cache size, and transaction support. No issues found.
src/services/DatabaseFactory.ts	Thin factory with clean backend detection logic; defaults to SQLite for backward compatibility.

Flowchart

%%{init: {'theme': 'neutral'}}%%
flowchart TD
    A[HTTP Request] --> B{getDatabaseType}
    B -->|sqlite| C[ClaudeMemDatabase bun:sqlite sync]
    B -->|mysql| D[MySQLDatabase mysql2 async]
    C --> E[SQLite SessionStore sync methods]
    D --> F[MySQL SessionStore async methods]
    E --> G[Route Handlers no await needed]
    F --> H{Route Handler async flag?}
    H -->|async: getObservations getSummaries getStats getProjects| I[await store method correct response]
    H -->|sync: getObservationById import getSessionById| J[missing await Promise serializes to empty object]
    G --> K[PaginationHelper resolveResults handles sync and async]
    F --> K

Loading

%%{init: {'theme': 'base', 'themeVariables': {"darkMode": true, "background": "#0d1117", "primaryColor": "#21262d", "primaryTextColor": "#e6edf3", "primaryBorderColor": "#8b949e", "lineColor": "#8b949e", "textColor": "#e6edf3", "edgeLabelBackground": "#161b22", "actorBkg": "#21262d", "actorBorder": "#8b949e", "actorTextColor": "#e6edf3", "actorLineColor": "#8b949e", "signalColor": "#8b949e", "signalTextColor": "#e6edf3", "noteBkgColor": "#373320", "noteBorderColor": "#d4a72c", "noteTextColor": "#f0e6c0", "labelBoxBkgColor": "#21262d", "labelBoxBorderColor": "#8b949e", "labelTextColor": "#e6edf3", "loopTextColor": "#e6edf3", "activationBkgColor": "#30363d", "activationBorderColor": "#8b949e"}}}%%
flowchart TD
    A[HTTP Request] --> B{getDatabaseType}
    B -->|sqlite| C[ClaudeMemDatabase bun:sqlite sync]
    B -->|mysql| D[MySQLDatabase mysql2 async]
    C --> E[SQLite SessionStore sync methods]
    D --> F[MySQL SessionStore async methods]
    E --> G[Route Handlers no await needed]
    F --> H{Route Handler async flag?}
    H -->|async: getObservations getSummaries getStats getProjects| I[await store method correct response]
    H -->|sync: getObservationById import getSessionById| J[missing await Promise serializes to empty object]
    G --> K[PaginationHelper resolveResults handles sync and async]
    F --> K

Loading

Comments Outside Diff (2)

1. src/services/worker/http/routes/DataRoutes.ts, line 130-219 (link)

   Six route handlers return wrong data in MySQL mode

   handleGetObservationById, handleGetObservationsByIds, handleGetSessionById, handleGetSdkSessionsByIds, handleGetPromptById, and handleImport are all synchronous handlers that call MySQLSessionStore methods which are async. Without await, each call returns a Promise object instead of real data. Concretely: res.json(observations) serializes a Promise to {}, the sessions.length === 0 guard is skipped (Promise has no .length), and in handleImport every item lands in the skipped counter because result.imported resolves to undefined on a pending Promise — so no data is ever written to the database via this endpoint in MySQL mode. The handlers must be converted to async and all store method calls must be awaited.

   Prompt To Fix With AI

   This is a comment left during a code review.
   Path: src/services/worker/http/routes/DataRoutes.ts
   Line: 130-219
   
   Comment:
   **Six route handlers return wrong data in MySQL mode**
   
   `handleGetObservationById`, `handleGetObservationsByIds`, `handleGetSessionById`, `handleGetSdkSessionsByIds`, `handleGetPromptById`, and `handleImport` are all synchronous handlers that call `MySQLSessionStore` methods which are `async`. Without `await`, each call returns a Promise object instead of real data. Concretely: `res.json(observations)` serializes a Promise to `{}`, the `sessions.length === 0` guard is skipped (Promise has no `.length`), and in `handleImport` every item lands in the `skipped` counter because `result.imported` resolves to `undefined` on a pending Promise — so no data is ever written to the database via this endpoint in MySQL mode. The handlers must be converted to `async` and all store method calls must be `await`ed.
   
   How can I resolve this? If you propose a fix, please make it concise.

2. src/services/worker/http/routes/DataRoutes.ts, line 145-167 (link)

   /api/observations/by-file always uses the SQLite implementation

   getObservationsByFilePath is imported directly from '../../../sqlite/observations/get.js' (line 18) and called with the raw db object. When the backend is MySQL, db is a MySQLDatabase instance, but the SQLite helper expects a bun:sqlite Database object — it will throw at runtime. A parallel MySQL implementation already exists in src/services/mysql/observations/get.ts; the handler should dispatch to the correct version based on this.dbManager.getDatabaseType(), similar to how handleGetStats branches on databaseType.

   Prompt To Fix With AI

   This is a comment left during a code review.
   Path: src/services/worker/http/routes/DataRoutes.ts
   Line: 145-167
   
   Comment:
   **`/api/observations/by-file` always uses the SQLite implementation**
   
   `getObservationsByFilePath` is imported directly from `'../../../sqlite/observations/get.js'` (line 18) and called with the raw `db` object. When the backend is MySQL, `db` is a `MySQLDatabase` instance, but the SQLite helper expects a `bun:sqlite` `Database` object — it will throw at runtime. A parallel MySQL implementation already exists in `src/services/mysql/observations/get.ts`; the handler should dispatch to the correct version based on `this.dbManager.getDatabaseType()`, similar to how `handleGetStats` branches on `databaseType`.
   
   How can I resolve this? If you propose a fix, please make it concise.

Prompt To Fix All With AI

Fix the following 2 code review issues. Work through them one at a time, proposing concise fixes.

---

### Issue 1 of 2
src/services/worker/http/routes/DataRoutes.ts:130-219
**Six route handlers return wrong data in MySQL mode**

`handleGetObservationById`, `handleGetObservationsByIds`, `handleGetSessionById`, `handleGetSdkSessionsByIds`, `handleGetPromptById`, and `handleImport` are all synchronous handlers that call `MySQLSessionStore` methods which are `async`. Without `await`, each call returns a Promise object instead of real data. Concretely: `res.json(observations)` serializes a Promise to `{}`, the `sessions.length === 0` guard is skipped (Promise has no `.length`), and in `handleImport` every item lands in the `skipped` counter because `result.imported` resolves to `undefined` on a pending Promise — so no data is ever written to the database via this endpoint in MySQL mode. The handlers must be converted to `async` and all store method calls must be `await`ed.

### Issue 2 of 2
src/services/worker/http/routes/DataRoutes.ts:145-167
**`/api/observations/by-file` always uses the SQLite implementation**

`getObservationsByFilePath` is imported directly from `'../../../sqlite/observations/get.js'` (line 18) and called with the raw `db` object. When the backend is MySQL, `db` is a `MySQLDatabase` instance, but the SQLite helper expects a `bun:sqlite` `Database` object — it will throw at runtime. A parallel MySQL implementation already exists in `src/services/mysql/observations/get.ts`; the handler should dispatch to the correct version based on `this.dbManager.getDatabaseType()`, similar to how `handleGetStats` branches on `databaseType`.

_{Reviews (6): Last reviewed commit: "排除构建产物：viewer-bundle.js、mcp-server.cjs、p..." | Re-trigger Greptile}

[greptile-apps]

merged_into_project column absent from MySQL schema

The getObservations and getSummaries queries both reference o.merged_into_project / ss.merged_into_project in the SELECT list and, when project is supplied, in the WHERE clause. No MySQL migration adds this column to observations or session_summaries. Every call to the /api/observations and /api/summaries endpoints in MySQL mode will fail immediately with Error: Unknown column 'o.merged_into_project' in 'field list'. The column reference was pre-existing for SQLite (where the column does exist), but this PR routes MySQL through the same PaginationHelper and so now exposes the gap.

Prompt To Fix With AI

This is a comment left during a code review.
Path: src/services/worker/PaginationHelper.ts
Line: 65-111

Comment:
**`merged_into_project` column absent from MySQL schema**

The `getObservations` and `getSummaries` queries both reference `o.merged_into_project` / `ss.merged_into_project` in the SELECT list and, when `project` is supplied, in the WHERE clause. No MySQL migration adds this column to `observations` or `session_summaries`. Every call to the `/api/observations` and `/api/summaries` endpoints in MySQL mode will fail immediately with `Error: Unknown column 'o.merged_into_project' in 'field list'`. The column reference was pre-existing for SQLite (where the column does exist), but this PR routes MySQL through the same `PaginationHelper` and so now exposes the gap.

How can I resolve this? If you propose a fix, please make it concise.

[thedotmack]

Closing as outdated. This large MySQL backend targets an older storage architecture and would now require a fresh design against the current SQLite/Postgres storage boundary.