Segmentation models

Membership is the source of truth, not an executable segment definition. Segments are registry rows; membership is materialised by whichever producer wrote it. Nothing is re-evaluated after the fact, so point-in-time correctness comes for free and historical numbers never change retroactively. It also means each producer keeps its own query instead of being normalised into a DSL first.

Membership and the daily list are different things — different grains, different lifetimes. Membership is slow-moving and concurrent (a contact sits in several segments at once); the daily list is one contact, one day, one reason.

Rollups are not stored. There is deliberately no aggregate table. ReengagementDailyListEntry is the fact table, and per (segment, strategy, arm, day) summaries are expected to come from SQL views over it. A materialised rollup would have to be recomputed as its attribution window matured, which a view gets for free by always reading the current facts.

Table segments. The reusable audience registry.

purpose is what keeps the table general — the same registry can hold the email campaign audiences that currently exist only informally.

Unique: (subscriptionId, key). Relations: memberships, strategies, runs, dailyListEntries.

Table segment_memberships. Slow-moving and concurrent, not a daily snapshot.

Soft-closing with removedAt preserves "was this contact in this segment in June" without writing a row per contact per day. Partial unique indexes enforce at most one open row per (segmentId, hubspotContactId) and per (segmentId, contactId) when the local contact is set — see Constraints outside the schema.

Refresh removals only close open rows with source = strategy:<that strategy’s id>. Legacy producer rows and members enrolled by another strategy are left alone. Stayers keep the same row and addedAt; sql_view refreshes update priorityScore in place. Qualifying identities for the run are staged in segment_refresh_qualifying (@@unique([runId, hubspotContactId]), with priorityScore) and deleted on finalize or run failure. Email import and the members list order open members by priorityScore DESC. Score updates stay scoped to source = strategy:<id> — a later sql_view strategy does not overwrite another strategy’s members.

Table segment_strategies. The methodology layer.

Separating who (segment) from how we treat them (strategy) is what gives the control/treatment arms something meaningful to vary. Without the split, "arm" has nothing to test.

dataSource and sourceTable are paired in Zod, not the database: Outreach uses one of the four app tables plus filterRules; BigQuery and HubSpot require sourceTable = custom_query and store customQuery. BigQuery also stores contactMappings (source_column + contact_field). Manual Outreach refresh compiles filterRules in lib/segments/compile-filter-rules.ts. BigQuery refresh runs SQL via querySegmentRows and resolves rows in lib/segments/resolve-contact-mappings.ts. Empty filters, empty SQL, or empty mappings fail the run so they never enroll the whole tenant. A HubSpot-id mapping can enroll with contactId = null; email, phone, and contact id only enroll when that Contact already has a HubSpot id.

The filterable Outreach columns live in lib/segments/source-table-fields.ts. The query builder cannot invent a column the catalog does not list.

prioritySource exists because the five producers do not agree. For sql_view, the named priorityColumn is the score: BigQuery uses the result column (leaf-name fallback, same as contact mappings); Outreach uses a type: "number" catalog field, including contact leadScore / hubspotSyncPriority even when the filter is on a child table. Refresh coerces with Math.round into Int. strategy_rules scoring is not implemented — inserts stay at 0 and scores are not updated.

lost_deal and supplier_account compute a weighted priority_score in their BigQuery view and the producer only rounds it:

The rest compute priority in producer code. A single priorityRules column would have misdescribed two of the five.

treatmentPercent lives here rather than on Segment so it versions alongside the methodology it varies.

Table segment_runs. Provenance for one producer run.

Because no executable definition is stored, the criteria in effect are frozen in criteriaSnapshot at write time — query name, thresholds, params. Descriptive, never re-evaluated.

Table reengagement_daily_list_entries. The daily list plus arm tracking.

Unique: (segmentId, hubspotContactId, listDate).

That is deliberately not (contactId, listDate). Logging every qualifying pick keeps producer wiring behaviour-neutral, and makes cross-segment overlap measurable before any precedence rule is legislated:

A winner view or a Segment.precedence tiebreak can be layered on later with no data migration.

The native picker (not the Python producers) sets reason = open_member and qualifyingContext = { source, priority_score } — no PII. hubspotOwnerId and salesRepId are copied from the local contact when present, for display only.

reason records which rule fired; qualifyingContext records what the contact looked like when it fired (lifecycle stage, deal stage, days since last call, click timestamp). Contact properties drift, so without the frozen context later analysis cannot ask questions nobody thought to log for.

prisma/sql/segmentation_constraints.sql holds things Prisma cannot express. The open-membership uniques also ship in prisma/migrations/20260902100000_segment_membership_active_unique.

The partial unique indexes are what make the soft-close design safe. A plain unique constraint would reject a contact who legitimately re-enters a segment later; without any constraint, a contact can silently hold several open membership rows (same HubSpot id, or same local contactId with a different HubSpot id). Closed rows may repeat.

scripts/seed-segments.ts inserts the five existing re-engagement channels as Segment rows, each with one baseline strategy at treatmentPercent = 0 — 100% control, so logging can ship before any challenger strategy exists and nothing about who gets called changes.

It is a script rather than a migration because it needs a real subscriptionId. Re-running is safe.

staleDays is left null on the seeded strategies. The current thresholds live as constants in the producer repository, and guessing them here would put wrong numbers into the baseline that later strategy versions get compared against.