Salesforce & CSV Mode

Salesforce is the system of record for who a partner is. The portal talks to it in both directions:

• Read (SF → portal): on login the portal resolves the partner by email → SF Contact → Account, reading Partnership_Status__c / mt_Account_Type__c / IsPartner. Most partners already exist in SF; this is the primary path. See Read modes.
• Write (portal → SF): approvals, account-type changes, profile edits, deal registrations, team invites, and opportunity notes are written back to Salesforce. See Write-back.

Admission is decided by account_type, not partnerStatus. The old model gated the portal on the SF-derived partnerStatus; that field is now session/display metadata only (tier, partner type, SF ids). The actual "can this person enter" decision is account_type — see /partners/auth. Keep that distinction in mind throughout this page: everything here populates metadata and writes back to SF; it no longer is the gate.

Live vs CSV. With the JWT creds present (SF_CLIENT_ID/SF_USERNAME/SF_PRIVATE_KEY/SF_LOGIN_URL — set in prod and local), the portal runs live: real SOQL reads + real write-backs. When SF_CLIENT_ID is blank it transparently falls back to CSV mode — a domain-match lookup against the seeded partner_accounts table, and every write-back becomes a guarded no-op. The switch is a single env var, no code change.

The single decision point lives in lib/sf/csv-mode.ts:

export function isCsvMode(): boolean {
  return !process.env.SF_CLIENT_ID || process.env.SF_CLIENT_ID.trim() === "";
}

And it is consulted at the top of fetchPartnerStatusFromSF in lib/sf/partner-status.ts:

export async function fetchPartnerStatusFromSF(email: string) {
  if (isCsvMode()) {
    return fetchPartnerStatusFromCSV(email);
  }
  // …live SOQL path…
}

Note the name: fetchPartnerStatusFromSF is the single entry point for both modes. Callers (the auth gate, the cron job, the callout processor) never branch on the mode themselves — they always call this function and it dispatches internally.

The portal partnerStatus vocabulary#

Both modes resolve to the same four-value union, defined once in partner-status.ts:

export type PartnerStatus = "active" | "inactive" | "pending" | "not_found";

Alongside the status, both modes return the same enrichment fields, which the portal persists onto the partner's session row: contactId, accountId, role, partnerType, and partnerTier. The partnerTier is always run through normalizeTier() from lib/tiers.ts, which collapses any raw SF/CSV casing to one of the canonical slugs registered < silver < gold (defaulting to registered on anything unrecognised). That tier later drives content visibility.

Mode 1 — Live Salesforce (JWT bearer)#

Authentication: server-to-server JWT bearer flow#

There is no human login in this flow and no refresh token. The portal authenticates as a dedicated Salesforce integration user using the OAuth 2.0 JWT bearer grant — it signs a short-lived JWT with an RSA private key, and Salesforce (which holds the matching public cert on the Connected App) trades that assertion for an access token.

All of this lives in lib/sf/client.ts. The assertion is built by hand (no SDK):

const header  = base64url({ alg: "RS256" });
const payload = base64url({
  iss: process.env.SF_CLIENT_ID,   // Connected App consumer key
  sub: process.env.SF_USERNAME,    // integration user to impersonate
  aud: loginUrl,                   // SF_LOGIN_URL (login. or test.)
  exp: now + 300,                  // 5-minute assertion lifetime
});
const sig = createSign("RSA-SHA256")
  .update(`${header}.${payload}`)
  .sign(createPrivateKey(pem), "base64url");
// POST assertion to {loginUrl}/services/oauth2/token
//   grant_type = urn:ietf:params:oauth:grant-type:jwt-bearer

Key implementation details:

• Private key is base64-encoded. SF_PRIVATE_KEY stores the PKCS#8 PEM base64-encoded so it survives single-line env storage. The client base64-decodes it back to PEM before signing: Buffer.from(process.env.SF_PRIVATE_KEY ?? "", "base64").toString("utf-8").
• aud must match the login host. SF_LOGIN_URL defaults to https://login.salesforce.com; point it at https://test.salesforce.com for a sandbox. The JWT aud claim is set to exactly this URL.
• Token caching. The successful token exchange returns { access_token, instance_url }. Both are cached in a module-level singleton (_cache) with an expiry of 55 minutes (expiresAt = Date.now() + 55 * 60 * 1000). The token() helper reuses the cache as long as it has at least 60 seconds of life remaining, otherwise it re-fetches. This keeps a hot serverless instance from re-signing a JWT on every query.
• Hard failure on bad exchange. A non-2xx token response throws SF token exchange failed {status}: {body} — there is no silent fallback to CSV mode here. CSV mode is only reached when SF_CLIENT_ID is blank, not when a configured SF connection fails.

