Documents
Compliance documents owned by exactly one of a user, herd, device, or product
| Column Name | Data Type | Constraints |
|---|---|---|
| id | bigint | NOT NULL |
| uri | text | NOT NULL |
| size_bytes | bigint | |
| template_id | bigint | FK -> document_templates |
| valid_from | timestamp with time zone | |
| valid_to | timestamp with time zone | |
| user_id | uuid | FK -> users |
| herd_id | bigint | FK -> herds |
| device_id | bigint | FK -> devices |
| product_id | bigint | FK -> products |
| created_by | uuid | FK -> users |
| created_at | timestamp with time zone | DEFAULT now() NOT NULL |
| updated_by | uuid | FK -> users |
| updated_at | timestamp with time zone | DEFAULT now() NOT NULL |
| is_approved | boolean | DEFAULT false NOT NULL |
| approved_by | uuid | FK -> users |
| approved_at | timestamp with time zone |
Exactly one of user_id, herd_id, device_id, or product_id is set (documents_exactly_one_owner).
Security Policies (RLS)
RLS enabled. Scope: Access resolved from the owner via document_access.
| Owner | SELECT | INSERT / UPDATE / DELETE |
|---|---|---|
| herd | herd_documents.read | herd_documents.write |
| device | device_documents.read (device's herd) | device_documents.write |
| product | has_view_role_any_herd | superadmin only |
| user | owner, or user_documents.read in a shared herd | owner, or user_documents.write in a shared herd |
Platform superadmin: FOR ALL bypass where defined.
Approval
Only user documents go through review. is_approved / approved_by / approved_at on a user document may be changed only by a holder of document_approve.binary in a shared herd (documents_approval_guard). Documents owned by a herd, device, product, or operating context are approved on insert by the same trigger, and later approval edits need only the owner's write ability.
Approved user documents cannot be deleted; other documents can be deleted by anyone with write access. Status-only reads of user documents are available through User_document_statuses.