File storage and media handling are critical components of modern web applications. This comprehensive guide teaches you how to implement robust, scalable file management with Supabase Storage and Next.js, from basic uploads to advanced optimization techniques.

Whether you are building a content management system, social platform, or e-commerce site, proper file handling directly impacts user experience, performance, and costs. Poor file management leads to slow uploads, security vulnerabilities, and expensive storage bills.

This guide covers everything from secure upload patterns to advanced image optimization, with production-tested code you can implement immediately.

Supabase Storage Fundamentals

Understanding Buckets and Policies

Supabase Storage organizes files into buckets with configurable access policies and security rules.

-- Create buckets for different use cases
INSERT INTO storage.buckets (id, name, public) VALUES 
('avatars', 'avatars', true),
('documents', 'documents', false),
('product-images', 'product-images', true),
('user-uploads', 'user-uploads', false);

-- Set up RLS policies for secure access
CREATE POLICY "Users can upload their own avatars" ON storage.objects
FOR INSERT WITH CHECK (
  bucket_id = 'avatars' 
  AND auth.uid()::text = (storage.foldername(name))[1]
);

CREATE POLICY "Users can view their own documents" ON storage.objects
FOR SELECT USING (
  bucket_id = 'documents' 
  AND auth.uid()::text = (storage.foldername(name))[1]
);

CREATE POLICY "Organization members can access uploads" ON storage.objects
FOR ALL USING (
  bucket_id = 'user-uploads'
  AND EXISTS (
    SELECT 1 FROM user_organizations uo
    WHERE uo.user_id = auth.uid()
    AND uo.organization_id::text = (storage.foldername(name))[1]
  )
);

Enter fullscreen mode Exit fullscreen mode

File Metadata Management

Store file metadata in your database for better querying and organization.

-- Create table for file metadata
CREATE TABLE file_uploads (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  filename TEXT NOT NULL,
  original_filename TEXT NOT NULL,
  file_size BIGINT NOT NULL,
  mime_type TEXT NOT NULL,
  storage_path TEXT NOT NULL,
  bucket_name TEXT NOT NULL,
  uploaded_by UUID REFERENCES users(id) ON DELETE CASCADE,
  organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
  upload_completed_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),

  -- Add constraints for data integrity
  CONSTRAINT valid_file_size CHECK (file_size > 0 AND file_size <= 53687091200), -- 50GB
  CONSTRAINT valid_mime_type CHECK (mime_type ~ '^[a-z]+/[a-z0-9\-\+\.]+$')
);

-- Create indexes for common queries
CREATE INDEX idx_file_uploads_user ON file_uploads(uploaded_by);
CREATE INDEX idx_file_uploads_org ON file_uploads(organization_id);
CREATE INDEX idx_file_uploads_bucket ON file_uploads(bucket_name);
CREATE INDEX idx_file_uploads_completed ON file_uploads(upload_completed_at) 
  WHERE upload_completed_at IS NOT NULL;

Enter fullscreen mode Exit fullscreen mode

Secure File Upload Implementation

Client-Side Upload Component

Build a robust upload component with progress tracking and error handling.

// components/FileUpload.tsx
'use client'

import { useState, useCallback } from 'react'
import { createClient } from '@/lib/supabase/client'
import { useAuth } from '@/hooks/useAuth'

interface FileUploadProps {
  bucket: string
  path?: string
  accept?: string
  maxSize?: number
  onUploadComplete?: (file: UploadedFile) => void
  onUploadError?: (error: string) => void
}

interface UploadedFile {
  id: string
  filename: string
  url: string
  size: number
}

