sqlite-db

SQLite データベーススキル

生の SQL による、組み合わせ可能なナレッジベース。

SQLite データベースはポータブルで自己完結しており、サーバーは不要です。sqlite3 CLI を使えば、インデックス、結合、集計、ウィンドウ関数、CTE、全文検索、JSON 関数、トリガー、ビューなど、リレーショナル SQL の機能を直接利用できます。このスキルでは、エージェントが SQLite をナレッジ管理の基盤として活用する方法を学びます。

基本方針

SQL がインターフェース

ラッパーも抽象化レイヤーもありません。SQL を直接組み立てます。これにより、複雑な結合、ウィンドウ関数、CTE、FTS5、JSON 操作、トリガー、ビューなど、SQLite の機能を余すところなく活用できます。冗長さが消費するのはトークンであり、キーストロークではありません。そして、その表現力は十分な見返りをもたらします。

スキーマは DDL

YAML による宣言はありません。CREATE TABLE 文そのものがスキーマです。.schema を実行すれば、すべてを確認できます。列の型、制約、外部キー、インデックスなど、すべてが DDL に表示されます。設計段階から自己文書化されています。

組み合わせ可能な .db ファイル

ドメインごとに独自の .db ファイルを用意します。メモのデータベース、投資トラッカー、課題トラッカーは、それぞれ別のファイルです。ポータブルなので、個別にコピー、共有、バックアップできます。中央サーバーは不要です。

エージェントとの互換性

sqlite3 CLI は決定論的で、呼び出しごとにステートレスです。出力モード（-header -column、.mode json、-line）は解析可能です。コマンドがセッション状態に依存することはありません。LLM 主導のワークフローに最適です。

データベースの指定

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

出力モード

用途に応じて出力モードを選択します。

モード	用途	実行方法
Column	人が読みやすいテーブル	sqlite3 -header -column mydata.db "SELECT ..."
JSON	jq を使ったエージェントによる解析	sqlite3 mydata.db "SELECT ..." | jq（.mode json の実行後）
CSV	スプレッドシートへのエクスポート	sqlite3 -csv -header mydata.db "SELECT ..."
Line	単一レコードの確認	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'

基本操作

データベースを初期化する

データベースのディレクトリを作成し、制約とプラグマを指定してテーブルを初期化します。

# 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 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 は、それぞれ異なる用途に適した 2 種類のリレーションシップをサポートしています。

構造的なリレーションシップ（外部キー列）

親子の所有関係や 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 スキルは、一貫した構成に従います。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の保存済みクエリよりも強力です。

2種類のリレーションシップを使います。 所有関係（clipping→resource）には構造的な外部キーを、臨機応変なグラフ上の関係（note→note）には柔軟なリンクテーブルを使います。

セットアップスクリプトを含めます。 データベースを作成し、DDLを実行して、ビューを初期化する冪等なsetup.shを用意します。ユーザーが1つのコマンドで、使用可能なデータベースを初期化できるようにします。

実際のワークフローを示します。 SQLのパターンを列挙するだけでなく、レコードの作成、リンク付け、クエリ、更新、長期的な分析まで、一連の流れを示します。

トレードオフを正直に説明します。 SQLは冗長です。文字列のエスケープには注意が必要です。しかし、その強力な機能（JOIN、FTS、集計、ウィンドウ関数）は、特定のドメインでは十分に活用する価値があります。