Step 4: Database Model

This chapter defines the data layer behind pipeline reliability. Step 3 covered operations; Step 4 explains how state is stored, moved, and validated.

4.1 Datastore topology

1. data/review.db - operational queue and editor-driven lifecycle state.
2. data/photos_info_revamp.db - local mirror of published catalog rows.
3. Remote MySQL table photos_info_revamp - website-facing published metadata.
4. data/prefill_qc_last.json - stage-6 prefill QC report artifact (duplicates/suspicious text summary).

Design intent: run-time decisions happen in local SQLite; remote systems are touched only in approved publish flow.

4.2 Quick field cheat sheet

Use this for fast orientation. Detailed per-column definitions are listed later in the full dictionary.

Important: review_queue.id is operational only; publish identity is resolved in photos_info_revamp.id.

4.3 Core operational table: review_queue

The table is initialized from data/init/review_queue.sql in the automation repository. A local docs copy is available at docs/review_queue.sql. Each row represents one image moving from ingest through review to publish decision.

Column groups

• Identity and paths: id, File_Name, Original_File_Name, Path, ollama_path, Thumb_Path.
• Capture metadata: DateTime, Camera, Lens_model, Width, Height, Exposure, Aperture, ISO, Focal_length.
• Editorial metadata: Folder, Subject, Location, Caption, alt_text, Keywords.
• Quality features: nima_score, blur_score, brightness_score, contrast_score, brisque_score, clip_aesthetic_score, QR, QC_Status.
• Lifecycle state: Review_Status.

4.4 Lifecycle semantics in queue rows

Status values are stage-driven and operator-driven. Common states in active code paths include:

• Queued - row inserted and awaiting downstream scoring/prefill completion.
• Pending - row is ready for review decision (and may contain AI prefill content).
• Generate action (editor) - rewrites Caption/alt_text/Keywords in-place while preserving review-first flow.
• Approved - approved for publish path.
• Rejected - not published; row may be removed depending on editor flow.
• Error - stage failure requiring triage/retry.
• Published/Uploaded - may appear in editor/legacy paths; publish pipeline still enforces approved-only selection in uploader.

Operational rule: queue state should be interpreted together with logs, not as a stand-alone signal.

4.5 Publish-side model and key mapping

During publish, db_uploader.py selects approved rows and writes to remote MySQL using File_Name as the stable unique key for upsert behavior.

• Remote row identity is resolved by SELECT id FROM photos_info_revamp WHERE File_Name=? after upsert.
• Local mirror data/photos_info_revamp.db is synchronized with canonical MySQL id.
• On successful upload, processed queue rows are deleted from review_queue.

Consequence: review_queue.id is an operational ID only; it is not the publish catalog primary key.

A local docs copy of the publish schema is available at docs/photos_info_revamp.sql.

4.6 Auxiliary review ledger

Editor flows can upsert into st_items (when available) for rating and review-history support. This ledger is useful for analysis and future model learning, but it is not required for publish writes.

4.7 Integrity rules to preserve

• Treat File_Name as a durable business key across publish and mirror synchronization.
• Preserve filename reservation consistency with data/used_filenames.json.
• Treat data/prefill_qc_last.json as operational telemetry, not as source-of-truth state.
• Do not perform ad-hoc schema edits in production DB files without matching init SQL updates.
• Do not assume queue rows persist after successful publish; uploader cleanup is expected behavior.

4.8 Operational SQL diagnostics

-- queue volume by status
SELECT COALESCE(Review_Status, 'NULL') AS status, COUNT(*) AS n
FROM review_queue
GROUP BY COALESCE(Review_Status, 'NULL')
ORDER BY n DESC;

-- pending rows waiting editor decision
SELECT id, File_Name, QC_Status
FROM review_queue
WHERE COALESCE(Review_Status,'Pending')='Pending'
ORDER BY id DESC;

-- approved rows ready for uploader
SELECT id, File_Name, Folder
FROM review_queue
WHERE Review_Status='Approved'
ORDER BY id ASC;

-- error rows requiring triage
SELECT id, File_Name, Review_Status, QC_Status
FROM review_queue
WHERE Review_Status='Error' OR QC_Status IN ('ResizeFailed')
ORDER BY id DESC;

4.9 Schema sources and runtime artifacts in automation repository

• docs/review_queue.sql (local docs copy)
• docs/photos_info_revamp.sql (local docs copy)
• data/init/review_queue.sql
• data/init/photos_info_revamp.sql
• docs/review_db_schema.sql (extended schema reference, including optional analysis tables)
• data/prefill_qc_last.json (stage-6 QC summary artifact)

Keep init SQL aligned with runtime code whenever schema or column semantics change.

4.10 Column dictionary (from SQL)

The following definitions map each column to its operational meaning.

4.11 Continuation path

Step 4 defines storage and state contracts. Step 5 defines how to extend these components safely as a developer.