Encrypted Document
Data Entity
Description
Stores metadata for encrypted sensitive documents, particularly assignment payloads and medical records transmitted to peer mentors. Actual file data lives in encrypted Supabase Storage with access gated by valid NDA agreement status.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Primary key. Auto-generated UUID for each encrypted document record. | PKrequiredunique |
owner_id |
uuid |
Foreign key to users table. The user (typically a coordinator) who created and owns this encrypted document. | required |
organization_id |
uuid |
Foreign key to organizations table. Scopes the document to an organization for RLS policy enforcement and data isolation. | required |
recipient_id |
uuid |
Foreign key to users table. The specific peer mentor the document is addressed to. Null if the document is not targeted to a single recipient (e.g., org-level records). | - |
document_type |
enum |
Classifies the nature of the encrypted content to drive access policy and UI rendering decisions. | required |
storage_path |
string |
Relative path within the encrypted Supabase Storage bucket where the ciphertext file resides. Format: {org_id}/{owner_id}/{uuid}.enc | requiredunique |
encryption_key_ref |
string |
Reference identifier to the encryption key used to protect this document. Points to a key record in the key management layer (Flutter Secure Storage / Supabase user profile public key registry). Never stores the actual key material. | required |
content_type |
string |
MIME type of the plaintext content before encryption (e.g., application/json for assignment payloads, application/pdf for medical records). Used to guide decryption rendering logic. | required |
file_size_bytes |
integer |
Size of the encrypted file in bytes as stored in Supabase Storage. Used for storage quota tracking and UI display. | required |
payload_hash |
string |
SHA-256 hash of the ciphertext blob. Used for integrity verification after download to detect tampering or corruption in transit. | required |
document_status |
enum |
Lifecycle status of the document, tracking delivery and consumption by the recipient. | required |
nda_required |
boolean |
When true, the recipient must have a valid, unexpired NDA agreement on record before the decrypted content is rendered. Enforced at the service layer before invoking decryption. | required |
access_restrictions |
json |
Additional structured access control metadata. Can include: allowed_role (e.g., 'peer_mentor'), require_biometric (boolean), max_view_count (integer), allowed_recipient_ids (array of UUIDs). Used by Task Encryption Service to gate decryption beyond NDA checks. | - |
expires_at |
datetime |
Optional UTC timestamp after which the document is no longer accessible and should be treated as expired regardless of NDA status. Used for time-limited assignment payloads. | - |
delivered_at |
datetime |
UTC timestamp when the encrypted payload was confirmed as downloaded by the recipient. Set by Delivery Confirmation Service. | - |
read_at |
datetime |
UTC timestamp when the recipient first successfully decrypted and rendered the plaintext content. Set by Read Receipt Service. | - |
revoked_at |
datetime |
UTC timestamp when access was revoked by the owner or an administrator. Once set, all decryption attempts must be rejected regardless of NDA status. | - |
revocation_reason |
string |
Optional human-readable reason recorded when the document is revoked. Stored for audit trail purposes. | - |
created_at |
datetime |
UTC timestamp when the encrypted document record was created. Set automatically by the database. | required |
updated_at |
datetime |
UTC timestamp of the last modification to this record. Updated automatically via database trigger on any field change. | required |
deleted_at |
datetime |
Soft-delete timestamp. When set, the record is excluded from all active queries but retained for audit and compliance purposes. Storage file deletion is handled separately. | - |
Database Indexes
idx_encrypted_documents_owner_id
Columns: owner_id
idx_encrypted_documents_organization_id
Columns: organization_id
idx_encrypted_documents_recipient_id
Columns: recipient_id
idx_encrypted_documents_document_type
Columns: document_type
idx_encrypted_documents_document_status
Columns: document_status
idx_encrypted_documents_storage_path
Columns: storage_path
idx_encrypted_documents_org_recipient
Columns: organization_id, recipient_id
idx_encrypted_documents_owner_org
Columns: owner_id, organization_id
idx_encrypted_documents_expires_at
Columns: expires_at
idx_encrypted_documents_deleted_at
Columns: deleted_at
Validation Rules
storage_path_format
error
Validation failed
payload_hash_format
error
Validation failed
file_size_positive
error
Validation failed
expires_at_in_future
error
Validation failed
content_type_allowed_values
error
Validation failed
access_restrictions_valid_json_schema
warning
Validation failed
encryption_key_ref_not_empty
error
Validation failed
document_type_matches_content_type
error
Validation failed
owner_id_and_recipient_id_differ
error
Validation failed
delivered_at_after_created_at
error
Validation failed
read_at_after_delivered_at
error
Validation failed
Business Rules
nda_gate_before_decryption
Before any decryption of the document payload is performed, the requesting user must have a valid, non-expired NDA agreement on record in the nda_agreements table. If nda_required is true and no valid NDA exists, decryption is blocked and the NDA signing flow is triggered instead.
organization_scoped_rls
All read, update, and delete operations on encrypted_documents are governed by Supabase RLS policies that restrict access to users whose auth.uid() matches owner_id, recipient_id, or who hold a coordinator/admin role within the document's organization_id. Cross-organization access is never permitted.
no_key_material_in_database
The encryption_key_ref field must only contain a reference identifier (e.g., user public key fingerprint or key ID), never the actual private key or symmetric key material. Key material is stored exclusively in Flutter Secure Storage on the device.
revocation_is_terminal
Once document_status is set to 'revoked' and revoked_at is populated, the document cannot transition back to any active status. All subsequent decryption requests must be rejected with a revocation error, even if the requester holds a valid NDA.
expiry_enforcement
If expires_at is set and the current UTC time exceeds expires_at, the document_status must be treated as 'expired' for access control purposes. Expired documents must not be decrypted. A scheduled edge function or service check may update document_status to 'expired' proactively.
storage_path_references_valid_object
The storage_path value must correspond to an object that actually exists in the designated encrypted Supabase Storage bucket. Document records must not be created if the storage upload failed. Creation is transactional: storage upload must complete successfully before the metadata record is inserted.
soft_delete_before_storage_removal
When a document is logically deleted, deleted_at must be set first before the Supabase Storage object is removed. This ensures the audit trail is preserved even if storage deletion fails. Physical storage deletion is handled asynchronously by secure-document-storage after the metadata soft delete is confirmed.
delivery_status_progression
document_status must follow the valid lifecycle progression: pending → delivered → read. Skipping states or regressing (e.g., 'read' → 'pending') is not permitted. The only valid transitions outside this progression are to 'expired' or 'revoked', which can occur from any non-terminal state.
audit_log_on_access
Every successful decryption event (status transition to 'read') must be logged with actor user ID, timestamp, and document ID. This is a GDPR and organizational compliance requirement for documents containing sensitive personal data such as medical summaries (epikriser).
recipient_must_belong_to_same_organization
If recipient_id is set, the referenced user must be a member of the same organization as the document's organization_id. Cross-organization document dispatch is forbidden to enforce data isolation between member organizations.
CRUD Operations
Storage Configuration
Entity Relationships
Encrypted documents are scoped to an organization for RLS access policy enforcement
A user may own multiple encrypted documents containing sensitive personal data for assignment delivery