ADR-0138 — Idempotency-Key Mechanism for Raptor Write Endpoints

Status: Accepted Date: 2026-07-10 UTC Deciders: Kristerpher (operator); software-architect (design) Scope: Raptor (backend_v2/); getraxx.com signup surface; all Raptor mutating routes that affect money, positions, or account state Refs: #4002 (this design card), #4001 (waitlist dedup, now closed),

4000 (parent epic), ADR-0127 (Stripe webhook idempotency, Stripe-side already solved)

Context

Raptor's write paths are called from Antlers (client retries on network failure), internal service-to-service calls, and operator tooling. Without a dedup layer, a transient 5xx followed by a client retry can double-apply state changes: a second order row, two billing checkout sessions, or two LCC roll legs submitted against the same position.

The deterministic-execution invariant makes silent double-fire uniquely dangerous here: every order the paper engine executes is intentional and audited. An accidental duplicate changes cash balance and position state in ways that are difficult to unwind after the fact.

Prior work — do not duplicate:

This ADR designs the general mechanism covering the remaining mutating routes — primarily order submission and other money/position-affecting writes.

Invariants

These constraints from the project charter govern this design and override any implementation convenience:

• No stored credentials. The response_body column in the idempotency store must never contain auth tokens, session secrets, or credentials. The response replay only covers order/billing responses, which do not carry credentials.
• Audit trail for every state change affecting money. A replayed response is not a new state change; the original execution's audit row is the canonical record. The middleware must log a replay event separately (tagged as replay) without writing a duplicate audit_log row.
• Deterministic execution. The idempotency mechanism must guarantee at-most-one execution of the business logic per idempotency key, not at-least-one. A race that causes two executions — even with dedup on the storage side — violates this.
• Paper-first gating. The order-path idempotency check cannot bypass or short- circuit any existing paper/live gate.
• GDPR. The idempotency store is in scope for data subject rights: erasure, access, portability.

Decision

Implement a client-supplied Idempotency-Key: <UUID> header pattern backed by a dedicated Postgres table (idempotency_keys). The concurrency serialization strategy is unique-constraint-first with in-flight status: the first request to INSERT a key wins and executes; concurrent duplicates read the status column and either replay the completed response or receive a 409 "in progress" response that instructs retry. For order-affecting endpoints, the middleware is fail-closed: if the idempotency store is unavailable, the request is rejected. For other endpoints (billing, account mutations), the middleware is fail-open: if the store is unavailable, the request proceeds without dedup protection but with a warning log.

Language choice rationale

This ADR does not introduce a new service. The idempotency middleware is a Python decorator and a Postgres table addition within the existing Raptor (Tier 2 Python) service. No tier reclassification is triggered.

Data model

idempotency_keys table

-- POSTGRES-ONLY
CREATE TABLE idempotency_keys (
    id              BIGSERIAL      PRIMARY KEY,
    idem_key        TEXT           NOT NULL,
    scope_user_id   INTEGER        REFERENCES users(id) ON DELETE CASCADE,
    scope_endpoint  TEXT           NOT NULL,
    request_hash    TEXT           NOT NULL,
    response_code   SMALLINT,
    response_body   JSONB,
    status          TEXT           NOT NULL DEFAULT 'in_flight'
                                   CHECK (status IN ('in_flight','completed','failed')),
    created_at      TIMESTAMPTZ    NOT NULL DEFAULT NOW(),
    locked_at       TIMESTAMPTZ    NOT NULL DEFAULT NOW(),
    expires_at      TIMESTAMPTZ    NOT NULL,
    CONSTRAINT uq_idem_key_user_endpoint
        UNIQUE (idem_key, scope_user_id, scope_endpoint)
);

CREATE INDEX idx_idem_keys_expires
    ON idempotency_keys (expires_at)
    WHERE status IN ('completed','failed');

CREATE INDEX idx_idem_keys_user
    ON idempotency_keys (scope_user_id)
    WHERE scope_user_id IS NOT NULL;

Column notes:

