Managing tagged image libraries for deep learning training

A vision project accumulates thousands of frames somewhere between wiring the panel and programming the machine: each frame belongs to a product family, a material, and carries one or more defects. Windows tags a JPEG but not a BMP, and no file property answers "every anodized part from family B with a scratch". The setup that survives production is small: one metadata table keyed to file paths, defect tags in a link table, SQLite on the capture PC with a defined point to move to Postgres, and DVC versioning the pixels. What follows is the schema, the queries, the storage arithmetic and the health checks, assembled from standard pieces with no custom application to maintain.

Do I need a database, or will folders and filenames do?

Folders stop answering at three traits: product family, material and defect combinations multiply faster than any directory tree, so put the traits in a database table and let SQL do the selecting — a schema you can write in under an hour.

Filenames encode one trait cleanly, a second one badly, and none after that. famB_alu_scratch_0042.bmp answers family and material, but add "also has a dent" and "re-inspected" and you are renaming ten thousand files by hand. Windows Explorer's tags are no better: they are written into the file's own metadata, BMP is not covered, and nothing queries them by combination across a hundred thousand files.

A database inverts the problem. The image stays a plain file named by capture time; the traits live in one row keyed to the file's content hash, so renaming, moving or copying never touches a tag. The pre-built photo managers are built around people, places and holidays — bending one to a defect taxonomy costs more work than the table.

SQLite or Postgres for the image metadata?

SQLite, as a single file on the capture PC, is the right first home for any library with one writer; move to Postgres only when a second station writes or several desks query the same metadata over the network.

SQLite needs no server and is already on the capture PC: Python's standard library ships the sqlite3 module that reads and writes it. One writer at a time is its real limit, and a training library is a batch-and-keyboard workflow, not a transaction storm.

Keep the SQLite file on local disk, never on a network share: its file locking is not dependable there, and a corrupted tag database is the loss of the index to every image you own. The backup is a file copy, and the file is small — at roughly 300 bytes per image row and 50 bytes per tag link, 100,000 images is about 40 MB of metadata.

The schema below is standard SQL and moves between the two engines unchanged; you migrate the week the second writer appears, not before.

How do I tag product family, material and multiple defects?

Give every single-valued trait its own column and every multi-value trait a link table: product family and material are columns on the image row, and the one-or-more defects become rows in an image_tag table keyed to it.

An illustrative schema, standard SQL, works in both engines:

CREATE TABLE image (
  image_id       INTEGER PRIMARY KEY,
  file_path      TEXT NOT NULL UNIQUE,
  sha256         TEXT NOT NULL,
  product_family TEXT NOT NULL,
  material       TEXT NOT NULL,
  captured_at    TEXT NOT NULL
);
CREATE TABLE defect_tag (
  tag_id INTEGER PRIMARY KEY,
  name   TEXT NOT NULL UNIQUE
);
CREATE TABLE image_tag (
  image_id INTEGER NOT NULL REFERENCES image(image_id),
  tag_id   INTEGER NOT NULL REFERENCES defect_tag(tag_id),
  PRIMARY KEY (image_id, tag_id)
);
CREATE INDEX idx_image_family_material ON image(product_family, material);
CREATE INDEX idx_imagetag_tag ON image_tag(tag_id);

A frame with two defects is inserted once into image and twice into image_tag:

INSERT INTO image VALUES (1, '2026/w12/famB_0042.bmp',
  'a3f0c2e19b7d4a55...', 'famB', 'anodized_alu', '2026-03-19T07:41:02');
INSERT INTO image_tag VALUES (1, 3);  -- scratch
INSERT INTO image_tag VALUES (1, 7);  -- dent

SELECT COUNT(*) FROM image_tag WHERE image_id = 1; returns 2 — one row per defect, never one row per image. That count is the test that the link table is doing its job.

The deterministic traits should never be typed by hand. The line controller knows family and material at capture time; write them in the same script that saves the frame. The capture side can even run code-free — save consecutive camera images without writing code covers it — and the offload point is where your tags get written.

How do I pull images by any combination of traits?

One JOIN between the image table and the tag tables returns any combination — family plus material plus a set of defects — and with the two indexes above the query stays in the milliseconds at 100,000 images.

SELECT i.file_path
FROM image AS i
JOIN image_tag AS it ON it.image_id = i.image_id
JOIN defect_tag AS t ON t.tag_id = it.tag_id
WHERE i.product_family = 'famB'
  AND i.material = 'anodized_alu'
  AND t.name = 'scratch';

The query that runs before every training session is the class count, sparse classes last:

SELECT t.name, COUNT(i.image_id) AS n
FROM defect_tag AS t
LEFT JOIN image_tag AS it ON it.tag_id = t.tag_id
LEFT JOIN image AS i ON i.image_id = it.image_id
GROUP BY t.name
ORDER BY n;

The interface is a Jupyter notebook with these queries saved as cells: change the trait values, run, open the paths. That is the whole application the task needs, and it holds up well past the prototype stage.

How many images a class needs before training is worth running is a target you set deliberately: deep learning defect inspection: how many samples you really need gives the method and how many images you need to train a custom YOLO dataset the detection-side figures. One number keeps expectations honest meanwhile: 3 product families × 4 materials = 12 family-material cells, and 12 defect classes over that grid at 150 frames each would be 21,600 curated images. Do not chase the grid. Tag at capture, let the count query expose the sparse cells, and train per defect class with family as a stratification check.

