Digital quality certificate for gearbox assemblies
Nine quality systems had to agree before one gearbox could be certified — and nobody could tell whether they had enough evidence to sign. We built the layer that reads all nine in place, refuses to certify on a partial sample, and issues the certificate.
It reads nine quality systems. It keeps two things.
- quality sources read in place, none copied
- 9quality sources read in place, none copied
- things the system stores: the certificate and the approval
- 2things the system stores: the certificate and the approval
- where thresholds and rules live, not in compiled code
- YAMLwhere thresholds and rules live, not in compiled code
A passing sample is not a passing gearbox
The client was explicit about this from the start: we do not want to take the small subset of parameters that are successful and call the whole component passed. So before any threshold is checked, the system asks a different question first — is there even enough evidence here to answer?
Evidence present
First-quality inspection
quality database
CoveredBolt tightening: torque and angle
tool management
Not answerable yetBreak-free torque
two test stations, TSV
CoveredVibration spectra
test rig
CoveredHydraulic tightening
semicolon-separated file
CoveredSealant application weight
consumables detection
Not answerable yetLocknut torque and angle
staged table
CoveredTool checks
shadow board app
CoveredAssembly levelling
levelling app
Covered
Two metrics short of their required count, so this gearbox produces no certificate — however good the other seven look.
Illustration of the rule, not client data. The required count is configured per gearbox model and per metric in the client’s own master data; a component below the line is neither passed nor failed, it is simply not yet answerable.
The problem: a passing sample is not a passing gearbox
A gearbox certificate has to vouch for the assembly and for every subassembly inside it. The evidence existed — torque, vibration, tightening, sealant, tool checks, first-quality inspection were all being captured somewhere. But it was captured in nine different places, in nine different shapes, and nothing checked whether enough of it was present before somebody signed.
Evidence in nine places, nine shapes
Some of it sat in quality, tool-management and test-rig databases. The rest arrived as tab-, semicolon- and comma-separated files dropped onto network shares by machines and shop-floor apps — one of them starting its data at row five.
A passing subset read as a pass
Nothing enforced how much evidence a component needed before it could be judged. Eighteen passing parameters looked exactly like a complete set, so a gearbox could be certified on a fraction of the checks it was supposed to carry.
The subassembly chain rebuilt by hand
Proving the chain of evidence from a gearbox down through its subassemblies meant reconstructing the bill of materials and matching records against it manually, every single time.
Deviations settled off the record
An out-of-spec parameter was argued through in person or over email. The decision to accept it existed, but not in any form that a later audit could follow.
Quality could not change a rule without a release
Thresholds and source mappings lived in compiled code, so every new data source or adjusted limit became a development ticket and a deployment.
We did not build a warehouse
The instinct on a project like this is to build a warehouse: copy all nine sources into one database and report off the copy. We deliberately did not. The system reads each source where it lives and stores almost nothing — because the moment it holds its own copy of a torque reading, that copy can disagree with the machine that produced it.
things it keeps
The certificate
The generated document itself, stored as binary with the user who produced it, the moment they did, and its revision number.
The approval
Each conditionally accepted deviation, against the exact parameter it applies to, with its author, timestamp and written reasoning.
What it only ever reads
- Bill of materials, from the manufacturing execution system
- First-quality inspection results
- Bolt tightening torque and angle, across a five-table join
- Vibration spectra, per accelerometer position
- Break-free torque, from two test stations
- Hydraulic tightening, sealant weight, tool checks, levelling
Databases are queried live. Scalar files are staged hourly into PostgreSQL only because a tab-separated dump on a network share cannot be queried where it lies — staging makes it readable, it does not make it the record.
Nine sources, one shape
Nothing about these sources agrees. They disagree on transport, on format, on how a component is identified, even on what “passed” is called. Rather than writing an evaluator per source, every source is made to express the same handful of fields — each one a SQL expression held in configuration.
What every source must express
- Identitywhat uniquely names this row
- Descriptionwhat was measured
- Valuewhat came back
- Limitsthe acceptable band
- Resultpass, or not
- Componentwhat it belongs to
- Orderhow the rows sequence
Because each is configuration rather than compiled code, adding a source or moving a limit is a config change — which is what lets the client’s own team do it.
What it has to absorb
A five-table SQL join
result, identifier, tightening, program and unit views in a tool-management database
Tab-separated dumps
written to a network share by two break-free torque stations
A CSV that starts at row five
exported by the consumables detection system for sealant application
Vibration spectra
one row per accelerometer position per load case, with its own ceiling
From nine sources to one signed document
Two access patterns feed one evaluation, and the evaluation is what produces the certificate. Nothing in the middle of this chain becomes a second system of record.
Sources
- MESbill of materials
- Quality, tool and test-rig databasesqueried live
- Machine and app files on sharesTSV, CSV, semicolon
Access
- Live querynothing copied
- Hourly stagingfiles made queryable
Evaluation
- One normalised field contractnine sources, one shape
- Minimum-evidence gateper model, per metric
- Status cascadeparameter → component → order
- Conditional approvalrole-gated, reasoned
Output
- Digital certificatePDF and Excel
- CTQ-only reportseparate download
- Revision on regenerationwhen data changes
What we built
One field contract across nine sources
Every source — a five-table join in a tool-management database, a tab-separated dump from two torque stations, vibration spectra per accelerometer position — is expressed as the same small set of normalised fields: what identifies the row, what it describes, its value, its limits, and whether it passed. Each of those is a SQL expression defined in configuration, so a new source is a config file rather than a code change.
A minimum-evidence gate
Master data defines, per gearbox model and per metric, how many parameters must actually be present before that metric counts as evaluated. Below the line, the component is not passed and not failed — it is not yet answerable, and the certificate is blocked. This was the client’s own rule, and it is the most important thing the system does.
Bill-of-materials drilldown
An order number is loaded from the manufacturing execution system with its full component hierarchy, and quality status is attached at every level. Status cascades upward: one unresolved parameter colours its component and the whole order until somebody accounts for it.
Role-gated conditional approval
An out-of-spec parameter can be conditionally accepted, but only by a user whose role permits it, and only with written remarks. The approval is stored against the specific parameter with its author and timestamp, so the reasoning travels with the record instead of living in an inbox.
Certificates, revisions and a CTQ cut
Once every parameter passes or has been approved, the certificate is generated as PDF for signing and Excel for analysis, stored with who produced it and when. Regenerating it after new data arrives creates a tracked revision rather than overwriting. Critical-to-quality parameters can also be pulled as their own report for customers who only want those.
What the certificate actually is
Once every parameter either passes or carries an approval, an authorised user issues the certificate. It comes out as a PDF for signing and an Excel file for whoever needs the underlying numbers, and it is stored with the person who produced it and the moment they did.
Regenerating it — because more data arrived, or a value changed — creates a new tracked revision rather than replacing the old one. Critical-to-quality parameters can be pulled as their own report for customers who only want those.
Every issued certificate records
- Who generated it
- When it was generated
- Its revision number
- The document itself, stored as binary
- Every deviation accepted along the way, with its reasoning
Cryptographic hashing and a full historical audit trail are on the roadmap for this system and are not described here as delivered.
What changed
Nine sources, one screen
Quality engineers evaluate an entire gearbox and its subassemblies from one page instead of opening several systems and a folder of files.
Incomplete evidence cannot certify
The gate is the point. A gearbox with eighteen of twenty-four required tightening results does not produce a certificate, however good those eighteen look.
Approvals carry their own reasoning
Every accepted deviation records who accepted it, when, and why, against the exact parameter it applies to.
The source systems stay authoritative
Because the platform reads in place rather than warehousing, there is no second copy of a torque reading to drift out of agreement with the machine that measured it.
Configurable without us
Thresholds, source mappings and CTQ selection are configuration. The client’s own team changes them without a development ticket or a deployment.
Systems that outlive the engagement
The configuration layer was the argument we had to win on this project. It is faster, in the short term, to hardcode a client’s thresholds — and it guarantees they come back to us every time a limit moves.
We would rather build the thing that keeps working after we leave. That instinct runs through everything we make, including our own products.
The client is not named on this page. The work and the technical detail are described exactly as delivered.
Signing off on data you cannot fully see?
If certifying one part means pulling records out of several systems and hoping the set is complete, the document is not the problem. Tell us where your quality data actually lives.
- An engineer reads it, not a sales queue.
- We reply within one business day.
- If it is not worth building, we will say so.
Prefer email? hemanand@machdatum.com