Key scoping rule: The unique constraint is (idem_key, scope_user_id, scope_endpoint). User A's key abc and User B's key abc on the same endpoint are distinct records. A service call without a user scope has scope_user_id = NULL; two such calls with the same key and endpoint on the same endpoint DO conflict (Postgres UNIQUE treats NULL = NULL only with partial indexes; the constraint is written without NULLs-as- not-equal which means NULL-scoped keys don't conflict — for service endpoints this is acceptable as they use endpoint-specific natural dedup).

TTL values:

API contracts

Request header

Idempotency-Key: <uuid-v4>

• Optional on all endpoints in Wave 1; enforcement (required) may be added per- endpoint in Wave 3+ at feature-developer discretion.
• If absent, the request is processed as today — no idempotency tracking.
• If present but malformed (not a UUID string), return 400 Bad Request: {"error": "Idempotency-Key must be a UUID string"}.

Response semantics

Endpoint coverage map

Tier 1 — Fail-closed (order/position writes)

These endpoints must REJECT the request if the idempotency store is unavailable. A retry on a 503 is safe; a duplicate fire is not.

Tier 2 — Fail-open (billing/account mutations)

These have upstream dedup (Stripe idempotency keys, Queue billing_action_log) or lower blast radius. Idempotency-Key is additive protection.

Already idempotent — do not instrument (would be redundant)

• DELETE /api/trading/orders/<id> — MbtFillEngine.cancel_order returns current status on double-call; no duplicate is created
• POST /api/waitlist/signup — IntegrityError catch + unique index (#4001)
• POST /api/auth/register/verify-with-token — existing idempotent retry (#2771)
• POST /api/billing/mirror-sync — ON CONFLICT DO UPDATE upsert
• Stripe webhooks — ADR-0127 / Queue processed_stripe_events

Concurrency serialization

This is the critical correctness property: two simultaneous requests with the same Idempotency-Key must not both execute the business logic.

Algorithm (fail-closed path):

1. Extract Idempotency-Key header. If absent, passthrough.
2. Compute request_hash = SHA256(sorted_json_body).
3. Attempt INSERT INTO idempotency_keys
       (idem_key, scope_user_id, scope_endpoint, request_hash, status, locked_at, expires_at)
   VALUES (:key, :uid, :endpoint, :hash, 'in_flight', NOW(), NOW() + :ttl)
   ON CONFLICT DO NOTHING
   RETURNING id, status, request_hash, response_code, response_body, locked_at;
4. If INSERT returned a row (we won the race):
   a. Execute the business logic handler.
   b. On success:
      UPDATE idempotency_keys
        SET status='completed', response_code=:code, response_body=:body
        WHERE id=:id;
      Return handler response.
   c. On failure:
      UPDATE idempotency_keys SET status='failed' WHERE id=:id;
      Return handler error response.
5. If INSERT returned no row (conflict — someone else holds or held this key):
   SELECT id, status, request_hash, response_code, response_body, locked_at
   FROM idempotency_keys
   WHERE idem_key=:key AND scope_user_id=:uid AND scope_endpoint=:endpoint;

   a. If status='completed': return stored response + "idempotency_replayed": true.
   b. If status='failed': return 409 idempotency_previous_failed.
   c. If status='in_flight':
      - If NOW() - locked_at > 30s (stale): treat as failed → 409 + log alert.
      - Else: return 409 idempotency_in_flight with retry_after_ms=5000.
   d. If request_hash != stored hash: return 422 idempotency_key_body_mismatch
      (checked before status).

Why not SELECT FOR UPDATE? The INSERT ON CONFLICT DO NOTHING RETURNING pattern achieves the same race-win determination in a single round-trip without holding a row-level lock across the business logic execution time (which could be 100-500ms for order submission). Holding a FOR UPDATE lock that long would serialize all concurrent requests for the same user behind one lock, causing timeout cascades under normal retry load.

Why INSERT-first rather than SELECT-then-INSERT? SELECT-then-INSERT has a check-then-act race: two requests both read "not found" and both INSERT, both winning. The unique constraint turns the INSERT into an atomic compare-and-swap.

Stale in-flight threshold (30s): Raptor's gunicorn worker timeout is configured at 30s. A request that has been in-flight for >30s has been killed by the worker timeout and will never complete — its row is an orphan. The 30s threshold therefore safely frees the key for a new attempt. Log a WARNING when a stale in-flight is detected (possible indicator of worker crashes or DB slowness).

Stale-heal audit note: If a roll's handler wrote to audit_log outside tx-2 (i.e. via a separate transaction rather than through caller_conn), those audit rows will have committed even though tx-2 was rolled back — leaving audit entries that describe an operation that never completed. Cross-check orphan audit_log entries against paper_orders when investigating stale-heal incidents on roll endpoints.

State machine

stateDiagram-v2
    [*] --> in_flight : INSERT wins race
    in_flight --> completed : handler succeeds
    in_flight --> failed : handler errors
    in_flight --> stale_in_flight : locked_at > 30s ago [read by loser]
    stale_in_flight --> [*] : 409 returned, new attempt may INSERT

    [*] --> replay : INSERT conflicts, status=completed
    replay --> [*] : stored response returned

    [*] --> conflict_failed : INSERT conflicts, status=failed
    conflict_failed --> [*] : 409 returned

    [*] --> in_progress : INSERT conflicts, status=in_flight (fresh)
    in_progress --> [*] : 409 retry_after returned

Middleware design

# Stub — feature-developer implements against this spec.
# Location: backend_v2/api/middleware/idempotency.py

def idempotency_key(*, ttl_hours: int = 24, fail_closed: bool = True):
    """
    Decorator for Raptor route handlers.

    @bp.route('/orders', methods=['POST'])
    @require_session
    @idempotency_key(ttl_hours=24, fail_closed=True)
    def place_order():
        ...

    fail_closed=True: 503 if idempotency store is unavailable.
    fail_closed=False: proceed without dedup, log warning.

    The decorator:
    1. Reads Idempotency-Key header; returns 400 if malformed UUID.
    2. Computes request_hash from request.get_json(silent=True).
    3. Runs the INSERT ON CONFLICT DO NOTHING algorithm above.
    4. On replay: returns stored response with "idempotency_replayed" injected.
    5. On winner path: calls the wrapped handler, then stores result.
    6. Records idempotency_hit / idempotency_miss / idempotency_replay counters
       for Prometheus (see [ADR-0121](https://internal-docs.raxx.app/architecture/adr/0121-status-surface-registry.html) observability surface).
    """

Flag gate: FLAG_IDEMPOTENCY_MIDDLEWARE. When off, the decorator is a passthrough. This enables the migration to land before any enforcement is active.

The decorator must be applied AFTER @require_session so that g.current_user_id is available for scope_user_id.

Migrations

Migration 0060 (0060_idempotency_keys.py):

• Creates idempotency_keys table with all columns above.
• Creates unique index uq_idem_key_user_endpoint.
• Creates expiry index idx_idem_keys_expires.
• Creates user index idx_idem_keys_user.
• Must carry -- POSTGRES-ONLY sentinel (TIMESTAMPTZ and BIGSERIAL are not supported by the SQLite test runner).
• Alembic single-head constraint: must depend on 0059_waitlist_unique_email_ci.
• No rollback data loss: table is new; downgrade() drops the table.

Cleanup job:

A Woodpecker nightly cron step (or Raptor CLI command flask idempotency cleanup) runs:

DELETE FROM idempotency_keys WHERE expires_at < NOW();

This is safe to run while the table is live. Index idx_idem_keys_expires makes it an index scan. Target: run at 04:00 UTC daily. Cleanup job is a sub-card (SC-IK-1) responsibility.

B1 flag migration: The FLAG_IDEMPOTENCY_MIDDLEWARE flag must have a console_flag_promotions migration in the same PR as the flag YAML entry, per the project standard (feedback: new_flag_needs_b1_migration_same_pr).

Rollout plan

Kill-switch: set FLAG_IDEMPOTENCY_MIDDLEWARE=0 to revert the decorator to a passthrough on all endpoints simultaneously without a code deploy.

getraxx signup gap vs #4001

4001 shipped:

• uix_waitlist_signups_email_ci unique index on lower(email) (migration 0059)
• IntegrityError catch in waitlist_signup → returns 202 idempotently
• register_verify_with_token already returns 200 on duplicate credential (#2771)

Remaining gap (covered by this ADR's general mechanism, not requiring new specialized work):

• The /api/marketing beta-registration path has its own already_registered path (found in marketing.py:222); this is sufficient for that surface.
• Full-account creation (/api/auth/register/verify-with-token) has the #2771 idempotent-retry path; no additional work needed for duplicate account prevention.
• The general Idempotency-Key mechanism in this ADR is not needed for signup paths because those paths already use natural-key dedup (email uniqueness). This ADR addresses write paths where natural-key dedup does not apply (order submission, billing mutations).

Net result: no sub-card is required for signup dedup beyond #4001.

Security considerations

• PII in response_body: Order responses contain symbol, qty, fill_price, and paper_order IDs — not PII. Billing responses may contain Stripe session IDs (not PII, but sensitive). If a future endpoint stores PII in its response, that response must be excluded from the idempotency body store (set response_body = NULL and disable replay — force re-execution).
• GDPR DSR: On erasure request, DELETE FROM idempotency_keys WHERE scope_user_id = :uid must run as part of the GDPR deletion sweep. Key records are not themselves PII but they link to a user. SC-IK-6 implements this.
• Audit: Replay events write a distinct audit_log entry (action='idempotency_replay', context includes original request hash and original response code). This preserves the audit invariant without doubling the business-logic audit row.
• Key entropy: The server must validate that client-supplied keys parse as UUIDs to prevent key-space enumeration (e.g. keys like "all" or "admin").
• Credential storage: response_body must never contain auth tokens. Enforced by the handler list — auth endpoints are excluded from idempotency instrumentation.
• Breach notification: The idempotency_keys table is an internal operational table. If breached, it exposes order timing and amounts but not PII or credentials. Standard breach notification timeline applies (72h to operator, 72h to regulators for EU users).
• Secret rotation: FLAG_IDEMPOTENCY_MIDDLEWARE is an environment flag. No secret material; no rotation required.
• Kill-switch: Setting FLAG_IDEMPOTENCY_MIDDLEWARE=0 disables idempotency enforcement without disabling order submission. This is intentional: a broken idempotency store on Tier 1 paths returns 503 (fail-closed), which is already a safe degradation.

Open questions

1. Live trading path: This design covers the paper-trading (MBT) path. When live-trading against a broker is enabled, the idempotency key should be forwarded as the broker's own idempotency key (e.g. client_order_id for Alpaca). This requires a separate design pass. Flag for future ADR.

2. response_body encryption: Responses stored in JSONB are visible to anyone with Postgres read access (support roles, sre-agent). Operator decision needed: encrypt at the column level (Postgres pgcrypto) or accept that order details in the idempotency store are readable with DB access.

3. Idempotency-Key required vs optional: Should the header be required (return 400 if absent) for Tier 1 endpoints in GA? Required header enforces client correctness but breaks unmodified clients. Operator decision needed before Wave 3 rollout.

Alternatives considered

A. Natural-key dedup per endpoint

Use the domain semantics to deduplicate: e.g. reject a second order with identical symbol/side/qty within N seconds. Rejected because: - A user legitimately wants two identical orders (averaging in). - The dedup window is arbitrary and leaks state to the caller. - Does not generalize across endpoints; requires bespoke logic per route.

B. Server-derived hash key (no client header)

Hash the request body server-side; use that as the idempotency key. Rejected because: - Two orders for the same stock submitted seconds apart are distinct operations, not retries. A body hash would falsely deduplicate them. - The client knows when it is retrying; the server cannot know without a client signal.

C. Pessimistic locking (SELECT FOR UPDATE)

Take a row lock on an idempotency row, hold it across the business logic execution, release on commit. Rejected because: - Holding a row lock for 100-500ms under retry load causes lock-wait timeout cascades. - INSERT ON CONFLICT DO NOTHING RETURNING achieves the same race safety without a held lock.

D. Redis-backed idempotency store

Use Redis SETNX for atomic claim. Rejected because: - Raptor does not currently have a Redis dependency; adding one for idempotency alone is not justified. - Postgres is already present; its unique constraint provides equivalent atomicity. - Redis persistence is optional; a crash before response write would drop the key and allow re-execution — the failure mode is worse than Postgres.

Security / GDPR checklist

• PII collected: None directly. scope_user_id links to users.id; response_body contains order details (symbol, qty, price) which are not PII under GDPR.
• Retention period: TTL per endpoint family (24h or 7d); nightly cleanup job deletes expired rows. Absolute maximum retention: 7 days.
• Deletion on DSR: DELETE FROM idempotency_keys WHERE scope_user_id = :uid included in GDPR erasure sweep (SC-IK-6).
• Audit trail: Replay events logged to audit_log as action='idempotency_replay' with original request hash. Original execution audit rows are not duplicated.
• Stored credentials: None. Auth endpoints excluded from instrumentation. No session tokens, WebAuthn credentials, or API keys in response_body.
• Breach notification path: Standard 72-hour window; table contains no credentials or PII. Severity: LOW.
• Secrets location + rotation: FLAG_IDEMPOTENCY_MIDDLEWARE is a Heroku config var; rotatable without redeploy (flag change takes effect on next request).
• Kill-switch: FLAG_IDEMPOTENCY_MIDDLEWARE=0 degrades all instrumented endpoints to pass-through. Tier 1 endpoints already have MBT_NEW_ORDERS_DISABLED as an independent order-path kill-switch.

Revisit when

• Live trading against a broker is enabled (live idempotency key forwarding design needed).
• Raptor p99 latency budget for order submission drops below 5ms (evaluate Tier 1 promotion for the idempotency middleware).
• Team grows beyond 5 engineers and shared DB access to response_body becomes a concern (evaluate column-level encryption).