You're Probably Building Kanban Boards Wrong (Here's How to Fix It)

Update (December 27, 2025): After using the LexoRank approach described in this article in production, Flowforge v3.x has evolved to a simpler decimal-based positioning system. While lexicographic fractional indexing worked well, we found that BCMath decimals offer the same benefits with less complexity. The core concurrency patterns (pessimistic locking, defense-in-depth) remain unchanged and are still the most important parts of this architecture.

Read the Evolution: From LexoRank to Decimals section at the end to see what changed and why.

The Problem: When Two Users Drag the Same Card

Imagine this scenario: Alice and Bob, both project managers, are working on the same Kanban board. At exactly 2:47:32 PM, they both drag task #347 from "In Progress" to "Done." What happens?

In a naive implementation:

Alice's browser reads the current position data
Bob's browser reads the same position data (simultaneously)
Alice's request calculates a new position and saves it
Bob's request calculates a position using stale data and overwrites Alice's change

Result: Position conflicts, data loss, or worse—position inversions where cards appear out of order.

This isn't a theoretical problem. In production Kanban boards with multiple concurrent users, this issue occurs frequently.

Why Position Management is Hard

The Integer Trap

Your first instinct might be: "Just use integers! Card 1, Card 2, Card 3..."

// This doesn't work
'position' => 1  // What if I need to insert between 1 and 2?

When you need to insert a card between positions 1 and 2, you have two bad options:

Renumber everything (position 2 becomes 3, 3 becomes 4, etc.) - Expensive!
Use decimals (1.5) - Eventually, you run out of precision

The Decimal Precision Problem

// This breaks down eventually
'position' => 1.5   // Between 1 and 2
'position' => 1.25  // Between 1 and 1.5
'position' => 1.125 // Between 1 and 1.25
'position' => 1.0625 // ...you see where this is going

After enough insertions, you hit floating-point precision limits and can't insert anymore without renumbering.

The Solution: Lexicographic Fractional Indexing

Instead of numbers, Flowforge uses strings that are compared lexicographically (alphabetically):

// This scales infinitely
'position' => 'U'    // First card (midpoint of character range)
'position' => 'V'    // After 'U'
'position' => 'UU'   // Between 'U' and 'V'
'position' => 'UB'   // Between 'U' and 'UU'

Important Note: This is lexicographic fractional indexing for a single-server application with pessimistic locking. It's NOT the same as CRDT "position strings" (like Matthew Weidner's work), which include random IDs for distributed systems. Flowforge's approach is simpler and relies on database transactions for conflict resolution.

How It Works

The Rank service generates positions using a character range from '0' to 'z' (ASCII 48-122):

public const MIN_CHAR = '0';
public const MAX_CHAR = 'z';
public const MAX_RANK_LEN = 1024; // MySQL sorts on first 1024 chars

Key Operations:

Empty sequence: Start with the middle character

Rank::forEmptySequence()  // Returns 'U' (ASCII 85, midpoint of '0' and 'z')

After a position: Increment the last character

Rank::after(Rank::fromString('U'))  // Returns 'V'
Rank::after(Rank::fromString('y'))  // Returns 'y1'
Rank::after(Rank::fromString('z'))  // Returns 'z1' (extends!)

Before a position: Decrement the last character

Rank::before(Rank::fromString('V'))  // Returns 'U'
Rank::before(Rank::fromString('1'))  // Returns '0y' (extends!)

Between two positions: Find the midpoint character

Rank::betweenRanks(
    Rank::fromString('U'),
    Rank::fromString('V')
)  // Returns 'UU' (midpoint between 'U' and 'V')

The Algorithm

Here's the actual implementation of finding a position between two ranks:

