Do not hand an AI your database schema

There are two ways to connect an AI to a database. Only one of them survives contact with a real organisation.

An AI assistant will happily write you a SQL query. It has read a great deal of code, so it knows the shape of an answer. It has never seen your database, so it guesses table and column names, and gets them wrong.

The obvious fix is to show it the schema: let it list tables, read column types, follow foreign keys. That works in a demo. In production it creates three new problems.

The alternative is a semantic layer: the model is given business entities with plain-English fields, and never learns that a table exists at all.

This tutorial builds the second design, using a working MCP server over an NYU academic database. Every snippet is copied from it unchanged.

MCP is a standard way to hand an AI a set of abilities

Model Context Protocol: a standard wire format for handing a model context and capabilities it did not ship with. One integration, then any client that speaks MCP can use it.

The roles are the ones you would expect, with one wrinkle worth stating: the server is usually a small local process, launched by the client as a subprocess and spoken to over standard input and output. No hosting, no network, no ports.

The chat app is the client. The piece you build is the server. Same server, same code, different pipe if you later want it remote instead of local.

Three building blocks, and the difference is who is in control

If you remember one thing a week from now, make it this.

An MCP server offers three kinds of thing. The difference has nothing to do with what they contain, and everything to do with who decides to use them.

Notice what is not there: nothing about databases, size, or complexity. The same information can be offered as a tool and a resource at once, and in the real project it is (chapter 3).

Throughout, tools are green and labelled Tool, resources blue Resource, prompts purple Prompt. The words are always there, so colour is a convenience.

Tool A button the AI can press

A tool is an action the AI can take mid-answer, without asking anyone first.

Think of a control panel of labelled buttons. You do not say which to press: you label them well and delegate the choice.

The real list the example server announces, as printed by running it. Square brackets mark optional filters:

  list_entities            (no arguments)
                            List the business entities this server can answer questions about.
  get_courses              ([department], [academic_level], [open_to_international], [enrollment_status], [limit])
                            Search the course catalogue.
  get_student_headcount    ([department], [degree_level], [residency], [limit])
                            Student numbers per department, split by degree level and residency.
  get_enrollments          ([department], [term], [course], [limit])
                            How many students are enrolled on each course, by term.
  get_research_output      ([department], [year], [limit])
                            Publication counts and citation totals per department and year.
  get_awards               ([department], [year], [limit])
                            Awards granted, aggregated by department, year and award name.
  get_finalists            ([competition], [year], [department], [limit])
                            Competition finalists, counted by competition, year and department.
  get_staffing             ([department], [role], [limit])
                            Staff headcount per department, by role.
  show_activity_log        ([limit])
                            Show what this server has been asked to do, newest first.
  set_course_enrollment_status (course_title, department_name, status)
                            Open, waitlist or close enrolment on one course.

Read it as the AI does: each line is a labelled button, and each argument is a business word. Not one of them is a table, a column, or a query.

Grouped by blast radius

The project splits its tools by what happens if the AI gets it wrong, not by subsystem. That keeps the dangerous surface small and obvious to a reviewer.

The anatomy of a real tool

get_courses, copied exactly from the project. Note what it does not contain: no SQL, no table name, no column name.

Tap any underlined part of the code to read what it does.

mcp_server/entities.py

    @mcp.tool()
    @anticipated
    def get_courses(
        department: str | None = None,
        academic_level: str | None = None,
        open_to_international: str | None = None,
        enrollment_status: str | None = None,
        limit: int = 50,
    ) -> dict:
        """Search the course catalogue.

        Returns course title, level, department, college, credits, whether
        international students may enrol, and current enrolment state.

        Args:
            department: One department, e.g. "Computer Science".
            academic_level: "undergraduate" or "graduate".
            open_to_international: "yes" to list only courses F-1 and J-1
                visa holders may take, "no" for the rest.
            enrollment_status: "open", "waitlist" or "closed".
            limit: Maximum rows, default 50.
        """
        return db.query_entity(
            get_entity("courses"),
            {...},
            limit,
        )

The point is how little there is, and how much of it is business vocabulary. The AI states what it wants; deciding how to fetch it is somebody else's job.

The description you write is the instruction the AI reads

The most important idea here, and the one most often missed.

Deciding whether to use a tool, the AI has only the description. It cannot open the code or experiment first. It reads a name and a paragraph, and commits.

So that paragraph is not documentation for a colleague. It is an instruction to someone you cannot follow up with.

The project's own words, at the top of the file that holds the tools:

mcp_server/entities.py

1. The description is a prompt.
   It is the only thing the model reads when deciding. Write descriptions for
   the model, not for a human reading the source.

What actually travels back and forth

1. The AI decides

You ask which graduate Computer Science courses international students can take. The AI sees get_courses on its list and works out which filters express that. Nobody told it to. It chose.

