Foreign Key ON DELETE Action Fix - verified_by Column

Overview

Fixed the verified_by foreign key constraint in the movers table to include ON DELETE SET NULL, preventing foreign key violations when an admin user who verified a mover is deleted from the system.

Problem

Original Definition (Line 146):

verified_by UUID REFERENCES auth.users(id),

Issues:

❌ No ON DELETE action - Default behavior is NO ACTION, which prevents deletion

❌ FK violation on admin deletion - Deleting an admin user who verified movers would fail with:

ERROR: update or delete on table "users" violates foreign key constraint
DETAIL: Key (id)=(xxx) is still referenced from table "movers"

❌ Data integrity risk - Cannot remove admin users without first updating all verified_by references
❌ Operational burden - Requires manual cleanup before admin account deletion

Solution

Updated Definition (Line 146):

verified_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,

Why ON DELETE SET NULL:

✅ Preserves verification history - The mover remains verified (verified_at timestamp preserved)
✅ Allows admin deletion - Admins can be removed without breaking FK constraints
✅ Historical accuracy - We know the mover was verified, just not by whom anymore
✅ Audit-friendly - Verification timestamp and notes remain intact
✅ Nullable by default - UUID columns are nullable unless explicitly marked NOT NULL

Verification Status

The verified_by column is already nullable (no NOT NULL constraint), which is required for ON DELETE SET NULL to work correctly.

Column characteristics:

Type: UUID
Nullable: ✅ Yes (implicit - no NOT NULL constraint)
Foreign Key: auth.users(id)
ON DELETE: SET NULL (now added)
Default: NULL

Behavior Examples

Scenario 1: Admin Verifies a Mover

-- Admin (user_id: admin-123) verifies a mover
UPDATE movers
SET 
  verification_status = 'verified',
  verified_at = NOW(),
  verified_by = 'admin-123'
WHERE id = 'mover-456';

Result:

verification_status: 'verified'
verified_at: '2025-10-08 10:30:00'
verified_by: 'admin-123'

Scenario 2: Admin Account is Deleted (BEFORE Fix)

-- Attempt to delete admin
DELETE FROM auth.users WHERE id = 'admin-123';

Result (WITHOUT ON DELETE SET NULL):

❌ ERROR: update or delete on table "users" violates foreign key constraint
   "movers_verified_by_fkey" on table "movers"
DETAIL: Key (id)=(admin-123) is still referenced from table "movers".

Scenario 3: Admin Account is Deleted (AFTER Fix)

-- Delete admin account
DELETE FROM auth.users WHERE id = 'admin-123';

Result (WITH ON DELETE SET NULL):

✅ DELETE successful

-- Mover record automatically updated:
verification_status: 'verified'  -- ✅ Preserved
verified_at: '2025-10-08 10:30:00'  -- ✅ Preserved
verified_by: NULL  -- ✅ Set to NULL (was 'admin-123')

Key Points:

✅ Verification status remains "verified"
✅ Verification timestamp preserved
✅ Only the admin reference is nullified
✅ No foreign key violation
✅ Data integrity maintained

Alternative ON DELETE Actions (Not Used)

1. ON DELETE CASCADE

verified_by UUID REFERENCES auth.users(id) ON DELETE CASCADE

Why NOT CASCADE:

❌ Would delete the entire mover record
❌ Loses all mover data and history
❌ Breaks bookings and payments
❌ Catastrophic data loss

Use case: Only when child records should be deleted with parent (not applicable here)

2. ON DELETE RESTRICT / NO ACTION

verified_by UUID REFERENCES auth.users(id) ON DELETE RESTRICT
-- or
verified_by UUID REFERENCES auth.users(id)  -- Default is NO ACTION

Why NOT RESTRICT:

❌ Prevents admin deletion
❌ Requires manual cleanup
❌ Operational overhead
❌ Blocks account management

Use case: When the relationship is critical and parent shouldn't be deletable (not applicable here)

3. ON DELETE SET DEFAULT

verified_by UUID REFERENCES auth.users(id) ON DELETE SET DEFAULT DEFAULT 'system-admin-id'

Why NOT SET DEFAULT:

❌ Requires a default value
❌ False attribution (implies different admin verified)
❌ Misleading audit trail
❌ Complexity in maintaining system admin account

