Network Intelligence System

1. Overview

Ingest email and calendar metadata from each admin user's Google Workspace or Microsoft 365 account and turn it into three transparent relationship indicators on contacts:

• Connection strength — per team-member × contact: Warm Known Cold
• Contact reachability — per team × contact: High / Medium / Low / None
• Key connection — the best person on the team to ask for an intro

2. System architecture

Five pipeline stages — ingestion, resolution, rollup, enrichment, scoring — each isolated, each restartable independently. No stage writes outside its own tables.

Stage responsibilities

3. Data model

Six new tables. No changes to Contact, ContactEmail, or Collection. Three existing tables get additive columns. CONTACT_CONNECTION (the unified edge table — see §3.1) is the only one shipped today; the others are planned across phases 1–5.

New columns on existing UCC table

Enum values (stored as strings / Rails enums)

• INTERACTION_EVENT.provider: gmail, outlook, gcal, ocal
• INTERACTION_EVENT.kind: email, meeting
• INTERACTION_EVENT.direction: inbound, outbound, attended
• UCC.strength_tier / strength_override: warm, known, cold
• CONTACT_REACHABILITY.tier: high, medium, low, none

Fields excluded from the diagram for brevity

• USER_CONTACT_STATS also has email_inbound_count_24mo, email_outbound_count_24mo, email_last_inbound_at.
• USER_ENRICHED_PROFILE.raw is actually jsonb; INTERACTION_EVENT.raw_headers is jsonb.
• INTERACTION_EVENT.contact_id is nullable (intentional — unresolved interactions).

Why these shapes

• InteractionEvent is the single normalized store. Gmail, Outlook, calendars all land here. The contact_id being nullable is deliberate: unresolved interactions wait for resolution rather than forcing a premature contact creation.
• UserContactInteractionStats is a materialized cache, not the source of truth. Nightly rebuild + incremental updates on event insert. email_activity_bitmap_12q is a 12-bit int encoding 2-way activity per quarter — cheap to maintain, powerful for sustained-relationship detection.
• UCC gets four columns, not a new table. Strength lives where the team-member⇄contact link already lives. Override is a nullable enum so the absence of an override is explicit.
• ContactReachability is denormalized per (contact, collection). A cheap row-level recompute beats a per-request aggregation over hundreds of UCCs.
• UserEnrichedProfile intentionally mirrors ContactEnrichedProfile — same enrichment engine (Findem), same shape, same webhook wiring.

3.1 ContactConnection — the unified edge table

Every connection between two contacts — work overlap, education overlap, investor relationship, email exchange, calendar meeting, LinkedIn 1st-degree — is one row in ContactConnection, distinguished by a kind enum. This single table replaces what an earlier draft of this spec called separate tables (UserContactInteractionStats, WorkOverlapCache, EducationOverlapCache, the per-type edge tables). It serves both consumers: 007 graph traversal AND 008 strength rules.

Schema

CREATE TABLE contact_connections (
  id                  bigserial   PRIMARY KEY,
  contact_a_id        int         NOT NULL,
  contact_b_id        int         NOT NULL,
  kind                enum        NOT NULL,
                      -- work_overlap | education_overlap |
                      -- investment_overlap | board_peer |
                      -- email_exchange | meeting | linkedin_connection

  -- For time-bound bridge edges (work, edu, investment, board)
  bridge_entity_type  enum        NULL,    -- organization | school | company
  bridge_entity_id    int         NULL,
  date_from           date        NULL,
  date_to             date        NULL,

  -- For interaction edges (email, meeting, linkedin)
  last_signal_at      timestamptz NULL,

  -- Universal
  strength_score      numeric     NOT NULL,
  metadata            jsonb       NOT NULL DEFAULT '{}',
  computed_at         timestamptz NOT NULL,

  CHECK (contact_a_id <> contact_b_id),
  UNIQUE (contact_a_id, contact_b_id, kind, COALESCE(bridge_entity_id, 0))
);

CREATE INDEX idx_cc_a_kind ON contact_connections (contact_a_id, kind);
CREATE INDEX idx_cc_b_kind ON contact_connections (contact_b_id, kind);
CREATE INDEX idx_cc_recent ON contact_connections (last_signal_at DESC NULLS LAST);
CREATE INDEX idx_cc_bridge ON contact_connections (bridge_entity_type, bridge_entity_id) WHERE bridge_entity_id IS NOT NULL;

