Architecture

A deep-dive into how AlgoBridge works internally.

Overview

AlgoBridge consists of two database layers and a sync engine that runs on a fixed 10-second schedule.

┌─────────────────────────────────────────────────────┐
│                   AlgoBridge Server                 │
│                                                     │
│  ┌─────────────┐        ┌──────────────────────┐   │
│  │  Platform DB │        │     Sync Engine       │   │
│  │  (platform PG) │     │  (runs every 10s)     │   │
│  │             │        │                       │   │
│  │  tenants    │        │  1. Poll _trigger_log │   │
│  │  workspaces │        │  2. Batch → SF API    │   │
│  │  mappings   │        │  3. Write back result │   │
│  └─────────────┘        └──────────────────────┘   │
└─────────────────────────────────────────────────────┘
         │                          │
         ▼                          ▼
  ┌─────────────┐          ┌────────────────┐
  │ Client PG   │          │  Salesforce    │
  │ (your DB)   │◄────────►│  API           │
  │             │          │                │
  │ _trigger_log│          │  SOAP / Bulk   │
  │ contact     │          │  v2            │
  │ opportunity │          └────────────────┘
  └─────────────┘

Two Database Model

AlgoBridge strictly separates two databases:

Platform Database (managed by AlgoBridge)

Stores operational metadata only. Never stores your sync data.

Client Database (your PostgreSQL)

Your own PostgreSQL instance. AlgoBridge installs a small set of system objects and then works entirely within them.

Trigger-Based Change Data Capture

When a row changes in a mapped table, the logger trigger fires and inserts a row into _trigger_log:

-- Installed automatically for each mapped table
CREATE OR REPLACE FUNCTION ab_contact_logger()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO _trigger_log (
    txid, table_name, record_id, sfid, action, state, values
  ) VALUES (
    txid_current(),
    'contact',
    NEW.id,
    NEW.sfid,
    TG_OP,           -- 'INSERT', 'UPDATE', or 'DELETE'
    'NEW',
    hstore(NEW) - hstore(OLD)  -- hstore diff: only changed columns
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER ab_contact_logtrigger
  AFTER INSERT OR UPDATE OR DELETE ON contact
  FOR EACH ROW EXECUTE FUNCTION ab_contact_logger();

Why hstore (not JSONB)?

hstore stores the column diff as a flat key-value map. This matches the de facto standard for Salesforce sync. The diff (hstore(NEW) - hstore(OLD)) captures only the columns that actually changed, reducing payload size to Salesforce.

State Machine

Every row in _trigger_log moves through a defined state machine:

NEW ──► PENDING ──► SUCCESS
                └──► FAILED

The _ab_lastop column on the mapped table itself reflects the outcome:

Sync Engine — 10-Second Batch

The sync worker runs on a fixed 10-second schedule (not configurable — this matches the de facto standard). Each cycle:

1. Claim: UPDATE _trigger_log SET state = 'PENDING' WHERE state = 'NEW' RETURNING *
2. Batch: Group claimed rows by Salesforce object
3. Route: ≤ 200 records → SOAP API; > 200 records → Bulk API v2
4. Write back: On success, set state = 'SUCCESS' and _ab_lastop = 'INSERTED' | 'UPDATED'
5. Guard: Before writing back to the mapped table, set SET LOCAL ab.in_sync = 'true' — the status trigger checks this variable and skips re-logging the write

SOAP vs Bulk API

SF → PG Write-Back

When Salesforce pushes changes back (via polling the SF REST API), the sync engine:

1. Queries Salesforce for records modified since the last systemmodstamp
2. Upserts rows into the mapped PostgreSQL table
3. Sets SET LOCAL ab.in_sync = 'true' before each write
4. The status trigger sees ab.in_sync = 'true' and skips inserting into _trigger_log, preventing a feedback loop

-- Status trigger function (installed per mapped table)
CREATE OR REPLACE FUNCTION ab_contact_status()
RETURNS TRIGGER AS $$
BEGIN
  IF current_setting('ab.in_sync', true) = 'true' THEN
    RETURN NEW;  -- skip logging, this is a write-back
  END IF;
  NEW._ab_lastop := 'PENDING';
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

31-Day Archive

After a _trigger_log row reaches SUCCESS or FAILED, it is moved to _trigger_log_archive:

INSERT INTO _trigger_log_archive SELECT * FROM _trigger_log WHERE state IN ('SUCCESS', 'FAILED');
DELETE FROM _trigger_log WHERE state IN ('SUCCESS', 'FAILED');

Rows in _trigger_log_archive are purged after 31 days. This gives you a full audit trail without unbounded table growth.

Credential Security

PostgreSQL connection strings and Salesforce OAuth tokens are stored encrypted at rest in the platform database. Encryption uses the ENCRYPTION_KEY environment variable (a 32-character key). The raw credentials are never logged or exposed through any API endpoint.

Exponential Backoff on FAILED Rows

When a trigger-log row transitions to FAILED, AlgoBridge does not immediately retry it on the next 10-second cycle — doing so would hammer Salesforce with known-broken records and exhaust your daily API quota.

Instead, FAILED rows follow an exponential backoff schedule:

The _trigger_log.retry_after timestamp controls when the row becomes eligible again. The sync worker skips rows where retry_after > now().

To bypass the backoff and force an immediate retry, use the Retry button in the Record Explorer or reset the row’s state to NEW directly.

CDC Gap Detection and SOQL Backfill

Salesforce’s Change Data Capture (CDC) replay window is 72 hours. If the AlgoBridge process is down or the CDC subscription lapses for longer than that, the replay window expires and CDC events are lost.

AlgoBridge detects this condition by comparing the last-seen CDC event timestamp against the current time. When the gap exceeds a configurable threshold (default: 72 hours), it automatically falls back to a full SOQL backfill for each affected mapping:

1. Queries Salesforce for all records where SystemModstamp > last_successful_sync_at
2. Upserts those records into PostgreSQL
3. Resumes CDC streaming from the current position

This backfill runs as a one-off job and does not interrupt the normal 10-second sync cycle for other mappings.

Bulk API v2 for Initial Loads

When a mapping is first activated, AlgoBridge performs an initial load — fetching the entire dataset for that Salesforce object and inserting it into PostgreSQL. For large objects (e.g. millions of Account records), this initial load uses Bulk API v2 regardless of record count, because the SOAP API batch limit (200 records per call) would exhaust the daily API quota.

The Bulk API v2 initial load:

1. Creates a Bulk API v2 query job for the mapped fields
2. Streams result batches directly into PostgreSQL via upsert
3. Sets _ab_lastop = 'SYNCED' on every inserted row
4. Marks the mapping as active only after the initial load completes successfully

Subsequent incremental syncs (CDC + SOQL polling) use the normal SOAP/Bulk threshold (≤ 200 → SOAP, > 200 → Bulk).

Compound External Keys (Multi-Field Upsert Keys)

By default, AlgoBridge uses the sfid column as the upsert key for SF→PG writes. For bidirectional sync, a secondary external key field (e.g. Email on Contact) handles the race window where a new record has not yet received its sfid from Salesforce.

AlgoBridge supports up to 3-column compound external keys for mappings where a single field is insufficient for deduplication. When a compound key is configured, the SF→PG upsert strategy becomes:

1. UPDATE … WHERE sfid = $1 — matches by Salesforce ID if known
2. UPDATE … WHERE ext_key_1 = $1 AND ext_key_2 = $2 AND sfid IS NULL — matches by the compound key when sfid has not yet been written back
3. INSERT — new record if neither of the above matched

Configure compound keys in Mappings → Edit Mapping → External Key Fields (select up to 3 fields).

Conflict Resolution for Bidirectional Sync

In bidirectional sync mode, the same record can be modified in both PostgreSQL and Salesforce within the same 10-second window. Without a resolution policy, the last writer wins — which can cause updates to be silently overwritten.

AlgoBridge resolves conflicts using Salesforce as the system of record by default:

1. The PG→SF sync sends the PostgreSQL change to Salesforce
2. Salesforce accepts the write and updates SystemModstamp
3. The next SF→PG poll detects the updated SystemModstamp and writes the Salesforce version back to PostgreSQL
4. If the Salesforce write failed (e.g. validation rule), the FAILED row in _trigger_log preserves the original PostgreSQL value for inspection

This means that if both sides change simultaneously, the Salesforce value wins on the next poll cycle (typically within 60–70 seconds).