After having merged the infrastructure to check data validity in an existing database before applying an upgrade script, I'm reviewing existing scripts to see if they may qualify to be enhanced with a check and user intervention.
One script which qualifies is 1.5/invoice-tbl-cogs-constraint.sql which adds a constraint verifying that the COGS routines post data which makes sense.
Existing data does not necessarily comply with the new constraint. I wonder though what corrective action to offer a user who is the proud owner of non-compliant data. I see a number of options:
1. Tell the user to contact the mailing list (and block his migration in the mean time)
2. Modify the data to be within the bounds to be enforced (and thereby loose the COGS audit trail?
3. Allow constraint creation to fail by modifying Loadorder and create a new script which creates a "not valid" constraint (which allows the existing data to remain in place, but verifies new data)
4. Create a check script which creates the "not valid" constraint and fakes script application by modifying dbchanges tables
5. Adjust the data back into bounds, allocating the overage to more recent invoices and creating a COGS adjustment journal which the admin must post
Which should we go with?
Robust and Flexible. No vendor lock-in.