PostgreSQL

Datafly Signal writes first-party events into a PostgreSQL table for reliable relational storage with advanced JSONB querying and the full Postgres extension ecosystem.

Why route PostgreSQL through Datafly Signal

• The data lands in infrastructure you own. Signal writes to your PostgreSQL directly from your own deployment. Datafly operates no intermediary and never holds a copy of your event data.
• Client IP and user agent are recorded as event context. Both come from the original visitor request rather than from your server, so geography and device analysis done in the warehouse reflects the actual visitor.
• Presets for Retail and General. The mapping starts from a working configuration for your vertical instead of a blank field map.
• One consent decision governs every destination. PostgreSQL is gated by the same consent state as the rest of your stack, so a withdrawal applies everywhere at once.

Prerequisites

Before configuring PostgreSQL in Signal, you need a PostgreSQL server (self-hosted or managed), a database with a schema and table, and a user with INSERT privileges.

CREATE DATABASE datafly_events;
 
\c datafly_events
 
CREATE SCHEMA IF NOT EXISTS datafly;
 
CREATE TABLE datafly.events (
  event_id VARCHAR(64) NOT NULL PRIMARY KEY,
  type VARCHAR(20),
  event VARCHAR(256),
  anonymous_id VARCHAR(64),
  user_id VARCHAR(256),
  timestamp TIMESTAMPTZ,
  received_at TIMESTAMPTZ,
  sent_at TIMESTAMPTZ,
  context JSONB,
  properties JSONB,
  traits JSONB,
  source_id VARCHAR(64),
  integration_id VARCHAR(64)
);
 
CREATE INDEX idx_events_timestamp ON datafly.events (timestamp DESC);
CREATE INDEX idx_events_type_event ON datafly.events (type, event);

CREATE USER datafly_signal WITH PASSWORD 'your_secure_password';
GRANT USAGE ON SCHEMA datafly TO datafly_signal;
GRANT INSERT ON TABLE datafly.events TO datafly_signal;

Configuration

Signal Setup

Quick Setup

1. Navigate to Integrations in the sidebar.
2. Open the Integration Library tab.
3. Find PostgreSQL or filter by Database.
4. Click Install, select a variant if available, and fill in the required fields.
5. Click Install Integration to create the integration with a ready-to-use default blueprint.

API Setup

curl -X POST http://localhost:8084/v1/admin/integration-catalog/postgresql/install \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "PostgreSQL",
    "variant": "default",
    "config": {
      "host": "db.example.com",
      "port": "5432",
      "database": "datafly_events",
      "schema_name": "datafly",
      "table_name": "events",
      "username": "datafly_signal",
      "password": "your_secure_password",
      "ssl_mode": "require"
    },
    "delivery_mode": "server_side"
  }'

Schema

Signal writes the standard event envelope. The recommended table definition:

Query JSONB with ->, ->>, @>, and ? operators.

Consent

PostgreSQL is a first-party destination under your control. The default blueprint forwards all events. Apply consent filtering via pipeline transforms or downstream views/policies over context->>'consent' if needed.

Testing

1. Enable the integration in Signal and trigger a test event on your website.
2. Connect to the PostgreSQL server and query the target table:

SELECT * FROM datafly.events ORDER BY timestamp DESC LIMIT 10;

3. Verify that event rows are appearing with correct data.
4. Test JSONB querying:

SELECT event_id, properties->>'order_id' AS order_id, (properties->>'total')::numeric AS total
FROM datafly.events
WHERE event = 'Order Completed'
ORDER BY timestamp DESC
LIMIT 5;

5. In Signal, check the Live Events view to confirm delivery status shows as successful.

Troubleshooting

See also

• Amazon RDS
• Google Cloud SQL
• MySQL
• Amazon Redshift