Notes — Technical Reference

This page covers the data models, query architecture, and security policies behind the Notes feature.

Data Model

All contact, task, and appointment notes are stored in a single unified notes table. Entity relationships are managed dynamically via a note_type discriminator and polymorphic foreign keys.

notes

Polymorphic Foreign Key Check: The database enforces that exactly one foreign key matching the note_type is non-null through the notes_entity_fk_check constraint:

CONSTRAINT notes_entity_fk_check CHECK (

CASE note_type WHEN 'contact' THEN contact_id IS NOT NULL AND task_id IS NULL AND appointment_id IS NULL WHEN 'task' THEN task_id IS NOT NULL AND contact_id IS NULL AND appointment_id IS NULL WHEN 'appointment' THEN appointment_id IS NOT NULL AND contact_id IS NULL AND task_id IS NULL END )

**Thread Reply Depth Check**:
Rejects nested replies-to-replies (limiting depth to exactly 1 level) using the `trg_check_note_reply_depth` trigger before INSERT or UPDATE operations:
```sql
CREATE OR REPLACE FUNCTION check_note_reply_depth() RETURNS trigger AS $$
BEGIN
  IF NEW.parent_id IS NOT NULL THEN
    -- Parent note must not itself be a reply (parent_id of parent must be NULL)
    IF EXISTS (SELECT 1 FROM notes WHERE id = NEW.parent_id AND parent_id IS NOT NULL) THEN
      RAISE EXCEPTION 'Thread reply depth exceeded. Re-nesting replies is not permitted.';
    END IF;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Thread Lookup Optimization: A partial index idx_notes_parent_id speeds up replies fetching:

CREATE INDEX idx_notes_parent_id ON notes(parent_id) WHERE parent_id IS NOT NULL;

note_templates

Workspace-scoped reusable content scaffolds for pre-filling the note editor.

Index: idx_note_templates_workspace on workspace_id.

Architecture

Unified Contact Notes Feed (get_contact_timeline)

Instead of making multiple waterfall queries on the frontend, the CRM aggregates a contact's timeline feed in a single database RPC call: get_contact_timeline(p_contact_id).

The function queries the unified notes table and locates relevant records through three distinct paths:

1. Direct Notes: Notes where notes.contact_id = p_contact_id.
2. Task-linked Notes: Notes associated with tasks assigned to the contact.
3. Appointment-linked Notes: Notes associated with appointments where the contact is registered as an external attendee.

Database Function Query

SELECT
    n.id,
    n.parent_id,
    n.note_type,
    n.note_text,
    n.note_content_html,
    n.pinned_at,
    n.created_at,
    up.first_name AS author_first_name,
    up.last_name AS author_last_name,
    t.id AS task_id,
    t.title AS task_title,
    a.id AS appointment_id,
    a.title AS appointment_title
FROM notes n
LEFT JOIN user_profiles up ON n.created_by = up.id
LEFT JOIN tasks t ON n.task_id = t.id
LEFT JOIN appointments a ON n.appointment_id = a.id
WHERE n.contact_id = p_contact_id
   OR n.task_id IN (SELECT tid.id FROM tasks tid WHERE tid.contact_id = p_contact_id)
   OR n.appointment_id IN (SELECT ea.appointment_id FROM appointment_external_attendees ea WHERE ea.contact_id = p_contact_id)
ORDER BY n.pinned_at DESC NULLS LAST, n.created_at DESC;

Rich-Text Editor

Notes across all CRM modules (contacts, tasks, appointments, and deals) use the @tiptap/react rich-text editor (RichTextEditor) with the following extensions: Bold, Italic, Strike, Heading (H1–H2), BulletList, OrderedList, TaskList, TaskItem, Blockquote, CodeBlock. The editor stores both note_text (plain text via editor.getText()) and note_content_html (HTML via editor.getHTML()). The plain-text version powers search and previews, while the HTML version is rendered via RichTextDisplay for formatting.

HTML Content Trimming & Cleanup

To prevent database clutter from empty paragraphs, trailing line breaks, or accidental user keystrokes, the server-side utility trimHtmlContent() is run on all note creation and updates:

• Whitespace & Tag Stripping: Removes leading/trailing empty paragraph blocks (<p></p>), space characters, non-breaking spaces ( ), zero-width spaces (​, \u200B), and line breaks (<br>) from the beginning and end of the document.
• Internal Trimming: Cleans up leading and trailing whitespaces/breaks from the content inside the very first and very last paragraph blocks.
• Empty Note Detection: If a note contains no readable text after tag stripping, the utility returns an empty string "" to prevent storing empty/phantom records (unless the HTML contains media elements like images, horizontal rules, or iframes).

Collapsible Note Content & Text Truncation (CollapsibleNoteContent)

To render long notes and reply threads cleanly without cluttered overflows or flickering layouts, the CRM encapsulates content layout inside a dedicated CollapsibleNoteContent component:

• Element-Scoped ResizeObserver: Replaces legacy character-count limiters with a dynamic ResizeObserver bound to individual DOM elements. Height checks measure the actual rendered layout space, accounting for text wrapping and custom CSS styling.
• Pre-Paint Height Measurement: Utilizes the useIsomorphicLayoutEffect hook to perform synchronous DOM measurements (scrollHeight > clientHeight) before the browser paints. This eliminates visual layout shift and prevents split-second "Show more" button flashes.
• State and Render Isolation: Moves expansion toggles and DOM measurements into the self-contained CollapsibleNoteContent component. This prevents parent-level re-render cascades when expanding/collapsing note text and applies equally to top-level parent notes and nested reply threads.

Active Modules Render Loop Resolution

To resolve infinite rendering loop conditions on the /notes dashboard, the activeModules feature configuration object is stabilized using React's useMemo hook. This prevents the recreation of the loadNotes callback reference on every render cycle, stopping the infinite cascade of effect triggers and Supabase RPC calls.

User Mentions and Notification Dispatch

When a note is created or updated:

• Mention Extraction: The server-side utility extractMentionUserIds(html) parses Tiptap's HTML structure to find <span data-type="mention" data-id="..."> tags, collecting the IDs of all mentioned workspace users.
• Self-Mention Guard: Users are blocked from receiving notifications for mentioning themselves.
• Edit-Diff Filtering: On note updates, the system compares the new mention list against the old note HTML. Notifications are dispatched only to newly mentioned users, preventing redundant alerts when correcting typos.
• Concurrent Dispatch: The server action resolves user profile names and links concurrently via Promise.allSettled to prevent network call waterfalls.
• Deep Linking: Notifications include contextual action links (View Contact, View Task, or View Appointment) pointing directly to the entity. For tasks, this includes a specific ?taskId={ID} query parameter.

Deep-Link Navigation Handling

To prevent race conditions during deep-link page loads, the Tasks page filter initialization has been refactored:

• Filter Widening: When a ?taskId={ID} query parameter is present, the default "Mine" filter is widened to "Everyone's" to ensure the target task is visible and can be successfully auto-opened in the detail drawer.

User Mentions & Reply Email Delivery & Daily Digest Cron

The @mention and threaded note replies systems integrate email notifications based on user preferences stored in user_profiles.mention_email_pref ('immediate', 'digest', or 'never'):

Note Reply Participant Discovery & Notification Engine

When a note reply is added via addNote with a parentId:

• Property Inheritance: The reply automatically inherits metadata (workspace_id, note_type, and associated entity foreign keys like contact_id, task_id, appointment_id, deal_id) server-side from the parent note.
• Participant Discovery (Model C): The engine searches the thread hierarchy to discover participants, notifying the parent note author, all subsequent reply authors, and any @mentioned users in the thread.
• De-duplication: Users mentioned directly in the reply take notification priority. The engine ensures a user receives exactly one email notification per note transaction (preventing double notifications if they are both a thread participant and mentioned).
• Email Copy Routing: Uses the same preference-based routing ('immediate', 'digest', 'never') but flags the email template with isReply = true to tailor the copy.

Immediate Email Dispatch

When dispatchMentionNotifications or dispatchReplyNotifications is called, the system performs a batched lookup of mention_email_pref to optimize database calls. For users set to 'immediate':

1. The system builds a styled HTML email using mention-template.ts (the emerald-header design system).
2. The email subject is normalized using a unified structure ([Gordon CRM] Note on {entityName}) to enable clean client-side threading in Gmail and Outlook.
3. The email is sent immediately using the Resend service.
4. The database row's email_sent column is flagged as true.

For users set to 'never', the database email_sent column is set to true (acting as a sentinel to skip the cron sweeper). For users set to 'digest', the database email_sent column remains false.

Daily Digest Cron (/api/cron/mention-digest)

For users who prefer a summary, a Vercel cron job sweeps unread mentions and note replies (notification types 'mention' and 'reply').

• Schedule: Runs daily at 14:00 UTC (8:00 AM Central) (configured in vercel.json):

  {
    "path": "/api/cron/mention-digest",
    "schedule": "0 14 * * *"
  }
• Digest Email Subject: Sent with the subject [Gordon CRM] {X} unread note(s) where X is the total count of unread notifications.
• Exclusion Logic: Already-read notifications are filtered out of the digest using a SQL NOT EXISTS check against notification_reads.
• Grouping & Templating: The digest aggregates the unread mentions and replies by entity type (Contact, Task, Appointment) in a single unified email listing all comments needing review.
• Pre-Processing Cleanup: A bookkeeping step automatically marks stale (already-read) notification rows as email_sent = true to optimize future cron sweeps and prevent database scanner bloat.

Colored-Dot Icon System

Note severity and categorizations are represented visually in note lists and feeds using a standardized colored-dot indicator:

• 🟢 Green: Standard user-created operational notes.
• 🟡 Amber: Informational messages or reminders.
• 🟠 Orange: Warning logs or transactional updates (e.g. Stripe checkout diff audit trails).
• 🔴 Red: Critical blocks or permanent restrictions (e.g. manual blocking logs).

System Note Immutability

All notes automatically generated by the CRM (created_by = NULL, authored by "⚡ System") are strictly read-only to preserve a reliable audit trail:

• Restriction: System notes cannot be edited, deleted, or pinned/unpinned in the UI or API.
• Actions: The inline edit and delete actions are hidden for these timeline items, and the server actions updateNote, deleteNote, pinNote, and unpinNote reject requests targeting system-generated notes.

Global Search Integration

Notes are included in the global_search RPC function, returning up to 5 results of matching notes.

• Interleaved Type Diversity: The search query uses a ROW_NUMBER partitioning strategy to ensure representation across all note types (contact, task, appointment) within the top 5 results:

  ROW_NUMBER() OVER (PARTITION BY n.note_type ORDER BY n.created_at DESC) AS rn
• Module Gating: Appointment notes are filtered out if the Appointments module is disabled for the workspace.

Unified Notes Search RPC (search_notes)

The /notes page and contact detail notes filters utilize a unified database function search_notes(p_workspace_id, p_query, p_type, p_contact_id, p_modules, p_limit, p_offset) to query and paginate across the unified notes table in a single round-trip.

• Search Parameters:
  • p_query: Matches note content plain text using trigram ILIKE searches.
  • p_type: Filters by entity type ('contact', 'task', or 'appointment').
  • p_contact_id: Pre-filters notes linked to a specific contact.
  • p_modules: Respects feature toggles (appointment notes are excluded if the appointments module is disabled).
  • p_limit / p_offset: Supports server-side pagination (default limit 25).
• Returned Columns: Returns standard note fields plus parent_id and parent context details resolving the parent thread's metadata:
  • parent_note_text: Text snippet of the parent note.
  • parent_author_first_name / parent_author_last_name: First and last name of the parent note's author.
• Trigram GIN Index: The search query is accelerated by a single unified trigram GIN index (idx_notes_text_trgm) matching on note_text.
• Lateral Join Execution: Patched to query parent contexts using LATERAL JOINs to safely retrieve primary attendee details without duplicating note records.
• Ordering: Results are returned in order of pinned notes first (pinned_at DESC NULLS LAST), then by creation time (created_at DESC).

Note Templates

Server Actions (src/lib/actions/notes.ts):

Lazy seeding: On the first getTemplates call that returns 0 rows, seedDefaultTemplates inserts the 3 default templates (Meeting Notes, Follow-Up Summary, Discovery/Intake) using the current user's ID as created_by, then re-fetches.

UI Components:

Availability: Templates are passed to the editor on the Contact Detail page, Task detail views/drawers, and Appointment detail pages. Deal notes use the RichTextEditor without the templates prop, so the picker does not render for deals.

Security

Row-Level Security (RLS)

The unified notes table consolidates 12 previous policies down to 4 tenant-scoped policies:

Optimistic Notes Engine & Offline Resiliency

Optimistic Notes & Thread Replies

To make note management feel instant, mutation handlers (Add Note, Add Reply, Edit, Delete) across the contacts feed, appointments feed, and task detail dialog feeds execute optimistic updates:

• Instant Local State Mutations: Handlers immediately modify local timeline states using temporary client-side IDs (temp-xxx), allowing new notes and thread replies to appear in the feed without waiting for database responses.
• Visual Syncing State: Optimistic items are rendered with a syncing treatment: 60% opacity and disabled pointer events (pointer-events-none).
• Control Disabling: Editing, deleting, and replying controls are disabled on in-flight syncing items to prevent sending requests with temporary client-side IDs.
• Background Identifier Swapping: Once the server action returns successfully, the temporary ID is swapped with the official database UUID in the local state, preserving nested reply trees without re-rendering or flashing the entire feed.
• Transactional Rollback Snapshot: Server actions are wrapped in try-catch closures. If a network offline exception occurs, the local state rolls back to a cached pre-mutation snapshot and triggers a failure toast, bypassing loading transitions.
• Optimistic Reference Guard: An optimisticRef guard is utilized on pages where parent properties sync to prevent props updates from overwriting in-flight optimistic UI states.

Offline Chunk Load Failure Prevention

The rich-text note editor is split and lazily loaded. To prevent the application from crashing when a user is offline or has a spotty network connection:

• Chunk Load Promise Rejections: Custom rejection handlers are registered in Next.js's dynamic imports (next/dynamic) to catch ChunkLoadError failures gracefully.
• Editor Preloading: The rich-text editor bundle chunks are preloaded on initial client mount across the contact detail timeline feed, appointments feed, notes dashboard list, and task detail drawer to ensure the component is available when offline note additions are attempted.