CESVI RAT

The problem

CESVI México's accident experts recorded measurements on paper, ran calculations in spreadsheets and had no single system to look up cases. The RAT module (Reconstrucción y Análisis de Tránsito, traffic accident reconstruction and analysis) had to cover the whole workflow, from the scene survey to the final report, inside the base platform CESVI already uses.

What I built

I was the database coordinator on a team of 6 (28 of the project's 132 commits). My work was the data layer:

• The 54-table schema across 7 functional groups: 3 infrastructure, 1 for the AI module, 1 expert profile, 27 catalogs, 3 McHenry technical tables, 15 transactional tables (from incident to report) and 4 for the template system.
• Evolving the model through versions as the requirements became clear: 51 tables in the first, 49 in v1.1, 51 in v1.3 and 54 in v1.4.
• A 969-line SQL script for MySQL 8.0+ with InnoDB, foreign keys and seeded catalogs.
• 6 migrations that create the 54 tables, plus the Eloquent models. 50 of my 54 models and 3 of my 4 controllers made it unchanged into the team's backend, including the step-by-step case intake.
• The v1.4 data dictionary and the technical documentation for each version, so backend, frontend and client worked from the same model.

Architecture

Results

There are no usage metrics: the deliverable was the data model and its documentation. What can be checked is that the schema and dictionary are in the repository and that the team's backend uses my migrations and models.

Known limits

• The schema includes the A/B stiffness table by McHenry category and an endpoint to query it, but the coefficients aren't seeded and the backend doesn't compute speed. That was outside the delivered scope.
• The project has no tests of its own, no CI and no deployment, and the main README is a template.

Links

• Code on GitHub