database-schema

Flow Platform — Database Schema

Last updated: 2026-05-28 — Added marketing lead tables waitlist (consumer pre-registration) and partner_leads (B2B: organizers/venues, merchants, artists, influencers), powering the web /waitlist and /partners pages. Both are server-only (service_role), RLS-enabled with no anon policies. Also reconciled git↔DB migration drift: migrations applied directly to prod (drop_legacy_event_columns, drop_squads_tables_use_crews_only, decouple_event_likes_from_attendance, security/index fixes, event-series cleanup) are now committed under their exact ledger versions.

Earlier (2026-05-08) — Added event_likes table + events.like_count column to decouple like (popularity) from attendance (event_attendees). Migration decouple_event_likes_from_attendance. See feat-event-details-v3 for the rationale and app-layer changes.

Earlier (2026-05-07): DB recon during event-details v3 refactor surfaced several undocumented tables (event_areas, event_lineup_slots) and column drift on events. Squads tables dropped (use crews only).

Earlier note (2026-04-28): event series Flutter integration complete + full mock data seeded (10 users, 4 series, 18 events, 31 ratings, connections, attendees). Series aggregates recalculated manually after replica-mode seed.

This document reflects the live Supabase schema (project hshqhtnkcsbenxorvlmm). Verified via migration files + MCP queries. Before any schema work, read 2026-04-27-architecture-refactor-master-guide §1 first.

---

Database Architecture Overview

Flow uses a Supabase-only architecture: a single Postgres database holds all operational truth, protected by RLS on every table.

Technology	Role
PostgreSQL (Supabase)	Authoritative source for all data: users, events, venues, chat, social graph, notifications
PostGIS	Geospatial queries (geo_point column)
pg_trgm	Fuzzy text search
RLS	Authorization at DB level — no application layer re-verification needed

Legacy architecture (historical)

Until 2026-03-29 Flow used a dual-database architecture (Postgres + MongoDB + Redis). The Node.js microservices in the legacy backend/ repo described that system. The Supabase-only migration eliminated MongoDB and Redis. See 2026-03-29-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
followers_count	—	Dropped in migration 000010 — no follower model
following_count	—	Dropped in migration 000010 — no follower model
events_created	INTEGER	Maintained by trg_events_created on events ✅
events_attended	INTEGER	Maintained by trg_events_attended on event_attendees ✅
friends_count	INTEGER	Maintained by trg_friends_count on connections ✅
xp	INTEGER DEFAULT 0	Gamification XP score
level	INTEGER DEFAULT 1	CHECK (level >= 1)
vibe_score	INTEGER DEFAULT 0
flow_score	INTEGER DEFAULT 0
badges	JSONB DEFAULT ’[]‘	Legacy — prefer user_badges junction table
ban_reason	TEXT	Denormalized from user_bans for quick admin display
ban_expires_at	TIMESTAMPTZ	NULL = not banned or permanent
banned_at	TIMESTAMPTZ
banned_by	UUID FK → profiles(id)
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_cents	INTEGER NOT NULL	Price in eurocents. 0 = free. Canonical monetary field (§2.2 no-float rule).
price_currency	TEXT	ISO 4217 code, default EUR. CHECK ^[A-Z]{3}$
max_attendees	INTEGER	Maximum capacity (never use capacity — that column is on venues)
attendee_count	INTEGER	Denormalized count — maintained by trigger
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, reported, deleted — default draft
series_id	UUID	FK → event_series(id) ON DELETE SET NULL — links instance to its series
deleted_at	TIMESTAMPTZ	Soft-delete timestamp — NULL = not deleted
deleted_by	UUID	FK → profiles(id) — admin who soft-deleted
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 → profiles(id) ON DELETE CASCADE
friend_id	UUID	FK → profiles(id) ON DELETE CASCADE
status	TEXT	CHECK: pending, accepted, rejected, blocked
connection_depth	INTEGER DEFAULT 0	0=acquaintance, 1=friend, 2=buddy, 3=crew — updated by compute-connection-depth Edge Function
created_at	TIMESTAMPTZ
updated_at	TIMESTAMPTZ

Counter trigger (fixed 2026-04-27): trg_friends_count fires AFTER INSERT OR UPDATE OR DELETE and updates profiles.friends_count for both user_id and friend_id. INSERT branch was missing before this fix.

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

