## 1) Data flow and data contract - **Source:** UTF-8 CSV fetched by the existing local worker. - **Mapping:** CSV `id` → PostgreSQL `items.record_id`; CSV `label` → `items.label`. - **Target:** Existing `items(record_id integer primary key, label text)`. No schema changes. - **Volume:** 200 raw rows per batch; peak 400. Accepted counts depend on validation. Each raw row receives a source row number and exactly one outcome: | Outcome | Meaning in a completed attempt | |---|---| | Inserted | This attempt confirmed that the row’s insert committed. | | Identical-existing | No insert occurred; the stored ID and label match. | | Rejected | The row failed input or business validation. | | Conflicting | The ID has a different label in the database or a contradictory label elsewhere in the batch. | **Proposed conservative batch rule:** If one valid ID appears with different labels within the CSV, classify every otherwise-valid row for that ID as conflicting and leave that ID unchanged. This avoids choosing a label based on file order. For repeated identical rows, process the first occurrence normally; subsequent occurrences become identical-existing after the matching database state is confirmed. **Open mapping points:** Confirm whether whitespace-only labels are empty and whether surrounding whitespace should be preserved or normalized. Until agreed, introduce no trimming, case conversion, or other normalization. ## 2) Pipeline architecture 1. **Trigger:** Configure the existing worker’s available scheduling mechanism for weekdays at 07:00. Confirm the scheduling timezone. Scheduling must not automatically replay a failed attempt. 2. **Fetch:** Retrieve the CSV using the specified fictional bearer authentication. Stop immediately on HTTP 401. Disable current automatic retries that could replay failed work. 3. **Capture:** Preserve the fetched input locally with an attempt identifier, input digest, and raw-row numbering, enabling review of the exact batch. 4. **Validate:** Check CSV structure, required headers, ID conversion, label rules, and contradictory duplicate IDs before writing. 5. **Deliver:** Process eligible rows sequentially using parameterized SQL and a short transaction per row: - Execute `INSERT ... ON CONFLICT (record_id) DO NOTHING RETURNING record_id`. - If inserted, commit before reporting inserted. - Otherwise, select the existing label and classify it as identical-existing or conflicting. - Never update an existing label. 6. **Report:** Write a local attempt report, including counts, rejected/conflicting row details, and any pending or unresolved rows. **Boundaries:** Fictional source API → local input and report files → existing PostgreSQL. No additional service or database object is required. ## 3) Reliability and repeatability **Mandatory controls:** - The existing primary key enforces at most one stored row per ID. - Conflict-aware inserts prevent duplicate-key errors from discarding unrelated valid rows. - Separate row transactions preserve earlier confirmed commits when later work fails. - The local checkpoint records progress but is **not authoritative evidence of a database commit**. - Every replay after failure requires explicit operator approval. Disable automatic request, transaction, worker, and scheduler replays for the failed attempt. Set finite fetch, database-statement, and connection timeouts. Select their values from fixture measurements within the 07:00–09:00 window; runtime is currently unknown. **Failure handling:** - Validation failures are listed while valid rows continue. - A database or infrastructure failure stops further writes. Preserve confirmed outcomes and mark untouched rows pending. - If a connection fails around commit, classify the affected row as **commit-outcome-unresolved**. Do not infer rollback or repeat the insert automatically. - If commit succeeded but local outcome recording failed, retain attribution uncertainty. - After operator approval, replay the captured batch as a **new attempt with its own report**. Existing matching rows are safely classified against current database state. A replay may establish the desired database contents without proving which earlier attempt inserted a row. Preserve that distinction. ## 4) Data quality and protection **Input and schema validation:** - Require UTF-8 decoding and readable CSV structure. - Require `id` and `label` headers; map `id` explicitly before validation. - Reject missing, malformed, or out-of-range PostgreSQL integer IDs. - Reject missing or empty labels; resolve whitespace policy before declaring that edge case accepted. - Reject malformed rows when their boundaries are identifiable. If corruption prevents reliable raw-row reconciliation, stop and report the structural problem rather than inventing counts. **Business validation:** - Compare labels exactly unless an approved normalization rule exists. - List different-label collisions for operator review. - Never overwrite them. Use the existing service account’s `SELECT` and `INSERT` permissions. Keep authentication values out of logs and reports; do not introduce real credentials into fixtures. Restrict local input/report access to the worker and authorized operator. Retention periods and any necessary data protection review remain open; the supplied fixture is fictional sales-example data. ## 5) Operation and observability Record: - Attempt identifier, input digest, start/end times, and status. - Raw-row count and each outcome count. - Row number and reason for rejected, conflicting, pending, or unresolved records. - Fetch/authentication failures, database failures, and elapsed runtime. For a completed attempt: **Raw rows = inserted + identical-existing + rejected + conflicting** For an interrupted attempt, add **pending + commit-outcome-unresolved** to the reconciliation. **Success criteria:** - Every raw row is accounted for exactly once. - No pending or unresolved rows remain. - Every accepted row has the intended ID and label in the database. - Existing different labels remain unchanged. - Report availability by 09:00 is demonstrated through measured normal and peak fixtures; it is not promised before measurement. - Expected fixture categories match exactly; unexpected processing failures are zero. Rejected and conflicting business records are reported separately from technical failures. Alert the operator on 401, infrastructure failure, unresolved outcomes, reconciliation mismatch, or a missing report at 09:00. **Recovery:** Review the report and approve a new attempt. **Rollback:** Roll back the active uncommitted transaction. Do not automatically delete earlier committed rows: unchanged schema and uncertain attribution can make compensating deletion unsafe. ## 6) Scaling and costs **Mandatory initial sizing:** One existing worker, sequential processing, and the existing database. At 200–400 raw rows, measure this implementation before adding concurrency or bulk staging. Stay within the EUR 50 fixture-test budget by using the existing environment and bounded fixture runs. Track actual billable usage where applicable; no external pricing assumptions are needed. **Later extension:** Optimize only if measured runtime threatens the reporting window or volumes exceed the tested 400-row peak. Consider fewer database round trips before additional infrastructure. Any optimization must preserve per-row reconciliation and commit uncertainty handling. ## 7) Implementation plan Prioritize the four developer hours as follows: 1. **Fix the immediate failure:** Map CSV `id` to `record_id`; validate required headers before row processing. 2. **Make writes safe:** Replace plain inserts with conflict-aware inserts, exact-label comparison, and short row transactions. 3. **Control recovery:** Disable automatic replays; add operator approval gating and explicit pending/unresolved statuses. 4. **Reconcile and verify:** Produce attempt reports, run fixtures, and measure execution time. Required fixtures: | Test | Expected result | |---|---| | Normal input | Valid new rows commit; mapping works; counts reconcile. | | Identical duplicate | One stored ID; subsequent matching rows classified identical-existing. | | Invalid record | Invalid row listed; unrelated valid rows preserved. | | Different-label collision | Conflict listed; existing label unchanged. | | Partial failure | Earlier confirmed commits retained; remaining rows pending. | | Lost commit acknowledgement | Affected outcome unresolved; no success claim or automatic replay. | | Restart | Failed attempt remains blocked until approval; approved replay gets a new report. | | HTTP 401 | Immediate stop; no automatic retry. | | 200- and 400-row batches | Actual runtime and report completion time recorded. | The four-hour limit is an implementation timebox, not evidence that every check will pass. Any unfinished recovery or verification control must remain explicit, and the pipeline must not be declared successful while unresolved rows remain.