public static function betweenRanks(self $prevRank, self $nextRank): self
{
    // Ensure prevRank < nextRank
    if (strcmp($prevRank->get(), $nextRank->get()) >= 0) {
        throw PrevGreaterThanOrEquals::betweenRanks($prevRank, $nextRank);
    }

    $rank = '';
    $i = 0;

    // Iterate through each character position
    while ($i <= self::MAX_RANK_LEN) {
        $prevChar = $prevRank->getChar($i, self::MIN_CHAR);
        $nextChar = $nextRank->getChar($i, self::MAX_CHAR);
        $i++;

        // Find the midpoint character
        $midChar = self::mid($prevChar, $nextChar);

        // If midpoint equals boundary, append and continue
        if (in_array($midChar, [$prevChar, $nextChar])) {
            $rank .= $prevChar;
            continue;
        }

        $rank .= $midChar;
        break;
    }

    return self::fromString($rank);
}

private static function mid(string $prev, string $next): string
{
    return chr((int) ((ord($prev) + ord($next)) / 2));
}

Real-World Example

Let's trace inserting 5 cards:

1. Empty board:        'U'
2. Add after:          'U', 'V'
3. Add after:          'U', 'V', 'W'
4. Insert between 1&2: 'U', 'UU', 'V', 'W'
5. Insert between 2&3: 'U', 'UU', 'Ug', 'V', 'W'

String comparison (strcmp) naturally sorts these lexicographically:

'U' < 'UU' < 'Ug' < 'V' < 'W'

Why this works: When comparing strings character by character:

'U' has length 1
'UU' starts with 'U', then has another 'U', making it greater
'Ug' starts with 'U' then has 'g' (ASCII 103 > ASCII 85), making it greater than 'UU'
'V' (ASCII 86) is greater than any string starting with 'U' (ASCII 85)

Performance Characteristics

From our actual test suite (149 tests, 4,140+ assertions):

Position String Length Growth

Cards	Avg Length	Max Length
25	1.0	1
50	1.26	2
100	1.63	2
200	2.35	4

Takeaway: Even with 200 cards, the average position string is only 2.35 characters.

Position Generation Speed

Note: Results may vary based on hardware and PHP configuration.

Operation	Avg Time
Empty sequence	0.62μs
After position	0.68μs
Between two	1.85μs

Takeaway: Sub-microsecond performance for position generation.

The Concurrency Challenge

Fast position generation is great, but what about concurrent updates?

The Race Condition

Time    Alice's Request              Bob's Request
----    ---------------              -------------
t=0     Read card positions
        Cards: A(m), B(n), C(o)

t=1                                  Read card positions
                                     Cards: A(m), B(n), C(o)

t=2     Calculate position
        between A and B: 'mn'

t=3                                  Calculate position
                                     between A and B: 'mn' (same!)

t=4     UPDATE card SET
        position='mn' WHERE id=123

t=5                                  UPDATE card SET
                                     position='mn' WHERE id=456

Result: Two cards with position 'mn' - COLLISION!

Phase 1: Pessimistic Locking

Flowforge prevents this using pessimistic locking with Laravel's lockForUpdate():

protected function calculateAndUpdatePosition(
    Model $card,
    string $targetColumnId,
    ?string $afterCardId,
    ?string $beforeCardId
): string {
    $newPosition = null;

    DB::transaction(function () use ($card, $targetColumnId, $afterCardId, $beforeCardId, &$newPosition) {
        $board = $this->getBoard();
        $query = $board->getQuery();

        // LOCK reference cards for reading to prevent stale data
        $afterCard = $afterCardId
            ? (clone $query)->where('id', $afterCardId)->lockForUpdate()->first()
            : null;

        $beforeCard = $beforeCardId
            ? (clone $query)->where('id', $beforeCardId)->lockForUpdate()->first()
            : null;

        // Calculate position INSIDE transaction with locked data
        $newPosition = $this->calculatePositionBetweenLockedCards(
            $afterCard,
            $beforeCard,
            $targetColumnId
        );

        // Update card position atomically
        $card->update([
            $board->getColumnIdentifierAttribute() => $columnValue,
            $board->getPositionIdentifierAttribute() => $newPosition,
        ]);
    });

    return $newPosition;
}

How `lockForUpdate()` Works

