Relationship Diagram The following diagram shows the relationships between all database tables:
Foreign Key Relationships profiles Foreign Key References On Delete Description user_idauth.users(id)CASCADE Links profile to auth user
Notes:
user_id has a UNIQUE constraint (one profile per user)Profile is auto-created via trigger when user signs up
user_roles Foreign Key References On Delete Description user_idauth.users(id)CASCADE Links role to auth user
Notes:
(user_id, role) has a UNIQUE constraint (prevent duplicate role assignments)A user can have multiple roles (e.g., both client and event_manager)
organizations Foreign Key References On Delete Description owner_idauth.users(id)CASCADE Organization owner
Notes:
No incoming foreign keys (top of the organization hierarchy)
Deleting the owner deletes the organization and all child records (events, attendees, etc.)
organization_members Foreign Key References On Delete Description organization_idorganizations(id)CASCADE Parent organization user_idauth.users(id)CASCADE Member user account
Notes:
(organization_id, user_id) has a UNIQUE constraint (user can only be added once per org)Deleting the organization removes all member associations
Deleting the user removes their membership
events Foreign Key References On Delete Description organization_idorganizations(id)CASCADE Parent organization
Notes:
(organization_id, slug) has a UNIQUE constraint (event slugs unique within org)Deleting the organization deletes all events and their attendees
Event deletion should call return_attendee_tokens_on_event_delete() to refund tokens
event_manager_assignments Foreign Key References On Delete Description event_idevents(id)CASCADE Assigned event user_idauth.users(id)CASCADE Assigned event manager
Notes:
(event_id, user_id) has a UNIQUE constraint (manager assigned once per event)Deleting the event removes all manager assignments
Deleting the user removes their event assignments
attendees Foreign Key References On Delete Description event_idevents(id)CASCADE Parent event
Notes:
(event_id, unique_id) has a UNIQUE constraint(event_id, email) has a UNIQUE constraintDeleting the event deletes all attendees
Deleting attendees triggers counter updates in the events table
token_transactions Foreign Key References On Delete Description organization_idorganizations(id)CASCADE Organization that purchased tokens
Notes:
Transactions are immutable (no UPDATE policies)
Deleting the organization deletes transaction history
audit_logs Foreign Key References On Delete Description organization_idorganizations(id)SET NULL Organization context
Notes:
organization_id is nullable (platform-level actions may not have an org)ON DELETE SET NULL preserves audit history even if org is deleted
user_id is not a foreign key (allows logging of deleted users)
token_pricing No foreign keys. This is a configuration table managed by super admins.
No foreign keys. This is a global configuration table.
Cascading Delete Behavior Understanding cascading deletes is critical for data integrity:
Deleting a User (auth.users) Impact:
If the user owns an organization, all events, attendees, transactions, and members are deleted
This is by design to maintain referential integrity
Consider transferring ownership before deleting a client account
Deleting an Organization Impact:
All events, attendees, members, and transactions are permanently deleted
Audit logs remain but lose organization reference
No token refunds occur automatically
Deleting an Event Impact:
All attendees and manager assignments are deleted
Application should call return_attendee_tokens_on_event_delete() to refund attendee tokens
Event token is not automatically refunded
Unique Constraints Unique constraints prevent duplicate data:
Single-Column Unique Constraints Table Column(s) Purpose profilesuser_idOne profile per user organizationsslugGlobally unique organization slugs platform_settingskeyOne row per setting
Composite Unique Constraints Table Column(s) Purpose user_roles(user_id, role)Prevent duplicate role assignments organization_members(organization_id, user_id)User can only join an org once events(organization_id, slug)Event slugs unique within organization event_manager_assignments(event_id, user_id)Manager assigned once per event attendees(event_id, unique_id)Unique attendee IDs per event attendees(event_id, email)Unique attendee emails per event
The attendees table has two unique constraints: one on unique_id and one on email, both scoped to the event. This prevents duplicate attendees by either identifier.
Relationship Patterns One-to-One
User → Profile : Each user has exactly one profile
Enforced by UNIQUE constraint on profiles.user_id
Created automatically via trigger on signup
One-to-Many
Organization → Events : An organization owns multiple eventsEvent → Attendees : An event has multiple attendeesOrganization → Token Transactions : An organization has multiple purchasesUser → Organizations (as owner): A user can own multiple organizations
Many-to-Many
Users ↔ Organizations (via organization_members)
A user can be a member of multiple organizations
An organization can have multiple members
Users ↔ Events (via event_manager_assignments)
An event manager can be assigned to multiple events
An event can have multiple event managers
Users ↔ Roles (via user_roles)
A user can have multiple roles (e.g., client + event_manager)
A role can be assigned to multiple users
Authorization Patterns Foreign keys support RLS policy authorization:
Ownership-Based Access -- Users can view their own organization CREATE POLICY "Owners can view own org" ON organizations FOR SELECT USING ( auth . uid () = owner_id); -- Owners can manage their events CREATE POLICY "Org owners can manage their events" ON events FOR ALL USING (organization_id IN ( SELECT id FROM organizations WHERE owner_id = auth . uid () ));
Membership-Based Access -- Organization members can view events CREATE POLICY "Org members can view events" ON events FOR SELECT USING (organization_id IN ( SELECT organization_id FROM organization_members WHERE user_id = auth . uid () AND status = 'active' ));
Assignment-Based Access -- Event managers can view assigned events' attendees CREATE POLICY "Event managers can view attendees" ON attendees FOR SELECT USING (event_id IN ( SELECT event_id FROM event_manager_assignments WHERE user_id = auth . uid () ));
Database Functions (RPC) Several security definer functions abstract complex relationship queries:
Authorization Helpers Function Purpose has_role(_user_id, _role)Check if user has a specific role is_org_member(_user_id, _org_id)Check if user is an active org member get_user_org_id(_user_id)Get the organization ID for a user (owner or member)
Public Access Functions Function Purpose get_org_public_info(_slug)Safe organization lookup for public pages get_event_public_info(_event_slug, _org_id)Event details for check-in and portal pages public_atomic_checkin(...)Validate and perform check-in in one call get_attendee_portal_data(...)Attendee data for portal access
Business Logic Functions Function Purpose create_org_and_assign_role(...)Atomic org creation + role assignment on signup deduct_event_token(_organization_id)Safely deduct one event token deduct_attendee_tokens(_organization_id, _count)Safely deduct attendee tokens return_attendee_tokens_on_event_delete(...)Refund attendee tokens when event is deleted log_audit_event(...)Create audit log entry with authorization checks
All token deduction functions use FOR UPDATE row locking to prevent race conditions under concurrent access.
Circular Dependency Prevention The schema avoids circular foreign key dependencies:
auth.users (managed by Supabase) is at the rootprofiles and user_roles reference users but nothing references themorganizations reference users but only via owner_idorganization_members references both orgs and users (bridge table)All tenant data flows from organizations down (events → attendees)
This unidirectional flow ensures clean cascading deletes without deadlocks.
Next Steps
Schema Overview High-level architecture and design principles
Tables Reference Detailed column specifications