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. 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. Inspect the exact row first, avoid broad writes, and verify the managed API can read the repaired value. ## Commands List tables: ```bash python scripts/mp-db.py tables ``` Show table schema: ```bash python scripts/mp-db.py schema downloadhistory ``` Run a read query: ```bash python scripts/mp-db.py query "SELECT COUNT(*) AS total FROM downloadhistory" ``` Read SQL from stdin or a file: ```bash python scripts/mp-db.py query --file /path/to/query.sql ``` Run a write statement: ```bash 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` ### `site` - Purpose: Stores private-tracker URLs, RSS, credentials, rate limits, proxy state, and downloader binding. - Useful queries: Inspecting enablement, domain, rate limits, or downloader binding with minimal credential exposure. - Write boundary: Contains cookies, API keys, and tokens; manage it through the site API. - Columns: `id`, `name`, `domain`, `url`, `pri`, `rss`, `cookie`, `ua`, `apikey`, `token`, `proxy`, `filter`, `render`, `public`, `note`, `limit_interval`, `limit_count`, `limit_seconds`, `timeout`, `is_active`, `lst_mod_date`, `downloader` ### `siteicon` - Purpose: Caches site names, domains, icon URLs, and Base64 icon content. - Useful queries: Diagnosing missing icons, incorrect domain mapping, or cache generation. - Write boundary: Rebuildable cache owned by site-icon synchronization; direct writes are not recommended. - Columns: `id`, `name`, `domain`, `url`, `base64` ### `sitestatistic` - Purpose: Aggregates site request successes, failures, durations, latest state, and diagnostic notes. - Useful queries: Comparing site availability, failure rate, and the most recent access state. - Write boundary: Accumulated by site access statistics; never edit counters to conceal runtime behavior. - Columns: `id`, `domain`, `success`, `fail`, `seconds`, `lst_state`, `lst_mod_date`, `note` ### `siteuserdata` - Purpose: Stores tracker account level, traffic, ratio, seeding, and unread-message data. - Useful queries: Inspecting account state, traffic trends, seeding volume, and the latest collection error. - Write boundary: A site-scraping projection refreshed by synchronization; do not edit it directly.