Contacts — Technical Reference

This page covers the data models, workspace isolation, and import architecture behind the Contacts feature.

Data Models

contacts

Unique constraint: UNIQUE(workspace_id, email) — one contact per email per workspace.

transactions

Unique constraint: UNIQUE(external_stripe_transaction_id) — prevents duplicate purchase recording.

Architecture

Workspace Isolation

Contacts are strictly isolated by workspace. Two workspaces can each have a contact with jane@example.com — they are completely independent records. This isolation is enforced at the database level using PostgreSQL Row-Level Security (RLS), meaning it cannot be bypassed by application bugs.

Contact Source Enum

The source field tracks how the contact entered the workspace:

If no source is provided during creation, it defaults to manual.

CSV Import Pipeline

The CSV import runs as a single atomic database transaction. If any critical error occurs, everything rolls back — no partial data is committed.

Upsert strategy — keyed on workspace_id + email:

1. Parse and validate each row (email required, birthday validation)
2. For each row, check if a contact with that email already exists
3. New contacts → INSERT with all provided fields
4. Existing contacts → UPDATE only non-empty CSV columns (blank columns are skipped to prevent accidental data erasure)
5. Process tags: create missing tags, insert contact_tags records with ON CONFLICT DO NOTHING
6. Process notes: insert notes records with type 'contact'
7. If "Mark as Subscribed" is enabled, record consent proof (preserving existing consent for already-subscribed contacts)

Birthday validation:

• birth_month and birth_day must both be provided or both omitted
• Valid ranges: month 1–12, day 1–31
• Invalid values are skipped per-row with an error logged

Error handling:

• Invalid rows are skipped, not failed
• Error report includes row number and reason for each skip
• All valid rows are processed regardless of individual row failures

Email Change Behavior

When a contact's email is updated via updateContact():

• Duplicate Email Prevention: The server action catches database unique constraint violations (Postgres error code 23505 on the (workspace_id, email) composite key) and returns a friendly error message: "A contact with this email already exists in this workspace."
• Suppression Cleanup: bounce suppressions are automatically deleted (inbox reputation resets with the new address).
• Suppression Preservation: complaint and unsubscribe suppressions are explicitly preserved (respecting human preferences).

See Subscriptions & Consent — Technical Reference for the full suppression lifecycle.

Contact Detail Query (getContactById)

To populate the Contact Detail view, the getContactById server action retrieves the contact record and performs database joins to resolve:

• updated_by_profile: Resolves the coworker profile (first_name, last_name) who last modified the contact record.
• Creator Details: Resolves the author profile if the contact was manually created.

These fields feed the unified vertical audit trail in the UI, displaying the creators/modifiers and dates clearly.

Consent Audit Lifecycle

Marketing consent changes (opt-in or opt-out) write records to a dynamic consent audit timeline:

• Tracking Variables: Captures the opt-in timestamp, the source channel (e.g. Stripe checkout, Eventbrite webhook, manual dashboard consent checkbox), and the client IP address.
• Audit Trails: Rendered chronologically in the timeline, ensuring that every transition in a contact's email status is logged with audit proof.

Stripe Webhook Ingestion

The /api/webhooks/stripe endpoint handles incoming Stripe Connect checkout events (checkout.session.completed) to automatically populate contact records and purchase ledgers:

• Workspace Resolution: In production, the workspace is resolved via the Connect platform's stripe_account_id matching a record in the stripe_connections table. In development or test environments where event.account may be absent, a fallback mechanism attempts to match the workspace by querying stripe_connections for a single record matching the event's livemode.
• Data Capture: Ingests the customer's email, name, phone, and full billing address (address_line1, address_line2, city, state, postal_code, country) from Stripe's customer_details.

  [!TIP] For the phone and billing address fields to sync, administrators must configure their Stripe Payment Links in the Stripe Dashboard to "Require phone number" and "Collect billing address".

• Transaction Recording: Upserts a record in the transactions table mapping the purchase to the resolved contact. This captures the product ID, currency, amount paid, and stages the Stripe Payment Intent ID (stripe_payment_intent_id) to enable subsequent refund tracking. If the checkout represents a free or fully discounted order, the Payment Intent ID is recorded as null.
• Auto-Subscription Opt-In: If the workspace's Stripe Connection has auto_subscribe_purchasers enabled, the webhook automatically attempts to opt the purchaser into marketing emails:
  • Spam Complaint Guard: If the contact has an active permanent complaint or manual block (manual) suppression, the auto-subscribe action is bypassed.
  • Opt-in Chain of Custody Preservation: If the contact is already cleanly subscribed (is_subscribed = true and unsubscribed_at is null), the system skips updating opt-in metadata (preserving opt_in_source, opt_in_timestamp, and opt_in_ip for compliance).
  • Subscription State: For eligible purchasers who are not subscribed, the system sets is_subscribed = true, sets opt_in_source = 'stripe_auto_subscribe', records the opt-in timestamp, and clears unsubscribed_at.
  • Suppression Cleanup: Automatically deletes active non-hard suppressions (bounce, unsubscribe) for the contact, resetting their email eligibility.
