Data Model
Tap uses 7 PostgreSQL tables hosted on Supabase. The schema supports multi-tenancy through organizations, campaign lifecycle management, and conversational data storage with AI analysis caching.
Entity-Relationship Diagram
The diagram below shows all 7 tables and how they relate to each other. An organization has members who create campaigns. Campaigns have participants who engage in conversations made up of messages. Analysis results are cached per campaign.
Table Descriptions
organizations
Represents a company or team workspace. Users can belong to an organization to share campaigns and collaborate.
organization_members
Join table linking users to organizations with a role. Roles are owner (full control), admin (manage campaigns), and member (view only).
campaigns
The core entity. A campaign represents a single feedback initiative — it has a goal (mission), an opening question, a list of participants, and configurable settings. Campaigns progress through a lifecycle: draft → active → paused → completed → archived.
participants
People invited to provide feedback. Each participant gets a unique token that lets them access their conversation without logging in. Their status tracks where they are in the process.
conversations
Links a participant to a campaign with conversation state tracking. A conversation is active while the participant is chatting, completed when the AI ends it, or abandoned if dropped.
messages
Individual messages within a conversation. The sender field distinguishes between participant responses and AI-generated follow-up questions (bot messages).
campaign_analysis
A cache table that stores AI-generated analysis results. It has a one-to-one relationship with campaigns (unique constraint on campaign_id). Stores structured data including key findings, conversation groupings by theme, sentiment distribution, extracted themes, and a full executive summary.
Security
Tap uses Supabase Row-Level Security (RLS) to enforce data isolation:
- Campaign creators can only see their own campaigns (or their organization's campaigns)
- Organization members can only see members of organizations they belong to
- Participants access conversations via their unique token — no database-level auth needed
- The backend uses a service role key to bypass RLS for admin operations (sending emails, updating metrics)
Migrations
The schema has evolved through 5 migration files:
| Migration | Purpose |
|---|---|
add_auth.sql | Auth and multi-tenancy setup |
add_analysis_cache.sql | Analysis cache table |
add-mission-summary.sql | Mission summary column on campaigns |
add-archived-field.sql | Archive support for campaigns |
fix-rls-recursion.sql | Fixed recursive RLS policy bug |