from __future__ import annotations import json import os import secrets from datetime import datetime, timezone from core_db import INTERNAL_TOKEN_FILE, connect, ensure_dirs def utc_now() -> str: return datetime.now(timezone.utc).replace(microsecond=0).isoformat() def ensure_internal_token() -> str: ensure_dirs() if INTERNAL_TOKEN_FILE.exists(): token = INTERNAL_TOKEN_FILE.read_text(encoding="utf-8").strip() if token: return token token = secrets.token_urlsafe(48) INTERNAL_TOKEN_FILE.write_text(token, encoding="utf-8") try: os.chmod(INTERNAL_TOKEN_FILE, 0o600) except OSError: pass return token def _ensure_column(con, table: str, column: str, ddl: str) -> None: columns = {str(row[1]) for row in con.execute(f"PRAGMA table_info({table})").fetchall()} if column not in columns: con.execute(f"ALTER TABLE {table} ADD COLUMN {column} {ddl}") def init_core_schema() -> None: ensure_dirs() ensure_internal_token() with connect() as con: con.executescript(""" CREATE TABLE IF NOT EXISTS connectors( connector_key TEXT PRIMARY KEY, connector_name TEXT NOT NULL, connector_type TEXT NOT NULL, base_url TEXT, status TEXT NOT NULL DEFAULT 'not_configured', last_test_at TEXT, last_sync_at TEXT, last_error TEXT, meta_json TEXT NOT NULL DEFAULT '{}' ); CREATE TABLE IF NOT EXISTS tracked_documents( id INTEGER PRIMARY KEY, source_key TEXT NOT NULL, document_slug TEXT NOT NULL, document_code TEXT NOT NULL, search_query TEXT NOT NULL, discovered_url TEXT, enabled INTEGER NOT NULL DEFAULT 1, last_checked_at TEXT, last_changed_at TEXT, last_hash TEXT, last_snapshot_id INTEGER, last_error TEXT, UNIQUE(source_key, document_slug) ); CREATE TABLE IF NOT EXISTS source_snapshots( id INTEGER PRIMARY KEY, source_key TEXT NOT NULL, document_slug TEXT NOT NULL, document_code TEXT NOT NULL, source_url TEXT NOT NULL, checked_at TEXT NOT NULL, content_hash TEXT NOT NULL, title TEXT, text_path TEXT, html_path TEXT, downloaded_file_path TEXT, manifest_path TEXT, is_current INTEGER NOT NULL DEFAULT 1, extraction_status TEXT NOT NULL DEFAULT 'raw_saved', extraction_meta_json TEXT NOT NULL DEFAULT '{}' ); CREATE INDEX IF NOT EXISTS idx_core_snapshots_document ON source_snapshots(source_key, document_slug, checked_at); CREATE INDEX IF NOT EXISTS idx_core_tracked_enabled ON tracked_documents(source_key, enabled); CREATE TABLE IF NOT EXISTS ai_runs( id INTEGER PRIMARY KEY, purpose TEXT NOT NULL, provider TEXT, model TEXT NOT NULL, created_at TEXT NOT NULL, status TEXT NOT NULL, input_hash TEXT, result_meta_json TEXT NOT NULL DEFAULT '{}', error TEXT ); CREATE TABLE IF NOT EXISTS ai_settings( singleton INTEGER PRIMARY KEY CHECK(singleton=1), primary_provider TEXT NOT NULL DEFAULT 'groq', fallback_enabled INTEGER NOT NULL DEFAULT 0, fallback_order_json TEXT NOT NULL DEFAULT '["gemini","openrouter"]', updated_at TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS knowledge_documents( id INTEGER PRIMARY KEY, document_slug TEXT NOT NULL, source_snapshot_id INTEGER NOT NULL, document_code TEXT NOT NULL, title TEXT, source_hash TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'review_required', completeness_status TEXT NOT NULL DEFAULT 'unknown', completeness_score REAL, completeness_reasons_json TEXT NOT NULL DEFAULT '[]', last_visible_section TEXT, model TEXT, extracted_at TEXT NOT NULL, is_current INTEGER NOT NULL DEFAULT 1, meta_json TEXT NOT NULL DEFAULT '{}', UNIQUE(document_slug, source_snapshot_id) ); CREATE INDEX IF NOT EXISTS idx_knowledge_documents_current ON knowledge_documents(document_slug, is_current); CREATE TABLE IF NOT EXISTS knowledge_rules( id INTEGER PRIMARY KEY, knowledge_document_id INTEGER NOT NULL, document_slug TEXT NOT NULL, source_snapshot_id INTEGER NOT NULL, rule_type TEXT NOT NULL, legal_basis TEXT, statement TEXT NOT NULL, conditions_json TEXT NOT NULL DEFAULT '[]', outcomes_json TEXT NOT NULL DEFAULT '[]', confidence REAL, review_status TEXT NOT NULL DEFAULT 'review_required', ai_run_id INTEGER, created_at TEXT NOT NULL, FOREIGN KEY(knowledge_document_id) REFERENCES knowledge_documents(id) ON DELETE CASCADE ); CREATE INDEX IF NOT EXISTS idx_knowledge_rules_document ON knowledge_rules(document_slug, rule_type, review_status); CREATE TABLE IF NOT EXISTS product_analyses( id INTEGER PRIMARY KEY, created_at TEXT NOT NULL, input_hash TEXT NOT NULL, provider TEXT, model TEXT NOT NULL, result_json TEXT NOT NULL, ai_run_id INTEGER ); """) # Upgrade databases created by Core 0.1/0.2. _ensure_column(con, "ai_runs", "provider", "TEXT") _ensure_column(con, "product_analyses", "provider", "TEXT") con.execute(""" INSERT INTO connectors(connector_key,connector_name,connector_type,base_url,status,meta_json) VALUES('techexpert','Техэксперт','legal_source','http://nps3.cntd.ru/docs','not_configured','{}') ON CONFLICT(connector_key) DO UPDATE SET connector_name=excluded.connector_name, connector_type=excluded.connector_type, base_url=COALESCE(connectors.base_url, excluded.base_url) """) providers = [ ('groq','Groq Free','ai','https://api.groq.com/openai/v1', {'model':'openai/gpt-oss-120b','role':'primary'}), ('gemini','Gemini Free','ai','https://generativelanguage.googleapis.com/v1beta/openai/', {'model':'gemini-3.6-flash','role':'fallback'}), ('openrouter','OpenRouter Free','ai','https://openrouter.ai/api/v1', {'model':'openrouter/free','role':'fallback'}), ] for key,name,ctype,url,meta in providers: con.execute(""" INSERT INTO connectors(connector_key,connector_name,connector_type,base_url,status,meta_json) VALUES(?,?,?,?, 'not_configured', ?) ON CONFLICT(connector_key) DO UPDATE SET connector_name=excluded.connector_name, connector_type=excluded.connector_type, base_url=excluded.base_url """, (key,name,ctype,url,json.dumps(meta,ensure_ascii=False))) # Old OpenAI row is no longer part of active routing, but do not delete # a possible encrypted secret automatically. This avoids destructive migration. con.execute("UPDATE connectors SET status='legacy' WHERE connector_key='openai' AND connector_type='ai'") con.execute(""" INSERT INTO ai_settings(singleton,primary_provider,fallback_enabled,fallback_order_json,updated_at) VALUES(1,'groq',0,'["gemini","openrouter"]',?) ON CONFLICT(singleton) DO NOTHING """, (utc_now(),)) con.commit()