5 · Security

Three threat models matter here, and they are genuinely different:

1. The model writes destructive SQL — accidentally or through prompt injection in the data.
2. A tenant reads another tenant’s data.
3. Secrets leak — API keys, database credentials.

1 · Generated SQL is untrusted input

src/pipeline/stages/sql_validate.py, six layers in order:

Layer 1 — Parse

statements = sqlglot.parse(sql, dialect=dialect)

parse, not parse_one — the plural form is what makes layer 2 possible.

Layer 2 — Single statement only

if len(statements) > 1:
    raise SQLValidationError(f"Only a single statement is allowed. Got {len(statements)}.")

Blocks SELECT 1; DROP TABLE users.

Layer 3 — Root-shape allow-list

ALLOWED_ROOT_TYPES = (exp.Select, exp.Subquery, exp.Union, exp.Except, exp.Intersect)
 
if not isinstance(parsed, ALLOWED_ROOT_TYPES):
    raise SQLValidationError(f"Only read-only SELECT statements are allowed. Got: {type(parsed).__name__}")

An allow-list of safe shapes, not a deny-list of bad words. Deny-lists are a losing game — you are enumerating an infinite set. exp.Select also covers WITH … SELECT, because sqlglot attaches the CTE to the Select.

Layer 4 — Walk the whole tree

BLOCKED_NODE_TYPES = (exp.Insert, exp.Update, exp.Delete, exp.Drop,
                      exp.Create, exp.TruncateTable, exp.AlterTable,
                      exp.Command)   # catch-all: GRANT, CALL, …
 
for node in parsed.walk():
    if isinstance(node, BLOCKED_NODE_TYPES):
        raise SQLValidationError(...)

This is the layer that earns its keep. PostgreSQL supports data-modifying CTEs:

WITH evil AS (DELETE FROM orders RETURNING *)
SELECT * FROM evil

The root node is a perfectly innocent Select. Layer 3 passes it. Only walking the tree catches the Delete hiding inside.

Layer 5 — Keyword sweep, on literal-stripped SQL

_LITERAL_PATTERN = re.compile(
    r"'(?:[^']|'')*'"        # 'string literals'
    r"|\"(?:[^\"]|\"\")*\""  # "quoted identifiers"
    r"|--[^\n]*"             # -- line comments
    r"|/\*.*?\*/",           # /* block comments */
    re.DOTALL,
)
stripped = _LITERAL_PATTERN.sub(" ", sql)

Stripping first is essential. Without it, this legitimate query is rejected:

SELECT * FROM stores WHERE city = 'Alter Markt'

because Alter looks like ALTER. The pattern deliberately excludes words that are legal in a read-only query — REPLACE and COPY are scalar functions in several dialects; SET and MERGE show up as column names. Those are already covered by layer 4, which is the authoritative check.

Layer 6 — Transpile

transpiled = sqlglot.transpile(sql, read=dialect, write=dialect)

Normalises quoting and function names for the target dialect.

The layer outside the code

Connect Retail Mind with a read-only database user. Every layer above is application-level and shares one failure mode: a bug in the validator. A read-only grant is enforced by the database itself and fails closed. Recommending this is what turns a good answer into a complete one.

2 · Multi-tenancy

Every data source and provider config carries an owner_key_id:

The NULL case exists so the public demo dataset can be queried by the demo key embedded in the frontend, without that key also being able to delete it.

Filter in the query, not in Python

# src/services/access.py
def filter_readable(stmt, model, key):
    if key.is_admin:
        return stmt
    return stmt.where(or_(model.owner_key_id == key.id, model.owner_key_id.is_(None)))

404, not 403

def require_readable(resource, key, what="Resource"):
    if resource is None or not can_read(resource, key):
        raise HTTPException(404, detail=f"{what} not found.")

Returning 403 for a resource that exists but isn’t yours confirms it exists. An attacker can enumerate IDs. Missing and forbidden are both 404.

Writes are different — there, 403 is correct and informative:

def require_writable(resource, key, what="Resource"):
    if resource is None or not can_read(resource, key):
        raise HTTPException(404, detail=f"{what} not found.")
    if not can_write(resource, key):
        raise HTTPException(403, detail=f"{what} is shared and can only be modified by an admin key.")

