How to Configure Vercel AI SDK ClickHouse Data Masking

Published
Length
3 min read

Luis Felipe Dias

All posts

To set up vercel ai sdk clickhouse data masking, restrict column reads and rewrite sensitive values before they reach the model. You can grant specific column permissions in the database or scrub fields like emails in your app code. Many teams start by giving their AI tool a broad read role on analytics tables. When a model runs a wide query, that broad role returns raw phone numbers and card numbers in the output. How to set up vercel ai sdk clickhouse data masking In a standard setup, the V

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 vercel ai sdk clickhouse data masking, restrict column reads and rewrite sensitive values before they reach the model. You can grant specific column permissions in the database or scrub fields like emails in your app code. Many teams start by giving their AI tool a broad read role on analytics tables. When a model runs a wide query, that broad role returns raw phone numbers and card numbers in the output.

How to set up vercel ai sdk clickhouse data masking

In a standard setup, the Vercel AI SDK uses tool calls to query ClickHouse tables. The model writes a SQL statement or fills in query inputs based on a user prompt. Your app backend receives that tool call and opens a database session. It runs the query and sends the rows back to the model context.

ClickHouse manages users, roles, row policies, settings profiles, and quotas as SQL access entities. To turn on SQL access control, an admin sets the storage directory on the server. At least one admin account must hold access management rights to create new users. Remember that a database user can only grant privileges that they already hold themselves.

Once access control is active, you can create a dedicated user for your AI app. The user creation statement sets the login method, allowed client hosts, default roles, and settings. Next, you can limit which columns the AI user can read. ClickHouse lets admins grant read permissions on exact columns in a table.

If you grant read rights on only safe columns, the user can query only those fields. Queries that ask for restricted columns fail right away. A full wildcard query also fails and returns no data unless every column is allowed. Since version 24.10, admins can also use a trailing asterisk to grant rights by table name prefix.

What native column grants solve and what they leave open

Native column grants block an AI tool from reading forbidden columns at login time. They work well when a model never needs to touch a sensitive column at all. You can also build views that replace emails or national IDs with static strings. Then you grant the AI role read rights on the view instead of the base table.

Still, static grants and views create real friction for growing engineering teams. Every time a schema changes, someone must update the column grants and rebuild the views. Wildcard queries break when one column in a wide table is off limits. This breaks many AI data tools that inspect schemas and run broad read queries.

AI Data Exfiltration Prevention + Data Masking (Static): architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Native grants also decide who logs in, not what individual queries return in flight. If an app needs a column for a join or filter, the database user must have read rights. Once granted, the query output can carry raw card numbers or phone numbers back to the SDK. Static database roles cannot rewrite matched fields in query results on the fly without extra view logic.

Managing sensitive fields across AI tool calls

Teams often try to fix this gap by writing custom scrubbing code inside each SDK tool handler. Developers write regular expressions in the app to spot emails and phone numbers before returning results. This approach scatters masking rules across many app repos and serverless functions. When a developer adds a new query tool and forgets the scrubber, raw data goes straight to the model.

A cleaner pattern separates field masking from both the app code and the raw table schema. Instead of maintaining dozens of restricted views, teams inspect and rewrite query results as they return. Masking rewrites matched fields like card numbers and national IDs in the results before the SDK reads them. This keeps analytics queries working while keeping sensitive strings out of model prompts.

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

What happens when the AI SDK runs a wildcard query on a restricted ClickHouse table?

ClickHouse blocks a wildcard query if the user lacks read rights on every single column in that table. The database refuses the query and returns no data at all to the app.

Can ClickHouse grant column permissions across multiple tables at once?

Yes, starting in version 24.10, admins can use a trailing asterisk to grant permissions by table name prefix. You can still pair these grants with specific column names to limit what the AI user reads.

Which fields should teams mask before sending ClickHouse rows to an AI model?

Teams should mask structured identifiers like emails, phone numbers, card numbers, and national IDs. Masking rewrites these matched fields in the query results so the AI tool receives safe placeholder text.

The query runs. The emails stay hidden.