Notes — Technical Reference

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

Data Models

Notes are stored in three separate tables — one per entity type. All three share the same column structure.

contact_notes

Column	Type	Description
`id`	`uuid`	Primary key
`workspace_id`	`uuid`	Tenant isolation (`ON DELETE CASCADE`)
`contact_id`	`uuid`	FK → `contacts.id` (`ON DELETE CASCADE`)
`note_text`	`text`	Plain-text note content (required)
`note_content_html`	`text`	Rich-text HTML content (Tiptap output)
`pinned_at`	`timestamptz`	When the note was pinned (NULL if not pinned)
`created_by`	`uuid`	FK → `user_profiles.id` — the author
`created_at`	`timestamptz`	When the note was added
`updated_at`	`timestamptz`	When the note was last modified

task_notes

Column	Type	Description
`id`	`uuid`	Primary key
`workspace_id`	`uuid`	Tenant isolation (`ON DELETE CASCADE`)
`task_id`	`uuid`	FK → `tasks.id` (`ON DELETE CASCADE`)
`note_text`	`text`	Plain-text note content (required)
`note_content_html`	`text`	Rich-text HTML content
`created_by`	`uuid`	FK → `user_profiles.id` — the author
`created_at`	`timestamptz`	When the note was added
`updated_at`	`timestamptz`	When the note was last modified

Design note: task_notes has no foreign key to contacts. Task notes belong to the task, not the contact. The connection to a contact's notes feed is established at read time through a union query — not through database relationships.

appointment_notes

Column	Type	Description
`id`	`uuid`	Primary key
`workspace_id`	`uuid`	Tenant isolation (`ON DELETE CASCADE`)
`appointment_id`	`uuid`	FK → `appointments.id` (`ON DELETE CASCADE`)
`note_text`	`text`	Plain-text note content (required)
`note_content_html`	`text`	Rich-text HTML content
`created_by`	`uuid`	FK → `user_profiles.id` — the author
`created_at`	`timestamptz`	When the note was added
`updated_at`	`timestamptz`	When the note was last modified (auto-managed by trigger)

note_templates

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

Column	Type	Description
`id`	`uuid`	Primary key
`workspace_id`	`uuid`	FK → `workspaces.id` — tenant isolation
`name`	`text`	User-visible template label
`content_html`	`text`	Rich HTML content (Tiptap-compatible)
`created_by`	`uuid`	FK → `user_profiles.id` — who created the template
`created_at`	`timestamptz`	When the template was created
`updated_at`	`timestamptz`	When the template was last modified

Index: idx_note_templates_workspace on workspace_id.

Architecture

Unified Contact Notes Feed

The getContactTimeline() function builds the contact's notes feed by executing a union query at read time:

1. Fetch all contact_notes for this contact
2. Fetch all tasks linked to this contact → get task IDs
3. Fetch all task_notes for those task IDs
4. Fetch all appointments linked to this contact → get appointment IDs
5. Fetch all appointment_notes for those appointment IDs
6. Merge all three sets into a single array
7. Sort by created_at descending (newest first)

Each entry carries a type field (contact_note, task_note, or appointment_note) and optional linked entity metadata:

Field	Type	Description
`id`	`string`	The note's primary key
`type`	`"contact_note"`, `"task_note"`, or `"appointment_note"`	Distinguishes the source
`note_text`	`string`	The note content
`created_at`	`string`	Timestamp for sorting
`author_name`	`string`	Display name of the note author
`task_id`	`string`	Only present for `task_note` entries
`task_title`	`string`	Only present for `task_note` entries
`appointment_id`	`string`	Only present for `appointment_note` entries
`appointment_title`	`string`	Only present for `appointment_note` entries

Rich-Text Editor

Notes use @tiptap/react 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; the HTML version powers display.

Global Search Integration

All three note types are included in the global_search RPC function. Note content is cleaned with REGEXP_REPLACE to flatten newlines for preview display. Appointment notes are module-gated — excluded from results when the Appointments module is disabled.

Full-Text Search

The /notes page uses pg_trgm trigram indexes for ILIKE-based full-text search across all three note tables in a single query.

Note Templates

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

Function	Purpose
`getTemplates(workspaceId)`	Fetches all templates; lazy-seeds defaults if workspace has none
`createTemplate(workspaceId, { name, content_html })`	Creates a new template
`updateTemplate(templateId, updates)`	Updates name and/or content
`deleteTemplate(templateId)`	Hard-deletes a template

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:

Component	Location	Role
`TemplatePicker`	Inside `RichTextEditor` (`rich-text-editor.tsx`)	Dropdown in the toolbar; calls `editor.commands.setContent(html)` to replace content
`TemplatesDialog`	`notes/page.tsx`	Full CRUD management view (list → create/edit)
`RichTextEditor`	Shared component	Accepts optional `templates?: NoteTemplateItem[]` prop; when present, renders the picker

Availability: Templates are only passed to the editor on the Contact Detail page (contacts/[id]/page.tsx). Task notes, appointment notes, and the dashboard task detail widget use RichTextEditor without the templates prop, so the picker does not render.

Security

RLS Policies

All three note tables use the same workspace-scoped RLS pattern:

Operation	Policy
SELECT	Workspace members can view notes in their workspace
INSERT	Workspace members can create notes in their workspace
UPDATE	Workspace members can edit notes in their workspace
DELETE	Workspace members can delete notes in their workspace

Overview