Self-hosting regulated AI agents without leaking warehouse credentials

The problem

A healthcare network wants AI agents to help medical directors track clinical spend and patient treatment volumes. But regulatory compliance is strict under HIPAA and internal security mandates. Patient healthcare data and database credentials can never leave the private virtual private cloud. Direct database access for large language models is forbidden because agents can hallucinate destructive SQL statements, execute unindexed table scans, or leak sensitive personal health information through prompt injection.

Connecting agents to third-party cloud data warehouses creates major compliance risks. Security leads reject giving third-party SaaS vendors database credentials. They also reject giving AI agents direct SQL database connections. When an LLM connects directly to a relational database, any vulnerability in system prompts can expose raw rows to unauthorized users.

Compliance teams require an architectural separation of concerns. You need a self-hosted architecture where internal AI agents query certified metrics through the Model Context Protocol. The database password remains locked inside an isolated Query Runner proxy, and every agent query produces a tamper-proof audit trail.

+-------------------------------------------------------------------+
| Customer Private VPC                                              |
|                                                                   |
|  +--------------+         +---------------+        +------------+ |
|  |   AI Agent   |         |  Self-Hosted  |        |   Query    | |
|  |  (Claude /   |--MCP--->|  hashi Engine |--SQL-->|   Runner   | |
|  |  Copilot)    |         | (Catalog/RLS) |        |  (Holds PW)| |
|  +--------------+         +---------------+        +------------+ |
|                                                           |       |
|                                                           v       |
|                                                    +------------+ |
|                                                    | Regulated  | |
|  No data or credentials leave the VPC ✓            | Warehouse  | |
|                                                    +------------+ |
+-------------------------------------------------------------------+

The data

The database stores clinical operational data across clinics and patient encounters. The clinics table lists clinical facilities and regulatory jurisdictions. The treatment_encounters table tracks patient treatment dates, encounter costs, and patient identifiers.

+--------------------+              +----------------------+
|      clinics       |              | treatment_encounters |
+--------------------+              +----------------------+
| clinic_id (PK)     |<---+         | encounter_id (PK)    |
| clinic_name        |    +---------| clinic_id (FK)       |
| jurisdiction       |              | recorded_at          |
+--------------------+              | encounter_cost       |
                                    | patient_id           |
                                    +----------------------+

Here is the SQL DDL and sample rows runnable directly in DuckDB:

CREATE TABLE clinics (
    clinic_id VARCHAR PRIMARY KEY,
    clinic_name VARCHAR NOT NULL,
    jurisdiction VARCHAR NOT NULL
);

CREATE TABLE treatment_encounters (
    encounter_id VARCHAR PRIMARY KEY,
    clinic_id VARCHAR NOT NULL REFERENCES clinics(clinic_id),
    recorded_at DATE NOT NULL,
    encounter_cost DOUBLE NOT NULL,
    patient_id VARCHAR NOT NULL
);

INSERT INTO clinics VALUES
    ('cln_east', 'Eastside Medical Center', 'US-East'),
    ('cln_west', 'Westside Specialty Clinic', 'US-West'),
    ('cln_cent', 'Central Regional Hospital', 'US-Central');

INSERT INTO treatment_encounters VALUES
    ('enc_01', 'cln_east', '2026-10-01', 12500.0, 'pt_901'),
    ('enc_02', 'cln_east', '2026-10-01', 8400.0, 'pt_902'),
    ('enc_03', 'cln_east', '2026-10-02', 15200.0, 'pt_903'),
    ('enc_04', 'cln_west', '2026-10-01', 22000.0, 'pt_904'),
    ('enc_05', 'cln_west', '2026-10-02', 18500.0, 'pt_905'),
    ('enc_06', 'cln_cent', '2026-10-01', 9300.0, 'pt_906'),
    ('enc_07', 'cln_cent', '2026-10-02', 11400.0, 'pt_907');

The model

The semantic model exposes only safe aggregate measures and dimensions. Individual patient identifiers are declared as internal dimensions for aggregation only, never as unmasked public attributes.

The metrics expose clinical_spend, patient_volume, and active_patients. The metric active_patients counts distinct patient identifiers inside an aggregate measure, preventing the raw patient table from being exposed.

