Design Rationale — Files
Non-obvious design choices for the files module — the WHY behind each decision.
Files Module (Locked 2026-06-11)
DR1 — Bytes in R2, metadata here (reference-don't-copy for blobs)
Decision: files.file stores only the R2 object key + metadata (size, hash, visibility, ownership, lifecycle). File content is never stored in Postgres. R2 is the system of record for bytes; Files is the system of record for metadata.
Why: Storing file content in Postgres (as bytea or base64 text) would blow out row sizes, destroy query performance, exhaust Postgres memory, and make backups unmanageable. Object storage (R2) is built for blobs; Postgres is built for structured metadata. This is reference-don't-copy for blobs — the same principle as provider_call not copying notification content (that lives in Notifications), and audit_log pointing at source records rather than copying their state.
Rejected: bytea column on file (would store blob content in Postgres — catastrophic for any file larger than a few KB). Base64-encoded text column (same problem, worse encoding overhead). Inline JSONB blob (same).
Guard: Never add a content, data, blob, or base64 column to files.file or any other table. File content lives in R2, accessed via R2 key. files.file is metadata only.
DR2 — One generic file table with polymorphic owner backref
Decision: All modules' files in one files.file table, owner tracked via owner_module / owner_type / owner_ref polymorphic discriminator. Owning modules hold a file_id UUID FK files.file ref. files.file holds owner_module / owner_ref as a loose backref for lifecycle and orphan-cleanup — NOT an enforced FK back to owner tables.
Why: Per-module file tables (admin_file, pos_file, audit_file, etc.) would require the same schema, RLS policy, index set, lifecycle sweep, and presigned-URL logic duplicated 11 times. Common file operations (quota tracking, lifecycle enforcement, access grants, signed-URL generation) can only be expressed generically with one table. The polymorphic owner backref pattern is established in this codebase (audit_log.source_table / source_ref, approval_request.source_module / source_ref). Bidirectional enforcement (an FK from files.file back to every owning table) is impossible in SQL for cross-schema polymorphic references — the backref is intentionally loose; the owning module's file_id FK is the authoritative reference direction.
Rejected: Per-module file tables — duplicated schema and logic. A central FK enforcement from files.file back to owners — impossible across polymorphic targets; the forward ref (file_id on the owning table) is authoritative.
Guard: Do not create admin_file, pos_file, or any other per-module file table. All file metadata goes in files.file. The owner_module / owner_ref discriminator is a loose backref for lifecycle only — the enforced FK direction is owning_table.file_id → files.file.
DR3 — Three visibility tiers with public-RLS-bypass
Decision: file.visibility has three values: 'public' (CDN read, no auth — logos, product images), 'private' (tenant-RLS — compliance docs, signatures), 'signed' (time-limited URL generated by FilesService.getSignedUrl() on demand — exports, DSR packages). The 'public' tier deliberately bypasses tenant-RLS for reads: WHERE visibility = 'public' OR tenant_id = current_setting('app.current_tenant_id')::UUID. This means public files (logos, product images) are readable by anyone without auth — by design.
Why: Logos and product images must be servable from a CDN without authentication — putting them behind tenant-RLS would require every storefront page load to pass a JWT, adding latency and invalidating CDN caching. The 'signed' tier exists because some files need controlled access that isn't per-tenant (a DSR response package must be delivered to one specific data subject, not everyone in the tenant). 'private' covers the middle case. The three tiers map to three fundamentally different access patterns: public CDN, tenant auth, time-limited link.
Rejected: Putting public files behind tenant-RLS (breaks CDN, forces auth on every image load). A single is_public boolean (doesn't capture the signed-URL access pattern). A visibility = 'public' that still requires a tenant JWT (defeats CDN caching).
Guard: public = read-only, no auth — NEVER allow public writes. The RLS write policy is always tenant-scoped regardless of visibility. NULL-tenant + visibility != 'public' files are service_role ONLY — never reachable via the normal tenant auth path. RLS read policy is exactly WHERE visibility = 'public' OR tenant_id = current_tenant — do NOT add OR tenant_id IS NULL (that would expose private platform-level assets to all authenticated users).
DR4 — item_image FK, not absorbed into Files schema
Decision: inventory.item_image remains in the Inventory schema and owns display semantics (is_primary flag, sort_order, alt_text, storage_key/url for direct CDN access). It gains a file_id UUID nullable FK files.file to link to the R2 object's Files metadata. files.file owns the R2 object + lifecycle + quota. The two tables are complementary, not redundant.
Why: item_image is product-catalog data — it belongs to an item or variant, has a sort order, a primary flag, and alt text. These are display and catalog concerns owned by Inventory. Moving item_image into the files schema would make Files aware of product catalog concepts (item/variant ownership, primary flags, sort order), which is the wrong direction. Files is generic; Inventory is domain-specific. The file_id FK is the seam — Inventory calls FilesService for upload, lifecycle, and signed-URL operations; the display data stays in Inventory.
Rejected: Moving item_image into the files schema (would pollute the generic Files abstraction with product-catalog domain concepts). Replacing item_image entirely with a generic files.file lookup (would lose the primary/sort/alt display semantics that belong in Inventory).
Guard: inventory.item_image stays in the inventory schema. Do not move it to files. files.file is the storage layer; item_image is the display layer. The file_id FK is the link — not a migration target.
DR5 — file_access_grant for what visibility cannot express
Decision: When visibility alone cannot express the required access model — a DSR response package that must be delivered to exactly one data subject, a document shared with one external auditor, a customer-specific download — a file_access_grant row records the grantee (discriminated by grantee_type), access level, expiry, and access count. visibility and file_access_grant are complements, not duplicates.
Why: The GDPR requirement for a Data Subject Request response package is specific: the data package must be delivered to the requesting data subject and to nobody else. visibility='signed' generates a URL that anyone with the link can use — insufficient for GDPR compliance. file_access_grant with grantee_type='external', grantee_identifier=email, and expires_at set provides the per-recipient, time-limited, audited access required. The same mechanism handles external auditor access (where a specific non-tenant person needs access to a compliance doc) and customer-specific documents (where only one crm.customer should be able to download). 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 all three conditions.
Rejected: Using visibility='public' for DSR packages (exposes to anyone — not GDPR compliant). Using visibility='signed' alone without a grant (a signed URL is link-based, not identity-based; anyone with the link can access). Adding a recipient column directly to files.file (doesn't support multiple grants per file).
Guard: visibility and file_access_grant are complements — do not try to collapse them. The DSR-to-subject grant is legally required; do not remove grantee_type='external'. Access-check MUST evaluate all three: revoked_at IS NULL AND deleted_at IS NULL AND not expired. A soft-deleted grant (deleted_at IS NOT NULL) also denies access — checking only revoked_at would leak a deleted grant's file.
DR6 — Storage usage in Files, storage limit in Platform
Decision: files.file_storage_usage tracks per-tenant storage USAGE (maintained cache of total_bytes and file_count). Platform's tenant_entitlement table owns the tier storage LIMIT. The upload flow reads the limit from Platform's entitlement and compares it to total_bytes before allowing the upload to proceed. file_storage_usage.total_bytes is incremented on upload-complete and decremented on deletion.
Why: The same usage-here-limit-in-platform pattern as notifications.notification_quota_usage for notification volume. Centralizing quotas in Platform (via tenant_entitlement) means the limit can change without touching the module that tracks usage. Having the usage counter in the module that owns the files keeps the increment/decrement logic co-located with the file operations. Storing the limit in Files would duplicate tier-configuration logic that belongs in Platform's entitlement system.
Rejected: Storing the tier storage limit in files.file_storage_usage (would duplicate Platform's entitlement configuration). Not tracking usage at all and computing live from file rows every time (too expensive at quota-check frequency). Tracking usage in Platform directly (cross-schema writes from Platform on every file upload/delete — wrong direction).
Guard: Never add a storage_bytes_limit or tier-limit column to files.file_storage_usage or any other files.* table. The limit belongs in platform.tenant_entitlement. file_storage_usage owns USAGE only.
DR7 — Platform R2 refs are Platform-managed exceptions
Decision: platform.tenant_data_lifecycle.download_url (tenant data export download link, with download_expires_at inline) and platform.agreement_version.document_url (legal document URL — R2 or public CDN) are Platform-managed R2 references. They are NOT routed through FilesService and are NOT FK'd to files.file. These are documented exceptions to the "Files owns all R2 metadata" rule.
Why: Platform's tenant data export job generates a short-lived signed R2 URL inline as part of the export process — the URL and its expiry are properties of the export lifecycle record itself, managed entirely within Platform. agreement_version.document_url is a Vrida-internal legal document URL (R2 or public CDN) that Platform manages directly as part of its agreement versioning workflow. Routing these through FilesService would create a circular dependency (Platform would depend on Files; Files depends on Platform via tenant_id FK) and would add unnecessary indirection for Platform's own operational data. These are the only two Platform-managed R2 refs; all other R2 refs in the system are routed through FilesService.
Rejected: Moving tenant_data_lifecycle.download_url into files.file (circular schema dependency Platform→Files→Platform; adds indirection to Platform's own export lifecycle). Moving agreement_version.document_url into files.file (Platform-internal legal doc management; Vrida-own content not tenant-scoped).
Guard: platform.tenant_data_lifecycle.download_url and platform.agreement_version.document_url are Platform-managed. Do NOT add file_id FKs to these columns or route their R2 operations through FilesService. All other R2 refs (outside Platform) route through Files.