DC-MEM-006: Local Issue System Client [MVP-1]

[radoxtech]

Epic: #11 Memory and Project State Backbone [MVP-1]
Iteration: MVP-1
Package: @diricode/memory
Spec: docs/mvp/epic-memory.md → DC-MEM-006

Goal

Deliver a local-first issue management interface backed by SQLite for managing tasks, epics, and their relationships. All runtime issue state is stored locally — no network dependency, no rate limits, sub-millisecond reads.

Scope

• Interface: Define IIssueClient (backend-agnostic abstraction)
  • Must be testable via mocks/stubs without SQLite
  • Consumed by @diricode/core and pipeline through interface contracts only
• SQLite-native adapter implementing IIssueClient:
  • createIssue() — INSERT into local SQLite
  • updateIssue() — UPDATE local SQLite row
  • closeIssue() — UPDATE status in local SQLite
  • listIssues() — SELECT with filters (status, labels, assignee, parent)
  • searchIssues() — FTS5 full-text search over issue titles and descriptions
• Epic support:
  • Parent issue → child issue relationships via foreign keys
  • Parent-child traceability via parent_id column (not checklist text parsing)
  • Recursive queries for epic → all descendants
• Offline-first guarantees:
  • All operations write and read only from local SQLite
  • No network connection is required or checked for any state operation
• Sync adapter boundary:
  • Sync adapters (GitHub, GitLab, Jira) are output targets — they receive state changes from the local issue system
  • Adapters are planned for v2+ (see ADR-048)
  • No sync adapter logic in this issue — clean separation

Acceptance Criteria

• IIssueClient abstraction is backend-agnostic and testable via mocks
• SQLite adapter supports full CRUD + list + FTS5 search coverage
• Epic parent-child relationships are supported via foreign keys with parent_id
• All operations are local-only (no network dependency)
• No non-abstract direct SQL calls leak into other packages (@diricode/memory boundary enforced)
• Issue schema includes: id, title, description, status, priority, labels (JSON), parent_id, created_at, updated_at
• FTS5 virtual table covers title and description columns with write-through sync

Schema Sketch

CREATE TABLE issues (
  id TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  description TEXT,
  status TEXT NOT NULL DEFAULT 'open' CHECK(status IN ('open','in_progress','done','closed')),
  priority TEXT DEFAULT 'medium' CHECK(priority IN ('critical','high','medium','low')),
  labels TEXT DEFAULT '[]',  -- JSON array
  parent_id TEXT REFERENCES issues(id),
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE VIRTUAL TABLE issues_fts USING fts5(title, description, content=issues, content_rowid=rowid);

Implementation Notes

Mandatory Web Research Before Implementation

Research the following before starting:

1. Local-first issue tracking systems — Study git-bug (github.com/git-bug/git-bug), Fossil's built-in ticket system, and sit (Sit Issue Tracker) for local-first issue management patterns
2. Repository pattern for SQLite — Study how better-sqlite3 projects structure repository classes with typed queries. See the Drizzle ORM repository pattern for SQLite
3. FTS5 write-through triggers — Study SQLite FTS5 documentation on content tables and triggers for keeping FTS index in sync with base table on INSERT/UPDATE/DELETE
4. Parent-child hierarchies in SQLite — Study recursive CTEs (WITH RECURSIVE) for epic → subtask tree traversal

Reference Implementations

• OpenAI Agents SDK (openai-agents-python): Uses AdvancedSQLiteSession with SQLite for persistent state including message structure, branch management, and usage tracking. Schema includes message_structure table with foreign keys and turn_usage table — OpenAI Agents Advanced SQLite Session docs
• git-bug: Distributed bug tracker embedded in git. Demonstrates local-first issue management with offline capability and optional sync to GitHub/GitLab/Jira — architecture validates the "local-first with sync adapters" approach
• SQLite AI Ecosystem (sqliteai/sqlite-agent): Shows how SQLite can serve as both state store and agent runtime, with extensions for FTS, vector search, and memory — validates SQLite as a capable foundation for agent issue state
• Yodoca (VitalyOborin/yodoca): Graph cognitive memory on SQLite with FTS5 + typed edges + temporal ordering — demonstrates advanced SQLite patterns for AI agent state

Existing Code Context

• SQLite client already exists at packages/memory/src/db/client.ts (uses better-sqlite3, WAL mode enabled)
• Migration system exists at packages/memory/src/db/migrations/
• This issue adds a new migration for the issues table + FTS5 virtual table
• Must follow existing repository pattern in packages/memory/src/

Dependencies

• Upstream: DC-MEM-001 (SQLite setup + migrations) — ✅ already implemented
• Upstream: DC-MEM-004 (FTS5 search) — FTS5 infrastructure should be established first
• Downstream: Pipeline uses issue system for sprint task management
• Cross-epic: epic-pipeline (task execution reads/writes issues)

References

• ADR-048 (SQLite Issue System — architectural rationale)
• ADR-022 (superseded — old GitHub Issues approach)
• docs/mvp/epic-memory.md (canonical spec)
• cross-cutting.md (package boundary rules)