Reachability & Strongest Connection Filter

Turn the contacts list from a directory into an action list. Every row gets a "how easy is it for the team to get a warm intro?" badge that's filterable, sortable, and usable as an auto-update list rule.

Vocabulary first

Rollup

A single summary value computed from many smaller values. Here: combining each teammate's individual relationship score into one team-level number per contact.

Reachability

The team-level rollup. Answers "how easy is it for the team to get a warm intro to this contact?" Values: High Medium Low None

Strongest Connection

The best individual teammate level for a contact. Values: Warm | Known | Cold | Inferred | Unknown (same vocabulary as the per-leg badge from 037).

What ships in 039

Cut from 039

• Strongest Connection column renderer + sort → 041-strongest-connection-rollup (data ships now via 039, UI later)
• Key Connection chip on compact contact cards → 042-key-connection-chip
• Reachability on shared lists — deliberately omitted from the shared-list serializer
• Reachability customization (per-network threshold tuning) → 040-relationship-strength-customization

The data pipeline

Three layers, each owned by a different spec:

008                          037                                  039
contact_connections    →     RelationshipStrength::Scorer    →   contact_relationship_rollups
(raw graph data)             (per-leg Warm/Known/Cold)            (team-level High/Medium/Low/None)

• 008 populates contact_connections — work overlap, LinkedIn 1st-degree, and other primitive edges. Shipped.
• 037 turns each (team_member, contact) pair into a Warm/Known/Cold/Inferred/Unknown level. Shipped.
• 039 rolls up across all teammates in a collection to a single team-level value per contact.

The rollup, in plain English

Pick a contact — Teagan Thomas. She has 4 teammates with relationships to her:

039 collapses those four scores into one team-level answer: High (because at least one teammate has Warm).

Mapping table:

The rollup is scoped to (collection, contact). The same contact in a different collection gets a different value because the "team" is different.

Why a Postgres table (contact_relationship_rollups)

It's the cache layer. Three reasons it earns its keep:

1. Incremental updates need cached intermediate counts

The table doesn't only store the final answer — it also stores warm_count, known_count, cold_count. When a single contact_connection changes:

• Read cached counts
• Adjust by ±1 for the changed leg
• Recompute the level (still High because warm_count > 0)
• Write back

That's O(1) per update. Without the table, each invalidation would have to walk every teammate, re-run the scorer, and recompute counts from scratch — ~10–100× more expensive.

2. OpenSearch rebuilds happen

Mapping changes, version upgrades, accidental wipes — they all require reindexing. With the Postgres table, reindex = bulk export from a table. Without it, reindex = re-run the scorer for every contact in every collection (days of compute on a 1M-contact universe).

3. Different read paths want different stores

• List endpoint (filter/sort over big result sets) → OpenSearch
• Contact-detail endpoint (one row at a time) → Postgres
• Debugging ("why is this contact rated High?") → SELECT * FROM contact_relationship_rollups ...
• Auto-update list rules (Phase 4) → both, depending on predicate

Schema & how it links

Source of truth in Postgres, mirrored to OpenSearch for the list endpoint. Below are the full column shapes and the relationships to existing tables.

Columns — contact_relationship_rollups

How it links to existing tables

collections                 contact_relationship_rollups                contacts
+----+-------+               +----+----------------+-------------+       +----+------+
| id | name  | <-----FK------ | id | collection_id  | contact_id  | --FK--> | id | name |
+----+-------+               |    | reachability   |             |       +----+------+
                             |    | strongest_conn |             |
                             |    | warm_count     |             |
                             |    | known_count    |             |
                             |    | cold_count     |             |
                             |    | inferred_count |             |
                             |    | computed_at    |             |
                             +----+----------------+-------------+
                                       ▲
                                       │  after_commit (create/update/destroy)
                                       │
                             contact_connections (008)
                             +----+-------------+--------------+----------+
                             | id | team_user_id | contact_id  | kind     |
                             +----+-------------+--------------+----------+
                                       │
                                       └── triggers RelationshipRollupRefreshWorker
                                           which re-runs 037's scorer per (collection, contact)
                                           and updates the rollup row above

