Memex

-- Base data table
CREATE TABLE chunks (
    id INTEGER PRIMARY KEY,
    source_type TEXT,  -- 'file', 'issue', 'pr', 'commit', 'wiki'
    path TEXT,         -- file path, issue number, etc.
    title TEXT,        -- filename, issue title, commit subject
    body TEXT,         -- content
    metadata TEXT      -- JSON blob
);

-- FTS5 trigram index (substring/fuzzy matching)
CREATE VIRTUAL TABLE search_trigram USING fts5(
    source_type, path, title, body, metadata,
    content=chunks, content_rowid=id,
    tokenize='trigram'
);

-- FTS5 porter index (stemmed whole-word search)
CREATE VIRTUAL TABLE search_porter USING fts5(
    source_type, path, title, body, metadata,
    content=chunks, content_rowid=id,
    tokenize='porter unicode61'
);

The database is served with Accept-Ranges: bytes, enabling sparse access. Clients can query without downloading the entire file.

# Verify range request support
curl -I https://zackees.github.io/self/index.db | grep -i accept-ranges
# Accept-Ranges: bytes

# Fetch just the first 1KB (SQLite header + first page)
curl -H "Range: bytes=0-1023" -o header.bin https://zackees.github.io/self/index.db

Compatible libraries

• sqlite-wasm-http — Browser WASM, HTTP range requests + FTS5 (requires bundler)
• sql.js-httpvfs — Browser WASM, HTTP range requests, FTS3 only
• Official SQLite WASM — FTS5 built-in (used on this page)
• sqlite-vfs-http — Rust crate for native HTTP range request VFS

Use rusqlite with an HTTP VFS crate for sparse remote access, or download the DB for local queries.

// Cargo.toml
// rusqlite = { version = "0.31", features = ["bundled", "fts5"] }
// sqlite-vfs-http = "0.1"

use rusqlite::Connection;
use sqlite_vfs_http::{register_http_vfs, HTTP_VFS};

register_http_vfs();
let conn = Connection::open_with_flags_and_vfs(
    "https://zackees.github.io/self/index.db",
    OpenFlags::SQLITE_OPEN_READ_ONLY | OpenFlags::SQLITE_OPEN_NO_MUTEX,
    HTTP_VFS,
)?;

// FTS5 trigram fuzzy search with BM25 ranking
let mut stmt = conn.prepare(
    "SELECT path, title, bm25(search_trigram) as rank
     FROM search_trigram WHERE search_trigram MATCH '\"claud\"'
     ORDER BY rank LIMIT 10"
)?;

This page uses sqlite-wasm-http (SQLite WASM with FTS5 + trigram) running in a background Web Worker. Only the database pages needed per query are fetched via HTTP range requests — typically <1% of the total DB size.

AI agents running in Node.js can query the index directly using better-sqlite3 (native) or sql.js (WASM). Download the DB once, then query locally.

Option 1: better-sqlite3 (native, fastest)

// npm install better-sqlite3
const Database = require('better-sqlite3');
const fs = require('fs');

// Download once
const resp = await fetch('https://zackees.github.io/self/index.db');
fs.writeFileSync('index.db', Buffer.from(await resp.arrayBuffer()));

const db = new Database('index.db', { readonly: true });

// FTS5 trigram fuzzy search
const results = db.prepare(`
  SELECT source_type, path, title, bm25(search_trigram) as rank
  FROM search_trigram WHERE search_trigram MATCH '"auth"'
  ORDER BY rank LIMIT 10
`).all();

// Porter stemmed search
const docs = db.prepare(`
  SELECT path, snippet(search_porter, 3, '**', '**', '...', 20) as snip
  FROM search_porter WHERE search_porter MATCH 'error handling'
  LIMIT 5
`).all();

Option 2: sql.js (WASM, no native deps)

// npm install sql.js
const initSqlJs = require('sql.js');

const SQL = await initSqlJs();
const resp = await fetch('https://zackees.github.io/self/index.db');
const buf = new Uint8Array(await resp.arrayBuffer());
const db = new SQL.Database(buf);

// Note: sql.js default build has FTS3 only.
// Use better-sqlite3 for FTS5 support in Node.

Option 3: Python

import sqlite3, urllib.request

urllib.request.urlretrieve(
    'https://zackees.github.io/self/index.db', 'index.db')

conn = sqlite3.connect('index.db')
rows = conn.execute("""
    SELECT source_type, path, title, bm25(search_trigram) as rank
    FROM search_trigram WHERE search_trigram MATCH '"skill"'
    ORDER BY rank LIMIT 10
""").fetchall()

Agent integration pattern

For AI agents that need RAG over this codebase:

• Download index.db once at startup (or cache it)
• Use search_trigram for fuzzy substring matches (typos, partial names)
• Use search_porter for natural language queries ("error handling", "authentication")
• Use bm25() to rank results by relevance
• Read metadata JSON for file paths, line numbers, issue labels, commit SHAs

The index is rebuilt automatically on every push to main via GitHub Actions. It can also be triggered manually via workflow_dispatch.

Sources indexed: repository files, git commits (last 200), GitHub issues + comments, pull requests + review comments, and wiki pages.

The build script is at scripts/build_index.py.