Reveric SQLite Skill

Directly query and modify Reveric's SQLite databases for debugging, data inspection, and manual fixes.

Database Locations

Select Target First

macOS Debug and Release/Profile use different bundle identifiers, so they read different Application Support directories. Do not assume one path fits all.

Before querying or writing, identify which app build the user means:

# Debug build
REVERIC_APP_SUPPORT="$HOME/Library/Application Support/ai.reveric.debug"
REVERIC_MAIN_DB="$REVERIC_APP_SUPPORT/reveric.sqlite"

# Release/Profile build
REVERIC_APP_SUPPORT="$HOME/Library/Application Support/ai.reveric"
REVERIC_MAIN_DB="$REVERIC_APP_SUPPORT/reveric.sqlite"

If the user says "debug", use ai.reveric.debug. If the user says "release", "profile", or "installed app", use ai.reveric. If unclear, inspect both with read-only counts before writing.

Main Database Schema

novels

Unique constraint: (title, deleted_at)

app_settings

ai_providers

agents

Unique constraint: (scope, novel_id, slug, deleted_at)

ai_chat_sessions

app_states

memories

todos

agent_skills

Unique constraint: (scope, novel_id, name, deleted_at)

tips

tasks

Novel Content Database Schema

chapter_nodes

Canonical writable table for chapter tree nodes. Insert, update, move, and delete chapters here, not in the chapters view.

Indexes: (parent_id, sibling_order), (type, sibling_order)

chapters (VIEW)

Computed read view over chapter_nodes with additional computed columns. Do not write to this view directly.

Useful chapter checks:

SELECT id, type, parent_id, sibling_order, title, chapter_num, volume_chapter_num
FROM chapters
ORDER BY COALESCE(parent_sibling_order, sibling_order),
         CASE WHEN parent_id IS NULL THEN 0 ELSE sibling_order END;

SELECT id, type, parent_id, sibling_order, title, deleted_at
FROM chapter_nodes
WHERE deleted_at = 0;

character_profiles

items

Unique constraint: (type, title, deleted_at)

foreshadowings

Unique constraint: (title, deleted_at)

ai_chat_messages

ai_embedding_documents

working_changes

change_commits

change_commit_items

novel_states

agent_rules

documents

Unique constraint: (type, parent_id, title, deleted_at)

entity_relations

Relation types by entity pair:

locations

novel_maps

map_locations

Unique constraint: (map_id, location_id, deleted_at)

map_features

Unique constraint: (map_id, location_id, deleted_at)

map_layers

Unique constraint: (map_id, name, deleted_at)

location_connections

Unique constraint: (map_id, source_map_location_id, target_map_location_id, type, deleted_at)

outlines

outline_beats

Common Query Patterns

Get all novels

sqlite3 -header -column "$REVERIC_MAIN_DB" "SELECT id, title, status, word_count, datetime(created_at/1000, 'unixepoch') as created FROM novels WHERE deleted_at = 0 ORDER BY updated_at DESC;"

Get novel content database path

sqlite3 "$REVERIC_MAIN_DB" "SELECT id, title, content_database_path FROM novels WHERE deleted_at = 0;"

Get all chapters for a novel (using chapters VIEW)

sqlite3 -header -column "$REVERIC_APP_SUPPORT/novel_workspaces/novel_xxx.sqlite" "SELECT id, type, chapter_num, title, word_count, stat, datetime(updated_at/1000, 'unixepoch') as updated FROM chapters WHERE type IN ('chapter', 'extra') ORDER BY chapter_num;"

Get all characters for a novel

sqlite3 -header -column "$REVERIC_APP_SUPPORT/novel_workspaces/novel_xxx.sqlite" "SELECT id, name, role, gender, summary FROM character_profiles WHERE deleted_at = 0 ORDER BY sort_order;"

Get AI chat sessions for a novel

sqlite3 -header -column "$REVERIC_MAIN_DB" "SELECT id, title, provider, model, mode, agent_slug FROM ai_chat_sessions WHERE novel_id = 'novel_xxx' AND deleted_at = 0 ORDER BY updated_at DESC LIMIT 10;"

Get chat messages for a session

sqlite3 -header -column "$REVERIC_APP_SUPPORT/novel_workspaces/novel_xxx.sqlite" "SELECT role, substr(content, 1, 100) as preview, input_tokens, output_tokens, datetime(created_at/1000, 'unixepoch') FROM ai_chat_messages WHERE session_id = 'SESSION_ID' ORDER BY sequence;"

Get all AI providers

sqlite3 -header -column "$REVERIC_MAIN_DB" "SELECT id, name, type, enabled, temperature FROM ai_providers WHERE deleted_at = 0;"

Get app settings

sqlite3 -header -column "$REVERIC_MAIN_DB" "SELECT key, value FROM app_settings;"

Update a novel's title

sqlite3 "$REVERIC_MAIN_DB" "UPDATE novels SET title = 'New Title', updated_at = strftime('%s', 'now') * 1000 WHERE id = 'novel_xxx';"

Count words in all chapters

