Module schema 

SQL schema definitions for the mcpr storage engine.

All table and index definitions live here as constants. The migration runner in super::db executes these on first open and on version upgrades.

Schema design decisions

• Two tables: requests (one row per MCP request) and sessions (one row per MCP session). Client identity lives in sessions only — no denormalization.
• Soft foreign keys: requests.session_id references sessions.session_id but without a FOREIGN KEY constraint. SQLite FK enforcement requires per-connection pragmas and can cause constraint violations on ordering edge cases in async writes.
• No body storage: Request/response bodies can be MB-scale. Only metadata is stored.
• Timestamps as unix milliseconds: Sufficient resolution, avoids i64 overflow, and is the natural unit for latency math.
• UUIDv7 for request_id: Time-ordered for efficient indexing, globally unique for cloud sink correlation.

Constants

CLOSE_SESSION_SQL

Mark a session as ended (clean transport close). Only updates if not already ended (idempotent).

GET_SCHEMA_HASH_SQL

Fetch the current schema_hash and payload for a given proxy+upstream+method. Used by the writer to detect changes before upserting. Parameters: ?1=proxy, ?2=upstream_url, ?3=method.

INSERT_REQUEST_SQL

INSERT a new request row. All parameters are positional (?1 .. ?14).

INSERT_SCHEMA_CHANGE_SQL

Insert a schema change record into the append-only log. Parameters: ?1=proxy, ?2=upstream_url, ?3=method, ?4=change_type, ?5=item_name, ?6=old_hash, ?7=new_hash, ?8=detected_at.

INSERT_SESSION_SQL

INSERT a new session row. Uses INSERT OR IGNORE because a reconnecting client may re-send initialize with the same session ID.

SCHEMA_VERSION

Current schema version. Stored in the meta table and checked on startup. Bump this when adding migrations.

UPDATE_SESSION_COUNTERS_SQL

UPDATE session counters and last_seen_at. Executed in the same transaction as the request INSERT to keep counters consistent.

UPSERT_MCPR_VERSION

SQL to insert or update the mcpr_version meta key on every startup.

UPSERT_SERVER_SCHEMA_SQL

UPSERT a server_schema row. ON CONFLICT updates the existing row. Parameters: ?1=proxy, ?2=upstream_url, ?3=method, ?4=payload, ?5=captured_at, ?6=schema_hash.

V1_META_SEED

SQL to insert the initial meta rows after schema creation.

V1_SCHEMA

Initial schema: requests table, sessions table, meta table, and all indexes.

V2_SCHEMA

V1 → V2 migration: add server_schema and schema_changes tables.

V3_SCHEMA

V2 → V3 migration: add proxy column to server_schema and schema_changes.

V4_SCHEMA

V3 → V4 migration: rename latency_ms → latency_us and convert existing values from milliseconds to microseconds for sub-ms precision.

V5_SCHEMA

V4 → V5 migration: add resource_uri and prompt_name columns to the requests table. Captured by the proxy’s TargetExtractMiddleware: resources/{read,subscribe,unsubscribe} → params.uri → resource_uri prompts/get → params.name → prompt_name