# Service MCP — Generic FastMCP + SQLAlchemy CRUD Template

A production-ready, generic [FastMCP](https://github.com/jlowin/fastmcp) server template with
SQLAlchemy async CRUD, built by distilling a real-world fund NAV MCP service into a reusable
skeleton. Use it to bootstrap new MCP data-management services.

**Stack**: Python 3.12+ · FastMCP 3.x · SQLAlchemy 2.x (async) · Pydantic v2 · Typer CLI · uv

---

## Features

- **Registry-driven CRUD tools** — add/update/delete (single + batch) are generated from a one-line
  entity registry; no per-entity boilerplate.
- **FK code auto-resolution** — business codes (`product_code`) are resolved to internal IDs by the
  handler layer; callers never see primary keys.
- **Auto-placeholder creation** — child records referencing a missing parent create a
  `abnormal=Placeholder` stub parent automatically (e.g. price records for an unknown product).
- **Orphan marking** — deleting a parent marks dependent child rows `abnormal=Orphaned` instead of
  cascade-deleting; a `review_abnormal_items` tool aggregates all pending-review rows.
- **Composite-key delete** — child records can be located by their natural compound key
  (`product_code + price_date`).
- **Dynamic Filter/Search** — `Filter`/`SearchByKeyword`/`SearchByFields` classes are generated at
  import time from ORM introspection; `.pyi` stubs are regenerated by a script for IDE support.
- **Conflict versioning** — same-day-same-source price conflicts bump a `version` column and mark
  the row for human review.
- **Multi-transport CLI** — `stdio` / `sse` / `streamable-http` / `ui` (FastMCP Apps dashboard).
- **Layered config** — env vars → TOML → code defaults (`MCP_` prefix, `MCP_ENV` selects the TOML).
- **Auth-ready** — JWT middleware + `mcp_perm` decorators with permission discovery; async-faithful
  wrappers and admin-mode fail-closed rejection. **Three-tier data scoping**
  (`scope_visibility_where`: platform / team / personal + publish-review gate), a
  parent-visibility registry (`PARENT_ENTITY_MAP`) for child rows without own ownership columns
  (orphans stay visible), and soft-deleted rows hidden from list/search by default.
- **Typed tool UX** — `ToolAnnotations` presets (read-only / idempotent / mutating / destructive)
  on every tool; predictable business failures raise `ToolError` and are converted to
  `UtilResponse` business errors (never 500); batch mutations capped at `MAX_BATCH_SIZE`.
- **Docker deployment** — compose stack (PostgreSQL 18 + Redis 8, optional pgAdmin) with
  lifecycle scripts (`ctl.sh` / `ctl.ps1`).
- **Extras** — mock data generator, idempotent migration example, SQLite/MySQL/PostgreSQL/InfluxDB
  support, FastMCP Apps config UI.
- **Generic infrastructure** — async per-key
  rate limiter with jitter (`utils/rate_limiter.py`), large-result spill-to-file with startup
  sweep (`utils/spill.py`), cron expression parsing (`utils/cron.py`), background task manager +
  cron scheduler (`task/`), and a BOM-safe CSV export helper with demo tool `export_products_csv`
  (`utils/export.py` + `tools/export_tools.py`).

## Quick start

```bash
uv venv && uv sync --dev

# Run the server (pick one transport)
uv run service-mcp stdio
uv run service-mcp streamable-http --host 0.0.0.0 --port 8001
uv run service-mcp sse --host 0.0.0.0 --port 8001
uv run service-mcp ui --dev-port 8080 --mcp-port 8001
```

On first start the server auto-creates `configs/config.{MCP_ENV}.toml` with a default in-memory
SQLite database + Redis cache config — zero configuration to get going.

## Example entities

The template ships one minimal domain — **Product** + **ProductPrice** — implemented end-to-end to
demonstrate every pattern you need to replicate for your own entities:

| Entity | Demonstrates |
|---|---|
| `Product` | unique business code, soft-delete flag, auto-placeholder creation (orphan **parent**) |
| `ProductPrice` | FK code resolution, composite unique key (`product_id + price_date + data_source + version`), version conflict detection, orphan marking target, composite-key delete |

Follow the chain: `add_product` → `AddHandler` → `CodeResolveMixin._resolve_fk_codes` →
`ProductPrice` rows auto-resolve `product_code` → `product_id`; `delete_product` marks all its
prices `abnormal=Orphaned`; `add_product_price` with a conflicting same-day-same-source value
bumps `version` and flags `abnormal=PriceConflict`.

## Project structure

```
service_mcp/
├── server.py          # FastMCP app + Typer CLI (stdio/sse/streamable-http/ui)
├── config.py          # MCPSettings layered config (env → TOML → code)
├── apps/              # FastMCP Apps UI (config_app: DB/cache management dashboard)
├── auth/              # JWT middleware, mcp_perm decorator, permission/entity discovery
├── db/                # DBManager (async SQLAlchemy CRUD/paginate) + InfluxDBManager
├── handlers/          # CodeResolveMixin + Add/Update/Delete/Query handlers
├── models/
│   ├── orm/           # SQLAlchemy models (base.py audit columns, product.py example)
│   ├── pydantic/      # dynamic Filter/Search generators + per-entity request/response models
│   └── schemas.py     # DB/cache config schemas + pagination
├── tools/             # crud_factory (registry-driven), query_tools, basic_tools, dict_tools
├── task/              # generic background task manager + cron scheduler (TaskManager/TaskScheduler)
└── utils/             # enums, logging, path helpers, rate_limiter, spill, cron, export (CSV)
configs/               # config.example.toml skeleton (per-env TOMLs are git-ignored)
docker/                # compose files, entrypoint, ctl.sh/ctl.ps1
mock/                  # mock_product_data.py
scripts/               # rename_project.py, refresh_project_stub.py, migrate_example.py
tests/                 # pytest suite (in-memory SQLite)
```

## Add a new entity

1. **ORM**: create `service_mcp/models/orm/<entity>.py` subclassing `Base` (audit columns are
   inherited); add a unique business code column with a `comment` (used by friendly duplicate
   messages) and an `abnormal: AbnormalType | None` column for orphan marking. Export it in
   `models/orm/__init__.py` (import `base` first).
2. **Pydantic**: create `<Entity>Base` / `Create` / `Update` / `Delete` (extends `BaseDeleteModel`,
   requires at least one lookup field) / `Response` in `models/pydantic/<entity>.py`; reuse the
   validator helpers in `product_validators.py` as a template.
3. **Filter/Search**: add `create_filter_class(...)` / `create_search_class(...)` calls in
   `models/pydantic/filter.py` / `search.py`. If you override the generated class with an explicit
   `class`, re-register it with `register_pyi_class(..., explicit=True)`.
4. **Regenerate stubs**: `uv run python scripts/refresh_project_stub.py` (run twice; the second
   run must produce no diff).
5. **Handlers**: add registry rows —
   `_CODE_RESOLVE_MAP` (FK codes), `_NAME_RESOLVE_MAP` (name fallback), `_OWN_CODE_FIELDS` (own
   unique codes), `_AUTO_CREATE_MODELS` (placeholder auto-creation), `_DELETE_NAME_LOOKUP`,
   `_COMPOUND_TARGET_REGISTRY` (compound delete keys), `_ORPHAN_REGISTRY` (children to mark on
   delete), `FIELD_MAPPING_CONFIG` (FK display fields for query results).
6. **Tools**: add a row to `crud_factory._ENTITIES` (gives you add/update/delete single+batch
   tools) and list/search tools in `query_tools.py`.
7. **Enums**: add `EntityType` / `AuthResource` entries and any domain enums in `utils/enums.py`.
8. **Mock/tests**: add a TABLE_META row in `mock/mock_product_data.py` and seeded fixtures in
   `tests/conftest.py`.

## Rename the project (one command)

The template uses placeholder naming (`service_mcp` / `service-mcp` / "Service MCP"). To create a
new project from this template:

```bash
uv run python scripts/rename_project.py my_company \
    --project my-company-mcp --display "My Company MCP" --db my_company_data
uv sync            # regenerate uv.lock / reinstall
uv run pytest      # confirm green
```

The script rewrites all file contents and renames the package directory. Run with `--dry-run` to
preview. `uv.lock` is intentionally skipped — regenerate it with `uv sync`.

## Docker deployment

```bash
cp docker/.env.example docker/.env   # edit passwords/DB names
./docker/ctl.sh deploy -e prod       # or: ctl.ps1 on Windows
```

Infra only (app runs locally):

```bash
cd docker && docker compose up -d
```

Services: PostgreSQL 18 (5432), Redis 8 (6379), optional pgAdmin (5050).

## Configuration reference

| Env var | Meaning | Default |
|---|---|---|
| `MCP_ENV` | environment name; selects `configs/config.{env}.toml` | `dev` |
| `MCP_CONFIG_PRIORITY` | `init_first` / `env_first` / `toml_first` / `env_only` / `toml_only` | `init_first` |
| `MCP_TRANSPORT` | default transport | `stdio` |
| `MCP_HOST` / `MCP_PORT` / `MCP_UI_PORT` | HTTP transport bindings | `0.0.0.0` / `8001` / `8080` |
| `MCP_CACHE_ENABLED` | enable Redis cache | `true` |
| `MCP_AUTH_MODE` | `tool` or `admin` (JWT) | `tool` |
| `MCP_DATABASES__<NAME>__*` | per-database config (nested `__`) | — |
| `MCP_LOGGING__*` | logging config (console/file/JSON rotation) | — |

## Testing & quality

```bash
pytest                          # all tests (in-memory SQLite, no external services)
ruff check .                    # lint
ruff format .                   # format
mypy service_mcp                # type check
```

## License

MIT