File Uploads Design

Overview

Add file upload/download support to Guava so that faculty can attach documents (PDFs, Word docs) to entities like didactic sessions, and residents can download them. Eventually residents will also upload files for different use cases.

Files are stored in Cloudflare R2 (S3-compatible, zero egress fees) and served via presigned URLs with short expiry (~15 minutes). The Go backend never proxies file data — it only manages metadata and generates presigned URLs.

Storage

Cloudflare R2 bucket, accessed via the AWS SDK for Go v2 (S3-compatible API).

Configuration via environment variables:

Variable Purpose
R2_ACCOUNT_ID Cloudflare account ID
R2_ACCESS_KEY_ID R2 API token key ID
R2_ACCESS_KEY_SECRET R2 API token secret
R2_BUCKET_NAME Bucket name (e.g., guava-files)

Database Schema

files table

Tracks file metadata. Actual bytes live in R2.

CREATE TABLE files (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    program_id    BIGINT NOT NULL REFERENCES programs(id),
    uploaded_by   BIGINT NOT NULL REFERENCES users(id),
    filename      TEXT NOT NULL,
    content_type  TEXT NOT NULL,
    size_bytes    BIGINT NOT NULL,
    r2_key        TEXT NOT NULL UNIQUE,
    confirmed     BOOLEAN NOT NULL DEFAULT false,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

Association tables

Join tables link files to domain entities. Starting with didactic sessions:

CREATE TABLE didactic_session_files (
    didactic_session_id BIGINT NOT NULL REFERENCES didactic_sessions(id) ON DELETE CASCADE,
    file_id             UUID NOT NULL REFERENCES files(id) ON DELETE CASCADE,
    PRIMARY KEY (didactic_session_id, file_id)
);

To attach files to new entity types in the future, add more join tables (e.g., resident_files). The files table stays unchanged.

Upload Flow

  1. Client calls CreateUploadURL RPC with filename, content type, size, and the target entity (e.g., didactic session ID).
  2. Backend validates auth and permission on the target entity. Generates a UUID, constructs the R2 key, inserts an unconfirmed files row, and generates a presigned PUT URL with constraints:
  3. Backend returns the presigned URL and file ID.
  4. Client uploads the file directly to R2 via HTTP PUT to the presigned URL.
  5. Client calls ConfirmUpload RPC with the file ID.
  6. Backend verifies:
  7. Backend sets confirmed = true and creates the join table entry (e.g., didactic_session_files row).

Handling upload failures

If the upload to R2 succeeds but the confirm call fails (e.g., user loses internet):

Download Flow

  1. Client calls GetDownloadURL RPC with the file ID.
  2. Backend validates auth — checks the user has access to the entity the file is attached to.
  3. Backend generates a presigned GET URL (15 min expiry) and returns it.
  4. Client downloads directly from R2 via the presigned URL.

Delete Flow

  1. Client calls DeleteFile RPC with the file ID.
  2. Backend validates auth and permission.
  3. Backend deletes the R2 object, then deletes the files row (cascades to join tables).

Backend Architecture

Feature structure

internal/features/file/
├── service.go          # RPC handlers
├── service_test.go     # Tests
├── queries.sql         # sqlc queries
├── models.go           # Type converters
└── r2.go               # R2 storage client wrapper

Storage client interface

Thin wrapper around AWS SDK, makes testing straightforward:

type StorageClient interface {
    GeneratePresignedPutURL(ctx context.Context, key, contentType string, maxSize int64) (string, error)
    GeneratePresignedGetURL(ctx context.Context, key string) (string, error)
    HeadObject(ctx context.Context, key string) (*ObjectInfo, error)
    DeleteObject(ctx context.Context, key string) error
}

Production implementation uses the AWS SDK for Go v2 with R2 endpoint configuration. Tests use a mock implementation.

Service constructor

Follows existing pool pattern, plus the storage dependency:

type Service struct {
    pool    *pgxpool.Pool
    storage StorageClient
}

func NewService(pool *pgxpool.Pool, storage StorageClient) *Service {
    return &Service{pool: pool, storage: storage}
}

Registration

In cmd/server/main.go:

storageClient := file.NewR2Client(r2Config)
fileService := file.NewService(pool, storageClient)
mux.Handle(filev1connect.NewFileServiceHandler(fileService))

Proto definition

New proto/file/v1/file.proto:

RPC details

CreateUploadURLRequest: - filename (string) — original filename - content_type (string) — MIME type - size_bytes (int64) — file size for validation - Entity reference (oneof): didactic_session_id (int64), extensible to other entities

CreateUploadURLResponse: - upload_url (string) — presigned PUT URL - file_id (string) — UUID of the created file record

ConfirmUploadRequest: - file_id (string) — UUID returned from CreateUploadURL

GetDownloadURLRequest: - file_id (string)

GetDownloadURLResponse: - download_url (string) — presigned GET URL - filename (string) — original filename (for UI display) - content_type (string) - size_bytes (int64)

ListFilesRequest: - Entity reference (oneof): didactic_session_id (int64)

ListFilesResponse: - files (repeated File) — file metadata list

File message: - id, filename, content_type, size_bytes, created_at, uploaded_by_name

Frontend Integration

Upload hook

A useFileUpload custom hook:

  1. Call createUploadURL mutation via Connect-Web to get presigned URL + file ID
  2. PUT the file directly to R2 using fetch (or XMLHttpRequest for progress tracking)
  3. Call confirmUpload mutation with the file ID
  4. Invalidate the entity’s file list query

Download

Call getDownloadURL → open the presigned URL in a new tab or trigger browser download.

UI components

A reusable FileAttachments component that: - Takes an entity type + ID as props - Displays list of attached files (name, size, upload date) - Handles upload (file picker + drag-and-drop) - Handles download (presigned URL redirect) - Handles delete (with confirmation)

Start by integrating into the didactic session detail page. Reuse for other entities as needed.

Cache invalidation

Add file list queries to frontend/app/lib/query-relationships.ts so that upload/confirm/delete mutations properly invalidate the file list.

Access Control

Files inherit access from their associated entity: - If a user can view the didactic session, they can view and download its files - Upload/delete permissions follow the same rules as modifying the parent entity - The uploaded_by field on files enables “only the uploader can delete” rules if needed later

Presigned URLs provide short-lived (15 min) access without requiring authentication on the R2 side. The auth check happens in the backend when generating the URL.

Testing

Follows existing two-layer approach:

Dependencies

New Go dependency: - github.com/aws/aws-sdk-go-v2 (+ S3 and presign sub-packages) — R2 is S3-compatible

No new frontend dependencies needed — fetch handles the presigned URL upload.