lunarfront-app/planning/23_File_Storage_Architecture.md

Music Store Management Platform

File Storage Architecture

Version 1.0 | Draft

1. Overview

The platform requires file storage for images, PDFs, and documents across many domains: member identity documents, product photos, rental agreement PDFs, repair ticket photos, signature captures, barcode labels, and reports. This document defines a storage abstraction layer that supports both local filesystem and S3-compatible backends, with integrated backup/restore.

2. Storage Provider Interface

The storage layer is abstracted behind a StorageProvider interface. The application never writes to disk or S3 directly — all file operations go through the provider. This allows stores to choose their storage backend without code changes.

2.1 Providers

Provider | Backend | Best For local | Local filesystem directory | Self-hosted stores, Docker volume mounts, bare metal installs s3 | S3-compatible API | SaaS deployment, AWS S3, MinIO, DigitalOcean Spaces, Cloudflare R2

2.2 Configuration

Variable | Description | Default STORAGE_PROVIDER | local or s3 | local STORAGE_LOCAL_PATH | Filesystem path for local provider | ./data/files S3_BUCKET | S3 bucket name | (required if s3) S3_REGION | AWS region or endpoint region | us-east-1 S3_ENDPOINT | Custom endpoint for non-AWS S3 (MinIO, DO, R2) | (optional) S3_ACCESS_KEY | Access key | (required if s3) S3_SECRET_KEY | Secret key | (required if s3)

2.3 Interface

interface StorageProvider {
  put(path: string, data: Buffer, contentType: string): Promise<void>
  get(path: string): Promise<Buffer>
  delete(path: string): Promise<void>
  exists(path: string): Promise<boolean>
  getUrl(path: string, expiresIn?: number): Promise<string>
}

• put — write a file
• get — read a file
• delete — remove a file
• exists — check if a file exists
• getUrl — get a URL to serve the file
  • Local: returns /v1/files/{path} (served by Fastify)
  • S3: returns a presigned URL (default 1 hour expiry)

3. File Table Schema

All files are tracked in a central file table. Domain tables reference files by UUID foreign key — never by URL, path, or base64.

3.1 file

Column | Type | Notes id | uuid PK | company_id | uuid FK | Tenant scoping path | varchar | Relative path within storage root (e.g. {company_id}/members/{member_id}/identifiers/{id}/front.jpg) filename | varchar | Original filename from upload content_type | varchar | MIME type (image/jpeg, application/pdf, etc.) size_bytes | integer | File size in bytes entity_type | varchar | What this file belongs to: member_identifier, product, rental_agreement, repair_ticket, etc. entity_id | uuid | ID of the owning record category | varchar | Purpose within entity: front, back, signature, intake, completed, primary, thumbnail, etc. uploaded_by | uuid FK | Employee who uploaded created_at | timestamptz |

3.2 Indexes

• (company_id, entity_type, entity_id) — find all files for a record
• (company_id, path) — unique, prevents duplicates

4. File Path Convention

Files are organized by company and entity type for easy browsing and backup:

{company_id}/
  members/{member_id}/
    identifiers/{identifier_id}/
      front.jpg
      back.jpg
  products/{product_id}/
    primary.jpg
    gallery-{n}.jpg
    thumbnail.jpg
  rentals/{rental_id}/
    agreements/{agreement_id}.pdf
    signatures/{agreement_id}.png
  repairs/{ticket_id}/
    intake-{n}.jpg
    completed-{n}.jpg
    invoice.pdf
  labels/
    batch-{batch_id}.pdf
  reports/
    {report_type}-{date}.pdf

Path is always relative to the storage root. The storage provider prepends the root path or S3 bucket prefix.

5. Upload API

5.1 Upload Endpoint

POST /v1/files/upload

Multipart form data:

• file — the file (required)
• entityType — string (required)
• entityId — uuid (required)
• category — string (required)

Response: the created file record (id, path, contentType, sizeBytes, url)

5.2 Limits

Limit | Value Max file size | 10 MB (images), 25 MB (PDFs) Allowed types | image/jpeg, image/png, image/webp, application/pdf Max files per entity | 20

5.3 Image Processing

On upload of image files:

• Validate dimensions and format
• Generate thumbnail (200x200, cover crop) stored as a sibling file with category thumbnail
• Strip EXIF data for privacy (GPS coordinates, camera info)
• Convert to JPEG if PNG and > 1MB (with quality 85)

No processing for PDFs.

6. Serving Files

6.1 Download Endpoint

GET /v1/files/{fileId}

• Authenticated — requires valid JWT
• Returns the file with correct Content-Type header
• Local provider: reads from disk, streams to response
• S3 provider: redirects to presigned URL (302)

6.2 Thumbnail Shortcut

GET /v1/files/{fileId}/thumbnail

Returns the thumbnail version if it exists, otherwise the original.

6.3 Listing Files

