database-schema

Flow Platform — Database Schema

Ultima revisione: 2026-04-20 — post migrazione Supabase-only

Questo documento riflette lo schema live in Supabase (project hshqhtnkcsbenxorvlmm), verificato via PostgREST API.

---

Database Architecture Overview

Flow usa un’architettura Supabase-only: un unico database Postgres contiene tutta la verità operativa del prodotto, protetto da RLS su ogni tabella.

Tecnologia	Ruolo
PostgreSQL (Supabase)	Sorgente autoritativa per tutto: utenti, eventi, venue, chat, social graph, notifiche
PostGIS	Query geospaziali (colonna geo_point)
pg_trgm	Fuzzy text search nativo
RLS	Autorizzazione: nessun layer API intermedio deve riverificare

Architettura precedente (storica)

Fino al 2026-03-29 Flow usava un’architettura dual-database (Postgres + MongoDB + Redis). I microservizi Node.js nel repo legacy backend/ descrivevano quel sistema. La migrazione Supabase-only ha eliminato MongoDB e Redis; i dati operativi vivono tutti in Postgres, la cache è gestita da materialized views e connessioni Supabase persistenti. Vedi Supabase-only Migration Design.

---

PostgreSQL / Supabase Schema

Security Model

All tables have Row-Level Security (RLS) enabled. The general pattern is:

• Authenticated users can read/write their own rows
• Admins and super-admins can access all rows via the is_admin() helper function
• Service role (backend) bypasses RLS entirely

is_admin() and is_super_admin() helpers

-- SECURITY DEFINER: runs as postgres, bypassing RLS on profiles.
-- This prevents infinite recursion when admin policies need to check profiles.role.
SELECT public.is_admin();       -- true if current user has role 'admin' or 'super_admin'
SELECT public.is_super_admin(); -- true if current user has role 'super_admin'

Why SECURITY DEFINER? A policy on profiles that queries profiles to check the role creates infinite recursion. The SECURITY DEFINER function runs with elevated privileges and bypasses RLS, breaking the cycle. See 20260324000008_db_audit_fix.sql Section 1 for details.

---

Core Tables

profiles

Extends auth.users (Supabase Auth) with application-level profile data.

Column	Type	Notes
id	UUID PK	FK → auth.users(id) ON DELETE CASCADE
email	TEXT UNIQUE
username	TEXT UNIQUE
first_name	TEXT
last_name	TEXT
phone_number	TEXT
profile_picture	TEXT	URL
bio	TEXT
date_of_birth	TIMESTAMPTZ
location	TEXT	Human-readable location string
latitude	FLOAT8
longitude	FLOAT8
geo_point	GEOGRAPHY(Point,4326)	PostGIS point for spatial queries
website	TEXT
interests	TEXT[]	Array of interest strings
settings	JSONB	User preferences/settings
role	TEXT	CHECK: user, moderator, admin, super_admin, vendor, partner, influencer, pr, dj_artist — default user
status	TEXT	CHECK: active, suspended, banned, pending — default active
is_active	BOOLEAN	Legacy flag — kept for backward compat, backfilled into status
is_email_verified	BOOLEAN
is_phone_verified	BOOLEAN
is_verified	BOOLEAN	Platform verification badge
karma_score	NUMERIC(3,2)	0.00–5.00, default 3.00
karma_reviews_count	INTEGER
posts_count	INTEGER	Denormalized — not auto-updated, always 0. Use JOIN.
followers_count	INTEGER	Denormalized — not auto-updated. Use JOIN.
following_count	INTEGER	Denormalized — not auto-updated. Use JOIN.
events_created	INTEGER	Denormalized — not auto-updated. Use JOIN.
events_attended	INTEGER	Denormalized — not auto-updated. Use JOIN.
friends_count	INTEGER	Denormalized — not auto-updated. Use JOIN.
last_active	TIMESTAMPTZ
created_at	TIMESTAMPTZ
updated_at	TIMESTAMPTZ	Auto-updated via trigger

RLS policies:

• "Users can read own full profile" — SELECT where auth.uid() = id
• "Authenticated users can read safe profile fields" — SELECT for all authenticated (use public_profiles view for unauthenticated reads)
• "Users can update own profile" — UPDATE where auth.uid() = id
• "admin_read_all_profiles" — SELECT via is_admin()
• "admin_update_any_profile" — UPDATE via is_admin()

