ArchitectureAdrADR-0026: GraphRAG Plain-SQL Implementation

ADR-0026: GraphRAG Plain-SQL Implementation

ADR-0026: GraphRAG Plain-SQL Implementation

Date: 2026-02-28 Status: Accepted Author: Architect Agent Supersedes: ADR-0011, ADR-0020

1. Context

In Phase S, Jorvis adopted Apache AGE as the GraphRAG backend (ADR-0011, ADR-0020). While functionally successful locally, moving to production revealed a critical infrastructure constraint: the production database (pgvector:pg15) does not include the age extension. We could not run Apache AGE inside the same container as the primary data warehouse, and running it as a sidecar introduced unacceptable synchronization complexity and potential drift.

Additionally, our graph use case (schema metadata and foreign keys) is relatively small and highly structured, primarily requiring simple neighbor expansion rather than deep multi-hop algorithms (like PageRank).

2. Decision

We will replace Apache AGE with a Plain-SQL graph implementation operating directly within the primary PostgreSQL database using standard tables and Recursive CTEs.

2.1 Architecture Changes

  1. Storage: Remove the jorvis-graph-db container.
  2. Schema: Create a graph_edges table directly in the primary database (in the jorvis schema) to store source_table, target_table, and the relationship type.
  3. Expansion: Rewrite GraphExpansionService to use a standard PostgreSQL Recursive CTE instead of Cypher queries.
  4. Sync: GraphSyncService will truncate and repopulate graph_edges by querying information_schema.key_column_usage from the local database.

2.2 Concurrency & Freshness (Phase 2)

To ensure operational stability and prevent sync overlap in a multi-instance deployment:

  • Locking: Utilize PostgreSQL Advisory Locks (pg_advisory_xact_lock) to serialize sync execution.
  • Scheduling: Implement GraphSyncScheduler using @nestjs/schedule to run background synchronization at a configurable interval (e.g., hourly).
  • Freshness Signals: Broadcast the last successful sync timestamp through SSE metadata (state.metadata.graphFreshness) to surface the graph state in the Open WebUI Transparency panel.

3. Data Model

CREATE TABLE jorvis.graph_edges (
    id SERIAL PRIMARY KEY,
    source_table VARCHAR(255) NOT NULL,
    target_table VARCHAR(255) NOT NULL,
    relationship_type VARCHAR(50) NOT NULL,
    metadata JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_graph_edges_source ON jorvis.graph_edges(source_table);
CREATE INDEX idx_graph_edges_target ON jorvis.graph_edges(target_table);
CREATE UNIQUE INDEX idx_graph_edges_unique ON jorvis.graph_edges(source_table, target_table, relationship_type);

4. Consequences

Positive

  • Zero Extension Dependencies: Removes the requirement for Apache AGE or Neo4j, making the system deployable on standard managed PostgreSQL instances (e.g., AWS RDS, standard GCP Cloud SQL).
  • Consolidated Infrastructure: Eliminates the jorvis-graph-db container and complex cross-container sync logic.
  • ACID Compliance: Graph data lives in the same transactional context as the core application data.

Negative

  • Loss of OpenCypher: We lose the expressiveness of the Cypher language, requiring SQL CTEs for graph traversal.
  • Algorithmic Limits: Complex graph algorithms (centrality, community detection) are harder to implement in pure SQL compared to dedicated graph databases.

5. Alternatives Considered

  1. Keep Apache AGE (Sidecar): Rejected. Requires cross-database connection/syncing, which introduces race conditions and complicates the single-tenant deployment model.
  2. Neo4j: Rejected. Introduces a heavy JVM dependency and separate licensing/infrastructure overhead.
  3. In-Memory Graph (NetworkX): Rejected. Lacks persistence and requires rehydrating the graph into application memory on every boot.

6. Implementation Status

  • Phase 1 (Plain-SQL): ✅ Implemented (PR #276). graph_edges deployed to production, AGE pool disabled.
  • Phase 2 (Robustness): ✅ Implemented (PR #279, #280, #281). Advisory locks, cron scheduler, and freshness SSE signals deployed.

7. Approval

  • Approved by: George (Release Authority)
  • Date: 2026-02-28
  • Notes: Fully deployed to production. This decision officially deprecates the Apache AGE era of Jorvis GraphRAG.