Starts a database transaction
Locks the rows being read (afterCard, beforeCard)
Other transactions must wait until the lock is released
Calculates position using fresh, locked data
Updates atomically within the same transaction
Releases locks when the transaction commits

The Timeline (Fixed)

Time    Alice's Request                     Bob's Request
----    ---------------                     -------------
t=0     BEGIN TRANSACTION
        SELECT * FROM cards
        WHERE id IN (1,2)
        FOR UPDATE
        LOCKS cards 1 & 2

t=1                                         BEGIN TRANSACTION
                                            SELECT * FROM cards
                                            WHERE id IN (1,2)
                                            FOR UPDATE
                                            WAITING for Alice's lock...

t=2     Calculate: 'mn'
        UPDATE card SET position='mn'
        COMMIT
        UNLOCKS cards 1 & 2

t=3                                         ACQUIRES lock
                                            Reads FRESH data (sees 'mn')
                                            Calculate: 'mn8' (different!)
                                            UPDATE card SET position='mn8'
                                            COMMIT

Result: Positions are 'mn' and 'mn8' - NO COLLISION!

Key Insight: Bob's transaction waits for Alice's to complete, then reads the fresh data, including Alice's changes.

Phase 2: Defense-in-Depth (Optional)

While pessimistic locking prevents most conflicts, we added an optional second layer for high-traffic scenarios:

Database-Level Unique Constraint

Schema::table('tasks', function (Blueprint $table) {
    $table->unique(['status', 'order_position'], 'unique_position_per_column');
});

This ensures, at the database level, that no two cards in the same column can have the same position.

Automatic Retry with Exponential Backoff

If a constraint violation occurs (extremely rare with locking), Flowforge automatically retries:

protected function calculateAndUpdatePositionWithRetry(
    Model $card,
    string $targetColumnId,
    ?string $afterCardId,
    ?string $beforeCardId,
    int $maxAttempts = 3
): string {
    $baseDelay = 50; // milliseconds
    $lastException = null;

    for ($attempt = 1; $attempt <= $maxAttempts; $attempt++) {
        try {
            return $this->calculateAndUpdatePosition(
                $card,
                $targetColumnId,
                $afterCardId,
                $beforeCardId
            );
        } catch (QueryException $e) {
            // Check if this is a unique constraint violation
            if (! $this->isDuplicatePositionError($e)) {
                throw $e; // Not a duplicate, rethrow
            }

            $lastException = $e;

            // Log the conflict for monitoring
            Log::info('Position conflict detected, retrying', [
                'card_id' => $card->id,
                'target_column' => $targetColumnId,
                'attempt' => $attempt,
                'max_attempts' => $maxAttempts,
            ]);

            // Max retries reached?
            if ($attempt >= $maxAttempts) {
                throw new MaxRetriesExceededException(
                    "Failed to move card after {$maxAttempts} attempts",
                    previous: $e
                );
            }

            // Exponential backoff: 50ms, 100ms, 200ms
            $delay = $baseDelay * pow(2, $attempt - 1);
            usleep($delay * 1000);

            // Refresh reference cards before retry
            continue;
        }
    }

    throw $lastException ?? new \RuntimeException('Unexpected retry loop exit');
}

Cross-Database Constraint Detection

protected function isDuplicatePositionError(QueryException $e): bool
{
    $errorCode = $e->errorInfo[1] ?? null;

    // SQLite: SQLITE_CONSTRAINT (19)
    // MySQL: ER_DUP_ENTRY (1062)
    // PostgreSQL: unique_violation (23505)

    return in_array($errorCode, [19, 1062, 23505]) ||
           str_contains($e->getMessage(), 'unique_position_per_column') ||
           str_contains($e->getMessage(), 'UNIQUE constraint failed');
}

Database Collation: The Hidden Gotcha

For lexicographic ordering to work correctly, you must use a binary collation:

