Surrealdb

SurrealDB Skill

This skill provides comprehensive guidance for working with SurrealDB in the Bookstrap framework, covering database patterns, query strategies, and hybrid retrieval-augmented generation (RAG).

SurrealDB Overview

SurrealDB is a multi-model database that combines:

• Document storage: JSON-like records
• Graph database: Native relationships with RELATE statements
• Vector search: Native embedding support with MTREE indexes
• SQL-like queries: Familiar SurrealQL syntax

This makes it ideal for book writing, where you need:

• Semantic search (find similar content)
• Graph traversal (character relationships, citation chains)
• Timeline queries (chronological events, sequences)
• Combined queries (semantic + graph + timeline)

Core Database Patterns

1. Hybrid RAG Architecture

Hybrid RAG combines three retrieval strategies:

Semantic Search    +    Graph Traversal    +    Timeline Queries
     (vectors)              (relationships)           (sequences)
        ↓                        ↓                         ↓
                   Combined Context
                        ↓
                  Writing/Editing

Why Hybrid?

• Semantic alone: Misses explicit relationships
• Graph alone: Misses conceptual similarity
• Timeline alone: Misses both semantics and structure
• Combined: Comprehensive context retrieval

2. Vector Search Pattern

SurrealDB uses MTREE indexes for efficient k-nearest neighbor (kNN) search.

Basic Vector Search:

-- Find similar sections by semantic meaning
SELECT *, vector::similarity::cosine(embedding, $query_vector) AS similarity
FROM section
WHERE vector::similarity::cosine(embedding, $query_vector) > 0.7
ORDER BY similarity DESC
LIMIT 5;

Vector Index Configuration:

DEFINE INDEX idx_section_embedding
    ON section
    FIELDS embedding
    MTREE DIMENSION 1536 DIST COSINE TYPE F32;

Important: Embedding dimensions must match your provider:

• Gemini text-embedding-004: 768 dims
• OpenAI text-embedding-3-small: 1536 dims
• Ollama nomic-embed-text: 768 dims

3. Graph Traversal Pattern

Use -> and <- operators to traverse relationships.

Outbound Traversal (following relationships):

-- Get all sections where character Anna appears
SELECT ->appears_in->section.*
FROM character:anna;

Inbound Traversal (reverse relationships):

-- Get all characters in a section
SELECT <-appears_in<-character.*
FROM section:ch3_s2;

Multi-hop Traversal:

-- Get characters known by Anna's friends
SELECT ->knows->character->knows->character.*
FROM character:anna;

4. Timeline Query Pattern

Use sequence fields and datetime for chronological ordering.

Sequence-Based:

-- Get events before current point
SELECT * FROM event
WHERE sequence < $current_sequence
ORDER BY sequence DESC
LIMIT 10;

Date-Based:

-- Get events in date range
SELECT * FROM event
WHERE date >= $start_date
  AND date <= $end_date
ORDER BY date ASC;

Query Strategy Selection

Choose query strategy based on retrieval goal:

Pre-Write Query Workflow

Before writing a section, gather context using hybrid queries:

Step 1: Semantic Search

Find conceptually similar content:

-- Find related sections by theme
SELECT *, vector::similarity::cosine(embedding, $query_embedding) AS similarity
FROM section
WHERE vector::similarity::cosine(embedding, $query_embedding) > 0.7
ORDER BY similarity DESC
LIMIT 5;

Step 2: Graph Context

Get related entities and relationships:

-- Get characters and their relationships
SELECT
    c.name,
    c.description,
    ->knows->character.name AS knows,
    ->appears_in->section.id AS appears_in
FROM character c
WHERE c.id IN $character_ids;

Step 3: Timeline Context

Get chronological context:

-- Get recent events
SELECT * FROM event
WHERE sequence < $current_sequence
ORDER BY sequence DESC
LIMIT 10;

Step 4: Citation Chain

Get source support:

-- Get sources for concept
SELECT
    <-supports<-source.title,
    <-supports<-source.reliability,
    <-supports<-source.url
FROM concept:wireless_protocols;

Hybrid Query Patterns

Combine multiple strategies for comprehensive context.

Pattern 1: Semantic + Graph

-- Find similar sections that mention Anna
SELECT
    s.*,
    vector::similarity::cosine(s.embedding, $query_embedding) AS similarity
FROM section s
WHERE vector::similarity::cosine(s.embedding, $query_embedding) > 0.7
  AND s->appears_in->character:anna
ORDER BY similarity DESC
LIMIT 5;

Pattern 2: Semantic + Timeline

-- Find similar sections before current point
SELECT
    s.*,
    vector::similarity::cosine(s.embedding, $query_embedding) AS similarity
FROM section s
WHERE vector::similarity::cosine(s.embedding, $query_embedding) > 0.7
  AND s.sequence < $current_sequence
ORDER BY similarity DESC, s.sequence DESC
LIMIT 5;

Pattern 3: Graph + Timeline

-- Get Anna's appearances in chronological order
SELECT
    s.*,
    s.sequence
FROM character:anna->appears_in->section s
WHERE s.sequence < $current_sequence
ORDER BY s.sequence ASC;

Pattern 4: Full Hybrid (Semantic + Graph + Timeline)

