files — Phase 5

Schema locked 2026-06-11. 3 tables, 43 cols: file (19), file_access_grant (16), file_storage_usage (8).

Files is the generic file-metadata registry over Cloudflare R2. R2 owns the bytes; Files owns the metadata, access control, lifecycle, and storage quota. All tenant-scoped with RLS, except file rows with tenant_id = NULL (platform-level/Vrida-own assets — documented below).

Design principles (document at every session touching this schema):

• Bytes in R2, metadata here. file stores the R2 object key + metadata — never file content in Postgres. This is reference-don't-copy for blobs: the bytes live in R2, the metadata (key, size, hash, visibility, ownership, lifecycle) lives here. R2 is the system of record for the bytes; Files is the system of record for the metadata.
• One generic file table. Any module's files in one file table, owner tracked via polymorphic owner_module / owner_type / owner_ref discriminator. This is the same pattern as audit_log.source_table / source_ref and approval_request.source_module / source_ref. Owning modules hold the file_id FK ref; Files holds owner_module / owner_ref for lifecycle and orphan-cleanup — a weak bidirectional reference (NOT enforced FK back to owners; owner_ref is a plain UUID discriminated by owner_module).
• Three visibility tiers. public = CDN read, no auth (logos, product images — served to anyone); private = tenant-RLS (compliance docs, tax certs, canvas signatures — only that tenant's authenticated users); signed = time-limited URL generated on demand (audit exports, DSR response packages — FilesService.getSignedUrl() generates a short-lived URL). file_access_grant handles the cases visibility alone cannot express (one specific data subject, one external auditor, a customer-specific document).
• Fine-grained sharing via file_access_grant. When visibility cannot express the access model — a DSR package to exactly one data subject, a document shared with one external auditor, a file to a specific customer — a grant records the grantee, expiry, and access count. The DSR-to-subject case is legally required (GDPR package must be delivered to one person; grantee_type='external', grantee_identifier=email, expires_at set).
• Presigned upload is service-layer, not a table. FilesService generates the presigned PUT URL; the caller uploads directly to R2; FilesService records metadata on completion and advances status from 'pending' to 'uploaded'. No presigned-URL table — the URL lives briefly in the service layer only.
• Storage quota: Files owns USAGE, Platform owns LIMIT. file_storage_usage tracks per-tenant bytes and file count. Platform's tenant_entitlement owns the tier storage limit. Upload checks limit (reads Platform) vs total_bytes, increments on upload-complete, decrements on delete. Same pattern as notification_quota_usage for notification volume.
• Platform exception (documented, not a violation). Files owns tenant-scoped file storage. Platform manages its own platform-level R2 refs directly: platform.tenant_data_lifecycle.download_url (tenant data exports — generated inline by Platform's export job) and platform.agreement_version.document_url (legal docs — R2 or CDN URL, Vrida-managed). These are documented exceptions — not Files-managed, not FK'd to files.file. FilesService.getSignedUrl() serves everyone else (audit exports, DSR packages). Do not "fix" this boundary by trying to move Platform's refs into Files.
• Retention: Files owns lifecycle enforcement. Owning modules set retain_until on file rows at upload time: compliance/tax certs (7yr+); canvas signatures (retain while the owning record is active + some period); audit export files (7yr per ARCHITECTURE.md); DSR response packages (GDPR minimum). A Files lifecycle sweep deletes R2 objects + soft-deletes file rows after retain_until lapses. retain_until = NULL = indefinite (logos, product images).

Cross-Phase FK seams:

Column	References	Status
*.tenant_id	platform.tenant	Locked — enforced (nullable on file — see NULL case below)
file.uploaded_by_user_id	identity.identity_user	Locked — enforced (nullable)
file_access_grant.file_id	files.file	Intra-schema — enforced
file_access_grant.grantee_user_id	identity.identity_user	Locked — enforced (nullable)
file_access_grant.grantee_customer_id	crm.customer	Locked — enforced (nullable)
file_access_grant.granted_by_user_id	identity.identity_user	Locked — enforced (nullable)
file_storage_usage.tenant_id	platform.tenant	Locked — enforced
file.owner_module / owner_ref	Any module's record (polymorphic backref)	Not enforced FK — loose polymorphic discriminator for lifecycle/orphan-cleanup; owner_module is the discriminator (e.g. 'admin', 'crm', 'pos', 'audit', 'inventory')
file.tenant_id = NULL	Platform-level / Vrida-own assets	NULL case: non-tenant files (e.g. Vrida's own brand assets). Readable per visibility; not tenant-isolated. Rare. RLS read policy: WHERE visibility = 'public' OR tenant_id = current_setting('app.current_tenant_id')::UUID. Public files (logos, product images) readable by anyone; tenant files readable by their tenant. NULL-tenant + visibility != 'public' (private platform-level assets) are service_role ONLY — NOT exposed via any OR tenant_id IS NULL branch. Writes always tenant-scoped; never public write.

Seams closed by this module:

Owning module column	File kind	Seam status
admin.tenant_branding.logo_ref → files.file.id	Brand logo — visibility='public'	Closed at Files lock
admin.compliance_document.document_ref → files.file.id	Compliance PDF — visibility='private'	Closed at Files lock
crm.customer_tax_certificate.document_ref → files.file.id	Tax cert PDF — visibility='private'	Closed at Files lock
pos.sale.signature_ref → files.file.id	Canvas signature PNG — visibility='private'	Closed at Files lock
pos.guarantee.signature_ref → files.file.id	Guarantee canvas signature PNG — visibility='private'	Closed at Files lock
purchasing.purchase_receipt.shipment_photo_ref → files.file.id	Receiving photo — visibility='private'	Closed at Files lock (FORWARD-REF label added 2026-06-11 — purchasing doc-only touch, no col change)
integrations.sync_run.file_ref → files.file.id	Import file (Picas CSV, bulk load) — visibility='private'	Closed at Files lock
audit.audit_export_job.export_ref → files.file.id	Audit export — visibility='signed', retain_until 7yr	Closed at Files lock
audit.data_subject_request.response_artifact_ref → files.file.id	DSR data package — visibility='signed' + file_access_grant to data subject	Closed at Files lock
audit.dpa_agreement.document_ref → files.file.id	Signed DPA — visibility='private', long retention	Closed at Files lock
inventory.item_image.file_id	Product images — visibility='public', additive col (see below)	Closed at Files lock (inventory additive touch 2026-06-11)

NOT closed by this module (documented exceptions):

Column	Reason
platform.tenant_data_lifecycle.download_url	Platform-managed exception — Platform's export job generates signed URL inline; download_expires_at also inline on that record. Not moved to Files.
platform.agreement_version.document_url	Platform-managed exception — "R2 or public URL" for legal docs; Vrida-internal asset; Platform manages.
platform.agreement_acceptance.signature_ref	NOT an R2 ref — HelloSign/DocuSign envelope ID. No Files involvement.
platform.subscription_invoice.invoice_pdf_url	NOT R2 — Stripe-hosted PDF URL. Stripe generates and hosts.
*_credentials_ref, access_token_ref, refresh_token_ref, secret_ref, ein_ref	Vault references — secrets manager; not object storage.

Deferred items:

• Image processing / thumbnails / resize — no seam requires multiple sizes; inventory.item_image has a single url. Add when a product-image resize use case is built (v1.5).
• Virus scanning / content validation on upload — service-layer hook; no table needed. Deferred to v1.5.
• File versioning (previous_file_id / version chains) — no seam references document version history. Add when a compliance-doc or contract version use case requires it.
• Platform's inline R2 refs (tenant_data_lifecycle.download_url, agreement_version.document_url) — documented platform-managed exception; revisit only if Platform's R2 management becomes a real operational problem.
• Storage quota tier limits — Platform's tenant_entitlement needs a storage_bytes_limit entitlement key. Trigger: when file_storage_usage enforcement is wired up in the upload flow.

---

files.file — 19 cols

The generic file-metadata registry. One row per R2 object. Stores the R2 key, display metadata, visibility tier, upload status, owner discriminator, and lifecycle retention floor. The bytes live in R2; this row is the metadata. status tracks the presigned-upload lifecycle: 'pending' when a presigned PUT URL has been issued but upload is not yet confirmed; 'uploaded' when R2 confirms receipt; 'failed' if upload was never completed.

tenant_id is nullable — platform-level or Vrida-own assets (logo, legal docs managed by Platform) may have no tenant scope. The RLS policy for Files must handle: tenant-scoped reads return WHERE tenant_id = current_tenant_id, PLUS visibility = 'public' rows are also readable regardless of tenant (CDN-served logos, product images). tenant_id = NULL rows are platform-level and accessible only via service_role or explicit public visibility.

RLS: Tenant-isolated on tenant_id with public-read bypass for visibility = 'public'. Non-tenant (tenant_id = NULL) rows are service_role or public-visibility only.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	nullable	—	FK → platform.tenant. NULL for platform-level / Vrida-own assets (rare). See RLS note above.
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete (lifecycle sweep sets this; R2 object deleted separately)
r2_key	text	NOT NULL	—	Cloudflare R2 object key — the authoritative storage pointer. Unique within bucket: UNIQUE (r2_key) WHERE deleted_at IS NULL.
r2_bucket	text	nullable	—	R2 bucket name if multi-bucket; NULL = default bucket. Allows future bucket segregation (e.g. private bucket for compliance docs vs CDN bucket for logos) without a schema change.
file_name	text	NOT NULL	—	Original / display file name (e.g. 'logo.png', 'tax_cert_2026.pdf'). Not the R2 key.
mime_type	text	NOT NULL	—	MIME type (e.g. 'image/png', 'application/pdf', 'text/csv'). Used for Content-Type on signed URL generation.
file_size_bytes	bigint	NOT NULL	0	File size in bytes. 0 until upload is confirmed (status = 'uploaded'). Used for quota tracking — file_storage_usage.total_bytes is a running sum of this column for status = 'uploaded' rows.
file_hash	text	nullable	—	SHA-256 hex digest of the file content — computed client-side or post-upload. Used for integrity verification and dedup detection. NULL until confirmed.
visibility	text	NOT NULL	'private'	CHECK IN ('public', 'private', 'signed'). 'public' = CDN read, no auth (logos, product images). 'private' = tenant-RLS authenticated access only (compliance docs, tax certs, signatures). 'signed' = FilesService.getSignedUrl() generates a time-limited URL on demand (exports, DSR packages).
status	text	NOT NULL	'pending'	CHECK IN ('pending', 'uploaded', 'failed', 'deleted'). 'pending' = presigned PUT issued, awaiting upload. 'uploaded' = R2 confirmed. 'failed' = upload never completed (stale pending). 'deleted' = R2 object removed (set by lifecycle sweep before deleted_at).
owner_module	text	nullable	—	The module that owns this file (e.g. 'admin', 'crm', 'pos', 'audit', 'inventory', 'integrations'). Polymorphic discriminator — NOT an enforced FK; used for lifecycle/orphan-cleanup.
owner_type	text	nullable	—	The record type within the owner module (e.g. 'compliance_document', 'customer_tax_certificate', 'sale_signature', 'guarantee_signature', 'audit_export', 'dsr_response', 'item_image'). Refines the polymorphic discriminator for orphan-cleanup queries.
owner_ref	UUID	nullable	—	The owning record's id. Plain UUID; not an enforced FK (polymorphic cross-module). Used with owner_module + owner_type to identify the owning record for lifecycle and orphan-cleanup: "all files owned by audit.audit_export_job rows that are now deleted".
uploaded_by_user_id	UUID	nullable	—	FK → identity.identity_user. The user who initiated the upload. NULL for system-generated files (export jobs, DSR packages generated by background jobs).
retain_until	timestamptz	nullable	—	Lifecycle retention floor — Files lifecycle sweep will not delete before this timestamp. NULL = indefinite (logos, product images). Examples: compliance docs / tax certs = now() + 7 years; canvas signatures = retain while owning record is active; audit export files = 7yr; DSR response packages = GDPR minimum (typically 1yr after delivery).
uploaded_at	timestamptz	nullable	—	When R2 confirmed the upload (set when status transitions to 'uploaded'). NULL until confirmed.

Table-level CHECK:

CHECK (
  (owner_module IS NULL AND owner_ref IS NULL)
  OR (owner_module IS NOT NULL AND owner_ref IS NOT NULL)
)

owner_module and owner_ref are all-or-nothing — a row with one set and the other NULL is malformed for lifecycle/orphan-cleanup. owner_type is independently optional (excluded from this CHECK).

Indexes:

• PK on id
• on (tenant_id)
• UNIQUE on (r2_key) WHERE deleted_at IS NULL — one metadata row per R2 object key
• on (owner_module, owner_ref) WHERE owner_ref IS NOT NULL — orphan-cleanup and owner backref lookup
• on (tenant_id, owner_module) — "all files for this tenant from this module" (lifecycle sweep, quota reconciliation)
• on (visibility) WHERE visibility = 'public' — CDN / public-read query path
• on (status) WHERE status IN ('pending', 'failed') — stale-upload cleanup sweep
• on (retain_until) WHERE retain_until IS NOT NULL — lifecycle expiry sweep
• on (tenant_id) WHERE status = 'uploaded' AND deleted_at IS NULL — quota-reconciliation sweep: SUM(file_size_bytes) per tenant

---

files.file_access_grant — 16 cols

Fine-grained sharing for the cases visibility alone cannot express. When a DSR response package must be delivered to exactly one data subject (GDPR requirement), when a compliance doc is shared with one external auditor, or when a file is shared with a specific customer — a grant records the grantee, access level, expiry, and access count.

grantee_type discriminates which grantee columns are populated: 'user' sets grantee_user_id (an identity_user, e.g. a staff member or manager); 'customer' sets grantee_customer_id (a crm.customer, e.g. for customer-specific documents); 'external' sets grantee_identifier (email address for an external party like a DSR data subject or auditor); 'public_link' sets grantee_identifier (a signed share token — a more shareable public link than visibility='public' but still tracked and expirable).

RLS: tenant-isolated on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete. Access-check invariant: deleted_at IS NOT NULL ALSO denies access, same as revoked_at IS NOT NULL. A grant is valid only when revoked_at IS NULL AND deleted_at IS NULL AND (expires_at IS NULL OR expires_at > now()). Access-check logic MUST check both; checking only revoked_at would leak a soft-deleted grant's file.
file_id	UUID	NOT NULL	—	FK → files.file
grantee_type	text	NOT NULL	—	CHECK IN ('user', 'customer', 'external', 'public_link')
grantee_user_id	UUID	nullable	—	FK → identity.identity_user — populated when grantee_type = 'user'
grantee_customer_id	UUID	nullable	—	FK → crm.customer — populated when grantee_type = 'customer'
grantee_identifier	text	nullable	—	Email address or signed token — populated when grantee_type IN ('external', 'public_link'). Examples: DSR data subject's email address; a share token for a public link.
access_level	text	NOT NULL	'read'	CHECK IN ('read', 'download'). 'read' = view in-app; 'download' = can generate a signed download URL.
expires_at	timestamptz	nullable	—	When this grant expires (NULL = no expiry). DSR packages and external auditor access should always have an expiry. The expiry sweep revokes lapsed grants.
revoked_at	timestamptz	nullable	—	Explicit revocation timestamp (set when access is manually withdrawn before expires_at). Distinct from deleted_at (deletion of the grant record vs. revocation of access). Revoked grants are retained for audit trail; revoked_at IS NOT NULL means access is no longer valid.
granted_by_user_id	UUID	nullable	—	FK → identity.identity_user — the staff user who created this grant. NULL for system-generated grants (e.g. DSR response package grant created by the background DSR job).
access_count	integer	NOT NULL	0	Number of times this grant has been used to access the file. Incremented on each access. Audit trail for compliance.
last_accessed_at	timestamptz	nullable	—	When the grant was last exercised (last time the grantee accessed the file via this grant).

Table-level CHECKs:

CHECK (
  (grantee_type = 'user'     AND grantee_user_id IS NOT NULL     AND grantee_customer_id IS NULL  AND grantee_identifier IS NULL)
  OR (grantee_type = 'customer' AND grantee_customer_id IS NOT NULL AND grantee_user_id IS NULL    AND grantee_identifier IS NULL)
  OR (grantee_type IN ('external', 'public_link') AND grantee_identifier IS NOT NULL AND grantee_user_id IS NULL AND grantee_customer_id IS NULL)
)

Indexes:

• PK on id
• on (tenant_id)
• on (file_id)
• on (grantee_user_id) WHERE grantee_user_id IS NOT NULL
• on (grantee_customer_id) WHERE grantee_customer_id IS NOT NULL
• on (grantee_identifier) WHERE grantee_identifier IS NOT NULL
• on (expires_at) WHERE expires_at IS NOT NULL AND revoked_at IS NULL AND deleted_at IS NULL — expiry sweep: find active non-deleted grants that have lapsed

---

files.file_storage_usage — 8 cols

Per-tenant storage counter. Tracks total bytes and file count for uploaded, non-deleted files. Platform's tenant_entitlement owns the tier storage LIMIT; this table owns the USAGE. Upload completion increments total_bytes + file_count; deletion decrements. Reconcilable from file rows (SELECT SUM(file_size_bytes), COUNT(*) WHERE tenant_id = X AND status = 'uploaded' AND deleted_at IS NULL) — same pattern as notification_quota_usage.

RLS: tenant-isolated on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
total_bytes	bigint	NOT NULL	0	Maintained cache — running sum of file.file_size_bytes for status = 'uploaded', deleted_at IS NULL for this tenant. Incremented on upload-complete event; decremented on file deletion. Reconcile periodically against live file rows.
file_count	integer	NOT NULL	0	Maintained cache — count of uploaded, non-deleted files for this tenant.
last_calculated_at	timestamptz	nullable	—	When total_bytes / file_count were last reconciled against live file rows (scheduled reconciliation sweep).

Indexes:

• PK on id
• on (tenant_id)
• UNIQUE on (tenant_id) WHERE deleted_at IS NULL — one usage row per active tenant

---

Column counts: file(19) + file_access_grant(16) + file_storage_usage(8) = 43

---