Replication-Unsafe Schema Design

-- Another dangerous pattern: Unique constraints without region awareness

CREATE TABLE user_sessions (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    token VARCHAR(255) UNIQUE,  -- Will conflict if generated in multiple regions!
    created_at TIMESTAMP DEFAULT NOW(),
    -- No version or region tracking
    CONSTRAINT one_session_per_user UNIQUE(user_id)  -- Breaks during split-brain!
);

-- This will fail during network partition
const createSession = async (userId) => {
  const token = generateToken();
  // Both regions might try to create session for same user
  await db.query(
    'INSERT INTO user_sessions (user_id, token) VALUES ($1, $2)',
    [userId, token]
  );
};

-- Safe: Replication-aware schema design

-- Use UUIDs and include conflict resolution metadata
CREATE TABLE orders (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    user_id UUID NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) NOT NULL,
    version INTEGER DEFAULT 1,
    region VARCHAR(20) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    -- Include region in unique constraints where needed
    INDEX idx_user_orders (user_id, created_at)
);

-- Conflict-aware session management
CREATE TABLE user_sessions (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    user_id UUID NOT NULL,
    token VARCHAR(255) NOT NULL,
    region VARCHAR(20) NOT NULL,
    version INTEGER DEFAULT 1,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    
    -- Region-aware unique constraint
    UNIQUE(user_id, region),
    -- Token includes region prefix to avoid conflicts
    UNIQUE(token),
    INDEX idx_user_sessions (user_id, expires_at)
);

-- Application code that handles replication
const createOrder = async (userId, items, region) => {
  const orderId = uuid.v4();
  const now = new Date().toISOString();
  
  const result = await db.query(`
    INSERT INTO orders (id, user_id, total, status, region, created_at, updated_at)
    VALUES ($1, $2, $3, $4, $5, $6, $6)
    ON CONFLICT (id) DO UPDATE SET
      version = orders.version + 1,
      updated_at = $6
    WHERE orders.updated_at < $6  -- Last-write-wins
    RETURNING *
  `, [orderId, userId, calculateTotal(items), 'pending', region, now]);
  
  return result.rows[0];
};

-- Implement reconciliation for split-brain recovery
CREATE OR REPLACE FUNCTION reconcile_orders(
    primary_region TEXT,
    secondary_region TEXT
) RETURNS TABLE(id UUID, action TEXT) AS $
BEGIN
    RETURN QUERY
    WITH conflicts AS (
        SELECT 
            COALESCE(p.id, s.id) as id,
            p.version as primary_version,
            s.version as secondary_version,
            p.updated_at as primary_updated,
            s.updated_at as secondary_updated
        FROM orders p
        FULL OUTER JOIN orders_replica s ON p.id = s.id
        WHERE p.region = primary_region 
        AND s.region = secondary_region
        AND (p.version != s.version OR p.id IS NULL OR s.id IS NULL)
    )
    SELECT 
        id,
        CASE 
            WHEN primary_version IS NULL THEN 'INSERT_PRIMARY'
            WHEN secondary_version IS NULL THEN 'INSERT_SECONDARY'
            WHEN secondary_updated > primary_updated THEN 'UPDATE_PRIMARY'
            ELSE 'UPDATE_SECONDARY'
        END as action
    FROM conflicts;
END;
$ LANGUAGE plpgsql;