QAIL Documentation

The AST-Native Query Compiler with Built-in Row-Level Security

QAIL compiles typed query ASTs directly to database wire protocols. No application-level SQL string interpolation on the AST path. Built-in multi-tenant data isolation via PostgreSQL RLS context setup.

Product Map

For a deeper orientation, read the Platform Map. It explains which crate owns each safety boundary and which surface to choose for driver, gateway, schema, workflow, or vector workloads.

Latest Updates (June 2026)

• QAIL is now on the v1.3.3 stable line across the Rust workspace crates and CLI.
• The public API is the AST/DSL path: Qail::get/add/set/del, typed expressions, relation helpers, RLS contexts, and driver/pool execution.
• Compatibility aliases that hid fallible behavior were removed: use with_rls(&ctx)? and join_on(...)? directly.
• Legacy raw SQL builder APIs remain out of the normal runtime path; use AST-native commands and session AST helpers instead.
• PostgreSQL cancel-key APIs are bytes-native, matching protocol 3.0 and 3.2 behavior.
• Native vertical access policy is now first-class through qail_core::access and optional [access] config for operation and column permissions alongside PostgreSQL RLS.
• PostgreSQL prepared statement caching, NOTIFY flushing, MERGE/source-query access checks, and strict migration verification were hardened in the latest audit pass.
• Gateway numeric preservation, Qdrant vector encoding, workflow branch cursors, and SDK route-segment encoding now have focused regression coverage.
• Migration docs use the expand/backfill/contract apply model instead of presenting up/down as the primary workflow.

Read Access Policy for the vertical permission model and Workflows for Flow Engine / Flow Ledger production semantics.

Philosophy: AST = Meaning

If a database doesn’t let us encode semantic intent, we don’t fake it.

QAIL compiles typed query ASTs directly to database wire protocols with typed value encoding.

SQL String vs SQL Bytes

• SQL string: text query assembled in application code.
• SQL bytes: PostgreSQL protocol message bytes (Parse/Bind/Execute and results) plus encoded bind values.
• QAIL guarantee: AST flow removes app-side SQL interpolation as an injection surface.
• PostgreSQL behavior: server parse/plan/execute still applies normally.

Legacy Syntax Notice

Some search engines still surface old QAIL pages showing symbolic forms such as get::users•@id@email@role[active=true][lim=10] or macro snippets such as qail!("get::users:'id'email [ 'active == true ]").

Those pages are from historical pre-1.0 releases and are not the current API guidance.

Current QAIL 1.3.3 application code should use the native AST/DSL path:

#![allow(unused)]
fn main() {
let query = Qail::get("users")
    .columns(["id", "email", "role"])
    .eq("active", true)
    .limit(10);

let rows = driver.fetch_all(&query).await?;
}

Supported Databases

Redis support (qail-redis) was removed in v0.20.0.

❌ Not Supported

• Database protocols outside PostgreSQL and Qdrant are not part of the supported surface.

Quick Example

#![allow(unused)]
fn main() {
use qail_core::{Qail, Operator, SortOrder};

// Build a query with the AST builder
let cmd = Qail::get("users")
    .columns(["id", "email", "name"])
    .filter("active", Operator::Eq, true)
    .order_by("created_at", SortOrder::Desc)
    .limit(10);

// Execute with qail-pg driver
let mut driver = PgDriver::connect("localhost", 5432, "user", "db").await?;
let rows = driver.fetch_all(&cmd).await?;
}

Current Status (Production Ready, Actively Hardened)

Note: QAIL’s AST-native design eliminates app-side SQL interpolation on the AST path. Query plan caching (prepare(), pipeline_prepared_fast()) is a PostgreSQL performance optimization, not the primary security boundary.

Why Some SQL Features Don’t Exist in QAIL

QAIL is AST-first, not SQL-string-first. Many traditional SQL “security features” exist to mitigate string-construction risks that AST pipelines avoid by design:

The AST Guarantee

#![allow(unused)]
fn main() {
// SQL String (vulnerable):
let sql = format!("SELECT * FROM users WHERE id = {}", user_input);

// QAIL AST (structure is not interpolated from user text):
Qail::get("users").filter("id", Operator::Eq, user_input)
// user_input becomes Value::Int(123) or Value::Text("...") 
// — never interpolated into a string
}

Getting Help

• GitHub Repository
• Issue Tracker

Contributing & Support

We welcome issue reports on GitHub! Please provide detailed descriptions to help us reproduce and fix the problem. We aim to address critical issues within 1-5 business days.

Note

Stable Release: QAIL is now on the 1.x stable line. Breaking changes should be treated as release-line decisions and documented in CHANGELOG.md.

Platform Map

QAIL is a small platform made of separate crates. The important split is not the crate names; it is which layer owns which safety boundary.

Layers

Choose The Smallest Surface

Use qail-core + qail-pg when your app already owns routing, auth, and business logic. This is the normal Rust driver mode:

#![allow(unused)]
fn main() {
use qail_core::prelude::*;
use qail_pg::PgDriver;

let ctx = RlsContext::tenant(tenant_id).with_user(user_id);
let cmd = Qail::get("orders")
    .columns(["id", "status", "total"])
    .eq("status", "paid")
    .with_rls(&ctx)?;

let rows = driver.fetch_all(&cmd).await?;
}

Use qail-gateway when the database schema should become a controlled API surface. The gateway adds HTTP/WebSocket routing, JWT authentication, operation/column access policy, request limits, EXPLAIN guardrails, and tenant-boundary checks around the same AST path.

Use qail when changing schema. The CLI is the state/schema tool: it can pull a live PostgreSQL schema, validate schema.qail, compare drift, and apply explicit expand/backfill/contract migrations.

Use qail-workflow when business flows need durable pause/resume semantics. Use qail-workflow-postgres when those flows need production storage for leases, idempotency, side-effect replay, and timeout due-row discovery.

Use qail-qdrant only for vector workloads. It is not a relational database adapter; it shares the tenant-aware AST model where that maps cleanly to Qdrant filters.

Safety Ownership

QAIL does not claim that one crate magically owns every safety property. The boundaries are explicit:

Supported Backends

PostgreSQL is the primary SQL backend. The AST, migration model, RLS context, gateway, and workflow ledger are designed around PostgreSQL semantics.

Qdrant is supported for vector search through qail-qdrant.

Other database protocols are not part of the supported runtime surface. QAIL does not fake support for engines whose semantics do not map cleanly to its AST model.

Naming

The crate names are intentionally literal. The docs use product names to make the architecture easier to discuss:

• AST Kernel: qail-core
• Postgres Driver: qail-pg
• Access Gateway: qail-gateway
• SchemaOps CLI: qail
• Flow Engine: qail-workflow
• Flow Ledger: qail-workflow-postgres
• Vector Bridge: qail-qdrant

When debugging production behavior, start from the product name and then inspect the owning crate.

Installation

Rust (Recommended)

Add QAIL to your Cargo.toml:

[dependencies]
qail-core = "1.3.3"    # AST and builder
qail-pg = "1.3.3"      # PostgreSQL driver

CLI

Install the QAIL command-line tool:

cargo install qail

TypeScript SDK

npm install @qail/client

Swift SDK

Use the source package in this repository:

• sdk/swift/Package.swift

Kotlin SDK

Use the Gradle module in this repository:

• sdk/kotlin/build.gradle.kts

Deferred Bindings

• Node.js native binding: deferred
• WASM packaging: deferred

Verify Installation

qail --version
# qail 1.3.3

Quick Start

Connect to PostgreSQL

use qail_pg::PgDriver;

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    // Connect with password (SCRAM-SHA-256)
    let mut driver = PgDriver::connect_with_password(
        "localhost", 5432, "user", "database", "password"
    ).await?;

    // Or with SSL/TLS
    let mut driver = PgDriver::connect(
        "localhost", 5432, "user", "database"
    ).await?;

    Ok(())
}

Execute Your First Query

#![allow(unused)]
fn main() {
use qail_core::Qail;
use qail_core::ast::Operator;

// Build a SELECT query
let cmd = Qail::get("users")
    .columns(["id", "email"])
    .filter("active", Operator::Eq, true)
    .limit(10);

// Execute
let rows = driver.fetch_all(&cmd).await?;

for row in rows {
    let id: i32 = row.get("id")?;
    let email: String = row.get("email")?;
    println!("{}: {}", id, email);
}
}

Use Connection Pooling

#![allow(unused)]
fn main() {
use qail_pg::driver::{PgPool, PoolConfig};
use qail_core::Qail;

let config = PoolConfig::new("localhost", 5432, "user", "db")
    .password("secret")
    .max_connections(20);

let pool = PgPool::connect(config).await?;

// Acquire connection from pool
let mut conn = pool.acquire().await?;
let probe = Qail::get("users").columns(["id"]).limit(1);
let _rows = conn.fetch_all(&probe).await?;
// Connection automatically returned when dropped
}

Run Migrations

# Pull current schema from database
qail pull postgres://user:pass@localhost/db > schema.qail

# Create a new version with changes
# (edit schema.qail manually)

# Diff and apply through the safe phased model
qail diff old.qail new.qail
qail migrate apply --phase expand
qail migrate apply --phase backfill --backfill-chunk-size 10000
qail migrate apply --phase contract --codebase ./src

Schema Layout Examples (Single vs Modular)

Use the built-in samples in this repository:

• Single file: examples/schema/single/schema.qail
• Modular directory: examples/schema/modular/schema/

Try them:

qail check examples/schema/single/schema.qail
qail check examples/schema/modular/schema
qail check examples/schema/modular/schema.qail

The modular sample includes schema/_order.qail with strict manifest mode:

• -- qail: strict-manifest
• every discovered module must be listed (directly or through listed directories)

Qail Gateway

Qail Gateway is an auto-REST API server that turns your PostgreSQL database into a full-featured API — with zero backend code. Point it at a database, get instant CRUD, real-time subscriptions, and enterprise security.

Full-featured data API — without GraphQL complexity. Binary AST protocol instead of string-based queries.

Quick Start

# Set your database URL and run
DATABASE_URL=postgres://user:pass@localhost:5432/mydb cargo run --release

The gateway auto-discovers all tables and exposes them as REST endpoints:

GET    /api/{table}              # List (with filters, sort, pagination)
GET    /api/{table}/:id          # Get by ID
POST   /api/{table}              # Create
PATCH  /api/{table}/:id          # Update
DELETE /api/{table}/:id          # Delete
GET    /api/{table}/_explain     # EXPLAIN ANALYZE
GET    /api/{table}/aggregate    # Aggregations
GET    /api/{table}/_aggregate   # Aggregations (compat alias)
GET    /api/{table}/:id/{child}  # Nested resources (FK-based)
POST   /api/rpc/{function}       # Function RPC with JSON args

Query API

Filtering

All filter operators are supported as query parameters:

GET /api/orders?status=paid                              # Exact match
GET /api/orders?total=gt.100                             # Greater than
GET /api/orders?total=gte.50&total=lte.200               # Range
GET /api/orders?status=in.(paid,shipped)                 # IN list
GET /api/orders?name=like.*ferry*                        # Pattern match
GET /api/orders?name=ilike.*FERRY*                       # Case-insensitive
GET /api/orders?notes=is_null                            # NULL check
GET /api/orders?status=ne.cancelled                      # Not equal
GET /api/orders?tags=contains.premium                    # Array contains

Full-Text Search

GET /api/products?search=ferry+bali                      # Search all text columns
GET /api/products?search=ferry&search_columns=name,desc  # Search specific columns

Uses PostgreSQL’s to_tsvector / websearch_to_tsquery — supports natural language queries.

Sorting

GET /api/orders?sort=created_at                          # Ascending (default)
GET /api/orders?sort=-total                              # Descending (prefix -)
GET /api/orders?sort=-status,created_at                  # Multi-column

Pagination

GET /api/orders?limit=25&offset=50                       # Offset-based
GET /api/orders?limit=25&cursor=eyJpZCI6...              # Cursor-based

Distinct

GET /api/orders?distinct=status                          # Distinct values

Relationships & Expansion

FK-Based JOIN Expansion

Automatically resolves foreign key relationships with ?expand=:

GET /api/orders?expand=users                              # Inline user object
GET /api/orders?expand=users,operators                    # Multiple relations
GET /api/orders?expand=nested:users                       # Nested FK expansion

Response includes the full related object inline — no N+1 queries. The gateway performs a server-side JOIN.

expand= is for forward (many-to-one / one-to-one) relations. For reverse one-to-many expansion, use nested: to avoid parent-row duplication.

Nested Resource Routes

Access child resources through parent:

GET /api/operators/:id/orders                             # All orders for operator
GET /api/users/:id/bookings?status=confirmed              # Filtered child resources

Mutations

Create

# Single insert
curl -X POST /api/orders -d '{"user_id": "...", "total": 100}'

# Batch insert
curl -X POST /api/orders -d '[{"total": 100}, {"total": 200}]'

Update

curl -X PATCH /api/orders/:id -d '{"status": "shipped"}'

Upsert

curl -X POST /api/orders -H "X-Upsert: true" \
  -H "X-On-Conflict: order_number" \
  -d '{"order_number": "ORD-001", "total": 150}'

Delete

curl -X DELETE /api/orders/:id

Returning Clause

All mutations support ?returning=id,status to get back specific columns after the operation.

Aggregations

GET /api/orders/aggregate?fn=count                        # COUNT(*)
GET /api/orders/aggregate?fn=sum&column=total             # SUM(total)
GET /api/orders/aggregate?fn=avg&column=total             # AVG
GET /api/orders/aggregate?fn=min&column=created_at        # MIN
GET /api/orders/aggregate?fn=max&column=total             # MAX
GET /api/orders/aggregate?fn=count&status=paid            # Filtered aggregation
GET /api/orders/_aggregate?fn=count                        # Alias (compat)

Function RPC

POST /api/rpc/search_orders
Body: { "tenant_id": "acme", "limit": 25 }               # Named args

POST /api/rpc/rebuild_index
Body: ["orders", true]                                     # Positional args

Authentication & Security

JWT Authentication

The gateway validates Authorization: Bearer <jwt> tokens and extracts tenant context for RLS. Set JWT_SECRET in the environment:

export JWT_SECRET="your-hs256-secret"

Header-Based Dev Auth

For development, pass claims directly as headers:

curl \
  -H "x-user-id: user-123" \
  -H "x-user-role: operator" \
  -H "x-tenant-id: tenant-abc" \
  /api/orders

Row-Level Security (RLS)

Every query is scoped to the authenticated tenant via PostgreSQL’s native RLS. The gateway sets transaction-local session variables (app.current_tenant_id, app.current_user_id, app.current_agent_id, app.is_super_admin) before each query.

Native Access Policy

Fine-grained access control per table, operation, role, scope, and column:

[access]
enabled = true
path = "access-policy.toml"

# access-policy.toml
default_decision = "deny"

[tables.orders]
operations = ["read", "update"]
read_columns = { only = ["id", "status", "total", "created_at"] }
write_columns = { only = ["status"] }
require_any_role = ["operator", "administrator"]
require_scopes = ["orders:read"]

The gateway still supports the older YAML route policy engine for compatibility, but new deployments should prefer native access policy because it checks the QAIL AST directly.

tenant_id is the primary runtime scope. A legacy agent_id claim is only a secondary scope when tenant_id is present.

Query Allow-Listing

Lock down which queries can run in production:

# qail.toml
[gateway]
allow_list_path = "allow_list.txt"

Real-Time

WebSocket Subscriptions

Subscribe to table changes via WebSocket (PostgreSQL LISTEN/NOTIFY):

const ws = new WebSocket('ws://localhost:8080/ws');
ws.send(JSON.stringify({
  type: 'subscribe',
  channel: 'qail_table_orders'
}));

ws.onmessage = (event) => {
  const msg = JSON.parse(event.data);
  // type: "subscribed" | "notification" | "error"
  console.log(msg);
};

Live Queries

Auto-refresh query results when underlying data changes:

ws.send(JSON.stringify({
  type: 'live_query',
  qail: "get orders where status = 'paid' order by created_at desc limit 10",
  table: 'orders',
  interval_ms: 2000
}));

Event Triggers

Fire webhooks on database mutations:

- name: order_created
  table: orders
  operations: [create]
  webhook_url: "https://api.example.com/hooks/order-created"
  retry_count: 3
  headers:
    X-Secret: webhook-secret-key
- name: order_updated
  table: orders
  operations: [update]
  webhook_url: "https://api.example.com/hooks/order-updated"

Performance

Response Streaming

For large datasets, stream results as NDJSON:

GET /api/large_table?stream=true

Each row is sent as a newline-delimited JSON object — no buffering the entire result set.

EXPLAIN Endpoint

Inspect query plans without touching production:

GET /api/orders/_explain?status=paid&expand=users

Returns the PostgreSQL EXPLAIN ANALYZE output for the generated query.

