# Production Database Cleanup Plan

*Compiled by Cog — 2026-02-20 | For Builder & Harper Review*

---

## ⚠️ CRITICAL: DO NOT EXECUTE WITHOUT BUILDER + MICHAEL APPROVAL

This document is a **plan only**. No data has been modified. All queries below are DELETE statements that must be reviewed, tested against a backup, and executed by Builder.

---

## Executive Summary

**25 total profiles** in production. **18 are test accounts** (72%). **7 are real users**.

Test data will show up in match results, worker searches, and job listings visible to real contractors. This must be cleaned before launch outreach drives real signups.

---

## What's Real (DO NOT TOUCH)

| Profile | Type | Email | Company | Notes |
|---------|------|-------|---------|-------|
| Michael McLoughlin | worker | rocky.mcloughlin@gmail.com | — | Founder's test account (KEEP) |
| RATERIGHT PTY LTD | contractor | admin@rateright.com.au | RATERIGHT PTY LTD | Company account (KEEP) |
| LF CONSTRUCTION SERVICES | contractor | admin@lfcs.com.au | LF CONSTRUCTION SERVICES PTY LTD | Real contractor (KEEP) |
| fraserrrobertson | worker | fraserrrobertson@mail.com | — | Real signup (KEEP) |
| tjhooley | worker | tjhooley@gmail.com | — | Real signup (KEEP) |
| Liam Thompson | worker | rorywelsh2@outlook.com | — | Real signup (KEEP) |
| steveodonnell15 | worker | steveodonnell15@gmail.com | — | Real signup — newest (KEEP) |

**Real data to preserve:**
- 3 matches (all involving Michael or steveodonnell15 with RATERIGHT PTY LTD jobs)
- 1 payment ($50, charged, Stripe ID: `pi_3T2lTFRQ4311dYYb1vzDz2if`) — **REAL PAYMENT**
- 2 ratings (both 5-star, between steveodonnell15 and RATERIGHT)
- 6 jobs posted by RATERIGHT PTY LTD (company_id: `51845da0...`)

---

## What Must Be Removed (18 Test Accounts)

### Group 1: Seed Workers (10 accounts) — `@test.rateright.com.au`
Created 2026-02-14, all onboarding_completed=true. These look like real workers to the matching engine.

| # | Name | Email | Trade | Exp | Profile ID |
|---|------|-------|-------|-----|-----------|
| 1 | Jake Mitchell | jake.mitchell@test.rateright.com.au | Steel Fixer | 8yr | `38f3ee09...` |
| 2 | Liam O'Brien | liam.obrien@test.rateright.com.au | Steel Fixer, Formworker | 12yr | `efdb17e9...` |
| 3 | Sean Murphy | sean.murphy@test.rateright.com.au | Carpenter | 5yr | `90bb3e80...` |
| 4 | Tom Bradley | tom.bradley@test.rateright.com.au | Electrician | 15yr | `281e16f7...` |
| 5 | Mick Torres | mick.torres@test.rateright.com.au | Labourer | 2yr | `597a1d1e...` |
| 6 | Danny Reeves | danny.reeves@test.rateright.com.au | Plumber | 7yr | `f53620da...` |
| 7 | Callum Walsh | callum.walsh@test.rateright.com.au | Formworker | 6yr | `e86a689b...` |
| 8 | Ryan Chen | ryan.chen@test.rateright.com.au | Painter | 4yr | `11805cfd...` |
| 9 | Ben Nguyen | ben.nguyen@test.rateright.com.au | Concreter | 9yr | `84af2f14...` |
| 10 | Patrick Kelly | patrick.kelly@test.rateright.com.au | Scaffolder | 11yr | `c8c76b74...` |

### Group 2: Seed Contractors (3 accounts) — `@test.rateright.com.au`
Created 2026-02-14, all onboarding_completed=true. These have fake companies + fake jobs.

| # | Name | Email | Company | ABN | Profile ID |
|---|------|-------|---------|-----|-----------|
| 11 | Dave Thompson | dave@test.rateright.com.au | Dave's Construction | 51 824 753 556 | `ad15c675...` |
| 12 | Sarah Collins | sarah@test.rateright.com.au | Metro Build Group | 88 000 014 675 | `894507d7...` |
| 13 | Chris Antonelli | chris@test.rateright.com.au | Westside Formwork | 12 345 678 901 | `d2883d82...` |

