/database-operation

name: database-operation
version: 18
description: >-
  Use this skill when you need to inspect, query, maintain, or carefully modify
  the MoviePilot database. This skill uses the bundled scripts/mp-db.py helper,
  which reads MoviePilot local settings itself and never requires database
  passwords or full PostgreSQL DSNs in the agent prompt. Applicable scenarios
  include data statistics, counts, aggregations, inspecting or fixing records,
  cleanup requests, and questions like "how many downloads", "show site stats",
  "delete old records", or "why is this subscription stuck".
allowed-tools: execute_command

Database Operation

> All script paths are relative to this skill file.

Use `scripts/mp-db.py` for all database access. Do not extract database passwords, API tokens, or full PostgreSQL DSNs from the prompt. The script reads MoviePilot local settings and connects to SQLite or PostgreSQL internally.

Scope And Boundaries

This skill is the direct SQL boundary. It is implemented as a Python script and is appropriate when the agent must inspect records, run data statistics, repair stuck state, or perform an explicitly requested database update.

Run the bundled copy from the MoviePilot program directory with its project runtime, for example:

cd <MOVIEPILOT_ROOT>
python skills/database-operation/scripts/mp-db.py tables

The Agent command environment routes `python` to the project-specific `moviepilot-python` entry when available and falls back to the project or Docker `VENV_PATH` Python otherwise.

The runtime sets `MOVIEPILOT_ROOT` for copied skills. If you run a copied script directly from `<CONFIG_PATH>/agent/skills/`, set that variable to the program directory first; otherwise the helper reports how to relocate it. Use the project runtime instead of a system `python3`.

Prefer safer product surfaces first:

| Request | Preferred skill | |---|---| | Normal MoviePilot product operation | `moviepilot-api` structured operations | | Operation outside the structured API catalog | A more specific Skill or explicit unsupported result | | Slash commands or plugin/system command dispatch | `command-dispatch` | | Manual file organization | `organize-files` | | Retry failed transfer history records | `transfer-failed-retry` |

Use this skill as the final fallback for data access or mutation. It may run `SELECT`, `INSERT`, `UPDATE`, `DELETE`, and schema-changing statements through the bundled script, but broad or destructive writes still require explicit user authorization.

System settings have two managed sources and should not normally be edited here:

• Runtime `Settings` variables are queried and updated by `moviepilot-api`

operations `config.system.get` / `config.system.update`; updates perform type conversion and persist to `app.env`.

• `SystemConfigKey` values are stored in the database `systemconfig` table, but

the same API operations must be preferred because they enforce registered keys, plugin mutation admission, value normalization, secret redaction, and configuration-change events.

Use direct SQL against `systemconfig` only for an explicitly authorized repair when the managed API cannot complete the operation. The script blocks any statement that references `systemconfig` until you pass `--show-secrets`, so inspect the exact row first, avoid broad writes, and verify the managed API can read the repaired value.

Commands

List tables:

python scripts/mp-db.py tables

Show table schema:

python scripts/mp-db.py schema downloadhistory

Run a read query:

python scripts/mp-db.py query "SELECT COUNT(*) AS total FROM downloadhistory"

Read SQL from stdin or a file:

python scripts/mp-db.py query --file /path/to/query.sql

Run a write statement:

python scripts/mp-db.py write "UPDATE subscribe SET state = 'S' WHERE id = 123"

`query --write` is also supported for compatibility, but prefer the `write` subcommand for `INSERT`, `UPDATE`, `DELETE`, and schema changes.

SQLite also checks the actual table accesses while executing each statement, including single-quoted table names and reads through views. The temporary authorizer is removed when that statement finishes. PostgreSQL retains the identifier check described below.

Statements that reference `systemconfig` are rejected until you add `--show-secrets`, in read and write mode alike:

python scripts/mp-db.py query "SELECT key, value FROM systemconfig WHERE key = 'TMDB_API_KEY'" --show-secrets

The gate is per table, not per column, so even a count or key listing over `systemconfig` needs the flag. Read normal settings through `moviepilot-api` `config.system.get` instead, which redacts secrets for you, and pass the flag only for an explicitly authorized repair.

Workflow

1. Prefer existing MoviePilot tools or APIs for normal product workflows. 2. Use this skill for direct database inspection only when no existing tool covers the request. 3. For unknown schema, run `tables` first, then `schema <table>`. 4. For `SELECT` queries, execute directly with a narrow projection and an explicit `LIMIT` when reading rows. 5. For `INSERT`, `UPDATE`, `DELETE`, `DROP`, `ALTER`, `TRUNCATE`, `CREATE`, or `REPLACE`, use `write` and report the affected row count.

Built-in Safety

• `query` defaults to read-only mode.
• `write` executes data updates and schema-changing statements directly.
• `query --write` remains available as a compatibility alias for write statements.
• Multiple SQL statements in one invocation are rejected.
• Keywords and semicolons inside SQL literals, quoted identifiers, and comments

do not change a query into a write or a multi-statement request. Unclosed lexical structures remain invalid.

• Plain `SELECT` queries get a default `LIMIT 100` if no limit is present.
• `systemconfig` aggregates every instance-level API key, cookie and password,

so it stays unreadable and unwritable until you pass `--show-secrets