Architecture

Pipeline

Every query follows a strict path that separates the AI layer from the database layer. The AI generates a structured JSON blueprint. The compiler translates that blueprint into parameterized SQL. The AI never sees or generates raw SQL.

User Text
   -> LLM Provider (OpenAI / OpenRouter)
   -> JSON Blueprint (Pydantic validated)
   -> SQL Compiler (parameterized, tenant-isolated)
   -> PostgreSQL Execution
   -> Redis Cache
   -> Cached Response

Security model

• Request ID middleware, X-Request-ID on every request for end-to-end tracing.
• Parameterized SQL only, all values use %s placeholders. No string interpolation.
• Schema whitelist, ALLOWED_SCHEMA defines which tables and columns can be queried.
• JOIN graph, RELATIONSHIP_GRAPH defines allowed foreign key paths.
• Aggregation whitelist, ALLOWED_AGGREGATIONS controls permitted aggregate functions.
• Query timeouts, configurable per-request, default 30 seconds.
• Input validation, Pydantic models constrain AI output to safe schemas.
• Read-only DB role, predicate_reader cannot write, only INSERT into audit_logs.
• Security headers, X-Frame-Options DENY, HSTS, CSP, nosniff, Referrer-Policy, Permissions-Policy.
• CSRF protection, signed cookie with Secure flag for browser requests.
• Rate limiting, Redis fixed-window rate limiting per tenant, configurable by tier.
• Audit log, every prompt, compiled query, and result is logged to PostgreSQL for compliance.

Compiler design

The SQL compiler is a pure function: JSON blueprint in, parameterized SQL out. No side effects, no shared state, no I/O. This makes it trivially testable and allows it to scale linearly across CPU cores.

Benchmarked at 3.6 microseconds median latency and 174k queries/sec on a single process, scaling to 641k queries/sec across 4 cores with 92% efficiency.

Adaptability

To use predicate with a different database schema, modify only two definitions in app/compiler/sql_builder.py:

• ALLOWED_SCHEMA, your target tables and permitted columns.
• RELATIONSHIP_GRAPH, your foreign key definitions for automatic JOIN resolution.

The rest of the pipeline (caching, auth, rate limiting, exports) adapts automatically.

Observability

• Structured JSON logging with X-Request-ID correlation on every request.
• TraceContext spans for compile → validate → execute → cache phases.
• Prometheus metrics at /metrics: compile duration by provider, cache hits, query failures, query efficiency score.
• Health endpoints: /health checks db + redis, /ready for readiness probes.