Skip to main content
connic

PostgreSQL Change Notifier

Monitor PostgreSQL changes via LISTEN/NOTIFY, classify events by impact, and route notifications to the right team. Stores high-impact changes for auditing.

Talk to Sales
Install with the CLI
connic init my-project --templates=postgres-change-notifier
Difficulty
Intermediate
Source
GitHub
Publisher
By connic

What the template does

Listens to PostgreSQL NOTIFY events from database triggers and analyzes each change for significance. The change analyzer classifies events by impact level and routes notifications to the appropriate team using trigger_agent to call the notification dispatcher. High-significance events are stored in the database for audit trailing. Includes SQL examples for setting up database triggers.

Architecture
PostgreSQL NOTIFY
change-analyzer
Audit Events DB
notification-dispatcher
Outbound Webhook
Order monitoring
Alert ops and sales teams when orders are cancelled, refunded, or when large orders arrive.
Security auditing
Track permission changes, user deletions, and sensitive field modifications in real-time.
Data sync notifications
Notify downstream systems when critical data changes via outbound webhooks to Slack, Teams, or PagerDuty.

Every file lands in your project

connic init copies these files into your codebase, where you change them like the rest of your code.

agents/postgres-change-notifier/change-analyzer.yaml

LLM agent with its model, prompt and tools.

version: "1.0"

name: change-analyzer
type: llm
model: connic/gpt-5.6-luna
description: "Analyzes PostgreSQL LISTEN/NOTIFY events and decides who to alert"

system_prompt: |
  You process database change notifications from PostgreSQL LISTEN/NOTIFY.
  The payload is JSON from a database trigger, typically containing table,
  operation, old row, new row, and a _postgres metadata object.

  Use _postgres.channel to distinguish notification sources if multiple
  channels are configured.

  Your responsibilities:
  1. Determine the significance of the change:
     - High: order status changed to "cancelled" or "refunded", user deleted,
       payment amount modified, permission escalation
     - Medium: order status changed, user profile updated, new customer created
     - Low: log entries, status timestamps updated, minor field changes
  2. Decide which teams/channels should be notified:
     - "ops-team": order and payment changes
     - "security-team": permission changes, user deletions
     - "sales-team": new customers, large orders
     - "none": low-significance changes (no notification)
  3. For significant changes, dispatch ONE notification by calling trigger_agent
     with agent_name "notification-dispatcher" and a non-empty payload (a JSON
     string) shaped exactly like:
       {"channel": "<the single most relevant team>", "title": "<short title>",
        "message": "<what changed and why it matters>", "severity": "<info|warning|critical>"}
     Fill every field from the actual change. Dispatch once, not per channel.
  4. Log high-significance events using notification_tools.log_audit_event for audit trailing.

  Tool argument shapes: classify_change takes changed_fields as a list of
  column-name strings (e.g. ["role", "email"]); log_audit_event takes
  affected_ids as a list of record-id strings (e.g. ["u_42"]). Pass lists, not
  the row object.

  Ignore changes to internal system columns (updated_at, version, etc.) unless
  accompanied by meaningful data changes.

temperature: 0.2
output_schema: change-analysis
timeout: 110

tools:
  - trigger_agent
  - notification_tools.classify_change
  - notification_tools.log_audit_event
agents/postgres-change-notifier/notification-dispatcher.yaml

Tool agent that calls one function, with no model tokens.

version: "1.0"

name: notification-dispatcher
type: tool
description: "Formats and dispatches notifications to the appropriate channel"
tool_name: notification_tools.dispatch
tools/notification_tools.py

Functions the agents call as tools.

"""Notification tools for database change events."""

from datetime import datetime, timezone
from typing import Any
from connic.tools import db_insert


async def log_audit_event(
    table: str,
    operation: str,
    significance: str,
    description: str,
    affected_ids: list[str] | None = None,
    channels_notified: list[str] | None = None,
) -> dict:
    """Log a high-significance database change event for audit trailing.

    Args:
        table: Database table where the change occurred.
        operation: INSERT, UPDATE, or DELETE.
        significance: high, medium, or low.
        description: Human-readable summary of what changed and why it matters.
        affected_ids: IDs of affected records.
        channels_notified: Which notification channels were alerted.

    Returns:
        The inserted audit event document.
    """
    result = await db_insert("audit_events", {
        "table": table,
        "operation": operation,
        "significance": significance,
        "description": description,
        "affected_ids": affected_ids or [],
        "channels_notified": channels_notified or [],
        "recorded_at": datetime.now(timezone.utc).isoformat(),
    })
    return result["inserted"][0] if result.get("inserted") else result


def classify_change(
    table: str,
    operation: str,
    changed_fields: list[str] | None = None,
) -> dict[str, Any]:
    """Classify the significance of a database change.

    Provides a rule-based initial classification that the LLM can
    override with more nuanced analysis.

    Args:
        table: Database table name
        operation: INSERT, UPDATE, or DELETE
        changed_fields: List of column names that were modified

    Returns:
        Dict with significance level and suggested channels
    """
    high_sig_tables = {"orders", "payments", "users", "permissions", "subscriptions"}
    sensitive_fields = {"status", "amount", "role", "permissions", "email", "password_hash"}

    significance = "low"
    channels = []

    if table in high_sig_tables:
        significance = "medium"
        if operation == "DELETE":
            significance = "high"
            channels.append("security-team")
        elif changed_fields:
            if any(f in sensitive_fields for f in changed_fields):
                significance = "high"

    if table in ("orders", "payments"):
        channels.append("ops-team")
    if table == "users" and operation == "INSERT":
        channels.append("sales-team")

    if not channels and significance != "low":
        channels.append("ops-team")

    return {
        "significance": significance,
        "suggested_channels": channels,
        "table": table,
        "operation": operation,
    }