sqlite3 "$REVERIC_APP_SUPPORT/novel_workspaces/novel_xxx.sqlite" "SELECT type, SUM(word_count) as total_words FROM chapter_nodes WHERE deleted_at = 0 GROUP BY type;"

Get volumes structure

sqlite3 -header -column "$REVERIC_APP_SUPPORT/novel_workspaces/novel_xxx.sqlite" "SELECT id, sibling_order, title FROM chapter_nodes WHERE type = 'volume' AND deleted_at = 0 ORDER BY sibling_order;"

Get chapters within a volume

sqlite3 -header -column "$REVERIC_APP_SUPPORT/novel_workspaces/novel_xxx.sqlite" "SELECT id, type, sibling_order as chapter_num, title, word_count, stat FROM chapter_nodes WHERE parent_id = 'VOLUME_ID' AND deleted_at = 0 ORDER BY sibling_order;"

Get all agents

sqlite3 -header -column "$REVERIC_MAIN_DB" "SELECT id, slug, name, category, enabled, mode FROM agents WHERE deleted_at = 0 ORDER BY sort_order;"

Helper Scripts

Find all novel databases

ls "$REVERIC_APP_SUPPORT/novel_workspaces/"*.sqlite 2>/dev/null

Get novel info with content DB path resolved

sqlite3 -header -column "$REVERIC_MAIN_DB" "SELECT id, title, content_database_path, word_count, status FROM novels WHERE deleted_at = 0 ORDER BY updated_at DESC;"

List all tables with row counts

echo "=== Main DB ===" && sqlite3 "$REVERIC_MAIN_DB" "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;" && echo && echo "=== Novel Content DB ===" && for db in "$REVERIC_APP_SUPPORT/novel_workspaces/"*.sqlite; do echo "--- $(basename "$db") ---"; sqlite3 "$db" "SELECT name FROM sqlite_master WHERE type IN ('table','view') ORDER BY name;"; done

Important Notes

1. Select the target build first - Debug and Release/Profile use different bundle IDs and therefore different DB directories. Set REVERIC_APP_SUPPORT and REVERIC_MAIN_DB before querying or writing.

2. Two database types - Remember there's a main app database and per-novel content databases. Check novels.content_database_path to find the correct content DB for a novel.

3. Timestamps are milliseconds - Timestamp fields are stored as INTEGER milliseconds (Unix epoch * 1000). Use datetime(timestamp/1000, 'unixepoch') to convert to readable format.

4. Soft delete - Most tables use deleted_at (INTEGER DEFAULT 0) for soft deletes. Always filter with WHERE deleted_at = 0 unless you need to see deleted records.

5. Novel IDs are TEXT - Novel IDs are strings like novel_xxx, not integers.

6. Use transactions for writes - When making multiple related updates, wrap them in a transaction:

   BEGIN TRANSACTION;
   -- multiple UPDATE/INSERT statements
   COMMIT;
   

7. Backup before major changes - For destructive operations, backup first:

   cp "$REVERIC_MAIN_DB" ~/reveric.sqlite.backup
   

8. JSON columns - Many columns store JSON data (without _json suffix now). Use json_extract() if SQLite is compiled with JSON support, or parse in Dart/helper scripts. Key JSON columns: tags, ai_settings, models, image_models, embedding_models, callable_agent_slugs, allowed_tool_names, ignored_skill_names, blocks, metadata, vector, properties, settings, style, geometry, label, meta, data, extra_properties, photos.

9. chapters is a VIEW - The old chapters table was replaced by a chapter_nodes tree table + a chapters VIEW. When reading chapter data, query the VIEW by default. The VIEW filters deleted_at = 0 automatically and provides computed chapter_num, volume_num, volume_chapter_num.

10. agents table replaces global_sub_agents/novel_sub_agents - Agents now have scope (global/novel) and novel_id to distinguish global vs per-novel agents.

11. Dev-only: migration version merging - During pre-release, multiple migration versions for the same feature may be merged into one. Never drop tables to force re-migration — that destroys data. Use ALTER TABLE to add missing columns instead.

Dev-Only: Migration Merge Without Data Loss

When a migration method is edited under the same schemaVersion (e.g., migrateToV8 gains new columns but version stays 8), existing DBs at that version won't re-run, so they miss the new columns. Do not drop tables to force a re-run — that destroys all existing data.

Proceed with routine development maintenance

When implementing an authorized schema change, updating the affected local development databases is part of that task. Identify the Debug target, back it up, add the required columns/indexes, and verify the result directly. Do not ask separately for approval of the database list, maintenance SQL, backup, or execution, and do not leave this work pending at the end of the implementation.

If the old Debug instance holds the databases, normally quit that instance, verify its handles are closed, complete maintenance, and launch the new Debug build. This necessary restart is included in development maintenance; do not make it another approval gate. If the instance has already exited, proceed. Preserve unsaved work: an actual save/discard prompt must not be bypassed by force-quitting.

Record the backup location, affected databases, schema and data-preservation checks, and restart result in the task handoff. This guidance covers local development maintenance within the implementation scope; it does not authorize Release/production changes, deleting business data, or an unplanned table rebuild. Resolve only concrete scope or data-loss decisions with the user; continue independent work while doing so.