export function FileUpload({
  bucket,
  path = '',
  accept = '*/*',
  maxSize = 10 * 1024 * 1024, // 10MB default
  onUploadComplete,
  onUploadError
}: FileUploadProps) {
  const [uploading, setUploading] = useState(false)
  const [progress, setProgress] = useState(0)
  const { user } = useAuth()
  const supabase = createClient()

  const validateFile = useCallback((file: File): string | null => {
    if (file.size > maxSize) {
      return `File size must be less than ${Math.round(maxSize / 1024 / 1024)}MB`
    }

    if (accept !== '*/*') {
      const acceptedTypes = accept.split(',').map(type => type.trim())
      const isValidType = acceptedTypes.some(type => {
        if (type.startsWith('.')) {
          return file.name.toLowerCase().endsWith(type.toLowerCase())
        }
        return file.type.match(type.replace('*', '.*'))
      })

      if (!isValidType) {
        return `File type not allowed. Accepted types: ${accept}`
      }
    }

    return null
  }, [accept, maxSize])

  const uploadFile = useCallback(async (file: File) => {
    if (!user) {
      onUploadError?.('User not authenticated')
      return
    }

    const validationError = validateFile(file)
    if (validationError) {
      onUploadError?.(validationError)
      return
    }

    setUploading(true)
    setProgress(0)

    try {
      // Generate unique filename
      const fileExt = file.name.split('.').pop()
      const fileName = `${user.id}/${Date.now()}-${Math.random().toString(36).substring(2)}.${fileExt}`
      const filePath = path ? `${path}/${fileName}` : fileName

      // Upload file with progress tracking
      const { data, error } = await supabase.storage
        .from(bucket)
        .upload(filePath, file, {
          cacheControl: '3600',
          upsert: false,
          onUploadProgress: (progress) => {
            setProgress((progress.loaded / progress.total) * 100)
          }
        })

      if (error) throw error

      // Save metadata to database
      const { data: fileRecord, error: dbError } = await supabase
        .from('file_uploads')
        .insert({
          filename: fileName,
          original_filename: file.name,
          file_size: file.size,
          mime_type: file.type,
          storage_path: data.path,
          bucket_name: bucket,
          uploaded_by: user.id,
          upload_completed_at: new Date().toISOString()
        })
        .select()
        .single()

      if (dbError) throw dbError

      // Get public URL
      const { data: { publicUrl } } = supabase.storage
        .from(bucket)
        .getPublicUrl(data.path)

      const uploadedFile: UploadedFile = {
        id: fileRecord.id,
        filename: file.name,
        url: publicUrl,
        size: file.size
      }

      onUploadComplete?.(uploadedFile)
    } catch (error) {
      console.error('Upload error:', error)
      onUploadError?.(error instanceof Error ? error.message : 'Upload failed')
    } finally {
      setUploading(false)
      setProgress(0)
    }
  }, [user, bucket, path, validateFile, onUploadComplete, onUploadError, supabase])

  return (
    <div className="file-upload">
      <input
        type="file"
        accept={accept}
        onChange={(e) => {
          const file = e.target.files?.[0]
          if (file) uploadFile(file)
        }}
        disabled={uploading}
        className="hidden"
        id="file-input"
      />

      <label
        htmlFor="file-input"
        className={`
          cursor-pointer border-2 border-dashed border-gray-300 
          rounded-lg p-6 text-center hover:border-gray-400
          ${uploading ? 'opacity-50 cursor-not-allowed' : ''}
        `}
      >
        {uploading ? (
          <div>
            <div className="mb-2">Uploading... {Math.round(progress)}%</div>
            <div className="w-full bg-gray-200 rounded-full h-2">
              <div 
                className="bg-blue-600 h-2 rounded-full transition-all duration-300"
                style={{ width: `${progress}%` }}
              />
            </div>
          </div>
        ) : (
          <div>
            <div className="text-gray-600 mb-2">Click to upload file</div>
            <div className="text-sm text-gray-400">
              Max size: {Math.round(maxSize / 1024 / 1024)}MB
            </div>
          </div>
        )}
      </label>
    </div>
  )
}

Enter fullscreen mode Exit fullscreen mode

Server-Side Upload Validation

Implement server-side validation and processing for uploaded files.

// app/api/files/upload/route.ts
import { createClient } from '@/lib/supabase/server'
import { NextRequest } from 'next/server'