Blueprint::macro('flowforgePositionColumn', function (string $name = 'position') {
    $driver = DB::connection()->getDriverName();
    $column = $this->string($name)->nullable();

    return match ($driver) {
        'pgsql' => $column->collation('C'),          // PostgreSQL binary
        'mysql' => $column->collation('utf8mb4_bin'),// MySQL binary
        'sqlsrv' => $column->collation('Latin1_General_BIN2'), // SQL Server
        default => $column,  // SQLite: BINARY by default
    };
});

Why this matters: Default collations (like utf8mb4_unicode_ci) treat uppercase/lowercase as equal:

'M' == 'm' with unicode collation
'M' < 'm' with binary collation

Binary collation ensures strcmp() In the PHP matches database ORDER BY behavior.

Architecture Decisions

Why Pessimistic Instead of Optimistic Locking?

Optimistic locking checks the version on update:

// Optimistic: Often fails in high concurrency
UPDATE cards SET position='mn', version=2 WHERE id=123 AND version=1
// Returns 0 rows if someone else updated (version is now 2)

Pessimistic locking prevents conflicts upfront:

// Pessimistic: Waits instead of failing
SELECT * FROM cards WHERE id IN (1,2) FOR UPDATE
// Other transactions wait their turn

Trade-offs:

Pessimistic: Slower (waiting), but guaranteed success
Optimistic: Faster (no waiting), but frequent retries under load

For Kanban boards, pessimistic wins because:

Drag operations are user-initiated (occasional, not constant)
Users expect immediate success (not retry errors)
Lock duration is milliseconds (position calc is fast)

Why Make the Unique Constraint Optional?

Flowforge is a package, not an application. We can't force users to add constraints to their existing tables. Instead:

Phase 1 (pessimistic locking) works out-of-the-box
Phase 2 (unique constraint) is opt-in for extra safety
Retry logic is always present, activates if a constraint is added

This gives users flexibility:

Small teams: Phase 1 is sufficient
Large teams: Add Phase 2 for defense-in-depth

When Things Go Wrong: Repair Command

If positions somehow get corrupted (manual DB edits, bugs, etc.), Flowforge includes a repair command:

php artisan flowforge:repair-positions Task --column=status

This scans for:

Duplicate positions within the same column
Position inversions (cards out of order)
Null positions

Then offers strategies to fix:

Regenerate all: Fresh sequential positions
Fill gaps: Keep existing positions, only fix gaps
Reorder: Fix inversions while preserving relative order

Real-World Testing

Our test suite includes stress tests for real-world scenarios:

Concurrent Operations Stress Test

it('handles rapid successive moves of same card (20+ times)', function () {
    $targetCard = $todoCards->first();

    // Perform 20 rapid successive moves
    for ($i = 0; $i < 20; $i++) {
        $randomStatus = $statuses[array_rand($statuses)];
        $this->board->call('moveCard', (string) $targetCard->id, $randomStatus);

        // Verify card has valid position after each move
        expect($targetCard->refresh()->order_position)
            ->not()->toBeNull()
            ->toBeString();
    }

    // Verify positions are properly sorted in each column
    foreach ($statuses as $status) {
        $positions = Task::where('status', $status)
            ->orderBy('order_position')
            ->pluck('order_position')
            ->toArray();

        // Check positions are in ascending order
        for ($i = 0; $i < count($positions) - 1; $i++) {
            expect(strcmp($positions[$i], $positions[$i + 1]))->toBeLessThan(0);
        }
    }
});

Character Space Exhaustion Test

it('stresses 100 sequential insertions at bottom of column', function () {
    $cards = collect();

    for ($i = 1; $i <= 100; $i++) {
        $rank = $i === 1
            ? Rank::forEmptySequence()
            : Rank::after(Rank::fromString($cards->last()->order_position));

        $card = Task::factory()->create([
            'status' => 'todo',
            'order_position' => $rank->get(),
        ]);

        $cards->push($card);
    }

    // Verify all 100 positions are unique
    $uniquePositions = $cards->pluck('order_position')->unique()->count();
    expect($uniquePositions)->toBe(100);

    // Verify max length is reasonable
    $maxLength = $cards->pluck('order_position')->map(fn($p) => strlen($p))->max();
    expect($maxLength)->toBeLessThan(10);
});

