File Attachments for Helpdesk Tickets

[SvenVw]

Context

Users often need to share screenshots, PDFs, or CSV exports when reporting issues. File attachments enable richer communication between users and agents. Files are stored in Google Cloud Storage (GCS) — consistent with existing FDM infrastructure.

Depends on: #599 (MVP — messages table must exist)

Scope

In Scope

• attachments table: metadata (file name, size, MIME type, GCS path, message_id)
• File upload: customer and agent can attach files when submitting a ticket or replying
• GCS storage: signed upload URL (direct-to-GCS) or server-side upload
• File download: signed download URL (time-limited, authenticated)
• File validation: max 25 MB per file, max 5 files per message, allowed MIME types
• Inline display: images shown inline in message thread, other files as download links
• Upload UI: drag-and-drop zone + file picker button, progress indicator
• GCS lifecycle: auto-delete attachments after 1 year (GCS lifecycle policy)
• Virus scanning note: document that ClamAV or Cloud Storage scanning should be considered

Acceptance Criteria

• Users can attach up to 5 files (max 25 MB each) when creating a ticket or replying
• Agents can attach files when replying
• Uploaded files are stored in GCS under helpdesk/{ticket_id}/{message_id}/{filename}
• Images (jpg, png, gif, webp) display inline in the message thread
• Non-image files display as a download link with filename + size
• Download URLs are signed and expire after 1 hour
• Rejected files (too large, wrong type) show a clear error message
• Upload progress is visible to the user
• Attachments are included in email notifications (as links, not inline)
• GCS lifecycle policy deletes files older than 1 year
• Deleting a message soft-deletes its attachments (marks as deleted, GCS cleanup later)

Technical Implementation

1. Schema

export const attachments = fdmHelpdeskSchema.table("attachments", {
    attachment_id: text().primaryKey(),
    message_id: text().notNull(),
    ticket_id: text().notNull(),
    file_name: text().notNull(),
    file_size: integer().notNull(),           // bytes
    mime_type: text().notNull(),
    gcs_path: text().notNull(),               // full GCS object path
    uploaded_by: text().notNull(),
    deleted_at: timestamp({ withTimezone: true }),
    created: timestamp({ withTimezone: true }).notNull().defaultNow(),
}, (table) => [
    index("attachment_message_idx").on(table.message_id),
    index("attachment_ticket_idx").on(table.ticket_id),
])

2. Allowed MIME Types

const ALLOWED_MIME_TYPES = [
    "image/jpeg", "image/png", "image/gif", "image/webp",
    "application/pdf",
    "text/csv", "text/plain",
    "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", // .xlsx
    "application/vnd.openxmlformats-officedocument.wordprocessingml.document", // .docx
]

const MAX_FILE_SIZE = 25 * 1024 * 1024  // 25 MB
const MAX_FILES_PER_MESSAGE = 5

3. Upload Flow

Option A — Signed URL (preferred for large files):

1. Client requests signed upload URL from server (passes filename, size, MIME type)
2. Server validates (size, type, quota) → generates GCS signed URL (15 min expiry)
3. Client uploads directly to GCS using signed URL
4. Client confirms upload → server creates attachment record

Option B — Server-side upload (simpler, fine for <10 MB):

1. Client sends file in multipart form data with the message
2. Server validates → streams to GCS → creates attachment record

Recommend: Start with Option B for simplicity, upgrade to Option A if performance is a concern.

4. Download Flow

export async function getAttachmentUrl(fdm: FdmType, attachment_id: string, user_id: string): Promise<string> {
    // 1. Verify user has access (is requester or agent on this ticket)
    // 2. Generate signed download URL (1h expiry)
    // 3. Return URL
}

5. UI — Upload Component

┌──────────────────────────────────────────────────────────────┐
│  Antwoord                                                    │
│  ┌────────────────────────────────────────────────────────┐  │
│  │ [rich text reply area]                                 │  │
│  └────────────────────────────────────────────────────────┘  │
│                                                              │
│  ┌ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ┐  │
│  │  📎 Sleep bestanden hierheen of klik om te uploaden   │  │
│  │     Max. 25 MB per bestand · max. 5 bestanden         │  │
│  └ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ┘  │
│                                                              │
│  📄 screenshot.png (1.2 MB)  ████████░░ 80%        [✕]      │
│  📄 export.csv (340 KB)      ██████████ ✓                    │
│                                                              │
│                                            [Verstuur]        │
└──────────────────────────────────────────────────────────────┘

6. UI — Inline Display in Message Thread

┌──────────────────────────────────────────────────────────────┐
│  Gebruiker · 10 min geleden                                  │
│  Het scherm ziet er zo uit na het importeren:                 │
│                                                              │
│  ┌──────────────────────────────┐                            │
│  │ [inline image preview]       │                            │
│  │ screenshot.png · 1.2 MB      │                            │
│  └──────────────────────────────┘                            │
│                                                              │
│  📎 export.csv · 340 KB  [⬇ Download]                       │
└──────────────────────────────────────────────────────────────┘

7. GCS Configuration

Bucket: fdm-helpdesk-attachments
Path pattern: helpdesk/{ticket_id}/{message_id}/{filename}
Lifecycle: Delete objects older than 365 days
IAM: fdm-app service account has storage.objects.create + storage.objects.get
CORS: Allow uploads from app domain

8. Email Notifications with Attachments

When sending email notification for a new reply with attachments:

• Include attachment names and sizes in the email
• Provide signed download links (24h expiry for email links)
• Do NOT embed files in email (too large, deliverability issues)

9. App Routes

Route	Purpose
api.support.upload.tsx	Handle upload request, return signed URL or accept file
api.support.download.$attachment_id.tsx	Generate signed download URL and redirect

Testing Requirements

• Integration tests: Upload file → verify GCS path stored → download with signed URL → verify content
• Unit tests: MIME type validation, file size validation, GCS path generation
• Manual tests: Drag-and-drop, multiple files, oversized file rejection, inline image rendering
• Edge cases: Upload cancelled mid-way, duplicate filename, special characters in filename

Definition of Done

Users and agents can attach files to tickets and messages. Images display inline. Files are securely stored in GCS with time-limited access URLs and auto-deleted after 1 year.