def dispatch(
    payload: dict[str, Any] | None = None,
    context: dict[str, Any] | None = None,
) -> dict[str, Any]:
    """Format a notification payload for webhook delivery.

    This runs as a tool agent (notification-dispatcher), so it receives the
    triggering agent's payload directly. The outbound HTTP webhook connector
    delivers the result to the configured endpoint (Slack, Teams, PagerDuty…).

    Args:
        payload: The notification to send, with keys: channel (ops-team,
            security-team, sales-team), title, message, and optional severity
            (info, warning, critical) and metadata (table, operation, ids).
        context: Auto-injected run context.

    Returns:
        Structured notification ready for outbound webhook delivery
    """
    payload = payload or {}
    return {
        "channel": payload.get("channel", "ops-team"),
        "title": payload.get("title", "Database change"),
        "message": payload.get("message", ""),
        "severity": payload.get("severity", "info"),
        "metadata": payload.get("metadata", {}),
    }
schemas/change-analysis.json

JSON schema for an agent's structured output.

{
  "type": "object",
  "description": "Database change analysis result",
  "properties": {
    "table": {
      "type": "string",
      "description": "Source database table"
    },
    "operation": {
      "type": "string",
      "enum": ["INSERT", "UPDATE", "DELETE"]
    },
    "significance": {
      "type": "string",
      "enum": ["low", "medium", "high"],
      "description": "How significant this change is"
    },
    "notify_channels": {
      "type": "array",
      "items": {"type": "string"},
      "description": "Channels to notify (ops-team, security-team, sales-team, none)"
    },
    "summary": {
      "type": "string",
      "description": "Human-readable summary of what changed and why it matters"
    },
    "action_taken": {
      "type": "string",
      "enum": ["notified", "stored", "ignored"],
      "description": "What action was taken"
    }
  },
  "required": ["table", "operation", "significance", "action_taken"]
}
tests/change-analyzer.yaml

2 test cases for connic test.

version: "1.0"

# The custom classification and audit tools are mocked so nothing is written to
# the database. trigger_agent runs for real (it cannot be mocked), so the
# dispatcher is exercised on significant changes.
defaults:
  runs: 1
  timeout_s: 115
  mocks: notification_mocks

tests:
  - name: high_significance_update
    payload: '{"table": "users", "operation": "DELETE", "old": {"id": "u_42", "email": "a@b.com", "role": "admin"}, "new": null, "_postgres": {"channel": "data_changes"}}'
    expected_result: output.significance != ""
    expected_tool_calls:
      - notification_tools.classify_change
      - notification_tools.log_audit_event

  - name: low_significance_change
    payload: '{"table": "logs", "operation": "INSERT", "old": null, "new": {"id": "log_1", "level": "info", "message": "request handled"}, "_postgres": {"channel": "data_changes"}}'
    expected_result: output.significance != ""
    expected_tool_calls:
      - notification_tools.classify_change
tests/notification-dispatcher.yaml

1 test case for connic test.

version: "1.0"

# notification-dispatcher is a tool agent that calls dispatch(channel, title,
# message, severity, metadata). The tool is pure, so it runs for real and the
# output is the webhook-ready notification.
defaults:
  runs: 1
  timeout_s: 60

tests:
  - name: formats_ops_notification
    payload: '{"channel": "ops-team", "title": "Order cancelled", "message": "Order o_99 was cancelled", "severity": "warning", "metadata": {"table": "orders", "operation": "UPDATE"}}'
    expected_result: output.channel == "ops-team"

Install it and deploy

Let your coding agent set the template up and deploy it, or install it yourself and deploy through a connected Git repository or the CLI.

Prerequisites
  • Python 3.11+
  • A connic account (create a project first)
  • Project credit for a connic/* model, or credentials for a BYOK provider
Deploy with Git
Connect the repository under Project Settings → Git & Environments, map a branch to an environment, and push.
Deploy with the CLI
Create an API key under Project Settings → API Keys & MCP Auth, run connic login in the project folder, then connic deploy. Try changes first with connic dev and connic test.
Connect and configure
Add a PostgreSQL connector with the database credentials and LISTEN channel. Set up a trigger function to send NOTIFY events (see the README for SQL examples). Use a connic/* model, or add BYOK credentials under Project Settings → LLM Provider.
  1. Install the connic plugin. Skip this if your coding agent has it already. Paste this into Claude Code, Codex or Cursor:

    prompt
    Install the connic plugin from connic-org/connic-skill and instruct the user on how to finish the MCP auth setup.
  2. Start from the template. Paste this into a new session. The agent walks you through the template, adapts it to your use case with you, and deploys it:

    prompt
    I'd like to start from the connic postgres-change-notifier template. Show me what it includes, ask me how my use case differs, and propose the changes. Once we agree, set it up in this folder with `connic init --templates=postgres-change-notifier`, adapt it, validate it, and deploy it to my connic project.
~/my-project
Install
$ pip install connic-composer-sdk
Scaffold
$ connic init my-project --templates=postgres-change-notifier
connic Init
──────────────────────────────
→ Created directory: my-project
→ Creating project structure...
✓ agents/, tools/, middleware/, schemas/
→ Fetching templates from connic-awesome-agents...
✓ Fetched
→ Merging templates...
✓ Added template: postgres-change-notifier
→ Next steps:
1. Run `connic lint` to validate your project
2. Run `connic dev` to choose a dev environment with hot reload
3. Run `connic deploy` when ready
✓ Initialized with templates: postgres-change-notifier
Deploy
$ connic deploy
→ Validating project files...
✓ Lint passed: 2 agent(s) validated (change-analyzer, notification-dispatcher)
✓ Lint passed
✓ Deployment created.