# MCP-USAGE.md **Audience: AI coding agents (Claude Code, GitHub Copilot, Cursor).** **Companion to: CLAUDE.md (overall project conventions).** This document tells you, the AI agent, exactly how to use the central schema MCP server. Read it before doing anything that touches database schema. Read it again at every phase boundary. CLAUDE.md is for project-wide conventions (code style, file layout, phase discipline). MCP-USAGE.md is narrow: only the schema MCP workflow. If they ever contradict, MCP-USAGE.md wins on schema-MCP topics. --- ## The six tools you have The schema MCP server exposes exactly six tools. Their tool names in the runtime are prefixed `mcp__schema__`. | Tool | Purpose | Permission needed | |---|---|---| | `list_tables(schema?)` | List all tables, optionally filtered by schema name | `mcp.schema.read` | | `describe_table(qualified_name)` | Full column / type / FK / index / constraint detail for one table | `mcp.schema.read` | | `find_existing_tables_for_concept(concept, hints?)` | Search for reusable tables that match a data concept, ranked by reuse score | `mcp.schema.read` | | `list_pending_proposals(submitter?, status?)` | Review the proposal queue | `mcp.schema.read` | | `get_proposal(proposal_id)` | Look up one proposal by ID | `mcp.schema.read` | | `propose_table_change(...)` | Submit a schema-change proposal for DBA review | `mcp.schema.admin` | If your token only carries `mcp.schema.read`, calling `propose_table_change` returns 403. That is correct behaviour — you are read-only. Tell the user they need write-scope access; do not attempt to work around the error. --- ## The Prime Directive **You do not invent tables. You do not invent columns. You do not write SQL DDL directly. You do not modify database schema by any path other than `propose_table_change`. If the schema-MCP server is unreachable, you stop and tell the user — you do not proceed by guessing.** This is the strongest rule in this document. If at any point you find yourself about to: - Write a `CREATE TABLE`, `ALTER TABLE`, or `DROP TABLE` statement - Reference a table name you haven't verified via `list_tables` or `describe_table` - Add a column to a table without going through `propose_table_change` - Bypass the MCP server because a tool call failed ...stop. State to the user that the rule is blocking you. Do not proceed. --- ## When you need schema information — workflow ### A. Before writing any code that reads from or writes to the database 1. Call `list_tables(schema="")` to confirm what exists. 2. For each table your code touches, call `describe_table(qualified_name)` to get the exact column shape (types, nullability, defaults, FKs, indexes). Don't guess column names from memory — they may have changed, they may use a different naming convention than you expect. 3. Reference the verified table and column names in your code. ### B. When a feature seems to need new data storage Don't immediately propose a new table. Follow this sequence: 1. **Restate the concept** to yourself in one phrase. Example: "User identity record with email and Entra ID linkage." 2. Call `find_existing_tables_for_concept(concept="", hints=[...])` with the most relevant search hints (column names you'd expect, schema prefix, related terms). 3. Read the ranked results carefully. Each result has a reuse score (0-1) and a snippet of the table's structure. A score above 0.6 is a strong match; 0.4-0.6 is worth investigating; below 0.4 is usually a false positive but read anyway. 4. For each plausible match, call `describe_table` to see the full shape. 5. Decide one of: - **Reuse the existing table.** Reference it by FK in your design. This is the preferred outcome. - **Reuse a different existing table that the search didn't find.** If your domain knowledge suggests an obvious candidate, check it directly with `describe_table` even if `find_existing_tables_for_concept` missed it. - **Propose a new table** because no reusable option exists. Go to section C. ### C. When you need to propose a new table 1. Author the proposal in your head first. You need: - **schemaName**: which schema the table goes in (e.g., `contactmgmt`, `common`). Use existing schemas where possible; only propose a new schema if the concept genuinely doesn't fit any existing one. - **tableName**: snake_case, lowercase letters and digits and underscores only. Singular noun (e.g., `contact` not `contacts`). Reject the urge to use PascalCase — the server will reject it. - **columns**: an array. Each column needs: - `name`: PascalCase (the platform convention for column names is PascalCase even though tables are snake_case — yes, it's inconsistent, work with it). - `type`: SQL Server type with precision (e.g., `UNIQUEIDENTIFIER`, `NVARCHAR(320)`, `DATETIME2(3)`, `DECIMAL(5,4)`, `BIT`). - `nullable`: true or false. - `defaultValue`: SQL expression (e.g., `NEWSEQUENTIALID()`, `SYSUTCDATETIME()`, `1`) or omit. - `description`: brief sentence explaining what the column holds. If there's a CHECK constraint, name the rule here too. - `references`: for FKs, the qualified `schema.table.column` target. - **justification**: free text. Explain *why* this table is needed, what concepts you considered for reuse and rejected, what indexes you'd expect, and what CHECK constraints are required. This is what the DBA reads at review time. Be specific. The DBA needs to know: - What other tables you checked first (cite the `find_existing_tables_for_concept` calls you made) - Why those didn't fit - Required UNIQUE / non-unique indexes with their column lists - Required CHECK constraints with their predicates 2. Call `propose_table_change(schemaName, tableName, columns, justification)`. 3. The server returns a `proposalId` like `prop_XXXXXXXX-XXX`. Capture it. 4. Tell the user: "I've submitted proposal `prop_X` to the DBA. The table `` does NOT exist yet. I will not write code that queries or inserts into it until the DBA has approved AND fulfilled the proposal." 5. Stop. Do not proceed to write code that depends on the new table. ### D. When the DBA hasn't approved your proposal yet You may still proceed with code that does not depend on the new table. Example: if you proposed `contactmgmt.contact` and your current task is to build a login screen that touches `common.app_user` (which exists), build the login screen. You may NOT: - Write code that imports/exports/queries the proposed table. - Write code that references columns from the proposed table by name (the column shapes might change in DBA review). - Add the proposed table to an EF Core DbContext, an ORM mapping file, or a generated query. Check status periodically with `get_proposal(proposal_id)`. The status field progresses: `pending` → `approved` → `fulfilled`. Only when status is `fulfilled` does the table physically exist in the database. When you see `fulfilled`, you may write code that references the new table. Before that code reads/writes the database, run `describe_table(qualified_name)` to confirm the final column shape — the DBA may have made small adjustments during migration. ### E. When you need to modify an existing table This is a higher-stakes operation than creating a new one. Existing data exists; existing code references the columns. 1. First, call `describe_table(qualified_name)` to see the current shape. 2. Then identify the smallest possible change. Common modifications and their handling: | Change | Handling | |---|---| | Add a new column (nullable, no default) | Safe. Propose via `propose_table_change` with the same tableName plus the new column. Justification must explain why and confirm existing code won't break. | | Add a new column (NOT NULL with default) | Safe but more expensive. Same as above; justification must include the default value and note that existing rows will get it. | | Rename a column | DANGEROUS. Don't propose this through `propose_table_change` — it requires a coordinated app-and-DB change. Tell the user this needs human design. | | Change a column's type | DANGEROUS. Tell the user this needs human design. | | Drop a column | DANGEROUS. Tell the user this needs human design. | | Add an index | Safe. Propose with justification explaining the query that needs it. | | Add a CHECK constraint | Possibly safe (depends on whether existing rows satisfy it). Tell the user to verify before proposing. | When you propose a table modification, **the justification field MUST clearly say "MODIFICATION to existing table X" at the top**, otherwise the DBA may mistake it for a new-table proposal and reject as duplicate. ### F. When you discover a table that should not exist Sometimes you'll find legacy tables that contradict the platform's design. Example: a table with a free-text country name where a FK to `masterdata.Country` would be correct. You do NOT propose deleting it. You do NOT use `propose_table_change` to modify it. You write a note in your reply to the user saying: "I observed `` which appears to be a legacy table. Suggesting cleanup is outside my scope; flagging for your platform team to assess." Then continue with your actual task, working around the legacy table. --- ## When the MCP server fails The schema MCP server can fail in several ways. Each has a specific response. ### Server unreachable / 5xx / connection refused Tell the user: "The schema MCP server is unreachable. I cannot proceed with anything that touches database schema until it's back. The user's platform team should investigate." Do NOT fall back to writing SQL directly. Do NOT proceed by guessing column shapes. Do NOT continue and resolve later — schema bugs found later are expensive. ### 401 Unauthorized The bearer token has expired. Tell the user: "My MCP token has expired. Please run `mcp-refresh` (or `mcp-daystart` if it's a new session) and then ask me again." ### 403 Forbidden / "Requires scope: mcp.schema.admin" You have `mcp.schema.read` but the operation needs `mcp.schema.admin`. Tell the user: "I have read-only access to the schema MCP server. To file proposals, my account needs `mcp.schema.admin` scope. Please ask your platform admin to grant it, or have someone with admin scope file the proposal for me." ### Known Claude Code MCP SDK write-path bug If `propose_table_change` returns: > `Incompatible auth server: does not support dynamic client registration` This is a known bug in the Claude Code MCP SDK against Microsoft Entra ID. Read operations work; write operations fail. When you hit this, dump the full proposal payload as JSON in your reply and tell the user: > "MCP write path hit the known Entra ID compatibility bug. The proposal > I would have submitted is below. Please run `submit-proposals.sh` (from > the Section 11 archive) with this payload to file it manually, then > tell me the proposal ID it returns. I'll continue once I have the ID." Do NOT fall back to writing SQL directly. Do NOT abandon the proposal. --- ## What you must NEVER do Hard rules. No exceptions, no clever workarounds. 1. **Never write `CREATE TABLE`, `ALTER TABLE`, or `DROP TABLE` in any file — application code, migration script, or otherwise.** DDL is exclusively the DBA's responsibility, executed via Azure Data Studio with their own Entra credentials. The MCP server has no DDL capability. 2. **Never invent table or column names.** If `describe_table` doesn't know it, neither do you. Reference only verified names. 3. **Never write code that depends on a not-yet-fulfilled proposal.** The proposal might be modified during DBA review; your code would break. 4. **Never bypass `find_existing_tables_for_concept` before proposing a new table.** Reuse-first is the platform's central discipline. If you skip it, you produce drift. 5. **Never claim a table exists if you haven't verified it.** If a user says "use the contacts table" and you haven't run `describe_table`, verify before writing code. 6. **Never store credentials or keys in tables you propose.** Sensitive values go in Azure Key Vault and tables hold a reference (e.g., a Key Vault secret URI), not the value itself. 7. **Never propose tables that hold user-data in shared/common schemas without explicit user direction.** Schemas like `common` and `masterdata` are for cross-app shared data; app-specific data goes in app-specific schemas like `contactmgmt`, `sales`, etc. --- ## What success looks like A well-run schema interaction looks like this: > User: "Build a contact-list feature." > > AI: "First, let me check what schema already exists. > [calls list_tables(schema='contactmgmt')] > I see contact, company, tag, contact_tag, and a few others. Let me > describe the contact table. > [calls describe_table('contactmgmt.contact')] > OK, contact has OwnerUserId, FirstName, LastName, DisplayName, > PrimaryEmail (UNIQUE per owner with soft-delete), CompanyId FK, > RowVersion for concurrency. I'll write a GET /api/contacts endpoint > that queries this table filtered by OwnerUserId. Coming up..." That's it. Two MCP calls before any code. The code references real columns with real types. No guesswork. No drift. Compare to: > User: "Build a contact-list feature." > > AI: "I'll create a Contacts table with id, name, email columns..." This is the failure mode. The platform exists to prevent it. Use the MCP server every time. Even when you think you remember. --- ## Quick decision tree ``` Task involves data? ├── No → Just write code. MCP not needed. └── Yes → Continue ├── Does the data exist somewhere already? │ ├── Unsure → list_tables(schema) + describe_table(...) to find out │ └── Yes, you remember the table → describe_table to verify shape │ (memory can be wrong) │ └── Does a similar concept exist? ├── find_existing_tables_for_concept(concept) │ ├── Match found (score > 0.6) → describe_table, reuse it ├── Plausible match (0.4-0.6) → describe_table, evaluate └── No match → propose_table_change ├── Read-scope? → tell user to get admin-scope, stop ├── Admin-scope? → propose, capture ID, tell user, stop │ └── Wait for status='fulfilled' before writing code that references the new table. ``` --- ## Versioning This document is v1.0. The platform's MCP server and propose_table_change schema may evolve. When new tool fields are added (e.g., structured indexes[]), this document will be updated. AI agents should treat the MCP server's runtime tool descriptions as authoritative and this document as narrative guidance — but the workflow rules above are stable.