The client exposes three thin helpers over the cached token:

All three throw on a non-OK response with the status and body text included.

The status SOQL query#

fetchPartnerStatusFromSF issues a single SOQL Contact lookup joined to its parent Account:

SELECT Id, Email, Portal_Role__c, Portal_Active__c,
       Account.Id, Account.Partner_Status__c, Account.Partner_Type__c, Account.Partner_Tier__c
FROM Contact
WHERE Email = '<escaped email>'
LIMIT 1

The custom fields this relies on (Salesforce-side, must exist on the org):

SOQL injection note. The email is interpolated into the query string with a minimal escape (email.replace(/'/g, "\\'")). The match value originates from the authenticated Clerk session's email, not arbitrary user input, which bounds the exposure — but this is a single-quote escape, not a parameterised bind.

Status mapping logic#

The Account.Partner_Status__c value (lower-cased) is combined with the per-Contact Portal_Active__c flag to produce the portal status:

const sfStatus = c.Account?.Partner_Status__c?.toLowerCase();
let status: PartnerStatus = "not_found";
if (sfStatus === "active" && c.Portal_Active__c) status = "active";
else if (sfStatus === "inactive" || !c.Portal_Active__c) status = "inactive";
else if (sfStatus === "pending") status = "pending";

The important asymmetry: an active Account with Portal_Active__c = false still yields inactive. The per-Contact flag is an override that can suspend an individual even while the partner account as a whole is active. A SOQL hit with zero records (totalSize === 0) short-circuits to not_found with all enrichment fields null.

Mode 2 — CSV mode (the v1 default)#

CSV mode answers the same question without any Salesforce connectivity. It lives in lib/sf/csv-mode.ts and matches on email domain rather than on an exact Contact email.

Domain matching#

function extractDomain(email: string): string {
  return email.split("@")[1]?.toLowerCase().trim() ?? "";
}

fetchPartnerStatusFromCSV(email):

1. Extracts the domain (everything after @, lower-cased). An email with no domain returns not_found immediately.
2. Queries partner_accounts for the single row where websiteDomain === domain.
3. If no row matches, logs CSV mode: no partner account found for domain and returns not_found.
4. If a row matches, returns the partner status from that row's seeded fields.

const rows = await db()
  .select()
  .from(partnerAccounts)
  .where(eq(partnerAccounts.websiteDomain, domain))
  .limit(1);

The mapping from a partner_accounts row to the PartnerStatus shape:

Because the seed script writes portalStatus: "active" for every imported row (see below), in practice CSV mode resolves to active for any email whose domain is in the table, and not_found for any domain that is not. It is a coarse allow-list keyed on company domain — there is no per-person Portal_Active__c equivalent and no pending/inactive distinction unless a row's portalStatus is edited directly in the DB.

Failure mode. The whole lookup is wrapped in try/catch. Any DB error is logged via logger.error("CSV mode lookup failed", …) and returns not_found — it fails closed, so a database hiccup never accidentally grants access.

Seeding partner_accounts from the CSV#

The table is populated by scripts/seed-from-csv.ts, run with:

cd apps/partners
pnpm seed:csv          # → tsx scripts/seed-from-csv.ts

What it does:

• Reads .sf_partners_data.csv from the partners app working directory (resolve(process.cwd(), ".sf_partners_data.csv")). This is a manual export from Salesforce — it is not committed (it contains partner data).
• Loads env from, in order: ~/.envs/.matters-ai/.env.partners.prod, then .env.local, .env, .env.prod. It requires PARTNERS_NEON_DATABASE_URL and exits non-zero if missing. It connects directly via @neondatabase/serverless (neon() + drizzle), bypassing the app's runtime DB client.
• Parses CSV by hand with a small quote-aware row parser (handles "" escapes and commas inside quoted fields) — no CSV library dependency.
• Extracts the domain from the Website column via extractDomain(): prepends https:// if needed, parses with new URL(), strips a leading www., lower-cases. Falls back to a regex strip if URL parsing throws. This is the value matched against at lookup time, so the seed-side and lookup-side normalisation must agree (both strip www. and lower-case).
• Upserts into partner_accounts keyed on sfAccountId (the unique column) via onConflictDoUpdate. Re-running the seed refreshes mutable fields (accountName, websiteDomain, website, partnerType, industry, billingCountry, billingCity, accountOwner, updatedAt) without creating duplicates.

Expected CSV columns

The script header comment and column reads expect a Salesforce report export with these headers (it tolerates a couple of alternates):

Account Name, Account ID, Website, Partner Account Name, Partner Account ID,
Account Type (or Partner Account: Type), Partner Account: Industry,
Partner Account: Billing Country, Partner Account: Billing City,
Partner Account Owner

Mapping into the table:

Tiers are not seeded here. partnerTier is written as null by seed:csv. There is a separate pnpm seed:tiers (scripts/seed-tiers.ts) for tier assignment. So a freshly CSV-seeded portal treats everyone as the default registered tier until tiers are applied.

The partner_accounts and callout_events table shapes are documented in full at /partners/data-model; seeding and env setup are covered operationally at /partners/operations.

Write-back — portal → Salesforce#

Everything the portal writes to SF lives in lib/sf/writeback.ts. Every export is best-effort and guarded: it no-ops in CSV mode, and it try/catches internally so a Salesforce outage can never block or fail the primary Neon write that triggered it. Field names are transcribed verbatim from the live org — a field the integration user can't edit, or a picklist value outside the value set, 400s the whole PATCH (and the catch swallows it), so we never invent fields.

Account.IsPartner is platform-managed (no Edit FLS) and is never written. The authoritative partner-standing field is Partnership_Status__c (picklist: Active | In-Progress | Inactive).

Trigger inventory#

account-type → SF was a gap. Previously only the approval path reached Salesforce; a straight /admin/accounts promotion did not. syncAccountStandingToSalesforce closes that so both paths keep SF in step.

Company Profile — two-way sync#

The partner's own profile page is a genuine two-way surface with Salesforce as the source of truth for the shared Account fields.

• Save (portal → SF): syncProfileToSalesforce PATCHes the Account. One authoritative ACCOUNT_FIELD_MAP in writeback.ts maps every portal field to its SF API name; empty strings are sent as null so clearing a field clears it in SF too.
• Load (SF → portal): fetchAccountProfileFromSalesforce reads the Account and the profile page merges it over the local Neon row — an SF value wins where SF actually has one, so a partner who already exists in Salesforce sees their real data, and not-yet-synced local edits are never blanked.

The mapped fields (portal ⇄ SF API name):

The picklist value sets are transcribed verbatim into lib/sf/account-fields.ts and pinned by a unit test, so the form <select>, the Zod validation, and the write-back mapping can never drift from the org.

Read-only / never partner-editable (Sales-owned intel — surfaced at most, never written by the portal): mt_Account_Type__c, Sales_Region__c, mt_Sub_Region__c, mt_Territory_Tier__c, and all RB2B / ICP / enrichment / customer-success fields.

Open: no ecosystem sub-type field. mt_Account_Type__c is the coarse class (Prospect | Customer | Partner | Investor | Competition | Others), not the Cloud Provider / VAR / Distributor / MSSP sub-type that Tier-Benefits capabilities key off (matchPartnerType). Until a dedicated SF field exists, that sub-type is portal-assigned. Tracked on WEB-49.

The status cache & revalidation#

Resolving status (whether SF or CSV) is not done on every request. The result is cached on the partner's session row in the partner_sessions table with a 15-minute TTL.

Cache read/write — getOrRefreshPartnerStatus#

This is the function the auth gate actually calls. Flow:

1. Look up the partner_sessions row for the user. The lookup is keyed on clerkUserId alone in the default (single-account) mode; when the MULTI_ACCOUNT_ENABLED flag is on, it is keyed on the composite (clerkUserId, clerkOrgId).
2. Cache hit — if the row has a sfStatusCachedAt and it is less than 15 minutes old (CACHE_TTL_MS = 15 * 60 * 1000), return the stored partnerStatus without touching SF/CSV at all.
3. Cache miss / stale — call fetchPartnerStatusFromSF(email) (which dispatches to SF or CSV), then upsert the freshly resolved partnerStatus, sfContactId, sfAccountId, partnerRole (defaulting to "partner_user"), partnerType, partnerTier, and stamp sfStatusCachedAt = now.

const CACHE_TTL_MS = 15 * 60 * 1000;
// hit:
if (rows[0]?.sfStatusCachedAt &&
    now - rows[0].sfStatusCachedAt < CACHE_TTL_MS) {
  return rows[0].partnerStatus;
}

So a partner's effective status can be at most ~15 minutes stale relative to Salesforce (or relative to a partner_accounts edit).

Background revalidation cron#

To keep statuses warm without making any individual login pay the SF round-trip, a Vercel Cron route revalidates stale sessions in the background.

• Route: app/api/cron/revalidate-partner-status/route.ts (GET).
• Auth: requires Authorization: Bearer <CRON_SECRET>, compared with timingSafeEqual. Missing/empty CRON_SECRET rejects everything (401).
• Work: getStaleSessionsForRevalidation(100) selects up to 100 sessions whose sfStatusCachedAt is NULL or older than the 15-minute threshold, then calls getOrRefreshPartnerStatus for each via Promise.allSettled (one failure never aborts the batch).
• Response: { processed, failed }.

// getStaleSessionsForRevalidation: NULL or older than 15 min, capped at limit
where(
  or(isNull(sfStatusCachedAt), lt(sfStatusCachedAt, staleThreshold))
).limit(limit)

Inbound callout webhook (push from Salesforce)#

The cache + cron is the pull side. For near-real-time updates, Salesforce can push record changes to the portal via an outbound callout, handled at app/api/sf/callout/route.ts (POST). Like the live SF integration, this is part of the deferred v1 plan — it is wired and code-complete but only exercised once Salesforce is configured to send callouts.

Request validation pipeline#

In order, the route enforces:

1. Rate limit — sfCalloutLimiter.limit(ip) (Upstash, keyed on x-forwarded-for / x-real-ip). Over-limit → 429.
2. Required headers — x-sf-signature and x-sf-idempotency-key. The idempotency key is truncated to 256 chars (slice(0, 256)) to prevent oversized DB writes / ReDoS. Missing either → 400.
3. HMAC signature — the raw request body is HMAC-SHA256'd with SF_CALLOUT_HMAC_SECRET (same secret set on the SF Named Credential). The computed hex is compared to the header (after stripping a sha256= prefix) with timingSafeEqual, with an equal-length guard. Missing secret → 500; mismatch → 401.
4. Idempotency — the table is checked for an existing row with this idempotencyKey; if found, returns { ok: true, duplicate: true } and does no work.
5. Payload shape — body must be valid JSON with non-empty string objectType and sfRecordId. An empty sfRecordId is explicitly rejected (400) because it would otherwise match rows whose SF ids are unset and mass-update them.

Only after all five does it persist the event and kick off processing.

callout_events — idempotency + audit ledger#

Every accepted callout is inserted into callout_events (schema at /partners/data-model):

The unique constraint on idempotencyKey is what makes the webhook safely retryable — Salesforce can re-deliver and the second delivery is a no-op.

Asynchronous processing — callout-processor.ts#

The route inserts the event, then fires processCalloutEvent without awaiting it (returning { ok: true } to SF immediately). A processing failure is caught and written to the row's error column rather than bubbling to the caller.

processCalloutEvent switches on objectType:

• Contact — finds the partner_sessions row by sfContactId, forcibly invalidates the cache (sfStatusCachedAt = new Date(0)), then calls getOrRefreshPartnerStatus to immediately re-resolve. This is how a Salesforce-side status change reaches the portal faster than the 15-minute TTL.
• Lead — maps SF Status (New → registered, Working → under_review, Converted → opportunity_active, Closed - Not Converted → closed_lost) onto the local opportunities.portalStatus. On a converted lead (IsConverted + ConvertedOpportunityId) it stamps the sfOpportunityId and promotes status to opportunity_active. On a real status change it fires a deal-status-changed email (fire-and-forget; never blocks).
• Opportunity — maps SF StageName (Closed Won → closed_won, Closed Lost → closed_lost, else opportunity_active) onto opportunities.portalStatus and likewise fires a status-change email on change.

Finally it stamps processedAt = now on the callout_events row.

A related background job, app/api/cron/lead-lifecycle-sweep/route.ts, is the time-based counterpart: same CRON_SECRET bearer auth, it marks opportunities cancelled after 90 days of inactivity and stamps an inactivityWarningAt at day 83. It is part of the opportunity lifecycle rather than status resolution, but shares the cron-auth pattern.

Environment variables#

From .env.example:

Generation cheat-sheet (from the env file header):

SF_PRIVATE_KEY:          openssl genrsa 2048 | openssl pkcs8 -topk8 -nocrypt | base64
SF_CALLOUT_HMAC_SECRET:  openssl rand -hex 32
CRON_SECRET:             openssl rand -hex 32

To flip from CSV mode to live Salesforce: provision the Connected App + custom fields, set the four SF_* vars, and redeploy. isCsvMode() returns false the moment SF_CLIENT_ID is non-blank, and every status lookup begins hitting Salesforce — no code change.

Where this connects#

• /partners/auth — the access gate consumes getOrRefreshPartnerStatus; the partnerStatus resolved here is what allows, blocks, or pends a partner.
• /partners/data-model — full schema for partner_accounts (CSV-mode source of truth), partner_sessions (status cache), and callout_events (webhook ledger).
• /partners/operations — running pnpm seed:csv, supplying .sf_partners_data.csv, configuring the SF_* / CRON_SECRET env, and the Vercel Cron schedule.