• Intelligent Overwrite Guard: Rather than using a blind upsert, the CRM uses a fetch-then-conditional-update strategy to avoid overwriting existing contact details:
  • New Contacts: A new contact is created with all available Stripe information. No audit logs are created.
  • Existing Contacts: The CRM compares incoming Stripe data against the existing record, using the same diff-and-audit mechanism described below.

System Notes and Detail Overwrite Audits (diffAndUpdateContact)

To ensure that automated data ingestion does not silently overwrite existing contact details, the CRM utilizes a shared helper diffAndUpdateContact (in src/lib/utils/contact-diff.ts) during real-time contact creation and update paths:

• Diff Tracking: Compares incoming fields (first_name, last_name, phone) against the existing record. US phone numbers are normalized (digits stripped, leading +1 prefix removed for 11-digit numbers) before comparison to prevent false-positive change detections.
• Single Merged Write: Consolidates all changed fields with any additional updates (e.g. consent fields or source) and runs a single update query to minimize database load.
• System Notes: If any existing non-empty fields are overwritten, the helper automatically writes a dual-write (HTML + plain-text) system note to the notes table (setting created_by = NULL), which renders in the client timeline under the author "⚡ System".
• Application Paths: Integrated into Form Submissions (POST /api/forms/[id]), Form Verification (GET /api/forms/verify), Eventbrite Registration Ingestion (syncOrderAttendees), and Eventbrite Attendee Updates (handleAttendeeUpdated). It is bypassed in bulk import paths (CSV import, historical Eventbrite backfills) to optimize performance.
• Concurrency & Race Conditions: Webhook and public API routes wrap contact creation in a retry loop. If a unique constraint violation (error code 23505) occurs (because the contact was created between the initial check and insert), the code catches the error, re-fetches the existing contact, and routes updates through diffAndUpdateContact.

Stripe Refund Reconciliation

The CRM handles the charge.refunded Stripe webhook event using an automated 3-Tier Refund Matching Engine to reconcile purchases and update the transactions ledger.

The 3-Tier Matching Engine

When a refund is initiated in Stripe, the webhook maps the event to CRM transaction rows using the stripe_payment_intent_id (Stripe Payment Intent ID). The engine processes the refund in one of three tiers based on the refund amount and remaining balances:

1. Tier 1: Full Refund

   • Trigger: The refund amount (delta) is equal to or greater than the total remaining un-refunded balance across all candidate transaction rows for that Payment Intent.
   • Action: All candidate transaction rows are marked as 'refunded' with amount_refunded set to their full amount_paid.
   • Side Effects: Fires the product_refunded automation trigger for each refunded product. Auto-closes any open refund review tasks for this payment intent using the reason: "Automatically closed — a full refund was processed in Stripe for this transaction."

2. Tier 2: Exact Single-Product Match

   • Trigger: The refund amount matches the remaining un-refunded balance of exactly one transaction row for that Payment Intent.
   • Action: That matching transaction row is updated to 'refunded' with its amount_refunded set to its full amount_paid.
   • Side Effects: Fires the product_refunded automation trigger for the matched product. Auto-closes open review tasks for this payment intent using the reason: "Automatically closed — all refunds for this transaction have been reconciled."

3. Tier 3: Ambiguous or Partial Refund

   • Trigger: The refund amount does not match any single product's remaining balance, or it matches multiple products' remaining balances (making auto-resolution ambiguous).
   • Action: The system generates a manual "Review Refund" task assigned to the workspace owner.
   • Side Effects: Auto-closes (supersedes) previous open review tasks for the same payment intent. No product_refunded automation is triggered at this stage, as the refund must be manually reconciled.

Review Task UX & Timezone-Aware Due Dates

For Tier 3 review tasks, the CRM creates a detailed, user-friendly task description containing:

• Original purchase date and list of products (with already-refunded items clearly annotated).
• An explanation of why the refund could not be auto-applied (e.g. amount did not match any single product, or matched multiple).
• Clear action options (A: process additional refund in Stripe, B: navigate to contact profile and record partial refund).
• Timezone-Aware Due Date: The review task is set as due "tomorrow", with the due date calculated using the workspace owner's IANA timezone (from user_profiles.timezone) to prevent UTC drift. The date is stored as a YYYY-MM-DD date-only string in the tasks table.

