paperless-postgres-mcp-query

Files (1)

• SKILL.md

SKILL.md (first lines)

# Paperless-ngx PostgreSQL MCP queries

Query the Paperless-ngx metadata database via the `postgres_query` MCP tool
for read-only lookups: document IDs, titles, checksums, tags, correspondents,
document types, custom fields, and ingest task history.

## When to use

- Resolve a document ID referenced in a Beancount file to its title/checksum.
- Find documents matching a title, content, correspondent, tag, or date range.
- Audit ingest failures or recent activity from `documents_paperlesstask`.
- Inspect the schema before running ad-hoc queries.

## Prerequisites

The `postgres` MCP server must be registered in the active opencode config
(see `opencode.jsonc` → `mcp.postgres`). The server connects to the
Paperless database using the connection string configured there — no
credentials are passed to the agent. Verify the server is available before
running queries; if `postgres_query` is not present, the MCP server is not
loaded in this session and this skill cannot be used.

Do NOT shell out to `psql`. The connection string is not guaranteed to be
resolvable from the agent's environment, and the MCP server is the only
blessed path. Run all queries through `postgres_query`.

## Schema overview

72 tables; the handful that matter for document lookups. Row counts below
are **actual `COUNT(*)`** (verified), not `pg_stat_user_tables.n_live_tup`
(see Gotchas — that view is stale and wildly undercounts several tables).

| Table | Purpose | Actual rows |
|-------|---------|-------------|
| `documents_document` | Core document metadata (title, checksum, dates, FKs) | 2,559 (2,531 active, 28 soft-deleted) |
| `documents_tag` | Tag definitions (hierarchical via `tn_parent_id`, `tn_*`) | 587 |
| `documents_document_tags` | M2M join: document_id ↔ tag_id | 11,753 |
| `documents_correspondent` | Sender/source (e.g. "CloverLeaf PM", "IRS (Muni)") | 250 |
| `documents_documenttype` | Type classification (Statement, Receipt, Tax Form, …) | 32 |
| `documents_customfield` | Custom field definitions (monetary, string, date, …) | 13 |
| `documents_customfieldinstance` | Per-document custom field values | 1,366 |
| `documents_note` | Notes attached to documents | 0 |
| `documents_storagepath` | Custom storage path configs | 0 |
| `documents_paperlesstask` | Celery ingest/OCR task log (success/failure) | 7,749 |
| `documents_workflowrun` | Workflow execution log (type 2 = trigger, 3 = action) | 8,597 |
| `paperless_mail_processedmail` | Inbound mail rule processing log | 275 |
| `auditlog_logentry` | Django audit log (edits, tag changes, …) | 43,188 |

Key `documents_document` columns: `id`, `title`, `content` (extracted text,
searchable), `checksum` (MD5), `archive_checksum`, `created` (date), `added`
(timestamp), `modified`, `correspondent_id`, `document_type_id`,
`storage_path_id`, `owner_id`, `mime_type`, `page_count`,
`original_filename`, `archive_filename`, `archive_serial_number`,
`deleted_at` (soft-delete), `restored_at`, `transaction_id` (currently
unused — 0 docs populate it), `storage_type` (all `"unencrypted"`),
`filename`.

`documents_paperlesstask` columns: `id`, `task_id` (Celery UUID),
`acknowledged`, `date_created`, `date_started`, `date_done`, `result` (text,
error message on failure), `status` (`SUCCESS`/`FAILURE`/`STARTED`),
`task_name`, `task_file_name` (original upload filename — useful for
matching a failed ingest to a file), `type` (`auto_task`/`scheduled_task`/
`manual_task`), `owner_id`.

`auditlog_logentry` columns: `id`, `object_pk`/`object_id` (the affected
doc's id), `object_repr`, `action` (smallint: 0=create, 1=update, 2=delete),
`changes` (jsonb), `changes_text`, `timestamp`, `actor_id`, `actor_email`,
`content_type_id`, `remote_addr`, `additional_data`, `serialized_data`,
`cid`. Join `object_id` to `documents_document.id` to find a doc's edit
history.

`documents_workflowrun` columns: `id`, `type` (2=trigger, 3=action),
`run_at`, `document_id`, `workflow_id`, plus soft-delete fields.

`paperless_mail_processedmail` columns: `id`, `folder`, `uid`, `subject`,
`received`, `processed`, `status` (`SUCCESS`/`FAILED`/
`PROCESSED_WO_CONSUMPTION`), `error`, `owner_id`, `rule_id`.

Tags are flat in practice (`tn_level = 1`, `tn_parent_id` is NULL for all
top-level tags). Do not assume a deep tree; query `tn_parent_id IS NULL`
for the root set. `documents_tag.is_inbox_tag` marks the special "Inbox"
tag (id 2) and the legacy "Renamed (Workflow)" tag (id 14). Note: the
"Inbox" tag currently has **0 documents** attached — the inbox is clear.

All 13 custom fields (with data type and populated instance count):
`Previous Filename` (string, 487), `For Tax Period Ending Date` (date,
328), `Reference ID` (string, 271), `Cash Paid (USD)` (monetary, 192),
`Created Date` (date, 53), `Work Order ID` (string, 11), `Tax Form`
(string, 10), `Tax Filing Status` (string, 5), `Tax Package Purpose`
(string, 4), `Tax Package Type` (string, 4), `Cash Paid (EUR)` (monetary,
1), `Status` (string, 0), `Duplicate(s) Document ID` (integer, 0). Only
`monetary`, `string`, `date`, and `integer` data types are used —
`value_bool`/`value_float`/`value_url`/`value_select`/`value_document_ids`/
`value_long_text` are never populated but are kept in the Q11 `COALESCE`
defensively.

## Workflow

1. Confirm the MCP server is loaded: attempt a trivial
   `SELECT 1` via `postgres_query`. If the tool is missing, stop and tell
   the user the `postgres` MCP server is not registered in this opencode
   config.
2. Start with the **Schema overview** query (Q1) and **Row counts** (Q2)
   to orient — never assume table names or row scales from memory.
3. Pick the query below closest to the intent. All are parameterized with
   `:placeholder` tokens — substitute before running.
4. For lookups by title/content, prefer `ILIKE '%...%'` (Paperless titles
   vary in capitalization) and `LIMIT` results.
5. Cross-check document IDs against `deleted_at IS NULL` if a soft-delete
   filter matters; otherwise include all rows.

## Top 12 queries

### Q1 — Schema overview: list all tables with row counts

Purpose: orient before any ad-hoc query; confirm a table exists.

**Warning:** `n_live_tup` is stale and unreliable in this database (e.g. it
reports `documents_correspondent` = 0 when the real count is 250, and

From jason-riddle/agent-skills, MIT.

More from jason-riddle/agent-skills