1. 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.

2. 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.

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. 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.

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

PgArachne routes all endpoints under a configurable prefix segment: /db/{database}/jsonrpc, /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, /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. Legacy clients are supported via a 307 Temporary Redirect from the old paths.
• 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.

6. 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 an initialization handshake, ping, notifications, and potential future extensions (resources, prompts, sampling). 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 (initialize, 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 handshake doesn’t need a database: initialize and ping 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.