# TASK 2.2: Restrict Schema Discovery and SQL Execution to Approved Source-Mirror curated Objects

## Summary

Restrict the AI data access tools to approved Redshift source-mirror `curated` objects.

This task adds practical application-level guardrails on top of the curated-first database access boundary created by Redshift Foundation.

It is not a full SQL firewall.

## Dependencies

- TASK 2.1 (tenant-resolved Redshift query tools)

## Files to Modify

### 1. Warehouse query tool provider

Update schema/table discovery and SQL execution behavior.

Likely file:

- `backend/Kds.Ai.AnomalyDetector/Tools/WarehouseQueryToolProvider.cs`

## Required Behavior

Discovery tools must:

- list only approved source-mirror `curated` objects as the available runtime surface,
- list only approved tables/views in `curated`,
- describe only approved objects in `curated`,
- reject requests to describe non-approved objects.

SQL execution must:

- retain read-only command restrictions,
- reject mutation statements,
- reject obvious references to internal/source schemas:
  - `kdsdev.`
  - `staging.`
  - `etl_meta.`
- require or strongly enforce curated-qualified table references for analytical SQL,
- allow valid Redshift read-only constructs needed for analysis, such as CTEs.

## Status

Completed.

Implemented in:

- `backend/Kds.Ai.AnomalyDetector/Tools/WarehouseQueryToolProvider.cs`

Clarification applied during implementation:

- The branch-aligned runtime surface is source-mirror `curated.<system>_<table>`.
- Implementation should reject blocked schemas such as `kdsdev`, `staging`, `etl_meta`, and non-approved qualified objects, while allowing the approved source-mirror curated runtime objects.

## Acceptance Criteria

- [x] `list_schemas` returns only `curated` or equivalent curated-only behavior
- [x] `list_tables` exposes only approved source-mirror `curated` objects
- [x] `describe_table` rejects non-approved schemas or objects
- [x] `execute_sql` keeps read-only restrictions
- [x] `execute_sql` rejects obvious references to `kdsdev`
- [x] `execute_sql` rejects obvious references to `staging`
- [x] `execute_sql` rejects obvious references to `etl_meta`
- [x] Valid read-only SQL against approved source-mirror `curated` objects can execute
- [x] Solution builds without errors

## Notes

- Database permissions remain the durable boundary.
- This task adds pragmatic defense and better agent feedback.
- Do not add a production-grade SQL parser unless already available and simple to integrate.