Data Model¶

Substrate uses a single PostgreSQL instance with Apache AGE + pgvector. All relational data, vector embeddings, SSE replay, and graph topology live in the one substrate_graph database. Keycloak has its own separate keycloak database in the same instance.

There is no substrate_ingestion database — the monorepo consolidated to a single data boundary.

PostgreSQL schema¶

sources¶

Connected code repositories.

Unique: (source_type, owner, name).

sync_runs¶

One row per ingestion execution.

Indexes include idx_sync_runs_source_completed and idx_sync_runs_active (partial). Partial unique ux_sync_runs_one_active_per_source guarantees one active (pending|running) sync per source.

sync_issues¶

Structured warnings/errors recorded during a sync.

Hard-capped at 1,000 issues per sync.

sync_schedules¶

Periodic sync configuration.

Unique: (source_id, interval_minutes).

file_embeddings¶

Main node table — one row per file per sync.

Unique: (sync_id, file_path).

content_chunks¶

AST / semantic chunks for RAG and file reconstruction.

Unique: (file_id, chunk_index). Indexes: idx_content_chunks_file, idx_content_chunks_sync, idx_content_chunks_fts (GIN FTS over content).

Heads-up: content_chunks.embedding is populated on every sync but not yet read by any graph-service endpoint. It's compute + storage cost with no user-visible effect today; planned use is chunk-level search / unified retrieval (see graph-service.md).

sse_events¶

SSE replay buffer — backs GET /api/events.

Writers call NOTIFY substrate_sse, <id>::text; the gateway LISTENs on the same channel and fans out to every open EventSource client, replaying past rows greater than the client's Last-Event-ID header on reconnect.

Apache AGE graph schema¶

AGE (a PostgreSQL extension) gives us Cypher inside Postgres — Substrate does not run Neo4j. The graph is named substrate.

Node types¶

:File — represents a source file. Properties:

• file_id → file_embeddings.id
• sync_id → sync_runs.id
• source_id → sources.id
• name → display name
• type → file classification
• domain → domain label

:Symbol — represents a named construct (function/class/method) extracted by a graph-builder plugin. Properties:

• symbol_id
• file_id → owning file
• sync_id, source_id
• name, kind (function | class | method), line

Relationship types¶

• depends_on — file-to-file dependency (inferred from resolved imports). Properties: sync_id, source_id, weight.
• defines — file-to-symbol (the file defines this symbol). Properties: sync_id, source_id.

Cypher patterns used¶

-- Count all edges (stats)
MATCH ()-[r]->() RETURN count(r)

-- Merged snapshot edges
MATCH (a:File)-[r:depends_on]->(b:File)
WHERE r.sync_id IN ['uuid1', 'uuid2']
RETURN a.source_id, a.file_id, b.source_id, b.file_id, r.weight, r.sync_id

-- Neighbors of a specific file (node detail panel, enriched summary)
MATCH (a:File {file_id: '...'})-[r]-(b:File)
RETURN b.file_id, label(r), r.weight, r.sync_id

The btree expression index on properties -> '"file_id"'::agtype (V5) keeps file-id lookups logarithmic against the File vertex table.

Snapshot query system¶

When the frontend requests a graph, the Graph Service runs a merged snapshot over the requested syncs:

1. Node resolution — a SQL window function picks the latest version of each file across the requested syncs:

   row_number() OVER (
     PARTITION BY source_id, file_path
     ORDER BY completed_at DESC
   )
   

2. Divergence detection — a node is marked divergent: true when the same (source_id, file_path) appears in multiple requested syncs with different content_hash.
3. Edge retrieval — queries AGE for all depends_on edges where r.sync_id is in the requested set, deduplicates, and aggregates by (source, target).

Node identity format¶

Nodes in the API and frontend are identified by:

src_<source_id>:<file_path>

Example:

src_550e8400-e29b-41d4-a716-446655440000:src/main.py

Used in: - Frontend graph element IDs - API path parameters (/api/graph/nodes/{node_id:path}) - AGE neighbor resolution

Embeddings pipeline¶

File-level (file_embeddings.embedding)¶

Source string (from services/ingestion/src/chunker.py::file_summary_text):

path: <file_path>
type: <source|test|config|…>
language: <python|typescript|…>

<first 100 lines of file>

This string is prefixed with search_document:, hard-truncated to 1400 chars, and sent to the embedding LLM. The returned 896-dim vector is backfilled into file_embeddings.embedding after the initial row insert.

Chunk-level (content_chunks.embedding)¶

Each chunk's content (which already includes a breadcrumb header # file: <path>\n# in: <ancestor chain>\n\n<body>) is prefixed with search_document:, truncated to 1400 chars, and embedded. Populated but not yet queried by any endpoint.

Query-side¶

The graph service's /api/graph/search endpoint embeds the incoming query as search_query: <q> — the same embedding model; the search_document: / search_query: prefix scheme is how jina-code-embeddings clusters corpus vs queries.

Current model config¶

See services/graph/migrations/postgres/V4, V7, V8, V9, V10 for the history of embedding-dim migrations as models changed.

Enriched summary pipeline¶

Separate from embeddings. Triggered by GET /api/graph/nodes/{id}/summary. Pipeline:

1. Read file_embeddings row + all content_chunks for the file
2. Reconstruct the full file text via line-overlap dedup (reconstruct_chunks)
3. Fetch AGE edge neighbors, pull their cached file_embeddings.description + first 8 lines of their first chunk
4. Rank neighbors by cosine similarity of file_embeddings.embedding
5. Assemble prompt: full file (capped at 88 % of 100 k char budget) + top-10 neighbors (10 % of budget)
6. Call dense LLM (temperature=0.2, max_tokens=400, enable_thinking=false)
7. Cache output in file_embeddings.description + description_generated_at

Retry at [1.0, 0.5, 0.25] budget scales on HTTP 400 with a context-window error message.

Data retention¶