Neo4j: dedicated graph database

1. Overview

Like the AGE variant, everything in canonical 008 stays the same — InteractionEvent ingestion, OAuth, Findem enrichment, rule engine, UI. Postgres remains the source of truth and stores all per-pair caches and rollups. Neo4j is added for one purpose only: traversal queries that exceed what Postgres can do efficiently.

1.1 What Neo4j brings to the table

• Native graph storage: nodes and edges as first-class objects with O(1) traversal between them. No row-based table walks.
• Cypher: the original openCypher implementation, mature query language designed for traversal.
• Built-in algorithms: shortestPath(), allShortestPaths(), weighted Dijkstra, A*, PageRank, community detection — all native, all production-tested.
• Bidirectional search: at 4+ hop, Neo4j explores from both ends simultaneously, dramatically pruning the search space.

1.2 Why this variant exists

The canonical Postgres plan handles 1+2 hop queries beautifully and 3-hop adequately. The AGE variant pushes 3-hop and shortest-path into Postgres-native graph syntax. Neo4j is the option for when traversal queries become structurally important enough to justify their own infrastructure — typically when 4-hop, weighted shortest-path, or centrality become product requirements.

2. System architecture

Two databases. Postgres is the source of truth. Neo4j is a derived index optimized for graph traversal. A sync pipeline keeps them consistent.

2.1 What's same as canonical 008

• All source ingestion (Phase 1–3, 6, 7) is identical.
• Postgres remains the source of truth. Every record exists in Postgres first.
• Per-pair caches and rollups stay in Postgres tables.

2.2 What's different

• Neo4j cluster stands alongside Postgres in production.
• Sync pipeline: every after_commit from a relevant table writes to both Postgres and Neo4j (dual-write) or via change-data-capture.
• Cypher service layer: a Rails service for graph queries using the official Bolt-protocol Ruby driver.
• Drill-in queries with traversal > 2 hops hit Neo4j; everything else stays in Postgres.

3. Data model (graph schema)

One Neo4j database. Five vertex labels, four relationship types for v1. Schema constraints define uniqueness and indexed properties.

3.0 What lives where

Before reading the schema, anchor on the storage boundary. Postgres remains the source of truth. Neo4j is added as a derived graph store for traversal queries — it does not replace any relational tables.

3.1 Schema setup

// Constraints (uniqueness + creates indexes automatically)
CREATE CONSTRAINT contact_id IF NOT EXISTS FOR (c:Contact) REQUIRE c.id IS UNIQUE;
CREATE CONSTRAINT org_id IF NOT EXISTS FOR (o:Organization) REQUIRE o.id IS UNIQUE;
CREATE CONSTRAINT school_id IF NOT EXISTS FOR (s:School) REQUIRE s.id IS UNIQUE;
CREATE CONSTRAINT user_id IF NOT EXISTS FOR (u:TeamUser) REQUIRE u.id IS UNIQUE;

// Additional property indexes
CREATE INDEX contact_email IF NOT EXISTS FOR (c:Contact) ON (c.primary_email);
CREATE INDEX worked_at_dates IF NOT EXISTS FOR ()-[r:WORKED_AT]-() ON (r.date_from, r.date_to);
CREATE INDEX shared_in_collection IF NOT EXISTS FOR ()-[r:SHARED_IN]-() ON (r.collection_id);

3.2 Node labels

3.3 Relationship types

3.4 Native typing wins here

Unlike AGE's jsonb properties, Neo4j has native types: date, datetime, bool, int, string. Date-overlap predicates run on indexed typed properties; performance is materially better than jsonb-based pattern matching.

4. Query patterns

Side-by-side: canonical Postgres (recursive CTE) vs Neo4j (Cypher).

4.1 Direct connection (1-hop)

Stays in Postgres. Neo4j wouldn't add value for keyed lookups.

-- Canonical Postgres (recommended for 1-hop)
SELECT contact_id, current_title
FROM   collection_org_current_shared_contacts
WHERE  collection_id = $1 AND organization_id = $2;

4.2 Work-overlap intro path (2-hop)

Either substrate works; Postgres is faster for 2-hop in practice once ContactCoemploymentEdge is precomputed.

