using-db-operations

DB Operations

When to Use

Use when memory-keeper or task-orchestrator MCP tools don't expose needed data directly. When this plugin is installed, use the MCP tools — no Bash or script invocation needed.

• Full session UUIDs (MCP tools truncate to 8-char)
• Entry-to-session attribution when a key exists in multiple sessions
• Cross-session duplicate detection during context audits
• Session-level aggregates (entry counts, sizes)
• Ad-hoc read queries against any SQLite database file
• Exporting/importing MCP server databases (WAL-safe backup/restore)
• Packing/unpacking all MCP databases in one tarball operation

Safety Boundary

READ-ONLY. All writes (save, delete, update) must go through MCP tools. Direct SQL writes bypass triggers, sequence numbering, and change tracking. Use commit: true in query_database only when you have a specific reason to write directly and understand the consequences.

Common Database Paths

These are path hints for the db_abs parameter. ~ expansion is supported.

Preset	Path
memory-keeper	~/mcp-data/memory-keeper/context.db
task-orchestrator	~/mcp-data/task-orchestrator/tasks.db

MCP Tools

Six tools are loaded automatically when the plugin is installed.

query_database

Batch SQL queries against a SQLite file. Each request in the requests array runs independently and returns results under its label key.

Request object fields:

Field	Required	Description
query	yes	SQL string
label	yes	Key used for this result in the response
params	no	Array of strings for ? placeholders
processor	no	Python lambda or def string — must return a value, not print
output	no	"json" — returns ["<json-string>"]; access via json.loads(result["label"][0])
commit	no	true to enable write mode

Processor contract: The tool result contains whatever the processor returns. print() inside a processor goes to server stdout and is invisible to the caller. Always write returning processors.

Default processor behavior: With no processor, multi-column rows are flattened: SELECT a, b FROM t returns [a1, b1, a2, b2, ...]. Use a returning processor or output: "json" for any multi-column query where you need row structure.

Basic query:

query_database(
  db_abs="~/mcp-data/memory-keeper/context.db",
  requests=[
    { "query": "SELECT COUNT(*) FROM context_items", "label": "total" }
  ]
)

Parameterized query:

query_database(
  db_abs="~/mcp-data/memory-keeper/context.db",
  requests=[
    {
      "query": "SELECT key FROM context_items WHERE channel = ?",
      "label": "channel_keys",
      "params": ["claude-custom-skills"]
    }
  ]
)

Returning processor — scalar extraction:

query_database(
  db_abs="~/mcp-data/memory-keeper/context.db",
  requests=[
    {
      "query": "SELECT channel, COUNT(*) FROM context_items GROUP BY channel ORDER BY 2 DESC",
      "label": "channels",
      "processor": "lambda rows: [f'{r[1]:>4}  {r[0]}' for r in rows]"
    }
  ]
)

Multi-column query with JSON output:

query_database(
  db_abs="~/mcp-data/memory-keeper/context.db",
  requests=[
    {
      "query": "SELECT key, channel, value FROM context_items WHERE channel = ?",
      "label": "export",
      "params": ["working"],
      "output": "json"
    }
  ]
)
# Access result: json.loads(result["export"][0])

---

copy_database

WAL-safe copy of a single SQLite database using sqlite3.Connection.backup(). Use when you need to copy one specific database rather than packing all of ~/mcp-data/.

copy_database(
  src_abs="~/mcp-data/memory-keeper/context.db",
  dest_abs="/some/backup/context.db"
)

---

pack_local

Pack one or more ~/mcp-data/ subdirectories into a tarball stored in a local directory. WAL-safe for .db files.

pack_local(
  path_abs="/abs/path/to/data/data",
  include_dir=["~/mcp-data/memory-keeper", "~/mcp-data/task-orchestrator"]
)

---

pack_repo

Pack + git commit/push the tarball directly to a repository. Equivalent to pack_local followed by a git add/commit/push.

pack_repo(
  repo_path_abs="/abs/path/to/data/data",
  include_dir=["~/mcp-data/memory-keeper", "~/mcp-data/task-orchestrator"]
)

---

unpack_from_tarball

Restore databases from a locally stored tarball. Extracts to ~/ in-place.

