Database Migration Auditor

Project roadmap

1. 01

   Set up the project and test databases

   ~2h

   Create the project with a cpanfile (DBI, DBD::Pg, DBD::SQLite, LWP::UserAgent, JSON::PP) and bin/audit with Getopt::Long options: --dsn for the live database, --migrations (a folder or URL), --format text or json, and --webhook. Write a set of sample migrations in migrations/ and a script that creates a live test database with a few deliberate differences: an extra index, a changed type and a missing column. Done when: both test databases exist, and bin/audit --help works.

2. 02

   Read the live schema with DBI

   ~3h

   Write a Schema::Reader module that connects in read-only mode (set the PostgreSQL session to read-only right after connecting) and collects every table with its columns (type, nullable, default), primary key, indexes (name, columns, unique) and foreign keys, using DBI's table_info, column_info and foreign_key_info, plus catalog queries where DBI doesn't provide enough, such as indexes. Store it all in one plain Perl data structure. Done when: dumping the structure for the test database shows every table, column, index and foreign key.

3. 03

   Build the expected schema

   ~2h

   Create a temporary empty database (a throwaway schema or database), apply the migration files in order by their numeric prefix, then read it with the same Schema::Reader, so both sides are described the same way. Stop with a clear error, naming the file, if a migration fails. Always clean up the temporary database, even after an error. Done when: the expected schema is built from the migrations and can be dumped.

4. 04

   Normalize and compare

   ~3h

   Normalize both structures so harmless differences don't show up: type aliases (int4 and integer), default value formatting, and generated names for constraints and indexes, compared by their content instead. Then compare them table by table and report: missing and unexpected tables, columns, indexes and foreign keys, and changed types, nullability and defaults. Give each difference a severity: critical, warning or info. Done when: the comparison finds exactly the differences built into the test database.

5. 05

   Write clear reports

   ~1.5h

   Print a text report grouped by table, with each difference showing what was expected and what was found, for example column orders.total: type expected numeric(10,2), found double precision, followed by a summary by severity. With --format json, print the same data as JSON. Exit with code 2 for critical differences, 1 for warnings and 0 when everything matches. Done when: the report is readable, and the exit codes match.

6. 06

   Fetch migrations and post reports with LWP

   ~1.5h

   When --migrations is a URL (for example a raw file listing from a Git hosting service, given as a JSON list of file URLs), download each file with LWP::UserAgent with a timeout, and stop if any download fails. With --webhook, POST the JSON report to that URL after the audit. Never send database passwords or connection strings in the report. Done when: auditing with migrations served from a local HTTP server works, and the report reaches a test webhook.

7. 07

   Write tests

   ~2h

   Write tests in t/ with Test::More using SQLite databases created in the tests: identical schemas report nothing; an extra column, a missing index, a changed type and a missing foreign key are each detected with the right severity; and normalization ignores harmless differences. Done when: prove -l t/ passes.

Resources

• DocsDBI on MetaCPAN
• DocsDBD::Pg on MetaCPAN
• DocsPostgreSQL: information schema
• DocsLWP::UserAgent on MetaCPAN
• DocsTest::More on MetaCPAN