Spaces:
Running
Running
| import hashlib | |
| import os | |
| import secrets | |
| import shutil | |
| import subprocess | |
| from datetime import datetime | |
| from typing import List, Optional | |
| import psycopg2 | |
| import psycopg2.extras | |
| from fastapi import FastAPI, Header, HTTPException, Query | |
| from fastapi.responses import HTMLResponse | |
| from pydantic import BaseModel | |
| app = FastAPI( | |
| title="PostgreSQL General HF API", | |
| description="PostgreSQL + API Key dashboard for Hugging Face Docker Spaces.", | |
| version="1.3.0", | |
| ) | |
| POSTGRES_USER = os.environ.get("POSTGRES_USER", "admin") | |
| POSTGRES_PASSWORD = os.environ.get("POSTGRES_PASSWORD") | |
| POSTGRES_DB = os.environ.get("POSTGRES_DB", "appdb") | |
| BACKUP_DIR = os.environ.get("BACKUP_DIR", "/data/backups") | |
| DATABASE_URL = os.environ.get( | |
| "DATABASE_URL", | |
| f"postgresql://{POSTGRES_USER}:{POSTGRES_PASSWORD}@127.0.0.1:5432/{POSTGRES_DB}", | |
| ) | |
| def get_conn(): | |
| if not POSTGRES_PASSWORD and not os.environ.get("DATABASE_URL"): | |
| raise HTTPException(status_code=500, detail="POSTGRES_PASSWORD or DATABASE_URL is not configured") | |
| return psycopg2.connect(DATABASE_URL) | |
| def hash_key(api_key: str) -> str: | |
| return hashlib.sha256(api_key.encode("utf-8")).hexdigest() | |
| def verify_admin(x_admin_user: Optional[str], x_admin_password: Optional[str]): | |
| if x_admin_user != POSTGRES_USER or x_admin_password != POSTGRES_PASSWORD: | |
| raise HTTPException(status_code=403, detail="Invalid admin credentials") | |
| def init_tables(): | |
| conn = get_conn() | |
| cur = conn.cursor() | |
| cur.execute(""" | |
| CREATE TABLE IF NOT EXISTS api_keys ( | |
| id SERIAL PRIMARY KEY, | |
| name TEXT NOT NULL, | |
| key_hash TEXT NOT NULL UNIQUE, | |
| key_prefix TEXT NOT NULL, | |
| scopes TEXT[] DEFAULT ARRAY['read', 'write'], | |
| is_active BOOLEAN DEFAULT true, | |
| created_at TIMESTAMP DEFAULT now(), | |
| last_used_at TIMESTAMP | |
| ); | |
| CREATE TABLE IF NOT EXISTS files ( | |
| id SERIAL PRIMARY KEY, | |
| user_id TEXT, | |
| filename TEXT NOT NULL, | |
| file_path TEXT NOT NULL, | |
| file_size BIGINT, | |
| mime_type TEXT, | |
| created_at TIMESTAMP DEFAULT now() | |
| ); | |
| CREATE TABLE IF NOT EXISTS rss_articles ( | |
| id SERIAL PRIMARY KEY, | |
| source TEXT, | |
| title TEXT NOT NULL, | |
| url TEXT UNIQUE, | |
| summary TEXT, | |
| published_at TIMESTAMP, | |
| created_at TIMESTAMP DEFAULT now() | |
| ); | |
| CREATE TABLE IF NOT EXISTS openclaw_memories ( | |
| id SERIAL PRIMARY KEY, | |
| claw_id TEXT NOT NULL, | |
| content TEXT NOT NULL, | |
| metadata JSONB, | |
| created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP | |
| ); | |
| CREATE INDEX IF NOT EXISTS idx_openclaw_memories_claw_id ON openclaw_memories(claw_id); | |
| CREATE INDEX IF NOT EXISTS idx_openclaw_memories_created_at ON openclaw_memories(created_at DESC); | |
| """) | |
| conn.commit() | |
| cur.close() | |
| conn.close() | |
| def startup(): | |
| init_tables() | |
| def extract_api_key(authorization: Optional[str], api_key: Optional[str]) -> str: | |
| if api_key: | |
| return api_key.strip() | |
| if not authorization: | |
| raise HTTPException(status_code=401, detail="Missing API key. Use Authorization: Bearer YOUR_KEY or ?api_key=YOUR_KEY") | |
| if not authorization.startswith("Bearer "): | |
| raise HTTPException(status_code=401, detail="Invalid Authorization format") | |
| return authorization.replace("Bearer ", "", 1).strip() | |
| def verify_api_key(authorization: Optional[str], api_key: Optional[str] = None, required_scope: Optional[str] = None): | |
| raw_key = extract_api_key(authorization, api_key) | |
| key_hash = hash_key(raw_key) | |
| conn = get_conn() | |
| cur = conn.cursor() | |
| cur.execute("SELECT id, name, scopes, is_active FROM api_keys WHERE key_hash = %s LIMIT 1;", (key_hash,)) | |
| row = cur.fetchone() | |
| if not row: | |
| cur.close(); conn.close() | |
| raise HTTPException(status_code=403, detail="Invalid API key") | |
| key_id, name, scopes, is_active = row | |
| if not is_active: | |
| cur.close(); conn.close() | |
| raise HTTPException(status_code=403, detail="API key is inactive") | |
| if required_scope and required_scope not in scopes: | |
| cur.close(); conn.close() | |
| raise HTTPException(status_code=403, detail="Insufficient API key scope") | |
| cur.execute("UPDATE api_keys SET last_used_at = now() WHERE id = %s;", (key_id,)) | |
| conn.commit() | |
| cur.close(); conn.close() | |
| return {"id": key_id, "name": name, "scopes": scopes} | |
| DASHBOARD_HTML = r''' | |
| <!doctype html><html lang="en"><head><meta charset="utf-8"/><meta name="viewport" content="width=device-width,initial-scale=1"/><title>PostgreSQL General HF</title><style> | |
| :root{--bg:#070b1a;--side:#080d1d;--panel:#111827;--line:#26334f;--text:#e5edf9;--muted:#94a3b8;--brand:#6d5dfc;--ok:#35d399;--danger:#ff4d6d;--code:#050816}*{box-sizing:border-box}body{margin:0;background:var(--bg);color:var(--text);font-family:Inter,system-ui,sans-serif}.login{min-height:100vh;display:grid;place-items:center;padding:24px;background:radial-gradient(circle at top,#111b36,#070b1a 55%)}.login-card{width:min(480px,100%);background:var(--panel);border:1px solid var(--line);border-radius:18px;padding:28px}.logo{width:42px;height:42px;border-radius:9px;background:var(--brand);display:grid;place-items:center;font-weight:900}.brand{display:flex;align-items:center;gap:12px;font-weight:900;font-size:20px;margin-bottom:18px}.input,.field{background:#0a1020;border:1px solid #33415f;color:var(--text);border-radius:10px;padding:12px 13px;outline:none}.input{width:100%;margin:8px 0 14px}.field{min-width:260px}.btn{background:var(--brand);color:white;border:0;border-radius:10px;padding:12px 16px;font-weight:800;cursor:pointer}.btn.secondary{background:#1d2942}.btn.danger{background:transparent;color:var(--danger);border:1px solid #5a1e2a}.btn.full{width:100%}.small{font-size:12px;color:var(--muted);line-height:1.6}.hidden{display:none!important}.top{height:58px;display:flex;align-items:center;justify-content:space-between;border-bottom:1px solid var(--line);padding:0 22px;background:#0b1020}.top .brand{margin:0;font-size:18px}.online{font-size:12px;color:var(--ok);background:#06281f;border:1px solid #0a6b50;padding:5px 10px;border-radius:6px}.endpoint{font-family:monospace;color:#a5b4fc;font-size:13px}.wrap{display:grid;grid-template-columns:210px 1fr;min-height:calc(100vh - 58px)}.side{border-right:1px solid var(--line);background:var(--side);padding:18px 14px}.nav{padding:12px;border-radius:8px;color:var(--muted);margin-bottom:8px;cursor:pointer}.nav.active,.nav:hover{background:#172039;color:#a5b4fc}.main{padding:28px;max-width:1280px}.h1{font-size:26px;font-weight:900;margin:0}.sub{color:var(--muted);margin-top:8px}.card{background:var(--panel);border:1px solid var(--line);border-radius:14px;padding:20px;margin-top:24px}.row{display:flex;gap:10px;flex-wrap:wrap;align-items:center}.grid{display:grid;grid-template-columns:repeat(auto-fit,minmax(260px,1fr));gap:14px}.table{width:100%;border-collapse:collapse;margin-top:12px}.table th,.table td{border-bottom:1px solid var(--line);padding:12px;text-align:left}.table th{font-size:12px;color:var(--muted)}.code{background:var(--code);border:1px solid #1c2740;border-radius:8px;padding:12px;font-family:monospace;overflow:auto;color:#dbeafe}.pill{display:inline-block;border:1px solid #33415f;border-radius:999px;padding:3px 8px;font-size:12px;margin-right:4px}.toast{position:fixed;right:20px;bottom:20px;background:#102033;border:1px solid #2b4267;padding:14px 16px;border-radius:10px;z-index:5}.modal{position:fixed;inset:0;background:rgba(0,0,0,.65);display:none;align-items:center;justify-content:center;padding:20px;z-index:4}.modal.show{display:flex}.modal-card{background:#111827;border:1px solid var(--line);border-radius:16px;max-width:920px;width:100%;padding:22px}.linkbox{display:grid;grid-template-columns:1fr auto;gap:8px;align-items:center;margin-top:8px}</style></head><body> | |
| <section id="login" class="login"><div class="login-card"><div class="brand"><div class="logo">PG</div><div>PostgreSQL General<br><span class="small">Admin Login</span></div></div><p class="small">Use the PostgreSQL admin credentials configured in Hugging Face Secrets.</p><input id="loginUser" class="input" placeholder="Admin user" value="admin"/><input id="loginPass" class="input" type="password" placeholder="POSTGRES_PASSWORD"/><button class="btn full" onclick="login()">Login</button><p class="small">Credentials are saved only in this browser's localStorage.</p></div></section> | |
| <section id="app" class="hidden"><div class="top"><div class="brand"><div class="logo">PG</div><span>PostgreSQL General</span><span class="online">● SYSTEM ONLINE</span></div><div class="row"><span class="endpoint" id="endpoint"></span><button class="btn" onclick="runBackup()">One-Click Backup</button><button class="btn secondary" onclick="logout()">Logout</button></div></div><div class="wrap"><aside class="side"><div class="nav active" onclick="show('dashboard')">Dashboard</div><div class="nav" onclick="show('keys')">API Keys</div><div class="nav" onclick="show('reference')">API Reference</div><div class="nav" onclick="show('settings')">Settings</div><div class="nav" onclick="location.href='/docs'">FastAPI Docs</div></aside><main class="main"><section id="dashboard"><h1 class="h1">Dashboard</h1><p class="sub">Manage PostgreSQL API access without curl commands.</p><div class="grid"><div class="card"><b>Status</b><div class="code" id="health">Loading...</div></div><div class="card"><b>Quick Start</b><p class="small">Generate an API Key, then copy links. OpenClaw/OCDB should use the HF Postgres API base URL plus this key.</p><button class="btn" onclick="show('keys')">Generate Key</button> <button class="btn secondary" onclick="show('reference')">API Links</button></div></div></section><section id="keys" class="hidden"><h1 class="h1">API Integration</h1><p class="sub">Generate API Keys and ready-to-use links.</p><div class="card"><h3>Generate API Key</h3><div class="row"><input id="keyName" class="field" placeholder="Key name, e.g. ocdb_project"><label><input type="checkbox" id="scopeRead" checked> read</label><label><input type="checkbox" id="scopeWrite" checked> write</label><button class="btn" onclick="createKey()">Generate Key</button><button class="btn secondary" onclick="loadKeys()">Refresh</button></div><table class="table"><thead><tr><th>ID</th><th>Name</th><th>Prefix</th><th>Scopes</th><th>Active</th><th>Last used</th><th>Action</th></tr></thead><tbody id="keyRows"></tbody></table></div></section><section id="reference" class="hidden"><h1 class="h1">API Reference</h1><p class="sub">Use <code>?api_key=YOUR_KEY</code> or <code>Authorization: Bearer YOUR_KEY</code>.</p><div class="card"><h3>Base URL</h3><div class="code" id="baseUrl"></div><h3>Common API Links</h3><div class="code" id="exampleLinks"></div></div></section><section id="settings" class="hidden"><h1 class="h1">Settings</h1><div class="card"><input id="adminUser" class="field" placeholder="Admin user"><input id="adminPass" class="field" type="password" placeholder="POSTGRES_PASSWORD"><br><br><button class="btn" onclick="saveSettings()">Save Settings</button><button class="btn secondary" onclick="logout()">Logout</button></div></section></main></div></section> | |
| <div class="modal" id="modal"><div class="modal-card"><h2>API Key Generated</h2><p class="small">Copy and save this key now. It will not be shown again.</p><div class="code" id="newKey"></div><h3>Ready-to-use API links</h3><div id="newLinks"></div><br><button class="btn" onclick="copyText(document.getElementById('newKey').innerText)">Copy Key</button> <button class="btn secondary" onclick="closeModal()">Close</button></div></div><div id="toast" class="toast hidden"></div> | |
| <script> | |
| const base=location.origin;function el(id){return document.getElementById(id)}function toast(msg){const t=el('toast');t.textContent=msg;t.classList.remove('hidden');setTimeout(()=>t.classList.add('hidden'),3500)}function adminHeaders(){return {'X-Admin-User':localStorage.getItem('pg_admin_user')||'admin','X-Admin-Password':localStorage.getItem('pg_admin_pass')||'','Content-Type':'application/json'}}async function req(url,opts={}){const r=await fetch(url,opts);const text=await r.text();let data;try{data=JSON.parse(text)}catch{data=text}if(!r.ok)throw new Error(typeof data==='string'?data:(data.detail||JSON.stringify(data)));return data}function showLogin(){el('login').classList.remove('hidden');el('app').classList.add('hidden')}function showApp(){el('login').classList.add('hidden');el('app').classList.remove('hidden');el('endpoint').textContent=location.host;el('baseUrl').textContent=base;el('adminUser').value=localStorage.getItem('pg_admin_user')||'admin';el('adminPass').value=localStorage.getItem('pg_admin_pass')||'';loadHealth();renderExample()}async function login(){const u=el('loginUser').value.trim()||'admin';const p=el('loginPass').value;localStorage.setItem('pg_admin_user',u);localStorage.setItem('pg_admin_pass',p);try{await req('/admin/api-keys',{headers:adminHeaders()});toast('Login success.');showApp();loadKeys()}catch(e){localStorage.removeItem('pg_admin_pass');toast('Login failed: '+e.message)}}function logout(){localStorage.removeItem('pg_admin_user');localStorage.removeItem('pg_admin_pass');showLogin()}function saveSettings(){localStorage.setItem('pg_admin_user',el('adminUser').value||'admin');localStorage.setItem('pg_admin_pass',el('adminPass').value||'');toast('Settings saved.')}function show(id){document.querySelectorAll('main section').forEach(s=>s.classList.add('hidden'));el(id).classList.remove('hidden');document.querySelectorAll('.nav').forEach(n=>n.classList.remove('active'));[...document.querySelectorAll('.nav')].find(n=>n.textContent.toLowerCase().includes(id))?.classList.add('active');if(id==='keys')loadKeys()}async function loadHealth(){try{el('health').textContent=JSON.stringify(await req('/api/health'),null,2)}catch(e){el('health').textContent=e.message}}async function loadKeys(){try{const rows=await req('/admin/api-keys',{headers:adminHeaders()});el('keyRows').innerHTML=rows.map(k=>`<tr><td>${k.id}</td><td>${k.name}</td><td><code>${k.key_prefix}</code></td><td>${(k.scopes||[]).map(s=>`<span class="pill">${s}</span>`).join('')}</td><td>${k.is_active?'Yes':'No'}</td><td>${k.last_used_at||'Never'}</td><td>${k.is_active?`<button class="btn danger" onclick="revokeKey(${k.id})">Revoke</button>`:''}</td></tr>`).join('')||'<tr><td colspan="7" class="small">No keys yet.</td></tr>'}catch(e){toast(e.message)}}function linksFor(key){return [`${base}/api/db-health?api_key=${encodeURIComponent(key)}`,`${base}/api/openclaw/memories?api_key=${encodeURIComponent(key)}`,`${base}/api/openclaw/stats?api_key=${encodeURIComponent(key)}`,`${base}/api/files?api_key=${encodeURIComponent(key)}`,`${base}/api/rss/articles?api_key=${encodeURIComponent(key)}`]}function renderLinks(key){return linksFor(key).map(u=>`<div class="linkbox"><div class="code">${u}</div><button class="btn secondary" onclick="copyText('${u}')">Copy</button></div>`).join('')}async function createKey(){const name=el('keyName').value.trim();if(!name){toast('Please enter a key name.');return}const scopes=[];if(el('scopeRead').checked)scopes.push('read');if(el('scopeWrite').checked)scopes.push('write');try{const data=await req('/admin/api-keys',{method:'POST',headers:adminHeaders(),body:JSON.stringify({name,scopes})});el('newKey').textContent=data.api_key;el('newLinks').innerHTML=renderLinks(data.api_key);el('modal').classList.add('show');loadKeys();renderExample(data.api_key)}catch(e){toast(e.message)}}async function revokeKey(id){if(!confirm('Revoke this API Key?'))return;try{await req('/admin/api-keys/revoke',{method:'POST',headers:adminHeaders(),body:JSON.stringify({id})});toast('API Key revoked.');loadKeys()}catch(e){toast(e.message)}}async function runBackup(){try{const data=await req('/admin/backups/run',{method:'POST',headers:adminHeaders()});toast('Backup created: '+data.latest_file)}catch(e){toast(e.message)}}function copyText(txt){navigator.clipboard.writeText(txt);toast('Copied.')}function closeModal(){el('modal').classList.remove('show')}function renderExample(key='YOUR_API_KEY'){el('exampleLinks').textContent=linksFor(key).join('\n')+'\n\nOCDB connection JSON:\n'+JSON.stringify({baseUrl:base,apiKey:key},null,2)+'\n\nHeader style:\ncurl "'+base+'/api/db-health" -H "Authorization: Bearer '+key+'"'}if(localStorage.getItem('pg_admin_pass'))showApp();else showLogin(); | |
| </script></body></html> | |
| ''' | |
| def dashboard(): | |
| return HTMLResponse(DASHBOARD_HTML) | |
| def health(): | |
| return {"status": "ok", "time": datetime.utcnow().isoformat()} | |
| def db_health(authorization: Optional[str] = Header(default=None), api_key: Optional[str] = Query(default=None)): | |
| verify_api_key(authorization, api_key, required_scope="read") | |
| conn = get_conn(); cur = conn.cursor(); cur.execute("SELECT 1;"); result = cur.fetchone(); cur.close(); conn.close() | |
| return {"status": "ok", "db": result[0]} | |
| def api_db_gateway(authorization: Optional[str] = Header(default=None), api_key: Optional[str] = Query(default=None)): | |
| verify_api_key(authorization, api_key, required_scope="read") | |
| return {"status": "ok", "message": "PostgreSQL API gateway is online", "docs": "/docs"} | |
| class CreateApiKeyRequest(BaseModel): | |
| name: str | |
| scopes: List[str] = ["read", "write"] | |
| def create_api_key(body: CreateApiKeyRequest, x_admin_user: Optional[str] = Header(default=None), x_admin_password: Optional[str] = Header(default=None)): | |
| verify_admin(x_admin_user, x_admin_password) | |
| allowed_scopes = {"read", "write"} | |
| invalid_scopes = [scope for scope in body.scopes if scope not in allowed_scopes] | |
| if invalid_scopes: | |
| raise HTTPException(status_code=400, detail=f"Invalid scopes: {invalid_scopes}") | |
| raw_key = "pgk_" + secrets.token_hex(24) | |
| conn = get_conn(); cur = conn.cursor() | |
| cur.execute("INSERT INTO api_keys (name, key_hash, key_prefix, scopes) VALUES (%s, %s, %s, %s) RETURNING id, created_at;", (body.name, hash_key(raw_key), raw_key[:12], body.scopes)) | |
| row = cur.fetchone(); conn.commit(); cur.close(); conn.close() | |
| return {"id": row[0], "name": body.name, "api_key": raw_key, "key_prefix": raw_key[:12], "scopes": body.scopes, "created_at": row[1], "warning": "This API key is shown only once. Save it now."} | |
| def list_api_keys(x_admin_user: Optional[str] = Header(default=None), x_admin_password: Optional[str] = Header(default=None)): | |
| verify_admin(x_admin_user, x_admin_password) | |
| conn = get_conn(); cur = conn.cursor() | |
| cur.execute("SELECT id, name, key_prefix, scopes, is_active, created_at, last_used_at FROM api_keys ORDER BY id DESC;") | |
| rows = cur.fetchall(); cur.close(); conn.close() | |
| return [{"id": r[0], "name": r[1], "key_prefix": r[2], "scopes": r[3], "is_active": r[4], "created_at": r[5], "last_used_at": r[6]} for r in rows] | |
| class RevokeApiKeyRequest(BaseModel): | |
| id: int | |
| def revoke_api_key(body: RevokeApiKeyRequest, x_admin_user: Optional[str] = Header(default=None), x_admin_password: Optional[str] = Header(default=None)): | |
| verify_admin(x_admin_user, x_admin_password) | |
| conn = get_conn(); cur = conn.cursor(); cur.execute("UPDATE api_keys SET is_active = false WHERE id = %s RETURNING id, name;", (body.id,)); row = cur.fetchone(); conn.commit(); cur.close(); conn.close() | |
| if not row: | |
| raise HTTPException(status_code=404, detail="API key not found") | |
| return {"id": row[0], "name": row[1], "status": "revoked"} | |
| def run_backup(x_admin_user: Optional[str] = Header(default=None), x_admin_password: Optional[str] = Header(default=None)): | |
| verify_admin(x_admin_user, x_admin_password) | |
| os.makedirs(BACKUP_DIR, exist_ok=True) | |
| timestamp = datetime.utcnow().strftime("%Y%m%d_%H%M%S") | |
| backup_file = os.path.join(BACKUP_DIR, f"backup_{POSTGRES_DB}_{timestamp}.sql") | |
| latest_file = os.path.join(BACKUP_DIR, "latest.sql") | |
| env = os.environ.copy(); env["PGPASSWORD"] = POSTGRES_PASSWORD or "" | |
| cmd = ["pg_dump", "-h", "127.0.0.1", "-p", "5432", "-U", POSTGRES_USER, "-d", POSTGRES_DB] | |
| with open(backup_file, "w", encoding="utf-8") as f: | |
| result = subprocess.run(cmd, stdout=f, stderr=subprocess.PIPE, text=True, env=env) | |
| if result.returncode != 0: | |
| raise HTTPException(status_code=500, detail=result.stderr) | |
| shutil.copyfile(backup_file, latest_file) | |
| return {"status": "ok", "backup_file": backup_file, "latest_file": latest_file} | |
| class FileRecord(BaseModel): | |
| user_id: Optional[str] = None | |
| filename: str | |
| file_path: str | |
| file_size: Optional[int] = None | |
| mime_type: Optional[str] = None | |
| def create_file(record: FileRecord, authorization: Optional[str] = Header(default=None), api_key: Optional[str] = Query(default=None)): | |
| verify_api_key(authorization, api_key, required_scope="write") | |
| conn = get_conn(); cur = conn.cursor(); cur.execute("INSERT INTO files (user_id, filename, file_path, file_size, mime_type) VALUES (%s, %s, %s, %s, %s) RETURNING id, created_at;", (record.user_id, record.filename, record.file_path, record.file_size, record.mime_type)); row = cur.fetchone(); conn.commit(); cur.close(); conn.close() | |
| return {"id": row[0], "created_at": row[1], "filename": record.filename, "file_path": record.file_path} | |
| def list_files(authorization: Optional[str] = Header(default=None), api_key: Optional[str] = Query(default=None)): | |
| verify_api_key(authorization, api_key, required_scope="read") | |
| conn = get_conn(); cur = conn.cursor(); cur.execute("SELECT id, user_id, filename, file_path, file_size, mime_type, created_at FROM files ORDER BY id DESC LIMIT 100;"); rows = cur.fetchall(); cur.close(); conn.close() | |
| return [{"id": r[0], "user_id": r[1], "filename": r[2], "file_path": r[3], "file_size": r[4], "mime_type": r[5], "created_at": r[6]} for r in rows] | |
| class ArticleRecord(BaseModel): | |
| source: Optional[str] = None | |
| title: str | |
| url: str | |
| summary: Optional[str] = None | |
| published_at: Optional[str] = None | |
| def create_article(article: ArticleRecord, authorization: Optional[str] = Header(default=None), api_key: Optional[str] = Query(default=None)): | |
| verify_api_key(authorization, api_key, required_scope="write") | |
| conn = get_conn(); cur = conn.cursor() | |
| cur.execute("""INSERT INTO rss_articles (source, title, url, summary, published_at) VALUES (%s, %s, %s, %s, %s) ON CONFLICT (url) DO UPDATE SET title = EXCLUDED.title, summary = EXCLUDED.summary, published_at = EXCLUDED.published_at RETURNING id;""", (article.source, article.title, article.url, article.summary, article.published_at)) | |
| row = cur.fetchone(); conn.commit(); cur.close(); conn.close() | |
| return {"id": row[0], "status": "saved"} | |
| def list_articles(authorization: Optional[str] = Header(default=None), api_key: Optional[str] = Query(default=None)): | |
| verify_api_key(authorization, api_key, required_scope="read") | |
| conn = get_conn(); cur = conn.cursor(); cur.execute("SELECT id, source, title, url, summary, published_at, created_at FROM rss_articles ORDER BY id DESC LIMIT 100;"); rows = cur.fetchall(); cur.close(); conn.close() | |
| return [{"id": r[0], "source": r[1], "title": r[2], "url": r[3], "summary": r[4], "published_at": r[5], "created_at": r[6]} for r in rows] | |
| class OpenClawMemoryRecord(BaseModel): | |
| claw_id: str | |
| content: str | |
| metadata: Optional[dict] = None | |
| def create_openclaw_memory(record: OpenClawMemoryRecord, authorization: Optional[str] = Header(default=None), api_key: Optional[str] = Query(default=None)): | |
| verify_api_key(authorization, api_key, required_scope="write") | |
| conn = get_conn(); cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) | |
| cur.execute("INSERT INTO openclaw_memories (claw_id, content, metadata) VALUES (%s, %s, %s) RETURNING *;", (record.claw_id, record.content, psycopg2.extras.Json(record.metadata) if record.metadata is not None else None)) | |
| row = dict(cur.fetchone()); conn.commit(); cur.close(); conn.close() | |
| return row | |
| def list_openclaw_memories(claw_id: Optional[str] = None, limit: int = 50, authorization: Optional[str] = Header(default=None), api_key: Optional[str] = Query(default=None)): | |
| verify_api_key(authorization, api_key, required_scope="read") | |
| safe_limit = min(max(int(limit or 50), 1), 500) | |
| conn = get_conn(); cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) | |
| if claw_id: | |
| cur.execute("SELECT * FROM openclaw_memories WHERE claw_id = %s ORDER BY created_at DESC LIMIT %s;", (claw_id, safe_limit)) | |
| else: | |
| cur.execute("SELECT * FROM openclaw_memories ORDER BY created_at DESC LIMIT %s;", (safe_limit,)) | |
| rows = [dict(r) for r in cur.fetchall()]; cur.close(); conn.close() | |
| return rows | |
| def openclaw_stats(authorization: Optional[str] = Header(default=None), api_key: Optional[str] = Query(default=None)): | |
| verify_api_key(authorization, api_key, required_scope="read") | |
| conn = get_conn(); cur = conn.cursor() | |
| cur.execute("SELECT pg_database_size(current_database()) AS size_bytes, COUNT(*) FROM openclaw_memories;") | |
| row = cur.fetchone(); cur.close(); conn.close() | |
| return {"sizeBytes": int(row[0]), "memoryCount": int(row[1]), "isHealthy": True} |