Inspiration
When a measuring instrument fails calibration, the investigation reaches backward: which inspections used it, which lots depended on those decisions, and which orders already shipped? DriftTrace explores that problem for quality managers at small precision machine shops.
What it does
DriftTrace turns a pasted calibration notice into a reviewable investigation. LangChain and Gemini propose structured fields with source quotes. Code validates the evidence and exact instrument identity, then the reviewer confirms the investigation period using the last acceptable verification recorded in Db2.
The application traces instrument → inspection → lot → shipment. Each record receives a reasoned classification: needs review, outside this case, or insufficient data. Missing timestamps stay visible instead of disappearing from a filtered result.
An illustrative measurement-error bound shows which earlier acceptance decisions could cross specification limits under that assumption. It never removes an in-scope record or treats the observed calibration error as a proven historical correction.
The reviewer can save a case, reopen its evidence snapshot, export JSON/CSV, and append review outcomes with notes and remeasurement values.
How I built it
The application uses Python 3.12 and Streamlit, with LangChain structured output, Gemini and the native ibm_db driver. IBM Db2 Community Edition 12.1.5 stores eight related tables, with primary/foreign keys, CHECK constraints and indexes.
A Db2 aggregate view cross-checks exposure totals. Case, finding snapshots and audit events save in one transaction with rollback, stale-preview detection and duplicate-save protection. Database triggers enforce append-only history, while a dedicated application user has restricted grants. Setup and schema administration use separate credentials.
Challenges
The central challenges were preserving exact instrument identifiers, distinguishing missing evidence from a negative finding, avoiding duplicated quantities across inspection/shipment joins, and keeping hypothetical sensitivity separate from actual product decisions. Source validation, explicit review states, distinct-record aggregation and persisted snapshots address these problems.
Accomplishments
The synthetic example traces CAL-017 to three in-scope lots, two shipments containing 70 shipped units, and 40 unshipped units. A separate lot with a missing inspection timestamp remains in the insufficient-data category. The current build documentation records 40 passing tests, including live Db2 checks, and a clean-environment run. Screenshots and reproducible setup instructions are included in the repository.
What I learned
The useful boundary is clear: AI proposes facts from unstructured text; database records and deterministic checks establish lineage; the reviewer decides the investigation scope and records the outcome. Fixing an instrument does not by itself resolve questions about its previous use.
What's next
Support additional measurement functions and test ranges, import operational records from existing quality systems, and add reviewer assignment and authentication.
Scope and disclosure
All demonstration records and customer names are synthetic. DriftTrace produces an investigation worklist; it does not determine defects, safety, recalls or product release. The audit history is persistent and protected by database controls, not claimed to be tamper-proof. Built with AI coding assistance, disclosed in the README.
Built With
- ibm-db2
- langchain
- python
- streamlit