2. The request goes out

Tool name plus the filters it chose: department is Computer Science, academic_level is graduate, open_to_international is yes.

3. Real data comes back

The genuine reply from the example server:

{
  "entity": "courses",
  "rows": [
    {
      "course_title": "Distributed Systems",
      "academic_level": "graduate",
      "department_name": "Computer Science",
      "college_name": "Tandon School of Engineering",
      "credits": 3,
      "open_to_international": "yes",
      "enrollment_status": "open"
    },
    {
      "course_title": "Machine Learning",
      "academic_level": "graduate",
      "department_name": "Computer Science",
      "college_name": "Tandon School of Engineering",
      "credits": 3,
      "open_to_international": "yes",
      "enrollment_status": "waitlist"
    }
  ],
  "row_count": 2,
  "truncated": false
}

Read what came back, and then read what did not. There is no course_id, no dept_id, no hint that a join happened. The answer is complete and the schema is still private.

Behind it, a third graduate course exists that the AI was not shown: Secure Systems Practicum is restricted, so filtering on open_to_international excluded it. The filter did that, not the model.

Resource A file you attach to an email

A resource is content a person picks and attaches, usually before anything else happens.

Think of the paperclip on an email. You browse, pick a file, attach it. The recipient never asked; you decided it was relevant. In a chat app a resource shows up as exactly that: a paperclip or an "add context" menu.

The project puts it in one line:

mcp_server/resources.py

    Tool     -> "model, fetch this when you decide you need it"
    Resource -> "human, attach this before we start"

One consequence worth keeping: a resource has to be something you can meaningfully put in a list. If choosing it requires answering a question first, it wants to be a tool.

The anatomy of a real resource

A resource from the project: the semantic dictionary. It is the business vocabulary, in Markdown, and it is what a person attaches at the start of a conversation.

Tap any underlined part to read what it does.

mcp_server/resources.py

    @mcp.resource(
        "nyu://semantic/entities",
        name="Semantic dictionary",
        description="Every business entity this server answers questions about, in plain language.",
        mime_type="text/markdown",
    )
    def entities_resource() -> str:
        """The business dictionary.

        This is the resource to reach for at the start of a conversation:
        attach it once and the model knows the whole vocabulary, with no tool
        calls at all. Note what it contains and what it does not: business
        meaning, and nothing about how any of it is stored.
        """

And part of what a person actually attaches when they pick it, from a real run, trimmed to one entity:

# NYU Semantic Dictionary

The vocabulary this server understands. Every entity below is a business
concept, not a database table.

    ... six other entities ...

## Entity: student_headcount

How many students each department has, split by degree level and residency.

- **department_name**: The academic unit.
- **college_name**: The school it belongs to.
- **degree_level**: Undergraduate or graduate.
- **residency_status**: Domestic or international.
- **total_students**: Number of students in that group.

Filter by: `department`, `degree_level`, `residency`

_Counts only. No student is identifiable, by design._

The dictionary ends by stating its own limits, which is the part people forget to write:

## Deliberately not available

Student and staff names, email addresses, government identifiers, visa numbers
and salaries are held in the underlying systems and are never exposed here.
Neither are table names, column types or keys. Questions needing any of those
cannot be answered by this server, and should be refused rather than guessed at.

Fixed addresses and fill-in-the-blank addresses

Every resource has a URI so a client can ask for it by name. The nyu:// prefix was invented by the project: there is no registry, you pick something descriptive and stay consistent.

Two shapes:

nyu://semantic/entities      text/markdown        Semantic dictionary
nyu://semantic/catalog       application/json     Entity catalogue
nyu://semantic/course-catalog application/json     Course catalogue
nyu://semantic/glossary      text/markdown        Glossary

nyu://semantic/entity/{name} text/markdown        Entity definition
nyu://semantic/department/{name} application/json     Department profile

Why not list every department as its own fixed address? A real university has hundreds. The pattern says "give me the name and I will assemble that one".

Note what the department profile does: it gathers courses, headcount, staffing and research output into one attachment. A resource does not have to mirror a single query. It can be the document a person actually wants.

Both lists above are exactly what the server reports. Fixed URIs and templates are announced separately, because they are used differently.

The same thing, offered twice, on purpose

The clearest illustration of who-controls-what in the project.

The server can give you the whole course catalogue. It offers that two ways.

Same content, same view underneath, deliberately:

mcp_server/resources.py

        Deliberately the same content as the get_courses TOOL. Same answer, two
        access paths, because the two primitives answer different questions:
        the tool is for the model to call when it decides it needs it; this is
        for a human to attach before the conversation starts.

The question is never "what kind of data is this?" but "who should decide to bring it in?". Sometimes the honest answer is "either, depending on the day", and then you offer both.

