soft1-mcp

Imported from cpouldev/soft1-mcp (AGENTS.md). Install upstream with npx skills add cpouldev/soft1-mcp. Copyright stays with the author.

Working principles

• Clarify requirements that would materially change architecture or product behavior. For small gaps, choose the simplest safe interpretation and record it.
• Preserve unrelated work in this fresh worktree. Do not reset, overwrite, or broadly reformat files outside the task.
• Prefer small, typed, testable changes. Keep external side effects behind durable local state and idempotent/retry-safe boundaries.
• Treat eligibility, consent, billing, authentication, and account deletion as correctness-critical. Fail closed when source evidence or state is ambiguous.
• Never commit plaintext credentials, local certificates, database dumps, or production environment files.

Overview

Standalone, company-agnostic Python 3.13 MCP server exposing one Soft1 ERP SQL Server database for read-only analysis. Surface: tools s1_sql, s1_tables, s1_describe, s1_status, kb_search, kb_get; routes /healthz and /exports/{token}; five analyst prompts. Soft1 is the only business database — there is no application DB.

Commands

uv sync                                    # install (once)
uv run python main.py                      # serve on :6097
uv run pytest                              # baseline: 346 passed, 8 skipped (~9s)
uv run pytest tests/test_sql_guard.py      # one file
uv run pytest tests/test_config.py::test_valid_production_configuration_is_accepted
uv run pytest -k "placeholder or allowlist"
uv run ruff check . && uv run ruff format --check . && uv lock --check

One-time local setup for the two DuckDB-dependent test files and XLSX export (the Docker image pre-bakes these; a local machine must install them once):

uv run python -c "import duckdb; c=duckdb.connect(); c.execute('INSTALL fts'); c.execute('INSTALL excel'); c.close()"

tests/integration/ is collected by default and self-skips per test. There is no shared gate in tests/integration/conftest.py — each test reads env vars inline:

SOFT1_MCP_DSN='Driver={ODBC Driver 18 for SQL Server};Server=...' \
MCP_REDIS_TEST_URL='redis://localhost:6379/9' \
uv run pytest tests/integration

Docker / shell changes: docker build --check . and bash -n scripts/smoke-docker.sh are the cheap pre-checks; ./scripts/smoke-docker.sh is the full gate (builds, verifies ODBC Driver 18, loads DuckDB extensions with --network none, asserts uid 10001 and that dev deps did not leak into the image, then polls the healthcheck).

Architecture

Composition and discovery

main.py → init_sentry → build_server() → initialize_catalogs() → serve. Catalogs load after registration, and startup tolerates an unreachable Soft1.

src/server.py::_register_tool_modules walks src/tools/ with pkgutil and calls any module-level register(mcp) (optionally taking export_service=). Adding a tool namespace means adding a module, never editing the composition root. Modules prefixed with _ have no register and are deliberately invisible to discovery — they are private stages of one pipeline, not utilities.

Import/registration errors are intentionally uncaught: a loud boot failure beats serving a partial analytics surface.

Layering

Dependency inversion is typing.Protocol throughout (src/tools/_query_contract.py, src/catalog/adapters.py, src/catalog/cache.py, src/kb/ports.py). Concrete wiring lives only in composition roots (src/kb/composition.py, src/catalog/service.py), so tests inject fakes via constructor args rather than monkeypatching.

Query pipeline

Every read funnels through src/tools/_query.py::execute_and_audit_read_query, in order: caller identity → require_soft1_live_queries preflight (before any connection opens) → mode/format validation → guard + row cap → execute → normalise → envelope or export → audit. s1_query.py only supplies executors and policy callbacks. Audits are written on both success and failure, as structured logs — never to a database.

src/sqlguard.py parses with sqlglot in tsql dialect only. It accepts exactly one root SELECT (parsed as plural to expose stacked statements) and rejects EXEC, SELECT INTO, NEXT VALUE FOR, OPENDATASOURCE/OPENQUERY/OPENROWSET, four-part linked-server names, and nested DDL/DML inside CTEs and subqueries. cap_rows_with_status injects T-SQL TOP; a concrete existing TOP/FETCH under the limit is preserved, while PERCENT, WITH TIES, parameterized, and oversized bounds are replaced.

Adapters call cap_rows again on already-bounded SQL. This double-guarding is deliberate defence at the adapter boundary and is idempotent — do not "optimize" it away.

Limits live in one place each and are asserted by tests:

Envelopes and exports

envelope.build_envelope measures the serialized payload and, on overflow, binary-searches the largest complete-row prefix that fits 25 000 chars, sets truncated=True, and appends guidance steering the caller to mode="export". Optional payload keys are dropped before rows are sacrificed. Export mode bypasses the envelope entirely and returns {url, rows, size, expires}.

Export policy fetches max_rows + 1 — that sentinel row is how the writer detects overflow. Artifacts are written O_EXCL 0600, published with os.replace, and served through an HMAC capability (itsdangerous, salt soft1-mcp-export-v1, 24h) that re-validates the filename regex and rejects symlinks and paths escaping EXPORT_DIR. Retention sweeps 7-day-old service-owned files at startup and on every export. Rotating MCP_STORAGE_ENCRYPTION_KEY silently invalidates every outstanding download link.

Catalog

get_catalog_registry() is an lru_cache(maxsize=1) singleton. CatalogRuntime (src/catalog/runtime.py) owns all mutable state under one threading.Condition, so concurrent requests coalesce onto a single in-flight reflection. Reflection is two batched metadata queries (src/catalog/queries.py); the resulting snapshot is immutable.

Two cache tiers: in-memory is authoritative; Redis (src/adapters/catalog_cache.py) is consulted only when memory is empty, never on TTL refresh. A Redis-loaded snapshot keeps its original reflected_at, so TTL survives restarts correctly.

