NotAShelf
9f9aa80265
Signed-off-by: NotAShelf <raf@notashelf.dev> Change-Id: I98b8ed2eee464ecfd42f492dec49adeb6a6a6964
33 lines
1.2 KiB
SQL
33 lines
1.2 KiB
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);
|