RedStack

Which documents connect this person, organization, place, and event, and what evidence supports that connection?

python -m pip install 
  oracledb 
  pandas 
  python-dotenv 
  docling 
  gliner 
  langchain 
  langchain-community==0.3.31 
  langchain-huggingface 
  sentence-transformers

CREATE TABLESPACE graphrag_data
  DATAFILE '/opt/oracle/oradata/FREE/FREEPDB1/graphrag_data01.dbf'
  SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE 5G
  SEGMENT SPACE MANAGEMENT AUTO;
CREATE USER graphrag_app
  IDENTIFIED BY "replace-with-a-strong-password"
  DEFAULT TABLESPACE graphrag_data
  QUOTA UNLIMITED ON graphrag_data;
GRANT CREATE SESSION TO graphrag_app;
GRANT CREATE TABLE TO graphrag_app;
GRANT CREATE VIEW TO graphrag_app;
GRANT CREATE SEQUENCE TO graphrag_app;
GRANT CREATE PROCEDURE TO graphrag_app;
GRANT CREATE PROPERTY GRAPH TO graphrag_app;

export ORACLE_USER=graphrag_app
export ORACLE_PASSWORD="replace-with-a-strong-password"
export ORACLE_DSN="localhost:1521/FREEPDB1"

from dataclasses import dataclass
from pathlib import Path
import re
import textwrap
import pandas as pd
from docling.document_converter import DocumentConverter
DATASET_CSV = Path("data/wiki_movie_plots_deduped.csv")
DOC_DIR = Path("work/docling_input")
MAX_DOCUMENTS = 500  # Keep the tutorial run bounded and repeatable.
WORDS_PER_CHUNK = 220
@dataclass
class Chunk:
    document_key: str
    document_title: str
    section_title: str
    chunk_index: int
    chunk_text: str
def normalize_space(text: str) -> str:
    return re.sub(r"s+", " ", str(text)).strip()
def write_markdown_documents(csv_path: Path, output_dir: Path, limit: int) -> list[Path]:
    output_dir.mkdir(parents=True, exist_ok=True)
    frame = pd.read_csv(csv_path).dropna(subset=["Title", "Plot"])
    frame = frame.head(limit)
    paths = []
    for index, row in frame.iterrows():
        title = normalize_space(row["Title"])
        plot = normalize_space(row["Plot"])
        year = normalize_space(row.get("Release Year", ""))
        origin = normalize_space(row.get("Origin/Ethnicity", ""))
        genre = normalize_space(row.get("Genre", ""))
        path = output_dir / f"movie_{index:05d}.md"
        path.write_text(
            textwrap.dedent(
                f"""
                # {title}
                Release year: {year}
                Origin: {origin}
                Genre: {genre}
                ## Plot
                {plot}
                """
            ),
            encoding="utf-8",
        )
        paths.append(path)
    return paths
def chunk_words(document_key: str, title: str, section: str, text: str) -> list[Chunk]:
    words = normalize_space(text).split()
    chunks = []
    for chunk_index, start in enumerate(range(0, len(words), WORDS_PER_CHUNK), start=1):
        chunk = " ".join(words[start:start + WORDS_PER_CHUNK])
        if chunk:
            chunks.append(Chunk(document_key, title, section, chunk_index, chunk))
    return chunks
def docling_chunks(paths: list[Path]) -> list[Chunk]:
    converter = DocumentConverter()
    chunks = []
    for path in paths:
        result = converter.convert(path)
        markdown = result.document.export_to_markdown()
        title = path.stem
        heading = markdown.splitlines()[0].lstrip("# ").strip() if markdown else path.stem
        chunks.extend(chunk_words(path.stem, heading, "plot", markdown))
    return chunks
document_paths = write_markdown_documents(DATASET_CSV, DOC_DIR, MAX_DOCUMENTS)
all_chunks = docling_chunks(document_paths)
print(f"Prepared {len(all_chunks)} chunks from {len(document_paths)} documents")

DOCLING_OK documents=500 chunks=1155

