"""HTTP client for Scribase's public /v1 management API. Every function here takes an already-resolved `api_key`/`api_base` and returns a plain dict — never raises. The caller (`__init__.py`'s tool handler) is responsible for the handler contract (always return a JSON string, success or error alike). This module makes network calls only to `api_base` (default `https://api.scribase.com`, overridable via the plugin's optional `api_base_url` config key for self-hosted Scribase clusters) and uses only the Python standard library — no third-party dependencies. Four tools, four endpoints: - `create_project` — `PUT .../projects/{id}` + `POST /v1/environments`, provisions real billable resources. - `lint_migration` — `POST .../migrations` with `"apply": false` always hardcoded here, so it only ever runs the control plane's migration linter (missing-RLS, unsafe-grant, lock-unsafe-DDL style rules) and never touches the tenant database. - `run_query` — `POST .../database/query`, a statement the control plane itself runs inside a Postgres `READ ONLY` transaction; this client does not add its own write guard because the server enforces it twice (lexically, then transactionally). - `branch_task` — `POST /v1/environments` (`kind: "preview"`), `POST .../branches/merge`, and `DELETE .../environments/{env}`: a real copy-on-write Postgres branch an agent can create for one task, then either promote (merge its schema back) or discard (delete it), never touching the parent environment except through an explicit, auditable merge. - `memory_task` — `POST .../database/execute` (write-mode SQL, confirmed live against a real throwaway project: this is the write-capable sibling of `database/query` the Scribase Studio SQL editor uses in "write mode", gated to writer-scoped callers, see `crates/control-api/src/http/ database_editor.rs` and `adapters/sql_execute.rs`) for `store`/`forget`, and `POST .../database/query` (the same read-only route `run_query` calls) for `recall`/`search`: gives a Hermes agent a real, persistent, Postgres-backed memory store — not a prompt's own context window, a real table in the user's own Scribase project that survives across sessions. All SQL this function sends is built by this module, never passed through from the model's arguments as raw SQL (that's what `run_query` is for) — only `content`/`metadata`/`scope`/`memory_id` string values are interpolated, and every one goes through `_sql_literal()` first (hex- encoded as `convert_from(decode('', 'hex'), 'UTF8')`, not a quoted- and-escaped literal — see `_sql_literal()`'s docstring for why: live re-verification found that the classic doubled-single-quote escape, while not itself SQL-injectable, tripped the control plane's own backslash guard on ordinary non-adversarial content such as non-ASCII metadata) before it is ever placed in a statement. """ from __future__ import annotations import json import re import time import urllib.error import urllib.request import uuid from typing import Any, Dict, Optional, Tuple _TIMEOUT = 20 # seconds per HTTP call _POLL_ATTEMPTS = 8 _POLL_INTERVAL = 3 # seconds _DEFAULT_REGION = "eu-frankfurt" _DEFAULT_POSTGRES_MAJOR = 17 _DEFAULT_ENVIRONMENT_ID = "production" _TERMINAL_STATES = ("succeeded", "failed", "cancelled") # A branch with a TTL of this many hours or less lands in Scribase's own # "agent branch" slot pool instead of competing with human preview # environments for the project's regular preview quota (confirmed against # `control-api/src/adapters/usage.rs::AGENT_BRANCH_MAX_TTL_HOURS = 24` and # `is_agent_branch()`). `scribase_branch_task` always sends a TTL in this # range so an agent's branches never starve a human's preview slots, and so # a branch nobody discards still self-deletes instead of running forever. _AGENT_BRANCH_MAX_TTL_HOURS = 24 _DEFAULT_BRANCH_TTL_HOURS = 4 _VALID_DATA_MODES = ("snapshot", "sanitized", "schema_only") def _request( method: str, url: str, api_key: str, body: Optional[Dict[str, Any]] = None, extra_headers: Optional[Dict[str, str]] = None, ) -> Tuple[int, Any]: """One HTTP call against the Scribase management API. Returns ``(status, parsed_body)``. ``status`` is 0 when the request could not be sent at all (DNS/connection failure); callers treat that as a terminal error, never a retryable one, to avoid hanging an unattended run (plugin-catalog rule 12). """ data = json.dumps(body).encode("utf-8") if body is not None else None headers = { "Authorization": f"Bearer {api_key}", "Accept": "application/json", # Required, not cosmetic: api.scribase.com sits behind Cloudflare, and a # bare CPython urllib request (the literal "Python-urllib/3.x" default # User-Agent, with no header set at all otherwise) is hard-blocked by # Cloudflare's WAF with a 403 "browser_signature_banned" before it ever # reaches the control plane — reproduced live against production while # building the 2026-11-06 real-session demo. Any non-default string # clears it; this one just also identifies the client honestly. "User-Agent": "scribase-hermes-plugin/1.2.1 (+https://github.com/caelum0x/scribase-hermes-plugin)", } if data is not None: headers["Content-Type"] = "application/json" if extra_headers: headers.update(extra_headers) req = urllib.request.Request(url, data=data, method=method, headers=headers) try: with urllib.request.urlopen(req, timeout=_TIMEOUT) as resp: raw = resp.read().decode("utf-8", "replace") status = resp.status except urllib.error.HTTPError as exc: raw = exc.read().decode("utf-8", "replace") status = exc.code except urllib.error.URLError as exc: return 0, {"error": f"could not reach the Scribase API: {exc.reason}"} if not raw: return status, {} try: return status, json.loads(raw) except json.JSONDecodeError: return status, {"error": raw[:500]} def _idempotency_key() -> str: return str(uuid.uuid4()) def _poll_operation( operation_id: str, organization_id: str, *, api_key: str, api_base: str ) -> Tuple[Optional[str], Dict[str, Any]]: """Polls `GET /v1/operations/{id}` until it leaves pending/running, bounded so an unattended run (cron, gateway) can never hang on one tool call (plugin-catalog rule 12). Returns ``(final_state, last_operation_body)``; ``final_state`` is ``None`` if every poll attempt was spent without the operation reaching a terminal state. """ final_state: Optional[str] = None last_body: Dict[str, Any] = {} for _ in range(_POLL_ATTEMPTS): time.sleep(_POLL_INTERVAL) status, body = _request( "GET", f"{api_base}/v1/operations/{operation_id}", api_key, extra_headers={"X-Scribase-Organization-ID": organization_id}, ) if status == 200: last_body = body final_state = body.get("state") if final_state in _TERMINAL_STATES: break return final_state, last_body def _read_environment_access( organization_id: str, project_id: str, environment_id: str, *, api_key: str, api_base: str ) -> Dict[str, Any]: """Reads back a ready environment's API keys and Postgres connection URI. Returns a dict always containing ``status`` (``"ready"`` or a ``"warning"``-carrying partial result) plus whichever of ``api_url``/``anon_key``/``publishable_key``/``service_role_key``/ ``secret_key``/``connection_uri`` could be read. """ status, keys = _request( "GET", f"{api_base}/v1/organizations/{organization_id}/projects/{project_id}" f"/environments/{environment_id}/api-keys", api_key, ) if status != 200: return { "status": "ready", "warning": "environment is ready, but failed to read its API keys", "detail": keys, } status, conn = _request( "GET", f"{api_base}/v1/organizations/{organization_id}/projects/{project_id}" f"/environments/{environment_id}/connection", api_key, ) connection_uri = conn.get("connection_uri") if status == 200 else None return { "status": "ready", "api_url": keys.get("api_url"), "anon_key": keys.get("anon_key"), "publishable_key": keys.get("publishable_key"), "service_role_key": keys.get("service_role_key"), "secret_key": keys.get("secret_key"), "connection_uri": connection_uri, } def create_project(args: Dict[str, Any], *, api_key: str, api_base: str) -> Dict[str, Any]: """Create a Scribase project and its first environment, then return connection info. Required args: ``organization_id``, ``project_id``. Optional: ``environment_id`` (default "production"), ``region`` (default "eu-frankfurt", hosted Scribase's region), ``postgres_major`` (default 17, the only currently supported version). """ org = str(args.get("organization_id") or "").strip() project = str(args.get("project_id") or "").strip() environment = str(args.get("environment_id") or "").strip() or _DEFAULT_ENVIRONMENT_ID region = str(args.get("region") or "").strip() or _DEFAULT_REGION try: postgres_major = int(args.get("postgres_major") or _DEFAULT_POSTGRES_MAJOR) except (TypeError, ValueError): return {"error": "postgres_major must be an integer (only 17 is currently supported)."} if not org: return {"error": "organization_id is required (the existing Scribase org this project belongs to)."} if not project: return {"error": "project_id is required."} # 1. Create (or confirm) the project — idempotent PUT. status, body = _request( "PUT", f"{api_base}/v1/organizations/{org}/projects/{project}", api_key, body={"region": region, "postgres_major": postgres_major}, extra_headers={"Idempotency-Key": _idempotency_key()}, ) if status == 0: return body if status not in (200, 201): return {"error": "failed to create project", "status": status, "detail": body} # 2. Create its first environment — async; returns an operation to poll. # `ttl_hours` is omitted entirely rather than sent as a literal JSON # `null` — a production environment has no TTL, and the control # plane's request-schema validator rejects an explicit null for this # optional field with `400 invalid_json` ("request JSON does not # match the operation schema"), confirmed live against production # while building the 2026-11-06 real-session demo (A/B: omitting the # key succeeds — a real `409 conflict` on an already-provisioned # environment, or a real `202` on a genuinely new one; sending # `"ttl_hours": null` 400s every time). status, body = _request( "POST", f"{api_base}/v1/environments", api_key, body={ "organization_id": org, "project_id": project, "environment_id": environment, "kind": "production", "data_mode": "schema_only", "region": region, "postgres_major": postgres_major, }, extra_headers={"Idempotency-Key": _idempotency_key()}, ) if status == 0: return body if status != 202: return {"error": "failed to create environment", "status": status, "detail": body} operation_id = body.get("id") # 3. Poll the operation until it leaves pending/running/waiting_retry, bounded so an # unattended run (cron, gateway) can never hang on this tool call (rule 12). final_state, last_op_body = _poll_operation(operation_id, org, api_key=api_key, api_base=api_base) if final_state != "succeeded": still_going = final_state not in ("failed", "cancelled") return { "status": "provisioning" if still_going else "failed", "organization_id": org, "project_id": project, "environment_id": environment, "operation_id": operation_id, "message": ( f"Environment is still provisioning (state={final_state!r}). Call this tool " f"again shortly, or poll GET /v1/operations/{operation_id} directly." if still_going else f"Environment provisioning failed (state={final_state!r})." ), "operation": last_op_body, } # 4. Fetch the new environment's API keys and connection string. access = _read_environment_access(org, project, environment, api_key=api_key, api_base=api_base) if access.get("status") != "ready" or "warning" in access: return access return { "organization_id": org, "project_id": project, "environment_id": environment, "region": region, **access, } def lint_migration(args: Dict[str, Any], *, api_key: str, api_base: str) -> Dict[str, Any]: """Lint one migration's SQL against Scribase's real migration-safety rules. Calls ``POST .../migrations`` with a single ``migration`` entry and ``"apply": false`` (hardcoded here, never read from ``args``) — this is the control plane's own dry-run path: the migration is parsed into a schema model and run through the blocking rules (missing RLS, GRANT to PUBLIC, a permissive `USING (true)` policy, a write policy with no `WITH CHECK`, a `SECURITY DEFINER` function) and the one advisory rule (a foreign key with no supporting index), but nothing ever touches the tenant database — the control plane only opens a database session when ``apply`` is true and the lint is clean, which this call can never request. """ org = str(args.get("organization_id") or "").strip() project = str(args.get("project_id") or "").strip() environment = str(args.get("environment_id") or "").strip() up = str(args.get("up") or "").strip() down = args.get("down") name = str(args.get("migration_name") or "").strip() or "hermes_lint_check" if not org: return {"error": "organization_id is required."} if not project: return {"error": "project_id is required."} if not environment: return {"error": "environment_id is required."} if not up: return {"error": "up is required (the migration's forward SQL to lint)."} migration: Dict[str, Any] = { "version": time.strftime("%Y%m%d%H%M%S", time.gmtime()), "name": name, "up": up, } if down: migration["down"] = str(down) status, body = _request( "POST", f"{api_base}/v1/organizations/{org}/projects/{project}/environments/{environment}/migrations", api_key, # "apply" is always False here — this tool only ever lints. body={"migration": migration, "apply": False}, ) if status == 0: return body if status != 200: return {"error": "migration lint request failed", "status": status, "detail": body} findings = body.get("lintFindings", []) if isinstance(body, dict) else [] blocking = [f for f in findings if isinstance(f, dict) and f.get("severity") == "BLOCK"] warnings = [f for f in findings if isinstance(f, dict) and f.get("severity") == "WARN"] return { "status": "blocked" if blocking else ("warnings" if warnings else "clean"), "safe_to_apply": not blocking, "blocking_findings": blocking, "warning_findings": warnings, "plan": body.get("plan") if isinstance(body, dict) else None, } def run_query(args: Dict[str, Any], *, api_key: str, api_base: str) -> Dict[str, Any]: """Run one read-only statement via ``POST .../database/query``. The control plane itself enforces read-only in two independent layers (a static keyword check, then a Postgres `READ ONLY` transaction with a bounded statement timeout), so this client adds no SQL-shape validation of its own — any write/DDL statement is rejected by the server, not silently reinterpreted here. """ org = str(args.get("organization_id") or "").strip() project = str(args.get("project_id") or "").strip() environment = str(args.get("environment_id") or "").strip() query = str(args.get("query") or "").strip() if not org: return {"error": "organization_id is required."} if not project: return {"error": "project_id is required."} if not environment: return {"error": "environment_id is required."} if not query: return {"error": "query is required (a single read-only SQL statement)."} body: Dict[str, Any] = {"query": query} limit = args.get("limit") if limit is not None: try: body["limit"] = int(limit) except (TypeError, ValueError): return {"error": "limit must be an integer between 1 and 1000."} status, result = _request( "POST", f"{api_base}/v1/organizations/{org}/projects/{project}/environments/{environment}/database/query", api_key, body=body, ) if status == 0: return result if status != 200: return {"error": "query failed", "status": status, "detail": result} return result def branch_task(args: Dict[str, Any], *, api_key: str, api_base: str) -> Dict[str, Any]: """Dispatches one of three real, agent-native lifecycle actions on a throwaway, real copy-on-write Postgres branch: - ``"create"`` — cuts a new branch environment from a parent (default ``"production"``), with a bounded TTL that keeps it in Scribase's dedicated agent-branch slot pool, then returns its connection string and API keys, same shape as `create_project`. - ``"promote"`` — three-way merges the branch's schema back into its parent via ``POST .../branches/merge``. The server applies the merge only when it is conflict-free, or when ``force`` is set and the organization has no MFA factor (an MFA-protected organization's API key cannot answer the step-up challenge a forced merge requires — the server rejects it with ``mfa_required`` rather than silently skipping the check, and this client returns that rejection verbatim). - ``"discard"`` — deletes the branch outright via ``DELETE .../environments/{env}``, instead of waiting for its TTL. No action ever touches the parent environment except an applied promote, and even that is a schema-only three-way merge the control plane itself plans and can refuse on conflict. """ action = str(args.get("action") or "").strip().lower() org = str(args.get("organization_id") or "").strip() project = str(args.get("project_id") or "").strip() if not org: return {"error": "organization_id is required."} if not project: return {"error": "project_id is required."} if action == "create": return _branch_create(args, org, project, api_key=api_key, api_base=api_base) if action == "promote": return _branch_promote(args, org, project, api_key=api_key, api_base=api_base) if action == "discard": return _branch_discard(args, org, project, api_key=api_key, api_base=api_base) return {"error": "action must be one of: create, promote, discard."} def _branch_create( args: Dict[str, Any], org: str, project: str, *, api_key: str, api_base: str ) -> Dict[str, Any]: """``create``: cuts a new ``kind: "preview"`` environment forked from ``base_environment_id`` (default ``"production"``). The control plane captures the parent's schema as the branch's merge base automatically on creation — no separate `POST .../branches` call is needed (confirmed by reading `control-api/src/http/previews.rs::provision_environment()`, which calls `branch::record_fork_point` itself whenever a preview is cut from an explicit or implicit base). If that capture fails, the server reports it as a top-level `branch_lineage_error` on this same response; this client surfaces it as `branch_lineage_warning` rather than dropping it, since a branch with no recorded merge base cannot later be promoted. """ base = str(args.get("base_environment_id") or "").strip() or _DEFAULT_ENVIRONMENT_ID branch_id = str(args.get("environment_id") or "").strip() or f"agent-{uuid.uuid4().hex[:8]}" data_mode = str(args.get("data_mode") or "").strip() or "snapshot" region = str(args.get("region") or "").strip() or _DEFAULT_REGION try: postgres_major = int(args.get("postgres_major") or _DEFAULT_POSTGRES_MAJOR) except (TypeError, ValueError): return {"error": "postgres_major must be an integer (only 17 is currently supported)."} try: ttl_hours = int(args.get("ttl_hours") or _DEFAULT_BRANCH_TTL_HOURS) except (TypeError, ValueError): return {"error": "ttl_hours must be an integer."} if branch_id == base: return {"error": "a branch cannot be its own base environment."} if data_mode not in _VALID_DATA_MODES: return {"error": f"data_mode must be one of: {', '.join(_VALID_DATA_MODES)}."} if not 1 <= ttl_hours <= _AGENT_BRANCH_MAX_TTL_HOURS: return { "error": ( f"ttl_hours must be between 1 and {_AGENT_BRANCH_MAX_TTL_HOURS} — a branch " "this tool creates always stays in Scribase's agent-branch slot pool, never " "the human preview pool, and this is its allowed TTL range." ) } status, body = _request( "POST", f"{api_base}/v1/environments", api_key, body={ "organization_id": org, "project_id": project, "environment_id": branch_id, "kind": "preview", "data_mode": data_mode, "region": region, "postgres_major": postgres_major, "ttl_hours": ttl_hours, "base_environment_id": base, }, extra_headers={"Idempotency-Key": _idempotency_key()}, ) if status == 0: return body if status not in (200, 202): return {"error": "failed to create branch", "status": status, "detail": body} lineage_warning = body.get("branch_lineage_error") if isinstance(body, dict) else None operation_id = body.get("id") if isinstance(body, dict) else None if not operation_id: return {"error": "branch create accepted but no operation id was returned", "detail": body} final_state, last_op_body = _poll_operation(operation_id, org, api_key=api_key, api_base=api_base) if final_state != "succeeded": still_going = final_state not in ("failed", "cancelled") result = { "status": "provisioning" if still_going else "failed", "organization_id": org, "project_id": project, "environment_id": branch_id, "base_environment_id": base, "operation_id": operation_id, "message": ( f"Branch is still provisioning (state={final_state!r}). Call this tool again " f"shortly with action='create' and the same environment_id, or poll " f"GET /v1/operations/{operation_id} directly." if still_going else f"Branch provisioning failed (state={final_state!r})." ), "operation": last_op_body, } if lineage_warning: result["branch_lineage_warning"] = lineage_warning return result access = _read_environment_access(org, project, branch_id, api_key=api_key, api_base=api_base) result = { "organization_id": org, "project_id": project, "environment_id": branch_id, "base_environment_id": base, "data_mode": data_mode, "ttl_hours": ttl_hours, "region": region, **access, "next_step": ( "When the task is done, call this tool again with action='promote' to merge " "this branch's schema back into " f"{base!r}, or action='discard' to delete it immediately. Otherwise it " f"self-deletes in {ttl_hours}h." ), } if lineage_warning: result["branch_lineage_warning"] = lineage_warning return result def _branch_promote( args: Dict[str, Any], org: str, project: str, *, api_key: str, api_base: str ) -> Dict[str, Any]: """``promote``: three-way merges ``environment_id`` (the branch, "head") into ``base_environment_id`` (default ``"production"``) via ``POST .../branches/merge``. Reads the real response fields the control plane renders (`merge_to_json` in `control-api/src/adapters/branch.rs`) — ``applied``/``clean``/``changes``/``conflicts``/``statements`` — rather than the simplified ``merged`` field the docs page's example shows, since the compiled server is what actually answers. """ head = str(args.get("environment_id") or "").strip() base = str(args.get("base_environment_id") or "").strip() or _DEFAULT_ENVIRONMENT_ID force = bool(args.get("force")) dry_run = bool(args.get("dry_run")) if not head: return {"error": "environment_id is required (the branch to promote)."} if head == base: return {"error": "a branch cannot be promoted into itself."} status, body = _request( "POST", f"{api_base}/v1/organizations/{org}/projects/{project}/branches/merge", api_key, body={"base": base, "head": head, "force": force, "dry_run": dry_run}, ) if status == 0: return body if status != 200 or not isinstance(body, dict): # Real, common failures here: 409 branch_not_registered / merge_base_mismatch, # 403 mfa_required (a forced merge on an MFA-protected organization), 422 # merge_rejected, 429 rate_limited. The server's detail explains which. return {"error": "promote (merge) failed", "status": status, "detail": body} applied = bool(body.get("applied")) clean = bool(body.get("clean")) conflicts = body.get("conflicts") or [] changes = body.get("changes") or [] if applied: branch_status = "merged" elif conflicts: branch_status = "blocked_by_conflicts" elif dry_run: branch_status = "dry_run_clean" else: branch_status = "no_changes" result = { "status": branch_status, "base_environment_id": base, "environment_id": head, "applied": applied, "dry_run": dry_run, "clean": clean, "changes": changes, "conflicts": conflicts, "statements": body.get("statements") or [], "fork_base_trusted": body.get("fork_base_trusted"), "fork_base_origin": body.get("fork_base_origin"), } if body.get("warnings"): result["warnings"] = body["warnings"] return result def _branch_discard( args: Dict[str, Any], org: str, project: str, *, api_key: str, api_base: str ) -> Dict[str, Any]: """``discard``: deletes ``environment_id`` via ``DELETE .../environments/{env}`` instead of waiting for its TTL. Deletion is permanent and unconditional — there is no confirmation step, matching the real endpoint's own contract ("Deletion is permanent.", per `docs.scribase.com/docs/api/environments`). """ branch_id = str(args.get("environment_id") or "").strip() if not branch_id: return {"error": "environment_id is required (the branch to discard)."} status, body = _request( "DELETE", f"{api_base}/v1/organizations/{org}/projects/{project}/environments/{branch_id}", api_key, extra_headers={"Idempotency-Key": _idempotency_key()}, ) if status == 0: return body if status != 202: return {"error": "failed to discard branch", "status": status, "detail": body} operation_id = body.get("id") if isinstance(body, dict) else None if not operation_id: return {"error": "branch discard accepted but no operation id was returned", "detail": body} final_state, last_op_body = _poll_operation(operation_id, org, api_key=api_key, api_base=api_base) if final_state == "succeeded": return {"status": "discarded", "organization_id": org, "project_id": project, "environment_id": branch_id} still_going = final_state not in ("failed", "cancelled") return { "status": "discarding" if still_going else "failed", "organization_id": org, "project_id": project, "environment_id": branch_id, "operation_id": operation_id, "message": ( f"Branch is still being discarded (state={final_state!r}). It will finish shortly." if still_going else f"Branch discard failed (state={final_state!r})." ), "operation": last_op_body, } # --- memory_task ----------------------------------------------------------- # # Backs a Hermes agent's persistent memory with one dedicated table per # project, `public.hermes_agent_memories`, created on first use and reused # after that (idempotent — every DDL statement below is written so a second # call never errors). `scope` lets more than one agent/user share a project # without reading each other's memories (e.g. one scope per end user or per # conversation); it defaults to `"default"` for a single-agent setup. _MEMORY_TABLE = "public.hermes_agent_memories" _MEMORY_SCOPE_MAX_LEN = 128 _MEMORY_CONTENT_MAX_LEN = 8192 _VALID_MEMORY_ACTIONS = ("store", "recall", "search", "forget") # A Postgres UUID's canonical textual form — validated before any `forget` # call builds a `WHERE id = ''` clause from it. _UUID_RE = re.compile( r"^[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}$" ) def _sql_literal(value: str) -> str: """Encodes ``value`` as a Postgres text expression via a hex-encoded ``decode(..., 'hex')``, instead of a classically single-quoted-and- escaped string literal. This plugin used to double every embedded single quote (the textbook Postgres string-literal escape) and rely on the server always running these statements with `standard_conforming_strings = on` so a trailing backslash could never swallow the closing quote. That assumption does hold (`control-api/src/adapters/psql.rs` forces it on for every session before any statement runs) — but live re-verification against a real temporary Scribase project (2026-11-06) found a *different*, real, reproducible bug the quote-doubling approach was not defending against at all: the control plane's own client-syntax guard (`client_command_forbidden`, "it has a backslash outside an E'...' string or a dollar-quoted body") rejects the entire statement outright whenever the built literal contains a literal backslash character — which it will for *any* ordinary, non-adversarial content: `content` with a literal backslash (a Windows path, a regex), or any `metadata` value with a non-ASCII character (Python's json.dumps default ensure_ascii escapes those as a backslash-u-XXXX sequence) or an embedded double quote (escaped as a backslash-double-quote pair). Confirmed live: a `store` call with a metadata value containing non-English text (accented Latin, Turkish, Japanese) 400'd every time under the old quote-doubling helper — a real reliability gap for ordinary non-English content, not just an adversarial one. A hex-encoded `decode()` call sidesteps the whole class of problem: the literal it builds can only ever contain the ASCII digits/letters `0-9a-f` inside a plain single-quoted string with no embedded quote, backslash, or comment sequence possible, so it can never trip the backslash guard and never depends on `standard_conforming_strings` either way. NUL bytes are stripped first (Postgres `text` can never store one). """ cleaned = value.replace("\x00", "") hex_value = cleaned.encode("utf-8").hex() return f"convert_from(decode('{hex_value}', 'hex'), 'UTF8')" def _ensure_memory_table(organization_id: str, project_id: str, environment_id: str, *, api_key: str, api_base: str): """Idempotent DDL: creates the memory table, its scope index, and its permissive RLS policy if (and only if) they do not already exist. Safe to call on every `memory_task` invocation — every statement here is a no-op on a project that already has them. """ ddl = ( f"create table if not exists {_MEMORY_TABLE} (" "id uuid primary key default gen_random_uuid(), " "scope text not null default 'default', " "content text not null, " "metadata jsonb not null default '{}'::jsonb, " "created_at timestamptz not null default now()" "); " f"create index if not exists hermes_agent_memories_scope_idx " f"on {_MEMORY_TABLE} (scope, created_at desc); " f"alter table {_MEMORY_TABLE} enable row level security; " "do $hermes_memory_policy$ " "begin " "if not exists (" " select 1 from pg_policies" " where schemaname = 'public' and tablename = 'hermes_agent_memories'" " and policyname = 'hermes_agent_memories_service_all'" ") then " " create policy hermes_agent_memories_service_all on " f" {_MEMORY_TABLE} for all using (true) with check (true); " "end if; " "end " "$hermes_memory_policy$;" ) status, body = _request( "POST", f"{api_base}/v1/organizations/{organization_id}/projects/{project_id}" f"/environments/{environment_id}/database/execute", api_key, body={"query": ddl}, ) if status == 0: return body if status != 200: return {"error": "failed to prepare the memory table", "status": status, "detail": body} return None def memory_task(args: Dict[str, Any], *, api_key: str, api_base: str) -> Dict[str, Any]: """Dispatches one of four real, persistent-memory actions, all backed by a real table (`hermes_agent_memories`) in the project's own Postgres database — not anything held in the agent's own context window: - ``"store"`` — inserts one memory (`content`, optional `metadata` object, optional `scope`) via the write-mode `database/execute` route and returns its id. - ``"recall"`` — lists the most recent memories in a scope (optional `limit`, default 20, max 200) via the read-only `database/query` route, newest first. - ``"search"`` — same as `recall`, filtered to memories whose content contains `query` (case-insensitive substring match, `ILIKE`). - ``"forget"`` — deletes one memory by `memory_id` (a UUID returned by a prior `store`) via `database/execute`. Deleting by id only — there is no "forget everything in this scope" action, so a single bad call can never wipe a whole scope's memory. """ action = str(args.get("action") or "").strip().lower() org = str(args.get("organization_id") or "").strip() project = str(args.get("project_id") or "").strip() environment = str(args.get("environment_id") or "").strip() or _DEFAULT_ENVIRONMENT_ID if action not in _VALID_MEMORY_ACTIONS: return {"error": f"action must be one of: {', '.join(_VALID_MEMORY_ACTIONS)}."} if not org: return {"error": "organization_id is required."} if not project: return {"error": "project_id is required."} scope = str(args.get("scope") or "default").strip() or "default" if len(scope) > _MEMORY_SCOPE_MAX_LEN: return {"error": f"scope must be at most {_MEMORY_SCOPE_MAX_LEN} characters."} if action == "store": content = str(args.get("content") or "").strip() if not content: return {"error": "content is required for action='store'."} if len(content) > _MEMORY_CONTENT_MAX_LEN: return {"error": f"content must be at most {_MEMORY_CONTENT_MAX_LEN} characters."} metadata = args.get("metadata") if metadata is not None and not isinstance(metadata, dict): return {"error": "metadata must be a JSON object if provided."} prep_error = _ensure_memory_table(org, project, environment, api_key=api_key, api_base=api_base) if prep_error is not None: return prep_error metadata_json = json.dumps(metadata or {}) query = ( f"insert into {_MEMORY_TABLE} (scope, content, metadata) values (" f"{_sql_literal(scope)}, {_sql_literal(content)}, " f"{_sql_literal(metadata_json)}::jsonb" ") returning id, created_at;" ) status, body = _request( "POST", f"{api_base}/v1/organizations/{org}/projects/{project}/environments/{environment}/database/execute", api_key, body={"query": query}, ) if status == 0: return body if status != 200 or not isinstance(body, dict): return {"error": "failed to store memory", "status": status, "detail": body} statements = body.get("statements") or [] rows = statements[0].get("rows") if statements and isinstance(statements[0], dict) else None row = rows[0] if rows else {} return { "status": "stored", "scope": scope, "memory_id": row.get("id"), "created_at": row.get("created_at"), } if action == "forget": memory_id = str(args.get("memory_id") or "").strip() if not memory_id: return {"error": "memory_id is required for action='forget' (the id a prior store returned)."} if not _UUID_RE.match(memory_id): return {"error": "memory_id must be a UUID, as returned by a prior action='store' call."} query = ( f"delete from {_MEMORY_TABLE} where id = {_sql_literal(memory_id)} " f"and scope = {_sql_literal(scope)} returning id;" ) status, body = _request( "POST", f"{api_base}/v1/organizations/{org}/projects/{project}/environments/{environment}/database/execute", api_key, body={"query": query}, ) if status == 0: return body if status != 200 or not isinstance(body, dict): return {"error": "failed to forget memory", "status": status, "detail": body} statements = body.get("statements") or [] affected = statements[0].get("affected_rows") if statements and isinstance(statements[0], dict) else None rows = statements[0].get("rows") if statements and isinstance(statements[0], dict) else None found = bool(rows) or (isinstance(affected, int) and affected > 0) return { "status": "forgotten" if found else "not_found", "scope": scope, "memory_id": memory_id, } # action in ("recall", "search") try: limit = int(args.get("limit") or 20) except (TypeError, ValueError): return {"error": "limit must be an integer between 1 and 200."} limit = max(1, min(limit, 200)) where = f"scope = {_sql_literal(scope)}" if action == "search": search_text = str(args.get("query") or "").strip() if not search_text: return {"error": "query is required for action='search' (substring to match, case-insensitive)."} where += f" and content ilike {_sql_literal('%' + search_text + '%')}" query = ( f"select id, content, metadata, created_at from {_MEMORY_TABLE} " f"where {where} order by created_at desc limit {limit};" ) status, body = _request( "POST", f"{api_base}/v1/organizations/{org}/projects/{project}/environments/{environment}/database/query", api_key, body={"query": query, "limit": limit}, ) if status == 0: return body if status == 400 and isinstance(body, dict) and body.get("code") in ("relation_does_not_exist", "undefined_table"): # No memory has ever been stored in this project yet — the table # from `_ensure_memory_table` doesn't exist. Not an error: there is # nothing to recall. return {"status": "ok", "scope": scope, "memories": [], "note": "no memories stored yet"} if status != 200 or not isinstance(body, dict): return {"error": f"failed to {action} memories", "status": status, "detail": body} return { "status": "ok", "scope": scope, "memories": body.get("rows") or [], "row_count": body.get("row_count"), "truncated": body.get("truncated"), }