const ALLOWED_MIME_TYPES = {
  images: ['image/jpeg', 'image/png', 'image/webp', 'image/gif'],
  documents: ['application/pdf', 'text/plain', 'application/msword'],
  videos: ['video/mp4', 'video/webm', 'video/quicktime']
}

const MAX_FILE_SIZES = {
  images: 10 * 1024 * 1024, // 10MB
  documents: 50 * 1024 * 1024, // 50MB
  videos: 500 * 1024 * 1024 // 500MB
}

export async function POST(request: NextRequest) {
  try {
    const supabase = createClient()

    // Verify authentication
    const { data: { user }, error: authError } = await supabase.auth.getUser()
    if (authError || !user) {
      return Response.json({ error: 'Unauthorized' }, { status: 401 })
    }

    const formData = await request.formData()
    const file = formData.get('file') as File
    const bucket = formData.get('bucket') as string
    const category = formData.get('category') as keyof typeof ALLOWED_MIME_TYPES

    if (!file || !bucket || !category) {
      return Response.json({ error: 'Missing required fields' }, { status: 400 })
    }

    // Validate file type
    if (!ALLOWED_MIME_TYPES[category]?.includes(file.type)) {
      return Response.json({ 
        error: `Invalid file type. Allowed: ${ALLOWED_MIME_TYPES[category]?.join(', ')}` 
      }, { status: 400 })
    }

    // Validate file size
    if (file.size > MAX_FILE_SIZES[category]) {
      return Response.json({ 
        error: `File too large. Max size: ${MAX_FILE_SIZES[category] / 1024 / 1024}MB` 
      }, { status: 400 })
    }

    // Generate secure filename
    const fileExt = file.name.split('.').pop()?.toLowerCase()
    const fileName = `${user.id}/${Date.now()}-${crypto.randomUUID()}.${fileExt}`

    // Upload to Supabase Storage
    const { data, error } = await supabase.storage
      .from(bucket)
      .upload(fileName, file, {
        cacheControl: '3600',
        upsert: false
      })

    if (error) {
      console.error('Storage upload error:', error)
      return Response.json({ error: 'Upload failed' }, { status: 500 })
    }

    // Save metadata
    const { data: fileRecord, error: dbError } = await supabase
      .from('file_uploads')
      .insert({
        filename: fileName,
        original_filename: file.name,
        file_size: file.size,
        mime_type: file.type,
        storage_path: data.path,
        bucket_name: bucket,
        uploaded_by: user.id,
        upload_completed_at: new Date().toISOString()
      })
      .select()
      .single()

    if (dbError) {
      console.error('Database error:', dbError)
      // Clean up uploaded file
      await supabase.storage.from(bucket).remove([data.path])
      return Response.json({ error: 'Failed to save file metadata' }, { status: 500 })
    }

    return Response.json({
      id: fileRecord.id,
      filename: file.name,
      path: data.path,
      size: file.size,
      type: file.type
    })

  } catch (error) {
    console.error('Upload API error:', error)
    return Response.json({ error: 'Internal server error' }, { status: 500 })
  }
}

Enter fullscreen mode Exit fullscreen mode

Image Optimization and Processing

Automatic Image Transformations

Use Supabase Storage transformations for automatic image optimization.

// lib/image/transformations.ts
import { createClient } from '@/lib/supabase/client'

export class ImageTransformations {
  private supabase = createClient()

  getOptimizedImageUrl(
    bucket: string, 
    path: string, 
    options: {
      width?: number
      height?: number
      quality?: number
      format?: 'webp' | 'jpeg' | 'png'
      resize?: 'cover' | 'contain' | 'fill'
    } = {}
  ): string {
    const { data } = this.supabase.storage
      .from(bucket)
      .getPublicUrl(path, {
        transform: {
          width: options.width,
          height: options.height,
          quality: options.quality || 80,
          format: options.format || 'webp',
          resize: options.resize || 'cover'
        }
      })

    return data.publicUrl
  }