• collections ← collection_id defines the "team" (one rollup row per team's view of one contact).
• contacts ← contact_id is the rollup's subject.
• contact_connections (008) → not a direct FK, but every change here invalidates a rollup. The refresh worker re-runs 037's scorer for the affected (collection, contact) pair and writes the new counts/level.
• user_contact_collections (UCC) → not linked at the row level (different cardinality — UCC is per-(user, contact, collection), rollup is per-(collection, contact)). Joins/leaves trigger the refresh worker but the row written here is the aggregate.

Indexes

Derivation (state transitions)

The counts are computed from per-leg scorer output (037); the level enums are total functions of those counts.

warm_count     = direct_or_primary_leg_paths.count_where(strength == :warm)
known_count    = …(:known)
cold_count     = …(:cold)
inferred_count = 2_hop_secondary_leg_paths.count_where(strength == :inferred)

reachability =
  warm_count ≥ 1 OR known_count ≥ 3 OR cold_count ≥ 10 → :high
  known_count ≥ 1 OR cold_count ≥ 5                    → :medium
  cold_count ≥ 1                                        → :low
  else                                                  → :none

strongest_connection =
  warm_count    ≥ 1                              → :warm
  known_count   ≥ 1                              → :known
  cold_count    ≥ 1                              → :cold
  inferred_count ≥ 1 AND no direct evidence      → :inferred
  else                                            → :unknown

OpenSearch mirror (additive fields on the contacts index)

The Postgres row is the source of truth; OpenSearch carries a denormalized copy for filter + sort on the contacts-list endpoint.

The fields are nested under the existing collections array (one entry per collection the contact belongs to), so a contact in two networks gets two reachability values — one per team.

Why we can't ship 039 without this table

It's tempting to "just compute on the fly" or "just store in OpenSearch." Both fail concretely.

What breaks if there's no table at all

The reachability column without filter/sort is "decoration you can see but not act on" — which is most of the product value. Shipping without the table either ships a half-feature or eats massive runtime cost on every list query.

Why removing the table after launch would be costly

If we shipped 039 without the Postgres rollup and then changed our mind:

1. Read paths now expect it. Contact-detail endpoint, debug tools, and auto-update list evaluator all read from the table. Removing it is multi-PR work touching every consumer.
2. OpenSearch becomes load-bearing. Today OS is a mirror — if it's wrong, we re-export from Postgres. Without the table, OS becomes the source of truth. Every OS rebuild requires re-running the scorer for every contact (days). Every concurrent write needs CAS retry logic.
3. The intermediate warm_count / known_count cache is gone. Every invalidation walks every teammate again. With 800 jobs from a single LinkedIn sync, queue work multiplies by ~50×.
4. Migration off the table requires a full re-index of the OS contacts index — a planned maintenance event that requires the same backfill task we'd just deleted, plus a new "rollup-from-scratch" path inside the indexer.
5. Auto-update lists drift silently. Rules return different results day-over-day because the OS field is now eventually-consistent without a source-of-truth check.

Net: shipping without the table is a one-way door. We'd be paying the cost (build + backfill + migration plumbing) anyway, just later and under harder conditions (live customers, real data, tighter SLAs).

Why an OpenSearch mirror too

The contacts list endpoint reads from OpenSearch, not from Postgres (the list view is the read-hot path with full-text search and complex filters across millions of rows). For Reachability and Strongest Connection to be filterable and sortable, the values have to live in the OpenSearch index — not just Postgres.

The mirror is denormalized as collections[].reachability and collections[].strongest_connection (nested array, one entry per collection the contact belongs to).

Workers

Onboarding sequence (strict order)

Per-collection rollout — when turning the feature on for a new network:

1. BuildConnectionGraphWorker (008)              ← populates contact_connections
   ↓
2. BackfillRollupsTask (039 P0)                  ← computes & stores rollups
   ↓
3. UpdateContactsMapping (OS mapping update)     ← adds the new field to the index
   ↓
4. BackfillContactsReachability (039 P1)         ← indexes the field for existing contacts
   ↓
5. Flip `relationship_strength` flag for the collection

What happens when a new admin joins a network

This is the heaviest event the system handles. Walk-through:

1. Admin added — UserCollection row created. Refresh worker fires for every contact in the collection. New admin has no connections yet, so jobs run but rollups don't change (harmless waste).
2. Admin connects integrations — LinkedIn sync, Google account. Background workers start pulling.
3. Connections flow in — for each connection that matches an existing contact in the collection:
   • New contact_connections row inserted
   • after_commit enqueues RelationshipRollupRefreshWorker(collection_id, contact_id)
   • Worker re-runs 037's scorer for that contact, writes new rollup, OS re-indexes
4. Users see the list update live — as the queue drains, badges upgrade from None / Low → Medium / High. Filter results shift. Auto-update lists gain or lose contacts.

You don't have to re-backfill the collection when an admin joins. The invalidation path handles incremental updates. The per-collection backfill task is only for "first time turning the flag on."

Phase breakdown

Open questions worth a team conversation

• Should the refresh worker get its own Sidekiq queue? Big-onboarding events could starve user-facing work. Probably yes — minor ops change for big resilience win.
• Stale-read window communication — between a connection sync and the rollup landing, the list is "in between." We don't tell the user. Worth a subtle "Updating reachability…" indicator? Or accept the 60–90s lag as invisible?
• Multi-admin onboarding — when 5 admins join the same week, queue work multiplies. Debounce helps but not enough at scale. Worth a write-up on whether we throttle integration syncs during heavy onboarding periods.

Alternatives considered (and rejected)