template-saas/apps/database/ddl/schemas/storage/tables/01-files.sql

-- ============================================
-- Storage Files Table
-- Main table for tracking uploaded files
-- ============================================

CREATE TABLE storage.files (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants.tenants(id) ON DELETE CASCADE,
    uploaded_by UUID NOT NULL REFERENCES users.users(id) ON DELETE SET NULL,

    -- File info
    filename VARCHAR(255) NOT NULL,
    original_name VARCHAR(500) NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    size_bytes BIGINT NOT NULL CHECK (size_bytes > 0),

    -- Storage location
    bucket VARCHAR(100) NOT NULL,
    path VARCHAR(1000) NOT NULL,
    provider storage.storage_provider NOT NULL DEFAULT 's3',

    -- Status
    status storage.file_status NOT NULL DEFAULT 'ready',
    visibility storage.visibility NOT NULL DEFAULT 'private',

    -- Metadata
    metadata JSONB DEFAULT '{}',
    thumbnails JSONB DEFAULT '{}',

    -- Folder organization
    folder VARCHAR(100) DEFAULT 'files',

    -- Timestamps
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMPTZ,

    -- Unique constraint on path within bucket
    CONSTRAINT uq_storage_files_path UNIQUE (bucket, path)
);

-- Indexes
CREATE INDEX idx_storage_files_tenant ON storage.files(tenant_id);
CREATE INDEX idx_storage_files_tenant_folder ON storage.files(tenant_id, folder);
CREATE INDEX idx_storage_files_uploaded_by ON storage.files(uploaded_by);
CREATE INDEX idx_storage_files_mime_type ON storage.files(mime_type);
CREATE INDEX idx_storage_files_status ON storage.files(status);
CREATE INDEX idx_storage_files_created_at ON storage.files(created_at DESC);
CREATE INDEX idx_storage_files_deleted ON storage.files(tenant_id, deleted_at) WHERE deleted_at IS NULL;

-- RLS
ALTER TABLE storage.files ENABLE ROW LEVEL SECURITY;

CREATE POLICY storage_files_tenant_isolation ON storage.files
    FOR ALL
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- Comments
COMMENT ON TABLE storage.files IS 'Uploaded files metadata and storage location';
COMMENT ON COLUMN storage.files.path IS 'Full path in bucket: tenant_id/folder/upload_id/filename';
COMMENT ON COLUMN storage.files.thumbnails IS 'Generated thumbnail paths as JSON {thumb: path, medium: path}';
COMMENT ON COLUMN storage.files.folder IS 'Logical folder: avatars, documents, imports, etc.';


-- ============================================
-- Pending Uploads Table
-- Tracks upload requests before confirmation
-- ============================================

CREATE TABLE storage.pending_uploads (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants.tenants(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES users.users(id) ON DELETE CASCADE,

    -- Upload request info
    filename VARCHAR(255) NOT NULL,
    original_name VARCHAR(500) NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    size_bytes BIGINT NOT NULL,
    folder VARCHAR(100) DEFAULT 'files',

    -- Presigned URL info
    bucket VARCHAR(100) NOT NULL,
    path VARCHAR(1000) NOT NULL,
    provider storage.storage_provider NOT NULL DEFAULT 's3',

    -- Status
    status VARCHAR(20) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'completed', 'expired', 'failed')),
    expires_at TIMESTAMPTZ NOT NULL,

    -- Timestamps
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    completed_at TIMESTAMPTZ
);

-- Indexes
CREATE INDEX idx_storage_pending_tenant ON storage.pending_uploads(tenant_id);
CREATE INDEX idx_storage_pending_user ON storage.pending_uploads(user_id);
CREATE INDEX idx_storage_pending_status ON storage.pending_uploads(status);
CREATE INDEX idx_storage_pending_expires ON storage.pending_uploads(expires_at) WHERE status = 'pending';

-- RLS
ALTER TABLE storage.pending_uploads ENABLE ROW LEVEL SECURITY;

CREATE POLICY storage_pending_tenant_isolation ON storage.pending_uploads
    FOR ALL
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- Auto-cleanup expired uploads (can be called by cron)
CREATE OR REPLACE FUNCTION storage.cleanup_expired_uploads()
RETURNS INTEGER AS $$
DECLARE
    deleted_count INTEGER;
BEGIN
    UPDATE storage.pending_uploads
    SET status = 'expired'
    WHERE status = 'pending'
    AND expires_at < NOW();

    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    RETURN deleted_count;
END;
$$ LANGUAGE plpgsql;

COMMENT ON TABLE storage.pending_uploads IS 'Pending file uploads awaiting confirmation';
COMMENT ON FUNCTION storage.cleanup_expired_uploads IS 'Mark expired pending uploads';