MoviePilot Database Operation

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:

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. 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.

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.
• Plain SELECT queries get a default LIMIT 100 if no limit is present.
• Query results are returned exactly as stored. The agent may use sensitive values internally when needed, but must not echo secrets in the final user-facing response unless the user explicitly asks to inspect that value.

Safety Rules

1. Confirm before destructive or broad write operations when the user has not already clearly authorized the exact change.
2. Suggest a backup before destructive operations such as DELETE, DROP, or TRUNCATE.
3. Never run UPDATE or DELETE without a WHERE clause unless the user explicitly intends to affect all rows.
4. Raw secrets, cookies, passkeys, hashed passwords, OTP secrets, API keys, or tokens may appear in tool output. Use them only for the requested operation and avoid repeating them in the final response unless explicitly requested.
5. Keep output small. Summarize large results instead of dumping them.

Core Tables

tables returns the tables that exist in the current instance. The catalog below covers every MoviePilot ORM table plus Alembic metadata. Always treat the live schema <table> result as authoritative.

agentchat

• Purpose: Stores Web Agent and messaging-channel session indexes, titles, previews, and message snapshots.
• Useful queries: Tracing Agent history or context restoration by user, session, or update time.
• Write boundary: Owned by the Agent conversation service; do not rewrite message JSON, counters, or ownership.
• Columns: id, session_id, client_session_id, user_id, username, channel, source, original_chat_id, title, preview, agent_messages, display_messages, message_count, created_at, updated_at

agentinvocation

• Purpose: Durable Agent write invocation identity and last observed outcome, including confirmed asynchronous submission.
• Useful queries: Inspect an exact principal and session's running, unknown, pending, succeeded, or failed receipts; compare timestamps when diagnosing an interrupted write.
• Write boundary: Owned by the host's atomic claim and reconciliation path. Never change IDs, fingerprints, claim tokens, or statuses to bypass duplicate protection. Running and unknown records are recovery state; ordinary retention does not delete them. Pending means submission was confirmed, not that the external task finished. Raw arguments and tool output are not stored here.
• Columns: id, principal_id, session_id, invocation_id, tool_name, arguments_digest, claim_token, status, summary, created_at, updated_at

agenttask

• Purpose: Stores one-shot or recurring Agent task definitions, triggers, and the latest execution summary.
• Useful queries: Inspecting task ownership, enablement, cron/run_at settings, and the latest result.
• Write boundary: Create, update, enable, disable, or delete tasks through the Agent task API.
• Columns: id, name, content, trigger_type, cron_expression, run_at, enabled, user_id, username, session_id, channel, source, original_chat_id, last_status, last_run_at, last_result, last_run_id, run_count, created_at, updated_at

agenttaskrun

• Purpose: Stores the input snapshot, status, timestamps, and result of each Agent task execution.
• Useful queries: Auditing one run or correlating a failure with task_id, run_id, and trigger source.
• Write boundary: Execution evidence owned by the task runner; never fabricate rows or edit run status.
• Columns: id, run_id, task_id, trigger_source, name, content, trigger_type, cron_expression, run_at, user_id, username, session_id, channel, message_source, original_chat_id, status, started_at, finished_at, result

alembic_version

• Purpose: Records the Alembic migration revision currently applied to the database.
• Useful queries: Diagnosing startup migration failures or a database/code revision mismatch.
• Write boundary: Never edit it directly; advance or roll back revisions only through Alembic.
• Columns: version_num

downloadfailure

• Purpose: Stores stable fingerprints, media/torrent context, errors, and retry scheduling for failed downloads.
• Useful queries: Analyzing failure causes, retry counts, next retry time, and affected media or sites.
• Write boundary: Owned by download-failure compensation; retry or clean records through its business API.
• Columns: id, fingerprint, type, title, year, media_source, media_id, seasons, episodes, site, site_name, torrent_id, torrent_name, torrent_size, downloader, source, error_message, retry_count, first_failed_at, last_failed_at, next_retry_at

downloadfiles

• Purpose: Maps downloader task hashes to full paths, save directories, relative files, and active state.
• Useful queries: Finding task files by downloader/download_hash or diagnosing savepath associations.
• Write boundary: Maintained by download and transfer flows; do not manually change state or path mappings.
• Columns: id, downloader, download_hash, fullpath, savepath, filepath, torrentname, state

downloadhistory

