VibeCoding / src /adapters /indexes /knowledge_sqlite.py
dellweb3's picture
Deploy verified conversational workbench and isolated Java application previews
caf8059 verified
Raw History Blame Contribute Delete
8.56 kB
"""Local lexical index with citations and tenant ACL. No embeddings are required."""
from __future__ import annotations
import gzip
import hashlib
import json
import re
import sqlite3
from contextlib import contextmanager
from pathlib import Path
from src.modules.skill.domain.models import SkillError
ALIASES = {"库存": "inventory stock warehouse", "仓库": "warehouse inventory", "批次": "batch lot", "出库": "shipment delivery inventory", "订单": "order purchase", "审批": "approval workflow", "金额": "amount decimal", "设备": "device asset", "运维": "maintenance alarm", "维修": "maintenance work order", "巡检": "inspection schedule", "集合": "collection list map", "并发": "concurrent thread lock", "事务": "transaction commit rollback", "设计模式": "pattern factory strategy", "单元测试": "test assert mock", "页面": "html element document", "认证": "authentication authorization"}
class SqliteKnowledgeRepository:
def __init__(self, path):
self.path = Path(path)
self.path.parent.mkdir(parents=True, exist_ok=True)
with self.connect() as db:
db.executescript("""
CREATE TABLE IF NOT EXISTS kb_collections(tenant TEXT NOT NULL,collection_id TEXT NOT NULL,body TEXT NOT NULL,PRIMARY KEY(tenant,collection_id));
CREATE TABLE IF NOT EXISTS kb_records(tenant TEXT NOT NULL,collection_id TEXT NOT NULL,record_id TEXT NOT NULL,content_sha256 TEXT NOT NULL,body TEXT NOT NULL,PRIMARY KEY(tenant,record_id),UNIQUE(tenant,content_sha256));
CREATE INDEX IF NOT EXISTS kb_collection_idx ON kb_records(tenant,collection_id);
CREATE VIRTUAL TABLE IF NOT EXISTS kb_fts USING fts5(tenant UNINDEXED,collection_id UNINDEXED,record_id UNINDEXED,title,content,tokenize='unicode61');
CREATE TABLE IF NOT EXISTS kb_audit(id INTEGER PRIMARY KEY,tenant TEXT NOT NULL,user_id TEXT NOT NULL,query_digest TEXT NOT NULL,collections TEXT NOT NULL,result_count INTEGER NOT NULL);
""")
@contextmanager
def connect(self):
db = sqlite3.connect(self.path, timeout=60)
db.row_factory = sqlite3.Row
try:
yield db
db.commit()
except BaseException:
db.rollback()
raise
finally:
db.close()
def import_corpus(self, directory, tenant, catalog_path):
"""Trusted local ingestion entry point; unavailable as arbitrary path API."""
directory = Path(directory)
manifest = json.loads((directory / "manifest.json").read_text(encoding="utf-8"))
allowed = {c["id"] for c in json.loads(Path(catalog_path).read_text(encoding="utf-8"))["collections"]}
if {c["id"] for c in manifest["collections"]} != allowed:
raise ValueError("CORPUS_CATALOG_MISMATCH")
result = []
for entry in manifest["collections"]:
cid = entry["id"]
path = directory / (cid + ".jsonl.gz")
if hashlib.sha256(path.read_bytes()).hexdigest() != entry["sha256"]:
raise ValueError("CORPUS_DIGEST_MISMATCH")
with self.connect() as db:
prior = db.execute("SELECT body FROM kb_collections WHERE tenant=? AND collection_id=?", (tenant, cid)).fetchone()
revision = "corpus-" + entry["sha256"][:24]
if prior:
old = json.loads(prior["body"])
if old["revision"] == revision:
result.append(old)
continue
raise ValueError("IMMUTABLE_CORPUS_REVISION_EXISTS: import a new index/profile instead of replacing running references")
count = 0
with gzip.open(path, "rt", encoding="utf-8") as gz:
for line in gz:
if len(line) > 40000:
raise ValueError("CORPUS_RECORD_TOO_LARGE")
record = json.loads(line)
content = record["content"]
if record["collection_id"] != cid or not content or len(content) < 280 or record["quality_status"] != "imported_unreviewed" or not re.fullmatch(r"[a-f0-9]{40}", record["source_commit"]):
raise ValueError("CORPUS_RECORD_INVALID")
if hashlib.sha256(re.sub(r"\s+", " ", content).strip().encode()).hexdigest() != record["content_sha256"]:
raise ValueError("CORPUS_CONTENT_DIGEST_MISMATCH")
db.execute("INSERT INTO kb_records VALUES(?,?,?,?,?)", (tenant, cid, record["record_id"], record["content_sha256"], json.dumps(record, ensure_ascii=False)))
db.execute("INSERT INTO kb_fts VALUES(?,?,?,?,?)", (tenant, cid, record["record_id"], record["title"], content))
count += 1
if count != entry["records"]:
raise ValueError("CORPUS_COUNT_MISMATCH")
body = {"collection_id": cid, "name": entry["name"], "description": entry["description"], "revision": revision, "digest": entry["sha256"], "record_count": count, "quality_status": "imported_unreviewed", "enterprise_approved": False, "status": "reference_ready", "visibility": "tenant", "read_users": [], "schema": "zkcoding.knowledge-collection/v1"}
db.execute("INSERT INTO kb_collections VALUES(?,?,?)", (tenant, cid, json.dumps(body, ensure_ascii=False)))
result.append(body)
return result
@staticmethod
def readable(actor, body):
return body["visibility"] == "tenant" or actor.user_id in body.get("read_users", []) or "admin" in actor.roles
def collection(self, actor, cid):
with self.connect() as db:
row = db.execute("SELECT body FROM kb_collections WHERE tenant=? AND collection_id=?", (actor.tenant_id, cid)).fetchone()
body = json.loads(row["body"]) if row else None
if body is None or not self.readable(actor, body):
raise SkillError("KNOWLEDGE_NOT_FOUND", "知识库不存在或不可见。", 404)
return body
def collections(self, actor):
with self.connect() as db:
bodies = [json.loads(row["body"]) for row in db.execute("SELECT body FROM kb_collections WHERE tenant=? ORDER BY collection_id", (actor.tenant_id,))]
return [b for b in bodies if self.readable(actor, b)]
def search(self, actor, query, collection_ids, limit, *, for_code_reuse=False):
expanded = query + " " + " ".join(en for zh, en in ALIASES.items() if zh in query)
terms = list(dict.fromkeys(re.findall(r"[A-Za-z0-9_]{2,}|[\u4e00-\u9fff]+", expanded)))[:24]
if not terms:
return []
# Prefix matching makes Java identifiers such as inventoryTransaction discoverable.
# This remains lexical search, not semantic segmentation; quote every user-derived term.
expression = " OR ".join('"' + t + '"' + ('*' if re.fullmatch(r'[A-Za-z_][A-Za-z0-9_]{2,}', t) else '') for t in terms)
placeholders = ",".join("?" for _ in collection_ids)
with self.connect() as db:
# Fetch a bounded surplus so restricted code-reuse licenses can be excluded.
rows = db.execute(f"SELECT r.body FROM kb_fts JOIN kb_records r ON r.tenant=kb_fts.tenant AND r.record_id=kb_fts.record_id WHERE kb_fts MATCH ? AND kb_fts.tenant=? AND kb_fts.collection_id IN ({placeholders}) ORDER BY bm25(kb_fts),kb_fts.record_id LIMIT ?", (expression, actor.tenant_id, *collection_ids, min(200, limit * 10))).fetchall()
records = [json.loads(row["body"]) for row in rows]
if for_code_reuse:
records = [r for r in records if r["usage_policy"] == "attribution-required" and r["license"] in {"Apache-2.0", "MIT"}]
records = records[:limit]
db.execute("INSERT INTO kb_audit(tenant,user_id,query_digest,collections,result_count) VALUES(?,?,?,?,?)", (actor.tenant_id, actor.user_id, hashlib.sha256(query.encode()).hexdigest(), json.dumps(collection_ids), len(records)))
return [{"citation_id": r["record_id"], "collection_id": r["collection_id"], "title": r["title"], "snippet": r["content"], "source_url": r["source_url"], "source_commit": r["source_commit"], "line_start": r["line_start"], "line_end": r["line_end"], "content_sha256": r["content_sha256"], "license": r["license"], "usage_policy": r["usage_policy"], "quality_status": r["quality_status"], "trust": r["trust"]} for r in records]