postgres-activity-mcp

Give your coding agent eyes on your local database. A Model Context Protocol (MCP) server that records every INSERT, UPDATE, and DELETE in your local Postgres and lets an agent ask "what just changed?" — without writing a single bespoke query.

Why?

You ask your coding agent:

"I just clicked the Save button — did it actually create the order?"

Today the agent has to read your schema, guess which tables matter, and write a query. With postgres-activity-mcp, it just asks:

"What changed in the database in the last minute?"

…and gets a structured answer with the table, the operation, the changed columns, and the row's primary key — straight from a local audit log.

How it works

┌────────────┐      ┌──────────────────┐      ┌──────────────────┐
│  Your app  │ ──▶  │  Your tables     │ ──▶  │  db_activity_log │
└────────────┘      │  (with triggers) │      │  (audit table)   │
                    └──────────────────┘      └──────────────────┘
                                                       ▲
                                                       │ MCP tools
                                                       │
                                              ┌─────────────────┐
                                              │  Coding agent   │
                                              └─────────────────┘

1. You run setup_activity_logging once.
2. The server creates an audit table and attaches an AFTER INSERT/UPDATE/DELETE trigger to every table in your tracked schemas.
3. Every row change in your app gets logged with timestamp, user, transaction id, primary key, changed columns, and full old/new row JSON.
4. Your agent reads from that log via four small MCP tools.

Quick start

1. Add it to your MCP client

Point the server at your local Postgres with DATABASE_URL:

{
  "mcpServers": {
    "postgres-activity": {
      "command": "npx",
      "args": ["-y", "postgres-activity-mcp"],
      "env": {
        "DATABASE_URL": "postgres://postgres:postgres@localhost:5432/my_database"
      }
    }
  }
}

Heads up: the user in DATABASE_URL needs permission to create a table, create a function, and create triggers on the tables you want to track. A local development superuser is fine.

2. Have your agent run the setup tool once

Ask the agent something like "set up activity logging in my database" and it will call setup_activity_logging. After that, every row change is captured — no further wiring required.

Prefer running from source?

For local development or unreleased changes:

git clone [email protected]:ianjaku/postgres-activity-mcp.git
cd postgres-activity-mcp
npm install
npm run build

Then point your MCP client at the local build:

{
  "mcpServers": {
    "postgres-activity": {
      "command": "node",
      "args": ["/absolute/path/to/postgres-activity-mcp/dist/index.js"],
      "env": {
        "DATABASE_URL": "postgres://postgres:postgres@localhost:5432/my_database"
      }
    }
  }
}

The tools

| Tool | What it does | When to use | | --- | --- | --- | | setup_activity_logging | Creates the audit table, the trigger function, and triggers on every table in the tracked schemas. | Once per database. Safe to re-run. | | refresh_activity_triggers | Same as setup — installs triggers on any newly-added tables. | After a migration adds new tables. | | get_recent_db_changes | Lists recent row changes with filters for schema, table, operation, time, and row payloads. | "Did the click actually write anything?" | | summarize_recent_db_changes | Groups recent activity by schema/table/operation in a configurable lookback window. | "Give me a quick picture of what just happened." | | get_activity_log_status | Reports which tables are tracked and how many changes have been recorded. | Sanity-check that logging is on. |

A typical agent flow

1. setup_activity_logging
2. (you do something in the app)
3. summarize_recent_db_changes  →  "1 INSERT on public.orders, 1 UPDATE on public.inventory"
4. get_recent_db_changes(table=orders, includeRows=true)  →  full before/after row

What gets logged

Every change row in public.db_activity_log includes:

• changed_at, transaction_id, backend_pid
• session_user_name, database_name, application_name, client_addr
• schema_name, table_name, operation
• row_pk — JSON of the row's primary key
• changed_columns — for UPDATEs, the list of columns that actually changed
• old_row, new_row — full JSON snapshots (returned only when you ask for them)

This is row-level data only. DDL is not logged.

Testing

The repo ships with a Docker-backed integration test that proves the whole pipeline end-to-end:

npm run test:integration

It spins up a throwaway postgres:16-alpine container on a random local port, talks to the MCP server over stdio, performs an INSERT / UPDATE / DELETE, and asserts that each tool reports the right thing. The container is torn down automatically.

Configuration

| Env var | Required | Description | | --- | --- | --- | | DATABASE_URL | yes | Postgres connection string the server connects with. | | PGAPPNAME | no | Application name reported to Postgres. Defaults to postgres-activity-mcp. | | POSTGRES_TEST_IMAGE | no | Image used by the integration test. Defaults to postgres:16-alpine. |

You can also override the audit table location per call by passing logSchema and logTable to any tool.

Publishing

npx -y postgres-activity-mcp works once the package is published to npm. The package is configured with:

• a bin entry that exposes the postgres-activity-mcp command
• files: ["dist", "README.md"] so the published package contains only the runnable build and docs
• prepack to build dist/index.js before npm pack or npm publish

To publish a new version:

npm version patch
npm publish --access public

You can preview the npm tarball first with:

npm pack --dry-run

FAQ

Will this slow down my database? There is a small per-row trigger cost. This server is meant for local development databases where that overhead is invisible. Don't point it at production.

What about new tables I add later? Run refresh_activity_triggers (or just call setup_activity_logging again). It's idempotent.

Can I exclude noisy tables? Yes — pass excludeTables: ["audit_events", "public.sessions"] to setup or refresh.

Where does the audit data live? By default in public.db_activity_log. It's a normal Postgres table — you can query, truncate, or drop it like any other.

License

MIT