---

Social model — friends only

Flow uses a mutual-friend model exclusively (connections table). There is no follower/following asymmetric model. The followers table was dropped in migration 000010 (2026-04-27). All social features use connections with status = 'accepted'.

---

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	Notes
id	UUID PK
name	TEXT UNIQUE
category	TEXT	ENUM-equivalent CHECK
parent_id	UUID	Self-referential FK for sub-tags
usage_count	INTEGER	Incremented on INSERT to event_tags or profiles.interests
translations	JSONB DEFAULT ’{}‘	{"it": "...", "en": "..."} — added migration 20260414000001
created_at	TIMESTAMPTZ

event_series

The “brand” behind a recurring event. “La Prosperosa”, “Pullup”, etc. are series — each occurrence at any venue is a separate events row linked via series_id.

Searching by name finds the series → shows upcoming instances (next dates + venues) + past edition photos + aggregated star rating.

Column	Type	Notes
id	UUID PK
organizer_id	UUID	FK → profiles(id) ON DELETE SET NULL
name	TEXT NOT NULL	”La Prosperosa”
slug	TEXT UNIQUE	URL-safe, e.g. la-prosperosa
description	TEXT
cover_image_url	TEXT
category	TEXT	Same enum as events.category
recurrence_type	TEXT	irregular, weekly, biweekly, monthly, custom
recurrence_interval_days	INTEGER	Only when recurrence_type = 'custom'
avg_rating	NUMERIC(3,2)	Aggregated from event_ratings across all instances — maintained by trg_sync_series_rating
total_ratings	INTEGER
total_editions	INTEGER	Count of all events in this series — maintained by trg_sync_series_editions
created_at	TIMESTAMPTZ
updated_at	TIMESTAMPTZ

Querying a series:

-- Upcoming instances
SELECT e.* FROM events e
WHERE e.series_id = '<series_id>'
  AND e.start_date > now()
ORDER BY e.start_date ASC;
 
-- Past edition photos (from any edition)
SELECT ep.* FROM event_photos ep
JOIN events e ON e.id = ep.event_id
WHERE e.series_id = '<series_id>'
  AND e.start_date < now();

---

event_ratings

Stars-only rating (1–5) per user per event. No text reviews. Ratings aggregate to event_series.avg_rating via trigger.

Column	Type	Notes
id	UUID PK
user_id	UUID NOT NULL	FK → profiles(id) ON DELETE CASCADE
event_id	UUID NOT NULL	FK → events(id) ON DELETE CASCADE
stars	INTEGER	CHECK (stars BETWEEN 1 AND 5)
created_at	TIMESTAMPTZ

Unique constraint: (user_id, event_id) — one rating per user per event.

reviews — Dropped in migration 000010. Text reviews replaced by event_ratings.

---

event_likes

User → event “like” (bookmark / popularity signal). Independent of event_attendees since 2026-05-08 — a user can like an event without RSVPing, and vice-versa.

Column	Type	Notes
user_id	UUID FK → profiles(id) ON DELETE CASCADE	Composite PK part 1
event_id	UUID FK → events(id) ON DELETE CASCADE	Composite PK part 2
created_at	TIMESTAMPTZ NOT NULL DEFAULT now()

Indexes: (event_id) and (user_id) separately for both directions of fan-out.

RLS: public SELECT (popularity is a public signal); INSERT/DELETE only on own row (user_id = auth.uid()). FORCE RLS enabled.

Counter trigger event_likes_count_trg maintains events.like_count on INSERT/DELETE. Use events.like_count for the popularity stat — never count event_likes rows in user-facing reads.

Legacy

Before this table existed, “like” was mapped to event_attendees.status='interested'. The 2026-05-08 migration backfilled all such rows into event_likes and deleted them from event_attendees. The legacy column events.interested_count is no longer maintained and should be dropped in a future cleanup.

---

event_areas

Physical zones inside an event (“Main Stage”, “Garden”, “Underground”). Used by the lineup section.

Column	Type	Notes
id	UUID PK	uuid_generate_v4()
event_id	UUID FK → events(id) ON DELETE CASCADE	NOT NULL
name	TEXT NOT NULL	Display name
description	TEXT
capacity	INTEGER	Optional per-area capacity
sort_order	INTEGER NOT NULL DEFAULT 0
created_at/updated_at	TIMESTAMPTZ