  generateResponsiveImageUrls(bucket: string, path: string) {
    const sizes = [
      { width: 320, suffix: 'mobile' },
      { width: 768, suffix: 'tablet' },
      { width: 1024, suffix: 'desktop' },
      { width: 1920, suffix: 'large' }
    ]

    return sizes.map(size => ({
      url: this.getOptimizedImageUrl(bucket, path, { 
        width: size.width,
        quality: 80,
        format: 'webp'
      }),
      width: size.width,
      suffix: size.suffix
    }))
  }

  generateImageSrcSet(bucket: string, path: string, baseWidth: number) {
    const densities = [1, 1.5, 2, 3]

    return densities
      .map(density => {
        const width = Math.round(baseWidth * density)
        const url = this.getOptimizedImageUrl(bucket, path, { 
          width,
          quality: density > 2 ? 70 : 80,
          format: 'webp'
        })
        return `${url} ${density}x`
      })
      .join(', ')
  }
}

// Usage in components
// components/OptimizedImage.tsx
'use client'

import Image from 'next/image'
import { ImageTransformations } from '@/lib/image/transformations'

interface OptimizedImageProps {
  bucket: string
  path: string
  alt: string
  width: number
  height: number
  className?: string
  priority?: boolean
}

export function OptimizedImage({
  bucket,
  path,
  alt,
  width,
  height,
  className,
  priority = false
}: OptimizedImageProps) {
  const imageTransforms = new ImageTransformations()

  const src = imageTransforms.getOptimizedImageUrl(bucket, path, {
    width,
    height,
    quality: 80,
    format: 'webp'
  })

  const srcSet = imageTransforms.generateImageSrcSet(bucket, path, width)

  return (
    <Image
      src={src}
      srcSet={srcSet}
      alt={alt}
      width={width}
      height={height}
      className={className}
      priority={priority}
      sizes="(max-width: 768px) 100vw, (max-width: 1200px) 50vw, 33vw"
    />
  )
}

Enter fullscreen mode Exit fullscreen mode

Image Processing Pipeline

Implement server-side image processing for advanced transformations.

// lib/image/processor.ts
import sharp from 'sharp'
import { createClient } from '@/lib/supabase/server'

export class ImageProcessor {
  private supabase = createClient()

  async processAndUpload(
    file: File,
    bucket: string,
    path: string,
    options: {
      maxWidth?: number
      maxHeight?: number
      quality?: number
      generateThumbnail?: boolean
      watermark?: boolean
    } = {}
  ) {
    const buffer = Buffer.from(await file.arrayBuffer())

    // Process main image
    const processedImage = await this.processImage(buffer, {
      maxWidth: options.maxWidth || 1920,
      maxHeight: options.maxHeight || 1080,
      quality: options.quality || 80,
      watermark: options.watermark
    })

    // Upload main image
    const { data: mainUpload, error: mainError } = await this.supabase.storage
      .from(bucket)
      .upload(path, processedImage, {
        contentType: 'image/webp',
        cacheControl: '31536000' // 1 year
      })

    if (mainError) throw mainError

    const results = { main: mainUpload }

    // Generate and upload thumbnail if requested
    if (options.generateThumbnail) {
      const thumbnail = await this.generateThumbnail(buffer)
      const thumbnailPath = path.replace(/\.[^.]+$/, '_thumb.webp')

      const { data: thumbUpload, error: thumbError } = await this.supabase.storage
        .from(bucket)
        .upload(thumbnailPath, thumbnail, {
          contentType: 'image/webp',
          cacheControl: '31536000'
        })

      if (thumbError) throw thumbError
      results.thumbnail = thumbUpload
    }

    return results
  }

  private async processImage(
    buffer: Buffer,
    options: {
      maxWidth: number
      maxHeight: number
      quality: number
      watermark?: boolean
    }
  ): Promise<Buffer> {
    let pipeline = sharp(buffer)
      .resize(options.maxWidth, options.maxHeight, {
        fit: 'inside',
        withoutEnlargement: true
      })
      .webp({ quality: options.quality })

    // Add watermark if requested
    if (options.watermark) {
      const watermarkBuffer = await this.createWatermark()
      pipeline = pipeline.composite([{
        input: watermarkBuffer,
        gravity: 'southeast'
      }])
    }

    return pipeline.toBuffer()
  }

