SQLite backend for issue/PR storage

[tomzx]

Background

gh-cached currently stores cached GitHub issues and pull requests as JSON/YAML/markdown files on disk. While this works well for small repositories, performance degrades when caching repositories with hundreds or thousands of issues and PRs, since common operations (listing, searching, filtering) require reading and parsing many individual files.

A local SQLite database would provide significantly better performance for these workloads:

• Indexed lookups by issue/PR number, state, author, labels, etc.
• Efficient full-text or LIKE-based search across titles and bodies.
• Single-file storage that is still portable and inspectable.
• Lower I/O overhead than walking a directory of small files.

The goal is to introduce a SQLite-backed storage option for cached issues and PRs as an alternative (and eventually default) to the file-based backend.

Version

c263ac9

Acceptance Criteria

• A SQLite backend is implemented for storing cached issues and pull requests
• Schema covers the same fields currently persisted by the file-based backend (issue/PR metadata, body, labels, comments, etc.)
• Listing, searching, and filtering operations use indexed SQL queries rather than full directory scans
• The storage backend is selectable (config flag or option), so users can opt in to SQLite
• A migration path exists from the existing file-based cache to the SQLite cache (one-shot import or lazy migration)
• Benchmarks demonstrate improved performance over the file-based backend on a repository with at least a few hundred issues/PRs
• Tests cover the SQLite backend's read/write paths and the migration from file-based storage
• Documentation describes how to enable the SQLite backend and migrate existing caches

---

Created with create-issue (3004eed)