Contact Note
Data Entity
Description
Structured notes attached to a contact record, created by peer mentors or coordinators to document interactions, observations, and follow-up actions. Notes are timestamped, author-attributed, and subject to role-based visibility rules.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Immutable primary key, generated server-side on insert | PKrequiredunique |
contact_id |
uuid |
Foreign key referencing the contacts table. Identifies which contact record this note belongs to. Cascade-deletes when the parent contact is deleted. | required |
author_id |
uuid |
Foreign key referencing the users table. Identifies the peer mentor or coordinator who created the note. Immutable after creation. | required |
organization_id |
uuid |
Foreign key referencing the organizations table. Used for RLS enforcement to scope note access within an organisation boundary. Denormalized from the author's active organization context at creation time. | required |
body |
text |
The full textual content of the note. May be entered manually, via speech-to-text dictation, or pasted. No maximum length enforced at the database level; minimum 1 character enforced at application level. | required |
visibility |
enum |
Role-based visibility level controlling which roles may read this note. 'all' means any role within the organization may view it; 'coordinator_only' restricts to coordinators and admins; 'author_only' restricts to the creating user. | required |
is_deleted |
boolean |
Soft-delete flag. When true the note is excluded from all application-layer queries but retained for audit purposes. Hard deletes are not permitted on this table. | required |
deleted_at |
datetime |
Timestamp of when the note was soft-deleted. Null when is_deleted is false. | - |
deleted_by |
uuid |
Foreign key to users.id identifying who performed the soft delete. Null when the note has not been deleted. | - |
created_at |
datetime |
UTC timestamp of when the note was persisted. Auto-set by the database on insert and never modified thereafter. | required |
updated_at |
datetime |
UTC timestamp of the most recent body or visibility edit. Auto-updated by a database trigger on any UPDATE to this row. | required |
Database Indexes
idx_contact_notes_contact_id_created_at
Columns: contact_id, created_at
idx_contact_notes_contact_id
Columns: contact_id
idx_contact_notes_author_id
Columns: author_id
idx_contact_notes_organization_id
Columns: organization_id
idx_contact_notes_is_deleted
Columns: is_deleted
Validation Rules
body_non_empty
error
Validation failed
body_non_empty_on_update
error
Validation failed
visibility_valid_enum
error
Validation failed
contact_id_valid_reference
error
Validation failed
author_id_matches_session
error
Validation failed
updated_at_auto_maintained
warning
Validation failed
soft_delete_fields_consistent
error
Validation failed
Business Rules
author_immutability
The author_id field must never be changed after a note is created. Ownership is permanently attributed to the user who created the note. Any UPDATE that attempts to change author_id must be rejected at the database level via a check trigger.
visibility_edit_restriction
Only the note author or a coordinator/admin within the same organization may change the visibility value. A peer mentor who did not create the note cannot alter its visibility setting.
edit_permission_by_role
Only the original author or a coordinator/admin scoped to the same organization may edit the body of a note. Peer mentors cannot edit notes written by other users.
soft_delete_only
Physical row deletion is not permitted. All delete operations must set is_deleted = true, deleted_at = now(), and deleted_by = acting user ID. This preserves audit trail integrity for Bufdir reporting and organizational compliance.
organization_scoped_access
Notes are only accessible within the organization context of the author. Supabase RLS policies must enforce that SELECT, INSERT, UPDATE, and soft-delete operations are restricted to rows whose organization_id matches the JWT claim of the requesting user.
visibility_read_enforcement
When querying notes for display, the application layer must filter by visibility: 'author_only' notes are returned only to the author; 'coordinator_only' notes are returned only to coordinator and admin roles; 'all' notes are returned to all authenticated users within the organization. This is enforced redundantly at both the repository query level and via Supabase RLS policy.
contact_must_exist
A note cannot be created for a contact_id that does not exist in the contacts table and is not soft-deleted. The foreign key constraint enforces referential integrity at the database level.
chronological_ordering
Notes must always be displayed in descending created_at order (newest first) in the Contact Notes Widget to ensure the most recent interaction is immediately visible. This ordering must be encoded in repository queries, not deferred to the UI layer.
CRUD Operations
Storage Configuration
Entity Relationships
Each contact may have multiple chronological notes documenting interactions and follow-up actions