  private async generateThumbnail(buffer: Buffer): Promise<Buffer> {
    return sharp(buffer)
      .resize(300, 300, {
        fit: 'cover',
        position: 'center'
      })
      .webp({ quality: 70 })
      .toBuffer()
  }

  private async createWatermark(): Promise<Buffer> {
    return sharp({
      create: {
        width: 200,
        height: 50,
        channels: 4,
        background: { r: 255, g: 255, b: 255, alpha: 0.5 }
      }
    })
    .png()
    .toBuffer()
  }
}

Enter fullscreen mode Exit fullscreen mode

Advanced File Management

Chunked Upload Implementation

Implement chunked uploads for large files with resume capability.

// lib/upload/chunked-uploader.ts
export class ChunkedUploader {
  private supabase = createClient()
  private chunkSize = 5 * 1024 * 1024 // 5MB chunks

  async uploadLargeFile(
    file: File,
    bucket: string,
    path: string,
    onProgress?: (progress: number) => void
  ): Promise<string> {
    const totalChunks = Math.ceil(file.size / this.chunkSize)
    const uploadId = crypto.randomUUID()

    // Initialize multipart upload
    const { data: upload, error } = await this.supabase
      .rpc('initialize_multipart_upload', {
        bucket_name: bucket,
        object_path: path,
        upload_id: uploadId
      })

    if (error) throw error

    const uploadedParts: Array<{ partNumber: number, etag: string }> = []

    // Upload chunks sequentially
    for (let i = 0; i < totalChunks; i++) {
      const start = i * this.chunkSize
      const end = Math.min(start + this.chunkSize, file.size)
      const chunk = file.slice(start, end)

      const partNumber = i + 1
      const { data: part, error: partError } = await this.uploadChunk(
        bucket,
        path,
        uploadId,
        partNumber,
        chunk
      )

      if (partError) {
        // Abort multipart upload on error
        await this.abortMultipartUpload(bucket, path, uploadId)
        throw partError
      }

      uploadedParts.push({ partNumber, etag: part.etag })
      onProgress?.(((i + 1) / totalChunks) * 100)
    }

    // Complete multipart upload
    const { data: result, error: completeError } = await this.supabase
      .rpc('complete_multipart_upload', {
        bucket_name: bucket,
        object_path: path,
        upload_id: uploadId,
        parts: uploadedParts
      })

    if (completeError) throw completeError

    return result.path
  }

  private async uploadChunk(
    bucket: string,
    path: string,
    uploadId: string,
    partNumber: number,
    chunk: Blob
  ) {
    return this.supabase.rpc('upload_part', {
      bucket_name: bucket,
      object_path: path,
      upload_id: uploadId,
      part_number: partNumber,
      data: chunk
    })
  }

  private async abortMultipartUpload(
    bucket: string,
    path: string,
    uploadId: string
  ) {
    return this.supabase.rpc('abort_multipart_upload', {
      bucket_name: bucket,
      object_path: path,
      upload_id: uploadId
    })
  }
}

Enter fullscreen mode Exit fullscreen mode

File Cleanup and Lifecycle Management

Implement automatic cleanup of unused files and lifecycle management.

// lib/storage/cleanup.ts
export class StorageCleanup {
  private supabase = createClient()

  async cleanupOrphanedFiles(bucket: string, olderThanDays = 7) {
    // Find files not referenced in database
    const { data: orphanedFiles } = await this.supabase
      .rpc('find_orphaned_files', {
        bucket_name: bucket,
        older_than_days: olderThanDays
      })

    if (!orphanedFiles?.length) return { deleted: 0 }

    // Delete orphaned files in batches
    const batchSize = 100
    let totalDeleted = 0

    for (let i = 0; i < orphanedFiles.length; i += batchSize) {
      const batch = orphanedFiles.slice(i, i + batchSize)
      const paths = batch.map(file => file.name)

      const { error } = await this.supabase.storage
        .from(bucket)
        .remove(paths)

      if (!error) {
        totalDeleted += batch.length
      }
    }

    return { deleted: totalDeleted }
  }

