Skip to main content

​
Architecture Overview

PassTru uses Supabase (PostgreSQL) as its database backend, featuring a multi-tenant architecture with row-level security (RLS) policies. The schema supports role-based access control, token-based billing, event management, and attendee check-in workflows.

​
Core Design Principles

​
Multi-Tenancy

The database uses an organization-based multi-tenancy model:
  • Each organization is a separate tenant with isolated data
  • Organizations own events and manage attendees
  • Row-level security (RLS) enforces tenant isolation
  • Super admins can access all organizations for platform management

​
Security Architecture

All tables use Row-Level Security (RLS) policies to enforce access control at the database layer, preventing unauthorized data access even if application logic is compromised.
Security Features:
  • SECURITY DEFINER functions for privilege escalation where needed
  • Separate public RPCs for unauthenticated check-in pages
  • Authorization checks in all token deduction functions
  • Audit logging for compliance and debugging

​
Token-Based Billing

PassTru uses a prepaid token system:
  • Event tokens: Consumed when creating a new event (1 token per event)
  • Attendee tokens: Consumed when adding attendees (1 token per attendee)
  • Tiered pricing based on purchase quantity
  • Token balances tracked in the organizations table
  • All transactions recorded in token_transactions

​
Database Schema Layers

​
Layer Descriptions

LayerTablesPurpose
Auth Layerauth.users (Supabase managed)User authentication and session management
User Managementprofiles, user_rolesUser profiles and role assignments
Organizationorganizations, organization_membersMulti-tenant structure and team management
Event Managementevents, event_manager_assignmentsEvent configuration and manager assignments
Attendee ManagementattendeesEvent attendees and check-in status
Billingtoken_pricing, token_transactionsToken-based billing and payment tracking
Auditaudit_logsActivity tracking and compliance
Platformplatform_settingsGlobal platform configuration
StorageStorage bucketsFile uploads (logos, branding assets)

​
Entity Relationships

The database follows a hierarchical ownership model: 
User (auth.users)
  └─> Profile (profiles)
  └─> User Role (user_roles) [super_admin | client | event_manager]
  └─> Organization Owner (organizations.owner_id)
       └─> Events (events)
            └─> Attendees (attendees)
            └─> Event Manager Assignments (event_manager_assignments)
       └─> Organization Members (organization_members)
       └─> Token Transactions (token_transactions)
       └─> Audit Logs (audit_logs)

​
Key Features

​
Draft/Publish Workflow

Events support a draft-publish workflow for branding and content:
  • draft_* columns store work-in-progress content
  • Published columns (branding_portal, checkin_page_content, etc.) serve public pages
  • Changes can be previewed before publishing
Draft Columns:
  • draft_branding_portal, draft_branding_checkin, draft_branding_postcheckin
  • draft_puck_portal_data, draft_puck_checkin_data, draft_puck_post_checkin_data
  • draft_attendee_portal_content, draft_checkin_page_content, draft_post_checkin_content

​
Realtime Updates

The attendees table is enabled for Supabase Realtime, allowing:
  • Live check-in status updates
  • Real-time attendee list refreshes
  • Multi-user collaboration during events

​
Performance Optimizations

Denormalized Counters:
  • events.total_attendees and events.checked_in_count are maintained by triggers
  • Eliminates expensive COUNT queries on the attendees table
  • Atomic updates ensure consistency
Row-Level Locking:
  • Token deduction functions use FOR UPDATE to prevent race conditions
  • Ensures accurate token balances under concurrent access

​
Public Access Functions

Public pages (check-in, attendee portal) use SECURITY DEFINER functions instead of direct table access to prevent data leakage.
Public RPCs:
  • get_org_public_info(_slug) - Safe organization lookup by slug
  • get_event_public_info(_event_slug, _org_id) - Event details for public pages
  • public_atomic_checkin(...) - Single-call check-in with validation
  • get_attendee_portal_data(...) - Attendee portal data with privacy controls

​
Data Types

​
Enums

CREATE TYPE app_role AS ENUM ('super_admin', 'client', 'event_manager');

​
JSON Fields

Several tables use JSONB for flexible schema:
TableColumnPurpose
eventsattendee_fieldsCustom form fields configuration
eventsbranding_*Theme and styling JSON
eventspuck_*_dataPage builder component data
attendeescustom_fieldsCustom attendee data
audit_logsdetailsEvent-specific audit details
platform_settingsvalueConfiguration values

​
Storage Buckets

​
org-logos

  • Public: Yes
  • Purpose: Organization logo uploads
  • Folder structure: {user_id}/{filename}
  • Access: Owners can upload/update/delete, public read

​
branding-assets

  • Public: Yes
  • Purpose: Event branding images (backgrounds, confirmation email images)
  • Folder structure: {event_id}/{filename}
  • Access: Event owners and managers can upload/update/delete, public read

​
Next Steps

Tables Reference

Detailed column specifications for all tables

Relationships

Foreign keys and table relationships

Build docs developers (and LLMs) love

Get started for freeTalk to us