RLS: public SELECT; organizer + admin write. sort_order is the canonical render order.

event_lineup_slots

Time-bounded performance/activity slots, optionally tied to an event_areas row.

Column	Type	Notes
id	UUID PK
event_id	UUID FK → events(id) ON DELETE CASCADE	NOT NULL
area_id	UUID FK → event_areas(id)	Nullable — null = “general” slot, not tied to an area
title	TEXT NOT NULL
artist	TEXT
description	TEXT
start_time	TIMESTAMPTZ NOT NULL
end_time	TIMESTAMPTZ
sort_order	INTEGER NOT NULL DEFAULT 0	Tiebreaker when start_time is equal
created_at/updated_at	TIMESTAMPTZ

CHECK constraint: end_after_start (end_time IS NULL OR end_time > start_time).

The mobile app groups slots by area_id and within each area sorts chronologically by start_time. Slots with area_id = NULL render in a single “General” group.

Legacy events.lineup jsonb

The events.lineup jsonb column (default []) is the predecessor of this table. The mobile app no longer reads it. Drop pending confirmation that no production rows depend on it.

---

Crews

squads / squad_members tables were dropped 2026-05-07 (migration drop_squads_tables_use_crews_only). They had existed with 0 rows — the previous note (“do not exist”) was incorrect. The mobile app’s Squad Dart class and SquadService are naming-misleading: they already query the crews table internally. A future cleanup pass should rename them to align with the schema.

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

---

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.

waitlist

Consumer beta pre-registration captured by the marketing site /waitlist page (web/app/api/waitlist/route.ts). Email is the only required field; the rest enrich the lead for launch prioritisation and attribution.

Column	Type	Notes
id	UUID PK
email	TEXT NOT NULL	Unique via lower(email) index
first_name	TEXT	Optional
city	TEXT	Launch-prioritisation signal
platform	TEXT	CHECK: ios, android
referral_source	TEXT	CHECK: instagram, tiktok, friend, search, other — marketing attribution
age_band	TEXT	CHECK: under_18, 18_24, 25_34, 35_plus — soft 18+ signal
user_agent / source_ip	TEXT	Triage / abuse metadata
status	TEXT	CHECK: pending, invited, joined, spam — default pending
created_at	TIMESTAMPTZ

RLS enabled with no policies: server-only access via service_role (same pattern as contact_messages). Migration 20260528000001_waitlist.

partner_leads

B2B / pro partnership leads captured by /partners (web/app/api/partners/route.ts). Distinct funnel from waitlist.

Column	Type	Notes
id	UUID PK
role	TEXT NOT NULL	CHECK: organizer_venue, merchant, artist_dj, influencer
email	TEXT NOT NULL	Unique via (lower(email), role) index
contact_name	TEXT
business_name	TEXT	Venue / brand / stage name / handle
city	TEXT
website	TEXT
instagram	TEXT	Handle or URL
audience_size	TEXT	Free-form range (mostly influencers)
message	TEXT	”What you offer / what you’re looking for”
user_agent / source_ip	TEXT	Triage / abuse metadata
status	TEXT	CHECK: new, contacted, qualified, won, spam — default new
created_at	TIMESTAMPTZ

RLS enabled with no policies: server-only via service_role. Optional Resend team notification on insert. Migration 20260528000002_partner_leads.

---

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 — Dropped in migration 000010. Comments removed from product.

---

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). Also excludes posts_count (dropped 2026-04-27).

Always use this view for unauthenticated or public-facing profile reads. Has anon + authenticated SELECT grants.

Columns exposed: id, username, first_name, last_name, profile_picture, bio, location, website, interests, role, account_type, verification_status, status, xp, vibe_score, flow_score, karma_score, karma_reviews_count, badges, gender, hometown, last_active, created_at, profile_visibility, values_visibility, followers_count, following_count, events_created, events_attended, friends_count. Visibility-gated: drink_preferences, music_energy.

---

Dropped Tables