Prompt A saved recipe

The least used building block and, the project argues, the most underrated.

A prompt is a well-phrased request somebody worked out once, saved, and put on a menu. It appears as a menu entry or slash-command.

Worth flagging, because the word is overloaded: an MCP prompt is not "whatever you type into the box". It is a saved, named, parameterised request the server publishes and a person picks.

Tap any underlined part to read what it does.

mcp_server/prompts.py

    @mcp.prompt()
    def analyze_department(department: str = "Computer Science") -> str:
        """Review one department: size, teaching, staffing and research.

        Args:
            department: The department to review, e.g. Computer Science.
        """
        return (
            f"Give a rounded review of the {department} department. Work "
            f"through it in this order, using the available tools:\n"
            ...
        )

When a person picks it and types Physics, this is the text handed to the AI, exactly as the real server produced it:

Give a rounded review of the Physics department. Work through it in this order, using the available tools:

1. Size and mix. Call get_student_headcount for Physics. How large is it, and how does the domestic to international balance compare with what you would expect?
2. Teaching. Call get_courses for Physics. What is offered at each level, and is anything waitlisted or closed?
3. Demand. Call get_enrollments for Physics. Which courses are heavily subscribed, and which are quiet?
4. Staffing. Call get_staffing for Physics. Does the mix of roles look proportionate to the student numbers from step 1?
5. Research. Call get_research_output for Physics.

Finish with the two things you would look into first, most important first, and say what evidence would settle each one.

Work only from what the tools return. Do not ask for database schema, tables or columns: none are available, and none are needed.

A prompt returns words, not data

This surprises almost everyone.

What came back on the last step is not a table or a number. It is a set of instructions, ending with a boundary: do not go looking for schema.

The prompt never touched the database. It handed over a better question and named the buttons to press. The prompt sets the plan; the tools fetch.

Why not just type the question?

Three reasons, in the project's words:

mcp_server/prompts.py

  * Consistency  everyone's "review" means the same thing, so results are
                 comparable across people and across runs.
  * Expertise    you encode what a good answer looks like. Most users do not
                 know to ask about the domestic to international balance or
                 about waitlisted courses.
  * Discovery    a menu entry gets used; a README example does not.

The middle one is the prize. Somebody knows what a good departmental review looks like; a prompt is how that stops living in one person's head.

The decision guide

Three questions, in order. They land you in the right place almost every time.

A shortcut, not a law. When two answers both seem right, offering both is often honest, exactly as the project does with the course catalogue.

Worked examples from the real project

Real decisions from building the example server. The reasoning is the interesting part.

Three common mistakes

The semantic layer is the safety design

Everything so far has been reading. The server can also change one thing: whether a course is open, waitlisted or closed. That is a different risk, so the design is explicit.

Six controls, in order of importance:

Control 4 is the one worth dwelling on

The first five controls protect against a careless caller. The fourth protects against a careless us, and that is the failure mode people forget.

A semantic view is just SQL. In a year, somebody adds a column to v_semantic_course_catalog because a report needed it, joins in the students table to get it, and ships. Nothing errors. Every tool keeps working. The layer is now leaking, and nobody finds out until a model repeats a passport number back to a user.

So the server reads every exposed view at startup and fails if one has grown a forbidden field. Adding ssn to that view produces this, immediately, at boot:

RuntimeError: Semantic view `v_semantic_course_catalog` exposes ['ssn'],
which the semantic layer promises to hide. Fix the view.

The same check also catches the opposite mistake: an entity that claims a field its view does not actually have. Both are the same class of bug, a registry and a database drifting apart.

The lesson: a boundary that is only a convention is not a boundary. Make it something that fails loudly, at the earliest possible moment, in front of the person who broke it.

What to remember, and where to go next

Remember only the table below and this was worth the time.

Five things to carry away

• The description is the instruction. It is all the AI has, so write it for the AI, limits included.
• Expose business entities, not tables. A model that never learns your schema cannot leak it, misjoin it, or be broken by refactoring it.
• Fewer, sharper tools beat many overlapping ones. Every tool costs attention on every request.
• The same content can be offered two ways, because the real question is who decides to bring it in.
• If it can change data, make it narrow enough that the safety argument is short enough to check.

Where to go next

• Read the project this was built from. One building block per file, comments explaining why rather than what. Start with server.py, about ten meaningful lines, then semantic.py, which is the registry everything else is generated from.
• Look at scripts/create_nyu_db.sql to see the two layers side by side: raw tables holding the real data, and the v_semantic_* views that are the only thing the server may read.
• Then the exercise this was all building towards: take a feature somebody wants and say whether it should be a tool, a resource, or a prompt, and why. Then ask the second question, which entity does it belong to, and what must it never expose?