get_snapshot() may block on synchronous ODBC I/O; peek_snapshot() never does. Any new status or health path must use peek_snapshot() or it turns observation into reflection — this is why s1_status keeps working while Soft1 is down.

Schema tool output is budgeted, not truncated blindly: s1_describe grows its three streams round-robin one item at a time and returns per-stream next_offset, raising DescribeBudgetError rather than silently dropping metadata.

Knowledge base

Corpus = three bundled markdown files in src/kb/ (routing.md, soft1-cookbook.md, soft1-table-notes.md) plus schema chunks derived from the catalog. Indexed in a process-local in-memory DuckDB with stemmer='greek', strip_accents=1; Greek keywords are baked into the domain vocabulary so Greek queries match English-named tables. Rebuilds create a new physical table generation and swap atomically.

src/kb/soft1_semantics.py labels every meaning with a four-level SemanticConfidence: documented (curated), structural (derived from real FKs/PKs), inferred (name patterns), unknown. Soft1 installations differ by version, module set, and CCC* customizations, so an inferred meaning is a guess about grain and sign convention, not a contract. Never present inferred semantics as verified — s1_describe surfaces meaning_confidence per column precisely so callers can tell them apart.

Only search_text and title are FTS-indexed. A fact added only to full_content is unfindable.

Auth and config

config.py is a single pydantic-settings AppConfig behind get_config() (lru_cache), with a dev-safe default for every field — the server boots with zero configuration. validate_production_config is a no-op unless APP_ENV == "production"; there it rejects placeholder values (_is_placeholder: dev, change-me, todo, <...>, …) in ten required settings, requires an HTTPS non-loopback MCP_BASE_URL, validates both Fernet keys, and requires the FastMCP Host/Origin allowlists to match the base URL exactly.

Google is the upstream OAuth provider; FastMCP supplies the DCR/PKCE facade; a global AuthMiddleware enforces the email allowlist and fails closed on a missing token or non-string email claim. In development, placeholder keys are swapped for process-stable generated Fernet keys.

Project invariants

These are architectural commitments, not preferences:

• Company-agnostic. Never commit customer names, domains, hosts, credentials, database names, numeric company IDs, document-series codes, or deployment evidence. Treat grants, TLS trust, varchar encoding, and company scope as deployment config or facts to discover.
• One business database. Do not add a second application DB or cross-database path.
• Read-only, always. T-SQL SELECT / WITH ... SELECT only. Preserve validation, caps, timeouts, and external-data/linked-server rejection. Database grants vary per installation, so the application guard is mandatory regardless of them.
• No business rows at rest. Not in Redis, DuckDB persistence, logs, or committed knowledge files. Redis holds OAuth state and schema metadata only; DuckDB is process-local for FTS and XLSX generation only.
• Prefer small modules, typed dataclasses/Protocols, explicit error translation, and deterministic bounded outputs. Ruff at 100 columns. Update focused tests with behavior changes. Secrets in an untracked .env; generic defaults in mise.toml.

Testing notes

The default suite touches no network, Redis, or database. tests/conftest.py gives client_harness, which calls build_server(with_auth=False, config=...) and drives it over FastMCP's in-memory transport — no listener. tests/*_support.py modules hold the fakes (FakeMetadataAdapter + MutableClock for catalog, install_fake_query_runtime, install_schema_runtime, DB-API cursor/connection doubles, and an OAuth harness that exercises the real bearer path over httpx.ASGITransport with an in-memory store). Async tests use a plain run_async helper, not pytest-asyncio. Hypothesis is used in exactly two places: tests/test_top_injection.py and tests/test_envelope.py.

Gotchas

• DatabaseName is "soft1" at the catalog layer but "s1" at the tool/envelope layer; build_envelope hard-rejects anything but "s1". The namespaces are not interchangeable.
• build_envelope raising ValueError is a control-flow signal inside _schema_budget._candidate, which reads it as "doesn't fit". Changing the exception type breaks describe paging.
• truncated requires service_cap_applied. A caller-supplied TOP N under the limit never flags truncation, even at exactly N rows.
• KnowledgeBase.search rebuilds the corpus on every call, so editing a bundled .md takes effect without a restart — but kb_get doc ids are positional (section:chunk), so editing markdown silently invalidates previously returned ids. Schema ids are hashed from the identifier and stay stable.
• get_catalog_registry() and get_knowledge_base() are lru_cache(maxsize=1). Tests needing different config must inject through build_catalog_registry / KnowledgeBase, not monkeypatch config.
• Column.native_type is always None from live reflection — the reflection SQL never selects it. It exists only in the cache schema.
• XLSX export fully materializes into a pyarrow table; the 100 000-row cap is a memory boundary, not cosmetic.
• Calling s1_sql() directly instead of the registered closure passes export_service=None and needs configure_export_service — which is a ContextVar and will not cross contexts.
• src/adapters/soft1_dsn.py re-validates SOFT1_MCP_DSN overrides attribute by attribute; only PWD is caller-supplied.

Tooling

Use Serena MCP for Semantic Code Analysis instead of regular code search and editing

Serena MCP is available for advanced code retrieval and editing capabilities.

When to use Serena:

• Symbol-based code navigation (find definitions, references, implementations)
• Precise code manipulation in structured codebases
• Prefer symbol-based operations over file-based grep/sed when available

Key tools:

• find_symbol - Find symbol by name across the codebase
• find_referencing_symbols - Find all symbols that reference a given symbol
• get_symbols_overview - Get overview of top-level symbols in a file
• read_file - Read file content within the project directory

Usage notes:

• Memory files can be manually reviewed/edited in .serena/memories/
• This project's memories are core, conventions, tech_stack, suggested_commands, and task_completion; keep them in sync with substantive changes to this file.