Safe fix: ALTER TABLE (preserves all data)

Add missing columns directly. All new columns should be nullable (or have DEFAULT) so existing rows are unaffected.

Add columns to app DB:

DB="$HOME/Library/Application Support/ai.reveric.debug/reveric.sqlite"
sqlite3 "$DB" "ALTER TABLE breakdown_runs ADD COLUMN metrics_started_at INTEGER NULL;"
sqlite3 "$DB" "ALTER TABLE breakdown_runs ADD COLUMN metrics_ended_at INTEGER NULL;"
sqlite3 "$DB" "ALTER TABLE breakdown_runs ADD COLUMN usage TEXT NULL;"

Add columns to content DBs:

for db in "$HOME/Library/Application Support/ai.reveric.debug/novel_workspaces/"*.sqlite; do
  # Check column existence first (skip if already present)
  col=$(sqlite3 "$db" "PRAGMA table_info(breakdown_chapters);" | grep -c 'tension_reason' || true)
  if [ "$col" -eq 0 ]; then
    sqlite3 "$db" "ALTER TABLE breakdown_chapters ADD COLUMN tension_reason TEXT NULL;"
  fi
done

When ADD COLUMN isn't enough (rename + recreate + copy)

SQLite cannot ALTER COLUMN to change nullability, type, or defaults. When you need such changes, use rename + recreate + copy — this preserves all data without destructive drops.

# Example: make chapter_ids nullable (from NOT NULL to nullable)
TAB=breakdown_run_items
sqlite3 "$DB" "ALTER TABLE $TAB RENAME TO ${TAB}_old;"
sqlite3 "$DB" "CREATE TABLE $TAB (id TEXT NOT NULL PRIMARY KEY, run_id TEXT NOT NULL, phase TEXT NOT NULL DEFAULT 'map', status TEXT NOT NULL DEFAULT 'pending', chapter_ids TEXT NULL, error_message TEXT NULL, attempt INTEGER NOT NULL DEFAULT 0, started_at INTEGER NULL, completed_at INTEGER NULL, created_at INTEGER NOT NULL, updated_at INTEGER NOT NULL, deleted_at INTEGER NOT NULL DEFAULT 0);"
sqlite3 "$DB" "INSERT INTO $TAB (id, run_id, phase, status, chapter_ids, error_message, attempt, started_at, completed_at, created_at, updated_at, deleted_at) SELECT id, run_id, phase, status, chapter_ids, error_message, attempt, started_at, completed_at, created_at, updated_at, deleted_at FROM ${TAB}_old;"
sqlite3 "$DB" "DROP TABLE ${TAB}_old;"
# Recreate indexes
sqlite3 "$DB" "CREATE INDEX IF NOT EXISTS breakdown_run_items_by_run_id_status ON breakdown_run_items (run_id, status);"
sqlite3 "$DB" "CREATE INDEX IF NOT EXISTS breakdown_run_items_by_run_id_phase ON breakdown_run_items (run_id, phase);"

For a table rebuild not already explicitly authorized, show the proposed SQL and obtain confirmation before running it. This requirement applies to rebuilding tables, not routine development column/index maintenance above.

If you need to reset user_version after this (to trigger migration re-run for additional changes), check the current version first:

sqlite3 "$DB" "PRAGMA user_version;"
sqlite3 "$DB" "PRAGMA user_version = <previous_version>;"

Workflow

When the user asks to query or modify the database:

1. Identify the target - Determine which database (main or novel content) and which table(s) are involved.

2. Build the query - Construct the appropriate SQL query. Use -header and -column flags for readable output:

   sqlite3 -header -column ~/path/to/db.sqlite "SELECT ...;"
   

3. Execute and verify - Run the query and check the results.

4. For writes - Apply existing task authorization. Perform routine development maintenance above directly; do not ask the user to approve it again. Ask only when the intended write scope is unclear or the operation requires authorization not already provided.

5. Verify the result - After writing, run a SELECT to confirm the change took effect.

Examples

User: "Show me all novels"

sqlite3 -header -column "$REVERIC_MAIN_DB" "SELECT id, title, status, word_count, datetime(created_at/1000, 'unixepoch') as created FROM novels WHERE deleted_at = 0 ORDER BY updated_at DESC;"

User: "What chapters are in novel_xxx?"

CONTENT_DB=$(sqlite3 "$REVERIC_MAIN_DB" "SELECT content_database_path FROM novels WHERE id = 'novel_xxx' AND deleted_at = 0;")
sqlite3 -header -column "$CONTENT_DB" "SELECT chapter_num, type, title, word_count, stat FROM chapters WHERE type IN ('chapter', 'extra') ORDER BY chapter_num;"

User: "Update the API key for OpenAI provider"

sqlite3 "$REVERIC_MAIN_DB" "UPDATE ai_providers SET api_key = 'new-key-here', updated_at = strftime('%s', 'now') * 1000 WHERE id = 'openai';"