## 1) Process Overview **Current workflow:** Operator starts fixture run → worker fetches CSV → validates `record_id` and label → inserts rows → commits → writes local checkpoint → operator reads count report. **Immediate blocker:** The exporter now supplies `id`, while the importer reads `record_id`. Every current fixture import fails with `KeyError`. **Other weaknesses:** - Automatic retries conflict with mandatory operator approval after failure. - A plain `INSERT` can fail on an existing primary key. - Database commit and local checkpoint are separate operations. A crash between them can leave committed data with a stale checkpoint. - Error review and replay authorization consume approximately **20 minutes/day**. **Missing information:** Fetch protocol, transaction boundaries, checkpoint format, input size, and report-count definitions are unspecified. These affect implementation details, runtime estimates, and exact recovery behavior. The recommendations below use the supplied constraints; no actions are executed. ## 2) Automation Opportunities | Step | Recommendation | Reason | |---|---|---| | Header handling | Map incoming `id` to internal `record_id`; also accept the original header. Reject missing or ambiguous identifier columns before writes. | Fixes the immediate failure while preventing silent identifier selection. | | Validation | Check identifiers against the existing column’s requirements and reject empty or whitespace-only labels. List rejected rows with row number and reason. | Preserves valid rows and makes corrections actionable. | | Duplicate handling | Distinguish identical records from records with conflicting labels. | Prevents duplicates without overwriting existing data. | | Reporting | Generate one reconciled report containing row outcomes and transaction status. | Reduces manual comparison of separate logs and rejected-row lists. | | Recovery | Disable automatic replay; require explicit operator authorization after failure. | Meets the binding recovery rule. | For repeated identifiers inside the CSV, collapse identical copies. If their labels differ, flag that identifier for review rather than arbitrarily choosing a label. ## 3) New Workflow **Operator starts run → fetch → check response → validate headers → classify rows → database transaction → commit → checkpoint → reconciled report.** 1. **Fetch and stop conditions** - If the fetch returns **401**, stop immediately. - On any run failure, disable automatic replay and report that operator approval is required. 2. **Validate the input** - Resolve `id` or `record_id` before processing records. - If both appear, stop for clarification rather than choosing silently. - Classify invalid rows and conflicting identifiers without discarding unrelated valid rows. 3. **Write valid, nonconflicting records** - Use the existing primary key to enforce uniqueness. - Insert with `ON CONFLICT (record_id) DO NOTHING`. - For records not inserted, compare their labels with the stored values: - Same label: classify as **already present**. - Different label: classify as **requires operator review**; leave stored data unchanged. - Commit successful inserts together. On a database error, roll back and report the transaction failure. 4. **Checkpoint and report** - Write the checkpoint only after confirmed commit, using an atomic local-file replacement. - Treat PostgreSQL as authoritative; the checkpoint is supporting progress information. - If commit succeeds but checkpoint writing fails, report both facts explicitly. Any replay still requires operator approval. - If the commit outcome is uncertain, report it as **unknown**, then reconcile against the database before claiming success. **Tool handoffs:** CSV → Python validation and classification → PostgreSQL transaction → local checkpoint and report. An approved replay must revalidate and reconcile against the database. It must not blindly repeat inserts based only on the checkpoint. ## 4) Efficiency Improvements - **One routine review step is removed:** manual comparison of the error log with the rejected-row list becomes review of one consolidated report. - Operator authorization remains mandatory for every replay after failure. - The exact reduction from **20 minutes/day** cannot be established without testing. Use mutually exclusive report categories: **Input rows = invalid + input conflicts + duplicate copies + inserted + already present + database conflicts + uncommitted/unknown.** Only report rows as inserted after confirmed commit. This reduces misleading counts, duplicate-key failures, silent overwrites, and recovery errors caused by stale checkpoints. ## 5) Scaling Ideas - Start with one worker and one transaction for the fixture; this keeps recovery understandable. - For larger inputs, add bounded batches with explicit commit outcomes and the same reconciliation rules. - Add batch database lookups when individual comparisons become slow. - Keep reports detailed enough to trace every rejected or conflicting row. These changes can retain the existing worker, database, and schema. Parallel workers should wait until concurrency behavior has been tested. ## 6) Practical Tips Prioritize the **four-hour initial fix** as follows: | Time allocation | Work | |---|---| | 45 minutes | Header mapping, upfront checks, disable automatic replay, stop on 401 | | 90 minutes | Validation, duplicate classification, conflict-safe inserts and comparisons | | 45 minutes | Reconciled report and checkpoint failure handling | | 60 minutes | Targeted fixture tests | Test the renamed header, invalid rows mixed with valid rows, identical duplicates, conflicting labels, existing database records, 401 responses, and failures around commit and checkpoint writing. Verify that **no failure starts an automatic replay**. Use the existing local fixtures and PostgreSQL setup to avoid additional vendor costs within the **EUR 50 budget**. The four-hour allocation is an initial scope estimate; the unspecified worker structure and transaction behavior may affect completion time. Do not add automatic conflict updates: a different stored label always requires operator review.