Spaces:
Sleeping
Sleeping
Download sheet_sync.py from StudentCompanion/alu-chatbot: direct link, hf CLI and curl.
- Browser
- Download file 15.3 kB
-
https://huggingface.co/spaces/StudentCompanion/alu-chatbot/resolve/main/sheet_sync.py
- Command line
-
hf download hf://spaces/StudentCompanion/alu-chatbot/sheet_sync.py
-
curl -L -o sheet_sync.py https://huggingface.co/spaces/StudentCompanion/alu-chatbot/resolve/main/sheet_sync.py
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 | |
| 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 | |
| # ------------------------------------------------------------------ | |
| 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 | |
| # ------------------------------------------------------------------ | |
| 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() | |
| 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, | |
| } | |
| 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 | |