How to Configure Vercel AI SDK ClickHouse Prevent Delete

Published
Length
3 min read

Luis Felipe Dias

All posts

To set up a vercel ai sdk clickhouse prevent delete workflow, restrict the database user to read permissions only. You also must withhold mutation rights from the database role that your app uses. Many teams build AI tools that query analytics tables directly from a backend server. Developers often reuse a broad admin account during early testing to save time. When a model generates a bad query, broad rights allow accidental data loss. A single poor prompt can wipe out millions of rows in second

Your coding agents run with your access.

Claude Code reaches prod with a developer's credentials, and approval prompts get clicked through. hoop.dev checks every query, masks sensitive data, and holds risky changes for a human.

To set up a vercel ai sdk clickhouse prevent delete workflow, restrict the database user to read permissions only. You also must withhold mutation rights from the database role that your app uses. Many teams build AI tools that query analytics tables directly from a backend server. Developers often reuse a broad admin account during early testing to save time. When a model generates a bad query, broad rights allow accidental data loss. A single poor prompt can wipe out millions of rows in seconds.

Setting up a vercel ai sdk clickhouse prevent delete role

Your first defense happens inside the database permission system itself. ClickHouse manages users, roles, row policies, settings profiles, and quotas as access entities. You can create and manage all of these entities with SQL statements. To turn on SQL-driven access control, you first set a storage directory on the server. You also need at least one initial account with access management turned on. Once active, an admin can create a restricted user specifically for the AI tool. Creating a user requires the dedicated create user privilege on the admin account. Remember that a user can only grant the privileges that they hold themselves.

During user creation, you define the login method and allowed client hosts. You also assign default roles and specific per-user settings for that AI account. To block deletes, grant only select rights to the role your app uses. You can even grant select rights on specific columns in a table. If the AI user queries ungranted columns, the database refuses the request. A wildcard query across all columns will also fail and return no data. It only succeeds if the table holds just the granted columns. Recent database versions also let you grant select rights by table name prefix. By omitting delete and drop grants, the database rejects destructive statements from the model.

What native database roles solve and what they miss

Native database roles stop an AI tool from running delete statements when set up well. They define who can log in and which tables that account can read. Still, static database permissions still leave several operational gaps open in production. First, app servers must hold static database keys in memory to open sessions. If an attacker steals those keys, they gain permanent read access to your tables. Second, teams sometimes need AI agents to insert summary rows while blocking deletes. Managing granular roles across dozens of microservices quickly becomes hard to track.

Third, database grants only check who logs in at the start of a session. They do not inspect what individual queries do in flight before reaching the server. Even with column grants, allowed columns might hold sensitive text inside free-form fields. When an AI model reads those rows, raw emails or phone numbers enter the prompt. Card numbers and national IDs can also flow back to external model providers. Database access logs only show that the shared app account ran a query. They do not link each query to the specific human user or AI tool call.

AI Data Exfiltration Prevention + ClickHouse Access Management: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Inspecting AI tool queries in flight

To close these gaps, teams add query checks between the AI app and the database. Instead of trusting static app keys, each tool call passes through a central check. This step reads the generated SQL statement before the database ever sees it. If a prompt injection tricks the model into writing a delete statement, the query stops right there. You can allow safe inserts while blocking drops and deletes on the same table. At the same time, data masking rewrites matched fields like emails and card numbers in results. Every query log records the exact user identity and timestamp for full audit clarity.

Add runtime guardrails and masking beside ClickHouse

Database grants and network rules control who can reach ClickHouse. Even so, an allowed client or agent can send an alter mutation that deletes rows or read raw personal data. In practice, hoop.dev runs beside the server as a sidecar for your ClickHouse and checks native TCP queries with no change to your drivers.

With hoop.dev ClickHouse support, guardrails refuse banned queries before they run, and data masking rewrites matched string columns in results. The audit trail logs which entity types were masked and how many without storing raw values. Furthermore, hoop.dev holds SOC 2 Type II and is open source on GitHub.

Frequently asked questions

How do I stop an AI agent from deleting ClickHouse rows?

Create a dedicated database user with SQL-driven access control and grant only select privileges. Never assign delete, alter, or drop permissions to the role used by your AI app.

What happens if the AI runs a wildcard select on restricted columns?

ClickHouse refuses a wildcard select query if the user only has rights on specific columns. The query returns no data unless the table contains only those exact granted columns.

Why are read-only database roles not enough for AI apps?

Read-only roles cannot rewrite sensitive fields like phone numbers or national IDs inside query results. They also require storing static database keys inside your app server settings.

The query runs. The emails stay hidden.