Power of Attorney Document
Data Entity
Description
Digital or scanned legal documents such as powers of attorney and medical records managed by peer mentors and coordinators. Supports gradual digitization with signing status tracking, document versioning, and expiry notifications; manual fallback processes run in parallel.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Unique identifier for the document record, generated server-side via gen_random_uuid() | PKrequiredunique |
user_id |
uuid |
Foreign key referencing the user who owns or is the subject of this document (peer mentor or coordinator) | required |
organization_id |
uuid |
Foreign key referencing the organization this document is scoped to for access control and compliance tracking | required |
uploaded_by_user_id |
uuid |
Foreign key referencing the user who uploaded the document, which may differ from user_id when a coordinator uploads on behalf of a peer mentor | required |
document_type |
enum |
Classifies the legal or administrative nature of the document to drive workflow routing, display, and compliance handling | required |
title |
string |
Human-readable title or label for the document, used in list and detail views | required |
description |
text |
Optional free-text description providing additional context about the document's purpose or contents | - |
file_ref |
string |
Supabase Storage bucket path to the uploaded document file, used to generate pre-signed download URLs | required |
thumbnail_ref |
string |
Supabase Storage bucket path to a generated thumbnail image for PDF or scanned document preview in list views | - |
file_content_type |
string |
MIME type of the uploaded file (e.g., application/pdf, image/jpeg) used for rendering decisions and validation | required |
file_size_bytes |
integer |
Size of the uploaded file in bytes, stored for display and quota enforcement | required |
signing_status |
enum |
Current state of the document signing workflow, progressing through a defined state machine from unsigned through to signed or revoked | required |
signing_method |
enum |
Mechanism used or planned for signing — electronic via external provider, in-app acknowledgment, or manual paper fallback | - |
signing_session_id |
string |
External e-signature provider session identifier used to poll signing status and receive webhook callbacks | - |
signed_at |
datetime |
Timestamp of when the signing was completed, set server-side on signing callback. Null if document is not yet signed. | - |
expiry_date |
datetime |
Date after which this document is considered legally expired and notifications should be triggered to prompt renewal | - |
expiry_notification_sent_at |
datetime |
Timestamp of when the most recent expiry warning notification was dispatched, used to prevent duplicate notifications | - |
version |
integer |
Document version number, starting at 1 and incremented each time a new file is uploaded to replace a prior version | required |
previous_version_id |
uuid |
Self-referencing foreign key pointing to the prior version of this document, enabling version chain traversal and audit history | - |
is_encrypted |
boolean |
Whether the file stored in Supabase Storage is encrypted at the application layer in addition to storage-level encryption | required |
metadata |
json |
Flexible JSON payload for organization-specific or document-type-specific additional fields such as authority scope, delegatee name, or medical context | - |
created_at |
datetime |
Timestamp when the document record was first created, set server-side and immutable | required |
updated_at |
datetime |
Timestamp of the most recent update to the document record, updated automatically via trigger on every row modification | required |
deleted_at |
datetime |
Soft-delete timestamp; when set, the document is treated as logically deleted but retained for audit and compliance purposes | - |
Database Indexes
idx_poa_documents_user_id
Columns: user_id
idx_poa_documents_organization_id
Columns: organization_id
idx_poa_documents_user_org
Columns: user_id, organization_id
idx_poa_documents_signing_status
Columns: signing_status
idx_poa_documents_expiry_date
Columns: expiry_date
idx_poa_documents_document_type
Columns: document_type
idx_poa_documents_active_user_org
Columns: user_id, organization_id, deleted_at
idx_poa_documents_signing_session_id
Columns: signing_session_id
Validation Rules
file_ref_required_on_create
error
Validation failed
file_size_limit
error
Validation failed
allowed_content_types
error
Validation failed
expiry_date_future_on_create
error
Validation failed
signing_method_required_for_pending
error
Validation failed
signed_at_set_on_signing_complete
error
Validation failed
version_monotonically_increasing
error
Validation failed
title_non_empty
error
Validation failed
deleted_at_immutable
error
Validation failed
Business Rules
organization_scoped_access
Every poa_documents record must belong to exactly one organization. Supabase RLS policies enforce that users can only read and write documents belonging to organizations they are members of, and coordinators can only access documents within their assigned organizational scope.
signing_status_state_machine
signing_status transitions must follow the allowed state machine: unsigned → pending_signing → signed; unsigned → signed (for in_app_acknowledgment); any non-revoked state → revoked; signed → expired (system-driven on expiry_date). Direct transitions that skip states are rejected.
immutable_file_ref_after_signing
Once signing_status is 'signed', the file_ref and is_encrypted fields must not be modified. Any update attempt to these fields on a signed document is rejected to preserve document integrity for legal and audit purposes. To replace a signed document, a new version record must be created.
version_chain_integrity
When creating a new document version (version > 1), previous_version_id must reference a valid existing poa_documents record belonging to the same user_id and organization_id. The prior version's signing_status is set to 'revoked' atomically within the same transaction.
expiry_notification_deduplication
The expiry notification service checks expiry_notification_sent_at before dispatching a warning. If a notification was already sent within the configured lead-time window (e.g., 30 days, 7 days), a duplicate is not sent. The field is updated on every dispatch.
soft_delete_only
poa_documents records must never be hard-deleted from the database. Deletion operations set the deleted_at timestamp and the file is removed from Supabase Storage, but the metadata record is retained permanently for audit and compliance traceability.
coordinator_upload_on_behalf
Coordinators may upload documents on behalf of peer mentors within their organizational scope. In this case, uploaded_by_user_id (coordinator) differs from user_id (peer mentor). The POA Service validates the coordinator's role and org scope before persisting the record.
parallel_manual_fallback_allowed
Documents with signing_method 'manual_paper' are valid first-class records and may have signing_status 'signed' without a signing_session_id. This supports the gradual digitization model where paper-signed documents are scanned and registered in the system.
CRUD Operations
Storage Configuration
Entity Relationships
POA documents are scoped to an organization for access control and compliance tracking
A user manages multiple legal documents such as powers of attorney across their tenure