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

Optimistic Concurrency — Version Column Pattern

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

For business records that are edited concurrently (products, customers, tenant settings, pricing rules), use a version column to detect lost updates. Cheaper than SELECT FOR UPDATE for read-heavy entities; safer than last-write-wins.

Schema

Every concurrently-editable table includes:

ALTER TABLE catalog_products
  ADD COLUMN version BIGINT NOT NULL DEFAULT 0;

Application reads include the version:

SELECT id, name, description, price, version
  FROM catalog_products
 WHERE id = $1;

The application returns version to the client (as an If-Match ETag, or in the response body).

Updates require the version to match:

UPDATE catalog_products
   SET name = $name,
       description = $description,
       price = $price,
       version = version + 1,
       updated_at = now()
 WHERE id = $id
   AND version = $expected_version
 RETURNING version;

If the RETURNING is empty, the row was either deleted or modified by another writer. The application returns 409 Conflict and the client decides whether to retry with merged changes.

With trigger-based version bump (alternative)

If you prefer to avoid trusting application code to bump version:

CREATE OR REPLACE FUNCTION bump_version_on_update()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.version <> OLD.version THEN
    -- Optimistic check passed (caller supplied OLD.version+1); allow
    NEW.version := OLD.version + 1;
  ELSE
    -- Caller forgot to bump; do it for them
    NEW.version := OLD.version + 1;
  END IF;
  NEW.updated_at := now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER catalog_products_version_trg
  BEFORE UPDATE ON catalog_products
  FOR EACH ROW EXECUTE FUNCTION bump_version_on_update();

The application still includes WHERE version = $expected_version to detect the conflict. The trigger just guarantees the bump always happens.

HTTP integration (ETags)

Client                                  Server
------                                  ------
GET /products/9182                      SELECT * FROM catalog_products WHERE id = 9182
                                        version = 7
← 200 OK                              ←
  ETag: "7"
  { id: 9182, name: "Widget", version: 7, ... }

[Client edits the product locally]

PUT /products/9182                      UPDATE ... WHERE id = 9182 AND version = 7
If-Match: "7"                           ↓ no row matched
{ name: "Better Widget", ... }          (someone else updated it to version 8)
← 412 Precondition Failed             ←
  { error: "version_conflict", current_version: 8 }

[Client fetches latest, merges, retries]

Concurrent retry pattern (server-side)

For non-user-driven writes (background jobs that update aggregates), retry with exponential backoff up to N times:

def update_with_retry(product_id, mutator, max_attempts=3):
    for attempt in range(max_attempts):
        product = fetch_product(product_id)
        new_state = mutator(product)
        rows = db.execute("""
            UPDATE catalog_products
               SET name = %s, description = %s, version = version + 1
             WHERE id = %s AND version = %s
         RETURNING version
        """, [new_state.name, new_state.description, product_id, product.version])
        if rows:
            return rows[0].version
        time.sleep(0.05 * (2 ** attempt))  # 50ms, 100ms, 200ms
    raise ConflictError(product_id)

When to use this vs. `SELECT FOR UPDATE`

Scenario	Pattern
Inventory reservation (high contention, short tx)	`SELECT FOR UPDATE`
Editing a product (low contention, long-running edit UI)	Optimistic + `version`
Settling a payment (financial, must serialize)	`SELECT FOR UPDATE`
Editing tenant settings (rare conflict, long edit session)	Optimistic + `version`
Bulk import that may overwrite recent edits	Optimistic + manual conflict resolution

Common mistakes (don't)

Forgetting the WHERE version = $expected. The version column is decoration without it.
Using a timestamp instead of an integer. Clock skew on multi-writer setups creates false negatives.
Returning 200 OK on a no-op UPDATE. A zero-row RETURNING means someone won the race; return 409 so the client can react.
Bumping version manually inline without RETURNING. Without RETURNING, the application doesn't know the new version to send back.
Combining optimistic + pessimistic on the same row. Pick one strategy per write path. Mixing them creates contention puzzles no one can debug.