g14wxz/commerce-database-architect

Database architecture skills, docs, and rules for high-demand multi-tenant commerce platforms (PostgreSQL source of truth, Neo4j as derived GraphRAG projection, transactional outbox, RLS-based tenant isolation). Includes live schema introspection workflow via Postgres MCP.

Quality

97%

Does it follow best practices?

Impact

Pending

No eval scenarios have been run

Securityby

Passed

No known issues

Idempotency Keys

Name: g14wxz/commerce-database-architect
Rating: 97.27272727272728 (1 reviews)
Author: g14wxz

Every external-facing write endpoint accepts an Idempotency-Key header and uses this table to ensure that retries return the original response instead of re-executing the side effect.

Table

CREATE TABLE idempotency_keys (
  tenant_id     BIGINT NOT NULL,
  scope         TEXT   NOT NULL,                  -- 'order.create','payment.capture','refund.create'
  key           TEXT   NOT NULL,                  -- the client-supplied key
  request_hash  TEXT   NOT NULL,                  -- canonical hash of request body
  response      JSONB,                            -- cached response body
  status_code   SMALLINT,                         -- cached HTTP status
  state         TEXT   NOT NULL DEFAULT 'pending',  -- 'pending' | 'completed' | 'failed'
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  completed_at  TIMESTAMPTZ,
  expires_at    TIMESTAMPTZ NOT NULL DEFAULT now() + INTERVAL '24 hours',
  PRIMARY KEY (tenant_id, scope, key)
);

CREATE INDEX idempotency_keys_expiration_idx
  ON idempotency_keys (expires_at);

Tenant-scoped: keys from one tenant cannot collide with another tenant's keys.

Middleware contract

1. Extract Idempotency-Key header. If missing on a financial endpoint, 400.
2. Compute SHA-256 of canonicalized request body (sorted keys, no whitespace).
3. INSERT INTO idempotency_keys (tenant, scope, key, request_hash, state)
   VALUES (..., 'pending') ON CONFLICT DO NOTHING.
4. If insert succeeded → first request. Execute handler.
5. If insert conflicted → SELECT the existing row.
   - state='completed' AND request_hash matches → return cached response
   - state='completed' AND request_hash differs → 409 Conflict
   - state='pending' → 425 Too Early (or wait + retry)
   - state='failed' → return cached response (failures are also idempotent)
6. After handler completes, UPDATE state, response, status_code, completed_at.

Canonical request hash

Hash should be deterministic across clients:

import json, hashlib
def canonical_hash(body: dict) -> str:
    canonical = json.dumps(body, sort_keys=True, separators=(',', ':'))
    return hashlib.sha256(canonical.encode()).hexdigest()

For multipart or file uploads, hash file contents separately and include their hashes in the canonical body.

Expiration cleanup

Daily job:

DELETE FROM idempotency_keys
 WHERE expires_at < now()
 LIMIT 100000;

Run in batches to avoid long locks. Repeat until no rows remain.

Worked flow: order creation

Client                                  Server
------                                  ------
POST /orders                            INSERT idempotency_keys (state='pending')
Idempotency-Key: idem_abc123           ↓ first time
                                        Handler executes (creates order, reserves inventory, writes outbox)
                                        UPDATE idempotency_keys SET state='completed', response={...}, status_code=201
← 201 Created { order_id: 71001 }     ←

[Network blip — client retries]

POST /orders                            SELECT idempotency_keys WHERE key='idem_abc123'
Idempotency-Key: idem_abc123           ↓ found, state='completed', hash matches
                                        Return cached response
← 201 Created { order_id: 71001 }     ←

The order is created once, not twice. Inventory is reserved once. Outbox event is published once.

Domain event consumer idempotency

Different concept — same goal. Consumers track the highest outbox_messages.id they've processed, in consumer_offsets:

CREATE TABLE consumer_offsets (
  consumer_name        TEXT NOT NULL,
  partition_key        TEXT NOT NULL DEFAULT '',
  last_processed_id    BIGINT NOT NULL,
  last_processed_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (consumer_name, partition_key)
);

Consumer reads:

SELECT m.*
  FROM outbox_messages m
  JOIN consumer_offsets c
    ON c.consumer_name = $name
   AND c.partition_key = $partition
 WHERE m.id > c.last_processed_id
   AND m.processed_at IS NOT NULL
 ORDER BY m.id
 LIMIT 100;

After processing, advance the offset in the same transaction as the consumer's state change:

BEGIN;
-- consumer's domain logic
UPDATE consumer_offsets
   SET last_processed_id = $highest_id_in_batch,
       last_processed_at = now()
 WHERE consumer_name = $name AND partition_key = $partition;
COMMIT;

Re-running the consumer after a crash re-reads the same batch (no rows lost) but the offset advance ensures no double-processing on the next batch.

Common mistakes (don't)

Treating Idempotency-Key as optional on financial endpoints. Network retries will create duplicate orders/payments. Reject the request if missing.
Not hashing the request body. A client reusing a key with a different body should get 409, not the cached response.
Not expiring keys. The table grows forever and key cardinality becomes unmanageable.
Letting one tenant's keys collide with another's. Always scope by tenant.
Marking failed requests "succeeded" when retried. Failures are idempotent too — return the same failure on retry, don't re-execute.

g14wxz/commerce-database-architect