An inspection team exports a folder twice because the first upload appeared to stall. Both transfers finish. The reviewer now sees twice as many rows, although the team visited the same assets. A second folder arrives with an unchanged filename but different bytes. One importer silently replaces the earlier file, including the image that somebody already accepted. The photo viewer works. The handoff record has lost the ability to say what was reviewed.
This tutorial builds a small SQLite ledger for an inspection team evaluating a UI20 aircraft configuration. The ledger runs after files have been delivered to the team. The UI20 aircraft listing identifies the product to discuss with a supplier. The manifest and all payload bytes below are synthetic, and the code assumes the team supplies its own capture identifiers. The example requires Python's standard library and no access to the aircraft.
Decide what must survive a second import
Start by asking what should happen when the same export is delivered under a different folder name. A successful second import should leave the same captures and reviews in the ledger. A file path is useful for finding a file in one transfer, but the receiver cannot assume that path survives archiving, renaming or copying through a different operating system. A filename can also be recycled by a camera or a person's export script.
Define the unit of work with a key such as job ID, required view ID and capture ID. The job separates deliveries for different work orders. The view identifies the evidence requested for a particular asset or inspection question. The capture identifies one attempt to supply that view. The team assigns those identifiers and records them in a manifest before sending files. A path in that manifest points to the bytes in the current delivery. It can change on another delivery without changing the key. If two assets each need a label photo, their view IDs should be distinct in the job requirements.
Give the bytes a separate identity
Hash the original byte stream with SHA-256. The digest lets the receiver detect byte-for-byte equality. Include the byte count for storage and inspection, and retain the original bytes somewhere the ledger can reference. A digest of a preview or an image decoded and saved again describes a different artifact. For example, a program that strips metadata from a photo creates a new byte sequence even when a person cannot see a difference in the displayed image. Store derived artifacts with their own digest and a reference to the source.
Two capture keys can point to the same digest. The ledger retains both keys and only needs one blob record for those bytes. That situation can occur when a sender attaches the same file to multiple manifest rows. It warrants a review of the assignment. The digest alone cannot tell whether the image is suitable evidence for either required view. Keeping the two identities separate allows the receiver to find repeated bytes without silently dropping work or counting duplicates as additional completed views.
Define the three import outcomes
An unseen capture key is a new capture. A known key with the same digest is a replay. A known key with a different digest is a conflict. Those outcomes can be checked without timestamps or filenames. In particular, a file marked newer by a transfer service could still be a renamed copy of an older image. The team's manifest contract should say who can issue a new capture ID and what the receiver should do with a conflict.
This example rejects the conflicting batch and leaves earlier committed imports and reviews available. In production, a receiver can place the batch and its manifest in quarantine and give a human the job, view and capture key together with both digests. The sender can explain which item the delivery intended to replace. A corrected capture gets its own ID or follows a documented amendment process. A policy that overwrites old rows during ingestion makes the correction look like the original bytes were never there.
Build the ledger in SQLite
The example stores a blob digest and size, a capture-to-blob association, and one decision for each reviewed capture. The fixture uses small byte strings as stand-ins for image files. It stores no original bytes in SQLite. For a real handoff, save them in durable storage, verify the hash computed from what was written, and keep the locator alongside the digest. Build that storage and reconciliation layer before depending on the database as an operational record.
The import does the inserts and the comparison inside one transaction. The capture insert can skip a key that already exists. The following query checks the digest stored under that key. If the bytes conflict, an exception rolls back the import, including any new blob rows from that batch. The review uses a conditional insert so that a request referring to a stale or missing digest cannot attach a decision to a different capture. Identical decisions can be replayed. Changing an accepted decision requires a separate revision process with its own record.
from dataclasses import dataclass from hashlib import sha256 import sqlite3 @dataclass(frozen=True) class Capture: job_id: str view_id: str capture_id: str payload: bytes @property def key(self): return self.job_id, self.view_id, self.capture_id def open_ledger(): db = sqlite3.connect(":memory:") db.execute("PRAGMA foreign_keys = ON") db.executescript(""" CREATE TABLE blob ( digest TEXT PRIMARY KEY NOT NULL, byte_count INTEGER NOT NULL CHECK (byte_count >= 0) ); CREATE TABLE capture ( job_id TEXT NOT NULL CHECK (length(job_id) > 0), view_id TEXT NOT NULL CHECK (length(view_id) > 0), capture_id TEXT NOT NULL CHECK (length(capture_id) > 0), digest TEXT NOT NULL REFERENCES blob(digest), PRIMARY KEY (job_id, view_id, capture_id) ); CREATE TABLE review ( job_id TEXT NOT NULL, view_id TEXT NOT NULL, capture_id TEXT NOT NULL, digest TEXT NOT NULL REFERENCES blob(digest), decision TEXT NOT NULL CHECK (decision IN ('accepted', 'rejected')), PRIMARY KEY (job_id, view_id, capture_id), FOREIGN KEY (job_id, view_id, capture_id) REFERENCES capture(job_id, view_id, capture_id) ); """) return db def import_export(db, rows): with db: for row in rows: digest = sha256(row.payload).hexdigest() db.execute(""" INSERT INTO blob VALUES (?, ?) ON CONFLICT(digest) DO NOTHING """, (digest, len(row.payload))) db.execute(""" INSERT INTO capture VALUES (?, ?, ?, ?) ON CONFLICT(job_id, view_id, capture_id) DO NOTHING """, (*row.key, digest)) stored = db.execute(""" SELECT digest FROM capture WHERE job_id = ? AND view_id = ? AND capture_id = ? """, row.key).fetchone() if stored != (digest,): raise ValueError(f"Conflicting bytes for capture {row.key}") def record_review(db, key, expected_digest, decision): if decision not in {"accepted", "rejected"}: raise ValueError("Unsupported review decision") with db: db.execute(""" INSERT INTO review SELECT job_id, view_id, capture_id, digest, ? FROM capture WHERE job_id = ? AND view_id = ? AND capture_id = ? AND digest = ? ON CONFLICT(job_id, view_id, capture_id) DO NOTHING """, (decision, *key, expected_digest)) stored = db.execute(""" SELECT digest, decision FROM review WHERE job_id = ? AND view_id = ? AND capture_id = ? """, key).fetchone() if stored != (expected_digest, decision): raise ValueError(f"Review target or decision conflicts for {key}") def missing_views(db, job_id, required): accepted = db.execute(""" SELECT DISTINCT view_id FROM review WHERE job_id = ? AND decision = 'accepted' """, (job_id,)) return sorted(set(required) - {view_id for (view_id,) in accepted}) if __name__ == "__main__": ledger = open_ledger() front = Capture("JOB-17", "cabinet-front", "C-1", b"synthetic front bytes") label = Capture("JOB-17", "asset-label", "C-2", b"synthetic label bytes") requirements = {"cabinet-front", "asset-label"} import_export(ledger, [front, label]) import_export(ledger, [label, front]) record_review(ledger, front.key, sha256(front.payload).hexdigest(), "accepted") print(missing_views(ledger, "JOB-17", requirements)) record_review(ledger, label.key, sha256(label.payload).hexdigest(), "accepted") print(missing_views(ledger, "JOB-17", requirements)) Read the result as an evidence check
The first import records two views. The second import presents the same records in the opposite order and leaves the ledger unchanged. Accepting the cabinet front leaves ['asset-label'] in the missing-view report. Accepting the label then produces []. A populated capture table would have been a premature reason to call the job complete. The example checks required views against accepted reviews, so a file can be present while evidence is still due.
Decide what the report means before integrating it with a dashboard. An empty list says that the required view IDs passed this workflow's review rule for that job. That rule should be owned by a named person or role. A reviewer might need to verify that a label is legible, that the asset matches the work order, or that a close view includes the feature the request describes. A script can detect the missing decision. The decision itself depends on the acceptance criteria and the material presented.
Test retries and ambiguous inputs
Use at least one export fixture that includes a capture with a stable key and resend it in a different order. Compare the number of blob, capture and review rows before and after. Then create a batch with a new capture followed by a conflict for an existing key. Check that the new capture and its blob were rolled back, and that the accepted decision for the old capture still refers to the previous digest. This test catches a deceptively common bug where the importer commits each item and then reports that the overall batch failed.
Exercise a stale review request by changing its expected digest. The call should fail without creating a decision, even if another capture in the job has those bytes. Check that a rejected capture still leaves its view due. Check a second capture ID with the same bytes to confirm that capture identity and storage deduplication are independent. Finally, query the same required view in another job. Accepted views for the original work order should have no effect on a different work order.
Verify file integrity without overinterpreting a hash
A content hash can tell you that bytes changed between two measured points. It cannot tell you who assigned the capture ID or which asset appears in the photo. An operator could accidentally attach a valid file to the wrong manifest row. If the sender and receiver must agree on origin and authority, define a trusted manifest exchange with the parties responsible for its production and review. Store the received manifest and its revision alongside the receipt. Compare the hash of the stored original with the one used at review time.
If the manifest already includes a digest, compute the receiver's digest independently before accepting the claimed value. A failed match is useful evidence of a transfer or packaging problem. A passing match still leaves the human or a validated domain-specific check responsible for content acceptance. Keep timestamps for troubleshooting and audit, but state whether a timestamp describes image capture, export assembly, receipt or review. These times can differ without any defect in the delivered image.
Keep storage and review recoverable
A directory and a database cannot normally share one transaction. If a worker uploads a blob and then crashes before committing the capture association, the blob may be unreferenced. If it commits first and crashes before the upload, the ledger may point at an unavailable object. A practical sequence is to write and verify the blob first, commit its association second, and periodically reconcile unreferenced objects with a retention window. This example focuses on the database transaction and leaves storage operations outside the fixture.
Define the point where the system acknowledges a batch to the sender. If the importer acknowledges after commit but the reply is lost, the sender can present the same manifest again. If a conflict is found, a receipt can identify the exact row that stopped the import so the sender does not resend a corrected subset without understanding what was committed. Record whether the transaction policy is per batch or per item. Teams that mix those two policies have a harder time explaining counts after a restart.
Manage revisions with explicit review rules
An inspection request can change after some images were accepted. A maintenance engineer may ask for a closer view or specify that the asset tag must be visible together with a particular surrounding feature. Record the revised requirement under a version that the reviewer can see. Decide whether an earlier capture is still eligible under that version. A new revision of the request may change the missing-view report even when there is no new file. The important record is which requirement and which exact artifact justified the result.
The sample stores a single decision per capture and checks it against the expected digest. A team that allows two independent reviewers or wants to record a superseding decision can add append-only review events. Bind each event to the capture key, the source digest, the requirement revision and an event ID for retries. Define the projection that the dashboard uses to decide the current state. Otherwise, ordering by the latest row created can accidentally put a retried earlier decision ahead of a genuine correction.
Make the handoff fit the actual export
Ask for representative original files from the specific configuration under consideration. Have the person who builds the export show how stable identifiers are assigned and what happens when the same job is exported twice. When the aircraft, payload or downstream software does not produce a usable key, agree on where the team's manifest is assembled and who is authorized to correct it. Include at least one required view where an otherwise valid file is easy to mistake for a different asset. That makes the review criterion observable rather than a promise about the system.
Try the exercise with two deliveries and a deliberately conflicting row. Require the operator to identify the original decision and confirm that the database still points to the reviewed bytes. Ask the reviewer to demonstrate which view remains due after a rejected file. Finally, have another person repeat the procedure using only the work-order instructions and the retained export. If the second person cannot recreate the same result, write down which identifier or acceptance rule was implicit and make it part of the next handoff.
Record the limits before adding automation
This example uses a single local SQLite connection and an in-memory database. A production worker needs persistent storage, a clear writer policy, backup and restoration tests, and a rule for who can see the delivered material. Reuse the same logical keys even if files arrive by a different transport. Use file sizes and bounded reads to control how much work an unexpected export can demand. Keep personal, site-specific or sensitive image content out of application logs, and record only the fields needed for operational investigation.
If the team later automates parts of the review, introduce a distinct state for machine observations and specify when human approval is required. A failed image decoder, an uncertain asset match and an incomplete manifest have different meanings to the person doing the work. Report them independently. The ledger is most useful when someone can trace a readiness decision back to the work order, the exact original bytes, and the review criteria they agreed to use. That trace is what lets a second delivery become a safe replay rather than a new interpretation of the job.
