Skill: sqlite-database-recovery

• typ: interní publikace agentem vytvořeného skillu
• vytvořil: agent
• created_at: 2026-05-21T18:13:38.814662+00:00
• patch_count: 4
• dostupné profily: default
• zdroj: ~/.hermes/skills/software-development/sqlite-database-recovery/SKILL.md

---

SQLite database recovery

Use this when an app, dashboard, worker, or API starts failing with SQLite corruption symptoms such as:

• database disk image is malformed
• file is not a database
• one board / tenant / project fails while the rest of the app still works
• HTTP 500s or plugin failures trace back to opening a specific .db file

Goal

Restore service safely while preserving the corrupt file for later recovery and making it explicit whether the fix is a reset or a true salvage.

Workflow

1. Prove the failing DB path.
2. Read the app/service logs first.
3. Identify the exact SQLite file path mentioned in the traceback or implied by the failing tenant/board.

4. Check whether the rest of the service is healthy; often only one database file is bad.

5. Measure scope before touching anything.

6. Check which endpoints / tenants / boards fail versus which still return 200.
7. Run SQLite integrity checks on the suspected file and a few sibling DBs to confirm the blast radius.

8. If there is an application-specific selector (board, tenant, project), test both the broken selector and a healthy one.

9. Preserve evidence first.

10. Copy the corrupt DB to a backup location outside any public/app-serving root.
11. Also rename or move the in-place file with a timestamped .corrupt-<ts> suffix so the original bytes remain available.

12. Preserve adjacent logs / workspaces that may be enough to reconstruct lost state later.

13. Check for restorable backups before deciding.

14. Look for recent backup artifacts first, including ZIP archives that may contain the affected DB.
15. For Hermes DB incidents specifically, treat a ZIP backup as a recovery option that must be evaluated before ad-hoc reset/recreate work.

16. Do not restore, extract into place, or otherwise use the backup for recovery until the user explicitly approves that recovery path.

17. Choose recovery mode deliberately.

18. Restore from approved backup when a suitable backup exists and the user approves using it.
19. Reset/recreate when the damaged DB is a secondary board/cache/derived state and restoring availability matters more than keeping its contents.
20. Salvage/export when the DB is primary data and loss is unacceptable.

21. Be explicit in the final report which mode you used.

22. Recreate or migrate using the app’s own initializer when possible.

23. Prefer the application’s schema/bootstrap command or library init function over hand-written SQL.

24. This keeps schema, WAL settings, migrations, and indexes aligned with the app.

25. Verify at three layers.

26. Database: PRAGMA integrity_check returns ok.
27. App/API: the previously failing endpoint now returns success.

28. UX: the previously broken screen/plugin/page loads without the original error.

29. Report residual risk.

30. Say whether recovered DB contents were preserved, reset to empty, or still need later salvage.
31. Provide the backup paths.

Pitfalls

• Do not overwrite the corrupt DB in place before taking a backup.
• Do not restore from a backup ZIP, extract it into place, or present backup recovery as chosen until the user has explicitly approved that path.
• Do not claim data was recovered if you only recreated an empty schema.
• Do not stop after a healthy process check; verify the exact endpoint / selector that previously failed.
• If only one board/tenant DB is corrupt, avoid broad service restarts or unrelated config changes.
• If the app uses per-board/per-tenant DB files, verify the failing selector specifically; the default selector may still look healthy.

SQLite triage heuristics

• database disk image is malformed usually means the file has a valid SQLite header but broken pages/schema.
• file is not a database points more toward wrong file contents, truncation, or a non-SQLite file at that path.
• If sibling DBs pass integrity checks, prefer targeted recovery over global rollback.

Root-cause narrowing after recovery

When the visible outage appears after an app update or restart, do not assume the update caused the corruption.

1. Check the earliest service/gateway logs that mention the board/tenant DB becoming invalid.
2. Compare that timestamp with the corrupted file's mtime.
3. Distinguish these cases explicitly in your report:
4. Invalid header / not a database → file contents were overwritten/truncated or replaced with non-SQLite bytes.
5. Valid SQLite format 3 header + database disk image is malformed → the file is still SQLite-shaped, but internal pages/schema are damaged; this points more toward interrupted writes, torn file manipulation, or bad WAL/journal interaction than a plain wrong-file overwrite.
6. Check for corroborating host evidence before blaming infrastructure: reboot/power loss, OOM kill, kernel I/O errors, filesystem errors.
7. If there is no such corroboration, report the cause class conservatively as corruption during write or external live-file manipulation, not as a proven app-update regression.

For Hermes Kanban specifically, a stricter post-update corruption guard may make a previously latent problem suddenly visible. Phrase that as: the update surfaced existing corruption unless you have evidence that the upgrade path itself wrote the bad bytes.

Verification checklist

• Exact failing DB path identified
• Backup copy stored safely outside public roots
• In-place corrupt file preserved with timestamped suffix
• Fresh DB created via app-native init/migration path when reset was chosen
• Integrity check passes on the new DB
• Previously failing endpoint/page now returns 200 / renders
• Final report states whether data was reset or salvaged

References

• references/hermes-kanban-board-corruption.md — Hermes Kanban/dashboard-specific example of one corrupted board DB breaking a plugin while other boards remained healthy.