Eloqua Integration — Client & Admin Configuration

The Eloqua REST client and its admin-editable configuration (issue #103) — the foundation the contact association (#104), lead-capture form sync (#105), and visitor cookie recognition (#106) build on. Ported from the nanaawards client pattern, adapted to guided-selection data.

Two halves, two owners

Half	Where it lives	Who edits it
Credentials	Worker secrets (env) — never D1, never code	Ops, via wrangler secret put
Behavior	D1 (eloqua_settings, eloqua_field_mappings)	Admins, via /admin/eloqua (edit_data capability)

The client module is server-only by construction (src/lib/server/integrations/eloqua.ts — under $lib/server, so SvelteKit refuses to bundle it client-side). The issue's AC named src/lib/integrations/; the server/ placement is deliberate: credential-handling code must be impossible to import into client code.

Credentials (Worker secrets)

Secret	Meaning
ELOQUA_BASE_URL	REST endpoint base, e.g. https://secure.p01.eloqua.com
ELOQUA_SITE_ID	Company/site name — the site in Basic auth site\user
ELOQUA_USERNAME	API user
ELOQUA_PASSWORD	API user's password

Set each with wrangler secret put <NAME>; local dev reads them from .dev.vars (gitignored). eloquaConfigFromEnv(env) is the single resolver: it returns a config only when all four are present, otherwise the names of the missing variables — which the admin page surfaces verbatim, so misconfiguration is diagnosable without log access. With any secret missing the integration is simply off; nothing else degrades.

Client operations & degradation contract

EloquaClient (Basic auth, site\username:password base64-encoded):

Operation	Eloqua endpoint
findContactByEmail(email)	GET /api/REST/2.0/data/contacts?search=…&count=1
createContact(contact)	POST /api/REST/2.0/data/contact
updateContact(id, fieldValues)	PUT /api/REST/2.0/data/contact/{id}
addContactToList(contactId, listId)	POST /api/REST/2.0/data/contact/list/{listId}/membership

No operation ever throws into a caller. Every operation resolves an EloquaResult<T> union: { ok: true, value } or { ok: false, reason, status?, message } with reason one of auth (401/403), http, network, or malformed. A failed sync must never block the selection flow — callers branch on ok and move on. findContactByEmail distinguishes "reachable, no such contact" (ok, value: null) from every failure. Quote characters are stripped from the email before it is interpolated into Eloqua's search grammar.

Admin configuration (D1)

• eloqua_settings — a singleton row (id = 'default'): form_name (the Eloqua form lead-capture submits post through, #105; NULL = unbound), contact_list_id (optional list synced contacts join; NULL = none), plus the tracking pair (#106): tracking_enabled (default off — emitting the script is an explicit admin decision) and tracking_site_id (the public numeric id the script initializes with; not credential material, which is why it may live in D1), plus the hot-lead rule (#351): hot_lead_enabled (master switch, default off — flagging leads is an explicit decision), hot_lead_require_completed, hot_lead_min_tier_ordinal (a tier-ladder ordinal, NULL = no budget gate), and hot_lead_require_premium. The rule is scored by the pure evaluateHotLead (lib/server/lead-intelligence.ts) over signals derived from a converted session; the columns are only its config. Fail-quiet contract: disabled, or enabled with no active condition, flags nothing.
• eloqua_field_mappings — session_field → eloqua_field_id (numeric contact field id). Session-field keys follow the guided-selection session shape: recommendation, alternatives, systems_viewed, completed_at, or answer:<factorId>. Three warm hand-off keys (#456/#704) are also mappable — recommendation_resources and recommendation_configure (the recommended system's Resources and Configure & Price deep-links; both resolve to null and sync nothing when nothing was recommended) and recommendation_repfinder (the nanawall.com repfinder deep-link for "find a local representative" — static per deployment, since the repfinder self-geolocates). Key semantics are resolved at sync time by the association layer (#104/#105), so a mapping may be provisioned before the factor it references exists, and a mapping to a since-removed factor syncs nothing rather than failing. One computed key is also mappable: hot_lead (#351) — resolved at sync time from the hot-lead rule + tier data rather than the session JSON. Mapping it pushes true/false to the Eloqua field; that push is the sales alert (Eloqua's campaign fires on it). Pushing richer session context — not just email + name — is what these mappings are for.

Both are managed at /admin/eloqua (edit_data). Mutations are audited under entity type eloqua; blocked attempts write no audit record (the #131 idiom).

Connectivity check

The admin page's Test connection runs checkEloquaConnectivity: resolve credentials, then a harmless test lookup (a fixed probe address). Outcomes are typed — unconfigured (names the missing secrets, no request made), ok (authenticated; a probe miss counts as reachable), or failed with the client's failure reason. Read-only on both sides; not audited.

Contact association (#104)

eloqua_session_contacts attaches Eloqua identity to a server-side session as a separate, additive table — the anonymous session store's schema and anonymity are untouched, and a session has zero rows here until an association event occurs (lead-capture form submit #105, or a recognized consented cookie #106).

Column	Meaning
session_id	FK → selection_sessions.id, ON DELETE CASCADE
source	form | cookie — with session_id, the primary key
contact_id	Opaque numeric Eloqua contact id — a reference, never PII
associated_at	When the association event happened

The layer (src/lib/server/eloqua-associations.ts) enforces the contract:

• Once per source. associateSessionContact upserts on (session_id, source) — a first event inserts, a repeat through the same source updates the contact id and timestamp, never a duplicate row. A session can therefore carry at most two associations (one per source).
• No PII by construction. isValidContactId admits only opaque numeric ids; an email address, a name, or free text is rejected before it can reach D1 — the same boundary stance as the session store's sanitizer.
• No ghost associations. The session must exist and be unexpired (getSession applies the #116 TTL); associating an unknown or expired session is refused.
• Never throws. Every outcome is a typed AssociationResult — a failed association can't block the selection flow (the #103 contract).
• Purged with the parent. The #116 lifecycle purge deletes sessions with one statement; ON DELETE CASCADE removes their association rows in the same operation (verified live against local D1 — see #104's chunk report).

Lead capture & sync (#105)

The results surface offers an optional "Email my results" card under the recommendation. It is non-blocking by design: results are fully visible without submitting, and — as the consent copy states — nothing leaves the browser until the visitor submits (the card performs no I/O before then).

POST /api/lead is the single entry point for contact details (email required, names optional; sanitized like /api/session). The response is decided by validation alone: the sync runs after the response via waitUntil, so an Eloqua failure can never fail the user.

One submit runs syncLead (src/lib/server/eloqua-lead-sync.ts):

1. Contact upsert — find by email → update with the mapped session field values; unknown email → create (names + values). Uses the admin field mappings, resolved against the visitor's session record; a stale mapping or skipped answer resolves to nothing, never an error.
2. List add — best-effort when a contact list is configured.
3. Bound form post — resolveForm turns the admin's form binding (numeric id, or a name resolved through the assets API) into the form's id + field definitions; submitFormData posts values matched by the form's HTML names (emailAddress/email, firstName, lastName, plus any field whose HTML name equals a mapped session-field key). This is what fires Eloqua-side form processing and campaign triggers.
4. Association — the session is linked to the contact through the #104 layer, source form.

Steps degrade independently — a failed list-add doesn't cost the form post.

Retry queue (eloqua_sync_queue)

A submit whose contact upsert didn't land is enqueued (email/name captured only from that explicit, consented submit) and retried on a backoff — 5m, 30m, 2h, 12h, abandoned after 5 attempts. "Unconfigured" also enqueues, so submits made before the secrets are set are backfillable once they are. Draining happens two ways, one processor: the cron companion worker (workers/session-purge, every 30 min — requires the ELOQUA_* secrets on that worker too) and the audited Process retry queue now button on /admin/eloqua. A queue row references its session only while the session exists (ON DELETE SET NULL) — a purged session never blocks a contact retry; only the association half goes moot.

Lead intelligence (#351)

A converted session (one with a form association) is a lead, and the richest qualification data for it is the session itself. lead-intelligence-read.ts (getLeads) turns each form association joined to its session into a LeadProfile: the needs-profile (answered factors → option labels, in ask order), the recommendation + alternatives (system names), the systems viewed/compared, and the recommended system's budget tier (label + ladder ordinal). It resolves every factor/option/system label in one batch pass and folds each row through the pure buildLeadProfile, so the resolution logic is unit-tested without a DB.

Each profile carries a hot-lead verdict — the pure evaluateHotLead (lead-intelligence.ts) scored against the configured rule (see Admin configuration above): the budget signal is the recommended system's tier ordinal, and "premium viewed" is true when any considered system sits at the top of the ladder. No PII is read or stored — the store holds only the opaque Eloqua contactId; email/name live in Eloqua. Consent (AC-04) is honored by construction: a non-consented session is never associated, so it has no form row and can never surface as a lead — this path reads no consent column.

Surfaced at /admin/leads (view_audit, read-only — a first-class section like Analytics): a filterable table (track / date range / hot-only) of every lead with its needs-profile, recommendation + tier, systems viewed, and a Hot badge. The rule itself is edited on the Hot-lead scoring card at /admin/eloqua.

Visitor cookie recognition (#106)

The "won't have to log in" path: a visitor carrying Eloqua's first-party tracking cookie has their session associated with their existing contact record automatically — no login, no form re-entry. The pipeline is cookie → visitor GUID → Visitor API → linked contact → #104 association (source cookie), and every stage degrades silently to the anonymous + form path.

Tracking script (admin-toggleable)

Buyer pages emit Oracle's standard async tracking snippet when the Visitor tracking script toggle on /admin/eloqua is on and a numeric site id is saved. The snippet (src/lib/eloqua-tracking.ts) queues elqSetSiteId + elqTrackPageView and loads elqCfg.min.js, which sets/reads the first-party ELOQUA visitor cookie (the elqCustomerGUID flow); SPA navigations report additional page views via afterNavigate. The root layout resolves emission through a never-throws read (getTrackingScriptSiteId): /admin paths, a missing D1 binding, the toggle off, or any read failure all mean "no script", never an error. The builder guards the site id to digits-only before it can reach markup.

Recognition on first server touch

Recognition piggybacks on PUT /api/session — the first server touch that guarantees a session row exists — and runs after the response via waitUntil, so it can never cost the visitor latency or an error. The module (src/lib/server/eloqua-visitor-recognition.ts) enforces:

• GUIDs only. The ELOQUA cookie value is a small &-separated bag; extractVisitorGuid parses it and admits only 32-hex GUIDs — an email-shaped or free-text value never reaches the Visitor API.
• Server-side only. The GUID → visitor → contact lookup (GET /api/REST/2.0/data/visitors?search=externalId=…) runs with the Worker-secret credentials; only the opaque contact id is stored (through #104, source cookie). The lone contact datum that ever crosses to the client is a first-name greeting, fetched live by the recognition read surface (GET /api/session/recognition) — never stored.
• Exactly-once cheapness. An already-associated session short-circuits before any Eloqua call; repeat touches cost one D1 read.
• Silent fall-through. No GUID, an unresolvable GUID, a visitor with no linked contact, missing credentials, or an unreachable Eloqua — every outcome is typed, logged-not-thrown, and lands on the anonymous + form path with zero UX difference.

A cookie-recognized session's subsequent selection activity syncs to its contact record in the same background pass (syncRecognizedSessionActivity), resolving the admin field mappings against the current session row — the same graceful-degradation contract as the #105 lead sync. Form-sourced associations are untouched; their sync moment is the form submit.

Cookie visibility & the dev simulation path

Production will live at the nanawall.com domain (path or subdomain, TBD), so the .nanawall.com-scoped cookie is first-party either way — nothing in the implementation reads the request host, so both placements work unchanged.

The workers.dev / localhost dev host can never see that cookie, so recognition is simulatable behind an explicit env gate (see .dev.vars.example):

1. Set ELOQUA_ALLOW_SIMULATED_GUID="true" in .dev.vars (never on the production Worker).
2. Set a browser cookie ns_elq_sim_guid to a 32-hex Eloqua visitor GUID, e.g. in devtools: document.cookie = 'ns_elq_sim_guid=CB29DE3A0E3D4D5EB1D9A6D0B3C7F2A1; path=/'.
3. Drive the guided flow — the recognition path treats the simulated GUID exactly like a real cookie GUID.

The real ELOQUA cookie always wins over the simulation, and with the gate unset (production) a hand-set ns_elq_sim_guid is inert — the simulated cookie is consulted only when the gate is armed.

Tests

src/lib/server/integrations/eloqua.spec.ts (client: auth header, URL shapes, form resolve/submit, every failure mapping), src/lib/server/admin/eloqua.spec.ts (validation + connectivity report), src/lib/server/eloqua-associations.spec.ts (source/contact-id boundaries, schema-property anonymity check), src/lib/server/eloqua-lead-sync.spec.ts (mapping resolution, form-value building), src/lib/server/eloqua-retry.spec.ts (backoff schedule, abandon boundary), src/lib/server/eloqua-visitor-recognition.spec.ts (GUID validation/parsing, simulation gating, recognition outcomes, activity sync, read surface), and src/lib/eloqua-tracking.spec.ts (snippet shape, numeric guard) — all without a live endpoint or database.