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:

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

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

SELECT 
    address,
    COUNT(*) AS companies_at_address
FROM counterparties
GROUP BY address
HAVING COUNT(*) > 50;

3. Tax Burden

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:

The AI assigned each counterparty a score from 0 to 100. Thresholds: <30 — green zone, 30–70 — yellow (requires manual verification), >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:

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

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:

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.