Flow Docs

database-audit-2026-03-24

6 min read

Database Audit — 2026-03-24

This document records the findings and fixes from a comprehensive Supabase database audit conducted on 2026-03-24. All changes were applied via migration 20260324000008_db_audit_fix.sql.

Why This Audit Was Done

The admin portal was showing empty tables (users, events) despite data existing in the database. Investigation revealed a cascade of issues: RLS policies causing infinite recursion, column name mismatches between migrations and frontend code, dirty enum data, and missing constraints.

The audit was performed by connecting directly to the live Supabase instance via the PostgREST API (service role) to verify the real schema — not just the migration files, which were out of sync with the actual DB state.

Findings and Fixes

1. RLS Infinite Recursion (Critical)

Problem: Several admin-facing RLS policies on profiles checked profiles.role to authorize access. This caused the query to re-trigger the same policy, creating infinite recursion. Supabase silently returns 0 rows instead of raising an error — so admin queries appeared to succeed but returned nothing.

Affected policies: admin_read_all_profiles, admin_update_any_profile, and all other policies that did:

-- BAD: queries profiles from within a profiles policy
USING (EXISTS (SELECT 1 FROM profiles WHERE id = auth.uid() AND role = 'admin'))

Fix: Created two SECURITY DEFINER helper functions that run with elevated privileges, bypassing RLS:

CREATE OR REPLACE FUNCTION public.is_admin() RETURNS BOOLEAN ...
CREATE OR REPLACE FUNCTION public.is_super_admin() RETURNS BOOLEAN ...

All admin policies were rewritten to use USING (public.is_admin()). This pattern was applied to: profiles, events, app_config, app_issues, moderator_permissions, verification_requests, pr_profiles.

2. profiles.role — Invalid Values and Missing super_admin

Problem: The role CHECK constraint did not include super_admin, which is used in the codebase. There were also some uppercase role values in old rows.

Fix:

  • Lowercased all existing role values
  • Dropped and recreated the CHECK constraint to include: user, moderator, admin, super_admin, vendor, partner, influencer, pr, dj_artist

3. profiles.status Column Missing

Problem: The admin portal code references a status column (active, suspended, banned, pending). This column was defined in migration 006 but never applied to the live database.

Fix: Added status TEXT DEFAULT 'active' CHECK (status IN ('active', 'suspended', 'banned', 'pending')) and backfilled existing rows from is_active (true → active, false → suspended).

4. Overly Broad Public Profile SELECT

Problem: The "Public profiles are viewable by everyone" RLS policy exposed all columns including email, phone_number, date_of_birth, latitude, longitude to unauthenticated users.

Fix: Dropped the broad policy. Replaced with:

  • "Users can read own full profile" — full access to own row
  • "Authenticated users can read safe profile fields" — authenticated users can read all rows (but the public_profiles VIEW should be used for public-facing reads, as it excludes sensitive columns)

5. events.type — Dirty Enum Data

Problem: The type column was supposed to represent event modality (in_person, virtual, hybrid) but contained free-text genre values from an early period when the column was misused: nightclub, concert, festival, aperitivo, bar, club, inPerson, in-person.

Fix: Data was corrected before adding the CHECK constraint:

UPDATE events SET type = 'in_person' WHERE type IN ('inPerson', 'in-person', 'nightclub', 'concert', 'festival', 'aperitivo', 'bar', 'club');

Then added CHECK (type IN ('in_person', 'virtual', 'hybrid')).

6. events.category — Not Normalized

Problem: Category values were mixed-case (e.g., Social, Music).

Fix: Lowercased all values. Added CHECK constraint with all valid categories: social, nightlife, music, arts, food_drinks, clubbing, party, live_music, tech, sports, wellness, culture, networking, generale

7. events Defaults Missing

Problem: events.type and events.category had no DEFAULT values, causing vendor INSERT failures when these fields were omitted.

Fix: Set DEFAULT 'in_person' and DEFAULT 'generale'. (This was migration 007, now consolidated.)

8. events.status — No CHECK Constraint

Problem: The status column had no validation. Any string could be inserted.

Fix: Added CHECK (status IN ('draft', 'active', 'published', 'cancelled')).

9. chats.name for Direct Messages

Problem: Direct message chats had name = 'Direct Chat' — a meaningless placeholder instead of NULL.

Fix:

UPDATE chats SET name = NULL WHERE type = 'direct' AND name = 'Direct Chat';

Also added CHECK (type IN ('direct', 'group', 'crew', 'squad', 'venue')).

10. chat_participants — No Role/Status Validation

Problem: role and status columns accepted any string.

Fix:

  • role CHECK: owner, admin, member
  • status CHECK: active, muted, banned, left

11. Dropped Dead Tables

TableReason
_deprecated_posts0 rows, already had “_deprecated” prefix, no active RLS policies
friendshipsExact structural duplicate of connections, 0 rows, never populated

12. campaigns — FK and Constraints

Problem: creator_id had no FK reference to profiles. type and status had no validation.

Fix:

  • Added FK: creator_id REFERENCES profiles(id) ON DELETE SET NULL
  • Added type CHECK: social, environmental, music, health, education, business, art, sports, culture, other
  • Added status CHECK: active, completed, cancelled, draft

Note: All 23 existing campaigns have creator_id = NULL (system/seed data). The column remains nullable. Enforce non-null at the application layer until seed data is replaced.

13. app_config — Duplicate Keys

Problem: Three keys were duplicated under different names:

Removed keyReplaced by
max_events_per_day_userevent_rate_limit_user (same value: 10)
max_events_per_day_venueevent_rate_limit_venue (same value: 50)
feedback_survey_percentagefeedback_survey_pct (value 15 removed, 20 kept)

14. app_issues — Issue Tracking Workflow

Problem: No way to assign issues to admins or track resolution.

Fix: Added columns:

  • assigned_to UUID FK → profiles(id)
  • resolved_by UUID FK → profiles(id)
  • resolved_at TIMESTAMPTZ
  • Index on assigned_to

Intentional Non-Changes

The following issues were identified but deliberately left for future migrations:

IssueReason not changed
profiles counter columns always 0Dropping risks app crashes; no triggers exist. Use JOIN instead.
comments table — unclear usageNon-empty, kept. Usage audit needed.
followers + user_follows duplicateProduct decision required on social model
squads + crews dual tablesUsed by different migrations; risky to consolidate without full audit
connections FK → auth.users (not profiles)Low risk; changing requires constraint recreation
campaigns.creator_id nullableAll 23 rows are NULL (seed data)
events.tags TEXT[] alongside event_tagsBackward compat; remove after app fully migrates to junction table

Frontend Fix: events.max_attendees

Separately from the database migration, the admin portal had a bug where the event capacity column was queried as capacity instead of max_attendees. Fixed in:

  • web/app/api/admin/events/route.ts — SELECT now includes max_attendees
  • web/app/admin/(dashboard)/events/page.tsx — interface and display updated

How to Apply

Migration 20260324000008_db_audit_fix.sql is idempotent where possible (uses IF NOT EXISTS, DROP CONSTRAINT IF EXISTS, ADD COLUMN IF NOT EXISTS).

# Via Supabase CLI (requires SUPABASE_ACCESS_TOKEN)
SUPABASE_ACCESS_TOKEN=<token> npx supabase db push
 
# Or paste the SQL directly into the Supabase SQL editor
# Dashboard → SQL Editor → paste supabase/migrations/20260324000008_db_audit_fix.sql

Graph View

Backlinks