Mailed Records API

Read when: you are implementing, operating, or deploying the partner-facing mailed-records API or browser portal.

Overview

The Mailed Records API gives approved partners a product-safe search surface over mailed-record data. It supports two access paths:

• direct server-side API calls for integrations
• the mailed-records partner portal for browser-based search and contact updates

The canonical partner documentation route is /partners/mailed-records-api. The browser portal is available at /partners/mailed-records-portal.

Legacy routes still redirect for compatibility:

• /resources/mailed-records-api redirects to /partners/mailed-records-api
• /mailed-records-portal redirects to /partners/mailed-records-portal

Safety Model

The API reads approved fields from the core public.mailed_records table through server-side routes only. Browser clients and direct partners never receive unrestricted table access.

It uses:

Rules:

• Do not expose public.mailed_records directly to browser clients or partners.
• Client-facing routes may query public.mailed_records server-side only through an approved field allow-list.
• Do not expose non-approved mailhouse fields, internal scoring fields, customer fields, or internal identifiers.
• Mailing history is not returned by the direct API or browser portal. It stays in the shared database for suppression, cadence, and reference-resolution workflows.
• Do not write partner email or phone updates back into public.mailed_records.
• Reference search maps through mailed_record_reference_lookup, which resolves current and historical mailhouse references back to the current mailed_records row.
• Address search maps to mailed_records.property_address.

Quickstart

1. Request an API key or organization-assigned portal user through the partner access process.
2. For direct integrations, store the API key only on your server.
3. Send the key with either Authorization: Bearer <token> or x-api-key: <token>.
4. Start with a narrow GET /api/mailed-records/search request.
5. Use pageInfo.nextCursor to request the next page.
6. Use PATCH /api/mailed-records/contact only when updating the effective owner email or phone.
7. For browser access, sign in at /partners/mailed-records-portal instead of handling an API key.

Example search:

curl -H "Authorization: Bearer $MAILED_RECORDS_TOKEN" \
  "https://www.ratespedia.com/api/mailed-records/search?state=TX&query=Main%20Street&limit=25"

Example contact update:

curl -X PATCH \
  -H "Authorization: Bearer $MAILED_RECORDS_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "mailedRecordId": "00000000-0000-0000-0000-000000000000",
    "ownerEmail": "updated@example.com",
    "ownerPhone": "+1 555-555-0199"
  }' \
  "https://www.ratespedia.com/api/mailed-records/contact"

Authentication

Direct API access requires an API key. Supported headers:

Recommended production pattern:

• issue one key per consuming system, team, or environment
• use descriptive labels such as ops-dashboard, sales-ro, or api-smoke-test
• rotate by replacing the token while keeping the label stable for audit continuity
• never send direct API keys to browser clients

Credential sources:

Environment fallback variables:

• MAILED_RECORDS_API_KEYS
• MAILED_RECORDS_API_KEY
• MAILED_RECORDS_API_DEFAULT_DAILY_LIMIT
• MAILED_RECORDS_API_DEFAULT_WEEKLY_LIMIT
• MAILED_RECORDS_API_DEFAULT_MONTHLY_LIMIT
• MAILED_RECORDS_ORGANIZATIONS
• MAILED_RECORDS_SESSION_SECRET
• MAILED_RECORDS_ORG_SESSION_TTL_SECONDS

Fallback examples:

ops-dashboard:secret-token:500:2500:10000
sales-ro:secret-token:200:1000:4000
customer-portal|acme-user|strong-password|300

Customer Portal

The mailed-records partner portal is for customers or partner operations users who should search records and update contact overrides without handling raw API keys.

Portal route:

/partners/mailed-records-portal

Portal behavior:

• sign-in uses Supabase email/password users assigned in mailed_records_organization_members
• setup and reset links land on /partners/mailed-records-portal/setup-password
• BRONCO-generated manual setup links may include type=recovery and token_hash; the setup page verifies that token before allowing password changes, confirms the recovery session exists, and times out stalled Supabase auth calls instead of leaving the user on Saving...
• portal API requests pass the Supabase user access token to server routes; the server resolves auth_user_id to the active organization member
• legacy organizational username/password cookies remain as a fallback for old sessions
• portal API requests flow through /api/mailed-records/portal/session, /api/mailed-records/portal/search, and /api/mailed-records/portal/contact
• portal Bonzo exports flow through /api/mailed-records/portal/bonzo-export, which resolves the signed-in member's webhook URL server-side
• portal usage is audited through mailed_records_api_audit_logs with organization, organization member, Auth user ID, and user email fields
• portal reference search uses mailed_record_reference_lookup, so older mailhouse references still find the current record after repeat mail drops
• each organization defaults to 300 combined searches, updates, or exports per UTC day unless configured otherwise
• the portal page must remain noindex

Bonzo export behavior:

• each organization user can have its own Bonzo webhook URL managed from BRONCO admin configuration
• the expected user-profile column is bonzo_webhook_url on mailed_records_organization_members
• Ratespedia looks up the member row by auth_user_id, matching the authenticated Supabase user session
• the browser receives only a bonzoExportEnabled flag, never the webhook URL
• exports reload the approved record from mailed_records server-side and apply current email/phone overrides before posting to Bonzo
• owner phone is required before export; owner email is optional and is sent only when provided
• env fallback supports label|username|password|dailyLimit|bonzoWebhookUrl for local or transitional configuration

Endpoint: Search Mailed Records

GET /api/mailed-records/search