Results

~150 tests passing
~4,140+ assertions
Zero memory leaks
Sub-microsecond position generation
No position inversions under any scenario

Lessons Learned

1. Start Simple, Add Layers

We didn't build the full defence-in-depth system on day one:

1: Integer positions (renumber on insert) - Simple but slow

2: Lexicographic positions (Rank algorithm) - Fast but vulnerable to races

3: + Pessimistic locking - Production-ready for most teams

4: + Optional unique constraint - Extra safety for high traffic

Each layer adds value without breaking the previous one.

2. Performance Matters

Position calculation happens on every drag operation. If it's slow, the UI feels sluggish. Optimizations:

Immutable Rank objects (no mutation overhead)
Minimal string operations (substr, chr, ord)
Early exit in betweenRanks() (stops at first valid midpoint)
String length capped at 1024 (MySQL sort limit)

Result: Sub-microsecond position generation.

3. Test Real-World Scenarios

Unit tests aren't enough. We needed:

Stress tests: 100+ sequential insertions
Concurrency tests: Simulated parallel operations
Exhaustion tests: Finding breaking points
Edge cases: What happens at MIN_CHAR and MAX_CHAR?

These are bugs that unit tests missed.

4. Observability is Critical

Logging position conflicts helps catch issues early:

Log::info('Position conflict detected, retrying', [
    'card_id' => $card->id,
    'target_column' => $targetColumnId,
    'attempt' => $attempt,
    'max_attempts' => $maxAttempts,
]);

In production, you can monitor:

Conflict frequency: Increasing? Time to add a unique constraint.
Retry patterns: Which columns see the most conflicts?
Max attempts reached: Indicates serious concurrency issues.

5. Package Design Requires Flexibility

As a package (not an app), we can't assume:

Users will run our migrations
Users have control over the table schema
Users want our exact conventions

Instead, we:

Provide macros for position columns
Make constraints optional
Detect constraint violations automatically
Work with any column names (configurable)

Evolution: From LexoRank to Decimals

What Changed in Flowforge v3.x

After running the LexoRank implementation in production for several months, we migrated to a decimal-based positioning system using PHP's BCMath extension. Here's what we learned and why we simplified.

The New Approach: DECIMAL(20,10)

Instead of string positions like 'U', 'UU', 'Ug', we now use decimal numbers:

// Flowforge v3.x - Decimal positions
'position' => 65535.0000000000   // First card (default gap)
'position' => 131070.0000000000  // After first card
'position' => 98302.5000000000   // Between first and second
'position' => 81918.7500000000   // Between first and middle

Database column definition:

$table->decimal('position', 20, 10)->nullable();
// 10 integer digits + 10 decimal places = ~33 bisections before MIN_GAP

Why We Switched

1. Simpler Implementation

LexoRank required:

Character range management (MIN_CHAR, MAX_CHAR)
Custom strcmp() logic
Binary collation configuration (utf8mb4_bin, C, etc.)
Character-by-character midpoint calculations

Decimal positioning uses:

Native numeric comparison (database handles it)
Simple midpoint: ($a + $b) / 2
No collation concerns (numbers are numbers)
Built-in BCMath precision

// LexoRank: Complex character arithmetic
private static function mid(string $prev, string $next): string
{
    return chr((int) ((ord($prev) + ord($next)) / 2));
}

// Decimal: Simple BCMath addition
public static function between(string $after, string $before): string
{
    $sum = bcadd($after, $before, 10);
    return bcdiv($sum, '2', 10);
}

2. No Character Space Exhaustion

LexoRank could theoretically run out of characters between positions (though rare). With DECIMAL(20,10):

10 decimal places = 10 billion subdivisions per integer
Effective capacity: ~33 bisections before hitting MIN_GAP (0.0001)
When gap gets too small, auto-rebalancing spreads positions out

