# MCP Redshift — Copilot + Entra ID + Propagated Identity

MCP server that integrates Microsoft Copilot, Entra ID, DataZone (catalog), DynamoDB (query templates) and Amazon Redshift, propagating the real user's identity end to end (OBO + native IdP federation).

## Documentation

### Reference (the "what")

| Document | Content |
|---|---|
| [Architecture](docs/architecture.md) | Overview, components, Entra ID, Redshift native IdP, AWS infra, risks |
| [Tools Spec](docs/tools-spec.md) | Contracts for the 5 tools, error envelope, security invariants, acceptance criteria |
| [Setup & Local Dev](docs/setup-and-local-dev.md) | Setup runbook (Entra/Redshift/Copilot) and dev guide with the real components |

### Execution (the "how" and "when")

| Document | Content |
|---|---|
| [**Implementation Roadmap**](docs/implementation-roadmap.md) | Development phases, tasks, deliverables and acceptance criteria |
| [ADRs](docs/adr/) | Architecture decisions (Python version, Docker-only, dependency emulation, stack, driver choice, stateless sessions, Terraform, templates as files, OBO credential, delivery pipeline) |
| [CLAUDE.md](CLAUDE.md) | Code conventions: typing, docstrings, tests, coverage |

## Development principles

1. **100% containerized** — no code runs directly on the local machine; build, execution and tests happen via Docker Compose ([ADR-0002](docs/adr/0002-docker-only-development.md)).
2. **Local-first** — no access to the real components (Entra, Redshift, DataZone, AWS DynamoDB) at this stage; every external dependency sits behind an interface (port) with a local emulator/fake ([ADR-0003](docs/adr/0003-external-dependency-emulation.md)).
3. **Typing and documentation are mandatory** — every function with complete type hints (inputs and output) and a docstring; enforced by `mypy --strict` in CI.
4. **Test coverage as a gate** — `pytest --cov` with a coverage floor that fails the build.

## Quick start

Requirements: Docker (with Compose) and git — nothing else.

```bash
docker compose up -d postgres dynamodb mock-idp     # start the local emulators
docker compose run --rm test python scripts/seed_dynamodb.py   # seed the templates table
docker compose run --rm test                        # full test suite with coverage gate
docker compose run --rm test sh scripts/ci.sh       # everything CI runs (format, lint, mypy, secrets, tests)
```

With `make` installed the same commands are `make up`, `make seed`, `make test` and `make ci`.

## Running the MCP server locally

```bash
docker compose up -d --build mcp-server                        # emulators + server on :8080
docker compose run --rm test python scripts/seed_dynamodb.py   # seed templates (dynamodb-local is in-memory)
```

Every MCP route requires a Bearer JWT — mint one at the mock-idp:

```bash
curl -s -X POST http://localhost:8081/mint | jq -r .access_token
```

## Query templates

Templates live in [`templates/`](templates/) as YAML and are published to
DynamoDB by the pipeline ([ADR-0009](docs/adr/0009-templates-as-versioned-files.md)).
Git is the source of truth; the table is a projection of it.

```bash
docker compose run --rm test python -m mcp_redshift.template_source check
```

`check` needs no AWS and runs in CI: it parses every file, matches declared
parameters against the placeholders the SQL binds, applies the catalog denylist
and refuses duplicates — so a broken template is a red build rather than a
conversation that fails in front of a user. See
[templates/README.md](templates/README.md) for the format, the optional-filter
pattern and how publishing treats a template the validation job marked broken.

## Validating templates against the schema

Stored templates drift as the schema evolves. The validation job parses every
template, compares its tables and columns against `information_schema` and
reports what no longer resolves — so the drift is found by an operator instead
of by a user mid-conversation.

```bash
docker compose run --rm -e VALIDATION_DB_USER=admin -e VALIDATION_DB_PASSWORD=admin test python -m mcp_redshift.validation
```

It prints a JSON report and exits 1 when it finds drift (the signal a scheduled
task turns into an alarm). Add `--apply` to mark the affected templates
`broken`, which makes them refuse to execute and appear flagged in listings.
Templates that start matching the schema again are reported but never
reactivated automatically — a template may have been broken on purpose.

The job needs a database user that sees the whole schema (`information_schema`
only exposes what the connected user has rights on) and runs outside the MCP
runtime, whose task role has no write access to the template table.

### Validating with MCP Inspector

1. `npx @modelcontextprotocol/inspector`
2. Transport: **Streamable HTTP**, URL: `http://localhost:8080/mcp`.
3. Add a custom header `Authorization: Bearer <token from /mint>`.
4. Connect and run the smoke sequence: `search_catalog` → `search_query_templates` → `get_query_template` → `run_query_template` (dates like `2026-01-01`/`2026-12-31`). Every response is the standard envelope; `executed_as` must show `aad:user-a@company.com`.

Requests without a token (including `initialize`) are rejected with 401 by design.

## Pointing at the real services (phase 7)

Everything above runs against the Docker emulators. Switching to real Entra,
Redshift and AWS is a configuration change and nothing else — no code path, no
build argument, no import differs ([ADR-0003](docs/adr/0003-external-dependency-emulation.md),
asserted by `tests/unit/test_wiring.py`).

```bash
cp .env.real.example .env.real   # fill from docs/phase7-values.md
docker compose --profile real up mcp-server-real
docker compose --profile real run --rm test-real
```

Three variables are omitted from `.env.real.example` on purpose: `MOCK_IDP_URL`,
`CATALOG_FIXTURE_PATH` and `DYNAMODB_ENDPOINT_URL`. Any one of them left set in
a real deployment silently restores a local stand-in — including a password
connection instead of the user's token, which is identity propagation gone with
everything still green.

## Current status

**All locally-testable phases (0–6) are complete**: foundation, domain core, authentication, data layer, execution with the identity-isolation CI gate, the MCP server with the 4 main tools, and the flag-gated ad-hoc query with guardrails.

Everything phases 7 and 8 allowed to be built without access has been built too: the [versioned Redshift DDL](infra/sql/), the [values worksheet and checklist](docs/phase7-values.md), the self-skipping `tests/real/` suite, the [Terraform stack](infra/terraform/) with alarms and a dashboard, the [deploy pipeline](.github/workflows/deploy.yml), the [template validation job](src/mcp_redshift/validation.py) and the [operations runbook](docs/operations-runbook.md).

What remains genuinely needs the real thing: applying any of it, confirming `token_type` on the driver, the real AADSTS surface, DataZone's actual `forms` shape, and Copilot's refresh behavior past ~75 minutes. See the [roadmap](docs/implementation-roadmap.md) and the [security review](docs/security-review.md).

To enable the optional 5th tool locally: set `ADHOC_ENABLED: "true"` in `docker-compose.yml` and restart `mcp-server`.