Skip to content

sqlite-queries.md

Latest commit

 

History

History
348 lines (271 loc) · 6.55 KB

sqlite-queries.md

File metadata and controls

348 lines (271 loc) · 6.55 KB
Raw

SQLite query cookbook

The application database is a regular SQLite file with two virtual-table extensions:

  • reels_fts uses SQLite FTS5;
  • reels_vec uses sqlite-vec.

Open the database read-only and load sqlite-vec before SQLite reads the schema:

SQLITE_VEC_PATH="$(
  uv run python -c 'import sqlite_vec; print(sqlite_vec.loadable_path())'
)"

sqlite3 \
  -readonly \
  -cmd ".load $SQLITE_VEC_PATH" \
  -header \
  -column \
  data/reels/state.sqlite3

The .load step is required even when a query does not use vectors because SQLite must recognize the vec0 module while inspecting the complete schema. The application performs this step automatically. In the command above, -cmd ".load $SQLITE_VEC_PATH" is expanded by Bash before the interactive SQLite shell starts, so the extension is already loaded. Do not repeat .load $SQLITE_VEC_PATH at the sqlite> prompt: SQLite dot commands do not expand shell variables.

Confirm the loaded extension from the sqlite> prompt:

SELECT vec_version();

Shell basics

Show all tables, including FTS5 and sqlite-vec internal shadow tables:

.tables

Show only application-facing tables:

SELECT name, type
FROM sqlite_schema
WHERE name IN (
    'reels',
    'embeddings',
    'reels_fts',
    'reels_vec',
    'schema_migrations'
)
ORDER BY name;

Inspect schemas and indexes:

.schema reels
.schema embeddings
.schema reels_fts
.schema reels_vec
.indexes reels

Make wide output easier to read:

.mode box
.headers on

Exit:

.quit

Migration and database health

Show applied migrations:

SELECT version, applied_at
FROM schema_migrations
ORDER BY version;

Run safe integrity checks:

PRAGMA integrity_check;
PRAGMA foreign_key_check;

Show the SQLite and sqlite-vec versions:

SELECT sqlite_version() AS sqlite_version, vec_version() AS vec_version;

Reel lifecycle

Count all Reel records:

SELECT COUNT(*) AS total
FROM reels;

Count processing and embedding statuses:

SELECT processing_status, COUNT(*) AS count
FROM reels
GROUP BY processing_status
ORDER BY processing_status;

SELECT embedding_status, COUNT(*) AS count
FROM reels
GROUP BY embedding_status
ORDER BY embedding_status;

Show the combined lifecycle matrix:

SELECT
    processing_status,
    embedding_status,
    COUNT(*) AS count
FROM reels
GROUP BY processing_status, embedding_status
ORDER BY processing_status, embedding_status;

Show URLs currently queued for embedding:

SELECT url, post_type, created_at
FROM reels
WHERE processing_status = 'described'
  AND embedding_status IN ('pending', 'error')
ORDER BY created_at, url
LIMIT 100;

Show recent embedding errors:

SELECT url, embedding_error
FROM reels
WHERE embedding_status = 'error'
ORDER BY created_at DESC
LIMIT 50;

Embedding metadata

Count persisted metadata, vectors, and FTS documents:

SELECT
    (SELECT COUNT(*) FROM embeddings) AS metadata_rows,
    (SELECT COUNT(*) FROM reels_vec) AS vector_rows,
    (SELECT COUNT(*) FROM reels_fts) AS fts_rows;

Show recently updated embedding records:

SELECT
    url,
    category,
    mood,
    model,
    dimension,
    updated_at
FROM embeddings
ORDER BY updated_at DESC
LIMIT 50;

Inspect one URL:

SELECT
    r.url,
    r.post_type,
    r.processing_status,
    r.embedding_status,
    e.summary,
    e.category,
    e.mood,
    e.topics,
    e.model,
    e.dimension
FROM reels AS r
LEFT JOIN embeddings AS e USING (url)
WHERE r.url = 'https://www.instagram.com/reel/POST_ID/';

Compare the number of indexed rows with lifecycle status:

SELECT
    (SELECT COUNT(*) FROM reels WHERE embedding_status = 'embedded')
        AS marked_embedded,
    (SELECT COUNT(*) FROM embeddings) AS metadata_rows,
    (SELECT COUNT(*) FROM reels_vec) AS vector_rows,
    (SELECT COUNT(*) FROM reels_fts) AS fts_rows;

Unique values and distributions

List unique post types:

SELECT post_type, COUNT(*) AS count
FROM reels
GROUP BY post_type
ORDER BY count DESC, post_type;

List unique categories and moods:

SELECT category, COUNT(*) AS count
FROM embeddings
GROUP BY category
ORDER BY count DESC, category;

SELECT mood, COUNT(*) AS count
FROM embeddings
GROUP BY mood
ORDER BY count DESC, mood;

List models and dimensions present in the database:

SELECT model, dimension, COUNT(*) AS count
FROM embeddings
GROUP BY model, dimension
ORDER BY count DESC;

topics, actions, and objects are JSON arrays. Expand them with json_each() to find unique values:

SELECT topic.value AS topic, COUNT(*) AS count
FROM embeddings AS e, json_each(e.topics) AS topic
GROUP BY topic.value
ORDER BY count DESC, topic
LIMIT 100;

SELECT action.value AS action, COUNT(*) AS count
FROM embeddings AS e, json_each(e.actions) AS action
GROUP BY action.value
ORDER BY count DESC, action
LIMIT 100;

SELECT object.value AS object, COUNT(*) AS count
FROM embeddings AS e, json_each(e.objects) AS object
GROUP BY object.value
ORDER BY count DESC, object
LIMIT 100;

FTS5 inspection and search

Inspect lexical fields:

SELECT url, transcript, caption, hashtags, on_screen_text
FROM reels_fts
LIMIT 20;

Run the same weighted BM25 branch used by the application:

SELECT
    url,
    bm25(reels_fts, 0.0, 1.0, 0.6, 0.4, 0.8) AS score
FROM reels_fts
WHERE reels_fts MATCH '"coffee" OR "espresso"'
ORDER BY score
LIMIT 20;

Smaller BM25 values represent better matches. User input should not be inserted into SQL directly; the application tokenizes and quotes it before MATCH.

Find rows containing a normalized hashtag:

SELECT url, hashtags
FROM reels_fts
WHERE reels_fts MATCH 'hashtags:"coffee"'
LIMIT 50;

Vector inspection

Check vector dimensions without printing the full vectors:

SELECT
    url,
    vec_length(embedding) AS dimension
FROM reels_vec
LIMIT 20;

KNN search requires a 1,024-dimensional query vector. It is easier and safer to generate that vector through the configured embedding API:

uv run reelscope search "coffee tutorial" --vector-only

Use reelscope search for normal vector or hybrid searches instead of manually pasting a large vector literal into SQLite.

Export read-only query results

From the interactive shell:

.headers on
.mode csv
.once /tmp/reels-categories.csv
SELECT category, COUNT(*) AS count
FROM embeddings
GROUP BY category
ORDER BY count DESC;

The examples in this document are read-only. Do not manually update virtual tables or any reels_fts_* / reels_vec_* shadow tables.