Tabular Records Validator

Automation that acts as a quality filter between record capture and the system that will consume them: it reads a spreadsheet, blocks whatever is inconsistent while explaining the reason for each rejection, prioritizes what is valid by an urgency criterion, and even attempts to automatically recover, with AI, the records that were blocked.

The original use case is factory orders (made-to-order production), but the logic applies to any set of tabular records that arrives inconsistent and needs review before moving forward — imports, registrations, system integrations. The business rules live in config.yaml; changing the domain means editing YAML, not code.

Live service: https://validador-pedidos-gocase.onrender.com

The problem

Whenever records enter a system from multiple sources, each source validates at its entry point differently — or does not validate at all. The result is a batch where perfect records coexist with records that have an empty required field, a broken email, a zeroed number, a value that does not add up, a date in the past, or duplicates.

Checking this by hand is slow, tiring, and lets subtle errors slip through — a difference of cents, a duplicate separated by dozens of rows. Worse: a valid record can be rejected due to a filling error, not a content error — a missing name, an @ that disappeared from an email. The correct data exists; it just did not arrive formatted.

In the original use case, each record is an order that becomes a physical production order. An order with broken data is not just a wrong record — it is custom material wasted, machine time lost, and a customer left without delivery. It is the same pattern as any flow where a bad record costs dearly downstream.

Related MCP server: fcp-sheets

How it works

The core is a four-step pipeline, exposed through three surfaces (terminal, HTTP API, MCP) that call the same function:

flowchart LR
    A[Planilha .xlsx] --> B[Leitura + schema]
    B --> C[Validação<br/>9 regras]
    C -->|válidos| D[Priorização<br/>por prazo]
    C -->|rejeitados| E[Recuperação por IA]
    E -->|corrigido| C
    E -->|indeduzível| F[Revisão humana]
    D --> G[3 planilhas .xlsx]
    C --> G

1. Reading (src/leitor.py) — reads the Excel file, types columns, and checks the expected schema. A missing column becomes a readable error, not a generic failure.

2. Validation (src/validador.py) — applies the 9 rules to each record; separates valid from rejected; accumulates all reasons per record.

3. Prioritization (src/organizador.py) — calculates dias_restantes and sorts the valid ones into an urgency queue.

4. Report (src/relatorio.py) — generates the 3 formatted spreadsheets.

5. AI recovery (src/assistente_ia.py, optional) — attempts to recover the rejected records; what the AI fixes goes back through validation, which makes no exceptions.

How the operator uses it

1. Opens the form in the browser.

2. Uploads the .xlsx spreadsheet.

3. Receives a .zip back with the three ready spreadsheets.

Nothing is installed on anyone's machine: processing runs on the server and the result comes back through the browser. The form is published by the n8n flow that ships with the project in integracoes/, imported a single time. Those who do not use n8n consume the API directly — the contract is in the same guide.

To try it without preparing data, the repository includes exemplo/pedidos_exemplo.xlsx: 50 records, of which 10 contain representative defects.

First run of the day. The service is hosted on a free plan and hibernates after a few minutes without use. The first call takes about 50 seconds to wake the server; subsequent ones respond in under 1 second. If the flow reports a timeout on the first attempt, just repeat.

Validation rules

Each record is evaluated against all rules. A record can accumulate multiple reasons, concatenated in the motivo_rejeicao column — the complete list of problems at once, not one error per reprocessing pass.

The field names above are those of the original domain (orders). The mapa_colunas in config.yaml translates the headers of any export to these names, so a spreadsheet from another system does not require new code.

Priority

The approved records receive dias_restantes and enter a queue sorted by urgency — the tightest deadlines first. The bands (names, ranges, and colors) live in config.yaml.

What is delivered

Stack

Python 3.10+.

Measured result

Demo batch: 50 records, with 10 real problems.

The numbers above come from running over exemplo/pedidos_exemplo.xlsx (synthetic data), measured locally. They are not a projection of real production volume.

What the AI fixed in the real run

What it correctly did not resolve

Of the 10 rejected records, 5 remained — and that is how it should be:

• 2 duplicates — require a human decision on which record counts.

• 1 overdue deadline — not a data error, but an operational problem.

• 2 inconsistent values — the AI adjusted the quantity, but the valor_total did not add up, so the record remained rejected. Validation makes no exception for the AI.

AI layer — recovery of rejected records

Blocking a record solves half the problem. The other half is recovering it when the error is one of filling, not content. The division of labor is explicit:

• Mechanical error (value that does not add up, extra space, email to normalize) → resolved by rule, without AI.

• Semantic error (missing name, incomplete email) → the AI infers by cross-referencing the other fields of the record itself.

• Data impossible to infer → flagged for human review, never invented.

Audit trail

Automatic correction is only trustworthy if it is auditable. The AI signs what it did, inside the delivered spreadsheets:

• Column corrigido_por_ia marks the recovered records.

• Column correcao_ia records the before → after of each changed field.

• The summary includes the line "Records recovered by AI".

Inferring the name from the email is a plausible inference, not confirmed data. That is why the trail exists: the AI speeds up recovery and the final decision remains verifiable by a person.

Architecture

Single responsibility per module — each file does one thing and is testable in isolation.

Single source of truth. The flow lives in executar_pipeline; metrics are built once and reused by the report, the log, and the API. Names, order, and colors of the priority bands exist only in config.yaml.

Consumption methods

One validation logic, three surfaces — no duplicated rule.

n8n runs the batch automation; MCP lets you query it in natural language — "how many records were blocked and why?". To enable it in a compatible client (Claude Desktop, for example), point it to the server:

{
  "mcpServers": {
    "validador-gocase": {
      "command": "python",
      "args": ["mcp_server.py"],
      "cwd": "caminho/para/validador-pedidos-gocase"
    }
  }
}

Exposed tools: validar_pedidos, consultar_resumo, analisar_rejeitados, revalidar_com_correcoes and gerar_dados_exemplo, plus a guide prompt. The middle two form the assisted correction loop: the client's own model proposes the corrections and the server revalidates.

The integration does not lock the tool in: since it is pure HTTP, Make, Power Automate or custom code consume the same API. n8n is the documented and tested path.

No-code setup

Business rules live outside the code, in config.yaml: value tolerance, email pattern, required columns and the priority ranges (names, ranges and colors). A manager adjusts limits without opening Python.

mapa_colunas translates the headers from a real export into the expected names — it is the domain swap point: another spreadsheet, same logic.

Missing or invalid configuration does not bring anything down: the system warns and uses the built-in defaults.

Tests

testar.py runs 13 end-to-end checks, with no external framework — it is a script that runs the real flow and verifies invariants:

• generation of the sample spreadsheet and pipeline execution;

• existence and content of the 3 spreadsheets and the log;

• consistency (approved + rejected = total);

• presence of a reason in all rejected records;

• the API (validation, package download, refusal of an out-of-format spreadsheet with a readable error);

• the MCP Server, exercised through the real protocol: handshake, tool catalog and one tool executed end to end.

Other built-in safeguards: a report open in Excel is handled with retries and a clear message; malformed correction coming from the AI is discarded without bringing down the batch; temporary server files expire on their own in 1 hour.

python testar.py

How to run

Prerequisites: Python 3.10+.

# 1. Dependências
pip install -r requirements.txt

# 2a. Modo terminal — gera dados de exemplo se não houver planilha real
python main.py

# 2b. Modo API HTTP
uvicorn api:app --host 0.0.0.0 --port 8000
# Docs interativas em http://localhost:8000/docs

To use the real spreadsheet, save it to data/pedidos_entrada.xlsx before running main.py.

Environment variables (optional)

All have defaults; none is required for validation. AI correction only turns on when the key is present.

The key never lives in the repository — only in the server environment.

Limitations and next steps

Scope of this delivery. The API is published without authentication, by scope decision. The URL should only be used with the demo spreadsheet (synthetic data); real records contain personal data and require key-based authentication before traveling over an open URL. This is a conscious roadmap step, not an oversight.

What would break at larger scale. Processing is synchronous and loads the entire spreadsheet into memory (pandas) — suitable for batches of thousands of rows, not millions. Duplicate detection only looks within the current batch, not across runs.

Natural evolution. Read records directly from the source (ERP, database) instead of a spreadsheet; write the status back to the source system; active notification when the rejection rate rises; history across batches to detect duplicates that span runs.

Project origin

This project started as a business case for the RPA Internship selection process at GoCase (GoGroup), Factory Operations area. The original domain is validation of on-demand production orders, where each broken record becomes spent custom material and lost machine time.

The documentation was generalized because the solution — automatic checking of tabular records that arrive inconsistent, with recovery of what is a filling error rather than a content error — applies to any flow of the same kind. The order vocabulary remains in the rules and examples because it is the real measured case, not because it is the only applicable one.