api.py

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()


@app.on_event("startup")
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>
'''


@app.get("/", response_class=HTMLResponse)
def dashboard():
    return HTMLResponse(DASHBOARD_HTML)


@app.get("/api/health")
def health():
    return {"status": "ok", "time": datetime.utcnow().isoformat()}


@app.get("/api/db-health")
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]}


@app.get("/api/db")
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"]


@app.post("/admin/api-keys")
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."}


@app.get("/admin/api-keys")
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


@app.post("/admin/api-keys/revoke")
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"}


@app.post("/admin/backups/run")
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


@app.post("/api/files")
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}


@app.get("/api/files")
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


@app.post("/api/rss/articles")
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"}


@app.get("/api/rss/articles")
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


@app.post("/api/openclaw/memories")
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


@app.get("/api/openclaw/memories")
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


@app.get("/api/openclaw/stats")
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}