Task Auto-Closure & System Audit Notes

When a refund is reconciled (Tier 1/2) or updated (Tier 3), any open refund review tasks associated with the stripe_payment_intent_id are automatically closed. The auto-closure logic:

• Sets the task's status to 'completed' and updates the completed_at timestamp.
• Inserts a system-authored audit note in the notes table with type 'task' (with created_by = NULL, which renders as "⚡ System" in the UI) explaining the reason for closure.

Checkout Idempotency Hardening

To prevent duplicate data recording and accidental automation triggers from replayed Stripe webhook events:

• If the transaction is already recorded, the webhook is a complete no-op (no duplicate transaction records, no duplicate automation rule executions, and no timestamp modifications).

Administrative Stripe Backfill Infrastructure

To reconcile historical records that predate the Stripe webhook integration, the CRM provides administrative backfill endpoints and a local CLI orchestration script. These tools reconstruct the database ledger without causing duplicate triggers or user alerts.

1. Payment Intent Backfill Endpoint (GET /api/admin/backfill-payment-intents)

• Purpose: Retrieves Stripe Payment Intent IDs for historical transaction rows lacking one.
• Mechanism: Parses the composite external ID key (external_stripe_transaction_id format: [session_id]_[product_id]) to extract the Stripe checkout session ID. It then queries the Stripe API (checkout.sessions.retrieve()) to fetch the corresponding payment intent.
• Self-Draining Offset-0 Pagination: Queries rows where stripe_payment_intent_id IS NULL. To ensure the query pool drains even when errors occur, the endpoint writes one of four sentinel values to the database:
  • Real pi_xxx value: Successfully matched.
  • 'none': Free order or 100% discount checkouts.
  • 'error_fetching': Stripe API or network lookup failure.
  • 'invalid_format': Composite key format mismatch.
• Tenant Isolation & Security: Scoped with a Stripe-Account Connect header. It enforces a disconnected workspace check, failing immediately if the workspace does not have a Stripe connection.

2. Refund Backfill Endpoint (GET /api/admin/backfill-refunds)

• Purpose: Retroactively synchronizes Stripe refunds with the database transactions ledger.
• Mechanism: Queries Stripe's refunds API with optional date range query parameters (since and until Unix timestamps) to fetch charges. It builds the brackets query parameters (created[gte] and created[lte]) manually to prevent standard URLSearchParams formatting errors.
• Processing: Deduplicates refund events by charge ID. For each charge, it retrieves the full Stripe charge resource and passes it through the shared 3-Tier Refund Matching Engine.
• No Side-Effects: Enforces skipAutomations = true, silencing campaign auto-enrollments, internal notifications, and review task creations.
• Cursor-Based Pagination: Returns a nextCursor value mapping to Stripe's starting_after cursor parameter for sequential execution.

3. CLI Orchestration Script (scripts/run-backfill.js)

• Purpose: Local administrative script to orchestrate the backfill stages.
• Supabase Service Role Bypass: Authenticates using the Supabase Service Role key (SUPABASE_SERVICE_ROLE_KEY), bypassing Row-Level Security (RLS) to modify database records directly.
• Orchestration Sequence:
  1. Runs the payment intent backfill loop until the remaining count reaches 0.
  2. Runs the refund backfill loop with cursor pagination to reconcile all historical refunds.
• Robustness: Manages Stripe API rate-limits, handles network retries, and outputs formatted console progress metrics.

[!NOTE] Detailed operational instructions, dev and production runbooks, environment variable requirements, and troubleshooting tables for the backfill script are documented inside the repository at scripts/README.md.

Unified Transactions Feed

The getContactTransactions() server action builds a unified transaction feed for the contact details page. This feed integrates:

1. Direct purchases from the transactions table (such as Stripe product purchases).
2. Paid event registrations from the event_registrations table.
3. Completed payments from the deal_payments table (where the deal is associated with the contact and the payment status is 'paid'). These entries are formatted as [Deal Title] — [Payment Label].

All entries are merged and sorted chronologically (newest first) by the payment or creation date.

Phone Number Formatting

For display purposes across the CRM dashboard (such as the contacts list, contact detail card, and import preview tables), phone numbers are normalized using a cosmetic utility:

• US Numbers: US country code numbers in E.164 format (+1XXXXXXXXXX) and other 10-digit formats are formatted as (XXX) XXX-XXXX.
• Other Formats: Non-US or unrecognized number formats are rendered as-is.
• Database Scope: This formatting is purely cosmetic and does not alter the raw stored text in the Supabase database or editing forms.