• Purpose: Stores media identity, torrent, downloader, user, and recognition context for submitted downloads.
• Useful queries: Reviewing download history or tracing a media identity or hash back to its source.
• Write boundary: Written by the download use case; delete or correct records through the download-history API.
• Columns: id, path, type, title, year, media_source, media_id, music_type, seasons, episodes, image, poster, downloader, download_hash, torrent_name, torrent_description, torrent_site, userid, username, channel, date, note, media_category_id, media_category, classification_rule_id, classification_policy_revision, classification_source, episode_group, custom_words

mediaserveritem

• Purpose: Stores the local index and canonical media identity projected from media-server libraries.
• Useful queries: Checking library presence, server/library/path placement, and season information.
• Write boundary: This is a rebuildable projection; writes and cleanup belong to media-server synchronization.
• Columns: id, server, library, item_id, item_type, title, original_title, year, media_source, media_id, path, seasoninfo, note, lst_mod_date

message

• Purpose: Stores inbound and outbound messages, channels, content, attachments, users, and timestamps.
• Useful queries: Paging notification history, distinguishing direction, or tracing duplicates by source.
• Write boundary: Written by messaging and notification services; clean it through the message API or retention job.
• Columns: id, channel, source, mtype, title, text, image, link, userid, reg_time, action, note

outboxmessage

• Purpose: Stores externally visible side-effect intents committed atomically with business transactions.
• Useful queries: Diagnosing pending/processing/failed state, leases, attempts, and the last error.
• Write boundary: Owned by the Outbox Dispatcher state machine; never mark completion or delete undelivered events manually.
• Columns: id, event_key, topic, payload_version, payload, status, attempt, next_retry_at, lease_until, last_error, created_at, completed_at

passkey

• Purpose: Stores WebAuthn/PassKey credentials, public keys, signature counters, and activation state.
• Useful queries: Authorized authentication diagnostics such as ownership, activation, and last use.
• Write boundary: Security-sensitive; manage it only through the PassKey API and never disclose credential material.
• Columns: id, user_id, credential_id, public_key, sign_count, name, aaguid, created_at, last_used_at, is_active, transports

plugindata

• Purpose: Stores plugin-owned JSON values isolated by plugin_id and key.
• Useful queries: Diagnosing persistence or migration issues for one explicitly identified plugin and key.
• Write boundary: The plugin owns these values; prefer plugin capabilities or the plugin-data API.
• Columns: id, plugin_id, key, value

pluginidentity

• Purpose: Stores trusted source, payload source, version, receipt, and CAS revision for a physical plugin package.
• Useful queries: Auditing source binding, package generation, payload application, or identity conflicts.
• Write boundary: Plugin supply-chain state owned exclusively by installation and update transactions.
• Columns: id, plugin_id, normalized_plugin_id, trusted_source_type, trusted_source_key, binding_basis, payload_source_type, payload_source_key, declared_version, package_generation, declared_metadata, payload_receipt, revision, created_at, updated_at, bound_at, payload_applied_at

plugininstallation

• Purpose: Stores plugin installation phase, membership target, identity revisions, and backup state.
• Useful queries: Diagnosing interrupted installations, rollback conditions, and package or backup presence.
• Write boundary: Owned by the plugin installation state machine; never advance phase or overwrite evidence manually.
• Columns: id, transaction_id, plugin_id, phase, membership_before, membership_target, identity_before_revision, identity_target_revision, package_existed, persistent_backup_existed, created_at, updated_at, schema_version

plugininstance

• Purpose: Stores one row per shared-source plugin runtime instance, covering both clones and the host plugin itself (instance_id equals source_plugin_id, so equality identifies the host and inequality a clone), together with that instance's display overrides, its own log-level override and the moment that override expires, and the plugin's own configuration payload. A clone exists exactly while its row exists, so deleting the row uninstalls the clone and discards its configuration.
• Useful queries: Diagnosing clone naming and ownership, inspecting what a plugin or one of its clones is configured with, or finding which instance currently overrides the global log level and until when.
• Write boundary: Owned by the plugin instance, plugin configuration, and plugin log-level APIs; never edit rows directly.
• Columns: id, instance_id, source_plugin_id, plugin_name, plugin_desc, plugin_icon, is_default_target, is_enabled, log_level, log_expires_at, config_data, created_at, updated_at

searchsession

• Purpose: Stores the page cursor and pending candidates of one unfinished subscription search task, without site credentials; removed when the task ends and purged after 14 days without updates.
• Useful queries: Diagnosing a paused or resumed paged search through task_id, version, and updated_at.
• Write boundary: Written only under the queue task lease with version CAS; never edit payloads, they can skip pages or resubmit candidates.
• Columns: id, task_id, version, payload, updated_at