How fast will the library fill the disk?

Budget on raw, uncompressed bytes — width times height times bytes per pixel — because that is the worst case the disk must swallow: a 12 MP 8-bit mono frame is 12 MB, and 250 of them a day is 3 GB.

Worked example, with the inputs you would change — one 12 MP mono camera, 8-bit frames at 4000 × 3000 px, 250 frames kept per day, 240 working days per year:

• Bytes per frame: 4000 px × 3000 px × 1 B/px = 12,000,000 B = 12 MB.
• Per day: 250 frames × 12 MB = 3,000 MB = 3 GB.
• Per year: 3 GB × 240 days = 720 GB.
• First training set: 12 defect classes × 200 usable frames, plus as many good-part frames, about 5,000 frames × 12 MB = 60 GB.

Year one is 60 GB + 720 GB = 780 GB, so a 2 TB drive holds roughly 2.5 years at that rate (2,000 GB ÷ 780 GB ≈ 2.6). Capturing 16-bit frames instead — worthwhile when you need shadow detail — doubles every number: 24 MB a frame, 1.4 TB a year. The padding inside 16-bit containers has its own surprise, covered in 12-bit data in a 16-bit V4L2 frame.

Lossless PNG on grayscale inspection frames typically lands between a third and two-thirds of raw; size the disk on raw and treat compression as margin. If the camera is not bought yet, the pixel count is the storage dial — the resolution calculator sizes it from the smallest defect you must resolve.

How do I version and share the images between machines?

Version the pixels with a data-versioning tool and the metadata with git: DVC records which files, by content hash, made up each training set and pushes and pulls them to remote storage such as a NAS, while the small tag database sits in the repository.

The failure mode without versioning is quiet: a model's error rate moves, and nobody can say which images it was trained on, because the folder was cleaned up in between. DVC pins the dataset per commit; git carries the small things — schema, notebook, a CSV export of the metadata — and the pixels stay files, referenced by hash.

A second machine then reconstructs the whole library with a repository clone plus one pull command. Your sha256 column and the versioning tool's hashes do the same job on opposite sides: identity by content, not by name. When a trained model later ships back to the cell, the trait fields become the logging fields beside each inference, and the architecture choices for deep learning integration with industrial robots start where this library ends.

What must stay identical in every capture?

Lock the camera, lens aperture, lighting and exposure before you tag a single image, because a tag is only meaningful if every image with that tag was taken the same way — change the light and you have silently created a new domain.

Standardize on three things:

• One camera model and one resolution across the library. Mixing 12 MP and 20 MP frames in one training set forces resampling in the pipeline and wastes the larger sensor's pixels.
• Fixed exposure and aperture. The auto-exposure that looked fine on a bright afternoon shifts every histogram in that batch.
• One lighting geometry per station, same diffusers, same cable set — lighting accessories exist so stations match instead of each bench improvising.

The bench camera can be the LS-CMR-0166, a 12 MP USB3 area scan camera with a 4000 × 3000 output.

Where the smallest defect needs more pixels, decide before the first session: at 20 MP an 8-bit frame is 20 MB, and storage scales linearly with pixel count. The MV-CS200-10UM is a 20 MP USB3.0 mono camera for that role.

Smart cameras that inspect on the device still export frames for training; the LS-SMC-0063 in that pair is a 1.6 MP GigE smart camera with a 16 mm lens. Add the station name as a fourth trait the day a second station appears. All three come from the area scan cameras and smart cameras ranges: the camera finder filters by resolution and interface, and the capture configurator walks camera, lens, light and controller together.

When is a tag library the wrong tool?

A trait-tag library stops being the right tool the moment you need per-object labels, more than one simultaneous writer, or tags that describe pixel regions — those belong in an annotation format, a server database, or the annotation step, respectively.

Three cases cover nearly all of it:

• Boxes and masks are not traits. Tags select images; annotation labels their content, and its output files sit beside the images referenced from your table — the drawing discipline is in how to annotate images for YOLO training the right way.
• A second station that writes at the same time is the Postgres row of the table above, decided the day the second capture PC appears.
• Training one model across many product classes is a split-and-schedule question — training anomaly detection across multiple product classes covers it, with the tag library as its input rather than its answer.

What checks keep the library trustworthy?

Five checks, each one query or one script, run on a schedule, are the difference between a library and a folder that used to have training images in it.

• Orphans, after every offload: rows whose file_path no longer resolves. A row without pixels is a lie in your class counts.
• Strays, monthly: walk the image folder and list files with no row — captures that bypassed the tagging script.
• Duplicates, before every training run: group by sha256; two paths with one hash is one image counted twice in a class.
• Class counts, before every training run: the LEFT JOIN query above. Under 150 usable frames in a defect class, go capture instead of training.
• Restore, once, on a spare machine: clone the repository, pull the data remote, open the metadata. An untested backup is a hope, not a plan.

From here the work is mechanical: fix the capture station, write the two INSERT statements, schedule the checks. More on the systems around capture and training sits under machine vision systems, and if you want a second opinion on the capture side before you standardize it, the contact page reaches the applications team.