Contact and registry models

Seller

Table sellers. A marketplace seller company; maps to a HubSpot Company.

Field group Fields Notes

Keys

id (String @id, not @db.Uuid), subscriptionId, sellerId (@unique)

sellerId is the marketplace’s own identifier

Classification

platform (SellerPlatform), datasource (SellerDatasource), lifeCycleStage (SellerLifeCycleStage, default new), primaryCategory, primarySubCategory

Identity

name?, businessName?, email? (@unique), phone? (@unique)

Email and phone are globally unique, unlike Contact

Brand

singleBrand, brandNameMatch (both default false), numBrands?, numberWinningBrands?

Scale

estimateSales?, avgPrice?, percentFba?, numberAsins?, numberTopAsins?, numberReviewsLifetime?, numberReviews30Days?

Growth

moMGrowth?, threeMonthGrowth?, sixMonthGrowth?, yearGrowth?, moMGrowthCount?, sixMonthGrowthCount?

Address

street?, city?, state?, country?, zipCode?

Status

isSuspended (default false), lastSuspendedDate?, startedSellingDate?

HubSpot

hubspotCompanyId? (@unique), hubspotOwnerId?, hubspotSyncStatus?, hubspotLastSyncedAt?, hubspotEngagementId?

Enrichment

website?, description?, linkedinUrl?, industry?, annualRevenue?, numberOfEmployees?, payoutPainScore?, enrichedAt?, scrapedAt?

payoutPainScore is the qualification signal

Diagnostics

note?, errors (Json, default [])

Enrichment failures

Indexes: subscriptionId, platform, sellerId, hubspotCompanyId, country, isSuspended, primaryCategory.

Supplier

Table suppliers. A Payability supplier-summary copy from BigQuery PayabilitySheets.v_supplier_summary_extended. Distinct from Seller (marketplace company / SmartScout) and not linked to Contact.

Field group Fields Notes

Keys

id (@db.Uuid), subscriptionId, supplierKey

Identity is Payability supplier_key, not Seller.sellerId

Names

supplierName?, companyName?

Source times

sourceCreatedAt? (created), sourceUpdatedAt? (updated)

BQ updated only reflects one table in the view

Sync

lastSyncedAt

Our last write. Set on every successful upsert, including stale key re-pulls

Status

status?, stageName?, payabilityStatus?, cancelationStatus?, accessStatus?, apiStatus?

Overwritten from BQ on each match

Primary contact

contactFirstName?, contactLastName?, contactEmail?, phoneNumber?

Address

countryIso2?, stateCode?, address?, zipPostalCode?, timezone?, hide (default false)

Flags

legalSigned?, contractSigned?

Scale

estimatedMonthlyRevenue?, revenue?, revenue30?

Ops

ownerName?, storeName?, merchantToken?, firstPurchaseDate? (@db.Date, BQ first_purchase_date)

Calendar date YYYY-MM-DD; null when the source has no first purchase

Extra

extra (Json?)

Remaining BQ columns (tax id, UTMs, purchase/mp/chargeback windows, UW fields)

Unique: (subscriptionId, supplierKey).

Indexes: subscriptionId, sourceUpdatedAt, lastSyncedAt, status.

Inbound Payability import

suppliers/payability-import (5x daily ET, also suppliers/payability-import.requested) queries PayabilitySheets.v_supplier_summary_extended for up to 1000 rows after Configs supplier_sync_cursor ({ updated, supplier_key }), then upserts them into the workspace in HUBSPOT_CONTACT_SYNC_SUBSCRIPTION_ID.

  • Skip when supplier_key is missing.

  • Create when the key is new to the workspace.

  • Overwrite mapped columns on match so status changes apply (unlike HubSpot contact fill-nulls).

  • Stamp lastSyncedAt on every successful upsert — this is the last-updated time we trust, not BQ updated.

  • Advance the cursor to the last row’s (updated, supplier_key) keyset so same-day rows are not skipped.

  • A missing cursor starts at 1970-01-01 / empty key.

  • After the incremental batch, re-fetch up to 1000 existing suppliers with the oldest lastSyncedAt (excluding keys just imported). The view’s updated column does not move when other constituent tables change, so a key re-pull is how status still lands and lastSyncedAt advances.

The workspace list is /suppliers (GET /api/suppliers). Operators filter on apiStatus, accessStatus, and cancelationStatus first. The Sellers sidebar item is hidden; /sellers remains reachable by URL.

Contact

Table contacts. The single source of truth for a person.

Field group Fields Notes

Keys

subscriptionId, sellerId?

Identity

firstName?, lastName?, email?, phone?, mobilePhone?, companyName?, jobTitle?, website?

Address

