Apache AGE: graph-in-Postgres

1. Overview

Everything in the canonical 008 spec stays the same — InteractionEvent ingestion, OAuth, Findem enrichment, rule engine, UI. What changes: the storage substrate for connection-path edges and graph traversal queries. Per-pair caches and rollups can live in either standard Postgres tables or AGE-managed graph storage; the choice is per-cache, not all-or-nothing.

1.1 What Apache AGE actually is

• Postgres extension (installed via CREATE EXTENSION age;) that adds graph database semantics to a Postgres database. Open-source, Apache 2.0.
• Same database, same connection: graph data is stored in tables AGE manages internally (ag_label_vertex, ag_label_edge) but your app reads/writes via Cypher inside SQL queries.
• Cypher syntax inside cypher() function calls — you embed openCypher queries inside SQL. Not a separate query language at the protocol level.
• One repo to deploy, one DB to back up, one credential to rotate.

1.2 Why this variant exists

The canonical 008 plan handles 1+2 hop queries beautifully but degrades for 3-hop traversal (recursive CTEs explode at branching factor) and lacks native shortest-path. AGE is the smallest possible step toward graph capability without adopting a separate graph database.

2. System architecture

The pipeline shape is the same as canonical 008. The change is purely at the storage layer: graph edges and certain caches live in AGE-managed graph storage instead of vanilla Postgres tables. Workers still write via after_commit; reads still happen via the same Rails service layer; the only difference is whether a query goes through SQL or through cypher().

2.1 What's same as canonical 008

• Source ingestion (Phase 1–3, 6, 7) is identical.
• Per-pair caches (WorkOverlapCache, UserContactInteractionStats) stay as standard Postgres tables — no graph value-add for per-pair lookups.
• List-view rollup (SharedListNetworkSummary) stays as a standard Postgres table — aggregation is SQL's home turf.
• Reachability rollup (ContactReachability) stays as a standard Postgres table.

2.2 What's different

• Cross-pair edges (ContactCoemploymentEdge, etc.) stored as AGE graph edges instead of Postgres tables.
• Drill-in queries use Cypher via cypher() instead of recursive CTEs.
• Shortest-path queries become available via Cypher's built-in shortestPath().
• 3-hop traversal becomes idiomatic instead of a recursive-CTE workaround.

3. Data model (graph schema)

One AGE graph named getro_network. Five vertex labels, four edge labels for v1.

3.0 What lives where

Before reading the schema, anchor on the storage boundary. Postgres remains the source of truth. AGE adds graph storage as a derived index for traversal queries — it does not replace the relational tables.

3.1 Schema setup

-- One-time graph creation
LOAD 'age';
SET search_path = ag_catalog, "$user", public;
SELECT create_graph('getro_network');

-- Vertex labels
SELECT create_vlabel('getro_network', 'Contact');
SELECT create_vlabel('getro_network', 'Organization');
SELECT create_vlabel('getro_network', 'School');
SELECT create_vlabel('getro_network', 'TeamUser');
SELECT create_vlabel('getro_network', 'Collection');

-- Edge labels
SELECT create_elabel('getro_network', 'WORKED_AT');
SELECT create_elabel('getro_network', 'STUDIED_AT');
SELECT create_elabel('getro_network', 'SHARED_IN');     -- TeamUser→Contact via Collection
SELECT create_elabel('getro_network', 'CURRENT_AT');    -- Contact→Org for current employment

3.2 Vertex properties

3.3 Edge properties

3.4 Indexes

AGE supports B-tree indexes on vertex/edge properties via standard Postgres syntax:

CREATE INDEX ON getro_network."Contact" USING BTREE ((properties->>'id'));
CREATE INDEX ON getro_network."Organization" USING BTREE ((properties->>'id'));
CREATE INDEX ON getro_network."WORKED_AT" USING BTREE ((properties->>'organization_id'),
                                                       (properties->>'date_from'),
                                                       (properties->>'date_to'));

Property-based indexes are functional indexes on the underlying properties jsonb column. Performance is acceptable but slightly worse than native typed columns in vanilla Postgres tables.

4. Query patterns

Side-by-side comparison: canonical 008 (recursive CTEs in vanilla Postgres) vs the AGE variant (Cypher inside Postgres). Same physical database, different syntax.

4.1 Direct connection (1-hop)

SELECT contact_id, current_title
FROM   collection_org_current_shared_contacts
WHERE  collection_id = $1
  AND  organization_id = $2;

SELECT * FROM cypher('getro_network', $$
  MATCH (u:TeamUser)-[s:SHARED_IN]->(c:Contact)
        -[ca:CURRENT_AT]->(o:Organization)
  WHERE s.collection_id = $1
    AND o.id = $2
  RETURN c.id, ca.title
$$, $1::int, $2::int) AS (id agtype, title agtype);

For 1-hop, AGE is a step backward — Cypher syntax adds noise without traversal value. Recommendation: keep direct-connection queries in vanilla Postgres tables.

4.2 Work-overlap intro path (2-hop)

-- Reads precomputed ContactCoemploymentEdge
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)
ORDER BY overlap_to DESC;

-- Computes overlap at query time
SELECT * FROM cypher('getro_network', $$
  MATCH (u:TeamUser)-[s:SHARED_IN]->(via:Contact)
        -[w1:WORKED_AT]->(bridge:Organization)
        <-[w2:WORKED_AT]-(target:Contact)
        -[:CURRENT_AT]->(targetOrg:Organization)
  WHERE s.collection_id = $1
    AND targetOrg.id = $2
    AND w1.date_from <= w2.date_to
    AND w2.date_from <= w1.date_to
  RETURN via.id, target.id, bridge.id,
         w1.date_from, w1.date_to
  ORDER BY w1.date_to DESC
$$, ...) AS (...);

