PostgreSQL
PostgreSQL NOTIFY messages from application code or database triggers start agent runs.
On this page
Setup Instructions
Ensure accessibility
The PostgreSQL database must be reachable from the internet, or use the Connic Bridge for private networks.
Send NOTIFY messages
Send notifications from application code or add a trigger function to the tables to monitor.
Create the connector
Open the agent, click Add inbound connector in the Connector Flow, then Create New Connector and select PostgreSQL.
Configure and create
Enter Host, Port, Database, Username, Password, Channel, and SSL settings. Click Create and Connic listens on the channel for notifications.
How It Works
The connector maintains a persistent connection to the PostgreSQL database and subscribes to a NOTIFY channel. When the database sends a notification (via triggers or application code), the connector receives it and dispatches agent runs with the notification payload.
- 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
The 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 application code:
-- Send a notification manually from the application
SELECT pg_notify('new_customers', '{"customer_id": "12345", "action": "created"}');
-- Or using NOTIFY directly
NOTIFY new_customers, '{"customer_id": "12345", "action": "created"}';Handling Inserts and Updates
Extend the 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 use: Each active connector maintains one persistent database connection.