The CHECK (contact_a_id <> contact_b_id) prevents self-edges. The composite uniqueness ensures one edge per (pair, kind, bridge): two contacts can have multiple work_overlap edges if they shared multiple past employers, but only one row per shared employer.

Kind enum and what each row carries

How the strength engine reads it

def evaluate_strength(contact_a_id, contact_b_id)
  rows = ContactConnection
           .where(contact_a_id: contact_a_id, contact_b_id: contact_b_id)
           .index_by(&:kind)

  return :warm  if w1_match?(rows['email_exchange'])    # 5+ each direction in 12mo
  return :warm  if w2_match?(rows['email_exchange'])    # sustained 2yr bitmap
  return :warm  if w3_match?(rows)                       # multi-kind: email + work/linkedin
  return :warm  if w4_match?(rows['meeting'])           # meeting in 180d
  return :warm  if w5_match?(rows['work_overlap'])      # small-org coemployment
  # ... K1-K6, C1-C5
  :cold
end

def w1_match?(email_row)
  return false unless email_row
  md = email_row.metadata
  md['inbound_count_12mo'].to_i  >= 5 &&
  md['outbound_count_12mo'].to_i >= 5
end

Each rule explicitly names which kinds it depends on. Read pattern is one query per pair; downstream logic is a hash-keyed lookup. No cross-table UNION ALL, no JOIN gymnastics.

Per-list rollup tables (still separate — different consumer)

Two rollup tables remain separate from ContactConnection because they're aggregations, not edges:

These tables exist because the page-load read budget (50ms p99) requires single indexed lookups. Re-running the rule engine on every list-view render is too expensive; the rollup is built at write time.

For the storage substrate decision (Postgres tables vs graph DB vs extension), see the Graph DB vs Postgres ADR companion doc. The summary: at depth-2 with aggregation-heavy read patterns, well-indexed Postgres tables outperform any graph DB option.

4. Integration status

Google Workspace Mostly live

Microsoft 365 Greenfield

Findem Pending capabilities

5. Service layer

Rule engine + scorer

The scorer is stateless: given a User and a Contact, it reads the rollup + overlap caches and returns a deterministic result.

Result = Struct.new(:tier, :score, :reasons)
# tier:    :warm | :known | :cold
# score:   Float, for within-tier sort only
# reasons: [{ code: :twoway_12mo_5plus, met: true, detail: "7 in / 9 out" }, ...]

class RelationshipStrengthService
  def self.call(user:, contact:)
    stats   = UserContactInteractionStats.find_by(user: user, contact: contact)
    overlap = ContactConnection.work_overlaps.for_pair(user.contact_id, contact.id)
    edu     = ContactConnection.kind_education_overlap.for_pair(user.contact_id, contact.id)

    evaluated = RULES.map { |rule| rule.evaluate(stats, overlap, edu) }
    tier = %i[warm known cold].find { |t| evaluated.any? { |r| r.tier == t && r.met } } || :cold
    score = WEIGHTED_SCORE.call(evaluated)
    Result.new(tier, score, evaluated.select(&:met))
  end
end

Rules are declared as data, not code:

RULES = [
  Rule.new(code: :twoway_12mo_5plus, tier: :warm, signal_weight: 3.0,
           predicate: ->(s, _, _) { s && s.email_inbound_count_12mo >= 5 && s.email_outbound_count_12mo >= 5 }),
  Rule.new(code: :sustained_2yr, tier: :warm, signal_weight: 2.5,
           predicate: ->(s, _, _) { s && s.email_active_quarters_consecutive >= 8 }),
  # ... 14 more ...
]

Adding a new signal = adding a Rule row. No changes to the evaluator.

Reachability service

Pure aggregation — runs once per (contact, collection) whenever any UCC tier in that collection changes.