Purpose: paginated server-side search over approved fields from public.mailed_records.

Query Parameters

Pagination

Pagination is keyset-based.

• stable sort: property_state, property_city, property_address, mailed_record_id
• cursor is an opaque base64url token generated from the last returned record
• clients should treat cursors as opaque and should not parse or modify them
• do not replace this with offset pagination for large datasets

Success Response

{
  "success": true,
  "items": [
    {
      "mailedRecordId": "uuid",
      "owner": {
        "firstName": "Jane",
        "lastName": "Doe",
        "address": "123 Owner St",
        "city": "Dallas",
        "state": "TX",
        "zip": "75001",
        "email": "jane@example.com",
        "phone": "+1 555-555-0100"
      },
      "property": {
        "address": "456 Property Ave",
        "city": "Dallas",
        "state": "TX",
        "zip": "75001",
        "zip4": null,
        "type": "SFR",
        "marketValue": 450000,
        "purchasePrice": 320000,
        "lengthOfOwnership": 7
      },
      "loan": {
        "amount": 280000,
        "rate": 3.625,
        "lender": "Example Bank",
        "originationDate": "2021-02-01",
        "type": "CONVENTIONAL",
        "loanToValue": 62.2,
        "veteranInHousehold": false
      }
    }
  ],
  "pageInfo": {
    "limit": 25,
    "returned": 25,
    "hasNextPage": true,
    "nextCursor": "opaque-token"
  },
  "appliedFilters": {
    "query": "Main Street",
    "state": "TX"
  }
}

Portal search responses return a single best-match record and are limited to the lookup-result fields shown in the browser portal: reference, address, first mortgage amount, first position rate, lender, first position mortgage date, loan type code, loan-to-value ratio, owner first name, owner last name, email, and phone. Direct API search keeps the broader paginated response contract.

Endpoint: Update Contact Override

PATCH /api/mailed-records/contact

Purpose: update the effective owner email and phone for one mailed record without modifying the base mailed-record row.

Request Body

If both ownerEmail and ownerPhone are null, the override row is removed and the API falls back to source contact values from mailed_records.

Success Response

Contact update responses return JSON with success: true when the override operation completes. The updated effective contact values should be confirmed by searching the same record again.

Response Data

Allowed response groups:

• owner information
• property information
• loan information
• owner email
• owner phone

Excluded fields:

• mailhouse_ref
• ratespedia_id
• datasource
• artwork
• expired_data
• rate
• apr
• payment
• did
• deadline_date
• cashout_amount
• escrow_refund
• state_disclosure
• purl
• qr_code
• internet_data_date
• next_mortgage_date
• mailhouse_job_id
• imb_digits
• last_mail_date
• customer
• any future internal-only field unless explicitly approved and added to the isolated product layer

Errors

All API failures return JSON with:

{
  "success": false,
  "error": "Human-readable message.",
  "code": "machine_readable_code"
}

Common statuses:

Handling expectations:

• validation failures stop before database calls
• auth failures stop before search or update execution
• audit-log write failures are logged but do not fail an otherwise successful request
• unexpected database failures are logged server-side and returned as generic API errors

Rate Limits And Quotas

The API enforces per-minute request throttles plus longer quota windows through the audit log table.

Environment variables:

Quota behavior:

• direct API keys use UTC calendar-day, calendar-week, and calendar-month windows
• per-key quotas can be configured in MAILED_RECORDS_API_KEYS or mailed_records_api_keys
• organization logins use the same audit-log counter
• organization logins default to 300 combined searches or updates per UTC day unless overridden

Go-Live Checklist

Database:

1. Apply supabase/migrations/20260502_mailed_records_product_search_api.sql when contact override support is not already present.
2. Apply supabase/migrations/20260512234500_contact_overrides_reference_mailed_records.sql so contact overrides reference the mailed_records source table.
3. Apply supabase/migrations/20260504_mailed_records_access_controls.sql when BRONCO will manage credentials.
4. Confirm the shared access-control and contact-override tables exist.
5. Confirm contact overrides are empty or intentionally populated.

Application config:

1. Configure direct API keys in shared access-control tables or env fallback.
2. Configure organization logins in shared access-control tables or env fallback.
3. Set MAILED_RECORDS_SESSION_SECRET.
4. Set optional quota, rate-limit, page-size, or session-TTL variables only when defaults are not acceptable.
5. Keep all credentials in deployment config, not in source control.

Verification:

1. Run npm run build.
2. Request GET /api/mailed-records/search without a key and confirm 401.
3. Request search with a valid key and confirm 200.
4. Submit an invalid cursor and confirm 400.
5. Run a valid PATCH /api/mailed-records/contact request and confirm the override row is created.
6. Sign in at /partners/mailed-records-portal and confirm portal search succeeds without a browser-visible API key.
7. Confirm audit rows appear in mailed_records_api_audit_logs.

Troubleshooting

Rollback

Application rollback:

• revert API route, helper, portal, and documentation route changes
• redeploy the previous app version

Database rollback:

• stop API traffic first
• drop only the isolated product-search tables and functions if needed
• do not drop or modify public.mailed_records during rollback of this feature

Operational Notes

• Portal search quality and performance are driven by lookup_mailed_record_portal_best_match, normalized lookup indexes on mailed_records, and exact reference matching before any fuzzy address fallback.
• mailed_records_product_search is retained for older compatibility paths, but live search, contact saves, and Bonzo export should not depend on its freshness.
• New public fields require explicit review before they appear in API responses.