sqlite-db: Скилл для создания и управления базами данных

--- name: sqlite-db description: General guide for using the sqlite3 CLI to build composable knowledge databases. Use this skill when creating SQLite databases, designing schemas, querying data, managing relationships, or building new sqlite-based domain skills. Provides the foundational patterns that all specialized sqlite skills build upon. --- # SQLite Database Skills **Composable knowledge databases via raw SQL.** SQLite databases are portable, self-contained, and require no server. The `sqlite3` CLI provides direct access to the full power of relational SQL: indexes, joins, aggregations, window functions, CTEs, full-text search, JSON functions, triggers, and views. This skill teaches agents how to use SQLite as a knowledge management substrate. ## Philosophy ### SQL is the Interface No wrapper, no abstraction layer. You compose SQL directly. This gives you the full power of SQLite: complex joins, window functions, CTEs, FTS5, JSON operations, triggers, and views. Verbosity costs tokens, not keystrokes — and the expressiveness pays dividends. ### Schemas Are the DDL No YAML declarations. The `CREATE TABLE` statements *are* the schema. Run `.schema` to see everything. Column types, constraints, foreign keys, indexes — all visible in the DDL. Self-documenting by design. ### Composable .db Files Each domain gets its own `.db` file. Your notes database, investment tracker, and issue tracker are separate files. Portable — copy, share, back up independently. No central server required. ### Agent-Compatible The `sqlite3` CLI is deterministic and stateless per invocation. Output modes (`-header -column`, `.mode json`, `-line`) are parseable. Commands never rely on session state. Perfect for LLM-driven workflows. ## Database Targeting **Always pass the database path as the first argument to `sqlite3`.** This ensures stateless, deterministic behavior. ```bash # 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 ``` ## Output Modes Choose the output mode based on your needs: | Mode | Use Case | Invocation | |------|----------|------------| | **Column** | Human-readable tables | `sqlite3 -header -column mydata.db "SELECT ..."` | | **JSON** | Agent parsing with jq | `sqlite3 mydata.db "SELECT ..." | jq` (after `.mode json`) | | **CSV** | Export to spreadsheets | `sqlite3 -csv -header mydata.db "SELECT ..."` | | **Line** | Single record inspection | `sqlite3 -line mydata.db "SELECT * FROM notes WHERE id = 'NOTE-...';"` | ### JSON Mode Example ```bash # Enable JSON output and query sqlite3 /path/to/mydata.db <<'SQL' .mode json SELECT id, title, tags FROM notes LIMIT 5; SQL ``` Then pipe to `jq` for filtering or transformation: ```bash sqlite3 /path/to/mydata.db "SELECT ..." | jq -r '.[] | select(.status == "active") | .id' ``` ## Core Operations ### Initialize a Database Create the database directory and initialize tables with constraints and pragmas: ```bash # 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 ``` **Important pragmas:** - `PRAGMA journal_mode = WAL;` — enables concurrent reads and better performance - `PRAGMA foreign_keys = ON;` — enforces referential integrity ### Generate IDs Use inline SQL expressions to generate unique, time-ordered, human-readable IDs: ```sql 'PREFIX-' || strftime('%Y%m%d', 'now') || '-' || lower(hex(randomblob(4))) ``` **Examples:** - `'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` **Prefix conventions:** - Notes: `NOTE-` - Tasks: `TASK-` - Resources: `RES-` - Clippings: `CLIP-` - Breadcrumbs: `CRUMB-` - Reflections: `REFL-` ### Create Records Insert records with inline ID generation: ```bash 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 ``` **Note:** Use `json_array()` for JSON array fields, not string concatenation. ### Query Records ```bash # 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 ``` **Pagination example:** ```sql SELECT id, title FROM notes ORDER BY created_at DESC LIMIT 20 OFFSET 40; -- Page 3 (20 per page) ``` **JSON output for scripting:** ```bash sqlite3 /path/to/.sqlite/mydata.db <<'SQL' .mode json SELECT id, title, tags FROM notes WHERE status = 'active'; SQL ``` ### Show a Record Use `-line` mode for human-readable single-record display: ```bash sqlite3 -line /path/to/.sqlite/mydata.db <<'SQL' SELECT * FROM notes WHERE id = 'NOTE-20260208-a3f8c291'; SQL ``` Output: ``` 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 ``` ### Update Records ```bash sqlite3 /path/to/.sqlite/mydata.db <<'SQL' UPDATE notes SET status = 'archived', updated_at = datetime('now') WHERE id = 'NOTE-20260208-a3f8c291'; SQL ``` **Batch update example:** ```sql UPDATE notes SET status = 'archived', updated_at = datetime('now') WHERE created_at < date('now', '-1 year'); ``` ### Delete Records ```bash sqlite3 /path/to/.sqlite/mydata.db <<'SQL' DELETE FROM notes WHERE id = 'NOTE-20260208-a3f8c291'; SQL ``` ## Relationships SQLite supports two relationship styles, each with distinct use cases. ### Structural Relationships (Foreign Key Columns) Use foreign key columns for parent-child ownership and 1:1 or N:1 relationships: ```sql 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); ``` **Query pattern:** ```sql -- 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'; ``` ### Flexible Relationships (Links Table) Use a generic `links` table for many-to-many, ad-hoc, named relationships: ```sql 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); ``` **Create links:** ```sql -- 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'); ``` **Query outgoing links:** ```sql 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'; ``` **Query incoming links:** ```sql 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'; ``` **Remove links:** ```sql DELETE FROM links WHERE source_id = 'NOTE-20260208-a3f8c291' AND target_id = 'NOTE-20260205-b2c3d4e5' AND rel_type = 'linksTo'; ``` **Common relationship types:** - `linksTo` — general connection - `derivedFrom` — content derived from another note - `partOf` — belongs to a container/collection - `analyzedNotes` — breadcrumb analyzed these notes - `basedOnNotes` — reflection based on these notes - `promotedTo` — reflection promoted to note ## Views as Saved Queries Views are more powerful than memhub's saved queries — they can use joins, aggregations, and reference other views. ### Create a View ```sql CREATE VIEW active_notes AS SELECT id, title, status, created_at FROM notes WHERE status = 'active' ORDER BY created_at DESC; ``` ### Query a View ```sql SELECT * FROM active_notes LIMIT 10; ``` ### List All Views ```sql SELECT name FROM sqlite_master WHERE type = 'view'; ``` ### Drop a View ```sql DROP VIEW IF EXISTS active_notes; ``` ### Complex View Example ```sql -- 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; ``` ## Triggers for Automation Triggers automate repetitive tasks like timestamp updates and FTS synchronization. ### Auto-Update Timestamps ```sql 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 Sync Triggers See "Full-Text Search" section below for complete examples. ## Full-Text Search (FTS5) SQLite's FTS5 extension provides ranked full-text search. Memhub cannot do this. ### Create FTS Virtual Table ```sql CREATE VIRTUAL TABLE notes_fts USING fts5( id UNINDEXED, title, body, tags, content='notes', content_rowid='rowid' ); ``` ### Sync Triggers Keep the FTS index synchronized with the base table: ```sql -- 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; ``` ### Search Queries ```sql -- 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 Functions SQLite provides robust JSON support for array and object fields. ### Creating JSON Arrays ```sql -- 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') ); ``` ### Querying JSON Arrays ```sql -- 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; ``` ### Updating JSON Arrays ```sql -- 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 Validation Use `json_valid()` in CHECK constraints: ```sql 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 Constraints for Validation CHECK constraints replace YAML enum and pattern validation. ### Enum-Style Constraints ```sql 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')) ); ``` ### Range Constraints ```sql CREATE TABLE resources ( id TEXT PRIMARY KEY, rating INTEGER CHECK (rating >= 1 AND rating <= 5) ); ``` ### Pattern Constraints (Regex) SQLite doesn't have native regex in CHECK constraints, but you can validate formats: ```sql CREATE TABLE resources ( id TEXT PRIMARY KEY, url TEXT CHECK (url LIKE 'http%') ); ``` For complex validation, use application-level checks before INSERT. ## Aggregations SQLite supports GROUP BY, COUNT, SUM, AVG, MIN, MAX, and more. Memhub cannot do this. ### Basic Aggregations ```sql -- 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; ``` ### Advanced Aggregations ```sql -- 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; ``` ## Building a SQLite-DB Skill Specialized sqlite-db skills follow a consistent structure. They teach agents how to manage a specific domain using SQLite. ### Directory Layout ``` 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 ``` ### What a SQLite Skill Should Define 1. **Tables** — DDL for all domain entities with constraints, indexes, and foreign keys 2. **Indexes** — B-tree indexes for common queries, FTS indexes for search 3. **FTS** — Virtual tables and sync triggers for full-text search 4. **Views** — Saved queries as first-class database objects 5. **Triggers** — Auto-timestamps, FTS sync, validation 6. **Links vocabulary** — Named relationship types (same as memhub: `linksTo`, `derivedFrom`, `partOf`, etc.) 7. **Database path** — Where the `.db` file lives (e.g., `.sqlite/notes.db`) 8. **ID prefixes** — Conventions for generating human-readable IDs 9. **Workflows** — Step-by-step SQL examples for common tasks ### Design Principles **Schemas encode domain knowledge.** The DDL is the documentation. Use meaningful column names, CHECK constraints, foreign keys, and indexes. The schema should tell you what's important. **Target database explicitly.** Every `sqlite3` command should specify the full path to the database. Stateless invocation only. **Views are your menu.** Create views for common queries. Views compose — they can reference other views, use joins, and include aggregations. They're more powerful than memhub's saved queries. **Use both relationship styles.** Structural foreign keys for ownership (clipping→resource), flexible links table for ad-hoc graph relationships (note→note). **Include setup scripts.** Provide an idempotent `setup.sh` that creates the database, runs the DDL, and initializes views. Users should be able to initialize a working database with one command. **Show real workflows.** Don't just list SQL patterns — show the full flow of creating records, linking them, querying, updating, and analyzing over time. **Be honest about tradeoffs.** SQL is verbose. String escaping is hazardous. But the power (JOINs, FTS, aggregations, window functions) makes it worthwhile for certain domains.