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       │   │
│  │  (Neon/RDS) │        │  (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.

Table	Purpose
`tenants`	Workspace accounts
`tenant_connections`	Salesforce + PostgreSQL credentials
`tenant_mappings`	Object-to-table mapping configuration
`sync_history`	Per-batch sync audit log
`plans` / `subscriptions`	Billing metadata

Client Database (your PostgreSQL)

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

Object	Type	Purpose
`_trigger_log`	Table	CDC queue — captures every change
`_trigger_log_archive`	Table	31-day audit trail of processed rows
`_abmeta`	Table	Schema metadata (mapped columns, sfid mapping)
`ab_{table}_logtrigger`	Trigger	Fires on INSERT/UPDATE/DELETE, writes to `_trigger_log`
`ab_{table}_logger()`	Function	Trigger function for the log trigger
`ab_{table}_status_trigger`	Trigger	Write-back guard — skips the log trigger during SF→PG writes
`ab_{table}_status()`	Function	Checks `ab.in_sync` session variable

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

State	Meaning
`NEW`	Row inserted by trigger, not yet picked up
`PENDING`	Sync engine has claimed the row, API call in-flight
`SUCCESS`	Salesforce confirmed the operation
`FAILED`	API call failed; `_ab_err` populated with error detail

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

`_ab_lastop`	Set when
`PENDING`	Row first inserted (trigger sets this)
`INSERTED`	SF confirmed a new record was created
`UPDATED`	SF confirmed an existing record was updated
`SYNCED`	Row written back from Salesforce → PostgreSQL
`FAILED`	Sync failed

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:

Claim: UPDATE _trigger_log SET state = 'PENDING' WHERE state = 'NEW' RETURNING *
Batch: Group claimed rows by Salesforce object
Route: ≤ 200 records → SOAP API; > 200 records → Bulk API v2
Write back: On success, set state = 'SUCCESS' and _ab_lastop = 'INSERTED' | 'UPDATED'
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

	SOAP API	Bulk API v2
Threshold	≤ 200 records per batch	> 200 records per batch
Latency	Low (~200ms round-trip)	Higher (job-based, async)
Best for	Small, frequent changes	Large initial loads or bulk updates
SF API calls used	1 per record (counted against daily limit)	1 job + chunks (lower per-record cost)

SF → PG Write-Back

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

Queries Salesforce for records modified since the last systemmodstamp
Upserts rows into the mapped PostgreSQL table
Sets SET LOCAL ab.in_sync = 'true' before each write
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 are stored in AWS SSM Parameter Store as SecureString values — AES-256 encrypted at rest, never stored in environment variables or the platform database.

Salesforce OAuth tokens are encrypted at rest in the platform database using the workspace’s encryption key.