source: warehouse_main

entities:
  - name: Clinic
    table: clinics
    primary_key: clinic_id
    dimensions:
      - name: clinic_id
        column: clinic_id
        type: string
      - name: clinic_name
        column: clinic_name
        type: string
      - name: jurisdiction
        column: jurisdiction
        type: string

  - name: TreatmentEncounter
    table: treatment_encounters
    primary_key: encounter_id
    dimensions:
      - name: encounter_id
        column: encounter_id
        type: string
      - name: clinic_id
        column: clinic_id
        type: string
      - name: recorded_at
        column: recorded_at
        type: date
        grains: [day, month, quarter, year]
      - name: patient_id
        column: patient_id
        type: string
    measures:
      - name: total_cost
        expr: encounter_cost
        agg: sum
      - name: encounter_count
        expr: encounter_id
        agg: count
      - name: unique_patients
        expr: patient_id
        agg: count_distinct

relationships:
  - name: encounter_clinic
    from: TreatmentEncounter
    to: Clinic
    cardinality: many_to_one
    keys:
      - [clinic_id, clinic_id]

metrics:
  - name: clinical_spend
    expr: total_cost
    description: "Total regulated clinical treatment spend"
    unit: USD
    format: currency
  - name: patient_volume
    expr: encounter_count
    description: "Total patient treatment encounters count"
  - name: active_patients
    expr: unique_patients
    description: "Distinct patients treated in period"

Notice how the model enforces data minimization:

• Patient identifiers remain internal to the TreatmentEncounter entity. The unique_patients measure counts unique IDs without exposing individual patient records to the agent.
• Clinic identifiers and facility names are safe attributes for grouping clinical operations.
• The certified metrics clinical_spend, patient_volume, and active_patients define unambiguous calculations across facilities.

The model strictly separates physical table names from certified metrics. See the model specification for complete configuration syntax.

The queries

AI agents interact through the Model Context Protocol (MCP). The server exposes six tools: list_catalog, describe_metric, run_semantic_query, drill_down, refine_semantic_query, and list_dimension_values. The tools accept structured semantic parameters, never raw SQL, and they never return SQL. Agents call list_catalog to discover available metrics, inspect formulas with describe_metric, and fetch governed results using run_semantic_query. The SQL shown below is what the engine compiles. REST returns it only when the engine is started with ENGINE_ALLOW_SQL=true and the request sets include_sql.

Query 1: Hero agent query via MCP run_semantic_query

When an executive assistant agent asks for clinical spend and patient volume across clinics, it invokes the MCP tool run_semantic_query:

{
  "name": "run_semantic_query",
  "arguments": {
    "metrics": ["clinical_spend", "patient_volume", "active_patients"],
    "dimensions": [
      {
        "name": "clinic_name",
        "entity": "Clinic"
      }
    ],
    "order": [
      { "ref": "clinical_spend", "desc": true }
    ]
  }
}

The self-hosted engine validates the request, compiles the SQL, routes the query to the local Query Runner, and returns the result table:

clinic_name	clinical_spend	patient_volume	active_patients
Westside Specialty Clinic	40500	2	2
Eastside Medical Center	36100	3	3
Central Regional Hospital	20700	2	2

The engine hands compiled SQL to the isolated Query Runner proxy:

SELECT
  q0."clinic_name" AS "clinic_name",
  CAST(q0."total_cost" AS DOUBLE) AS "clinical_spend",
  CAST(q0."encounter_count" AS DOUBLE) AS "patient_volume",
  CAST(q0."unique_patients" AS DOUBLE) AS "active_patients"
FROM (
  SELECT
    "Clinic"."clinic_name" AS "clinic_name",
    CAST(SUM("TreatmentEncounter"."encounter_cost") AS DOUBLE) AS "total_cost",
    COUNT("TreatmentEncounter"."encounter_id") AS "encounter_count",
    COUNT(DISTINCT "TreatmentEncounter"."patient_id") AS "unique_patients"
  FROM "treatment_encounters" AS "TreatmentEncounter"
  JOIN "clinics" AS "Clinic"
    ON "TreatmentEncounter"."clinic_id" = "Clinic"."clinic_id"
  GROUP BY "Clinic"."clinic_name"
) AS q0
ORDER BY "clinical_spend" DESC

