# Portal File Sharing — Implementation Plan **Spec:** docs/specs/2026-05-31-portal-file-sharing.md · **Slug:** portal-file-sharing · **Wave:** 11 **Depends on:** customer-portal-access-control, customers-module, foundation-auth-rbac, projects-module, tenant-portals ## Goal Deliver the staff-to-client file sharing flow for the customer portal: staff upload deliverables, contracts, and documents scoped to a customer (optionally a project), and clients download them — and optionally upload back — through the portal. This fills the Files tab that `customers-module` (spec 9) explicitly delegates here, and the `/portal/{tenantSlug}/files` page gated by the portal `show_files` visibility flag. Files live in R2 behind short-lived signed URLs; metadata lives in a new `portal_files` table. ## Architecture A single new table `portal_files` (tenant- and customer-scoped, optionally project-scoped) records file metadata; the binary lives in Cloudflare R2 under the `STORAGE` binding with key convention `{tenantId}/portal/{customerId}/{uuid}-{filename}`. Two distinct route namespaces consume it: - **Staff routes** (`apps/zync-api/src/server/routes/portal-files.ts`, hyphenated `/api/portal-files/*` + `/api/customers/:customerId/portal-files`) use `authMiddleware` + `requirePermission('customers:read'|'customers:write')` and the staff `tenantQuery(db, tenantId)` factory. - **Portal routes** (`apps/zync-api/src/server/portal/files.ts`, slash `/api/portal/files*`) use `portalAuthMiddleware` (from `tenant-portals`/`customer-portal-access-control`) and the `portalQuery(db, tenantId, customerId)` factory, taking `customerId` from the JWT only — never from URL or body. Upload is a two-step signed-PUT flow (mirrors `unified-attachments` spec 41): client requests a signed R2 PUT URL, PUTs directly to R2, then POSTs to create the metadata row. Download (`GET /api/portal-files/:id/download`) is **shared** by staff and portal callers with dual ownership auth and a 300s signed URL. Consumed upstream interfaces (do NOT redefine): tables `tenants`, `customers`, `projects`, `users`, `customer_portal_users`, `tenant_settings`; functions `authMiddleware`, `requirePermission`, `tenantQuery`, `portalAuthMiddleware`, `portalQuery`; binding `STORAGE`; settings `tenant_settings.portal_visibility.show_files` (spec 82, in depends_on) and `tenant_settings.portal_can_upload_files` (spec 136 `customer-portal-settings-ui`, same wave 11, NOT in depends_on — read defensively, default `false` if column/flag absent). The customer-detail Files tab shell is owned by `customers-module`; this plan fills it. ## Tech Stack - **apps/zync-api** (Hono on Cloudflare Workers): staff + portal API routes, R2 signed-URL generation via `STORAGE.createSignedUrl` / signed PUT. - **packages/db** (Drizzle ORM, Neon Postgres via Hyperdrive): `portal_files` table + schema + migration + data-access functions. - **apps/zync-app** (Vite + React): staff Files tab (customer detail + project detail), upload sheet, file list. - **apps/zync-app** portal surface (React): `/portal/{tenantSlug}/files` page. - Bindings: `STORAGE` (R2), `DB`/Hyperdrive (Neon). Shared UI from `@zync/ui` (`Sheet`, `DataTable`, `Button`, `Badge`, `Select`, `Switch`, `Radio`, `Input`, `Textarea`, `Progress`, `Spinner`, `EmptyState`, `toast`). ## Wave Plan | Sub-wave | Tasks | Files touched | Parallelizable? | |----------|-------|---------------|-----------------| | A | 1 | packages/db schema + migration | No (blocks all) | | B | 2, 3 | packages/db data-access, R2 helper in zync-api | After A; 2 and 3 parallel | | C | 4, 5 | zync-api staff routes, zync-api portal routes | After B; 4 and 5 parallel | | D | 6, 7 | zync-app staff Files tab, zync-app portal Files page | After C; 6 and 7 parallel | ## Tasks ### Task 1: `portal_files` table — Drizzle schema + migration **Blocks:** 2, 3, 4, 5 · **Blocked by:** — **Files:** - Create: `packages/db/src/schema/portal-files.ts` - Modify: `packages/db/src/schema/index.ts` (export the new table) - Create: `packages/db/migrations/_portal_files.sql` **Steps:** - [ ] Define the `portal_files` Drizzle table matching the canonical DDL below (UUID PK, UUID FKs, TIMESTAMPTZ, BOOLEAN, INTEGER size). - [ ] Add the partial-uniqueness-free `r2_key` UNIQUE constraint and the composite index `idx_portal_files_customer`. - [ ] Export `portalFiles` and its inferred select/insert types from the schema barrel. - [ ] Generate/author the migration SQL (Postgres dialect) and confirm `drizzle-kit` diff is clean. **Schema / Interfaces:** ```sql CREATE TABLE portal_files ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE, customer_id UUID NOT NULL REFERENCES customers(id) ON DELETE CASCADE, project_id UUID REFERENCES projects(id) ON DELETE SET NULL, uploaded_by UUID REFERENCES users(id), -- null for client uploads uploaded_by_portal_user UUID REFERENCES customer_portal_users(id), -- null for staff uploads -- exactly one of uploaded_by / uploaded_by_portal_user must be non-null (enforced app-layer) filename TEXT NOT NULL, r2_key TEXT NOT NULL UNIQUE, -- {tenantId}/portal/{customerId}/{uuid}-{filename} file_size_bytes INTEGER NOT NULL, mime_type TEXT NOT NULL, description TEXT, visible_to_portal BOOLEAN NOT NULL DEFAULT true, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), expires_at TIMESTAMPTZ -- null = no expiry; non-null = auto-hide after date ); CREATE INDEX idx_portal_files_customer ON portal_files(tenant_id, customer_id, created_at DESC); ``` ```ts // packages/db/src/schema/portal-files.ts (Drizzle) export const portalFiles = pgTable('portal_files', { id: uuid('id').primaryKey().defaultRandom(), tenantId: uuid('tenant_id').notNull().references(() => tenants.id, { onDelete: 'cascade' }), customerId: uuid('customer_id').notNull().references(() => customers.id, { onDelete: 'cascade' }), projectId: uuid('project_id').references(() => projects.id, { onDelete: 'set null' }), uploadedBy: uuid('uploaded_by').references(() => users.id), uploadedByPortalUser: uuid('uploaded_by_portal_user').references(() => customerPortalUsers.id), filename: text('filename').notNull(), r2Key: text('r2_key').notNull().unique(), fileSizeBytes: integer('file_size_bytes').notNull(), mimeType: text('mime_type').notNull(), description: text('description'), visibleToPortal: boolean('visible_to_portal').notNull().default(true), createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(), expiresAt: timestamp('expires_at', { withTimezone: true }), }, (t) => ({ customerIdx: index('idx_portal_files_customer').on(t.tenantId, t.customerId, t.createdAt.desc()), })); export type PortalFile = typeof portalFiles.$inferSelect; export type NewPortalFile = typeof portalFiles.$inferInsert; ``` **Acceptance:** - [ ] Migration applies cleanly to Neon; `\d portal_files` shows UUID PK/FKs, BOOLEAN `visible_to_portal`, INTEGER `file_size_bytes`, TIMESTAMPTZ columns, UNIQUE `r2_key`, and `idx_portal_files_customer`. - [ ] `portalFiles`, `PortalFile`, `NewPortalFile` are importable from `@zync/db`. ### Task 2: Data-access layer for `portal_files` **Blocks:** 4, 5 · **Blocked by:** 1 **Files:** - Create: `packages/db/src/queries/portal-files.ts` - Modify: `packages/db/src/queries/index.ts` (export) **Steps:** - [ ] Implement `listPortalFilesForStaff(db, tenantId, { customerId, projectId?, limit, offset })` — staff view, returns ALL files (internal + visible) for the customer, newest first, optional project filter; joins `users.name` (uploader) and resolves an `uploaded_by_client: boolean` flag (`uploaded_by_portal_user IS NOT NULL`). Scoped through `tenantQuery(db, tenantId)`. - [ ] Implement `listPortalFilesForPortal(db, tenantId, customerId, { limit, offset })` — portal view, WHERE `visible_to_portal = true AND (expires_at IS NULL OR expires_at > now())`, newest first. Scoped through `portalQuery(db, tenantId, customerId)`. - [ ] Implement `getPortalFileById(db, tenantId, id)` — single row scoped to tenant (used by download/patch/delete; ownership refined in route layer). - [ ] Implement `createPortalFile(db, input: NewPortalFile)` — INSERT; enforce the exactly-one-uploader invariant in code: throw `InvalidUploaderError` unless exactly one of `uploadedBy` / `uploadedByPortalUser` is non-null. - [ ] Implement `updatePortalFile(db, tenantId, id, { description?, visibleToPortal?, expiresAt? })` — only these three mutable fields; scoped to tenant. - [ ] Implement `deletePortalFile(db, tenantId, id)` — DELETE row scoped to tenant, returns the deleted row (caller needs `r2_key` to remove the R2 object). - [ ] Define and export `InvalidUploaderError extends Error`. **Schema / Interfaces:** ```ts export interface StaffPortalFileRow extends PortalFile { uploader_name: string | null; uploaded_by_client: boolean } export function listPortalFilesForStaff(db: Db, tenantId: string, opts: { customerId: string; projectId?: string; limit: number; offset: number }): Promise<{ items: StaffPortalFileRow[]; total: number }>; export function listPortalFilesForPortal(db: Db, tenantId: string, customerId: string, opts: { limit: number; offset: number }): Promise<{ items: PortalFile[]; total: number }>; export function getPortalFileById(db: Db, tenantId: string, id: string): Promise; export function createPortalFile(db: Db, input: NewPortalFile): Promise; // throws InvalidUploaderError export function updatePortalFile(db: Db, tenantId: string, id: string, patch: { description?: string | null; visibleToPortal?: boolean; expiresAt?: Date | null }): Promise; export function deletePortalFile(db: Db, tenantId: string, id: string): Promise; export class InvalidUploaderError extends Error {} ``` **Acceptance:** - [ ] `createPortalFile` throws `InvalidUploaderError` when both or neither uploader column is set; succeeds with exactly one. - [ ] `listPortalFilesForPortal` never returns rows with `visible_to_portal = false` or expired `expires_at`. - [ ] Staff list returns `uploaded_by_client = true` for rows created by a portal user. ### Task 3: R2 helper — key convention, signed PUT, signed download, sanitization **Blocks:** 4, 5 · **Blocked by:** 1 **Files:** - Create: `apps/zync-api/src/server/lib/portal-file-storage.ts` **Steps:** - [ ] Implement `sanitizeFilename(name: string): string` — strip path separators and control chars, collapse whitespace, keep extension; the UUID prefix guarantees key uniqueness so sanitization is for safety/readability only. - [ ] Implement `buildPortalFileKey(tenantId, customerId, filename): string` → `${tenantId}/portal/${customerId}/${crypto.randomUUID()}-${sanitizeFilename(filename)}`. - [ ] Implement `MAX_PORTAL_FILE_BYTES = 100 * 1024 * 1024` (100 MB) and `validateUpload({ mime_type, file_size_bytes })` — reject size > 100 MB with `400 FILE_TOO_LARGE`; all file types allowed (no mime allowlist per spec — "All file types"), but require a non-empty `mime_type`. - [ ] Implement `createSignedPutUrl(env, r2Key, { contentType, expiresIn = 300 })` — return a signed R2 PUT URL via `env.STORAGE.createSignedUrl(r2Key, { method: 'PUT', expiresIn })` (or the equivalent presign for the deployed R2 access path); used by both staff and portal upload-url endpoints. - [ ] Implement `createSignedDownloadUrl(env, r2Key, { expiresIn = 300 })` — short-lived (300s) signed GET URL via `env.STORAGE.createSignedUrl(r2Key, { expiresIn })`. - [ ] Implement `deleteR2Object(env, r2Key)` — `await env.STORAGE.delete(r2Key)` (synchronous delete per this spec; no soft-delete queue). **Schema / Interfaces:** ```ts export const MAX_PORTAL_FILE_BYTES = 100 * 1024 * 1024; export function sanitizeFilename(name: string): string; export function buildPortalFileKey(tenantId: string, customerId: string, filename: string): string; export function validateUpload(input: { mime_type: string; file_size_bytes: number }): void; // throws ApiError(400) on violation export function createSignedPutUrl(env: Env, r2Key: string, opts: { contentType: string; expiresIn?: number }): Promise; export function createSignedDownloadUrl(env: Env, r2Key: string, opts?: { expiresIn?: number }): Promise; export function deleteR2Object(env: Env, r2Key: string): Promise; ``` **Acceptance:** - [ ] `buildPortalFileKey('t','c','../etc/passwd report.pdf')` produces `t/portal/c/-etc_passwd report.pdf` (no traversal, no leading slash). - [ ] `validateUpload` throws `400 FILE_TOO_LARGE` at > 100 MB and passes at exactly 100 MB. - [ ] Download signed URL TTL is 300s. ### Task 4: Staff API routes (`/api/portal-files/*` + `/api/customers/:customerId/portal-files`) **Blocks:** 6 · **Blocked by:** 2, 3 **Files:** - Create: `apps/zync-api/src/server/routes/portal-files.ts` - Modify: `apps/zync-api/src/server/routes/index.ts` (mount router under `authMiddleware`) - Create: `apps/zync-api/src/server/validation/portal-files.ts` (zod schemas) **Steps:** - [ ] Mount all staff routes behind `authMiddleware`; derive `tenantId`/`userId` from `Session`. Use `tenantQuery(db, tenantId)` exclusively (never `portalQuery`). - [ ] `GET /api/customers/:customerId/portal-files` — `requirePermission('customers:read')`; query `{ project_id?, page? }` (page-size fixed at 50, offset = (page-1)*50); return staff list (internal + visible) via `listPortalFilesForStaff`, each item including `uploaded_by_client` badge flag and `uploader_name`. - [ ] `POST /api/portal-files/upload-url` — `requirePermission('customers:write')`; zod-validate body `{ filename, mime_type, file_size_bytes, customer_id }`; `validateUpload`; build `r2_key` via `buildPortalFileKey(tenantId, customer_id, filename)`; return `{ upload_url, r2_key }` where `upload_url = createSignedPutUrl(env, r2_key, { contentType: mime_type })`. - [ ] `POST /api/portal-files` — `requirePermission('customers:write')`; zod-validate `{ customer_id, project_id?, r2_key, filename, mime_type, file_size_bytes, description?, visible_to_portal, expires_at? }`; call `createPortalFile` with `uploadedBy = userId`, `uploadedByPortalUser = null`; map `InvalidUploaderError` → 400. Verify `customer_id` belongs to the tenant before insert. - [ ] `PATCH /api/portal-files/:id` — `requirePermission('customers:write')`; zod-validate `{ description?, visible_to_portal?, expires_at? }` (only these); `updatePortalFile`; 404 if not in tenant. - [ ] `DELETE /api/portal-files/:id` — `requirePermission('customers:write')`; `deletePortalFile` (returns row), then `deleteR2Object(env, row.r2_key)`; respond 204. 404 if not in tenant. - [ ] `GET /api/portal-files/:id/download` — **shared endpoint**, `authMiddleware` only (any authenticated staff or portal session); resolve the file; dual ownership check: if staff session → require `file.tenant_id === session.tenantId`; if portal session (JWT carries `customerId`) → require `file.customer_id === session.customerId` AND for portal also require `visible_to_portal = true` and not expired. Return `{ url, filename }` with `url = createSignedDownloadUrl(env, file.r2_key)` (300s). **Schema / Interfaces:** ```ts // validation/portal-files.ts export const uploadUrlSchema = z.object({ filename: z.string().min(1), mime_type: z.string().min(1), file_size_bytes: z.number().int().positive(), customer_id: z.string().uuid() }); export const createPortalFileSchema = z.object({ customer_id: z.string().uuid(), project_id: z.string().uuid().optional(), r2_key: z.string().min(1), filename: z.string().min(1), mime_type: z.string().min(1), file_size_bytes: z.number().int().positive(), description: z.string().optional(), visible_to_portal: z.boolean(), expires_at: z.string().datetime().optional() }); export const updatePortalFileSchema = z.object({ description: z.string().optional(), visible_to_portal: z.boolean().optional(), expires_at: z.string().datetime().nullable().optional() }); // Routes (Hono): // GET /api/customers/:customerId/portal-files -> { items: StaffPortalFileRow[], total } // POST /api/portal-files/upload-url -> { upload_url, r2_key } // POST /api/portal-files -> PortalFile (201) // PATCH /api/portal-files/:id -> PortalFile // DELETE /api/portal-files/:id -> 204 // GET /api/portal-files/:id/download -> { url, filename } ``` **Acceptance:** - [ ] Staff without `customers:write` cannot reach upload-url/create/patch/delete (403); `customers:read` suffices for list. - [ ] Creating a record sets `uploaded_by` to the staff user and leaves `uploaded_by_portal_user` null. - [ ] DELETE removes both the DB row and the R2 object. - [ ] `/download` returns 403/404 for a staff caller whose tenant doesn't own the file, and for a portal caller whose `customerId` doesn't match. - [ ] All bodies are zod-validated; no raw Drizzle in route handlers (uses Task 2 functions). ### Task 5: Portal API routes (`/api/portal/files`, `/api/portal/files/upload-url`) **Blocks:** 7 · **Blocked by:** 2, 3 **Files:** - Create: `apps/zync-api/src/server/portal/files.ts` - Modify: `apps/zync-api/src/server/portal/index.ts` (mount under `portalAuthMiddleware`) **Steps:** - [ ] Mount behind `portalAuthMiddleware`; take `tenantId` and `customerId` from JWT claims only (never URL/body). Use `portalQuery(db, tenantId, customerId)` exclusively. - [ ] Gate the whole router on `tenant_settings.portal_visibility.show_files`: if `show_files !== true`, return 404 (section not enabled). Read the flag defensively (treat missing as false). Source: `customer-portal-access-control` (spec 82). - [ ] `GET /api/portal/files` — list visible, non-expired files for the JWT customer via `listPortalFilesForPortal`; response items expose only `{ id, filename, file_size_bytes, mime_type, project_id, created_at }` (no `uploaded_by`, no internal fields). - [ ] `POST /api/portal/files/upload-url` — guard on `tenant_settings.portal_can_upload_files === true` (defensive: column from spec 136 `customer-portal-settings-ui`, same wave 11 and NOT a hard dependency; if the column is absent treat as `false` and return 403 `UPLOAD_DISABLED`). zod-validate `{ filename, mime_type, file_size_bytes }`; `validateUpload`; build key with the JWT `customerId`; return `{ upload_url, r2_key }`. - [ ] Add a portal-side create step so client uploads land in `portal_files`: after PUT, the client calls `POST /api/portal/files` (same router) with `{ r2_key, filename, mime_type, file_size_bytes, project_id? }`; create the row with `uploadedBy = null`, `uploadedByPortalUser = portalUser.id` (the JWT `sub`), `visible_to_portal = true`. Re-check `portal_can_upload_files` here too. - [ ] Downloads reuse the shared `GET /api/portal-files/:id/download` endpoint (Task 4) — portal callers are authorized there via the `customerId`/visibility/expiry check; do NOT duplicate a portal download route. **Schema / Interfaces:** ```ts // Routes (Hono, portalAuthMiddleware): // GET /api/portal/files -> { items: { id, filename, file_size_bytes, mime_type, project_id, created_at }[], total } // POST /api/portal/files/upload-url -> { upload_url, r2_key } (403 UPLOAD_DISABLED if !portal_can_upload_files) // POST /api/portal/files -> created file (uploaded_by_portal_user = JWT.sub) // Portal upload-url body: z.object({ filename: z.string().min(1), mime_type: z.string().min(1), file_size_bytes: z.number().int().positive() }) ``` **Acceptance:** - [ ] With `show_files` false, every `/api/portal/files*` route returns 404. - [ ] With `portal_can_upload_files` false/absent, `POST /api/portal/files/upload-url` and `POST /api/portal/files` return 403 `UPLOAD_DISABLED`. - [ ] Portal list never returns `visible_to_portal=false` or expired files, and never another customer's files (customerId from JWT). - [ ] Client-created rows have `uploaded_by = NULL` and `uploaded_by_portal_user = JWT.sub`. ### Task 6: Staff UI — Files tab (customer detail + project detail) **Blocks:** — · **Blocked by:** 4 **Files:** - Create: `apps/zync-app/src/features/portal-files/PortalFilesTab.tsx` - Create: `apps/zync-app/src/features/portal-files/UploadFileSheet.tsx` - Create: `apps/zync-app/src/features/portal-files/usePortalFiles.ts` (react-query hooks) - Modify: `apps/zync-app/src/features/customers/CustomerDetail.tsx` (render `` in the existing `[Files]` tab placeholder — do not redefine the tab shell) - Modify: `apps/zync-app/src/features/projects/ProjectDetail.tsx` (add a `[Files]` tab scoped to that project's customer + project) **Steps:** - [ ] `usePortalFiles`: react-query hooks `usePortalFileList(customerId, { projectId?, page })`, `useUploadFileMutation`, `useUpdatePortalFile`, `useDeletePortalFile`, and a `useDownloadPortalFile` that GETs `/api/portal-files/:id/download` then navigates to the signed `url`. - [ ] `PortalFilesTab`: render a `DataTable`/list of files — filename, `file_size_bytes` (humanized), project name (or "No project"), uploaded date + uploader, a "Visible to client ✓" indicator, and an **"Uploaded by client"** `Badge` when `uploaded_by_client`. Row actions: `[Download]`, `[Edit]` (opens edit sheet for description/visibility/expiry), `[Remove]` (confirm dialog → DELETE). Show `EmptyState` when no files. When rendered from project detail, pass `projectId` to scope the list and pre-select the project in the upload sheet. - [ ] `UploadFileSheet`: `Sheet` with file drop/browse input ("Max 100 MB · All file types"), `Description` `Textarea`, `Link to project` `Select` (No project + tenant's projects for that customer), `Visibility` `Radio` (Visible to client / Internal only → `visible_to_portal`), `Expires` `Radio` (Never / On date → date input → `expires_at`). On submit: (1) `POST /api/portal-files/upload-url`; (2) client-side PUT the file to `upload_url`; (3) `POST /api/portal-files` with `r2_key` and metadata; show `` during PUT and `toast` on success/failure; invalidate the list query. - [ ] Client-side guard: reject files > 100 MB before requesting an upload URL. - [ ] a11y: file list as a labeled region with `aria-label`; each row's actions are real `