## 1) Current process as a verifiable sequence of steps All details below describe a fictional fixture scenario. No fixes or tests have been executed. | Step | Input | Activity | Output | Responsible role | |---|---|---|---|---| | 1 | Local fixture configuration | Operator starts the run | Worker attempt begins | Operator | | 2 | Fictional API endpoint and bearer authorization | Worker fetches UTF-8 CSV | CSV with current columns `id,label` | Worker | | 3 | CSV headers and rows | Importer reads `record_id` and validates integer IDs and nonempty labels | Intended validated rows; current attempt raises `KeyError: record_id` | Worker | | 4 | Validated rows | Plain `INSERT` into existing `items` table | Pending database records | Worker | | 5 | Pending database transaction | Commit | Stored records | Worker | | 6 | Successful commit | Write separate local checkpoint | Local completion state | Worker | | 7 | Count report | Operator reads and checks results | Accepted result or error investigation | Operator | **Observed execution:** one controlled evaluation, one worker attempt, failure at header mapping before database writes. Steps 4–7 were not demonstrated successfully. **Handovers and controls:** - Exporter → importer: the header changed from `record_id` to `id`, breaking the interface. - Validation → database: mandatory control before writing. - Database commit → local checkpoint: separate persistence operations create a recovery gap. - Worker → operator: report and rejected-row list require human inspection. - Failure → replay: automatic retries are currently enabled, contrary to the required operator approval. - Logs → manual diagnosis → replay approval: manual intervention; effort is estimated at 20 minutes/day. - Waiting times, transaction boundaries, current report generation behavior and retry scope are unspecified. ## 2) Diagnosis by impact | Problem | Observation or assumption | Cause and effect | Affected steps | Suitable metric | |---|---|---|---|---| | Header mismatch | Observed | Importer expects `record_id`; exporter supplies `id`. Processing stops before writes. | 2–3 | Header failures per attempted run | | Unauthorized automatic replay | Configuration stated; no replay observed | Enabled retries can repeat failed processing without operator approval. | Failure handling | Replays without recorded approval; required value: zero | | Duplicate handling | Risk, not observed | Plain insertion can encounter an existing primary key. The current response and transaction scope are unknown; valid rows could remain unprocessed. | 4–5 | Raw rows left unclassified after a duplicate | | Commit/checkpoint disagreement | Structural risk, not observed | Database commit and checkpoint cannot complete as one transaction. Failure between them can leave stored rows with an incomplete checkpoint. | 5–6 | Runs requiring uncertain-commit investigation | | Incomplete reconciliation | Current report logic unknown | A count alone cannot demonstrate what happened to each raw row, including repeated IDs and invalid rows. | 3–7 | Unclassified raw rows | | Manual diagnosis effort | Estimate, not measured baseline | Operator investigates failure and authorizes rerun. Daily recurrence is stated, but only one attempt is evidenced. | 7 and recovery | Measured operator minutes per run | No measured savings, production failure rate or successful processing rate can be calculated from this evidence. ## 3) Target picture and guardrails **Priority order:** data integrity → safe recovery → operator effort. For a completed report: \[ \text{raw rows} = \text{inserted} + \text{identical-existing} + \text{rejected} + \text{conflicting} \] Each raw row must appear in exactly one category, with its source row number. Counts refer to input rows; distinct record IDs should be reported separately where useful. | Requirement | Target or status | |---|---| | Duplicate stored IDs | At most one record per `record_id`, enforced by the existing primary key | | Existing different labels | No overwrite; list for operator review | | Invalid rows | Listed with reasons; other valid rows continue | | Reconciliation | Every raw row classified before declaring completion | | HTTP 401 | Stop; no automatic replay | | Replay | Explicit operator approval before every replay | | Checkpoint | Written only after confirmed commit | | Operator capacity | Recurring handling within 30 minutes/day; verify by measurement | | Delivery | Desired by 09:00 after the 07:00 start; verify runtime | | Initial repair | Four developer hours | | Fixture-test spending | Maximum EUR 50; actual costs must be recorded | **Non-goals:** production rollout, vendor migration, schema changes, automatic conflict resolution and automatic replay. **Target conflicts and limits:** - A timeout after a commit request can leave its outcome uncertain. Finding a matching row afterward confirms its present state, but does not necessarily prove this attempt inserted it. - The existing table contains no batch attribution. Exact historical “inserted by this attempt” counts cannot always be reconstructed after an ambiguous commit, especially if another writer operates concurrently. - Therefore, an uncertain run must remain **incomplete**, with unresolved rows explicitly identified. It must not publish invented final category counts. - A four-hour repair should address the immediate defect and essential controls. It cannot be assumed to include comprehensive recovery engineering, staff documentation and all later verification. - The two-hour delivery window and 30-minute operator allowance are unverified. If recovery exceeds either, preserve integrity and report the missed target. ## 4) Two target processes ### A. Minimal variant using existing means 1. **Operator starts the fixture run.** Worker records an attempt identifier and source identity. Disable automatic worker and processing replays. 2. **Worker fetches the CSV.** On 401, stop and report the authentication failure. On timeout, stop and request operator-authorized replay. 3. **Worker checks the header before writing.** Explicitly map `id` → `record_id`. A missing or ambiguous required header stops the run with a clear error. 4. **Worker validates and numbers every raw row.** Reject noninteger IDs and empty labels with reasons. Preserve valid rows for processing. Use the supplied rules; do not introduce silent label normalization. 5. **Worker processes valid rows in source order.** Use insertion that leaves existing records unchanged on primary-key collision. Record an insertion when confirmed; otherwise read the existing label: - Same label: `identical-existing`. - Different label: `conflicting`; no update. Repeated IDs within the CSV receive their own row classifications. 6. **Worker commits the transaction.** If commit is confirmed, retain the classifications for the report. If the connection fails or commit is uncertain, mark the attempt incomplete and stop. 7. **Worker writes the checkpoint, then the reconciled report.** Verify category totals against raw-row count. If checkpoint or report writing fails after commit, report the persistence failure and require approval before replay. 8. **Operator reviews the report.** Review rejected and conflicting rows. An approved replay creates a new attempt; it does not silently replace the failed attempt’s history. **Removed behavior:** automatic replay, failing through `KeyError` on the known header, duplicate insertion as an unmanaged error. **New dependencies:** reliable local report/checkpoint storage and recorded replay approval. **Limitation:** approved replay can safely classify the current input against current database state, but cannot necessarily recover the previous attempt’s exact insertion attribution. ### B. Optimal variant with justified recovery controls 1. **Operator starts or explicitly approves a replay.** Worker records approval, attempt identity and any relationship to a previous attempt. 2. **Worker fetches and retains the exact source snapshot.** Keep source metadata and a content digest so recovery uses the same input. Stop on 401 or fetch failure. 3. **Worker validates the complete batch.** Produce a row ledger containing source row number, original values, mapped ID, validation result and rejection reason. 4. **Worker records a durable prewrite attempt manifest.** Preserve the source identity and intended processing state locally before database writes. This is recovery evidence, not a postcommit checkpoint. 5. **Worker applies insert-only processing and records provisional outcomes.** Compare existing labels without overwriting them. Serialize this worker’s local attempts. Determine whether other writers exist before relying on exclusive access. 6. **Worker commits.** Confirmed commit permits completion. An uncertain outcome routes to operator review; local evidence must not be treated as proof of database commit. 7. **Worker durably records completion and publishes the report.** Write checkpoint after commit confirmation, then publish the report using an atomic local file replacement. Verify raw-row reconciliation first. 8. **Operator handles exceptions.** Authentication failures, uncertain commits, conflicts and local persistence failures have separate instructions. Every replay requires approval. **Removed steps:** repeated manual header diagnosis and unnecessary refetching of a retained source for recovery. **New dependencies:** retained source files, a durable local row ledger and manifest, access controls, retention rules and single-worker coordination. **Additional effort justification:** these controls improve traceability and make failures easier to diagnose. They still do not create atomicity between PostgreSQL and local storage or guarantee historical attribution under concurrent writes. ## 5) Automation decisions | Point | Trigger, inputs and processing | Output and error handling | Class | Benefit, effort and control | |---|---|---|---|---| | Header mapping | CSV received; inspect headers and map `id` explicitly | Valid mapping or clear prewrite failure | Existing Python worker | Directly fixes the observed defect; small scope | | Row validation | Header accepted; validate numbered raw rows | Valid candidates and reasoned rejections; continue past invalid rows | Python script | Preserves valid rows and supports reconciliation | | Duplicate/conflict classification | Valid row; attempt insert without replacing an existing row, then compare label if necessary | Inserted, identical-existing or conflicting; database error stops the attempt | PostgreSQL SQL through worker | Uses existing key protection; operator retains conflict decisions | | Report reconciliation | Confirmed commit; row ledger available | Report only if counts reconcile; otherwise incomplete status | Python script | Prevents misleading completion counts | | Recovery evidence | Run starts and processing advances | Source snapshot and durable local manifest; storage failure stops before further writes where possible | Local file handling | Reduces diagnostic effort; adds storage and retention obligations | | Replay gate | Failed attempt selected for replay | Recorded approval before a new attempt; missing approval blocks execution | Worker control | Enforces a binding rule; verify all retry paths | The database operation must use the existing service account’s insert/select permissions. No update permission or schema migration is required. **Remain manual:** - Replay approval: explicitly required, particularly after uncertain commits. - Conflicting-label decisions: existing values must remain unchanged pending review. - Authentication correction after 401: stop and let the owner resolve authorization. - Acceptance of an incomplete or delayed run: human decision; never relabel it as successful. Do not place bearer tokens in logs, manifests or reports. Limit local artifact access to relevant staff. ## 6) Implementation plan ### First 48 hours Allocate the four developer hours in this order: 1. Disable automatic replay and implement the approval gate. 2. Fix and check the `id` header mapping before writes. 3. Add row-level rejection and existing-record conflict classification. 4. Check reconciliation and exercise the highest-risk fixture failures. **Acceptance:** expected fixture results match actual row classifications; existing labels remain unchanged; invalid rows do not prevent valid rows from processing; 401 stops; replay without approval is blocked. If four hours are insufficient, document unfinished controls and keep the worker in controlled fixture evaluation. No production readiness claim is justified. ### Prioritized two-week plan | Period | Responsible | Work and dependency | Acceptance | |---|---|---|---| | Week 1 | Developer | Complete initial repair and controlled fixture cases | Header, invalid-row, duplicate, conflict, 401 and replay behavior verified | | Week 1 | Operator | Run supervised 200-row and 400-row fixtures after repair | Row totals reconcile; runtime and review time recorded | | Week 2 | Developer | Test failure before commit, uncertain commit and postcommit checkpoint failure | No automatic replay or false successful report | | Week 2 | Developer and operator | Add recovery evidence where justified; write operating instructions | New staff can start, inspect and approve replay without changing code | Use a disposable fixture database with the same existing table definition. Inject failures only in the local test environment. ### 30-day roadmap | Stage | Responsible | Dependencies | Acceptance criteria | |---|---|---|---| | Days 1–2: repair | Developer | Available worker, fixtures and database | Initial controls verified within the available effort or gaps explicitly recorded | | Days 3–14: pilot | Developer and operator | Repair accepted | Normal and failure cases demonstrated; 200/400-row timing measured | | Days 15–21: recovery hardening | Developer, subject to additional capacity | Pilot findings; local storage and writer assumptions checked | Durable evidence and exception instructions verified | | Days 22–30: operational review | Operator and owner | Enough weekday fixture observations | Assess deadline, review capacity, unresolved failures and test expenditure | Additional developer capacity after the initial four hours is unspecified; later engineering work depends on its availability. **Fallback:** stop the run, retain the input and evidence, notify the operator and wait for approved recovery. Do not restore automatic replay. ## 7) Success control | KPI | Definition and collection | Baseline, target or threshold | |---|---|---| | Row reconciliation | Compare raw-row count with the four category totals; verify each source row appears once | Target: exact reconciliation for completed reports | | Integrity violations | Count duplicate stored IDs and unauthorized label changes using fixture assertions and before/after comparison | Target: zero | | Unauthorized replay | Compare attempt records with approval records | Target: zero | | Delivery time | Measure start-to-report duration and report availability time | Baseline required; target: report by 09:00 | | Operator effort | Record diagnosis, review and approval minutes per run | Estimated baseline: 20 minutes/day; capacity: 30 minutes/day | | Unresolved recovery | Count incomplete attempts awaiting outcome resolution | Baseline required; any unresolved commit blocks that attempt’s successful completion | Measure during initial fixtures, review after the two-week pilot and reassess at day 30. **Maintain** when integrity and approval controls pass and measured delivery and operator effort fit the targets. **Improve** when reports remain correct but timing, review effort or repeated exceptions exceed capacity. **Stop or roll back the change** on an overwrite, unauthorized replay, incorrect reconciliation or falsely confirmed commit. Preserve evidence and use the stopped-run fallback; require operator approval for any subsequent replay.