ABSTRACT
This document provides the essential SQL commands for managing the Candidate Register database on Cloudflare D1. It covers how to soft-delete candidates (move to trash), recover them, and manage visa status flags.
🗑️ Trash Management (Soft Delete)
We use a “soft delete” system where candidates are marked as deleted but not immediately removed from the database. This allows for recovery if needed.
Move a Candidate to Trash
To hide a candidate from the dashboard and mark them as deleted:
UPDATE candidates
SET deleted_at = datetime('now')
WHERE id = 116;Replace 116 with the actual Candidate ID.
Recover a Candidate from Trash
To restore a candidate so they appear in the dashboard again:
UPDATE candidates
SET deleted_at = NULL
WHERE id = 116;View All Active Candidates
To see a list of all candidates who are NOT in the trash:
SELECT * FROM candidates
WHERE deleted_at IS NULL
ORDER BY created_at DESC;View Trashed Candidates
To see only the candidates currently in the trash:
SELECT * FROM candidates
WHERE deleted_at IS NOT NULL
ORDER BY deleted_at DESC;Permanently Delete (Irreversible)
To completely remove a candidate from the database forever:
DELETE FROM candidates
WHERE id = 116;CAUTION
This action cannot be undone. Use with extreme care.
🛂 Visa Status Management
Candidates can indicate if they need visa assistance. This affects their visibility in certain dashboard views.
Mark as “Needs Visa Assistance”
If a candidate stated they don’t need help but actually does:
UPDATE candidates
SET visa_status = 'need_help'
WHERE id = 116;Mark as “No Visa Assistance Needed”
If a candidate requested help but actually doesn’t need it:
UPDATE candidates
SET visa_status = 'No, I have an EU passport / I don''t need a visa'
WHERE id = 116;🔍 General Queries
Check Candidate Details
To view all information for a specific candidate before making changes:
SELECT * FROM candidates
WHERE id = 116;Count Total Candidates
To see the total number of applications received:
SELECT COUNT(*) as total_candidates
FROM candidates;