45 lines
2.2 KiB
Markdown
45 lines
2.2 KiB
Markdown
|
---
|
||
|
|
id: "0004"
|
||
|
|
date: 2026-06-03
|
||
|
|
status: Superseded
|
||
|
|
supersedes:
|
||
|
|
superseded-by: "0010"
|
||
|
|
affected-paths: []
|
||
|
|
affected-components: []
|
||
|
|
migration_confidence: medium
|
||
|
|
migration_source: "docs/memory-system/03-architecture-decisions.md### ADR-004 — SQLite + Sequel (Ruby) tag index as the knowledge-layer cache"
|
||
|
|
---
|
||
|
|
|
||
|
|
# 0004 — SQLite + Sequel (Ruby) tag index as the knowledge-layer cache
|
||
|
|
|
||
|
|
## Context
|
||
|
|
|
||
|
|
The AI can't use Obsidian tags directly; tag filtering needs a machine-queryable
|
||
|
|
index. A previous `~/Documents/SecondBrain/` tag database was lost track of.
|
||
|
|
|
||
|
|
## Decision
|
||
|
|
|
||
|
|
A small **Ruby program using the Sequel ORM over SQLite**, exposed as a **CLI**.
|
||
|
|
Schema: `files(path, mtime, summary, scope)`, `tags(name)`, `files_tags` join
|
||
|
|
(`many_to_many`). The summary is a **column on `files`** (an attribute), not a join.
|
||
|
|
- **Rationale**: Normalized `tags` table makes enumerating the vocabulary a first-class cheap
|
||
|
|
query (the "virtual index" goal). The `summary` column is what turns the index from a
|
||
|
|
*finder* into a *router* — the AI sees enough to pick a file without opening it (progressive
|
||
|
|
disclosure, low tokens). Ruby + Sequel + CLI keeps the contract clean and the DB swappable;
|
||
|
|
the AI never touches SQLite directly.
|
||
|
|
- **Failure-mode guard (the lost-SecondBrain lesson)**: **markdown is always authoritative; the
|
||
|
|
SQLite file is a disposable cache** that is never synced and can be rebuilt from frontmatter
|
||
|
|
anytime (`index update --rebuild`).
|
||
|
|
|
||
|
|
## Consequences
|
||
|
|
|
||
|
|
A Ruby/Sequel/SQLite CLI tag index was built as the machine-queryable cache over the vault, with markdown treated as always-authoritative and the SQLite file as a disposable, rebuildable cache — never synced. This bespoke index and its CLI were later dropped and superseded by ADR-010's Graphify graph, though the summary + namespaced-tag frontmatter it introduced was retained as note metadata.
|
||
|
|
|
||
|
|
## Alternatives rejected
|
||
|
|
|
||
|
|
Plain-markdown generated `INDEX.md` (must regenerate; grep-at-scale
|
||
|
|
is token-heavy). Frontmatter grep on demand (scales badly). Milvus/Postgres for knowledge
|
||
|
|
(overkill; QMD/memsearch prove SQLite is enough — see ADR-006/008).
|
||
|
|
- **Query output**: returns **path + summary + matched tags** (option C) — tags are cheap and
|
||
|
|
show *why* a result matched, useful for cross-client queries.
|