sqlite-db

What this skill does

# 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 O