Skip to content
VisibilityIQ365 VisibilityIQ365 VisibilityIQ Help

Connectors & Sync

Two tables back every external data source: external_connections holds the per-project credential and configuration for one connector, and connector_sync_runs is the append-only log of pull attempts against it. Both are defined in migrations/001_initial_schema.sql and accessed exclusively through src/lib/db/connections.ts.

external_connections

Section titled “external_connections”

One row per (project_id, source). The source column started as gsc | ga4 | crux | bing and was widened by later migrations (015, 027) to also allow indexnow, psi, and plausible — SQLite cannot alter a CHECK in place, so each addition recreates the table. The token_data column stores the connector’s encrypted credentials (see below).

CREATE TABLE IF NOT EXISTS external_connections (
  id            TEXT PRIMARY KEY,
  project_id    TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  source        TEXT NOT NULL
                  CHECK(source IN ('gsc','ga4','crux','bing','indexnow','psi','plausible')),
  status        TEXT NOT NULL DEFAULT 'not-connected'
                  CHECK(status IN ('connected','reauth-required','not-connected')),
  account_name  TEXT,
  account_email TEXT,
  property_id   TEXT,
  sync_cadence  TEXT NOT NULL DEFAULT 'daily',
  scope_summary TEXT,
  token_data    TEXT,            -- encrypted token JSON (access_token, refresh_token, expiry_date, scope)
  last_sync_at  TEXT,
  created_at    TEXT NOT NULL,
  updated_at    TEXT NOT NULL,
  UNIQUE(project_id, source),
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

Indexes: idx_connections_project_source (UNIQUE, project_id, source), idx_connections_project, idx_connections_source.

Connection status states

Section titled “Connection status states”
statusMeaning
not-connectedNo credentials stored (default)
connectedValid token stored; the connector can sync
reauth-requiredToken refresh failed or scope revoked; the dashboard prompts a re-connect

Credential encryption

Section titled “Credential encryption”

token_data is never written in plaintext. upsertConnection runs the JSON through encryptSecret before the INSERT … ON CONFLICT, and getConnection / listProjectConnections transparently decryptSecret it on read (src/lib/security/crypto.ts). The upsert uses token_data = COALESCE(excluded.token_data, token_data), so a metadata-only update (e.g. changing sync_cadence) can pass null for the token without clobbering the stored credential. The row id is deterministic — conn-{projectId}-{source} — and updateConnectionLastSync stamps last_sync_at after a successful pull. See OAuth Connector Flow for how Google tokens land here.

connector_sync_runs

Section titled “connector_sync_runs”

Append-only pull history; one row per sync attempt, written by recordSyncRun. Its source CHECK was widened in lockstep with external_connections (migrations 013, 014, 027). status is a three-state outcome rather than a long-running lifecycle — the run row is written after the pull resolves.

CREATE TABLE IF NOT EXISTS connector_sync_runs (
  id            TEXT PRIMARY KEY,
  project_id    TEXT NOT NULL,
  source        TEXT NOT NULL
                  CHECK(source IN ('gsc','ga4','crux','bing','psi','indexnow','plausible')),
  status        TEXT NOT NULL CHECK(status IN ('success','partial','failed')),
  rows_pulled   INTEGER NOT NULL DEFAULT 0,
  summary       TEXT,
  error_message TEXT,
  pulled_at     TEXT NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

Indexes: idx_sync_runs_project, idx_sync_runs_source, idx_sync_runs_pulled (pulled_at DESC, for the most-recent-runs list).

Sync run status states

Section titled “Sync run status states”
statusMeaning
successThe pull completed; rows_pulled reflects rows written
partialSome data pulled, some failed (e.g. one date range or property errored)
failedThe pull errored before any data was committed; error_message set

summary carries a human-readable note about what was pulled; error_message is populated on partial/failed. Listing is bounded — listSyncRuns defaults to the 30 most recent runs ordered by pulled_at DESC.

How a sync uses these tables

Section titled “How a sync uses these tables”

POST /api/connectors/sync (src/pages/api/connectors/sync.ts) validates { projectId, source } with Zod (source enum: gsc | ga4 | crux | bing | psi | plausible), then:

  1. getConnection(db, projectId, source) — loads and decrypts the credential.
  2. Pulls and normalizes data into the source’s metrics tables (e.g. GSC/GA4 daily metrics, CrUX, Bing, Plausible).
  3. recordSyncRun(...) — appends a connector_sync_runs row with the outcome and rows_pulled.
  4. updateConnectionLastSync(...) — stamps external_connections.last_sync_at.

There is no in-row checkpoint column; resumability is expressed at the run granularity — a partial or failed run is re-issued and its successor recorded as a fresh row, keeping the history immutable.

Section titled “Related”