alu-chatbot / sheet_sync.py
Donne120
Claude Opus 5
Add per-organization Google Sheet sync
2b18225
Raw History Blame Contribute Delete
15.3 kB
"""
Google Sheet → vector store sync.
Fetches a JSON payload produced by the Apps Script publisher and upserts each
entry into the existing ChromaDB collection used by `retrieval_engine`. Entries
sourced from the sheet are tagged with `origin=sheet` so we can prune ones the
staff have deleted without touching uploaded documents.
The payload schema (produced by sheet_integration/apps_script/Code.gs):
{
"version": "<sha256-prefix>",
"generated_at": "...",
"entries": [
{ "id": "...", "department": "...", "tab": "...", "category": "...",
"title": "...", "content": "...", "source_url": "...",
"last_updated": "...", "row": 12 }, ...
]
}
Public API:
SheetSync(retrieval_engine).refresh(force=False) -> dict
SheetSync(retrieval_engine).status() -> dict
"""
from __future__ import annotations
import json
import logging
import os
import re
import time
from dataclasses import dataclass, field
from pathlib import Path
from typing import Any, Dict, List, Optional
import urllib.request
import urllib.error
logger = logging.getLogger(__name__)
SHEET_JSON_URL_ENV = "KNOWLEDGE_JSON_URL"
DATA_DIR = Path(os.environ.get("DATA_DIR", "./data"))
VERSION_FILE = DATA_DIR / "sheet_version.json"
FETCH_TIMEOUT_SECONDS = 20
SHEET_ID_PREFIX = "sheet:" # vector store id namespace
def _version_file_for(org_id: Optional[str]) -> Path:
"""Where this sync's last-seen version marker lives.
Each organization needs its own marker: sharing one file would make the
first university's version look current for everyone, so the others would
be skipped as "unchanged" and silently never update. `None` keeps the
original single-Sheet path writing to the original filename.
"""
if not org_id:
return VERSION_FILE
safe = re.sub(r"[^a-zA-Z0-9_.-]", "_", str(org_id))
return DATA_DIR / f"sheet_version_{safe}.json"
# Max characters per chunk when an entry's content is long. Matches the value
# used by the document_processor pipeline so retrieval behaves consistently.
MAX_CHARS_PER_CHUNK = 1000
CHUNK_OVERLAP = 150
@dataclass
class SyncResult:
ok: bool
version: Optional[str] = None
entries_seen: int = 0
entries_upserted: int = 0
entries_deleted: int = 0
skipped_unchanged: bool = False
error: Optional[str] = None
fetched_at: float = field(default_factory=time.time)
def to_dict(self) -> Dict[str, Any]:
return {
"ok": self.ok,
"version": self.version,
"entries_seen": self.entries_seen,
"entries_upserted": self.entries_upserted,
"entries_deleted": self.entries_deleted,
"skipped_unchanged": self.skipped_unchanged,
"error": self.error,
"fetched_at": self.fetched_at,
}
class SheetSync:
"""Syncs one Google Sheet into one retrieval engine's collection.
Pass `json_url` and `organization_id` to sync a specific university's
Sheet into that university's own collection. Omit both and it behaves
exactly as before, reading KNOWLEDGE_JSON_URL and writing to whichever
engine it was handed — that keeps ALU's existing sync untouched.
"""
def __init__(
self,
retrieval_engine: Any,
json_url: Optional[str] = None,
organization_id: Optional[str] = None,
) -> None:
self.retrieval_engine = retrieval_engine
self.organization_id = organization_id
self.json_url = (json_url if json_url is not None else os.getenv(SHEET_JSON_URL_ENV, "")).strip()
self._version_file = _version_file_for(organization_id)
self._last_result: Optional[SyncResult] = None
self._version_file.parent.mkdir(parents=True, exist_ok=True)
# ------------------------------------------------------------------
# Public
# ------------------------------------------------------------------
@property
def enabled(self) -> bool:
return bool(self.json_url) and self._collection() is not None
def status(self) -> Dict[str, Any]:
info: Dict[str, Any] = {
"enabled": self.enabled,
"json_url_set": bool(self.json_url),
"last_version": self._read_version(),
}
if self._last_result:
info["last_result"] = self._last_result.to_dict()
info["entry_count"] = self._count_sheet_entries()
return info
def refresh(self, force: bool = False) -> SyncResult:
if not self.json_url:
return self._fail(
f"no sheet URL configured for organization {self.organization_id}"
if self.organization_id
else "KNOWLEDGE_JSON_URL not set"
)
collection = self._collection()
if collection is None:
return self._fail("Retrieval engine has no Chroma collection")
try:
payload = self._fetch_payload()
except Exception as e:
logger.error("Sheet fetch failed: %s", e)
return self._fail(f"fetch failed: {e}")
version = str(payload.get("version") or "")
entries = payload.get("entries") or []
if not isinstance(entries, list):
return self._fail("payload.entries is not a list")
last_version = self._read_version()
if not force and version and version == last_version:
result = SyncResult(
ok=True, version=version, entries_seen=len(entries),
skipped_unchanged=True,
)
self._last_result = result
logger.info("[sheet_sync] unchanged (version=%s, %d entries)", version, len(entries))
return result
# Upsert every entry. Chroma's `upsert` replaces existing ids.
upserted = self._upsert_entries(collection, entries)
# Remove sheet-origin entries that are no longer in the payload.
deleted = self._prune_missing(collection, entries)
if version:
self._write_version(version)
result = SyncResult(
ok=True, version=version,
entries_seen=len(entries),
entries_upserted=upserted,
entries_deleted=deleted,
)
self._last_result = result
logger.info(
"[sheet_sync] refreshed: version=%s seen=%d upserted=%d deleted=%d",
version, len(entries), upserted, deleted,
)
return result
# ------------------------------------------------------------------
# Internals
# ------------------------------------------------------------------
def _collection(self) -> Any:
return getattr(self.retrieval_engine, "collection", None)
def _fetch_payload(self) -> Dict[str, Any]:
req = urllib.request.Request(
self.json_url,
headers={"Accept": "application/json", "User-Agent": "alu-chatbot-sheet-sync"},
)
with urllib.request.urlopen(req, timeout=FETCH_TIMEOUT_SECONDS) as resp:
raw = resp.read().decode("utf-8")
return json.loads(raw)
def _upsert_entries(self, collection: Any, entries: List[Dict[str, Any]]) -> int:
ids: List[str] = []
documents: List[str] = []
metadatas: List[Dict[str, Any]] = []
# Track how many times each base entry_id has been seen so we can
# disambiguate collisions (e.g. two rows with identical titles whose
# slugs collide). Chroma rejects duplicate ids in a single batch.
id_counts: Dict[str, int] = {}
for entry in entries:
entry_id = str(entry.get("id") or "").strip()
content = (entry.get("content") or "").strip()
title = (entry.get("title") or "").strip()
if not entry_id or (not content and not title):
continue
seen = id_counts.get(entry_id, 0)
id_counts[entry_id] = seen + 1
# Mutate the id we use downstream — keeps the metadata.doc_id stable
# for citations but makes the Chroma id unique. We append the
# 1-indexed sheet row number when available, falling back to a
# running counter so we never collide within a batch.
if seen > 0:
row_suffix = entry.get("row") or seen + 1
entry_id_for_index = f"{entry_id}--r{row_suffix}"
else:
entry_id_for_index = entry_id
text = self._format_text(title, content, entry)
chunks = self._chunk(text)
for i, chunk in enumerate(chunks):
chunk_id = f"{SHEET_ID_PREFIX}{entry_id_for_index}#{i}"
ids.append(chunk_id)
documents.append(chunk)
metadatas.append(self._build_metadata(entry, chunk_index=i, chunk_total=len(chunks)))
if not ids:
return 0
# Chroma's `upsert` is idempotent; falls back to delete+add if absent.
try:
collection.upsert(documents=documents, ids=ids, metadatas=metadatas)
except AttributeError:
# Older Chroma versions: emulate upsert with delete-then-add.
try:
collection.delete(ids=ids)
except Exception:
pass
collection.add(documents=documents, ids=ids, metadatas=metadatas)
return len(ids)
def _prune_missing(self, collection: Any, entries: List[Dict[str, Any]]) -> int:
try:
existing = collection.get(where={"origin": "sheet"})
except Exception as e:
logger.warning("[sheet_sync] could not list existing sheet docs: %s", e)
return 0
existing_ids = set((existing or {}).get("ids") or [])
keep_ids: set[str] = set()
for entry in entries:
entry_id = str(entry.get("id") or "").strip()
if not entry_id:
continue
# Chunks share the same prefix; we don't know chunk_total at this
# point without re-chunking, so just keep anything that starts with
# the entry's prefix.
prefix = f"{SHEET_ID_PREFIX}{entry_id}#"
for existing_id in existing_ids:
if existing_id.startswith(prefix):
keep_ids.add(existing_id)
to_delete = [i for i in existing_ids if i not in keep_ids]
# Also delete ids whose prefix matches an entry but whose chunk index
# is beyond the current chunk count (handles content shrinking).
# The upsert above will have rewritten the kept ones already.
current_chunk_ids: set[str] = set()
for entry in entries:
entry_id = str(entry.get("id") or "").strip()
if not entry_id:
continue
content = (entry.get("content") or "").strip()
title = (entry.get("title") or "").strip()
text = self._format_text(title, content, entry)
chunk_count = max(1, len(self._chunk(text)))
for i in range(chunk_count):
current_chunk_ids.add(f"{SHEET_ID_PREFIX}{entry_id}#{i}")
stale = [i for i in existing_ids if i not in current_chunk_ids]
to_delete = sorted(set(to_delete) | set(stale))
if not to_delete:
return 0
try:
collection.delete(ids=to_delete)
except Exception as e:
logger.warning("[sheet_sync] delete failed: %s", e)
return 0
return len(to_delete)
def _count_sheet_entries(self) -> int:
collection = self._collection()
if collection is None:
return 0
try:
result = collection.get(where={"origin": "sheet"})
return len((result or {}).get("ids") or [])
except Exception:
return 0
# ------------------------------------------------------------------
# Formatting helpers
# ------------------------------------------------------------------
@staticmethod
def _format_text(title: str, content: str, entry: Dict[str, Any]) -> str:
dept = (entry.get("department") or "").strip()
cat = (entry.get("category") or "").strip()
header_bits = [b for b in [dept, cat] if b]
header = " — ".join(header_bits)
parts: List[str] = []
if header:
parts.append(f"[{header}]")
if title:
parts.append(title)
if content:
parts.append(content)
return "\n".join(parts).strip()
@staticmethod
def _build_metadata(entry: Dict[str, Any], chunk_index: int, chunk_total: int) -> Dict[str, Any]:
# Chroma metadata must be flat scalars.
return {
"origin": "sheet",
"doc_id": str(entry.get("id") or ""),
"title": (entry.get("title") or "ALU Knowledge Base").strip()[:200],
"department": (entry.get("department") or "").strip(),
"tab": (entry.get("tab") or "").strip(),
"category": (entry.get("category") or "").strip(),
"source": (entry.get("source_url") or "").strip(), # legacy key
"source_url": (entry.get("source_url") or "").strip(), # canonical
"last_updated": (entry.get("last_updated") or "").strip(),
"row": int(entry.get("row") or 0),
"chunk_index": chunk_index,
"chunk_total": chunk_total,
}
@staticmethod
def _chunk(text: str) -> List[str]:
if not text:
return []
if len(text) <= MAX_CHARS_PER_CHUNK:
return [text]
chunks: List[str] = []
start = 0
n = len(text)
while start < n:
end = min(start + MAX_CHARS_PER_CHUNK, n)
if end < n:
window = text.rfind(". ", start, end)
if window == -1:
window = text.rfind("\n", start, end)
if window == -1:
window = text.rfind(" ", start, end)
if window != -1 and window > start + 200:
end = window + 1
chunks.append(text[start:end].strip())
if end >= n:
break
start = max(end - CHUNK_OVERLAP, start + 1)
return [c for c in chunks if c]
# ------------------------------------------------------------------
# Version persistence
# ------------------------------------------------------------------
def _read_version(self) -> Optional[str]:
try:
if self._version_file.exists():
data = json.loads(self._version_file.read_text())
v = data.get("version")
return str(v) if v else None
except Exception as e:
logger.warning("[sheet_sync] could not read version file: %s", e)
return None
def _write_version(self, version: str) -> None:
try:
self._version_file.write_text(json.dumps({
"version": version,
"written_at": time.time(),
}))
except Exception as e:
logger.warning("[sheet_sync] could not write version file: %s", e)
def _fail(self, message: str) -> SyncResult:
result = SyncResult(ok=False, error=message)
self._last_result = result
return result