Note
Data Entity
Description
A freeform text note created by a peer mentor or coordinator and linked to a specific contact. Provides qualitative context about a contact that does not fit into structured activity fields. Notes support soft-deletion, full-text search via FTS5, and speech-to-text input.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Globally unique identifier for the note, generated server-side on creation | PKrequiredunique |
author_id |
uuid |
Foreign key referencing the users table — the peer mentor or coordinator who created the note | required |
contact_id |
uuid |
Foreign key referencing the contacts table — the contact this note is about | required |
content |
text |
The freeform text body of the note. May be authored via keyboard or speech-to-text dictation. Stored as plain text; FTS5 index is maintained separately for search. | required |
input_method |
enum |
Records how the note content was authored — typed manually or dictated via speech-to-text | - |
local_association_id |
uuid |
Foreign key referencing local_associations — denormalized for fast association-scoped queries by coordinators without a JOIN through the author's membership | required |
deleted_at |
datetime |
Timestamp set when the note is soft-deleted. NULL means the note is active. Soft-deleted notes are excluded from all standard queries but preserved in the database for audit purposes. | - |
created_at |
datetime |
UTC timestamp of when the note was first persisted, set automatically by the database on insert | required |
updated_at |
datetime |
UTC timestamp of the last content edit, updated automatically on any UPDATE to the row. Does not change when deleted_at is set. | required |
Database Indexes
idx_note_contact_active
Columns: contact_id, deleted_at
Primary read path: fetch all active notes for a contact. Partial variant WHERE deleted_at IS NULL is recommended for Postgres to minimize index size.
idx_note_author_id
Columns: author_id
Support queries for all notes authored by a specific user (row-level access check and audit)
idx_note_local_association_id
Columns: local_association_id
Enables coordinator-scoped queries for all notes across their association without joining through user memberships
idx_note_contact_created
Columns: contact_id, created_at
Chronological ordering of notes per contact for the notes list screen
Validation Rules
content_non_empty
error
Validation failed
content_non_empty_on_update
error
Validation failed
content_max_length
error
Validation failed
content_max_length_on_update
error
Validation failed
author_id_valid_user
error
Validation failed
contact_id_valid_contact
error
Validation failed
input_method_valid_enum
error
Validation failed
deleted_at_immutable_once_set
error
Validation failed
offline_sync_conflict_detection
warning
Validation failed
Business Rules
author_or_coordinator_write_access
Only the note's original author or a coordinator within the same local association may edit or soft-delete a note. Peer mentors cannot modify notes created by other users.
role_scoped_read_visibility
Peer mentors may only read notes they authored. Coordinators may read all notes linked to contacts within their local association. Organization and Global Administrators have no direct mobile read path; access is via admin panel with appropriate scope.
soft_delete_only
Notes must never be hard-deleted via the application layer. Deletion sets deleted_at to the current UTC timestamp. Hard deletes are a database-admin-only operation reserved for legal erasure requests.
fts5_index_synchronization
The FTS5 virtual table must be updated atomically with every INSERT, UPDATE, or soft-DELETE on a note. Insert/update writes the new content to the index; soft-delete removes the entry from the index so it no longer appears in search results.
fts5_index_synchronization_on_update
FTS5 index entry for the note must be replaced (delete old + insert new) whenever note content is updated to keep search results accurate.
fts5_index_removal_on_delete
Soft-deleting a note must remove its entry from the FTS5 index immediately so it no longer surfaces in search results, even though the row is retained in the primary table.
contact_must_be_accessible_to_author
A note can only be created against a contact that is accessible to the author under their current role and association context. Prevents notes being attached to contacts in other associations.
immutable_author_and_contact
author_id and contact_id are set at creation and must never be changed in subsequent updates. Reassigning a note to a different contact or author requires deleting and recreating.
local_association_denormalization
local_association_id must be resolved from the author's active membership context at creation time and stored on the note row. This denormalization supports efficient coordinator-scoped queries without runtime JOIN through user_organization_roles.