Attachment System Tables

Attachments (attachments)

Purpose: Manages file attachments for various entities or as shared resources

Use Case Example: Upload a project poster image that displays on the project dashboard, or attach a call sheet PDF to a specific shooting day.

Column	Type	Description
id	UUID	Unique identifier
file_name_original	TEXT	Original filename as uploaded
file_name_stored	TEXT	System-generated unique filename
storage_path_or_url	TEXT	Full path in storage or external URL
mime_type	TEXT	File MIME type (e.g., 'image/jpeg', 'application/pdf')
file_size_bytes	BIGINT	File size in bytes
file_hash	TEXT	SHA-256 hash of file content for duplicate detection
description	TEXT	Optional description of the file
uploaded_by_user_id	UUID	User who uploaded the file
parent_entity_type	TEXT	Type of parent entity (e.g., 'project', 'budget_item')
parent_entity_id	UUID	ID of the parent entity
external_id	TEXT	External ID from third-party services (e.g., WhatsApp media ID)
external_id_created_at	TIMESTAMPTZ	When the external ID was created (for expiration tracking)
project_id	UUID	Optional reference to the project (FK to projects)
organisation_id	UUID	Optional reference to the organisation (FK to organisations). NULL for user-level attachments like avatars
superseded_by_id	UUID	Reference to the attachment that supersedes this one (FK to attachments)
form_submission_id	UUID	Form submission a form-fill upload belongs to (FK to form_submissions, ON DELETE SET NULL). Server-managed
created_at	TIMESTAMPTZ	Upload timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Key Features:

• Can be linked to any entity type via parent_entity_type/id
• Tracks both internal storage and external URLs
• Maintains upload history with user tracking
• Supports project posters and document attachments
• Supports attachment versioning via superseded_by_id self-reference
• form_submission_id (partial index idx_attachments_form_submission_id WHERE NOT NULL) durably links a form-fill upload to its submission, replacing the old description-marker association

form_submission_id — server-managed with DB-point guards: the column is set only by the service-role form-fill flow; three private trigger functions enforce this and serialise attachment writes against the submission lifecycle. All are no-ops when form_submission_id is NULL, so every non-form attachment flow is untouched.

• private.guard_attachment_form_submission_id (SECURITY INVOKER, on BEFORE INSERT OR UPDATE OF form_submission_id): rejects any INSERT that sets, or UPDATE that changes, form_submission_id when the caller is the authenticated or anon role. INVOKER so current_user reflects the actual caller (a DEFINER fn would see its owner); this catches both JWT-carrying and claim-less sessions where auth.role() alone would not.
• private.guard_form_attachment_insert (SECURITY DEFINER, BEFORE INSERT WHEN form_submission_id IS NOT NULL): locks the submission row FOR UPDATE, requires status IN ('requested','in_progress'), and enforces a per-submission cap of 20 attachments. Serialises against the submission's terminal transition.
• private.guard_form_attachment_delete (SECURITY DEFINER, BEFORE DELETE WHEN form_submission_id IS NOT NULL): locks the submission FOR UPDATE, requires active status, and rejects the delete when the attachment has already been promoted (referenced by project_relationship_attachments or entity_attachments).

See VIEWS_AND_FUNCTIONS.md for the trigger-function ACL/definer details and FORMS.md for the capability flow.

RLS (SELECT / INSERT) — scoped to the owning tenant via the row's own project_id / organisation_id columns (NOT the parent entity type):

• SELECT a row if any of: you uploaded it; it is project-owned and you have access to that project_id (caller_has_permission('{}', project_id, true), incl. org cascade); it is org-owned (no project) and you are a member of that organisation_id (v_user_accessible_organisations); or it has neither tenant column set. The no-tenant case is intentional — those are public-assets attachments (integration-provider logos, import-mapping templates) whose files live in the public public-assets bucket and are not tenant data. The row_is_visible_to_caller sensitivity gate also applies.
• INSERT mirrors the same tenant scoping and additionally requires uploaded_by_user_id = auth.uid().
• Earlier the SELECT/INSERT policies had a parent_entity_type <> 'project' carve-out that treated every non-project attachment (transaction receipts, entity docs, budget files) as effectively public — a cross-organisation metadata leak (storage paths, file names, hashes). That carve-out was removed.

Entity Attachments (entity_attachments)

Purpose: Links attachments to entities with categorization and versioning support for identity documents, contracts, and other entity-related files.

Use Case Example: Upload a passport scan for a cast member entity, which can later be superseded by a newer passport when renewed.

Column	Type	Description
id	UUID	Unique identifier
entity_id	UUID	Reference to entity (FK to entities)
attachment_id	UUID	Reference to attachment record (FK to attachments)
attachment_type	entity_attachment_type	Type of attachment (identity_document, contract, certificate, etc.)
document_key	TEXT	Unique key for document type per entity (e.g., 'gb_passport', 'nz_driver_license')
description	TEXT	Optional description of the attachment
metadata	JSONB	Additional metadata (OCR extracted data, expiry dates, etc.)
is_superseded	BOOLEAN	Whether this attachment has been superseded by a newer version (default false)
created_at	TIMESTAMPTZ	Creation timestamp
created_by_user_id	UUID	User who created the link (FK to users)

Key Features:

• Links attachments to entities with type categorization
• document_key enables versioning of same document type (e.g., multiple passports from different countries)
• Partial unique index ensures only one active (non-superseded) document per entity per document_key
• is_superseded flag maintains historical records while tracking current valid documents
• metadata field stores OCR-extracted data for identity documents
• RLS policies use v_user_accessible_organisations for access control

Project Relationship Attachments (project_relationship_attachments)

Purpose: Links attachments to a specific project_relationship (a person-in-this-project), distinct from entity_attachments which are global to the entity. Used for documents that belong to the relationship rather than the person — contracts, starter forms, proof of work, certificates, and the per-relationship copy of an identity document used to verify the person for THIS project role.

Use Case Example: Upload a signed contract for a crew member when they accept their role on a specific film production. The same person might have a different contract on a different project — each relationship gets its own row.

Column	Type	Description
id	UUID	Unique identifier
project_relationship_id	UUID	Reference to the project_relationship this attachment belongs to (FK to project_relationships, ON DELETE CASCADE)
attachment_id	UUID	Reference to the underlying attachment record (FK to attachments)
attachment_type	project_relationship_attachment_type	Type/category of the attachment (identity_document, contract, starter_form, proof_of_work, certificate, other)
description	TEXT	Optional description or notes about the attachment
metadata	JSONB	Additional metadata about the attachment (e.g., OCR extraction results, parsed document details)
is_superseded	BOOLEAN	Whether this attachment has been replaced by a newer version (default false). TRUE means it is a historical record
superseded_by_id	UUID	When is_superseded is true, points to the newer project_relationship_attachments row that replaced this one (self-FK). Forms an explicit replace chain
created_at	TIMESTAMPTZ	Creation timestamp
created_by_user_id	UUID	User who created this record (FK to users, default auth.uid())

Key Features:

• Distinct from entity_attachments — these are scoped to a specific person-in-this-project, not globally to the entity
• attachment_type covers contracts, starter forms, proof of work, certificates, identity documents (the per-relationship copy), and a catch-all other
• Partial unique index (project_relationship_id, attachment_type) WHERE is_superseded = false AND attachment_type <> 'other' enforces "one active doc per type per relationship", with other exempt as a multi-use bucket
• superseded_by_id is a self-FK forming an explicit replace chain so previous versions can be walked back from the current row
• The legacy project_relationships.identity_attachment_id column was dropped in migration 20260403000000 and existing references were backfilled into this table with attachment_type = 'identity_document'
• The corresponding attachments.parent_entity_type is set to project_relationships and parent_entity_id to the project_relationship id, so attachment-history queries (useSupersededAttachmentsQuery) work the same way as for transactions and entities
• RLS policies gate access by joining through project_relationships → projects to v_user_accessible_organisations for auth.uid()

Storage-object ownership (storage.objects) — the ingest asymmetry

Attachment ROWS live in attachments; the bytes live in the files bucket as storage.objects. The two have different access models, and the mismatch is a recurring source of bugs.

The files bucket policies (verified live on production):

Command	Policy	Predicate
SELECT	Users can view files they have access to	owner = auth.uid() OR a matching attachments row the caller can reach (own upload, or project_id in caller_accessible_project_ids)
INSERT	Users can upload files to their organisations	path-scoped: organisations/<code>/… via caller_can_access_organisation_code, or users/<auth.uid()>/…
UPDATE	Users can update their own files	owner = auth.uid()
DELETE	Users can delete their own files	owner = auth.uid()

The asymmetry: SELECT is generous (it falls back to the attachments join), but UPDATE/DELETE are strictly owner-scoped. Every service-role write — email ingest, WhatsApp/Sana, server-side adoption of a staged file — records owner = NULL, because there is no auth.uid() in that context. On production 1205 of 1305 files objects (~92%) have owner IS NULL.

Consequences for any flow that mutates an existing object:

• A user-session move/rename/overwrite of an ingested object always fails. storageHelper.moveFile returns false for this (it collapses both API errors and thrown exceptions into false), so an unchecked if (moved) silently skips the follow-up bookkeeping.
• A subsequent upload to the same key with upsert:false then 409s, because the original object is still sitting there.
• It looks intermittent in testing: an admin replacing their own upload is owner-matched and succeeds, and a re-upload whose hash matches an existing project file takes a pure-DB path that never touches storage at all. The real-world case (replacing an emailed invoice) fails every time. This was the DEV-823 production bug.

The pattern to follow: perform the storage writes under a service-role client — runWithContext({ ...ctx(), database: createServiceRoleClient() }, …) — from the server layer, behind an explicit authorization gate that (a) reads the record under the caller's RLS, (b) derives project/organisation/current-attachment from THAT read rather than trusting client-supplied ids, and (c) mirrors the UI's real entitlement rules. Thread the actor explicitly (uploaded_by_user_id = ctx().userId) — under service role there is no auth.uid() to default to. Reference implementation: replaceTransactionDocumentFromStagedDocument in services/transaction/server.ts.

Do NOT loosen the bucket's UPDATE policy to work around this — it is deliberately owner-scoped (migration 20260615222658), and widening it affects every consumer of the bucket.

To check the current split in any environment:

SELECT owner IS NULL AS service_role_written, count(*)
FROM storage.objects
WHERE bucket_id = 'files'
GROUP BY 1;