Support Portal + CRM Reconciliation Design

Status: Accepted
Date: 2026-07-17 UTC
Owner: software-architect
Parent spikes/epics: #4250 (CRM direction), #651 (support portal epic), #707 (FreeScout productionization)
ADR: 0141
Supersedes: #652 (data model + API contract spec — folded here)
Downstream sub-cards: #4251, #4252, #4253, #4254, #4255

1. Context

Two directions landed in the same week and needed reconciliation before implementation could proceed:

• #651 (existing): customers use a standalone support.raxx.app React SPA to view and manage their own tickets. Raptor mediates all FreeScout access. No FreeScout URL, name, or key touches the browser.
• #4250 (2026-07-14 direction change): console.raxx.app becomes the customer CRM — the surface where the Raxx team manages customer relationships. Distinct from console.moosequest.net, which remains the internal engineering/ops hub.

These directions are compatible, not contradictory. They address different audiences. This document names both surfaces, specifies their data model, defines the API contract for each, and produces an ordered implementation plan.

The interim stopgap for customer ticket submission is tickets.raxx.app/new-conversation (#4249, pending fix). That fix is independent of this design and should not wait for this design to land.

2. Invariants

The following constraints are non-negotiable and govern every decision below:

1. No stored credentials. The FreeScout API key never touches the browser, CF Pages env accessible to client code, or any log. It lives in Infisical and loads into Raptor via Heroku config var.
2. Passkeys / WebAuthn only. No password, no SMS OTP, no email OTP fallback. Customer authentication at support.raxx.app uses the existing Raxx passkey (RP ID raxx.app). Per the A+B recovery policy (#670, confirmed 2026-04-30), every customer has at least two enrolled authenticators. The portal does not create an alternate auth path.
3. Privacy boundary is absolute. Raptor verifies ticket ownership on every single-ticket endpoint — not just the list endpoint. Customer A never sees Customer B's ticket. The check is server-side, not in the SPA.
4. Internal notes and operator fields never reach the customer. FreeScout type: note threads, assignee, tags, component_tag, incident_severity, _internal_* custom fields are stripped server-side before any response leaves Raptor.
5. No vendor names to customers. "FreeScout," "Tagras," and all underlying vendor names are absent from customer-facing copy, URLs, error messages, and email templates.
6. Audit trail for every state change. Every ticket create, reply, status change, and customer-identity lookup is written to support_audit_log with actor, timestamp, and action.
7. GDPR by default. Retention limits apply per §8. DSR erasure cascades automatically for Raptor-managed tables; FreeScout-side deletion is a documented manual operator step (OQ-3, inherited from support-raxx-app.md).
8. Kill-switch. FLAG_SUPPORT_PORTAL_API=0 takes down all /api/v1/support/* routes returning 503. The portal SPA shows a maintenance state. FreeScout email intake continues independently.

3. Surface Decision

Decision: two surfaces, two audiences. Both ship.

"Customer CRM" in #4250 means a CRM used by the Raxx team to manage their customers — not a surface customers log into. The two surfaces share the same underlying data store (FreeScout via Raptor) but serve it to different audiences under different scoping rules.

customer browser
   └── support.raxx.app          (CF Pages SPA — project raxx-support)
          │  HTTPS, no CF Access gate; app-level auth required for all data
          └── api.raxx.app/api/v1/support/*    (Raptor — customer-scoped proxy)
                 │  server-to-server with FreeScout API token (Infisical)
                 └── tickets.raxx.app          (FreeScout REST API — CF Access gated)

operator browser
   └── console.raxx.app/customers/:id/support  (CRM section — existing console)
          └── api.raxx.app/api/internal/customers/:id/support-history
                 │  reads from freescout_ticket_cache (Postgres, primary path)
                 │  hydrates from FreeScout API on cache miss
                 └── tickets.raxx.app (on cache miss or explicit sync)

The CF Pages project raxx-support was created and DNS was set in #653 (CLOSED). The backend proxy blueprint was implemented in #654 (CLOSED). The SPA (#655) and FreeScout mailbox setup (#656) remain open and are un-deferred by this design.

4. Data Model

4a. freescout_ticket_cache — promoted to first-class CRM table

This table was originally scoped as a security-audit side-table (#3286). Under the CRM direction it becomes the primary store for the operator CRM's customer ticket history view. The webhook-push path populates it; the CRM customer profile reads from it.

The customer-facing portal (support.raxx.app) does NOT read from this cache. It reads live from FreeScout on demand, preserving the existing design in support-raxx-app.md §7. Cache inconsistency cannot affect customer-visible ticket data.

Schema (migration NNNN_freescout_ticket_cache_crm.sql):

CREATE TABLE freescout_ticket_cache (
    conversation_id      INTEGER PRIMARY KEY,
    mailbox_id           INTEGER NOT NULL,
    customer_email       TEXT NOT NULL,
    raxx_user_id         TEXT REFERENCES users(id) ON DELETE SET NULL,
    subject              TEXT NOT NULL,
    status               TEXT NOT NULL
        CHECK (status IN ('active', 'pending', 'closed', 'spam')),
    created_at_fs        TIMESTAMPTZ,
    last_reply_at        TIMESTAMPTZ,
    thread_count         INTEGER NOT NULL DEFAULT 0,
    last_thread_type     TEXT CHECK (last_thread_type IN ('message', 'note', 'lineitem')),
    tags                 TEXT[],
    -- CRM enrichment
    customer_raxx_id     TEXT,       -- from FreeScout custom field (set by Raptor on portal create)
    -- Cache bookkeeping
    synced_at            TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    webhook_event_id     TEXT        -- idempotency key from FreeScout webhook payload
);

CREATE INDEX ftc_email_recent
    ON freescout_ticket_cache (customer_email, last_reply_at DESC NULLS LAST);
CREATE INDEX ftc_raxx_user
    ON freescout_ticket_cache (raxx_user_id)
    WHERE raxx_user_id IS NOT NULL;
CREATE INDEX ftc_status ON freescout_ticket_cache (status);

raxx_user_id is populated by Raptor's webhook receiver when it can match customer_email to a Raxx user record. ON DELETE SET NULL preserves ticket history in the cache even if the user is deleted; the GDPR DSR job must additionally redact customer_email for deleted users (see §9 and OQ-2).

No ticket body content is cached. Subject and metadata only. Body content stays in FreeScout.

Sync direction: - Primary: FreeScout webhook → Raptor → upsert freescout_ticket_cache (via #3286, event-driven) - Fallback: Pull-on-demand at CRM customer-profile load time when cache has no rows for the customer (for historical tickets that predate webhook registration)

4b. support_customer_map, support_audit_log, support_pending_submissions

Fully specified in docs/architecture/support-raxx-app.md §4. No changes.

4c. customer-by-email endpoint — expanded contract

GET /api/internal/customers/by-email/<email> (#2071) becomes the CRM join at customer-profile load time. The existing six fields remain unchanged. A support_summary object is added as a new key (additive; existing consumers ignore unknown fields):

// GET /api/internal/customers/by-email/<email>
{
  "raxx_customer_id": "uuid",
  "tier": "free|pro|pro_plus",
  "account_status": "active|suspended|closed",
  "signup_date": "2026-01-01T00:00:00Z",
  "last_active_at": "2026-07-01T00:00:00Z",
  "total_paid_lifetime": 0,
  // NEW — populated from freescout_ticket_cache
  "support_summary": {
    "open_tickets": 2,
    "total_tickets": 5,
    "last_ticket_at": "2026-07-01T00:00:00Z",
    "source": "cache"   // "cache" | "live" | "none"
  }
}

source: "live" means the summary was fetched from FreeScout directly (cache miss). source: "none" means no FreeScout record exists for this email. The support_summary key is always present when the customer exists; it is omitted only on 404.

5. API Contract

5a. Customer-facing API (/api/v1/support/*)

Fully specified in docs/architecture/support-raxx-app.md §5. No changes to this contract. Implemented in #654 (CLOSED). The routes, request/response shapes, FreeScout field mapping, privacy-boundary algorithm, and JWT model are canonical there. This document does not duplicate them.

Route summary:

5b. Operator CRM API (internal)

New endpoints added to Raptor's internal API surface. Auth: internal service auth (not reachable without operator console session or service credentials). These are not behind the paper-first gate — support is not a trading code path.

GET /api/internal/customers/:raxx_user_id/support-history

Returns the cached ticket list for display in the CRM customer profile panel.

// Response 200
{
  "raxx_user_id": "uuid",
  "tickets": [
    {
      "conversation_id": 4821,
      "subject": "Backtest won't run",
      "status": "active",
      "last_reply_at": "2026-07-01T10:00:00Z",
      "thread_count": 4,
      "tags": ["billing"],
      "freescout_url": "https://tickets.raxx.app/conversations/4821"
    }
  ],
  "source": "cache",
  "synced_at": "2026-07-17T08:00:00Z"
}

freescout_url is for operator use only — it never appears in customer-facing responses. On cache miss, source is "live" and the response is freshly fetched from FreeScout.

POST /api/internal/customers/:raxx_user_id/support-history/sync

Forces cache hydration from FreeScout. Used when cache has no rows for a customer (historical tickets) and as operator manual refresh. Rate-limited: 10 calls per customer per hour.

// Response 200
{
  "synced": 7,
  "raxx_user_id": "uuid",
  "synced_at": "2026-07-17T09:00:00Z"
}

5c. Webhook receiver (SC-A4, per #3286)

POST /api/internal/freescout-webhook/audit — already specified in #3286 and docs/ops/runbooks/freescout-webhook-setup.md. Signature: HMAC-SHA256 via X-FreeScout-Signature. Secret at Infisical /MooseQuest/freescout/FREESCOUT_AUDIT_WEBHOOK_SECRET.

New responsibility under CRM direction: this webhook upserts freescout_ticket_cache on every handled event. The table must exist before FLAG_FREESCOUT_WEBHOOK_RECEIVE=1 is set.

Idempotency: webhook_event_id (from FreeScout payload id field) prevents duplicate upserts on webhook replay.

6. Sequence Diagrams

6a. CRM customer profile load — cache hit

sequenceDiagram
    actor Operator
    participant Console as console.raxx.app
    participant Raptor as api.raxx.app
    participant DB as freescout_ticket_cache

    Operator->>Console: Opens /customers/:id
    Console->>Raptor: GET /api/internal/customers/:id/support-history
    Raptor->>DB: SELECT WHERE raxx_user_id = :id
    DB-->>Raptor: [{conversation_id, subject, status, ...}]
    Raptor-->>Console: {tickets: [...], source: "cache"}
    Console->>Operator: Render support history panel

6b. CRM customer profile load — cache miss (historical hydration)

sequenceDiagram
    actor Operator
    participant Console as console.raxx.app
    participant Raptor as api.raxx.app
    participant DB as freescout_ticket_cache
    participant FS as tickets.raxx.app

    Operator->>Console: Opens /customers/:id (first time)
    Console->>Raptor: GET /api/internal/customers/:id/support-history
    Raptor->>DB: SELECT — 0 rows
    Raptor->>Raptor: cache miss
    Raptor->>FS: GET /api/conversations?customer[email]={email}
    FS-->>Raptor: [{id:4821,...},{id:3302,...}]
    Raptor->>DB: UPSERT freescout_ticket_cache
    Raptor-->>Console: {tickets: [...], source: "live"}
    Console->>Operator: Render support history panel

6c. Webhook push — ticket created via email intake

sequenceDiagram
    actor Customer
    participant Email as Customer email
    participant FS as tickets.raxx.app
    participant Raptor as api.raxx.app
    participant DB as freescout_ticket_cache

    Customer->>Email: Sends to support@raxx.app
    Email->>FS: Postmark Inbound → new conversation
    FS->>Raptor: POST /api/internal/freescout-webhook/audit
    Raptor->>Raptor: Verify HMAC-SHA256
    Raptor->>Raptor: Lookup customer_email in users table
    Raptor->>DB: INSERT freescout_ticket_cache (raxx_user_id linked if found)
    Raptor-->>FS: HTTP 200
    Note over DB: Cache current; next CRM load is cache hit

7. Migrations

Rollback for freescout_ticket_cache: DROP TABLE IF EXISTS freescout_ticket_cache;. It is a cache; authoritative data remains in FreeScout. The three support_* tables have the same clean-drop rollback (no data pre-launch).

8. Rollout Plan

Interim until SPA ships: #4249 fix restores tickets.raxx.app/new-conversation as the customer entry point. Ship #4249 immediately; it does not wait for this design.

9. Security Considerations

• PII in freescout_ticket_cache: customer email and ticket subject. No ticket body is cached. On GDPR DSR for a deleted user: raxx_user_id is set to NULL automatically; the GDPR DSR job must additionally set customer_email = '<redacted>' in this table. The manual-dsr-handling.md runbook must be updated. See OQ-2.
• Audit trail: Webhook upserts are attributed to source: freescout_webhook with timestamp. Operator CRM access to the internal support-history endpoint is logged in the existing console audit log (not support_audit_log, which is for customer actions).
• No credential replay: FreeScout API key and webhook secret are server-side only (Infisical, loaded as Heroku config vars). Rotatable without redeploy.
• Rate limits: /api/internal/customers/:id/support-history/sync is limited to 10 calls per customer per hour to prevent FreeScout API exhaustion at CRM mass-load. Customer-facing endpoints carry the limits in support-raxx-app.md §5 (60 req/min shared; tighter on write paths).
• Breach path: freescout_ticket_cache exfiltration exposes customer emails and ticket subjects. No financial data, no ticket body content. GDPR Art. 33/34 notification within 72h. Raptor's existing breach-notification pipeline covers this.
• Kill-switches: FLAG_SUPPORT_PORTAL_API (customer SPA) and FLAG_FREESCOUT_WEBHOOK_RECEIVE (cache sync) are independent. Either can be toggled without affecting the other or FreeScout email intake.

10. Card Reconciliation

Stand unchanged

Cards that change scope

Close as superseded

11. Open Questions

Blocking items that require operator input before the indicated sub-cards can be claimed:

OQ-1 — New CRM epic needed? The CRM customer profile panel (§5b, §6a–b) is currently unparented. #651 covers the customer portal; #707 covers FreeScout productionization; #4250 is the spike (will close when this design lands). A new epic console.raxx.app/customers — CRM phase 1 should be filed to parent sub-cards #4254 and #4255. Recommend filing before card-groomer picks up those sub-cards. Blocking: #4254, #4255 need a parent before grooming.

OQ-2 — DSR redaction of freescout_ticket_cache.customer_email. The GDPR DSR job must set customer_email = '<redacted>' for deleted users in this table (the ON DELETE SET NULL on raxx_user_id is not sufficient — customer_email remains PII). This is a new step in the automated DSR job and manual-dsr-handling.md runbook. Confirm attorney guidance or operator acceptance of manual step before GA. Not blocking schema migration or pre-GA phases; blocking GA.

OQ-3 (inherited) — FreeScout-side ticket deletion on DSR. Automated vs. manual. Pending attorney guidance. Not blocking portal sub-cards; blocking GA.

OQ-4 (inherited) — FreeScout retention config. Recommend 2-year conversation cap. Decision needed before #707 GA. Not blocking portal sub-cards.