Use case: When there's a meaningful default fallback (not applicable here)

Database Schema Impact

Movers Table Structure

CREATE TABLE public.movers (
  -- ... other columns ...
  
  -- Verification & Documents
  verification_status verification_status_enum DEFAULT 'pending',
  verification_notes TEXT,
  verified_at TIMESTAMPTZ,
  verified_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,  -- ✅ Fixed
  
  -- ... other columns ...
);

Related Columns Interaction

Column	Type	Nullable	Purpose
`verification_status`	`verification_status_enum`	No	Current verification state
`verified_at`	`TIMESTAMPTZ`	Yes	When verification occurred
`verified_by`	`UUID`	Yes	Who verified (can be NULL)
`verification_notes`	`TEXT`	Yes	Admin notes during verification

Data Integrity:

✅ verification_status = 'verified' can exist with verified_by = NULL
✅ verified_at timestamp remains as historical record
✅ verification_notes preserved for audit trail

Migration Safety

Safe to Apply

✅ Yes - This is a DDL change that modifies the foreign key constraint

No Data Migration Needed

✅ No existing data affected - Only changes future deletion behavior

Rollback

If needed, can revert to original constraint:

-- Remove the constraint
ALTER TABLE public.movers
DROP CONSTRAINT IF EXISTS movers_verified_by_fkey;

-- Add back without ON DELETE action (original)
ALTER TABLE public.movers
ADD CONSTRAINT movers_verified_by_fkey
FOREIGN KEY (verified_by) REFERENCES auth.users(id);

Production Deployment

-- Check for existing constraint
SELECT 
  tc.constraint_name,
  rc.delete_rule
FROM information_schema.table_constraints tc
JOIN information_schema.referential_constraints rc 
  ON tc.constraint_name = rc.constraint_name
WHERE tc.table_name = 'movers' 
  AND tc.constraint_type = 'FOREIGN KEY'
  AND tc.constraint_name LIKE '%verified_by%';

-- Apply the migration
-- (Run the full migration script)

Testing Recommendations

Test 1: Verify ON DELETE SET NULL Behavior

-- 1. Create test admin user
INSERT INTO auth.users (id, email) 
VALUES ('test-admin-001', 'testadmin@example.com');

-- 2. Create test mover
INSERT INTO public.movers (id, user_id, business_name, phone_primary)
VALUES ('test-mover-001', 'test-customer-001', 'Test Movers', '+254712345678');

-- 3. Verify the mover (simulate admin verification)
UPDATE public.movers
SET 
  verification_status = 'verified',
  verified_at = NOW(),
  verified_by = 'test-admin-001'
WHERE id = 'test-mover-001';

-- 4. Confirm verification
SELECT 
  id,
  verification_status,
  verified_at,
  verified_by
FROM public.movers
WHERE id = 'test-mover-001';

-- Expected: verification_status='verified', verified_by='test-admin-001'

-- 5. Delete the admin user
DELETE FROM auth.users WHERE id = 'test-admin-001';

-- 6. Check mover record
SELECT 
  id,
  verification_status,  -- Should still be 'verified'
  verified_at,          -- Should still have timestamp
  verified_by           -- Should now be NULL
FROM public.movers
WHERE id = 'test-mover-001';

-- Expected:
-- verification_status: 'verified' ✅
-- verified_at: <timestamp> ✅
-- verified_by: NULL ✅

Test 2: Verify Nullable Column

-- Check column definition
SELECT 
  column_name,
  data_type,
  is_nullable,
  column_default
FROM information_schema.columns
WHERE table_name = 'movers'
  AND column_name = 'verified_by';

-- Expected:
-- is_nullable: 'YES' ✅

Test 3: Verify Foreign Key Constraint

-- Check constraint details
SELECT 
  tc.constraint_name,
  kcu.column_name,
  ccu.table_name AS foreign_table_name,
  ccu.column_name AS foreign_column_name,
  rc.delete_rule
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu 
  ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu 
  ON ccu.constraint_name = tc.constraint_name
JOIN information_schema.referential_constraints rc 
  ON rc.constraint_name = tc.constraint_name
