Process: 02Attached to batchFiled against products and lots
Store: 03Archived, never deletedSuperseded versions kept
Process: 04Keyed REST pushSent to each store that sells it
Output: 05Live on the productQR label resolves to it
Approve a certificate once and it lands on each storefront that sells the product.
The problem
A testing operation ran on spreadsheets and shared folders. Certificates of analysis are records of record: lose one and you have a compliance gap, not an inconvenience. Anyone with access could overwrite or delete one.
What I built
Batches, products, certificates, testing labs and a compliance calendar in one application. The certificate history for a product hangs off the product, so finding it stopped depending on somebody's memory.
I took deletion out of the system. Every destructive path became an archive flag with a restore screen, and I rewrote sixty-four read queries to filter archived rows. Schema changes copy into the new shape and check row counts before I rename the old object. Nothing gets dropped.
A security pass closed the gaps that mattered. I revoked anonymous insert and delete on the certificate storage bucket, pinned the definer functions against search-path hijacking, and pulled execute permissions back to the roles that call them.
A catalogue consolidation folded 43 duplicate products into 30 keepers, taking 190 active products down to 147. I re-parented the variations and re-pointed every certificate and log, writing each original value to a backup table first so the whole merge reverses.
Label and QR printing, with the code on the vial resolving to the current certificate for that lot.
A companion WordPress plugin publishes an approved certificate to each storefront over a keyed REST API. It writes to the right field for the product type, swaps the old image out of the gallery without deleting the file, and records who published what.
An in-app assistant that calls the application's own service functions instead of writing SQL. Writes go preview, confirm, execute, and the card shows old-to-new values and the blast radius before anything runs.