class ReachabilityService
  THRESHOLDS = {
    high:   ->(c) { c[:warm] >= 1 || c[:known] >= 3 || c[:cold] >= 10 },
    medium: ->(c) { c[:known] >= 1 || c[:cold] >= 5 },
    low:    ->(c) { c[:cold] >= 1 }
  }.freeze

  def self.call(contact:, collection:)
    uccs = UserContactCollection.where(contact: contact, collection: collection)
    counts = uccs.group(:strength_tier).count.symbolize_keys
    tier = THRESHOLDS.find { |_, pred| pred.call(counts) }&.first || :none
    key = uccs.order(strength_tier_ordinal: :desc, strength_score: :desc).first&.user_id
    ContactReachability.upsert({ contact_id: contact.id, collection_id: collection.id,
                                 tier: tier, key_user_id: key, **counts, computed_at: Time.now })
  end
end

Rollup worker

Two entry points:

• Incremental: on every InteractionEvent insert via after_commit, bump counters and update the activity bitmap. O(1) per event.
• Nightly full: recompute all rows touched in the last 24h. Catches drift, re-bucketizes aging windows (e.g. emails that just fell outside the 12-month window).

Findem enrichment resolver

Lookup → cache → Contact match. Conservative: ambiguous Findem responses leave contact_id null.

6. Caching architecture

This section explains how the system's caches are organized, how they're maintained, and why they're shaped the way they are. Two halves: Part A is plain English for product, design, and reviewers; Part B is technical detail for engineers implementing or operating the system.

6.A — In plain English

The system has three things to keep track of, all derived from the same raw data: who shares a job history with whom (for intro paths), who's in regular email or calendar contact (for relationship strength), and the rolled-up answers for whole lists at a time. The challenge is that computing any of those questions from scratch takes too long when you ask for fifty or two hundred answers at once. So we pre-compute and store the answers, then update the stored answers whenever the underlying data changes. That's the whole idea.

There are three kinds of stored answers. The first kind, which we call per-pair caches, holds answers shaped like "for this team member and this contact, here's what we know — they've exchanged twelve emails in the last year, they had two meetings in the last six months, they once worked together at Stripe." There's a separate cache for each kind of signal: one for email, one for work overlap, one for education overlap, one for shared investments. Each cache is small, narrow, and maintained independently from the others.

The second kind, which we call edge tables, holds shortcuts shaped like "Mike and Sarah worked at Stripe together from 2020 to 2022." This is what 007 reads when it needs to find intro paths between contacts. It's structurally similar to the per-pair caches but keyed differently — it's about two contacts, not about a team member and a contact.

The third kind, which we call list rollups, are the at-a-glance summaries. For each list a user has, and each company on that list, we store one row that says "you have three direct contacts here, twelve intro paths, strength score 42, most recent signal April 12." This is what the company-list page reads when it loads — one indexed query, no fan-out. Without this rollup, the list page would have to walk through every other cache, every time, and the page would never finish loading.

When something changes in the underlying data — a contact updates their job, a new email comes in, someone joins your shared list — a small Rails hook fires immediately after the change is committed. That hook doesn't do the work itself; it just queues a background Sidekiq job for each cache that might need updating. There's one job per cache because each cache is independent. The work happens out of band, so the user who triggered the change doesn't wait. Each job is also deduplicated, so if ten emails arrive in a second, we only run one rollup recomputation, not ten.

When a page loads, it reads only from the caches and rollups, never from the raw underlying data. The list view hits the rollup table once and gets back fifty rows, one per company, with everything it needs to render. A contact-detail page hits two or three of the per-pair caches. Drilling into a specific company opens up the edge table to enumerate the actual paths. None of these reads ever touch the original work-history or email tables — they're far too slow to be on a hot path.

Two things keep the system honest. First, every job is idempotent and version-checked, so if a worker dies mid-write, it can safely re-run from where it stopped. Second, a nightly reconciliation job sweeps through each cache and double-checks it against the source data, fixing any drift. So if a Sidekiq job genuinely failed and we missed an update, we catch it within twenty-four hours and the cache heals itself.

The reason we use this many small, narrow caches instead of one big universal one is failure isolation. If the work-overlap calculator has a bug, only the work-overlap cache is wrong; the email signal cache keeps working. If we want to rebuild the investor cache from scratch, we don't have to touch anything else. Each heuristic gets its own little pipeline, its own worker, its own reconciliation, and the consumers stitch the answers together at read time. The trade-off is that one underlying change can fan out to several different jobs, but those jobs are all small and quick, so the total cost is fine.

