Integrity Failures in Chi with Cockroachdb

An integrity failure in the context of a Chi API service using CockroachDB occurs when an attacker can alter, reorder, or partially apply database transactions in a way that violates application-level consistency rules. Because CockroachDB provides strong ACID guarantees at the SQL layer, integrity issues typically arise from how Chi routes requests, constructs SQL statements, or handles errors and retries rather than from the database engine itself.

Chi is a composable, data-driven routing library for Clojure, and its typical pattern of passing request contexts and parameters into database queries can expose integrity risks if values are used to dynamically select schema objects (e.g., table names) or to build SQL without validation. For example, using a request parameter to determine a target table or index name—such as a tenant identifier or a time-based suffix—without strict allowlisting enables BOLA/IDOR and can lead to cross-tenant reads or writes, effectively bypassing row-level safety controls.

Another common pattern is conditional updates that rely on in-memory checks rather than atomic SQL constraints. If Chi handlers perform a read, validate, then write sequence across multiple HTTP handlers or background jobs, an attacker can race between the read and the write to violate uniqueness constraints or business rules. CockroachDB will reject conflicting serializable transactions, but the application may not handle serialization errors correctly, leading to lost updates or inconsistent state being returned to the user.

Middleware concerns also contribute. If Chi pipelines do not enforce idempotency keys for mutating requests, retries from clients or load balancers may cause duplicate writes that break uniqueness constraints (for example, duplicate email entries in a user table). Similarly, insufficient input validation allows malformed or oversized payloads to pass into JSONB columns, which can corrupt structured data expectations and weaken application-level invariants.

Because middleBrick scans the unauthenticated attack surface and includes Integrity checks within its 12 parallel security checks, it can surface risky patterns such as dynamic schema references, missing constraint enforcement, and missing idempotency handling. The scan cross-references any OpenAPI/Swagger spec definitions with runtime findings, highlighting areas where parameter-driven SQL construction or weak error handling may undermine data integrity in a Chi and CockroachDB stack.

Remediation focuses on ensuring all database interactions are deterministic, atomic, and validated. Avoid using request-derived values to construct schema or table names. If multi-tenancy is required, use row-level policies and always qualify objects with static, allowlisted names. Prefer upserts and declarative constraints so that CockroachDB enforces uniqueness and referential integrity rather than the application.

Example 1: Safe multi-tenant query with a static table and tenant_id column

(ns myapp.db
  (:require [cheshire.core :as json]
            [next.jdbc :as jdbc]))

(def db-spec {:dbtype "cockroachdb"
              :dbname "appdb"
              :host "localhost"
              :port 26257
              :user "appuser"})

(defn get-user-orders [tenant-id user-id]
  (jdbc/execute! db-spec
    ["SELECT id, total, status FROM tenant_orders WHERE tenant_id = $1 AND user_id = $2 ORDER BY created_at DESC"
     tenant-id user-id]))

Example 2: Conditional upsert to prevent duplicate emails (idempotent operation)

(defn upsert-user [email display-name]
  (jdbc/execute-one! db-spec
    ["INSERT INTO users (email, display_name, created_at)
      VALUES ($1, $2, now())
      ON CONFLICT (email) DO UPDATE SET display_name = EXCLUDED.display_name"
     email display-name]))

Example 3: Handling CockroachDB serializable transaction retries in Chi

(ns myapp.handler
  (:require [com.climate.claypoole :as cp]
            [next.jdbc :as jdbc]
            [ring.util.response :as resp]))

(defn transfer-funds [tenant-id from-account to-account amount]
  (jdbc/with-transaction [conn db-spec]
    (let [balance (jdbc/get-by-id conn :accounts from-account {:builder-fn :as-unqualified-keyword})]
      (when (< (:balance balance) amount)
        (throw (ex-info "Insufficient funds" {:status 400})))
      (jdbc/update! conn :accounts {:balance (- (:balance balance) amount)} {:where ["id = ?" from-account]})
      (jdbc/update! conn :accounts {:balance (+ (:balance (jdbc/get-by-id conn :accounts to-account {:builder-fn :as-unqualified-keyword})) amount)} {:where ["id = ?" to-account]})
      {:status 200})))

In the transfer example, wrap the logic in a retry loop that respects CockroachDB’s serializable transaction errors (error code 40001). Use an idempotency key stored in a separate table keyed by request fingerprint to avoid double-charges or double-transfers on retries. Validate all incoming IDs against the tenant context to ensure BOLA/IDOR is not possible, and prefer parameterized SQL over string concatenation to prevent injection and ensure plan stability.

middleBrick can validate these patterns by scanning your Chi endpoints and flagging dynamic SQL construction, missing idempotency safeguards, and inconsistent error handling for serialization failures. With the CLI or Web Dashboard you can track these findings over time and, if using the Pro plan, enforce thresholds in CI/CD before merging changes that would weaken data integrity.