CREATE TABLE documents (
  document_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  title VARCHAR2(400) NOT NULL,
  source_url VARCHAR2(1000),
  license_note VARCHAR2(1000)
);
CREATE TABLE chunks (
  chunk_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  document_id NUMBER NOT NULL REFERENCES documents(document_id),
  section_title VARCHAR2(400),
  chunk_index NUMBER NOT NULL,
  chunk_text CLOB NOT NULL,
  CONSTRAINT chunks_uq UNIQUE (document_id, section_title, chunk_index)
);
CREATE TABLE entities (
  entity_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  canonical_name VARCHAR2(400) NOT NULL,
  entity_type VARCHAR2(64) NOT NULL,
  aliases_json CLOB CHECK (aliases_json IS JSON),
  confidence NUMBER,
  CONSTRAINT entities_uq UNIQUE (canonical_name, entity_type)
);
CREATE TABLE entity_mentions (
  mention_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  entity_id NUMBER NOT NULL REFERENCES entities(entity_id),
  chunk_id NUMBER NOT NULL REFERENCES chunks(chunk_id),
  surface_text VARCHAR2(400) NOT NULL,
  char_start NUMBER,
  char_end NUMBER,
  confidence NUMBER
);
CREATE TABLE relationships (
  relationship_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  source_entity_id NUMBER NOT NULL REFERENCES entities(entity_id),
  target_entity_id NUMBER NOT NULL REFERENCES entities(entity_id),
  relationship_type VARCHAR2(100) NOT NULL,
  evidence_chunk_id NUMBER NOT NULL REFERENCES chunks(chunk_id),
  evidence_text CLOB NOT NULL,
  confidence NUMBER,
  extraction_method VARCHAR2(200),
  CONSTRAINT rel_no_self CHECK (source_entity_id <> target_entity_id)
);
CREATE TABLE chunk_embeddings (
  embedding_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  chunk_id NUMBER NOT NULL REFERENCES chunks(chunk_id),
  embedding_kind VARCHAR2(32) NOT NULL,
  embedding_model VARCHAR2(200) NOT NULL,
  embedding_text CLOB NOT NULL,
  embedding VECTOR(384, FLOAT32) NOT NULL,
  CONSTRAINT chunk_embeddings_kind_ck
    CHECK (embedding_kind IN ('RAW', 'GRAPH_ENRICHED')),
  CONSTRAINT chunk_embeddings_uq
    UNIQUE (chunk_id, embedding_kind, embedding_model)
);

import array
import json
import os
import oracledb
from sentence_transformers import SentenceTransformer
EMBEDDING_MODEL = "sentence-transformers/all-MiniLM-L6-v2"
model = SentenceTransformer(EMBEDDING_MODEL)
def to_float32_array(vector):
    return array.array("f", [float(value) for value in vector])
def read_lob(value):
    return value.read() if hasattr(value, "read") else value
def batched(items, size: int):
    batch = []
    for item in items:
        batch.append(item)
        if len(batch) >= size:
            yield batch
            batch = []
    if batch:
        yield batch
connection = oracledb.connect(
    user=os.environ["ORACLE_USER"],
    password=os.environ["ORACLE_PASSWORD"],
    dsn=os.environ["ORACLE_DSN"],
)
cursor = connection.cursor()
document_id_map = {}
chunk_id_map = {}
document_rows = []
chunk_rows = []
for chunk in all_chunks:
    if chunk.document_key not in document_id_map:
        document_id = len(document_id_map) + 1
        document_id_map[chunk.document_key] = document_id
        document_rows.append(
            (
                document_id,
                chunk.document_title,
                "Record the Kaggle dataset URL used for the run",
                "Record the dataset license and attribution required by the Kaggle dataset page.",
            )
        )
    chunk_id = len(chunk_rows) + 1
    chunk_id_map[(chunk.document_key, chunk.section_title, chunk.chunk_index)] = chunk_id
    chunk_rows.append(
        (
            chunk_id,
            document_id_map[chunk.document_key],
            chunk.section_title,
            chunk.chunk_index,
            chunk.chunk_text,
        )
    )