### Group 3: Debug/E2E Test Accounts (5 accounts)
Created during development/testing. Incomplete profiles.

| # | Name | Email | Type | Profile ID |
|---|------|-------|------|-----------|
| 14 | testdebug999 | testdebug999@example.com | worker | `8de6ec3d...` |
| 15 | (none) | test.worker1@fake.com | worker | `59e295a8...` |
| 16 | (none) | e2e-worker-...@test.rateright.com.au | worker | `f7b2ceed...` |
| 17 | Jake Murphy | test-audit@example.com | worker (ai_generated) | `6dc9361c...` |
| 18 | Sean Kelly | testdebug1770772151@example.com | worker (ai_generated) | `9c1e9031...` |

---

## Related Data to Remove

### Test Companies (3)

| Company | ABN | Company ID | Owner |
|---------|-----|-----------|-------|
| Dave's Construction | 51 824 753 556 | `cb34c6d2...` | Dave Thompson (test) |
| Metro Build Group | 88 000 014 675 | `dcf94014...` | Sarah Collins (test) |
| Westside Formwork | 12 345 678 901 | `2208a791...` | Chris Antonelli (test) |

### Test Jobs (8) — All status=active, all posted by test companies

| Job Title | Trade | Suburb | Company | Job ID |
|-----------|-------|--------|---------|--------|
| Steel Fixer needed | Steel Fixer | Parramatta | Dave's Construction | `557f8e4a...` |
| Carpenter - Commercial Fit-out | carpenter | Parramatta | Dave's Construction | `a39a6ff6...` |
| General Labourers | Labourer | Parramatta | Dave's Construction | `883def2d...` |
| Experienced Carpenter | Carpenter | Chatswood | Metro Build Group | `3a6e8dfe...` |
| Electrician for fitout | Electrician | Sydney | Metro Build Group | `dbe185d2...` |
| Electrician - Industrial Warehouse | electrician | Liverpool | Metro Build Group | `27b31e63...` |
| Formwork team | Formworker | Blacktown | Westside Formwork | `68ef179f...` |
| General Labourers - Road Works | labourer | Blacktown | Westside Formwork | `31f881a4...` |

### Test Worker Profiles (10)
All in `worker_profiles` table, linked to Group 1 seed workers.

### Matches: **0** involving test accounts (clean — no cross-contamination)
### Payments: **0** involving test accounts (clean)
### Notifications: **0** for test accounts (clean)
### Ratings: **0** involving test accounts (clean)

---

## Deletion Order (FK Constraints)

Supabase has `ON DELETE CASCADE` on most foreign keys, but the safest approach is bottom-up:

```sql
-- ============================================
-- STEP 1: Verify counts before deletion
-- ============================================

-- Should return 18
SELECT count(*) FROM profiles 
WHERE email ILIKE '%@test.rateright.com.au%' 
   OR email ILIKE '%@example.com%' 
   OR email ILIKE '%@fake.com%'
   OR email ILIKE 'e2e-%';

-- Should return 8
SELECT count(*) FROM jobs 
WHERE company_id IN (
  SELECT id FROM companies WHERE profile_id IN (
    SELECT id FROM profiles WHERE email ILIKE '%@test.rateright.com.au%'
  )
);

-- ============================================
-- STEP 2: Delete test jobs (no matches/payments reference them)
-- ============================================

DELETE FROM jobs 
WHERE company_id IN (
  SELECT id FROM companies WHERE profile_id IN (
    SELECT id FROM profiles 
    WHERE email ILIKE '%@test.rateright.com.au%'
       OR email ILIKE '%@example.com%'
       OR email ILIKE '%@fake.com%'
       OR email ILIKE 'e2e-%'
  )
);

-- ============================================
-- STEP 3: Delete test worker_profiles
-- ============================================

DELETE FROM worker_profiles 
WHERE profile_id IN (
  SELECT id FROM profiles 
  WHERE email ILIKE '%@test.rateright.com.au%'
     OR email ILIKE '%@example.com%'
     OR email ILIKE '%@fake.com%'
     OR email ILIKE 'e2e-%'
);

-- ============================================
-- STEP 4: Delete test companies
-- ============================================

DELETE FROM companies 
WHERE profile_id IN (
  SELECT id FROM profiles 
  WHERE email ILIKE '%@test.rateright.com.au%'
     OR email ILIKE '%@example.com%'
     OR email ILIKE '%@fake.com%'
     OR email ILIKE 'e2e-%'
);

-- ============================================
-- STEP 5: Delete test profiles
-- ============================================

DELETE FROM profiles 
WHERE email ILIKE '%@test.rateright.com.au%'
   OR email ILIKE '%@example.com%'
   OR email ILIKE '%@fake.com%'
   OR email ILIKE 'e2e-%';

-- ============================================
-- STEP 6: Delete test auth.users (Supabase Auth)
-- ============================================
-- NOTE: This requires Supabase Dashboard or Admin API.
-- Profile cascade should handle profiles table,
-- but auth.users entries may remain as orphans.
-- 
-- Builder: Use Supabase Dashboard > Authentication > Users
-- to delete users matching test email patterns.
-- OR use the Admin API:
--
-- For each test user ID:
-- curl -X DELETE "https://eciepjpcyfurbkfzekok.supabase.co/auth/v1/admin/users/{user_id}" \
--   -H "apikey: SERVICE_ROLE_KEY" \
--   -H "Authorization: Bearer SERVICE_ROLE_KEY"

-- ============================================
-- STEP 7: Verify cleanup
-- ============================================

SELECT count(*) FROM profiles;  -- Should be 7
SELECT count(*) FROM companies; -- Should be 2 (RATERIGHT + LFCS)
SELECT count(*) FROM jobs;      -- Should be 6 (all RATERIGHT jobs)
SELECT count(*) FROM matches;   -- Should be 3 (unchanged)
SELECT count(*) FROM payments;  -- Should be 1 (unchanged)
SELECT count(*) FROM ratings;   -- Should be 2 (unchanged)
```

---

## Risk Assessment

| Risk | Level | Mitigation |
|------|-------|-----------|
| Accidentally delete real user | LOW | All test emails use distinct patterns. Real emails don't match. |
| FK cascade deletes real data | NONE | No test accounts have matches/payments/ratings with real accounts. Verified: 0 cross-links. |
| Stripe payment affected | NONE | Only payment is from RATERIGHT PTY LTD (real). Not touched. |
| Auth.users orphaned | MEDIUM | Must clean auth.users separately after profiles deletion. |
| Test data re-seeded by code | MEDIUM | Check if Builder has a seed script that auto-runs. Disable or guard with env flag. |

---

## Additional Recommendations

### 1. Prevent Future Test Data in Production
- Add a seed script guard: `if (process.env.NODE_ENV === 'production') throw 'Refusing to seed production'`
- Add `is_test` boolean column to profiles (default false) — filter in all queries
- Or use a naming convention filter in the matching engine

### 2. Fake ABN Cleanup
- ABN `12 345 678 901` (Westside Formwork) is obviously fake
- ABN `51 824 753 556` and `88 000 014 675` should be verified — may be real ABNs assigned to test accounts
- After deletion, check `abn_cache` table for orphaned entries

### 3. Jobs Audit
- 8 test jobs showing as "active" in search results — real contractors would see these
- 6 RATERIGHT jobs are AI-generated (`ai_generated=true`) — Michael should review whether these should remain active or be closed

### 4. Trade Name Inconsistency (Side Finding)
- Test jobs use inconsistent trade names: "Steel Fixer" vs "steel-fixer", "Carpenter" vs "carpenter", "Labourer" vs "labourer"
- Real jobs also have this issue. May cause matching bugs.
- **Recommend: Builder normalises trade names to lowercase with hyphens, or adds a lookup table.**

---

## Decision Needed

**From Builder:** Review SQL, confirm FK cascade safety, execute cleanup.

**From Harper:** Confirm no compliance/audit reason to retain test data (data retention policy).

**From Michael:** Approve deletion. Review whether RATERIGHT's 6 AI-generated active jobs should stay or be closed before real contractors see them.

---

*Report delivered. Zero data modified. Cog does ops, Builder does execution.*