# Unified Attachments **Date:** 2026-05-31 **Status:** Draft **Spec:** 41 **Depends on:** `foundation-auth-rbac`, `foundation-design-system`, `tasks-detail-communication`, `expenses-module`, `crm-support-center`, `kb-module` **Referenced by:** `tasks-detail-communication`, `expenses-module`, `crm-support-center`, `kb-articles` --- ## Problem Attachments are implemented independently in three modules, producing three near-identical database tables, three upload endpoints, three URL-generation strategies, and three React components that render the same chip UI. Each duplication drifts in subtle ways: | Concern | Tasks | Expenses | Support Tickets | |---|---|---|---| | Table | `task_message_attachments` | `expenses` (inline columns) | `ticket_message_attachments` | | Max size | 25 MB | 10 MB | 25 MB | | Mime allowlist | broad | jpg/png/heic/pdf | broad | | R2 key format | `{tenantId}/tasks/{taskId}/{uuid}-{filename}` | `{tenantId}/expenses/{expenseId}/{uuid}` | undefined | | Signed URLs | per-spec | not specified | not specified | | Soft delete | no | no | no | | PDF viewer | yes | no | no | Adding attachments to KB articles would require a fourth duplication. This spec unifies everything. --- ## Solution A single `attachments` table covers all entity types. A single upload endpoint enforces per-entity validation rules. A single signed-URL endpoint caches results in Cloudflare KV. Two reusable React components (`` and ``) replace the scattered inline implementations. A `` modal is shared across all contexts. Existing per-entity attachment tables (`task_message_attachments`, `ticket_message_attachments`) are superseded by this spec. Expense attachment columns in the `expenses` table are superseded. A migration moves existing rows and R2 objects to the new convention. --- ## Database Schema ```sql CREATE TABLE attachments ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE, entity_type TEXT NOT NULL CHECK (entity_type IN ( 'task_message', 'expense', 'ticket_message', 'kb_article', 'vendor' )), entity_id UUID NOT NULL, uploader_id UUID NOT NULL REFERENCES users(id) ON DELETE SET NULL, filename TEXT NOT NULL, -- original filename, sanitised r2_key TEXT NOT NULL UNIQUE, mime_type TEXT NOT NULL, file_size_bytes INTEGER NOT NULL CHECK (file_size_bytes > 0), created_at TIMESTAMPTZ NOT NULL DEFAULT now(), deleted_at TIMESTAMPTZ -- soft delete; NULL = live ); -- Fast lookup: all attachments for an entity CREATE INDEX idx_attachments_entity ON attachments (tenant_id, entity_type, entity_id) WHERE deleted_at IS NULL; -- Uploader history (for permission checks & audit) CREATE INDEX idx_attachments_uploader ON attachments (tenant_id, uploader_id) WHERE deleted_at IS NULL; ``` **Drizzle table definition** (`src/db/schema/attachments.ts`): ```ts import { pgTable, uuid, text, integer, timestamp } from 'drizzle-orm/pg-core'; export const attachments = pgTable('attachments', { id: uuid('id').primaryKey().defaultRandom(), tenantId: uuid('tenant_id').notNull(), entityType: text('entity_type').notNull(), // union enforced in app layer entityId: uuid('entity_id').notNull(), uploaderId: uuid('uploader_id').notNull(), filename: text('filename').notNull(), r2Key: text('r2_key').notNull().unique(), mimeType: text('mime_type').notNull(), fileSizeBytes: integer('file_size_bytes').notNull(), createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(), deletedAt: timestamp('deleted_at', { withTimezone: true }), }); export type Attachment = typeof attachments.$inferSelect; export type NewAttachment = typeof attachments.$inferInsert; ``` --- ## R2 Key Convention ``` {tenantId}/{entityType}/{entityId}/{uuid}-{safeFilename} ``` - `tenantId` — UUID of the owning tenant (namespace isolation) - `entityType` — the `entity_type` value: `task_message`, `expense`, `ticket_message`, `kb_article`, or `vendor` - `entityId` — UUID of the parent entity - `uuid` — `crypto.randomUUID()` generated at upload time - `safeFilename` — original filename after sanitisation: lowercase, spaces → `_`, strip non-`[a-z0-9._-]` characters, max 100 chars **Examples:** ``` 3f4a…/task_message/9c1b…/01928a…-project_brief.pdf 3f4a…/expense/7e3d…/01928b…-receipt.jpg 3f4a…/ticket_message/2aa9…/01928c…-screenshot.png 3f4a…/kb_article/b5f0…/01928d…-diagram.png ``` #### Filename Component Sanitization The `{filename}` component in all R2 keys must be sanitized before key construction. The UUID prefix handles tenant isolation; sanitization prevents bidi override characters and null bytes from corrupting admin log display. ```ts function sanitizeFilenameComponent(original: string): string { return original .replace(/[/\\]/g, '_') // strip path separators (defense in depth) .replace(/\0/g, '') // strip null bytes .replace(/[‪-‮⁦-⁩]/g, '') // strip bidi override chars .slice(0, 200) // cap length for key legibility } // Mandatory key construction pattern for all entity types: const r2Key = `${tenantId}/${module}/${entityId}/${randomUUID()}-${sanitizeFilenameComponent(filename)}` ``` This applies to every row in the per-entity validation matrix below. --- ## Per-Entity Validation Matrix | `entity_type` | Max size | Allowed MIME types | |---|---|---| | `task_message` | 25 MB | `image/*`, `application/pdf`, `text/plain`, `text/csv`, `application/vnd.openxmlformats-officedocument.*`, `application/msword`, `application/vnd.ms-excel`, `application/zip`, `video/mp4`, `video/quicktime` | | `expense` | 10 MB | `image/jpeg`, `image/png`, `image/heic`, `image/heif`, `application/pdf` | | `ticket_message` | 25 MB | same as `task_message` | | `kb_article` | 25 MB | `image/jpeg`, `image/png`, `image/gif`, `image/webp`, `image/svg+xml`, `application/pdf` | | `vendor` | 10 MB | `application/pdf`, `image/jpeg`, `image/png` (withholding-exemption certificate, spec 182) | MIME type is validated server-side against the binary magic bytes using `file-type` (not the `Content-Type` header, which is client-controlled). File extension must be consistent with detected MIME type or the upload is rejected. --- ## API ### Upload ``` POST /api/attachments Authorization: Bearer Content-Type: multipart/form-data Fields: file File required entity_type string required — 'task_message' | 'expense' | 'ticket_message' | 'kb_article' | 'vendor' entity_id string required — UUID of the parent entity ``` **Flow:** 1. Authenticate caller; resolve `tenant_id` from JWT. 2. Verify caller has read access to the entity (`entity_type` + `entity_id` must exist and belong to the same tenant). 3. Parse the file from the multipart body (stream directly to R2; do not buffer in Worker memory). 4. Detect MIME type from first 4 KB of bytes using `file-type`. 5. Validate against the per-entity matrix (size, mime). 6. Generate `r2_key` per convention above. 7. Upload stream to R2 with `httpMetadata: { contentType: detectedMime }`. 8. Insert row into `attachments`. 9. Return `201`: ```json { "id": "uuid", "filename": "project_brief.pdf", "mime_type": "application/pdf", "file_size_bytes": 204800, "created_at": "2026-05-31T12:00:00Z" } ``` **Error responses:** | Status | Code | Condition | |---|---|---| | 400 | `MISSING_FIELD` | `file`, `entity_type`, or `entity_id` absent | | 400 | `INVALID_ENTITY_TYPE` | unknown `entity_type` | | 400 | `FILE_TOO_LARGE` | exceeds per-entity limit | | 400 | `MIME_NOT_ALLOWED` | detected MIME not in allowlist | | 400 | `MIME_EXTENSION_MISMATCH` | extension inconsistent with detected MIME | | 403 | `FORBIDDEN` | caller cannot access the target entity | | 404 | `ENTITY_NOT_FOUND` | `entity_id` does not exist or belongs to another tenant | | 413 | — | Content-Length exceeds hard cap of 25 MB (rejected before parsing) | --- ### Get Signed URL ``` GET /api/attachments/:id/url Authorization: Bearer ``` Returns a time-limited signed URL for direct R2 download. **Flow:** 1. Load the `attachments` row; verify `tenant_id` matches caller and `deleted_at IS NULL`. 2. Check Cloudflare KV namespace `ATTACHMENT_URL_CACHE` for key `url:{id}`. 3. **Cache hit** → return cached URL immediately. 4. **Cache miss** → call `R2Bucket.createSignedUrl(r2Key, { expiresIn: 3600 })`, store in KV with TTL of **3300 seconds** (55 min), return URL. ```json { "url": "https://.r2.cloudflarestorage.com//…?X-Amz-Expires=3600&…", "expires_at": "2026-05-31T13:00:00Z" } ``` KV key format: `url:{attachmentId}` KV value: `{ url: string, expires_at: ISO8601 }` The 55-minute KV TTL ensures cached URLs are always served with at least 5 minutes of remaining validity. --- ### List Attachments ``` GET /api/attachments?entity_type=task_message&entity_id=:entityId Authorization: Bearer ``` Returns all live (non-deleted) attachments for the entity, ordered by `created_at ASC`. ```json { "attachments": [ { "id": "uuid", "filename": "brief.pdf", "mime_type": "application/pdf", "file_size_bytes": 204800, "uploader_id": "uuid", "created_at": "2026-05-31T12:00:00Z" } ] } ``` --- ### Delete Attachment ``` DELETE /api/attachments/:id Authorization: Bearer ``` **Authorization:** Caller must be the uploader (`uploader_id = caller.id`) OR have role `ADMIN` or `OWNER` within the tenant. **Flow:** 1. Load attachment; verify tenant ownership and `deleted_at IS NULL`. 2. Check authorization (uploader or elevated role). 3. Set `deleted_at = now()` in the database. 4. Enqueue a `r2_delete` job to Cloudflare Queue with payload `{ r2Key, tenantId }`. 5. Delete KV cache entry `url:{id}` if present. 6. Return `204 No Content`. The R2 object is **not deleted synchronously**. The queue consumer (`src/workers/queues/r2-delete.ts`) calls `R2Bucket.delete(r2Key)` and handles transient R2 failures with up to 3 retries before dead-lettering. Soft deletion means the row is retained for audit purposes (the file is gone from R2, but the metadata record — filename, size, uploader, timestamp — persists and is visible in audit logs). --- ## Components ### `` Fetches and renders all attachments for an entity. Optionally renders the upload dropzone. ```tsx interface AttachmentListProps { entityType: 'task_message' | 'expense' | 'ticket_message' | 'kb_article'; entityId: string; // UUID of the parent entity canUpload: boolean; // render when true className?: string; } ``` **Renders:** - `` when `canUpload` is true (collapses to an "Add attachment" button when no drag is in progress and no upload is pending) - A row of `` items for each live attachment - Nothing (no empty state message) when there are no attachments and `canUpload` is false **`` internal sub-component:** Displays: file icon (by MIME family) + filename + human-readable size (e.g. "204 KB") + delete icon (shown when the viewer is the uploader or has ADMIN/OWNER role). Clicking a chip: - **PDF** → opens `` modal with the signed URL - **Image** → opens `` (basic `` in a modal overlay, no external lib) - **Other** → calls `GET /api/attachments/:id/url` then triggers `window.open(url, '_blank')` Deleting a chip: calls `DELETE /api/attachments/:id`, removes the chip from local state optimistically, shows a toast on error and re-inserts the chip. **RTL:** The chip layout uses `flex` with `gap-2`. Icon is on the inline-start (`me-2`). Delete button is on the inline-end (`ms-auto`). The component must render correctly in both LTR and RTL contexts. --- ### `` ```tsx interface AttachmentDropzoneProps { entityType: 'task_message' | 'expense' | 'ticket_message' | 'kb_article'; entityId: string; onUploaded: (attachment: AttachmentSummary) => void; onError?: (message: string) => void; className?: string; } ``` **Behaviour:** - Accepts drag-and-drop onto the zone and click-to-browse (hidden ``). - Multiple files may be selected; each is uploaded as a separate request. - Shows per-file upload progress (XHR with `onprogress`; Workers stream the body so partial progress is real). - Enforces client-side size and MIME pre-validation against the per-entity matrix before initiating the request (fail fast, no round trip). - On success, calls `onUploaded` for each file, passing the response body. - On server-side rejection (400/413), calls `onError` with a localised Hebrew message derived from the error `code`. - Drag-active state: border changes to `var(--accent)`, background shifts to `var(--surface)`. **Visual structure:** ``` ┌─────────────────────────────────────────────┐ │ [icon] גרור קבצים לכאן או לחץ להוספה │ ← idle state └─────────────────────────────────────────────┘ ┌─────────────────────────────────────────────┐ │ [icon] שחרר להעלאה │ ← drag-active state └─────────────────────────────────────────────┘ ``` Border: `1.5px dashed var(--ink-soft)`. Radius: `var(--radius)`. Padding: `24px`. Min-height: `80px`. On drag-active: border-color switches to `var(--accent)`. File-in-progress sub-row: filename + animated progress bar (`width: {pct}%`, background `var(--accent)`). Removed from the list when the upload resolves. --- ### `` ```tsx interface PdfViewerProps { attachmentId: string; // used to fetch signed URL on open filename: string; // shown in modal header onClose: () => void; } ``` **Behaviour:** 1. On mount, `GET /api/attachments/:attachmentId/url` to obtain the signed URL. 2. Render the PDF using `react-pdf` (`` + ``) inside a modal overlay. 3. Page navigation: ← / → buttons + current page indicator (`עמוד 3 מתוך 12`). 4. Download button in the modal header: same signed URL, ``. 5. Close button (×) in the header and click-outside on the backdrop. Modal sizing: `max-width: 860px`, `max-height: 90vh`, scrollable page container. On mobile (`< 640px`): full-screen. The signed URL is fetched once on modal open; the KV cache (55-min TTL) ensures the URL remains valid for the entire viewing session without redundant signing calls. --- ## KV Namespace Namespace binding name: `ATTACHMENT_URL_CACHE` | Key pattern | Value | TTL | |---|---|---| | `url:{attachmentId}` | `{ url: string, expires_at: string }` | 3300 s | The namespace is bound in `wrangler.toml`: ```toml [[kv_namespaces]] binding = "ATTACHMENT_URL_CACHE" id = "" [[kv_namespaces]] binding = "ATTACHMENT_URL_CACHE" id = "" preview_id = "" ``` --- ## Async R2 Deletion via Queue Queue binding name: `R2_DELETE_QUEUE` **Producer** (delete API handler): ```ts await env.R2_DELETE_QUEUE.send({ r2Key: attachment.r2Key, tenantId: attachment.tenantId, }); ``` **Consumer** (`src/workers/queues/r2-delete.ts`): ```ts export default { async queue(batch: MessageBatch<{ r2Key: string; tenantId: string }>, env: Env) { for (const msg of batch.messages) { try { await env.ATTACHMENTS_BUCKET.delete(msg.body.r2Key); msg.ack(); } catch (err) { msg.retry({ delaySeconds: 30 }); } } }, }; ``` Retry policy: max 3 retries with 30-second delay. After 3 failures the message is dead-lettered to `R2_DELETE_DLQ` for manual review. `wrangler.toml` queue bindings: ```toml [[queues.producers]] binding = "R2_DELETE_QUEUE" queue = "r2-delete-queue" [[queues.consumers]] queue = "r2-delete-queue" max_batch_size = 50 max_retries = 3 dead_letter_queue = "r2-delete-dlq" ``` --- ## Migration Path ### Superseded tables and columns | Old artifact | Superseded by | |---|---| | `task_message_attachments` table | `attachments` with `entity_type = 'task_message'` | | `ticket_message_attachments` table | `attachments` with `entity_type = 'ticket_message'` | | `expenses.r2_key`, `expenses.file_name`, `expenses.file_type`, `expenses.file_size_bytes` | `attachments` with `entity_type = 'expense'` | ### Migration script (`migrations/041_unified_attachments.sql`) ```sql -- 1. Create the new table (DDL above) -- 2. Migrate task_message_attachments INSERT INTO attachments ( id, tenant_id, entity_type, entity_id, uploader_id, filename, r2_key, mime_type, file_size_bytes, created_at ) SELECT id, (SELECT tenant_id FROM task_messages tm WHERE tm.id = message_id), 'task_message', message_id, (SELECT author_id FROM task_messages tm WHERE tm.id = message_id), filename, r2_key, COALESCE(mime_type, 'application/octet-stream'), file_size_bytes, created_at FROM task_message_attachments; -- 3. Migrate ticket_message_attachments INSERT INTO attachments ( id, tenant_id, entity_type, entity_id, uploader_id, filename, r2_key, mime_type, file_size_bytes, created_at ) SELECT id, (SELECT tenant_id FROM ticket_messages tm WHERE tm.id = message_id), 'ticket_message', message_id, (SELECT author_id FROM ticket_messages tm WHERE tm.id = message_id), filename, r2_key, COALESCE(mime_type, 'application/octet-stream'), file_size_bytes, created_at FROM ticket_message_attachments; -- 4. Migrate expense receipt columns INSERT INTO attachments ( id, tenant_id, entity_type, entity_id, uploader_id, filename, r2_key, mime_type, file_size_bytes, created_at ) SELECT gen_random_uuid(), tenant_id, 'expense', id, -- expense id is the entity_id created_by, file_name, r2_key, COALESCE(file_type, 'application/octet-stream'), file_size_bytes, created_at FROM expenses WHERE r2_key IS NOT NULL; -- 5. Drop superseded columns from expenses -- (run after application code is deployed and verified) -- ALTER TABLE expenses DROP COLUMN r2_key, DROP COLUMN file_name, -- DROP COLUMN file_type, DROP COLUMN file_size_bytes; -- 6. Drop superseded tables (after deploy + verify) -- DROP TABLE task_message_attachments; -- DROP TABLE ticket_message_attachments; ``` The destructive `DROP` and `ALTER` statements are commented out. They are executed as a second migration (`042_unified_attachments_cleanup.sql`) only after the new code is deployed, verified, and the product team confirms the rollback window has passed. ### R2 key normalisation Existing R2 keys do not match the new `{tenantId}/{entityType}/{entityId}/{uuid}-{filename}` convention. The migration script inserts them as-is. A background worker (`src/workers/scripts/normalise-r2-keys.ts`) copies each object to the new key, updates the `attachments.r2_key` column, then deletes the old key. This runs once post-deployment and is not part of the hot path. --- ## Design Decisions | Decision | Rationale | |---|---| | Single `attachments` table | Eliminates schema drift between modules. One place for indexes, RLS, audit, and future features (e.g. virus scanning, tagging). | | Soft delete | Preserves the metadata record for audit logs and support investigations. The R2 object (the actual bytes) is deleted asynchronously; the database row is never removed. | | Signed URLs, not public R2 | Files may contain sensitive data (receipts, support evidence). Public R2 URLs would bypass tenant isolation. Signed URLs expire and are scoped to the specific key. | | KV URL cache (55-min TTL) | `createSignedUrl` is a CPU operation inside the Worker; caching it avoids redundant work on every render of ``. The 55-min TTL gives 5 minutes of buffer before the 1-hour signed URL expires. | | Async R2 deletion via queue | R2 deletes can have transient failures. Queuing the deletion decouples the user-facing API response from the R2 operation and provides automatic retry semantics without blocking the HTTP handler. | | MIME detected server-side | Client-supplied `Content-Type` is untrusted. `file-type` detection against the binary magic bytes prevents MIME confusion attacks (e.g. uploading a JS file with `Content-Type: image/png`). | | Stream to R2, no Worker buffering | A 25 MB file buffered in a Worker would exhaust the 128 MB Workers memory limit quickly under concurrent uploads. Streaming the multipart body directly to R2 avoids the intermediate copy. | | `entity_type` as TEXT + CHECK | Avoids a separate enum type. New entity types can be added by adding a value to the CHECK constraint in a non-blocking DDL migration. | | Separate `` component | Allows `` to be used in read-only contexts (e.g. public KB article view) without bundling upload logic. | --- ## Open Questions None. All design points are resolved by the requirements above. --- ## Accessibility & RTL - All interactive elements (chips, dropzone, modal buttons) have visible focus rings (`outline: 2px solid var(--accent); outline-offset: 2px`). - Delete button has `aria-label="מחק קובץ {filename}"`. - Progress bars have `role="progressbar"` with `aria-valuenow`, `aria-valuemin`, `aria-valuemax`. - `` modal has `role="dialog"` with `aria-labelledby` pointing to the modal header. - Component uses `dir="auto"` on filename text to handle mixed Hebrew/English filenames. - Spacing uses logical properties: `ms-*`, `me-*`, `ps-*`, `pe-*` throughout.