All projects

AI & Data

AI Data Assistant

The design of a reference application for safe LLM access to structured enterprise data: approved tools, authorization before data access, validated answers and full observability.

Status
Design complete
Level
Advanced

Design complete: this page documents the architecture, specification and implementation plan. The reusable implementation has not been published yet; code, templates and demos will be linked here as each milestone ships.

Project overview

Who this is for

  • Engineers who want to see how a real LLM application is structured beyond a chat prompt
  • Engineers building assistants over company data who need security and tenant boundaries
  • Architects evaluating where the model ends and the application's responsibility begins

What this project demonstrates

  • How tool or function calling works, and why the model never gets direct database access
  • How to enforce authorization and tenant isolation before any data is read
  • How to keep a clear boundary between instructions, user input and retrieved data
  • How to validate model output before it is shown or acted on
  • How to log and observe LLM applications without collecting sensitive data carelessly
  • Where caching helps, and where it leaks data between users

Tech stack

  • Python
  • LLM
  • SQL Server
  • Azure

The problem

Connecting a language model to company data is easy to demo and hard to do safely. The usual shortcuts are to give the model a database connection, let it write free-form SQL, or paste whole tables into the prompt. They create the real risks: data leaking across users or tenants, unbounded queries, prompt injection through data, and answers nobody can trace back to a source.

The AI Data Assistant is a reference application for the safer pattern described in Using LLMs with Your Data: Practical Patterns. The model can choose among approved tools, and the application decides what each tool may do, for whom, and with which data.

Architecture

  1. User

    • Web UI or API client
  2. Application

    • API / orchestrationOwns the conversation and tool loop
  3. Auth

    • Authentication
    • Authorization
    • Tenant context

    Trust boundary: resolved before any tool runs.

  4. Approved tools

    • Parameterized queries
    • Metadata lookup
    • Retrieval
  5. Data

    • SQL / data platformRow-level security per tenant
  6. LLM

    • LLMChooses tools, drafts the answer
  7. Response

    • Validated responseSchema, sources and policy checks

Cross-cutting concerns

  • Audit log
  • Tracing
  • Cost limits
  • Evaluation
  • Per-user caching
Intended architecture. Identity is checked before any tool runs, the model sees only bounded results, and every answer is validated and logged.

How it works

  1. 1QuestionFrom an authenticated user
  2. 2Resolve identityUser, roles and tenant
  3. 3Model selects a toolFrom the approved list, with arguments
  4. 4Validate the callSchema, allowed values, row limits
  5. 5Run with user permissionsParameterized query, tenant filter enforced
  6. 6Bounded result to the modelOnly the rows and columns needed
  7. 7Validate the answerFormat, cited sources, policy
  8. 8Respond and logAnswer plus an audit record
Lifecycle of one question. The model proposes tool calls; the application validates them and executes them with the user's permissions.

The model never builds SQL that runs directly. Each tool is a function with a typed signature, for example “sales by region for a date range”. It maps to a parameterized query that the application owns. Tenant and permission filters are added by the application, never by the model, so a cleverly phrased question cannot remove them.

Security boundaries

  • Approved tools only: the model can request one of a fixed list of tools; it has no connection string and cannot run free-form SQL.
  • Authorization before data access: identity, roles and tenant are resolved before any tool runs, and enforced again in the data layer.
  • Structured data, bounded results: tools return only the rows and columns a question needs, with row and size limits.
  • Validation on both sides: tool arguments are validated before execution, and answers are validated before they are shown.
  • Observability without over-collection: every step is traced and audited, without storing full prompts or results by default.

Implementation milestones

Nothing is published yet. The reference application will be built in these milestones:

  1. Tool layer: a small set of typed tools over a sample database, with parameter validation.
  2. Identity and tenancy: authentication, roles and a tenant filter applied in the data layer.
  3. Model integration: the tool-calling loop, with a provider-neutral interface.
  4. Validation: structured output checks and source references in every answer.
  5. Observability: traces of each step, an audit log, token and cost tracking.
  6. Evaluation: a test set of questions with expected tool calls and answers.

Project deliverables

  • A typed tool layer over a sample database, testable without a model
  • Authentication, roles and tenant isolation enforced in the data layer
  • A provider-neutral tool-calling loop
  • Answer validation with source references
  • Traces, an audit log and cost tracking
  • An evaluation set of questions with expected tool calls and answers

How to explore this project

Getting started

Prerequisites

  • Python, basic SQL, and a basic understanding of HTTP APIs
  • Access to an LLM API that supports tool or function calling

Concepts to understand first

Read Using LLMs with Your Data, especially the sections on tools and on security being an application responsibility.

Working through the milestones

The milestones are ordered so that the tools work and are tested before a model is involved. The model is added only once the tools behave correctly on their own, and identity follows, so the same question can be checked against users in different tenants.

Acceptance checks

  • Every tool rejects invalid parameters before any query runs.
  • An attempt to reach another tenant’s data is refused, and the audit log records the attempt.
  • A question that the available tools cannot answer gets an explicit “I cannot answer that” rather than a guess.

Professional considerations

For professionals

Security and tenant isolation

Authorization is enforced in the data layer, for example with row-level security keyed on the tenant, and again in the tool layer. The model’s context never contains data the user could not query directly.

Prompt and data boundaries

Retrieved data is passed as data, clearly separated from instructions, and treated as untrusted. Tool results are size-limited, which bounds both cost and exposure.

Observability

Each request produces a trace: tool calls, arguments, row counts, latency and token usage. Logs avoid storing full prompts or results by default, because they can contain sensitive data. Detailed logging is an explicit, time-limited debugging option.

Caching

Caching applies to tool results per user and tenant, never globally, so one user’s cached answer cannot be served to another.

Failure modes

Covered failure modes include tools returning nothing, model timeouts, invalid tool arguments, and answers that fail validation. Each has a defined user-facing response rather than a raw error.

Alternatives considered

  • Free-form text-to-SQL: flexible, but hard to secure and validate; it may be added later for read-only, sandboxed exploration.
  • Retrieval only: works for documents, not for questions that need aggregation over structured data.
  • A semantic layer as the only tool: a strong option when one already exists; the tool interface is designed to sit on top of one.

Common mistakes

  • Giving the model a database connection string or free-form SQL execution.
  • Applying the tenant filter in the prompt instead of in the query.
  • Logging full prompts and results containing personal data.
  • Caching answers globally across users.
  • Treating a plausible-sounding answer as a validated one.

Next improvements

  • Publish the tool layer and sample database.
  • Add an evaluation report to the repository.
  • Document a version on top of a Fabric SQL analytics endpoint.

Tags

  • AI
  • LLM
  • Python
  • API
  • SQL Server
  • Multi-Tenancy
  • Observability
  • Logging