street?, city?, state?, country?, zipCode?

Source

source (String, default manual), lifecycleStage?, leadStatus?, heatStatus (default idle), totalRevenue?, leadScore?

source is a string, not the ContactSource enum

Calling

disposition?, nextAction?, stopCalling (default false), validConversation?, lastCallRecordingUrl?

HubSpot

hubspotContactId?, hubspotOwnerId?, hubspotSyncStatus?, hubspotLastSyncedAt?, hubspotEngagementId?, hubspotSyncPriority? (default 0), hubspotDealId?, hubspotDealStatus?, hubspotDealStage?

Supplier

supplierKey?, supplierStatus?, supplierCancellationStatus?, supplierMarketplace?

Free-form

notes?, customFields (Json, default {})

Unique: (subscriptionId, email), (subscriptionId, phone), (subscriptionId, hubspotContactId), (subscriptionId, supplierKey). Nullable-unique semantics — many `NULL`s are allowed, non-null values must be unique.

Indexes: subscriptionId, sellerId, email, phone, disposition, hubspotContactId, supplierKey, source, lifecycleStage, leadStatus, stopCalling.

Relations: subscription, seller, emailCampaignTargets, activities, targetPhoneNumbers, targetEmails, emails, campaignTargets, followUpTasks, contactSequences.

Inbound HubSpot import

contacts/hubspot-import (5x daily ET, also contacts/hubspot-import.requested) queries hubspot4.v_contacts_latest for up to 1000 contacts that are not in outreach_app.contacts, then writes them into the workspace in HUBSPOT_CONTACT_SYNC_SUBSCRIPTION_ID.

  • Skip when hubspotContactId already exists.

  • Else match email, then phone, on a local contact with a null HubSpot id and fill empty fields only.

  • Else create a Contact with source = hubspot.

  • Create or link TargetEmails / TargetPhoneNumbers when email or phone is present.

  • Does not enroll campaign targets.

TargetPhoneNumbers

Table target_phone_numbers. Canonical registry of dial-to numbers.

Field Type Notes

phoneNumber

String @unique

Globally unique across workspaces

subscriptionId

String @db.Uuid

contactId

String? @db.Uuid

carrierName, countryCode, mobileCountryCode, mobileNetworkCode

String?

Carrier lookup data

lineType

LineTypes?

riskLevel

RiskLevel?

Only low or null is dialable

isActive, isDeleted, isBlocked

Boolean

syncTwilio, syncHubspot

Boolean

Sync intent flags

hubspotSyncedAt

DateTime?

Relations: subscription, contact, campaignTargets, plus calls through relation CallRegistryTargetPhone.

Set isBlocked to suppress a number across every campaign; the DNC path does this automatically.

TargetEmails

Table target_emails. Canonical registry of email addresses and their opt-out state.

Field Type Notes

subscriptionId

String @db.Uuid

email

String

Normalised to lowercase by the application

contactId

String? @db.Uuid

isActive, isDeleted, isBlocked

Boolean

isBlocked set on spam complaints

newsletterOptOut / newsletterOptOutAt

Boolean / DateTime?

RFC 8058, hosted unsubscribe, or email.unsubscribed

marketingOptOut / marketingOptOutAt

Boolean / DateTime?

Set on email.complained

oneToOneOptOut

Boolean

Direct email only; not part of campaign suppression

newsletterLastSentAt

DateTime?

Last marketing send, for the cadence gap

newsletterGapDays

Int?

Minimum days between marketing emails; default 7

Unique: (subscriptionId, email).

EmailCampaignTarget.emailId is a required FK here, which is what allows suppression and cadence to be joined into the target-leasing query.

Activity

Table Activity. The unified interaction log.

Field Type Notes

subscriptionId

String @db.Uuid

type

ActivityType

sms, email, call, task, meeting, note, reminder

contactId

String? @db.Uuid

Nullable for backward compatibility with pre-Contact rows

contactPhone, contactEmail

String?

campaignId, targetId, taskId

String? @db.Uuid

taskId links a FollowUpTask

subject, body, notes

String?

body is truncated to 500 characters by producers

status

String

default sent; also failed, delivered, received

direction

String

default outbound

retellChatId

String?

SMS chat id

hubspotContactId, hubspotEngagementId

String?

externalId

String?

Provider id, used for idempotent upserts

metadata

Json?

Unique: (subscriptionId, externalId).

Indexes: subscriptionId, type, hubspotEngagementId, createdAt, contactPhone, contactId, taskId.

Relations: subscription, contact, task, contactSequenceSteps.

Calls are not duplicated into Activity; GET /api/activity merges the Call table in at query time, because Call carries far more structure than an activity row could hold.