files — Module Spec

1. Purpose

files is the generic file-metadata registry for the whole platform — the single place any module points to when it needs to store bytes (an uploaded document, a captured photo, a generated PDF) without inventing its own storage logic. It owns metadata, storage location, lifecycle, malware-scan status, text extraction, and access control; it never re-implements what a module already owns (e.g. inventory.item_image's own display metadata stays there — it just gains a real file_id). This is module #26 in the build sequence and the first true v2 build of this schema — v1 planned it (3 tables / 43 cols, locked design, never migrated) but it was never built; see §11 for the full v1→v2 delta. files is also the first module to lay the vector spine (document_index / document_chunk) — built now, populated later — ahead of any AI-driven semantic search feature actually shipping.

2. Ownership

Owns — 6 tables, 89 columns:

Table	Cols	Role
file	27	The core registry row: one per stored object, its location, lifecycle, scan/extraction status
attachment	17	Polymorphic many-to-many join: which file(s) attach to which business entity, in what role
file_access_grant	16	Fine-grained access grant for what visibility alone can't express (e.g. a DSR delivery)
tenant_storage_usage	8	Cached usage counter (bytes/objects) per tenant — the limit lives in platform.tenant_entitlement
document_index	10	Per-file indexing status for the text/vector search surface
document_chunk	11	Chunked extracted text + optional embedding — the FTS/semantic-search unit
Total	89

Does NOT own: the actual bytes (Cloudflare R2 does — files is metadata only); the extraction pipeline itself (PyMuPDF/Textract/Bedrock calls are a service-layer concern, riding the ai module for any agent-driven step); the storage-quota LIMIT (owned by platform.tenant_entitlement — usage-here/limit-in-platform, the same pattern as notification_quota_usage); any display-specific image metadata a module already owns (inventory.item_image keeps its own alt_text/sort_order/is_primary — files only supplies the underlying bytes' file_id).

3. Layer & Dependencies

files is a foundation-layer schema (SCHEMA_CONVENTIONS.md §1: foundation = platform, identity, payments, integrations, ai, files, search, shared). Per the layer rule, files must not FK or call into any business or consumer schema — every seam into files runs the other direction (a business/consumer table holds the file_id and points at files.file). Depends on: platform (tenant, legal_entity indirectly via entity_id patterns elsewhere — not used here), identity (actor, for uploaded_by_actor_id/created_by_actor_id/reviewed_by_actor_id). Depended on by: admin, crm, pos, receiving, integrations (not yet built), ai (via import_file.file_id), inventory (item_image.file_id) — all via a plain file_id column pointing at files.file.id, most still deferred forward-refs until this module exists (see §11). The consumer layer reaches into files exclusively through consumer.get_files_for_consumer(), a consumer-owned SECURITY DEFINER function (§9) — files itself has zero knowledge of, or dependency on, the consumer schema.

4. Storage Architecture (Hybrid A — locked decision)

This section is the single canonical explanation of how and why files stores bytes where it does. It exists so a future contributor (human or agent) never has to re-derive this from a bare storage_provider column.

4.1 Cloudflare R2 is the sole storage of record

Every file this module tracks — images and documents alike — lives in Cloudflare R2, permanently. R2 is the storage of record because egress, not storage, dominates cost for this product: Vrida is image-heavy (plant photos in the POS catalog, product images across the tenant app, a consumer-facing app browsing image catalogs), and R2 charges $0 egress at any volume, while S3 charges roughly $0.09/GB. At an illustrative scale of 5TB stored / 20TB egress per month:

Provider	Storage (5TB @ published rate)	Egress (20TB)	Monthly total (illustrative)
Cloudflare R2	~$75 (5,120 GB × $0.015/GB)	$0	~$75
AWS S3 (Standard)	~$115 (5,120 GB × ~$0.023/GB)	~$1,800 (20,480 GB × ~$0.09/GB)	~$1,915

— a roughly 25× difference at this illustrative volume, growing without bound as egress scales, since R2's egress term is always zero. R2 is S3-API-compatible (the same S3 SDK calls work against both endpoints with a different base URL/credential set), so this is a cost decision, not a rewrite — no bespoke storage client is needed.

R2 cost reference (for legibility without re-researching): $0.015/GB-month storage, $0 egress, $4.50 per million Class A operations (writes/uploads), $0.36 per million Class B operations (reads/downloads); free tier covers the first 10GB storage + 1M writes + 10M reads per month.

4.2 AWS S3 is used only as transient staging for Textract's async path

AWS Textract is the chosen OCR/extraction engine for scanned or image-based documents (see §4.4). Textract has two APIs with different input requirements:

• Synchronous API (AnalyzeExpense, DetectDocumentText) — accepts raw bytes directly in the request (≤10MB, single-page). Most real-world invoices, receipts, and purchase orders are 1–2 pages and comfortably under 10MB, so the sync path covers the large majority of documents with zero S3 involvement: files reads the object's bytes straight from R2 and POSTs them to Textract. No staging bucket, no S3 object ever created.
• Asynchronous API (StartDocumentAnalysis) — required for genuinely large or multi-page documents (long contracts, multi-page POs), and only reads its input from an S3 bucket — it cannot read from Cloudflare R2 directly. For this minority of documents, the pipeline copies the R2 object to a temporary S3 staging bucket, runs Textract against it, retrieves the result, and deletes the S3 copy once extraction completes. The S3 object is transient by construction — never a second copy of record, never referenced by any files.file row.

S3 is an implementation detail of the extraction service, never a storage tier files itself models. files.file.storage_provider is always 'r2' for every row this module durably tracks; the temporary S3 staging object during an async Textract run has no corresponding files.file row of its own and is not files' concern to track or clean up beyond the extraction service's own responsibility to delete it.

4.3 storage_provider — one column of insurance, not an active multi-provider split

file.storage_provider (r2 / s3, CHECK-constrained, default 'r2') exists so a fuller multi-provider storage split remains possible without a schema change, should a future requirement emerge (e.g. a decision that certain documents should durably live in S3 for deeper native AWS integration — Lambda triggers on S3 Event Notifications, Glacier archival for 7-year financial-record retention). Today, storage_provider is always 'r2' in every real row — 's3' never appears as a durable value in this module's own data; it is defensive schema headroom, not an active feature.

4.4 Text extraction — the cost-optimized tiering, in full

Extraction is tiered specifically to avoid paying for OCR when it isn't needed, and to avoid paying for LLM structured-extraction when a cheaper deterministic tool suffices:

1. Tier 1 — PDF with a real text layer. The majority of vendor-issued PDFs (invoices, POs, most receipts) are generated digitally, not scanned — they carry a text layer. These are extracted in-process, for free, via PyMuPDF/pdfplumber: fast, perfectly accurate (no OCR error), zero external API cost.
2. Tier 2 — scan or image with no text layer. Routed to AWS Textract — sync API (raw bytes, ≤10MB, single page) for the large majority; async + temporary S3 staging (§4.2) only for large multi-page scans.
3. Tier 3 — structured field extraction (turning a PDF into a real vendor_invoice row with line items, not just plain text). This uses Textract's AnalyzeExpense and/or a Bedrock LLM call, riding the ai module (a duty-granted, credit-metered extraction agent) and routing the resulting draft through the approvals engine for human confirmation before it becomes a live financial record — a mis-extracted invoice amount is a money bug, not a UX inconvenience. This structured-extraction pipeline is explicitly NOT part of the files module — files owns the source file and the plain extracted text; the pipeline that turns that text into a domain row is a separate service concern (see §9).

Regardless of which tier produced it, the extracted plain text lands in document_chunk.text — FTS (and, later, semantic search) is agnostic to whether the text came from a free in-process parse or a paid Textract call. file.extraction_status (pending / extracted / needs_ocr / processing / failed) and file.textract_job_id (set only for the async path) are the schema surface tracking this pipeline's state; document_index.status tracks the downstream indexing step separately (a file can be extracted but not yet ready for search).

4.5 Why pgvector in Postgres, not an external vector database

Pinecone, Weaviate, and similar external vector stores were considered and rejected. The reason is specific to a documented, real industry failure mode: tenant isolation for a multi-tenant RAG system is already solved in this codebase's own Postgres — RLS enforces it structurally for every other tenant-scoped table. Moving vectors to an external store would mean re-solving that same isolation problem in a second system with its own access-control model, and cross-tenant RAG leaks are exactly what happens when that second solving is incomplete or inconsistent with the first. Keeping document_chunk.embedding as a pgvector column inside files.document_chunk — a normal tenant-scoped, RLS-policed table — means a similarity query issued through tenantDB() cannot retrieve another tenant's chunks, structurally, even if the similarity query itself is written wrong (see §8's AI/MCP access-boundary discussion for the one caveat this does not cover: a query that bypasses tenantDB() entirely).

4.6 Embedding dimension: vector(1024)

document_chunk.embedding is vector(1024), sized for Amazon Titan Text Embeddings V2 — Bedrock-native, avoiding a third-party model dependency inside an already-Bedrock-based stack (Cohere Embed v3 is a dimension-compatible alternative if a model change is ever warranted). The column is built now and left NULL until semantic search is actually turned on — a model change, if one ever happens, means re-embedding from the already-stored document_chunk.text, never a re-OCR of the source file. search_vector (a generated tsvector column, GIN-indexed) works immediately, with zero embeddings populated, covering full-text search from day one.

5. Capabilities — honest Part D framing

files itself makes no autonomous judgment calls about business outcomes — it is infrastructure. The one legitimate agent surface living partly in this module's schema is attachment's own review seam: an AI agent may propose that a given file attaches to a given entity in a given role (e.g. bulk-matching newly uploaded photos to inventory.item rows by filename/EXIF heuristics — the same class of judgment call already named on inventory.item_image's own build). This is draft_only — the agent creates an attachment row with review_status='pending', a human confirms or rejects it; the agent never marks its own proposal approved. Malware scanning (file.scan_status) and text extraction (file.extraction_status) are deterministic pipeline steps, not judgment calls, and carry no review seam of their own — a scan is either clean or it isn't, and extraction either succeeded or didn't. No FilesService exists yet — schema-only build.

6. Service Contract — FilesService (not built this pass)

Binding future requirements for whoever builds FilesService:

• Every write to file.storage_key MUST use the documented path convention ({tenant_id}/{owner_module}/{owner_ref}/{id}.{ext}) for operator debuggability — but access control MUST NEVER be derived from that path shape; it is always the row's own tenant_id/visibility/RLS.
• A file transitions pending → uploaded only after R2 confirms the object landed; uploaded → ready only after both scan_status='clean' and (if extraction was attempted) extraction_status reaches a terminal state.
• The orphan-reconciliation job (§8) is a required, scheduled service-layer job — not optional, not deferred to "someday."
• tenant_storage_usage is a cache; the periodic recalculation job (§8) is required for it to stay trustworthy.
• consumer.get_files_for_consumer() is the only path a consumer-authenticated session may use to read files.file rows — FilesService itself never accepts a raw consumer_id filter parameter from a consumer-facing route without routing through that function.

7. Design Rationale (DR-1…9)

• DR-1 — R2 sole storage of record, S3 transient-only. See §4.1–4.3 in full. This preserves v1's own DR1 (bytes in R2, metadata in Postgres) while adding the Textract-driven staging exception v1 never needed to consider.
• DR-2 — one generic file table, polymorphic owner backref, not enforced FK. Preserved from v1 DR2 verbatim: owner_module/owner_type/owner_ref stay a loose, unenforced polymorphic triple (a real FK is structurally impossible against an open target set spanning a dozen future modules).
• DR-3 — 3 visibility tiers, public-RLS-bypass, the exact guard preserved from v1. visibility IN ('public','private','signed'). The RLS read policy is exactly WHERE visibility='public' OR tenant_id=current_tenant — not OR tenant_id IS NULL, preserving v1's own DR3 guard verbatim (a NULL-tenant, non-public row is a service_role-only artifact, never merchant-readable via this policy). signed is kept for the reason audit.audit_export_job.export_ref's own established usage demonstrates: a visibility='signed' file with no file_access_grant row is a service-layer signal meaning "force ephemeral-link-only access, never surface in ordinary tenant UI" — a distinction 'private' alone cannot express. (An earlier draft of this design justified signed via the DSR-delivery case instead; that case is actually covered by file_access_grant's own 3-condition check regardless of visibility tier, so it does not, on its own, require signed to exist — the audit_export_job no-grant usage is the rationale that actually survives scrutiny.)
• DR-4 — inventory.item_image stays in inventory, gains a real file_id. Not absorbed into files — display-specific metadata (alt_text, sort_order, is_primary) belongs with the module that renders it; files only ever supplies the underlying bytes.
• DR-5 — file_access_grant for what visibility can't express. Preserved verbatim from v1, 16 columns, zero changes — the DSR-to-subject legal-delivery case, and any other named-recipient, time-bounded, revocable grant.
• DR-6 — usage in files, limit in platform. tenant_storage_usage tracks bytes/objects only; the tier ceiling lives in platform.tenant_entitlement (2 new entitlement keys, deferred — see §10). Never add a limit column to any files.* table — this is both v1's own DR6 and the codebase-wide "usage-here/limit-in-platform" standing pattern (CROSS_MODULE_CONTRACTS.md).
• DR-7 — Platform's own R2 refs stay Platform-managed exceptions. platform.tenant_data_lifecycle.download_url, platform.agreement_version.document_url, platform.agreement_acceptance.signature_ref (a HelloSign envelope ID, not R2 at all), and platform.subscription_invoice.invoice_pdf_url (Stripe-hosted) are NOT routed through FilesService — routing legal/billing artifacts Platform already owns end-to-end through files would add an unnecessary dependency and obscure an ownership boundary that was already deliberately drawn in Platform's own build. Preserved verbatim from v1 DR7.
• DR-8 — the vector spine is built now, populated later. document_index/document_chunk are genuinely new (no v1 precedent — v1 predates any AI-search feature). Building the tables now, with embedding nullable and left NULL, means FTS works immediately via search_vector while semantic search remains a pure activation switch later (backfill-from-stored-text, never a schema change) — see §4.6.
• DR-9 — attachment is a new join table, not a retrofit of existing single-column forward-refs. The 8 already-existing forward-ref columns (tenant_branding.logo_ref, compliance_document.document_ref, stock_movement.photo_ref, item_image.file_id, goods_receipt.shipment_photo_ref, sale.signature_ref, customer_tax_certificate.document_ref, import_file.file_id) each express a genuinely 1:1 (or 1: at-most-one) relationship and are left as plain columns pointing at files.file.id once wired (§11) — no benefit to forcing them through attachment. attachment exists for the genuinely many-to-many cases this module is built to serve going forward (e.g. multiple damage photos on one receiving.goods_receipt, closing a real, disclosed limitation of shipment_photo_ref's single-column cap — not fixed this pass, logged to §10).

8. Named Pain Points — designed against directly

• Orphaned objects (the #1 operational pain in any file-storage system). The pending → uploaded → ready → (failed|deleted) lifecycle plus pending_expires_at is the schema half; a required, scheduled service-layer reconciliation job sweeps both directions: (1) pending rows past pending_expires_at are checked against R2 via HEAD request and marked failed if the object never landed (the row is never silently deleted — it's evidence of an incomplete upload); (2) R2 objects with no live file row claiming them are deleted from R2. Direction (2) has no schema representation of its own — it is a required, disclosed service-layer job, not implied to exist by the schema alone.
• Quota drift. tenant_storage_usage.bytes_used/object_count are maintained caches, reconciled periodically against SELECT SUM(file_size_bytes), COUNT(*) FROM files.file WHERE tenant_id=X AND status='ready' AND deleted_at IS NULL. This is honestly weaker than the same-transaction atomic-trigger counters this codebase's rewards/offers ledgers use, because a file upload's authoritative "did this really succeed" event is an external R2 confirmation — no Postgres trigger can fire an R2 HEAD request inside the same transaction. Drift is expected and bounded by the reconciliation job's own frequency, not eliminated by design.
• ACL in path only — rejected. storage_key's {tenant_id}/{owner_module}/{owner_ref}/{id}.{ext} convention exists purely for human debuggability. The isolation guarantee is always the row's own tenant_id/visibility/RLS, never the key's prefix shape.
• Signed-URL leakage. Recommended: a short TTL (5–15 minutes), and, where the use case allows it (a DSR package, a single named recipient's grant), a single-use token rather than a reusable link for the TTL's full duration.
• Overbuilt DMS — resisted. No versioning table in this build. No workflow engine (the review seam on attachment is enough for the one legitimate agent use case). Revisit only if a real compliance-document/contract version-history requirement appears — not speculatively built in.

9. Cross-Module Seams

• The 8 confirmed forward-ref columns (admin.tenant_branding.logo_ref, admin.compliance_document.document_ref, inventory.stock_movement.photo_ref, inventory.item_image.file_id, receiving.goods_receipt.shipment_photo_ref, pos.sale.signature_ref, crm.customer_tax_certificate.document_ref, ai.import_file.file_id) → real composite (col, tenant_id) → files.file(id, tenant_id) FKs, once a dedicated follow-up multi-module reopen wires them (§10 — files.file carries UNIQUE(id, tenant_id) from this build's own day one, so the prerequisite is already satisfied).
• files ← everything, going forward, via attachment — the polymorphic join for any new many-to-many file relationship (multiple damage photos, multiple supporting documents on one entity).
• files ↔ consumer — exclusively via consumer.get_files_for_consumer(p_consumer_id uuid), a consumer-schema-owned, parameter-scoped SECURITY DEFINER function (mirrors consumer.get_cross_tenant_activity()'s own established shape) that reads files.file WHERE consumer_id = p_consumer_id AND deleted_at IS NULL — no join, no cross-schema table access from files' own side, and no direct GRANT of any kind to consumer_authenticated on the files schema itself.
• Extraction pipeline → ai + approvals — not part of files' own schema. An extraction agent (via identity.agent_duty_grant) creates a draft row in the owning business module (e.g. purchasing.vendor_invoice), routed through approvals.approval_request (source_module='purchasing'). files' own contribution is file.extraction_status transitioning to extracted, plus an attachment row (entity_type='vendor_invoice', role='source_document') linking the source scan back to the draft record for a reviewer to open.
• Future search module — document_chunk.search_vector is already a complete, standalone FTS surface, parallel to (not merged with) the search_vector columns already living on inventory.item/item_variant, crm.customer, purchasing.vendor, orders.order_header, pos.sale. A future unified search module federates across all of these; it needs no schema change from files to do so.
• files → platform — tenant_storage_usage (usage) vs. platform.tenant_entitlement (limit); no FK, the standing usage-here/limit-in-platform pattern.

10. Deferred / Future Items

The Files FK-wiring bundle (§9, 6-module coordinated reopen); platform.tenant_entitlement's 2 new storage-limit keys and widening limit_value from int4 to bigint (must land together); file_access_grant.grantee_user_id/granted_by_user_id's candidate retarget to identity.actor (deferred, not applied this pass); receiving.goods_receipt.shipment_photo_ref's single-photo cap (a real limitation attachment could resolve — not fixed here); the orphan-reconciliation sweep's R2→no-row direction (required service-layer job, no schema representation); quota-recalculation frequency (an ops decision); semantic-search activation (the embedding-backfill pass, no trigger date set); integrations/audit (unbuilt) should include their own Files FK from day one once designed; file versioning (explicitly resisted, §8); a dedicated agent_reader Postgres role for agent-triggered vector reads (blocked on the AI agent-execution runtime existing — today's guarantee is the tenantDB()-only convention, honestly weaker than a GRANT-level guarantee, and a codebase-wide gap, not files-specific); an ESLint no-restricted-imports rule banning adminDb/getAdminDb from any future agent-execution code path (buildable independently of files, today); whether some consumer_id-tagged uploads (e.g. an ID/age-verification photo) need vault-style handling instead of standard files.file storage, mirroring crm.customer.pii_vault_ref/platform.tenant_profile.ein_ref (a human product decision, not resolved here); ai.import_file.file_id's live NOT NULL contradicting its own code comment claiming nullable (a pre-existing, unrelated ai-module bug, fix at a future ai touch); CROSS_MODULE_CONTRACTS.md's existing Files seam section needing its file_storage_usage references updated to tenant_storage_usage.

11. v1 Exclusions / Deltas Re-Confirmed

v1 (locked design, 2026-06-11, never migrated): 3 tables / 43 cols — file (19), file_access_grant (16), file_storage_usage (8). Every v1 table and column survives — nothing consolidated, nothing dropped. Two disclosed renames (file_storage_usage→tenant_storage_usage; r2_key/r2_bucket→storage_key/storage_bucket, justified by the new storage_provider column making the old R2-specific naming misleading), one widened CHECK (file.status gains 'ready', splitting "R2 confirmed" from "safe to serve"), and one FK retarget (uploaded_by_user_id→uploaded_by_actor_id → identity.actor, the standard autonomy-first pattern applied to every genuinely new attribution column in this codebase since PROJECT_DECISIONS #19). v2 adds 3 tables (attachment, document_index, document_chunk) and 46 net columns — every addition traces to a capability v1 never had a reason to model: the storage-provider/extraction pipeline (§4), the vector spine (§4.6, DR-8), and the polymorphic many-to-many join (DR-9). See PROJECT_DECISIONS #58 for the full build record and the mandatory independent verification findings.