---
title: "Sync SaaS Data to Postgres: Upserts, pgvector, Neon & Supabase"
slug: sync-saas-data-to-postgres-upserts-pgvector-neon-supabase
date: 2026-11-01
author: Sidharth Verma
categories: [Product Updates, Guides]
excerpt: "Sync SaaS data straight into Postgres with Truto sync jobs. Idempotent upserts, parameterized SQL, and pgvector on Neon or Supabase."
tldr: "Truto Sync Jobs write SaaS data directly to Postgres, Neon, or Supabase. Master idempotent upserts, safe parameterized SQL, rate limit handling, and native pgvector embeddings without custom ETL."
canonical: https://truto.one/blog/sync-saas-data-to-postgres-upserts-pgvector-neon-supabase/
---

# Sync SaaS Data to Postgres: Upserts, pgvector, Neon & Supabase


If you are building B2B SaaS, your application is only as valuable as the data it can ingest. Your customers expect you to pull millions of records from their existing CRMs, HRIS platforms, ATSs, ticketing systems, and accounting tools directly into your application. You need a reliable way to sync SaaS data to Postgres. You do not want to build and maintain 50 custom data pipelines, nor do you want to force customer-facing integrations through an internal ELT tool designed for offline analytics, a challenge we explore in our guide to [solving the bulk extraction problem](https://truto.one/etl-workflows-using-unified-apis-solving-the-bulk-extraction-problem/). You need a unified API Postgres sync that writes directly to your multi-tenant database.

The market data supports this urgency. According to the 2025 Stack Overflow Developer Survey, Postgres holds a 55.6% adoption rate, maintaining its position as the dominant database for modern applications. The mandate is clear: build the integrations, and get the data into Postgres safely and efficiently.

Postgres has also picked up a second job: it is no longer just the transactional database behind your app. It is now where teams store normalized SaaS records, JSON payloads, audit trails, and vector embeddings. Serverless hosts like Neon and Supabase have grown quickly on the back of AI workloads. That explains why modern integration pipelines increasingly end in Postgres, Neon, or Supabase instead of only traditional data warehouses.

This guide breaks down exactly how to architect a multi-tenant data sync using Truto's Sync Job V4 runtime. We will cover how to configure Postgres as a first-class datastore destination, handle idempotent upserts, execute parameterized SQL securely, and write API data to pgvector on modern serverless hosts.

## The Engineering Reality of Syncing SaaS Data to Postgres

Bulk data extraction in a B2B SaaS context is fundamentally different from internal data warehousing. When you pull data for internal business intelligence, you control the source, the credentials, and the destination. When you extract data on behalf of your customers, you control none of those things. 

**Syncing SaaS data to Postgres means:**
- Pulling paginated data from highly volatile third-party APIs.
- Normalizing wildly inconsistent provider-specific payloads into a shape your product can query.
- Writing rows into tenant-scoped Postgres tables with deterministic inserts, upserts, deletes, and checkpoints.
- Handling OAuth, aggressive rate limits, schema drift, and partial failures without corrupting customer data.

Traditional ETL pipelines fail here because they are not designed for multi-tenant, customer-facing syncs. If you have 1,000 customers and offer 50 integrations, you are managing 50,000 unique integration states. A single broken sync is not just an analytics gap; it becomes a support ticket, a failed onboarding, or a blocked enterprise deal.

Truto solves this by removing integration-specific code from your application, enabling you to [ship integrations as config, not code](https://truto.one/rapidbridge-building-declarative-data-sync-pipelines-with-jsonata/). The platform normalizes upstream API responses into a common data model using generic JSONata expressions, and then routes that normalized data directly to a destination. By configuring Postgres as a datastore destination, Truto's distributed execution engine writes the data directly to your tables without requiring an intermediary storage bucket.

```mermaid
graph TD
  Upstream["Upstream API<br>(Salesforce, HubSpot)"] -->|"Extract"| SyncRuntime["Truto Sync Runtime"]
  SyncRuntime -->|"Normalize"| Mapping["JSONata Mapping"]
  Mapping -->|"Datastore Destination"| Postgres["Postgres Database<br>(Neon, Supabase)"]
  Postgres -->|"upsertRows"| TargetTable["Target Table"]
```

For a broader look at the architectural differences between internal ETL and customer-facing syncs, read our guide on [Handling Bulk Data Extraction and ETL Workflows Through Unified APIs](https://truto.one/handling-bulk-data-extraction-and-etl-workflows-through-unified-apis/) and the overview of [Syncing Data from SaaS Apps](https://truto.one/sync-data-from-saas-apps/).

## Configuring Postgres as a Sync Job Destination in Truto

**A Truto Postgres sync has three moving parts:**
1. A **Postgres datastore** that stores the connection string.
2. A **Sync Job V4 resource graph** that pulls from a unified API resource.
3. A **datastore destination node** that calls `insertRows`, `upsertRows`, `deleteRows`, or `executeStatement`.

In Truto, a datastore separates the *how to connect* (credentials, connection strings) from the *what to do* (which sync method to call, what data to write). Datastores are scoped to your multi-tenant environments, and sensitive configuration fields are encrypted at rest. You can reuse one Postgres datastore across multiple jobs—for example, one job for CRM contacts, one for companies, and one for ticket history.

A minimal datastore configuration looks like this:

```yaml
type: postgres
config:
  connection_string: ${POSTGRES_URL}
validation_methods:
  - testConnection
```

The connection string must point to a publicly accessible Postgres host. Truto opens a Postgres connection for the method call, runs the write, and closes it. That design is intentionally boring. There is no hidden staging bucket and no proprietary data warehouse in the middle.

### The TLS Requirement and the Supabase Gotcha

Truto requires TLS for all Postgres connections unless the connection string explicitly includes `sslmode=disable`. The execution environment verifies TLS certificates against public Certificate Authorities (CAs) only.

This introduces a specific architectural gotcha when connecting to Supabase. Supabase's connection pooler and direct hosts chain to a private CA ("Supabase Root 2021 CA"). Because Truto's runtime cannot be provided with custom private CAs, a standard TLS handshake with Supabase will fail with a `TLS_HANDSHAKE_FAILED` error.

**To successfully connect Truto to Supabase, you must append `sslmode=disable` to your connection string.** Treat that as a compatibility workaround, not a security recommendation. 

> [!WARNING]
> **Connection Lifecycle Note:** Truto's Postgres client opens one TCP connection per sync method call and closes it immediately after execution. It does not maintain a long-lived connection pool. Ensure your database is configured to handle high connection churn, or route requests through a connection pooler like PgBouncer. If your Supabase project strictly enforces SSL, `sslmode=disable` will fail. For Neon, standard public CA TLS usually avoids this specific private CA problem entirely.

## Handling Data Mutations: Insert, Upsert, and Delete Rows

Once your datastore is configured, you define it as a destination node in your V4 Sync Job resource graph. Use the mutation method that matches the data contract:

| Method | Best for | Operational rule |
|---|---|---|
| `insertRows` | Append-only events, audit logs, immutable snapshots | Missing row keys insert `NULL`, not column defaults. |
| `upsertRows` | Current-state objects like contacts, employees, candidates, tickets | Requires non-null `conflict_columns` that match a unique key. |
| `deleteRows` | Tombstones, hard deletes, cleanup by scoped predicates | Empty `where` filters are strictly refused by the runtime. |

### Idempotent Syncs with upsertRows

The most critical method for reliable data synchronization is `upsertRows`. Network failures, worker crashes, and upstream API timeouts are inevitable. Your sync jobs must be idempotent—running the same sync twice should result in the exact same database state without duplicating records.

The `upsertRows` method maps directly to PostgreSQL's `INSERT ... ON CONFLICT (...) DO UPDATE` syntax, guaranteeing an atomic insert-or-update outcome under normal conditions.

Start with the database contract, not the sync job. You need a tenant-safe table:

```sql
create table app.crm_contacts (
  tenant_id text not null,
  integrated_account_id uuid not null,
  remote_id text not null,
  email text,
  first_name text,
  last_name text,
  updated_at timestamptz,
  raw jsonb not null default '{}'::jsonb,
  synced_at timestamptz not null default now(),
  primary key (tenant_id, integrated_account_id, remote_id)
);
```

Then make your `upsertRows conflict_columns` match the primary key:

```yaml
# 1. A transform node shapes each unified contact into a table row
name: shape_contacts
type: transform
depends_on: [list_contacts]
config:
  expression: |
    resources.crm.contacts.{
      "tenant_id": $args.tenant_id,
      "integrated_account_id": $args.integrated_account_id,
      "remote_id": id,
      "email": email_addresses[0].email,
      "first_name": first_name,
      "last_name": last_name,
      "updated_at": updated_at,
      "raw": $
    }[]
---
# 2. The destination upserts those rows into Postgres
name: write_contacts
type: destination
depends_on: [shape_contacts]
resources_to_persist: [shape_contacts]
destination_type: datastore
method: upsertRows
config:
  id: ds_postgres_prod
  config:
    table: app.crm_contacts
    rows: '{{payload.records:json}}'
    conflict_columns:
      - tenant_id
      - integrated_account_id
      - remote_id
    update_columns:
      - email
      - first_name
      - last_name
      - updated_at
      - raw
      - synced_at
```

**Architectural Nuances and Production Gotchas for upsertRows:**
1. **Batching:** Rows are sent in one statement per set of keys. At most 1,000 rows and 65,535 parameters are allowed per statement.
2. **Do not conflict only on `remote_id`:** Unless you enjoy cross-tenant data corruption, always include `tenant_id`. Most vendors only guarantee uniqueness inside one customer account.
3. **Include `integrated_account_id`:** This is vital if the same tenant can connect multiple accounts from the exact same provider.
4. **Keep `raw jsonb`:** Even when you map first-class columns, vendor APIs drift. Storing raw payloads saves you from executing painful backfill migrations later.
5. **Pass arrays with `:json`:** A bare `{{payload.records}}` placeholder resolves to a string. The `:json` suffix keeps `rows` an array of objects.
6. **Use `update_columns` deliberately:** If a row omits a column in the payload, it retains its stored value in the database rather than being overwritten with `NULL`. This is highly useful for enrichment fields your application owns.
7. **Reject bad keys early:** If a row is missing a column specified in `conflict_columns` (or if it is explicitly `null`), Truto will refuse the row to prevent catastrophic overrides.

### Standard Inserts and Deletes

If you are writing append-only data, use `insertRows`. Missing keys in the payload will insert `NULL` rather than triggering the column's default value.

For `deleteRows`, you provide a `where` object. Equality is checked per column. Passing `null` translates to `IS NULL`, and passing an array translates to an `IN (...)` clause. Use it for scoped cleanup, not broad destructive operations. If you need soft deletes, prefer an upsert or `executeStatement` that sets `is_deleted = true`.

## Running Parameterized SQL with executeStatement

While the CRUD methods handle 90% of sync use cases, you occasionally need to execute complex SQL logic—such as calling stored procedures, updating JSONB deeply, or managing complex relational constraints. For this, Truto provides the `executeStatement` method.

### The executeStatement Security Mandate

When using `executeStatement`, you must pass all dynamic untrusted values through the `params` array. **Never template values directly into the `statement` string.**

The dangerous version looks like this:

```yaml
method: executeStatement
loop_on: payload.records
config:
  statement: |
    update app.crm_contacts
    set email = '{{payload.records.email}}'
    where remote_id = '{{payload.records.remote_id}}'
```

With `loop_on`, the destination runs once per record, and `payload.records` is the current record. The statement looks harmless until a vendor field contains a quote, a semicolon, or a malicious payload. Truto's V4 runtime resolves placeholders across the entire method configuration. If a malicious user in the upstream CRM sets their name to `'); DROP TABLE app.crm_contacts;--`, and you template that into the statement, the Postgres driver will execute it as a simple query, allowing stacked statements to destroy your database.

**Correct Usage (Parameterized):**

```yaml
method: executeStatement
loop_on: payload.records
config:
  statement: |
    update app.crm_contacts
    set email = $1,
        updated_at = $2,
        synced_at = now()
    where tenant_id = $3
      and integrated_account_id = $4
      and remote_id = $5
  params:
    - '{{payload.records.email}}'
    - '{{payload.records.updated_at}}'
    - '{{args.tenant_id}}'
    - '{{args.integrated_account_id}}'
    - '{{payload.records.remote_id}}'
```

OWASP's SQL injection guidance is blunt: define SQL structure separately from data values, then bind values through the database API. By passing variables through `params`, the Postgres driver sends the query and the parameters separately over the wire, treating bound values strictly as data rather than executable SQL syntax.

> [!CAUTION]
> Never put `{{payload.*}}`, `{{args.*}}`, or vendor-provided text directly inside `statement`. Use `$1`, `$2`, `$3` placeholders. If the table name or column name must vary dynamically, map it from an application-owned allow-list before the sync job is even created.

## Writing AI Embeddings to pgvector on Neon or Supabase

The rise of Retrieval-Augmented Generation (RAG) means B2B SaaS applications are no longer just syncing tabular data—they are generating vector embeddings of that data and storing them for semantic search. If your application relies on AI agents, you need to write API data to pgvector natively.

Records from a SaaS API do not arrive with vectors, and Truto does not compute embeddings. A practical split is: the sync job keeps the source text and raw record current, and your own worker writes the vectors into the same table.

A practical schema for RAG over CRM contacts or support tickets looks like this:

```sql
create extension if not exists vector;

create table app.crm_contact_embeddings (
  tenant_id text not null,
  integrated_account_id uuid not null,
  remote_id text not null,
  source_text text not null,
  embedding vector(1536),
  raw jsonb not null,
  updated_at timestamptz,
  primary key (tenant_id, integrated_account_id, remote_id)
);

create index crm_contact_embeddings_cosine
on app.crm_contact_embeddings
using hnsw (embedding vector_cosine_ops);
```

The sync job upserts the text and leaves `embedding` alone. Because `embedding` is not in `update_columns`, an upsert never overwrites a vector your worker already wrote:

```yaml
method: upsertRows
config:
  id: ds_postgres_prod
  config:
    table: app.crm_contact_embeddings
    rows: '{{payload.records:json}}'
    conflict_columns:
      - tenant_id
      - integrated_account_id
      - remote_id
    update_columns:
      - source_text
      - raw
      - updated_at
```

Your worker then selects rows whose `embedding` is `null` (or whose `updated_at` is newer than the vector), calls your embedding model, and writes the result. Clear `embedding` in the same statement that changes `source_text` if you want stale vectors to be re-embedded.

Truto's Postgres client sends objects and arrays as JSON text. JSON text suits `json`, `jsonb`, and pgvector `vector` columns, because pgvector parses `[0.12, -0.45, 0.78]` as a vector. So if your records already carry a float array, for example from a transform over data that includes embeddings, an `insertRows` or `upsertRows` call writes it straight into the `vector` column with no casting SQL.

> [!NOTE]
> **Postgres Array Columns vs pgvector:** While JSON text works flawlessly for `jsonb` and pgvector `vector` columns, it does not automatically cast to standard Postgres array columns like `int []` or `text []`. If you need to write to native Postgres arrays, use the `executeStatement` method with explicit casting in the SQL string. Additionally, do not index pgvector on day one unless you have enough rows to justify it. Start with exact search, measure query latency, then add HNSW or IVFFlat.

For Supabase-specific agent workflows, see [Connect Supabase to ChatGPT: Run SQL Queries and Manage Projects](https://truto.one/connect-supabase-to-chatgpt-run-sql-queries-and-manage-projects/).

## Managing Rate Limits and API Pagination

When you sync SaaS data to Postgres at scale, treating pagination, checkpoints, and HTTP 429s (Too Many Requests) as normal control flow is mandatory. This section applies when your own code calls Truto's Unified API, for example to backfill or to fetch records outside a sync job.

Many unified APIs claim to "magically handle" rate limits by silently queueing, retrying, and absorbing 429 errors. Truto takes a radically honest, developer-first approach: **we do not silently swallow rate limit errors.**

Hidden retries make latency unpredictable, consume customer quota without visibility, and make debugging miserable. When an upstream provider returns a 429, Truto passes that error directly back to the caller and normalizes the chaotic, provider-specific rate limit headers into standardized IETF headers:
- `ratelimit-limit`: The maximum number of requests permitted.
- `ratelimit-remaining`: The number of requests remaining in the current window.
- `ratelimit-reset`: The time at which the rate limit window resets.

By passing these standardized headers back to your application, you retain complete control over your backoff and retry architecture. You own the retry policy, bounded backoff, jitter, and idempotency.

A minimal caller-side wrapper in TypeScript looks like this:

```ts
const sleep = (ms: number) => new Promise(resolve => setTimeout(resolve, ms));

export async function runWithRateLimitBackoff(run: () => Promise<Response>, attempt = 0): Promise<Response> {
  const res = await run();

  if (res.status !== 429 || attempt >= 5) {
    return res;
  }

  const retryAfter = Number(res.headers.get('retry-after') ?? 0);
  const reset = Number(res.headers.get('ratelimit-reset') ?? 0);
  const baseMs = Math.max(retryAfter, reset, 1) * 1000;
  const jitterMs = Math.floor(Math.random() * 500);

  await sleep(baseMs + jitterMs);
  return runWithRateLimitBackoff(run, attempt + 1);
}
```

For pagination, use vendor-supported cursors where available, store watermarks per tenant, and design every page write to be idempotent. A job should be entirely safe to restart after page 37 without duplicating 36 pages of data.

## Production Checklist for a Postgres Sync

| Area | Good default | Failure mode if ignored |
|---|---|---|
| Tenant key | `tenant_id`, `integrated_account_id`, `remote_id` | Cross-customer overwrites and data corruption |
| Write method | `upsertRows` for mutable objects | Duplicate rows or stale records during incremental syncs |
| Raw payload | `raw jsonb` column | Lost vendor fields during upstream schema drift |
| SQL safety | `executeStatement params` only | Catastrophic SQL injection vulnerabilities |
| Deletes | Soft delete first, hard delete later | Accidental data loss and impossible recoveries |
| Rate limits | Caller-side backoff with jitter | Runaway retries, phantom latency, and quota exhaustion |
| Vectors | Validate embedding dimension | Failed database writes or terrible semantic search quality |
| Observability | Per-tenant sync run status and record errors | Support cannot debug failures when customers complain |

## Start with a Safe Sync, Then Specialize

Syncing B2B SaaS data directly into your Postgres database requires a rigorous architectural approach. You cannot rely on fragile custom scripts, and you cannot force customer-facing integrations through internal ETL tools—a distinction we cover in our analysis of [reverse ETL vs unified APIs](https://truto.one/reverse-etl-vs-unified-apis-syncing-warehouse-data-to-customer-saas/).

Do not begin with a fully generalized integration platform inside your codebase. Start with one high-value object—usually contacts, companies, tickets, or invoices. Create a tenant-scoped table. Keep the raw payload. Use `upsertRows` to guarantee data integrity. Add `executeStatement params` only when the built-in mutation methods cannot express the custom write logic. Add the pgvector worker after the base tabular sync is stable and observable.

Integrations should be declarative configuration, not a maintenance nightmare. By utilizing Truto's V4 Sync Jobs with a Postgres datastore destination, you eliminate integration-specific code and stop rewriting the same OAuth, pagination, and object mapping logic for every CRM or ATS.

If you want Truto to write unified API data directly to your Postgres, Neon, or Supabase database, start with the datastore destination pattern from our [product update on syncing unified API data to your datastore](https://truto.one/product-update-directly-sync-unified-api-data-to-a-data-store-of-your-choice/). Then harden it with the rules in this guide.

> Want to sync customer SaaS data into Postgres without building and maintaining every connector yourself? Partner with Truto. We can help you design the sync job, schema, upsert strategy, and pgvector path before your integration backlog turns into an unmanageable platform.
>
> [Talk to us](https://cal.com/truto/partner-with-truto)