GET /v1/files?entityType=product&entityId={uuid}

Returns all files for a given entity. Response: { data: File[] }

7. Backup & Restore

7.1 Backup Command

forte-backup [--output backup.tar.gz]

Steps:

1. pg_dump the database → backup/db.sql
2. Copy the storage directory → backup/files/
3. Write metadata (version, date, provider) → backup/manifest.json
4. Compress → forte-backup-{YYYY-MM-DD}.tar.gz

For S3 provider: syncs the bucket to a local temp directory first, then archives.

7.2 Restore Command

forte-restore backup.tar.gz [--target-db forte] [--target-storage ./data/files]

Steps:

1. Extract archive
2. Drop and recreate target database
3. psql < db.sql to restore schema and data
4. Copy files/ to storage root (or upload to S3 bucket)
5. Verify file count matches database records

7.3 Manifest

{
  "version": "1.0",
  "date": "2026-03-28T12:00:00Z",
  "database": "forte",
  "storageProvider": "local",
  "fileCount": 1234,
  "totalSizeBytes": 524288000,
  "forteVersion": "0.1.0"
}

8. Domain Integration

8.1 Which Domains Use File Storage

Domain | Entity Type | Categories | Typical Files Members | member | profile | Profile photo (1 per member). UI shows silhouette placeholder when empty. Member Identifiers | member_identifier | front, back | DL/passport/school ID photos (2 per ID) Products | product | primary, gallery, thumbnail | Product catalog photos (1-5 per product) Rental Agreements | rental_agreement | document, signature, guardian_signature | Signed PDF + signature images Repair Tickets | repair_ticket | intake, in_progress, completed, invoice | Condition photos + invoice PDF Batch Repairs | repair_batch | summary, invoice | Batch summary PDF Delivery | delivery_event | proof, signature | Delivery proof photos + signature Barcode Labels | label_batch | labels | Generated label PDFs Reports | report | report | Exported report PDFs Consignment | consignment_detail | agreement | Consignment agreement PDF

8.2 Schema Changes

member — Add Profile Photo

Column | Type | Notes profile_image_file_id | uuid FK | References file.id — profile photo. UI shows silhouette placeholder when null.

member_identifier — Replace Base64 Columns

Remove image_front (text) and image_back (text) columns. Replace with:

Column | Type | Notes image_front_file_id | uuid FK | References file.id — front image image_back_file_id | uuid FK | References file.id — back image

product — Add Image Support

Column | Type | Notes primary_image_file_id | uuid FK | References file.id — main product photo

Additional images stored via the file table with entity_type = product.

rental_agreement — Document Storage

Column | Type | Notes document_file_id | uuid FK | References file.id — signed PDF signature_file_id | uuid FK | References file.id — signature image guardian_signature_file_id | uuid FK | References file.id — guardian signature (if minor)

9. Docker Compose — Dev Storage

9.1 Local Provider (Default)

No additional services needed. Files stored in ./data/files/ volume:

services:
  api:
    volumes:
      - ./data/files:/app/data/files
    environment:
      - STORAGE_PROVIDER=local
      - STORAGE_LOCAL_PATH=/app/data/files

9.2 MinIO for S3 Testing (Optional)

services:
  minio:
    image: minio/minio:latest
    ports:
      - "9000:9000"
      - "9001:9001"
    volumes:
      - minio-data:/data
    environment:
      - MINIO_ROOT_USER=minioadmin
      - MINIO_ROOT_PASSWORD=minioadmin
    command: server /data --console-address ":9001"

  api:
    environment:
      - STORAGE_PROVIDER=s3
      - S3_ENDPOINT=http://minio:9000
      - S3_BUCKET=forte
      - S3_REGION=us-east-1
      - S3_ACCESS_KEY=minioadmin
      - S3_SECRET_KEY=minioadmin

10. Implementation Order

1. Create file table and migration
2. Implement StorageProvider interface with LocalProvider
3. Add @fastify/multipart for file uploads
4. Create upload endpoint (POST /v1/files/upload)
5. Create download endpoint (GET /v1/files/:id)
6. Create list endpoint (GET /v1/files?entityType=&entityId=)
7. Add image processing (thumbnails, EXIF strip) via sharp
8. Update member_identifier to use file references
9. Implement S3Provider
10. Add backup/restore CLI commands
11. Add to Docker Compose dev config

11. Business Rules

• Files are always tenant-scoped by company_id — no cross-company access
• Deleting an entity should soft-delete its files (mark inactive, don't remove from storage)
• Hard delete of files only via backup restore or explicit admin action
• File paths must not contain user input — generated from UUIDs only
• All uploads require authentication
• Image uploads are validated for content type (magic bytes, not just extension)
• PDFs are validated for structure (not just MIME type)
• Thumbnails generated server-side — frontend never resizes
• Storage provider is set at startup — cannot be changed at runtime
• Backup includes ALL files regardless of soft-delete status