Prepared Statement Caching

The gateway caches prepared statements per query shape, eliminating repeated parse overhead.

Query Cache

LRU cache with TTL and table-level invalidation. Identical queries hit cache instead of the database.

Observability

Prometheus Metrics

GET /metrics

Exposes request counts, latencies, error rates, and connection pool stats.

Request Tracing

Every response includes:

• x-request-id — unique request identifier
• x-response-time — duration in milliseconds

Health Check

GET /health

Schema Introspection

GET /api/_schema         # Full schema with tables, columns, types, FKs
GET /api/_openapi        # Auto-generated OpenAPI 3.0 spec

Benchmark: Gateway vs GraphQL (Current)

The gateway’s ?expand= executes server-side JOINs over HTTP (single-query shape).

On loopback, DataLoader and single-query paths are very close. As RTT increases, one-query gateway/AST paths pull ahead because they pay fewer round trips.

?expand= resolves expansions through JOINs in one SQL execution shape, avoiding resolver-level fan-out.

Architecture & Invariants

This page describes the gateway’s internal architecture — the request lifecycle, security boundaries, and the invariants that make multi-tenant isolation structural rather than behavioral.

Read time: ~10 minutes.

Recent hardening:

• Policy evaluation now avoids premature deny outcomes when later matching policies allow the operation.
• Handler execution paths consistently run command optimization before DB execution.
• Native access policy now covers operation and column checks across gateway execution paths.

Request Lifecycle

Every HTTP request flows through a fixed, ordered pipeline:

 Client Request
       │
 ┌─────▼─────────────────────────────────────────────────────┐
 │  1. CORS + Security Headers                               │
 │     X-Content-Type-Options: nosniff                       │
 │     X-Frame-Options: DENY                                 │
 │     Body size limit: 2 MiB                                │
 ├───────────────────────────────────────────────────────────┤
 │  2. Rate Limiter                                          │
 │     Token bucket per IP (configurable burst + rate)       │
 │     → 429 Too Many Requests on exhaustion                 │
 ├───────────────────────────────────────────────────────────┤
 │  3. Authentication                                        │
 │     JWT validation (HS256) -> extract tenant_id,           │
 │     user_id, role/scope claims                            │
 │     Dev headers only when QAIL_DEV_MODE=true              │
 ├───────────────────────────────────────────────────────────┤
 │  4. Policy Checks                                         │
 │     Native TOML/JSON access policy for operation/columns  │
 │     Legacy gateway policy compatibility when configured   │
 │     → 403 Forbidden on policy violation                   │
 ├───────────────────────────────────────────────────────────┤
 │  5. Tenant Concurrency Gate                               │
 │     Per-tenant semaphore (configurable permits)           │
 │     Prevents one tenant from consuming all connections    │
 │     → 429 on tenant saturation                            │
 ├───────────────────────────────────────────────────────────┤
 │  6. Connection Acquisition                                │
 │     pool.acquire_with_rls(RlsContext) or                  │
 │     pool.acquire_with_rls_timeout(RlsContext, timeout)    │
 │     Sets PostgreSQL GUCs:                                 │
 │       set_config('app.current_tenant_id', '...', true)    │
 │       set_config('app.current_user_id', '...', true)      │
 │       set_config('app.current_agent_id', '...', true)     │
 │       set_config('app.is_super_admin', '...', true)       │
 ├───────────────────────────────────────────────────────────┤
 │  7. EXPLAIN Pre-Check (reads only)                        │
 │     EXPLAIN on generated SQL before execution             │
 │     Rejects if cost > explain_max_cost                    │
 │              or rows > explain_max_rows                   │
 │     → QUERY_TOO_EXPENSIVE with structured JSON detail     │
 ├───────────────────────────────────────────────────────────┤
 │  8. Query Execution                                       │
 │     AST → SQL transpilation → prepared statement cache    │
 │     PostgreSQL RLS policies filter rows invisibly         │
 │     Result row cap: max_result_rows (configurable)        │
 ├───────────────────────────────────────────────────────────┤
 │  9. Connection Release                                    │
 │     COMMIT → resets txn-local RLS + statement_timeout      │
 │     Prepared statement caches remain hot for reuse         │
 │     Connection returned to pool in clean state            │
 └───────────────────────────────────────────────────────────┘
       │
 JSON Response + X-Request-Id + X-Response-Time

Connection Safety Model

The connection pool enforces a strict lifecycle:

Acquisition

Four public methods:

Release

Every connection release executes:

COMMIT;   -- Ends txn opened by RLS setup; resets transaction-local GUCs

With transaction-local settings (set_config(..., true) + SET LOCAL), this resets:

• RLS context GUCs
• statement_timeout

Prepared statement caches are intentionally preserved for performance while tenant context is reset on each release.

Why acquire_raw() Is Restricted

acquire_raw() returns a connection with no RLS context. If used for tenant queries, it would bypass row-level security entirely.

Every internal call site must include a // SAFETY: comment explaining why raw acquisition is justified (typically: “RLS context is set immediately on the next line”). This convention is enforced via CI:

# Must return empty — every acquire_raw() must have a SAFETY comment
grep -rn "acquire_raw" pg/src/ | grep -v "// SAFETY:"

Core Invariants

These are the properties the system guarantees. If any are violated, it’s a bug.

1. Fail-Closed RLS

Every tenant query runs on a connection where RLS GUCs are set before any SQL executes. If GUC setup fails, the connection is not used — the error propagates to the caller.

There is no code path where a tenant query runs on a connection with another tenant’s context.

2. Cost-Bounded Execution

Read queries are gated by EXPLAIN analysis before execution. If the estimated cost or row count exceeds configured limits, the query is rejected before touching the database.

Limits are configurable per role via [gateway.role_overrides.<role>] in qail.toml, allowing analytics roles to run heavier queries without weakening default safety.

3. Tenant Isolation on Connection Reuse

When a connection is released and re-acquired by a different tenant:

1. COMMIT resets transaction-local RLS state and timeout
2. New RLS context is set for the new tenant
3. Query executes under the new tenant context

The integration test test_pool_connection_recycling_isolation verifies this with a pool of size 1, forcing the same physical connection to serve two different tenants sequentially.

4. Bounded Resource Consumption

5. Graceful Shutdown

On SIGTERM or Ctrl+C:

1. Stop accepting new connections
2. Wait for in-flight requests to complete
3. Drain the connection pool
4. Exit cleanly

No request is silently dropped. No connection is leaked.

Component Map

GatewayState (shared Arc across all handlers)
├── pool: PgPool                    — Connection pool with RLS-aware acquisition
├── config: GatewayConfig           — All configuration (qail.toml + env overrides)
├── policy_engine: PolicyEngine     — legacy gateway per-table/per-role policies
├── access_policy: Option<AccessPolicy> — native operation/column policy
├── schema: SchemaRegistry          — Auto-discovered table/column/FK metadata
├── cache: QueryCache               — moka-backed LRU with table-level invalidation
├── rate_limiter: RateLimiter       — Token bucket rate limiting
├── explain_cache: ExplainCache     — Cached EXPLAIN results per query shape
├── explain_config: ExplainConfig   — Cost/row thresholds for EXPLAIN pre-check
├── tenant_semaphore: TenantSemaphore — Per-tenant concurrency limiter
├── event_engine: EventTriggerEngine  — Webhook triggers on mutations
└── user_tenant_map: HashMap        — JWT user_id -> tenant_id resolution cache

Where to Hook Observability

REST API Reference

The gateway auto-discovers all tables and exposes them as REST endpoints under /api/.

Endpoints

Filtering

All filter operators are supported as query parameters. Both key-style (column.op=value) and value-style (column=op.value) are accepted:

GET /api/orders?status=paid                              # Exact match
GET /api/orders?status.eq=paid                           # Exact match (key-style)
GET /api/orders?total=gt.100                             # Greater than
GET /api/orders?total.gte=50&total.lte=200               # Range (key-style)
GET /api/orders?total=gte.50&total=lte.200               # Range
GET /api/orders?status=in.(paid,shipped)                 # IN list
GET /api/orders?status.in=paid,shipped                   # IN list (key-style)
GET /api/orders?name=like.*ferry*                        # Pattern match
GET /api/orders?name=ilike.*FERRY*                       # Case-insensitive
GET /api/orders?notes=is_null                            # NULL check
GET /api/orders?notes.is_null=true                       # NULL check (key-style)
GET /api/orders?status=ne.cancelled                      # Not equal
GET /api/orders?status.ne=cancelled                      # Not equal (key-style)
GET /api/orders?tags=contains.premium                    # Array contains

Operator Reference

Full-Text Search

GET /api/products?search=ferry+bali                      # Search all text columns
GET /api/products?search=ferry&search_columns=name,desc  # Search specific columns

Uses PostgreSQL’s to_tsvector / websearch_to_tsquery — supports natural language queries.

Sorting

GET /api/orders?sort=created_at                          # Ascending (default)
GET /api/orders?sort=-total                              # Descending (prefix -)
GET /api/orders?sort=-status,created_at                  # Multi-column
GET /api/orders?sort=total:desc,created_at:asc           # Multi-column (explicit)

Pagination

GET /api/orders?limit=25&offset=50                       # Offset-based
GET /api/orders?limit=25&cursor=eyJpZCI6...              # Cursor-based

Column Selection & Distinct

GET /api/orders?select=id,status,total                   # Return specific columns
GET /api/orders?distinct=status                           # Distinct values

Relationships & Expansion

FK-Based JOIN Expansion

Automatically resolves foreign key relationships with ?expand=:

GET /api/orders?expand=users                              # Inline user object
GET /api/orders?expand=users,operators                    # Multiple relations
GET /api/orders?expand=nested:users                       # Nested FK expansion

Response includes the full related object inline — no N+1 queries. The gateway performs a server-side JOIN.

expand= is for forward (many-to-one / one-to-one) relations. For reverse one-to-many expansion, use nested: to avoid parent-row duplication.

Nested Resource Routes

Access child resources through parent:

GET /api/operators/:id/orders                             # All orders for operator
GET /api/users/:id/bookings?status=confirmed              # Filtered child resources

Mutations

Create

# Single insert
curl -X POST /api/orders -d '{"user_id": "...", "total": 100}'

# Batch insert
curl -X POST /api/orders -d '[{"total": 100}, {"total": 200}]'

Update

curl -X PATCH /api/orders/:id -d '{"status": "shipped"}'

Upsert

curl -X POST /api/orders -H "X-Upsert: true" \
  -H "X-On-Conflict: order_number" \
  -d '{"order_number": "ORD-001", "total": 150}'

Delete

curl -X DELETE /api/orders/:id

Returning Clause

All mutations support ?returning=id,status to get back specific columns after the operation.

Aggregations

GET /api/orders/aggregate?fn=count                        # COUNT(*)
GET /api/orders/aggregate?fn=sum&column=total             # SUM(total)
GET /api/orders/aggregate?fn=avg&column=total             # AVG
GET /api/orders/aggregate?fn=min&column=created_at        # MIN
GET /api/orders/aggregate?fn=max&column=total             # MAX
GET /api/orders/aggregate?fn=count&status=paid            # Filtered aggregation
GET /api/orders/_aggregate?fn=count                        # Alias (compat)

Supported functions: count, sum, avg, min, max.

RPC Functions

Call PostgreSQL functions directly:

# Named arguments (object body)
curl -X POST /api/rpc/search_orders \
  -d '{"tenant_id":"acme","limit":25}'

# Positional arguments (array body)
curl -X POST /api/rpc/rebuild_index \
  -d '["orders", true]'

Accepted body formats:

• JSON object: named args (arg => value)
• JSON array: positional args
• scalar/null: single positional arg
• empty body: no args

Authentication & Security

The gateway provides multiple layers of security: JWT authentication, PostgreSQL Row-Level Security integration, native operation/column access policy, gateway route policy compatibility, and query allow-listing.

Note: Webhook-based authentication has been removed. JWT (JWT_SECRET) is the only supported authentication mechanism. If your reverse proxy needs to authenticate, forward user identity within the JWT — not via custom headers.

JWT Authentication

The gateway validates JWT tokens and extracts tenant context for RLS.

Set JWT_SECRET as an environment variable:

export JWT_SECRET="your-hs256-secret"

The extracted claims (tenant_id, user_id, role) are set as PostgreSQL session variables before every query, enabling native RLS enforcement:

set_config('app.current_tenant_id', '<from JWT>', true);
set_config('app.current_user_id', '<from JWT>', true);
set_config('app.current_agent_id', '<from JWT agent_id claim>', true);
set_config('app.is_super_admin', 'false', true);

Header-Based Dev Auth

For development, pass claims directly as headers:

curl \
  -H "x-user-id: user-123" \
  -H "x-user-role: operator" \
  -H "x-tenant-id: tenant-abc" \
  /api/orders

Warning: Header-based auth is only active when QAIL_DEV_MODE=true is set. This works independently of JWT_SECRET — you can have both JWT and dev-mode headers active simultaneously. If a Bearer token is provided but fails validation, the request is denied (not degraded to dev-mode or anonymous). Startup is fail-closed in dev mode: gateway boot is rejected unless bind address is localhost and JWT_SECRET is set.

Row-Level Security (RLS)

Every query is scoped to the authenticated tenant through PostgreSQL’s native RLS. The gateway sets transaction-local session variables before each query:

-- Automatically executed before every query:
set_config('app.current_tenant_id', '<from JWT>', true);
set_config('app.current_user_id', '<from JWT>', true);
set_config('app.current_agent_id', '<from JWT agent_id claim>', true);
set_config('app.is_super_admin', 'false', true);

Your PostgreSQL RLS policies reference these variables:

CREATE POLICY tenant_isolation ON orders
  FOR ALL
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

The gateway + RLS combination provides database-level multi-tenancy when the database policies are enabled and the app role cannot bypass RLS.

Important: Your application database role must be a non-superuser with NOBYPASSRLS. Superusers bypass RLS even with FORCE ROW LEVEL SECURITY.

Native Access Policy

Fine-grained operation and column control is handled by the native access policy in qail_core::access. Enable it in qail.toml:

[access]
enabled = true
path = "access-policy.toml"

Example access-policy.toml:

default_decision = "deny"

[tables.orders]
operations = ["read", "update"]
read_columns = { only = ["id", "status", "total", "created_at"] }
write_columns = { only = ["status"] }
returning_columns = { only = ["id", "status"] }
require_any_role = ["operator", "administrator"]
require_scopes = ["orders:read"]

[tables.users]
operations = ["read"]
read_columns = { only = ["id", "email", "display_name"] }
require_any_role = ["administrator"]
require_scopes = ["users:read"]

Policy files may be TOML or JSON. They are checked against the QAIL AST before execution. See Access Policy for full semantics, including MERGE/source-query handling and fail-closed expression rules.

Migration note: operator_id JWT claims are preserved in extra claims but are not mapped into tenant_id. Use a tenant_id claim for tenant scope.

Gateway Policy Compatibility

The gateway still supports the older YAML route policy engine through [gateway].policy / policy_path for compatibility with existing deployments:

policies:
  - name: orders_operator_read
    table: orders
    role: operator
    operations: [read]
    filter: "tenant_id = $tenant_id"
    allowed_columns: ["id", "status", "total", "created_at"]

For new deployments, prefer native [access] policy for vertical permissions because it lives in qail-core and checks the AST command directly.

Column Permissions

Control which columns each role can read or write:

• Whitelist: Only the listed columns are returned/allowed.
• Blacklist: All columns except the listed ones are returned/allowed.

Policies are applied at the AST level before the query reaches PostgreSQL — denied columns never leave the database.

Operation Permissions

Control CRUD operations per role per table:

Query Allow-Listing

Lock down which queries can run in production:

# qail.toml
[gateway]
allow_list_path = "allow_list.txt"

When enabled, any query pattern not in the allow-list is rejected with 403 Forbidden. This provides defense-in-depth: even if auth is bypassed, only pre-approved query shapes can execute.

RPC Contract Hardening

Harden /api/rpc/{function} with strict function naming and signature checks:

[gateway]
rpc_require_schema_qualified = true
rpc_allowlist_path = "rpc_allowlist.txt"
rpc_signature_check = true

rpc_allowlist_path format:

# One function per line (case-insensitive)
api.search_orders
public.health_check

What each control does:

When rpc_signature_check=true, the gateway also uses a parser-only PostgreSQL probe (PREPARE ...; DEALLOCATE) to align overload resolution with PostgreSQL itself before execution.

RPC DevEx endpoint:

• GET /api/_rpc/contracts returns callable function signatures (identity_args, defaults, variadic, return type) for typed client generation.

RPC result format control:

• Optional header x-qail-result-format: binary enables binary column format on RPC responses.
• Default is x-qail-result-format: text.

Database Auth/TLS Hardening

Gateway database transport/auth policy can be configured through database_url query parameters:

[gateway]
database_url = "postgresql://app:secret@db.internal:5432/app\
?sslmode=require\
&sslrootcert=/etc/qail/ca.pem\
&channel_binding=require\
&auth_mode=scram_only"

Supported parameters:

If sslcert or sslkey is provided, both must be set.

If gss_provider=linux_krb5 is set, build the gateway with feature enterprise-gssapi on Linux.

Startup runs Kerberos preflight checks and emits clear diagnostics for common misconfiguration (missing explicit credential cache/keytab paths, invalid KRB5_CONFIG, etc). The gateway does not perform enterprise SSO login or ticket acquisition. It uses the configured provider to consume Kerberos/GSS tokens from your existing OS credential cache, keytab, sidecar, or identity infrastructure, then applies the configured database auth policy fail-closed.

Example:

[gateway]
database_url = "postgresql://app@db.internal:5432/app\
?sslmode=require\
&auth_mode=gssapi_only\
&gss_provider=linux_krb5\
&gss_service=postgres\
&gss_connect_retries=3\
&gss_retry_base_ms=200\
&gss_circuit_threshold=8\
&gss_circuit_window_ms=30000\
&gss_circuit_cooldown_ms=15000"

Security Summary

Internal Endpoint Protection (M4)

The /metrics and /health/internal endpoints expose operational details. Protect them in production:

# qail.toml
[gateway]
admin_token = "your-secret-admin-token"

When set, both endpoints require Authorization: Bearer <admin_token>. Without the token, they return 401 Unauthorized.

Alternatively, restrict access via network policy (firewall rules, reverse proxy).

Real-Time & Events

The gateway supports real-time data through WebSocket subscriptions, live queries, and event triggers with webhook delivery.

WebSocket Subscriptions

Subscribe to table changes via WebSocket (backed by PostgreSQL LISTEN/NOTIFY):

const ws = new WebSocket('ws://localhost:8080/ws');
ws.send(JSON.stringify({
  type: 'subscribe',
  channel: 'qail_table_orders'
}));

ws.onmessage = (event) => {
  const msg = JSON.parse(event.data);
  // type: "subscribed" | "notification" | "error"
  console.log(msg);
};

Subscriptions are scoped by your RLS policies — each client only receives events for rows they are authorized to see.

Live Queries

Auto-refresh query results when underlying data changes:

ws.send(JSON.stringify({
  type: 'live_query',
  qail: "get orders where status = 'paid' order by created_at desc limit 10",
  table: 'orders',
  interval_ms: 2000
}));

The gateway re-executes the query at the specified interval and pushes updated results only when data has changed.

Event Triggers

Fire webhooks on database mutations. Define triggers in YAML:

- name: order_created
  table: orders
  operations: [create]
  webhook_url: "https://api.example.com/hooks/order-created"
  headers:
    X-Secret: webhook-secret-key
  retry_count: 3
- name: order_updated
  table: orders
  operations: [update]
  webhook_url: "https://api.example.com/hooks/order-updated"

Webhook Payload

{
  "trigger": "order_created",
  "table": "orders",
  "operation": "INSERT",
  "data": {
    "new": { "id": "uuid-...", "total": 150.00, "status": "pending" },
    "old": null
  },
  "timestamp": "2025-01-15T10:30:00Z"
}

Delivery Guarantees

• Non-blocking: Webhooks fire asynchronously via tokio::spawn. The REST response is never delayed.
• Retry with backoff: Failed deliveries retry with exponential backoff up to the configured count.
• Custom headers: Attach secret keys or auth tokens to webhook requests.

Operations

Each trigger can fire on one or more operations:

NDJSON Streaming

For large datasets, stream results as newline-delimited JSON:

GET /api/events?stream=true

Each row is sent as a separate JSON line with chunked transfer encoding — no buffering:

{"id":"uuid-1","type":"click","timestamp":"2025-01-01T00:00:00Z"}
{"id":"uuid-2","type":"purchase","timestamp":"2025-01-01T00:01:00Z"}
...

Ideal for data exports, ETL pipelines, and processing large tables without memory pressure.

Performance & Observability

The gateway is built for production throughput with prepared statement caching, query caching, rate limiting, and full observability.

Query Cache

LRU cache with configurable TTL and automatic table-level invalidation.

[gateway.cache]
enabled = true
max_entries = 1000
ttl_secs = 60

Behavior

• Cache key: Normalized query string + auth context hash
• Invalidation: Automatic on any mutation (INSERT, UPDATE, DELETE) to the same table
• Eviction: LRU when capacity is reached
• TTL: Configurable per-entry time-to-live
• Correctness: Concurrent reads during a mutation never serve stale data — invalidation is atomic

Cache Headers

Responses include cache status headers:

X-Cache: HIT                    # Served from cache
X-Cache: MISS                   # Fresh query executed

Prepared Statement Caching

The gateway caches prepared statements per query shape, eliminating repeated parse overhead. This is separate from the query cache — it caches the PostgreSQL server-side prepared statement, not the result.

Rate Limiting

Built-in token-bucket rate limiter keyed by client IP:

[gateway]
rate_limit_rate = 100.0         # Tokens refilled per second
rate_limit_burst = 200          # Maximum burst capacity

When exceeded, the gateway returns:

HTTP/1.1 429 Too Many Requests
Retry-After: 1

{
  "error": {
    "code": "RATE_LIMITED",
    "message": "Too many requests",
    "status": 429
  }
}

Request Timeouts

All queries have a configurable timeout (default: 30 seconds, set via statement_timeout_ms in qail.toml). Long-running queries are cancelled and return:

{
  "error": {
    "code": "TIMEOUT",
    "message": "Request timed out",
    "status": 408
  }
}

Prometheus Metrics

GET /metrics

Exposes request counts, latencies (p50/p95/p99), error rates, cache hit ratios, and connection pool stats in Prometheus format.

Request Tracing

Every response includes tracing headers:

X-Request-Id: req-uuid-...       # Unique request identifier
X-Response-Time: 12ms            # Duration

Health Check

GET /health

Returns 200 OK when the gateway and database connection are healthy.

Schema Introspection

GET /api/_schema         # Full schema: tables, columns, types, FKs
GET /api/_openapi        # Auto-generated OpenAPI 3.0 spec

The OpenAPI spec is generated from the live database schema — always up to date.

EXPLAIN Cost Guard

The gateway runs EXPLAIN on read queries before execution to reject expensive queries early.

Configure thresholds in qail.toml:

[gateway]
explain_max_cost = 100000.0    # Reject if estimated cost exceeds this
explain_max_rows = 1000000     # Reject if estimated rows exceed this

Per-role overrides allow analytics roles to run heavier queries:

[gateway.role_overrides.reporting]
explain_max_cost = 500000.0

Rejected queries return a structured QUERY_TOO_EXPENSIVE error:

{
  "code": "QUERY_TOO_EXPENSIVE",
  "message": "Query rejected: estimated cost 150000 exceeds limit 100000...",
  "details": "{\"estimated_cost\":150000,\"cost_limit\":100000,\"estimated_rows\":2000000,\"row_limit\":1000000,\"suggestions\":[\"Add WHERE clauses\",\"Reduce ?expand depth\"]}"
}

The details field is machine-readable JSON — client SDKs can parse it to display actionable suggestions.

EXPLAIN Endpoint

Inspect query plans manually:

GET /api/orders/_explain?status=paid&expand=users

Returns the PostgreSQL EXPLAIN ANALYZE output for the generated query.

Error Responses

All errors follow a consistent JSON structure:

{
  "error": {
    "code": "QUERY_ERROR",
    "message": "column \"foo\" does not exist",
    "status": 400,
    "request_id": "req-uuid-..."
  }
}

Error Codes

Benchmark: Gateway vs GraphQL

The gateway’s ?expand= executes a server-side JOIN over HTTP. In this benchmark, every approach is validated to return the same canonical payload (id, name, origin_harbor, dest_harbor) before timing.

Loopback (BATTLE_SIMULATED_RTT_US=0)

Simulated RTT (BATTLE_SIMULATED_RTT_US=1000)

?expand= stays in the same single-query class as AST execution and avoids resolver fan-out.

The gateway shape avoids resolver-driven N+1 behavior by resolving expansions through a single SQL JOIN plan.

Benchmark: Qail vs GraphQL vs REST Patterns

This benchmark measures one endpoint objective executed with different data-access patterns:

• Single query with server-side JOIN
• Batched lookup pattern (DataLoader style)
• Naive N+1 resolver/call pattern

All approaches must return the same canonical payload shape before timing: { id, name, origin_harbor, dest_harbor }.

Workload

• Dataset: current swb_staging_local PostgreSQL schema
• Primary table: odyssey_connections
• Related table: harbors
• Query shape for single-query variants: 2x LEFT JOIN
• Filter: odyssey_connections.is_enabled = true
• Sort: odyssey_connections.name ASC
• Limit: 50

Methodology

• Build profile: --release
• Iterations: 200
• Per-approach warmup: 15
• Global warmup: 15
• Execution order: randomized each run
• RLS: bypassed uniformly (SET app.is_super_admin = 'true') to isolate pattern cost
• Correctness guard: benchmark aborts if payload equivalence check fails
• Optional RTT injection: BATTLE_SIMULATED_RTT_US (applied per query dispatch in harness)
• Stats reported: median, p95, avg (avg in raw log), query-count/request

Results (Snapshot: March 25, 2026)

Loopback (BATTLE_SIMULATED_RTT_US=0)

Simulated RTT (BATTLE_SIMULATED_RTT_US=250)

Simulated RTT (BATTLE_SIMULATED_RTT_US=1000)

Technical Takeaways

• The dominant cost shift is query fan-out, not framework branding.
• On loopback, single-query and DataLoader are effectively tied.
• As RTT rises, single-query patterns pull ahead because they pay one round trip.
• Naive N+1 inflates round trips from 1-2 to 101, producing order-of-magnitude latency growth.
• Prepared vs uncached remains workload-sensitive in this harness and query shape.

AST Builder API

The recommended way to use QAIL. Build queries as typed Rust structs.

Query Types

SELECT Queries

#![allow(unused)]
fn main() {
use qail_core::{Qail, Operator, SortOrder};

let cmd = Qail::get("users")
    .columns(["id", "email", "name"])
    .filter("active", Operator::Eq, true)
    .order_by("created_at", SortOrder::Desc)
    .limit(10)
    .offset(20);
}

INSERT Queries

#![allow(unused)]
fn main() {
let cmd = Qail::add("users")
    .columns(["email", "name"])
    .values(["alice@example.com", "Alice"])
    .returning(["id", "created_at"]);
}

UPDATE Queries

#![allow(unused)]
fn main() {
let cmd = Qail::set("users")
    .set_value("status", "active")
    .set_value("verified_at", "now()")
    .where_eq("id", 42);
}

DELETE Queries

#![allow(unused)]
fn main() {
let cmd = Qail::del("users")
    .where_eq("id", 42);
}

Pub/Sub (LISTEN/NOTIFY)

#![allow(unused)]
fn main() {
// Subscribe to a channel
let listen = Qail::listen("orders");
// → LISTEN orders

// Send a notification
let notify = Qail::notify("orders", "new_order:123");
// → NOTIFY orders, 'new_order:123'

// Unsubscribe
let unlisten = Qail::unlisten("orders");
// → UNLISTEN orders
}

Builder Methods

Column Selection

Filtering

Sorting & Pagination

Joins

Grouping & Aggregation

Mutations

Advanced

Materialized Views

#![allow(unused)]
fn main() {
// Create
let view = Qail::create_materialized_view(
    "monthly_stats",
    Qail::get("orders")
        .columns(["date_trunc('month', created_at) AS month", "sum(total)"])
        .group_by(["month"])
);

// Refresh
let refresh = Qail::refresh_materialized_view("monthly_stats");

// Drop
let drop = Qail::drop_materialized_view("monthly_stats");
}

Expression Types

QAIL v0.14.2 provides 100% PostgreSQL expression coverage. All expression types are native AST nodes that encode directly to wire protocol bytes.

Coverage

Basic Expressions

Column Reference

#![allow(unused)]
fn main() {
use qail_core::ast::Expr;

// Simple column
let expr = Expr::Named("email".to_string());
// → email

// With alias
let expr = Expr::Aliased {
    name: "users.email".to_string(),
    alias: "user_email".to_string(),
};
// → users.email AS user_email
}

Literals

#![allow(unused)]
fn main() {
use qail_core::ast::{Expr, Value};

let expr = Expr::Literal(Value::Int(42));
// → 42

let expr = Expr::Literal(Value::String("hello".into()));
// → 'hello'
}

Aggregate Functions

#![allow(unused)]
fn main() {
use qail_core::ast::{Expr, AggregateFunc};

let expr = Expr::Aggregate {
    func: AggregateFunc::Count,
    col: "*".into(),
    distinct: false,
    filter: None,
    alias: Some("total".into()),
};
// → COUNT(*) AS total
}

With FILTER Clause (v0.14.2+)

#![allow(unused)]
fn main() {
let expr = Expr::Aggregate {
    func: AggregateFunc::Sum,
    col: "amount".into(),
    distinct: false,
    filter: Some(vec![condition]),  // WHERE condition
    alias: Some("filtered_sum".into()),
};
// → SUM(amount) FILTER (WHERE ...) AS filtered_sum
}

Window Functions

#![allow(unused)]
fn main() {
use qail_core::ast::{Expr, WindowFrame, FrameBound};

let expr = Expr::Window {
    func: "SUM".into(),
    params: vec![Expr::Named("amount".into())],
    partition: vec!["department".into()],
    order: vec![order_spec],
    frame: Some(WindowFrame::Rows {
        start: FrameBound::UnboundedPreceding,
        end: FrameBound::CurrentRow,
    }),
    alias: Some("running_total".into()),
};
// → SUM(amount) OVER (
//     PARTITION BY department
//     ORDER BY date
//     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
//   ) AS running_total
}

CASE Expressions

#![allow(unused)]
fn main() {
let expr = Expr::Case {
    when_clauses: vec![
        (condition1, Value::String("A".into())),
        (condition2, Value::String("B".into())),
    ],
    else_value: Some(Value::String("C".into())),
    alias: Some("grade".into()),
};
// → CASE WHEN ... THEN 'A' WHEN ... THEN 'B' ELSE 'C' END AS grade
}

New in v0.14.2

Array Constructor

#![allow(unused)]
fn main() {
let expr = Expr::ArrayConstructor {
    elements: vec![
        Expr::Named("col1".into()),
        Expr::Named("col2".into()),
    ],
    alias: Some("arr".into()),
};
// → ARRAY[col1, col2] AS arr
}

Row Constructor

#![allow(unused)]
fn main() {
let expr = Expr::RowConstructor {
    elements: vec![
        Expr::Named("id".into()),
        Expr::Named("name".into()),
    ],
    alias: Some("person".into()),
};
// → ROW(id, name) AS person
}

Subscript (Array Access)

#![allow(unused)]
fn main() {
let expr = Expr::Subscript {
    expr: Box::new(Expr::Named("tags".into())),
    index: Box::new(Expr::Literal(Value::Int(1))),
    alias: Some("first_tag".into()),
};
// → tags[1] AS first_tag
}

Collation

#![allow(unused)]
fn main() {
let expr = Expr::Collate {
    expr: Box::new(Expr::Named("name".into())),
    collation: "C".into(),
    alias: None,
};
// → name COLLATE "C"
}

Field Access (Composite Types)

#![allow(unused)]
fn main() {
let expr = Expr::FieldAccess {
    expr: Box::new(Expr::Named("address".into())),
    field: "city".into(),
    alias: Some("city".into()),
};
// → (address).city AS city
}

Type Casting

#![allow(unused)]
fn main() {
let expr = Expr::Cast {
    expr: Box::new(Expr::Named("id".into())),
    target_type: "TEXT".into(),
    alias: None,
};
// → id::TEXT
}

JSON Access

#![allow(unused)]
fn main() {
let expr = Expr::JsonAccess {
    column: "data".into(),
    path_segments: vec![
        ("user".into(), false),  // ->
        ("name".into(), true),   // ->>
    ],
    alias: Some("name".into()),
};
// → data->'user'->>'name' AS name
}

GROUP BY Modes

#![allow(unused)]
fn main() {
use qail_core::ast::GroupByMode;

// GROUPING SETS
let mode = GroupByMode::GroupingSets(vec![
    vec!["year".into(), "month".into()],
    vec!["year".into()],
    vec![],  // grand total
]);
// → GROUP BY GROUPING SETS ((year, month), (year), ())
}

DDL Actions

#![allow(unused)]
fn main() {
use qail_core::ast::{Qail, Action};

// Create view
let mut cmd = Qail::get("orders")
    .columns(["customer_id", "SUM(amount) AS total"])
    .group_by(["customer_id"]);
cmd.action = Action::CreateView;
cmd.table = "customer_totals".into();
// → CREATE VIEW customer_totals AS SELECT ...
}