Contact List Query & Email Status Badges

The /contacts list page queries contact profiles along with their associated tags and suppression logs in a single query. The 5-tier email eligibility state is calculated dynamically on the client side at runtime using a priority hierarchy:

1. Spam: Contact has a suppression record with reason 'complaint' (rendered in dark red).
2. Bounced: Contact has a suppression record with reason 'bounce' (rendered in orange).
3. Unsubscribed: Contact's unsubscribed_at timestamp is set (rendered in red).
4. Subscribed: Contact has is_subscribed = true (rendered in green).
5. Not Opted In: Default fallback state (rendered in slate).

getContacts Server Action

The getContacts server action retrieves a paginated, filtered, and sorted list of contacts.

export interface ContactListParams {
    page?: number;
    pageSize?: number;
    search?: string;
    tagId?: string;
    tagIds?: string[];
    tagMode?: "any" | "all";
    excludeTagIds?: string[];
    source?: string;
    eventId?: string;
    sortBy?: string;
    sortDir?: "asc" | "desc";
}

Filtering Parameters:

• tagIds: An array of tag UUID strings to filter contacts by.
• tagMode: Determines how multi-tag selection is resolved. Supports "any" (default) or "all".
• excludeTagIds: An array of tag UUID strings representing contacts to exclude.
• source: An exact-match source string (e.g. 'manual', 'form', 'import').
• tagId: Legacy. Preserved single-tag filter parameter for backward compatibility (e.g., when deep-linking from events).
• eventId: Filters contacts registered for a specific event.

Tag Filtering Resolution:

The server action processes tag filters sequentially using in-memory set algebra:

1. Multi-tag AND (tagMode = 'all'): For each tag in tagIds, it queries contact_tags for matching contact_id arrays. It then computes the intersection of these arrays (contacts must possess all selected tags).
2. Multi-tag OR (tagMode = 'any'): Queries contact_tags matching any tag in tagIds using .in() and deduplicates the resulting contact_id array in memory (contacts must possess at least one selected tag).
3. Tag Exclusion (excludeTagIds): Queries contacts matching any excluded tag and generates a subtraction set. If include tags were active, it subtracts these IDs from the included contact ID set. If no include tags were active, it fetches all contact IDs in the workspace and subtracts the excluded set from it.

createContact Server Action

The createContact server action creates a new contact record in the workspace:

export async function createContact(params: {
    workspaceId: string;
    email: string;
    firstName?: string;
    lastName?: string;
    phone?: string;
    addressLine1?: string;
    addressLine2?: string;
    city?: string;
    state?: string;
    postalCode?: string;
    country?: string;
    birthMonth?: number;
    birthDay?: number;
    isSubscribed?: boolean;
}): Promise<{ data?: ContactRecord; error?: string }>

• Validation: Enforces unique email checks per workspace and rejects birth month/day mismatches.
• Fields Capture: Natively supports full address fields and birthday month/day parameters on initial creation.

updateContactSource

The updateContactSource action manually updates a contact's original entrance source:

export async function updateContactSource(
    contactId: string,
    workspaceId: string,
    newSource: string,
    reason: string
): Promise<{ success?: boolean; error?: string }>

• Role-Based Access Control (RBAC): Validates that the executing user's role is either 'owner' or 'admin' within the workspace, rejecting the operation with Insufficient permissions otherwise.
• System Audit Note: On success, inserts a system-authored audit note (createSystemNote) in the contact's timeline, logging the change (e.g. 🔄 Source manually changed from '{old}' to '{new}' by {user}. Reason: {reason}).

getDistinctSources

The getDistinctSources action retrieves all unique contact sources present in the workspace:

export async function getDistinctSources(workspaceId: string): Promise<{
    data: string[];
}>