The caller can already see the shared resource, so its existence is not a secret — only the reason for refusal.

Creating shared resources is admin-only

if body.is_shared and not current_key.is_admin:
    raise HTTPException(403, detail="Only an admin key may create a shared data source.")

Otherwise any caller could publish a source — or an AI provider, with the vendor bill attached — as the default for every tenant.

3 · Secrets

API keys

Stored as bcrypt hashes; the raw key is returned exactly once at creation and is unrecoverable. Lookup is by a short prefix, then bcrypt-verify the candidates — you cannot index a bcrypt hash.

The bootstrap problem

POST /v1/auth/keys requires an admin key. A fresh deployment has zero keys. Chicken and egg:

configured = settings.admin_bootstrap_token
if x_admin_token and configured and secrets.compare_digest(x_admin_token, configured):
    return None                      # authorised by bootstrap token
if credentials is not None:
    api_key = await AuthService(db).verify_key(credentials.credentials)
    if api_key and api_key.is_admin:
        return api_key
raise HTTPException(403, ...)

Two things to notice:

• secrets.compare_digest, not ==. Constant-time comparison; a normal string compare short-circuits on the first differing byte and leaks the token through timing.
• Before this existed, the endpoint was completely unauthenticated — anyone who could reach the deployment could mint a live key and use every other route.

Config that refuses to start

def validate_production_ready(self) -> list[str]:
    problems = []
    if not self.is_production:
        return problems
    if not self.encryption_key:
        problems.append("ENCRYPTION_KEY is not set — data source credentials cannot be encrypted.")
    if self.secret_key in ("", "dev-secret"):
        problems.append("SECRET_KEY is still the development default.")
    if any(o == "*" for o in self.api_cors_origins):
        problems.append("API_CORS_ORIGINS is '*' while credentials are allowed.")
    return problems

Called in the lifespan hook; a production deployment with development secrets fails to boot rather than serving traffic insecurely. Failing loudly at startup beats failing silently for months.

Public-by-design secrets

NEXT_PUBLIC_DEMO_API_KEY is compiled into the browser bundle — anyone can read it. That is acceptable only because it is a non-admin key pointing at a shared, read-only-to-it source. The ownership model is what makes an intentionally public key safe.

Rate limiting

def rate_limit_key(request: Request) -> str:
    auth = request.headers.get("Authorization", "")
    if auth.lower().startswith("bearer "):
        token = auth[7:].strip()
        if token:
            return "key:" + hashlib.sha256(token.encode()).hexdigest()[:32]
    return "ip:" + client_ip(request)

Two bugs fixed here:

1. Keyed on client IP. Behind Render/Vercel/nginx every request arrives from the proxy, so all callers shared one bucket.
2. In-process storage. Each worker kept its own counters, so the effective limit was N× the configured value and reset on every deploy.

Now keyed on the API key (hashed, so it never reaches Redis keys or logs in plaintext), stored in Redis.

X-Forwarded-For is trusted only when TRUST_PROXY_HEADERS=true:

if settings.trust_proxy_headers:
    forwarded = request.headers.get("X-Forwarded-For")
    if forwarded:
        return forwarded.split(",")[0].strip()

The header is trivially spoofable. On a directly-exposed deployment, trusting it lets anyone bypass IP limits by rotating the value. Behind a known proxy it is safe precisely because the proxy overwrites it.

Degrading open

swallow_errors=True,

slowapi’s Redis storage connects lazily, so a Redis outage after boot turned every request into a 500. Losing rate limiting is bad; losing the entire API is worse. This is a deliberate availability-over-enforcement trade, and it matches what the boot-time fallback already did.

What is missing — be honest

• No per-key spend cap. Tokens are recorded; nothing stops an expensive key.
• No audit trail for credential access beyond query logs.
• No secret rotation flow — changing ENCRYPTION_KEY requires re-encrypting stored credentials.
• No row-level security in the user’s own database — Retail Mind queries with whatever grants the stored credential has, which is exactly why a read-only user is recommended.