Architectural Decisions

1. Why PostgreSQL

PgArachne is deliberately built exclusively on top of PostgreSQL and does not try to be database-agnostic. Most of what the gateway offers is not custom Go logic, but a direct use of PostgreSQL features.

Why PostgreSQL:

• Built-in permission model: Roles, GRANT/REVOKE, and EXECUTE privileges on functions are part of the database. PgArachne therefore needs no authorization layer of its own — it simply switches the role with SET LOCAL ROLE and PostgreSQL takes care of the rest. The same access rules apply to the API, psql, and any other tool.
• Row-Level Security: Row-level policies are evaluated against the role the call runs as. Data isolation between users or tenants thus lives in the database, not in the gateway code.
• Native JSON (jsonb): The function(jsonb) → json contract maps exactly onto the body of a JSON-RPC request and response. There is no need to map parameters to types or generate envelopes — JSON passes through the gateway unchanged.
• Transactional guarantees: Every call runs in a single transaction with full ACID guarantees. A function can atomically write to multiple tables, and on error everything is rolled back, without the gateway having to know anything about it.
• Powerful programming model: PL/pgSQL, SQL functions, and other procedural languages let you write business logic where the data lives, without shipping intermediate results over the network.
• LISTEN/NOTIFY: Real-time notifications (decision 5) come straight from a PostgreSQL mechanism. No external message broker is needed.
• System catalog introspection: The list of callable methods, their descriptions, and their parameter schemas are read from pg_proc and function comments. capabilities, MCP tools/list, and the OpenAPI specification are all derived from a single source of truth, so they cannot drift from reality.
• Extension ecosystem: PostGIS, pgvector, TimescaleDB, pg_trgm, and other extensions are immediately available as ordinary SQL functions, and therefore also as API methods and tools for AI agents — without a single line of Go code.
• Openness and maturity: A free license with no vendor lock-in, decades of proven production use, active development, and availability from all major cloud providers as well as a self-hosted instance.

Why not another database or a database-agnostic layer:

• Lowest common denominator: Supporting multiple databases would mean giving up precisely the features PgArachne is built on — the role-based security model, jsonb, LISTEN/NOTIFY, and function introspection. What would remain is a thin and less secure layer on top of generic SQL.
• MySQL/MariaDB: They have no native Row-Level Security or equivalent of LISTEN/NOTIFY, and their JSON and procedural logic support is more limited.
• SQL Server and Oracle: Proprietary licensing and operating costs run counter to the goal of easy, free deployment as a single binary.
• NoSQL databases (document, key-value, columnar): The scaling and schema flexibility that NoSQL is chosen for are not the main benefit for PgArachne. The document storage it needs is covered by jsonb, including indexing and querying, alongside relational data and within the same transaction. What is missing, on the other hand, is what PgArachne stands on: server-side functions callable with granular EXECUTE privileges for a specific role, Row-Level Security, function introspection from the catalog, and a unified query language. Without them, the gateway would have to handle authorization, validation, and API description itself — and would cease to be a thin layer.
• SQLite: It is an embedded database without the server-side role and permission model on which all of PgArachne’s security is built.

As a result, PgArachne stays small: it delegates security, transactions, notifications, and API discovery to PostgreSQL and itself takes care only of protocol translation and authentication.

2. PostgreSQL Functions as API Surface

PgArachne intentionally exposes database functions rather than raw tables.

Why functions:

• Encapsulation: Business logic lives co-located with the data in the database—one place to audit, version, and secure.
• Explicit Security: Only functions that are explicitly granted EXECUTE permissions to a specific role are accessible via the API.
• Abstraction: Input validation, computed fields, and complex multi-table operations are hidden from the client, providing a clean interface.

Why not table-level CRUD:

• Tight Coupling: Exposing tables directly ties your API to your internal database schema, making it difficult to refactor the database without breaking clients.
• Business Rule Fragmentation: Business logic ends up split between database constraints and whatever middleware is used to filter HTTP requests.

3. Go vs. Alternatives

PgArachne is written in Go to provide the best balance of performance and deployment simplicity.

Why Go:

