The Knowledge Graph Architecture: How AiFiler Stores Relationships at Scale

You've got 5,000 documents. A client contract references three proposals, which mention two vendors, which appear in five other contracts. A team member's email thread connects to a Slack conversation, which connects to a design file. Finding all of it shouldn't require manual hunting—it should be automatic.

That's what the AiFiler knowledge graph does. It's not just a search index. It's a map of how your documents relate to each other: who wrote what, what references what, which concepts appear together, which clients appear in which projects.

But building that at scale is hard. Query a traditional relational database with seven joins and you're waiting seconds. Query a graph database without the right indexes and you're waiting longer. We needed something that felt instant while handling thousands of documents and hundreds of thousands of relationships.

The Problem We Solved

Most document tools treat files as isolated objects. You search for "Q4 budget" and get back a list. You have to manually figure out which budget connects to which project, which project connects to which client.

We wanted the opposite: show the connections first. When you find one document, the graph should immediately surface related documents, key people, referenced concepts, and project context. All without making you wait.

The challenge: doing this with eight different types of relationships (document-to-document, document-to-person, document-to-concept, etc.) while keeping query latency under 200ms. That meant rethinking how we stored and indexed the data.

The Architecture: Three Layers

Our knowledge graph sits on three layers:

Layer 1: The Relationship Store (Supabase PostgreSQL)

We use a single relationships table with a flexible schema:

relationships (
  id uuid,
  source_id uuid,        -- document, person, or concept
  source_type text,      -- 'document' | 'person' | 'concept'
  edge_type text,        -- 'references' | 'authored_by' | 'mentions' | etc.
  target_id uuid,
  target_type text,
  metadata jsonb,        -- weight, confidence, context
  created_at timestamp,
  updated_at timestamp
)

Why not eight separate tables? Because relationships are directional and cross-type. A document references a person, a person is associated with a concept, a concept appears in multiple documents. One schema handles all of it.

The edge_type column is the key. We support eight types:

• references (document → document)
• authored_by (document → person)
• mentions (document → concept)
• associated_with (person → concept)
• belongs_to (document → project)
• related_to (concept → concept)
• cites (document → external source)
• contains (folder → document)

Layer 2: The Indexing Strategy

Raw relationships are useless without fast lookups. We maintain three critical indexes:

1. Source-target index: (source_id, source_type, edge_type, target_id) — for "find everything this document references"
2. Reverse index: (target_id, target_type, edge_type, source_id) — for "find everything that references this document"
3. Type-based index: (source_type, edge_type, target_type) — for "find all person-to-concept relationships"

These are partial indexes. We only index relationships created in the last 90 days or marked as "high confidence." This keeps the index size manageable while covering 95% of real queries.

Layer 3: The Query Layer (TypeScript + SWR)

On the client side, we use SWR (stale-while-revalidate) with localStorage prefixing to cache relationship queries. When you open a document, we fetch its relationships in parallel:

// From lib/intelligence/universalRouter.ts pattern
const { data: references } = useSWR(
  `/api/relationships?source=${docId}&edge_type=references`,
  fetcher,
  { revalidateOnFocus: false }
);

const { data: authors } = useSWR(
  `/api/relationships?source=${docId}&edge_type=authored_by`,
  fetcher
);

const { data: mentions } = useSWR(
  `/api/relationships?source=${docId}&edge_type=mentions`,
  fetcher
);

Each query hits the database, but the indexes make them fast. And SWR keeps the data warm in the browser, so switching between documents doesn't require re-fetching.

How Relationships Get Built

Relationships don't appear magically. They're built three ways:

1. During File Ingestion

When you upload a document, our parsing pipeline (in lib/ingest/parseFile.ts) extracts:

• Explicit mentions: "See Q4_Budget_2024.xlsx" → creates references edge
• Metadata: Document author → creates authored_by edge
• Named entities: Company names, people, dates → creates mentions edges to concepts

2. Via the Intelligence System

The Universal Command (Ctrl+Shift+A) lets you create relationships manually:

> Link this to Q4 budget
> Add John Smith as author
> Tag this as confidential

These go through the intent handlers in lib/intelligence/intentHandlers.ts, which validate the relationship and insert it into the graph.

3. Via Background Indexing

Every night, we run a job that:

• Analyzes document text for implicit relationships (mentions of other documents, people, projects)
• Calculates relationship confidence scores (0-1)
• Merges duplicate relationships
• Prunes relationships older than 180 days with low confidence

This is where the metadata.confidence field matters. A relationship created during parsing might have confidence 0.7. If a user manually confirms it, it jumps to 1.0 and gets higher priority in queries.

The Edge Cases We Hit

Problem 1: Circular References

A document references B, B references C, C references A. Naive graph traversal goes infinite. We solve this with a visited set and a max-depth limit of 3:

function getRelatedDocuments(docId: string, depth = 0, visited = new Set()) {
  if (depth > 3 || visited.has(docId)) return [];
  visited.add(docId);
  
  const direct = await fetchRelationships(docId);
  const indirect = direct.flatMap(rel => 
    getRelatedDocuments(rel.target_id, depth + 1, visited)
  );
  
  return [...direct, ...indirect];
}

Problem 2: Stale Relationships

When you delete a document, its relationships become orphaned. We handle this with a soft-delete pattern: mark the relationship as deleted_at = now() and exclude it from queries. A weekly cleanup job hard-deletes relationships older than 30 days.

Problem 3: Relationship Explosion

A large document might mention 200 concepts. That's 200 edges. A person might be associated with 50 projects. That's 50 more edges. Without aggressive filtering, the graph becomes noise.

We solve this by:

• Only storing relationships with confidence > 0.5
• Limiting mentions per document to the top 20 by relevance
• Using relationship weight (stored in metadata) to rank results

Why This Matters for You

The architecture translates to three practical benefits:

1. Discovery Without Search

Open any document in AiFiler. The right panel shows related documents, people, and concepts instantly. You're not searching—you're exploring. The graph does the work.

2. Batch Operations Know Context

When you use Batch Operations to move 100 documents, the system understands which documents belong together. It can suggest grouping by author, by project, by client. That intelligence comes from the graph.

3. The Intelligence System Gets Smarter

The Universal Command's intent handlers use the knowledge graph to understand context. When you say "Add this to the Q4 project," it doesn't just move the document—it creates relationships to all other Q4 documents, connects you as the editor, and tags it with the project concept. The graph makes that possible.

What We'd Do Differently

If we rebuilt this today, we'd consider two changes:

Vector embeddings for semantic relationships: Right now, relationships are explicit (document A references document B). We could add semantic edges: "this document is conceptually similar to this other document." That would require embedding every document and using vector similarity search. It's on the roadmap.

Real-time relationship updates: Currently, relationships are built during ingestion and updated nightly. For a truly live graph, we'd need to recalculate relationships as documents change. That's a performance problem we haven't solved yet.

The Takeaway

A knowledge graph isn't magic. It's careful schema design, strategic indexing, and pragmatic limits on depth and breadth. It's choosing PostgreSQL over a specialized graph database because the query patterns are simple enough and the data volume is manageable.

Most importantly, it's built for the way people actually work: they don't think in isolated documents. They think in projects, in relationships, in "this connects to that." The graph makes that thinking visible.

That's the architecture. It's not revolutionary. It's just engineering.