🔗 2026-09-13 Cafe24 spent-marker link FK failure
🔗

Cafe24 spent-marker link FK failures

Environment: Production (prod_gbstyle, app1-srv)
Date: 2026-09-13 KST
Symptom: Repeated ERROR Failed linking spent marker (138+). POS payment still succeeded.
Status: Today's unlinked done markers were backfilled. Code defense is not deployed yet.

What happened

On POS pay with Cafe24 points, _process_order does this:

  1. Call Cafe24 decrease API.
  2. Insert cafe24.point.spent.marker as state=done on a separate committed cursor.
  3. Create pos.order on the main uncommitted transaction.
  4. Immediately link marker.pos_order_id on another separate committed cursor.

Step 4 fails:

ForeignKeyViolation: cafe24_point_spent_marker_pos_order_id_fkey
Key (pos_order_id)=(<new id>) is not present in table "pos_order"

The new pos.order is not visible to the other connection until the HTTP request commits. The exception is caught, so the sale still commits. The marker stays done with an empty pos_order_id.

Impact before backfill

  • POS payment: succeeds
  • Cafe24 points deducted: yes (correct for paid sales)
  • Marker tracking: unlinked
  • Rollback / points restored: none today
  • Volume: 147 done markers today after catch-up; about 1.18M points across 42 stores

A second bug: marker create_date is written with PostgreSQL now() (Asia/Seoul), while cron_reconcile_spent_markers compares it to a UTC grace cutoff. During the day the cron sees zero of today's unlinked rows. After about 9 hours it would start processing them.

If the cron then fails to match pos_reference + member_id + points_spent, it moves the marker to rollback_pending and credits points back even though the sale already paid. Refunds are the risky case: Cafe24 decrease can be recorded while pos.order.points_spent stays 0.

Immediate data fix (2026-09-13 14:18 KST)

  1. Backup: /data/backup/ops_manual/20260913_link_spent_markers_cafe24_point_spent_marker.sql
  2. Linked 142 markers where reference, member, and points_spent matched.
  3. Linked 4 refund leftovers by reference + member only (points_spent = 0).
  4. Linked 1 sale that arrived during the update.

After fix: 0 unlinked done markers today, rollback_pending = 0.

Defense (code, not deployed)

  1. Do not link on a separate cursor before commit. Set pos_order_id on the main cursor after super()._process_order(), or queue the link for after commit / cron only.
  2. Keep the cron as a safety net, not the first writer. Match by reference + member + amount. If several orders share a reference, prefer the points_spent match, then the non-refund order. Do not use limit=1 blindly.
  3. Use one clock for marker timestamps. Write create_date / write_date / last_attempt with UTC (fields.Datetime.now()), not PostgreSQL now() under Asia/Seoul.
  4. Do not rollback a done marker that already has a paid POS order. If an order exists for that reference and member, link it. Only roll back when no paid order exists after the grace window.
  5. Ops check after a spike of this ERROR: count state='done' AND pos_order_id IS NULL, link matches, and confirm rollback_pending is still 0 before the evening UTC cutoff.

Code pointers

  • bss_gbstyle_cafe24_integration/models/pos_order.py — _process_order, _spent_marker_write, _spent_marker_link_order
  • bss_gbstyle_cafe24_integration/models/cafe24_point_spent_marker.py — cron_reconcile_spent_markers

Verification SQL

SELECT state,
       count(*) FILTER (WHERE pos_order_id IS NULL) AS unlinked,
       count(*) FILTER (WHERE pos_order_id IS NOT NULL) AS linked
FROM cafe24_point_spent_marker
WHERE create_date >= '2026-09-13 00:00:00'
GROUP BY state;