Technical Reference

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: Windsor.ai API for Google Ads, LinkedIn, Meta, Spotify, Vibe, Simplifi, Bing/Microsoft, HubSpot, and GA4. InboxIQ (a separate GK3 Railway project) is the source for cold email data, pulled via its own export endpoint rather than 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.

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.app is 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 the serviceUpdate GraphQL mutation (railway api) since the CLI itself has no rename command -- railway service --help only 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_URL and EMAIL_FROM updated on the main service; VISIONIQ_ADMIN_EMAIL/_PASSWORD/_URL renamed to PROOFIQ_* 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
  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 and
                                Visitor Fit Scoring as tabs of one client-
                                scoped shell, both hosted on the crd-match
                                service, 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
  faq.html                      Client FAQ
  strategist-guide.html          Strategist guide
  tech-reference.html            This document

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:

1. Pulls ad platform spend (whichever channels the client has account IDs configured for) via Windsor, upserts into channel_spend
2. 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
3. Pulls GA4 traffic via Windsor (clients with has_ga4), upserts into ga4_traffic

Cold email sync (InboxIQ) and tier backfill are separate, triggered from /api/admin/sync rather than the cron job.

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 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

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.

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:

1. Any active per-client override rows in attribution_rules (priority ascending, first match wins)
2. GLOBAL_DEFAULT_RULES -- a fixed priority list checked top to bottom, starting with the hand-curated secondary_referral_source field (most reliable when present), then record_source_detail_1, gclid, and various utm_source/utm_medium matches
3. Falls through to attributed_channel = 'unknown', is_paid_lead = false if nothing matches

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.amount for closed-won deals, grouped by COALESCE(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 own hs_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 as unattributed rather than bucketed into a fake "unknown" channel row. The same response also carries dealStats: { 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 excludes unknown and 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.

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 info

An agency_admin can scope a team member (agency_member) down to specific clients and/or specific client-facing tabs, using two new nullable columns on users: allowed_client_ids UUID[] and allowed_tabs TEXT[] (added by backend/db/migrate-v2-user-access-scope.js). NULL on either column means unrestricted, which is also the only state agency_admin can ever be in: enforcement always checks role === 'agency_member' first, so admins are never affected by what's stored on their own row.

allowed_client_ids is the real, fully-enforced security boundary. isClientAllowed(user, orgId) in backend/middleware/auth.js is checked by resolveOrgScope before any client-scoped request proceeds, and directly by the two agency-wide handlers that don't go through resolveOrgScope: GET /api/admin/overview (filters the client list itself via AND o.id = ANY($1)) and GET /api/admin/clients/:id. A request for a client outside the list gets a 403 regardless of what tab or query string claims to be asking for it.

allowed_tabs is mostly a UI affordance. The report data endpoints (attribution, performance, campaign-metrics, pipeline) are already correctly scoped by client via resolveOrgScope no matter which tab a request claims to come from, so hiding a tab doesn't need backend enforcement to keep data safe. The one exception is POST /api/crd-match/upload-token, which has a real side effect (minting a short-lived upload token), gated with requireTabAccess('crd-match'). Tab keys match the route paths: attribution, performance, campaign-reporting, pipeline, crd-match, audience-match.

Frontend: frontend/index.html's enterApp() and the matching loadSidebarUser() in the five standalone pages call applyTabRestrictions(user), which hides sidebar links carrying a non-matching data-tab-key attribute and, on the standalone pages, redirects to / if the current page itself isn't allowed. Both GET /api/auth/me and the /login, /login/verify-2fa, and /accept-invite responses in backend/routes/auth.js include allowedClientIds/allowedTabs (null for every other role) since the frontend seeds currentUser straight from whichever of those responses it just received.

Setting restrictions: POST /api/admin/clients/:id/users and PUT /api/admin/users/:userId both accept optional allowedClientIds/allowedTabs arrays in the body. An empty or omitted array (or a non-agency_member role) stores NULL, i.e. unrestricted. On the PUT route specifically, the columns are only touched when the key is present in the request body at all (checked via hasOwnProperty, not just truthiness), so unrelated edits, password changes, 2FA toggles, resending an invite, never accidentally clear an existing restriction.

Separate from allowed_tabs: a .admin-only class marks nav items and page actions (Team, Audit log, Client Users, Deal Settings, Data Sync, and the Overview page's Sync all clients/New client actions) that call requireAgencyAdmin-gated routes regardless of any per-user restriction, since agency_member has never had permission to call those endpoints at all. applyAdminOnlyGating(user) hides every .admin-only element for any role other than agency_admin. Security stays visible for agency_member since it manages the signed-in user's own account (2FA, password), not a client's.

Client selection moved from a topbar-right dropdown into a persistent sidebar section, placed directly above Agency, styled to match InboxIQ's layout. On the five standalone pages (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 is tied to the same #nav-agency show/hide calls that already existed (agency users only; a client-logged-in user never sees it). 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" grid was left as-is; it's a distinct browse view, not a redundant control, and clicking a card already calls the same openClient().

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.

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.

GET /api/visitor-audit/5p/:orgId   (X-Upload-Token)
  Returns: { exists: false } or
           { exists: true, filename, uploaded_at, include_wealth_fields }

POST /api/visitor-audit/5p/:orgId   (X-Upload-Token)
  Multipart form, field: file (PDF or plain text). Replaces any
  existing 5P for that org. Returns: { exists: true, filename }

DELETE /api/visitor-audit/5p/:orgId   (X-Upload-Token)
  Returns: { exists: false }

PATCH /api/visitor-audit/5p/:orgId/config   (X-Upload-Token)
  Body: { include_wealth_fields: true|false }
  Flips the wealth-vs-company scoring mode without re-uploading the
  5P doc. When on, each result below also carries net_worth,
  income_range, age_range, homeowner, has_children, city, state,
  personal_phone -- null when off. Default false; only meaningful
  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, ...wealth fields
    above when enabled }], 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.

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.

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).

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.

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_SECRET

POST /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.

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_seconds

touchpoints 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>';

1. Create the org in ProofIQ: log in as agency_admin, click + New client, enter name and slug
2. 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.
3. Add the client to backend/config/client_windsor_config.js: key is the org's real organizations.id UUID, not a guess or a copy-pasted placeholder
4. Commit and push: Railway auto-deploys both services
5. Run initial sync manually: see Data Sync Pipeline above
6. Run /api/admin/backfill-tiers: sets tier/has_hubspot/has_ga4/avg_deal_value from the data that just synced
7. Verify in ProofIQ: open the client's Attribution and Pipeline pages, confirm real numbers appear
8. 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,
},

## 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

## 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.

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.

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.

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.