3. Cryptographic Jitter for Concurrency

Instead of relying solely on pessimistic locking + unique constraints, we added jitter to the position calculation:

// Each concurrent insert gets a UNIQUE position near the midpoint
public static function between(string $after, string $before): string
{
    $midpoint = bcdiv(bcadd($after, $before, 10), '2', 10);
    $gap = bcsub($before, $after, 10);

    // Add ±5% jitter using cryptographically secure randomness
    $jitterRange = bcmul($gap, '0.1', 10);
    $halfJitter = bcdiv($jitterRange, '2', 10);
    $jitter = self::generateJitter($halfJitter); // Uses random_bytes()

    return bcadd($midpoint, $jitter, 10);
}

This means:

Alice and Bob insert "between Card A and B" simultaneously
Alice gets: 98302.4872341
Bob gets: 98302.5127659
No collision, no retry needed (jitter creates natural separation)

Pessimistic locking still prevents data races, but jitter adds an extra safety layer.

4. Native Database Ordering

-- LexoRank: Required binary collation
ORDER BY position COLLATE utf8mb4_bin

-- Decimal: Just works everywhere
ORDER BY position

No collation configuration, no cross-database compatibility concerns.

What Stayed the Same (The Important Stuff!)

Pessimistic Locking - Still the Core

DB::transaction(function () use (...) {
    $afterCard = $afterCardId
        ? $query->where('id', $afterCardId)->lockForUpdate()->first()
        : null;

    $beforeCard = $beforeCardId
        ? $query->where('id', $beforeCardId)->lockForUpdate()->first()
        : null;

    $newPosition = DecimalPosition::calculate(
        $afterCard?->position,
        $beforeCard?->position
    );

    $card->update(['position' => $newPosition]);
});

This is still the hero that prevents race conditions.

Defense-in-Depth Philosophy

Phase 1: Pessimistic locking (out-of-the-box)
Phase 2: Jitter for additional collision prevention (built-in)
Phase 3: Optional unique constraints (user decides)
Phase 4: Repair commands for manual corruption

Auto-Rebalancing

When positions get too close (gap < 0.0001), the rebalancer spreads them out:

php artisan flowforge:rebalance-positions --model=Task --column=status

Performance Comparison

Metric	LexoRank	Decimal (BCMath)
Avg position calculation	1.85μs	0.42μs
Empty sequence	0.62μs	0.18μs
Database ORDER BY	Requires collation	Native numeric
Character/precision exhaustion	Possible (rare)	Effectively impossible
Position string length (200 cards)	~2.35 chars	Fixed: 22 chars
Cross-database compat	Collation-dependent	Works everywhere

When to Use Each Approach

Use LexoRank (string-based) when:

You need true CRDT behavior for distributed systems (use proper CRDT libraries like Matthew Weidner's position-strings)
You want minimal storage (2-3 chars vs 22 chars)
You enjoy the elegance of lexicographic ordering

Use Decimal Positioning (Flowforge v3.x) when:

You have a centralized database (single source of truth)
You want simplicity over cleverness
You want native database ordering without collation concerns
You're building a Laravel application (BCMath is commonly available)

Key Takeaway

Simplicity won. LexoRank was a fascinating deep dive into lexicographic ordering, and it worked perfectly in production. But when we asked, "What problem does this complexity solve that decimals don't?" the answer was: "Nothing significant for our use case."

The real engineering lesson? Pessimistic locking is the 80% solution. Jitter is the 15%. Unique constraints are the last 5%. The position calculation algorithm (LexoRank vs decimal) is just a detail—as long as it never throws exceptions and scales well.

For Flowforge's use case (centralized database, occasional drag operations, Laravel ecosystem), DECIMAL(20,10) with BCMath hit the sweet spot of simplicity, performance, and reliability.

Try Flowforge

composer require relaticle/flowforge

Full documentation: https://relaticle.github.io/flowforge/

Found this valuable? Share it with your team and star Flowforge on GitHub!