Request Pipeline

Every POST /data request passes through a 9-step pipeline. The client sends parameterized SQL + params via Drizzle Proxy, and the server validates, applies permissions, and executes. Understanding this pipeline helps you debug permission issues, optimize queries, and reason about security.

  Incoming Request — POST /data + Bearer JWT + SQL + params
    │
    ▼
  1. Rate Limiting — Per-user (200/min) and per-IP (500/min)
    │
    ▼
  2. Body Validation — Parse SQL + params, validate structure
    │
    ▼
  3. JWT Extraction — Decode token from Authorization header
    │
    ▼
  4. Session Resolution — resolveSession(user, db) → enriched user
    │
    ▼
  5. Role Injection — Map user.role → permission slugs
    │
    ▼
  6. Permission Check — Validate SQL → Inject filters → Restrict columns → Check → Preset
    │
    ▼
  6a. Middleware (before next) — Custom TypeScript logic (optional)
    │
    ▼
  7. DuckDB Execution — next() runs the permission-filtered SQL
    │
    ▼
  7a. Middleware (after next) — Custom TypeScript logic (optional)
    │
    ▼
  8. Audit Log — Record query, params, duration, user
    │
    ▼
  9. Response — Return JSON result to client

Step-by-Step

1. Rate Limiting

The engine enforces rate limits before processing any request logic:

limits: {
  rateLimitPerUser: 200,  // 200 requests per minute per authenticated user
  rateLimitPerIP: 500,    // 500 requests per minute per IP address
}

If exceeded, returns 429 Too Many Requests.

2. Body Validation

The request body contains parameterized SQL + params sent by the Drizzle Proxy client:

{
  "sql": "SELECT \"id\", \"amount\", \"status\" FROM \"main\".\"orders\" WHERE \"status\" = $1 LIMIT $2",
  "params": ["active", 100],
  "method": "all"
}

The method field indicates the query type: all (returns rows as arrays), get (single row), values (raw values), or run (no return). Invalid structure returns 400 Bad Request.

3. JWT Extraction

The Authorization: Bearer <token> header is extracted and passed to the auth provider's verifyToken method. Invalid or expired tokens return 401 Unauthorized.

4. Session Resolution

The auth provider's findUser locates the user record, then resolveSession enriches it with additional data:

// Input: decoded JWT payload
// Output: enriched session object
{
  id: 'usr_123',
  email: 'alice@example.com',
  org_ids: ['org_1', 'org_2'],
  current_org_id: 'org_1',
  role: 'editor',
}

User not found returns 401 Unauthorized.

5. Role Injection

The engine looks up user.role in the roles config and resolves the list of active permission slugs:

// user.role = 'editor'
// roles.editor = ['view_own_orders', 'edit_org_orders', 'create_orders']
// → Active permissions: view_own_orders, edit_org_orders, create_orders

6. Permission Check

The engine parses the incoming SQL and evaluates each active permission against it:

1. Table match — Does any permission cover the tables in the SQL?
2. Operation match — Does that permission allow the detected operation (SELECT/INSERT/UPDATE/DELETE)?
3. Column match — Are the referenced columns in the allowed list?
4. Filter injection — Inject WHERE clauses from the permission's filter into the SQL
5. FK traversal — Resolve relationship paths in filters to subqueries
6. Check validation — For writes, validate parameter values against check rules
7. Preset injection — For writes, inject preset values into the SQL

Query limits are also enforced at this stage:

limits: {
  maxLimit: 10_000,       // Maximum rows per query
  maxIncludeDepth: 3,     // Maximum JOIN depth
  maxFilterDepth: 5,      // Maximum nested filter depth
}

If any check fails, returns 403 Forbidden.

6a. Middleware (before next)

If the matching permission defines a middleware function, it runs now with destructured parameters (user, db, table, operation, columns, query, input, filter) and a next() function. Before calling next(), the middleware can:

• Throw to reject the request with 403 Forbidden
• Pass overrides to next({ filter, input, columns, db }) to modify the query
• Run queries via db to look up related data
• Wrap in db.transaction() for atomic operations

See Middleware for details.

7. DuckDB Execution

The permission-filtered SQL is executed against DuckDB, which routes the query to the appropriate attached database (Postgres, MySQL, etc.):

duckdb: {
  queryTimeout: 30_000,  // Kill after 30 seconds
}

Timeout returns 408 Request Timeout.

7a. Middleware (after next)

After next() returns the query results, the middleware can:

• Transform rows — redact fields, add computed columns, filter results
• Run side effects — write audit entries, trigger notifications
• Return rows unchanged — pass through when no transformation is needed

See Middleware for details.

8. Audit Log

If audit logging is enabled, the engine records:

• User ID, role, IP address
• Table, operation
• SQL query and parameters (if configured)
• Execution duration
• Success or error status

9. Response

The query results are returned as JSON:

{
  "data": [
    { "id": 1, "amount": 500, "status": "active" },
    { "id": 2, "amount": 1200, "status": "draft" }
  ],
  "count": 2
}

Actions Pipeline

Requests to POST /actions/{actionName} follow a shorter pipeline — no SQL parsing or permission filtering, just authentication and role checks:

  POST /actions/incrementStock + Bearer JWT + { productId, amount }
    │
    ▼
  1. Rate Limiting
    │
    ▼
  2. JWT Extraction + Session Resolution
    │
    ▼
  3. Role check — is 'action_incrementStock' in the user's role array?
    │
    ▼
  4. Input validation — parse input against Zod schema (400 if invalid)
    │
    ▼
  5. Execute action function — run({ user, db }, validatedInput)
    │
    ▼
  6. Audit Log + Response

See Actions for details.

Error Responses