AGE is more readable for 2-hop. But it's also doing more work — every query recomputes overlaps that the canonical plan precomputes. Latency parity at depth 2 is roughly equal once indexes are tuned.

4.3 Extended intro path (3-hop)

WITH RECURSIVE paths AS (
  SELECT contact_id AS via, 0 AS depth, ARRAY[]::int[] AS path
  FROM   user_contact_collections
  WHERE  collection_id = $1
    AND  source = 'shared'
  UNION ALL
  SELECT edge.other_contact_id, p.depth + 1,
         p.path || edge.organization_id
  FROM   paths p
  JOIN   contact_coemployment_edges edge
         ON edge.via_contact_id = p.via
  WHERE  p.depth < 3
)
SELECT * FROM paths
WHERE via IN (SELECT contact_id
              FROM collection_org_current_shared_contacts
              WHERE collection_id = $1
                AND organization_id = $2);

SELECT * FROM cypher('getro_network', $$
  MATCH p = (u:TeamUser)-[:SHARED_IN]->
            (via:Contact)
            -[:WORKED_AT*1..3]->(bridge:Organization)
            <-[:WORKED_AT*1..3]-(target:Contact)
            -[:CURRENT_AT]->(o:Organization {id: $2})
  WHERE all(rel IN relationships(p)
            WHERE rel.date_from IS NOT NULL)
  RETURN p
  LIMIT 50
$$, ...) AS (path agtype);

This is where AGE earns its keep. The 3-hop CTE is verbose, brittle, and gets uglier with weights or filters. Cypher's path syntax is a clear improvement. Performance: AGE is ~2× faster than the recursive CTE at depth 3 with a typical branching factor.

4.4 Weighted shortest-path

SELECT * FROM cypher('getro_network', $$
  MATCH p = shortestPath(
    (u:TeamUser {id: $1})-[*..4]-(target:Contact)
    -[:CURRENT_AT]->(o:Organization {id: $2})
  )
  RETURN p, length(p) AS hops
$$, $1::int, $2::int) AS (path agtype, hops agtype);

AGE supports shortestPath(). Weighted variants require Dijkstra-style accumulator patterns; not as elegant as Neo4j but doable.

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 AGE variant. The point of this section: most heuristics don't change at all. AGE earns its keep on a specific subset where graph traversal or pattern matching is the natural shape.

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

Each clause evaluates against an in-memory (team_user, contact) stats row. AGE only enters the picture when the clause needs a graph traversal (LinkedIn 1st-degree edge, coemployment shape, school-overlap fact).

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. SELECT collection_id, contact_id, COUNT(*) FILTER (WHERE strength_tier = 'warm') AS warm_count ...
• AGE involvement: none. Aggregation is SQL's home turf; cypher() wrapping would slow this down 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. The picker reads UCC strength tiers, applies a deterministic rule (Warm beats Known beats Cold; within tier, highest strength_score), writes ContactReachability.key_user_id.

• Substrate: PG primary — the picker is a sort-and-pick on UCC rows.
• AGE bonus opportunity: if you want "best path to the contact" (not just best direct UCC), AGE shines. Cypher's shortestPath() returns the cheapest weighted route through the graph — useful when the contact has no direct UCC owner but is reachable via 2-hop intro.
• Honest assessment: V1 picks based on direct UCC; AGE doesn't help. V2 with weighted multi-hop key-connection lookup is where Cypher meaningfully wins.

5.4 Connection paths (007 — direct + intro paths)

This is where AGE genuinely earns its keep. Every 007 path type maps to a graph traversal, and Cypher pattern matching is more natural than recursive CTEs.

5.5 Honest summary

Of 23 distinct queries the system needs to answer (16 strength clauses + reachability + key connection + 6 path types), AGE meaningfully helps on ~9. The remaining 14 stay in Postgres because they're either counter aggregations (most strength clauses) or rollup queries (reachability, list view).

• Pure win zone: 007 connection-path queries (5–6 queries). Cypher is genuinely cleaner here.
• Modest win zone: 008 hybrid clauses (W3, W5, K4, K5, K6, C3, C4) — 7 queries. Cypher shaves complexity but isn't transformative.
• No-help zone: counter-aggregation strength clauses (W1, W2, W4, K1–K3, C1, C2, C5) and reachability rollup. Stay vanilla Postgres.

The decision becomes clear: AGE is worthwhile if 007 connection-path traversal is the dominant new feature. If the system is mostly 008 strength scoring (per-pair counters), AGE adds infrastructure for limited value.

6. Caching architecture

The three-layer cache strategy from canonical 008 §6 still applies. The difference is which layer lives where:

The critical decision: do we materialize coemployment edges in AGE, or compute them at query time?

• Compute at query time (recommended for v1): smaller graph, no write amplification for derived edges, pattern matching does the work. Read latency scales with branching factor.
• Materialize as :COWORKER_OF edges (option for v2): faster reads at depth 3+, larger graph, write amplification on every WORKED_AT change. Worth it only if 3-hop drill-in becomes a hot path.

7. Operational envelope

8. Migration cost

Effort to move from canonical 008 to AGE variant. Assumes 008 v1 has shipped (Postgres tables with coemployment edges).

Total: ~3–4 weeks engineering, plus a 1-week canary. Add 1–3 weeks if hosting migration is required.

9. What you give up

8.1 Hosting compatibility deserves its own paragraph

This is the most likely deal-breaker. Confirm with the team's infra owner that current Postgres hosting supports AGE before any further investment in this variant. If hosting requires migration, the effort estimate doubles and the operational complexity argument starts to look more like the Neo4j variant — at which point Neo4j may be the cleaner choice anyway.

10. Mini-ADR