⚠️ Counter columns (posts_count, followers_count, etc.) are all 0 because there are no triggers to update them. Compute these via JOIN instead of reading the denormalized columns. A future migration should either add triggers or drop these columns.

---

events

Core event data with geospatial support.

Column	Type	Notes
id	UUID PK
organizer_id	UUID	FK → profiles(id)
venue_id	UUID	FK → venues(id) — nullable
title	TEXT
description	TEXT
image_url	TEXT
image_urls	TEXT[]
start_date	TIMESTAMPTZ
end_date	TIMESTAMPTZ
location	JSONB	{address, city, country, lat, lng, ...}
location_point	GEOGRAPHY(Point,4326)	PostGIS for radius queries
latitude	FLOAT8
longitude	FLOAT8
price	NUMERIC
currency	TEXT
max_attendees	INTEGER	Maximum capacity (was capacity in early code — use max_attendees)
attendee_count	INTEGER	Denormalized count
is_public	BOOLEAN
is_free	BOOLEAN
type	TEXT	CHECK: in_person, virtual, hybrid — default in_person
category	TEXT	CHECK: social, nightlife, music, arts, food_drinks, clubbing, party, live_music, tech, sports, wellness, culture, networking, generale — default generale
status	TEXT	CHECK: draft, active, published, cancelled
tags	TEXT[]	Legacy array — prefer event_tags junction table
ticket_url	TEXT
dress_code	TEXT
age_requirement	INTEGER
additional_info	JSONB
created_at	TIMESTAMPTZ
updated_at	TIMESTAMPTZ	Auto-updated via trigger

⚠️ The column is max_attendees, NOT capacity. Early frontend code mistakenly used capacity — this caused null values in the admin portal events table.

---

venues

Physical venue data.

Column	Type	Notes
id	UUID PK
owner_id	UUID	FK → profiles(id)
name	TEXT
description	TEXT
address	TEXT
city	TEXT
country	TEXT
latitude	FLOAT8
longitude	FLOAT8
location_point	GEOGRAPHY(Point,4326)	PostGIS
capacity	INTEGER	Physical venue capacity
category	TEXT
image_url	TEXT
images	TEXT[]
website	TEXT
phone	TEXT
email	TEXT
social_links	JSONB
amenities	TEXT[]
opening_hours	JSONB
is_verified	BOOLEAN
is_active	BOOLEAN
rating	NUMERIC(3,2)
review_count	INTEGER
created_at	TIMESTAMPTZ
updated_at	TIMESTAMPTZ

---

Social Graph

connections

The canonical friend/connection table. Used by all social RPCs.

Column	Type	Notes
id	UUID PK
user_id	UUID	FK → auth.users(id)
connected_user_id	UUID	FK → auth.users(id)
status	TEXT	pending, accepted, blocked
created_at	TIMESTAMPTZ
updated_at	TIMESTAMPTZ

ℹ️ FKs reference auth.users instead of profiles. This is safe (auth.users.id === profiles.id always) but non-standard. A future migration should update the FK target to profiles.

friendships — Dropped in migration 008. It was a duplicate of connections with 0 rows.

---

followers

Column	Type	Notes
id	UUID PK
follower_id	UUID	FK → profiles(id)
following_id	UUID	FK → profiles(id)
created_at	TIMESTAMPTZ

⚠️ There is also a user_follows table (created in a 2026 migration) with an identical structure — these two tables are duplicates. A product decision is needed: does the platform use a follower model or a mutual-friends model? See project_social_model memory for context. Until decided, both tables exist.

---

Events Social Graph

event_attendees

Column	Type	Notes
id	UUID PK
event_id	UUID	FK → events(id)
user_id	UUID	FK → profiles(id)
status	TEXT	going, interested, not_going
ticket_id	UUID
created_at	TIMESTAMPTZ

event_tags

Junction table linking events to tags (preferred over events.tags TEXT[]).

Column	Type
event_id	UUID FK → events(id)
tag_id	UUID FK → tags(id)

tags

Column	Type
id	UUID PK
name	TEXT UNIQUE
created_at	TIMESTAMPTZ

---

Crews & Squads

⚠️ Two separate table families exist for roughly the same concept (small social groups attached to events). This is a known design issue. Standardization is deferred — see migration 008 SUMMARY comment.

