> ## Documentation Index
> Fetch the complete documentation index at: https://help.statisfy.com/llms.txt
> Use this file to discover all available pages before exploring further.

# PostgreSQL Integration

> Connect a PostgreSQL database to Statisfy to sync product usage, custom fields, and custom objects with self-serve query configuration.

### Connect PostgreSQL with Statisfy

The PostgreSQL integration lets you import data from any PostgreSQL-compatible database into Statisfy — your application database, an analytics replica, or any Postgres-wire-compatible engine. Author SQL queries in the Statisfy settings UI, preview results, and schedule them.

PostgreSQL uses the same self-serve configuration model as the other warehouse integrations. You can find the shared concepts — query types, account resolvers, transformers, schedules, configuration history — in [Integration Concepts](/integrations/self_serve_configuration). This page focuses on what's specific to PostgreSQL.

## Video Tutorial

<video controls className="w-full aspect-video rounded-xl" src="https://publicassets.us.prod.clueso.io/2f8a9fc9-c323-4fe7-909f-ec00073876d9/694297af-71d5-45f3-8913-b2f92b203c07/published_v8/video.mp4" />

**Prerequisites:**

* **Admin access** to Statisfy
* A PostgreSQL instance reachable from Statisfy's egress IPs (see [Network Access](#network-access))
* A database user with `SELECT` access on the schemas/tables you want to sync

### What You Can Import

| Data Type          | Description                           | Use Case                                     |
| ------------------ | ------------------------------------- | -------------------------------------------- |
| **Product Usage**  | Time-series usage metrics per account | MAU, API calls, feature usage                |
| **Custom Fields**  | Account- or person-level attributes   | Health scores, segments, industry, plan tier |
| **Custom Objects** | Domain-specific records               | Subscriptions, deployments, contracts        |

### Set Up the Database User

Run as a Postgres superuser (or a user with `CREATE ROLE`):

```sql theme={null}
-- 1. Create a dedicated read-only role
CREATE ROLE statisfy_reader WITH LOGIN PASSWORD '<set-and-store-securely>';

-- 2. Allow it to connect to the database
GRANT CONNECT ON DATABASE analytics TO statisfy_reader;

-- 3. Grant read access on the schemas Statisfy should see
GRANT USAGE ON SCHEMA public TO statisfy_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO statisfy_reader;

-- Make future tables readable too
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO statisfy_reader;
```

Replace `analytics` and `public` with the database and schema(s) you want Statisfy to read.

<Tip>
  If you can, point Statisfy at a **read replica** rather than your primary database. Sync queries can be large; a replica isolates that load from your live application traffic.
</Tip>

### Steps to Connect

1. **Log in to Statisfy.**

2. Navigate to **Integrations → Admin Apps → PostgreSQL → Connect**.

3. Enter your connection details:
   * **Host** — DNS name or IP of the database
   * **Port** — Default `5432`
   * **Database** — Database name
   * **Username** — `statisfy_reader` (or whichever read-only user you created)
   * **Password** — The user's password
   * **SSL Mode** — `require` is recommended; use `verify-full` if you've installed your CA cert

4. Click **Test Connection** to confirm Statisfy can reach the host and authenticate.

5. Once connected, PostgreSQL will appear as **Connected** on the Integrations page.

### Configure Queries

Open **Integrations → PostgreSQL → Settings** to:

* **Browse schemas and tables** the user can see, with column types
* **Author queries** using Postgres SQL with autocomplete and a preview pane
* **Map results** to Statisfy accounts (via Statisfy account ID, CRM ID, organization ID, email domain, account name, or any custom field) and to fields, metrics, or custom objects
* **Apply column transformers** (`strip_prefix`, `regex_extract`, `lookup`, etc.) to reshape values before they're written
* **Set the cadence** — daily, weekly, monthly, or a sub-day interval (HOURLY through TWELVE\_HOURS)
* **Run a one-off sync** on demand