Expression Builders

QAIL provides ergonomic builder functions for constructing AST expressions without verbose struct creation.

Import

#![allow(unused)]
fn main() {
use qail_core::ast::builders::*;
}

Column References

#![allow(unused)]
fn main() {
// Named column
col("phone_number")

// Star (*)
star()
}

Aggregate Functions

#![allow(unused)]
fn main() {
// COUNT(*)
count()

// COUNT(DISTINCT column)
count_distinct("phone_number")

// COUNT(*) FILTER (WHERE ...)
count_filter(vec![
    eq("direction", "outbound"),
    gt("created_at", now_minus("24 hours")),
]).alias("messages_sent_24h")

// Other aggregates
sum("amount")
avg("score")
min("price")
max("quantity")
}

All aggregates support:

• .distinct() — Add DISTINCT modifier
• .filter(conditions) — Add FILTER clause
• .alias("name") — Add AS alias

Time Functions

#![allow(unused)]
fn main() {
// NOW()
now()

// INTERVAL 'duration'
interval("24 hours")

// NOW() - INTERVAL 'duration' (common pattern)
now_minus("24 hours")

// NOW() + INTERVAL 'duration'
now_plus("7 days")
}

Type Casting

#![allow(unused)]
fn main() {
// expr::type
cast(col("amount"), "float8")

// With alias
cast(col("amount"), "float8").alias("amount_f")
}

CASE WHEN Expressions

#![allow(unused)]
fn main() {
// Simple CASE
case_when(gt("score", 80), text("A"))
    .otherwise(text("F"))
    .alias("grade")

// Multiple WHEN clauses
case_when(gt("score", 90), text("A"))
    .when(gt("score", 80), text("B"))
    .when(gt("score", 70), text("C"))
    .otherwise(text("F"))
    .alias("grade")
}

Condition Helpers

#![allow(unused)]
fn main() {
// Equality
eq("status", "active")      // status = 'active'
ne("status", "deleted")     // status != 'deleted'

// Comparisons
gt("created_at", now_minus("24 hours"))   // created_at > NOW() - INTERVAL '24 hours'
gte("age", 18)              // age >= 18
lt("price", 100)            // price < 100
lte("quantity", 10)         // quantity <= 10

// IN / NOT IN
is_in("status", ["delivered", "read"])    // status IN ('delivered', 'read')
not_in("type", ["spam", "junk"])          // type NOT IN ('spam', 'junk')

// NULL checks
is_null("deleted_at")       // deleted_at IS NULL
is_not_null("email")        // email IS NOT NULL

// Pattern matching
like("name", "John%")       // name LIKE 'John%'
ilike("email", "%@gmail%")  // email ILIKE '%@gmail%'
}

Function Calls

#![allow(unused)]
fn main() {
// Generic function
func("MY_FUNC", vec![col("a"), col("b")])

// COALESCE
coalesce(vec![col("nickname"), col("name"), text("Anonymous")])

// NULLIF
nullif(col("value"), int(0))
}

Binary Expressions

#![allow(unused)]
fn main() {
// Arithmetic
binary(col("price"), BinaryOp::Mul, col("quantity"))

// With alias
binary(
    cast(col("success"), "float8"),
    BinaryOp::Div,
    cast(col("total"), "float8")
).alias("success_rate")
}

Literals

#![allow(unused)]
fn main() {
int(42)           // Integer literal
float(3.14)       // Float literal
text("hello")     // String literal (quoted)
}

Complete Example

Here’s a complex analytics query using all the builders:

#![allow(unused)]
fn main() {
use qail_core::ast::builders::*;

let stats = Qail::get("whatsapp_messages")
    .columns([
        count_distinct("phone_number").alias("total_contacts"),
        count().alias("total_messages"),
        count_filter(vec![
            eq("direction", "outbound"),
            gt("created_at", now_minus("24 hours")),
        ]).alias("messages_sent_24h"),
        count_filter(vec![
            eq("direction", "inbound"),
            eq("status", "received"),
        ]).alias("unread_messages"),
    ]);

let cmd = Qail::get("stats")
    .with_cte("stats", stats)
    .columns([
        col("total_contacts"),
        col("total_messages"),
        case_when(gt("messages_sent_24h", 0),
            binary(
                cast(col("successful"), "float8"),
                BinaryOp::Div,
                cast(col("messages_sent_24h"), "float8")
            )
        ).otherwise(float(0.0)).alias("delivery_rate"),
    ]);
}

This replaces 40+ lines of raw SQL with type-safe, compile-time checked Rust code.

QAIL: The AST-Native Approach

QAIL takes a unique approach to building SQL queries: instead of strings or macros, queries are constructed as a typed Abstract Syntax Tree.

The AST-Native Difference

What QAIL Enables

Native PostgreSQL Features

#![allow(unused)]
fn main() {
use qail_core::{Qail, builders::*};

// Native JSON operators (->, ->>)
json_path("metadata", ["vessel_bookings", "0", "key"])

// COALESCE with type safety
coalesce([col("booking_number"), text("N/A")])

// String concatenation
concat([col("first_name"), text(" "), col("last_name")])

// Type casting  
cast(col("total_fare"), "float")

// CASE WHEN expressions
case_when(gt("score", 80), text("pass"))
    .otherwise(text("fail"))
}

Full Query Example

A production WhatsApp integration query with JSON access, string concat, and type casts:

#![allow(unused)]
fn main() {
use qail_core::{Qail, Operator, builders::*};

let route = coalesce([
    concat([
        json_path("o.metadata", ["vessel_bookings", "0", "depart_departure_loc"]),
        text(" → "),
        json_path("o.metadata", ["vessel_bookings", "0", "depart_arrival_loc"]),
    ]),
    text("Route"),
]).alias("route");

let cmd = Qail::get("orders")
    .table_alias("o")
    .column_expr(col("o.id"))
    .column_expr(coalesce([col("o.booking_number"), text("N/A")]).alias("booking_number"))
    .column_expr(cast(col("o.status"), "text").alias("status"))
    .column_expr(route)
    .column_expr(coalesce([
        json_path("o.metadata", ["vessel_bookings", "0", "depart_travel_date"]),
        text("TBD")
    ]).alias("travel_date"))
    .filter_cond(cond(json("o.contact_info", "phone"), Operator::Eq, param(1)))
    .or_filter_cond(cond(
        replace(json("o.contact_info", "phone"), text("+"), text("")),
        Operator::Eq, 
        param(1)
    ))
    .order_desc("o.created_at")
    .limit(10);

let orders = pool.fetch_all::<OrderRow>(&cmd).await?;
}

QAIL Highlights

ColumnType Validation

QAIL validates types at build time:

#![allow(unused)]
fn main() {
pub enum ColumnType {
    Uuid, Text, Varchar(Option<u16>), Int, BigInt, 
    Serial, BigSerial, Bool, Float, Decimal(Option<(u8,u8)>),
    Jsonb, Timestamp, Timestamptz, Date, Time, Bytea,
}

// Compile-time validation
ColumnType::Uuid.can_be_primary_key()     // true
ColumnType::Jsonb.can_be_primary_key()    // false - caught at build time
ColumnType::Jsonb.supports_indexing()     // false - warned before migration
}

When to Use QAIL

QAIL shines for:

• Complex PostgreSQL queries with JSON, CTEs, aggregates
• Type-safe query building with IDE support
• Production systems where safety is critical
• Projects that need advanced SQL features without string literals

Text Syntax

For CLI and LSP usage. Parses to AST internally.

Keywords

Examples

Simple Select

get users fields *

→ SELECT * FROM users

Filtered Query

get users 
    fields id, email, name
    where active = true
    order by created_at desc
    limit 50

Join Query

get users 
    inner join bookings
    fields id, email, bookings.total
    where created_at >= 2024-01-01

Insert

add users values (email = "alice@example.com", name = "Alice")

Update

set users values (status = "active") where id = 42

PostgreSQL Driver

The qail-pg crate provides a native PostgreSQL driver with AST-native wire protocol encoding. It communicates directly with Postgres at the wire level — no libpq, no ORM, and no app-side SQL interpolation on the AST path.

Features

• AST-Native — Direct AST to wire protocol on the primary query path
• Zero-Alloc — Reusable buffers, no heap allocation per query
• LRU Statement Cache — Bounded cache (100 max), auto-evicts
• SSL/TLS — Full TLS with mutual TLS (mTLS) support
• Password Auth Modes — Supports SCRAM-SHA-256, MD5, and cleartext server flows
• Enterprise Auth Policy — Configure allowed auth mechanisms and SCRAM channel binding mode
• Kerberos/GSS/SSPI Hooks — Protocol-level support with pluggable token providers (legacy + stateful)
• Built-in Linux Kerberos Provider — Optional enterprise-gssapi feature for native krb5/GSS flow
• Protocol Negotiation — Requests startup protocol 3.2 by default with one-shot fallback to 3.0 on explicit server version rejection
• Cancel-Key API — Native bytes cancel-key APIs for protocol 3.0 and 3.2 keys
• Connection Pooling — Efficient resource management with RLS-safe checkout
• COPY Protocol — Bulk insert for high throughput (1.63M rows/sec)
• Pipeline Execution — Multiple queries per round-trip
• Cursors — Stream large result sets
• Transactions — BEGIN/COMMIT/ROLLBACK

Architecture

Understanding which type to use is the most important concept:

PgPool (manages N connections, handles checkout/return)
  └── PooledConnection (call `release().await` for deterministic pool return)
        └── PgConnection (raw TCP/TLS stream, wire protocol I/O)
              └── PgDriver (convenience wrapper over PgConnection)

Rule of thumb: If you’re building a server, use PgPool. Everything else is for specialized cases.

Connection Methods

Choose based on your deployment:

Basic Connection

#![allow(unused)]
fn main() {
use qail_pg::PgDriver;

// Trust mode (no password)
let driver = PgDriver::connect("localhost", 5432, "user", "db").await?;

// With password (auto-detects MD5 or SCRAM-SHA-256)
let driver = PgDriver::connect_with_password(
    "localhost", 5432, "user", "db", "password"
).await?;

// From DATABASE_URL env var
let driver = PgDriver::connect_env().await?;
}

SSL/TLS

#![allow(unused)]
fn main() {
use qail_pg::PgConnection;

// Standard TLS — verifies server certificate
let conn = PgConnection::connect_tls("localhost", 5432, "user", "db").await?;
}

Mutual TLS (Client Certificates)

#![allow(unused)]
fn main() {
use qail_pg::{PgConnection, TlsConfig};

let config = TlsConfig {
    client_cert_pem: cert_bytes,
    client_key_pem: key_bytes,
    ca_cert_pem: Some(ca_bytes),
};

let conn = PgConnection::connect_mtls("localhost", 5432, "user", "db", config).await?;
}

Enterprise Auth/TLS Policy

#![allow(unused)]
fn main() {
use qail_pg::{AuthSettings, ConnectOptions, PgDriver, ScramChannelBindingMode, TlsMode};

let options = ConnectOptions {
    tls_mode: TlsMode::Require,
    auth: AuthSettings {
        allow_cleartext_password: false,
        allow_md5_password: false,
        allow_scram_sha_256: true,
        channel_binding: ScramChannelBindingMode::Require,
        ..AuthSettings::default()
    },
    ..Default::default()
};

let driver = PgDriver::connect_with_options(
    "db.internal",
    5432,
    "app_user",
    "app_db",
    Some("secret"),
    options,
)
.await?;
}

Kerberos / GSSAPI Token Hook

QAIL treats Kerberos/GSS/SSPI as an external identity concern. The driver consumes response tokens through Rust callbacks and enforces the configured authentication policy; it does not own SSO login, ticket acquisition, or a C ABI controller for enterprise auth. Use your OS ticket cache, keytab, sidecar, or enterprise identity stack to obtain credentials, then provide tokens through ConnectOptions.

#![allow(unused)]
fn main() {
use qail_pg::{
    AuthSettings, ConnectOptions, EnterpriseAuthMechanism, PgDriver,
};

fn gss_provider(
    mech: EnterpriseAuthMechanism,
    challenge: Option<&[u8]>,
) -> Result<Vec<u8>, String> {
    // Plug your krb5/gssapi token generation here.
    // Return initial token when challenge=None, then continue tokens per challenge.
    let _ = (mech, challenge);
    Err("not wired yet".to_string())
}

let options = ConnectOptions {
    auth: AuthSettings::gssapi_only(),
    gss_token_provider: Some(gss_provider),
    ..Default::default()
};

let _driver = PgDriver::connect_with_options(
    "db.internal", 5432, "app_user", "app_db", None, options
).await?;
}

Stateful GSS Provider (Per-Session Context)

#![allow(unused)]
fn main() {
use std::sync::Arc;
use qail_pg::{AuthSettings, ConnectOptions, GssTokenRequest, PgDriver};

let provider = Arc::new(move |req: GssTokenRequest<'_>| -> Result<Vec<u8>, String> {
    // req.session_id is stable for one auth handshake.
    // Keep per-session context in your own map if your GSS stack requires it.
    let _ = req;
    Err("wire your stateful provider".to_string())
});

let options = ConnectOptions {
    auth: AuthSettings::gssapi_only(),
    gss_token_provider_ex: Some(provider),
    ..Default::default()
};

let _driver = PgDriver::connect_with_options(
    "db.internal", 5432, "app_user", "app_db", None, options
).await?;
}

Built-in Linux Kerberos Provider (Feature-Gated)

The built-in Linux provider is a thin adapter over the local Kerberos/GSS runtime. It validates common environment wiring and uses existing credentials; it is not an identity broker and does not replace enterprise SSO tooling.

#![allow(unused)]
fn main() {
#[cfg(all(feature = "enterprise-gssapi", target_os = "linux"))]
{
    use qail_pg::{
        AuthSettings, ConnectOptions, LinuxKrb5ProviderConfig, PgDriver,
        linux_krb5_preflight, linux_krb5_token_provider,
    };

    let gss_cfg = LinuxKrb5ProviderConfig {
        service: "postgres".to_string(),
        host: "db.internal".to_string(),
        target_name: None, // optional override, e.g. Some("postgres@db.internal".into())
    };

    let report = linux_krb5_preflight(&gss_cfg)?;
    for warning in &report.warnings {
        eprintln!("Kerberos preflight warning: {}", warning);
    }

    let provider = linux_krb5_token_provider(gss_cfg)?;

    let options = ConnectOptions {
        auth: AuthSettings::gssapi_only(),
        gss_token_provider_ex: Some(provider),
        ..Default::default()
    };

    let _driver = PgDriver::connect_with_options(
        "db.internal", 5432, "app_user", "app_db", None, options
    ).await?;
}
}

Unix Socket

#![allow(unused)]
fn main() {
let conn = PgConnection::connect_unix(
    "/var/run/postgresql",  // socket directory
    "user",
    "db"
).await?;
}

AST-Native Queries

All queries are constructed through the typed AST — no raw SQL strings.

#![allow(unused)]
fn main() {
use qail_core::Qail;

let cmd = Qail::get("users").select_all().limit(10);

// Fetch all rows
let rows = driver.fetch_all(&cmd).await?;

// Fetch one row
let row = driver.fetch_one(&cmd).await?;

// Execute mutation (returns affected rows)
let affected = driver.execute(&cmd).await?;
}

Statement Cache (LRU)

Prepared statements are cached automatically. The AST is hashed by structure, so identical query shapes reuse the same prepared statement.

Cached execution includes one-shot self-heal for common server-side invalidation cases (prepared statement does not exist, cached plan must be replanned): local cache state is cleared and the statement is retried once automatically.

#![allow(unused)]
fn main() {
// Cache is bounded (default: 100 statements)
// Auto-evicts least recently used when full

let (size, capacity) = driver.cache_stats();  // (42, 100)

// Manual clear if needed
driver.clear_cache();
}

Pipeline Methods

Pipelining sends multiple queries in a single network round-trip. This is the key to high throughput. Choose based on your needs:

Do you need return values from each query?
├── Yes
│   └── pipeline_ast()              — full parse/bind/execute per query, returns rows
│
└── No (fire-and-forget mutations)
    ├── Repeating the same query shape with different params?
    │   ├── pipeline_ast_cached()   — hash-based statement reuse
    │   └── pipeline_prepared_fast()— named prepared statement reuse
    │
    └── Different query shapes?
        ├── pipeline_ast_fast()     — parse+bind+execute, discard results
        ├── pipeline_simple_fast()  — simple query protocol (no params)
        └── pipeline_bytes_fast()   — pre-encoded buffers (fastest possible)

Quick Reference

Example: Pipelined Inserts

