Code for Pending-Purgatory Bug Fix

[Capt_Roger]

@Manu @Laurel

I'm going to post here some code solutions for the pending-purgatory bug based on my guesses of what actual platform Literotica is built on. If you need help there are several authors, to my knowledge, who are willing and able...

 

[Capt_Roger]

1. Fix Schema

-- Add index for faster stuck submission queries
CREATE INDEX IF NOT EXISTS idx_submissions_stuck_check
ON submissions(last_action, status, days_pending, submitted_at);

-- Add tracking column for moderation queue state
ALTER TABLE submissions
ADD COLUMN IF NOT EXISTS moderation_flag_reset_at TIMESTAMP NULL,
ADD COLUMN IF NOT EXISTS original_submission_id INT NULL;

 

[Capt_Roger]

2. Backend Patch (PHP, so if you need Python just reach out)

<?php
/**
* Literotica Submission Bug Fix
* Fixes stuck submissions in "Pending Purgatory"
*/

class SubmissionBugFix {
private $db;

public function __construct($database) {
$this->db = $database;
}

/**
* PATCH: Reset moderation flags on resubmission
* Call this BEFORE inserting into moderation queue
*/
public function fixResubmitFlags($storyId, $userId) {
$sql = "
UPDATE submissions
SET
status = 'submitted',
resubmit_count = 0,
status_id = NULL,
rejection_reason = NULL,
rejected_at = NULL,
days_pending = 0,
last_action = 'submitted',
moderation_flag_reset_at = NOW(),
updated_at = NOW()
WHERE story_id = ?
AND user_id = ?
AND status IN ('rejected', 'pending')
";

$stmt = $this->db->prepare($sql);
$stmt->bind_param("ii", $storyId, $userId);
$stmt->execute();

// Ensure entry exists in moderation queue
$queueSql = "
INSERT INTO moderation_queue
(submission_id, story_id, user_id, status, created_at)
SELECT
s.id, s.story_id, s.user_id, 'pending', NOW()
FROM submissions s
WHERE s.story_id = ? AND s.user_id = ? AND s.status = 'submitted'
ON DUPLICATE KEY UPDATE
status = 'pending',
updated_at = NOW()
";

$stmt2 = $this->db->prepare($queueSql);
$stmt2->bind_param("ii", $storyId, $userId);
$stmt2->execute();

return $stmt->affected_rows > 0;
}

/**
* Cleanup job for existing stuck submissions
* Run via cron daily
*/
public function cleanupStuckSubmissions($dryRun = true) {
$sql = "
SELECT s.id, s.story_id, s.user_id, s.submitted_at,
DATEDIFF(NOW(), s.submitted_at) as days_stuck
FROM submissions s
WHERE s.last_action = 'resubmit'
AND s.status = 'pending'
AND s.days_pending > 2
AND s.submitted_at < DATE_SUB(NOW(), INTERVAL 7 DAY)
";

$result = $this->db->query($sql);
$fixed = [];

while ($row = $result->fetch_assoc()) {
$fixed[] = $row;

if (!$dryRun) {
$this->repairStuckSubmission($row['id'], $row['story_id']);
}
}

return $fixed;
}

private function repairStuckSubmission($submissionId, $storyId) {
$fixSql = "
UPDATE submissions
SET status = 'submitted',
resubmit_count = 0,
status_id = NULL,
last_action = 'manual_fix',
moderation_flag_reset_at = NOW()
WHERE id = ?
";

$stmt = $this->db->prepare($fixSql);
$stmt->bind_param("i", $submissionId);
$stmt->execute();

// Add to moderation queue with high priority
$queueSql = "
INSERT INTO moderation_queue
(submission_id, story_id, status, priority, created_at)
VALUES (?, ?, 'pending', 'high', NOW())
ON DUPLICATE KEY UPDATE
status = 'pending',
priority = 'high'
";

$stmt2 = $this->db->prepare($queueSql);
$stmt2->bind_param("ii", $submissionId, $storyId);
$stmt2->execute();
}
}

// Monkey-patch the existing resubmit function
function patchedHandleResubmit($storyId, $userId, $db) {
$fixer = new SubmissionBugFix($db);

// CRITICAL: Reset flags BEFORE normal processing
$fixer->fixResubmitFlags($storyId, $userId);

// Continue with normal submission flow...
return true;
}
?>

 

[Capt_Roger]

3. Cron job for clean up
#!/bin/bash
# Run daily to fix stuck submissions
# /etc/cron.d/literotica-pending-fix

0 2 * * * www-data /usr/bin/php /var/www/literotica/cron/fix-pending-purgatory.php --fix-stuck >> /var/log/lit/pending-fix.log 2>&1

_____________


<?php
// fix-pending-purgatory.php
require_once 'SubmissionBugFix.php';

$db = new mysqli('localhost', 'user', 'pass', 'literotica');
$fixer = new SubmissionBugFix($db);

$dryRun = !in_array('--fix-stuck', $argv);
$stuck = $fixer->cleanupStuckSubmissions($dryRun);

echo date('Y-m-d H:i:s') . " - Found " . count($stuck) . " stuck submissions\n";

if ($dryRun) {
echo "Dry run mode - no changes made. Use --fix-stuck to repair.\n";
} else {
echo "Repaired " . count($stuck) . " stuck submissions\n";
}

foreach ($stuck as $s) {
echo " - Story {$s['story_id']} (User {$s['user_id']}): {$s['days_stuck']} days stuck\n";
}
?>