Each saved query is one of three types — `product_usage`, `custom_field`, or `custom_object`. See [Query types](/integrations/self_serve_configuration#query-types) and [Account resolvers](/integrations/self_serve_configuration#account-resolvers) for the shared model.

### PostgreSQL-Specific Notes

* **Date placeholders.** Use `{START_DATE}` / `{END_DATE_SQL}` in `WHERE` clauses to keep queries incremental. Postgres's `DATE_TRUNC()`, `INTERVAL`, and `::date` casts compose naturally with these.
* **Schema search path.** Statisfy does not rely on `search_path` — queries should fully qualify tables (`schema.table`) when they live outside `public`.
* **Read isolation.** For long-running scans, consider `SET TRANSACTION ISOLATION LEVEL REPEATABLE READ` in the query if you need a consistent snapshot — but in most cases the default isolation is fine.

### Account Matching

| Resolver                | Description                              |
| ----------------------- | ---------------------------------------- |
| **Statisfy Account ID** | Native Statisfy account identifier       |
| **CRM Account ID**      | Salesforce or HubSpot account identifier |
| **Organization ID**     | External org identifier                  |
| **Email Domain**        | Company website domain                   |
| **Account Name**        | Company name (fuzzy matching)            |
| **Custom Field**        | Any custom field defined on accounts     |

<Warning>
  Rows that don't resolve to an existing Statisfy account are skipped. For CRM ID resolvers, make sure your CRM integration has synced the matching accounts first.
</Warning>

For `custom_field` queries that target people instead of accounts, configure a **person resolver** (email column, optionally name) — Statisfy will upsert the person record by email.

### Sync Behavior

**Scheduled Jobs:**

* Each saved query runs on its own cadence.
* The first run after connecting is a **bootstrap** covering the last 30 days; subsequent runs cover only the active window for the cadence.
* Self-serve configuration takes precedence over any built-in fallback set up by support.

**Error Handling:**

* Per-query errors are logged but do not block other queries from running.
* Invalid rows within a query are skipped and reported in the job audit log.

### Network Access

If your Postgres host has IP allowlisting or is in a private network, allow Statisfy's egress IPs:

```text theme={null}
35.203.185.27
35.197.52.231
```

If your database isn't reachable from the public internet, reach out to [support@statisfy.com](mailto:support@statisfy.com) to discuss VPN / SSH-tunnel options.

### Security

* Credentials are stored encrypted in Google Cloud Secret Manager.
* All connections use TLS — Statisfy refuses to connect over plaintext when `ssl_mode` is set to `require` or higher.
* Queries authored in the Settings page are read-only — Statisfy validates each query and rejects DDL/DML before save.
* Access is scoped to the database user you provide — grant `SELECT` only on the schemas you want Statisfy to read.

### Troubleshooting

<Accordion title="Connection test fails">
  **Check network access:**

  * Verify the host is reachable from the Statisfy egress IPs above
  * Confirm the firewall / security group allows inbound traffic on the Postgres port (default `5432`)
  * Confirm the database accepts SSL connections (check `pg_hba.conf`)

  **Check credentials:**

  * Verify host, port, database, username, and password
  * Confirm the user has `CONNECT` privilege on the database
</Accordion>

<Accordion title="Schema or tables not visible in the browser">
  * Confirm the database user has `USAGE` on the schema and `SELECT` on the tables
  * System schemas (`pg_catalog`, `information_schema`, `pg_toast`) are intentionally hidden
  * New grants may require closing and reopening the schema picker
</Accordion>

<Accordion title="Query validation errors at save time">
  * Statisfy validates queries against the Postgres dialect — vendor-specific functions from other warehouses will be flagged
  * DDL (`CREATE`, `DROP`, `ALTER`) and DML (`INSERT`, `UPDATE`, `DELETE`) are rejected — only read-only queries are allowed
  * Check the validation error for the offending function or syntax
</Accordion>

<Accordion title="Records not matching to accounts">
  * Verify the resolver column contains values that exist in Statisfy
  * For CRM ID resolvers, confirm your CRM integration has synced the matching accounts
  * Try a different resolver (switch from account name to email domain) if matching is unreliable
  * Add a `regex_extract` or `strip_prefix` transformer if the column contains the right ID with extra characters
</Accordion>

<Accordion title="Sync is slow or impacting production">
  * Point Statisfy at a **read replica** instead of the primary
  * Lower the cadence to `DAILY` so each run scans a smaller window
  * Use `{START_DATE}` / `{END_DATE_SQL}` to keep queries incremental
  * Add indexes on the columns you filter on
</Accordion>

### Need Help?

For initial setup, query design, or troubleshooting, contact [support@statisfy.com](mailto:support@statisfy.com).