pinakes/migrations/postgres/V15__managed_storage.sql

-- V15: Managed File Storage
-- Adds server-side content-addressable storage for uploaded files

-- Add storage mode to media_items (external = file on disk, managed = in content-addressable storage)
ALTER TABLE media_items ADD COLUMN storage_mode TEXT NOT NULL DEFAULT 'external';

-- Original filename for managed uploads (preserved separately from file_name which may be normalized)
ALTER TABLE media_items ADD COLUMN original_filename TEXT;

-- When the file was uploaded to managed storage
ALTER TABLE media_items ADD COLUMN uploaded_at TIMESTAMPTZ;

-- Storage key for looking up the blob (usually same as content_hash for deduplication)
ALTER TABLE media_items ADD COLUMN storage_key TEXT;

-- Managed blobs table - tracks deduplicated file storage
CREATE TABLE managed_blobs (
    content_hash TEXT PRIMARY KEY NOT NULL,
    file_size BIGINT NOT NULL,
    mime_type TEXT NOT NULL,
    reference_count INTEGER NOT NULL DEFAULT 1,
    stored_at TIMESTAMPTZ NOT NULL,
    last_verified TIMESTAMPTZ
);

-- Index for finding managed media items
CREATE INDEX idx_media_storage_mode ON media_items(storage_mode);

-- Index for finding orphaned blobs (reference_count = 0)
CREATE INDEX idx_blobs_reference_count ON managed_blobs(reference_count);