Database Tables

Core Tables

profiles

User profile information, automatically created on signup.

Column	Type	Nullable	Default	Description
`id`	UUID	NO	`gen_random_uuid()`	Primary key
`user_id`	UUID	NO	-	Foreign key to `auth.users.id` (unique)
`full_name`	TEXT	NO	-	User’s full name
`avatar_url`	TEXT	YES	-	URL to user avatar image
`created_at`	TIMESTAMPTZ	NO	`now()`	Record creation timestamp
`updated_at`	TIMESTAMPTZ	NO	`now()`	Last update timestamp

Indexes:

Primary key on id
Unique index on user_id

Triggers:

update_profiles_updated_at - Auto-updates updated_at on row changes
Created automatically via handle_new_user() trigger on auth.users

RLS Policies:

Users can view and update their own profile
Super admins can view all profiles

user_roles

Role assignments for users. Separate from profiles for security.

Column	Type	Nullable	Default	Description
`id`	UUID	NO	`gen_random_uuid()`	Primary key
`user_id`	UUID	NO	-	Foreign key to `auth.users.id`
`role`	app_role	NO	-	Enum: `super_admin`, `client`, `event_manager`

Indexes:

Primary key on id
Unique constraint on (user_id, role)

RLS Policies:

Users can view their own roles
Super admins can manage all roles

organizations

Client organizations (tenants).

Column	Type	Nullable	Default	Description
`id`	UUID	NO	`gen_random_uuid()`	Primary key
`name`	TEXT	NO	-	Organization name
`slug`	TEXT	NO	-	URL-safe unique identifier
`logo_url`	TEXT	YES	-	Organization logo URL
`owner_id`	UUID	NO	-	Foreign key to `auth.users.id`
`status`	TEXT	NO	`'active'`	Enum: `active`, `suspended`, `deleted`
`event_tokens`	INTEGER	NO	`0`	Available event tokens
`attendee_tokens`	INTEGER	NO	`0`	Available attendee tokens
`created_at`	TIMESTAMPTZ	NO	`now()`	Record creation timestamp
`updated_at`	TIMESTAMPTZ	NO	`now()`	Last update timestamp

Indexes:

Primary key on id
Unique index on slug

Constraints:

status must be in ('active', 'suspended', 'deleted')

Triggers:

update_organizations_updated_at - Auto-updates updated_at

RLS Policies:

Owners can view and update their organization
Organization members can view their organization
Super admins can view and manage all organizations
Authenticated users can create organizations (signup)

organization_members

Links event managers to organizations.

Column	Type	Nullable	Default	Description
`id`	UUID	NO	`gen_random_uuid()`	Primary key
`organization_id`	UUID	NO	-	Foreign key to `organizations.id`
`user_id`	UUID	NO	-	Foreign key to `auth.users.id`
`role`	app_role	NO	`'event_manager'`	Typically `event_manager`
`status`	TEXT	NO	`'active'`	Enum: `active`, `suspended`
`created_at`	TIMESTAMPTZ	NO	`now()`	Record creation timestamp

Indexes:

Primary key on id
Unique constraint on (organization_id, user_id)

Constraints:

status must be in ('active', 'suspended')

RLS Policies:

Organization owners can manage members
Members can view their own membership
Super admins can manage all members

events

Event configuration and content.

Show View all event columns (38 columns)

Column	Type	Nullable	Default	Description
`id`	UUID	NO	`gen_random_uuid()`	Primary key
`organization_id`	UUID	NO	-	Foreign key to `organizations.id`
`name`	TEXT	NO	-	Event name
`slug`	TEXT	NO	-	URL-safe identifier (unique per org)
`details`	TEXT	YES	-	Event description
`date`	DATE	NO	-	Event date
`venue`	TEXT	YES	-	Event location
`category`	TEXT	YES	-	Event category/type
`status`	TEXT	NO	`'active'`	Event status
`attendee_fields`	JSONB	YES	`'["Name","Email"]'`	Custom form fields configuration
`checkin_page_active`	BOOLEAN	NO	`false`	Enable/disable public check-in
`total_attendees`	INTEGER	NO	`0`	Cached attendee count
`checked_in_count`	INTEGER	NO	`0`	Cached check-in count
Content Columns (Published)
`checkin_page_content`	TEXT	YES	-	Check-in page HTML/markdown
`attendee_portal_content`	TEXT	YES	-	Attendee portal HTML/markdown
`post_checkin_content`	TEXT	YES	-	Post check-in page content
`confirmation_email_content`	TEXT	YES	-	Email template (Tiptap JSON)
Content Columns (Draft)
`draft_checkin_page_content`	TEXT	YES	-	Draft check-in page
`draft_attendee_portal_content`	TEXT	YES	-	Draft attendee portal
`draft_post_checkin_content`	TEXT	YES	-	Draft post check-in
Branding Columns (Published)
`branding_portal`	JSONB	YES	-	Attendee portal theme JSON
`branding_checkin`	JSONB	YES	-	Check-in page theme JSON
`branding_postcheckin`	JSONB	YES	-	Post check-in theme JSON
Branding Columns (Draft)
`draft_branding_portal`	JSONB	YES	-	Draft portal branding
`draft_branding_checkin`	JSONB	YES	-	Draft check-in branding
`draft_branding_postcheckin`	JSONB	YES	-	Draft post check-in branding
Puck Editor Data (Published)
`puck_portal_data`	JSONB	YES	-	Portal page builder data
`puck_checkin_data`	JSONB	YES	-	Check-in page builder data
`puck_post_checkin_data`	JSONB	YES	-	Post check-in page builder data
Puck Editor Data (Draft)
`draft_puck_portal_data`	JSONB	YES	-	Draft portal builder data
`draft_puck_checkin_data`	JSONB	YES	-	Draft check-in builder data
`draft_puck_post_checkin_data`	JSONB	YES	-	Draft post check-in builder data
Timestamps
`created_at`	TIMESTAMPTZ	NO	`now()`	Record creation timestamp
`updated_at`	TIMESTAMPTZ	NO	`now()`	Last update timestamp

