​
Common Database Queries

Essential SQL queries for FindU staff to handle support tasks efficiently.
Always use read-only queries when investigating issues. Only use UPDATE/DELETE with supervisor approval.

​
Partner Queries

​
Find Partners

SELECT 
  pu.id,
  pu.email,
  pu.full_name,
  pu.created_at,
  COUNT(DISTINCT pa.entity_id) as entity_count
FROM partner_users pu
LEFT JOIN partner_affiliations pa ON pu.id = pa.partner_user_id
WHERE pu.email ILIKE '%partner@example%'
GROUP BY pu.id;

​
Check Permissions

-- Complete permission check for a user
SELECT 
  pe.name as entity,
  pa.role,
  pa.permissions,
  pcr.display_name as custom_role
FROM partner_affiliations pa
JOIN partner_entities pe ON pa.entity_id = pe.id
LEFT JOIN partner_custom_roles pcr ON pa.role = pcr.role_name
WHERE pa.partner_user_id = 'USER_ID';

​
Student Interaction Queries

​
Engagement Metrics

-- Student interactions by partner entity
SELECT 
  pe.name,
  COUNT(DISTINCT ssi.student_id) as unique_students,
  COUNT(*) as total_interactions,
  COUNT(CASE WHEN ssi.liked = true THEN 1 END) as likes,
  COUNT(CASE WHEN ssi.disliked = true THEN 1 END) as dislikes
FROM student_school_interactions ssi
JOIN partner_entities pe ON ssi.partner_entity_id = pe.id
WHERE ssi.interaction_date > NOW() - INTERVAL '30 days'
GROUP BY pe.id, pe.name
ORDER BY unique_students DESC;

​
Troubleshooting Queries

​
Account Issues

-- Check authentication attempts
SELECT 
  id,
  user_email,
  ip_address,
  created_at,
  CASE 
    WHEN raw_data->>'event_message' LIKE '%Invalid%' THEN 'Invalid Password'
    WHEN raw_data->>'event_message' LIKE '%not found%' THEN 'User Not Found'
    ELSE raw_data->>'event_message'
  END as error_type
FROM auth.audit_log_entries
WHERE user_email = 'partner@example.com'
AND created_at > NOW() - INTERVAL '24 hours'
ORDER BY created_at DESC;

​
Data Integrity

-- Find affiliations without valid entities
SELECT pa.*
FROM partner_affiliations pa
WHERE NOT EXISTS (
  SELECT 1 FROM partner_entities pe
  WHERE pe.id = pa.entity_id
);

-- Find entities without organizations
SELECT pe.*
FROM partner_entities pe
WHERE pe.organization_id IS NOT NULL
AND NOT EXISTS (
  SELECT 1 FROM partner_organizations po
  WHERE po.id = pe.organization_id
);

​
Quick Fixes

​
Common Updates

-- Make user admin of their entity
UPDATE partner_affiliations
SET 
  role = 'admin',
  permissions = '{"all": true}'::jsonb
WHERE partner_user_id = 'USER_ID'
AND entity_id = 'ENTITY_ID';
Always verify the WHERE clause before running UPDATE queries. Consider wrapping in a transaction:
BEGIN;
-- Your update query here
-- Check affected rows
ROLLBACK; -- or COMMIT if correct

​
Reporting Queries

​
Platform Health

-- Daily active partners
SELECT 
  DATE(created_at) as date,
  COUNT(DISTINCT partner_user_id) as active_partners
FROM messages
WHERE sender_type = 'partner'
AND created_at > NOW() - INTERVAL '30 days'
GROUP BY DATE(created_at)
ORDER BY date DESC;

-- Support ticket metrics
SELECT 
  category,
  priority,
  AVG(EXTRACT(EPOCH FROM (resolved_at - created_at))/3600) as avg_hours_to_resolve,
  COUNT(*) as ticket_count
FROM support_tickets
WHERE created_at > NOW() - INTERVAL '30 days'
GROUP BY category, priority
ORDER BY priority, category;

​
Query Best Practices

1

Always Use ILIKE

For text searches, use ILIKE for case-insensitive matching:
WHERE email ILIKE '%example%'  -- Good
WHERE email LIKE '%example%'   -- Case-sensitive
2

Check Before Updating

Always SELECT first to verify your WHERE clause:
-- First, check what will be affected
SELECT * FROM table WHERE condition;

-- Then update if correct
UPDATE table SET column = value WHERE condition;
3

Use Transactions

For critical updates, use transactions:
BEGIN;
UPDATE partner_entities SET show_in_app = true WHERE id = 'XXX';
-- Verify: SELECT * FROM partner_entities WHERE id = 'XXX';
COMMIT; -- or ROLLBACK if wrong
4

Save Common Queries

Keep frequently used queries in a personal document for quick access
Pro tip: The Supabase SQL editor has query history. Use it to find queries you’ve run before!