SORM Module for MFO

A standalone lawful intercept module (PTS ORM) built for a microfinance organization's information system. The module ingests data from internal services, stores it in PostgreSQL, and exposes it to an authorized government control panel via a regulated GraphQL API over a dedicated encrypted channel.

Architecture

Deployed as an independent software module (variant «б» per PTS requirements) with its own database, isolated from the main information system. Built with Python 3.11+, PostgreSQL 15+ (PostGIS, pg_trgm), Strawberry GraphQL, and Uvicorn/Starlette. Offline task processing via Celery + Redis.

GraphQL API

Four endpoints: /query (HTTP 1.1) for search and special queries with cursor and offset pagination, /subscription (WebSocket, graphql-transport-ws subprotocol) for real-time data subscriptions and system trap signals, /download for raw file transfer with chunked encoding and range requests, and /metric with Prometheus-compatible PromQL endpoints for monitoring.

Data Model

Five entity types ingested from internal services via a validated REST API: Users (with SCD Type 2 change history), AAA events (login/logout), Service events (account creation, loan issuance/repayment), Communication events, and Payment events. All tables are monthly-partitioned by timestamp for efficient 3-year retention (~1 TB projected).

Offline Query Engine

Complex queries that exceed configurable timeouts are automatically routed to Celery workers. The control panel subscribes to status updates (NOTSTARTED → RUNNING → READY) via WebSocket and retrieves results when ready. Supports cancellation, deletion, and TTL-based automatic cleanup.

Security

Mutual TLS (X.509) authentication with per-client certificates over a VPN-tunneled dedicated channel. Comprehensive audit logging of all sessions, queries, configuration changes, and unauthorized access attempts. Trap signals (UNAUTHORIZEDACCESS, CRITICALERROR, SCHEMACHANGED, etc.) are pushed to the control panel in real time, with guaranteed delivery on reconnection.

Key Highlights

• GraphQL + WebSocket — regulated API with real-time subscriptions (graphql-transport-ws)
• Offline query engine — Celery-based async processing with status tracking
• 3-year retention — monthly partitioning, ~1 TB projected storage
• Full compliance with Federal Law No. 153-FZ — personal data handling per Russian regulatory requirements
• Prometheus monitoring — PromQL-compatible /metric endpoint
• Trap signals — real-time system health notifications with guaranteed delivery
• ~80K events/day — validated, normalized, and indexed for full-text search