SELECT via_contact_id, other_contact_id,
       organization_id, overlap_from, overlap_to
FROM   contact_coemployment_edges
WHERE  collection_id = $1
  AND  other_contact_id IN (
         SELECT contact_id
         FROM   collection_org_current_shared_contacts
         WHERE  collection_id = $1
           AND  organization_id = $2);

MATCH (u:TeamUser)
      -[s:SHARED_IN {collection_id: $cid}]->
      (via:Contact)
      -[w1:WORKED_AT]->(bridge:Organization)
      <-[w2:WORKED_AT]-(target:Contact)
      -[:CURRENT_AT]->
      (targetOrg:Organization {id: $oid})
WHERE w1.date_from <= w2.date_to
  AND w2.date_from <= w1.date_to
RETURN via, target, bridge, w1, w2
ORDER BY w1.date_to DESC;

4.3 Extended intro path (3-hop and 4-hop)

This is where Neo4j wins decisively. Variable-length path matching with a depth bound is one Cypher line; the Postgres recursive CTE equivalent is dozens of lines and starts to fall over at depth 4.

// 4-hop intro path with date-overlap predicate
MATCH path = (u:TeamUser {id: $uid})
             -[:SHARED_IN {collection_id: $cid}]->
             (start:Contact)
             -[:WORKED_AT*1..4]-(:Contact)
             -[:CURRENT_AT]->
             (target:Organization {id: $oid})
WHERE all(rel IN [r IN relationships(path) WHERE type(r) = 'WORKED_AT']
          WHERE rel.date_from IS NOT NULL)
RETURN path, length(path) AS hops
ORDER BY hops ASC
LIMIT 25;

4.4 Weighted shortest-path

Native, sub-second.

// Find the cheapest intro path to anyone at TargetCo
MATCH (u:TeamUser {id: $uid}),
      (target:Organization {id: $oid})
CALL apoc.algo.dijkstra(u, target, 'WORKED_AT|SHARED_IN|CURRENT_AT', 'weight')
YIELD path, weight
RETURN path, weight
ORDER BY weight ASC
LIMIT 5;

Edge weights can be set per relationship at write time (e.g., SHARED_IN = 1, WORKED_AT overlap = 3, longer time gaps = higher weight). Adjust weights freely; queries adapt without changing structure.

4.5 Network analytics

Neo4j Graph Data Science library ships PageRank, betweenness centrality, community detection, etc. Useful for "find super-connectors in my network" or "cluster contacts by community" features. Out of scope for v1 but available as a future product capability.

5. Heuristic mapping

Every signal the system computes — every strength clause, the reachability rollup, key connection picker, and all 007 connection-path types — mapped to where it actually executes in the Neo4j variant. The point of this section: most heuristics still execute in Postgres. Neo4j earns its keep on traversal-shaped queries and unlocks graph algorithms (centrality, community detection) that no other substrate can match.

5.1 Strength heuristics (008 — Warm / Known / Cold)

Counter-aggregation clauses stay in Postgres. Neo4j is invoked only when a clause needs graph traversal. The Postgres ↔ Neo4j boundary is identical to the AGE variant; only the graph-side syntax differs.

5.2 Reachability (008 — High / Medium / Low / None)

Reachability rolls up strength tiers across all team members for a given (contact, collection): count Warm / Known / Cold owners, apply tier rules, write to ContactReachability.

• Substrate: PG only
• Why: pure aggregation of UCC strength columns. SQL GROUP BY with FILTER clauses.
• Neo4j involvement: none. Aggregation is SQL's home turf; round-tripping to Neo4j costs latency without value.

5.3 Key Connection (008 — best person on team to ask for an intro)

Per (contact, collection), pick the team member with the strongest tie. Two flavors:

5.3.1 V1 — direct UCC pick

• Substrate: PG only
• Sort UCCs by tier (Warm > Known > Cold) then by strength_score; take first row.
• Neo4j adds nothing; PG ORDER BY + LIMIT 1 is fastest.

5.3.2 V2 — best weighted path including indirect intros

