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.
• Personal data is hashed on the way in. Email, phone (shaped to E.164 first), first, last and full name and postcode are normalised (trimmed and lower-cased) and hashed with SHA-256 inside your own deployment, so the table holds email_sha256, phone_sha256 and the other digests rather than the raw values. Storing a raw value is a deliberate edit to the installed blueprint.
• Only what the blueprint maps is stored. The properties Signal writes are exactly the fields the blueprint maps. Identify traits and client properties the blueprint does not map are never written, so a stray email in an event property cannot land in PostgreSQL in plain text.
• Presets for Retail, Insurance, Travel, Media & Streaming, B2B / SaaS, Automotive and General. Each vertical preset names one field per property in the matching event spec, with canonical names (transaction_id, value, items), legacy aliases (order_id, revenue, products) read automatically, numbers typed as numbers and currency codes upper-cased.
• 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.
• 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 (
  message_id      TEXT PRIMARY KEY,
  event_type      TEXT NOT NULL,
  event_name      TEXT,
  anonymous_id    TEXT,
  user_id         TEXT,
  ip_address      TEXT,
  user_agent      TEXT,
  page_url        TEXT,
  page_path       TEXT,
  page_referrer   TEXT,
  org_id          TEXT,
  pipeline_id     TEXT,
  event_timestamp TIMESTAMPTZ,
  received_at     TIMESTAMPTZ,
  properties_json JSONB,
  context_json    JSONB
);
 
CREATE INDEX idx_events_ts   ON datafly.events (event_timestamp DESC);
CREATE INDEX idx_events_name ON datafly.events (event_name, event_timestamp DESC);

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;
GRANT SELECT (message_id) 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 inserts one row per event into exactly these sixteen columns, so the table must have them all (extra columns of your own are fine if they are nullable or have defaults):

Signal inserts with ON CONFLICT (message_id) DO NOTHING, so message_id must be the primary key or carry a unique index; without one every insert fails. Use JSONB for the two JSON columns (a TEXT column also works). Column types are yours to choose as long as the values fit: a value too long for a VARCHAR(n) fails the insert.

What the properties hold

The blueprint decides what properties_json contains, and only mapped fields are written (identify traits and unmapped client properties never are):

• The event’s own fields from the preset sit at the top level, for example transaction_id, value, currency, items, insurance_type or policy_term_months.
• The preset’s global block sits under @root: copies of the envelope (event_name, event_type, user_id, anonymous_id, event_id, message_id, event_timestamp, page_url, page_referrer, ip_address, user_agent, locale, timezone, source_id), utm_source, utm_medium, utm_campaign, the consent flags consent_analytics and consent_marketing, and the hashed identity email_sha256, phone_sha256, first_name_sha256, last_name_sha256, name_sha256.
• identify adds name_sha256 and postal_code_sha256 at the top level.
• An event the preset does not list stores its envelope and the @root block only.

For example, the purchases with their order ID, value and hashed email:

SELECT event_timestamp,
       properties_json->>'transaction_id'                   AS transaction_id,
       (properties_json->>'value')::numeric                 AS value,
       properties_json->>'currency'                         AS currency,
       properties_json->'@root'->>'email_sha256'            AS email_sha256
FROM datafly.events
WHERE event_name = 'purchase'
ORDER BY event_timestamp DESC
LIMIT 10;

Signal does not write mapped fields to their own columns; create views over properties_json for the fields you query often. What else a row carries outside the blueprint (the whole event context) is under review in #1414.

Stored event context

Alongside the fields your blueprint maps (where email, phone, names and address are stored as normalised SHA-256 by default), each row carries the event’s collected context exactly as Signal received it. The blueprint does not filter or hash the context: this is deliberate (decision 2026-09-23), so the context is complete for attribution, fraud and debugging queries, and it is listed here so you can decide what to keep.

They are written to the ip_address, user_agent, page_url, page_path and page_referrer columns and, in full, to the context_json column, beside anonymous_id and user_id. Click and collected IDs outside the context are stored only where the blueprint maps them.

The IP address, device and advertising IDs, and cookie and click identifiers are personal data under UK and EU GDPR. To keep any of them out of storage, strip, mask or hash the field for every integration with the organisation data layer, apply URL redaction to page URLs, or remove it in the installed blueprint; and set the storage’s retention to match your privacy notice. See Personal data by integration.

Consent

PostgreSQL is a first-party destination under your control, gated by the same consent state as every other destination. Each stored event also records the visitor’s consent at collection time: the preset writes consent_analytics and consent_marketing under @root in the properties (properties_json), and the full consent object is in the context. Use them in views or queries if your governance needs a per-row consent filter.

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 message_id, event_type, event_name, event_timestamp
FROM datafly.events
ORDER BY event_timestamp DESC
LIMIT 10;

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

SELECT event_timestamp,
       properties_json->>'transaction_id'                   AS transaction_id,
       (properties_json->>'value')::numeric                 AS value,
       properties_json->>'currency'                         AS currency,
       properties_json->'@root'->>'email_sha256'            AS email_sha256
FROM datafly.events
WHERE event_name = 'purchase'
ORDER BY event_timestamp DESC
LIMIT 10;

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