6.B — Technical detail

6.B.1 The three layers

Every cache in the system fits into one of three layers, distinguished by key shape and consumer:

(Layer 1 is source data — InteractionEvent, ContactWorkExperience, ContactEducation, UserContactCollection. Never read on hot paths.)

The layers are read independently — a contact-detail page hits Layer 2 only, a list view hits Layer 4 only — and written together via fan-out from Layer 1 source events.

6.B.2 Write path: source event → fan-out to caches

Per ADR-007-B, all kinds of edges write to the single contact_connections table. There is one Sidekiq worker class per kind (not per table); each worker is responsible for upserting rows of its kind into the unified table. Today, only WorkOverlapEdgeWorker is implemented — other kinds are placeholders for future phases.

Write semantics:

• Trigger: Rails after_commit hook on the source-table model. The hook never blocks the user-facing request; it only enqueues Sidekiq jobs. ContactWorkExperience hook is shipped today (app/models/contact_work_experience.rb) and fires on create / relevant column updates / destroy.
• One worker class per kind, all writing to the same table. WorkOverlapEdgeWorker upserts contact_connections rows of kind=work_overlap; future EmailExchangeRollupWorker will upsert kind=email_exchange, etc. Workers are independent (different queues, retry policies) but share the substrate. A bug in one kind's writer doesn't corrupt another kind's rows because they're partitioned by the kind column.
• Idempotency: each worker delegates to its kind's backfill service (e.g., WorkOverlapBackfillService) which performs an UPSERT with record_timestamps: true, preserving created_at on conflict and refreshing updated_at + the domain-level computed_at.
• Deduplication: sidekiq-unique-jobs with :until_executed on (worker_class, contact_id). A burst of N CWE writes for one contact during enrichment collapses to one job.
• Cascading rollups: when an edge write completes, it enqueues the downstream Layer-4 rollup (planned). The rollup is also debounced — a burst of edge writes for one (list, org) collapses to one rollup compute.

6.B.3 Read path: page-load queries

Each user-facing page hits a specific layer. Not every layer is needed for every page.

None of the hot-path queries touch Layer 1 directly. Layer 1 is only read by workers (write path) and by reconciliation jobs.

6.B.4 Reconciliation: keeping caches honest

Each cache has a paired reconciler Sidekiq job, scheduled nightly via cron. The reconciler walks the cache's source rows in batches, recomputes the cache row deterministically, and either confirms or corrects the stored value. Drift is logged with a counter; if drift exceeds a threshold for any cache, on-call gets paged.

• Version columns: each cache row carries computed_at and a source_version hash. The reconciler compares the source version against the cache version to detect skew.
• Bounded batch size: reconcilers process N rows per Sidekiq job (default 1,000) and re-enqueue with a cursor for the next batch. Doesn't lock tables.
• Idempotent: running the reconciler twice produces the same result. Safe to trigger manually after a deploy or after a backfill.
• Per-cache scheduling: each reconciler runs on its own cron entry. A long-running work-overlap reconciler doesn't block the email-stats reconciler.

6.B.5 Failure isolation

The architecture's main reliability property is that one broken heuristic doesn't corrupt the others. This is enforced at three levels:

• Worker level: each kind has its own worker class, its own Sidekiq queue, and its own retry policy. A bug in WorkOverlapEdgeWorker leaves the future EmailExchangeRollupWorker entirely unaffected. Both write to contact_connections but to disjoint subsets of rows partitioned by the kind column.
• Reconciliation level: each kind has its own nightly reconciler scoped to WHERE kind = '...'. A regression in one kind's logic shows up as drift in only that subset; on-call investigates one kind at a time.
• Read level: rollup queries treat missing edges as zeros (or null tier), not as errors. If no rows exist for a (user, contact) pair with kind=investment_overlap, strength rules fall through to the next clause that doesn't depend on it. The user sees a slightly less-rich answer; the page still loads.

6.B.5a DeepFinder — target-rooted backward BFS over contact_connections

The 3-hop spike API (GET /api/v2/collections/:id/contacts/:id/deep_connection_paths) is implemented by Contacts::ConnectionPaths::DeepFinder. Algorithm note worth recording because the naive forward walk does not scale and the choice is not obvious from the table schema:

• Forward walk (rejected): start at every UCC.shared seed in the collection, walk N hops via contact_connections, filter post-hoc to paths terminating at the target. Frontier grows as O(seed_count × branching^N). On seeded data (24k contacts, 27k seeds, ~25 edges/contact), depth=3 produces 5.4M intermediate walk rows of which 99.98% are discarded by the target filter. Wall clock: ~13 s, dominated by recursion fan-out.
• Target-rooted backward walk (shipped): start at the target, walk N−1 hops outward via contact_connections, then INNER JOIN the terminal contacts against UCC.shared seeds. Frontier grows as O(branching^N), independent of seed count. Same workload: ~900 walk rows, ~10 ms — a ~1700× speedup with bit-identical result set.
• Per-hop edge cap: the recursive case uses INNER JOIN LATERAL ... LIMIT MAX_EDGES_PER_HOP=25, ordered by last_signal_at DESC, to bound worst-case fan-out for hyper-connected targets (a contact with 500+ edges would otherwise expand to 125M frontier rows at depth=3).
• Statement timeout: 250 ms via SET LOCAL statement_timeout, returning failure(error: "deep_finder.timeout: ...") rather than blocking the request.
• Implication for ADR-007-A: the original ADR claim that "Postgres recursive CTE handles depth=3 acceptably" is true only with target-rooted BFS. The naive forward-walk approach times out on seeded data. Substrate choice (Postgres vs graph DB) was correct; algorithm choice was load-bearing.

6.B.6 Trade-offs

6.B.7 Storage and write-amplification numbers

Concrete sizing for a collection with ~10,000 shared contacts and ~5 active team members. Indexes ~3× row size; total figures include indexes. All "cache" rows below live in the unified contact_connections table partitioned by kind (per ADR-007-B); the Cache column names them by their conceptual role.

Total per active collection (when all kinds populated): ~50–150 MB on disk including indexes. Write-amplification: a single ContactWorkExperience change triggers ~1 worker job per touched contact (Sidekiq lock collapses bursts), upserting ~5–20 contact_connections rows in <500 ms.

6.B.8 When to skip caching for a heuristic

Not every heuristic needs a cache. Skip the cache and read live from source tables when all three are true:

1. The heuristic is only read on cold paths (admin tools, drill-ins, one-off audits).
2. The live query takes <500 ms with proper indexes.
3. The query is bounded (single contact, single pair, single org — never a full-collection scan).

Examples that meet all three: per-contact strength rule re-evaluation in admin tools, single-org drill-in for connection paths, debug queries. Building caches for these adds storage and worker complexity without earning meaningful read latency back.

6.B.9 Operational gaps (known issues, not yet addressed)

Three gaps surfaced by the work_overlap spike that the architecture above describes correctly in principle but the shipped code doesn't handle yet. Worth documenting so they're not silently inherited by future kinds.

• Ghost edges on destroy. WorkOverlapBackfillService uses upsert_all — it inserts new edges and updates existing ones, but never DELETEs. When the only ContactWorkExperience linking contacts A and B at organization Z is destroyed, the corresponding contact_connections row persists as a stale "edge to nowhere." The ContactWorkExperience#after_commit destroy hook re-runs the backfill scoped to that contact, but the backfill's INSERT path can't observe pairs that no longer have any source rows. Fix path: extend the per-contact backfill to compute the set of (pair, org) tuples currently in contact_connections for that contact, diff against the freshly-computed set from CWEs, and DELETE the difference. ~30 LOC; not done.
• Initial production backfill is unbounded. The contact_connections:backfill_work_overlap rake task scans every CWE pair in the database when called without scoping. On dev (71k CWEs) this took 30s. Production has multiple orders of magnitude more rows; the scan would lock CPU on the writer for hours and the result-set could exceed memory. Fix path: chunked backfill driven by Maintenance::Task (the Shopify maintenance_tasks gem already in use), iterating per-organization or per-N-thousand contact-IDs. Resumable, observable in maintenance-tasks UI, throttleable. Ship before the table is enabled in any production environment.
• Reconciliation worker described but not implemented. §6.B.4 above promises "each cache has a paired reconciler Sidekiq job, scheduled nightly via cron." For kind=work_overlap, that worker does not exist. The after_commit hook is the only edge-write path today; a missed Sidekiq enqueue (Redis flap, deploy race) leaves a permanent gap until the next manual backfill. Fix path: schedule the chunked backfill from gap #2 as a nightly cron filtered to contact_work_experiences.updated_at > last_run; emit a drift counter when the reconciler updates a row whose timestamps are older than the threshold. Required before any production rollout that depends on freshness SLOs.

