404 lines
8.4 KiB
Markdown
404 lines
8.4 KiB
Markdown
# Database Diagnostics
|
|
|
|
SQLite database troubleshooting for claude-mem.
|
|
|
|
## Database Overview
|
|
|
|
Claude-mem uses SQLite3 for persistent storage:
|
|
- **Location:** `~/.claude-mem/claude-mem.db`
|
|
- **Library:** better-sqlite3 (synchronous, not bun:sqlite)
|
|
- **Features:** FTS5 full-text search, triggers, indexes
|
|
- **Tables:** observations, sessions, user_prompts, observations_fts, sessions_fts, prompts_fts
|
|
|
|
## Basic Database Checks
|
|
|
|
### Check Database Exists
|
|
|
|
```bash
|
|
# Check file exists
|
|
ls -lh ~/.claude-mem/claude-mem.db
|
|
|
|
# Check file size
|
|
du -h ~/.claude-mem/claude-mem.db
|
|
|
|
# Check permissions
|
|
ls -la ~/.claude-mem/claude-mem.db
|
|
```
|
|
|
|
**Expected:**
|
|
- File exists
|
|
- Size: 100KB - 10MB+ (depends on usage)
|
|
- Permissions: Readable/writable by your user
|
|
|
|
### Check Database Integrity
|
|
|
|
```bash
|
|
# Run integrity check
|
|
sqlite3 ~/.claude-mem/claude-mem.db "PRAGMA integrity_check;"
|
|
```
|
|
|
|
**Expected output:** `ok`
|
|
|
|
**If errors appear:**
|
|
- Database corrupted
|
|
- Backup immediately: `cp ~/.claude-mem/claude-mem.db ~/.claude-mem/claude-mem.db.backup`
|
|
- Consider recreating (data loss)
|
|
|
|
## Data Inspection
|
|
|
|
### Count Records
|
|
|
|
```bash
|
|
# Observation count
|
|
sqlite3 ~/.claude-mem/claude-mem.db "SELECT COUNT(*) FROM observations;"
|
|
|
|
# Session count
|
|
sqlite3 ~/.claude-mem/claude-mem.db "SELECT COUNT(*) FROM sessions;"
|
|
|
|
# User prompt count
|
|
sqlite3 ~/.claude-mem/claude-mem.db "SELECT COUNT(*) FROM user_prompts;"
|
|
|
|
# FTS5 table counts (should match main tables)
|
|
sqlite3 ~/.claude-mem/claude-mem.db "SELECT COUNT(*) FROM observations_fts;"
|
|
sqlite3 ~/.claude-mem/claude-mem.db "SELECT COUNT(*) FROM sessions_fts;"
|
|
sqlite3 ~/.claude-mem/claude-mem.db "SELECT COUNT(*) FROM prompts_fts;"
|
|
```
|
|
|
|
### View Recent Records
|
|
|
|
```bash
|
|
# Recent observations
|
|
sqlite3 ~/.claude-mem/claude-mem.db "
|
|
SELECT
|
|
created_at,
|
|
type,
|
|
title,
|
|
project
|
|
FROM observations
|
|
ORDER BY created_at DESC
|
|
LIMIT 10;
|
|
"
|
|
|
|
# Recent sessions
|
|
sqlite3 ~/.claude-mem/claude-mem.db "
|
|
SELECT
|
|
created_at,
|
|
request,
|
|
project
|
|
FROM sessions
|
|
ORDER BY created_at DESC
|
|
LIMIT 5;
|
|
"
|
|
|
|
# Recent user prompts
|
|
sqlite3 ~/.claude-mem/claude-mem.db "
|
|
SELECT
|
|
created_at,
|
|
prompt
|
|
FROM user_prompts
|
|
ORDER BY created_at DESC
|
|
LIMIT 10;
|
|
"
|
|
```
|
|
|
|
### Check Projects
|
|
|
|
```bash
|
|
# List all projects
|
|
sqlite3 ~/.claude-mem/claude-mem.db "
|
|
SELECT DISTINCT project
|
|
FROM observations
|
|
ORDER BY project;
|
|
"
|
|
|
|
# Count observations per project
|
|
sqlite3 ~/.claude-mem/claude-mem.db "
|
|
SELECT
|
|
project,
|
|
COUNT(*) as count
|
|
FROM observations
|
|
GROUP BY project
|
|
ORDER BY count DESC;
|
|
"
|
|
```
|
|
|
|
## Database Schema
|
|
|
|
### View Table Structure
|
|
|
|
```bash
|
|
# List all tables
|
|
sqlite3 ~/.claude-mem/claude-mem.db ".tables"
|
|
|
|
# Show observations table schema
|
|
sqlite3 ~/.claude-mem/claude-mem.db ".schema observations"
|
|
|
|
# Show all schemas
|
|
sqlite3 ~/.claude-mem/claude-mem.db ".schema"
|
|
```
|
|
|
|
### Expected Tables
|
|
|
|
- `observations` - Main observation records
|
|
- `observations_fts` - FTS5 virtual table for full-text search
|
|
- `sessions` - Session summary records
|
|
- `sessions_fts` - FTS5 virtual table for session search
|
|
- `user_prompts` - User prompt records
|
|
- `prompts_fts` - FTS5 virtual table for prompt search
|
|
|
|
## FTS5 Synchronization
|
|
|
|
The FTS5 tables should stay synchronized with main tables via triggers.
|
|
|
|
### Check FTS5 Sync
|
|
|
|
```bash
|
|
# Compare counts
|
|
sqlite3 ~/.claude-mem/claude-mem.db "
|
|
SELECT
|
|
(SELECT COUNT(*) FROM observations) as observations,
|
|
(SELECT COUNT(*) FROM observations_fts) as observations_fts,
|
|
(SELECT COUNT(*) FROM sessions) as sessions,
|
|
(SELECT COUNT(*) FROM sessions_fts) as sessions_fts,
|
|
(SELECT COUNT(*) FROM user_prompts) as prompts,
|
|
(SELECT COUNT(*) FROM prompts_fts) as prompts_fts;
|
|
"
|
|
```
|
|
|
|
**Expected:** All pairs should match (observations = observations_fts, etc.)
|
|
|
|
### Fix FTS5 Desync
|
|
|
|
If FTS5 counts don't match, triggers may have failed. Restart worker to rebuild:
|
|
|
|
```bash
|
|
pm2 restart claude-mem-worker
|
|
```
|
|
|
|
The worker will rebuild FTS5 indexes on startup if they're out of sync.
|
|
|
|
## Common Database Issues
|
|
|
|
### Issue: Database Doesn't Exist
|
|
|
|
**Cause:** First run, or database was deleted
|
|
|
|
**Fix:** Database will be created automatically on first observation. No action needed.
|
|
|
|
### Issue: Database is Empty (0 Records)
|
|
|
|
**Cause:**
|
|
- New installation (normal)
|
|
- Data was deleted
|
|
- Worker not processing observations
|
|
|
|
**Fix:**
|
|
1. Create test observation (use any skill and cancel)
|
|
2. Check worker logs for errors:
|
|
```bash
|
|
pm2 logs claude-mem-worker --lines 50 --nostream
|
|
```
|
|
3. Verify observation appears in database
|
|
|
|
### Issue: Database Permission Denied
|
|
|
|
**Cause:** File permissions wrong, database owned by different user
|
|
|
|
**Fix:**
|
|
```bash
|
|
# Check ownership
|
|
ls -la ~/.claude-mem/claude-mem.db
|
|
|
|
# Fix permissions (if needed)
|
|
chmod 644 ~/.claude-mem/claude-mem.db
|
|
chown $USER ~/.claude-mem/claude-mem.db
|
|
```
|
|
|
|
### Issue: Database Locked
|
|
|
|
**Cause:**
|
|
- Multiple processes accessing database
|
|
- Crash left lock file
|
|
- Long-running transaction
|
|
|
|
**Fix:**
|
|
```bash
|
|
# Check for lock file
|
|
ls -la ~/.claude-mem/claude-mem.db-wal
|
|
ls -la ~/.claude-mem/claude-mem.db-shm
|
|
|
|
# Remove lock files (only if worker is stopped!)
|
|
pm2 stop claude-mem-worker
|
|
rm ~/.claude-mem/claude-mem.db-wal ~/.claude-mem/claude-mem.db-shm
|
|
pm2 start claude-mem-worker
|
|
```
|
|
|
|
### Issue: Database Growing Too Large
|
|
|
|
**Cause:** Too many observations accumulated
|
|
|
|
**Check size:**
|
|
```bash
|
|
du -h ~/.claude-mem/claude-mem.db
|
|
sqlite3 ~/.claude-mem/claude-mem.db "SELECT COUNT(*) FROM observations;"
|
|
```
|
|
|
|
**Options:**
|
|
1. Delete old observations (manual cleanup):
|
|
```bash
|
|
sqlite3 ~/.claude-mem/claude-mem.db "
|
|
DELETE FROM observations
|
|
WHERE created_at < datetime('now', '-90 days');
|
|
"
|
|
```
|
|
|
|
2. Vacuum to reclaim space:
|
|
```bash
|
|
sqlite3 ~/.claude-mem/claude-mem.db "VACUUM;"
|
|
```
|
|
|
|
3. Archive and start fresh:
|
|
```bash
|
|
mv ~/.claude-mem/claude-mem.db ~/.claude-mem/claude-mem.db.archive
|
|
pm2 restart claude-mem-worker
|
|
```
|
|
|
|
## Database Recovery
|
|
|
|
### Backup Database
|
|
|
|
**Before any destructive operations:**
|
|
```bash
|
|
cp ~/.claude-mem/claude-mem.db ~/.claude-mem/claude-mem.db.backup
|
|
```
|
|
|
|
### Restore from Backup
|
|
|
|
```bash
|
|
pm2 stop claude-mem-worker
|
|
cp ~/.claude-mem/claude-mem.db.backup ~/.claude-mem/claude-mem.db
|
|
pm2 start claude-mem-worker
|
|
```
|
|
|
|
### Export Data
|
|
|
|
Export to JSON for safekeeping:
|
|
|
|
```bash
|
|
# Export observations
|
|
sqlite3 ~/.claude-mem/claude-mem.db -json "SELECT * FROM observations;" > observations.json
|
|
|
|
# Export sessions
|
|
sqlite3 ~/.claude-mem/claude-mem.db -json "SELECT * FROM sessions;" > sessions.json
|
|
|
|
# Export prompts
|
|
sqlite3 ~/.claude-mem/claude-mem.db -json "SELECT * FROM user_prompts;" > prompts.json
|
|
```
|
|
|
|
### Recreate Database
|
|
|
|
**WARNING: Data loss. Backup first!**
|
|
|
|
```bash
|
|
# Stop worker
|
|
pm2 stop claude-mem-worker
|
|
|
|
# Backup current database
|
|
cp ~/.claude-mem/claude-mem.db ~/.claude-mem/claude-mem.db.old
|
|
|
|
# Delete database
|
|
rm ~/.claude-mem/claude-mem.db
|
|
|
|
# Start worker (creates new database)
|
|
pm2 start claude-mem-worker
|
|
```
|
|
|
|
## Database Statistics
|
|
|
|
### Storage Analysis
|
|
|
|
```bash
|
|
# Database file size
|
|
du -h ~/.claude-mem/claude-mem.db
|
|
|
|
# Record counts by type
|
|
sqlite3 ~/.claude-mem/claude-mem.db "
|
|
SELECT
|
|
type,
|
|
COUNT(*) as count
|
|
FROM observations
|
|
GROUP BY type
|
|
ORDER BY count DESC;
|
|
"
|
|
|
|
# Observations per month
|
|
sqlite3 ~/.claude-mem/claude-mem.db "
|
|
SELECT
|
|
strftime('%Y-%m', created_at) as month,
|
|
COUNT(*) as count
|
|
FROM observations
|
|
GROUP BY month
|
|
ORDER BY month DESC;
|
|
"
|
|
|
|
# Average observation size (characters)
|
|
sqlite3 ~/.claude-mem/claude-mem.db "
|
|
SELECT
|
|
AVG(LENGTH(content)) as avg_content_length,
|
|
MAX(LENGTH(content)) as max_content_length
|
|
FROM observations;
|
|
"
|
|
```
|
|
|
|
## Advanced Queries
|
|
|
|
### Find Specific Observations
|
|
|
|
```bash
|
|
# Search by keyword (FTS5)
|
|
sqlite3 ~/.claude-mem/claude-mem.db "
|
|
SELECT title, created_at
|
|
FROM observations_fts
|
|
WHERE observations_fts MATCH 'authentication'
|
|
ORDER BY created_at DESC;
|
|
"
|
|
|
|
# Find by type
|
|
sqlite3 ~/.claude-mem/claude-mem.db "
|
|
SELECT title, created_at
|
|
FROM observations
|
|
WHERE type = 'bugfix'
|
|
ORDER BY created_at DESC
|
|
LIMIT 10;
|
|
"
|
|
|
|
# Find by file path
|
|
sqlite3 ~/.claude-mem/claude-mem.db "
|
|
SELECT title, created_at
|
|
FROM observations
|
|
WHERE file_path LIKE '%auth%'
|
|
ORDER BY created_at DESC;
|
|
"
|
|
```
|
|
|
|
## Database Maintenance
|
|
|
|
### Regular Maintenance Tasks
|
|
|
|
```bash
|
|
# Analyze for query optimization
|
|
sqlite3 ~/.claude-mem/claude-mem.db "ANALYZE;"
|
|
|
|
# Rebuild FTS5 indexes
|
|
sqlite3 ~/.claude-mem/claude-mem.db "
|
|
INSERT INTO observations_fts(observations_fts) VALUES('rebuild');
|
|
INSERT INTO sessions_fts(sessions_fts) VALUES('rebuild');
|
|
INSERT INTO prompts_fts(prompts_fts) VALUES('rebuild');
|
|
"
|
|
|
|
# Vacuum to reclaim space
|
|
sqlite3 ~/.claude-mem/claude-mem.db "VACUUM;"
|
|
```
|
|
|
|
**Run monthly to keep database healthy.**
|