• Static Binaries: Compiles to a single binary with zero external dependencies. Deployment is as simple as copying the file to the server.
• Concurrency: Go’s goroutines make handling thousands of concurrent SSE and database connections lightweight and straightforward.
• Robust Standard Library: The built-in libraries for HTTP, TLS, and JSON are production-grade and require no “node_modules” or external runtimes.
• Cross-Compilation: Easily targets Linux, macOS, and Windows (amd64 and arm64) from any development machine.

Why not Node.js, PHP, or Ruby:

• Runtimes: These require installing a specific runtime environment on every target machine.
• Efficiency: Node’s single-threaded loop or PHP’s process-per-request model are less efficient for maintaining thousands of idle SSE connections.
• Memory Footprint: Go uses significantly less memory per connection than scripted languages.

Why not Rust:

• Development Velocity: While Rust offers extreme performance, its complexity (borrow checker) slows down iteration for an I/O-bound tool where Go’s performance is already more than sufficient.

Why not C/C++:

• Safety: Manual memory management adds significant security risks (buffer overflows) for no meaningful performance gain in a gateway application.

4. JSON-RPC 2.0 vs. REST

PgArachne uses JSON-RPC 2.0 as its primary communication protocol instead of traditional REST.

Why JSON-RPC 2.0:

• Single Endpoint: All communication happens via POST /{prefix}/{database}/jsonrpc. There is no need to design complex URL structures or debate HTTP verb semantics.
• Self-Contained Calls: Each request is a complete JSON object (method + params + id). This format is trivially generated and parsed by LLMs and AI agents with high reliability.
• Standardized Error Handling: Error codes and messages are part of the specification, eliminating the need to “invent” HTTP status code conventions for business errors.
• Batching: The protocol natively supports batch requests, allowing multiple operations (e.g., several function calls) in a single HTTP round-trip without extra work.
• Discovery: The capabilities endpoint provides a full description of the API in a format that AI agents can consume to understand available tools without hallucinations.

Why not REST:

• Complexity for AI: REST semantics (GET/POST/PATCH/DELETE + URL params + body) are spread across multiple places, making it harder for AI agents to construct calls reliably.
• Schema Leakage: CRUD-over-tables (like PostgREST) often leaks the internal database structure directly into the API. PgArachne deliberately exposes functions, keeping business logic encapsulated in SQL.
• Lack of Standards: REST offers no universal standard for batch operations, cross-platform error envelopes, or automated API discovery.

5. SSE (Server-Sent Events) vs. WebSockets

For real-time notifications, PgArachne implements Server-Sent Events (SSE).

Why SSE:

• Plain HTTP: SSE is standard HTTP. It works through proxies, load balancers, and CDNs without special “protocol upgrade” configuration.
• Native Browser Support: The EventSource API is built into all modern browsers and handles automatic reconnection without any client libraries.
• Matches NOTIFY Semantics: PostgreSQL’s NOTIFY is unidirectional (server to client), which maps perfectly to SSE.
• Multiplexing: Over HTTP/2, hundreds of SSE streams can share a single TCP connection, making it extremely efficient.
• Operational Simplicity: SSE connections appear as normal HTTP requests in logs and monitoring tools, making them easier to debug and rate-limit.

Why not WebSockets:

• Unnecessary Bidirectionality: Since the client never needs to send data back over the notification channel, the complexity of WebSockets provides no benefit.
• Connectivity Issues: WebSockets are often blocked or prematurely closed by corporate firewalls and some cloud load balancers.
• Higher Overhead: Adds protocol complexity (handshakes, ping/pong frames) that isn’t required for simple event streaming.

6. URL Structure: /{prefix}/{database}/{endpoint}

PgArachne routes all endpoints under a configurable prefix segment: /db/{database}/jsonrpc, /db/{database}/file, /db/{database}/sse, /db/{database}/mcp. The prefix defaults to db and can be changed via API_PREFIX.

Why this structure:

• Reverse proxy routing: A single PgArachne instance can serve multiple databases. A reverse proxy (Nginx, Caddy, Traefik) can route by prefix or database name without inspecting the request body, which is critical for load balancing and path-based routing rules.
• Horizontal scalability: With the database name in the URL path, you can run multiple PgArachne instances and route traffic to specific instances per database using standard proxy rules — no sticky sessions or body inspection required.
• Protocol multiplexing per database: Grouping /jsonrpc, /file, /sse, and /mcp under the same /{prefix}/{database}/ namespace makes it natural to apply per-database authentication, rate limiting, and access control at the proxy layer.
• Configurable prefix: Deployments that already use /api/ as a prefix in their infrastructure can set API_PREFIX=api to match their conventions.
• Observability: Log aggregators and metrics systems can group and filter traffic by database name directly from the URL without parsing JSON bodies.

Why not a flat structure like /api/{database}:

• Protocol ambiguity: A single flat endpoint cannot distinguish between JSON-RPC, SSE, and MCP traffic at the routing layer — that decision falls into application logic or header inspection.
• Harder to extend: Adding new protocols (e.g., GraphQL, gRPC-gateway) requires introducing new top-level paths anyway, so the structured namespace future-proofs the design.

7. MCP as a Translation Layer, Not a Database Protocol

PgArachne implements the Model Context Protocol (MCP) as a thin translation layer in the Go server. PostgreSQL functions are never aware of MCP — they remain simple jsonb → json functions.

Why translate MCP on the server:

• Zero changes to existing functions: Any function already exposed via JSON-RPC is instantly available as an MCP tool. No SQL changes, no redeployment of database objects.
• MCP is more than just tools: The protocol includes a discovery method (server/discover), per-request protocol version and capability metadata, notifications, and extensions (resources, prompts) beyond simple tool calls. These are protocol-level concerns that belong in Go, not in SQL functions.
• Security stays in one place: Authentication, role switching, and input validation are already implemented in Go. The MCP endpoint reuses this logic unchanged.
• Multiple protocols, one backend: The same PostgreSQL function can be called via JSON-RPC (from a regular client), MCP (from Claude Desktop or Cursor), or SSE (for event subscriptions). The database is protocol-agnostic.
• Simpler SQL: Processing MCP envelopes (server/discover, tools/list, notification handling) inside PostgreSQL functions would require parsing complex JSON structures in PL/pgSQL, making functions harder to write, test, and maintain.

Why not push MCP into the database:

• MCP transport validation doesn’t need a database: server/discover and the per-request protocol-version/header checks are pure protocol messages. Opening a database connection for them wastes resources and adds latency.
• SQL is the wrong tool for protocol logic: JSON-RPC 2.0 error codes, notification routing, and idempotency key management are middleware concerns, not data concerns.

8. OpenAPI Export: Virtual Paths, Not a New Protocol

GET /{prefix}/{database}/openapi.json (or .yaml) generates an OpenAPI 3.1 document describing every method the authenticated caller may execute. Real invocation still happens exclusively through the single POST /{prefix}/{database}/jsonrpc endpoint — the spec additionally lists each method under its own path, such as /{prefix}/{database}/rpc/api.hello_world, but that path is documentation only and does not exist as an HTTP route you can call directly.

Why virtual per-method paths:

• Tooling compatibility: Swagger UI, Postman, Insomnia, and most OpenAPI code generators expect one operation per paths entry. A single JSON-RPC path can’t otherwise represent N different method signatures in a way these tools understand.
• No backend changes required: Adding a real route per method would mean a second way to invoke every function, with its own auth, error-handling, and versioning surface to maintain in lockstep with JSON-RPC. Generating the paths purely from capabilities() keeps the protocol surface exactly as described in decision 4, while still satisfying tools that need per-operation schemas.
• Self-documenting: Each virtual operation’s description spells out the exact JSON-RPC request (method + params) needed to actually call it, so nothing is lost by not having a real route.

Why the spec is authenticated and role-filtered:

• Consistency with the rest of the API: Every other endpoint (JSON-RPC, MCP tools/list) only ever reveals methods the caller’s role may execute. An unauthenticated, unfiltered OpenAPI document would leak the existence and parameter shapes of functions a given caller can’t actually invoke.
• Same mechanism as everywhere else: The Go handler authenticates with the same Basic/JWT/API-token logic as /jsonrpc, then SET LOCAL ROLEs before generating the spec — pgarachne.generate_openapi_spec() is SECURITY INVOKER specifically so its internal call to capabilities() sees that role, the same way MCP tools/list already works.