schema_name = os.environ["ORACLE_USER"].upper()
connection.direct_path_load(
    schema_name,
    "DOCUMENTS",
    ["DOCUMENT_ID", "TITLE", "SOURCE_URL", "LICENSE_NOTE"],
    document_rows,
    batch_size=5000,
)
connection.commit()
for batch in batched(chunk_rows, 5000):
    connection.direct_path_load(
        schema_name,
        "CHUNKS",
        ["CHUNK_ID", "DOCUMENT_ID", "SECTION_TITLE", "CHUNK_INDEX", "CHUNK_TEXT"],
        batch,
        batch_size=len(batch),
    )
connection.commit()

embedding_id = 1
for chunk_batch in batched(all_chunks, 256):
    texts = [chunk.chunk_text for chunk in chunk_batch]
    vectors = model.encode(
        texts,
        batch_size=256,
        normalize_embeddings=True,
        show_progress_bar=False,
    )
    embedding_rows = []
    for chunk, text, vector in zip(chunk_batch, texts, vectors):
        chunk_id = chunk_id_map[
            (chunk.document_key, chunk.section_title, chunk.chunk_index)
        ]
        embedding_rows.append(
            (
                embedding_id,
                chunk_id,
                "RAW",
                EMBEDDING_MODEL,
                text,
                to_float32_array(vector),
            )
        )
        embedding_id += 1
    connection.direct_path_load(
        schema_name,
        "CHUNK_EMBEDDINGS",
        [
            "EMBEDDING_ID",
            "CHUNK_ID",
            "EMBEDDING_KIND",
            "EMBEDDING_MODEL",
            "EMBEDDING_TEXT",
            "EMBEDDING",
        ],
        embedding_rows,
        batch_size=len(embedding_rows),
    )
connection.commit()

CREATE VECTOR INDEX chunk_embedding_hnsw_idx
ON chunk_embeddings (embedding)
ORGANIZATION INMEMORY NEIGHBOR GRAPH
DISTANCE COSINE
WITH TARGET ACCURACY 95;

ALTER SYSTEM SET vector_memory_size = 1G SCOPE = SPFILE;

SHOW PARAMETER vector_memory_size;

for chunk_batch in batched(all_chunks, 256):
    texts = [
        enriched_text_for_chunk(
            cursor,
            chunk_id_map[(chunk.document_key, chunk.section_title, chunk.chunk_index)],
            chunk.chunk_text,
        )
        for chunk in chunk_batch
    ]
    vectors = model.encode(texts, batch_size=256, normalize_embeddings=True)
    # Build rows as above, using embedding_kind="GRAPH_ENRICHED",
    # then call connection.direct_path_load(...).

from gliner import GLiNER
ENTITY_LABELS = ["person", "organization", "location", "event", "work", "object"]
ENTITY_TYPE_MAP = {
    "person": "PERSON",
    "organization": "ORG",
    "location": "PLACE",
    "event": "EVENT",
    "work": "WORK",
    "object": "OBJECT",
}
entity_model = GLiNER.from_pretrained("urchade/gliner_medium-v2.1")
def sentence_spans(text: str):
    start = 0
    for match in re.finditer(r"(?<=[.!?])s+", text):
        end = match.start()
        yield start, end, text[start:end].strip()
        start = match.end()
    if start < len(text):
        yield start, len(text), text[start:].strip()
