sqlite-db

SQLite 数据库技能

通过原始 SQL 构建可组合的知识数据库。

SQLite 数据库可移植、自包含，无需服务器。sqlite3 CLI 可直接使用关系型 SQL 的全部功能：索引、联接、聚合、窗口函数、CTE、全文搜索、JSON 函数、触发器和视图。本技能将教会 Agent 如何将 SQLite 用作知识管理的基础。

理念

SQL 即接口

没有包装器，没有抽象层。直接编写 SQL。这样便能使用 SQLite 的全部功能：复杂联接、窗口函数、CTE、FTS5、JSON 操作、触发器和视图。冗长只会消耗令牌，不会增加敲键次数；而表达能力会带来丰厚回报。

架构就是 DDL

无需 YAML 声明。CREATE TABLE 语句本身就是架构。运行 .schema 即可查看全部内容。列类型、约束、外键、索引——都在 DDL 中清晰可见。设计上即可自我说明。

可组合的 .db 文件

每个领域各用一个 .db 文件。笔记数据库、投资跟踪器和问题跟踪器都是独立文件。可移植——可分别复制、共享和备份。无需中央服务器。

兼容 Agent

sqlite3 CLI 每次调用都是确定且无状态的。输出模式（-header -column、.mode json、-line）均可解析。命令不会依赖会话状态。非常适合由大模型驱动的工作流。

数据库定位

始终将数据库路径作为第一个参数传给 sqlite3。 这样可确保行为无状态且确定。

# Single-line command
sqlite3 /path/to/mydata.db "SELECT * FROM notes WHERE status = 'active';"

# Multi-line command via heredoc
sqlite3 /path/to/mydata.db <<'SQL'
SELECT id, title, created_at
FROM notes
WHERE status = 'active'
ORDER BY created_at DESC;
SQL

输出模式

根据需要选择输出模式：

模式	用途	调用方式
列	便于阅读的表格	sqlite3 -header -column mydata.db "SELECT ..."
JSON	使用 jq 解析的 Agent	sqlite3 mydata.db "SELECT ..." | jq（在 .mode json 之后）
CSV	导出到电子表格	sqlite3 -csv -header mydata.db "SELECT ..."
行	检查单条记录	sqlite3 -line mydata.db "SELECT * FROM notes WHERE id = 'NOTE-...';"

JSON 模式示例

# Enable JSON output and query
sqlite3 /path/to/mydata.db <<'SQL'
.mode json
SELECT id, title, tags FROM notes LIMIT 5;
SQL

然后通过管道传给 jq 进行筛选或转换：

sqlite3 /path/to/mydata.db "SELECT ..." | jq -r '.[] | select(.status == "active") | .id'

核心操作

初始化数据库

创建数据库目录，并使用约束和 pragma 初始化表：

# Create directory
mkdir -p /path/to/.sqlite