unpack_from_tarball(
  path_abs="/abs/path/to/data/data"
)

---

unpack_from_git_ref

Restore from a specific git ref (commit hash, branch, tag) in a repo. Use for rollback to a named snapshot.

unpack_from_git_ref(
  repo_path_abs="/abs/path/to/data/data",
  git_ref="abc1234"
)

Scripts

The CLI scripts below are for use in terminal sessions or when MCP tools are unavailable. Prefer MCP tools when the plugin is installed.

scripts/sqlq.py

Accepts flat CLI flags to specify a database path and a single SQL query. Executes via sqlite3 and prints results.

Flags:

• --path / -p (required) - Absolute path to the target .db file
• --query / -q (required) - SQL query string
• --label / -l (required) - Human-readable description logged with results
• --processor (optional) - Python callable as a string; auto-detected as lambda or def; CLI only: processors here may use print() since output goes to your terminal
• --params (optional) - One or more ? placeholder values: --params value1 value2
• --commit (optional flag) - Enable write mode; omit for read-only queries
• --output json (optional) - Emit results as a JSON array to stdout

Row access: Rows are sqlite3.Row objects — access by name (r["key"]) or index (r[0]).

Stdlib injection: json, re, and datetime are available in processor code without importing.

One invocation = one query. For multiple queries, run separate invocations.

scripts/dbcp.py

WAL-safe copy of a single SQLite database file using sqlite3.Connection.backup().

Flags:

• --src SRC (required) - Source database path
• --dst DST (required) - Destination database path

python3 dev-marketplace/db-operations/scripts/dbcp.py \
  --src ~/mcp-data/memory-keeper/context.db \
  --dst data/data/memory-keeper/context.db

scripts/pack.py

Packs ~/mcp-data/ (or specified dirs) into a single tarball, or restores from one. WAL-safe for .db files. Supports rollback from git history by commit hash or hostname.

Configuration (set one, not both):

Variable	Description
DBO_EXPORT_REPO	Export path + git auto add/commit/push after --pack
DBO_EXPORT_PATH	Export path only, no git

Flags:

• --pack - backup dirs to tarball
• --unpack - restore from tarball (extracts to ~/)
• --repo REPO - override DBO_EXPORT_REPO
• --path PATH - override DBO_EXPORT_PATH
• --include DIR [DIR...] - dirs to pack (default: ~/mcp-data/); must be under ~/
• --from-commit HASH - unpack from a specific git commit (requires --repo/DBO_EXPORT_REPO)
• --from-host [HOST] - unpack from most recent commit for HOST; omit HOST to use current hostname

# Pack + auto-commit/push to git repo
DBO_EXPORT_REPO=data/data python3 dev-marketplace/db-operations/scripts/pack.py --pack

# Unpack (restore to ~/)
DBO_EXPORT_PATH=data/data python3 dev-marketplace/db-operations/scripts/pack.py --unpack

# Rollback to last pack from another host
DBO_EXPORT_REPO=data/data python3 dev-marketplace/db-operations/scripts/pack.py \
  --unpack --from-host ZR-XPS14

Plugin Inventory Queries

state.db was retired 2026-05-19; plugin/skill/agent metadata now lives on the filesystem (see scripts/schema.py:scan_plugin_dirs for the canonical reader). For ad-hoc plugin inventory inspection, walk the filesystem directly:

# List every plugin name + version (versions come from src/.claude-plugin/marketplace.json)
python3 -c "from scripts.schema import scan_plugin_dirs; import json, pathlib; \
mp = json.loads(pathlib.Path('src/.claude-plugin/marketplace.json').read_text()); \
versions = {e['name']: e.get('version') for e in mp['plugins']}; \
[print(f'{n}: {versions.get(n, \"\")}') for n, _ in scan_plugin_dirs(pathlib.Path('src'))]"

# Status from per-plugin forge.json
python3 -c "from scripts.schema import scan_plugin_dirs, read_plugin_config; \
import pathlib; \
[print(f'{n}: {read_plugin_config(d).get(\"status\", \"released\")}') \
for n, d in scan_plugin_dirs(pathlib.Path('src'))]"

The MCP tools (query_database, etc.) still apply to context.db (memory-keeper) and tasks.db (task-orchestrator) — see the patterns above.