Workshop Note
Data Entity
Description
Rich-text notes created during or after a workshop session, capturing coordinator observations, participant outcomes, and follow-up actions. Supports speech-to-text dictation and auto-save for continuity across interrupted sessions.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Primary key — universally unique identifier for the note record | PKrequiredunique |
session_id |
uuid |
Foreign key reference to the workshop_sessions record this note belongs to. A note is always scoped to a single session. | required |
author_id |
uuid |
Foreign key reference to the users record identifying who created or last saved this note. Used for RLS enforcement and display attribution. | required |
content |
text |
Rich-text note body stored as a serialized document (e.g., Delta JSON from a rich-text editor or plain markdown). May be empty string for auto-saved drafts that have not yet received content. | required |
content_format |
enum |
Encoding format of the content field, allowing the frontend to select the appropriate renderer. Defaults to plain_text; set to delta_json when a rich-text editor is used. | required |
is_draft |
boolean |
Indicates whether this note is an auto-saved draft (true) or a manually committed save (false). Draft notes are displayed with a visual indicator in the UI and excluded from read-only session views. | required |
input_method |
enum |
Records how the note content was captured. Used for analytics and to determine speech-to-text post-processing status. | - |
speech_transcript_raw |
text |
Optional raw transcript from the speech-to-text engine before any manual editing. Stored for audit and to allow coordinator to revert to original dictated content if edits are unwanted. | - |
word_count |
integer |
Denormalized word count of the content field, recomputed on each save. Used to display a subtle content size indicator and to enforce minimum content rules for finalized notes. | - |
version |
integer |
Optimistic concurrency version counter, incremented on each update. Used to detect and reject conflicting concurrent saves (e.g., same user on two devices). | required |
last_auto_saved_at |
datetime |
Timestamp of the most recent auto-save operation. Updated independently of updated_at to allow distinguishing auto-saves from intentional saves. Null if the note has never been auto-saved. | - |
finalized_at |
datetime |
Timestamp when the note was committed as a final save (is_draft set to false). Null while the note remains a draft. Immutable once set — further edits re-open the draft flag. | - |
created_at |
datetime |
Timestamp of note creation. Set by the database on insert and never modified. | required |
updated_at |
datetime |
Timestamp of the most recent update to any field. Maintained by a database trigger. | required |
deleted_at |
datetime |
Soft-delete timestamp. When set, the note is hidden from all UI queries but retained in the database for audit and Bufdir compliance purposes. Null for active records. | - |
Database Indexes
idx_workshop_notes_session_id
Columns: session_id
idx_workshop_notes_author_id
Columns: author_id
idx_workshop_notes_session_author
Columns: session_id, author_id
idx_workshop_notes_is_draft
Columns: session_id, is_draft
idx_workshop_notes_created_at
Columns: created_at
idx_workshop_notes_active
Columns: session_id, deleted_at
Validation Rules
content_not_null_on_finalize
error
Validation failed
content_max_length
error
Validation failed
valid_session_reference
error
Validation failed
valid_content_format
error
Validation failed
optimistic_lock_version_check
error
Validation failed
word_count_consistency
warning
Validation failed
author_is_authenticated_user
error
Validation failed
Business Rules
session_scoped_authorship
A note may only be created by a user who is the facilitating coordinator or an active participant of the parent workshop session. Supabase RLS enforces this by joining workshop_participants and checking that auth.uid() matches either the session coordinator or a participant's user_id.
single_draft_per_author_per_session
Each author may have at most one active draft note per session (is_draft = true, deleted_at IS NULL). Before creating a new note, the service checks for an existing draft and returns it for continued editing rather than creating a duplicate. This prevents note fragmentation during interrupted sessions.
auto_save_preserves_draft_state
Periodic auto-saves (triggered every 30 seconds of inactivity) must update content and last_auto_saved_at but must NOT set is_draft to false. Only an explicit user-initiated save action may finalize the note. This ensures coordinators can freely dictate without risk of premature commitment.
finalization_is_irreversible_within_session
Once finalized_at is set (is_draft = false), further edits re-open the draft state (is_draft = true, finalized_at reset to null) and increment the version counter. This preserves the distinction between committed snapshots and in-progress work.
notes_persist_after_session_archive
Workshop notes are retained permanently even when the parent session is archived or soft-deleted. The ON DELETE CASCADE in the FK is intentionally NOT set on the session-level delete — the database uses soft delete on sessions, so notes remain accessible for historical reporting and Bufdir compliance.
speech_transcript_immutability
Once speech_transcript_raw is written on creation, it must not be overwritten on subsequent saves. The raw transcript represents the original dictated text and must remain unchanged for audit purposes. Manual edits are applied only to the content field.
coordinator_admin_delete_only
Soft deletion of a note (setting deleted_at) is restricted to the note's author, the session coordinator, or an organization admin. Peer mentor participants may not delete notes they did not author. Enforced via Supabase RLS policy checking user role against session coordinator and note author_id.
CRUD Operations
Storage Configuration
Entity Relationships
Each workshop session may accumulate multiple note entries created during or after the session