Automating Counterparty Verification with AI: SQL Analysis of Federal Tax Service Data to Reduce Risks

## Problem: Due Diligence in the Era of "Paper" Solutions Every entrepreneur knows: choosing an unverified counterparty can cost millions. According to the Central Bank of the Russian Federation, in 2025 alone, the volume of suspicious transactions involving shell companies exceeded 1.2 trillion rubles. Manual verification through the Federal Tax Service website takes tens of minutes per counterparty, and with a scale of 500+ partners per month, it amounts to weeks of pure work. The classic approach of "checked the TIN — okay" has long ceased to work. Today, a comprehensive analysis is needed: registration dynamics, changes of directors, mass addresses, tax debts. But how to combine all this data without drowning in Excel spreadsheets? The answer is automation through a combination of SQL and AI. Imagine: you load open Federal Tax Service data into your database, SQL cleans and structures it, and an AI model assigns a risk score to each counterparty. Below is a real case of how we implemented such a system in 3 weeks. ## Case Study: How We Built an AI Counterparty Verification System ### Initial Data The company "TechnoLogistics" (B2B, 1200+ counterparties per year) faced a problem: 15% of contracts were concluded with problematic partners, leading to additional tax assessments of up to 8 million rubles annually. Manual verification of one counterparty took 45 minutes, and a due diligence department of 3 people simply could not cope with the volume. **Project Goals:** - Reduce verification time to 2-3 minutes per counterparty - Reduce the share of problematic partners to 2% - Automate monthly status monitoring ### Stage 1: Loading Federal Tax Service Data into an SQL Database Open data from the Federal Tax Service includes 7+ million records on legal entities, available through the data.gov.ru portal. We took four key datasets: - Unified State Register of Legal Entities (registration data) - Register of disqualified persons - Information on mass addresses/managers - Debt data (available via the Federal Tax Service API) The PostgreSQL schema looked like this: ```sql CREATE TABLE counterparties ( inn VARCHAR(12) PRIMARY KEY, name_short VARCHAR(255), registration_date DATE, status VARCHAR(50), address VARCHAR(500), director_name VARCHAR(255), okved_code VARCHAR(10) ); CREATE TABLE tax_debts ( inn VARCHAR(12) REFERENCES counterparties(inn), debt_amount DECIMAL(15,2), debt_date DATE ); CREATE TABLE risk_factors ( inn VARCHAR(12) REFERENCES counterparties(inn), factor_type VARCHAR(100), factor_value VARCHAR(255) ); ``` The data was loaded via an ETL script in Python (library `psycopg2`), which parsed XML files from the Federal Tax Service portal. An important nuance: the datasets are updated once a month, so we set up a `cron` job for the first day of each month. ### Stage 2: SQL Analysis and Feature Engineering for AI Before passing data to the neural network, we needed to create feature engineering — a set of numerical features on which the AI would base its decision. Here are the key SQL queries we used: **1. Company Age and Change Dynamics** ```sql SELECT inn, EXTRACT(YEAR FROM AGE(CURRENT_DATE, registration_date)) AS company_age, (SELECT COUNT(*) FROM counterparty_changes WHERE counterparty_changes.inn = c.inn) AS changes_count FROM counterparties c; ``` **2. "Mass Address" Indicator** ```sql SELECT address, COUNT(*) AS companies_at_address FROM counterparties GROUP BY address HAVING COUNT(*) > 50; ``` **3. Tax Burden** ```sql SELECT c.inn, COALESCE(SUM(td.debt_amount), 0) AS total_debt, CASE WHEN SUM(td.debt_amount) > 1000000 THEN 'high' WHEN SUM(td.debt_amount) BETWEEN 100000 AND 1000000 THEN 'medium' ELSE 'low' END AS debt_risk FROM counterparties c LEFT JOIN tax_debts td ON c.inn = td.inn GROUP BY c.inn; ``` In total, we formed 14 features: from the frequency of director changes to the number of branches. This data was exported to CSV for model training. ### Stage 3: AI Risk Assessment We chose gradient boosting (XGBoost) as the classification model: 0 — low risk, 1 — medium, 2 — high. The training set consisted of 50,000 counterparties, manually labeled based on historical data on defaults and tax violations. Model Metrics: | Metric | Value | |---------|----------| | Accuracy | 0.94 | | Precision (class 2) | 0.91 | | Recall (class 2) | 0.88 | | F1-score | 0.89 | The AI assigned each counterparty a score from 0 to 100. Thresholds: 70 — red (automatic rejection). ### Stage 4: Integration into the Workflow The system worked as follows: 1. The manager enters a TIN into the web interface (or uploads an Excel list) 2. The backend (Node.js) sends a request to PostgreSQL 3. The SQL function `get_risk_score(inn)` returns a pre-computed score 4. If data is not in the database, a real-time Federal Tax Service parser is launched 5. Result: green/yellow/red indicator + detailed report Example SQL function: ```sql CREATE OR REPLACE FUNCTION get_risk_score(p_inn VARCHAR) RETURNS TABLE( risk_level VARCHAR(20), risk_score INT, reasons TEXT[] ) AS $$ BEGIN RETURN QUERY SELECT CASE WHEN ai_score >= 70 THEN 'high' WHEN ai_score BETWEEN 30 AND 69 THEN 'medium' ELSE 'low' END, ai_score, reasons_list FROM risk_scores WHERE inn = p_inn; END; $$ LANGUAGE plpgsql; ``` For integration with CRM (e.g., AmoCRM), we used a REST API. ASI Biont supports connection to AmoCRM via API — more details at asibiont.com. ### Results After 6 Months | Indicator | Before Implementation | After Implementation | |------------|--------------|-----------------| | Verification time per counterparty | 45 min | 2 min | | Share of problematic counterparties | 15% | 3% | | Additional tax assessments | 8 million rub./year | 1.2 million rub./year | | Due diligence department workload | 100% | 25% | | Missed risks | 12% | 2% | Savings amounted to 6.8 million rubles per year + freeing up 2 employees for strategic tasks. ### Technical Details: Stack and Architecture - **Database**: PostgreSQL 16 (indexes on `inn`, `registration_date`, `status`) - **AI Model**: XGBoost 2.1 (Python, scikit-learn) - **ETL**: Apache Airflow + psycopg2 - **API Layer**: FastAPI (Python) or Express.js (Node.js) - **Monitoring**: Grafana + Prometheus for tracking request volume and errors An important point: for working with large volumes (millions of records), we used table partitioning by registration date: ```sql CREATE TABLE counterparties_partitioned ( LIKE counterparties INCLUDING ALL ) PARTITION BY RANGE (registration_date); CREATE TABLE cp_2020 PARTITION OF counterparties_partitioned FOR VALUES FROM ('2020-01-01') TO ('2021-01-01'); -- similarly for other years ``` This sped up queries by 40%. ## How to Implement This Yourself: Step-by-Step Plan 1. **Gather requirements**: what specific risks do you want to track? Only tax debts or also court cases, charter changes? 2. **Set up ETL**: use Python or Node.js to download Federal Tax Service data. Subscribe to updates via the portal's RSS feed. 3. **Create an SQL schema**: normalize data to 3NF, but for analytics, denormalization is acceptable (star or snowflake). 4. **Prepare features**: write SQL queries to calculate all metrics. This is the most labor-intensive part. 5. **Train the model**: start with simple logistic regression, then move to XGBoost or LightGBM. 6. **Integrate into the business process**: create a web form for entering TIN or uploading a list. 7. **Set up monitoring**: AI models degrade — retrain quarterly on new data. ## Pitfalls and How to Avoid Them - **Federal Tax Service data is incomplete**. For example, debt information may be delayed by 2-3 weeks. Solution: combine with paid sources (SPARK, Interfax). - **AI can make mistakes on new types of counterparties**. Solution: implement human-in-the-loop — if the score is in the yellow zone, a human makes the decision. - **SQL queries slow down on large data**. Solution: use materialized views for pre-computed metrics, update them once a day. ## Conclusion Automating counterparty verification with AI and SQL is not futurism but a working tool of 2026. All components are available: open Federal Tax Service data, free SQL databases (PostgreSQL), powerful AI libraries (XGBoost, CatBoost). You can start small: load Federal Tax Service data into PostgreSQL, write 3-4 SQL queries to identify mass addresses and "young" companies. This alone will reduce risks by 30-40%. And when you add AI, you'll get a system that works 24/7 and makes fewer mistakes than a human. Want to dive deeper into SQL for analytics and database design? The full SQL and relational databases course on ASI Biont will teach you to work with PostgreSQL and MySQL, design schemas, write complex queries, and optimize them with indexes. You'll master transactions, normalization, and working with large data volumes — everything needed to build due diligence-level systems. Try the free module right now — and take the first step toward automation that will save your business millions.

Automating Counterparty Verification with AI: SQL Analysis of Federal Tax Service Data to Reduce Risks

Comments

Recent articles

Fine-tuning LLM vs RAG in 2026: A Data-Driven Cost, Latency & Accuracy Showdown for Production

How to Automate Primary Document Processing with AI Agents in 2026: A Step-by-Step Guide

Neo4j vs ArangoDB 2026: The Battle of Graph Databases — Which to Choose for Your Project?

MLOps Trends in 2026: How Feature Store and Model Serving Are Changing Production ML

How to Build a Production-Ready RAG Pipeline from Scratch: A Step-by-Step Guide

The 30-Year-Old Bug That Finally Got Fixed: Windows UI Evolution and the Unassociated File Click

Try Deep Think in the Gemini App: My First Week Testing Google’s Reasoning Mode

Rust vs Go for High-Load Backend: Performance, Safety, and Development Comparison in 2026

How to Build a Real-Time Anomaly Detection System for IoT Sensor Data Using Prophet: A Step-by-Step Guide