Issue Type?
├── Connection Issues → Workflow 1: Connection Diagnostics
├── Auth Problems → Workflow 2: Authentication Debugging
├── Slow Performance → Workflow 3: Query Optimization
├── Realtime Issues → Workflow 4: Subscription Debugging
├── Memory Leaks → Workflow 5: Memory Investigation
├── RLS Violations → Workflow 6: Policy Validation
├── Pre-Deploy Check → Workflow 7: Production Readiness
├── Data Resurrection → Workflow 8: localStorage Fallback Audit
└── Production API Issues → Workflow 9: Production Debugging (Cloudflare + Caddy)

# Check if Docker is running
docker ps

# Check Supabase services status
npx supabase status

# Test PostgreSQL connection directly
psql "postgresql://postgres:postgres@localhost:5432/postgres" -c "SELECT 1"

# Check connection from application
curl http://localhost:54321/rest/v1/ -H "apikey: YOUR_ANON_KEY"

-- Check active connections
SELECT count(*), state, wait_event_type
FROM pg_stat_activity
GROUP BY state, wait_event_type;

-- Check max connections
SHOW max_connections;

// Decode JWT token (in browser console)
const token = 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...';
const payload = JSON.parse(atob(token.split('.')[1]));
console.log({
  userId: payload.sub,
  email: payload.email,
  role: payload.role,
  expiresAt: new Date(payload.exp * 1000),
  isExpired: Date.now() > payload.exp * 1000
});

-- Check auth.users table
SELECT id, email, created_at, last_sign_in_at, confirmed_at
FROM auth.users
WHERE email = 'user@example.com';

-- Check auth settings
SELECT * FROM auth.config;

// Set up auth state listener
supabase.auth.onAuthStateChange((event, session) => {
  console.log('Auth event:', event);
  console.log('Session:', session);
});

// Implement token refresh before expiration
const { data, error } = await supabase.auth.refreshSession();
if (error) {
  console.error('Refresh failed:', error.message);
  // Handle by redirecting to login
}

-- Enable pg_stat_statements extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- View slow queries
SELECT
  query,
  calls,
  total_time / calls as avg_time_ms,
  rows
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%'
ORDER BY total_time DESC
LIMIT 20;

-- Explain analyze a slow query
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM tasks WHERE user_id = 'uuid-here';

-- Find missing indexes on foreign keys
SELECT
  tc.table_name,
  kcu.column_name,
  'CREATE INDEX idx_' || tc.table_name || '_' || kcu.column_name
    || ' ON ' || tc.table_name || '(' || kcu.column_name || ');' as suggested_index
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
AND NOT EXISTS (
  SELECT 1 FROM pg_indexes
  WHERE tablename = tc.table_name
  AND indexdef LIKE '%' || kcu.column_name || '%'
);

// BEFORE: Slow query
const { data } = await supabase
  .from('users')
  .select('*')
  .limit(100);

// AFTER: Optimized query
const { data } = await supabase
  .from('users')
  .select('id, name, email')  // Specific columns
  .order('created_at', { ascending: false })
  .limit(100);

// Check subscription state
const channel = supabase.channel('test');
channel.on('system', {}, (payload) => {
  console.log('System event:', payload);
}).subscribe((status) => {
  console.log('Subscription status:', status);
});

// Simple broadcast test
const channel = supabase.channel('test-channel');

channel
  .on('broadcast', { event: 'test' }, (payload) => {
    console.log('Received:', payload);
  })
  .subscribe(async (status) => {
    if (status === 'SUBSCRIBED') {
      await channel.send({
        type: 'broadcast',
        event: 'test',
        payload: { message: 'hello', timestamp: Date.now() }
      });
    }
  });

-- Verify realtime is enabled on table
SELECT * FROM pg_publication_tables
WHERE pubname = 'supabase_realtime';

-- Enable realtime for a table
ALTER PUBLICATION supabase_realtime ADD TABLE your_table;

// Browser memory tracking
const initialMemory = performance.memory?.usedJSHeapSize || 0;
setInterval(() => {
  const currentMemory = performance.memory?.usedJSHeapSize || 0;
  const delta = currentMemory - initialMemory;
  console.log(`Memory delta: ${(delta / 1024 / 1024).toFixed(2)} MB`);
}, 5000);