It queries the contacts table for all non-null source values, filters out blanks, deduplicates them in-memory (to bypass PostgREST's lack of SELECT DISTINCT support), sorts the values alphabetically, and returns them to populate the filter builder radio picker.

Sorting Parameters:

• sortBy: Specifies the column to sort by. Supported values:
  • "name": Sorts alphabetically by first_name ascending/descending, then by last_name ascending/descending.
  • "source": Sorts alphabetically by the contact creation source (e.g. 'manual', 'form', 'import').
  • "created_at" (default): Sorts by contact creation date (created_at).
• sortDir: The sort direction ("asc" or "desc"). Defaults to "desc".

recordTransactionRefund

The recordTransactionRefund action records a full or partial refund for a specific product purchase transaction:

export async function recordTransactionRefund(params: {
    transactionId: string;
    refundAmount: number; // Refund amount in dollars
    runAutomations: boolean; // Whether to fire trigger side effects (full refunds only)
}): Promise<{ success: boolean; error?: string }>

• Validation: Ensures the refund amount is greater than 0 and does not exceed the transaction's remaining balance. It clamps the value using cents-based calculation (Math.round(amount * 100)) to prevent floating-point overshoot.
• Ledger Update: Updates amount_refunded (reconciling with cents math) and transitions status to 'refunded' if the remaining balance reaches zero.
• Automation Side Effects: If runAutomations is set to true and the transaction is fully refunded, it fires the product_refunded automation trigger.
• Task Auto-Closure: If a payment intent is fully balanced (all sibling transactions are fully refunded), it auto-closes any open refund review tasks associated with the payment intent.

revertTransactionRefund

The revertTransactionRefund action reverts a transaction's refund status:

export async function revertTransactionRefund(
    transactionId: string
): Promise<{ success: boolean; error?: string }>

• Ledger Reset: Resets the transaction's status to 'succeeded' and clears the amount_refunded column to 0.

Related Entity Connections

A contact can be connected to many entities through foreign key relationships:

Security

RLS Policies

Transactions RLS Policies

Database Triggers

contacts_protect_source (Migration 079)

To ensure the integrity of ingestion tracking, a BEFORE UPDATE trigger is registered on the contacts table to prevent automated processes from overwriting a contact's original source column:

• Automated context block: Webhooks, form endpoints, and sync scripts authenticate using Supabase's service role key (createAdminClient()), which does not set an active user session (auth.uid() = NULL). If OLD.source is set, the trigger silently forces NEW.source := OLD.source.
• Manual override allowance: If auth.uid() is present (meaning a workspace member initiates the update through an authenticated client session) and the user has 'owner' or 'admin' role, the trigger permits the source change.

Performance & Hydration Optimization

Server/Client Page Split & Lazy Loading

To eliminate initial-load waterfalls and optimize performance, the contacts directory has been split into dedicated server and client layers:

• Server Component (pages/contacts/page.tsx): Resolves cookies and active workspace context, fetching only page 1 data and basic metadata in a single server-side step to support fast initial HTML delivery.
• Client Component (contacts-client.tsx): Handles client state, search input filters, active query parameter parsing, sorting headers, and table pagination.
• Dynamic Lazy Loading: Defer chunk loading for heavy dialogs (AddContactDialog, ImportCsvDialog, and ImportResultsDialog) using next/dynamic dynamic imports with ssr: false. These components are only downloaded and mounted when a user clicks the respective action button, reducing the bundle size of the initial contacts dashboard.
• Hydration & Flash Guards:
  • isFirstQueryRef Guard: A client-side mounting reference guard that prevents firing duplicate data fetch requests during initial mounting.
  • Workspace Sync Gate: When switching workspaces, a useEffect synchronization hook compares the active workspace ID and resets pagination index and query filters only when a workspace transition occurs, preventing UI flashes.
  • DOM Hydration Warning Fix: Replaced nested div containers with block span components inside dialog descriptions to enforce strict HTML validation and prevent React hydration mismatch warnings.

Mobile Viewport Containment & Scroll-Spy Navigation

To resolve horizontal page overflows and coordinate sticky navigation across contact detail pages on small screens:

• Global Viewport Containment: Added overflow-x-hidden on the document body to contain horizontal page drift without breaking sticky positions.
• Flex Container Shrinkage: min-w-0 is propagated down layout containers, grid rows, and card headers to let flex/grid items shrink cleanly on small screens, preventing card blowouts. Card lists (Tasks, Courses, Agreements, etc.) are insulated with w-full and overflow-x-hidden to force text truncation.
• Coordinate-Based Navigation: Native .scrollIntoView() is replaced with coordinate-based .scrollTo() calculations in React and vanilla click handlers to eliminate jitter. Viewport middle comparisons trigger tab transitions:
  • Top edge crossing the middle of the viewport scrolling down triggers a forward tab transition.
  • Bottom edge crossing the middle of the viewport scrolling up triggers a backward tab transition.
  • Calculation removes dependency on header height lookups.
• Scroll Lock Safety: The programmatic scroll-lock safety timeout is increased to 2000ms.
• Vanilla Bootstrapper: An inline <script> tags block installs lightweight click handlers and scroll tracking before hydration to ensure instant mobile utility. The vanilla scroll handler is cleanly unbound upon React mount to prevent event duplication or race conditions.
• Coordination: Uses suppressHydrationWarning on root divs to permit pre-hydration DOM mutations by the bootstrapper without React runtime errors.