# Initialize database with pragmas and schema
sqlite3 /path/to/.sqlite/mydata.db <<'SQL'
PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS notes (
  id TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  body TEXT,
  status TEXT NOT NULL CHECK (status IN ('draft', 'active', 'archived')) DEFAULT 'draft',
  tags TEXT CHECK (json_valid(tags)),
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_notes_status ON notes(status);
CREATE INDEX IF NOT EXISTS idx_notes_created ON notes(created_at DESC);
SQL

重要 pragma：

• PRAGMA journal_mode = WAL; — 支持并发读取并提升性能
• PRAGMA foreign_keys = ON; — 强制执行引用完整性

生成 ID

使用内联 SQL 表达式生成唯一、按时间排序且便于阅读的 ID：

'PREFIX-' || strftime('%Y%m%d', 'now') || '-' || lower(hex(randomblob(4)))

示例：

• 'NOTE-' || strftime('%Y%m%d', 'now') || '-' || lower(hex(randomblob(4))) → NOTE-20260208-a3f8c291
• 'TASK-' || strftime('%Y%m%d', 'now') || '-' || lower(hex(randomblob(4))) → TASK-20260208-7b2e9f41

前缀约定：

• 笔记：NOTE-
• 任务：TASK-
• 资源：RES-
• 剪藏：CLIP-
• 面包屑：CRUMB-
• 反思：REFL-

创建记录

使用内联 ID 生成来插入记录：

sqlite3 /path/to/.sqlite/mydata.db <<'SQL'
INSERT INTO notes (id, title, body, status, tags)
VALUES (
  'NOTE-' || strftime('%Y%m%d', 'now') || '-' || lower(hex(randomblob(4))),
  'Understanding Composability',
  'Systems that compose are systems that scale...',
  'active',
  json_array('systems', 'design', 'composability')
);
SQL

注意： JSON 数组字段请使用 json_array()，不要使用字符串拼接。

查询记录

# Simple query with filtering and ordering
sqlite3 -header -column /path/to/.sqlite/mydata.db <<'SQL'
SELECT id, title, status, created_at
FROM notes
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 10;
SQL

分页示例：

SELECT id, title
FROM notes
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;  -- Page 3 (20 per page)

用于脚本处理的 JSON 输出：

sqlite3 /path/to/.sqlite/mydata.db <<'SQL'
.mode json
SELECT id, title, tags FROM notes WHERE status = 'active';
SQL

显示记录

使用 -line 模式，以便于阅读的格式显示单条记录：

sqlite3 -line /path/to/.sqlite/mydata.db <<'SQL'
SELECT * FROM notes WHERE id = 'NOTE-20260208-a3f8c291';
SQL

输出：

        id = NOTE-20260208-a3f8c291
     title = Understanding Composability
      body = Systems that compose are systems that scale...
    status = active
      tags = ["systems","design","composability"]
created_at = 2026-02-08 14:32:01
updated_at = 2026-02-08 14:32:01

更新记录

sqlite3 /path/to/.sqlite/mydata.db <<'SQL'
UPDATE notes
SET
  status = 'archived',
  updated_at = datetime('now')
WHERE id = 'NOTE-20260208-a3f8c291';
SQL

批量更新示例：

UPDATE notes
SET status = 'archived', updated_at = datetime('now')
WHERE created_at < date('now', '-1 year');

删除记录

sqlite3 /path/to/.sqlite/mydata.db <<'SQL'
DELETE FROM notes WHERE id = 'NOTE-20260208-a3f8c291';
SQL

关系

SQLite 支持两种关系形式，各有不同的适用场景。

结构关系（外键列）

对于父子归属关系以及 1:1 或 N:1 关系，请使用外键列：

CREATE TABLE clippings (
  id TEXT PRIMARY KEY,
  content TEXT NOT NULL,
  resource_id TEXT,  -- Foreign key to resources table
  clipped_at TEXT NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY (resource_id) REFERENCES resources(id) ON DELETE CASCADE
);

CREATE INDEX idx_clippings_resource ON clippings(resource_id);

查询模式：

-- All clippings from a specific resource
SELECT c.id, c.content, c.clipped_at
FROM clippings c
WHERE c.resource_id = 'RES-20260208-f1a2b3c4';

-- Join to get resource details
SELECT c.id, c.content, r.title AS resource_title
FROM clippings c
JOIN resources r ON c.resource_id = r.id
WHERE r.status = 'finished';

灵活关系（链接表）

使用通用的 links 表来表示多对多、临时定义的命名关系：

CREATE TABLE links (
  source_id TEXT NOT NULL,
  target_id TEXT NOT NULL,
  rel_type TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  PRIMARY KEY (source_id, target_id, rel_type)
);

CREATE INDEX idx_links_source ON links(source_id, rel_type);
CREATE INDEX idx_links_target ON links(target_id, rel_type);

创建链接：

-- Single link
INSERT INTO links (source_id, target_id, rel_type)
VALUES ('NOTE-20260208-a3f8c291', 'NOTE-20260205-b2c3d4e5', 'linksTo');

-- Batch link creation
INSERT INTO links (source_id, target_id, rel_type)
SELECT 'CRUMB-20260208-f1f2f3f4', id, 'analyzedNotes'
FROM notes
WHERE tags LIKE '%systems%' AND created_at > date('now', '-7 days');

查询出站链接：

SELECT l.rel_type, n.id, n.title
FROM links l
JOIN notes n ON l.target_id = n.id
WHERE l.source_id = 'NOTE-20260208-a3f8c291';

查询入站链接：

SELECT l.rel_type, n.id, n.title
FROM links l
JOIN notes n ON l.source_id = n.id
WHERE l.target_id = 'NOTE-20260208-a3f8c291';

移除链接：

DELETE FROM links
WHERE source_id = 'NOTE-20260208-a3f8c291'
  AND target_id = 'NOTE-20260205-b2c3d4e5'
  AND rel_type = 'linksTo';

常见关系类型：

• linksTo — 通用连接
• derivedFrom — 内容源自另一条笔记
• partOf — 属于某个容器／集合
• analyzedNotes — 面包屑分析了这些笔记
• basedOnNotes — 反思基于这些笔记
• promotedTo — 反思提升为笔记

将视图用作已保存查询

视图比 memhub 的已保存查询功能更强大——它们可以使用联接、聚合，并引用其他视图。

创建视图

CREATE VIEW active_notes AS
SELECT id, title, status, created_at
FROM notes
WHERE status = 'active'
ORDER BY created_at DESC;

查询视图

SELECT * FROM active_notes LIMIT 10;

列出所有视图

SELECT name FROM sqlite_master WHERE type = 'view';

删除视图

DROP VIEW IF EXISTS active_notes;

复杂视图示例

-- Note graph view with link counts
CREATE VIEW note_graph AS
SELECT
  n.id,
  n.title,
  n.status,
  COUNT(DISTINCT lo.target_id) AS outgoing_links,
  COUNT(DISTINCT li.source_id) AS incoming_links
FROM notes n
LEFT JOIN links lo ON n.id = lo.source_id
LEFT JOIN links li ON n.id = li.target_id
GROUP BY n.id, n.title, n.status;

用于自动化的触发器

触发器可自动执行重复任务，例如更新时间戳和同步 FTS。

自动更新时间戳

CREATE TRIGGER update_notes_timestamp
AFTER UPDATE ON notes
FOR EACH ROW
BEGIN
  UPDATE notes SET updated_at = datetime('now') WHERE id = OLD.id;
END;

FTS 同步触发器

完整示例请参见下方的“全文搜索”部分。

全文搜索（FTS5）

SQLite 的 FTS5 扩展提供带排名的全文搜索。Memhub 无法实现此功能。

创建 FTS 虚拟表

CREATE VIRTUAL TABLE notes_fts USING fts5(
  id UNINDEXED,
  title,
  body,
  tags,
  content='notes',
  content_rowid='rowid'
);

同步触发器

使 FTS 索引与基础表保持同步：

-- Trigger: insert
CREATE TRIGGER notes_fts_insert AFTER INSERT ON notes BEGIN
  INSERT INTO notes_fts(rowid, id, title, body, tags)
  VALUES (NEW.rowid, NEW.id, NEW.title, NEW.body, NEW.tags);
END;

-- Trigger: update
CREATE TRIGGER notes_fts_update AFTER UPDATE ON notes BEGIN
  UPDATE notes_fts
  SET title = NEW.title, body = NEW.body, tags = NEW.tags
  WHERE rowid = OLD.rowid;
END;

-- Trigger: delete
CREATE TRIGGER notes_fts_delete AFTER DELETE ON notes BEGIN
  DELETE FROM notes_fts WHERE rowid = OLD.rowid;
END;

搜索查询

-- Simple search
SELECT id, title FROM notes_fts WHERE notes_fts MATCH 'composability';

-- Boolean operators
SELECT id, title FROM notes_fts WHERE notes_fts MATCH 'systems AND composability';

-- Phrase search
SELECT id, title FROM notes_fts WHERE notes_fts MATCH '"knowledge management"';

-- Ranked search with snippets
SELECT
  n.id,
  n.title,
  snippet(notes_fts, 1, '**', '**', '...', 32) AS snippet,
  bm25(notes_fts) AS rank
FROM notes_fts
JOIN notes n ON notes_fts.id = n.id
WHERE notes_fts MATCH 'composability'
ORDER BY rank
LIMIT 10;

JSON 函数

SQLite 为数组和对象字段提供了强大的 JSON 支持。

创建 JSON 数组

-- Inline array
INSERT INTO notes (id, title, tags)
VALUES (
  'NOTE-' || strftime('%Y%m%d', 'now') || '-' || lower(hex(randomblob(4))),
  'Example Note',
  json_array('tag1', 'tag2', 'tag3')
);

查询 JSON 数组

-- Check if array contains a value
SELECT id, title
FROM notes
WHERE EXISTS (
  SELECT 1 FROM json_each(notes.tags)
  WHERE json_each.value = 'systems'
);

-- Count array elements
SELECT id, title, json_array_length(tags) AS tag_count
FROM notes
WHERE json_array_length(tags) > 3;

-- Extract unique tags across all notes
SELECT DISTINCT json_each.value AS tag
FROM notes, json_each(notes.tags)
WHERE notes.status = 'active'
ORDER BY tag;

-- Tag cloud (aggregation)
SELECT
  json_each.value AS tag,
  COUNT(*) AS note_count
FROM notes, json_each(notes.tags)
GROUP BY json_each.value
ORDER BY note_count DESC;

更新 JSON 数组

-- Add a tag (append to array)
UPDATE notes
SET tags = json_insert(tags, '$[#]', 'new-tag')
WHERE id = 'NOTE-20260208-a3f8c291';

-- Remove a tag (requires rebuilding array)
UPDATE notes
SET tags = (
  SELECT json_group_array(value)
  FROM json_each(notes.tags)
  WHERE value != 'old-tag'
)
WHERE id = 'NOTE-20260208-a3f8c291';

JSON 验证

在 CHECK 约束中使用 json_valid()：

CREATE TABLE notes (
  id TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  tags TEXT CHECK (json_valid(tags)),
  metadata TEXT CHECK (json_valid(metadata) OR metadata IS NULL)
);

用于验证的 CHECK 约束

CHECK 约束可替代 YAML 枚举和模式验证。

枚举式约束

CREATE TABLE notes (
  id TEXT PRIMARY KEY,
  status TEXT NOT NULL CHECK (status IN ('draft', 'active', 'archived')) DEFAULT 'draft',
  epistemic TEXT CHECK (epistemic IN ('hypothesis', 'tested', 'validated', 'outdated'))
);

范围约束

CREATE TABLE resources (
  id TEXT PRIMARY KEY,
  rating INTEGER CHECK (rating >= 1 AND rating <= 5)
);

模式约束（正则表达式）

SQLite 的 CHECK 约束不支持原生正则表达式，但你可以验证格式：

CREATE TABLE resources (
  id TEXT PRIMARY KEY,
  url TEXT CHECK (url LIKE 'http%')
);

对于复杂验证，请在 INSERT 前使用应用层检查。

聚合

SQLite 支持 GROUP BY、COUNT、SUM、AVG、MIN、MAX 等。Memhub 无法实现此功能。

基本聚合

-- Notes per status
SELECT status, COUNT(*) AS count
FROM notes
GROUP BY status;

-- Average rating per resource type
SELECT resource_type, AVG(rating) AS avg_rating
FROM resources
WHERE rating IS NOT NULL
GROUP BY resource_type;

-- Monthly note creation counts
SELECT
  strftime('%Y-%m', created_at) AS month,
  COUNT(*) AS note_count
FROM notes
GROUP BY month
ORDER BY month DESC;

高级聚合

-- Tag cloud with percentages
WITH tag_counts AS (
  SELECT
    json_each.value AS tag,
    COUNT(*) AS count
  FROM notes, json_each(notes.tags)
  GROUP BY json_each.value
)
SELECT
  tag,
  count,
  ROUND(100.0 * count / SUM(count) OVER (), 2) AS percentage
FROM tag_counts
ORDER BY count DESC
LIMIT 20;

构建 SQLite-DB 技能

专用的 sqlite-db 技能采用一致的结构。它们指导 Agent 如何使用 SQLite 管理特定领域。

目录布局

skills/sqlite-<domain>/
├── SKILL.md                # Skill instructions (when to use, workflows, SQL examples)
├── assets/
│   ├── schema.sql          # DDL: tables, indexes, FTS, triggers
│   └── views.sql           # Reusable views
├── scripts/
│   ├── setup.sh            # Idempotent initialization script
│   └── examples.sh         # Demo workflows
└── references/
    └── queries.md          # Complex query recipes

SQLite 技能应定义的内容

1. 表 — 所有领域实体的 DDL，包含约束、索引和外键
2. 索引 — 用于常见查询的 B-tree 索引，以及用于搜索的 FTS 索引
3. FTS — 用于全文搜索的虚拟表和同步触发器
4. 视图 — 作为一等数据库对象保存的查询
5. 触发器 — 自动更新时间戳、同步 FTS、验证
6. 链接词汇表 — 命名关系类型（与 memhub 相同：linksTo、derivedFrom、partOf 等）
7. 数据库路径 — .db 文件的存放位置（例如，.sqlite/notes.db）
8. ID 前缀 — 生成人类可读 ID 的约定
9. 工作流 — 常见任务的分步 SQL 示例

设计原则

架构编码领域知识。 DDL 就是文档。使用有意义的列名、CHECK 约束、外键和索引。架构应该让你一眼看出哪些内容至关重要。

明确指定目标数据库。 每条 sqlite3 命令都应指定数据库的完整路径。仅使用无状态调用。

视图就是你的菜单。 为常见查询创建视图。视图可以组合使用——它们可以引用其他视图、使用联接并包含聚合。它们比 memhub 的已保存查询更强大。

兼顾两种关系形式。 使用结构性外键表示所有权（clipping→resource），使用灵活的链接表表示临时图关系（note→note）。

附上设置脚本。 提供一个幂等的 setup.sh，用于创建数据库、运行 DDL 并初始化视图。用户应能用一条命令初始化可用的数据库。

展示真实工作流。 不要只列出 SQL 模式——展示创建记录、建立关联、查询、更新和长期分析的完整流程。

坦诚说明权衡。 SQL 冗长，字符串转义也容易出错。但它的强大功能（JOIN、FTS、聚合、窗口函数）让它在某些领域值得采用。