# Unified Attachments — Implementation Plan **Spec:** docs/specs/2026-05-31-unified-attachments.md · **Slug:** unified-attachments · **Wave:** 7 **Depends on:** crm-support-center, expenses-module, foundation-auth-rbac, foundation-design-system, kb-module, tasks-detail-communication ## Goal Replace the three (soon four) duplicated per-module attachment implementations with a single `attachments` table, one streaming upload endpoint with per-entity validation, one KV-cached signed-URL endpoint, async R2 deletion via a Cloudflare Queue, and a small set of reusable React components (``, ``, ``). All entity types (`task_message`, `expense`, `ticket_message`, `kb_article`, `vendor`) share the same metadata schema, R2 key convention, audit trail, and soft-delete semantics. A migration moves existing rows and normalises R2 keys to the new convention. ## Architecture - **New table `attachments`** in `@zync/db` covers every entity type. It supersedes the upstream `task_message_attachments` and `ticket_message_attachments` tables (from `tasks-detail-communication` and `crm-support-center`) and the inline receipt columns on `expenses` (from `expenses-module`). - **Upload flow** authenticates via `authMiddleware`, resolves `tenant_id` from the session, verifies entity ownership by querying the owning upstream table through `tenantQuery(db, tenantId)`, streams the multipart body straight to R2 (binding `STORAGE`), detects MIME from the first 4 KB via `file-type`, validates against a per-entity matrix, then inserts a row. - **Signed-URL flow** caches `createSignedUrl` output in Cloudflare KV (binding `ATTACHMENT_URL_CACHE`) under `url:{id}` with a 3300 s TTL so repeated `` renders avoid redundant signing. - **Delete flow** soft-deletes the row (`deleted_at = now()`), purges the KV cache entry, and enqueues an `r2_delete` job onto `R2_DELETE_QUEUE`; a queue consumer performs the physical R2 delete with retry/DLQ semantics. - **Entity ownership resolution** reads upstream tables by `entity_type`: `task_messages(id, tenant_id, author_id)`, `ticket_messages(id, tenant_id, author_id)`, `expenses(id, tenant_id, created_by)`, `kb_articles(id, tenant_id)`, and `vendor` (entity owned by the withholding/vendor module, spec 182 — resolved by tenant-scoped existence check on the supplied `entity_id`). - **Components** live in `@zync/ui` and are consumed by tasks, expenses, support tickets, and KB article views. The exported `Attachment` type (already reserved in the locked interface sheet) is the canonical row type; `AttachmentSummary` is the API response shape. - **Authorization for delete**: caller is the uploader OR holds `RoleId` `ADMIN`/`OWNER` (from `foundation-auth-rbac`). ## Tech Stack - **Package `@zync/db`**: Drizzle schema (`attachments`), query helpers, migrations `041`/`042`. - **App `apps/zync-api`** (Hono on Cloudflare Workers): routes under `/api/attachments`, queue consumer, R2-key normalisation script. - **Package `@zync/ui`** (React, Vite): ``, ``, ``, ``, `` using `react-pdf`. - **Libraries**: `file-type` (server MIME sniffing), `react-pdf` (PDF rendering), `zod` (request validation), Drizzle ORM. - **Cloudflare bindings**: `STORAGE` (R2 bucket), `ATTACHMENT_URL_CACHE` (KV), `R2_DELETE_QUEUE` (Queue producer), `r2-delete-queue` consumer with `r2-delete-dlq` dead-letter. - Cross-cutting: CSP-safe (no inline handlers), timing-safe nothing required here but tenant isolation enforced on every query; a11y focus rings + `role="dialog"`/`role="progressbar"`; RTL via logical properties (`ms-*`/`me-*`/`ps-*`/`pe-*`); `prefers-reduced-motion` honoured on progress/drag animations; Hebrew UI strings. ## Wave Plan | Sub-wave | Tasks | Files touched | Parallelizable? | |----------|-------|---------------|-----------------| | A — Data & infra | 1, 2 | `packages/db/src/schema/attachments.ts`, migrations, `wrangler.toml` | Task 1 then 2 | | B — Server core | 3, 4 | `packages/db` query helpers, `apps/zync-api/src/lib/attachments/*` | Task 3 ∥ Task 4 after A | | C — API routes | 5, 6, 7, 8 | `apps/zync-api/src/routes/attachments.ts` | 5 first; 6,7,8 ∥ after 5 | | D — Async + migration ops | 9, 10 | `apps/zync-api/src/workers/queues/r2-delete.ts`, `apps/zync-api/src/workers/scripts/normalise-r2-keys.ts` | ∥ after A | | E — UI components | 11, 12, 13, 14 | `packages/ui/src/components/attachments/*` | 11 first; 12,13 ∥; 14 after 11 | | F — Integration | 15 | tasks/expenses/tickets/KB consumers | after C+E | ## Tasks ### Task 1: `attachments` table — Drizzle schema & DDL **Blocks:** 2, 3, 4 · **Blocked by:** — **Files:** - Create: `packages/db/src/schema/attachments.ts` - Modify: `packages/db/src/schema/index.ts` (export the new table) **Steps:** - [ ] Define the `attachments` pgTable with all columns, the unique `r2_key`, and the `file_size_bytes > 0` check. - [ ] Add the two partial indexes (`idx_attachments_entity`, `idx_attachments_uploader`) scoped to `deleted_at IS NULL`. - [ ] Export `Attachment` (`$inferSelect`) and `NewAttachment` (`$inferInsert`) types; re-export from the package barrel. - [ ] Enforce the `entity_type` CHECK constraint in the SQL migration (Drizzle column stays `text`; union enforced in app layer per spec). **Schema / Interfaces:** ```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, 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 ); CREATE INDEX idx_attachments_entity ON attachments (tenant_id, entity_type, entity_id) WHERE deleted_at IS NULL; CREATE INDEX idx_attachments_uploader ON attachments (tenant_id, uploader_id) WHERE deleted_at IS NULL; ``` ```ts // packages/db/src/schema/attachments.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(), 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; ``` **Acceptance:** - [ ] `drizzle-kit` generates a migration that creates `attachments` with UUID PK default `gen_random_uuid()`, both FKs UUID→UUID, the CHECK constraint verbatim, the unique on `r2_key`, and both partial indexes. - [ ] `Attachment` and `NewAttachment` are importable from `@zync/db`. ### Task 2: Cloudflare bindings — KV, Queue producer/consumer, DLQ **Blocks:** 6, 7, 9 · **Blocked by:** — **Files:** - Modify: `apps/zync-api/wrangler.toml` - Modify: `apps/zync-api/src/env.ts` (the `Env` type — already exported upstream) **Steps:** - [ ] Add the `ATTACHMENT_URL_CACHE` KV namespace binding (prod + preview). - [ ] Add the `R2_DELETE_QUEUE` producer binding (queue `r2-delete-queue`). - [ ] Add the queue consumer config for `r2-delete-queue` (`max_batch_size=50`, `max_retries=3`, `dead_letter_queue=r2-delete-dlq`). - [ ] Confirm the existing R2 bucket binding `STORAGE` is available to the attachments routes and queue consumer (the spec's `ATTACHMENTS_BUCKET` name maps to the canonical `STORAGE` binding). - [ ] Extend the `Env` interface with `ATTACHMENT_URL_CACHE: KVNamespace` and `R2_DELETE_QUEUE: Queue`. **Schema / Interfaces:** ```toml [[kv_namespaces]] binding = "ATTACHMENT_URL_CACHE" id = "" [[kv_namespaces]] binding = "ATTACHMENT_URL_CACHE" id = "" preview_id = "" [[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" ``` ```ts export interface R2DeleteMessage { r2Key: string; tenantId: string; } // Env additions: // ATTACHMENT_URL_CACHE: KVNamespace; // R2_DELETE_QUEUE: Queue; // STORAGE: R2Bucket; // existing R2 binding reused for attachment objects ``` **Acceptance:** - [ ] `wrangler types` regenerates and `Env` exposes `ATTACHMENT_URL_CACHE`, `R2_DELETE_QUEUE`, and `STORAGE`. - [ ] `wrangler dev` boots with the new bindings without config errors. ### Task 3: R2 key convention & filename sanitisation helper **Blocks:** 5 · **Blocked by:** 1 **Files:** - Create: `apps/zync-api/src/lib/attachments/r2-key.ts` **Steps:** - [ ] Implement `sanitizeFilenameComponent(original: string): string` exactly per spec: strip path separators, null bytes, bidi-override chars; cap at 200 chars. - [ ] Implement `buildR2Key({ tenantId, entityType, entityId, filename })` returning `${tenantId}/${entityType}/${entityId}/${crypto.randomUUID()}-${sanitizeFilenameComponent(filename)}`. - [ ] Implement a stricter display-safe sanitiser for the stored `filename` column (lowercase, spaces → `_`, strip non-`[a-z0-9._-]`, max 100 chars) used when persisting `attachments.filename`. **Schema / Interfaces:** ```ts export function sanitizeFilenameComponent(original: string): string { return original .replace(/[/\\]/g, '_') .replace(/\0/g, '') .replace(/[‪-‮⁦-⁩]/g, '') .slice(0, 200); } export function safeStoredFilename(original: string): string { return original .toLowerCase() .replace(/\s+/g, '_') .replace(/[^a-z0-9._-]/g, '') .slice(0, 100); } export function buildR2Key(args: { tenantId: string; entityType: AttachmentEntityType; entityId: string; filename: string; }): string { return `${args.tenantId}/${args.entityType}/${args.entityId}/${crypto.randomUUID()}-${sanitizeFilenameComponent(args.filename)}`; } ``` **Acceptance:** - [ ] Bidi-override and null bytes are stripped; path separators become `_`. - [ ] Generated keys match `^{uuid}/{entity_type}/{uuid}/{uuid}-{name}$`. ### Task 4: Per-entity validation matrix & entity-ownership resolver **Blocks:** 5 · **Blocked by:** 1 **Files:** - Create: `apps/zync-api/src/lib/attachments/validation.ts` - Create: `apps/zync-api/src/lib/attachments/entity-access.ts` **Steps:** - [ ] Define `ATTACHMENT_ENTITY_TYPES` and the `AttachmentEntityType` union, plus `ATTACHMENT_RULES` mapping each entity type to `{ maxBytes, allowedMime }` per the matrix. - [ ] Implement `validateUpload({ entityType, detectedMime, declaredFilename, sizeBytes })` returning a discriminated result with error codes `FILE_TOO_LARGE`, `MIME_NOT_ALLOWED`, `MIME_EXTENSION_MISMATCH`. - [ ] Implement MIME-vs-extension consistency check (extension derived from `declaredFilename` must be compatible with the `file-type`-detected MIME). - [ ] Implement `resolveEntityOwner(db, tenantId, entityType, entityId)` that, per `entity_type`, queries the owning table via `tenantQuery(db, tenantId)` and returns `{ exists: boolean }`; map to `ENTITY_NOT_FOUND` (404) when missing and `FORBIDDEN` (403) when tenant mismatch. Tables queried: `task_messages`, `ticket_messages`, `expenses`, `kb_articles`, and the vendor table (`entity_type='vendor'`). **Schema / Interfaces:** ```ts export const ATTACHMENT_ENTITY_TYPES = ['task_message','expense','ticket_message','kb_article','vendor'] as const; export type AttachmentEntityType = typeof ATTACHMENT_ENTITY_TYPES[number]; const DOC_OFFICE = [ 'application/vnd.openxmlformats-officedocument.wordprocessingml.document', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', 'application/vnd.openxmlformats-officedocument.presentationml.presentation', 'application/msword','application/vnd.ms-excel', ]; const BROAD = ['application/pdf','text/plain','text/csv',...DOC_OFFICE,'application/zip','video/mp4','video/quicktime']; // plus image/* wildcard export const ATTACHMENT_RULES: Record boolean }> = { task_message: { maxBytes: 25*1024*1024, allowedMime: m => m.startsWith('image/') || BROAD.includes(m) }, ticket_message: { maxBytes: 25*1024*1024, allowedMime: m => m.startsWith('image/') || BROAD.includes(m) }, expense: { maxBytes: 10*1024*1024, allowedMime: m => ['image/jpeg','image/png','image/heic','image/heif','application/pdf'].includes(m) }, kb_article: { maxBytes: 25*1024*1024, allowedMime: m => ['image/jpeg','image/png','image/gif','image/webp','image/svg+xml','application/pdf'].includes(m) }, vendor: { maxBytes: 10*1024*1024, allowedMime: m => ['application/pdf','image/jpeg','image/png'].includes(m) }, }; export type UploadValidationError = | 'FILE_TOO_LARGE' | 'MIME_NOT_ALLOWED' | 'MIME_EXTENSION_MISMATCH'; ``` **Acceptance:** - [ ] Each entity type enforces its exact size cap and allowlist from the matrix. - [ ] A JPEG byte stream uploaded as `evil.png` is rejected with `MIME_EXTENSION_MISMATCH`; a JS payload labelled `image/png` is rejected with `MIME_NOT_ALLOWED`. - [ ] `resolveEntityOwner` returns 404 for a non-existent `entity_id` and 403 for a cross-tenant `entity_id`. ### Task 5: Upload route `POST /api/attachments` **Blocks:** 15 · **Blocked by:** 2, 3, 4 **Files:** - Create: `apps/zync-api/src/routes/attachments.ts` - Modify: `apps/zync-api/src/index.ts` (mount the route group under `authMiddleware`) - Create: `packages/db/src/queries/attachments.ts` (`insertAttachment`) **Steps:** - [ ] Reject early with `413` when `Content-Length` exceeds the 25 MB hard cap (before parsing). - [ ] Validate presence of `file`, `entity_type`, `entity_id` (`MISSING_FIELD`); validate `entity_type` against the union (`INVALID_ENTITY_TYPE`); validate `entity_id` is a UUID with zod. - [ ] Authenticate via `authMiddleware`; resolve `tenant_id` from the session payload; call `resolveEntityOwner` (→ `FORBIDDEN`/`ENTITY_NOT_FOUND`). - [ ] Buffer only the first 4 KB to run `file-type` detection; then stream the multipart file body directly to `STORAGE.put(r2Key, stream, { httpMetadata: { contentType: detectedMime } })` without buffering the whole file in Worker memory. - [ ] Run `validateUpload` against detected MIME + size (`FILE_TOO_LARGE`, `MIME_NOT_ALLOWED`, `MIME_EXTENSION_MISMATCH`). - [ ] Build `r2_key` via `buildR2Key`; persist with `insertAttachment` (store `safeStoredFilename` in `filename`). - [ ] Return `201` with `{ id, filename, mime_type, file_size_bytes, created_at }`. **Schema / Interfaces:** ```ts // packages/db/src/queries/attachments.ts export async function insertAttachment(db: Db, row: NewAttachment): Promise; // Response 201 interface AttachmentSummary { id: string; filename: string; mime_type: string; file_size_bytes: number; uploader_id?: string; created_at: string; } ``` Error codes: `MISSING_FIELD` (400), `INVALID_ENTITY_TYPE` (400), `FILE_TOO_LARGE` (400), `MIME_NOT_ALLOWED` (400), `MIME_EXTENSION_MISMATCH` (400), `FORBIDDEN` (403), `ENTITY_NOT_FOUND` (404), hard-cap (413). **Acceptance:** - [ ] A valid 200 KB PDF for an owned `task_message` returns 201 and creates one R2 object plus one `attachments` row whose `r2_key` matches the convention. - [ ] An 11 MB receipt for `expense` returns 400 `FILE_TOO_LARGE`. - [ ] A request with no `entity_id` returns 400 `MISSING_FIELD`; a 30 MB body returns 413 before parsing. - [ ] The file is never fully buffered in memory (streamed to R2). ### Task 6: Signed-URL route `GET /api/attachments/:id/url` (KV-cached) **Blocks:** 13 · **Blocked by:** 2, 5 **Files:** - Modify: `apps/zync-api/src/routes/attachments.ts` - Modify: `packages/db/src/queries/attachments.ts` (`getAttachmentById`) **Steps:** - [ ] Load the row via `getAttachmentById`; verify `tenant_id` matches the caller and `deleted_at IS NULL` (else 404). - [ ] Read KV `ATTACHMENT_URL_CACHE` key `url:{id}`; on hit, return the cached `{ url, expires_at }`. - [ ] On miss, call `STORAGE.createSignedUrl(r2Key, { expiresIn: 3600 })`, compute `expires_at = now + 3600s`, store in KV with TTL `3300` seconds, and return. **Schema / Interfaces:** ```ts export async function getAttachmentById(db: Db, tenantId: string, id: string): Promise; // KV value: { url: string, expires_at: string } // ISO8601 // Response 200: { url: string, expires_at: string } ``` **Acceptance:** - [ ] First call signs and writes KV (`url:{id}`, TTL 3300); second call within TTL returns the identical cached URL without re-signing. - [ ] A deleted or cross-tenant attachment id returns 404. ### Task 7: List route `GET /api/attachments` **Blocks:** 12 · **Blocked by:** 5 **Files:** - Modify: `apps/zync-api/src/routes/attachments.ts` - Modify: `packages/db/src/queries/attachments.ts` (`listAttachmentsForEntity`) **Steps:** - [ ] Validate `entity_type` (union) and `entity_id` (UUID) query params with zod. - [ ] Verify caller can read the entity via `resolveEntityOwner` (403/404 as appropriate). - [ ] Return live attachments (`deleted_at IS NULL`) for `(tenant_id, entity_type, entity_id)` ordered by `created_at ASC`. **Schema / Interfaces:** ```ts export async function listAttachmentsForEntity( db: Db, tenantId: string, entityType: AttachmentEntityType, entityId: string ): Promise; // Response 200: { attachments: AttachmentSummary[] } // each includes uploader_id ``` **Acceptance:** - [ ] Returns only non-deleted rows for the entity, ascending by `created_at`, scoped to the caller's tenant. ### Task 8: Delete route `DELETE /api/attachments/:id` (soft delete + enqueue) **Blocks:** 9, 13 · **Blocked by:** 2, 5 **Files:** - Modify: `apps/zync-api/src/routes/attachments.ts` - Modify: `packages/db/src/queries/attachments.ts` (`softDeleteAttachment`) **Steps:** - [ ] Load the row; verify tenant ownership and `deleted_at IS NULL` (else 404). - [ ] Authorize: `uploader_id === caller.id` OR caller `RoleId` is `ADMIN`/`OWNER` (else 403). - [ ] Set `deleted_at = now()` via `softDeleteAttachment`. - [ ] `R2_DELETE_QUEUE.send({ r2Key, tenantId })`. - [ ] Delete KV entry `url:{id}` if present. - [ ] Return `204 No Content`. **Schema / Interfaces:** ```ts export async function softDeleteAttachment(db: Db, tenantId: string, id: string): Promise; ``` **Acceptance:** - [ ] Uploader and ADMIN/OWNER can delete; an unrelated member gets 403. - [ ] After delete, the row has `deleted_at` set, the KV entry is gone, and exactly one `R2DeleteMessage` is enqueued. The R2 object is NOT removed synchronously. ### Task 9: R2 delete queue consumer **Blocks:** — · **Blocked by:** 2, 8 **Files:** - Create: `apps/zync-api/src/workers/queues/r2-delete.ts` - Modify: `apps/zync-api/src/index.ts` (wire the `queue` handler) **Steps:** - [ ] Implement the `queue(batch, env)` handler iterating `batch.messages`; call `env.STORAGE.delete(msg.body.r2Key)`, then `msg.ack()`. - [ ] On failure call `msg.retry({ delaySeconds: 30 })`; after 3 retries Cloudflare dead-letters to `r2-delete-dlq` per the wrangler config. **Schema / Interfaces:** ```ts export async function handleR2DeleteBatch(batch: MessageBatch, env: Env): Promise { for (const msg of batch.messages) { try { await env.STORAGE.delete(msg.body.r2Key); msg.ack(); } catch { msg.retry({ delaySeconds: 30 }); } } } ``` **Acceptance:** - [ ] A successful delete acks; a transient R2 error retries with 30 s delay; after 3 failures the message lands in `r2-delete-dlq`. ### Task 10: Migration `041` + cleanup `042` + R2 key normalisation script **Blocks:** — · **Blocked by:** 1 **Files:** - Create: `packages/db/migrations/041_unified_attachments.sql` - Create: `packages/db/migrations/042_unified_attachments_cleanup.sql` - Create: `apps/zync-api/src/workers/scripts/normalise-r2-keys.ts` **Steps:** - [ ] In `041`: create `attachments` (Task 1 DDL), then backfill from `task_message_attachments`, `ticket_message_attachments`, and the `expenses` receipt columns. Resolve `tenant_id`/`uploader_id` via subqueries on `task_messages`/`ticket_messages` (`author_id`) and `expenses` (`created_by`). `COALESCE` legacy mime to `application/octet-stream`. Keep `DROP`/`ALTER` commented out in `041`. - [ ] In `042` (run only after deploy + verify + rollback window): drop the superseded `expenses` columns and the two legacy tables. - [ ] Implement `normalise-r2-keys.ts`: for each migrated row whose `r2_key` does not match the new convention, copy the R2 object to the new key (`buildR2Key`-style but reusing the existing stored UUID prefix is not required — generate per convention), update `attachments.r2_key`, then delete the old key. One-shot, off the hot path. **Schema / Interfaces:** ```sql -- 041_unified_attachments.sql (after CREATE TABLE 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; 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; 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, 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; ``` ```sql -- 042_unified_attachments_cleanup.sql ALTER TABLE expenses DROP COLUMN r2_key, DROP COLUMN file_name, DROP COLUMN file_type, DROP COLUMN file_size_bytes; DROP TABLE task_message_attachments; DROP TABLE ticket_message_attachments; ``` **Note:** The legacy `task_message_attachments`/`ticket_message_attachments` size column upstream is `size_bytes`; the spec's backfill `SELECT ... file_size_bytes` must reference the actual upstream column name. Use `size_bytes` for those two SELECTs and `file_size_bytes` for `expenses`. **Acceptance:** - [ ] `041` runs idempotently: every legacy row appears once in `attachments` with correct `entity_type`, `tenant_id`, `uploader_id`. - [ ] `042` is a separate migration; running it drops the legacy columns/tables only. - [ ] After `normalise-r2-keys.ts`, every `attachments.r2_key` matches `{tenantId}/{entityType}/{entityId}/{uuid}-{filename}` and old R2 objects are removed. ### Task 11: ``, `` and chip interactions **Blocks:** 12, 14 · **Blocked by:** 6, 8 **Files:** - Create: `packages/ui/src/components/attachments/AttachmentChip.tsx` - Create: `packages/ui/src/components/attachments/ImageLightbox.tsx` - Create: `packages/ui/src/components/attachments/icons.ts` (MIME-family icon map) - Modify: `packages/ui/src/index.ts` (exports) **Steps:** - [ ] Render file icon (by MIME family) + filename (`dir="auto"`) + human-readable size + delete button. - [ ] Show the delete button only when the viewer is the uploader OR has `ADMIN`/`OWNER` role; `aria-label="מחק קובץ {filename}"`. - [ ] Click behaviour: PDF → open ``; image → open `` (plain `` in modal overlay, no external lib); other → `GET /api/attachments/:id/url` then `window.open(url, '_blank')`. - [ ] Delete: call `DELETE /api/attachments/:id`, optimistically remove from local state; on error re-insert and `toast(...)`. - [ ] Layout: `flex gap-2`, icon `me-2`, delete button `ms-auto`; visible focus ring `outline: 2px solid var(--accent); outline-offset: 2px`; correct in LTR and RTL. **Schema / Interfaces:** ```tsx interface AttachmentChipProps { attachment: AttachmentSummary; canDelete: boolean; onDeleted: (id: string) => void; } ``` **Acceptance:** - [ ] Delete button visibility honours uploader/role; `aria-label` present. - [ ] PDF/image/other click paths route to viewer/lightbox/`window.open` respectively. - [ ] Renders correctly under `dir="rtl"` (icon inline-start, delete inline-end). ### Task 12: `` **Blocks:** 15 · **Blocked by:** 7, 11 **Files:** - Create: `packages/ui/src/components/attachments/AttachmentList.tsx` - Modify: `packages/ui/src/index.ts` **Steps:** - [ ] Fetch `GET /api/attachments?entity_type=&entity_id=` on mount; render a row of `` for each live attachment. - [ ] Render `` when `canUpload` is true (collapsed to an "Add attachment" button when idle and no upload pending). - [ ] Render nothing (no empty-state text) when there are no attachments and `canUpload` is false. - [ ] On dropzone `onUploaded`, append the new chip to local state. **Schema / Interfaces:** ```tsx interface AttachmentListProps { entityType: 'task_message' | 'expense' | 'ticket_message' | 'kb_article'; entityId: string; canUpload: boolean; className?: string; } ``` **Acceptance:** - [ ] Lists attachments ascending by `created_at`; dropzone appears only when `canUpload`. - [ ] Read-only context with zero attachments renders empty (no message). ### Task 13: `` **Blocks:** — · **Blocked by:** 6, 8 **Files:** - Create: `packages/ui/src/components/attachments/PdfViewer.tsx` - Modify: `packages/ui/src/index.ts` - Modify: `packages/ui/package.json` (add `react-pdf`) **Steps:** - [ ] On mount, `GET /api/attachments/:attachmentId/url`; render ``+`` from `react-pdf` inside a modal overlay. - [ ] Page nav ← / → with indicator `עמוד {n} מתוך {total}`; download button (`` to signed URL); close (×) and backdrop click. - [ ] Modal `role="dialog"` with `aria-labelledby` pointing at the header; focus rings on all buttons; honour `prefers-reduced-motion`. - [ ] Sizing: `max-width: 860px`, `max-height: 90vh`, scrollable page container; full-screen below `640px`. **Schema / Interfaces:** ```tsx interface PdfViewerProps { attachmentId: string; filename: string; onClose: () => void; } ``` **Acceptance:** - [ ] Signed URL fetched once on open (KV-cached for the session); paging and download work; `role="dialog"` + `aria-labelledby` present. ### Task 14: `` **Blocks:** 15 · **Blocked by:** 11 (uses chip/error toasts), 5 (upload endpoint) **Files:** - Create: `packages/ui/src/components/attachments/AttachmentDropzone.tsx` - Modify: `packages/ui/src/index.ts` **Steps:** - [ ] Support drag-and-drop and click-to-browse (hidden ``); upload each file as a separate request. - [ ] Client-side pre-validation of size + MIME against the per-entity matrix (fail fast, no round trip) before initiating upload. - [ ] Upload via `XHR` with `onprogress`; render a per-file progress sub-row (`role="progressbar"` with `aria-valuenow`/`aria-valuemin`/`aria-valuemax`, `width: {pct}%`, background `var(--accent)`); remove on resolve. - [ ] On success call `onUploaded(response)` per file; on 400/413 call `onError` with a localised Hebrew message derived from the error `code`. - [ ] Idle text `גרור קבצים לכאן או לחץ להוספה`; drag-active text `שחרר להעלאה`, border switches to `var(--accent)`, background to `var(--surface)`. - [ ] Styling: border `1.5px dashed var(--ink-soft)`, radius `var(--radius)`, padding `24px`, min-height `80px`; logical spacing props; honour `prefers-reduced-motion` for the progress animation. **Schema / Interfaces:** ```tsx interface AttachmentDropzoneProps { entityType: 'task_message' | 'expense' | 'ticket_message' | 'kb_article'; entityId: string; onUploaded: (attachment: AttachmentSummary) => void; onError?: (message: string) => void; className?: string; } ``` **Acceptance:** - [ ] Multiple files upload as separate requests with real per-file progress; oversize/wrong-MIME files are rejected client-side before any request. - [ ] Server 400/413 surfaces a Hebrew message via `onError`; progress bars expose correct ARIA values; drag-active styling applies. ### Task 15: Integrate `` into consuming modules **Blocks:** — · **Blocked by:** 5, 7, 12, 14 **Files:** - Modify: tasks message detail view (consumes `entityType="task_message"`) - Modify: expenses detail/upload view (`entityType="expense"`) - Modify: support ticket message view (`entityType="ticket_message"`) - Modify: KB article editor/view (`entityType="kb_article"`) **Steps:** - [ ] Replace the legacy inline attachment UI in each module with ``. - [ ] Wire `canUpload` to each module's existing permission check (e.g. message author / expense creator / staff role). - [ ] Remove now-dead per-module upload endpoints and components that the unified table supersedes. - [ ] For the public/read-only KB article view, render ``. **Acceptance:** - [ ] Tasks, expenses, tickets, and KB articles all render attachments through the unified component and upload through `POST /api/attachments`. - [ ] No module still references `task_message_attachments`/`ticket_message_attachments` tables or the dropped `expenses` receipt columns.