Doors, not keys: read-only AI access to production data

The problem

RVM CAD’s CEO wanted an operations assistant: something that pulls the job portal’s numbers every day, compares them with targets, follows up on what’s slipping and reports weekly. The portal already computed every metric. What was missing was a safe way for an external AI client to read them.

Days later a second request arrived on the company’s operations platform: the CEO needed to answer “who changed what, when, and why” across admissions, fees, HR and attendance. The evidence existed but was scattered across three different record-keeping mechanisms, none of which an assistant could safely query.

The shortcut in both cases is to hand the assistant a database connection or an API key. That gives a language model far more power than the job needs, attributes every query to a shared credential, and makes “the model returned a wrong number” indistinguishable from “the data was incomplete”.

Constraints

• Authenticated, not open. The brief ruled out an unauthenticated endpoint; departmental data was involved.
• Aggregates only. The assistant could see totals and rosters, never candidate-level personal data.
• Bounded responses. Answers within about ten seconds, results paged at 500 rows.
• No partial data. If a number could not be computed completely, the tool had to say so rather than return what it had.
• Read-only in version one. No tool could change anything.
• Live systems. On the operations platform, the backend uses a privileged database role, sessions are stateless and long-lived, and migrations are applied by hand after deploys. The CEO had to be the only person able to read the audit trail.

What I built

One-time connection

1. AI clientExternal assistant
2. OAuth 2.1 + PKCEAdmin approves; codes and refresh tokens stored only as hashes
3. Scoped tokenBound to the connector URL, acts as a read-only bot account

Every tool call

1. Identity re-checkBot account still active and still read-only?
2. Bounded toolDate range ≤ 400 days, 500-row pages, time budget, rate limit
3. Aggregated queryNo personal data; complete result or an explicit "unavailable"
4. Production database

Audit: every call is written to the activity log with its arguments, outcome and duration. On the operations platform, queries of the audit trail are themselves audited.

A separate identity for the assistant. Each connector acts as a dedicated read-only principal, never as a logged-in person’s browser session. On every call the server reloads that account and checks its role and active flag, so switching it off cuts access immediately rather than when a token expires.

OAuth 2.1 with PKCE, hardened. Authorisation codes and refresh tokens are stored only as hashes. Refresh tokens rotate, and reuse of an old one is treated as theft: every live token for that client is revoked. Access tokens are bound to the connector’s own URL. I deliberately skipped dynamic client registration — one configured client is a smaller attack surface than an open registration endpoint.

A tool contract designed for a model as the caller. Every tool takes a date range in Indian Standard Time, defaults to the last seven days and caps at 400. Every response states when it was generated and the range it actually used, so the model can quote facts instead of assuming them. Large results page with a cursor. If any source cannot be read, the tool returns an explicit unavailable error rather than partial numbers. Each tool is annotated as read-only and idempotent, runs inside a time budget, and is rate-limited.

An audit trail the assistant can investigate, but not escape. On the operations platform I first built the trail itself: append-only events that record the actor and their role at the time, the route, the outcome, an allow-listed before-and-after, and redaction by field name. Queries of the trail are recorded too. The eight investigation tools are registered only for a client bound to an active CEO account, so an unregistered tool cannot be called by name. Results carry a coverage note (what the window cannot include) and mark any human-typed text as untrusted, so a model reading them is told which parts are data and which are someone’s words. There is no raw-SQL tool.

Decisions and trade-offs

Decision	Why	Cost
Dedicated bot identity, rechecked on every call	Deactivation works instantly; every query is attributable	One database read per tool call
One static OAuth client, no dynamic registration	Smaller attack surface	Rotating the client secret disconnects the assistant until it is re-authorised
Refuse rather than return partial data	A confident wrong number is worse than “unavailable”	The assistant sometimes has to say it cannot answer
Audit writes never block a request	An audit outage must not take down admissions or fees	Coverage depends on every write path being instrumented; nothing enforces it
Audit tools read the database directly under the bound-principal check	The connector had no credential for the CEO-guarded API	A weaker design than routing through the API; recorded as a known deviation
In-memory rate limiting	The job portal runs as a single container	Needs a shared store before the app scales horizontally

What broke on delivery day

Two production bugs surfaced within hours of the first deploy, and both were fixed the same day:

• The consent redirect pointed at 0.0.0.0. The OAuth flow built its redirect from the incoming request URL, which inside the container is not the public address. Redirects now use the configured issuer. The lesson: production containers do not know their own public URL.
• The consent flow dropped its return path. An admin who had to log in first lost the next parameter and landed on the dashboard instead of the approval screen. One-click connect became a restart.

Testing and status

The job portal connector has 15 dedicated test files and 141 tests covering the authorisation and token flows, PKCE, refresh-token replay, route authentication, date and pagination behaviour, each data tool and the bot’s permissions. The audit layer has unit and integration tests for the service, policy, read API and tools, plus a read-only script that verifies the five audit tables and their columns.

Both connectors are merged. When this was written, production secrets for the job portal connector and the CEO’s acceptance test (“pull the roster and last seven days’ scorecard; it must match the Reports tab”) had not been verified, and the audit trail runs in shadow mode by design.

What this does not prove yet

There are no usage, latency or error-rate figures, and no measurement of time saved. Those need the connectors running in production for a few weeks, with the call logs aggregated. Until then the honest claim is about design and test coverage, not impact.

What I learned

The model is the easy part. The product is the door: who is holding the key, what the room contains, what happens when a shelf is empty, and who can see that the door was opened.