files — module #26, the generic file-metadata registry

6 tables, 89 columns — schema-locked 2026-07-11. files owns file metadata over Cloudflare R2 (sole storage of record), the polymorphic attachment join, fine-grained access grants, storage-usage tracking, and the "vector spine" (document_index/document_chunk) built now for future semantic search, populated later. v1 planned this module (3 tables/43 cols, locked design 2026-06-11) but never built it — this is its first real v2 build: every v1 table/column survives (2 disclosed renames, 1 widened CHECK, 1 FK retarget), plus 3 genuinely new tables (+46 cols net). Foundation-layer schema (SCHEMA_CONVENTIONS.md §1) — depends on platform (tenant), identity (actor, identity_user); depended on by admin, crm, pos, receiving, inventory, ai (all via a plain file_id/*_ref forward-ref column, mostly still deferred), and the consumer layer (exclusively via consumer.get_files_for_consumer()).

PROJECT_DECISIONS entries: #58 (the R2/S3 storage-architecture decision) and #59 (the module build).

Global rules for this schema:

• Cloudflare R2 is the sole storage of record. AWS S3 is transient staging ONLY for AWS Textract's async extraction path — never a durable tier files itself models. See module_spec/files.md §4 and PROJECT_DECISIONS #58 for the full storage architecture + cost model.
• Foundation-layer FK discipline. file.consumer_id and file_access_grant.grantee_customer_id are deliberately LOOSE, unenforced uuid columns — no .references() — since SCHEMA_CONVENTIONS.md §1 forbids a foundation schema FK-ing into the consumer or business layers. Matches crm.customer.consumer_id's own established treatment. Independently confirmed live: zero FK constraint from any files.* table into consumer.*.
• The dual-principal (merchant/consumer) boundary is closed at the GRANT layer, not just RLS. authenticated (merchant) gets the standard blanket GRANT + ALTER DEFAULT PRIVILEGES on the whole files schema. consumer_authenticated gets ZERO grant of any kind on files — by construction AND an explicit belt-and-suspenders REVOKE. The one path a consumer session may use is consumer.get_files_for_consumer(p_consumer_id uuid) — owned by the consumer schema (the permitted calling direction), SECURITY DEFINER, parameter-scoped (never reads an ambient session GUC), mirroring consumer.get_cross_tenant_activity()'s own established shape exactly.
• files.file carries UNIQUE(id, tenant_id) from day one — the prerequisite every composite (col, tenant_id) → files.file(id, tenant_id) FK needs, built in at this module's own first lock rather than a later reopen (a first for this codebase's history — every prior module needing this prerequisite added it in a dedicated follow-up reopen).
• document_chunk is NOT append-only — embedding is populated later via a real UPDATE after initial insert. Re-indexing (a new extraction pass superseding a file's prior chunk set) soft-deletes the prior generation (UPDATE ... SET deleted_at = now()) before inserting the new one — never a hard DELETE (SCHEMA_CONVENTIONS.md §6). Every read filters WHERE deleted_at IS NULL.
• attachment.entity_type is a CLOSED CHECK enum; role stays open. Mirrors approvals.approval_request.source_module (closed — a typo'd module name is a referential-integrity risk) / .source_type (open — avoids CHECK-widening churn) split exactly.
• tenant_storage_usage tracks usage only — no limit column. The tier ceiling lives in platform.tenant_entitlement (2 new keys + an int4→bigint width fix, both deferred — see Open Items below). Never add a limit column to any files.* table (v1's own DR6, and the codebase-wide usage-here/limit-in-platform pattern).

Cross-Phase Foreign Keys (files)

Column	Target	Notes
file.tenant_id	platform.tenant	nullable — platform-level asset or a consumer-only file with no owning tenant
file.consumer_id	— (loose, no FK)	dimension only, populated at file-creation time by whichever service already resolved the consumer's identity
file.uploaded_by_actor_id	identity.actor	nullable — renamed + retargeted from v1's uploaded_by_user_id
attachment.tenant_id	platform.tenant	NOT NULL
attachment.file_id (composite, with tenant_id)	files.file(id, tenant_id)	composite FK, closes cross-tenant reference (live-tested: 23503)
attachment.entity_id	— (loose, no FK)	polymorphic, structurally impossible across an open target set
attachment.created_by_actor_id / .reviewed_by_actor_id	identity.actor	nullable
file_access_grant.file_id (composite, with tenant_id)	files.file(id, tenant_id)	composite FK
file_access_grant.grantee_user_id / .granted_by_user_id	identity.identity_user	v1's own targeting, unretouched — a retarget to identity.actor is a disclosed, deferred candidate
file_access_grant.grantee_customer_id	— (loose, no FK)	foundation-layer discipline, same as file.consumer_id
tenant_storage_usage.tenant_id	platform.tenant	NOT NULL
document_index.file_id (composite, with tenant_id)	files.file(id, tenant_id)	composite FK
document_chunk.file_id (composite, with tenant_id)	files.file(id, tenant_id)	composite FK
consumer.get_files_for_consumer(p_consumer_id)	reads files.file	SECURITY DEFINER, owned by consumer schema, not a table-level FK

---

files.file (27 cols)

The core registry row: one per stored object, its location, lifecycle, scan/extraction status.

Tenant-scoped (nullable tenant_id). RLS enabled — file_tenant_isolation, with the DR-3 public-visibility bypass (USING (visibility = 'public' OR tenant_id = current_tenant)) preserved verbatim from v1. Soft delete: deleted_at. updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	nullable	—	FK → platform.tenant
consumer_id	UUID	nullable	—	Loose dimension, no FK
created_at / updated_at / deleted_at	timestamptz	standard	now() / now() / —
storage_provider	text	NOT NULL	'r2'	CHECK IN (r2,s3); always 'r2' in practice today
storage_bucket	text	nullable	—	Renamed from r2_bucket
storage_key	text	NOT NULL	—	Renamed from r2_key; UNIQUE WHERE deleted_at IS NULL
file_name	text	NOT NULL	—
mime_type	text	NOT NULL	—
file_size_bytes	bigint	NOT NULL	—	CHECK >= 0
file_hash	text	nullable	—	SHA-256
visibility	text	NOT NULL	'private'	CHECK IN (public,private,signed) — v1's exact 3 values
status	text	NOT NULL	'pending'	CHECK IN (pending,uploaded,ready,failed,deleted) — widened from v1's 4 values (+ready)
scan_status	text	NOT NULL	'pending'	CHECK IN (pending,clean,infected)
extraction_status	text	NOT NULL	'pending'	CHECK IN (pending,extracted,needs_ocr,processing,failed)
textract_job_id	text	nullable	—	Async-Textract path only
owner_module / owner_type / owner_ref	text/text/UUID	nullable	—	v1's polymorphic owner backref trio, loose, unenforced (preserved verbatim)
uploaded_by_actor_id	UUID	nullable	—	FK → identity.actor; renamed + retargeted from uploaded_by_user_id
retain_until	timestamptz	nullable	—
pending_expires_at	timestamptz	nullable	—	The pending-upload half of the orphan-reconciliation sweep
uploaded_at	timestamptz	nullable	—
last_accessed_at	timestamptz	nullable	—
user_metadata	jsonb	nullable	—	e.g. {"campaign": "spring-2026", "alt_source": "vendor_portal"}

CHECK constraints (6): chk_file_storage_provider; chk_file_visibility; chk_file_status; chk_file_scan_status; chk_file_extraction_status; chk_file_size_nonneg.

Indexes: PK; tenant_id; consumer_id partial WHERE not null; storage_key unique WHERE deleted_at IS NULL; status partial WHERE IN (pending,failed); scan_status partial WHERE != 'clean'; extraction_status partial WHERE = 'needs_ocr'; pending_expires_at partial WHERE not null; (owner_module, owner_type, owner_ref) partial WHERE owner_ref not null.

Constraints: file_id_tenant_id_unique UNIQUE(id, tenant_id) — the composite-FK prerequisite for every child table below.

Triggers: set_updated_at.

---

files.attachment (17 cols, NEW)

The polymorphic many-to-many join: which file(s) attach to which business entity, in what role. Full autonomy pack — the AI bulk-photo-matching review seam.

Tenant-scoped. RLS enabled — attachment_tenant_isolation. Soft delete: deleted_at. updated_at: trigger-maintained.

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
file_id	UUID	NOT NULL	—	Composite FK → files.file(id, tenant_id)
entity_type	text	NOT NULL	—	CLOSED CHECK IN (product,purchase_order,vendor_invoice,compliance_document,goods_receipt,other)
entity_id	UUID	NOT NULL	—	Loose polymorphic, no FK
role	text	nullable	—	Open — e.g. primary_image,thumbnail,care_guide,damage_photo,source_document
sort_order	integer	NOT NULL	0
created_by_actor_id	UUID	nullable	—	FK → identity.actor
automation_source	text	NOT NULL	'human'	CHECK IN (human,agent,system,seed)
review_status	text	NOT NULL	'not_required'	CHECK IN (not_required,pending,approved,rejected)
review_reason	text	nullable	—
reviewed_by_actor_id	UUID	nullable	—	FK → identity.actor; cannot equal created_by_actor_id
reviewed_at	timestamptz	nullable	—
decision_provenance	jsonb	nullable	—
created_at / updated_at / deleted_at	timestamptz	standard	now() / now() / —

CHECK constraints (4): chk_attachment_entity_type; chk_attachment_automation_source; chk_attachment_review_status; chk_attachment_reviewer_not_creator.

Indexes: PK; tenant_id; (tenant_id, entity_type, entity_id) — the reverse-lookup path, matching tax.tax_calculation/billing.ar_charge's own identical-shaped index; (file_id, entity_type, entity_id) unique WHERE deleted_at IS NULL.

Triggers: set_updated_at.

---

files.file_access_grant (16 cols)

v1 verbatim, zero column changes. Fine-grained access for what visibility alone can't express (e.g. a DSR package delivered to a named recipient).

Tenant-scoped. RLS enabled — file_access_grant_tenant_isolation. Soft delete: deleted_at. updated_at: trigger-maintained.

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at / updated_at / deleted_at	timestamptz	standard	now() / now() / —
file_id	UUID	NOT NULL	—	Composite FK → files.file(id, tenant_id)
grantee_type	text	NOT NULL	—	CHECK IN (user,customer,external,public_link)
grantee_user_id	UUID	nullable	—	FK → identity.identity_user
grantee_customer_id	UUID	nullable	—	Loose, no FK (foundation-layer discipline)
grantee_identifier	text	nullable	—	e.g. an external email
access_level	text	NOT NULL	—	CHECK IN (read,download)
expires_at	timestamptz	nullable	—
revoked_at	timestamptz	nullable	—
granted_by_user_id	UUID	nullable	—	FK → identity.identity_user
access_count	integer	NOT NULL	0	CHECK >= 0
last_accessed_at	timestamptz	nullable	—

CHECK constraints (4): chk_file_access_grant_grantee_type; chk_file_access_grant_access_level; chk_file_access_grant_grantee_presence (NULL-safe — each grantee_type branch explicitly requires its own matching column IS NOT NULL, public_link requires none); chk_file_access_grant_access_count_nonneg.

Indexes: PK; tenant_id; file_id; file_id partial WHERE revoked_at IS NULL AND deleted_at IS NULL (the active-grant fast path).

Triggers: set_updated_at.

---

files.tenant_storage_usage (8 cols)

Renamed from v1's file_storage_usage (total_bytes→bytes_used, file_count→object_count, last_calculated_at→last_recalculated_at; wording only, zero capability change). Cached counter — the tier limit lives in platform.tenant_entitlement.

Tenant-scoped, one active row per tenant. RLS enabled — tenant_storage_usage_tenant_isolation. Soft delete: deleted_at. updated_at: trigger-maintained.

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant; UNIQUE WHERE deleted_at IS NULL
bytes_used	bigint	NOT NULL	0	CHECK >= 0 — renamed from total_bytes
object_count	integer	NOT NULL	0	CHECK >= 0 — renamed from file_count
last_recalculated_at	timestamptz	nullable	—	Renamed from last_calculated_at
created_at / updated_at / deleted_at	timestamptz	standard	now() / now() / —

CHECK constraints (2): chk_tenant_storage_usage_bytes_used_nonneg; chk_tenant_storage_usage_object_count_nonneg.

Indexes: PK; tenant_id unique WHERE deleted_at IS NULL.

Triggers: set_updated_at.

---

files.document_index (10 cols, NEW — the vector spine, part 1)

Per-file indexing status for the text/vector search surface, built now, populated later.

Tenant-scoped, one active row per file. RLS enabled — document_index_tenant_isolation. Soft delete: deleted_at (added per design-verification — SCHEMA_CONVENTIONS §4 mandatory column). updated_at: trigger-maintained. 2026-07-17 (Phase 6 of the agents-v2/v3 build): a NEW, dedicated policy document_index_agent_reader_select (FOR SELECT TO agent_reader, same tenant predicate) also grants the new agent_reader role read access — see "The agent-reader seam" below.

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
file_id	UUID	NOT NULL	—	Composite FK → files.file(id, tenant_id); UNIQUE(tenant_id, file_id) WHERE deleted_at IS NULL
source_type	text	NOT NULL	—	CHECK IN (vendor_document,contract,tax_form,insurance_cert,care_guide,free_text_note,other) — what KIND of unstructured document, distinct from file.owner_module/.owner_type (who it belongs to)
status	text	NOT NULL	'processing'	CHECK IN (processing,ready,failed)
error_reason	text	nullable	—	Populated only when status='failed'
last_indexed_at	timestamptz	nullable	—
created_at / updated_at / deleted_at	timestamptz	standard	now() / now() / —

CHECK constraints (2): chk_document_index_source_type; chk_document_index_status.

Indexes: PK; tenant_id; (tenant_id, file_id) unique WHERE deleted_at IS NULL; status partial WHERE != 'ready'.

Triggers: set_updated_at.

---

files.document_chunk (11 cols, NEW — the vector spine, part 2)

Chunked extracted text + optional embedding — the FTS/semantic-search unit. NOT append-only — embedding is populated later via a real UPDATE.

Tenant-scoped. RLS enabled — document_chunk_tenant_isolation. Soft delete: deleted_at (added per design-verification, alongside the corrected soft-delete re-indexing contract — the pre-verification draft's own description was an undisclosed hard DELETE). updated_at: trigger-maintained (genuinely mutated when embedding is populated). 2026-07-17 (Phase 6 of the agents-v2/v3 build): a NEW, dedicated policy document_chunk_agent_reader_select (FOR SELECT TO agent_reader, same tenant predicate) also grants the new agent_reader role read access — see "The agent-reader seam" below.

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
file_id	UUID	NOT NULL	—	Composite FK → files.file(id, tenant_id)
chunk_index	integer	NOT NULL	—	CHECK >= 0; UNIQUE(file_id, chunk_index) WHERE deleted_at IS NULL
page_number	integer	nullable	—	NULL when the source has no page concept
text	text	NOT NULL	—	Set once, at extraction time — the single, universal extracted-text store regardless of extraction tier
search_vector	tsvector	generated	to_tsvector('english', text) STORED	GIN-indexed; works immediately with zero embeddings
embedding	vector(1024)	nullable	—	Amazon Titan Text Embeddings V2 dimension; left NULL until semantic search activates
created_at / updated_at / deleted_at	timestamptz	standard	now() / now() / —

CHECK constraints (1): chk_document_chunk_chunk_index_nonneg.

Indexes: PK; tenant_id; (tenant_id, file_id); (file_id, chunk_index) unique WHERE deleted_at IS NULL; search_vector GIN. No vector similarity index (ivfflat/hnsw) yet — deliberately deferred until embeddings are actually populated.

Triggers: set_updated_at.

---

The seams

The consumer-boundary seam. consumer.get_files_for_consumer(p_consumer_id uuid) — owned by consumer (not files), SECURITY DEFINER, SET search_path = pg_catalog, reads SELECT * FROM files.file WHERE consumer_id = p_consumer_id AND deleted_at IS NULL. No JOIN needed at query time — files.file.consumer_id is already a direct dimension column. files grants zero USAGE to consumer_authenticated; this function (owned by consumer, which already has USAGE from the prior consumer/rewards/offers build) reads on the caller's behalf, mirroring get_cross_tenant_activity()'s own established shape. Live-tested both directions: the correct consumer's file is returned; a different consumer id returns zero rows; consumer_authenticated cannot query files.file directly (42501).

The agent-reader seam (2026-07-17, Phase 6 of the agents-v2/v3 build). A new agent_reader Postgres role (NOLOGIN NOINHERIT, mirrors consumer_authenticated's own shape exactly, granted to authenticator) gets GRANT SELECT on document_chunk/document_index plus 2 NEW, dedicated RLS policies — document_chunk_agent_reader_select / document_index_agent_reader_select (FOR SELECT TO agent_reader, the same tenant_id = current_setting('app.current_tenant_id')::uuid predicate as the existing authenticated-scoped policies). These are genuinely new policies, not a widening of the existing ones — Postgres role-scoped RLS policies apply only to the exact role(s) named in USING/TO; document_chunk_tenant_isolation/document_index_tenant_isolation are scoped to authenticated alone (confirmed live via pg_policies: roles = {authenticated}) and do not extend to agent_reader. A real gap was found and fixed during this build: GRANT SELECT to agent_reader with no corresponding policy would have left every agent_reader query silently returning zero rows. This closes the OPEN_ITEMS row that flagged agent-triggered vector reads as needing a GRANT-level (not just conventional) tenant-isolation guarantee. All 4 guards (tenant-scoped read, cross-tenant isolation, write rejection 42501, EXECUTE on the signals.*_as_of() functions) live-reproduced. Grant/policy-only — files stays 6 tables / 89 columns, no table or column change. agent_reader/agentReaderDB() (the mirrored consumerDB()-shaped connection helper in packages/db/src/client.ts) have zero real call sites today — no AgentsService or other agent-execution read path exists yet — logged as a new, disclosed OPEN_ITEMS row rather than treated as fully closed. See PROJECT_DECISIONS #67 and packages/db/migrations/20260717000001_agent_reader_role.sql.

The 8 forward-ref seams (mostly still deferred). 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 — all plain columns pointing at files.file.id, none yet wired to a real composite FK (a dedicated follow-up reopen, now unblocked since files.file carries UNIQUE(id, tenant_id) from this build's own day one).

The extraction-pipeline seam (service-layer, not part of this schema). An extraction agent 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: file.extraction_status → 'extracted', plus an attachment row (entity_type='vendor_invoice', role='source_document') linking the source scan back to the draft.

The Files↔Platform quota seam. tenant_storage_usage (usage, here) vs. platform.tenant_entitlement (limit, there) — no FK, the standing usage-here/limit-in-platform pattern.

---

files — Design Patterns Summary

Column-count reconciliation

Table	Cols
file	27
attachment	17
file_access_grant	16
tenant_storage_usage	8
document_index	10
document_chunk	11
Total	89

Verified live: 6 tables / 89 columns (v1: 3 tables / 43 cols → +3 tables / +46 cols). Every v1 table and column survives — zero consolidation, zero drop. 17 CHECK constraints across the 6 tables (6 + 4 + 4 + 2 + 2 + 1); 4 composite (col, tenant_id) → files.file(id, tenant_id) FKs (attachment, file_access_grant, document_index, document_chunk), all independently live-tested for cross-tenant rejection (23503); RLS enabled + a tenant-isolation policy on all 6 tables; 6 set_updated_at triggers (all 6 tables are genuinely mutable — document_chunk is deliberately NOT append-only since embedding is updated post-insert); 1 new function, consumer.get_files_for_consumer(), owned by the consumer schema.

2026-07-17 addendum (Phase 6 of the agents-v2/v3 build): document_chunk/document_index each gained 1 new agent_reader-scoped SELECT RLS policy (see "The agent-reader seam" above) — grant/policy-only, no new table, no new column. Still 6 tables / 89 columns.

Service layer

No FilesService yet — schema-only this pass.

JSONB columns

file.user_metadata (documented example inline); attachment.decision_provenance (project-wide reason/evidence/confidence/memory_refs convention, same shape as every other autonomy-pack table).

Open items carried forward (see OPEN_ITEMS.md)

The Files FK-wiring bundle (8 forward-ref columns → real composite FKs, a 6-module coordinated reopen); platform.tenant_entitlement's 2 new storage-limit keys + the int4→bigint width fix (must land together); file_access_grant.grantee_user_id/.granted_by_user_id's deferred retarget candidate to identity.actor; 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-object-with-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); a dedicated agent_reader Postgres role for agent-triggered vector reads — CLOSED 2026-07-17 (Phase 6 of the agents-v2/v3 build): now exists, with GRANT SELECT + 2 new dedicated RLS policies on document_chunk/document_index (see "The agent-reader seam" above and PROJECT_DECISIONS #67); agentReaderDB() having zero real call sites yet is the new, disclosed open item; an ESLint no-restricted-imports rule banning adminDb/getAdminDb from agent-execution code paths (buildable today, independent of files); whether some consumer_id-tagged uploads need vault-style handling instead of standard files.file storage (human product decision); ai.import_file.file_id's live NOT NULL contradicting its own code comment claiming nullable (pre-existing ai-module bug, unrelated to this build); CROSS_MODULE_CONTRACTS.md's Files seam section needing its file_storage_usage references updated to tenant_storage_usage.