PostgreSQL
Trigger agents when database rows are inserted or updated. React to data changes in real-time using PostgreSQL LISTEN/NOTIFY.
Inbound (Database Events)
Listens for PostgreSQL NOTIFY events on a configured channel. When a notification is received, the agent is triggered with the payload data.
How It Works
The connector maintains a persistent connection to your PostgreSQL database and subscribes to a NOTIFY channel. When your database sends a notification (via triggers or application code), the connector receives it and dispatches agent runs with the notification payload.
Configuration
- Host: PostgreSQL server hostname or IP address
- Port: Server port (default: 5432)
- Database: Database name to connect to
- Username: Database user for authentication
- Password: Database password
- Channel: NOTIFY channel name to listen on (e.g.,
new_customers) - SSL Mode: Connection security (disable, prefer, require)
- Parse JSON Payload: Automatically parse notification payload as JSON
Required Permissions
The database user needs CONNECT permission on the database. No special permissions are required to receive NOTIFY events.
Event Payload
Your agent receives the notification payload with PostgreSQL metadata:
{
"customer_id": "12345",
"email": "customer@example.com",
"company_name": "Acme Corp",
"created_at": "2024-01-15T10:30:00.000Z",
"_postgres": {
"channel": "new_customers",
"pid": 12345,
"timestamp": "2024-01-15T10:30:00.123456"
}
}The _postgres metadata includes the channel name, backend process ID, and timestamp.
Database Setup
Option 1: Trigger-based Notifications
Create a trigger function that sends notifications when rows are inserted:
-- Create a trigger function that sends notifications
CREATE OR REPLACE FUNCTION notify_new_customer()
RETURNS TRIGGER AS $$
BEGIN
-- Send JSON payload with row data
PERFORM pg_notify(
'new_customers',
json_build_object(
'customer_id', NEW.id,
'email', NEW.email,
'company_name', NEW.company_name,
'created_at', NEW.created_at
)::text
);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Attach trigger to table
CREATE TRIGGER customer_insert_trigger
AFTER INSERT ON customers
FOR EACH ROW
EXECUTE FUNCTION notify_new_customer();Option 2: Manual Notifications
Send notifications from your application code:
-- Send a notification manually from your application
SELECT pg_notify('new_customers', '{"customer_id": "12345", "action": "created"}');
-- Or using NOTIFY directly
NOTIFY new_customers, '{"customer_id": "12345", "action": "created"}';Handling Updates and Deletes
Extend your trigger to handle multiple operations:
-- Trigger for both INSERT and UPDATE
CREATE OR REPLACE FUNCTION notify_customer_changes()
RETURNS TRIGGER AS $$
BEGIN
PERFORM pg_notify(
'customer_changes',
json_build_object(
'operation', TG_OP,
'customer_id', NEW.id,
'old_email', CASE WHEN TG_OP = 'UPDATE' THEN OLD.email ELSE NULL END,
'new_email', NEW.email,
'updated_at', NOW()
)::text
);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER customer_change_trigger
AFTER INSERT OR UPDATE ON customers
FOR EACH ROW
EXECUTE FUNCTION notify_customer_changes();Best Practices
- Keep payloads small: NOTIFY payloads are limited to 8000 bytes. Send IDs and let the agent fetch full data.
- Use specific channels: Create separate channels for different event types (e.g.,
orders_created,users_updated). - JSON payloads: Enable "Parse JSON Payload" for structured data. The agent receives parsed objects directly.
- Connection pooling: The connector maintains a single persistent connection per channel. This is efficient for most use cases.
Connection Considerations
Each PostgreSQL connector maintains a persistent connection. If your database has connection limits, consider this when creating multiple connectors.