Data Model
Tap uses 10 PostgreSQL tables hosted on Supabase. The schema supports multi-tenancy through organizations, campaign lifecycle management, and conversational data storage with AI analysis caching, and developer metrics ingestion.
Entity-Relationship Diagram
The diagram below shows all 10 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. Developer metrics are tracked via ingestion runs linked to canonical developer identities.
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.
developer_identities
Maps different identifiers (GitHub login, email) to a canonical developer record. A single developer may appear in GitHub Copilot exports by login and in Claude Code exports by email -- this table unifies them so metrics from different tools are attributed correctly.
daily_developer_metrics
Stores daily productivity metrics per developer -- acceptances, suggestions, completions, and sessions. Each row is tied to a developer identity and an ingestion run for auditability. Uses a unique constraint on (developer_identity_id, date) to prevent double-counting.
metric_ingestion_runs
Audit trail for metrics uploads. Records the file name, processing status, row counts, and any errors for each upload. Linked to the uploading organization for multi-tenant isolation.
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 multiple 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 |
20260213_cohort_analytics_schema.sql | Developer metrics tables and identity resolution |