Architecture Overview
Backend: Node.js (Express) on Railway, project cozy-stillness, region ams. Two services share this repo: proofiq (the app) and proofiq-daily-sync (the cron job). See Data Sync Pipeline below.
Database: PostgreSQL on Railway with persistent volume, service name Postgres in the same project.
Frontend: No framework, no build step. index.html is the main single-page dashboard (client-scoped views toggled by JS). performance.html, pipeline.html, and crd-match.html are separate standalone pages sharing the same sidebar/topbar CSS pattern and reachable from the nav.
Data layer (updated Oct 2026 -- see "Native API migration" below, this replaces the old Windsor-only description): native, direct API clients for Google Ads, Bing/Microsoft Ads, LinkedIn Ads, Meta (Facebook/Instagram) Ads, Spotify, Vibe, Simpli.fi, GA4, and HubSpot -- each a backend/lib/*NativeClient.js file, confirmed live against real client accounts. Windsor.ai is now only used for Reddit (no native Reddit Ads API access yet -- requires Reddit's own sales/partner allow-listing). InboxIQ (a separate GK3 Railway project) is the source for cold email data, pulled via its own export endpoint, never via Windsor.
Auth: JWT in httpOnly cookies, bcrypt password hashing (cost 12), TOTP 2FA, rate limiting (5 attempts/15 min/IP).
Cron: Separate Railway service proofiq-daily-sync, same repo/branch as the main app but its own Settings. See Data Sync Pipeline for how its config actually works. It has bitten this project twice, see Known Issues.
Deploy: GitHub auto-deploy from main. Railway builds both proofiq and proofiq-daily-sync on every push to main, since both watch the same repo/branch.
Google Ads, Bing Ads, LinkedIn Ads, Meta Ads, Spotify, Vibe, Simpli.fi, GA4, and HubSpot have all been migrated off the Windsor.ai broker onto native, direct connections to each platform's own API -- confirmed live against real client accounts. The Data Sync Pipeline section further down this page was written for the Windsor-era pipeline and describes windsor-adapter.js/CLIENT_WINDSOR_MAP as the live mechanism for every channel -- that's now true only for Reddit (no native Reddit Ads API access yet; requires Reddit's own sales/partner allow-listing, not a self-serve developer flow like the others). Treat entries below describing a specific channel's Windsor quirks (date-range caps, field names, connector IDs) as historical/reference for how that channel used to sync, not the current code path, unless re-verified against the native client file for that channel.
Each native channel has its own client file in backend/lib/ (googleAdsNativeClient.js, bingAdsNativeClient.js, linkedinAdsNativeClient.js, metaNativeClient.js, spotifyNativeClient.js, vibeNativeClient.js, simplifiNativeClient.js, ga4NativeClient.js, hubspotNativeClient.js) plus a thin wrapper (e.g. googleAdsFetch.js) that intercepts that one connector's calls and falls through to Windsor if the native credentials aren't configured -- same drop-in shape for every migrated channel, so windsor-adapter.js//api/admin/sync's call sites didn't need to change, just the function passed in.
HubSpot specifically uses "Service Keys" (Settings > Development > Keys > Service Keys in each client's own portal), not legacy private apps -- HubSpot disabled creating new legacy private apps Oct 26, 2026 and is sunsetting them account-wide by Sept 2027. One Service Key per client portal, mapped in backend/config/client_hubspot_map.js (org id -> Railway env var holding that portal's token). Some clients additionally have a second key (contentApiKeyEnvVar in that same config, e.g. named "ProofIQ-ContentSync" in the actual HubSpot portal) scoped to just forms + content -- HubSpot has no read-only scope for either of those two APIs, so rather than grant write-capable scopes on the main CRM key (which should stay genuinely read-only), those two scopes live on their own separate, honestly-named key. tokenForPortalContent() in that config file falls back to the main token when a client has no separate content key configured yet.
HubSpot page-view/visit analytics (not page listings) are a confirmed dead end for Service Keys: the legacy Analytics v2 Reporting API requires a scope (cms-analytics-api-access) that isn't offered to Service Keys at all in HubSpot's current scope picker (not gated by subscription tier -- entirely absent from the list, confirmed live against a real portal). fetchPagePerformance in hubspotNativeClient.js only lists pages (name/url/created date) now; GA4 (already native) is the real source for landing-page traffic numbers going forward.
Text-only rebrand, same look/colors -- just the name. But since it also touched infrastructure, here's everywhere the old name is gone from and what to know if you're still thinking in "VisionIQ" terms:
- Domain:
visioniq-production-0917.up.railway.appis retired (404, clean cutover, no redirect kept). Current:proofiq-production.up.railway.app. - Railway services:
visioniq->proofiq,visioniq-daily-sync->proofiq-daily-sync. Renamed via theserviceUpdateGraphQL mutation (railway api) since the CLI itself has no rename command --railway service --helponly offers list/delete/link/source/status/logs/redeploy/restart/scale/files. - Session cookie:
viq_token->piq_token. This logged out every active session once the deploy landed -- one-time only, not an ongoing thing to worry about. - GitHub repo:
GK3-VisionIQ/visioniq->GK3-VisionIQ/proofiq. The org name (GK3-VisionIQ) was deliberately left alone -- only the repo under it was renamed. GitHub auto-redirects the old repo URL, so old clone/CI references keep working. - Railway variables:
APP_URLandEMAIL_FROMupdated on the main service;VISIONIQ_ADMIN_EMAIL/_PASSWORD/_URLrenamed toPROOFIQ_*on the daily-sync service (these three aren't read by any current code path -- renamed for consistency only, values otherwise unchanged).
Deliberately left alone: the /visitoriq and /inboxiq routes/marketing pages -- VisitorIQ and InboxIQ are separate GK3 products sharing this same Express app, unrelated to this rename. (All three of /visioniq, /visitoriq, and /inboxiq were already dead routes pointing at HTML files that don't exist in this repo, independent of the rebrand -- /visioniq was renamed to /proofiq anyway for consistency, but none of the three currently serve anything.)
GitHub: GK3-VisionIQ/proofiq (private). Deploys from main.
backend/
config/
client_windsor_config.js CLIENT_WINDSOR_MAP: orgId -> Windsor
account IDs per channel, per client
db/
pool.js Postgres connection pool
migrate-v2-attribution.js v2 schema (contacts/deals/etc.)
migrate-v2-crd-match.js crd_number/crd_confidence columns
migrate-v2-referral-source.js secondary/primary_referral_source
migrate*.js Earlier v1-era migrations (2FA, invites, etc.)
jobs/
daily-sync.js v1-era cron logic -- NOT the active
cron entrypoint, see below
lib/
attributionRules.js The v2 attribution rule engine
hubspotSync.js HubSpot contact/deal sync via Windsor
channelSpendSync.js Ad platform spend sync via Windsor
ga4Sync.js GA4 traffic sync via Windsor
coldEmailSync.js Cold email sync via InboxIQ's export API
syncLog.js withSyncLog() wrapper, writes sync_log
middleware/
auth.js JWT auth + role middleware
routes/
admin.js User + org management, tier backfill
attribution.js v1 endpoints -- legacy, not called by
the current frontend, see Known Issues
attribution-v2.js v2 endpoints -- what the app actually uses
crd-match.js Integration surface for the crd-match service
auth.js Login, 2FA, invite, password
export.js PDF export (PDFKit) -- rebuilt against v2
data (channel_spend/contacts/deals),
tier-aware, working
insights.js AI insights (Anthropic API)
performance.js Legacy v1 (reads touchpoints, a dead
table) -- still mounted, no longer
called by performance.html, see Known
Issues
contentCalendar.js Content Calendar CRUD + AI generate +
share links (Sep 2026), see API Reference
paidMediaTracker.js UTM Plan CRUD, post-launch validator,
QA history, GA4 pull (Sep 2026), see
API Reference
mediaPlan.js Budget Plan CRUD, AI import, Windsor
actuals sync, UTM-item backfill (Sep
2026), see API Reference
coldEmail.js InboxIQ reporting reads (Sep 2026),
see API Reference
guides.js Guides library CRUD (Sep 2026), see
API Reference
lib/ (cont.)
creativeMetricsSync.js Ad/creative-level Windsor sync, sibling
to campaignMetricsSync.js (Sep 2026)
contentInputParse.js Generic xlsx/docx/pdf/csv/txt -> text,
for Content Calendar's uploaded briefs
mediaPlanImport.js AI-assisted Budget Plan file import,
same propose-then-commit shape as Content
Calendar's generator (Sep 2026)
mediaPlanSync.js Fills Budget Plan actual_amount from
channel_spend for mapped rows (Sep 2026)
landingPageCheck.js HEAD/GET resolver used by Paid Media
Tracker's full-funnel QA (Sep 2026)
smartleadClient.js Direct Smartlead calls for real
per-lead engagement status (Sep 2026)
config/ (cont.)
client_smartlead_map.js orgId -> Smartlead API key env-var
name (never a raw key), Sep 2026
server.js Express entry point
windsor-adapter.js The actual cron entrypoint, see below
package.json
frontend/
index.html Main SPA dashboard
performance.html Performance page (fixed Aug 2026 to use
the same /api/v2/attribution/* endpoints
as pipeline.html -- see Known Issues)
pipeline.html Pipeline Intelligence page
crd-match.html Data Enrichment page -- CRD Verification
only (Visitor Fit Scoring moved out to its
own page, viq-audit.html, Sep 2026), hosted
on the crd-match service, see API Reference
below
viq-audit.html Prospect Fit Report page -- pulls a client's synced
VisitorIQ data (backend/routes/visitoriq.js,
backend/visitoriq-adapter.js) or a manual
pixel CSV, runs the same crd-match
visitor-audit pipeline (now a combined
identity-alignment + 5P-fit Claude pass,
business/personal email priority toggle),
see API Reference below
audience-match.html Audience Builder page -- separate feature on
the crd-match service, not client-scoped,
see API Reference below
ppc-builder.html PPC Builder page -- client-scoped, calls
crd-match's Campaign Builder feature, see
API Reference below
content-calendar.html Content Calendar page (Sep 2026)
content-calendar-share.html Public, unauthenticated read-only
Content Calendar view (Sep 2026)
paid-media-tracker.html Paid Media Tracker page (Sep 2026)
faq.html Client FAQ
strategist-guide.html Strategist guide
tech-reference.html This document
(Non-exhaustive above the Sep 2026 additions -- billing.html,
icp-library.html, demandiq-clean.html, etf-simulator.html,
campaign-reporting.html, and content-calendar/paid-media-tracker's
shared sidebar markup are real pages/routes not relisted here; this
tree was never fully kept in lockstep with how many standalone pages
this app grew to. Treat it as a map of major subsystems, not a
literal, current `ls -R`.)
Data Sync Pipeline
The proofiq-daily-sync Railway service's configured start command is node backend/windsor-adapter.js 90, not daily-sync.js, despite the service name and that file's continued existence in the repo. The 90 argument is now effectively ignored: SYNC_LOOKBACK_DAYS (a Railway variable on that service, currently 365) is checked first and wins if set -- see below. It runs on a cron schedule (currently 6am UTC) set in the service's own Settings, and separately on every push to main (Railway triggers a fresh deploy attempt for every service watching the repo, and a deploy of a non-web service means "run the start command once"). For each client in CLIENT_WINDSOR_MAP it:
- Pulls ad platform spend (whichever channels the client has account IDs configured for) via Windsor, upserts into
channel_spend - Pulls HubSpot contacts and deals via Windsor (3-tier field fallback if the account is missing optional fields), classifies each contact's channel via the attribution rule engine, upserts into
contacts/deals - Pulls GA4 traffic via Windsor (clients with
has_ga4), upserts intoga4_traffic
Cold email sync (InboxIQ) and tier backfill are separate, triggered from /api/admin/sync rather than the cron job.
syncMediaPlanActuals (lib/mediaPlanSync.js) now runs in the same per-client loop as the cold-email/tier-backfill steps: for every Budget Plan row with a channel_key set, it sums that channel's real channel_spend by month and overwrites actual_amount, so a strategist never has to hand-enter a mapped row's actuals. Rows without a mapping are untouched -- their actual-spend inputs stay manual. syncColdEmailCampaignStatus, syncColdEmailLeads, syncColdEmailInboxes, and syncColdEmailLeadEngagement (the last one calling Smartlead directly per client, see Known Issues for the endpoint it replaced) also joined this same route's flow, alongside the pre-existing rollup sync. The route's JSON response gained a mediaPlanActuals field reporting rows updated.
POST /api/admin/sync is now gated by users.can_run_sync (migrate-v2-sync-permission.js), not just the existing agency-admin check -- as more sync steps landed in one route (spend, HubSpot, GA4, cold email, media plan actuals), triggering it casually got more expensive, so it needed its own narrower permission separate from general agency-admin access. Existing agency_admin accounts were grandfathered to true at migration time; a new agency admin needs the flag explicitly set. Checked in routes/admin.js (if (!req.user.can_run_sync) return res.status(403)...) and reflected in the frontend by hiding the "Sync all clients" control for a user who lacks it.
Found while investigating why GK3 Capital's Meta leads (real, captured via HubSpot's native Lead Gen Form integration, classified correctly by the attribution rule engine as meta) had no matching spend anywhere on the Performance or Attribution pages. The cause: nobody had ever wired up a Meta/Facebook branch in the sync code at all, for any client, not just GK3 -- windsor-adapter.js and /api/admin/sync only ever fetched google_ads, linkedin, hubspot, and ga4 from Windsor. Every client's meta field in client_windsor_config.js was null and had no code path to act on even if it weren't.
Both sync entrypoints now fetch a facebook connector (Windsor's slug for Meta/Facebook & Instagram ads, confirmed directly against the live Windsor.ai account this project uses) when a client's config has a meta account ID set, and write the rows under channel key 'meta' (not 'facebook') so it matches the existing HubSpot lead-side classification and the frontend's channel display map, which both already expected meta.
Still blocked: checking the live Windsor.ai account directly (via its MCP connection) confirmed no Meta/Facebook ad account has ever been authorized there, for GK3 or any other client -- this isn't a missing account ID, it's a connector that was never connected. Someone with access to the relevant Meta Business Manager needs to complete Windsor's OAuth flow (https://onboard.windsor.ai/connect?connector=facebook&next=/facebook/authorize) before any client's meta field in client_windsor_config.js can be filled in with a real account ID. The sync code is ready and waiting; it does nothing until that connection exists.
Same gap as Meta, but these three already had real connected Windsor accounts for GK3 Capital sitting completely unused (confirmed via Windsor's own account list) -- no OAuth wait needed, just missing sync code. Field names are not uniform across connectors: Bing matches Google Ads' generic date/spend/clicks/impressions/conversions, but Spotify's fields are Campaign-level and prefixed (campaign_spend, not spend), and Vibe (a CTV/audio platform) has no click metric at all and uses number_of_leads instead of conversions. Both are remapped to the generic shape upsertChannelSpend expects right after the Windsor fetch.
Fixed same day: the first live sync after wiring these up returned 0 rows for both Spotify and Vibe, each with a real validation error from Windsor: Spotify rejects any requested window of 90 days or more, Vibe rejects anything before 45 days ago. Both entrypoints previously passed one platform-wide dateFrom (up to 365 days via SYNC_LOOKBACK_DAYS) to every connector uniformly. Added a capDateFrom(maxDays) helper to both windsor-adapter.js and /api/admin/sync that clamps to the more recent of the requested window or that connector's real limit (89 and 44 days respectively, staying one day inside the stated limit to avoid boundary-off-by-one errors), threaded through as an optional override param on windsorFetch. Verified against a real run: Spotify and Vibe both synced real rows (59 and 45 respectively) for GK3 once capped.
Worth checking a brand-new connector's actual date-range tolerance directly (via a get_data call or a first live sync) before assuming the platform-wide lookback window is safe -- Windsor doesn't apply per-connector limits consistently, and a silent 0-row result with no visible error is easy to mistake for "no spend data" rather than "request rejected."
Revolution FMO is a new client whose whole paid-media presence is 13+ individually-branded agents (e.g. "Suncrest Wealth - Fred Adler"), each running Simplifi display campaigns, with more agents added regularly. Unlike every other channel in ProofIQ, this doesn't fit the one-Windsor-account-per-client model at all: there's a single shared Simplifi account (Windsor id 399872) that also carries campaigns for STP, Tortoise Capital, Equiton, Donoghue Forlines, and GK3 Capital's own marketing, all in the same account, distinguished only by a prefix in campaign_name.
backend/config/simplifi_campaign_map.js is the real mapping (prefix -> client, and for Revolution FMO, prefix -> agent), checked longest-prefix-first so a short client prefix can never shadow a more specific one (e.g. GK3 Capital_Donoghue Forlines must resolve before the shorter GK3 Capital_ catches it as GK3's own campaign). backend/lib/simplifiSync.js fetches the whole shared account once (not per-client) and distributes rows by that mapping, wired into both windsor-adapter.js and /api/admin/sync as a step after the normal per-client loop (in the manual sync route, only in "sync all clients" mode, since it isn't scoped to any single client).
Two new columns on channel_spend, agent_name/firm_name (migration: migrate-v2-agent-name.js), are populated at sync time from the matched config rather than re-parsed from campaign_name at every report query -- makes per-agent grouping a plain GROUP BY agent_name.
Any campaign whose prefix isn't in the map yet is logged, not silently dropped: printed to the sync's own console output and recorded in sync_log as sync_type = 'simplifi_unmatched' (client_id null), so a newly-added agent or client shows up as a visible gap instead of vanishing. The first real production run surfaced 22 campaigns the initial mapping had missed entirely (STP under three different naming variants, an unrecognized "CAI Investments", and GK3 Capital's own un-prefixed campaigns) that a smaller manual data sample hadn't captured -- confirms it's worth checking sync_log after adding a new client/agent here rather than assuming a clean first pass.
GET /api/v2/attribution/agents?orgId=&from=&to= returns spend/leads/CPL grouped by agent_name, empty array for any client without agent data (checked via WHERE agent_name IS NOT NULL, not hardcoded to Revolution FMO). Performance page shows an "Agent performance" card automatically whenever this returns rows: a table of agents with a combined summary row, plus a PDF link per agent and one for the all-agents summary. Leads come from channel_spend.conversions directly (Simplifi's own on-platform lead metric), not the contacts table the channel-level query uses -- agent-based clients here have no CRM connected at all.
GET /api/export/pdf detects an agent-based client the same way (real EXISTS check, not a hardcoded org id) and branches to sendAgentPDF(), which reuses generatePDF()'s existing media-mode renderer unchanged: agents (or one agent's own campaigns, via ?agentName=) are shaped into the same {name, spend, credit, roas, creditShare} rows the channel table already expected, so no second renderer was needed, just a rowLabel/tableTitle override on top of the existing "Channel" labels.
Real campaign names (e.g. "Suncrest Wealth - Fred Adler_3PS_Display_8/10/26-11/10/26") ran well past the table's fixed single-line row height and visually overlapped the row below once wired up -- channel names never hit this since they're short by construction ("Google Ads", "LinkedIn"). Fixed two ways in the single-agent view: the longest common prefix shared by that agent's own campaigns (their own name, already in the report title) is stripped before rendering, and anything still over ~34 characters is truncated with an ellipsis as a safety net.
New shared backend/lib/pdfHelpers.js (header/KPI-row/table/footer PDFKit primitives) backs both. Deliberately kept separate from routes/export.js's existing generatePDF()/sendAgentPDF() -- that renderer stays untouched (beyond a one-line "VisionIQ" → "ProofIQ" header-text fix caught while in the file) so this addition carries zero regression risk to the two already-working Attribution/Agent exports. A future export should build on pdfHelpers.js too rather than hand-rolling another one-off PDFKit layout.
GET /api/v2/campaign-metrics/pdf?orgId=&from=&to=&channel=: same optional channel filter as the page itself, sourced from the same campaign_metrics table the page's own /campaigns endpoint reads. When filtered to one channel, CHANNEL_BONUS_COL (mirroring the first entry of campaign-reporting.html's CHANNEL_EXTRA_COLS per channel) adds that channel's headline extra metric as a bonus column -- video completion rate for Google Ads, audio plays for Spotify, etc. -- rather than a generic column set every channel shares.
GET /api/v2/attribution/pipeline-pdf?orgId=&from=&to=: channel spend/clicks/leads/cost-per-lead plus the lifecycle funnel by channel (MQLs/SQLs/Opportunities/Customers). Deliberately doesn't re-derive tier-aware revenue -- that's already covered by the existing Attribution PDF for the same client and date range, so this complements it rather than duplicating it.
Campaign Reporting's own export (above) also picked up two more tables when Revolution FMO's agent data exists: Agent Performance (spend/leads/CPL, same query as Performance's own agent card) and Agent Website Traffic (GA4 sessions/engagement) -- in addition to those pages' own cards, not instead of them, per direct confirmation. The Wealth Score Assessment funnel (below) is not currently included in this PDF -- only shown on-page.
A view inside Campaign Reporting, not a separate sidebar page -- a toggle at the top of the page ("Campaign Performance" / "Wealth Score Assessment") switches between the existing view and this one. The toggle itself is only shown when the selected client's org id matches REVOLUTION_FMO_ID (hardcoded in campaign-reporting.html, same UUID as REVOLUTION_FMO_CLIENT_ID elsewhere) -- this funnel only ever has data for that one client's 13 agent sites.
Tracks Revolution FMO's finance-score-calculator WordPress plugin, which fires three GA4 events per agent site: assessment_start, assessment_50_percent, assessment_complete. New table assessment_funnel_events (client_id, agent_name, date, event_name, event_count) is a different shape from ga4_traffic's channel-group session rollup, so it's its own table rather than another column there. lib/ga4Sync.js's new syncAgentAssessmentEvents() pulls every event name from each agent's GA4 property (no server-side event_name filter -- unconfirmed whether Windsor's REST endpoint supports a filter param the same way the MCP get_data tool does, so all events are pulled and reduced to the three wanted ones in-process) and is called from the same per-agent loop in windsor-adapter.js that already syncs session data, reusing REVOLUTION_FMO_AGENTS' ga4Account mapping -- no new config needed.
Field names verified live against Windsor before building (a first-draft handoff brief for this feature guessed eventName/eventCount): the real ones, confirmed via get_fields, are event_name and event_count. A live get_data call against Marathon Group Financial's connected GA4 account (546713321), filtered to the three assessment event names, ran clean and returned zero rows -- consistent with the tracking plugin not being deployed yet, not a field-name or query problem.
GET /api/v2/campaign-metrics/wealth-score-funnel?orgId=&from=&to= returns per-agent starts/half/complete plus a network-level rollup, with completion_rate and dropoff_rate computed server-side. Empty agents array for every client other than Revolution FMO, same pattern /agents and /agent-ga4 already use.
Demo mode is a manual, clearly-labeled checkbox (WEALTH_SCORE_DEMO_DATA, a static illustrative dataset matching the agent names in REVOLUTION_FMO_AGENTS) -- deliberately never an automatic fallback when real data is empty, so a strategist can't mistake sample numbers for real ones. The page shows an explicit "no assessment events synced yet" note instead when real data is empty and demo mode is off. Once the plugin ships and events start flowing, the same endpoint starts returning real rows with no code change needed.
GK3-only as of this build -- not exposed to Revolution FMO's own client login, per direct confirmation.
Every other client has exactly one GA4 property (client_windsor_config.js's ga4 field, a single account id). Revolution FMO's 13 agents each run their own site, so this needed a second shape entirely: REVOLUTION_FMO_AGENTS in config/simplifi_campaign_map.js gained a ga4Account field per agent (the same array that already carried agentName/firmName for Simplifi's campaign-prefix matching) rather than trying to force this into CLIENT_WINDSOR_MAP, which Revolution FMO was never part of (it has no google_ads/linkedin/hubspot concept at all -- handled entirely via the dedicated Simplifi-shared-account block in windsor-adapter.js).
ga4_traffic gained a nullable agent_name column (null for every other client) and its unique index changed from (client_id, date, channel_group) to (client_id, date, channel_group, COALESCE(agent_name, '')) so multiple agents' rows for the same client/date/channel don't collide. lib/ga4Sync.js's syncClientGA4() takes an optional agentName, threaded through by a new block in windsor-adapter.js that loops REVOLUTION_FMO_AGENTS and skips any agent whose ga4Account is still null (4 of 13 as of this batch: Suncrest Wealth, MTD, Orfin Associates, Knepp Financial -- not yet connected on Windsor's side, confirmed live against Windsor's own connector account list, not guessed).
GET /api/v2/attribution/agent-ga4?orgId=&from=&to= rolls up sessions/users/engagement/conversions per agent from ga4_traffic, same "empty array for every client without agent data" pattern /agents already uses. Shown as its own "Agent website traffic" card on Performance, deliberately not merged into the existing agent spend/leads table -- different data source (GA4 vs. ad spend) and a different question (site engagement vs. cost per lead).
New tab, deliberately independent of HubSpot/lead-cost reporting (that's what Attribution/Pipeline/Performance already cover): real campaign-level impressions, clicks, video/audio engagement, view-through conversions, and platform-specific engagement (LinkedIn dwell time, likes/comments/shares) for Google Ads, Bing, LinkedIn, Spotify, Vibe, and Display (Simplifi). Meta is deliberately not covered yet -- no Windsor account connected as of Aug 2026, so there was nothing real to verify field names against; adding it later is a config change, not a rebuild, once that connection exists.
New campaign_metrics table, a different table from channel_spend on purpose: every existing channel_spend sync (google_ads, linkedin, bing_ads, spotify, vibe, meta) requests date/spend/clicks/impressions/conversions with no campaign dimension at all, so channel_spend.campaign_name has been null for every row except the Simplifi shared-account sync (which needs it to split one account across several clients). campaign_metrics is the first sync path with real campaign-level granularity for every platform, and named columns for metrics common enough across platforms to be worth one (view-through conversions, video views/quartiles/completion, audio plays/quartiles, engagements/likes/comments/shares, dwell time, viewability), plus a raw_metrics JSONB column for whatever's platform-specific beyond that -- a new field showing up on one platform doesn't force a schema migration.
Every field name was verified directly against the live Windsor account before writing any sync code, not guessed: Google Ads reports video engagement as quartile rates (video_quartile_p100_rate), LinkedIn reports raw quartile counts (quartile_1/2/3) plus real dwell time and engagement in milliseconds/seconds, Spotify's fields are Campaign-level and prefixed the same way its channel_spend sync already handles, Bing only exposes basic video view counts (no quartile breakdown), and Vibe has no audio metrics at all (a CTV/video-only platform despite "Vibe" sounding audio-adjacent). Simplifi additionally splits into separate video/audio sub-campaign tables with real data confirmed for at least one GK3 campaign, deferred here to keep the initial build's scope bounded -- basic display metrics (spend, view-through conversions) for Simplifi are covered.
backend/lib/campaignMetricsSync.js holds the real per-channel field lists and mapping functions (CHANNEL_CONFIG); wired into windsor-adapter.js's per-client loop right after each channel's existing channel_spend call. GET /api/v2/campaign-metrics/channels and /campaigns (both ?orgId=&from=&to=, /campaigns also takes an optional &channel=) serve the new campaign-reporting.html page, which shows only the KPI cards and table columns that channel actually has non-null data for, rather than a wall of blank cells for metrics that channel can't report.
coldEmailSync.js matches InboxIQ's export rows to a ProofIQ org by client_id (InboxIQ's own slug) against organizations.slug. A row with no matching slug is silently dropped -- console.log only, nothing persisted. Fixed (Aug 2026): withSyncLog only ever wrote rows_synced; rows_skipped stayed at its DB default (0) regardless of what actually happened, so the audit trail could never have surfaced a real mismatch on its own. Now records a real rows_skipped count when a sync function returns one.
Verified directly against InboxIQ's own data at the time: STP Investment Services is stpis on InboxIQ's side but stp-investment in organizations.slug -- STP already had campaigns configured there with zero touch events logged, meaning the moment that changed, rows would have vanished with no signal at all. Added a hardcoded SLUG_ALIASES map in coldEmailSync.js for known mismatches (currently just stpis -> stp-investment) rather than trying to reconcile slug conventions between the two systems. Add an entry there for any future mismatch found the same way -- don't assume a 0 in rows_skipped means nothing was missed; it only reflects sync runs since this fix shipped.
windsor-adapter.js resolves its lookback as process.env.SYNC_LOOKBACK_DAYS || process.argv[2] || '90' -- the env var takes priority specifically so the window can be changed (or temporarily widened for a one-off backfill) via railway variable set without touching the service's dashboard-configured start-command argument, which isn't reachable from the CLI. Currently set to 365 on proofiq-daily-sync, so both the daily cron and any future one-off deploy pull a full year.
Unlike HubSpot contacts/deals (which can go back years via a one-off full-history pull -- see the diagnostic query under Database Schema), ad platform spend and GA4 have never had deeper history than whatever the lookback window covered on the day they were last synced. Widening SYNC_LOOKBACK_DAYS going forward doesn't retroactively backfill anything on its own -- it takes one full sync cycle (cron or a manual railway up --service proofiq-daily-sync --ci) to actually pull the wider range, since the sync is what writes the rows.
GET /api/v2/attribution/channels returns earliestSpendDate (the real MIN(date) from channel_spend for that client) and spendDataGap (true if the requested from is earlier than that). The Attribution tab and Pipeline page both surface a note when this is true, so a genuine sync-coverage gap -- a client onboarded less than a year ago, a channel added recently -- reads as "data isn't synced back that far" rather than a silent, misleading $0.
From the local backend/ directory, with DATABASE_URL and WINDSOR_API_KEY set:
node windsor-adapter.js 90 # 90 = lookback days, ignored if
# SYNC_LOOKBACK_DAYS is set in the env
For local runs against production data, DATABASE_URL needs the Postgres service's public connection string (DATABASE_PUBLIC_URL in Railway Variables on the Postgres service), not the internal one: the internal hostname only resolves inside Railway's network. Easiest via the Railway CLI without ever printing the secret:
railway link -p cozy-stillness -e production -s Postgres railway run --service Postgres bash -c \ 'DATABASE_URL="$DATABASE_PUBLIC_URL" node windsor-adapter.js 90'
To run a real deploy on Railway itself instead (correct internal DB access, real WINDSOR_API_KEY already configured, no public-URL juggling) -- useful for a one-off backfill after changing SYNC_LOOKBACK_DAYS:
railway link -p cozy-stillness -e production -s proofiq-daily-sync railway up --service proofiq-daily-sync --ci
The regular cron (windsor-adapter.js) already calls syncCampaignMetrics on every scheduled run, but only over its normal 90-day SYNC_LOOKBACK_DAYS window -- so the Campaign Reporting tab only ever had real per-campaign history back to whenever that feature shipped. This script re-runs just the campaign_metrics half of the pipeline (not channel_spend, HubSpot, GA4, Simplifi, or cold email, which already have their own history and don't need re-work) over a much wider date range, for every client in CLIENT_WINDSOR_MAP.
node backend/db/backfill-campaign-metrics.js [dateFrom=YYYY-MM-DD] [orgId]
upsertCampaignMetrics is an idempotent ON CONFLICT upsert, so this is always safe to re-run, including over ranges it already backfilled.
Realistic date range, learned live: a 2015-01-01 window (matching HubSpot's existing fullHistory convention) was tried first and never completed -- Google Ads/LinkedIn's Windsor connectors took several minutes on just one channel for one client at that range, unlike HubSpot's CRM-record date filter which doesn't care how far back it goes. 365 days completes reliably (a 730-day window already made LinkedIn hang on a single client). Spotify (dateCap 89) and Vibe (dateCap 44) are always clamped via the same capDateFrom logic as the regular cron sync, regardless of the requested dateFrom -- those two connectors hard-reject an out-of-range window instead of truncating.
Already run once against production with a 365-day window: 13,170 total rows across all 15 clients (some Google Ads accounts returned real history going back to 2017 on their own, since Windsor doesn't error on a request that predates an account's retained data -- it just returns whatever actually exists). The Campaign Reporting tab's existing "Last 12 months" date-range preset already surfaces this without any frontend change.
Connector Account ID format STP example google_ads XXX-XXX-XXXX 456-062-3625 linkedin numeric org ID 505509869 hubspot numeric portal ID 20095280 ga4 numeric property ID 271459244
backend/config/client_windsor_config.js matches the real organizations.id for that client, not just that a UUID is present.Windsor URL format matters: https://connectors.windsor.ai/{connector}?api_key=...&account={id} -- the connector name is a path segment, and the parameter is account, not account_id. Getting this wrong returns a 404 with no data and no obvious error in the sync logs.
Windsor connects an account the moment it's authorized on their side -- that doesn't mean CLIENT_WINDSOR_MAP has been updated to actually pull it. The Windsor MCP connector's get_connectors tool lists every account with a live connection; cross-referencing that list against the map surfaced 9 existing clients with a real, connected account sitting unused (GA4 for The Bascom Group, Stone Coast, FGG1031, Donoghue Forlines, Pursuit Funds, RiemerPlus, Tortoise Capital, and Blackworks Capital; LinkedIn + Spotify for Valoran Capital; Vibe for STP and Tortoise) plus 7 connected accounts with no matching organizations row at all (Growth 1031, Kopernik Global, Teucrium, AXS Investments, PTAM, Tradr ETFs, Society Hill Capital).
New client orgs were created with the same INSERT INTO organizations (name, slug, type, primary_color) VALUES (..., 'client', ...) the POST /api/admin/clients route itself runs -- no admin user or invite email yet, since real contact info wasn't in hand; those get added later through the normal "New client" / "Add user" UI once a real relationship exists.
Newly-connected accounts don't always mean new data: three of the newly-wired GA4 properties (Donoghue Forlines, RiemerPlus, Blackworks Capital) returned zero sessions even over a 2-year window when checked directly against Windsor -- likely a tracking tag that isn't firing yet on a newly-created property, not a wiring bug. Confirm with get_data on the specific account before assuming a "0 rows" sync result means something's broken.
After changing client_windsor_config.js, run node backend/windsor-adapter.js 90 once (see "Running the sync manually" above) to pull the normal 90-day window for every channel/client, then backend/db/backfill-campaign-metrics.js scoped to just the newly-wired ad channels (not GA4 -- that's not part of campaign_metrics) to bring their campaign-level history up to the same depth as everyone else's.
Attribution Rule Engine
Engine lives in backend/lib/attributionRules.js, called once per contact at sync time from hubspotSync.js. attributed_channel is written once to the contacts row and never recomputed afterward -- there is no live "run attribution" step, no model parameter, and nothing cached separately from the contact row itself.
classifyContact(fields, clientRules) evaluates, in order:
- Any active per-client override rows in
attribution_rules(priority ascending, first match wins) GLOBAL_DEFAULT_RULES-- a fixed priority list checked top to bottom, starting with the hand-curatedsecondary_referral_sourcefield (most reliable when present), thenrecord_source_detail_1,gclid, and variousutm_source/utm_mediummatches- Falls through to
attributed_channel = 'unknown',is_paid_lead = falseif nothing matches
hs_analytics_source = 'OFFLINE' (CRM imports, manual entries) -- not a bug, just the reality of how most contacts actually enter a financial services CRM.Fixed (Aug 2026): original_source = 'PAID_SEARCH' had no fallback rule mapping it to a channel, unlike ORGANIC_SEARCH/DIRECT_TRAFFIC right next to it -- any contact HubSpot itself tagged as paid-search-sourced but with no gclid/utm_source fell through to unknown. Platform-wide: 939 of 1,997 PAID_SEARCH contacts were affected (most visible on Equiton, Valoran Capital, Donoghue Forlines). Added a rule mapping it to google_ads (the dominant paid-search platform across clients; override per-client via attribution_rules if a client's paid search is genuinely on Bing). Since the sync upsert recomputes attributed_channel on every run, this self-healed for all contacts within the 365-day lookback on the next nightly sync -- no backfill script needed. Other original_source enum values with no rule and meaningfully lower volume (SOCIAL_MEDIA, REFERRALS, EMAIL_MARKETING, OTHER_CAMPAIGNS, AI_REFERRALS) were deliberately left unmapped, same as the file's existing convention for ambiguous sources -- flag to Andy before adding channel mappings for those.
GET /api/v2/attribution/revenue branches on organizations.tier:
- Tier 3 (has real closed deals): sums
deals.amountfor closed-won deals, grouped byCOALESCE(contact.attributed_channel, deal.attributed_channel). Deals have no reliable contact association from Windsor's hubspot connector (no deal->contact ID field exposed), so most fall back to the deal's ownhs_analytics_source-- which HubSpot itself leaves blank/OFFLINE for the majority of closed-won deals (verified: 79% for STP). Deals that can't be attributed to any channel are summed separately asunattributedrather than bucketed into a fake "unknown" channel row. The same response also carriesdealStats: { median_deal_value, max_deal_value }-- a dedicated per-deal query (PERCENTILE_CONT/MAX) across all closed-won deals in range, independent of the channel-grouped rollup above (needed since that rollup excludesunknownand is grouped, so it can't answer a per-deal question). Powers the Conversion Anchor panel's Median/Highest Deal Value and the equivalent PDF export section, both of which showed "--" before this existed. - Tier 2 (HubSpot contacts, no reliable deal data): estimated as contacts reaching Customer lifecycle stage ×
organizations.avg_deal_value. That value defaults to 0 for any client with no real deal history yet (it's only ever set automatically from real closed deals via the tier-backfill job) -- see Deal Settings below for the admin UI that lets a strategist set it by hand instead of waiting. - Tier 1 (no HubSpot): no revenue concept at all -- paid media metrics only.
The same endpoint also computes pipelineStale: true if a tier-3 client has never once closed a deal, or a tier-2 client has never had a contact reach Customer stage. The frontend uses this to switch the Attribution tab and Pipeline page to a media-performance view (spend/leads/CPL) instead of a misleadingly-empty revenue view.
PUT /api/admin/clients/:id accepts an optional avgDealValue body field (alongside the pre-existing name/primaryColor/logoUrl), validated non-negative, written via COALESCE($n, avg_deal_value) so omitting it leaves the value untouched. Logged to audit_log as update_avg_deal_value with { targetOrgId, avgDealValue } in metadata (following the codebase's org_id convention -- that column is the acting user's org, the affected client goes in metadata, not the audit row's org_id itself). Frontend at view-deal-settings in index.html reuses the existing GET /api/admin/clients list (already returns tier/avg_deal_value) rather than a dedicated endpoint. The nav entry existed since Aug 19 with no view or backend behind it -- clicking it threw Cannot read properties of null -- before this was built out.
Stored values look like "Sales Qualified Lead" or "Customer" (Title Case, spaces) -- not a lowercase-no-space enum. Any query filtering on this column needs LOWER(REGEXP_REPLACE(lifecycle_stage, '[ -]', '', 'g')) normalization, or it silently matches nothing. This bit both the funnel query and the tier-2 estimated-revenue query before being fixed.
API Reference
POST /api/auth/login
Body: { email, password }
Returns: { token } | { requires2fa: true, tempToken }
| { requires2faSetup: true }
POST /api/auth/verify-2fa
Body: { token: tempToken, code: totpCode }
Returns: { token }
POST /api/auth/forgot-password Body: { email }
POST /api/auth/reset-password Body: { token, newPassword }
POST /api/auth/change-password Body: { currentPassword, newPassword }
POST /api/auth/setup-2fa Returns: { secret, qrCodeUrl }
POST /api/auth/enable-2fa Body: { code }
POST /api/auth/disable-2fa Body: { password }
POST /api/auth/logout
GET /api/auth/me Current user + org infoA full MCP server (@modelcontextprotocol/sdk) so any GK3 employee can connect their own Claude (claude.ai, Desktop, or Code) to ProofIQ and ask about client data directly. Separate auth model from the normal browser session: personal access tokens (mcp_tokens table, raw token shown once at creation, only its hash stored) managed from Settings > Connect Claude under normal requireAuth, but the actual MCP endpoint (POST/GET/DELETE /api/mcp) authenticates via requireApiToken -- a Bearer token, since an external Claude client has no browser cookie. Every tool call re-checks the calling token's user against the same allowed_client_ids/role scoping resolveOrgScope enforces for the dashboard -- a token can never see more than that employee's own ProofIQ login already can. Stateless transport: one server+transport pair per HTTP request, no session id.
Almost every tool is read-only: list_clients, get_client_account_mapping, get_channel_summary, get_campaign_breakdown, get_ad_breakdown, get_inboxiq_summary, get_insights, the full set of get_ga4_* and get_hubspot_* tools, get_zip_codes_near_location, check_domain_availability, get_negative_keyword_suggestions. Two deliberate write exceptions: register_domain (spends real money via GK3's Spaceship account -- agency_admin only, requires the domain list confirmed twice, audit-logged, emails a notification) and decide_negative_keywords (pushes an approved negative keyword live to a real Google Ads account, EXACT match only -- agency staff only, audit-logged, the tool's own description instructs Claude to get an explicit human yes on the exact term/numbers before calling it with approve:true). See Database Schema for mcp_tokens/negative_keyword_decisions.
GET /api/v2/search-terms?from=&to=&channel=&campaign= (gated behind the campaign-reporting tab) serves search_terms -- real per-query search-term-level data, Google Ads and Bing Ads only (the only two platforms with a real query report API; backend/lib/searchTermsSync.js has one fetch function per channel rather than going through the generic CHANNEL_CONFIG dispatcher). Google Ads rows also carry the numeric account_id/campaign_id/ad_group_id (not just names), needed to actually address an ad group when pushing a negative keyword.
backend/lib/negativeKeywordSuggestions.js's generateSuggestions() is pure read/analysis: flags a Google Ads search term as a candidate negative when it has real spend/clicks (default: 3+ clicks) and zero conversions over a lookback window (default 30 days), upserts it into negative_keyword_decisions as pending (re-running never resets an existing decision). decide() is the only function that can call googleAdsNativeClient.js's addNegativeKeyword mutate call, and only for a decision already marked approved by a real user -- always pushes EXACT match, so an approval can only block the one query that wasted spend, never a broader swath of real traffic. Surfaced entirely via the MCP server above (get_negative_keyword_suggestions / decide_negative_keywords) -- no dashboard UI for this as of this writing.
An agency_admin can scope a user down to specific tabs, using two nullable columns on users: allowed_client_ids UUID[] and allowed_tabs TEXT[] (added by backend/db/migrate-v2-user-access-scope.js, no schema change needed for the Sep 2026 extension since both were already generic, unconstrained arrays). NULL means unrestricted. As of Sep 2026, allowed_tabs restriction is no longer agency_member-only: it applies to any role except agency_admin, i.e. agency_member, client_admin, and client_viewer can all be tab-restricted now. allowed_client_ids stays agency_member-only (it's meaningless for a client-org user, who's already locked to their own org -- see below).
allowed_client_ids is the real, fully-enforced security boundary -- and it was never the gap. isClientAllowed(user, orgId) in backend/middleware/auth.js is checked by resolveOrgScope before any client-scoped request proceeds, and directly by GET /api/admin/overview and GET /api/admin/clients/:id. More fundamentally, resolveOrgScope (backend/middleware/auth.js:102-120) forces req.targetOrgId = req.orgId for any non-agency org type regardless of what orgId a request claims to want -- a client_admin/client_viewer can never reach another org's data no matter what's sent, restricted or not. The Sep 2026 work was purely about which tools a client user sees, not data scoping, which was already correct.
allowed_tabs is mostly a UI affordance for agency users (report data endpoints are already client-scoped regardless of which tab claims to be asking) but is the actual point for client users: an agency admin can now hide, say, everything under "Under Development" from a given client login. The one side-effect-bearing exception remains POST /api/crd-match/upload-token, gated with requireTabAccess('crd-match'). Tab keys match route paths; see TAB_OPTIONS in frontend/index.html for the current full list (14 entries as of Sep 2026, including content-calendar and paid-media-tracker).
Frontend: every standalone page's own loadSidebarUser() plus frontend/index.html's enterApp() call applyTabRestrictions(user, thisTabKey), which hides sidebar links carrying a non-matching data-tab-key and redirects to / if the current page itself isn't allowed. The role gate changed from u.role === 'agency_member' to u.role !== 'agency_admin' in all ~14 copies of this function (one per page -- this codebase has no shared JS module, each standalone page duplicates its own sidebar/auth JS). GET /api/auth/me and the login/2FA/refresh responses in backend/routes/auth.js changed the same way (4 call sites) so allowedTabs actually reaches a client-role user instead of always coming back null.
Setting restrictions: POST /api/admin/clients/:id/users (create) and PUT /api/admin/users/:userId (edit) both accept optional allowedClientIds/allowedTabs. The create route's gate is role !== 'agency_admin' for tabs, role === 'agency_member' for client ids (unchanged, explicit guard so a client-role user can never get client-ids stored even if something sent them). The edit route already stored whatever was sent unconditionally when the key was present (via hasOwnProperty, not truthiness, so unrelated edits like password/2FA changes never clear an existing restriction) -- it now also explicitly checks role === 'agency_member' before storing client ids, closing the same gap on that route.
Admin UI (frontend/index.html): the shared "New User" modal (#modal-new-user, used for both Team and Client Users) now shows the restriction picker in client mode too (onNewUserRoleChange(), createUser()), with the "restrict to specific clients" half of the picker hidden in client mode (irrelevant, see above). openEditUserModal(user) was generalized from agency-only to branch on the target's actual role, and the Client Users table (loadUsers()) got an Edit button + openEditClientUserModalById() -- there was previously no way to edit an existing client user's role or restrictions at all. A single "Under Development (all tools below)" master checkbox in the tab picker (onUnderDevMasterChange/onTabCheckChange, driven by a group: 'underdev' field on the relevant TAB_OPTIONS entries) is a pure UI convenience over the same allow-list -- it doesn't add any new stored state.
Separate from allowed_tabs: a .admin-only class marks nav items/actions gated by requireAgencyAdmin regardless of any per-user restriction (Client Users, Deal Settings, AI Usage, Data Sync, Team, Audit log). applyAdminOnlyGating(user) hides every .admin-only element for any role other than agency_admin. Security stays visible for everyone since it manages the signed-in user's own account, not a client's.
Client selection moved from a topbar-right dropdown into a persistent sidebar section, placed directly above Agency (Agency was later removed entirely, Sep 2026 -- see the Nav Restructure entry below; the client selector itself didn't move again), styled to match InboxIQ's layout. On the five standalone pages that existed at the time (performance.html, campaign-reporting.html, pipeline.html, crd-match.html, ppc-builder.html) this is a straight relocation, not a new control: the same <select id="clientSelect"> element and its existing onchange handler (loadData() or onClientChange()) just live inside a new #nav-client-selector div now, wrapped in .sidebar-client-select styling instead of .client-select. No JS logic changed on those five pages. Visibility was tied to the same #nav-agency show/hide calls that existed at the time (agency users only); #nav-agency itself no longer exists as of Sep 2026 (its remaining useful contents, Team and Audit log, moved into Admin -- see below), so this visibility toggle now runs off a generic .agency-only class instead. audience-match.html has no client selector at all (its tools aren't client-scoped) so it only received the theme toggle.
frontend/index.html is the one exception, since it never had a single unified client-select: it gets a new #sidebarClientSelect element, populated in loadOverview() alongside the pre-existing Attribution-tab #attr-client-select (which still exists, unchanged, inside the Attribution view's own header), and its onchange calls the same onAttrClientSelect(clientId) function that dropdown already used. openClient() keeps both selects' values in sync. The Overview page's "All clients" tile grid was removed in Sep 2026 (see Nav Restructure below) -- this paragraph's original note that it "was left as-is" no longer applies.
Theme toggle: a sun/moon icon button next to the ProofIQ wordmark, same row, right-aligned via justify-content:space-between on .sidebar-logo (the logo+wordmark got wrapped in a new .sidebar-logo-mark span so the button could sit as a second flex child rather than splitting from the wordmark). Every page already had a fully-built [data-theme="dark"] CSS variable block from the original build, but nothing had ever wired anything to set the attribute; the toggle was net-new, not a restoration -- confirmed by grepping the full git history for any prior toggle implementation and finding none.
toggleTheme() flips document.documentElement's data-theme attribute and persists the choice to localStorage['proofiq-theme'] (each of the 7 pages is a separate HTML document, not an SPA, so persistence has to be per-browser rather than in-memory). The saved preference is re-applied via a tiny inline <script> placed in <head>, right after <title>, so data-theme="dark" is set before first paint and there's no flash of light theme on load; a second small script right before </body> syncs the sun/moon icon's visibility to whatever the head script already applied, since the icon elements don't exist yet when the head script runs. Binary light/dark only, no prefers-color-scheme fallback -- the CSS was never built with a media-query default, so today the site is always light unless a saved preference says otherwise.
Applied identically across all 13 standalone pages plus index.html's own sidebar markup (this codebase has no shared nav component; every page still carries its own copy).
Agency section removed. The "All clients" link/button and the "Agency" section header are gone -- "All clients" pointed at a homepage that no longer shows a client tile grid (replaced with a static welcome message, see below), so it stopped doing anything a strategist couldn't already do via the sidebar client selector. Team and Audit log were still real, functional, agency-admin-only features, so they moved into the Admin section instead of disappearing, gated by a new .agency-only class (toggled the same place #nav-agency's show/hide used to run) layered on top of the existing .admin-only role gate -- a client-org user still never sees them, an agency_member never sees them either (agency-admin-only, unchanged).
Homepage ("All clients" view) replaced with a welcome message. index.html's #view-overview no longer renders #client-grid; it's now a static "Welcome to ProofIQ, by GK3 Capital" card with a short how-to-navigate blurb. loadOverview() still fetches /api/admin/overview (needed to populate the client selectors and _overviewClients, used elsewhere e.g. the New User modal's client picker) but no longer builds any tile markup from the response. The "New client" and "Sync all clients" actions that lived in the tile grid's header lost their spot; "Sync all clients" was given a permanent home as its own card in the Data Sync admin tab (reusing the existing runAllClientsSync() unchanged); "New client" (openNewClientModal()) and prospect-promotion (promoteProspect()) are currently unwired dead code, left in place rather than deleted in case they get a new home later.
"Under Development" collapsible section (#underDevItems/#underDevToggle, toggleUnderDev()) groups Attribution, Performance, Pipeline, Lead Simulator (etf-simulator.html), Deal Settings, Data Enrichment (crd-match.html), and DemandIQ Compliance (demandiq-clean.html) at the bottom of the sidebar, collapsed by default, expanded state persisted per-browser via localStorage['proofiq-underdev-expanded']. All existing ids, data-tab-key attributes, and admin-gating classes were preserved on every moved link -- only physical position and default visibility changed, so tab-restriction logic and direct-navigation behavior are unaffected. Deal Settings has no data-tab-key (it was never restrictable per-tab, unchanged) so it isn't included in the "Under Development" master-checkbox convenience toggle in the New User modal (see the tab-restrictions entry above) -- only the six tab-keyed items are.
Admin section made collapsible too (#adminItems/#adminToggle, toggleAdminSection(), localStorage['proofiq-admin-expanded']), same collapsed-by-default pattern, independent state from Under Development. Billing was moved to sit inside Admin (previously listed just above it, outside any Admin/agency gating) as part of the same pass, before Under Development existed.
Pre-existing drift found and fixed along the way (unrelated to the restructure itself, just surfaced while touching every page's sidebar): campaign-reporting.html and audience-match.html were each missing several Admin-section links entirely (Client Users/Deal Settings/AI Usage/Data Sync on campaign-reporting; AI Usage/Data Sync on audience-match) -- confirms the standing risk noted elsewhere in this doc that a mechanical "edit all N pages" change reliably finds at least one page that already silently drifted from the rest.
New client-scoped page, sidebar under Activation. backend/routes/contentCalendar.js, tables content_calendar_items, content_calendar_checkpoints, content_calendar_inputs, content_calendar_shares (migrations: migrate-v2-content-calendar.js, migrate-v2-content-calendar-share.js). POST /api/content-calendar/generate pulls the client's 5P/ICP (server-side proxy to crd-match's /api/visitor-audit/5p/:orgId, same shared-secret-token pattern backend/routes/admin.js already used for usage-report) plus any attached content_calendar_inputs briefs (uploaded via backend/lib/contentInputParse.js -- generic xlsx/docx/pdf/csv/txt-to-text, new dependencies exceljs, mammoth, pdf-parse, multer; exceljs chosen over the more common xlsx package specifically because xlsx pulls in a known high-severity prototype-pollution/ReDoS CVE and this endpoint parses untrusted uploads), and prompts Claude (plain fetch to the Messages API, no SDK, matching insights.js's existing convention in this repo) for a JSON array of dated items.
Date serialization bug (found and fixed same day): node-postgres parses DATE columns into JS Date objects, which res.json() serializes as full ISO timestamps ("2026-10-06T04:00:00.000Z") instead of "YYYY-MM-DD". The frontend calendar grid's date-bucketing (and its fmtDate() string-concat parsing) expected the plain form, so every generated/manual item silently failed to match any day cell -- confirmed live against a real client (Equiton, 30 real generated items, 0 visible). Fixed with a normalizeItem()/normalizeCheckpoint() helper applied at every response point in the route file (list, create, update, checkpoints, generate, and the public share endpoint) rather than a global pg type-parser override, to keep the fix scoped to this one route instead of changing date-handling behavior for the rest of the app.
The page defaults to an actual month-grid calendar view (a List/Calendar toggle keeps the old flat list available); clicking a chip jumps to and flashes the corresponding list row. GET/POST/DELETE /api/content-calendar/share manage a durable, revocable share token (crypto.randomBytes(24).toString('hex')) per org; GET /api/content-calendar/public/:token is deliberately unauthenticated (no requireAuth/resolveOrgScope) -- the whole point is a link anyone can open, scoped to exactly the one org the token was minted for, serving a stripped-down read-only payload (no inputs, no org settings). frontend/content-calendar-share.html is the standalone public page, and GET /content-calendar/share/:token in server.js serves it.
New client-scoped page, sidebar under Activation. backend/routes/paidMediaTracker.js, table paid_media_plan_items (migrate-v2-paid-media-tracker.js).
Phase 1 (instrumentation, no new UI): the existing campaign-level ad-platform syncs never requested ad/creative-level fields even though Windsor already exposes them per-connector (verified live: Google Ads ad_id/ad_name/ad_group_name; LinkedIn creative_id/sponsored_creative_content_title; Spotify ad/ad_entity_id; Vibe creative_id/creative_name). New backend/lib/creativeMetricsSync.js requests these into a new creative_metrics table, sibling to (not a replacement for) campaign_metrics, so nothing reading campaign-level data changes behavior. Same pattern for GA4: new ga4_campaign_traffic table carries source/medium/campaign/ad_content (utm_content) breakdown via syncClientGA4CampaignDetail() in lib/ga4Sync.js, alongside the existing channel-group-only ga4_traffic, unchanged. Both new tables added by migrate-v2-creative-tracking.js; wired into windsor-adapter.js alongside each existing campaign-level sync call. Also wired up HubSpot's already-fetched-but-dropped utm_term and the never-fetched utm_content into contacts (columns pre-existed in schema, unpopulated). Bing has no Windsor account connected for this login yet, so its fields are unverified pending that connection. Every path verified live against real GK3 Capital data before deploying.
Phase 2 (pre-launch plan UI): CRUD over paid_media_plan_items -- channel, campaign name, creative/ad name, planned utm_source/utm_medium/utm_campaign/utm_content, launch date, status (planned/live/paused/ended), notes. Column names deliberately mirror the UTM vocabulary creative_metrics/ga4_campaign_traffic already use so Phase 3 can cross-reference directly.
Phase 3 (post-launch validator): GET /api/paid-media-tracker/validate?days= checks every non-planned item against creative_metrics/ga4_campaign_traffic/contacts, matching on campaign_name (exact, case-insensitive, for ad-platform) or the planned utm_campaign (ILIKE, for GA4/HubSpot -- loose on purpose since hand-typed UTM values won't always match byte-for-byte), plus a specific check for the planned utm_content against ga4_campaign_traffic.ad_content/contacts.utm_content when set. Returns found/spend/impressions/sessions/last-seen per item; the frontend renders it as a simple ✓/✗ table with supporting detail, no new tables of its own.
New "Budget Plan" tab on the same Paid Media Tracker page, deliberately separate from paid_media_plan_items (the UTM-tagging spec above) -- different job: this tracks planned-vs-actual spend over a plan's lifetime, that tracks tagging correctness. backend/routes/mediaPlan.js, three new tables (migrate-v2-media-plan.js): media_plans (one per client per plan period, start_month + num_months rather than a fixed calendar year, since real client plans don't all start in January or run exactly 12 months -- confirmed against two real client files, a 12-month Jan-Dec grid and a 16-month Sep-2026-through-Dec-2027 flowchart), media_plan_rows (channel, optional sub-label, optional channel_key mapping to channel_spend.channel), media_plan_months (planned + actual per row per calendar month).
AI-assisted import (backend/lib/mediaPlanImport.js): real budget files vary in shape (sectioned vs. flat, "Jan"/"Sep 26"/"Sep 2026" date labels, narrative content mixed into the numeric grid), so this hands the extracted file text (via the existing contentInputParse.js) to Claude and asks for a structured JSON mapping, rather than a positional parser tied to one layout. POST /api/media-plan/import returns the proposed structure unsaved; the strategist reviews/edits it in a preview screen (including per-row channel-key assignment); POST /api/media-plan/import/commit writes the confirmed version. A "Section" field/column existed briefly (mirroring one source file's grouping) and was removed after the fact -- added width without earning it; the DB column is still there, nullable, always null going forward.
Auto-fill actuals from Windsor (backend/lib/mediaPlanSync.js): a row with channel_key set gets its actual_amount overwritten from real channel_spend (summed by month) on every "Sync all clients" run, wired in after the existing per-client channel syncs and the shared Simplifi block complete. Unmapped rows (flat-fee line items, channels Windsor doesn't carry) stay manual: the grid shows an always-visible actual-spend input under every planned amount (not just via the separate "Log actuals" modal, which still exists for entering a whole month across every row at once), saving on blur via PUT /rows/:rowId/actuals.
Linked to the UTM Plan, one-way: paid_media_plan_items.media_plan_row_id (nullable FK, ON DELETE SET NULL -- deleting a budget row unlinks its UTM items rather than destroying real tagging records). A single budget row commonly maps to several real campaigns/creatives, so only channel cascades from row to linked items on a row edit (UPDATE paid_media_plan_items SET channel = ... WHERE media_plan_row_id = ...); campaign_name/UTM fields stay strategist-owned per item, there's no sensible reverse mapping for those. POST /:planId/generate-utm-items backfills a linked draft item for every row in a plan that doesn't have one yet (needed because linking only ever happened going forward -- a plan imported before this existed, or a row added manually, had nothing bridging it to the UTM Plan at all; this is exactly what left the UTM Plan completely empty for every client the first time it was checked).
Full-funnel QA, replacing the old stateless validator with real history: new table paid_media_qa_runs (one row per check per item). POST /:id/qa-check runs the same ad-platform/GA4/HubSpot matching /validate already did, plus a new landing-page-resolves check (backend/lib/landingPageCheck.js -- real HTTP HEAD/GET against a new paid_media_plan_items.landing_url column, following up to 3 redirects, falling back to GET when a server rejects HEAD), and records every run rather than only ever showing "right now." GET /:id/qa-history returns the last 20.
Pulling real UTM values from GA4 instead of typing them in: ad platforms (creative_metrics) never carry raw UTM parameters, only GA4 does (ga4_campaign_traffic.source/medium/campaign/ad_content are literally GA4's own session_manual_* dimensions, already synced via Windsor). GET /:id/ga4-suggestions surfaces real combinations matched loosely by campaign name, ranked by session volume, for one item. GET /ga4-suggestions-bulk does the same across every item at once but only returns a preview (top match + an ambiguous flag when more than one distinct combo matched); POST /apply-ga4-suggestions is the separate confirmed write, since a blind bulk auto-pick risks silently overwriting an item that already has confirmed UTM values or guessing wrong on an ambiguous match -- the frontend defaults the apply-checkbox off for any item that already has UTM values set.
New client-scoped page, sidebar under Reporting next to Campaign Reporting -- frontend/inboxiq.html, backend/routes/coldEmail.js (mounted at /api/inboxiq). InboxIQ (the separate cold-email product) already fed a daily rollup into cold_email_campaigns via coldEmailSync.js; this is the first place that data (plus leads and inbox health, newly synced) gets its own reporting UI inside ProofIQ rather than requiring a second login.
Two new tables (migrate-v2-coldemail-inboxiq.js): cold_email_leads, cold_email_inboxes, plus a status column added to the existing cold_email_campaigns. Three new sync functions in coldEmailSync.js (syncColdEmailCampaignStatus, syncColdEmailLeads, syncColdEmailInboxes), each pulling from a new InboxIQ export endpoint (/api/sync-export/campaigns, /leads, /inboxes -- same X-Sync-Secret machine-to-machine pattern as the existing rollup export), wired into the same "Sync all clients" flow as the daily rollup.
Real per-lead engagement status (syncColdEmailLeadEngagement, backend/lib/smartleadClient.js, backend/config/client_smartlead_map.js): cold_email_leads.status initially only ever carried Smartlead's raw sequence-progress state (STARTED/COMPLETED/BLOCKED), never real engagement (Opened/Clicked/Replied/etc.), because InboxIQ's own cache doesn't reliably carry that either. Fixed by having ProofIQ call Smartlead directly with each client's own API key (env vars SMARTLEAD_API_KEY_<CLIENT>, never in source -- config only maps org id to env var name) and overlay the result onto cold_email_leads.status. Verified live that the endpoint this used to use is dead: /campaigns/{id}/leads?lead_status=X now returns 400 ("lead_status is not allowed") -- the real endpoint is /campaigns/{id}/statistics?email_status=<lowercase>, confirmed against Donoghue Forlines' real campaign (157 opens, 148 clicks came back correctly where the old endpoint silently returned zero for everything). InboxIQ's own nightly-sync.js still uses the dead parameter with a swallowed catch, which is almost certainly why no client's cold-email leads ever had a real engagement status to begin with, independent of anything built here.
New "Insights" sub-tab: pipeline matching (emailed leads vs. real HubSpot contacts, matched by email -- computed from ProofIQ's own already-synced contacts, not a second copy of HubSpot data), red-flag campaigns, rankings by open rate, best-performing subject lines (a live proxy to InboxIQ's own /subject-lines, which calls Smartlead live, no cache table for this on either side). Overview gained a Deliverability Score (A-F, ported from InboxIQ's own client-side scoring logic) and a rules-based Recommendations panel, both computed client-side from data the page already has.
New page, sidebar under a new "Resources" section -- frontend/guides.html, backend/routes/guides.js, table guides (migrate-v2-guides.js). Not org-scoped: shared reference guides/sell sheets, visible and downloadable by every authenticated user regardless of role, since this is platform-wide documentation rather than per-client data. Upload/edit/delete is agency_admin only (requireAgencyAdmin on those three routes; GET / and GET /:id/download are plain requireAuth).
PDF bytes are stored directly in Postgres (file_data BYTEA), not on local disk -- Railway's filesystem isn't persistent across deploys/restarts, and these files are small enough that a new object-storage dependency wasn't worth adding. PUT /:id accepts multipart form data with the file field optional, so the same route handles metadata-only edits (title/category/description) and file replacement identically.
GET /api/v2/attribution/channels?orgId=&from=&to=
Returns: { channels: [{ channel, total_spend, total_clicks,
total_impressions, leads_generated, cost_per_lead }],
earliestSpendDate, spendDataGap }
Spend and leads are aggregated independently then joined by
channel key, never row-joined -- a direct join fans out spend
once per matching contact and inflates totals by an order of
magnitude on any channel with real lead volume.
earliestSpendDate/spendDataGap: see "Lookback window" above.
GET /api/v2/attribution/funnel?orgId=&from=&to=
Returns: { funnel: [{ attributed_channel, subscribers, leads,
mqls, sqls, opportunities, customers, total_contacts }] }
Paid leads only (is_paid_lead = true).
GET /api/v2/attribution/revenue?orgId=&from=&to=
Returns: { tier, mode: 'actual_deals'|'estimated', revenue: [...],
unattributed?, pipelineStale, pipelineStaleReason }
See Attribution Rule Engine above for the tier branching.
GET /api/v2/attribution/cold-email?orgId=&from=&to=
Returns: { campaigns: [...] } -- from InboxIQ sync, real data
only exists for clients InboxIQ has actually synced.
GET /api/v2/attribution/contacts?orgId=&from=&to=&limit=&offset=
Returns: { contacts: [{ id, email, first_name, last_name, company,
create_date, attributed_channel, is_paid_lead,
lifecycle_stage, crd_number, crd_confidence,
crd_verified_at }], total, limit, offset }One shared implementation (insights.js, calls the Anthropic API directly) behind two different prompts, selected by a context field: the Attribution tab's credit/spend model-comparison framing (default, context omitted or 'attribution'), and a Performance-page framing added Aug 2026 (context: 'performance') built from real spend/leads/CPL, CRM funnel stage counts, and tier-aware revenue instead of attribution-model credit. Both write to the same insights_cache table, keyed by (org_id, model) -- the Performance page passes model: 'performance' so its cached row never collides with the Attribution tab's (which uses the attribution model name, e.g. 'linear').
POST /api/insights/generate
Body (attribution, default): { orgId, model, channelCredits,
channelSpend, hubspotSummary?, summary? }
Body (performance): { orgId, model: 'performance',
context: 'performance', channels, funnel, revenue }
-- channels/funnel/revenue are passed through verbatim from the
/api/v2/attribution/channels, /funnel, /revenue responses already
loaded on the page, no separate aggregation needed client-side.
Returns: { insights, model, generatedAt }
Calls Anthropic directly (ANTHROPIC_API_KEY), then caches the result.
GET /api/insights/cache?orgId=&model=
Returns: { insights: null } or { insights, generatedAt }
Read on page load so a client with recent insights doesn't trigger
a fresh (slower, costs tokens) generation just from opening the page.
Claude's own writing style leans on the em dash character by default, which conflicts with ProofIQ's house style, and doesn't reliably follow a plain style instruction on its own -- the route strips any em dash from the response as a fallback (replaced with a comma) regardless of what the prompt asked for. The prompt also explicitly tells the model not to give each insight a bolded headline: earlier drafts produced a bold one-line header followed by a blank line before the real paragraph, which the frontend's paragraph splitter (blank-line-separated) rendered as two separate insight blocks instead of one.
Narrow, scoped surface for the standalone crd-match Railway project/DB -- ProofIQ never shares raw DB credentials with it. Shared-secret header pattern (X-Match-Secret, env var MATCH_SECRET), same pattern used for InboxIQ's export endpoint.
POST /api/crd-match/upload-token (normal session auth)
Body: { orgId }
Returns: { token, orgId, expiresIn: 900 }
Issues a 15-minute JWT signed with MATCH_SECRET, containing
{ orgId, purpose: 'crd-upload', issuedBy }. The frontend passes
this token directly to crd-match's own POST /api/upload as the
X-Upload-Token header -- ProofIQ never touches the uploaded file.
GET /api/crd-match/contacts?orgId=&limit=&offset= (X-Match-Secret)
Returns: { contacts: [{ id, email, name, firm }], total, limit, offset }
What crd-match's own service calls to fetch match candidates.
POST /api/crd-match/results (X-Match-Secret)
Body: { updates: [{ contact_id, crd_number, crd_confidence }] }
(max 500/request; crd_confidence must be a JSON number 0-1,
not a string -- node-postgres returns NUMERIC columns as
strings by default, which bit the crd-match side once)
Writes crd_number/crd_confidence/crd_verified_at onto contacts.
Vendor file parsing status (per the Source dropdown on the upload card): FINTRX and Discovery Data validated against real vendor exports. AdvisorPro validated as of Aug 2026, after fixing two real bugs found against a real AdvisorPro file: its export is a genuine two-sheet workbook (Person + Firm, different column schemas) and sheet 2's rows were silently being parsed using sheet 1's field mapping (e.g. a phone number landing in the email field); it also has 2 metadata rows before the real header row, where the parser had assumed row 1 was always the header. Dakota Data has not yet been validated against a real export -- treat results from a Dakota Data upload with more scrutiny than the other three until someone runs a real file through it.
Same crd-match service, same POST /api/crd-match/upload-token above -- client-scoped like CRD Verification (the token's orgId claim must match the :orgId in the 5P routes below, 403 otherwise). No ProofIQ-side route was added; the frontend calls crd-match's origin directly. Field names below verified directly against production (Aug 2026), not just from the crd-match session's description. As of Sep 2026, ProofIQ's own ICP Library tab (frontend/icp-library.html) is the only page that calls the write endpoints below; Visitor Fit Scoring and PPC Builder only ever call the GET below now, for a read-only summary.
GET /api/visitor-audit/5p/:orgId (X-Upload-Token)
Returns: { exists: false, source: null } or
{ exists: true, source: 'file'|'text', filename, icp_text,
uploaded_at, include_wealth_fields }
filename is set only when source is 'file'; icp_text only when
source is 'text' -- exactly one of the two is ever populated.
POST /api/visitor-audit/5p/:orgId (X-Upload-Token)
Multipart form. Send EITHER field: file (PDF or plain text) OR
field: icp_text (plain string) -- a file takes priority if somehow
both arrive. Replaces whatever's on file for that org, including
switching source (a text-based ICP fully replaces a prior uploaded
file and vice versa; the old Claude-side file is deleted when
replaced by either mode). Returns: { exists: true, source, filename }
or { exists: true, source, icp_text }.
DELETE /api/visitor-audit/5p/:orgId (X-Upload-Token)
Returns: { exists: false }. Works for either source.
PATCH /api/visitor-audit/5p/:orgId/config (X-Upload-Token)
Body: { include_wealth_fields: true|false }
A display hint only, not a data gate (changed Aug 2026): net_worth,
income_range, age_range, homeowner, has_children, marital_status,
city, state, home_address, personal_phone are carried through for
every client now, whatever the source pixel row actually has --
they're the data used to judge fit and reach a good match, not
incidental PII to withhold. This flag still switches which columns
the on-screen results preview emphasizes and hints Claude's scoring
toward wealth signals as primary criteria for HNW/individual-investor
clients (e.g. Growth1031) whose real ICP isn't job title/company.
POST /api/visitor-audit/upload (X-Upload-Token)
Multipart form, field: file (CSV, the pixel/IP-matching export
as-is). 400 { error: "No 5P document on file for this client --
upload one before running an audit" } if no 5P exists for that org.
Returns: 202 { job_id }
GET /api/visitor-audit-jobs/:id (no auth -- job_id UUID is the
access boundary, same pattern as CRD Verification's job status)
status: pending | processing | completed | failed
pending/processing: { id, status, total_rows, audited_rows, error,
created_at, started_at, completed_at } -- total_rows populates
once the file finishes parsing; audited_rows increments live as
concurrent batches complete (real progress, not just a spinner).
completed: adds results: [{ first_name, last_name, business_email,
business_verified_emails, job_title, company_name, company_phone,
linkedin_url, fit_score, fit_tier, rationale, ...consumer fields
above, null only when the source row didn't have them }], sorted
by fit_score DESC. A non-null error
on a completed job means some (not all) batches failed --
surface as a soft warning, not a failure; results still reflect
whatever succeeded.
failed: only when every batch failed (or nothing to batch at all,
e.g. no 5P). No results key.
GET /api/visitor-audit-jobs/:id/export?format=csv|docx|xlsx|auto
csv: raw rows. docx: one page per prospect (name/title/company,
every contact method, fit tier + score, rationale). xlsx: one row
per prospect, tier-tinted, frozen header. auto (default): docx
below VISITOR_AUDIT_DOC_THRESHOLD matched prospects (default 50),
xlsx above it.
**Field retention changed Aug 2026 (Andy's explicit direction, not a code
default):** lib/pixelReduce.js (crd-match) previously dropped every
consumer-PII field (home address, net worth, marital status, personal
phone) unconditionally, gating them behind the include_wealth_fields
opt-in above. That's reversed now: reduceConsumerFields() in that file
runs unconditionally for every client, and only SHA256 hash columns and
internal tracking IDs are ever dropped. Added marital_status and
home_address as new fields (visitor_audit_results columns, Claude's
output schema, both export renderers) since neither existed at all
before, even under the old opt-in. HOME_ADDRESS/STREET_ADDRESS/ADDRESS/
MAILING_ADDRESS are tried as candidate column names (same
defensive-fallback pattern already used for personal_phone) since the
exact vendor column name for a full street address hasn't been
confirmed against a real export yet -- verify against the next real
pixel file that has it.
No enforced row-count limit as of Aug 2026 -- only a shared
MAX_UPLOAD_BYTES cap (default 500MB) across all crd-match uploads.
Real measured throughput: ~10s per 20 rows at concurrency=5 (up from
~49s/20 rows sequential before batch concurrency shipped) -- a file
of a few thousand rows can still genuinely take over an hour. Don't
hard-code a client-side row cap against this number; ask the
crd-match session for the current figure before relying on it.
Other honest known limitations as of Aug 2026, not fixes -- current
state:
- VISITOR_AUDIT_CONCURRENCY=5 is logically verified, not load-tested
against Anthropic's real rate limits at that concurrency. 429s under
real volume are expected troubleshooting territory, not a surprise.
- Batch API pricing is still a proposal under discussion with Andy,
not built. Don't assume uploads are being discounted today.
- Any $/row cost figures floating around are estimated from typical
token counts, not measured from a real large-scale run -- don't
present them as guaranteed billing numbers in anything client-facing
until someone measures a real one.
PROPOSED, NOT BUILT: a pre-qualification filter. Deterministic,
rule-based code, not a cheaper-model pass -- $0 cost, no LLM call,
runs before a row ever reaches Claude. Drops a row if it has no usable
contact info (no email, no phone), and, only for clients with the
wealth-fields toggle on, if it falls below a minimum net-worth/
qualification threshold. Mirrors what Growth1031's own reference
deliverable already does by hand: their file shows 2,383 raw records
filtered to 973 qualified ones, a 59% reduction, on exactly this kind
of contact-info + net-worth-floor logic.
Estimated impact (cost figures from typical per-row token counts, not
a measured large-scale run -- flag that caveat wherever these numbers
get used), using the 59% Growth1031 filter rate as the reference
point, at VISITOR_AUDIT_CONCURRENCY=5:
- 250K rows: ~$1,250 unfiltered -> ~$510 filtered; ~14hrs unfiltered
at current concurrency
- 500K rows: ~$2,500 unfiltered -> ~$1,020 filtered
Tradeoff: a deterministic filter is blunt by nature -- the contact-info
and net-worth-threshold rules need real tuning per use case, and there
is no accuracy benchmark yet since it hasn't been built or tested. The
risk is filtering out a real borderline prospect, not a hallucination-
style risk, since no model is involved in the filter step itself. No
decision has been made to build this as of Aug 2026.A separate matching workflow hosted on the same crd-match service, not client-scoped, no ProofIQ contacts involved -- reuses the same POST /api/crd-match/upload-token above (call it with no orgId body, it defaults to the caller's own org via resolveOrgScope; the value isn't meaningful to audience-match's logic, the token is just proof of an authenticated ProofIQ session -- but the JWT still needs a real orgId claim structurally, a token minted with none is rejected). No ProofIQ-side route was added for this feature -- the frontend calls crd-match's origin directly for everything past the token.
Two modes as of Aug 2026, both confirmed against production. match (default) is the original symmetric two-list overlap. crd_enrich pulls CRD numbers from a licensed vendor reference file onto an arbitrary uploaded list (list_b) -- what Andy originally had in mind for this page. In crd_enrich, the reference side is either list_a (a CRD-bearing vendor file, FINTRX/AdvisorPro/Discovery Data/Dakota Data, auto-detected by header, same detection as CRD Verification -- deleted immediately after matching, same retention policy CRD Verification already has for licensed vendor data) or reference_list_id (a saved list's UUID, see Reference Lists below) -- exactly one of the two is required, nothing else changes. list_b and the results stay for the normal 48h window either way.
POST https://crd-match-production.up.railway.app/api/audience/upload
Multipart form, fields: list_b, mode (optional: "match" default, or
"crd_enrich"), and exactly one of: list_a OR reference_list_id
Header: X-Upload-Token (from the token endpoint above)
Returns: 202 { job_id }
GET https://crd-match-production.up.railway.app/api/audience-jobs/:id
No auth -- job_id UUID is the access boundary, same pattern as
CRD Verification's job status.
Returns: { id, mode, status, total_a, total_b, matched_email,
matched_domain, matched_fuzzy, matched_total,
match_rate, match_rate_b, error, created_at, started_at,
completed_at, reference_list_id, reference_list_name,
reference_list_vendor }
match_rate is matched_total/total_a; match_rate_b is
matched_total/total_b -- in crd_enrich mode, list_b is "my list"
and list_a is just the reference file, so use match_rate_b/total_b
as the denominator in any UI, not match_rate/total_a.
reference_list_id/name/vendor are only populated when the job used
a saved reference list instead of a fresh list_a upload.
status: pending | processing | completed | failed. No incremental
progress -- counts stay 0 until the job finishes, then jump to
final values.
GET .../api/audience-jobs/:id/export?platform=X&side=Y
No auth. platform: meta | linkedin-contacts | linkedin-company |
google-ads (also registered under youtube -- same underlying
Customer Match system, one format covers both) | spotify | vibe |
csv | crd-enriched. side: a | b -- which uploaded list to export
the matched subset from; not used for crd-enriched (400 if the
job's mode isn't crd_enrich).
crd-enriched returns every row from list_b, matched or not --
email, phone, first_name, last_name, full_name, firm_name, domain,
crd_number, match_method, match_confidence -- crd_number/
match_method/match_confidence blank where nothing matched.
Deliberately the full list back, not filtered to matches-only, so
someone can see verification status on every contact they uploaded
(confirmed directly with Andy).
X-Export-Warning response header (CORS-exposed via
exposedHeaders, readable through fetch()) is set when an export's
row count is under that platform's real documented minimum:
LinkedIn 300, Spotify 1,000, Vibe 20,000. The export still
completes and downloads either way -- this is a warning to surface
in the UI, not a block. ProofIQ's frontend fetches the export URL
live to read this header rather than duplicating the thresholds
client-side, so it can't drift if the real minimums change.
Streams a CSV (Content-Disposition: attachment) -- a plain <a href>
triggers the download client-side, no fetch+blob needed for the
actual download (the warning check above is a separate, extra
fetch purely to read the header).
Job data (and therefore exports) is purged 48h after completion.Persistent, named CRD reference lists on the crd-match service, so a client doesn't have to re-upload the same vendor file every time they run crd_enrich mode. Built and shipped Aug 2026. Same X-Upload-Token pattern as the rest of Audience Builder for the write endpoints; the list endpoint itself has no auth (matches the job-status pattern elsewhere on this service -- not org-scoped, any authenticated ProofIQ session can see all saved lists).
GET /api/reference-lists (no auth)
Returns: a bare array, not wrapped in an object --
[{ id, name, vendor, row_count, uploaded_at, updated_at }]
POST /api/reference-lists (X-Upload-Token)
Multipart form, fields: file, name, vendor. Vendor file parsing
reuses the same detection as CRD Verification/Audience Builder
(FINTRX/AdvisorPro/Discovery Data/Dakota Data, auto-detected by
header).
Returns: 202 { job_id }
POST /api/reference-lists/:id/refresh (X-Upload-Token)
Multipart form, field: file. Full replace of the list's rows --
not a merge/append. name/vendor stay as originally set.
Returns: 202 { job_id }
GET /api/reference-list-jobs/:id (no auth -- job_id UUID is the
access boundary, same pattern as the other job-status endpoints)
Poll this after POST /api/reference-lists or .../refresh.
status: pending | processing | completed | failed
DELETE /api/reference-lists/:id (X-Upload-Token)
Permanent -- no confirmation step server-side, the frontend's
delete button is the only guard.
To use a saved list in an enrichment job, pass its id as reference_list_id in POST /api/audience/upload instead of uploading a fresh list_a -- see Audience Builder above.
A genuinely different data shape from the pairwise match/crd_enrich modes above (N files in, one unioned+deduped list out), so it's a separate mini-schema on crd-match rather than a forced extension of audience_jobs/audience_rows/audience_matches: combine_jobs, combine_job_files, combine_rows. Ingestion only captures what this mode actually needs (Andy's explicit call): verified business email (falling back to verified personal email), first/last name, company, and LinkedIn URL -- not the dozens of columns some source exports carry, and no raw JSONB blob of the original row either.
POST /api/combine/upload (X-Upload-Token)
Multipart form, field: files (multer .array, max 10), field: name
(required -- validated client-side and server-side).
Returns: 202 { job_id }
GET /api/combine-jobs/:id (no auth -- job_id UUID is the access
boundary, same pattern as every other job-status endpoint here)
GET /api/combine-jobs (X-Upload-Token, unlike the single-job GET --
listing every recent job for an org needs a real token check, there's
no per-job UUID to prove access to the list itself)
Returns recent (limit 20, non-expired) jobs for the token's orgId,
newest first -- backs the "Past combines" UI.
GET /api/combine-jobs/:id/export?platform= (no auth, same as above)
platform: csv | meta | linkedin-contacts | linkedin-company |
google-ads | youtube | spotify | vibe | bing-ads | chatgpt
No "side" param, unlike Match two lists/CRD enrichment's export --
Combine only ever produces one deduped list.
Dedup runs two tiers, not three: exact email, then a fuzzy name+company pass -- deliberately no domain-based tier (unlike audienceMatcher.js's three-tier match mode). Shared company domain is a useful "these are probably the same entity" signal when comparing two separate lists, but it's the wrong signal when merging one list into itself: two different real people at the same company would get wrongly collapsed into one row.
Real production incident, not a hypothetical -- three separate bugs found and fixed the same day, in order:
1) The first fuzzy-dedupe query used similarity(a,b) > threshold across every possible row pair -- an O(n^2) self cross-join with zero index acceleration. Two real 9-file jobs (94K rows each) got stuck running it for 20+ minutes before being killed. A first attempted fix routed the comparison through a candidates CTE (mirroring matchReferenceListFuzzy's shape in audienceMatcher.js) and still didn't use the index: materializing both join sides as a CTE forces Postgres into a nested loop with % as a post-join filter, not an index condition (confirmed via EXPLAIN: ~100M cost) -- a third job got stuck on that shape too. The fix that actually works: a correlated EXISTS subquery directly against the base table, no CTE on either side -- confirmed via EXPLAIN: Bitmap Index Scan on idx_combine_rows_match_key_trgm, ~2.9M cost. No score/ranking needed either: dedup only needs "is there ANY sufficiently similar earlier row," not which one scores highest.
2) Even with the index fix, combine_rows had accumulated 565K rows across the 6 failed test jobs above (a failed job's rows were never cleaned up) -- confirmed via EXPLAIN that Postgres abandoned the job_id index entirely for a full Parallel Seq Scan once the table reached that size (a single-column index isn't selective enough when each job_id is only ~1/6 of the table). Fixed with a composite (job_id, id) index (idx_combine_rows_job_id_range) and worker.js now deletes a failed job's combine_rows immediately on failure instead of waiting on the 48h purge -- a failed job has nothing worth re-exporting.
3) With both of those fixed, a real 94K-row job (an actual DemandIQ export) still timed out on its very first 5,000-row batch specifically. Root cause: match_key is "name + firm", and a firm name shared by hundreds of rows (confirmed: "Citibank N.A. (California)" alone was 252 rows in that one batch, several other megabanks/brokerages in the 60-190 range) makes the whole string's trigram signature dominated by the long, invariant firm-name portion -- a firm with N rows contributes on the order of N^2/2 candidate pairs, and it's also a real correctness risk (two different Citibank employees could plausibly trigram-match each other on firm name alone). Fixed the same shape as the domain tier's CONSUMER_EMAIL_DOMAINS precedent in audienceMatcher.js: any firm name appearing more than COMBINE_FUZZY_DEDUPE_COMMON_FIRM_THRESHOLD (default 15, env-configurable) times within a job is excluded from the fuzzy tier for that job -- those rows still dedupe on exact email, just not fuzzy name+firm.
The fuzzy-dedupe query is also batched (COMBINE_FUZZY_DEDUPE_BATCH_SIZE, default 5,000 rows by id range) with a per-batch SET LOCAL statement_timeout (COMBINE_FUZZY_DEDUPE_BATCH_TIMEOUT_MS, default 60,000ms) as a standing safety net on top of the three fixes above -- a slow region of data can now only ever cost one batch, and a genuinely stuck batch fails loudly with a specific id range in the error instead of hanging indefinitely with zero signal. SET LOCAL doesn't accept a bound parameter (a real bug caught by this exact safety net on its first real run: failed in ~11s with a Postgres syntax error instead of hanging, exactly as designed) -- the timeout value is safely interpolated directly since it's a validated Number from config, never user input.
Export bugs, found via a real Vibe upload rejection, not assumption: Vibe's real template (audience_upload_template.csv, obtained directly from Andy) has headers email,phone,firstName,lastName,postalAddress,city,zipCode,state,device_id -- the version here was previously "confirmed live against their help center" but was still wrong (missing postalAddress/device_id entirely, state/zipCode reversed). Fixed to match the real template exactly, with all headers always emitted (unlike every other formatter's "only if a row has data" convention -- Vibe's own template shows genuinely sparse rows under a fixed full header set).
Fixing the header format alone didn't resolve the real upload error ("Missing or invalid identifiers... each row must have a valid email or a valid device_id"): nothing captured by any ingest path here ever populates device_id, so toVibeExportWithSkipped() (lib/exportFormats.js) now drops any row with no valid email from the Vibe export specifically, surfacing the dropped count via X-Export-Warning instead of silently shipping a file Vibe would reject outright.
That still wasn't the full story: the same rejection persisted after the above, traced (via a real CSV parser, not just grep -- 20,452 of 45,313 rows in one real export had this shape) to the source data sometimes packing multiple known emails into one field, e.g. "a@b.com, c@d.com", for a single contact. That value isn't blank, so it survived the "drop rows with no email" fix, but it also isn't a single valid email, which is exactly what Vibe's strict validator rejects. normalizeEmail() in lib/exportFormats.js now splits on ,/; and keeps only the first token that actually looks like one email -- this is the single shared function every formatter in the file calls, so it fixed every platform (Meta, Google Ads, Spotify, Vibe, ChatGPT, Bing), not just Vibe.
Separately, export download responses across both combineExport.js and audienceExport.js now send Cache-Control: no-store, and the frontend appends a &_ts=Date.now() to every export link -- a real incident during this same investigation: a fix was confirmed correct via a direct server-side check, but re-testing the exact same URL kept returning pre-fix bytes. (The two combined address both a browser/CDN layer that might cache a GET response with no explicit cache header, and any URL that was already cached before no-store existed on it.) Bing (Microsoft Advertising, real documented Customer Match column names) and ChatGPT (best-effort, unverified -- no downloadable template was available, plain lowercase email/phone, flagged in the dropdown) were added as export formats the same day, alongside a "Download all formats" option in every platform dropdown (ALL_EXPORT_PLATFORMS in audience-match.html, fetch+Blob+synthetic-click per platform with a delay between each -- triggering many downloads in the same tick gets browsers to start silently dropping them).
New sidebar page (frontend/icp-library.html, route added in backend/server.js), consolidating what used to be an upload control duplicated across Visitor Fit Scoring and PPC Builder into one canonical place to set a client's ICP. Same crd-match origin, same POST /api/crd-match/upload-token pattern, calls the same GET/POST/DELETE /api/visitor-audit/5p/:orgId and PATCH .../config endpoints documented above -- no new backend route, this is a frontend-only consolidation.
Visitor Fit Scoring and PPC Builder were both cut over to read-only: they still call the GET to show a summary card (file or pasted text, upload date, wealth-fields status), but no longer call the POST/DELETE/PATCH endpoints at all -- those calls, and the file-upload/paste UI that drives them, exist only in icp-library.html now. PPC Builder kept its own separate one-off paste flow (job-scoped icp_text on POST /api/campaign-builds, unrelated to client_5p_docs) unchanged, since that's a genuinely different feature (a per-build override that's never saved), not a second place to set the client's real ICP.
client_5p_docs.icp_text (crd-match schema change, same release) is what makes "paste text" a real persistent option here rather than only PPC Builder's ephemeral per-build one: claude_file_id/filename were relaxed from NOT NULL so a text-only row can exist, and exactly one of claude_file_id or icp_text is populated per client (app-enforced, not a DB constraint). lib/claudeAuditClient.js's auditBatch() and worker.js's campaign-build job both resolve whichever is set into a Claude document block (file) or plain text block (pasted), the same branching PPC Builder's per-build override already used.
Fourth Data I/O tool on crd-match, client-scoped like CRD Verification and Visitor Fit Scoring -- reuses the same POST /api/crd-match/upload-token (real orgId claim required) and the same 5P/ICP document Visitor Fit Scoring uses (GET /api/visitor-audit/5p/:orgId, see above). Built entirely on crd-match's side (lib/campaignBuildClient.js for the Claude-generated plan, lib/campaignBuildGoogleXlsx.js/campaignBuildBingXlsx.js/campaignBuildLinkedInXlsx.js for the three workbook exports, lib/keywordsEverywhere.js for real Keyword Planner volume/CPC data); frontend/ppc-builder.html is the only ProofIQ-side addition, no new backend route.
icp_text (added Aug 2026): an optional field on POST /api/campaign-builds that bypasses client_5p_docs entirely for that one build -- lib/campaignBuildClient.js's generateCampaignPlan() takes an options object now ({fivePFileId, icpText, clientName, websiteUrl}, not positional args) and passes whichever is set as either a Claude Files API document block or a plain text block. Never persisted beyond the one campaign_build_jobs row (icp_text TEXT column) -- uploading a 5P still writes to the shared client_5p_docs store, but a pasted ICP stays scoped to that single build so it can never silently overwrite a client's real saved ICP. frontend/ppc-builder.html checks for an existing 5P on client select and shows filename + upload date if one's on file, with an explicit choice to use it, upload a different one, or paste ICP text for just this build.
POST /api/campaign-builds (X-Upload-Token)
Body: { client_name, website_url, icp_text? }
icp_text omitted: 400 if no 5P doc on file for that org (same
precondition as Visitor Fit Scoring). icp_text present: no 5P
required, used directly for this build only.
Returns: 202 { job_id }
GET /api/campaign-builds/:id (no auth -- job_id UUID is the access
boundary, same pattern as every other job-status endpoint here)
status: pending | processing | completed | failed
used_pasted_icp: boolean -- which input this build actually ran on,
without echoing the pasted text itself back over this unauthenticated
route.
Once completed, response also includes plan (the full generated
campaign structure: campaigns/ad_groups/keywords/negatives/
sitelinks/linkedin_ads/persona_summary/compliance_notes).
GET /api/campaign-builds/:id/export?platform=google-ads|bing|linkedin|google-ads-editor|bing-bulk
409 if the job isn't completed yet: { error: "Job is <status>,
not completed yet" }. google-ads/bing/linkedin stream a real .xlsx;
google-ads-editor/bing-bulk stream a real .csv (different
Content-Type/extension, same endpoint).
/api/campaign-build-jobs/:id and shipped wrong on the frontend first. Confirmed directly against crd-match's server.js: both campaignBuilds.js and campaignBuildJobs.js are mounted at the same prefix, /api/campaign-builds -- caught via live testing (a real build ran to completion but the frontend's poll requests 404'd against the wrong path), fixed on ppc-builder.html. If any other integration work references "campaign-build-jobs", it's wrong; the real prefix is campaign-builds for every verb.Real end-to-end build time is genuinely 3-4 minutes (Claude opus-5 at high reasoning effort generating a full multi-campaign plan) -- not something to poll aggressively or assume is stuck. google-ads/bing/linkedin are review workbooks (real column names/terminology, verified against each platform's docs, but organized as separate per-entity-type tabs for a strategist to review, not the single linked-row file either platform's bulk-import tooling actually ingests).
google-ads-editor and bing-bulk (added Aug 2026) are the real import files, not more review documents -- lib/campaignBuildGoogleEditorCsv.js and lib/campaignBuildBingBulkCsv.js. Both verified directly against each platform's real docs (Google: support.google.com/google-ads/editor answers 56368/57747; Microsoft: learn.microsoft.com's per-record-type Bulk Service pages -- Campaign, Ad Group, Keyword, Campaign/Ad Group Negative Keyword, Responsive Search Ad, Sitelink Ad Extension, Campaign Sitelink Ad Extension), not assumed from the review workbooks' column names.
The two formats are structurally different from each other. Google Ads Editor's CSV is flat and name-matched: Campaign/Ad Group names are the join key, no numeric Id system at all, one shared header row (the union of every column any row type needs) with entity type inferred from which columns are populated per row -- Editor only accepts CSV, never XLS/XLSX. Microsoft's Bulk Upload file requires a Format Version header row first (value 6.0, placed under whatever column lands on Name in the shared header -- confirmed by counting columns against several of Microsoft's own real example rows, not guessed), a Type column identifying each row's record type, and a negative-integer Id/Parent Id reference-key system: every campaign/ad group/sitelink-extension gets its own synthetic negative Id via a simple shared decrementing counter, and child rows (ad group -> campaign, keyword/RSA/negative -> ad group, the sitelink association record -> both the extension's Id and the campaign's Id) reference their parent by that same negative number. Responsive Search Ad Headline/Description are each one CSV column holding a JSON-encoded array of {text} objects, not separate numbered columns the way the review workbook and Google's Editor CSV both use -- this was the single most important thing to verify directly rather than assume, since it's the opposite of every other export in this codebase.
Known, deliberate gap: the shared plan schema (lib/campaignBuildClient.js's CampaignPlanSchema) doesn't generate a campaign budget or explicit ad-group language, since neither the review workbook nor Google's Editor CSV require them up front. Microsoft's format requires both on Add (Budget/Budget Type on every Campaign row, Language on every Ad Group row unless set at the campaign level), so campaignBuildBingBulkCsv.js sets a conservative placeholder ($20/day, English) purely so the file imports at all -- flagged in the UI (ppc-builder.html's hint text under the download buttons) and in the file's own header comment, not a real recommendation. A strategist must review and adjust both before an actual Bulk Upload import.
No shared CSV-writing dependency existed in crd-match (csv-parse for reading, nothing for writing), and the escaping rules here are simple and well-defined (RFC 4180), so lib/csvWriter.js is a small hand-rolled helper rather than a new dependency -- verified against real generated output that it correctly double-escapes the RSA JSON fields' own embedded quote characters when they're wrapped in an outer CSV quote pair.
The LinkedIn export (added after the initial Google/Bing build) has a real platform limitation baked into its structure: LinkedIn's own bulk CSV import can create Campaign Groups and Campaigns but not ads at all, so the workbook's third tab is "Ad Copy (manual entry)" -- reference copy pasted in by hand per ad in Campaign Manager, not part of the bulk import.
Also confirmed and fixed the same day: VISIONIQ_APP_ORIGIN (crd-match's CORS allowlist variable, a Railway variable on the crd-match service, not in this repo) was still pointing at the old visioniq-production-0917.up.railway.app domain after the ProofIQ rebrand, silently blocking every cross-origin call from ProofIQ's frontend to crd-match with no error surfaced anywhere except the browser console (a CORS preflight failure, not an HTTP error status). Whenever ProofIQ's own domain changes again, this variable needs updating on crd-match too -- it isn't inherited or shared between the two services.
ppc-builder.html's "Generate a campaign build" card has a Google Ads / Microsoft Ads / LinkedIn checkbox row above Generate, all checked by default and remembered per client in localStorage['ppcLastChannels:'+orgId]. This is purely a frontend display filter, not a generation-time option: the Claude call and the Keywords Everywhere lookup already only ever ran once per build regardless of channel (one stored plan/keyword_data row backs every export format), so unchecking a channel only hides that channel's download buttons (and its native-import-file counterpart and channel-specific hint text) after the build completes -- it does not change what gets generated or what it costs. The selection made at build time is stored per job in localStorage['ppcBuildChannels:'+jobId] and read back wherever that job's downloads are shown (results card, saved-builds history, version history); a job with no stored selection (built before this existed) defaults to showing every format.
A build's plan/keyword data were already kept indefinitely (see the schema comment on campaign_build_jobs) so a job could be re-exported without re-paying for Claude + Keywords Everywhere, but nothing in the UI ever surfaced more than the single most-recent job id (kept in localStorage) -- navigating away or starting a new build effectively lost access to a finished one even though the data was never gone. Two new crd-match endpoints fix that, both scoped by the upload token's orgId rather than trusting a job id alone (unlike every other job route here, which treats the opaque UUID itself as the access boundary): GET /api/campaign-builds (list, newest first, max 50) and DELETE /api/campaign-builds/:id (ownership-checked against the token before deleting; cascades to campaign_build_results and campaign_build_plan_versions via ON DELETE CASCADE). ppc-builder.html's "Saved builds" card calls the list endpoint on every client switch and after every build completes.
Before this, the only way to change a generated plan was to regenerate it entirely or hand-edit an exported spreadsheet, which desynced it from what crd-match had stored -- the next re-download from ProofIQ reverted any manual spreadsheet edit. Both new edit routes write straight to campaign_build_results instead:
PUT /api/campaign-builds/:id/plan
Body: { plan, summary? }
plan must validate against the exact same CampaignPlanSchema a
fresh generation produces (lib/campaignBuildClient.js, now
exported for reuse) -- 400 with zod's issue list if it doesn't.
Manual edit: the frontend fetches the current plan via
GET /:id, lets a strategist edit specific fields in a form, and
posts the whole object back so every field the form doesn't
surface (ad_group_type, status, max_cpc, notes, persona_summary,
linkedin_ads) rides through unchanged.
POST /api/campaign-builds/:id/feedback
Body: { feedback_text }
feedback_text is the strategy team's own written review, pasted
in verbatim. lib/campaignBuildEditClient.js sends it to Claude
alongside the full current plan and gets back the complete
updated plan (same schema, same validateAndTruncatePlan() call
as generation) -- an edit, not a fresh generation, so a field the
feedback doesn't mention must come back unchanged. Verified live:
asked to remove one specific word from ad copy and compliance
notes, it reworded the affected copy naturally, left an untouched
keyword containing that same word alone since the feedback only
named descriptions/notes, and flagged that specific ambiguity in
compliance_notes rather than guessing silently either way.
Both routes archive-then-replace: right before overwriting campaign_build_results, its current contents (plan, keyword_data, version, edit_source, edit_summary, updated_at) are copied into the new campaign_build_plan_versions table under the version number they held, then the current row is updated with the new plan and version = version + 1. A plan nobody has ever edited has zero rows in the versions table -- its one and only version lives entirely in campaign_build_results, which is why GET /:id/versions unions that row in as the newest entry rather than requiring a versions-table row to exist for an unedited plan. keyword_data is carried over unchanged by both edit routes (never re-fetched from Keywords Everywhere) -- a keyword renamed or added by an edit just shows blank volume/CPC in the Keyword Estimates tab, the same tolerant fallback the export already has for any keyword Keywords Everywhere has no data for.
GET /api/campaign-builds/:id/versions
Returns: { versions: [{ version, edit_source, edit_summary,
created_at, is_current }] }, newest first.
edit_source: 'original' | 'manual' | 'ai'. edit_summary: the
strategist's own note (manual) or the raw pasted feedback text
(ai); null for 'original'.
GET /api/campaign-builds/:id/versions/:version/export?platform=...
Same five platform values and export logic as the un-versioned
/export route (both now call one shared sendExport() helper) --
read-only, for reviewing/downloading a past version. The current
version stays reachable at the un-versioned route either way.
express.json()'s default 100kb body limit was raised to 2mb on crd-match's server.js for this -- a full multi-campaign plan's headlines/descriptions/LinkedIn ads can exceed 100kb on its own once it's being PUT back whole rather than only ever read.
ppc-builder.html's Edit button (shown on any completed row in Saved Builds) opens a card with the AI feedback textarea, a manual form built from the fetched plan (one section per ad group: final URL/paths/headlines/descriptions/keywords, plus plan-wide negatives/sitelinks/compliance notes as line-delimited textareas with a field | field | field convention per line), and the version history list with per-version download links. The manual form's serialize/deserialize only ever touches the fields it renders, mutating the same fetched plan object in place, so anything the form doesn't show is never at risk of being dropped on save.
Feedback from an uploaded document (added Sep 2026): an "Or upload a feedback document" file input sits under the paste box, for when the strategy team's review arrives as an actual file (a PDF or plain-text memo) rather than something to copy/paste. POST /api/campaign-builds/:id/feedback-file (multipart, field file, .pdf/.txt only, 20MB max, same restriction as the 5P upload since Claude's document content block only reads those two formats natively) uploads the file to Claude's Files API (lib/claudeAuditClient.js's uploadFivePDoc(), reused as a generic single-file upload helper despite the 5P-specific name) and lib/campaignBuildEditClient.js's new applyPlanFeedbackFromFile() sends it as a Claude document content block alongside the current plan, same instructions and same schema-validated response as the pasted-text path (applyPlanFeedback() and applyPlanFeedbackFromFile() now share one BASE_INSTRUCTIONS template). Unlike a client's persistent 5P doc, this upload is a one-off: both the local temp file and the Claude-side file are deleted right after the call returns (success or failure), regardless of whether the request succeeded, since a feedback memo has no reason to persist once it's been applied. The resulting version's edit_summary is the uploaded filename (e.g. "Feedback document: Q3_review.pdf"), not the document's contents, since those were never extracted to text server-side.
Cost was previously only ever an estimate -- response.usage was returned by every Claude call in generateCampaignPlan()/applyPlanFeedback()/applyPlanFeedbackFromFile() but never read or stored. All three now return {plan, usage, model} instead of a bare plan, and a new crd-match table, campaign_build_usage, gets one row per Claude call (call_type: 'generate' | 'edit_feedback' | 'edit_feedback_file') plus one row per Keywords Everywhere lookup (call_type: 'keywords_everywhere'). lib/campaignBuildUsage.js's recordClaudeUsage()/recordKeywordsEverywhereUsage() write these from worker.js (the initial generation) and both edit routes in routes/campaignBuildJobs.js; a logging failure is caught and never fails the build itself, same reasoning as the existing KWE try/catch.
estimated_cost_usd is computed and stored at write time from a fixed per-model rate table in code (currently just claude-opus-5: $5/$25 per 1M input/output tokens), not derived at read time -- a rate change later shouldn't silently rewrite what a past build actually cost. Keywords Everywhere rows store keyword_count instead (1 credit per keyword, per its own documented pricing) and deliberately carry no dollar estimate: KWE is a credit-based subscription product, not billed per call, so a per-row dollar figure would be invented, not real. job_id uses ON DELETE SET NULL, not CASCADE -- deleting a build (the existing DELETE /:id) must not erase its cost history, so a usage row can outlive the job it came from; org_id is denormalized onto the row for exactly this reason.
GET /api/campaign-builds/usage-report?from=&to=&orgId= (X-Match-Secret)
Admin-wide, not the per-org X-Upload-Token pattern every other route in
this file uses -- there's no single org's claim to check for a report
that spans every client. Same MATCH_SECRET env var, direct compare
instead of JWT verification. Returns { rows: [...] }, raw and
unaggregated; ProofIQ's admin route does the grouping.ProofIQ side: GET /api/admin/campaign-build-usage (backend/routes/admin.js, requireAgencyAdmin) calls the endpoint above with X-Match-Secret (mirroring the existing reverse-direction pattern in routes/crd-match.js, where crd-match calls into ProofIQ instead), resolves each row's org_id against ProofIQ's own organizations table for the display name rather than trusting crd-match's copy of client_name (which can go missing on a deleted job's usage rows, by design), and returns summary/byClient/byCallType/rows. New sidebar entry AI Usage under Admin (frontend/index.html, admin-only) shows KPI totals, grouped-by-client and grouped-by-type tables, and a collapsed-by-default (same pattern as PPC Builder's Manual Edits section) list of individual runs.
Built for Revolution FMO's growth to 250+ individually-branded agents, each needing their own campaign build. First designed around each agent's zip-code list (already used for Simplifi ad-spend attribution, see the Revolution FMO/Simplifi entry under Data Sync), but that turned out not to be viable: Google's own Geo Target Constants file (checked live, 2026-08-12 edition, developers.google.com/google-ads/api/data/geotargets) marks every single US Postal Code criterion Removal Planned -- not hard to source IDs for, being retired platform-wide. Radius targeting around each agent's own office address is the real, currently-supported mechanism used instead, confirmed against both platforms' actual bulk-format docs before building (Google Ads Editor's proximity format, Microsoft's Campaign Radius Criterion record), not guessed.
New crd-match tables: campaign_build_agent_roster (per-client agent list: name, website, office address, geocoded latitude/longitude, radius in miles, default 35, active flag) and campaign_build_batches (groups the N per-agent jobs one "Run Batch Build" click creates). campaign_build_jobs gained batch_id, latitude/longitude/radius_miles, and office_address -- copied from the roster at job-creation time, not re-read later, so a roster edit after a batch starts never changes an in-flight job.
Geocoding uses the US Census Bureau's free public geocoder (geocoding.geo.census.gov, no API key, confirmed live against a real agent address before relying on it) -- lib/campaignBuildRoster.js's geocodeAddress(). A real upload of Revolution FMO's actual 14-agent roster geocoded 13 correctly; the 14th's address genuinely doesn't resolve in Census's own address-range data (not a bug -- confirmed by testing the same address with and without the suite number, and with just city/state/zip, all three came back with no match), so it's surfaced in the upload response's geocodeFailures list for a human to fix by hand rather than silently left broken or guessed at.
Radius reaches the plan two different ways, deliberately never conflated: the office address is passed to generateCampaignPlan() as plain-text context (new localAreaAddress param) so Claude writes genuinely locally-relevant ad copy and keywords -- verified live: a real build for an agent in Bloomfield Twp, MI produced campaign names referencing "Metro Detroit" and "Oakland County" unprompted. The actual location targeting (the radius itself) is never trusted to Claude to echo back -- it's applied deterministically from the job's own stored lat/long/radius at export time in routes/campaignBuildJobs.js, so it can't be mistyped or hallucinated.
Every export format updated (all verified live against real builds, not just unit-level): the three review workbooks each gained a Locations sheet (office address, coordinates, radius -- for compliance/client reference; LinkedIn's notes it must be set manually since LinkedIn's bulk import has no location field at all, same limitation already true for objective/bid strategy). lib/campaignBuildGoogleEditorCsv.js writes a real (35mi:lat:long) proximity value in the Location column per campaign (Google's own documented format -- the exact row/Type-inference shape wasn't independently confirmed against a live Editor import the way the rest of that file's shape has been, same caveat that file's negative-match-type convention already carries). lib/campaignBuildBingBulkCsv.js writes a real Campaign Radius Criterion row per campaign (Type, Radius, Unit, Latitude, Longitude, Bid Adjustment) -- Microsoft's own docs confirm Radius must be a whole number 1-500 when Unit is Miles, rounded rather than sent fractional.
New routes/campaignBuildBatch.js, mounted before campaignBuildJobs.js
deliberately -- that router's GET /:id is a wildcard that would
otherwise swallow /roster and /batch/* requests first (matching
"roster"/"batch" as if they were a job id).
GET /api/campaign-builds/roster?orgId= (X-Upload-Token)
POST /api/campaign-builds/roster/upload (X-Upload-Token, multipart)
Merge semantics: existing agents (exact name match) get updated,
new names get inserted, near-miss names (Levenshtein distance,
same approach documented for the CRD roster mismatch elsewhere)
come back in possibleDuplicates for a human to resolve via
POST /roster/resolve-duplicate rather than silently creating a
second agent for the same person under a different spelling.
POST /api/campaign-builds/roster (add one agent)
PUT /api/campaign-builds/roster/:id (edit one agent)
POST /api/campaign-builds/batch (X-Upload-Token)
Body: { agentIds: [...] | "all" }
Every selected agent must have a website_url -- a batch job needs
one, same as campaign_build_jobs.website_url NOT NULL always has
for a single build. A client-wide 5P is NOT required for a batch
job (see the website-derived-ICP fallback below) -- unlike a single
build, which still hard-requires one.
GET /api/campaign-builds/batches List past batches for
this org, newest first, with completed/failed counts -- added so a
batch's progress/downloads survive a page refresh; previously they
only ever lived in the frontend's in-memory window._currentBatchId.
GET /api/campaign-builds/batch/:id Progress: completed/
failed/pending counts, per-agent status and any error.
POST /api/campaign-builds/batch/:id/retry-failed Re-queues only the
failed jobs, not the whole batch.
GET /api/campaign-builds/batch/:id/download?platform=
A zip of every completed agent's export for one format, streamed via
archiver (new dependency) rather than buffered whole in memory --
verified live against a real completed batch. Deliberately NOT behind
requireUploadToken (same opaque-UUID-is-the-auth-boundary pattern as
GET /:id and GET /:id/export above) -- it's rendered as a plain download link in the frontend, and a plain anchor can never
send a custom header.
Website-derived ICP fallback (added after initial build, once Andy's real 14-agent roster surfaced the gap): Revolution FMO will never have a per-agent 5P document ("I will never have a 5P for each agent" -- Andy's explicit call), so lib/campaignBuildWebsiteIcp.js derives one live instead when a batch job's client has no shared 5P on file. One extra Claude call per such agent (Claude's real web_fetch_20260209 server tool, no beta header needed on claude-opus-5, reads the agent's actual website), producing a plain-text ICP brief fed into the same generateCampaignPlan() pipeline as a pasted ICP -- additive, not a parallel code path. Logged in AI Usage as derive_icp_from_website. The derived brief is capped at MAX_ICP_TEXT_CHARS (2000 chars / ~300 words) before being reused -- added after a real batch run produced a JSON-parse failure ("Unexpected end of JSON input") traced to an overly long derived brief inflating the plan-generation prompt enough to truncate the model's own JSON output.
Worker concurrency: worker.js previously processed exactly one campaign build at a time -- fine for an occasional single build, but a 250-agent batch at ~3-4 minutes each sequentially would take 12+ hours. CAMPAIGN_BUILD_CONCURRENCY (default 5, same pattern as the existing VISITOR_AUDIT_CONCURRENCY) now fires that many processNextCampaignBuildJob() calls per poll tick instead of one; the existing FOR UPDATE SKIP LOCKED claim query was already safe for this, it just wasn't being used concurrently. maybeCompleteBatch() checks after every job finishes (not on a timer) whether its batch has any jobs still pending/processing, and stamps completed_at the moment the last one lands. A real 14-agent batch (2026-09-02/03) surfaced that the first version of this cap wasn't real: the poll loop fired CAMPAIGN_BUILD_CONCURRENCY new claims every tick regardless of how many prior claims were still running, so all 14 jobs went to "processing" simultaneously instead of 5 at a time. Fixed with a real in-flight counter (campaignBuildInFlight, incremented on claim and decremented in a .finally()) so the poll loop only starts a new job when a slot is actually free.
Frontend: a "Single Build" / "Batch Build" toggle in ppc-builder.html, shown only once GET /roster returns at least one agent (checked on every client switch) -- implemented as a pure show/hide of the existing single-build cards plus one new sibling section, not a DOM restructure, to avoid any risk to the working single-build flow. The batch view covers roster upload/display (including inline-editable rows -- Agent/Website/Address/Radius/Status per row with its own Save, plus a "+ Add new agent" row for manual entry with no spreadsheet at all), duplicate resolution, agent selection with a select-all, and running a batch. A "Past batches" list (backed by GET /batches above) shows every batch for the current client with a View button that restores the progress card for that batch -- added because the progress/download UI only ever lived in memory, so a page refresh previously left a completed batch's exports unreachable even though the DB still had them. Downloads use an authenticated fetch + blob-download (downloadBatchZip()), not a plain link, since the download route requires X-Upload-Token and a plain <a href> can never send a custom header -- the first version of this got that wrong and every click 401'd. The 25-agent warning: selecting more than LARGE_BATCH_WARNING_THRESHOLD (25) triggers a confirm dialog stating the count and an estimated cost range (currently a static $0.30-$0.50/agent estimate, bumped up from the original $0.25-$0.40 once the website-ICP fallback added a second Claude call per agent for clients with no shared 5P -- still not wired to the real AI Usage numbers, a reasonable next step once enough real batches have run to trust an average). Progress polls every 8 seconds; failed agents list individually with a "Retry failed only" action once any exist. A failed agent's error can be a real, transient Anthropic "Overloaded" (529) -- not a bug, just retry.
GET /api/admin/clients List orgs
GET /api/admin/clients/:id Single org
POST /api/admin/clients Create org
PUT /api/admin/clients/:id Update name/primaryColor/
logoUrl/avgDealValue -- see
Deal Settings under
Attribution Rule Engine above
GET /api/admin/clients/:id/users List users for org
POST /api/admin/clients/:id/users Create user + send invite
POST /api/admin/users/:id/resend-invite Resend invite email
POST /api/admin/users/:id/require-2fa Toggle 2FA requirement
DELETE /api/admin/users/:id Deactivate user
POST /api/admin/backfill-tiers Recompute tier/has_hubspot/
has_ga4/avg_deal_value per
client from real data
POST /api/admin/sync Triggers cold-email sync
(InboxIQ) and GA4 sync;
gated on INBOXIQ_SYNC_SECRETPOST /api/attribution/run, GET /api/attribution/cached, GET /api/performance, and GET /api/export/pdf still exist in backend/routes/attribution.js, performance.js, and the old export.js and are still mounted in server.js. Confirmed accurate as of Aug 2026: nothing in the current frontend calls any of them, but GET /api/attribution/cached and GET /api/performance specifically were being called by performance.html until fixed this session -- see Known Issues for what that actually did.
Self-serve billing for GK3 add-on products (DemandIQ, VisitorIQ, InboxIQ -- 3 mutually-exclusive volume tiers), separate from media spend and separate from ProofIQ itself (never billed here). backend/lib/stripeClient.js lazily inits the Stripe SDK; backend/routes/billing.js is the whole surface, mounted at /api/billing; frontend/billing.html is the client-facing page.
GET /api/billing/status Current org's subscription
state + active plan keys
POST /api/billing/checkout-session { planKeys: [...] } -- Stripe
Checkout, recurring monthly.
Server-side enforces the
InboxIQ-tier mutual exclusion
(exclusiveGroup in
backend/config/billingPlans.js),
not just a frontend nicety.
POST /api/billing/portal-session Opens Stripe's own hosted
Billing Portal (manage/cancel)
for the org's existing customer
POST /api/billing/webhook Stripe webhook, raw-body
signature verification via
express.json's own verify
hook in server.js (captures
req.rawBody) -- updates
organizations.stripe_customer_id
and client_subscription_items
backend/config/billingPlans.js holds the 5 real Stripe Price IDs and dollar amounts (DemandIQ $1,500/mo, VisitorIQ $500/mo, InboxIQ 30k/60k/100k at $1,500/$3,000/$5,000) -- these are the final client-facing prices as given directly, not a cost basis to mark up further (an earlier version of this file mistakenly applied a 5x markup on top of them; corrected the same day, old inflated Stripe Price objects deactivated not deleted). db/migrate-v2-billing.js added organizations.stripe_customer_id and client_subscriptions/client_subscription_items (the latter for multiple simultaneous plans per subscription).
Verified end-to-end in Stripe test mode via a real Checkout session (card 4242...), including the webhook firing and /billing's status updating live, and the Billing Portal's cancel-at-period-end flow. A "Billing" sidebar link exists on every page now (added to the 7 pages that didn't already have it, for consistency with ICP Library's presence everywhere).
Database Schema
organizations (extended, not replaced)
...existing columns, plus:
tier (int, 1|2|3), has_hubspot, hubspot_portal_id,
avg_deal_value, has_ga4, ga4_property_id, is_active
channel_spend
id, client_id (FK), channel, date, campaign_name, ad_set_name,
spend, clicks, impressions, video_views, reach, conversions,
conversion_value, data_source, synced_at
UNIQUE(client_id, channel, date, campaign_name)
contacts
id, client_id (FK), hubspot_contact_id, email, first_name,
last_name, create_date, original_source(_drill1/_drill2),
record_source_detail_1, utm_source/medium/campaign/content/term,
gclid, attributed_channel, is_paid_lead, lifecycle_stage,
lifecycle_stage_updated_at, is_engaged, synced_at,
company, secondary_referral_source, primary_referral_source,
crd_number, crd_confidence, crd_verified_at
UNIQUE(client_id, hubspot_contact_id)
attributed_channel is computed once at insert, never overwritten
except that hubspotSync.js's ON CONFLICT DO UPDATE does refresh
it on resync so rule-engine fixes apply retroactively.
contact_stage_history
id, client_id (FK), contact_id (FK), stage_from, stage_to,
entered_at, days_in_previous_stage, attributed_channel
deals
id, client_id (FK), hubspot_deal_id, deal_name, amount, currency,
deal_stage, is_closed_won, is_closed_lost, create_date,
close_date, sales_cycle_days, contact_id (FK, usually NULL --
see Attribution Rule Engine), hubspot_contact_id,
attributed_channel, synced_at
UNIQUE(client_id, hubspot_deal_id)
cold_email_campaigns
id, client_id (FK), inboxiq_campaign_id, campaign_name, date,
emails_sent, emails_delivered, opens, clicks, replies,
meetings_booked, unsubscribes, leads_generated,
open_rate, reply_rate, meeting_rate, synced_at
ga4_traffic
id, client_id (FK), date, channel_group, sessions, new_users,
total_users, engaged_sessions, engagement_rate, bounce_rate,
avg_session_duration, conversions, conversion_rate, synced_at
attribution_rules (per-client overrides, checked before
GLOBAL_DEFAULT_RULES)
id, client_id (FK), priority, field_name, match_type,
match_value, attributed_channel, is_paid_lead,
campaign_name_pattern, is_active, created_at
sync_log
id, client_id (FK), sync_type, status, rows_synced,
rows_skipped, rows_failed, date_from, date_to, error_message,
started_at, completed_at, duration_secondstouchpoints and attribution_runs are the old synthetic multi-touch-model tables. Neither is read by anything in v2.
Fixed (Aug 2026): windsor-adapter.js -- the actual live cron entrypoint for proofiq-daily-sync, confirmed via its own log banner, not daily-sync.js below -- had a dead upsertTouchpoints() call inserting into touchpoints without setting its NOT NULL source column (no default). Every insert threw and was silently swallowed by a bare try/catch, every night, for every client × channel, for months: confirmed via 14,803 existing rows with none newer than several days before the fix despite nightly runs. Removed the function, its two call sites, and the now-unneeded schema bootstrap that only existed to support its ON CONFLICT clause.
backend/jobs/daily-sync.js is a separate, older job file that is not the live cron entrypoint (confirmed the same way) -- it still contains a dead INSERT INTO attribution_runs (...) call with a ran_by foreign key, but since this file doesn't currently run in production, that insert doesn't fail on every run today. Worth deleting outright if this file is confirmed fully dead, rather than leaving it as a landmine that only breaks if someone reactivates it.
All migrations are idempotent Node scripts using IF NOT EXISTS. Safe to re-run.
node backend/db/migrate-v2-attribution.js node backend/db/migrate-v2-crd-match.js node backend/db/migrate-v2-referral-source.js
Run from Railway Console (proofiq service) or locally with DATABASE_URL (public connection string, see Data Sync Pipeline above) set.
-- Pipeline health for every active client SELECT o.name, o.tier, (SELECT COUNT(*) FROM deals d WHERE d.client_id=o.id AND d.is_closed_won)::int AS ever_closed_won, (SELECT MAX(c.create_date) FROM contacts c WHERE c.client_id=o.id) AS last_contact FROM organizations o WHERE o.is_active = true; -- Channel classification distribution for a client SELECT attributed_channel, is_paid_lead, COUNT(*)::int FROM contacts WHERE client_id = '<org_id>' GROUP BY attributed_channel, is_paid_lead ORDER BY 3 DESC; -- lifecycle_stage real values (Title Case, not an enum -- see above) SELECT DISTINCT lifecycle_stage, COUNT(*)::int FROM contacts WHERE client_id = '<org_id>' GROUP BY lifecycle_stage ORDER BY 2 DESC; -- Reset a user password hash (set to 'ProofIQ2026!') UPDATE users SET password_hash = '$2b$12$hAt2NzS5YCc8xVssoNBGCevT1zqokwAgVoVuFSm/sAqUYyB7wwm8m' WHERE email = '<email>';
Adding a New Client
- Create the org in ProofIQ: log in as agency_admin, click + New client, enter name and slug
- Collect account IDs: Google Ads customer ID (format XXX-XXX-XXXX), LinkedIn org ID (numeric), HubSpot portal ID (numeric), GA4 property ID if applicable. Confirm the HubSpot portal ID directly with the client. A wrong ID silently pulls the wrong account's data with no error.
- Add the client to
backend/config/client_windsor_config.js: key is the org's realorganizations.idUUID, not a guess or a copy-pasted placeholder - Commit and push: Railway auto-deploys both services
- Run initial sync manually: see Data Sync Pipeline above
- Run
/api/admin/backfill-tiers: sets tier/has_hubspot/has_ga4/avg_deal_value from the data that just synced - Verify in ProofIQ: open the client's Attribution and Pipeline pages, confirm real numbers appear
- Invite client user: add their email from the client account in ProofIQ
// In CLIENT_WINDSOR_MAP in backend/config/client_windsor_config.js:
'<real organizations.id UUID>': {
name: 'Client Name',
google_ads: '123-456-7890', // or null if not running
linkedin: '123456789',
hubspot: '20095280',
ga4: '271459244',
meta: null,
},Depends on the platform -- this is the real trade-off between the credential models covered under Recent Changes. Adding the org ID and account IDs to config (above) is always required regardless of platform; the question is what else, if anything, has to happen manually per client before that connector will pull real data:
- GA4 -- usually no extra setup. One shared ProofIQ service account covers every property; a new client just needs that service account granted Viewer on their specific GA4 property (GA4 Admin > Property Access Management, or re-run
backend/add_proofiq_viewer.pywith the new property ID added to itsPROPERTIESlist). - Google Ads -- usually no extra setup. One shared OAuth app + refresh token via GK3's Manager (MCC) account (930-016-0264) covers every linked account. A new client's Google Ads account just needs to be linked under that MCC (a manager-link request the client accepts); once linked, the existing credential set reaches it with no new OAuth flow.
- HubSpot -- always needs manual per-client setup, no way around it. A HubSpot Service Key only ever scopes to the one portal it's created inside, so every new client needs: a Service Key created in their own HubSpot portal (Settings > Development > Keys > Service Keys, scopes
crm.objects.contacts.read+crm.objects.deals.read), a new Railway env var (HUBSPOT_TOKEN_<CLIENT>, set on bothproofiqandproofiq-daily-sync), and a new entry inbackend/config/client_hubspot_map.jsmapping the org ID to that env var and the portal ID.
organizations.id for client_hubspot_map.js (or any per-client config) sometimes isn't a simple name match -- some clients are stored under a different name than their common/brand name (e.g. "Connors" is stored as "Connors Investor Services"). If an exact-name lookup in the organizations table returns nothing, try ILIKE '%partial%' before assuming the client doesn't exist in ProofIQ yet.Environment Variables
## proofiq service (Railway Variables tab)
DATABASE_URL Auto-set by Railway Postgres plugin (internal)
JWT_SECRET 32+ char random string. Changing this
invalidates all existing sessions.
NODE_ENV production
WINDSOR_API_KEY Windsor.ai account settings page -- verify
it's set on BOTH proofiq and
proofiq-daily-sync, they're independent
MATCH_SECRET Shared secret with the crd-match service
INBOXIQ_SYNC_SECRET Shared secret with InboxIQ's export endpoint
## proofiq-daily-sync service (independent variable set --
## does NOT inherit from proofiq)
DATABASE_URL, WINDSOR_API_KEY Same as above, set separately here too
PROOFIQ_ADMIN_EMAIL
PROOFIQ_ADMIN_PASSWORD
## proofiq service, added Sep 2026
SPACESHIP_API_KEY Domain Finder (/domain-finder) -- spaceship.com
SPACESHIP_API_SECRET API Manager. Both required for domain
availability search; without them
/api/domains/* returns a clear config
error, not a silent failure.
SPACESHIP_REGISTRANT_CONTACT_ID Only needed for the Register
button (not search) -- a saved contact
profile ID from Spaceship's own dashboard.
QA_AGENCY_ADMIN_EMAIL / QA_AGENCY_ADMIN_PASSWORD Dedicated,
QA_AGENCY_MEMBER_EMAIL / QA_AGENCY_MEMBER_PASSWORD harmless test
QA_CLIENT_ADMIN_EMAIL / QA_CLIENT_ADMIN_PASSWORD logins for the
QA_CLIENT_VIEWER_EMAIL / QA_CLIENT_VIEWER_PASSWORD automated QA
sweep (backend/qa/run.js). A role whose pair
isn't set is skipped in the report, not
failed -- safe to add these incrementally.
2FA must be off on all four; the agency_member
one should have allowedTabs actually restricted
or the tab-restriction check is a no-op.
QA_NOTIFY_EMAIL Where the QA sweep's report email goes.
Defaults to Andy's address if unset.
GA4_SERVICE_ACCOUNT_JSON GA4 traffic sync's native replacement for
Windsor (see Data Sync Pipeline) -- a Google
Cloud service account's JSON key, as a single
-line JSON string. That service account also
needs Viewer access granted on each client's
GA4 property directly (GA4 Admin > Property
Access Management, or run backend/add_proofiq_
viewer.py to bulk-grant), separate from this
var. Must be set on BOTH proofiq and proofiq-
daily-sync services in Railway -- they do not
share variables; a value on only one still
leaves the other's GA4 sync throwing "not
configured". A Railway variable reference
(${{proofiq.GA4_SERVICE_ACCOUNT_JSON}}) works
fine for the second service instead of pasting
the key twice.
GOOGLE_ADS_CLIENT_ID Google Ads native sync's replacement for
GOOGLE_ADS_CLIENT_SECRET Windsor's google_ads connector (see Data
Sync Pipeline / Recent Changes) -- from a
Desktop-app OAuth client in the proofiq-510111
Cloud project.
GOOGLE_ADS_REFRESH_TOKEN One-time OAuth consent flow's output --
run backend/scripts/google-ads-get-refresh-
token.js on a machine with a real browser and
localhost access (not this cloud session/a
headless box) to obtain it.
GOOGLE_ADS_LOGIN_CUSTOMER_ID GK3 Capital's Google Ads Manager (MCC)
customer ID, digits only, no dashes (930016
0264 as of this writing). Required whenever
querying a client account through a manager
account.
GOOGLE_ADS_DEVELOPER_TOKEN Still required despite Google retiring
the application/review process Sept 9, 2026 --
see Recent Changes for the full story. Find the
existing token under Google Ads > Tools &
Settings > Admin > API Center > View token.
## Local backend/.env and root .env (gitignored -- never committed)
Same keys as Railway Variables, for local script runs. For DB
access from a local machine use DATABASE_PUBLIC_URL from the
Postgres service (see Data Sync Pipeline), not DATABASE_URL --
the internal hostname only resolves inside Railway's network.Known Issues
Found during a full front-end QA pass (Aug 2026), not something anyone noticed by using the page: performance.html was calling GET /api/performance (reads touchpoints) and GET /api/attribution/cached (reads attribution_runs) -- both dead v1 tables. Neither had a live writer: touchpoints' last writer was the upsertTouchpoints() call removed earlier the same session for silently failing on every insert (see Database Schema), and attribution_runs is only ever written by daily-sync.js, which isn't the live cron entrypoint at all.
The page still rendered real-looking numbers for clients with old historical rows inside its 90-day lookback window, so nothing about it looked broken. But with no writer ever refreshing that data again, those numbers would have silently gone to zero for every client as the window kept sliding forward -- no error, no banner, nothing to notice until a client asked why their numbers disappeared. Separately, the "Lead funnel" card was hardcoded static demo data (var FUNNEL = [...]) that never changed regardless of which client was selected, despite a subtitle claiming it was live GK3 Capital data.
Rebuilt against the same /api/v2/attribution/channels, /funnel, and /revenue endpoints pipeline.html already used correctly -- verified end to end against STP's real data (Tier 3, real channel spend/leads/clicks, real per-channel funnel, real closed-won revenue) via both direct API calls and a live browser session. Also fixed the channel color/label map, which still had a stale facebook key where v2 actually uses meta -- Meta-attributed data was silently falling through to a grey "unknown channel" default instead of its real branding. backend/routes/performance.js and GET /api/attribution/cached are both still mounted but now genuinely unused -- see Legacy v1 endpoints above.
crd-match's audience-export endpoint formats Meta's CSV against their confirmed current docs, but the LinkedIn contacts/company export column names were reconstructed from public docs, not a real Campaign Manager template -- flagged honestly by the session that built it. If a real LinkedIn ad upload fails on formatting, check against an actual downloaded Campaign Manager template before assuming the match data itself is wrong.
Found while building ProofIQ's own per-lead engagement sync (Sep 2026): Smartlead's /campaigns/{id}/leads?lead_status=<STATUS> endpoint now returns HTTP 400 ("lead_status is not allowed") -- confirmed live against a real API key. InboxIQ's own server/nightly-sync.js (a separate repo) still calls this exact dead endpoint, wrapped in a catch that swallows the failure silently, which is almost certainly why cold-email leads never carried a real engagement status (Opened/Clicked/Replied) anywhere, on either side, before this session.
ProofIQ's own backend/lib/smartleadClient.js was built against the current live endpoint instead (/campaigns/{id}/statistics?email_status=<lowercase>, confirmed against a real Donoghue Forlines campaign). InboxIQ's own code has not been fixed -- flagged to Andy, not patched, since it's a separate repo/product.
The proofiq-daily-sync service has a "Config as Code" path set in its own Settings tab (Deploy > Config-as-code), independent of anything in the repo. When that path points at a filename (historically railway.json) that doesn't exist in the repo, every deploy of that service fails during initialization with service config at 'railway.json' not found -- before the build even starts, no build log produced.
Recent Changes (Sep 2026)
The sidebar (<aside class="sidebar" id="sidebar">) is copy-pasted into every frontend/*.html page rather than shared from one source. When "Data I/O"/"Activation"/"Resources" were made into collapsible sections (button + chevron + localStorage-backed expand state, matching the older Admin/Under Development pattern), that change only landed on index.html -- the other 18 pages kept the old plain, non-interactive <div class="nav-section"> headers. Clients on any tool page other than the dashboard saw a genuinely broken/inconsistent sidebar. Fixed across all 18 pages (three parallel agents plus one manual fix for billing.html, which was independently missing the Admin/Under Development toggle CSS and JS entirely -- a pre-existing, unrelated break). Also added outline:none + a deliberate :focus-visible style to .nav-section-toggle, since clicking a header with no outline reset left a stray focus-ring box sitting on it.
Separately, express.static was serving every HTML file with default browser-heuristic caching, so a deploy landing didn't guarantee a given browser tab would actually see it -- backend/server.js now sends Cache-Control: no-cache for .html specifically (JS/CSS/images keep normal caching).
frontend/*.html files individually, or it will silently only exist on whichever page it was tested on. There is no single source of truth for this markup today.All three admin cards were added to view-data-sync (reachable only via /#data-sync) instead of view-overview (the default landing view) -- an edit matched the wrong "Sync all clients" card, since Data Sync has its own separate one. Moved to view-overview, where they were always meant to be. Worth remembering if a future card seems to "not exist" on Overview: check which .view div it actually landed in.
Generates candidate sending-domain variants of a client's main domain (alt TLDs, short prefix/suffix, optional no-hyphens variant) and checks live availability/pricing via the Spaceship.com registrar API (backend/lib/spaceshipClient.js, backend/lib/domainSuggestions.js). Defaults to .com-only (a checkbox opts into .io/.co/.net/.org/.us), since GK3 normally only registers .com for sending domains. Available domains can be registered on the spot under GK3's own Spaceship account (a real purchase, confirmed via a browser dialog before the request fires), copied to the clipboard as a plain-text list, or exported as a real .docx (backend/lib/docxHelpers.js, the docx npm package) for sending to a client to approve.
Originally built as a subtab inside inboxiq.html, then moved to its own standalone page + backend/routes/domains.js (replacing the old /api/inboxiq/domains/* routes) once it was pointed out that InboxIQ is client-visible -- a tool that spends real money has no business being reachable by a client login. The page hard-redirects away any non-agency_admin role on load, independent of tab restrictions, since this isn't a tab-scoping decision.
Playwright-driven: logs in as up to 4 configured QA test accounts (one per role) and, for every page in backend/qa/registry.js, checks that access matches what that role should have and that the page loads with no console/JS errors. registry.js is the single file to touch when a new page ships -- one entry (path, label, tabKey or agencyAdminOnly) is enough; expected access per role is derived from that account's real /api/auth/me response at runtime (mirroring requireTabAccess/applyTabRestrictions), never hardcoded, so the registry never needs updating when roles/permissions change independently.
Runnable directly (node backend/qa/run.js), via a Railway cron (not yet provisioned -- needs a start command that installs Playwright's Chromium first, since it's deliberately not bundled into the main app's deploy), or via the "Run QA check" card on Overview (POST /api/admin/qa/run, agency_admin only). Emails an HTML report either way (see Environment Variables for the 4 QA_* account pairs this needs before it checks anything real).
For every client with recent Campaign Reporting data, emails a narrative summary + PDF attachment to internal GK3 account-team users (agency_admin, or agency_member scoped by allowed_client_ids/allowed_tabs) -- never the client directly. Reuses buildCampaignReportingPdf() and generateCampaignReportingNarrative(), extracted from the existing /pdf and /insights/generate routes so the digest can never disagree with what the live page or a manual export would show. lib/email.js's sendEmail() gained attachment support (Resend's attachments array) for this. Not yet on a schedule -- needs a Railway cron job provisioned, same pattern as proofiq-daily-sync.
First step in reducing Windsor.ai's per-connection cost. GA4 was the easiest channel to move since Google's own Analytics Data API is free and needs no partnership approval, unlike LinkedIn's gated Marketing Developer Platform. backend/lib/ga4NativeClient.js wraps the official @google-analytics/data package; ga4Sync.js's three Windsor-fetching functions (session/channel rollup, assessment funnel events, campaign-detail traffic) now call it instead, each mapping GA4's native dimension/metric names back to the exact same field keys the existing insert functions already expected -- windsor-adapter.js and admin.js needed zero changes. Merged, deployed, and confirmed live with real row counts (Axar: 1,577 rows; Tortoise: 3,200; GK3: 3,552; and others) -- Windsor's own GA4 connector has since been disconnected entirely, so this is the only GA4 data path now.
backend/add_proofiq_viewer.py is a one-time script (Python, Analytics Admin API) that bulk-grants the ProofIQ service account Viewer access across every client GA4 property, rather than doing it one property at a time in the GA4 UI -- run with --dry-run first to preview, then for real. As of this writing, 23 of 36 known properties are granted (21 added live, 2 already had access). Of the 13 remaining no_access properties, only two are worth chasing: www.stpis.com - GA4 (271459244, STP Investment Services) and NexMetro - GA4 (372354828) -- both real clients in this migration, just needing someone with real Administrator rights on those specific properties to re-run the script. The other 10 (1031exchangesite, Retallick Wealth, Marathon Group Financial, Mitchell Financial Group, Sue Gordon Retirement Strategies, Messina's Wealth Management, Hybrid Financial Freedom, Freedom Dream Team, Flying Eagle, relayerbenefits.com) are out of scope for this migration -- don't spend time chasing access for them.
PERMISSION_DENIED in the daily sync log is not a code bug -- it means the service account was never granted (or the grant script's user lacked Administrator rights on that specific property). Since Windsor is disconnected, there is no fallback: an ungranted property gets zero GA4 data until access is fixed, not degraded/old data. Check [WARN] GA4 (<property_id>): ... lines in the sync log against the property list in add_proofiq_viewer.py.A real bug worth knowing about if GA4 syncing ever silently shows 0 rows again with no warning in the log: windsorRequest/windsorEventRequest/windsorCampaignDetailRequest in ga4Sync.js originally caught the native client's error but only destructured rows, discarding error before it ever reached a log line -- every GA4 failure looked identical to "no traffic in range." Fixed to log [WARN] GA4 (<property>): <message> in all three; if a future connector's sync goes quietly to 0 with no warning, check whether its error is being swallowed the same way.
backend/lib/googleAdsNativeClient.js wraps the google-ads-api npm package with raw GAQL, mirroring the same "one generic report function, each caller maps its own fields" shape as ga4NativeClient.js. Unlike GA4's single shared service account or HubSpot's one-credential-per-portal model, Google Ads uses one shared OAuth app + refresh token authorized against GK3's Google Ads Manager (MCC) account (930-016-0264) -- every client account linked under that MCC is reachable through the same credential set, passing each client's own Google Ads customer ID per call. backend/lib/googleAdsFetch.js is the swap layer: windsor-adapter.js passes its own generic windsorFetch through it, and only the google_ads connector gets intercepted and routed to the native client -- every other connector (LinkedIn, Meta, Bing, Spotify, Vibe) keeps going through windsorFetch to Windsor unchanged, since that fetch function is shared generically across connectors by syncCampaignMetrics/syncCreativeMetrics.
Required env vars: GOOGLE_ADS_CLIENT_ID, GOOGLE_ADS_CLIENT_SECRET (from a Desktop-app OAuth client in the proofiq-510111 Cloud project), GOOGLE_ADS_REFRESH_TOKEN (one-time interactive consent flow -- see backend/scripts/google-ads-get-refresh-token.js, must run on a machine with a real browser and localhost access, not a headless/cloud session), GOOGLE_ADS_LOGIN_CUSTOMER_ID (the MCC's own customer ID, digits only, no dashes), and GOOGLE_ADS_DEVELOPER_TOKEN (see below).
google-ads-api package's gRPC error details (reason/domain/metadata) can fail to decode client-side, printing No data type found for X noise directly to stdout and leaving e.message/e.code/e.errors all empty by the time application code's own catch block sees it. googleAdsFetch.js's catch now dumps the full error via util.inspect(e, {depth: null}) rather than picking specific fields, since guessing which field holds the real detail wasted multiple deploy cycles.Status as of this writing: code deployed, all 5 env vars set, still debugging a Google Ads API auth failure live in production. Windsor's own google_ads connector has already been disconnected, so -- same as GA4 -- there is currently no fallback while this is unresolved: every client shows 0 rows for Google Ads until it's fixed.
HubSpot's native sync (see Architecture/API sections above for hubspotNativeClient.js/client_hubspot_map.js) needs one credential created per client portal, since a HubSpot app only ever scopes to the portal it's created inside. The rollout started with legacy private apps (/private-apps/{portal}/create-legacy), but HubSpot disabled creating new legacy private apps for existing accounts on Oct 26, 2026 and is sunsetting them account-wide by Sept 2027 -- so the credential type actually being provisioned is now Service Keys (Settings > Development > Keys > Service Keys in each portal), HubSpot's replacement. Service Keys authenticate identically -- same Authorization: Bearer <token> header, same REST API surface -- so this was a comment-only update in hubspotNativeClient.js/client_hubspot_map.js, no logic changed. A handful of portals were already provisioned as legacy private apps before the switch; those were redone as Service Keys and the old legacy apps deleted to avoid two live credentials per portal.
Three separate real bugs, found one at a time by actually reading sync logs rather than assuming a config change had worked:
- Vibe had no account ID configured for Valoran at all originally (
client_windsor_config.jsis per-client, so a missing key silently skips that channel -- no error). - Once added, the ID had a transcription typo (an extra character) --
syncCampaignMetricswas requesting an account that doesn't exist, which Windsor's own API reports as a warning, not a hard failure, so it was easy to miss. - Bing was configured under GK3 Capital's entry with an account ID Windsor doesn't recognize -- Windsor's own account-list error message revealed the real Bing account under that login is named "Valoran Capital Management," misfiled under the wrong client entirely.
Also: Bing's own Microsoft Advertising API rejects a daily-aggregation report spanning more than 36 months outright (confirmed live) -- the full-history backfill script (backend/db/backfill-campaign-metrics.js, requests back to 2015) blew past that and the whole request errored. Same failure shape Spotify/Vibe already had their own dateCap clamp for; added the same pattern for Bing (dateCap: 1080 days) in windsor-adapter.js, campaignMetricsSync.js, and the backfill script.
Troubleshooting
WINDSOR_API_KEY is missing or stale on whichever service ran the sync. proofiq and proofiq-daily-sync have completely independent variable sets in Railway -- setting the key on one does not set it on the other. Check both explicitly.
Should not happen anymore -- /api/v2/attribution/revenue excludes unattributed deals from the per-channel breakdown and reports them separately as unattributed. If you see this, something regressed; check that the query still has AND COALESCE(c.attributed_channel, d.attributed_channel, 'unknown') != 'unknown' in its WHERE clause.
Check pipelineStale on /api/v2/attribution/revenue for that client before assuming a bug -- if the client's HubSpot pipeline has genuinely never had a deal closed or a contact reach Customer stage, $0 is the honest number and the frontend should already be showing the media-performance view instead (Cost Per Lead / Leads per $1K) rather than $0 revenue. If it's still showing $0 revenue with no banner, that's the actual bug to chase.
Check that service's Settings > Config-as-code path first -- see Known Issues above, this is the most likely cause for proofiq-daily-sync specifically. If it's the main proofiq service, check for a JS syntax error crashing Node at startup instead:
node -e "
const fs = require('fs');
const html = fs.readFileSync('frontend/index.html', 'utf8');
const scripts = [...html.matchAll(/<script>([\s\S]*?)<\/script>/g)].map(m=>m[1]);
scripts.forEach((s,i)=>{ try{new Function(s);}catch(e){console.log(i,e.message);} });
"gk3capital.com must be verified in Resend for transactional email to deliver reliably. Go to resend.com, Domains, add gk3capital.com, and add the DNS TXT/MX records provided. Temporary workaround: reset passwords directly via SQL in the Railway Postgres Data tab using the bcrypt hash from the diagnostic queries above.
Add ANTHROPIC_API_KEY to the proofiq service Variables tab in Railway. The panel renders but calls fail silently if the key is missing.