crews

Column	Type	Notes
id	UUID PK
name	TEXT
creator_id	UUID	FK → profiles(id)
chat_id	UUID	FK → chats(id)
event_id	UUID	FK → events(id)
max_size	INTEGER
status	TEXT
created_at	TIMESTAMPTZ

crew_members

Column	Type
id	UUID PK
crew_id	UUID FK → crews(id)
user_id	UUID FK → profiles(id)
role	TEXT
joined_at	TIMESTAMPTZ

crew_feedback

Karma reviews between crew members.

Column	Type
id	UUID PK
crew_id	UUID FK → crews(id)
reviewer_id	UUID FK → profiles(id)
reviewee_id	UUID FK → profiles(id)
rating	INTEGER (1-5)
comment	TEXT
created_at	TIMESTAMPTZ

squads

Similar to crews but used by a different feature path (squad_chat migration).

Column	Type	Notes
id	UUID PK
name	TEXT
creator_id	UUID	FK → profiles(id)
event_id	UUID	FK → events(id)
max_members	INTEGER
status	TEXT
invite_code	TEXT
created_at	TIMESTAMPTZ

squad_members

Column	Type
id	UUID PK
squad_id	UUID FK → squads(id)
user_id	UUID FK → profiles(id)
role	TEXT
joined_at	TIMESTAMPTZ

---

Messaging

chats

Column	Type	Notes
id	UUID PK
name	TEXT	NULL for DMs (direct chats), set for groups
type	TEXT	CHECK: direct, group, crew, squad, venue
created_by	UUID	FK → profiles(id)
created_at	TIMESTAMPTZ
updated_at	TIMESTAMPTZ

Direct message chats have name = NULL. Before migration 008, they incorrectly had name = 'Direct Chat'.

chat_participants

Column	Type	Notes
id	UUID PK
chat_id	UUID	FK → chats(id)
user_id	UUID	FK → profiles(id)
role	TEXT	CHECK: owner, admin, member
status	TEXT	CHECK: active, muted, banned, left
last_read_at	TIMESTAMPTZ
joined_at	TIMESTAMPTZ

messages

Column	Type	Notes
id	UUID PK
chat_id	UUID	FK → chats(id)
sender_id	UUID	FK → profiles(id)
content	TEXT
type	TEXT	text, image, system
metadata	JSONB
is_deleted	BOOLEAN	Soft delete
created_at	TIMESTAMPTZ
updated_at	TIMESTAMPTZ

---

Campaigns & Marketing

campaigns

Column	Type	Notes
id	UUID PK
creator_id	UUID	FK → profiles(id) — nullable (system campaigns have NULL)
title	TEXT
description	TEXT
type	TEXT	CHECK: social, environmental, music, health, education, business, art, sports, culture, other
status	TEXT	CHECK: active, completed, cancelled, draft
target_audience	JSONB
participant_count	INTEGER
banner_url	TEXT
icon_url	TEXT
start_date	TIMESTAMPTZ
end_date	TIMESTAMPTZ
created_at	TIMESTAMPTZ

There are currently 23 seed/system campaigns with creator_id = NULL. The FK was added in migration 008 (ON DELETE SET NULL). Enforce creator_id NOT NULL at the application layer.

---

Platform Administration

app_config

Key-value store for runtime-configurable application settings.

Column	Type
id	UUID PK
key	TEXT UNIQUE
value	JSONB
description	TEXT
updated_at	TIMESTAMPTZ

Current keys (as of 2026-03-24):

Key	Value	Description
event_rate_limit_user	10	Max events a user can create per day
event_rate_limit_venue	50	Max events a venue can create per day
feedback_survey_pct	20	% of users shown feedback survey
max_crew_size	10	Default max crew size
max_squad_size	8	Default max squad size
karma_min_score	1.0
karma_max_score	5.0
karma_default	3.0

RLS: only admins can write. All authenticated users can read.

app_issues

User-reported issues / bug reports submitted from the app.

Column	Type	Notes
id	UUID PK
user_id	UUID	FK → profiles(id)
title	TEXT
description	TEXT
category	TEXT
status	TEXT	open, in_progress, resolved, closed
priority	TEXT
assigned_to	UUID	FK → profiles(id) — admin assignee
resolved_by	UUID	FK → profiles(id) — admin who resolved
resolved_at	TIMESTAMPTZ
metadata	JSONB	Device info, app version, etc.
created_at	TIMESTAMPTZ
updated_at	TIMESTAMPTZ