WHERE tc.table_name = 'movers'
  AND tc.constraint_type = 'FOREIGN KEY'
  AND kcu.column_name = 'verified_by';

-- Expected:
-- delete_rule: 'SET NULL' ✅

Audit Trail Preservation

Even with verified_by set to NULL, the audit trail remains intact:

What is Preserved:

✅ Verification status - verification_status = 'verified' ✅ Verification timestamp - verified_at (exact time of verification) ✅ Verification notes - verification_notes (admin's comments)

What is Lost:

❌ Admin identity - Cannot trace back to which admin performed verification

Mitigation for Audit Requirements:

If you need to maintain full audit trail including admin identity:

Option 1: Separate Audit Log Table

CREATE TABLE mover_verification_history (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  mover_id UUID NOT NULL REFERENCES movers(id) ON DELETE CASCADE,
  verified_by UUID NOT NULL, -- No FK constraint, store value permanently
  verified_by_email TEXT,
  verified_at TIMESTAMPTZ NOT NULL,
  verification_status verification_status_enum NOT NULL,
  notes TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Trigger to log verification changes
CREATE OR REPLACE FUNCTION log_mover_verification()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.verification_status = 'verified' AND 
     OLD.verification_status != 'verified' AND
     NEW.verified_by IS NOT NULL THEN
    INSERT INTO mover_verification_history (
      mover_id, verified_by, verified_at, verification_status, notes
    ) VALUES (
      NEW.id, NEW.verified_by, NEW.verified_at, NEW.verification_status, NEW.verification_notes
    );
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_log_mover_verification
  AFTER UPDATE OF verification_status ON movers
  FOR EACH ROW
  EXECUTE FUNCTION log_mover_verification();

Option 2: JSONB Status History

-- Add to movers table
ALTER TABLE movers ADD COLUMN verification_history JSONB DEFAULT '[]'::jsonb;

-- Store verification events
UPDATE movers
SET verification_history = verification_history || jsonb_build_object(
  'verified_at', NOW(),
  'verified_by', 'admin-123',
  'status', 'verified',
  'notes', 'All documents verified'
)
WHERE id = 'mover-456';

Related Tables to Review

Check other tables that might have similar issues:

-- Find all foreign keys to auth.users without ON DELETE actions
SELECT 
  tc.table_name,
  kcu.column_name,
  rc.delete_rule
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu 
  ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.referential_constraints rc 
  ON rc.constraint_name = tc.constraint_name
JOIN information_schema.constraint_column_usage ccu 
  ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND ccu.table_name = 'users'
  AND ccu.table_schema = 'auth'
  AND rc.delete_rule = 'NO ACTION';

Common patterns to check:

created_by columns
updated_by columns
approved_by columns
assigned_to columns
Any audit/tracking columns referencing users

Benefits

1. Operational Flexibility

✅ Admins can be removed without data cleanup
✅ No cascade of manual updates required
✅ Simplified user account management

2. Data Integrity

✅ Verification status preserved
✅ Historical timestamps maintained
✅ No data loss on admin deletion

3. Database Health

✅ No foreign key violations
✅ Clean referential integrity
✅ Predictable behavior

4. Audit Compliance

✅ Verification fact remains (status + timestamp)
✅ Clear that verification occurred
✅ Admin notes preserved

Documentation Updates

Add to database documentation:

### Admin User Deletion

When an admin user is deleted:
- All `verified_by` references in the `movers` table are set to NULL
- Verification status and timestamps remain unchanged
- Movers remain verified; only the admin attribution is removed
- This preserves data integrity while allowing admin account cleanup

Files Changed

supabase/migrations/20251008_part1_marketplace_schema.sql

Line 146: Added ON DELETE SET NULL to verified_by foreign key constraint

Verification Checklist

✅ Column is nullable - UUID type without NOT NULL constraint ✅ ON DELETE SET NULL added - Foreign key constraint updated ✅ Syntax correct - SQL grammar validated ✅ Behavior documented - Clear explanation of impact ✅ Test cases provided - SQL scripts for validation ✅ Migration safe - No data changes required

The verified_by foreign key constraint now properly handles admin user deletion by setting the field to NULL while preserving all other verification data! 🎉

FilesExpand file tree

FK_ON_DELETE_SET_NULL_FIX.md

Latest commit

History