Db Janitor

⚠️ CRITICAL SAFETY RULES:

• Always ask for explicit confirmation before executing any SQL
• Only work with files in the docs/ folder
• Never modify SQL files, only execute them
• Only operate in development/test environments
• Clearly warn about data loss before destructive operations

Three Main Actions

Action 1: Reset Database

Purpose: Complete database cleanup and recreation

What it does:

1. Drops all existing tables, policies, functions (teardown.sql)
2. Creates fresh database schema (complete_setup.sql)
3. ⚠️ DESTROYS ALL DATA

When to use:

• Fresh start needed
• Database structure is corrupted
• Testing new schema changes
• Setting up new environment

Confirmation required: YES - Must explicitly warn about data loss

Action 2: Seed Database

Purpose: Populate database with test data

What it does:

1. Inserts 7 test users across 3 projects
2. Inserts 21 time-off requests (various statuses)
3. Inserts 8 holidays for 2026

When to use:

• After database reset
• Testing features with realistic data
• Demo preparation

Confirmation required: YES - Should confirm before inserting

Action 3: Set Admin Email

Purpose: Configure admin access for the application

What it does:

1. Asks user for admin email address
2. Updates company_settings table with admin email(s)
3. Validates email format

When to use:

• After database reset
• Changing admin access
• Initial setup

Confirmation required: YES - Show email before updating

Supabase CLI Commands

Prerequisites Check

# Check if Supabase CLI is installed
supabase --version

# If not installed, guide user to install:
# macOS: brew install supabase/tap/supabase
# Windows: scoop install supabase
# Linux: https://supabase.com/docs/guides/cli

Authentication

# Login to Supabase (if not authenticated)
supabase login

# Link to project (first time only)
supabase link --project-ref <project-ref>

# Or initialize if needed
supabase init

Execute SQL Files

# Execute SQL file against linked project
supabase db execute -f docs/teardown.sql
supabase db execute -f docs/complete_setup.sql
supabase db execute -f docs/seed_data.sql

# Execute SQL query directly
supabase db execute -f - <<SQL
UPDATE company_settings 
SET setting_value = '["[email protected]"]'::jsonb 
WHERE setting_key = 'admin_emails';
SQL

Detailed Workflows

Workflow 1: Reset Database

Step 1: Confirm Action

⚠️ WARNING: This will DELETE ALL DATA in your database.

This action will:
1. Drop all tables (users, time_off_requests, holidays, company_settings)
2. Remove all policies and functions
3. Recreate fresh database schema
4. ALL DATA WILL BE PERMANENTLY LOST

Are you sure you want to proceed? (yes/no)

Step 2: Execute if Confirmed

# Be sure to run from project root

# Step 1: Drop everything
echo "Executing teardown.sql..."
supabase db execute -f docs/teardown.sql

# Step 2: Recreate schema
echo "Executing complete_setup.sql..."
supabase db execute -f docs/complete_setup.sql

echo "✅ Database reset complete!"

Step 3: Verify

# Check tables exist
supabase db execute -f - <<SQL
SELECT tablename FROM pg_tables 
WHERE schemaname = 'public' 
AND tablename IN ('users', 'time_off_requests', 'holidays', 'company_settings');
SQL

Workflow 2: Seed Database

Step 1: Confirm Action

This will insert test data into your database:
- 7 test users (across 3 projects)
- 21 time-off requests (various statuses)
- 8 holidays for 2026

This is safe and can be cleaned up later.

Proceed with seeding? (yes/no)

Step 2: Execute if Confirmed

echo "Executing seed_data.sql..."
supabase db execute -f docs/seed_data.sql

echo "✅ Test data inserted successfully!"

Step 3: Verify

# Check data counts
supabase db execute -f - <<SQL
SELECT 'Users' as table_name, COUNT(*) as count FROM users WHERE id LIKE 'seed-%'
UNION ALL
SELECT 'Time-off Requests', COUNT(*) FROM time_off_requests WHERE user_id LIKE 'seed-%'
UNION ALL
SELECT 'Holidays', COUNT(*) FROM holidays WHERE year = 2026;
SQL

Workflow 3: Set Admin Email

Step 1: Ask for Email

Please enter the admin email address(es).

For single admin:
  [email protected]

For multiple admins (comma-separated):
  [email protected],[email protected]

Admin email(s):

Step 2: Validate Email

# Basic email validation
if [[ ! "$email" =~ ^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$ ]]; then
    echo "❌ Invalid email format"
    exit 1
fi

Step 3: Confirm Before Update

You entered: [email protected]

This will set the admin email(s) in the database.
Current admin users will be able to:
- Manage users
- Approve time-off requests
- Manage holidays
- Access settings

Proceed? (yes/no)

Step 4: Execute if Confirmed

# Convert email to JSON array format
emails_json=$(echo "$emails" | awk -F',' '{
    printf "[";
    for(i=1; i<=NF; i++) {
        gsub(/^[ \t]+|[ \t]+$/, "", $i);
        printf "\"%s\"", $i;
        if(i<NF) printf ",";
    }
    printf "]";
}')

echo "Setting admin email(s)..."
supabase db execute -f - <<SQL
UPDATE company_settings 
SET setting_value = '$emails_json'::jsonb 
WHERE setting_key = 'admin_emails';
SQL

echo "✅ Admin email(s) configured!"

Step 5: Verify

# Show current admin emails
supabase db execute -f - <<SQL
SELECT setting_key, setting_value, description 
FROM company_settings 
WHERE setting_key = 'admin_emails';
SQL

Combined Workflows

Full Reset (Most Common)

Purpose: Complete fresh start with test data

Steps:

1. Reset Database (teardown + setup)
2. Seed Database (insert test data)
3. Set Admin Email (configure access)

Execution:

# Be sure to run from project root

# Step 1: Reset
echo "⚠️  Resetting database..."
supabase db execute -f docs/teardown.sql
supabase db execute -f docs/complete_setup.sql

# Step 2: Seed
echo "📦 Inserting test data..."
supabase db execute -f docs/seed_data.sql

# Step 3: Set admin
echo "👤 Setting admin email..."
read -p "Admin email: " admin_email
supabase db execute -f - <<SQL
UPDATE company_settings 
SET setting_value = '["$admin_email"]'::jsonb 
WHERE setting_key = 'admin_emails';
SQL

echo "✅ Database fully initialized!"

Fresh Setup (No Test Data)

Purpose: Clean database for production-like setup

Steps:

1. Reset Database
2. Set Admin Email only

# Be sure to run from project root

# Step 1: Reset
supabase db execute -f docs/teardown.sql
supabase db execute -f docs/complete_setup.sql

# Step 2: Set admin (skip seed)
read -p "Admin email: " admin_email
supabase db execute -f - <<SQL
UPDATE company_settings 
SET setting_value = '["$admin_email"]'::jsonb 
WHERE setting_key = 'admin_emails';
SQL

echo "✅ Clean database ready!"

Safety Checks

Before executing any command, verify:

1. Project Root Check

# Ensure we're in the right directory
if [[ ! -f "docs/complete_setup.sql" ]]; then
    echo "❌ Error: Not in HollyDayz project root"
    echo "Navigate to: hollydayz project root directory before running commands"
    exit 1
fi

2. Supabase CLI Check

# Check if CLI is installed
if ! command -v supabase &> /dev/null; then
    echo "❌ Supabase CLI not installed"
    echo "Install: https://supabase.com/docs/guides/cli"
    exit 1
fi

3. Authentication Check

# Check if logged in
supabase projects list &> /dev/null
if [ $? -ne 0 ]; then
    echo "❌ Not authenticated with Supabase"
    echo "Run: supabase login"
    exit 1
fi

4. Project Link Check

# Check if project is linked
if [[ ! -f ".supabase/config.toml" ]]; then
    echo "⚠️  Project not linked to Supabase"
    echo "Run: supabase link --project-ref YOUR_PROJECT_REF"
    exit 1
fi

Error Handling

Common Issues

Issue 1: Permission Denied

Error: permission denied for table users

Solution: Check Supabase project permissions, ensure you have access

Issue 2: SQL Syntax Error

Error: syntax error at or near "..."

Solution: SQL files might be corrupted, verify file contents

Issue 3: Connection Timeout

Error: connection timeout

Solution: Check internet connection, verify Supabase project is active

Issue 4: File Not Found

Error: no such file or directory: docs/teardown.sql

Solution: Ensure running from project root: hollydayz project root directory before running commands

Best Practices

1. Always Backup Production

   • Never run these scripts on production database
   • Use Supabase dashboard backups before major changes

2. Test in Stages

   • Run teardown → verify
   • Run setup → verify
   • Run seed → verify

3. Keep SQL Files Safe

   • Never modify the SQL files
   • If changes needed, create new versions

4. Document Changes

   • Log what was done
   • Keep track of admin emails set

5. Verify After Actions

   • Always check tables exist
   • Verify data was inserted
   • Test admin login works

Quick Commands Reference

# Full reset with seed data
supabase db execute -f docs/teardown.sql && \
supabase db execute -f docs/complete_setup.sql && \
supabase db execute -f docs/seed_data.sql

# Reset only (no seed)
supabase db execute -f docs/teardown.sql && \
supabase db execute -f docs/complete_setup.sql

# Seed only (add test data)
supabase db execute -f docs/seed_data.sql

# Set admin email (replace with actual email)
supabase db execute -f - <<SQL
UPDATE company_settings 
SET setting_value = '["[email protected]"]'::jsonb 
WHERE setting_key = 'admin_emails';
SQL

# View current admin emails
supabase db execute -f - <<SQL
SELECT setting_value FROM company_settings WHERE setting_key = 'admin_emails';
SQL

# Remove seed data only
supabase db execute -f - <<SQL
DELETE FROM time_off_requests WHERE user_id LIKE 'seed-%';
DELETE FROM users WHERE id LIKE 'seed-%';
SQL

User Interaction Guidelines

When user asks to perform database operations:

1. Clarify Intent

   • Understand what they want to achieve
   • Suggest appropriate action (reset/seed/admin)

2. Explain Consequences

   • Clearly state what will happen
   • Warn about data loss if applicable

3. Ask for Confirmation

   • Always require explicit "yes" for destructive operations
   • Show what will be executed

4. Execute Step-by-Step

   • Run commands in correct order
   • Show progress/output
   • Verify after each major step

5. Provide Summary

   • Confirm what was done
   • Show verification results
   • Suggest next steps

Response Templates

Asking for Confirmation

⚠️ WARNING: [Action] will [consequence]

This will:
- [Step 1]
- [Step 2]
- [Step 3]

Type 'yes' to proceed or 'no' to cancel:

Success Message

✅ [Action] completed successfully!

Summary:
- [What was done]
- [What was created]
- [Current state]

Next steps:
- [Suggested action]

Error Message

❌ Error during [action]

Error: [error message]

Possible causes:
- [Cause 1]
- [Cause 2]

Solution:
[How to fix]

Remember

• This skill only works with the three SQL files in docs/
• Always ask for confirmation before executing
• Only for development/test environments
• Verify authentication before starting
• Show clear warnings for destructive operations
• Provide helpful error messages and solutions