• Substrate: Neo4j wins
• Cypher with shortestPath() or apoc.algo.dijkstra finds the cheapest weighted route from any team user to the contact, allowing intermediate via-contacts.
• Native algorithm; sub-200ms even at depth 4. Equivalent in vanilla PG would be a custom recursive CTE with weighted cost accumulation — possible but ugly.
• Sample query: MATCH (u:TeamUser), (c:Contact {id: $cid}) CALL apoc.algo.dijkstra(u, c, 'WORKED_AT|STUDIED_AT|SHARED_IN', 'cost') YIELD path, weight RETURN path, weight ORDER BY weight ASC LIMIT 1.

5.4 Connection paths (007 — direct + intro paths)

This is where Neo4j shines structurally. Native graph storage, native shortest-path, native multi-edge-type traversal. Latency at depth 3–4 is materially better than AGE.

5.5 Honest summary

Of 23 distinct queries the system needs to answer in v1/v2 (16 strength clauses + reachability + key connection + 6 path types), Neo4j meaningfully helps on ~9 — same as AGE. The Neo4j-specific upside shows up beyond v2:

• 4-hop traversal becomes feasible (~600ms vs infeasible in PG, slow in AGE).
• Native shortest-path with weights for Key Connection v2 and best-intro-path features.
• Graph Data Science library opens centrality, community detection, PageRank — capabilities that don't exist in AGE or PG.

The decision becomes clear: Neo4j is worthwhile only if 4-hop traversal, weighted shortest-path, or graph algorithms become product features within the next 2–3 quarters. For the canonical 008 + 007 v1/v2 scope, the gap between Neo4j and AGE is mostly syntactic; the operational overhead is what determines the answer.

6. Caching architecture

The layered cache strategy from canonical 008 §6 still applies. Same per-cache reasoning, different placement.

Neo4j's role is narrow: it answers traversal-shaped questions. Aggregation, filtering by enum, sorting by computed columns — all stay in Postgres. The result is a hybrid where each query lands in the substrate that handles it best.

7. Sync layer (the tricky part)

The biggest engineering cost in this variant. Postgres is source of truth; Neo4j must stay in sync. Three approaches, increasing complexity.

6.1 Dual-write from Sidekiq workers

The simplest approach. Every after_commit hook that writes to Postgres also enqueues a Sidekiq job that writes to Neo4j.

class ContactWorkExperience < ApplicationRecord
  after_commit :enqueue_neo4j_sync, on: [:create, :update, :destroy]

  def enqueue_neo4j_sync
    Neo4j::WorkExperienceSyncWorker.perform_async(id, action_taken)
  end
end

• Pros: easy to reason about; all sync logic in Sidekiq; same retry semantics as other workers.
• Cons: dual-write race conditions (Postgres committed but Neo4j write failed). Need reconciliation jobs to detect drift. If Sidekiq is backed up, Neo4j lags.

6.2 Change-data-capture (CDC) via Debezium / logical replication

Postgres logical replication streams every committed change to a CDC consumer that writes to Neo4j.

• Pros: at-most-once delivery semantics from the WAL; no race conditions; lag is observable.
• Cons: significant ops complexity; new infrastructure (Debezium, Kafka or equivalent); CDC schema mapping per table.

6.3 Periodic full rebuild

Nightly cron drops the Neo4j graph and rebuilds from scratch by reading Postgres.

• Pros: simplest; correctness guaranteed.
• Cons: stale by up to 24h; rebuild may take hours at scale.

6.4 Recommended: dual-write + nightly reconciliation

Start with 6.1 (dual-write) for write path. Add a nightly reconciliation job that compares row counts and sample-checks key entities; flags drift. Move to CDC only if dual-write proves unstable in production.

8. Operational envelope

9. Migration cost

Total: 6–10 weeks engineering, plus a 2-week canary. Add 2–4 weeks for any team upskilling on Cypher.

10. What you give up

9.1 The honest summary

Neo4j is the right answer when graph traversal is core to the product, not when it's a side feature. If 4+ hop traversal, weighted shortest-path, and graph algorithms are 30%+ of the read pattern, Neo4j pays for itself. If they're occasional drill-in queries, the operational cost outweighs the latency win — that's when AGE or vanilla Postgres CTEs are the right answer.

11. Mini-ADR