-- Comprehensive context query
SELECT
    s.*,
    vector::similarity::cosine(s.embedding, $query_embedding) AS similarity,
    s.sequence
FROM section s
WHERE vector::similarity::cosine(s.embedding, $query_embedding) > 0.7
  AND s->appears_in->character IN $character_ids
  AND s.sequence < $current_sequence
ORDER BY similarity DESC, s.sequence DESC
LIMIT 10;

Entity Extraction and Storage

When ingesting sources or writing sections, extract and store entities.

Character Extraction

-- Create character with embedding
CREATE character SET
    name = "Anna",
    description = "SOE wireless operator, recruited 1942",
    embedding = $character_embedding,
    status = "alive",
    introduced_in = section:ch1_s3;

Location Extraction

-- Create location
CREATE location SET
    name = "Beaulieu Manor",
    description = "SOE training facility in Hampshire",
    embedding = $location_embedding,
    introduced = true;

Event Extraction

-- Create event with timeline info
CREATE event SET
    name = "Anna begins training",
    description = "Wireless operator course starts at Beaulieu",
    embedding = $event_embedding,
    sequence = 5,
    date = "1942-08-15T00:00:00Z";

Relationship Creation

-- Link character to section
RELATE character:anna->appears_in->section:ch3_s2;

-- Link section to location
RELATE section:ch3_s2->located_in->location:beaulieu;

-- Link section to source
RELATE section:ch3_s2->cites->source:soe_manual;

-- Timeline relationship
RELATE event:training_begins->precedes->event:deployment;

Query Optimization

1. Use Vector Indexes

Ensure MTREE indexes exist for all embedding fields:

-- Check existing indexes
INFO FOR TABLE section;

-- Create missing index
DEFINE INDEX idx_section_embedding
    ON section
    FIELDS embedding
    MTREE DIMENSION 1536 DIST COSINE TYPE F32;

2. Filter Before Vector Search

Apply filters before vector operations when possible:

-- Efficient: Filter first, then vector search
SELECT * FROM section
WHERE chapter = $chapter_id
  AND vector::similarity::cosine(embedding, $query_vector) > 0.7;

-- Less efficient: Vector search entire table
SELECT * FROM section
WHERE vector::similarity::cosine(embedding, $query_vector) > 0.7
  AND chapter = $chapter_id;

3. Limit Results Early

Use LIMIT to reduce processing:

SELECT * FROM section
WHERE vector::similarity::cosine(embedding, $query_vector) > 0.7
ORDER BY vector::similarity::cosine(embedding, $query_vector) DESC
LIMIT 5;  -- Only process top 5

4. Use LET for Reusable Queries

Store intermediate results:

-- Store character list
LET $characters = (
    SELECT * FROM character WHERE id IN $character_ids
);

-- Reuse in multiple queries
SELECT * FROM section
WHERE ->appears_in->character IN $characters;

Common Query Patterns

Find Uncited Claims

SELECT * FROM section
WHERE count(->cites->source) = 0
  AND length(content) > 100;

Get Citation Chain

SELECT
    s.id,
    s.content,
    ->cites->source.title AS sources,
    ->cites->source.reliability AS reliability
FROM section s
WHERE s.id = $section_id;

Character Relationship Graph

SELECT
    c1.name AS character,
    ->knows->character.name AS knows,
    <-knows<-character.name AS known_by
FROM character c1;

Timeline Consistency Check

-- Find events with contradictory sequence and dates
SELECT
    e1.name,
    e1.sequence,
    e1.date,
    e2.name,
    e2.sequence,
    e2.date
FROM event e1, event e2
WHERE e1.sequence < e2.sequence
  AND e1.date > e2.date;

Database Schema Reference

See schema.surql for the complete database schema including:

• Table definitions (chapter, section, source, character, location, event, concept)
• Field types and constraints
• Vector indexes (MTREE configuration)
• Edge tables (appears_in, located_in, cites, supports, precedes, follows, knows, related_to)

Query Pattern Files

This skill includes specialized query pattern files:

• semantic.surql: Vector search patterns with examples
• graph.surql: Graph traversal patterns for relationships
• timeline.surql: Timeline and sequence query patterns

Load these files when you need specific query types.

Best Practices

1. Always use embeddings: Generate embeddings for all content
2. Create relationships: Use RELATE for explicit connections
3. Track sequences: Maintain timeline order with sequence fields
4. Verify indexes: Ensure MTREE indexes exist before vector queries
5. Combine strategies: Use hybrid queries for comprehensive context
6. Limit results: Don't retrieve more than you need
7. Store metadata: Track created_at, updated_at for debugging
8. Use transactions: Group related operations for consistency

Error Handling

Common Errors

Vector dimension mismatch:

Error: Vector dimensions do not match (expected 1536, got 768)

Fix: Ensure config embedding dimensions match schema indexes.

Missing index:

Error: No vector index found on field 'embedding'

Fix: Create MTREE index with DEFINE INDEX.

Invalid relationship:

Error: Cannot RELATE records of incompatible types

Fix: Verify edge table schema matches record types.

Supporting Files

• schema.surql — Complete database schema reference
• semantic.surql — Vector search query patterns
• graph.surql — Graph traversal examples
• timeline.surql — Timeline query patterns