// List all active channels
const channels = supabase.getChannels();
console.log('Active channels:', channels.length);
channels.forEach(ch => {
  console.log(`- ${ch.topic}: ${ch.state}`);
});

// React/Vue cleanup pattern
useEffect(() => {
  const channel = supabase
    .channel('my-channel')
    .on('postgres_changes',
      { event: '*', schema: 'public', table: 'tasks' },
      handleChange
    )
    .subscribe();

  // CRITICAL: Cleanup on unmount
  return () => {
    supabase.removeChannel(channel);
  };
}, []);

// Implement connection pooling
const supabase = createClient(url, key, {
  db: {
    schema: 'public',
  },
  realtime: {
    params: {
      eventsPerSecond: 10,  // Rate limit
    },
  },
});

-- View all policies on a table
SELECT
  schemaname,
  tablename,
  policyname,
  permissive,
  roles,
  cmd,
  qual,
  with_check
FROM pg_policies
WHERE tablename = 'your_table';

-- Temporarily switch to user role and test
SET ROLE authenticated;
SET request.jwt.claim.sub = 'user-uuid-here';

SELECT * FROM your_table;  -- Test SELECT
INSERT INTO your_table (col) VALUES ('test');  -- Test INSERT

RESET ROLE;

-- Users can only see their own data
CREATE POLICY "Users see own data"
ON tasks FOR SELECT
USING (auth.uid() = user_id);

-- Users can insert their own data
CREATE POLICY "Users create own data"
ON tasks FOR INSERT
WITH CHECK (auth.uid() = user_id);

-- Users can update their own data
CREATE POLICY "Users update own data"
ON tasks FOR UPDATE
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);

// Check if operation succeeded
const { data, error, count } = await supabase
  .from('tasks')
  .insert({ title: 'Test' })
  .select()
  .single();

if (error) {
  console.error('Insert error:', error);
} else if (!data) {
  console.warn('RLS may have silently blocked the operation');
} else {
  console.log('Success:', data);
}

-- Check for missing indexes
SELECT
  relname as table,
  seq_scan,
  idx_scan,
  n_tup_ins + n_tup_upd + n_tup_del as writes
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan * 10
AND seq_scan > 1000;

-- Check table sizes
SELECT
  relname as table,
  pg_size_pretty(pg_total_relation_size(relid)) as size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

-- Check all tables have RLS enabled
SELECT
  schemaname,
  tablename,
  rowsecurity
FROM pg_tables
WHERE schemaname = 'public';

-- Verify each table has policies
SELECT
  t.tablename,
  COUNT(p.policyname) as policy_count
FROM pg_tables t
LEFT JOIN pg_policies p ON t.tablename = p.tablename
WHERE t.schemaname = 'public'
GROUP BY t.tablename
HAVING COUNT(p.policyname) = 0;

-- Capture slow query baseline
SELECT
  query,
  calls,
  mean_time,
  max_time
FROM pg_stat_statements
WHERE mean_time > 100
ORDER BY mean_time DESC
LIMIT 10;

# Scan codebase for all localStorage usage
grep -roh "localStorage\.\(get\|set\|remove\)Item(['\"][^'\"]*['\"]" src/ \
  --include="*.ts" --include="*.vue" | \
  sed "s/localStorage\.\(get\|set\|remove\)Item(['\"]//g" | \
  sed "s/['\"]//g" | sort -u

// ❌ BAD: Resurrects deleted data
const loadedGroups = await fetchGroups()  // Returns [] (all deleted)
if (loadedGroups.length === 0) {
  const localGroups = loadFromLocalStorage()  // Has OLD deleted groups!
  _rawGroups.value = localGroups  // RESURRECTS DELETED DATA
}

// ✅ GOOD: Supabase is source of truth
const loadedGroups = await fetchGroups()
_rawGroups.value = loadedGroups  // Empty means empty

// In browser console after fix:
// 1. Create some groups
// 2. Delete all groups
// 3. Refresh page
// 4. Check groups are still gone:
console.log('Groups after refresh:',
  JSON.parse(localStorage.getItem('flowstate-guest-groups') || '[]').length,
  'local,',
  // Check Pinia store
  useCanvasStore().groups.length,
  'in store'
);