  async archiveOldFiles(bucket: string, archiveBucket: string, olderThanDays = 365) {
    const { data: oldFiles } = await this.supabase
      .rpc('find_old_files', {
        bucket_name: bucket,
        older_than_days: olderThanDays
      })

    if (!oldFiles?.length) return { archived: 0 }

    let totalArchived = 0

    for (const file of oldFiles) {
      try {
        // Copy to archive bucket
        const { error: copyError } = await this.supabase.storage
          .from(archiveBucket)
          .copy(file.name, `${bucket}/${file.name}`)

        if (copyError) continue

        // Delete from original bucket
        const { error: deleteError } = await this.supabase.storage
          .from(bucket)
          .remove([file.name])

        if (!deleteError) {
          totalArchived++
        }
      } catch (error) {
        console.error(`Failed to archive file ${file.name}:`, error)
      }
    }

    return { archived: totalArchived }
  }
}

// Database functions for cleanup operations

Enter fullscreen mode Exit fullscreen mode

-- Function to find orphaned files
CREATE OR REPLACE FUNCTION find_orphaned_files(
  bucket_name TEXT,
  older_than_days INTEGER DEFAULT 7
)
RETURNS TABLE (name TEXT, created_at TIMESTAMPTZ) AS $$
BEGIN
  RETURN QUERY
  SELECT o.name, o.created_at
  FROM storage.objects o
  WHERE o.bucket_id = bucket_name
    AND o.created_at < NOW() - INTERVAL '1 day' * older_than_days
    AND NOT EXISTS (
      SELECT 1 FROM file_uploads f 
      WHERE f.storage_path = o.name
    );
END;
$$ LANGUAGE plpgsql;

-- Function to find old files for archiving
CREATE OR REPLACE FUNCTION find_old_files(
  bucket_name TEXT,
  older_than_days INTEGER DEFAULT 365
)
RETURNS TABLE (name TEXT, created_at TIMESTAMPTZ, size BIGINT) AS $$
BEGIN
  RETURN QUERY
  SELECT o.name, o.created_at, o.metadata->>'size'::BIGINT
  FROM storage.objects o
  WHERE o.bucket_id = bucket_name
    AND o.created_at < NOW() - INTERVAL '1 day' * older_than_days;
END;
$$ LANGUAGE plpgsql;

Enter fullscreen mode Exit fullscreen mode

This comprehensive guide provides everything you need to implement robust file storage and media handling with Supabase and Next.js. From secure uploads to advanced image processing, these patterns will help you build scalable, efficient file management systems that provide excellent user experiences while maintaining security and performance.

Remember to monitor storage usage, implement proper cleanup procedures, and optimize images for web delivery to keep costs manageable and performance optimal.

CDN Integration and Performance

Implementing CDN Caching

Optimize file delivery with proper CDN configuration and caching strategies.

// lib/cdn/configuration.ts
export class CDNManager {
  private supabase = createClient()

  getCDNUrl(bucket: string, path: string, options: {
    cacheDuration?: number
    transform?: {
      width?: number
      height?: number
      quality?: number
      format?: string
    }
  } = {}): string {
    const baseUrl = `${process.env.SUPABASE_URL}/storage/v1/object/public/${bucket}/${path}`

    const params = new URLSearchParams()

    // Add cache control
    if (options.cacheDuration) {
      params.append('cache', options.cacheDuration.toString())
    }

    // Add transformation parameters
    if (options.transform) {
      Object.entries(options.transform).forEach(([key, value]) => {
        if (value !== undefined) {
          params.append(key, value.toString())
        }
      })
    }

    return params.toString() ? `${baseUrl}?${params.toString()}` : baseUrl
  }