#![allow(unused)]
fn main() {
let commands: Vec<Qail> = users.iter().map(|u| {
    Qail::add("users")
        .set_value("name", u.name.as_str())
        .set_value("email", u.email.as_str())
}).collect();

// Fire-and-forget — fastest for bulk mutations
let affected = driver.pipeline_ast_fast(&commands).await?;

// With results — slower but returns inserted rows
let rows = driver.pipeline_ast(&commands).await?;
}

Connection Pooling

#![allow(unused)]
fn main() {
use qail_pg::{PgPool, PoolConfig};
use qail_core::Qail;

let config = PoolConfig::new("localhost", 5432, "user", "db")
    .password("secret")
    .tls_mode(qail_pg::TlsMode::Require)
    .auth_settings(qail_pg::AuthSettings::scram_only())
    .max_connections(20)
    .min_connections(5);

let pool = PgPool::connect(config).await?;

// Acquire connection (return deterministically with release())
let mut conn = pool.acquire().await?;
let probe = Qail::get("users").columns(["id"]).limit(1);
let _ = conn.fetch_all(&probe).await?;
conn.release().await;

// Check idle count
let idle = pool.idle_count().await;
}

Pool with RLS (Multi-Tenant)

#![allow(unused)]
fn main() {
use qail_core::RlsContext;

let ctx = RlsContext::tenant_and_agent("tenant-123", "agent-456");

// Acquire + set RLS context in one call
// Call release() after query work to reset context and return to pool
let mut conn = pool.acquire_with_rls(ctx).await?;
conn.release().await;
}

Important: When using acquire_with_rls(), the RLS context is automatically cleared when the connection is returned to the pool. This prevents cross-tenant data leakage — a connection used by Tenant A will never carry Tenant A’s context when checked out by Tenant B.

Pool Configuration

#![allow(unused)]
fn main() {
use std::time::Duration;

let config = PoolConfig::new("localhost", 5432, "user", "db")
    .idle_timeout(Duration::from_secs(600))    // 10 min
    .acquire_timeout(Duration::from_secs(30))  // 30 sec
    .connect_timeout(Duration::from_secs(10)); // 10 sec
}

Bulk Insert (COPY Protocol)

High-performance bulk insert using PostgreSQL’s COPY protocol. Benchmarked at 1.63M rows/sec for 100M rows.

#![allow(unused)]
fn main() {
use qail_core::ast::Value;

let cmd = Qail::add("users").columns(&["name", "email"]);

let rows = vec![
    vec![Value::Text("Alice".into()), Value::Text("a@x.com".into())],
    vec![Value::Text("Bob".into()), Value::Text("b@x.com".into())],
];

let count = driver.copy_bulk(&cmd, &rows).await?;
// count = 2
}

Performance Comparison

Cursor Streaming

Stream large result sets in batches:

#![allow(unused)]
fn main() {
let cmd = Qail::get("logs").select_all();

let batches = driver.stream_cmd(&cmd, 1000).await?;
for batch in batches {
    for row in batch {
        // Process row
    }
}
}

Transactions

#![allow(unused)]
fn main() {
let mut conn = pool.acquire().await?;

conn.begin_transaction().await?;
// ... queries ...
conn.commit().await?;

// Or rollback on error
conn.rollback().await?;
}

Row Decoding

By Index

#![allow(unused)]
fn main() {
let name = row.get_string(0);
let age = row.get_i32(1);
}

By Column Name (Recommended)

#![allow(unused)]
fn main() {
// Safer — column order changes don't break code
let name = row.get_string_by_name("name");
let age = row.get_i32_by_name("age");
let email = row.get_string_by_name("email");

// Check if NULL
if row.is_null_by_name("deleted_at") { ... }
}

Available get_by_name methods:

• get_string_by_name, get_i32_by_name, get_i64_by_name
• get_f64_by_name, get_bool_by_name
• get_uuid_by_name, get_json_by_name
• is_null_by_name, column_index

Supported Types

✅ AST-Only Driver

Raw SQL helper APIs (execute_raw, fetch_raw) were removed.

#![allow(unused)]
fn main() {
// ✅ Use AST-native transaction APIs
driver.begin().await?;
// ... execute QAIL commands ...
driver.commit().await?;
}

API Decision Guide

Quick reference: “I want to do X → use Y.”

Connecting

Querying

Bulk Operations

Connection Pool

Transactions

Multi-Tenant (RLS)

Performance Tips

1. Use the pool — PgPool reuses connections and caches prepared statements.
2. Use pipeline_ast_fast() for bulk mutations — one round-trip instead of N.
3. Use copy_bulk() for truly massive inserts (>10K rows) — 10x faster than pipelining.
4. Use fetch_all() (cached) not fetch_all_uncached() — statement caching gives ~2x speedup.
5. Use acquire_with_rls() in multi-tenant apps — auto-clears on Drop, prevents cross-tenant leaks.

Migrations

QAIL supports two migration workflows:

1. Schema-Diff (State-Based): Compare standard schema files (good for evolving production DBs)
2. File-Based Phased Migrations: Apply .qail files from deltas/ through expand, backfill, and contract phases

1. Schema-Diff Workflow (State-Based)

QAIL uses an intent-aware .qail schema format that solves the ambiguity problem of state-based migrations.

The Problem with JSON/State-Based Migrations

// v1: {"users": {"username": "text"}}
// v2: {"users": {"name": "text"}}

Did we rename username → name or delete + add? JSON can’t express intent.

The Solution: .qail Schema Format

# schema.qail - Human readable, intent-aware
table users {
  id serial primary_key
  name text not_null
  email text unique
}

# Migration hints express INTENT
rename users.username -> users.name

Single File vs Modular Directory

Qail supports both:

• Single file: schema.qail
• Modular: schema/*.qail (recursive), optional schema/_order.qail

Modular schema is useful when one file becomes very large. If _order.qail exists, listed modules load first; in strict mode, every module must be listed.

Repository examples:

• examples/schema/single/schema.qail
• examples/schema/modular/schema/

Workflow

1. Pull Current Schema

qail pull postgres://user:pass@localhost/db > v1.qail

2. Create New Version

Edit v2.qail with your changes and any migration hints:

table users {
  id serial primary_key
  name text not_null          # was 'username'
  email text unique
  created_at timestamp not_null
}

rename users.username -> users.name

3. Preview Migration

qail diff v1.qail v2.qail
# Output:
# ALTER TABLE users RENAME COLUMN username TO name;
# ALTER TABLE users ADD COLUMN created_at TIMESTAMP NOT NULL;

4. Apply Migration

qail migrate apply --phase expand
qail migrate apply --phase backfill --backfill-chunk-size 10000
qail migrate apply --phase contract --codebase ./src

5. Rollback (if needed)

qail migrate rollback --to 20260527090000_add_user_name.expand.up.qail
# or apply explicit down files
qail migrate apply --direction down

2. File-Based Workflow (Expand / Backfill / Contract)

For hybrid projects or simple setups, use phased .qail files in the deltas/ directory.

Structure

deltas/
  └── 20260527090000_add_user_name/
      ├── expand.qail
      ├── backfill.qail
      └── contract.qail

Applying Migrations

# Apply one safety phase at a time
qail migrate apply --phase expand
qail migrate apply --phase backfill --backfill-chunk-size 10000
qail migrate apply --phase contract --codebase ./src

Generating from Sync Rules

Hybrid projects can auto-generate migrations for sync triggers:

qail sync generate
# Creates phased delta files for qail sync triggers

Migration Hints

3. Drift Detection

Compare a live database against a .qail schema file to find unexpected drift:

qail diff _ schema.qail --live --url postgres://localhost/mydb
# Drift detection: [live DB] → schema.qail
#   → Introspecting live database...
#     80 tables, 287 indexes introspected
#
#   ✅ No drift detected — live DB matches schema file.

If drift exists, it shows categorized changes with risk levels:

# 🔴 HIGH   — missing column (was dropped outside migrations)
# 🟡 MEDIUM — index mismatch
# 🟢 LOW    — default value difference

4. Migration Reset

Nuclear option for development — drops everything and recreates from schema:

qail migrate reset schema.qail postgres://...
# Phase 1: DROP all tables (FK-ordered)
# Phase 2: CLEAR migration history
# Phase 3: CREATE from schema
# ✓ Reset complete

⚠️ Warning: This is destructive. Use only in development or staging.

5. Migration Status

Rich tabular view of migration history:

qail migrate status postgres://...
# 📋 Migration Status — mydb
# ┌──────────┬────────────────────┬─────────────────────┬──────────────┐
# │ Version  │ Name               │ Applied At          │ Checksum     │
# ├──────────┼────────────────────┼─────────────────────┼──────────────┤
# │ 001      │ qail_queue         │ 2026-02-01 10:00:00 │ a3b8d1...    │
# └──────────┴────────────────────┴─────────────────────┴──────────────┘

Named Migrations

Named migrations provide version-controlled migration files with metadata for better tracking.

Creating a Named Migration

qail migrate create add_user_avatars --depends 002_add_users --author orion

Output:

📝 Creating Named Migration

  ✓ Created: deltas/20251226071129_add_user_avatars/expand.qail

  Migration: 20251226071129_add_user_avatars
  Depends:   002_add_users
  Author:    orion

Migration File Format

-- migration: 20251226071129_add_user_avatars
-- depends: 002_add_users
-- author: orion
-- created: 2025-12-26T07:11:29+08:00

+table avatars {
  id UUID primary_key
  user_id UUID not_null references(users.id)
  url TEXT not_null
}

Metadata Fields

CLI Options

qail migrate create <name>
  -d, --depends <migration>  # Dependencies (comma-separated)
  -a, --author <name>        # Author attribution

Dependency Resolution

QAIL validates dependencies before applying migrations:

• Checks all dependencies exist
• Detects circular dependencies
• Applies in topological order

Data-Safe Migrations

QAIL provides enterprise-grade migration safety features that protect your data during schema changes.

Overview

Phase 1: Impact Analysis & Backup Prompt

When running migrations with destructive operations, QAIL analyzes the impact:

$ qail migrate apply --phase contract --codebase ./src --url postgres://...

🚨 Migration Impact Analysis
━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  DROP COLUMN users.email → 1,234 values at risk
  DROP TABLE  sessions    → 5,678 rows affected
━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  Total: 6,912 records at risk

Choose an option:
  [1] Proceed (I have my own backup)
  [2] Backup to files (_qail_snapshots/)
  [3] Backup to database (with rollback support)
  [4] Cancel migration

Options Explained

• [1] Proceed - Continue without QAIL backup (you manage your own)
• [2] File Backup - Export affected data to _qail_snapshots/ directory
• [3] Database Backup - Store data in _qail_data_snapshots table (enables true rollback)
• [4] Cancel - Abort the migration

Phase 2: Record-Level Database Backup

When you choose option [3], QAIL creates a snapshot table:

-- Automatically created
CREATE TABLE _qail_data_snapshots (
    id SERIAL PRIMARY KEY,
    migration_version VARCHAR(255) NOT NULL,
    table_name VARCHAR(255) NOT NULL,
    column_name VARCHAR(255),
    row_id TEXT NOT NULL,
    value_json JSONB NOT NULL,
    snapshot_type VARCHAR(50) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

What Gets Backed Up

True Data Rollback

After migration, you can restore data:

# Schema rollback (uses applied migration history)
qail migrate rollback --to 20260527090000_add_user_name.expand.up.qail --url postgres://...

# Data rollback (restores values)
# Coming in future release: qail rollback --data

Phase 3: Shadow Database (Blue-Green)

For zero-downtime migrations, use shadow database mode:

# Step 1: Create shadow, apply migrations, sync data
qail migrate shadow old.qail:new.qail postgres://...

🔄 Shadow Migration Mode
━━━━━━━━━━━━━━━━━━━━━━━━━━
  [1/4] Creating shadow database: mydb_shadow ✓
  [2/4] Applying migration to shadow... ✓
  [3/4] Syncing data from primary to shadow...
    ✓ users (1,234 rows)
    ✓ orders (5,678 rows)
    ✓ Synced 2 tables, 6,912 rows
  [4/4] Shadow ready for validation

  Shadow URL: postgres://...mydb_shadow

  Available Commands:
    qail migrate promote → Switch traffic to shadow
    qail migrate abort   → Drop shadow, keep primary

Shadow Workflow

1. Create Shadow - New database with new schema
2. Apply Migrations - Run DDL on shadow only
3. Sync Data - Copy data from primary
4. Validate - Test your application against shadow
5. Promote or Abort - Make the decision

Promote (Go Live)

$ qail migrate promote postgres://...

🚀 Promoting Shadow to Primary
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  [1/3] Renaming mydb → mydb_old_20241226 ✓
  [2/3] Renaming mydb_shadow → mydb ✓
  [3/3] Keeping old database as backup

✓ Shadow promoted successfully!
  Old database preserved as: mydb_old_20241226
  To clean up: DROP DATABASE mydb_old_20241226

Abort (Rollback)

$ qail migrate abort postgres://...

🛑 Aborting Shadow Migration
━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  Dropping shadow database: mydb_shadow

✓ Shadow database dropped. Primary unchanged.

Comparison with Other Tools

Best Practices

1. Always use database backup for production migrations
2. Test in shadow before promoting
3. Keep old database for 24-48 hours after promotion
4. Use transactions (QAIL does this automatically)

Configuration

No configuration required! Features are enabled by default when destructive operations are detected.

Migration Impact Analyzer

Prevents runtime errors by scanning your codebase before running migrations.

Why Use It?

Dropping a table or column is easy—but if your code still references it, you’ll get runtime errors. The analyzer:

1. Scans your codebase for QAIL AST/text query usage and SQL markers
2. Detects breaking changes like dropped tables/columns
3. Shows exact file:line locations with code snippets
4. Prevents downtime by catching issues before production

Usage

qail migrate analyze old.qail:new.qail --codebase ./src

Real-World Example

Testing against a production codebase:

🔍 Migration Impact Analyzer

  Schema: 001_initial_schema.up.qail → breaking_change.qail
  Codebase: ~/api.fortunebali.com/src

Scanning codebase...
🔍 Analyzing files...
   ├── 🦀 main.rs (AST: 60 refs)
   └── 1 files analyzed

  Found 60 query references

⚠️  BREAKING CHANGES DETECTED

Affected files: 1

┌─ DROP TABLE admin_otps (11 references) ─────────────────────────┐
│ ❌ main.rs:397 → Qail::del("admin_otps")
│ ❌ main.rs:402 → Qail::add("admin_otps")
│ ❌ main.rs:403 → .columns(["email", "code_hash", "expires_at"])
│ ... and 8 more
└──────────────────────────────────────────────────────────────────┘

┌─ DROP TABLE inquiries (11 references) ─────────────────────────┐
│ ❌ main.rs:238 → Qail::add("inquiries")
│ ❌ main.rs:239 → .columns(["name", "email", ...])
│ ... and 9 more
└──────────────────────────────────────────────────────────────────┘

┌─ DROP COLUMN portfolio.status (2 references) ─────────────────┐
│ ❌ main.rs:179 → uses status in .columns(["id" +8])
│ ⚠️  RAW SQL main.rs:225 → "SELECT id, title, status FROM..."
└──────────────────────────────────────────────────────────────────┘

What would you like to do?
  1. Run anyway (DANGEROUS - will cause 5 runtime errors)
  2. Dry-run first (show SQL, don't execute)
  3. Let me fix the code first (exit)

Semantic Scanning Modes

The analyzer auto-detects file types and uses the most appropriate scanning method.

Rust analyzer mode and build-time validation share the same semantic QAIL extraction path and query-IR checks.

Rollback Safety Analysis

Warning

Data-Destructive Changes Cannot Be Rolled Back!

Some migrations are irreversible. The analyzer identifies:

Breaking Change Types

CI/CD Integration

For GitHub Actions, use --ci flag for annotations:

- name: Check migration safety
  run: qail migrate analyze $OLD:$NEW --codebase ./src --ci

This outputs GitHub Actions annotations that appear inline in PR diffs:

::error file=src/main.rs,line=225,title=Breaking Change::Column 'portfolio.status' is being dropped but referenced here

Best Practices

1. Always run before the contract phase

   qail migrate analyze old.qail:new.qail --codebase ./src
   qail migrate apply --phase expand --url $DATABASE_URL
   qail migrate apply --phase backfill --url $DATABASE_URL
   qail migrate apply --phase contract --codebase ./src --url $DATABASE_URL
   

2. Use with migrate plan for full preview

   qail migrate plan old.qail:new.qail     # See SQL
   qail migrate analyze old.qail:new.qail  # Check codebase
   qail migrate apply --phase expand --url $URL
   qail migrate apply --phase backfill --url $URL
   qail migrate apply --phase contract --codebase ./src --url $URL
   

3. Handle irreversible changes carefully

   • Backup data before DROP TABLE or DROP COLUMN
   • Consider soft-delete (add deleted_at column) instead of hard delete

Foreign Key Validation

QAIL provides compile-time validation for foreign key references, ensuring your schema is consistent before migrations run.

Defining Foreign Keys

Use the builder API to define foreign key constraints:

#![allow(unused)]
fn main() {
use qail_core::migrate::{Column, ColumnType, ForeignKey, FkAction};

let user_id = Column::new("user_id", ColumnType::Uuid)
    .references("users", "id")
    .on_delete(FkAction::Cascade)
    .on_update(FkAction::NoAction);
}

FK Actions

Schema Validation

Call validate() to check all FK references exist:

#![allow(unused)]
fn main() {
let mut schema = Schema::new();

schema.add_table(Table::new("users")
    .column(Column::new("id", ColumnType::Uuid).primary_key()));

schema.add_table(Table::new("posts")
    .column(Column::new("id", ColumnType::Uuid).primary_key())
    .column(Column::new("user_id", ColumnType::Uuid)
        .references("users", "id")));

// Validate all FK references
match schema.validate() {
    Ok(()) => println!("Schema is valid"),
    Err(errors) => {
        for e in errors {
            eprintln!("Error: {}", e);
        }
    }
}
}

Error Messages

If a FK references a non-existent table or column:

FK error: posts.user_id references non-existent table 'users'
FK error: posts.author_id references non-existent column 'users.author_id'

Best Practices

1. Always validate before migrating

   #![allow(unused)]
   fn main() {
   let schema = parse_qail(&content)?;
   schema.validate()?;
   }

2. Use Cascade carefully - it can delete more data than expected

3. Prefer SetNull for optional relationships

   #![allow(unused)]
   fn main() {
   .references("categories", "id")
   .on_delete(FkAction::SetNull)
   }

First-Class Relations

QAIL enables implicit joins through schema-defined foreign key relationships. Instead of manually specifying join conditions, define relationships once in schema.qail and use join_on() to automatically infer the join.

The Dream

#![allow(unused)]
fn main() {
// ❌ Before: Explicit join conditions
Qail::get("users")
    .left_join("posts", "users.id", "posts.user_id")

// ✅ After: Implicit joins via schema
Qail::get("users")
    .join_on("posts")?
}

1. Define Relations in Schema

Use ref: syntax to declare foreign key relationships:

table users {
    id UUID primary_key
    email TEXT not_null
}

table posts {
    id UUID primary_key
    user_id UUID ref:users.id
    title TEXT
}

The ref:users.id annotation tells QAIL that posts.user_id references users.id.

2. Load Relations at Runtime

Before using join_on(), load the schema relations:

#![allow(unused)]
fn main() {
use qail_core::schema;

// Load at application startup
schema::load_schema_relations("schema.qail")?;
}

3. Use Implicit Joins

#![allow(unused)]
fn main() {
use qail_core::Qail;

// Auto-infers: LEFT JOIN posts ON users.id = posts.user_id
let query = Qail::get("users")
    .columns(["users.id", "users.email", "posts.title"])
    .join_on("posts")?;

// Forward and reverse relations work automatically
let posts_with_users = Qail::get("posts")
    .join_on("users")?;  // Infers: LEFT JOIN users ON posts.user_id = users.id
}

API Reference

join_on(table)

Joins a related table using the schema-defined foreign key. Returns an error if no relation exists or relation metadata is ambiguous.

#![allow(unused)]
fn main() {
Qail::get("users").join_on("posts")?
}

join_on_optional(table)

Same as join_on(), but returns self unchanged if no relation exists or metadata is ambiguous.

#![allow(unused)]
fn main() {
Qail::get("users").join_on_optional("comments")  // No-op if no relation
}

How It Works

1. Schema Parsing: build.rs parses ref: annotations and stores them as ForeignKey entries
2. Runtime Registry: schema::load_schema_relations() populates a global RelationRegistry
3. Lookup: join_on() calls lookup_relation_state() to find the join condition or return a structured error path
4. Bidirectional: Both forward (posts.user_id → users.id) and reverse directions are checked

Typed Codegen

QAIL can generate fully typed Rust modules from your schema.qail file, enabling compile-time checked table and column references.

Setup

1. Add build script

Create or update your build.rs:

fn main() {
    let out_dir = std::env::var("OUT_DIR").unwrap();
    qail_core::build::generate_typed_schema(
        "schema.qail",
        &format!("{}/schema.rs", out_dir)
    ).unwrap();
    
    println!("cargo:rerun-if-changed=schema.qail");
}

2. Include generated module

In your lib.rs or main.rs:

#![allow(unused)]
fn main() {
include!(concat!(env!("OUT_DIR"), "/schema.rs"));
}

Usage

Generated Structure

From this schema:

table users {
    id UUID primary_key
    email TEXT not_null
    age INT
}

table posts {
    id UUID primary_key
    user_id UUID ref:users.id
    title TEXT
}

QAIL generates:

#![allow(unused)]
fn main() {
pub mod users {
    pub struct Users;
    impl Table for Users { ... }
    
    pub const table: Users = Users;
    pub const id: TypedColumn<uuid::Uuid> = ...;
    pub const email: TypedColumn<String> = ...;
    pub const age: TypedColumn<i32> = ...;
}

pub mod posts {
    pub struct Posts;
    pub const table: Posts = Posts;
    pub const user_id: TypedColumn<uuid::Uuid> = ...;
}
}

Using Typed References

#![allow(unused)]
fn main() {
use schema::{users, posts};

// Tables and columns are type-safe
Qail::get(users::table)
    .columns([users::id, users::email])
    .join_on(posts::table)?
}

Type Mapping

Logic-Safe Relations (Scenario B)

QAIL codegen now generates compile-time relationship checking using the RelatedTo<T> trait.

How It Works

When schema.qail contains foreign key references:

table posts {
    user_id UUID ref:users.id
}

The codegen produces:

#![allow(unused)]
fn main() {
// Forward: child -> parent
impl RelatedTo<users::Users> for posts::Posts {
    fn join_columns() -> (&'static str, &'static str) { ("user_id", "id") }
}

// Reverse: parent -> children
impl RelatedTo<posts::Posts> for users::Users {
    fn join_columns() -> (&'static str, &'static str) { ("id", "user_id") }
}
}

Compile-Time Safety

This enables “logic-safe” joins that fail at compile time:

#![allow(unused)]
fn main() {
// ✅ Compiles - tables are related
Qail::get(users::table).join_related(posts::table)

// ❌ Compile Error: "Users: RelatedTo<Products> is not satisfied"
Qail::get(users::table).join_related(products::table)
}

Data Access Policies (Phase 4)

QAIL now supports compile-time data governance using the protected keyword.

Schema Definition

Mark sensitive columns with protected:

table users {
    id UUID primary_key
    email TEXT not_null
    password_hash TEXT protected
    two_factor_secret TEXT protected
}

Generated Types

Protected columns get TypedColumn<T, Protected> instead of TypedColumn<T, Public>:

#![allow(unused)]
fn main() {
// Public - accessible by default
pub const email: TypedColumn<String, Public> = ...;

// Protected - requires capability witness
pub const password_hash: TypedColumn<String, Protected> = ...;
}

Policy Hierarchy

Capability Witness API

Access protected columns using the builder pattern:

#![allow(unused)]
fn main() {
use qail_core::typed::{CapabilityProvider, WithCap};    

// In your auth middleware (Root of Trust):
let admin_cap = CapabilityProvider::mint_admin();  // After JWT verification

// Build query with typed table reference (no strings!)
let query = Qail::get(users::table)                 // ✓ Typed, not string
    .with_cap(&admin_cap)                           // Prove authorization
    .column(users::email)                           // Public - always allowed
    .column_protected(users::password_hash)         // Protected - now allowed!
    .build();
}

Root of Trust

Important

AdminCap and SystemCap have sealed constructors (private fields). They can only be minted via CapabilityProvider::mint_*(). Place this in a single, auditable auth layer.

#![allow(unused)]
fn main() {
// In your AuthService (the ONLY place that can mint capabilities):
impl AuthService {
    pub fn verify_admin(&self, token: &str) -> Result<AdminCap, AuthError> {
        let claims = self.verify_jwt(token)?;
        if claims.role == "admin" {
            Ok(CapabilityProvider::mint_admin())
        } else {
            Err(AuthError::Forbidden)
        }
    }
}
}

Compile-Time Enforcement

Attempting to access protected columns without capability fails at compile time:

#![allow(unused)]
fn main() {
// ❌ Compile Error: Protected: PolicyAllowedBy<NoCap> is not satisfied
Qail::get(users::table)
    .with_cap(&NoCap)
    .column_protected(users::password_hash)
}

Connection Pooling

Efficient connection reuse with built-in multi-tenant safety.

Configuration

#![allow(unused)]
fn main() {
use qail_pg::{PgPool, PoolConfig};

let config = PoolConfig::new("localhost", 5432, "user", "database")
    .password("secret")
    .max_connections(20)
    .min_connections(5);
}

Or load from qail.toml:

#![allow(unused)]
fn main() {
let pool = PgPool::from_config().await?;
}

Acquiring Connections

Always use RLS-aware methods for tenant queries:

#![allow(unused)]
fn main() {
use qail_core::rls::RlsContext;

// Tenant-scoped connection — RLS is set before any query runs
let ctx = RlsContext::tenant(tenant_id);
let mut conn = pool.acquire_with_rls(ctx).await?;

// With custom statement timeout (milliseconds)
let timeout_ctx = RlsContext::tenant(tenant_id);
let mut conn = pool.acquire_with_rls_timeout(timeout_ctx, 30_000).await?;

// System connection — no tenant context (for schema introspection, migrations)
let mut conn = pool.acquire_system().await?;
}

Warning: Never use acquire_raw() for tenant queries. It returns a connection with no RLS context, bypassing row-level security. This method is crate-internal only.

Connection Lifecycle

Every connection follows a strict lifecycle:

acquire_with_rls(ctx)
  → BEGIN
  → SET LOCAL statement_timeout = ...
  → set_config('app.current_tenant_id', '...', true)
  → set_config('app.current_user_id', '...', true)
  → set_config('app.current_agent_id', '...', true)
  → set_config('app.is_super_admin', '...', true)
  → execute queries (RLS policies filter rows automatically)
  → release()
      → COMMIT (resets transaction-local GUCs and SET LOCAL values)
      → return to pool

Transaction-local settings reset on COMMIT. Prepared statement caches remain available for performance, while tenant/user context is set again on every RLS-aware checkout.

Pool Stats

#![allow(unused)]
fn main() {
let stats = pool.stats();
println!("Active: {}, Idle: {}", stats.active, stats.idle);
}

Best Practices

1. Create pool once at application startup
2. Share via Arc across threads/tasks
3. Don’t hold connections longer than needed
4. Always use acquire_with_rls() for tenant queries — never acquire_raw()
5. Set appropriate pool size — CPU cores × 2 is a good start

#![allow(unused)]
fn main() {
use std::sync::Arc;

let pool = Arc::new(PgPool::connect(config).await?);

// Clone Arc for each task
let pool_clone = pool.clone();
tokio::spawn(async move {
    let ctx = RlsContext::tenant(tenant_id);
    let conn = pool_clone.acquire_with_rls(ctx).await?;
    // ...
});
}

Hybrid Architecture

QAIL bridges the gap between relational data (PostgreSQL) and vector search (Qdrant) using a robust Outbox Pattern.

Overview

Instead of dual-writing to both databases (which is prone to inconsistencies), QAIL uses a transactional outbox approach:

1. Transactional Write: You write to your PostgreSQL tables.
2. Auto-Trigger: A database trigger captures the change and writes a “sync event” to the _qail_queue table in the same transaction.
3. Async Worker: The qail worker daemon polls the queue and reliably syncs data to Qdrant.

Setup

1. Initialize Hybrid Project

qail init
# Select "3. Hybrid (PostgreSQL + Qdrant)" when prompted

This creates phased delta files defining the _qail_queue table.

2. Configure Sync Rules

Define which tables should sync to Qdrant in qail.toml:

[[sync]]
source_table = "products"           # Table to watch
trigger_column = "description"      # Only sync if this column changes (optional optimization)
target_collection = "products_search" # Qdrant collection name
embedding_model = "candle:bert-base"  # (Coming soon)

3. Generate Triggers

Run the command to generate PostgreSQL triggers based on your rules:

qail sync generate
# Creates phased delta files for sync triggers

This generates triggers that:

• INSERT: Adds UPSERT event to queue.
• UPDATE: Adds UPSERT event (only if trigger_column changed).
• DELETE: Adds DELETE event to queue.

4. Apply Migrations

Apply the migration files to your database:

qail migrate apply

5. Start Worker

Start the background worker to process the queue:

qail worker

Resilience

The qail worker is designed for production reliability:

• At-Least-Once Delivery: Events are only removed from the queue after successful sync to Qdrant.
• Circuit Breaker: If Qdrant goes down, the worker pauses and retries with exponential backoff (up to 30s).
• Per-Item Error Handling: A single reduced item won’t crash the worker; it will be marked with an error and retried later.
• Batch Processing: Updates are sent to Qdrant in batches for high throughput.

The _qail_queue Table

Type System

QAIL provides type conversion between Rust and PostgreSQL.

Supported Types

Compile-Time Type Safety

QAIL uses the ColumnType enum for compile-time validation in schema definitions:

#![allow(unused)]
fn main() {
use qail_core::migrate::{Column, ColumnType};

// ✅ Compile-time enforced - no typos possible
Column::new("id", ColumnType::Uuid).primary_key()
Column::new("name", ColumnType::Text).not_null()
Column::new("email", ColumnType::Varchar(Some(255))).unique()

// Available types:
// Uuid, Text, Varchar, Int, BigInt, Serial, BigSerial,
// Bool, Float, Decimal, Jsonb, Timestamp, Timestamptz, Date, Time, Bytea
}

Validation at compile time:

• primary_key() validates the type can be a PK (UUID, INT, SERIAL)
• unique() validates the type supports indexing (not JSONB, BYTEA)

Usage

Reading Values

#![allow(unused)]
fn main() {
use qail_pg::types::{Timestamp, Uuid, Json};

for row in rows {
    let id: i32 = row.get("id")?;
    let uuid: Uuid = row.get("uuid")?;
    let created: Timestamp = row.get("created_at")?;
    let data: Json = row.get("metadata")?;
}
}

Temporal Types

#![allow(unused)]
fn main() {
use qail_pg::types::{Timestamp, Date, Time};

// Timestamp with microsecond precision
let ts = Timestamp::from_micros(1703520000000000);

// Date only
let date = Date::from_ymd(2024, 1, 15);

// Time only
let time = Time::from_hms(14, 30, 0);
}

JSON

#![allow(unused)]
fn main() {
use qail_pg::types::Json;

let json = Json("{"key": "value"}".to_string());
}

Custom Types

Implement FromPg and ToPg for custom types:

#![allow(unused)]
fn main() {
use qail_pg::types::{FromPg, ToPg, TypeError};

impl FromPg for MyType {
    fn from_pg(bytes: &[u8], oid: u32, format: i16) -> Result<Self, TypeError> {
        // Decode from wire format
    }
}

impl ToPg for MyType {
    fn to_pg(&self) -> (Vec<u8>, u32, i16) {
        // Encode to wire format
        (bytes, oid, format)
    }
}
}

Compile-Time Type Safety

New in v0.14.20 — Full Diesel-like type checking for QAIL queries

QAIL now supports compile-time type validation through generated schema files, similar to Diesel but with AST-native architecture.

Quick Start

1. Generate Schema

qail types schema.qail -o src/schema.rs

2. Use Type-Safe Builders

#![allow(unused)]
fn main() {
use crate::schema::users;

let query = Qail::get(users::TABLE)
    .typed_column(users::id())
    .typed_column(users::email())
    .typed_eq(users::active(), true)  // Compile-time: active must be bool
    .typed_gt(users::age(), 18);       // Compile-time: age must be numeric
}

Schema Generation

Input: schema.qail

table users {
    id          uuid primary_key
    email       text not_null unique
    name        text
    active      boolean default(true)
    age         integer
    created_at  timestamptz default(now())
}

Output: schema.rs

#![allow(unused)]
fn main() {
pub mod users {
    use qail_core::typed::{TypedColumn, Table};
    
    pub const TABLE: &str = "users";
    
    pub fn id() -> TypedColumn<uuid::Uuid> {
        TypedColumn::new("id")
    }
    
    pub fn email() -> TypedColumn<String> {
        TypedColumn::new("email")
    }
    
    pub fn active() -> TypedColumn<bool> {
        TypedColumn::new("active")
    }
    
    pub fn age() -> TypedColumn<i32> {
        TypedColumn::new("age")
    }
}
}

Type-Safe Methods

SQL to Rust Type Mapping

Reserved Keywords

Rust reserved keywords are automatically escaped:

Compile-Time Errors

Type mismatches are caught at compile time:

#![allow(unused)]
fn main() {
// ✅ Compiles - active is bool
query.typed_eq(users::active(), true);

// ❌ Compile error - age is i32, not string
query.typed_eq(users::age(), "eighteen");
// error[E0277]: the trait bound `&str: ColumnValue<i32>` is not satisfied
}

Integration with Existing Code

Type-safe methods can be mixed with dynamic methods:

#![allow(unused)]
fn main() {
let query = Qail::get(users::TABLE)
    .typed_eq(users::active(), true)  // Type-safe
    .filter("created_at", Operator::Gte, "2024-01-01")  // Dynamic
    .typed_column(users::email());
}

Row-Level Security (RLS)

QAIL carries tenant/user scope through the AST and driver pipeline, then qail-pg sets transaction-local PostgreSQL GUCs before executing tenant queries. PostgreSQL RLS policies remain the database enforcement boundary.

This gives application code a tenant-first API while keeping row isolation in the database:

#![allow(unused)]
fn main() {
use qail_core::prelude::*;

let ctx = RlsContext::tenant(tenant_id).with_user(user_id);

let query = Qail::get("bookings")
    .columns(["id", "status", "total"])
    .eq("status", "confirmed")
    .with_rls(&ctx)?;

let rows = driver.fetch_all(&query).await?;
}

The Problem

Multi-tenant apps fail when row ownership is treated as a convention:

#![allow(unused)]
fn main() {
// Easy to remember in one handler
let sql = "SELECT * FROM bookings WHERE tenant_id = $1";

// Easy to forget in another handler
let sql = "SELECT * FROM invoices";
}

QAIL does not replace PostgreSQL RLS. It makes the tenant context explicit in the query/connection lifecycle so app code does not hand-roll scope setup on every call.

Context Constructors

SuperAdminToken cannot be fabricated with public fields. It must be created through a named constructor such as for_system_process, for_webhook, or for_auth, which makes bypass intent visible at the call site.

PostgreSQL Session Context

qail-pg opens a transaction and sets transaction-local context before the query runs:

BEGIN;
SET LOCAL statement_timeout = ...;
SET LOCAL app.is_global = 'false';
SELECT
  set_config('app.current_user_id',   '<user>',   true),
  set_config('app.current_tenant_id', '<tenant>', true),
  set_config('app.current_agent_id',  '<agent>',  true),
  set_config('app.is_super_admin',    'false',    true);

On release, the connection commits the transaction. Transaction-local GUCs and SET LOCAL values reset on COMMIT, while prepared statement caches can remain hot for reuse.

PostgreSQL Policy Example

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

CREATE POLICY orders_tenant_isolation ON orders
  FOR ALL
  USING (
    tenant_id = current_setting('app.current_tenant_id', true)::uuid
    OR current_setting('app.is_super_admin', true) = 'true'
  )
  WITH CHECK (
    tenant_id = current_setting('app.current_tenant_id', true)::uuid
    OR current_setting('app.is_super_admin', true) = 'true'
  );

Use a database role that is not a superuser and does not have BYPASSRLS. Superusers bypass RLS regardless of client-side discipline.

Horizontal And Vertical Access

RLS is horizontal: it decides which rows are visible or writable.

Native access policy is vertical: it decides which tables, operations, roles, scopes, and columns are allowed before the query reaches PostgreSQL.

Use both:

[access]
enabled = true
path = "access-policy.toml"

See Access Policy for operation and column semantics.

Gateway Behavior

qail-gateway extracts tenant/user/role/scope from JWT claims. tenant_id is the primary runtime contract. A legacy agent_id claim is only used as a secondary scope when tenant_id is present; it does not create tenant scope by itself.

Header-based dev auth can provide the same claims only when QAIL_DEV_MODE=true and the gateway is bound safely for development.

Guarantees And Non-Guarantees

If the database policy is wrong, QAIL cannot infer the correct row rule. If the application uses a raw connection outside the RLS-aware path, it owns the risk.

Operational Checklist

• Use RlsContext::tenant(...) as the default runtime scope.
• Attach with_user(...) when database policies need user ownership.
• Keep agent_id as a secondary legacy scope, not the primary tenant identity.
• Use transaction-local GUCs through qail-pg pool/driver APIs.
• Enable and force PostgreSQL RLS on tenant-owned tables.
• Run app roles as NOBYPASSRLS non-superusers.
• Use native access policy for vertical permissions.
• Keep super_admin token creation limited to named internal paths.

Access Policy

QAIL has two different access-control layers:

• Row-Level Security (RLS) handles horizontal isolation: which rows a tenant, user, or platform context can see.
• Native access policy handles vertical isolation: which tables, operations, columns, roles, and scopes a subject can use before the AST reaches the database.

Use both for production SaaS APIs. RLS protects row ownership in PostgreSQL; the access policy protects the API and AST surface from reading or writing fields the subject should not be able to touch.

Where It Runs

Native access policy lives in qail_core::access. It checks a Qail command directly, so it works before PostgreSQL execution:

#![allow(unused)]
fn main() {
use qail_core::access::{
    AccessContext, AccessOperation, AccessPolicy, ColumnRule, TableAccessPolicy,
};
use qail_core::Qail;

let policy = AccessPolicy::new().with_table(
    "orders",
    TableAccessPolicy::new()
        .allow_operations([AccessOperation::Read, AccessOperation::Update])
        .read_columns(ColumnRule::only(["id", "status", "total"]))
        .write_columns(ColumnRule::only(["status"]))
        .require_any_role(["operator"])
        .require_scopes(["orders:read"]),
);

let ctx = AccessContext::subject("user-1")
    .with_tenant("tenant-1")
    .with_role("operator")
    .with_scope("orders:read");

let cmd = Qail::get("orders").columns(["id", "status"]);
policy.check_command(&ctx, &cmd)?;
}

qail-gateway loads this policy when [access] is configured and applies it to REST, QAIL text/binary, batch, transaction, RPC, and live-query paths before execution.

Configuration

Enable gateway integration through qail.toml:

[access]
enabled = true
path = "access-policy.toml"

Policy files may be TOML or JSON. YAML is not supported for the native access policy.

Example access-policy.toml:

default_decision = "deny"

[tables.orders]
operations = ["read", "update"]
denied_operations = ["delete"]
read_columns = { only = ["id", "status", "total", "created_at"] }
write_columns = { only = ["status"] }
returning_columns = { only = ["id", "status"] }
require_any_role = ["operator", "administrator"]
require_scopes = ["orders:read"]

[tables.order_audit]
operations = ["read"]
read_columns = { except = ["internal_note"] }
require_any_role = ["administrator"]
require_scopes = ["audit:read"]

Use default_decision = "deny" for production. AccessPolicy::new() is deny-by-default. AccessPolicy::allow_by_default() exists for trusted internal tools, not public API exposure.

Operation Semantics

QAIL maps commands to the operations that must be allowed:

For MERGE, QAIL checks the target action clauses. A WHEN MATCHED UPDATE requires update; WHEN NOT MATCHED INSERT requires create; DELETE requires delete. The source table or source query also needs read policy.

Column Semantics

Column rules can be:

Restrictive read rules reject wildcard projections. If a user can only read id and status, then Qail::get("orders") is rejected because it means an implicit wildcard. Use explicit columns:

#![allow(unused)]
fn main() {
Qail::get("orders").columns(["id", "status"])
}

Read policy also covers filter columns, RETURNING, DISTINCT ON, grouping sets, window partition/order expressions, relevant payload right-hand column references, and recursively checked subqueries/CTEs.

Write policy covers explicit insert, update, upsert, and MERGE target columns. When a restrictive write rule is active, positional or ambiguous payloads fail closed because QAIL cannot prove which target column is being written.

Expressions And Fail-Closed Behavior

The checker allows expressions it can map back to a governed column. Examples:

• column
• table.column
• JSON access that starts from a concrete column
• aggregate expressions with a concrete column argument
• aliases that preserve a concrete source column name

It rejects shapes that cannot be enforced precisely under a restrictive policy:

• wildcard projections
• unsupported projection expressions
• raw function payload values under restrictive read policy
• restrictive policy on auxiliary tables in UPDATE FROM or DELETE USING
• restrictive policy on a MERGE table source unless the source is represented as an explicit source query with projected columns

This behavior is intentional. If QAIL cannot prove that the AST respects the policy, the command is denied instead of guessed.

Relationship To Gateway Policy

qail-gateway still has a gateway policy engine for legacy per-table route rules and filter injection. The native access policy is the current vertical permission model because it lives in qail-core and checks the AST itself.

For new deployments:

1. Use PostgreSQL RLS for row ownership.
2. Use native [access] policy for operation and column permissions.
3. Use gateway allow-listing, rate limits, EXPLAIN guardrails, and RPC controls as defense-in-depth around exposed routes.

Production Checklist

• Keep default_decision = "deny".
• Avoid wildcard projections for restricted roles.
• Split public and sensitive columns into explicit read/write rules.
• Require roles and scopes for privileged tables.
• Give source tables a read policy when using MERGE, subqueries, joins, or mutation source clauses.
• Use PostgreSQL RLS with a non-superuser role that has NOBYPASSRLS.
• Treat super_admin contexts as internal-only and log their call sites.

Workflows

QAIL Flow Engine is a storage-agnostic workflow state machine for business flows that pause, resume, branch, notify, charge, and run QAIL AST queries.

QAIL Flow Ledger is the PostgreSQL-backed executor wrapper that stores the runtime guarantees needed for production: workflow state, leases, operation idempotency, side-effect replay, and timeout due-row discovery.

When To Use It

Use workflows for app-level processes where a single HTTP request is too small to model the real business state:

• WhatsApp booking recovery after an operator declines
• vendor notification and accept/decline loops
• payment charge creation followed by confirmation events
• timeout fallback paths when a user, vendor, or provider never responds
• multi-step inventory or booking reconciliation

Do not use workflows as a replacement for database transactions. Use normal transactions for one atomic database change. Use workflows when the process spans external systems and time.

Definition Model

A workflow definition is a named state machine. Each transition owns a list of steps:

#![allow(unused)]
fn main() {
use std::time::Duration;
use qail_core::Qail;
use qail_workflow::{ChannelKind, WorkflowDefinition, WorkflowStep};

let definition = WorkflowDefinition::new("booking_recovery")
    .version("2026-06")
    .initial_state("operator_declined")
    .transition("operator_declined", "waiting_for_vendor", vec![
        WorkflowStep::query(
            &Qail::get("vendor_slots")
                .columns(["id", "phone", "price"])
                .eq("status", "available")
                .limit(10),
            Some("alternatives"),
        ),
        WorkflowStep::for_each("alternatives", vec![
            WorkflowStep::notify(
                ChannelKind::WhatsApp,
                "booking_opportunity",
                "item.phone",
            ),
        ]),
        WorkflowStep::wait_or(
            "vendor.accepted",
            Duration::from_secs(900),
            vec![WorkflowStep::transition("timed_out")],
        ),
    ]);
}

WorkflowStep::query persists QAIL wire text produced by qail_core::wire::encode_cmd_text; it is not a raw SQL payload.

Runtime Operations

The public runtime entry points are:

Use WorkflowRunOptions in production:

#![allow(unused)]
fn main() {
use std::time::Duration;
use qail_workflow::WorkflowRunOptions;

let options = WorkflowRunOptions::default()
    .with_lease("worker-a", Duration::from_secs(30))
    .with_idempotency_key("webhook:vendor.accepted:event-123");
}

A lease prevents concurrent workers from executing the same workflow id at the same time. An idempotency key prevents the same external event, scheduler tick, or retry from running the workflow operation twice.

Executor Contract

qail-workflow is generic. Your application implements WorkflowExecutor for business-side effects:

• execute a QAIL query payload
• send a notification
• create a payment charge
• save/load workflow state
• optionally acquire/release leases
• optionally record operation idempotency
• optionally record side-effect results
• optionally find due timeout rows

The default trait methods are permissive so tests and in-memory experiments are easy. Production executors should implement the runtime hooks. Otherwise, the engine can pause/resume, but it cannot prove distributed lease ownership, idempotent resume, or durable side-effect replay.

Flow Ledger For PostgreSQL

qail-workflow-postgres wraps an app executor:

#![allow(unused)]
fn main() {
use qail_workflow_postgres::{PgWorkflowExecutor, PgWorkflowStore};

let store = PgWorkflowStore::connect_url(database_url).await?;
store.install_schema().await?;

let executor = PgWorkflowExecutor::new(app_executor, store);
}

The default tables are:

• qail_workflow_states
• qail_workflow_leases
• qail_workflow_operations
• qail_workflow_side_effects

All store operations are built as QAIL AST commands and executed through qail-pg.

Exactly-Once Semantics

The engine provides replay control, not magic exactly-once delivery.

For payment and notification providers, pass the workflow side-effect operation id as the provider idempotency key whenever duplicates would be unsafe.

Side-Effect Ledger

The engine wraps query, notify, and charge steps with stable side-effect ids. The ledger returns one of two decisions:

• Execute: run the side effect now.
• AlreadyCompleted: skip execution and replay the stored result when needed.

Query and charge steps store their result because later workflow steps may need that value in context. Notification steps normally store no result; once they are completed, a replay skips delivery.

Failed side effects can be retried. Started side effects remain in progress until the in-progress TTL expires; this lets another worker recover from a crash after begin_* but before fail_* or complete_*.

Timeout Model

WorkflowStep::wait_or stores the expected event and a deadline in the cursor. The engine does not spawn a background scheduler. Production apps should run a worker that calls timeout_due_workflows with a stable idempotency key for the drain attempt.

The Postgres Flow Ledger discovers due rows with row locking and a claim TTL so multiple timeout workers can run without picking the same workflow row at the same time.

Definition Versioning

Workflow contexts carry definition name and version. Resume and timeout paths validate cursor state against the supplied definition so an old paused cursor does not blindly continue through a different branch layout.

For production upgrades:

1. Version workflow definitions.
2. Keep old definitions available until paused runs drain.
3. Avoid changing step order or branch paths for in-flight versions.
4. Use explicit migration logic when an in-flight context shape changes.

v2 Side-Effect Id Rollout

Current side-effect operation ids include workflow state generation. This fixes replay identity for workflows that revisit the same state/step path after a state transition.

Deploy this over an empty or drained side-effect ledger. If a deployment has already-running v1 side-effect rows, either drain them before upgrading or explicitly accept replay risk for those in-flight workflows.

Production Checklist

• Wrap the app executor with PgWorkflowExecutor for Postgres deployments.
• Install Flow Ledger tables before enabling workers.
• Use WorkflowRunOptions::with_lease for run/resume/timeout execution.
• Use stable idempotency keys for webhook events and scheduler batches.
• Pass workflow side-effect ids to payment/notification providers.
• Set lease TTL and in-progress TTL longer than normal operation runtime.
• Keep old workflow definitions available while paused runs exist.
• Test crash/retry cases around notify, charge, query, resume, and timeout.

Centralized Configuration

Qail uses a single qail.toml file as the source of truth for all components. Configuration follows a layered priority:

Environment Variables > qail.toml > Hardcoded Defaults

Quick Start

Generate a config with qail init, or create qail.toml manually:

[project]
name = "my-app"
mode = "postgres"

[postgres]
url = "postgres://${DB_USER}:${DB_PASSWORD}@${DB_HOST:-localhost}:5432/mydb"
max_connections = 20
min_connections = 2
idle_timeout_secs = 600
acquire_timeout_secs = 30
connect_timeout_secs = 10
test_on_acquire = false

[postgres.rls]
default_role = "app_user"
super_admin_role = "super_admin"

[qdrant]
url = "http://localhost:6333"
grpc = "localhost:6334"
max_connections = 10

[gateway]
bind = "0.0.0.0:8080"
cors = true
policy = "policies.yaml"
max_result_rows = 10000
statement_timeout_ms = 30000
explain_max_cost = 100000.0
explain_max_rows = 1000000
max_expand_depth = 3
tenant_max_concurrent = 10

# Per-role guard overrides — override global defaults for specific roles.
# Any field not specified here falls back to the global [gateway] default.
[gateway.role_overrides.reporting]
max_result_rows = 100000
statement_timeout_ms = 120000
explain_max_cost = 500000.0

[gateway.role_overrides.admin]
max_expand_depth = 5

[gateway.cache]
enabled = true
max_entries = 1000
ttl_secs = 60

[access]
enabled = true
path = "access-policy.toml"

[[sync]]
source_table = "products"
trigger_column = "description"
target_collection = "products_search"
embedding_model = "candle:bert-base"

Environment Variable Expansion

Use ${VAR} syntax inside TOML values to reference environment variables:

Example:

[postgres]
url = "postgres://${DB_USER}:${DB_PASSWORD}@${DB_HOST:-localhost}:5432/mydb"

Set DB_USER and DB_PASSWORD in your .env or shell. DB_HOST falls back to localhost if unset.

Environment Variable Overrides

These env vars always override their TOML counterparts, regardless of ${VAR} expansion:

This lets you keep one qail.toml across dev/staging/prod and switch databases purely via env vars.

Usage in Rust

PostgreSQL Pool (one-liner)

#![allow(unused)]
fn main() {
use qail_pg::driver::pool::PgPool;

let pool = PgPool::from_config().await?;
}

Manual Config Loading

#![allow(unused)]
fn main() {
use qail_core::config::QailConfig;
use qail_pg::driver::pool::PoolConfig;

let qail = QailConfig::load()?;                       // reads ./qail.toml
let pg_config = PoolConfig::from_qail_config(&qail)?;  // parse postgres section
let pool = PgPool::connect(pg_config).await?;
}

Qdrant

#![allow(unused)]
fn main() {
use qail_qdrant::pool::PoolConfig as QdrantPoolConfig;

let qail = QailConfig::load()?;
if let Some(config) = QdrantPoolConfig::from_qail_config(&qail) {
    let pool = QdrantPool::new(config).await?;
}
}

Gateway

#![allow(unused)]
fn main() {
use qail_gateway::config::GatewayConfig;

let qail = QailConfig::load()?;
let gw = GatewayConfig::from_qail_config(&qail);
}

Section Reference

Native Access Policy

RLS handles row ownership; [access] handles operation and column access before the gateway sends a QAIL AST to Postgres. It is opt-in:

[access]
enabled = true
path = "access-policy.toml"

Example access-policy.toml:

default_decision = "deny"

[tables.orders]
operations = ["read", "update"]
read_columns = { only = ["id", "status", "total"] }
write_columns = { only = ["status"] }
require_any_role = ["operator", "administrator"]
require_scopes = ["orders:read"]

Policy files may be TOML or JSON. When enabled = true, path is required. See Access Policy for the full operation, column, role, scope, and fail-closed semantics.

Generated Files

qail init creates:

• qail.toml — project config with commented-out optional sections
• .env.example — documents all supported env var overrides

CLI Commands

The qail command-line tool — v1.3.3.

Installation

cargo install qail

Commands

qail init

Initialize a new QAIL project. Auto-detects running PostgreSQL instances on the host, Docker, and Podman.

# Interactive mode — scans for running databases
qail init
# 🪝 QAIL Project Initialization
# Scanning for PostgreSQL instances...
#   ✓ Found 2 instance(s):
#     1.  🖥  host localhost:5432 (host)
#     2.  🐳 docker localhost:5433 (docker) — my-pg
#     3.  Enter URL manually
# Select [1-3]:

# Non-interactive mode (CI/scripting)
qail init --name myapp --mode postgres --url postgres://localhost/mydb
qail init --name myapp --mode hybrid --url postgres://localhost/mydb --deployment docker

Options:

• --name <NAME>: Project name
• --mode <MODE>: Database mode (postgres, qdrant, hybrid)
• --url <URL>: Database URL (skips interactive prompt)
• --deployment <TYPE>: Deployment type (host, docker, podman)

Generates qail.toml and necessary migration files.

qail exec

Execute QAIL statements against a database:

# Inline queries
qail exec "get users'id'email[active = true]" --url postgres://...
qail exec "add users fields name, email values 'Alice', 'a@test.com'" --url postgres://... --tx
qail exec "set users[id = 1] fields name = 'Bob'" --url postgres://...

# Count rows
qail exec "cnt orders[status = 'paid']" --url postgres://...
# → SELECT COUNT(*) FROM orders WHERE status = 'paid'

# JSON output (pipe-friendly)
qail exec "get users" --url postgres://... --json
qail exec "get users" --url postgres://... --json | jq '.[].email'

# From file
qail exec -f seed.qail --url postgres://...

# Dry-run (preview generated SQL)
qail exec "get users'*'" --dry-run
# 📋 Parsed 1 QAIL statement(s)
# 🔍 DRY-RUN MODE — Generated SQL:
#   SELECT * FROM users

# With SSH tunnel
qail exec "get users" --url postgres://remote/db --ssh user@bastion

Syntax:

add <table> fields <col1>, <col2> values <val1>, <val2>
set <table>[id = $1] fields name = 'new', updated_at = now
del <table>[id = $1]
get <table>'id'name[active = true]
cnt <table>[active = true]

Value Types:

Options:

• -f, --file <FILE>: Path to .qail file with statements
• -u, --url <URL>: Database connection URL
• --json: Output SELECT results as JSON array
• --tx: Wrap all statements in a transaction
• --dry-run: Preview generated SQL without executing
• --ssh <USER@HOST>: SSH tunnel via bastion host

qail pull

Extract schema from a live database:

qail pull postgres://user:pass@localhost/db > schema.qail

qail diff

Compare two schemas or detect drift against a live database:

# Compare two schema files
qail diff old.qail new.qail
qail diff old.qail new.qail --format json

# Live drift detection (introspects running database)
qail diff _ schema.qail --live --url postgres://localhost/mydb
# Drift detection: [live DB] → schema.qail
#   → Introspecting live database...
#     80 tables, 287 indexes introspected
#   ✅ No drift detected — live DB matches schema file.

Options:

• --format <FMT>: Output format (sql, json, pretty)
• --live: Use live database introspection as “old” schema
• --url <URL>: Database URL (required with --live)

qail check

Validate a schema file or preview migration safety:

# Validate schema
qail check schema.qail
# ✓ Schema is valid
#   Tables: 80 | Columns: 1110 | Indexes: 287
#   ✓ 82 primary key(s)

# Validate modular schema directory
qail check schema/

# Fallback mode: if schema.qail is missing, sibling schema/ is used
qail check schema.qail

# Check migration safety
qail check old.qail:new.qail
# ✓ Both schemas are valid
# Migration preview: 4 operation(s)
#   ✓ 3 safe operation(s)
#   ⚠️  1 reversible operation(s)

Migrate Commands

qail migrate status

View migration history with rich tabular output:

qail migrate status postgres://...
# 📋 Migration Status — mydb
# ┌──────────┬────────────────────┬─────────────────────┬──────────────┐
# │ Version  │ Name               │ Applied At          │ Checksum     │
# ├──────────┼────────────────────┼─────────────────────┼──────────────┤
# │ 001      │ qail_queue         │ 2026-02-01 10:00:00 │ a3b8d1...    │
# │ 002      │ add_users          │ 2026-02-05 14:32:00 │ f81d4f...    │
# └──────────┴────────────────────┴─────────────────────┴──────────────┘

qail migrate apply

Apply file-based migrations from deltas/ with the expand/backfill/contract model:

# Add compatible schema first
qail migrate apply --phase expand

# Move existing data in resumable chunks
qail migrate apply --phase backfill --backfill-chunk-size 10000

# Remove old schema only after code references are gone
qail migrate apply --phase contract --codebase ./src

Phased migrations may be stored as:

deltas/
  20260527090000_add_user_name/
    expand.qail
    backfill.qail
    contract.qail

Use --phase all only when a release is safe to run through all phases in order. For rollback, use qail migrate rollback --to <version> or apply explicit down files with qail migrate apply --direction down.

qail migrate plan

Preview migration SQL without executing (dry-run):

qail migrate plan old.qail:new.qail
# 📋 Migration Plan (dry-run)
# ┌─ UP (2 operations) ─────────────────────────────────┐
# │ 1. ALTER TABLE users ADD COLUMN verified BOOLEAN
# │ 2. CREATE INDEX idx_users_email ON users (email)
# └─────────────────────────────────────────────────────┘

# Save to file
qail migrate plan old.qail:new.qail --output migration.sql

qail migrate analyze

Analyze codebase for breaking changes before migrating:

qail migrate analyze old.qail:new.qail --codebase ./src
# 🔍 Migration Impact Analyzer
# Scanning codebase... Found 395 query references
#
# ⚠️  BREAKING CHANGES DETECTED
# ┌─ DROP TABLE promotions (6 references) ─────────────┐
# │ ❌ src/repository/promotion.rs:89 → INSERT INTO...
# │ ❌ src/repository/promotion.rs:264 → SELECT...
# └────────────────────────────────────────────────────┘

qail migrate apply --phase all

Apply all pending file-based migration phases from deltas/:

qail migrate apply
# → Found 1 migrations to apply
# ✓ Connected to mydb
#   → 20260527090000_add_user_name/expand.qail... ✓
# ✓ All migrations applied successfully!

qail migrate reset

Nuclear option — drop all objects, clear history, re-apply target schema:

qail migrate reset schema.qail postgres://...
# ⚠️  This will DROP all tables, clear migration history, and recreate from schema.
# Phase 1: DROP all tables...
# Phase 2: CLEAR migration history...
# Phase 3: CREATE from schema...
# ✓ Reset complete

qail migrate shadow

Test migrations against a shadow database:

qail migrate shadow v1.qail:v2.qail postgres://shadow-db/...

Other Commands

qail explain

Parse and explain a QAIL query:

qail "get users'id'email[active = true]"
# SELECT id, email FROM users WHERE active = true

qail repl

Interactive QAIL REPL — type queries, see SQL in real-time:

qail repl
# QAIL REPL
# Type QAIL queries, see SQL output.
# qail> get users[active = true]
# SELECT * FROM users WHERE active = true

qail types

Generate typed Rust schema from .qail file:

qail types schema.qail > src/generated/schema.rs

qail watch

Watch schema file for changes and auto-generate migrations:

qail watch schema.qail --url postgres://... --auto-apply

qail lint

Check schema for best practices:

qail lint schema.qail
# 🔍 Schema Linter
# ⚠ 144 warning(s)
# ℹ 266 info(s)

qail sync generate

Generate trigger migrations from [[sync]] rules in qail.toml (Hybrid mode):

qail sync generate
# ✓ Created phased delta files for sync triggers

qail worker

Start the background worker to sync PostgreSQL → Qdrant (Hybrid mode):

qail worker --interval 1000 --batch 100

Global Options

API Reference

Full API documentation is generated from source code.

Rust Crates

SDKs

Generate Local Docs

cargo doc --no-deps --open

Key Types

qail-core

• Qail - Query command builder
• Operator - Comparison operators
• SortOrder - ASC/DESC
• Expr - Expression AST nodes
• QailBuildError - Structured builder error type

qail-pg

• PgDriver - Database connection
• PgPool - Connection pool
• PgRow - Result row
• PgError - Error types

1.0 API Notes

• Use Qail::get/add/set/del and typed builder methods for normal database work.
• Use with_rls(&ctx)?; the older try_with_rls() alias is removed.
• Use join_on(...)? for schema-driven relation joins; the older try_join_on() alias is removed.
• Use bytes-native PostgreSQL cancel-key APIs instead of legacy i32 wrappers.
• Keep QailBuildError structured; broad string conversion is no longer part of the stable API.
• Avoid raw SQL builder APIs on the public runtime path. Session settings should use session AST commands.

Source Code

View the source on GitHub:

• qail-core
• qail-pg
• qail-gateway
• qail-cli
• qail-sdk

Changelog

For the full project changelog, see the repository file:

• CHANGELOG.md

Current Highlights (v1.3.3)

• PostgreSQL protocol hardening: fast-path backend control-frame validation, frontend send/flush handling, prepared statement lifecycle checks, and pooled raw cleanup behavior now fail closed more consistently.
• Native AST/parser validation: QAIL grammar, schema/query-file parsing, native MERGE handling, identifiers, and expression encoding reject more malformed or unsafe AST shapes before PostgreSQL execution.
• MERGE live behavior: PostgreSQL coverage now exercises complex expressions, CTE/query sources, RLS-scoped update/insert/delete flows, and invalid ASTs that must fail before mutation.
• Panic-safety gate: legacy COPY/time helper paths avoid runtime panics, and encoder FFI cleanup sites now satisfy the stricter unsafe documentation gate.
• Real database validation: PostgreSQL 18 lab coverage passed for cursor cleanup, set operations, recursive CTEs, access-checked execution, MERGE, legacy integration, and seeded RLS behavior.

v1.3.0 Highlights

• Detailed changelog: QAIL.rs v1.3.0: Native Vertical Policy and the Audit Pass Behind It
• Native vertical access policy: qail_core::access adds deny-by-default table policies, role/scope requirements, operation permissions, and read/write/returning column rules before AST execution.
• Gateway policy integration: [access] in qail.toml loads TOML/JSON policies so gateway REST, QAIL text/binary/batch, transaction, RPC, nested, expanded, and live-query paths can enforce vertical table and column boundaries alongside PostgreSQL RLS.
• PostgreSQL statement cache safety: hot prepared statements are promoted, evicted, reparsed, and retained only in states that match the real backend statement lifecycle.
• Migration verification: composite foreign-key options now survive parse/diff/apply and strict post-apply checks verify table constraints against the live database.
• Gateway hardening: precise numerics, oversized integers, Qdrant JSON integer drift, transaction subqueries, branch replay, and tenant guard exemptions are handled on explicit fail-closed paths.
• Workflow and encoder fixes: workflow guards, charge amounts, branch cursors, null bind params, zero-parameter binds, and Qdrant vector byte order were tightened.
• SDK path safety: TypeScript, Kotlin, and Swift SDK builders encode table and ID path segments before constructing REST routes.
• Real database validation: PostgreSQL lab coverage passed for strict migrations, MERGE, access-checked execution, seeded RLS, and gateway native access policy behavior.

v1.2.1 Highlights

• Schema parser compatibility: pulled PostgreSQL schemas now accept table-level enable_rls and force_rls directives.
• PostgreSQL type parsing: multi-word types such as DOUBLE PRECISION and TIMESTAMP WITH TIME ZONE parse correctly from pulled schemas.
• Comment parsing: schema comments containing quoted examples no longer break parsing.

v1.2.0 Highlights

• PostgreSQL protocol safety: COPY, LISTEN/NOTIFY, replication, pooled fetch, driver fetch, query, and pipeline paths now fail closed and desynchronize bad connections on malformed backend state.
• NUL and UTF-8 hardening: savepoints, SQL rendering, AST SQL buffers, gateway explain SQL, COPY text rows, backend wire strings, and PostgreSQL URL decoding now reject invalid input instead of silently mutating it.
• Real database validation: PostgreSQL 18 lab coverage passed for MERGE, set operations, recursive CTEs, cursor cleanup, COPY callback recovery, LISTEN/NOTIFY payloads, savepoint rejection, and NUL query rejection.

v1.1.1 Highlights

• Live migration introspection: schema pulls and shadow verification now account for generated columns, identity defaults, expression indexes, enum extensions, and composite foreign-key drift.
• Migration replay safety: post-apply checks compare constraints, defaults, generated expressions, indexes, and extension dependencies against the real database state.
• Branch overlay coverage: live PostgreSQL audit paths verify merge, set-operation, recursive-CTE behavior, and bad overlay replay failure modes.

v1.1.0 Highlights

• Workflow engine hardening: fixed nested loop context preservation, wait-event resume validation, timeout fallbacks, and transition checkpointing.
• Gateway security hardening: tightened tenant guards, RLS policy injection, write-side column policies, idempotency, transaction paths, branch overlays, and REST mutation/event semantics.
• Qdrant tenant safety: tenant-scoped vector upserts now namespace point IDs while preserving the caller-facing original ID.
• Branch overlay correctness: branch reads and merges now use deterministic chronological ordering with post-policy filtering and projection.
• Durable events: webhook delivery now has an outbox-backed path and stricter old/new payload handling.
• Runtime surface cleanup: the supported stable runtime is centered on PostgreSQL and Qdrant; legacy SQLite/DynamoDB/MongoDB transpiler symbols remain compatibility-only for 1.x consumers, and obsolete PostgreSQL examples were removed.

v1.0.0 Highlights

• Promoted QAIL to 1.0.0 Stable, declaring the API complete and production-grade.
• gRPC Connection State Machine: Implemented concurrent reconnection protection using a connection generation counter in the Qdrant engine.
• Webhook Scaling: Scaled webhook concurrency limit to 512 paired with safe timeouts.
• Connection Pool Locking: Replaced async-wait locks with standard library unwrap synchronization under heavy concurrent loads.
• Workspace Crates: All workspace crates, internal path dependencies, and VSCode LSP extension bumped to 1.0.0.
• API Cleanup Carried Into 1.0: try_with_rls() and try_join_on() compatibility aliases are gone; call with_rls(&ctx)? and join_on(...)? on the fallible path.
• Raw SQL Runtime Surface: Qail::raw_sql(...), Qail::is_raw_sql(), Qail::raw_where(...), and Qail::nextval(...) are not part of the stable public runtime path.
• Cancel-Key API: legacy i32 cancel-key wrappers are gone; use bytes-native cancel-key APIs.
• Error Conversion: broad From<QailBuildError> for String compatibility conversion was removed so callers keep structured build errors.