// src/stores/canvas.ts - CORRECT PATTERN
const loadFromDatabase = async () => {
  const authStore = useAuthStore()

  // Guest mode: start empty (ephemeral)
  if (!authStore.isAuthenticated) {
    _rawGroups.value = []
    return
  }

  // Authenticated: Supabase is SINGLE source of truth
  const loadedGroups = await fetchGroups()
  _rawGroups.value = loadedGroups  // Empty = empty, no fallback!

  // Log for debugging
  if (loadedGroups.length === 0) {
    console.log('📭 [SUPABASE] No groups (all deleted or none created)')
  }
}

# 1. Test Cloudflare → Origin (bypass CF cache)
curl -sI --resolve "api.example.com:443:YOUR_VPS_IP" \
  "https://api.example.com/rest/v1/tasks" \
  -H "apikey: YOUR_ANON_KEY"

# 2. Test via Cloudflare
curl -sI "https://api.example.com/rest/v1/tasks" \
  -H "apikey: YOUR_ANON_KEY" | grep -iE "cf-cache|content-type"

# 3. Test CORS preflight
curl -X OPTIONS "https://api.example.com/rest/v1/tasks" \
  -H "Origin: https://example.com" \
  -H "Access-Control-Request-Method: GET" \
  -I

# 4. Test WebSocket upgrade
curl -sI "https://api.example.com/realtime/v1/websocket" \
  -H "Upgrade: websocket" \
  -H "Connection: Upgrade"

api.example.com {
    tls /etc/caddy/certs/cloudflare-origin.pem /etc/caddy/certs/cloudflare-origin.key

    # Handle CORS preflight FIRST
    @options method OPTIONS
    handle @options {
        header Access-Control-Allow-Origin "*"
        header Access-Control-Allow-Methods "GET, POST, PUT, DELETE, PATCH, OPTIONS"
        header Access-Control-Allow-Headers "*"
        header Access-Control-Expose-Headers "*"
        header Access-Control-Max-Age "86400"
        respond 204
    }

    # Proxy to Supabase Kong
    handle {
        reverse_proxy localhost:8000 {
            # Required headers
            header_up X-Forwarded-Proto https
            header_up X-Forwarded-Host api.example.com

            # WebSocket support
            header_up Upgrade {http.request.header.Upgrade}
            header_up Connection {http.request.header.Connection}
        }

        # CORS headers on responses
        header Access-Control-Allow-Origin "*"
        header Access-Control-Expose-Headers "*"
    }
}

# 1. Get auth token (login)
curl -X POST "https://api.example.com/auth/v1/token?grant_type=password" \
  -H "apikey: YOUR_ANON_KEY" \
  -H "Content-Type: application/json" \
  -d '{"email":"test@example.com","password":"password"}'

# 2. Test authenticated request
curl "https://api.example.com/rest/v1/tasks" \
  -H "apikey: YOUR_ANON_KEY" \
  -H "Authorization: Bearer YOUR_ACCESS_TOKEN"

# 3. Check JWT claims
echo "YOUR_ACCESS_TOKEN" | cut -d'.' -f2 | base64 -d 2>/dev/null | jq

// Browser console debug for production realtime
const supabase = createClient('https://api.example.com', 'YOUR_ANON_KEY');

// Enable debug logging
supabase.realtime.setAuth(yourAccessToken);

const channel = supabase
  .channel('debug-channel')
  .on('system', {}, (payload) => {
    console.log('🔌 System event:', payload);
  })
  .on('postgres_changes',
    { event: '*', schema: 'public', table: 'tasks' },
    (payload) => console.log('📬 DB change:', payload)
  )
  .subscribe((status, err) => {
    console.log('📡 Status:', status, err);
  });

-- Table row counts
SELECT schemaname, relname, n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

-- Index usage
SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes;

-- Active locks
SELECT * FROM pg_locks WHERE granted = false;

-- Current connections
SELECT count(*), state FROM pg_stat_activity GROUP BY state;

// Monitor all database changes
supabase
  .channel('db-changes')
  .on('postgres_changes',
    { event: '*', schema: 'public' },
    (payload) => console.log('Change:', payload)
  )
  .subscribe();

// Get current session
const { data: { session } } = await supabase.auth.getSession();
console.log('Current user:', session?.user);
console.log('Token expires:', new Date(session?.expires_at * 1000));