atrax-fleet-sync-409-replate

Playbook — n8n “atrax fleet sync” failing with 409 / 23505 on p24_l_cars

Workflow: atrax, kravag-scheduled-fleet-updates (id CCx9UMdphmGficDX, bms-4 n8n, 3-min schedule) Alert: N8nWorkflowPersistentlyFailing · First seen: #1174 (2026-06-23)

⚠️ Signature disambiguation — read first (#6313). This playbook applies only to a Supabase 409 / 23505 duplicate-key error on p24_l_cars (the re-plate case below). It does NOT apply to failures of the external Atrax API on this same workflow: an intermittent 401 invalid_token (“Authorization failed”) on GET https://tronik.atrax4.com/api/autoreportorder (node HTTP Request1) or an intermittent ETIMEDOUT on POST https://tronik.atrax4.com/api/transport/filtr (node et-auta-na-placu) is a different class — the external-Atrax-API case in §External Atrax API failures below. Never apply the one-shot UPDATE p24_l_cars SET atrax_car_id=NULL … DB unblock to that class — it does nothing for a 401/timeout and needlessly touches unrelated fleet rows. Mis-routing to this playbook happened for #6312; this note exists to prevent it recurring.

Trigger

The workflow’s update-atrax-pojazdy-w car-atrax node (a POST to Supabase p24_l_cars_atrax?on_conflict=atrax_id) fails every run with NodeApiError / HTTP 409. The Postgres error is 23505: duplicate key value violates unique constraint "p24_l_cars_atrax_car_id_unique".

Note: despite the name, this node writes to Supabase, not the Atrax API. The 409 is PostgREST surfacing a Postgres unique-violation, not an Atrax rejection.

Confirmation commands

Pull the exact failing node + error from the n8n execution store on bms-4:

docker exec bms-4-n8n-postgres-1 psql -U n8n -d n8n -t -A -c "
SELECT ed.data FROM execution_data ed
JOIN execution_entity ee ON ee.id=ed.\"executionId\"
WHERE ee.\"workflowId\"='CCx9UMdphmGficDX' AND ee.status='error'
ORDER BY ee.id DESC LIMIT 1;" | grep -o '23505[^}]*}'

Find which fleet rows hold a stale atrax_car_id (collision-capable = has_target_row=true):

SELECT c.plates AS fleet_plate, c.atrax_car_id, a.registration_no AS atrax_plate,
       EXISTS(SELECT 1 FROM p24_l_cars c2 WHERE c2.plates=a.registration_no) AS has_target_row
FROM p24_l_cars c JOIN p24_l_cars_atrax a ON a.atrax_id=c.atrax_car_id
WHERE c.plates IS DISTINCT FROM a.registration_no
ORDER BY has_target_row DESC;

Root cause

A vehicle is re-plated in Atrax (e.g. HVLET442 → BET824). The merge_atrax_to_fleet() trigger on p24_l_cars_atrax tries to move the vehicle’s atrax_id onto the fleet row matching the new plate, but the atrax_id is still attached to the old plate’s row. p24_l_cars.atrax_car_id is UNIQUE, so the reassignment raises 23505 → 409 → the whole batch upsert aborts.

Fix (durable — already shipped)

Migration supabase/migrations/20260623_fix_1174_merge_atrax_replate_safe.sql makes merge_atrax_to_fleet() release a stale atrax_car_id from any other row before reassigning, guarded by EXISTS(fleet row for current plate) so cosmetic-only mismatches (*BET561, B ET 633) are never unmapped. Once this trigger is in place, re-plate cases self-heal on the next scheduled run.

If the trigger is somehow reverted, a one-shot manual unblock:

-- release the stale mapping, then re-fire the trigger
UPDATE p24_l_cars SET atrax_car_id=NULL, gps_atrax_installed=false
WHERE atrax_car_id='<offending_id>' AND plates <> '<current_atrax_plate>';
UPDATE p24_l_cars_atrax SET updated_at=NOW() WHERE atrax_id='<offending_id>';

Verify

Wait one schedule cycle (≤3 min) and confirm a success execution:

KEY=$(grep '^BMS4_N8N_API_KEY=' /opt/p24-infra/bms-4/.env | cut -d= -f2- | tr -d '\r')
curl -s -H "X-N8N-API-KEY: $KEY" \
  "http://localhost:5678/api/v1/executions?workflowId=CCx9UMdphmGficDX&limit=1" \
  | python3 -c "import sys,json;e=json.load(sys.stdin)['data'][0];print(e['status'],e['startedAt'])"

Escalation

If 23505 persists with has_target_row=true rows that are NOT a clean re-plate (e.g. two distinct physical vehicles claiming the same Atrax id), it is a fleet data-modeling issue — escalate to the fleet/workflow owner; do not auto-merge rows.

---

External Atrax API failures (401 / ETIMEDOUT) — NOT this playbook

Signature: the N8nWorkflowPersistentlyFailing alert fires, but the failing node is a direct call to the third-party Atrax API (tronik.atrax4.com, 46.229.146.98) — not the Supabase upsert:

Node	Request	Failure
HTTP Request1	GET https://tronik.atrax4.com/api/autoreportorder	intermittent 401 invalid_token (“Authorization failed”)
et-auta-na-placu	POST https://tronik.atrax4.com/api/transport/filtr	intermittent ETIMEDOUT

Two executions fire per 3-min cycle; the :30 (Supabase-writing) branch always succeeds, the :32 (Atrax-API) branch fails every few cycles → ~20 errors/hour that keep re-tripping the alert. Failures are transient and self-heal on the next cycle (fleet data still updates) — this is alert noise + resilience, not a data outage. This is NOT the 23505 re-plate case above — do not apply the UPDATE p24_l_cars SET atrax_car_id=NULL … unblock; it does nothing here.

Token refresh mechanism (the Atrax bearer token IS workflow-managed)

The authorization: Bearer {{ $json.value }} header on both nodes uses an OAuth token this workflow fetches and stores itself — it is not a static credential and not a token an external party hands us:

1. 3 minutes1 schedule → get_token (POST https://tronik.atrax4.com/oauth/token) fetches a fresh token.
2. get_token → Update row(s) writes the token into an n8n data table.
3. Get row(s)_kravag / Get row(s)2 read that stored token back; HTTP Request1 / et-auta-na-placu send it as the bearer (Bearer {{ $json.value }}).
4. IF valid token gates a re-fetch (→ get_token) when the stored token is invalid.

So a proactive-refresh path already exists in the workflow. The intermittent nature of the 401 (most cycles succeed with the same stored token; a few fail) points to Atrax-side token-validation flakiness, not a clean local-token expiry — a genuinely expired token would fail every cycle until get_token refreshes it, not one-in-a-few. No new refresh mechanism is therefore warranted.

Mitigation (shipped, #6313)

HTTP Request1 and et-auta-na-placu now have Retry on Fail (retryOnFail=true, maxTries=3, waitBetweenTries=3000ms) — matching the convention already used by the workflow’s Supabase HTTP nodes (supabase upsert query, update-atrax-pojazdy-w car-atrax, …). A transient 401/ETIMEDOUT now clears within the same run instead of failing the execution and re-tripping the alert. Applied live via the n8n public REST API (GET → patch the two nodes’ retryOnFail/maxTries/waitBetweenTries → PUT, verifying the JSON round-trips cleanly); the workflow JSON is not exported into git. The workflow settings (incl. maxConcurrency: 1) are preserved across the PUT — the public API’s settings schema is a strict subset, so send only {callerPolicy, errorWorkflow, executionOrder} and n8n merges the rest, rather than sending the full internal settings object (which is rejected with settings must NOT have additional properties).

Verify (external-API class)

Confirm the two Atrax nodes carry the retry config, and that recent :32-branch executions succeed:

KEY=$(grep '^BMS4_N8N_API_KEY=' /opt/p24-infra/bms-4/.env | cut -d= -f2- | tr -d '\r')
curl -s -H "X-N8N-API-KEY: $KEY" "http://localhost:5678/api/v1/workflows/CCx9UMdphmGficDX" \
  | jq -r '.nodes[] | select(.name=="HTTP Request1" or .name=="et-auta-na-placu")
           | "\(.name): retryOnFail=\(.retryOnFail) maxTries=\(.maxTries) wait=\(.waitBetweenTries)"'
unset KEY