6.B.10 Sister-Finder integration gap (production read path still bypasses contact_connections)

The shipped 2-hop production endpoint, GET /api/v2/collections/:id/contacts/:id/connection_paths, is served by Contacts::ConnectionPaths::Finder. That service still reads from contact_work_experiences joined to itself by organization_id — it does not read from contact_connections. Until Finder is migrated, the unified table only serves the new 3-hop spike endpoint and cross-kind reads from the strength engine; the most-trafficked read path in the system is unaffected.

This is a quiet but load-bearing gap because:

• The "this table replaces six separate caches" claim in §3.1 is design-true but production-false. Two-hop reads for every list-view drill-in still pay the recursive-CTE-over-CWE cost on every call.
• Finder handles signal kinds that contact_connections doesn't carry yet — linkedin_connection and google_connection direct paths come from UserContactCollection.source introspection. Migrating Finder requires either (a) extending contact_connections backfill to include those kinds, or (b) keeping the UCC-source lookup as a parallel direct-path layer.
• Finder applies SIGNAL_WEIGHT ranking, consecutive-stint tolerance, and parent-source enrichment. DeepFinder intentionally does not. Migration is not a one-line swap; it's a several-week shadow-mode rollout.

Suggested migration sequence:

1. Build backfills for the missing kinds (linkedin_connection, google_connection) — Phase 9 territory in the rollout plan, but should be promoted earlier if Finder migration is a goal.
2. Carry over SIGNAL_WEIGHT, CONSECUTIVE_OVERLAP_TOLERANCE, and DIRECT_SIGNAL_TYPE mappings into DeepFinder (or a new UnifiedFinder that subsumes both).
3. Shadow-mode the new finder behind a flag — call both for every request, compare result sets, log divergence.
4. Once shadow shows ≥99% parity for >1 week, swap the controller binding. Keep Finder in the codebase as fallback for one release cycle.
5. Delete Finder + the CWE-self-join code path.

Estimated effort: 2–3 phases, not a single PR. Until this lands, the spec's "unified ContactConnection model" claim should be read as "unified for new consumers; legacy 2-hop reader still uses the old shape."

7. Operational envelope

Quantitative choices, rate limits, and volume estimates — the numbers reviewers ask about when deciding whether this will scale or what it will cost.

8. Phased plan

9. FAQ

The sharp questions reviewers already have.

10. Open questions

Findem capability confirmations

1. F1 — person lookup by email: does the endpoint exist? Rate limits, cost per call, response schema, confidence scoring?
2. F3 — email-only enrichment: fallback when no handle is known?
3. F4 — user enrichment: any product/TOS concern enriching authenticated users vs. external contacts?
4. F6 — investor data: does Findem expose company investor / funding rounds?
5. LinkedIn degree: does Findem track 1st-degree LinkedIn connections? Long shot; would close the last V1 gap.

Team decisions before execution

1. Ship Slice A (thin V1, 50% heuristic activation) as the first customer-facing release, or wait for Slice C?
2. "Sustained at lower volume" threshold — recommend 8 of last 8 quarters continuous. Alternative: 6 of 8.
3. Reachability editable? Recommend no in V1 — it's a pure aggregation, editing would create drift.
4. Strength-override audit: when a user overrides, keep a record of what the rule engine would have said (for future rule tuning)?
5. Backfill horizon: 24 months default. Confirm.
6. Browser-extension LinkedIn capture: worth a short spike to see if 1st-degree data is already being scraped.

11. File references

Key existing Getro code to extend or mirror.

Backend

Admin portal

12. Decision records

Short ADRs for the non-obvious choices landed during this spike. The "why we picked this" future maintainers will ask.