🗄️ SQL PACT DIFFICULTY: Adept Behavioral

The Command of the Deferred Invocation

Encapsulating operations as structured row data for later or asynchronous execution.

✧ GANG OF FOUR LINEAGE & ARCANUM ✧
GOF DESIGN PATTERN: Command
ARCANUM SCHOOL: Conjuration // Delayed Casting
SCRIBE / ARCHON: Joshua Edward McLaughlin Cox
CANONICAL REGISTRATION:
// SQL PATTERN //
CREATE TYPE command_status AS ENUM ('PENDING', 'PROCESSING', 'COMPLETED', 'FAILED');

-- The Command Queue
CREATE TABLE invocation_commands (
    command_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    target_entity VARCHAR(100) NOT NULL,
    spell_payload JSONB NOT NULL,
    status command_status DEFAULT 'PENDING',
    created_at TIMESTAMP DEFAULT NOW()
);

-- Enqueueing a Command
INSERT INTO invocation_commands (target_entity, spell_payload)
VALUES (
    'NEXUS_CORE', 
    '{"action": "PURGE", "intensity": 9000}'::jsonb
);

-- Worker retrieving the next Command (FOR UPDATE SKIP LOCKED)
SELECT command_id, target_entity, spell_payload
FROM invocation_commands
WHERE status = 'PENDING'
ORDER BY created_at ASC
LIMIT 1
FOR UPDATE SKIP LOCKED;

The Command of the Deferred Invocation

Not all spells should be cast immediately. Some require vast computational rituals and must be deferred. The Command pattern translates actions into state, storing the intent within a Job Queue table.

By encapsulating the target and the spell payload (conveniently stored as JSONB), we decouple the invocation from the execution. Background daemon mages can later pluck these commands from the ether utilizing FOR UPDATE SKIP LOCKED to prevent catastrophic concurrency collisions during processing.

Runic Tags: #queue #command #behavioral