  async preloadCriticalImages(images: Array<{ bucket: string, path: string }>) {
    // Preload critical images for better performance
    const preloadPromises = images.map(({ bucket, path }) => {
      const url = this.getCDNUrl(bucket, path, {
        cacheDuration: 31536000, // 1 year
        transform: { quality: 80, format: 'webp' }
      })

      return new Promise((resolve, reject) => {
        const img = new Image()
        img.onload = resolve
        img.onerror = reject
        img.src = url
      })
    })

    try {
      await Promise.all(preloadPromises)
    } catch (error) {
      console.warn('Some images failed to preload:', error)
    }
  }

  generateImageSizes(bucket: string, path: string) {
    const breakpoints = [320, 640, 768, 1024, 1280, 1920]

    return breakpoints.map(width => ({
      width,
      url: this.getCDNUrl(bucket, path, {
        cacheDuration: 31536000,
        transform: { width, quality: 80, format: 'webp' }
      })
    }))
  }
}

Enter fullscreen mode Exit fullscreen mode

Performance Monitoring

Monitor file delivery performance and optimize based on metrics.

// lib/monitoring/storage-metrics.ts
export class StorageMetrics {
  private supabase = createClient()

  async trackFileAccess(fileId: string, accessType: 'view' | 'download') {
    await this.supabase
      .from('file_access_logs')
      .insert({
        file_id: fileId,
        access_type: accessType,
        user_agent: navigator.userAgent,
        timestamp: new Date().toISOString()
      })
  }

  async getStorageUsageStats(organizationId: string) {
    const { data, error } = await this.supabase
      .rpc('get_storage_usage_stats', {
        org_id: organizationId
      })

    if (error) throw error
    return data
  }

  async getPopularFiles(organizationId: string, days = 30) {
    const { data, error } = await this.supabase
      .rpc('get_popular_files', {
        org_id: organizationId,
        days_back: days
      })

    if (error) throw error
    return data
  }
}

Enter fullscreen mode Exit fullscreen mode

-- Storage usage analytics functions
CREATE OR REPLACE FUNCTION get_storage_usage_stats(org_id UUID)
RETURNS TABLE (
  total_files BIGINT,
  total_size BIGINT,
  avg_file_size NUMERIC,
  files_by_type JSONB
) AS $$
BEGIN
  RETURN QUERY
  SELECT 
    COUNT(*) as total_files,
    SUM(file_size) as total_size,
    AVG(file_size) as avg_file_size,
    jsonb_object_agg(
      SPLIT_PART(mime_type, '/', 1),
      file_count
    ) as files_by_type
  FROM (
    SELECT 
      mime_type,
      file_size,
      COUNT(*) OVER (PARTITION BY SPLIT_PART(mime_type, '/', 1)) as file_count
    FROM file_uploads
    WHERE organization_id = org_id
      AND upload_completed_at IS NOT NULL
  ) stats;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION get_popular_files(
  org_id UUID,
  days_back INTEGER DEFAULT 30
)
RETURNS TABLE (
  file_id UUID,
  filename TEXT,
  access_count BIGINT,
  last_accessed TIMESTAMPTZ
) AS $$
BEGIN
  RETURN QUERY
  SELECT 
    f.id,
    f.original_filename,
    COUNT(al.id) as access_count,
    MAX(al.timestamp) as last_accessed
  FROM file_uploads f
  LEFT JOIN file_access_logs al ON f.id = al.file_id
  WHERE f.organization_id = org_id
    AND (al.timestamp IS NULL OR al.timestamp > NOW() - INTERVAL '1 day' * days_back)
  GROUP BY f.id, f.original_filename
  ORDER BY access_count DESC, last_accessed DESC
  LIMIT 50;
END;
$$ LANGUAGE plpgsql;

Enter fullscreen mode Exit fullscreen mode

This comprehensive file storage and media handling guide provides production-ready patterns for building scalable, secure, and performant file management systems with Next.js and Supabase. The techniques covered here will help you handle everything from simple image uploads to complex media processing workflows while maintaining optimal performance and user experience.

Originally published at https://www.iloveblogs.blog