The database credentials reside entirely in the Query Runner process. The semantic engine never stores or touches warehouse passwords.

Query 2: Daily clinical spend trends

The agent can also analyze temporal patterns by requesting day-grain aggregates:

{
  "metrics": ["clinical_spend", "patient_volume"],
  "dimensions": [
    {
      "name": "recorded_at",
      "entity": "TreatmentEncounter",
      "grain": "day"
    }
  ],
  "order": [
    { "ref": "recorded_at", "desc": false }
  ]
}

The gateway returns the daily totals:

recorded_at	clinical_spend	patient_volume
2026-10-01T00:00:00Z	52200	4
2026-10-02T00:00:00Z	45100	3

The query compiles into clean date-truncated SQL:

SELECT
  q0."recorded_at" AS "recorded_at",
  CAST(q0."total_cost" AS DOUBLE) AS "clinical_spend",
  CAST(q0."encounter_count" AS DOUBLE) AS "patient_volume"
FROM (
  SELECT
    date_trunc('day', "recorded_at") AS "recorded_at",
    CAST(SUM(encounter_cost) AS DOUBLE) AS "total_cost",
    COUNT(encounter_id) AS "encounter_count"
  FROM "treatment_encounters" AS "TreatmentEncounter"
  GROUP BY date_trunc('day', "recorded_at")
) AS q0
ORDER BY "recorded_at"

The Query Runner executes the query against the warehouse and records an audit log entry containing the caller subject, start time, duration, and scanned rows.

Query 3: Refusal for uncataloged PII column

If a compromised or confused agent hallucinates and asks for sensitive personal health identifiers, the engine halts the query:

{
  "metrics": ["patient_social_security_number"]
}

The engine rejects the request immediately with HTTP 404:

{
  "error": "model: resolve: metric \"patient_social_security_number\" is not exposed by the named view",
  "code": "not_found",
  "reason": "not_in_view",
  "ref": "patient_social_security_number"
}

No database query executes. The warehouse never sees an invalid or unauthorized request.

What to notice

• Zero password exposure. The semantic engine dials no database directly. It communicates with a Query Runner proxy running in your private VPC. The database password stays sealed inside the runner process. The query compiler never sees database credentials.
• Strict MCP boundaries. AI agents interact through the six MCP tools, including list_catalog, describe_metric, and run_semantic_query. Agents cannot pass custom SQL strings, arbitrary table joins, or data definition commands. Prompt injection cannot trick the engine into running rogue statements.
• Complete audit trail. Every executed query generates an audit event containing the request ID, caller principal, duration, row count, and compiled SQL statement. Security teams maintain full observability over agent queries, mapping every answer back to verified identities.
• PII shielding. Sensitive identifiers like individual patient names or Social Security numbers remain hidden. The model exposes only approved aggregate measures and certified business metrics.
• Self-contained VPC deployment. The entire stack runs within your own network perimeter. No telemetry, customer records, or schema metadata leave your infrastructure boundary.

For more details on connecting AI tools to certified metrics, see the article on building a Slack bot for weekly results and the guide on multi-tenant customer dashboards.

Limits

• Query Runner required. The semantic engine does not dial database ports directly. Every self-hosted installation requires running the Query Runner proxy alongside the database source.
• Operational infrastructure responsibility. Self-hosting means your infrastructure team manages compute provisioning, process supervision, network routing, and high-availability failover for both the engine and runner containers.
• No exploratory SQL console. MCP tools strictly expose semantic queries. If an analyst needs to run freeform exploratory SQL or create temporary database tables, they must use an authenticated SQL client directly on the warehouse.
• Warehouse-side access controls. The Query Runner enforces network connectivity and credential authentication. Fine-grained column masking and hardware encryption at rest remain the responsibility of your underlying database warehouse.
• Transformation boundary. The semantic engine aggregates existing table records. Deduplicating patient records, cleaning clinical encounter logs, and standardizing medical billing codes must occur in upstream data pipelines.