Indexes:

Primary key on id
Unique constraint on (organization_id, slug)

Triggers:

update_events_updated_at - Auto-updates updated_at

RLS Policies:

Organization owners can manage their events
Organization members can view events
Super admins can manage all events

The draft/publish pattern allows clients to preview changes before making them live. Publish actions copy draft columns to published columns.

event_manager_assignments

Assigns event managers to specific events.

Column	Type	Nullable	Default	Description
`id`	UUID	NO	`gen_random_uuid()`	Primary key
`event_id`	UUID	NO	-	Foreign key to `events.id`
`user_id`	UUID	NO	-	Foreign key to `auth.users.id`
`created_at`	TIMESTAMPTZ	NO	`now()`	Assignment timestamp

Indexes:

Primary key on id
Unique constraint on (event_id, user_id)

RLS Policies:

Organization owners and super admins manage assignments
Event managers can view their own assignments

attendees

Event attendees and check-in status.

Column	Type	Nullable	Default	Description
`id`	UUID	NO	`gen_random_uuid()`	Primary key
`event_id`	UUID	NO	-	Foreign key to `events.id`
`unique_id`	TEXT	NO	-	8-character alphanumeric ID (auto-generated)
`name`	TEXT	NO	-	Attendee name
`email`	TEXT	NO	-	Attendee email
`custom_fields`	JSONB	YES	`'{}'`	Custom attendee data
`checked_in`	BOOLEAN	NO	`false`	Check-in status
`checked_in_at`	TIMESTAMPTZ	YES	-	Check-in timestamp
`checkin_method`	TEXT	YES	-	Method: `qr_scan`, `self_service`, `manual`
`portal_active`	BOOLEAN	NO	`false`	Attendee portal access enabled
`confirmation_email_sent`	BOOLEAN	NO	`false`	Email sent status
`created_at`	TIMESTAMPTZ	NO	`now()`	Record creation timestamp
`updated_at`	TIMESTAMPTZ	NO	`now()`	Last update timestamp

Indexes:

Primary key on id
Unique constraint on (event_id, unique_id)
Unique constraint on (event_id, email)

Triggers:

update_attendees_updated_at - Auto-updates updated_at
generate_attendee_unique_id_trigger - Auto-generates unique 8-char ID on insert
trg_maintain_event_attendee_counts - Updates cached counts in events table

Realtime:

Enabled via ALTER PUBLICATION supabase_realtime ADD TABLE attendees

RLS Policies:

Organization owners can manage attendees
Event managers can view and update attendees (for check-in)

The unique_id is auto-generated using uppercase alphanumeric characters (A-Z, 0-9) and is guaranteed unique within each event.

Billing Tables

token_pricing

Tiered pricing configuration for token purchases.

Column	Type	Nullable	Default	Description
`id`	UUID	NO	`gen_random_uuid()`	Primary key
`type`	TEXT	NO	-	Enum: `event`, `attendee`
`min_quantity`	INTEGER	NO	-	Tier minimum quantity
`max_quantity`	INTEGER	NO	-	Tier maximum quantity
`price_per_unit`	DECIMAL(10,2)	NO	-	Price per token in this tier
`currency`	TEXT	NO	`'MYR'`	Currency code
`updated_at`	TIMESTAMPTZ	NO	`now()`	Last update timestamp

Constraints:

type must be in ('event', 'attendee')

Triggers:

update_token_pricing_updated_at - Auto-updates updated_at

RLS Policies:

Anyone authenticated can view pricing
Public (anon) can view pricing
Super admins can manage pricing

Example Data:

-- Event token pricing
(type='event', min=1, max=5, price=50.00)
(type='event', min=6, max=20, price=40.00)
(type='event', min=21, max=100, price=30.00)

-- Attendee token pricing
(type='attendee', min=1, max=100, price=1.00)
(type='attendee', min=101, max=500, price=0.80)
(type='attendee', min=501, max=2000, price=0.60)
(type='attendee', min=2001, max=10000, price=0.40)

token_transactions

Token purchase history.

Column	Type	Nullable	Default	Description
`id`	UUID	NO	`gen_random_uuid()`	Primary key
`organization_id`	UUID	NO	-	Foreign key to `organizations.id`
`type`	TEXT	NO	-	Enum: `event`, `attendee`
`quantity`	INTEGER	NO	-	Number of tokens purchased
`amount`	DECIMAL(10,2)	NO	-	Total amount paid
`currency`	TEXT	NO	`'MYR'`	Currency code
`payment_method`	TEXT	NO	`'stripe'`	Payment method
`stripe_session_id`	TEXT	YES	-	Stripe checkout session ID
`status`	TEXT	NO	`'pending'`	Payment status
`created_at`	TIMESTAMPTZ	NO	`now()`	Transaction timestamp

Constraints:

type must be in ('event', 'attendee')

RLS Policies:

Organization owners can view their transactions
Organization owners can insert transactions
Super admins can view all transactions

Audit & Platform Tables

audit_logs

Activity audit trail for compliance and debugging.

Column	Type	Nullable	Default	Description
`id`	UUID	NO	`gen_random_uuid()`	Primary key
`organization_id`	UUID	YES	-	Foreign key to `organizations.id`
`user_id`	UUID	YES	-	User who performed the action
`action`	TEXT	NO	-	Action name (e.g., ‘event.created’)
`entity_type`	TEXT	YES	-	Entity type (e.g., ‘event’, ‘attendee’)
`entity_id`	UUID	YES	-	Entity ID
`details`	JSONB	YES	-	Additional event details
`created_at`	TIMESTAMPTZ	NO	`now()`	Event timestamp

RLS Policies:

Organization owners can view their audit logs
Super admins can view all audit logs
All inserts go through log_audit_event() function (no direct INSERT policy)

Audit logs are written via the log_audit_event() security definer function, which validates authorization and prevents false attribution.

platform_settings

Global platform configuration (super admin only).

Column	Type	Nullable	Default	Description
`key`	TEXT	NO	-	Primary key (setting name)
`value`	JSONB	NO	-	Setting value
`updated_at`	TIMESTAMPTZ	NO	`now()`	Last update timestamp

Default Settings:

{
  "default_event_tokens": 0,
  "default_attendee_tokens": 0,
  "checkin_deactivation_days": 1,
  "portal_deactivation_days": 3
}

RLS Policies:

Super admins can manage all settings

Storage Buckets

While not database tables, storage buckets are defined in migrations:

org-logos

Bucket ID: org-logos
Public: Yes
Folder structure: {user_id}/filename.ext

Policies:

Organization owners can upload/update/delete logos in their folder
Public read access

branding-assets

Bucket ID: branding-assets
Public: Yes
Folder structure: {event_id}/filename.ext

Policies:

Event owners and assigned event managers can upload/update/delete
Public read access (for rendering confirmation emails and public pages)

Next Steps

Schema Overview

High-level architecture and design principles

Relationships

Foreign keys and table relationships

Overview

Endpoints

Edge Functions

Database Schema

Database Tables

Core Tables

profiles

user_roles

organizations

organization_members

events

event_manager_assignments

attendees

Billing Tables

token_pricing

token_transactions

Audit & Platform Tables

audit_logs

platform_settings

Storage Buckets

org-logos

branding-assets

Next Steps

Schema Overview

Relationships

Build docs developers (and LLMs) love

Overview

Endpoints

Edge Functions

Database Schema

​Core Tables

​profiles

​user_roles

​organizations

​organization_members

​events

​event_manager_assignments

​attendees

​Billing Tables

​token_pricing

​token_transactions

​Audit & Platform Tables

​audit_logs

​platform_settings

​Storage Buckets

​org-logos

​branding-assets

​Next Steps

Schema Overview

Relationships

Build docs developers (and LLMs) love

Core Tables

profiles

user_roles

organizations

organization_members

events

event_manager_assignments

attendees

Billing Tables

token_pricing

token_transactions

Audit & Platform Tables

audit_logs

platform_settings

Storage Buckets

org-logos

branding-assets

Next Steps