""" 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": "", "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