assigned_to, resolved_by, resolved_at were added in migration 008 for issue workflow tracking.

---

Verification & Moderation

verification_requests

Users requesting platform verification badge.

Column	Type	Notes
id	UUID PK
user_id	UUID	FK → profiles(id)
status	TEXT	pending, approved, rejected
submission_data	JSONB
reviewed_by	UUID	FK → profiles(id)
reviewed_at	TIMESTAMPTZ
created_at	TIMESTAMPTZ

moderator_permissions

Column	Type	Notes
id	UUID PK
moderator_id	UUID	FK → profiles(id)
permission	TEXT
granted_by	UUID	FK → profiles(id)
created_at	TIMESTAMPTZ

comments

User comments (on events or posts). Low usage currently but non-empty — kept.

Column	Type
id	UUID PK
user_id	UUID FK → profiles(id)
target_id	UUID
target_type	TEXT
content	TEXT
created_at	TIMESTAMPTZ

---

PR / Influencer Layer

pr_profiles

Extended profile data for users with the pr role.

Column	Type
id	UUID PK
user_id	UUID UNIQUE FK → profiles(id)
agency	TEXT
specializations	TEXT[]
contact_email	TEXT
portfolio_url	TEXT
created_at	TIMESTAMPTZ

venue_prs

Links PR users to venues they manage.

Column	Type
id	UUID PK
venue_id	UUID FK → venues(id)
pr_user_id	UUID FK → profiles(id)
role	TEXT
created_at	TIMESTAMPTZ

---

Public Views

public_profiles

A safe public projection of profiles — excludes sensitive columns (email, phone_number, date_of_birth, latitude, longitude).

Always use this view for unauthenticated or public-facing profile reads instead of querying the profiles table directly.

---

Dropped Tables

The following tables were removed in migration 20260324000008_db_audit_fix:

Table	Reason
_deprecated_posts	0 rows, no active RLS policies, replaced by application-level post system
friendships	Exact duplicate of connections, 0 rows, no references

---

Known Technical Debt

Issue	Status	Notes
Denormalized counter columns in profiles always 0	Open	No triggers update posts_count, followers_count, etc. Compute via JOIN.
Duplicate social graph: followers + user_follows	Open	Product decision needed on follower vs. mutual-friend model
Duplicate group model: crews + squads	Open	Evolved separately, now both 0 rows. Consolidation deferred.
connections FK references auth.users not profiles	Low priority	Same IDs in practice; changing requires constraint recreation
events.tags TEXT[] alongside event_tags junction	Open	Migrate app code to use junction table, then drop column
campaigns.creator_id nullable	Open	23 system/seed campaigns have NULL. Enforce NOT NULL once data is populated.

---

Migration History (PostgreSQL)

Migrations are in supabase/migrations/ ordered by timestamp prefix.

File	Purpose
20250601000000_mvp_foundations.sql	Initial schema: profiles, events, venues, crews, tags, RLS
20260101000001_chat_system.sql	chats, chat_participants, messages
20260101000002_campaigns.sql	campaigns table
20260101000003_app_config.sql	app_config key-value store
20260101000004_app_issues.sql	app_issues bug reporting
20260101000005_verification_moderation.sql	verification_requests, moderator_permissions
20260324000006_profiles_status_column.sql	⚠️ SUPERSEDED by 008
20260324000007_events_nullable_defaults.sql	⚠️ SUPERSEDED by 008
20260324000008_db_audit_fix.sql	Comprehensive security + integrity audit fix — see audit doc

For full details on what migration 008 fixed, see Database Audit 2026-03-24.

---

MongoDB Collections (per microservice)

Each backend microservice owns its own MongoDB database.

Service	Database	Key Collections
user-service	flow_users	users, preferences
event-service	flow_events	events, venues, categories
social-service	flow_social	connections, groups, posts
notification-service	flow_notifications	notifications, templates, user_preferences
realtime-service	flow_realtime	messages, rooms, user_presence

MongoDB data overlaps with PostgreSQL (known dual-write issue). The canonical data source for mobile app and admin portal is PostgreSQL. MongoDB is authoritative only for operational/transactional flows handled by the microservices.