Confidential by Construction: AI Assistants over One Company Database
How four departments of Stackademy got AI assistants over the same database, without any assistant able to read another department’s records, and why the database, not the prompt, is where that is enforced.
At a glance
Organization |
Stackademy: HR, Accounting, Course Content and Admissions, whose records share one MariaDB database of 25 tables. |
Challenge |
Each department wants an assistant that answers questions about its own records in plain language. Salaries, students’ payments and the general ledger sit side by side in one database, and an assistant writes no queries that anyone reviews before they run. |
Solution |
One read-only database user per department, granted exactly its tables; a connection, a set of fixed SQL tools, a budget and an assistant per department, all in the department’s own account; a company-wide PII guardrail; and a check, on every load, that each user can read its tables and nothing else. |
Smarter features |
SqlConnection, Secret, SqlPlugin, LLMClient, Guardrail, Budget, account sharing (ADR-036) |
Outcome |
Four assistants, Harper, Ledger, Reel and Ada, with 11 SQL tools between them; only one database user of seven may read compensation; every department’s AI spending capped at $150 a month. |
Executive summary
The fastest way to give a department an AI assistant over a database is to hand the assistant a connection and let the model write SQL. It is also the way to let any member of any department ask for the chief executive’s salary, and get it, the first time someone phrases the question well. The model is not the place to enforce confidentiality: it can be persuaded, it can be mistaken, and its instructions are not a control that an auditor can test.
Stackademy enforces it in three layers, each of which holds on its own. The database gives each department a read-only user that may read only that department’s tables, so that no query, however it is written, returns another department’s records. Smarter’s accounts keep each department’s connection, and the Secret that holds its password, in the department’s own account, where only its members may use them. And the assistants do not write SQL: each has a few SqlPlugins, fixed, reviewed queries that take parameters, so what an assistant can ask is known in advance.
Two further controls apply to everything that reaches a model: a guardrail that redacts credit card and social security numbers from every message, shared by IT with the whole company, and a monthly budget per department, which blocks the department’s assistant and tools when it is spent.
The result is four assistants over one database, whose confidentiality can be proven by a query, SHOW GRANTS,
that runs every time the database is loaded.
Background
Stackademy keeps its back office in one MariaDB database, stackademy, because its records are connected: an
enrollment belongs to a student and a cohort, a payment to an enrollment, a ledger line to a department, an
instructor’s salary to an employee. A generator, scripts/generate_backoffice.py, writes the whole database
from a fixed random seed: 40 employees, 320 students, 725 enrollments in 2025, 2,502 general ledger lines and the
departments’ monthly budgets. Every question about it has the same answer every time, which makes it possible to
check an assistant’s answers, the subject of When the Numbers Don’t Add Up: Assuring the Accuracy of AI Financial Reporting.
The departments’ needs overlap. HR needs the directory of employees, and compensation. Accounting needs the directory too, to allocate payroll, and enrollments and payments, to recognize revenue, but not compensation records, from which it would learn each person’s salary. Admissions needs students, enrollments and corporate contracts. Course Content needs the catalogue and its production schedule, and the directory, to assign work.
The challenge
The organization set four requirements:
No department reads another’s confidential records, whatever its members ask its assistant, and whatever the assistant does.
The control must be testable. “The assistant’s instructions forbid it” is not a control; a grant that a query can check is.
The credentials stay with the department. A department’s database password must not be usable outside it, even by mistake.
AI spending is bounded per department, so that one department’s experiments can’t spend another’s money.
And one fact made them hard: the departments share tables. employees is read by HR, Accounting and Course
Content; enrollments by Admissions and Accounting. So “one schema per department” would have meant copying
data, and keeping the copies in step.
Options considered
Option |
For |
Against |
|---|---|---|
One connection; the model writes SQL; the system prompt lists what it may not read |
Quick to build; answers any question |
Confidentiality rests on the model’s compliance; a reworded question, or a mistake, reads salaries |
A database per department, with copies of the shared tables |
Strong isolation |
Copies drift; reports across departments disagree; four times the data to secure |
Row-level security, or views per department |
Fine-grained |
MariaDB has no row-level security; a view per question multiplies objects without adding much over table grants |
A read-only database user per department, granted its tables; fixed SQL tools (chosen) |
Enforced by the database, checked by a query; shared tables are shared by granting them twice |
Confidentiality is per table: a column that one department may read and another may not must live in a table of its own, as compensation does |
The chosen design accepts the last point and designs the schema around it: salaries are not a column of
employees but rows of compensation, so that the directory can be shared and the pay cannot.
The solution
Grants in the database. The generator writes the grants along with the data. Each department’s user is created
with every privilege revoked, then granted SELECT on its tables, one by one:
CREATE USER IF NOT EXISTS 'stackademy_hr'@'%' IDENTIFIED BY 'stackademy_hr';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'stackademy_hr'@'%';
GRANT SELECT ON `stackademy`.`departments` TO 'stackademy_hr'@'%';
GRANT SELECT ON `stackademy`.`employees` TO 'stackademy_hr'@'%';
GRANT SELECT ON `stackademy`.`compensation` TO 'stackademy_hr'@'%';
GRANT SELECT ON `stackademy`.`pto_balances` TO 'stackademy_hr'@'%';
GRANT SELECT ON `stackademy`.`pto_requests` TO 'stackademy_hr'@'%';
GRANT SELECT ON `stackademy`.`job_requisitions` TO 'stackademy_hr'@'%';
Exhibit 1. What each department’s database user may read.
The executives’ user is the extreme case. It reads no table at all, only five views of totals, written SQL
SECURITY DEFINER so that they read the tables with their definer’s rights and return only aggregates, such as a
department’s headcount and payroll, never a person’s salary. That design, and why it mattered, is the subject of
The Board Pack: Orchestrating Assistants Across Organizational Boundaries.
Credentials in the department’s account. Each department has a SqlConnection, which names its database user, and a Secret, which holds the password. The connection names the Secret; the password never appears in a manifest:
# departments/hr/connections/stackademy-hr-db.yaml
apiVersion: smarter.sh/v1
kind: SqlConnection
metadata:
name: stackademy_hr_db
version: 1.0.0
spec:
connection:
dbEngine: django.db.backends.mysql
hostname: smarter-mariadb
port: 3306
database: stackademy
username: stackademy_hr
password: stackademy_hr_db_password # the name of a Secret, not the password itself
authenticationMethod: tcpip
useSsl: false
Both live in Stackademy HR’s account, which shares with no one (see Organizing for AI: Modeling a Company as a Tree of Accounts). The Head of IT
manages them, as administrator of HR’s account, but they are HR’s: a member of Accounting can’t name
stackademy_hr_db in a plugin, because to them it doesn’t exist.
Fixed queries, not generated ones. Each assistant’s tools are SqlPlugins: a reviewed query, its parameters, and a limit on the rows it returns. The model chooses a tool and fills in its parameters; it does not write SQL.
# departments/hr/plugins/stackademy-hr-headcount.yaml (abridged)
kind: SqlPlugin
metadata:
name: stackademy_hr_headcount
description: 'Headcount and pay by department: how many people each department has, and their average,
lowest and highest salary.'
spec:
connection: stackademy_hr_db
sqlData:
limit: 25
sqlQuery: |
SELECT d.name AS department, COUNT(e.employee_id) AS headcount,
ROUND(AVG(c.annual_salary), 0) AS average_salary, ...
FROM employees e
JOIN departments d ON d.department_id = e.department_id
JOIN compensation c ON c.employee_id = e.employee_id AND ...
WHERE (d.name LIKE CONCAT('%', {department}, '%') OR {department} IS NULL)
GROUP BY d.name
parameters:
- name: department
type: string
required: false
Exhibit 2. How HR’s assistant reaches HR’s records, and nothing else.
A shared guardrail and a budget per department. Every department’s assistant lists
stackademy_pii_redaction, IT’s guardrail, which redacts credit card and US social security numbers from each
message before it reaches the model. IT’s account shares it with the company, so each department uses one
definition, which IT maintains. And each department has a Budget, stackademy_<department>_ai, of $150 a month
and $1,800 in all, over its assistant and its plugins, which blocks them when it is spent, with a message that says
whom to ask.
# budgets/stackademy-hr-ai.yaml (abridged)
kind: Budget
metadata:
name: stackademy_hr_ai
spec:
config:
unit: cost
period: month
periodicLimit: 150.0
absoluteLimit: 1800.0
action: block
warningThreshold: 80
message: Stackademy HR has used this month's AI budget. Ask Stackademy's CFO to raise it.
resources:
- kind: LLMClient
name: stackademy_hr_assistant
accountNumber: 2718-2818-0002
- kind: SqlPlugin
name: stackademy_hr_headcount
accountNumber: 2718-2818-0002
Implementation
The grants are data, not configuration. The generator holds one table of department users and their tables,
GRANTS, and writes from it both the SQL above and scripts/backoffice-grants.txt, a plain list of each user
and its tables. The script that loads the database, scripts/create-course-database.sh, then checks the result
against that list, user by user, with SHOW GRANTS, and fails if any user may read a table it isn’t listed for,
or can’t read one it is:
checked: stackademy_hr may read its 6 tables, and nothing else
checked: stackademy_accounting may read its 15 tables, and nothing else
checked: stackademy_executive may read its 5 tables, and nothing else
Two details matter more than they appear to. Each user’s privileges are revoked before they are granted, so that a user that an earlier version of the database let read everything is narrowed on the next load rather than keeping what it had. And the former course database, with users that could read all of it, is dropped by the same script, which checks that it and its users are gone.
The work is two commits to the Stackademy repository, cc6099e (the back office, with a read-only user per
department) and de78c18 (four departments’ assistants, their SQL tools and budgets), and the tests of the
generator, 4ac6392, which check among other things that no user but HR’s is granted compensation.
Results
Measure |
Result |
|---|---|
Database users, and tables each may read |
7 users. HR 6 tables, Accounting 15, Admissions 8, Course Content 8, the catalogue 4, its proxy 4, the executives 5 views and no table |
Users that may read compensation |
1 of 7: HR’s |
Where that is proven |
|
Assistants, and their SQL tools |
4 assistants with 11 tools: HR 3, Accounting 4, Course Content 2, Admissions 2; and the executives’ 6 tools over the board views |
SQL written by a model |
None: every query is a reviewed SqlPlugin |
AI spending per department |
At most $150 a month and $1,800 in all; the assistant and its tools are blocked when it is spent |
Lessons for practitioners
Enforce confidentiality below the model. Instructions to a model are a request, not a control. Put the boundary where the data is, and make it something a query can check.
Design the schema for the grants. Table grants are simple and auditable, but they are per table. A field that one audience may read and another may not, such as pay, belongs in a table of its own.
Revoke, then grant. Grants that are only ever added accumulate. Rebuild them from the declared list each time, and check the result against it.
Fixed tools before free SQL. A tool that runs a reviewed query with parameters is less flexible than a model that writes SQL, and much easier to trust. Add free SQL, if ever, over a user whose grants already make the worst query harmless.
Keep credentials with their audience. A connection lives in the account whose members may use it, even when someone in another department manages it.
Cap spending where it is decided. A budget per department makes the cost of each assistant visible to the people who use it, and stops one department’s experiment from spending another’s money.
Teaching notes
Discussion questions
Accounting may read
employeesbut notcompensation, yet it posts payroll to the ledger, by department. Can Accounting deduce an individual’s salary from the ledger? In a department of one? What would you change?A colleague proposes letting the assistants write SQL, “but only SELECT, and only over their own user”. What does that add, and what does it risk, given the grants? Which department would you try it in first?
The guardrail redacts card and social security numbers from messages to the model, but not from the tools’ results. Should it? What would it cost?
Each department has the same budget. How would you set them instead, and who should decide?
Exercises
On a platform with Stackademy loaded, connect to MariaDB as
stackademy_accountingand trySELECT * FROM compensation. Record the error, then runSHOW GRANTSand compare it withscripts/backoffice-grants.txt.Sign in as
quinn_sandoval(HR) and ask Harper for the average salary in Course Content. Then sign in askofi_sandoval(Accounting) and ask Ledger the same. Explain each answer.Write a SqlPlugin for Admissions that lists a cohort’s enrollments by status, with the cohort as a parameter. Apply it in Admissions’ account, and show that a member of HR can’t use it.
Lower HR’s budget to $1 in a fork of the repository, load it, and use Harper until the budget blocks it. What does the user see, and what does the CFO see?
Sources
The Stackademy repository:
scripts/generate_backoffice.py(GRANTS,BOARD_VIEWS),scripts/backoffice-grants.txt,scripts/create-course-database.sh,departments/,budgets/: https://github.com/smarter-sh/stackademyStackademy commits
cc6099e,de78c18and4ac6392