About & Architecture
PgArachne™ serves as a lightweight, high-performance web server that sits between your HTTP clients and the PostgreSQL database. It eliminates the need for traditional backend languages (like Python, Node.js, or PHP) by mapping HTTP requests directly to database functions.
Core Technical Capabilities
- JSON-RPC 2.0 Gateway: All API interactions follow the strict JSON-RPC 2.0 specification. There are no REST endpoints to design; you simply call SQL functions by name via POST requests.
- Native DB Authentication: No separate user tables. Users log in with their actual PostgreSQL credentials to receive a JWT. Alternatively, service accounts can use persistent API Tokens.
- Role Masquerading: PgArachne™ connects as a proxy user but executes every
request using
SET LOCAL ROLEto switch to the authenticated user’s identity. Row-Level Security (RLS) works automatically. - MCP (Model Context Protocol): A native MCP endpoint allows AI clients such as Claude Desktop, Cursor, and other MCP-compatible tools to discover your PostgreSQL functions as tools and call them directly — no custom glue code required. The same authentication and role security applies. Resources (tables/views) and prompt templates are also exposed via MCP.
- Real-time Notifications (SSE): Subscribe to PostgreSQL
NOTIFYchannels over SSE for live updates without polling. - Static File Server: PgArachne™ can serve static assets (HTML, JS, CSS), allowing you to host Single Page Applications (SPAs) or the Explorer tool directly.
This architecture dramatically simplifies the stack: Database ↔ PgArachne ↔ Frontend/AI Agent.
Endpoint structure: All database endpoints share a common prefix (default
db, configurable via API_PREFIX):POST /{prefix}/{database}/jsonrpc — JSON-RPC 2.0 gateway
GET /{prefix}/{database}/sse — PostgreSQL NOTIFY stream
POST /{prefix}/{database}/mcp — Model Context Protocol