Table	Migration	Reason
_deprecated_posts	20260324000008	0 rows, no active RLS policies
friendships	20260324000008	Exact duplicate of connections, 0 rows
followers	20260427000010	Asymmetric follow model removed — friends only (connections)
comments	20260427000010	Comments feature removed from product
reviews	20260427000010	Text reviews replaced by event_ratings (stars only)
squads, squad_members	drop_squads_tables_use_crews_only (2026-05-07)	0 rows, never used by the app — crews is canonical

---

Known Technical Debt

Issue	Status	Notes
Counter columns in profiles unreliable	✅ Fixed 2026-04-27	Triggers added for all 5 counters; posts_count dropped (no source table)
connections FK references auth.users	✅ Fixed 2026-04-27	Migrated to profiles(id)
squads / squad_members tables	✅ N/A	These tables don’t exist in the live DB
user_follows / saved_events tables	✅ N/A	These tables don’t exist in the live DB; followers is canonical
events.tags TEXT[]	✅ Dropped 2026-04-27	Dropped in migration 000008. App uses event_tags junction table.
campaigns.creator_id nullable	Open	23 system/seed campaigns have NULL. Enforce NOT NULL once data is populated.
Social model — friends only	✅ Resolved 2026-04-27	followers dropped in 000010. Only connections (mutual) exists.

---

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.

---

Security & Moderation (post-2026-04-20)

These tables were added after the last schema doc revision and are now documented:

user_bans

Full ban history with lift tracking. GDPR-friendly ON DELETE SET NULL on admin FKs.

Column	Type
id	UUID PK
user_id	UUID FK → profiles(id)
banned_by	UUID FK → profiles(id) ON DELETE SET NULL
lifted_by	UUID FK → profiles(id) ON DELETE SET NULL
reason	TEXT
expires_at	TIMESTAMPTZ — NULL = permanent
lifted_at	TIMESTAMPTZ
created_at	TIMESTAMPTZ

admin_actions_log

Audit trail for all admin operations. Polymorphic target via target_type + target_id.

Column	Type
id	UUID PK
admin_id	UUID FK → profiles(id)
action	TEXT
target_type	TEXT
target_id	UUID
metadata	JSONB
created_at	TIMESTAMPTZ

RLS: admin read + insert only. Service role writes.

reports

Admin moderation queue (distinct from user_reports which is user-to-user flagging).

Column	Type
id	UUID PK
reporter_id	UUID FK → profiles(id)
target_type	TEXT
target_id	UUID
reason	TEXT
status	TEXT CHECK: pending, reviewed, dismissed, actioned
reviewed_by	UUID FK → profiles(id) — accountability column
created_at	TIMESTAMPTZ

tag_affinity

Co-occurrence weight table for neural tag ordering. See context-map tag section.

Column	Type	Notes
tag_a_id	UUID FK → tags(id)
tag_b_id	UUID FK → tags(id)
co_occurrences	INTEGER	How many users selected both
weight	FLOAT	P(B|A) — conditional probability, range 0–1
computed_at	TIMESTAMPTZ

RLS: public SELECT, service_role write. FORCE RLS ✅.

saved_events

Column	Type
user_id	UUID FK → auth.users(id) — §4.10 N/A: saved_events does not exist in live DB
event_id	UUID FK → events(id)
created_at	TIMESTAMPTZ

user_blocks / user_reports

• user_blocks (blocker_id, blocked_id) — composite PK, user-to-user blocks
• user_reports — user-submitted abuse reports (distinct from admin reports table)

---

Materialized Views

View	Migration	Purpose
leaderboard_xp	20260330000006	Top 100 users by XP, refreshed hourly via pg_cron. RPC: get_leaderboard()

---

Migration History

Migrations in supabase/migrations/ ordered by timestamp:

File	Purpose
20260324000001–20260324000013	Original schema (profiles, events, venues, crews, tags, RLS)
20260330000001	Notifications, badges, recommendations + RLS
20260330000002	Full-text search indexes (pg_trgm)
20260330000003	Connection depth + chat last_read_at
20260330000004	Drop deprecated followers table
20260330000005	Seed 20 badge definitions
20260330000006	Leaderboard materialized view + RPC
20260414000001	i18n translations JSONB on tags/badges/campaigns
20260420000001	user_bans, admin_actions_log, events soft-delete, profiles ban columns
20260422060818	reports table (admin moderation queue)
20260427000001	tag_affinity table + FORCE RLS (neural tag ordering)
20260427000002	Security & perf cleanup — FORCE RLS all tables, price_cents enforce, 6 new indexes
20260427000003	Counter triggers — followers_count/following_count on followers, fixed friends_count INSERT gap, dropped posts_count, rebuilt public_profiles view, backfilled all counters
20260427000004	connections FK — migrated user_id/friend_id FKs from auth.users to profiles(id)
20260427000005	event_tags migration — ILIKE + alias map; 33 rows across 13 events. events.tags TEXT[] marked DEPRECATED
20260427000006	app_config security keys — min_app_version, is_spoofing_prevention_active, app_hmac_key_version (Guide §7.1)
20260427000007	tag affinity cron config — cron_compute_tag_affinity row in app_config
20260427000008	Drop legacy event columns — events.price, events.currency, events.tags TEXT[]
20260427000009	Security & index fixes (audit 2026-04-27) — notifications policy, FORCE RLS squads/crew, event_types admin-only, kpi_events/venue_tags use is_admin(), campaigns UPDATE policy, 2 performance indexes
20260427000010	Social cleanup + event_series — drop followers/comments/reviews, drop followers_count/following_count from profiles, add event_series + event_ratings + series_id on events
20260427204655	Drop legacy event columns — events.price/currency/tags (ledger version; committed retroactively)
20260507112112	Drop squads/squad_members — use crews only (ledger version; committed retroactively)
20260507131441	Decouple event_likes — new event_likes table + events.like_count trigger, backfill from interested attendees (ledger version)
20260528000001	waitlist — consumer beta pre-registration table (marketing site)
20260528000002	partner_leads — B2B/pro partnership leads table (marketing site)

git↔DB reconciliation (2026-05-28)

Several migrations had been applied directly to production (dashboard/MCP) but never committed, and two local files carried mismatched 00000N timestamps. They are now committed under their exact ledger versions so supabase db push recognises them as applied. The event_areas_and_lineup_slots (20260502000001) ledger row was also inserted to match the existing tables.

---

Open Tech Debt

Items still requiring action from the Architecture Refactor Master Guide (2026-04-27):

Item	Guide ref	Status	Notes
events.tags TEXT[] column drop	§4.5	⏳ pending Flutter cutover	Column deprecated, data migrated. Drop only after event_api_service.dart confirmed in production and events.tags reads reach zero
saved_events.user_id FK → profiles(id)	§4.10	❌ N/A	saved_events table does not exist in live DB
event_tags write path in wizard	§4.5 follow-up	✅ done	EventWizardNotifier._saveCurrentProgress() now syncs event_tags on every auto-save. createEvent() (direct API) still has a TODO for tag names → IDs lookup — low priority
HMAC signing — Edge Function verification	§7.3	⏳ pending	is_spoofing_prevention_active = false. Activate only after client version shipping X-Flow-Signature headers

| Event series Flutter integration | ✅ done 2026-04-28 | Event.seriesId field added. EventSeriesApiService created. See feat-event-series. | | event_ratings Flutter integration | ✅ done 2026-04-28 | EventApiService.rateEvent() + getUserRating(). Star-tap UI in event_details_screen.dart. | | Series discovery screen | ✅ done 2026-04-28 | SeriesDetailScreen at /series/:id. Series banner in event details. Series results in search “Tutto” tab. See feat-event-series. | | profiles counter columns: followers_count, following_count | ✅ Dropped 2026-04-27 | Removed in 000010 along with the followers table |

Items closed in the 2026-04-27 session:

Item	Guide ref	Closed by
FORCE RLS missing from all tables	§2.1	20260427000002
Counter triggers (followers/following/friends)	§4.1	20260427000003
posts_count drop + view rebuild	§4.1	20260427000003
connections FKs → profiles(id)	§4.4	20260427000004
events.tags data migrated to event_tags	§4.5	20260427000005
squads / squad_members consolidation	§4.3	N/A — tables don’t exist
user_follows FK migration	§4.10	N/A — table doesn’t exist
app_config version & security keys	§7.1	20260427000006
Flutter reads tags from event_tags join	§4.5	event_api_service.dart commit 54d205c
Flutter price_cents migration (§2.2)	§2.2	Flutter commit ca97c3e — Event.priceCents, isFree, displayPriceEuros getters; all 5 UI call-sites updated