def normalize_entity_name(text: str) -> str:
    return normalize_space(text).strip(".,;:()[]{}"'")
def extract_mentions(chunk_id: int, text: str):
    predictions = entity_model.predict_entities(text, ENTITY_LABELS, threshold=0.35)
    mentions = []
    for item in predictions:
        name = normalize_entity_name(item["text"])
        if len(name) < 2:
            continue
        mentions.append(
            {
                "chunk_id": chunk_id,
                "canonical_name": name,
                "entity_type": ENTITY_TYPE_MAP.get(item["label"], "ENTITY"),
                "surface_text": item["text"],
                "char_start": int(item["start"]),
                "char_end": int(item["end"]),
                "confidence": float(item.get("score", 0.0)),
            }
        )
    return mentions
def relation_type_for(source_type: str, target_type: str) -> str:
    if source_type == "PERSON" and target_type == "ORG":
        return "associated_with_organization"
    if source_type == "PERSON" and target_type == "PLACE":
        return "associated_with_place"
    if target_type == "EVENT":
        return "connected_to_event"
    return "co_occurs_with"
def relationships_from_mentions(chunk_id: int, text: str, mentions: list[dict]):
    relationships = []
    for sent_start, sent_end, sentence in sentence_spans(text):
        in_sentence = [
            mention for mention in mentions
            if sent_start <= mention["char_start"] < sent_end
        ]
        unique = []
        seen = set()
        for mention in in_sentence:
            key = (mention["canonical_name"], mention["entity_type"])
            if key not in seen:
                unique.append(mention)
                seen.add(key)
        for index, source in enumerate(unique):
            for target in unique[index + 1:]:
                confidence = min(source["confidence"], target["confidence"])
                relationships.append(
                    {
                        "source": source["canonical_name"],
                        "source_type": source["entity_type"],
                        "target": target["canonical_name"],
                        "target_type": target["entity_type"],
                        "relationship_type": relation_type_for(
                            source["entity_type"],
                            target["entity_type"],
                        ),
                        "evidence_chunk_id": chunk_id,
                        "evidence_text": sentence,
                        "confidence": confidence,
                    }
                )
    return relationships

def get_or_create_entity(cursor, canonical_name, entity_type):
    cursor.execute(
        """
        SELECT entity_id
        FROM entities
        WHERE canonical_name = :1 AND entity_type = :2
        """,
        (canonical_name, entity_type),
    )
    row = cursor.fetchone()
    if row:
        return row[0]
    entity_id_var = cursor.var(int)
    cursor.execute(
        """
        INSERT INTO entities (canonical_name, entity_type, aliases_json, confidence)
        VALUES (:1, :2, :3, :4)
        RETURNING entity_id INTO :5
        """,
        (
            canonical_name,
            entity_type,
            json.dumps([]),
            0.85,
            entity_id_var,
        ),
    )
    return entity_id_var.getvalue()[0]
def insert_mention(cursor, mention):
    entity_id = get_or_create_entity(
        cursor,
        mention["canonical_name"],
        mention["entity_type"],
    )
    cursor.execute(
        """
        INSERT INTO entity_mentions (
          entity_id, chunk_id, surface_text, char_start, char_end, confidence
        )
        VALUES (:1, :2, :3, :4, :5, :6)
        """,
        (
            entity_id,
            mention["chunk_id"],
            mention["surface_text"],
            mention["char_start"],
            mention["char_end"],
            mention["confidence"],
        ),
    )
def insert_relationship(cursor, relationship):
    source_id = get_or_create_entity(
        cursor,
        relationship["source"],
        relationship["source_type"],
    )
    target_id = get_or_create_entity(
        cursor,
        relationship["target"],
        relationship["target_type"],
    )
    cursor.execute(
        """
        INSERT INTO relationships (
          source_entity_id,
          target_entity_id,
          relationship_type,
          evidence_chunk_id,
          evidence_text,
          confidence,
          extraction_method
        )
        VALUES (:1, :2, :3, :4, :5, :6, :7)
        """,
        (
            source_id,
            target_id,
            relationship["relationship_type"],
            relationship["evidence_chunk_id"],
            relationship["evidence_text"],
            relationship["confidence"],
            "deterministic_alias_sentence_rules_v1",
        ),
    )
for chunk in all_chunks:
    chunk_id = chunk_id_map[
        (chunk.document_key, chunk.section_title, chunk.chunk_index)
    ]
    mentions = extract_mentions(chunk_id, chunk.chunk_text)
    for mention in mentions:
        insert_mention(cursor, mention)
    for relationship in relationships_from_mentions(chunk_id, chunk.chunk_text, mentions):
        insert_relationship(cursor, relationship)
connection.commit()

CREATE OR REPLACE PROPERTY GRAPH corpus_entity_graph
  VERTEX TABLES (
    entities
      KEY (entity_id)
      LABEL entity
      PROPERTIES (
        entity_id,
        canonical_name,
        entity_type,
        confidence
      )
  )
  EDGE TABLES (
    relationships
      KEY (relationship_id)
      SOURCE KEY (source_entity_id) REFERENCES entities (entity_id)
      DESTINATION KEY (target_entity_id) REFERENCES entities (entity_id)
      LABEL related_to
      PROPERTIES (
        relationship_id,
        relationship_type,
        evidence_chunk_id,
        confidence,
        extraction_method
      )
  );

SELECT
  gt.source_name,
  gt.relationship_type,
  gt.target_name,
  gt.confidence,
  gt.evidence_chunk_id
FROM GRAPH_TABLE(
  corpus_entity_graph
  MATCH (src IS entity)-[rel IS related_to]->(dst IS entity)
  COLUMNS (
    src.canonical_name AS source_name,
    rel.relationship_type AS relationship_type,
    dst.canonical_name AS target_name,
    rel.confidence AS confidence,
    rel.evidence_chunk_id AS evidence_chunk_id
  )
) gt
ORDER BY gt.source_name, gt.relationship_type, gt.target_name;

source_name       relationship_type    target_name        confidence    evidence_chunk_id
<source entity>   <relationship>       <target entity>    <score>       <chunk_id>

from langchain_community.vectorstores.oraclevs import DistanceStrategy, OracleVS
from langchain_huggingface import HuggingFaceEmbeddings
embedding_function = HuggingFaceEmbeddings(model_name=EMBEDDING_MODEL)
oracle_vs = OracleVS(
    client=connection,
    table_name="LC_RAW_CHUNKS",
    embedding_function=embedding_function,
    distance_strategy=DistanceStrategy.COSINE,
    query="Oracle GraphRAG retrieval seed text",
)
oracle_vs.add_texts(
    texts=[chunk.chunk_text for chunk in all_chunks],
    metadatas=[
        {
            "chunk_id": chunk_id_map[
                (chunk.document_key, chunk.section_title, chunk.chunk_index)
            ],
            "document_key": chunk.document_key,
            "section_title": chunk.section_title,
            "chunk_index": chunk.chunk_index,
        }
        for chunk in all_chunks
    ],
    ids=[
        str(
            chunk_id_map[
                (chunk.document_key, chunk.section_title, chunk.chunk_index)
            ]
        )
        for chunk in all_chunks
    ],
)
baseline_docs = oracle_vs.similarity_search(
    "Which documents connect this person, organization, place, and event?",
    k=5,
)

def safe_top_k(top_k: int, maximum: int = 50) -> int:
    value = int(top_k)
    if value < 1 or value > maximum:
        raise ValueError(f"top_k must be between 1 and {maximum}")
    return value
def embed_query(question: str):
    embedding = model.encode([question], normalize_embeddings=True)[0]
    return to_float32_array(embedding)
def vector_search(cursor, question: str, embedding_kind: str, top_k: int = 5):
    top_k_literal = safe_top_k(top_k)
    query_embedding = embed_query(question)
    sql = f"""
        SELECT
          c.chunk_id,
          c.section_title,
          DBMS_LOB.SUBSTR(c.chunk_text, 700, 1) AS excerpt,
          VECTOR_DISTANCE(e.embedding, :query_embedding, COSINE) AS distance
        FROM chunk_embeddings e
        JOIN chunks c ON c.chunk_id = e.chunk_id
        WHERE e.embedding_kind = :embedding_kind
        ORDER BY distance
        FETCH FIRST {top_k_literal} ROWS ONLY
    """
    cursor.execute(
        sql,
        query_embedding=query_embedding,
        embedding_kind=embedding_kind,
    )
    return [
        {
            "chunk_id": row[0],
            "section_title": row[1],
            "excerpt": read_lob(row[2]),
            "distance": float(row[3]),
            "embedding_kind": embedding_kind,
        }
        for row in cursor.fetchall()
    ]
question = "Which documents connect a person, organization, place, and event?"
baseline_results = vector_search(cursor, question, "RAW", top_k=5)

def graph_facts_for_chunk(cursor, chunk_id):
    cursor.execute(
        """
        SELECT
          s.canonical_name,
          r.relationship_type,
          t.canonical_name,
          r.confidence
        FROM relationships r
        JOIN entities s ON s.entity_id = r.source_entity_id
        JOIN entities t ON t.entity_id = r.target_entity_id
        WHERE r.evidence_chunk_id = :1
        ORDER BY r.confidence DESC
        """,
        (chunk_id,),
    )
    return cursor.fetchall()
def enriched_text_for_chunk(cursor, chunk_id, chunk_text):
    facts = graph_facts_for_chunk(cursor, chunk_id)
    if not facts:
        return chunk_text
    fact_lines = [
        f"- {source} {relationship_type} {target} (confidence={confidence})"
        for source, relationship_type, target, confidence in facts
    ]
    return (
        f"{chunk_text}nn"
        "Extracted graph facts supported by this chunk:n"
        + "n".join(fact_lines)
    )
for chunk_batch in batched(all_chunks, 256):
    texts = []
    chunk_ids = []
    for chunk in chunk_batch:
        chunk_id = chunk_id_map[
            (chunk.document_key, chunk.section_title, chunk.chunk_index)
        ]
        chunk_ids.append(chunk_id)
        texts.append(enriched_text_for_chunk(cursor, chunk_id, chunk.chunk_text))
    vectors = model.encode(
        texts,
        batch_size=256,
        normalize_embeddings=True,
        show_progress_bar=False,
    )
    embedding_rows = []
    for chunk_id, text, vector in zip(chunk_ids, texts, vectors):
        embedding_rows.append(
            (
                embedding_id,
                chunk_id,
                "GRAPH_ENRICHED",
                EMBEDDING_MODEL,
                text,
                to_float32_array(vector),
            )
        )
        embedding_id += 1
    connection.direct_path_load(
        schema_name,
        "CHUNK_EMBEDDINGS",
        [
            "EMBEDDING_ID",
            "CHUNK_ID",
            "EMBEDDING_KIND",
            "EMBEDDING_MODEL",
            "EMBEDDING_TEXT",
            "EMBEDDING",
        ],
        embedding_rows,
        batch_size=len(embedding_rows),
    )
connection.commit()

enriched_results = vector_search(cursor, question, "GRAPH_ENRICHED", top_k=5)
print("RAW:", [row["chunk_id"] for row in baseline_results])
print("GRAPH_ENRICHED:", [row["chunk_id"] for row in enriched_results])

import json
def query_entity_names(question: str) -> str:
    mentions = extract_mentions(-1, question)
    names = sorted({mention["canonical_name"] for mention in mentions})
    return json.dumps(names)
query_embedding = embed_query(question)
query_entities_json = query_entity_names(question)

HYBRID_SQL = """
WITH query_entities AS (
  SELECT jt.entity_name
  FROM JSON_TABLE(
    :query_entities_json,
    '$[*]' COLUMNS entity_name VARCHAR2(400) PATH '$'
  ) jt
),
vector_candidates AS (
  SELECT
    c.chunk_id,
    c.section_title,
    DBMS_LOB.SUBSTR(c.chunk_text, 700, 1) AS excerpt,
    VECTOR_DISTANCE(e.embedding, :query_embedding, COSINE) AS vector_distance
  FROM chunk_embeddings e
  JOIN chunks c ON c.chunk_id = e.chunk_id
  WHERE e.embedding_kind = 'RAW'
  ORDER BY vector_distance
  FETCH FIRST 25 ROWS ONLY
),
graph_facts AS (
  SELECT
    gt.relationship_id,
    gt.source_name,
    gt.relationship_type,
    gt.target_name,
    gt.confidence,
    gt.evidence_chunk_id
  FROM GRAPH_TABLE(
    corpus_entity_graph
    MATCH (src IS entity)-[rel IS related_to]->(dst IS entity)
    COLUMNS (
      rel.relationship_id AS relationship_id,
      src.canonical_name AS source_name,
      rel.relationship_type AS relationship_type,
      dst.canonical_name AS target_name,
      rel.confidence AS confidence,
      rel.evidence_chunk_id AS evidence_chunk_id
    )
  ) gt
  WHERE EXISTS (
    SELECT 1
    FROM query_entities qe
    WHERE lower(gt.source_name) = lower(qe.entity_name)
       OR lower(gt.target_name) = lower(qe.entity_name)
  )
),
hybrid_candidates AS (
  SELECT
    vc.chunk_id,
    vc.section_title,
    vc.excerpt,
    vc.vector_distance,
    COUNT(gf.relationship_id) AS graph_fact_count,
    MAX(gf.confidence) AS max_graph_confidence,
    LISTAGG(
      gf.source_name || ' ' || gf.relationship_type || ' ' || gf.target_name,
      '; '
    ) WITHIN GROUP (ORDER BY gf.confidence DESC) AS graph_facts
  FROM vector_candidates vc
  LEFT JOIN graph_facts gf
    ON gf.evidence_chunk_id = vc.chunk_id
  GROUP BY
    vc.chunk_id,
    vc.section_title,
    vc.excerpt,
    vc.vector_distance
)
SELECT
  chunk_id,
  section_title,
  excerpt,
  vector_distance,
  graph_fact_count,
  graph_facts,
  (
    (1 / (1 + vector_distance)) +
    (0.10 * graph_fact_count) +
    (0.05 * COALESCE(max_graph_confidence, 0))
  ) AS hybrid_score
FROM hybrid_candidates
ORDER BY hybrid_score DESC, vector_distance
FETCH FIRST 5 ROWS ONLY
"""
cursor.execute(
    HYBRID_SQL,
    query_entities_json=query_entities_json,
    query_embedding=query_embedding,
)
hybrid_results = [
    {
        "chunk_id": row[0],
        "section_title": row[1],
        "excerpt": read_lob(row[2]),
        "vector_distance": float(row[3]),
        "graph_fact_count": int(row[4]),
        "graph_facts": row[5],
        "hybrid_score": float(row[6]),
    }
    for row in cursor.fetchall()
]

QUESTIONS = [
    "Which documents connect McTeague and Trina, and what evidence supports the co-occurs-with relationship?",
    "Which documents connect a person to an organization and a place?",
    "Which events connect multiple named people?",
    "Which locations appear in relationship-heavy documents?",
]
def hybrid_sql_search(cursor, question: str):
    cursor.execute(
        HYBRID_SQL,
        query_entities_json=query_entity_names(question),
        query_embedding=embed_query(question),
    )
    return [
        {
            "chunk_id": row[0],
            "section_title": row[1],
            "excerpt": read_lob(row[2]),
            "vector_distance": float(row[3]),
            "graph_fact_count": int(row[4]),
            "graph_facts": row[5],
            "hybrid_score": float(row[6]),
        }
        for row in cursor.fetchall()
    ]
def compare_question(cursor, question):
    raw = vector_search(cursor, question, "RAW", top_k=5)
    enriched = vector_search(cursor, question, "GRAPH_ENRICHED", top_k=5)
    hybrid = hybrid_sql_search(cursor, question)
    return {
        "question": question,
        "raw_chunk_ids": [row["chunk_id"] for row in raw],
        "graph_enriched_chunk_ids": [row["chunk_id"] for row in enriched],
        "hybrid_chunk_ids": [row["chunk_id"] for row in hybrid],
        "hybrid_graph_fact_counts": [row["graph_fact_count"] for row in hybrid],
    }
for item in [compare_question(cursor, question) for question in QUESTIONS]:
    print(item)

Which documents connect McTeague and Trina, and what evidence supports the co-occurs-with relationship?

def format_passages(results):
    blocks = []
    for row in results:
        blocks.append(
            f"[chunk_id={row['chunk_id']}, section={row['section_title']}]n"
            f"{row['excerpt']}"
        )
    return "nn".join(blocks)
def format_graph_facts(results):
    lines = []
    for row in results:
        if row.get("graph_facts"):
            lines.append(f"- chunk_id={row['chunk_id']}: {row['graph_facts']}")
    return "n".join(lines) if lines else "No graph facts retrieved."
prompt = f"""
You are answering a question using retrieved source passages and extracted graph facts.
Rules:
- Use the source passages as the primary evidence.
- Treat graph facts as extracted candidate relationships.
- Cite chunk IDs for every factual claim.
- If the passages do not support the answer, say that the retrieved evidence is insufficient.
- Do not invent facts that are not present in the passages or graph facts.
Question:
{question}
Source passages:
{format_passages(hybrid_results)}
Extracted graph facts:
{format_graph_facts(hybrid_results)}
Answer with citations:
"""

CREATE TABLE graphrag_vector_explorer_base AS
SELECT
  HEXTORAW(
    LPAD(TO_CHAR(e.embedding_id, 'FMXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'), 32, '0')
  ) AS id,
  e.embedding,
  DBMS_LOB.SUBSTR(c.chunk_text, 4000, 1) AS text
FROM chunk_embeddings e
JOIN chunks c ON c.chunk_id = e.chunk_id
WHERE e.embedding_kind = 'RAW'
ORDER BY STANDARD_HASH(TO_CHAR(e.embedding_id) || '42', 'SHA1')
FETCH FIRST 500 ROWS ONLY;

GRANT CREATE MINING MODEL TO graphrag_app;

python vector_3d_explorer.py 
  --dsn "localhost:1521/FREEPDB1" 
  --user "graphrag_app" 
  --password "replace-with-a-strong-password" 
  --table "GRAPHRAG_VECTOR_EXPLORER_BASE" 
  --distance-metric-default "COSINE" 
  --topk 5 
  --subset-dim 500 
  --subset-dim-plot 100

WITH graph_edges AS (
  SELECT *
  FROM (
    SELECT
      gt.source_name,
      gt.source_type,
      gt.relationship_type,
      gt.target_name,
      gt.target_type,
      gt.evidence_chunk_id,
      gt.confidence
    FROM GRAPH_TABLE(
      corpus_entity_graph
      MATCH (src IS entity)-[rel IS related_to]->(dst IS entity)
      COLUMNS (
        src.canonical_name AS source_name,
        src.entity_type AS source_type,
        rel.relationship_type AS relationship_type,
        dst.canonical_name AS target_name,
        dst.entity_type AS target_type,
        rel.evidence_chunk_id AS evidence_chunk_id,
        rel.confidence AS confidence
      )
    ) gt
    WHERE lower(gt.source_name) IN ('mcteague', 'trina')
       OR lower(gt.target_name) IN ('mcteague', 'trina')
    ORDER BY gt.confidence DESC
  )
  FETCH FIRST 100 ROWS ONLY
),
graph_nodes AS (
  SELECT DISTINCT source_name AS id, source_type AS type FROM graph_edges
  UNION
  SELECT DISTINCT target_name AS id, target_type AS type FROM graph_edges
)
SELECT JSON_OBJECT(
         'nodes' VALUE (
           SELECT JSON_ARRAYAGG(
                    JSON_OBJECT('id' VALUE id, 'type' VALUE type RETURNING CLOB)
                    RETURNING CLOB
                  )
           FROM graph_nodes
         ),
         'links' VALUE (
           SELECT JSON_ARRAYAGG(
                    JSON_OBJECT(
                      'source' VALUE source_name,
                      'target' VALUE target_name,
                      'label' VALUE relationship_type,
                      'chunk_id' VALUE evidence_chunk_id,
                      'confidence' VALUE confidence
                      RETURNING CLOB
                    )
                    RETURNING CLOB
                  )
           FROM graph_edges
         )
         RETURNING CLOB
       ) AS graph_json
FROM dual;