50 lines
2.1 KiB
SQL
50 lines
2.1 KiB
SQL
-- ============================================
|
|
-- DUAL STORAGE SUPPORT FOR DOCUMENTS
|
|
-- Store URLs for both Supabase Storage and S3 backends
|
|
-- Mirrors the avatar dual-storage pattern from migration 009
|
|
-- ============================================
|
|
|
|
-- Add local storage URL column
|
|
ALTER TABLE public.documents ADD COLUMN IF NOT EXISTS file_url_local TEXT;
|
|
|
|
-- Add S3 storage URL column
|
|
ALTER TABLE public.documents ADD COLUMN IF NOT EXISTS file_url_s3 TEXT;
|
|
|
|
-- Add storage path column (relative path used for both backends)
|
|
ALTER TABLE public.documents ADD COLUMN IF NOT EXISTS storage_path TEXT;
|
|
|
|
-- Add comments for documentation
|
|
COMMENT ON COLUMN public.documents.file_path IS 'Current active file URL (computed based on storage setting) - kept for backwards compatibility';
|
|
COMMENT ON COLUMN public.documents.file_url_local IS 'File URL when stored in Supabase Storage';
|
|
COMMENT ON COLUMN public.documents.file_url_s3 IS 'File URL when stored in S3/MinIO';
|
|
COMMENT ON COLUMN public.documents.storage_path IS 'Storage path for file (e.g., timestamp-random-filename.pdf)';
|
|
|
|
-- Migrate existing file_path values to storage_path and file_url_local
|
|
-- This handles documents uploaded before dual-storage was implemented
|
|
UPDATE public.documents
|
|
SET
|
|
storage_path = CASE
|
|
WHEN file_path LIKE 'http%' THEN
|
|
-- Extract filename from URL
|
|
CASE
|
|
WHEN file_path LIKE '%/storage/v1/object/public/documents/%' THEN
|
|
substring(file_path from '/storage/v1/object/public/documents/([^?]+)')
|
|
WHEN file_path LIKE '%/documents/%' THEN
|
|
substring(file_path from '/documents/([^?]+)')
|
|
ELSE file_path
|
|
END
|
|
ELSE file_path
|
|
END,
|
|
file_url_local = CASE
|
|
WHEN file_path LIKE 'http%' AND file_path LIKE '%/storage/v1/object/public/documents/%' THEN file_path
|
|
ELSE NULL
|
|
END,
|
|
file_url_s3 = CASE
|
|
WHEN file_path LIKE 'http%' AND file_path NOT LIKE '%/storage/v1/object/public/documents/%' THEN file_path
|
|
ELSE NULL
|
|
END
|
|
WHERE storage_path IS NULL;
|
|
|
|
-- Create index for faster lookups
|
|
CREATE INDEX IF NOT EXISTS idx_documents_storage_path ON public.documents(storage_path);
|